Migrations in production

An app on PostgreSQL keeps its migrations inside its binary. A server that runs the app needs nothing else to bring its database up to date: no command line tool, no SQL files beside it. This page is about what a run does, how to migrate a server, what keeps the app from starting, and how to move an older app to it.

A project on SQLite keeps its list of migrations, as Database shows, and nothing here concerns it. The code here is from Bookings, the example app in examples/bookings (source).

In the binary

The migrations are the .sql files of src/core/db/migrations/. One line of build.zig, spider.addMigrations(b, spider_dep, "src/core/db/migrations") given to the executable as the import migrations, builds every one of them into the app. A file added there is in the binary after the next build. main hands them to the server and applies the ones that have not run, before it serves:

/// The migrations: every file of src/core/db/migrations/, built into the
/// binary (build.zig: spider.addMigrations).
const migrations = @import("migrations").all;

pub fn main(init: std.process.Init) !void {
    const allocator = init.arena.allocator();
    const io = init.io;

    try db.init(allocator, io, .{});
    defer db.deinit();

    // Applies the migrations that have not run yet: a new database, or a new
    // container, is ready when the server starts. `spider migrate`
    // (SPIDER_MIGRATE=1) only migrates.
    const applied = try migrate(allocator);
    if (spider.migrating()) {
        std.log.info("{d} migration(s) applied", .{applied});
        return;
    }

    try serve(allocator, io);
}

/// Applies the migrations that have not run yet, in order, each in its own
/// transaction with its row in schema_migrations, under a lock: several
/// copies of the app starting at once apply each one once. Returns how many
/// ran. A migration edited after it ran stops it (error.MigrationChanged):
/// add a new one instead. main() calls it, and so can the tests, on a test
/// database.
pub fn migrate(allocator: std.mem.Allocator) !usize {
    var arena = std.heap.ArenaAllocator.init(allocator);
    defer arena.deinit();
    var migrator = spider.app(.{});
    defer migrator.deinit();
    return migrator.migrations(migrations).migrate(arena.allocator());
}

spider new --pg writes all of this. Things to know about the files:

• They run in the order of their names. A name is at least four digits, _, and a description in lowercase letters, digits and _: 1791668113_create_users.sql. Any other name does not compile.
• What runs is the part after -- migrate:up (or the whole file, without one) up to -- migrate:down. The down part never runs.

What a run does

Each migration that has not run yet runs in a transaction of its own, together with its row in schema_migrations: it is applied and recorded, or neither. The row keeps a checksum of the SQL that ran.

Each migration takes a lock in the database before it looks for its row. Several copies of the app starting at the same time, as a deploy with more than one replica does, apply each migration once: the others wait for the lock, find the row, and go on.

A migration that fails is rolled back and stops the run; the ones before it stay applied. The app does not start.

Migrating a server

SPIDER_MIGRATE=1 ./bookings

With SPIDER_MIGRATE set, the app migrates and exits instead of serving. Run it with the same environment as the app: the PG_* variables say which database. On a new database, Bookings prints:

info: pg: connected (10 connections)
info: migration 1791667709_create_rooms applied
info: migration 1791667710_create_bookings applied
info: migration 1791667711_seed_rooms applied
info: migration 1791668113_create_users applied
info: migration 1791668114_bookings_belong_to_users applied
info: 5 migration(s) applied

In a container, the same image does it:

docker run --rm --env-file .env -e SPIDER_MIGRATE=1 myapp

On your machine, spider migrate builds the app and runs it that way (zig build run with SPIDER_MIGRATE). spider.migrating() tells main it is such a run, to skip what migrating does not need.

A project made by spider new --pg also migrates every time it starts, so deploying the new version is enough. Migrating as a step of its own is for when you want it apart: after a backup, once, before the new version starts on every replica.

Never edit a migration that ran

The checksum is how Spider knows. A migration whose SQL is not what ran stops the run with error.MigrationChanged, and the app does not start: what changed belongs in a new migration.

What keeps a server from starting

A server given the migrations with .migrations(...) checks them before it listens, and refuses to start while one has not run, naming it. main above gives them only to the server that migrates, since it migrates before every start. An app that migrates as a step of its own gives them to the server that serves as well, server.migrations(migrations), and a version started on a database nobody migrated stops at once instead of failing on the first query. A test of Bookings gives a server one more file than the database has seen:

/// Every file of src/core/db/migrations/, as build.zig embeds them.
const migrations = @import("migrations").all;

test "a migration the database does not have keeps the server from starting" {
    _ = try app_test.running();
    var arena: std.heap.ArenaAllocator = .init(std.testing.allocator);
    defer arena.deinit();
    var threaded: std.Io.Threaded = .init(std.testing.allocator, .{});
    defer threaded.deinit();

    // A binary built with one more file than the database has seen.
    const newer = migrations ++ [_]spider.Migration{
        .{ .name = "9999999999_add_floor_to_rooms", .sql = "ALTER TABLE rooms ADD COLUMN floor INTEGER;" },
    };
    var server = spider.app(.{});
    defer server.deinit();
    const report = server.migrations(newer).check(arena.allocator(), threaded.io());
    try std.testing.expect(!report.ok());
    try std.testing.expectEqualStrings("app", report.problems.items[0].module);
    try std.testing.expectEqualStrings(
        "migration 9999999999_add_floor_to_rooms is not applied (run `spider migrate`)",
        report.problems.items[0].message,
    );
}

Going back to an older version

A rollback runs a binary older than its database: the database holds migrations this binary does not know. That is not a reason to refuse, or a rollback could never start. The app starts and says so:

warning: app: the database holds 1 migration(s) this binary does not know (1791668114_bookings_belong_to_users): a newer version applied them; this one runs on a schema it was not built for

test "a database ahead of the binary is a warning, not a refusal" {
    _ = try app_test.running();
    var arena: std.heap.ArenaAllocator = .init(std.testing.allocator);
    defer arena.deinit();
    var threaded: std.Io.Threaded = .init(std.testing.allocator, .{});
    defer threaded.deinit();

    // The binary of the version before the last migration: a rollback.
    const older = migrations[0 .. migrations.len - 1];
    var server = spider.app(.{});
    defer server.deinit();
    const report = server.migrations(older).check(arena.allocator(), threaded.io());
    try std.testing.expect(report.ok());
    try std.testing.expectEqual(@as(usize, 1), report.warnings.items.len);
    try std.testing.expect(std.mem.indexOf(u8, report.warnings.items[0].message, "1791668114_bookings_belong_to_users") != null);
}

The same warning is a sign of a migration renamed by hand (below). Migrations only go forward: undoing one is a new migration in the next version.

Moving an app made before

An app made before Spider 0.11 lists its migrations in src/core/db/migrations.zig and applies them with code of its own. To move it:

1. Add the addMigrations line to build.zig and make main look like the one above.
2. Delete src/core/db/migrations.zig, and src/core/db/mod.zig with the db line of src/core/mod.zig when that was all they held. From then on spider g feature only writes the .sql file.
3. Run it once on a copy of the production database before production.

The files keep working as they are:

• The rows already there count. A migration is recorded by its file name, as before, so nothing that ran runs again. The first run records the checksums of those rows: info: recorded the checksums of 4 migration(s) applied before Spider kept them.
• A BEGIN; ... COMMIT; around a whole file is left out: the transaction is Spider's. Any other transaction control is refused before anything runs.
• A statement PostgreSQL refuses inside a transaction (CREATE INDEX CONCURRENTLY) runs outside one, as long as it is alone in its file.

What can change is the order. The list ran migrations in the order it gave; the binary runs them in the order of their names. Bookings had two files made in the same second, 1791667710_create_bookings and 1791667710_create_rooms, listed with the rooms first. By name, the bookings come first, and they refer to the rooms: on a new database that migration fails. The fix was a new name, earlier, with IF NOT EXISTS for a database that already has the table:

-- migrate:up
-- A room people book. `features` is a list: PostgreSQL has arrays, and
-- "rooms with a projector and a whiteboard" is one comparison on it.
--
-- Migrations run in the order of their file names, and the bookings
-- (1791667710_create_bookings) refer to this table, so it comes first. It
-- was called 1791667710_create_rooms while a list in the app gave the
-- order: IF NOT EXISTS lets a database made then record it under its new
-- name without making the table twice.
CREATE TABLE IF NOT EXISTS rooms (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name TEXT NOT NULL UNIQUE,
    capacity INTEGER NOT NULL CHECK (capacity > 0),
    features TEXT[] NOT NULL DEFAULT '{}'
);

-- migrate:down
DROP TABLE IF EXISTS rooms;

On a database made before, the renamed migration only records itself, and the old row is one the binary does not know: the warning above, at every start, until the row goes. Delete it once, after the first run: DELETE FROM schema_migrations WHERE version = '1791667710_create_rooms'.

Before moving an app, read its list beside the file names. Where the orders differ, rename before anything else.

In the tests

The tests run main.migrate on their own database, as main does (PostgreSQL), and check that the database and the binary agree:

test "the migrations run once: a second run applies none" {
    // The running app migrated the test database before it served.
    _ = try app_test.running();
    try std.testing.expectEqual(@as(usize, 0), try main.migrate(std.testing.allocator));
}

test "the check finds every migration applied, under its file name" {
    _ = try app_test.running();
    var arena: std.heap.ArenaAllocator = .init(std.testing.allocator);
    defer arena.deinit();
    var threaded: std.Io.Threaded = .init(std.testing.allocator, .{});
    defer threaded.deinit();

    var server = spider.app(.{});
    defer server.deinit();
    const report = server.migrations(migrations).check(arena.allocator(), threaded.io());
    try std.testing.expect(report.ok());
    try std.testing.expectEqual(@as(usize, 0), report.warnings.items.len);

    const Row = struct { version: []const u8, checksum: ?[]const u8 };
    const rows = try spider.pg.query(Row, arena.allocator(), "SELECT version, checksum FROM schema_migrations ORDER BY version", .{});
    try std.testing.expectEqual(migrations.len, rows.len);
    try std.testing.expectEqualStrings("1791667709_create_rooms", rows[0].version);
    try std.testing.expect(rows[0].checksum != null);
}

Modules

Modules bring migrations too, and the same run applies them: the modules' first, in the order the modules start, then the app's. Their rows are module/name, as notify/0001_init. Modules shows it.