Database migration tips for CakePHP

CakePHP uses Phinx by default.
The following tips will help with larger and legacy projects to maybe clean things up.

Migration file count

Problems

If your migrations piled up over the years, and you have like hundreds of migrations, maybe even renaming tables and fields multiple times,
it can become hard to understand what field and what attributes on it have been introduced.

I also encountered some legacy migrations that used raw SQL to insert table structures.
This is also something that usually should be avoided in favor of using the consistent API.

Snapshots

The solution for the problems mentioned above is usually to make a fresh snapshot.
It will just dump the whole table and field structure into a fresh file.

Let’s imagine the project is already live for years.
Here you don’t want to create any downtime, or at least switch to the snapshot as smooth as possible.
I will outline a few steps how I did that once.

Process

Make sure all your servers are up to date with latest migrations before you start.
Also the team should be aware and not introduce new migrations while doing this snapshot reset.

Let’s get started then:

bin/cake bake migration_snapshot InitDb

I first bake my new snapshot.

Then I go inside and remove all generated tables that come from a plugin that I use to keep as plugin based tables.
In my case it is Tools and Queue for example:

"bin/cake migrations migrate -p Tools --no-lock",
"bin/cake migrations migrate -p Queue --no-lock",
"bin/cake migrations migrate --no-lock"

If you don’t have any or always copy the schema over to your app, then this step can be skipped.

Then I start to compare the freshly generated migration with the ones already present.
Once I think I have a good understanding I remove all other migration files from config/Migrations/, leaving only the new snapshot.

I then add the following logic to the top of it in up():

// Add the version string here of your freshly generated `InitDb` migration
$version = '20231203033518';
$result = $this->query('SELECT * FROM phinxlog WHERE version < ' . $version . ' LIMIT 1')
    ->fetch();
if ($result) {
    $this->execute('DELETE FROM phinxlog WHERE version < ' . $version);

    return;
}

// Actual migrations start here

This will do the following:
If this is an empty DB, it will just normally migrate.
But if it an existing and filled one (e.g. local, staging or production), it will skip them all and also remove all "missing" files we removed earlier before
marking this migration as fully migrated.

So either way you end up with the same DB structure and auto-cleaned on top.

The same process would also work for any of your plugins. Just maybe don’t do that on public ones, as other consumers might run into this without being aware.

You can also delete directly, and instead focus on the support of all DB types, including Mssql.
This is an actual example from a new major release (v8) of a plugin:

public function up(): void {
    // We expect all v7 migrations to be run before this migration (including 20231112807150_MigrationAddIndex)
    $version = '20240307154751';
    if (ConnectionManager::getConfig('default')['driver'] === 'Cake\Database\Driver\Sqlserver') {
        $this->execute('DELETE FROM queue_phinxlog WHERE [version] < \'' . $version . '\'');
    } else {
        $this->execute('DELETE FROM queue_phinxlog WHERE `version` < \'' . $version . '\'');
    }

    if ($this->hasTable('queued_jobs')) {
        return;
    }

    // Actual migrations are all here, generated via bake migration_snapshot

Finalization

Careful: Before actually moving forward, best to create a file based export of your actual DB and then run the migrations here on an empty DB and do the same.
Then compare (diff based) if those are actually the same or if some important fields or constraints might be missing, that somehow didn’t get regenerated.

Also run a full test in your new DB to see if the tests still pass. Finally also click through the GUI/frontend and confirm that it is working as it should.

Deployment

Once you are quite confident over it, you can deploy this and the deployment part of migrations should auto-change as mentioned above flawlessly.

Bad or conflicting migration names

I had some plugin migrations called

20150425180802_Init.php
20150511062806_AddIndex.php
20150621110142_Rename.php
...

and application migrations

20141129110205_Init.php
...

The problem here is that each of those names like "Init" will be a class name and loaded at the same time.
So having too generic class names across plugins and your application, it will fail the migration process (incl the test harness).

The good news:
Changing those names can be done in a BC way. Even if already run on production.
The reason is that it uses only the version number (timestamp) to identify, so renaming just the name after the _ is OK at any time.

I just made the plugin ones more unique by prefixing "Migration{PluginName}" in front of them:

20150425180802_MigrationQueueInit.php
20150511062806_MigrationQueueAddIndex.php
20150621110142_MigrationQueueRename.php
...

With this I had no more collisions between app and plugins and my migrations especially also for test harness now run fine:

(new \Migrations\TestSuite\Migrator())->runMany([
    ['connection' => 'test'],
    ['plugin' => 'Tools', 'connection' => 'test'],
    ['plugin' => 'Queue', 'connection' => 'test'],
    ['plugin' => 'QueueScheduler', 'connection' => 'test'],
]);

Hope these tips can help you in your everyday migration work.