How can I add a non-null column with a default using Doctrine Migrations without causing downtime?

To add a non-null column with a default value without causing significant table locks or downtime, you must avoid a single ALTER TABLE statement that forces the database to rewrite every row. The safest approach is a multi-stage rollout that separates the schema change from the data population.

The Safe Migration Sequence

Follow these three distinct phases to ensure the application remains available during the transition:

Phase 1: Add the Column as Nullable

Create a migration that adds the column without the NOT NULL constraint. This operation is typically fast and avoids long-term locks in most modern database engines.

$this->addSql('ALTER TABLE my_table ADD new_column VARCHAR(255) DEFAULT \'default_value\'');

Note: Even with a default, keeping the column nullable prevents the database from needing to validate existing rows against a strict constraint immediately upon creation.

Phase 2: Backfill Existing Data

Deploy the code that handles the new column, then run a data migration to update existing NULL values. To avoid locking the table for the duration of the update, perform this in batches rather than a single query.

// Example of a batched update in a migration or command
while ($rowsAffected > 0) {
    $this->connection->executeStatement(
        'UPDATE my_table SET new_column = \'default_value\' WHERE new_column IS NULL LIMIT 1000'
    );
    $rowsAffected = $this->connection->affectedRows();
}

Phase 3: Enforce the NOT NULL Constraint

Once all existing rows are populated and the application code is successfully writing the new value for all new records, apply the final constraint.

$this->addSql('ALTER TABLE my_table MODIFY new_column VARCHAR(255) NOT NULL');

Technical Assumptions and Constraints

• Database Version: This guide assumes MySQL 5.6+ or PostgreSQL 11+. Older versions may rewrite the table regardless of nullability, requiring external tools like pt-online-schema-change.
• Locking Behavior: While ADD COLUMN is often fast, the UPDATE phase is the highest risk for locking. Batching is essential for tables with millions of rows.
• Doctrine DBAL: Using $this->addSql()` is recommended over $this->changeColumn()` for these specific steps to ensure the exact SQL syntax for locking and defaults is preserved.

Verification Steps

Before applying these to production, verify the following in a staging environment with a production-sized dataset:

1. Run SHOW CREATE TABLE my_table; (MySQL) or \d my_table (PostgreSQL) after Phase 1 to confirm the column is nullable.
2. Monitor database CPU and lock wait times during the Phase 2 backfill.
3. Confirm that the final ALTER TABLE in Phase 3 completes quickly because the database only needs to verify the constraint, not populate data.

Diagnostic Detail Needed: To provide a more optimized SQL strategy, please specify your database engine (e.g., MySQL 8.0, PostgreSQL 15) and the approximate row count of the table.