The very first Doctrine migration in CaRMen's history — the one that creates the user and messenger_messages tables — was auto-generated against MySQL and never meant to run anywhere else. For a stretch of 2025 it had to run against SQLite too, because the test suite used SQLite while the actual migrations were written for MySQL. Getting one SQL file to satisfy both databases took four separate rounds of hand-editing, spread out over almost two months, each round fixing a problem the previous one hadn't noticed yet.
Round one: comments
Doctrine's auto-generated MySQL migrations annotate columns with their mapped PHP type as a SQL comment, so a JSON column reads:
roles JSON NOT NULL COMMENT '(DC2Type:json)'
That comment syntax is MySQL-specific. SQLite's CREATE TABLE parser doesn't accept it, so the very first migration failed before the test suite could create a single table. The fix, committed as "comments dont work in sqlite db," just deleted every COMMENT '(DC2Type:...)' fragment from the statement. The column type itself didn't need the comment to work — Doctrine's own ORM mapping lives in the entity classes, not in a database comment — so nothing was lost by removing it, only a hint that was never going to be read by anything on the SQLite side anyway.
Round two and three: the same evening, two different clauses
Two weeks later, a second migration (this one creating the password-reset table) hit the next MySQL-only clause: DEFAULT CHARACTER SET utf8mb4. One commit, "try to remove default character set," stripped it from every table definition in that file. Three minutes later, a second commit, "try to remove collate," found the clause right behind it — COLLATE `utf8mb4_unicode_ci` — and removed that too. Both are meaningless outside MySQL: SQLite doesn't have a per-table character set or collation concept in the same sense, so there was nothing to translate them into, only strip them out.
The two commit messages — "try to remove X," not "remove X" — read like someone genuinely unsure whether the next error would be a different clause entirely rather than a variation on the same theme. It was the same theme, twice, three minutes apart.
Round four: the one that wasn't cosmetic
The first three rounds all deleted syntax that meant nothing to SQLite. The fourth, over a month later, hit something that actually changed behavior. MySQL's auto-generated primary keys look like:
id INT AUTO_INCREMENT NOT NULL, ... PRIMARY KEY(id)
SQLite has no AUTO_INCREMENT keyword at all. Its equivalent is a single, specific column definition:
id INTEGER PRIMARY KEY AUTOINCREMENT
not a type plus a separate PRIMARY KEY(id) clause tacked on afterward — the autoincrement behavior in SQLite is a property of that exact column declaration, not a constraint you can bolt on top of an ordinary integer column the way MySQL lets you. This fix touched eleven tables in one SQLite migration file: address, customer, menu, menu_item, note, permission, project, reset_password_request, role, user and messenger_messages. Every one needed the same rewrite, because every one had been generated the same way.
Cleaning up after the workaround
Two more commits, neither one a fifth round of the same fix, cleaned up around the edges of this whole saga. One, "removed duplicate migrations," deleted two MySQL migration files that had ended up duplicated across migrations/mysql/ and migrations/sqlite/ after the directories were split — 118 lines of dead, never-run SQL sitting in the repo for no reason. And the CI workflow had been running bin/console make:migration --env=test as part of every test run, which is itself a strange thing for a CI pipeline to do: generating a new migration file at test time, rather than applying the ones already committed. That line was dropped from .github/workflows/symfony.yml the same day as the AUTO_INCREMENT fix above, about 45 minutes before it, once it was clear the real problem was the dual-database setup itself, not a missing migration.
The dual-database problem didn't get fixed. It got removed.
None of these four rounds made SQLite and MySQL migrations actually compatible going forward — each one just patched the specific incompatibility that had surfaced that week, for the specific migration that existed that week. A fifth table added later would have needed the same four lessons re-learned by hand, because the lesson never made it into anything that would catch the next instance automatically. That only stopped being a problem after CaRMen's larger rewrite in March, when both the application and its test suite moved onto the same database engine — PostgreSQL 16, confirmed in the current backend/.env.test and the GitHub Actions workflow, which now spins up a real postgres:16 service container for CI instead of pointing tests at SQLite at all.
The honest lesson isn't "write portable SQL." Auto-generated migrations are never going to be portable by default, because the whole point of auto-generating them is pulling in whatever dialect-specific syntax actually describes the behavior you asked Doctrine for. The lesson is narrower: if your tests and your production database disagree about which SQL dialect they speak, every migration is a bet that nobody will notice the gap until the next one. Four rounds of hand-editing is what losing that bet looks like in slow motion.
Leave a comment
Comments are reviewed before they appear.