turnley.dev

PHP and MySQL code for faucet operators

Tools & Calculators

OPS-002

A migration runner that is safe to run twice

Numbered SQL files, a table of what has run, and a splitter that does not break on semicolons inside strings.

Schema changes applied by hand drift. One install has a column, another does not, and the difference surfaces months later as a bug that only reproduces on one server. A migration runner makes the schema a function of the files in the repository rather than of who remembered to run what.

Two details make the difference between a runner that works and one that half works. The record of applied files has to live in the database, not in a file, so it travels with the schema it describes. And splitting a .sql file on semicolons has to respect quoted strings, or any seed data containing a semicolon gets cut in half and produces a syntax error that points at the wrong line.

Numbering the files is what fixes the order. Do not rename them afterwards.

PHP 
function run_migrations(PDO $pdo, string $dir): int
{
    $pdo->exec(
        'CREATE TABLE IF NOT EXISTS migrations (
            file VARCHAR(120) NOT NULL PRIMARY KEY,
            ran_at DATETIME NOT NULL
         ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4'
    );

    $done = $pdo->query('SELECT file FROM migrations')->fetchAll(PDO::FETCH_COLUMN);
    $done = array_flip($done);

    $files = glob(rtrim($dir, '/') . '/*.sql') ?: [];
    sort($files);                                   // 001_, 002_, 003_ …
    $ran = 0;

    foreach ($files as $path) {
        $name = basename($path);
        if (isset($done[$name])) {
            continue;
        }
        foreach (split_sql((string) file_get_contents($path)) as $stmt) {
            if (trim($stmt) === '') {
                continue;
            }
            $pdo->exec($stmt);
        }
        $pdo->prepare('INSERT INTO migrations (file, ran_at) VALUES (?, NOW())')->execute([$name]);
        $ran++;
    }
    return $ran;
}

/** Split on semicolons that are not inside a quoted string. */
function split_sql(string $sql): array
{
    $out = []; $buf = ''; $inS = false; $inD = false;
    for ($i = 0, $n = strlen($sql); $i < $n; $i++) {
        $ch = $sql[$i];
        $prev = $i > 0 ? $sql[$i - 1] : '';
        if ($ch === "'" && !$inD && $prev !== '\\') { $inS = !$inS; }
        elseif ($ch === '"' && !$inS && $prev !== '\\') { $inD = !$inD; }

        if ($ch === ';' && !$inS && !$inD) { $out[] = $buf; $buf = ''; continue; }
        $buf .= $ch;
    }
    if (trim($buf) !== '') { $out[] = $buf; }
    return $out;
}

Using it

Write migrations so re-running them is harmless: CREATE TABLE IF NOT EXISTS, INSERT IGNORE for seed rows, and a check before adding a column. Belt and braces, because someone will eventually clear the migrations table.

Run it before uploading the rest of the release. New code against an old schema fails loudly; old code against a new schema usually does not.

What bites people

MySQL commits implicitly on DDL, so a migration that fails halfway leaves the earlier statements applied. Keep each file small enough that partial application is easy to reason about.

Never renumber or edit a migration that has shipped. Add a new one. The applied-files table keys on the name.

Also in Admin and Operations