Migrations in Production
Large ALTERs lock tables, schema changes need two deploys, and running migrate automatically can take the site down.
php bin/console doctrine:migrations:up-to-date # gate a deploy on pending migrationsOn your laptop a migration is instant because the table has forty rows. On a table with forty million, the same statement can hold a lock for minutes while every request that touches it waits.
Know what the statement locks
The lock depends on the engine and the exact change. On MySQL you can append ALGORITHM=INPLACE, LOCK=NONE to an ALTER TABLE; the server then refuses the statement outright if it cannot run it without blocking writes, which is a far better outcome than discovering the block in production. On PostgreSQL an ALTER TABLE takes an exclusive lock on the table and queues behind any long-running transaction, and everything else then queues behind it.
Two steps, not one
Make each change backward compatible, because during a rolling deploy the old code is still serving requests. Add a column nullable. Deploy code that writes both the old and the new column. Backfill. Switch reads to the new column. Only in a later deploy drop the old one. A rename done in one migration breaks every instance of the old code the instant it runs.
Why automatic migration on deploy bites
Run several instances and several of them run migrate at once. A slow ALTER stalls the deploy with no way to stop it. And because MySQL commits on DDL, a failure halfway through leaves the schema half-changed and the version unrecorded, so the retry fails on the part that already applied.
Example
<?php
// migrations/Version20260710083000.php
declare(strict_types=1);
namespace DoctrineMigrations;
use Doctrine\DBAL\Schema\Schema;
use Doctrine\Migrations\AbstractMigration;
// Step 1 of 3. Step 2 backfills the column, step 3 makes it NOT NULL
// and drops the old one, each in its own migration and its own deploy.
final class Version20260710083000 extends AbstractMigration
{
public function getDescription(): string
{
return 'Add article.status, nullable, readable by the currently running code';
}
public function up(Schema $schema): void
{
// Nullable and with no default: MySQL adds this as metadata only.
// LOCK=NONE makes the server reject the statement rather than
// block concurrent writes; ALGORITHM=INPLACE avoids a table copy.
$this->addSql(
'ALTER TABLE article ADD status VARCHAR(16) DEFAULT NULL,'
. ' ALGORITHM=INPLACE, LOCK=NONE'
);
}
public function down(Schema $schema): void
{
$this->addSql('ALTER TABLE article DROP status, ALGORITHM=INPLACE, LOCK=NONE');
}
}
When to use it
- Add an index to a table large enough that locking it would cause an outage
- Rename a column across a rolling deploy without 500s from the old instances
- Decide whether the deploy pipeline should run migrations or refuse to start
- Work out why a migration that took 20ms locally hung for four minutes in production
More examples
The contract step, one deploy later
Note that the guard query runs immediately while the statements in addSql only run afterwards; that ordering is what makes abortIf useful as a precondition. A MODIFY that tightens a column to NOT NULL fails on the first NULL row, so checking first turns a failed production deploy into a readable error message.
<?php
// migrations/Version20260724140000.php
declare(strict_types=1);
namespace DoctrineMigrations;
use Doctrine\DBAL\Schema\Schema;
use Doctrine\Migrations\AbstractMigration;
final class Version20260724140000 extends AbstractMigration
{
public function getDescription(): string
{
return 'Make article.status NOT NULL and drop published_at';
}
public function up(Schema $schema): void
{
// Safe only because no running code still writes NULL into status,
// and no running code still reads published_at. That is what the
// two earlier deploys bought.
$this->abortIf(
$this->connection->fetchOne('SELECT COUNT(*) FROM article WHERE status IS NULL') > 0,
'Rows with a NULL status remain; run the backfill migration first.'
);
$this->addSql('ALTER TABLE article MODIFY status VARCHAR(16) NOT NULL');
$this->addSql('ALTER TABLE article DROP published_at');
}
public function down(Schema $schema): void
{
$this->addSql('ALTER TABLE article MODIFY status VARCHAR(16) DEFAULT NULL');
$this->addSql('ALTER TABLE article ADD published_at DATETIME DEFAULT NULL');
}
}A deploy that stops instead of migrating blindly
The shape that matters here is one migration runner, not one per application instance, and SQL captured before execution so that a failure leaves you something to read. Use up-to-date as the gate when you want a deploy to refuse to start rather than silently change the schema underneath the running version.
#!/bin/sh
# bin/deploy-db.sh - run once, from one place, never from every instance.
set -eu
# 0 means nothing pending; exit non-zero means there are migrations to run.
if php bin/console doctrine:migrations:up-to-date --no-interaction; then
echo "Schema already current; nothing to do."
exit 0
fi
# Write the SQL out and keep it with the deploy record before anything runs.
php bin/console doctrine:migrations:migrate \
--no-interaction \
--write-sql=var/pending-migration.sql
cat var/pending-migration.sql
# A human, or an explicit pipeline step, decides to continue from here.
php bin/console doctrine:migrations:migrate --no-interactionSeparating the schema change from the application rollout
These two keys decide what a half-failed run leaves behind, and the right answer differs per database. Read them before the first production migration rather than after, because the defaults are chosen for a development machine where a broken schema costs nothing.
# config/packages/doctrine_migrations.yaml
doctrine_migrations:
migrations_paths:
'DoctrineMigrations': '%kernel.project_dir%/migrations'
# Each migration runs in its own transaction where the platform supports
# transactional DDL. PostgreSQL does; MySQL commits on DDL regardless.
transactional: true
# false: a failing migration leaves earlier ones in this run applied.
# true: the whole run is wrapped in one transaction, which also means
# one long-held lock. Choose deliberately; do not assume.
all_or_nothing: false
check_database_platform: true
Discussion