Skip to main content

Database configuration

The ./config/database.js file (or the ./config/database.ts file for TypeScript) is used to define database connections that will be used to store the application content.

Warning

Strapi applications are not meant to be connected to a pre-existing database, not created by a Strapi application, nor connected to a Strapi v3 database. The Strapi team will not support such attempts. Attempting to connect to an unsupported database may, and most likely will, result in lost data.

Supported databases

The CLI installation guide details supported database and versions.

Configuration structure​

The /config/database.js|ts file accepts 2 main configuration objects:

connection configuration object​

ParameterDescriptionTypeDefault
clientDatabase client to create the connection.
Accepts the following values:
  • sqlite for SQLite databases
  • postgres for PostgreSQL databases
  • mysql for MySQL databases
String-
connectionDatabase connection informationKnex.Config object<br />Knex.Config Function (experimental)-
debugShow database exchanges and errors.Booleanfalse
useNullAsDefault

Optional, only for SQLite		Use NULL as a default valueBooleantrue
pool

Optional		Database pooling optionsObject-
acquireConnectionTimeout

Optional		How long knex will wait before throwing a timeout error when acquiring a connection (in milliseconds)Integer60000
Note

Strapi only supports the following client values, and will automatically rewrite the client value to the following options before passing the configuration to Knex:

client valueActual package used
sqlitebetter-sqlite3
mysqlmysql2
postgrespg

Connection parameters​

The connection.connection object found in ./config/database.js (or ./config/database.ts for TypeScript) is used to pass database connection information and accepts the following parameters:

ParameterDescriptionType
connectionStringDatabase connection string. When set, it overrides the other connection.connection properties. To disable use an empty string: ''.
Available in v4.6.2+		String
hostDatabase host name. Default value: localhost.String
portDatabase portInteger
databaseDatabase name.String
userUsername used to establish the connectionString
passwordPassword used to establish the connectionString
timezoneSet the default behavior for local time. Default value: utc Timezone optionsString
schemaSet the default database schema. Used only for Postgres DB.String
sslFor SSL database connection.
Use an object to pass certificate files as strings.		Boolean or Object
Note

Depending on the database client used, more parameters can be set (e.g., charset and collation for mysql). Check the database client documentation to know what parameters are available, for instance the pg, mysql, and better-sqlite3 documentations.

Database pooling options​

The connection.pool object optionally found in ./config/database.js (or ./config/database.ts for TypeScript) is used to pass Tarn.js database pooling options and accepts the following parameters:

Caution

When using Docker, change the pool min value to 0 as Docker will kill any idle connections, making it impossible to keep any open connections to the database (see the Tarn.js pool settings used by Knex.js for more information).

ParameterDescriptionTypeDefault
minMinimum number of database connections to keepaliveInteger2
maxMaximum number of database connections to keepaliveInteger10
acquireTimeoutMillisTime in milliseconds before timing out a database connection attemptInteger60000
createTimeoutMillisTime in milliseconds before timing out a create query attemptInteger30000
destroyTimeoutMillisTime in milliseconds before timing out a destroy query attemptInteger5000
idleTimeoutMillisTime in milliseconds before free database connections are destroyedInteger30000
reapIntervalMillisTime in milliseconds to check for idle database connections to destroyInteger1000
createRetryIntervalMillisTime in milliseconds to idle before retrying failed create actionsInteger200
afterCreateCallback function to execute custom logic when the pool acquires a new connection.

See the Knex.js documentation for more information		Function-

settings configuration object​

The settings object found in ./config/database.js (or ./config/database.ts for TypeScript) is used to configure Strapi-specific database settings and accepts the following parameters:

ParameterDescriptionTypeDefault
forceMigrationEnable or disable the forced database migration.Booleantrue
runMigrationsEnable or disable database migrations from running on start up.Booleantrue
useTypescriptMigrationsLook for migrations in the build dir instead of the src dirBooleanfalse
Note

When using useTypescriptMigrations you can continue to use existing JavaScript migrations by setting compilerOptions { allowJs: true } in your tsconfig file.

Configuration examples​

./config/database.js
module.exports = ({ env }) => ({
  connection: {
    client: 'postgres',
    connection: {
      host: env('DATABASE_HOST', '127.0.0.1'),
      port: env.int('DATABASE_PORT', 5432),
      database: env('DATABASE_NAME', 'strapi'),
      user: env('DATABASE_USERNAME', 'strapi'),
      password: env('DATABASE_PASSWORD', 'strapi'),
      schema: env('DATABASE_SCHEMA', 'public'), // Not required
      ssl: {
        rejectUnauthorized: env.bool('DATABASE_SSL_SELF', false), // For self-signed certificates
      },
    },
    debug: false,
  },
});
Caution

Strapi is aware that there is an issue regarding SSL support for the server. In order to fix it, you have to set the ssl:{} object as a boolean in order to disable it. See below for example:

module.exports = ({ env }) => ({
  connection: {
    client: 'postgres',
    connection: {
      ...
      ssl: env('DATABASE_SSL', false)
    },
  },
});

Please note that if you need client side SSL CA verification you will need to use the ssl:{} object with the fs module to convert your CA certificate to a string. You can see an example below:

const fs = require('fs');
module.exports = ({ env }) => ({
  connection: {
    client: 'postgres',
    connection: {
      ...
      ssl: {
        ca: fs.readFileSync(`${__dirname}/path/to/your/ca-certificate.crt`).toString(),
      },
    },
  },
});

Configuration in database​

Configuration files are not multi-server friendly. To update configurations in production you can use a data store to get and set settings.

Get settings​

  • environment (string): Sets the environment you want to store the data in. By default it's current environment (can be an empty string if your configuration is environment agnostic).
  • type (string): Sets if your configuration is for an api, plugin or core. By default it's core.
  • name (string): You have to set the plugin or api name if type is api or plugin.
  • key (string, required): The name of the key you want to store.
// strapi.store(object).get(object);
// create reusable plugin store variable
const pluginStore = strapi.store({
  environment: strapi.config.environment,
  type: 'plugin',
  name: 'users-permissions',
});
await pluginStore.get({ key: 'grant' });

Set settings​

  • value (any, required): The value you want to store.
// strapi.store(object).set(object);
// create reusable plugin store variable
const pluginStore = strapi.store({
  environment: strapi.config.environment,
  type: 'plugin',
  name: 'users-permissions'
});
await pluginStore.set({
  key: 'grant',
  value: {
    ...
  }
});

Environment variables in database configurations​

Strapi version v4.6.2 and higher includes the database configuration options in the ./config/database.js or ./config/database.ts file. When a new project is created the environment variable DATABASE_CLIENT with the value mysql, postgres, or sqlite is automatically added to the .env file depending on which database you choose during project creation. Additionally, all of the environment variables necessary to connect to your local development database are also added to the .env file. The following is an example of the generated configuration file:

const path = require('path');

module.exports = ({ env }) => {
  const client = env('DATABASE_CLIENT', 'sqlite');

  const connections = {
    mysql: {
      connection: {
        connectionString: env('DATABASE_URL'),
        host: env('DATABASE_HOST', 'localhost'),
        port: env.int('DATABASE_PORT', 3306),
        database: env('DATABASE_NAME', 'strapi'),
        user: env('DATABASE_USERNAME', 'strapi'),
        password: env('DATABASE_PASSWORD', 'strapi'),
        ssl: env.bool('DATABASE_SSL', false) && {
          key: env('DATABASE_SSL_KEY', undefined),
          cert: env('DATABASE_SSL_CERT', undefined),
          ca: env('DATABASE_SSL_CA', undefined),
          capath: env('DATABASE_SSL_CAPATH', undefined),
          cipher: env('DATABASE_SSL_CIPHER', undefined),
          rejectUnauthorized: env.bool(
            'DATABASE_SSL_REJECT_UNAUTHORIZED',
            true
          ),
        },
      },
      pool: { min: env.int('DATABASE_POOL_MIN', 2), max: env.int('DATABASE_POOL_MAX', 10) },
    },
    postgres: {
      connection: {
        connectionString: env('DATABASE_URL'),
        host: env('DATABASE_HOST', 'localhost'),
        port: env.int('DATABASE_PORT', 3306),
        database: env('DATABASE_NAME', 'strapi'),
        user: env('DATABASE_USERNAME', 'strapi'),
        password: env('DATABASE_PASSWORD', 'strapi'),
        ssl: env.bool('DATABASE_SSL', false) && {
          key: env('DATABASE_SSL_KEY', undefined),
          cert: env('DATABASE_SSL_CERT', undefined),
          ca: env('DATABASE_SSL_CA', undefined),
          capath: env('DATABASE_SSL_CAPATH', undefined),
          cipher: env('DATABASE_SSL_CIPHER', undefined),
          rejectUnauthorized: env.bool(
            'DATABASE_SSL_REJECT_UNAUTHORIZED',
            true
          ),
        },
        schema: env('DATABASE_SCHEMA', 'public'),
      },
      pool: { min: env.int('DATABASE_POOL_MIN', 2), max: env.int('DATABASE_POOL_MAX', 10) },
    },
    sqlite: {
      connection: {
        filename: path.join(
          __dirname,
          '..',
          env('DATABASE_FILENAME', 'data.db')
        ),
      },
      useNullAsDefault: true,
    },
  };

  return {
    connection: {
      client,
      ...connections[client],
      acquireConnectionTimeout: env.int('DATABASE_CONNECTION_TIMEOUT', 60000),
    },
  };
};

The following are examples of the corresponding .env file database-related keys for each of the possible databases:


# Database
DATABASE_CLIENT=mysql
DATABASE_HOST=127.0.0.1
DATABASE_PORT=3306
DATABASE_NAME=strapi
DATABASE_USERNAME=strapi
DATABASE_PASSWORD=strap1
DATABASE_SSL=false

Environment variables for Strapi applications before v4.6.2​

If you started your project with a version prior to v4.6.2 you can convert your database.js|database.ts configuration file following this procedure:

  1. Update your application to v4.6.2 or a later version. See the upgrades documentation.
  2. Replace the contents of your ./config/database.js or ./config/database.ts file with the preceding JavaScript or TypeScript code.
  3. Add the environment variables from the preceding code example to your .env file.
  4. (optional) Add additional environment variables such as DATABASE_URL and the properties of the ssl object.
  5. Save the changes and restart your application.
Caution

Do not overwrite the environment variables: HOST, PORT, APP_KEYS, API_TOKEN_SALT, and ADMIN_JWT_SECRET.

Database connections using connectionString​

Many managed database solutions use the property connectionString to connect a database to an application. Strapi v4.6.2 and later versions include the connectionString property. The connectionString is a concatenation of all the database properties in the connection.connection object. The connectionString:

  • overrides the other connection.connection properties such as host and port,
  • can be disabled by setting the property to an empty string: ''.

Database management by environment​

Development of a Strapi application commonly includes customization in the local development environment with a local development database, such as SQLite. When the application is ready for another environment such as production or staging the application is deployed with a different database instance, usually MySQL, MariaDB, or PostgreSQL. Database environment variables allow you to switch the attached database. To switch the database connection:

  • set a minimum of the DATABASE_CLIENT and DATABASE_URL for MySQL, MariaDB, and PostgreSQL,
  • or set a minimum of DATABASE_CLIENT and DATABASE_FILENAME for SQLite.

For deployed versions of your application the database environment variables should be stored wherever your other secrets are stored. The following table gives examples of where the database environment variables should be stored:

Hosting optionenvironment variable storage
Virtual private server/virtual machine (e.g. AWS EC2)ecosystem.config.js or .env
DigitalOcean App PlatformEnvironment Variables table
HerokuConfig vars table

Databases installation​

Strapi gives you the option to choose the most appropriate database for your project. Strapi supports PostgreSQL, SQLite, or MySQL.

SQLite​

SQLite is the default (see Quick Start Guide) and recommended database to quickly create an application locally.

Install SQLite during application creation​

Use one of the following commands:

yarn create strapi-app my-project --quickstart

This will create a new project and launch it in the browser.

Install SQLite manually​

In a terminal, run the following command:

yarn add better-sqlite3

Add the following code to your /config/database.ts|js file:

./config/database.js
module.exports = ({ env }) => ({
  connection: {
    client: 'sqlite',
    connection: {
      filename: path.join(__dirname, '..', env('DATABASE_FILENAME', '.tmp/data.db')),
    },
    useNullAsDefault: true,
  },
});

PostgreSQL​

When connecting Strapi to a PostgreSQL database, the database user requires SCHEMA permissions. While the database admin has this permission by default, a new database user explicitly created for the Strapi application will not. This would result in a 500 error when trying to load the admin console.

To create a new PostgreSQL user with the SCHEMA permission, use the following steps:

# Create a new database user with a secure password
$ CREATE USER my_strapi_db_user WITH PASSWORD 'password';
# Connect to the database as the PostgreSQL admin
$ \c my_strapi_db_name admin_user
# Grant schema privileges to the user
$ GRANT ALL ON SCHEMA public TO my_strapi_db_user;