Databases on Craft Cloud

Craft Cloud supports MySQL and Postgres databases. You will pick a database engine when creating a new Cloud project: if you are starting a new project, we recommend MySQL 8.0; existing projects should use the same engine and version used by their current infrastructure.

Both engines support automated and manual backups via Craft Console.

Database Engines #

Craft Cloud currently supports MySQL 8.0 and Postgres 15 in all regions.

Connecting to your Database #

Each environment gets its own database. Connection details are automatically provided to your application via the Cloud extension, so no configuration is necessary!

If you need to connect to your database from outside of Cloud (like with a database GUI), visit the Access screen of your environment to get credentials.

Backups #

In addition to automated nightly backups of your project’s production environment, you can trigger a manual backup of any environment from Craft Console.

→ Read more about database backups.

Importing Data #

From an existing Craft installation, run the db/backup command to generate a Cloud-compatible database dump. To restore data from another Craft Cloud environment, instead capture and download a backup from the Backups screen of the source environment.

The specific commands for importing a backup to Cloud depend on your driver, but they will always be run from your local machine (or from wherever your current Craft installation lives). Substitute the parameters in {brackets} with corresponding values from the Access screen of the target Cloud environment.

MySQL #

MySQL backups from Craft or Cloud can be imported with this command:

mysql \
    --host={cloud-db-hostname} \
    --port=3306 \
    --user={username} \
    --password={password} \
    --database={database} \
    < path/to/backup.sql

Postgres #

Postgres backups come in a few different formats. Craft uses the plaintext format by default, but backups from Cloud are captured in the custom format for interoperability.

To import a plaintext backup, run this command:

psql \
    --host="{hostname}" \
    --username="{username}" \
    --dbname "{database}" \
    --password \
    < path/to/backup.sql

custom format backups must be restored with the pg_restore command, and require additional flags:

pg_restore \
    --host="{hostname}" \
    --username="{username}" \
    --dbname "{database}" \
    --password \
    --no-owner \
    --clean \
    --single-transaction \
    --if-exists \
    --no-acl \
    < path/to/backup.sql

As of Craft 4.10, you can choose the format Craft uses to back up a Postgres database with the backupCommandFormat setting.

Make sure the version of pg_restore matches the version of pg_dump that was used to create the backup. Some GUI tools (like Table Plus) make it possible to choose the version when using binary backups.

When dumping and restoring, the --password flag forces a password prompt—you do not need to provide a value as part of the original command. If you need to perform either operation in an automated or non-interactive environment, you can set a temporary PGPASSWORD variable: PGPASSWORD=abc123 && pg_restore ...

Table Prefixes #

The tablePrefix setting (and the corresponding CRAFT_DB_TABLE_PREFIX environment variable) are not supported on Cloud.

Run php craft db/drop-table-prefix in a local environment before importing your data into Cloud to rename the tables. After doing so, you should unset tablePrefix in db.php and remove CRAFT_DB_TABLE_PREFIX from your .env.

Tips #

Here are some common sources of issues when backing up or restoring databases.

Craft’s backupCommand #

Using a custom backupCommand config setting can lead to unreliable backups. Be sure and check general.php and your .env file for a CRAFT_BACKUP_COMMAND environment override! Try the default command and reach out to support if you are still getting malformed backups.

Backing up without Craft #

Craft (and Cloud) automatically determine the correct command and options for the configured driver when backing up your database—but if you only have access to a database (and not a functional Craft installation), you can still approximate Craft’s behavior.

For MySQL, run this command:

mysqldump
    # Connection info:
    --host={host} \
    --port={port} \
    --user={username} \
    --password={password} \
    # Additiocal flags:
    --no-autocommit \
    --add-drop-table \
    --comments \
    --create-options \
    --dump-date \
    --routines \
    --set-charset \
    --triggers \
    --no-tablespaces \
    --quote-names \
    --set-gtid-purged=off \
    --quick \
    --single-transaction \
    --ignore-table=mydbname.cache \
    --ignore-table=mydbname.phpsessions \
    # Database name (passed as an argument, not an option):
    {database} \
    > path/to/backup.sql

Postgres users should use pg_dump’s custom format for consistency:

pg_dump
    # Connection info:
    --host="{hostname}" \
    --username="{username}" \
    --dbname="{database}" \
    --password \
    # Additional flags:
    --format=custom \
    --schema=public \
    --exclude-table=public.cache \
    --exclude-table=public.phpsessions \
    > path/to/backup.sql

Note that in both cases, we are ignoring the cache and phpsessions tables. Cloud will recreate these if necessary during your next deployment, and they are not typically necessary in local development.