Migrations

Migrate keeps the schema of a database in step with your code. A migration has a version, a name and an action: sql statements, or a function over the connection. Migrate.up(conn, migrations) applies the ones the database has not seen, in order, and records each in the table schema_migrations. Import it with import Migrate from "pkg/db/migrate".

Migrate.sql(version: Int, name: String, statements: List[String]) -> Migrate.Migration
Migrate.code(version: Int, name: String, body: Fn[Db.Connection, Result[Void]]) -> Migrate.Migration
Migrate.up(conn: Db.Connection, migrations: List[Migrate.Migration], table: String = "schema_migrations") -> Result[List[Int]]
import Db from "pkg/db/db"
import Migrate from "pkg/db/migrate"
import Sqlite from "pkg/db/sqlite"

def migrations() -> List[Migrate.Migration]
  return [
    Migrate.sql(1, "users", ["create table users (id integer primary key, name text not null)"]),
    Migrate.sql(2, "index", ["create unique index users_name on users (name)"]),
    Migrate.code(3, "first user", &first_user),
  ]
end

def first_user(tx: Db.Connection) -> Result[Void]
  try Db.execute(tx, "insert into users (name) values (?)", [Db.Value.Text("ana")])
  return ok()
end

def run() -> Result[Void]
  conn = try Db.open("sqlite::memory:", [Sqlite.driver()])
  puts(try Migrate.up(conn, migrations()))
  puts(try Migrate.up(conn, migrations()))
  Db.close(conn)
  return ok()
end

def main() -> Void
  case run()
    ok(_) -> puts("done")
    error(message) -> IO.warn(message)
  end
end
Output
[1, 2, 3]
[]
done

up gives the versions it applied, and an empty list when the database was already up to date. Call it at startup, before the server listens.

Lay out migrations #

Give each migration its own file, as Rails does, numbered with zero-padded versions so that the folder lists them in order: migrations/m0001_create_users.agc, migrations/m0002_users_name_index.agc… Each file exports one migration(), and a code migration keeps its function in the same file.

import Db from "pkg/db/db"
import Migrate from "pkg/db/migrate"

def migration() -> Migrate.Migration
  return Migrate.code(3, "first user", &first_user)
end

def first_user(tx: Db.Connection) -> Result[Void]
  try Db.execute(tx, "insert into users (name) values (?)", [Db.Value.Text("ana")])
  return ok()
end

migrations.agc only imports the files and returns the list in order. An import path is relative to the importing file, so the files of the folder are imported as migrations/m0001_create_users from the file next to the folder.

import Migrate from "pkg/db/migrate"
import CreateUsers from "migrations/m0001_create_users"
import UsersNameIndex from "migrations/m0002_users_name_index"
import FirstUser from "migrations/m0003_first_user"

def all() -> List[Migrate.Migration]
  return [CreateUsers.migration(), UsersNameIndex.migration(), FirstUser.migration()]
end

up does not see the folder, so a file that is missing from the list is never applied. Add a test for it: the versions go from 1 to N with no gap, and N is the number of .agc files in the folder (Dir.list is relative to the folder you run agentica test in).

import Migrations from "../src/migrations"

def main() -> Void
  Test.run([
    Test.test("every migration file is in the list, numbered 1 to N") do
      versions = Migrations.all().map do |migration| migration.version end
      try Test.eq(versions, (1..versions.length).to_a)
      files = (try Dir.list("src/migrations")).select do |name| name.end_with?(".agc") end
      return Test.eq(files.length, versions.length)
    end,
  ])
end

With timestamp versions (see “What up refuses”) the versions are not 1 to N, so keep only the count check: up itself refuses a list whose versions do not strictly ascend, which catches a file listed twice. A runnable copy of this layout is in src/db/examples/ of the repository (migrations.agc, migrations/ and migrate_layout.agc, which the specs run).

What up does #

  • It creates the history table when it is missing (version, name and checksum), then reads it.
  • Each pending migration runs in its own transaction, together with the insert of its row in the history. A migration that fails (an error from a statement or from the function) rolls back, and up stops with migrate error: migration 2 (index) failed: <the database message>. The migrations before it stay applied, so fixing the migration and calling up again continues from there.
  • The checksum is the SHA-256 of the statements. A function migration has the checksum of its name only, since a function has no text to compare: a change to its body is not noticed.

What up refuses #

up gives an error and runs no migration when the history does not fit the list. The message starts with migrate error:, as does an error of the database while up creates or reads the history table.

ProblemMessage
a version is not above the one before it in the list (versions are positive and ascend)migration 1 (b) must have a version above 2: ...
an applied migration is not in the listmigration 2 (b) is applied but not in the list
an applied migration has other statements than when it ranmigration 1 (users) was changed after it was applied
an applied migration has another name than when it ranmigration 1 (people) was renamed after it was applied, it was (users)
a name that is empty or longer than 255 bytesmigration 1 needs a name of 1 to 255 bytes
a new migration has a lower version than an applied onemigration 2 (b) is older than the applied migration 3, so it would run out of order
table is not letters, digits and _, or starts with a digitthe table name must be letters, digits and _, ...

Never edit or rename a migration that has been applied: add a new one. A version can be any increasing number, such as a timestamp (20261103120000), so that two people who add a migration on two branches do not pick the same number. The two migrations then both exist, but the one with the lower version is refused if the other one was applied first (the out-of-order row above). To recover, renumber the pending migration above the applied ones before you merge it; if it already ran somewhere, give it the same version there.

Databases #

The code is the same on SQLite, PostgreSQL and MySQL, and the statements are yours, so write sql the database takes. SQLite and PostgreSQL roll back a schema change that fails inside a migration. MySQL commits at once after a statement that changes the schema (create table, alter table…), so a failure between such a statement and the insert of its history row leaves it applied and unrecorded. On MySQL, put each schema change alone in its own migration and make a repeat harmless (create table if not exists, drop table if exists), so that running up again passes.

Run up from one process at a time. If two run together, one of them fails on the primary key of the history table. On PostgreSQL the first run of two can also fail in create table if not exists with a unique error on pg_type. On MySQL the schema change may already be done.

Things to know #

  • There is no down. To undo a change, add a new migration that does it.
  • A function migration runs inside the transaction of up: it must not start a transaction of its own (Db.transaction does not nest).
  • On SQLite, PRAGMA foreign_keys = off does nothing inside a transaction, so it does nothing in a migration. Rebuild a table in the way the SQLite documentation describes, or run the pragma on the connection before up.
  • name is 1 to 255 bytes, and it is recorded: renaming an applied migration is refused like changing its sql.

table names the history, so two lists of migrations can have two histories in one database (Migrate.up(conn, plugin_migrations, "plugin_migrations")).