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[1, 2, 3]
[]
doneup 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()
endmigrations.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()]
endup 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,
])
endWith 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,nameandchecksum), 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
errorfrom a statement or from the function) rolls back, andupstops withmigrate error: migration 2 (index) failed: <the database message>. The migrations before it stay applied, so fixing the migration and callingupagain 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.
| Problem | Message |
|---|---|
| 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 list | migration 2 (b) is applied but not in the list |
| an applied migration has other statements than when it ran | migration 1 (users) was changed after it was applied |
| an applied migration has another name than when it ran | migration 1 (people) was renamed after it was applied, it was (users) |
a name that is empty or longer than 255 bytes | migration 1 needs a name of 1 to 255 bytes |
| a new migration has a lower version than an applied one | migration 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 digit | the 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.transactiondoes not nest). - On SQLite,
PRAGMA foreign_keys = offdoes 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 beforeup. nameis 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")).