import "github.com/Deufel/migrate"
Overview
Package migrate runs numbered SQL migrations against a SQLite database — the fastmigrate rules (answer.ai) with two additions: every file runs in its own transaction with a foreign-key check before commit, and every applied file's checksum is recorded so a committed file can never change.
A migration is a file named NNNN-description.sql (a four-digit version, a kebab description). Files run in version order; each is applied at most once; the record lives in one table (sys_migration by default):
version · name · sha256 · applied_at
Rules, in the order the runner checks them:
- every applied version must still have its file, byte-identical (a changed or missing file is an error, never silently re-run);
- a file whose version is below the newest applied one but was never applied is an error (history is append-only);
- before the first pending file runs, an optional backup is taken with VACUUM INTO (the caller names the path; it must not exist);
- each file runs on one connection: PRAGMA foreign_keys=OFF, BEGIN, the file, PRAGMA foreign_key_check (any row fails), the record row, COMMIT, PRAGMA foreign_keys=ON — the documented SQLite rebuild procedure, so a file may drop and recreate a table with data kept;
- the first error stops the run; files before it stay applied, the failed one is rolled back whole.
A structure change is written as SQL: create the new table, INSERT … SELECT, drop the old, rename, recreate the indexes. The declaration changes and the data follows in the same file.
Functions
Baseline
func Baseline(ctx context.Context, db *sql.DB, o Options, version int) errorBaseline stamps every file up to and including version as applied WITHOUT running it — for a database that already carries the schema the baseline file describes. Refused when anything is recorded already.
Next
func Next(fsys fs.FS, description string) (string, error)Next answers the file name a new migration should take.
Types
Options
type Options struct {
FS fs.FS // the directory of NNNN-name.sql files (its root)
Table string // the record table; "" = sys_migration
Backup string // a path for VACUUM INTO before the first pending step; "" = none
Log func(format string, args ...any)
}Options configure a run.
Step
type Step struct {
Version int
Name string // the description part of the file name
File string
SHA string // sha256 of the file's bytes
AppliedAt string // RFC3339, "" when pending
}Step is one migration file, applied or pending.
Load
func Load(fsys fs.FS) ([]Step, error)Load reads and orders the files. A name off the pattern, a duplicate version or an empty file is an error.
Run
func Run(ctx context.Context, db *sql.DB, o Options) ([]Step, error)Run applies every pending file in order and answers the ones applied. The first error stops the run with the failed file rolled back.
Status
func Status(ctx context.Context, db *sql.DB, o Options) (applied, pending []Step, err error)Status answers what is applied and what is pending, after verifying rules 1 and 2. The applied steps carry the file's name and hash from the record; the pending ones from the file.
Examples
Baseline
package main
import (
"context"
"database/sql"
"fmt"
"os"
"path/filepath"
"github.com/Deufel/migrate"
_ "modernc.org/sqlite"
)
// dir writes a migrations directory for an example and answers it.
func dir(files map[string]string) string {
d, _ := os.MkdirTemp("", "migrate-example")
for name, body := range files {
_ = os.WriteFile(filepath.Join(d, name), []byte(body), 0o644)
}
return d
}
func open() *sql.DB {
d, _ := os.MkdirTemp("", "migrate-example")
db, _ := sql.Open("sqlite", filepath.Join(d, "app.db"))
return db
}
func main() {
db := open()
defer db.Close()
// a store that already has the table: the files up to 1 are declared
// applied without running
_, _ = db.Exec("CREATE TABLE thing (id INTEGER PRIMARY KEY)")
o := migrate.Options{FS: os.DirFS(dir(map[string]string{
"0001-baseline.sql": "CREATE TABLE thing (id INTEGER PRIMARY KEY);",
"0002-add-name.sql": "ALTER TABLE thing ADD COLUMN name TEXT NOT NULL DEFAULT '';",
}))}
_ = migrate.Baseline(context.Background(), db, o, 1)
applied, _ := migrate.Run(context.Background(), db, o)
fmt.Println(len(applied), "applied:", applied[0].File)
}1 applied: 0002-add-name.sqlLoad
package main
import (
"fmt"
"os"
"path/filepath"
"github.com/Deufel/migrate"
_ "modernc.org/sqlite"
)
// dir writes a migrations directory for an example and answers it.
func dir(files map[string]string) string {
d, _ := os.MkdirTemp("", "migrate-example")
for name, body := range files {
_ = os.WriteFile(filepath.Join(d, name), []byte(body), 0o644)
}
return d
}
func main() {
steps, _ := migrate.Load(os.DirFS(dir(map[string]string{
"0001-baseline.sql": "CREATE TABLE thing (id INTEGER PRIMARY KEY);",
"0002-add-name.sql": "ALTER TABLE thing ADD COLUMN name TEXT NOT NULL DEFAULT '';",
})))
for _, s := range steps {
fmt.Println(s.Version, s.Name)
}
}1 baseline
2 add-nameNext
package main
import (
"fmt"
"os"
"path/filepath"
"github.com/Deufel/migrate"
_ "modernc.org/sqlite"
)
// dir writes a migrations directory for an example and answers it.
func dir(files map[string]string) string {
d, _ := os.MkdirTemp("", "migrate-example")
for name, body := range files {
_ = os.WriteFile(filepath.Join(d, name), []byte(body), 0o644)
}
return d
}
func main() {
name, _ := migrate.Next(os.DirFS(dir(map[string]string{
"0001-baseline.sql": "CREATE TABLE thing (id INTEGER PRIMARY KEY);",
})), "add-name")
fmt.Println(name)
}0002-add-name.sqlRun
package main
import (
"context"
"database/sql"
"fmt"
"os"
"path/filepath"
"github.com/Deufel/migrate"
_ "modernc.org/sqlite"
)
// dir writes a migrations directory for an example and answers it.
func dir(files map[string]string) string {
d, _ := os.MkdirTemp("", "migrate-example")
for name, body := range files {
_ = os.WriteFile(filepath.Join(d, name), []byte(body), 0o644)
}
return d
}
func open() *sql.DB {
d, _ := os.MkdirTemp("", "migrate-example")
db, _ := sql.Open("sqlite", filepath.Join(d, "app.db"))
return db
}
func main() {
db := open()
defer db.Close()
o := migrate.Options{FS: os.DirFS(dir(map[string]string{
"0001-baseline.sql": "CREATE TABLE thing (id INTEGER PRIMARY KEY);",
"0002-add-name.sql": "ALTER TABLE thing ADD COLUMN name TEXT NOT NULL DEFAULT '';",
}))}
applied, _ := migrate.Run(context.Background(), db, o)
fmt.Println(len(applied), "applied")
again, _ := migrate.Run(context.Background(), db, o)
fmt.Println(len(again), "applied the second time")
}2 applied
0 applied the second timeStatus
package main
import (
"context"
"database/sql"
"fmt"
"os"
"path/filepath"
"github.com/Deufel/migrate"
_ "modernc.org/sqlite"
)
// dir writes a migrations directory for an example and answers it.
func dir(files map[string]string) string {
d, _ := os.MkdirTemp("", "migrate-example")
for name, body := range files {
_ = os.WriteFile(filepath.Join(d, name), []byte(body), 0o644)
}
return d
}
func open() *sql.DB {
d, _ := os.MkdirTemp("", "migrate-example")
db, _ := sql.Open("sqlite", filepath.Join(d, "app.db"))
return db
}
func main() {
db := open()
defer db.Close()
o := migrate.Options{FS: os.DirFS(dir(map[string]string{
"0001-baseline.sql": "CREATE TABLE thing (id INTEGER PRIMARY KEY);",
"0002-add-name.sql": "ALTER TABLE thing ADD COLUMN name TEXT NOT NULL DEFAULT '';",
}))}
_, _ = migrate.Run(context.Background(), db, migrate.Options{FS: os.DirFS(dir(map[string]string{
"0001-baseline.sql": "CREATE TABLE thing (id INTEGER PRIMARY KEY);",
}))})
applied, pending, _ := migrate.Status(context.Background(), db, o)
fmt.Println(len(applied), "applied,", len(pending), "pending:", pending[0].File)
}1 applied, 1 pending: 0002-add-name.sql