migrate

migrate applies SQL migration files to a database, in order, each at most once. It records every migration it runs in the database itself, and refuses to run when the files no longer match that history. Package evantann.com/migrate/sqlite implements the store for SQLite.

Migrations

A migration is a file in the migrations directory whose name starts with a number and ends in .sql, such as 1_users.sql or 20260108_orders.sql. Other files, directories, and hidden files are ignored. Files run in the numeric order of their prefix, so 2.sql runs before 10.sql, and two files may not share a number.

Each file is split into statements: a statement ends at a line ending in ;, or for a CREATE TRIGGER at a line ending in END;. Blank lines, -- comments, and /* */ comments that start a line are dropped. Statements run one at a time, and after each one a checkpoint with its SHA-256 checksum is saved, so a file that fails partway resumes after the last statement that succeeded, provided the statements already run are unchanged.

When a file finishes, migrate checks that no row's foreign key points at a missing parent row. If one does, the file is not recorded and its checkpoints are kept: append statements that fix the data and migrate again. Otherwise the file is recorded in the meta table with its content and the SHA-256 checksum of the whole file.

On every run migrate compares the recorded history against the files, and fails when a migration already run is missing, has a changed checksum, or when a new file sorts before one already run. Add new migrations; never edit or reorder old ones.

Usage

import (
    "evantann.com/migrate"
    "evantann.com/migrate/sqlite"
)

func migrateDB(
    ctx context.Context,
    logger *slog.Logger,
    path string,
    migrations fs.FS,
) error {
    db := sqlite.New(path)
    if err := db.Open(); err != nil {
        return fmt.Errorf("open: %w", err)
    }
    defer func() { _ = db.Close() }()

    m, err := migrate.New(
        ctx,
        db,
        logger,
        migrations,
    )
    if err != nil {
        return fmt.Errorf("new: %w", err)
    }
    if _, err = m.Migrate(ctx); err != nil {
        return fmt.Errorf("migrate: %w", err)
    }
    return nil
}

migrations is usually an embed.FS narrowed with fs.Sub to the directory holding the files. The sqlite store uses the pure-Go modernc.org/sqlite driver and opens the database in WAL mode with incremental auto-vacuum. Any other database can be used by implementing migrate.Store.

migrate.Statements splits SQL into statements the same way migrations are split, which is handy for loading seed files in tests.

On OpenBSD, Pledge and Unveil restrict a migration command to the syscalls and paths it needs.

License

ISC; see LICENSE. Derived from thankful-ai/migrate, also ISC.