Migration Management

The transaction kept a write whole. But the tables those writes stand on have to come from somewhere, and as the application grows, those tables change. Changing the schema by hand leads to a state in which nobody knows which change was made in which environment. The question is this: which database determines the schema?

The Migrations and Seeding lesson in the Web Application Framework course measured that a migration runs. This lesson does not explain the migration itself again; what it asks is who can change the number of migrations applied in this run. The schema migrations lesson in the Databases course also introduced the concept already; here only a PHP mechanism is built. The decision order does not change: client, third party, environment; if none of them can change the result, the server. The dominant party of this lesson is the environment. The core file is given verbatim.

<?php
// party.php — who can change this result?
declare(strict_types=1);

// The parties outside the code, in decision order: every request, every external call, every deployment.
const EXTERNAL_PARTIES = ['client', 'third party', 'environment'];

/** Computes the result once without deviation, then once with each external party's deviation. */
function probe(callable $result, array $deviations): array {
    $baseline = $result([]);
    $changed = [];
    foreach (EXTERNAL_PARTIES as $party) {
        if (array_key_exists($party, $deviations)) {
            $changed[$party] = $result([$party => $deviations[$party]]) !== $baseline;
        }
    }
    return $changed;
}

/** The first external party that can change the result; the server if none can. */
function party(array $changed): string {
    foreach (EXTERNAL_PARTIES as $p) {
        if ($changed[$p] ?? false) return $p;
    }
    return 'server';
}

function partyReport(string $name, callable $result, array $deviations): string {
    $changed = probe($result, $deviations);
    $lines = [];
    foreach ($changed as $p => $c) $lines[] = $p . ($c ? ' changed' : ' could not change');
    $owner = party($changed);
    echo "$name → $owner\n    ", implode(' · ', $lines), "\n";
    return $owner;
}

DA9. Migration files are PHP arrays and each migration carries one up query and one down query; the file system is not read. DA10. The migration log is the migrations table and holds the name of every migration that has been applied. DA11. The environment’s deviation is the state of the database: an empty database, or a database to which the migrations have already been applied. DA12. The client sends only the name of the member to be added after the migrations; its deviation is sending bob instead of alice.

Migrations, the Log and the Order

A migration is a file with an ordered name that describes a single change to the schema. The number at the start of the name determines the order: 001 is applied first, 003 later. The migration log is the table that holds which migrations have been applied to this database. The runner executes the migrations that are not in the log in order, applies each one inside a transaction, and adds its name to the log when the transaction ends. Because the log entry and the change are written in the same transaction, neither stays without the other.

Rollback uses the same structure in reverse: the down query of the last migration in the log is executed and its name is deleted from the log. The file below defines three migrations, the log and two runners. It has no output and is only written to disk.

<?php
// migrations.php — migration files, the migration log and ordered application
declare(strict_types=1);

const MIGRATIONS = [
    '001-members' => ['CREATE TABLE members (id INTEGER PRIMARY KEY, name TEXT)', 'DROP TABLE members'],
    '002-score' => ['ALTER TABLE members ADD COLUMN score INTEGER DEFAULT 0', 'ALTER TABLE members DROP COLUMN score'],
    '003-index' => ['CREATE INDEX members_name ON members (name)', 'DROP INDEX members_name'],
];

function setUpMigrationLog(PDO $pdo): void {
    $pdo->exec('CREATE TABLE IF NOT EXISTS migrations (name TEXT PRIMARY KEY)');
}

function appliedMigrations(PDO $pdo): array {
    return $pdo->query('SELECT name FROM migrations ORDER BY name')->fetchAll(PDO::FETCH_COLUMN);
}

/** Applies the migrations that are not in the log, in order; returns the names applied in this run. */
function applyMigrations(PDO $pdo): array {
    setUpMigrationLog($pdo);
    $done = appliedMigrations($pdo);
    $thisRun = [];
    foreach (MIGRATIONS as $name => [$up, $down]) {
        if (in_array($name, $done, true)) continue;
        $pdo->beginTransaction();
        $pdo->exec($up);
        $pdo->prepare('INSERT INTO migrations (name) VALUES (:name)')->execute(['name' => $name]);
        $pdo->commit();
        $thisRun[] = $name;
    }
    return $thisRun;
}

/** Rolls back the last migration in the log; returns the name of the one rolled back. */
function rollBackLastMigration(PDO $pdo): ?string {
    $done = appliedMigrations($pdo);
    $name = end($done);
    if ($name === false) return null;
    $pdo->beginTransaction();
    $pdo->exec(MIGRATIONS[$name][1]);
    $pdo->prepare('DELETE FROM migrations WHERE name = :name')->execute(['name' => $name]);
    $pdo->commit();
    return $name;
}

function currentVersion(PDO $pdo): ?string {
    $name = $pdo->query('SELECT MAX(name) FROM migrations')->fetchColumn();
    return $name === false || $name === null ? null : $name;
}

Now run the mechanism on an empty database. In the second run every migration is in the log, so no migration is applied.

<?php
// apply.php — application, the second run and rollback
declare(strict_types=1);
require 'migrations.php';

$pdo = new PDO('sqlite::memory:');
echo 'first run: ', implode(', ', applyMigrations($pdo)), "\n";
echo 'second run: ', count(applyMigrations($pdo)), " migrations\n";
echo 'rolled back: ', rollBackLastMigration($pdo), "\n";
echo 'log: ', implode(', ', appliedMigrations($pdo)), "\n";
echo 're-applied: ', implode(', ', applyMigrations($pdo)), "\n";

echo 'columns: ', implode(', ', $pdo->query("SELECT name FROM pragma_table_info('members')")->fetchAll(PDO::FETCH_COLUMN)), "\n";

first run: 001-members, 002-score, 003-index
second run: 0 migrations
rolled back: 003-index
log: 001-members, 002-score
re-applied: 003-index
columns: id, name, score

The second run applied zero migrations, because the log, not the migration file, decides whether a migration is applied. The migration rolled back is only the last one, and because it was deleted from the log, it is applied again in the next run. The files stay the same; what changes is the log.

A Migration That Carries Data

A schema migration changes only the table structure, but some migrations do more than add a column: they also write values into it. The down query of such a migration restores the schema to its old state, but it does not restore the written values. The file below applies the three migrations, inserts two members, adds a level column and fills it from the score, and then removes the column with the down query.

<?php
// data-migration.php — rolling back a migration that adds a column and fills it
declare(strict_types=1);
require 'migrations.php';

$pdo = new PDO('sqlite::memory:');
applyMigrations($pdo);
$pdo->exec("INSERT INTO members (name, score) VALUES ('alice', 7), ('bob', 3)");

$pdo->exec('ALTER TABLE members ADD COLUMN level TEXT');
$pdo->exec("UPDATE members SET level = CASE WHEN score > 5 THEN 'high' ELSE 'low' END");
echo 'after the migration: ', json_encode($pdo->query('SELECT name, level FROM members ORDER BY id')->fetchAll(PDO::FETCH_ASSOC), JSON_UNESCAPED_UNICODE), "\n";

$pdo->exec('ALTER TABLE members DROP COLUMN level');
echo 'after the rollback: ', json_encode($pdo->query('SELECT * FROM members ORDER BY id')->fetchAll(PDO::FETCH_ASSOC), JSON_UNESCAPED_UNICODE), "\n";

after the migration: [{"name":"alice","level":"high"},{"name":"bob","level":"low"}]
after the rollback: [{"id":1,"name":"alice","score":7},{"id":2,"name":"bob","score":3}]

The schema came back; the level values did not. A rollback restores a structure; it does not bring back written data. For this reason the down query of a migration that carries data may require taking a backup before the write, or being marked as irreversible. This decision belongs to whoever writes the migration and it stays recorded in the code; it does not depend on the environment the migration runs in.

Whose Result Is It

The measured result is the number of migrations applied in this run. The same code applies three migrations on an empty database and none on a database to which the migrations have already been applied. Which of the two states holds is not inside the code; it is in the database the application connects to, and the party that prepares the database is the one performing the deployment. The client, on the other hand, adds a member after the migrations are finished; this changes nothing in the log.

<?php
// migration-result.php — how many migrations were applied in this run?
declare(strict_types=1);
require 'party.php';
require 'migrations.php';

function appliedCount(array $d): int {
    $pdo = new PDO('sqlite::memory:');
    if (($d['environment'] ?? 'empty') === 'current') applyMigrations($pdo);
    $thisRun = applyMigrations($pdo);
    $pdo->prepare('INSERT INTO members (name) VALUES (:name)')->execute(['name' => $d['client'] ?? 'alice']);
    return count($thisRun);
}

echo 'empty database: ', appliedCount([]), "\n";
echo 'current database: ', appliedCount(['environment' => 'current']), "\n";
partyReport('applied migration count', 'appliedCount', ['client' => 'bob', 'environment' => 'current']);

empty database: 3
current database: 0
applied migration count → environment
    client could not change · environment changed

The client did not change the count: the name of the member has no part in whether the migrations are applied. The environment changed it: when the database is current, the count dropped from three to zero. The result belongs to the environment. The migration mechanism itself works correctly; but the result of a single run reflects the state of the database that runs it, not the mechanism.

The Bounding Measurement

Take another result from the same mechanism: the schema version after the migrations have run, that is, the name of the last migration in the log. Whether the database is empty or current, when the migrations have run the same last name stands in the log. This result does not depend on the starting state of the database.

<?php
// migration-bound.php — the schema version after the run
declare(strict_types=1);
require 'party.php';
require 'migrations.php';

function versionAfter(array $d): string {
    $pdo = new PDO('sqlite::memory:');
    if (($d['environment'] ?? 'empty') === 'current') applyMigrations($pdo);
    applyMigrations($pdo);
    $pdo->prepare('INSERT INTO members (name) VALUES (:name)')->execute(['name' => $d['client'] ?? 'alice']);
    return currentVersion($pdo) ?? 'none';
}

partyReport('version after the run', 'versionAfter', ['client' => 'bob', 'environment' => 'current']);

version after the run → server
    client could not change · environment could not change

Although the state of the database changed, the version did not change; it is the last name in the migration files, not the starting state of the deployment, that determines the version. Two different owners came out of the same mechanism: the applied count belongs to the environment, the version after the run to the server. A migration mechanism should be read according to which question it answers.

Summary

• A migration is a file with an ordered name; the migration log table holds which ones have been applied, and the runner executes only those not in the log, in order.
• Each migration and its log entry are written in the same transaction; a rollback executes the down query of the last migration and deletes its name from the log.
• The number of migrations applied in this run depends on the state of the database; in this lesson the party is the environment.
• The schema version after the run is the same in every environment and the result belongs to the server.
• Two results taken from the same mechanism can belong to different parties.

Next Step

The schema is now on record; but which queries does the object–relational mapping built on top of this schema send in the background? The next lesson counts the queries that go out while printing a list.