Database Connection Settings

Craft supports several database connection settings that give you control over how Craft connects to the database.

Database connection settings may be set from config/db.php, but we recommend using environment variables (such as in your .env file).

For example, in a new Craft 4 project (opens new window), your .env file should define these environment variables:

CRAFT_APP_ID=
CRAFT_ENVIRONMENT=dev
CRAFT_SECURITY_KEY=
CRAFT_DB_DRIVER=mysql
CRAFT_DB_SERVER=127.0.0.1
CRAFT_DB_PORT=3306
CRAFT_DB_DATABASE=
CRAFT_DB_USER=root
CRAFT_DB_PASSWORD=
CRAFT_DB_SCHEMA=public
CRAFT_DB_TABLE_PREFIX=

The DB_ variables are database connection settings, and the CRAFT_ prefix is a special convention for overriding any config setting—meaning you don’t need to use a config/db.php file in Craft 4.

If you wanted to use your own environment variables in a static config file, you could create a config/db.php to return an array of settings (defined below), using the thread-safe App::env() (opens new window) to get the value of each environment variable:

use craft\helpers\App;

return [
    'driver' => App::env('MY_DB_DRIVER'),
    'server' => App::env('MY_DB_SERVER'),
    'port' => App::env('MY_DB_PORT'),
    'database' => App::env('MY_DB_DATABASE'),
    'user' => App::env('MY_DB_USER'),
    'password' => App::env('MY_DB_PASSWORD'),
    'schema' => App::env('MY_DB_SCHEMA'),
    'tablePrefix' => App::env('MY_DB_TABLE_PREFIX'),
];

You may also provide a DB_DSN environment variable. If defined, Craft will use that.

We recommend this environment variable approach for two reasons:

  1. It keeps sensitive information out of your project’s codebase. (.env files should never be shared or committed to Git.)
  2. It makes collaborating with other developers easier, as each developer can define their own settings without overwriting someone else’s settings.

Here’s the full list of database connection settings that Craft supports:

# attributes

Allowed types
array (opens new window)
Default value
[]
Defined by
DbConfig::$attributes (opens new window)

An array of key => value pairs of PDO attributes to pass into the PDO constructor.

For example, when using the MySQL PDO driver (opens new window), if you wanted to enable a SSL database connection (assuming SSL is enabled in MySQL (opens new window) and 'user' can connect via SSL, you’d set these:

[
    PDO::MYSQL_ATTR_SSL_KEY    => '/path/to/my/client-key.pem',
    PDO::MYSQL_ATTR_SSL_CERT   => '/path/to/my/client-cert.pem',
    PDO::MYSQL_ATTR_SSL_CA     => '/path/to/my/ca-cert.pem',
],

# charset

Allowed types
string (opens new window)
Default value
'utf8'
Defined by
DbConfig::$charset (opens new window)

The charset to use when creating tables.

You can change the character set and collation across all existing database tables using this terminal command:

php craft db/convert-charset

# collation

Allowed types
string (opens new window), null (opens new window)
Default value
null
Defined by
DbConfig::$collation (opens new window)
Since
3.6.4

The collation to use when creating tables.

This is only used by MySQL. If null, the charset’s (opens new window) default collation will be used.

Charset Default collation
utf8 utf8_general_ci
utf8mb4 utf8mb4_0900_ai_ci

You can change the character set and collation across all existing database tables using this terminal command:

php craft db/convert-charset

# database

The name of the database to select.

# driver

The database driver to use. Either mysql for MySQL or pgsql for PostgreSQL.

# dsn

The Data Source Name (“DSN”) that tells Craft how to connect to the database.

DSNs should begin with a driver prefix (mysql: or pgsql:), followed by driver-specific parameters. For example, mysql:host=127.0.0.1;port=3306;dbname=acme_corp.

# password

Allowed types
string (opens new window)
Default value
''
Defined by
DbConfig::$password (opens new window)

The database password to connect with.

# port

The database server port. Defaults to 3306 for MySQL and 5432 for PostgreSQL.

# schema

The schema that Postgres is configured to use by default (PostgreSQL only).

To force Craft to use the specified schema regardless of PostgreSQL’s search_path setting, you must enable the setSchemaOnConnect (opens new window) setting.

# server

The database server name or IP address. Usually localhost or 127.0.0.1.

# setSchemaOnConnect

Allowed types
boolean (opens new window)
Default value
false
Defined by
DbConfig::$setSchemaOnConnect (opens new window)
Since
3.7.27

Whether the schema (opens new window) should be explicitly used for database queries (PostgreSQL only).

This will cause an extra SET search_path SQL query to be executed per database connection. Ideally, PostgreSQL’s search_path setting should be configured to prioritize the desired schema.

# tablePrefix

If you’re sharing Craft installs in a single database (MySQL) or a single database and using a shared schema (PostgreSQL), you can set a table prefix here to avoid per-install table naming conflicts. This can be no more than 5 characters, and must be all lowercase.

# unixSocket

MySQL only. If this is set, the CLI connection string (used for yiic) will connect to the Unix socket instead of the server and port. If this is specified, then server and port settings are ignored.

# url

The database connection URL, if one was provided by your hosting environment.

If this is set, the values for driver (opens new window), user (opens new window), database (opens new window), server (opens new window), port (opens new window), and database (opens new window) will be extracted from it.

# useUnbufferedConnections

Allowed types
boolean (opens new window)
Default value
false
Defined by
DbConfig::$useUnbufferedConnections (opens new window)
Since
3.7.0

Whether batched queries should be executed on a separate, unbuffered database connection.

This setting only applies to MySQL. It can be enabled when working with high volume content, to prevent PHP from running out of memory when querying too much data at once. (See https://www.yiiframework.com/doc/guide/2.0/en/db-query-builder#batch-query-mysql (opens new window) for an explanation of MySQL’s batch query limitations.)

# user

Allowed types
string (opens new window)
Default value
'root'
Defined by
DbConfig::$user (opens new window)

The database username to connect with.