The migration that squashed two hundred files

The test suite spent ninety seconds before the first assertion, replaying two hundred and fourteen migrations from 2014 onwards — including three that added a column and four that removed it again. Laravel 8 shipped schema:dump in September and it is a good answer with two specific ways to get it wrong.

The symptom

$ ls database/migrations/ | wc -l
214

$ time php artisan migrate:fresh --env=testing
Migrating: 2014_10_12_000000_create_users_table
... 213 more

real	1m28.402s

$ head -1 database/migrations/2014_10_12_000000_create_users_table.php
<?php   # six years ago. it has run on every CI job since.

Ninety seconds on every pipeline run and on every developer’s first test of the day. The schema it produces is a fixed thing — the history is only interesting to the migrations table.

Why it happens

Migrations are a log and are treated as a permanent one, because deleting a migration feels like deleting history. That instinct is correct for a file already applied in production and wrong for the test database, which never had the history in the first place.

The fix

The dump, and what it actually produces

$ php artisan schema:dump --prune
Database schema dumped successfully.

$ ls database/schema/
mysql-schema.dump

$ ls database/migrations/ | wc -l
0        # --prune deleted all 214

$ head -5 database/schema/mysql-schema.dump
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (...

The dump is mysqldump --no-data output with the migrations table contents appended, so the schema is loaded by the database rather than replayed by PHP — which is the entire speed difference. The framework loads it automatically when the migrations table is empty and falls back to running migrations otherwise.

The --prune flag deleting every migration file is the correct behaviour and is startling the first time. It is also why this should be done on a branch with a clean working tree, so that git checkout is the undo.

The first failure mode: a production database mid-history

the dump is loaded ONLY when the migrations table is empty.

so:
  fresh database (CI, a new developer)   → loads the dump
  production, 214 migrations applied     → ignores it,
                                           runs what is new

which works. and it means the dump's migration list must
be a SUPERSET of what production has applied, or the next
migrate run will try to re-apply something.

the rule: dump from a database that is fully migrated,
against the same branch that is deployed.

Dumping from a developer database with an unmerged migration applied puts that migration into the dump’s applied list, and every fresh database then believes it has run — so the real migration never applies and the column is missing on a schema that claims to be current. This is the failure that is hardest to see, because it only affects new environments.

Generating the dump in CI from a fully migrated database, rather than on a laptop, removes the class entirely. It is ten lines of pipeline and it is worth it, because the alternative depends on somebody’s local state being clean.

The second: the dump is MySQL-specific

// database/schema/mysql-schema.dump  — MySQL only.
// a suite running against SQLite in memory does not use it
// and still replays all 214, so the speedup does not appear
// where it was wanted.

// phpunit.xml
<env name="DB_CONNECTION" value="mysql"/>
<env name="DB_DATABASE" value="app_test"/>

A suite on SQLite gets no benefit, and moving it to MySQL to get one is a bigger change than it sounds — it is slower per test, it needs a service in CI, and it also makes the tests exercise the database the application actually uses, which is usually worth more than the speed.

The dump also contains MySQL-specific syntax that will not load into anything else, so a project supporting two databases needs two dumps and the framework supports that by connection name. A project claiming to support two and testing against one is the common case, and this makes the claim harder to maintain.

What to keep, and what to do with the old files

the files are in git. that is the history, and it is enough.

what is genuinely lost:
  `migrate:rollback` past the squash point. it was never
  going to work on a 2015 migration anyway.

what to keep in the tree:
  any migration containing DATA — a backfill, a seed, a
  lookup row inserted. schema:dump captures STRUCTURE only,
  and a data migration deleted by --prune is gone from every
  new environment.

  of 214: 6 were data migrations. they were moved to seeders.

The data migrations are the trap in --prune. A migration that inserts the default roles is structurally invisible to mysqldump --no-data, so a fresh database gets the tables and no rows, and the application fails at boot with a message about a missing role rather than a missing migration.

Finding them means reading all two hundred and fourteen files for DB::table and insert, which is a grep and an hour. Doing it after the prune means reading them out of git history instead, which is the same work with more friction.

$ grep -rln 'DB::table|->insert(' database/migrations/
database/migrations/2015_03_11_141002_seed_roles.php
database/migrations/2016_08_02_093311_backfill_slugs.php
... 4 more

Verifying it worked

$ time php artisan migrate:fresh --env=testing
Loading stored database schemas: mysql-schema.dump

real	0m3.902s          # was 1m28

# the assertion that matters: identical schema
$ mysqldump --no-data --skip-comments app_before > /tmp/a.sql
$ mysqldump --no-data --skip-comments app_after  > /tmp/b.sql
$ diff /tmp/a.sql /tmp/b.sql
$ echo $?
0

$ php artisan migrate --pretend
Nothing to migrate.

$ vendor/bin/phpunit
Tests: 1,204 passed

The mysqldump diff between a database built the old way and one built from the dump is the check that the squash preserved everything, and it catches the subtleties that a passing test suite does not — a collation difference on one column, a missing index that no test happens to need.

migrate --pretend against production is the check for the first failure mode, and it has to be run before the branch merges rather than after. Anything other than “nothing to migrate” means the dump’s applied list disagrees with production.

What this costs

A binary-ish artefact in the repository that has to be regenerated periodically, and a step that gets forgotten. Six months after the squash there are forty new migrations and the dump is stale — which is harmless, since the fresh path loads the dump and then runs the forty, and it is also a slow creep back to the original problem.

The honest limitation is that this optimises the test suite and does nothing for production, where migrations run one at a time regardless. It is worth doing for the ninety seconds and it should not be confused with a schema management strategy — the two hundred files were never the problem in production, and squashing them does not change anything about how the next schema change is deployed.