Craft Database Options and Best Practices

Choosing a Database Engine #

Whether it’s your first or fiftieth Craft project, choosing a database engine is important.

For each build, you should pick an engine and stick with it. Moving from MySQL to PostgreSQL (or vice-versa) can be painful and error-prone, and even finer differences between MySQL and MariaDB can cause issues.

We don’t have a standout database recommendation because it truly comes down to your requirements, tooling, and preferences. But here’s an overview of your options.

MySQL #

MySQL is the most prevalent of the supported databases—so whether you’re looking for a host or a local database client for your OS, you’ll likely have an easier time with more options to choose from.

MariaDB #

MariaDB is a fast option that’s similar to MySQL and was originally considered a drop-in replacement. Avoid switching between MariaDB and MySQL in different environments.

PostgreSQL #

PostgreSQL is the most versatile option, with the most granular control over schema changes, advanced storage options not found in MySQL, and more. This comes at the expense of being less prevalent—so you’ll need to make sure your web host and local tooling are PostgreSQL-friendly.

Some finer details in Craft and Craft plugins may require PostgreSQL-specific consideration and testing, so while it’s a fully-supported database engine you may occasionally bump into PostgreSQL-specific issues when interacting with the database, directly.

Making and Restoring Backups #

There are lots of ways to back up a database, whether it’s from Craft’s (control panel utility or console command), a GUI app, or a tool provided by your web host. Not all backups are the same, however, so it’s important to develop good backup hygiene that avoids potential issues.

Don’t hand edit. #

Do not hand-edit a database dump. It seems like maybe you should because it’s just text. But more often than not, it’s going to cause problems.

Keep it clean. #

We see a lot of support tickets that come from accidentally blending data. A clean, consistent process of backing up and restoring data will avoid all kinds of issues—some of which can lurk for a long time without notice.

When restoring a database, it’s best to completely delete and recreate the one you’re importing to, rather than only dropping tables and running the import. This rules out possible issues with lingering, dynamically-created tables and database schema objects—like sequences in PostgreSQL that control auto-incrementing values—that could lead to further issues during the restore process.

Keep it consistent. #

Seemingly-trivial differences in backup commands, database engines, and settings can be problematic. It’s much easier to ensure consistency than it is to identify and fix issues that could arise.

Backup Commands

pg_dump and mysqldump are commonly-available CLI tools that have a lot of arguments. Craft relies directly on these, so it may be useful to know what it uses when it creates backups:

• MySQL command
• PostgreSQL command

Craft’s defaults are carefully tailored to be useful in most cases since it’s a self-hosted web app.

Storage Engines

With MySQL, don’t create a backup using one storage engine (like InnoDB) and restore it somewhere that uses another one (like MyISAM). This is good to avoid generally, but in this specific case MyISAM doesn’t support foreign keys so they’ll be dropped on restore and likely result in issues running Craft.

Charsets and Collations

Charsets and collations matter, particularly with MySQL. If you create a backup on a server with a particular charset and collation, then try to restore on a server that doesn’t support it, that server’s default charset and collation will be used, which can lead to behavioral and performance issues.

Database Versions and Configurations

Database configurations matter. If you do a backup on MySQL 5.7.36 on server A and do a restore on MySQL 5.7.36 on server B and they have the same physical resources, but the server B install is having issues, chances are MySQL on server B has different my.cnf settings that are affecting things.

Database versions matter. If you do a backup on MySQL 5.6 with an InnoDB searchindex table and try to restore on a MySQL 5.5 install where InnoDB doesn’t support full-text searching, things will break.