Migrations Overview

A migration is a versioned database change stored with the application code. Migrations create tables, add columns, add indexes, change constraints, and sometimes backfill data.

The point is repeatability. Every developer, test environment, staging environment, and production environment should be able to apply the same schema changes in the same order.

A Simple Migration

ALTER TABLE users
    ADD COLUMN last_login_at TIMESTAMPTZ NULL;

CREATE INDEX idx_users_last_login_at
    ON users (last_login_at);

This migration adds a nullable column, then adds an index for queries that filter or sort by the new value.

Migration Files

Frameworks store migrations differently, but the idea is the same. A migration usually has a timestamped filename and an up method or SQL block that applies the change.

<?php

declare(strict_types=1);

final class AddLastLoginAtToUsers
{
    public function up(): array
    {
        return [
            'ALTER TABLE users ADD COLUMN last_login_at DATETIME NULL',
            'CREATE INDEX users_last_login_at_idx ON users (last_login_at)',
        ];
    }
}

print_r((new AddLastLoginAtToUsers())->up());

// Prints:
// ALTER TABLE users ADD COLUMN last_login_at DATETIME NULL

The exact code depends on the tool: Laravel migrations, Doctrine Migrations, Phinx, raw SQL files, or a custom migration runner.

Rollback And Roll Forward

Some migration tools support a down method. In production, rolling back schema changes is often more complicated than reversing one file. Dropping a column may destroy data. Renaming a column may break code that has already deployed.

A safer mindset is often "roll forward": write a new migration that fixes the problem while preserving data.

Backfills

A schema migration changes structure. A backfill changes existing data. Mixing a huge backfill into a schema migration can make deploys slow or risky.

For large tables, use batches and make the application tolerate both old and new data during the rollout.

<?php

declare(strict_types=1);

function backfillPlan(int $totalRows, int $batchSize): int
{
    return (int) ceil($totalRows / $batchSize);
}

echo backfillPlan(10500, 1000) . PHP_EOL;

// Prints:
// 11

Production Safety

Before applying a migration to production, ask:

• Is there a tested backup and restore path?
• Could this lock a large table?
• Does the application code tolerate the old and new schema during deployment?
• Does the migration need to run outside peak traffic?
• Can it be applied more than once safely by the tool?
• Is there a plan if it fails halfway?

What To Check

Before moving on, make sure you can:

• Explain why migrations are versioned with code.
• Recognise schema changes and data backfills.
• Understand why production rollback may be hard.
• Review a migration for table locks and data loss risk.
• Explain why backups and restore tests matter.