Database

Pruvious supports SQLite and PostgreSQL for storing website data, as well as Redis for caching resource-intensive SQL queries and JWTs.

SQLite

SQLite is a convenient database for developing your website with Pruvious. It's easy to manage because you don't need to set up a separate database server, and it's super fast. While you can use SQLite in production mode, especially if your server has a fast SSD, we recommend using PostgreSQL for live sites. It's best to work with SQLite only in the development environment.

The CMS uses the pruvious.db database file by default, which is located in the project's root directory. To change the database path, you can edit the database option in your nuxt.config.ts file like this:

# nuxt.config.ts

export default defineNuxtConfig({
  modules: ['pruvious'],
  pruvious: {
    // Use `NUXT_PRUVIOUS_DATABASE` env in production
    database: 'sqlite:./tmp/sqlite.db',
  },
})

PostgreSQL

To use PostgreSQL as your primary database, change the database option in your nuxt.config.ts file to a PostgreSQL connection URI. Here's an example:

# nuxt.config.ts

export default defineNuxtConfig({
  modules: ['pruvious'],
  pruvious: {
    // Use `NUXT_PRUVIOUS_DATABASE` env in production
    database: 'postgresql://username:password@127.0.0.1:5432/database',
  },
})

You can find more information about the connection string format on the official PostgreSQL documentation page.

Redis

If you want to optimize and reduce the number of SELECT queries on your primary database, you can use Redis to cache those queries. This is especially useful when fetching a large amount of populated collection records with many relations. You can define the execution time threshold in the cacheQueries property of your collection definitions. Additionally, user tokens will also be stored in the Redis cache when enabled.

To enable the Redis cache, set a connection string in the redis option in your nuxt.config.ts file.

# nuxt.config.ts

export default defineNuxtConfig({
  modules: ['pruvious'],
  pruvious: {
    // Use `NUXT_PRUVIOUS_REDIS` env in production
    redis: 'redis://username:password@127.0.0.1:6379/0',
  },
})

Instance

Pruvious uses Sequelize as the database ORM. You can use its instance in case you want to perform direct database operations. Here is an example of how it can be used:

# examples/db-instance.ts

import { db } from '#pruvious/server'

const instance = await db()
const queryInterface = instance.getQueryInterface()

if (!queryInterface.tableExists('custom_table')) {
  await queryInterface.createTable('custom_table', {})
}

If you only need to execute raw SQL queries, you can use the rawQuery function from the Pruvious query builder.

Migrations

In contrast to conventional frameworks, Pruvious automatically handles changes in your database structure whenever the server is restarted during development or when starting the server in production mode. Moreover, Pruvious rebuilds all indexes and constraints based on your collection and field specifications.

To deactivate automatic migrations, simply set the migration option to false in your nuxt.config.ts file.

System tables

In addition to the automatically generated tables from your collection names, Pruvious also includes the following internal system tables:

System columns

When inspecting collection tables, you may notice the following extra columns that are not specified in your collection fields: