Database Connection Settings

Craft can connect to MySQL and Postgres databases.

Database connection settings may be set from a config/db.php file, but because they’re often entirely environment-specific, Craft supports assigning directly from environment variables. In a new Craft 5 project (opens new window), your .env file will need to define these options:

# Required variables:
CRAFT_APP_ID=
CRAFT_ENVIRONMENT=dev
CRAFT_SECURITY_KEY=

# Database-specific variables:
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=

We recommend this approach because it:

Keeps sensitive information out of your project’s codebase (.env files should never be shared or committed to version control);
Makes collaborating with other developers easier, as each developer can define their own settings from scratch, without overwriting someone else’s settings.

Environment overrides are covered in greater detail on the configuration overview page.

If you need to use your own environment variables in a config file (or connection details are provided via platform-specific keys), create config/db.php and return an explicit array of settings:

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'),
    'attributes' => [
        PDO::MYSQL_ATTR_SSL_CA => '/path/to/my.crt.pem'
        // ...
    ],
];

Finer-grain control of Craft’s database connection is possible by configuring the underlying db application component. This may be necessary if you have specific security requirements, or your app needs to connect to multiple databases.

Note that if you need to supply custom PDO attributes to your primary database connection, you should do so via the attributes key in your config/db.php file, not from config/app.php while overriding the db component.

# Supported Settings

# `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:

->attributes([
    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 character set 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

If you set this to something besides utf8 or utf8mb4 for MySQL, you must also set the collation() (opens new window) setting to a compatible collation name.

Static Config
Environment Override

->charset('utf8mb3')
->collation('utf8mb3_general_ci')

# `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. (MySQL only.)

If null, the following collation will be used by default:

MySQL 8.0+: utf8mb4_0900_ai_ci
Older MySQL versions and MariaDB: utf8mb4_unicode_ci

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

php craft db/convert-charset

Static Config
Environment Override

->charset('utf8mb3')
->collation('utf8mb3_general_ci')

# `database`

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

The name of the database to select.

Static Config
Environment Override

->database('mydatabase')

# `driver`

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

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

Static Config
Environment Override

->driver('mysql')

# `dsn`

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

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.

MySQL parameters: https://php.net/manual/en/ref.pdo-mysql.connection.php (opens new window)
PostgreSQL parameters: https://php.net/manual/en/ref.pdo-pgsql.connection.php (opens new window)

Static Config
Environment Override

->dsn('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.

Static Config
Environment Override

->password('super-secret')

# `port`

Allowed types: integer (opens new window), null (opens new window)
Default value: null
Defined by: DbConfig::$port (opens new window)

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

Static Config
Environment Override

->port(3306)

# `schema`

Allowed types: string (opens new window), null (opens new window)
Default value: 'public'
Defined by: DbConfig::$schema (opens new window)

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.

Static Config
Environment Override

->schema('myschema,public')

# `server`

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

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

Static Config
Environment Override

->server('localhost')

# `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.

Static Config
Environment Override

->setSchemaOnConnect(true)

# `tablePrefix`

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

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.

Static Config
Environment Override

->tablePrefix('craft_')

# `unixSocket`

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

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.

Static Config
Environment Override

->unixSocket('/Applications/MAMP/tmp/mysql/mysql.sock')

# `url`

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

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.

Static Config
Environment Override

->url('jdbc:mysql://database.foo:3306/mydb')

# `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.)

For more on Craft batch queries, see https://craftcms.com/knowledge-base/query-batching-batch-each (opens new window).

Static Config
Environment Override

->useUnbufferedConnections(true)

# `user`

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

The database username to connect with.

Static Config
Environment Override

->user('db')

Was this page helpful?

Parts of this page are generated or assembled by automations. While we greatly appreciate contributions to the documentation, reporting automated content issues will allow us to fix them at the source!

Database Connection Settings

# Supported Settings

# attributes

# charset

# collation

# database

# driver

# dsn

# password

# port

# schema

# server

# setSchemaOnConnect

# tablePrefix

# unixSocket

# url

# useUnbufferedConnections

# user

Was this page helpful?

# `attributes`

# `charset`

# `collation`

# `database`

# `driver`

# `dsn`

# `password`

# `port`

# `schema`

# `server`

# `setSchemaOnConnect`

# `tablePrefix`

# `unixSocket`

# `url`

# `useUnbufferedConnections`

# `user`