Schema migrations
A migration is a numbered statement that changes the schema once. A program keeps a table of the versions it has applied, compares it with the list it carries, and runs the difference in order. The table is tiny and the discipline is the whole lesson: numbered, forward-only, committed with the code that needs it, and never edited after it shipped.
The version table
schema_migrations holds one row per applied version. The runner below creates it, then walks a list of versions and statements: a version already in the table is skipped, the others run and are recorded. Two runs of the same program apply two migrations and then none, which is the property that makes it safe to run at every start-up. The driver is modernc.org/sqlite v1.62.0.
package main
import (
"database/sql"
"fmt"
_ "modernc.org/sqlite"
)
type migration struct {
version int
stmt string
}
func apply(db *sql.DB, migrations []migration) (int, error) {
applied := 0
for _, m := range migrations {
var exists int
err := db.QueryRow(`SELECT COUNT(*) FROM schema_migrations WHERE version = ?`, m.version).Scan(&exists)
if err != nil {
return applied, err
}
if exists > 0 {
continue
}
if _, err := db.Exec(m.stmt); err != nil {
return applied, err
}
if _, err := db.Exec(`INSERT INTO schema_migrations (version) VALUES (?)`, m.version); err != nil {
return applied, err
}
applied++
}
return applied, nil
}
func main() {
db, err := sql.Open("sqlite", ":memory:")
if err != nil {
panic(err)
}
defer db.Close()
db.SetMaxOpenConns(1)
if _, err := db.Exec(`CREATE TABLE schema_migrations (version INTEGER PRIMARY KEY)`); err != nil {
panic(err)
}
migrations := []migration{
{1, `CREATE TABLE notes (id INTEGER PRIMARY KEY, title TEXT NOT NULL)`},
{2, `ALTER TABLE notes ADD COLUMN done INTEGER NOT NULL DEFAULT 0`},
}
for run := 1; run <= 2; run++ {
n, err := apply(db, migrations)
fmt.Println("run", run, "applied:", n, "err:", err)
}
rows, err := db.Query(`SELECT version FROM schema_migrations ORDER BY version`)
if err != nil {
panic(err)
}
defer rows.Close()
fmt.Print("versions:")
for rows.Next() {
var v int
if err := rows.Scan(&v); err != nil {
panic(err)
}
fmt.Print(" ", v)
}
fmt.Println()
}
go build -o app . && ./app
run 1 applied: 2 err: <nil>
run 2 applied: 0 err: <nil>
versions: 1 2
The second run read the table and did nothing, which is what a rolling deploy with ten replicas needs: the first pod applies the change and the others see it recorded. The order of the list is the order of the versions, and a gap is a bug in the list rather than something the runner repairs.
Applying the migrations
A migration is a string in the program or a file next to it. A file has two advantages: the SQL is edited in an editor with a SQL mode, and a test can read the same file the program runs. The name carries the version, as in 0002_add_done.sql, and the runner sorts the names before it applies them. A migration that was applied is never edited, because the databases that ran the old version will not run the new one.
The version is a number and a name together in most tools: goose, migrate and golang-migrate all keep a table like the one above and read a directory of files. A small service does not need the tool; it needs the table, the sorted list and the rule that the schema of the code and the schema of the database are the same version at start-up.
A migration that fails
A migration against a table that does not exist fails, and the failure is not recorded, so the next run tries again. That is the correct behaviour: the database is at the version it was, and an operator can fix the migration and restart. The program below shows the error and the version table after it.
package main
import (
"database/sql"
"fmt"
_ "modernc.org/sqlite"
)
func main() {
db, err := sql.Open("sqlite", ":memory:")
if err != nil {
panic(err)
}
defer db.Close()
db.SetMaxOpenConns(1)
if _, err := db.Exec(`CREATE TABLE schema_migrations (version INTEGER PRIMARY KEY)`); err != nil {
panic(err)
}
if _, err := db.Exec(`INSERT INTO schema_migrations (version) VALUES (1)`); err != nil {
panic(err)
}
_, err = db.Exec(`ALTER TABLE missing ADD COLUMN done INTEGER`)
fmt.Println("migration 2 error:", err)
var versions int
if err := db.QueryRow(`SELECT COUNT(*) FROM schema_migrations`).Scan(&versions); err != nil {
panic(err)
}
fmt.Println("recorded versions:", versions)
}
go build -o app . && ./app
migration 2 error: SQL logic error: no such table: missing (1)
recorded versions: 1
The version table still says one, so the runner is consistent with the schema. A service that runs migrations at start-up logs this error and exits, because a replica that serves requests against an old schema is worse than a replica that does not start.
One transaction per migration
A migration has two parts: the schema change and the version row. If the first succeeds and the second fails, the database has the change and the table says it does not, and the next run fails on a duplicate column. One transaction per migration makes the pair atomic; the program below runs a broken statement in a transaction, rolls back, and checks both the schema and the table.
package main
import (
"database/sql"
"fmt"
_ "modernc.org/sqlite"
)
func main() {
db, err := sql.Open("sqlite", ":memory:")
if err != nil {
panic(err)
}
defer db.Close()
db.SetMaxOpenConns(1)
if _, err := db.Exec(`CREATE TABLE notes (id INTEGER PRIMARY KEY, title TEXT NOT NULL)`); err != nil {
panic(err)
}
if _, err := db.Exec(`CREATE TABLE schema_migrations (version INTEGER PRIMARY KEY)`); err != nil {
panic(err)
}
tx, err := db.Begin()
if err != nil {
panic(err)
}
if _, err := tx.Exec(`ALTER TABLE notes ADD COLUMN broken INTEGER NOT NULL DEFAULT`); err != nil {
fmt.Println("migration error:", err)
if err := tx.Rollback(); err != nil {
panic(err)
}
} else {
if _, err := tx.Exec(`INSERT INTO schema_migrations (version) VALUES (2)`); err != nil {
panic(err)
}
if err := tx.Commit(); err != nil {
panic(err)
}
}
var columns int
err = db.QueryRow(`SELECT COUNT(*) FROM pragma_table_info('notes') WHERE name = 'broken'`).Scan(&columns)
fmt.Println("broken column added:", columns > 0, "err:", err)
var versions int
if err := db.QueryRow(`SELECT COUNT(*) FROM schema_migrations`).Scan(&versions); err != nil {
panic(err)
}
fmt.Println("recorded versions:", versions)
}
go build -o app . && ./app
migration error: SQL logic error: incomplete input (1)
broken column added: false err: <nil>
recorded versions: 0
Neither half happened: no column and no version row. The statement failed before SQLite changed anything, and the transaction is what makes that guaranteed. A migration runner wraps each statement, or each file, in one transaction, and a migration that cannot run in one (a large index build on some databases) says so and is handled as an exception.
Data in a migration
A schema change is only half of a migration: a new column usually needs a value for the rows that existed before it. The program below adds a column with a default and then backfills one row, and the select at the end shows the two rows with different values. The backfill is an ordinary statement in the same numbered migration, not a separate step a human remembers.
package main
import (
"database/sql"
"fmt"
_ "modernc.org/sqlite"
)
func main() {
db, err := sql.Open("sqlite", ":memory:")
if err != nil {
panic(err)
}
defer db.Close()
db.SetMaxOpenConns(1)
steps := []string{
`CREATE TABLE notes (id INTEGER PRIMARY KEY, title TEXT NOT NULL)`,
`INSERT INTO notes (title) VALUES ('buy milk'), ('write lesson')`,
`ALTER TABLE notes ADD COLUMN done INTEGER NOT NULL DEFAULT 0`,
`UPDATE notes SET done = 1 WHERE title = 'buy milk'`,
}
for _, step := range steps {
if _, err := db.Exec(step); err != nil {
panic(err)
}
}
rows, err := db.Query(`SELECT title, done FROM notes ORDER BY id`)
if err != nil {
panic(err)
}
defer rows.Close()
for rows.Next() {
var title string
var done int
if err := rows.Scan(&title, &done); err != nil {
panic(err)
}
fmt.Println(title, done)
}
}
go build -o app . && ./app
buy milk 1
write lesson 0
The backfill ran against the two rows that existed, and a row inserted after the migration gets the default until the application sets it. A backfill of a million rows is a different problem: it is batched, it is restartable, and it is often a migration of its own that runs in the background rather than at start-up.
Rules for a migration set
Numbered, forward-only, one change each. Never edit a migration that ran somewhere. A destructive change is two migrations: first the new column and the code that writes both, later the old column goes. Every migration is tested on a database that starts at the previous version, because a migration that works on an empty database can still fail on a populated one. And the version table is backed up with the data, since a migration that cannot be identified cannot be repaired.
The capstone service embeds its migrations with //go:embed and runs them at start-up through the function above, which is the version a small Go service needs. The next lesson puts a browser in front of that service.
Exercises
- Add a third migration that creates an index and run the program twice. How many migrations does the second run apply, and why?
- Remove the transaction from the broken migration and read the two checks. What state is the database in, and what would the next start-up do?
- Make the backfill a separate migration that runs after the schema change. Which order must the two have, and what happens if a row is inserted between them?
Questions
A new set of questions in a new order every time. Choose one answer for each, then check.
-
What does a migration table hold?
Answer
One row per applied version
The runner compares it with its list.
-
What does a second run of the same migrations apply?
Answer
Nothing
The recorded versions are skipped.
-
What happens to a migration that fails?
Answer
It is not recorded and is retried on the next run
The database stays at its version.
-
Why wrap a migration in a transaction?
Answer
The schema change and the version row must both happen
A half-applied migration breaks the next run.
-
Why is an applied migration never edited?
Answer
Some databases already ran the old version
A new version is the fix.
-
How is a destructive change done safely?
Answer
Add the new column and both writes first, drop the old one later
The old code must keep working during the deploy.
-
Where does a backfill belong?
Answer
In a numbered migration with the schema change or right after it
A human who remembers is not a step.
-
Why test a migration on the previous version?
Answer
A migration that works on an empty database can fail on a full one
The data is part of the input.
-
What should a service do when a start-up migration fails?
Answer
Log it and exit instead of serving an old schema
A wrong schema in production is worse than a stopped replica.
-
Which tool shape fits a small Go service?
Answer
A table, a sorted list of embedded SQL files and one transaction each
That is the runner built in this lesson.
The code on this page was run with go 1.26.10 on Alpine Linux 3.24.3 (x86_64) in a Docker container.
Article author: Arthur Isaev