Databases: MySQL, MariaDB, PostgreSQL and SQLite
This page is for developers who choose and prepare the database of a DXP project. The DXP stores content through Doctrine DBAL; the skeleton's guide documents four engines.
Contents
Which engine
| Engine | Minimum | Use it for | Notes |
|---|---|---|---|
| MySQL | 8.0 | Production | utf8mb4 with utf8mb4_unicode_520_ci |
| MariaDB | 10.3 (10.6 recommended) | Production | Same character set |
| PostgreSQL | 14 (16 recommended) | Production | UTF8 encoding |
| SQLite | 3.35 | Development, tests, demos, offline installations | No server; not for concurrent public use (SQLite for development) |
The guide's export path to Oracle is "export only"; Oracle is not an engine you install the DXP on.
Creating the database
MySQL or MariaDB:
CREATE DATABASE exponential CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
GRANT ALL PRIVILEGES ON exponential.* TO 'your_db_user'@'localhost';
FLUSH PRIVILEGES;PostgreSQL:
psql -U postgres -c "CREATE DATABASE exponential ENCODING 'UTF8';"Doctrine can also create it from your settings: php bin/console doctrine:database:create. SQLite needs no step:
the install command creates the file.
Then set the database variables in .env.local (Environment variables).
Installing the schema
php bin/console exponential:install exponential-ossOn later updates, apply database changes with Doctrine migrations, not with the install command (Doctrine migrations).
Converting between engines
The skeleton's guide (section 21) converts a running project with free tools only:
| From | To | Tool in the guide |
|---|---|---|
| MySQL or MariaDB | SQLite | mysql2sqlite (MIT) piped from mysqldump |
| PostgreSQL | SQLite | pgloader |
| SQLite | MySQL or MariaDB | sqlite3-to-mysql (Python, MIT) |
| SQLite | PostgreSQL | pgloader |
| MySQL or MariaDB | PostgreSQL | pgloader |
| PostgreSQL | MySQL or MariaDB | CSV export per table, schema with pgloader, import |
| Any | Oracle | export only |
Example from the guide, MySQL to SQLite:
mysqldump --no-tablespaces --skip-extended-insert --compact \
-u "$DATABASE_USER" -p"$DATABASE_PASSWORD" -h "$DATABASE_HOST" "$DATABASE_NAME" \
| ./mysql2sqlite - | sqlite3 var/data_dev.dbBack up the database, var/ and .env.local before you start, and do it in a maintenance window without traffic.
After a conversion
The guide's checklist (section 21g):
- Update
.env.localwith the new database settings. php bin/console cache:clear(the container caches the connection).php bin/console doctrine:schema:validate.php bin/console doctrine:migration:migrate --allow-no-migration.php bin/console exponential:reindex.- Smoke-test the site and
/adminui/. - For SQLite as the target, fix the file permissions.
| Symptom | Cause | Fix |
|---|---|---|
| "Base table not found" | A table was not transferred | Validate the schema; read the converter's log |
| Garbled text | Character set mismatch at export | Export with --default-character-set=utf8mb4 or CLIENT_ENCODING=UTF8 |
| Image variations return 404 | Rows missing in the field table | Compare row counts between source and target |
Related pages
- SQLite for development
- SQLite for Exponential Platform
- AdminNeo database manager
- Free documentation: this page is free to copy, translate and improve under the GNU FDL 1.3.
Become an Exponential Partner
Partners share their information, provide funding, contribute back and develop the products with us, and that is how Exponential grows. A partner website and dashboard are planned for mid-2027: please become a partner today to speed up our growth rate and land new features faster.