Database Schema Migrations in PHP

File-Naming and Ordering

migrations/
  0001_create_users.sql
  0002_create_teams.sql
  0003_create_matters.sql
  0004_add_soft_delete_to_matters.sql
  0005_create_workspace_items.sql
  0006_add_wa_message_id_to_workspace_items.sql

Naming convention: {NNNN}_{description}.sql
- Zero-padded 4-digit sequence number ensures alphabetical sort = execution order
- Description uses underscores, all lowercase, verbs: create_, add_, drop_, rename_, index_

The Migrations Tracking Table

CREATE TABLE migrations (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  filename    VARCHAR(255) NOT NULL UNIQUE,
  applied_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  checksum    CHAR(64) NOT NULL  -- SHA-256 of the migration file content
);

class MigrationRunner
{
    public function run(string $migrationsDir, PDO $pdo): void
    {
        $files = glob($migrationsDir . '/*.sql');
        sort($files);  // Alphabetical = sequential by the NNNN prefix

        foreach ($files as $file) {
            $filename = basename($file);
            $checksum = hash('sha256', file_get_contents($file));

            // Check if already applied
            $stmt = $pdo->prepare('SELECT checksum FROM migrations WHERE filename = ?');
            $stmt->execute([$filename]);
            $applied = $stmt->fetch();

            if ($applied) {
                if ($applied['checksum'] !== $checksum) {
                    throw new \RuntimeException("Migration {$filename} content has changed after application!");
                }
                continue;  // Already applied and unchanged
            }

            // Apply the migration
            $pdo->exec(file_get_contents($file));

            // Record it
            $pdo->prepare('INSERT INTO migrations (filename, checksum) VALUES (?,?)')
                ->execute([$filename, $checksum]);

            echo "Applied: {$filename}\n";
        }
    }
}

No-Lock ALTER Strategies

Adding a column to a large table with a standard ALTER TABLE takes an exclusive lock for the duration. For tables with millions of rows, this locks out the application for minutes.

-- Use ALGORITHM=INSTANT for column additions (MySQL 8.0+, InnoDB)
-- INSTANT doesn't rebuild the table — it completes in milliseconds
ALTER TABLE work_logs
  ADD COLUMN billable_note VARCHAR(500) DEFAULT NULL,
  ALGORITHM=INSTANT;

-- For operations that can't use INSTANT, use pt-online-schema-change:
pt-online-schema-change --alter "ADD INDEX idx_team_status (team_id, status)" \
  D=gps_edu,t=matters --execute

What to Watch For

• Never modify an applied migration — The checksum check catches this. A migration that has been applied is immutable. Create a new migration to fix a mistake.
• Test migrations on a copy first — Run every migration on a staging database restore before running on production. A syntax error in a migration will leave the schema in an inconsistent state.
• Rollback strategy — Up-only migrations (no down()) are simpler and safer for most cases. If you need to roll back, write a new migration that reverses the change rather than running a down() in production.