Schema changes and migrations #

This page is for engineers who add or change a table, a column or an index, in the kernel, in an extension or in one site, and need that change to reach every installation the same way. It is the overview: it says which of the three tools to use, how each is applied, and links to the guide, the recipe and the reference of each.

Contents #

1. Three ways a schema reaches an installation
2. Which to use
3. The files involved
4. How an installation applies a change
5. Migrations for a site's own changes
6. Testing, review and delivery
7. The tools
8. Checklist for a schema change
9. Known gaps
10. Related pages

Three ways a schema reaches an installation #

A kernel or extension change usually needs both of the first two: the .dba change so new installations have it, and an upgrade step so existing ones get it. A site's change needs only a migration. The upgrade steps are also listed among the migrations, as migrations of the source kernel, and a migration run applies the pending ones of the stages database and scripts first; a site migration names a step it needs in requires: (The steps among the migrations).

Which to use #

The longer comparison: Upgrade step or migration?.

The files involved #

Oracle's update files are in ezoracle; MongoDB has none, because it has no SQL schema.

How an installation applies a change #

Since 6.0.15, two commands do the work, both under the same installation lock and with the same captions:

./console exp:upgrade --apply              # the kernel's and the extensions' upgrade steps, all stages, then deploy
./console exp:migration:migrate --apply    # pending database and scripts steps, then the site's migrations

exp:upgrade takes a caption, applies every pending upgrade step on every engine, records each and deploys (Upgrading with the console). exp:migration:migrate takes a caption, runs the pending migrations in order, each in a transaction where the engine has one, restores the caption after a failure, and commits, tags and pushes the record of the run (Migrations).

By hand, as before 6.0.15 (Compatibility, releases and deployment):

1. Back up files and database.
2. Put the new code in place, regenerate the class maps and clear all caches; reload PHP-FPM and restart Velocity.
3. Apply the database update once, whole, in order: the file in update/database/<driver>/6.0/, then the scripts in update/common/scripts/6.0/ that the upgrade guide names.
4. Run the upgrade check: Setup > System upgrade, Check database consistency compares the tables with the schema files (The upgrade check).

The update file must say how to proceed on a database that already has part of it; for the 6.0.15 file, a statement for an existing column fails with its name, and only that statement is skipped. A team moving away from such files to migrations: Coming from SQL update files.

Migrations for a site's own changes #

A migration is a PHP class with plan() (what it would change, for the dry run), up() (the change), and where it can down() (the way back) and detect() (the state, asked of the installation). Tables are created on every engine from a definition in the .dba format with expKernelTableSchema::install(); columns and indexes through the engine's schema handler, as the kernel's own expUpgradeSchemaStep does. Start with the tutorial, then PHP migrations.

The design rules: one change per migration; find things by name or remote id, never by a numeric id; create only what is missing, so a migration is safe on any installation that has not recorded it; never edit a migration that ran anywhere (the checksum guard refuses it); keep secrets out (the run log is committed). More: Migrations for software engineers.

Testing, review and delivery #

The tools #

./console exp:upgrade:status               # every upgrade step and its state
./console exp:migration:status             # every migration (and step) and its state
./console exp:migration:check              # the lint of migration files, for CI
./console exp:migration:dry-run --simulate --diff   # run them in a transaction that is rolled back (PostgreSQL, SQLite)
php bin/php/ezsqldumpschema.php --help     # dump a database schema to a .dba file
php bin/php/ezsqlinsertschema.php --help   # load a .dba schema into an engine (--type)
php bin/php/ezsqldiff.php --help           # compare two schemas

Every migration command: Commands. Reviewing a .dba change by its diff is misleading: rows live in a numerically keyed array, so removing one renumbers every row after it. Compare the schema the files produce instead, for example with ezsqldiff.php or a test that loads the file into a throwaway SQLite database (Schema and dba files).

Checklist for a schema change #

• To do: The change is in share/db_schema.dba (and db_data.dba for seed rows) of the kernel or the extension, so a new installation has it; a site-only change needs no .dba.
• To do: Existing installations get it: an upgrade step that applies it on every engine and detects it (detect()), so exp:upgrade records it and a second run changes nothing; or, for one site, a migration.
• To do: A migration uses $context->db() and the schema handlers, works on every engine the site may run on (or names them in engines:), and creates only what is missing.
• To do: An update file exists for every driver with SQL when installations are upgraded by hand: MySQL/MariaDB, PostgreSQL, SQLite, and Oracle in ezoracle.
• To do: MongoDB is covered: the adapter or the installer creates what the code needs.
• To do: The update is safe on a database that already has part of it.
• To do: A persistent class (eZPersistentObject) describes a new table.
• To do: New tables use the exp prefix; columns added to ez tables keep the table's style (Layering and naming rules).
• To do: ./console exp:migration:check passes, and the migration was simulated or run on a copy.
• To do: The changelog lists it under Added or Updated, and the upgrade notes in doc/eng-us/bc/6.0/ say what to run (Changelogs and upgrade notes).
• To do: The CI database jobs (MySQL 8.0 and PostgreSQL 15) pass.

Known gaps #

• An installation upgraded by hand with the SQL update files has no record of which statements ran; exp:upgrade records every step it applies, and exp:upgrade:verify finds drift.
• Migrations are written in PHP, YAML, SQL or INI; a schema change that must run on every engine is best a schema step (Migrations: SQL and settings).
• The simulation runs on PostgreSQL and SQLite only; on MySQL/MariaDB, Oracle and MongoDB the plan is listed.
• MariaDB and Oracle have no CI job in the kernel repository.

Related pages #

• Migrations: the hub, All recipes, Frequently asked questions
• The guide, the tutorial, PHP migrations, Rollback and captions, The migrations repository
• In the administration, Commands, Settings, Policies, views and events
• The file format, The feature
• When a migration fails, Troubleshooting messages
• Migrations versus migrating a site
• Persistence and the database layer
• Database schema and dba files
• Upgrading, Upgrading with the console, Upgrade steps for extensions
• Software engineers: start here
• Free documentation: this page is free to copy, translate and improve under the GNU FDL 1.3.