migratekit

README

migratekit

Minimal database migration library with app-scoped migrations and automatic locking.

Install

go get github.com/open-rails/migratekit

Usage

PostgreSQL (Complete Example)

package main

import (
    "context"
    "database/sql"
    "embed"

    "github.com/open-rails/migratekit"
    _ "github.com/lib/pq"
)

//go:embed migrations/postgres/*.sql
var postgresFS embed.FS

func main() {
    ctx := context.Background()
    db, _ := sql.Open("postgres", "postgres://...")

    // Load migrations from embedded FS
    migrations, _ := migratekit.LoadFromFS(postgresFS, "migrations/postgres")

    // Run migrations (2 lines; ApplyMigrations ensures the tracking table)
    m := migratekit.NewPostgres(db, "doujins")
    m.ApplyMigrations(ctx, migrations)
}

ClickHouse (Complete Example)

package main

import (
    "context"
    "database/sql"
    "embed"

    "github.com/open-rails/migratekit"
    _ "github.com/lib/pq"
)

//go:embed migrations/clickhouse/*.sql
var clickhouseFS embed.FS

func main() {
    ctx := context.Background()
    pg, _ := sql.Open("postgres", "postgres://...")

    // Load migrations from embedded FS
    migrations, _ := migratekit.LoadFromFS(clickhouseFS, "migrations/clickhouse")

    // Run migrations (3 lines)
    m := migratekit.NewClickHouse(&migratekit.ClickHouseConfig{
        ClientAddr: "clickhouse:9000",
        Database:   "analytics",
        Username:   "analytics_user",
        Password:   "analytics_password",
        App:        "doujins",

        // ClickHouse migrations are tracked in Postgres public.migrations
        // (database='clickhouse') and use Postgres advisory locks.
        PostgresDB: pg,
    })
    m.ApplyMigrations(ctx, migrations)
}

Startup validation (read-only)

// Fail app startup if any migration is pending. Never creates tables.
err := migratekit.ValidatePostgresMigrations(ctx, db,
    migratekit.MigrationSource{App: "authkit", FS: authkitFS},
    migratekit.MigrationSource{App: "billing", FS: billingFS},
)

Stable API (v1)

Everything in this section is the v1 compatibility boundary. Within v1.x it will only grow — no removals, no signature changes, no breaking behavior changes to the documented contracts below. Anything NOT listed here (unexported helpers, exact error message text, internal locking mechanics) is an implementation detail and may change in any release.

Loading

Symbol	Contract
type Migration struct { Name, Content string }	One SQL migration: filename + raw file content.
LoadFromFS(fsys fs.FS, dir ...string) ([]Migration, error)	Loads every *.up.sql in dir (default "."), ordered by numeric prefix. Errors on duplicate normalized prefixes. Only the first dir element is used.
Prefix(name string) string	Normalized numeric prefix of a migration filename ("001_x.up.sql" → "1"). This is the tracking key stored in public.migrations.name.

Postgres

Symbol	Contract
NewPostgres(db *sql.DB, app string) *Postgres	Migrator for one app's migrations. Never closes db.
(*Postgres) WithSchema(schema string) *Postgres	Migrations run under SET LOCAL search_path = "<schema>", public. Tracking stays in public.migrations.
(*Postgres) ApplyMigrations(ctx, []Migration) error	The one-call path: ensures the tracking table, applies every unapplied migration in order under the advisory lock (lock taken only when there is work), records each by Prefix. Each migration runs in its own transaction.
(*Postgres) Applied(ctx) ([]string, error)	Recorded migration names (normalized prefixes) for this app, database='postgres'.
(*Postgres) Setup(ctx) error	Ensures public.migrations exists (idempotent). ApplyMigrations calls it for you.
(*Postgres) ValidateAllApplied(ctx, []Migration) error	Read-only startup gate: error naming pending migrations, never creates tables.

ClickHouse

Symbol	Contract
type ClickHouseConfig struct { ClientAddr, Database, Username, Password, App, Cluster string; PostgresDB *sql.DB }	PostgresDB is required: tracking rows live in Postgres public.migrations (database='clickhouse') and locking uses Postgres advisory locks. Cluster enables {{ON_CLUSTER}} expansion.
NewClickHouse(*ClickHouseConfig) *ClickHouse	Migrator; connects to ClickHouse lazily via native protocol.
(*ClickHouse) ApplyMigrations(ctx, []Migration) error	Same shape as Postgres. Statements run individually (no transactions) with up-to-30s retry on transient distributed-DDL errors.
(*ClickHouse) Applied(ctx) ([]string, error)	Recorded names for this app, database='clickhouse'.
(*ClickHouse) Setup(ctx) error	Ensures the Postgres tracker is ready.
(*ClickHouse) ValidateAllApplied(ctx, []Migration) error	Read-only startup gate.
(*ClickHouse) Close() error	Closes only the native ClickHouse connection the migrator itself opened (never PostgresDB).

Multi-source startup validation

Symbol	Contract
type MigrationSource struct { App string; FS fs.FS }	One app's migration filesystem.
ValidatePostgresMigrations(ctx, db, ...MigrationSource) error	ValidateAllApplied across several apps in one call.
ValidateClickHouseMigrations(ctx, *ClickHouseConfig, fs.FS) error	Same for ClickHouse.

Frozen behavioral contracts

These behaviors are part of the API and will not change within v1.x:

1. Tracking table: public.migrations (id, app, database, name, migrated_at, UNIQUE(app, database, name)) with database ∈ {postgres, clickhouse}. Operators may query and (carefully) repair it; its shape is stable.
2. Tracking key: the normalized numeric prefix (Prefix), not the filename — renaming 001_users.up.sql to 001_accounts.up.sql does not re-apply it.
3. Discovery: only *.up.sql files; *.down.sql is reserved; numeric-prefix ordering; duplicate prefixes are a load error.
4. Locking: appliers are serialized by Postgres advisory locks held on a dedicated pinned connection for the duration of the apply; the lock is taken only when unapplied migrations exist; process death releases the lock with the connection.
5. Templates: {{VAR}} / ${VAR} substitute from the environment at apply time; an unset variable is an error; an explicitly-empty variable substitutes as-is; {{ON_CLUSTER}}/${ON_CLUSTER} expand from ClickHouseConfig.Cluster (empty → removed).
6. Postgres atomicity: one migration = one transaction (DDL + tracking row commit together).
7. ClickHouse non-atomicity: statements are split (quote-aware) and run individually; a partial failure leaves earlier statements applied and the migration unrecorded — every statement must be individually idempotent.
8. Ownership: migrators never close a *sql.DB you pass in.

Removed in v1.0 (was public in v0.x)

• Postgres.Apply, Postgres.Lock, Postgres.Unlock, ClickHouse.Apply, ClickHouse.Lock, ClickHouse.Unlock — the manual lock/apply path bypassed the applied-set check and made stateful locking part of the surface; ApplyMigrations is the supported path. (No known consumer used these.)
• Postgres.Close — closed the caller's *sql.DB, which the migrator never owned.
• MigrationSource.FS and the ValidateClickHouseMigrations filesystem parameter are now fs.FS instead of embed.FS (source-compatible: embed.FS satisfies fs.FS).

Compatibility policy

• v1.x releases may add symbols, struct fields with useful zero values, and optional behavior — never remove or change what is documented above.
• Exact error message text is not part of the API; only the documented error conditions are. (Typed sentinel errors may be added additively later.)
• The module follows Go module semver: any future break means v2 with a new import path. The bar for v2 is intentionally very high.

Schema

migratekit creates one table in public schema on first Setup():

CREATE TABLE public.migrations (
    id BIGSERIAL PRIMARY KEY,
    app TEXT NOT NULL,
    database TEXT NOT NULL,
    name TEXT NOT NULL,
    migrated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE(app, database, name)
);

Locking:

• Postgres uses advisory locks (no lock table). The lock is acquired and released on a single dedicated connection pinned for the lock's lifetime — session advisory locks belong to the connection that took them, so going through the pool would acquire on one connection and "release" on another. If the process dies mid-migration, Postgres releases the lock when the pinned connection drops.
• ClickHouse uses Postgres advisory locks (same pinning) and requires ClickHouseConfig.PostgresDB.

Migration Files

Naming Convention

Files must follow this pattern: {number}{separator}{description}.up.sql

• Number: Any positive integer (leading zeros optional: 1, 01, 001 all work)
• Separator: Underscore _ or hyphen -
• Suffix: Must end with .up.sql

✅ Valid:

001_create_users.up.sql
1_create_users.up.sql
01-add-indexes.up.sql
42-add-timestamps.up.sql
0003_migrations.up.sql

❌ Invalid (will be skipped):

001_create_users.sql        # Missing .up.sql
create_users.up.sql         # Missing numeric prefix
001.create.users.up.sql     # Invalid separator (use _ or -)

Why .up.sql is required:

• Standard convention used by golang-migrate, bun, etc.
• Reserves .down.sql for future rollback support
• Prevents accidental execution of non-migration SQL files

What gets stored: Numeric prefixes are normalized (leading zeros removed) before storage:

• 001, 01, 1 all become "1"
• 042, 42 both become "42"

Ordering: migrations apply in numeric-prefix order (2_x before 10_x), not lexical filename order, so unpadded prefixes are safe.

Duplicate prefixes are rejected: because tracking is keyed by the normalized prefix, two files sharing a prefix (002_users.up.sql + 002_roles.up.sql, or 0042_x vs 42_y) would mean the second silently never runs. LoadFromFS returns an error instead.

Template Variables

Migration SQL may reference environment variables as {{VAR_NAME}} or ${VAR_NAME}; they are substituted at execution time. A referenced variable that is not set is an error — silent empty-string substitution previously meant a typo'd {{CLICKHOUSE_PASWORD}} shipped an empty password into DDL. A variable explicitly set to the empty string is substituted as-is. {{ON_CLUSTER}} is special-cased for ClickHouse (expanded from ClickHouseConfig.Cluster). Avoid ${...}/{{...}} sequences in migration SQL that are not meant as templates.

ClickHouse Semantics

ClickHouse has no transactional DDL. A multi-statement migration that fails partway leaves the earlier statements applied and the migration unrecorded, so the rerun re-executes them. Every statement in a ClickHouse migration must therefore be individually idempotent (CREATE TABLE IF NOT EXISTS, DROP ... IF EXISTS, etc.). Statements are split on ; with full awareness of string literals and comments, so semicolons inside quoted strings are safe.

Features

• Smart locking: Only locks when there's work to do
• App-scoped: Each app has independent migration sequences
• Correct Postgres locking: Uses Postgres advisory locks (no lock table)
• ClickHouse compatibility: Runs ClickHouse DDL via native protocol; tracks applied migrations in Postgres
• Self-contained: Creates own tables on first run
• Minimal: small codebase + minimal dependencies

Design

Why lock only when needed?

Checking what's applied (SELECT) doesn't need a lock. Only write operations need locks. This allows multiple services to check migrations concurrently without blocking.

Why per-app scoping?

Different apps (doujins, hentai0, billing) have independent migration sequences and can migrate concurrently.

Why single table?

Easy to query "show all migrations" and simpler permissions.

Documentation

Index

• func Prefix(name string) string
• func ValidateClickHouseMigrations(ctx context.Context, config *ClickHouseConfig, fsys fs.FS) error
• func ValidatePostgresMigrations(ctx context.Context, db *sql.DB, sources ...MigrationSource) error
• type ClickHouse
  • func NewClickHouse(config *ClickHouseConfig) *ClickHouse
  • func (c *ClickHouse) Applied(ctx context.Context) ([]string, error)
  • func (c *ClickHouse) ApplyMigrations(ctx context.Context, migrations []Migration) (err error)
  • func (c *ClickHouse) Close() error
  • func (c *ClickHouse) Setup(ctx context.Context) error
  • func (c *ClickHouse) ValidateAllApplied(ctx context.Context, migrations []Migration) error
• type ClickHouseConfig
• type Migration
  • func LoadFromFS(fsys fs.FS, dir ...string) ([]Migration, error)
• type MigrationSource
• type Postgres
  • func NewPostgres(db *sql.DB, app string) *Postgres
  • func (p *Postgres) Applied(ctx context.Context) ([]string, error)
  • func (p *Postgres) ApplyMigrations(ctx context.Context, migrations []Migration) (err error)
  • func (p *Postgres) Setup(ctx context.Context) error
  • func (p *Postgres) ValidateAllApplied(ctx context.Context, migrations []Migration) error
  • func (p *Postgres) WithSchema(schema string) *Postgres

Functions

func Prefix

func Prefix(name string) string

Prefix extracts numeric prefix from migration filenames and normalizes it. Supports both underscore and hyphen separators. Examples:

"001_create_users.up.sql" -> "1"
"1-create-users.up.sql"   -> "1"
"0042_add_field.up.sql"   -> "42"

func ValidateClickHouseMigrations

func ValidateClickHouseMigrations(ctx context.Context, config *ClickHouseConfig, fsys fs.FS) error

ValidateClickHouseMigrations validates ClickHouse migrations. Returns an error if any migrations are pending.

func ValidatePostgresMigrations

func ValidatePostgresMigrations(ctx context.Context, db *sql.DB, sources ...MigrationSource) error

ValidatePostgresMigrations validates multiple Postgres migration sources at once. Returns an error if any migrations are pending.

Types

type ClickHouse

type ClickHouse struct {
	// contains filtered or unexported fields
}

ClickHouse handles ClickHouse migrations via native protocol

func NewClickHouse

func NewClickHouse(config *ClickHouseConfig) *ClickHouse

NewClickHouse creates a ClickHouse migrator from config. Uses native protocol for all connections.

func (*ClickHouse) Applied

func (c *ClickHouse) Applied(ctx context.Context) ([]string, error)

Applied returns list of applied migrations

func (*ClickHouse) ApplyMigrations

func (c *ClickHouse) ApplyMigrations(ctx context.Context, migrations []Migration) (err error)

ApplyMigrations applies all unapplied migrations (only locks if needed) Automatically calls Setup() to ensure migration tables exist before proceeding.

func (*ClickHouse) Close

func (c *ClickHouse) Close() error

Close closes the connection

func (*ClickHouse) Setup

func (c *ClickHouse) Setup(ctx context.Context) error

Setup ensures database and tables exist

func (*ClickHouse) ValidateAllApplied

func (c *ClickHouse) ValidateAllApplied(ctx context.Context, migrations []Migration) error

ValidateAllApplied checks if all provided migrations have been applied. Returns an error listing any pending migrations if validation fails. This is intended for use during application startup to ensure the database schema is up-to-date before the app starts serving requests.

type ClickHouseConfig

type ClickHouseConfig struct {
	ClientAddr string // Native protocol address (e.g., clickhouse:9000)
	Database   string
	Username   string
	Password   string
	App        string
	Cluster    string // Optional; if specified, uses ON CLUSTER for DDL statements

	// Required. ClickHouse migration tracking + locking is done in Postgres:
	// - tracking: Postgres public.migrations with database='clickhouse'
	// - locking: Postgres advisory locks
	//
	// This intentionally avoids ClickHouse-based migration tables (`migrations`, `migration_locks`)
	// which are awkward to restore/merge and are not a good fit for authoritative state.
	PostgresDB *sql.DB
}

ClickHouseConfig holds configuration for ClickHouse migrations

type Migration

type Migration struct {
	Name    string
	Content string
}

Migration is a single SQL migration

func LoadFromFS

func LoadFromFS(fsys fs.FS, dir ...string) ([]Migration, error)

LoadFromFS loads migrations from an embedded filesystem. Reads all .up.sql files, ordered by numeric prefix (so unpadded names like 2_x and 10_x apply in numeric order), falling back to filename order for non-numeric names. Returns an error if two files normalize to the same Prefix() — tracking is prefix-keyed, so a duplicate prefix would silently skip the second file as "already applied". If dir is empty, defaults to "." (root of the filesystem).

type MigrationSource

type MigrationSource struct {
	App string
	FS  fs.FS
}

MigrationSource represents a migration source with an app name and a migration filesystem (any fs.FS; embed.FS satisfies it).

type Postgres

type Postgres struct {
	// contains filtered or unexported fields
}

Postgres handles PostgreSQL migrations

func NewPostgres

func NewPostgres(db *sql.DB, app string) *Postgres

NewPostgres creates a Postgres migrator

func (*Postgres) Applied

func (p *Postgres) Applied(ctx context.Context) ([]string, error)

Applied returns list of applied migration names

func (*Postgres) ApplyMigrations

func (p *Postgres) ApplyMigrations(ctx context.Context, migrations []Migration) (err error)

ApplyMigrations applies all unapplied migrations (only locks if needed). Setup() always runs first, so there is no missing-table special case.

func (*Postgres) Setup

func (p *Postgres) Setup(ctx context.Context) error

Setup ensures migration tables exist (idempotent)

func (*Postgres) ValidateAllApplied

func (p *Postgres) ValidateAllApplied(ctx context.Context, migrations []Migration) error

ValidateAllApplied checks if all provided migrations have been applied. Returns an error listing any pending migrations if validation fails. This is intended for use during application startup to ensure the database schema is up-to-date before the app starts serving requests.

func (*Postgres) WithSchema

func (p *Postgres) WithSchema(schema string) *Postgres

WithSchema configures the schema to target for unqualified DDL/DML in migrations (via SET LOCAL search_path). This allows embedded subsystems to create tables in the host application's schema (River-style).