migratenumbered SQL migrations for SQLite

migrate

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:

  1. every applied version must still have its file, byte-identical (a changed or missing file is an error, never silently re-run);
  2. a file whose version is below the newest applied one but was never applied is an error (history is append-only);
  3. before the first pending file runs, an optional backup is taken with VACUUM INTO (the caller names the path; it must not exist);
  4. 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;
  5. 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) error

Baseline 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)
}
Output
1 applied: 0002-add-name.sql

Load

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)
	}
}
Output
1 baseline
2 add-name

Next

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)
}
Output
0002-add-name.sql

Run

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")
}
Output
2 applied
0 applied the second time

Status

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)
}
Output
1 applied, 1 pending: 0002-add-name.sql