queen

Queen

Database migrations for Go.

Queen is a database migration library that lets you define migrations in code, not separate files. It supports both SQL and Go function migrations, with built-in testing helpers and a simple, idiomatic API.

Features

• Migrations in code - Define migrations as Go code, not separate .sql files
• Flexible syntax - Use SQL strings in code, Go functions, or mix both
• Testing helpers - Built-in support for testing your migrations
• Natural sorting - Smart version ordering: "1" < "2" < "10" < "100", "user_1" < "user_10"
• Flexible versioning - Use sequential numbers, prefixes, or any naming scheme
• Type-safe - Full Go type safety for programmatic migrations
• Multiple databases - PostgreSQL, MySQL, SQLite, MS SQL Server support with extensible driver interface
• Lock protection - Prevents concurrent migration runs
• Checksum validation - Detects when applied migrations have changed
• CLI tool - Built-in command-line interface for migration management

Quick Start

Installation

PostgreSQL

go get github.com/honeynil/queen
go get github.com/honeynil/queen/drivers/postgres
go get github.com/jackc/pgx/v5/stdlib

MySQL

go get github.com/honeynil/queen
go get github.com/honeynil/queen/drivers/mysql
go get github.com/go-sql-driver/mysql

SQLite

go get github.com/honeynil/queen
go get github.com/honeynil/queen/drivers/sqlite
go get github.com/mattn/go-sqlite3

ClickHouse

go get github.com/honeynil/queen
go get github.com/honeynil/queen/drivers/clickhouse
go get github.com/ClickHouse/clickhouse-go/v2

YandexDB (YDB)

go get github.com/honeynil/queen
go get github.com/honeynil/queen/drivers/ydb
go get github.com/ydb-platform/ydb-go-sdk/v3

MS SQL Server

go get github.com/honeynil/queen
go get github.com/honeynil/queen/drivers/mssql
go get github.com/microsoft/go-mssqldb

Basic Usage

PostgreSQL

package main

import (
    "context"
    "database/sql"
    "log"

    _ "github.com/jackc/pgx/v5/stdlib"

    "github.com/honeynil/queen"
    "github.com/honeynil/queen/drivers/postgres"
)

func main() {
    // Connect to database
    db, _ := sql.Open("pgx", "postgres://localhost/myapp?sslmode=disable")
    defer db.Close()

    // Create Queen instance
    driver := postgres.New(db)
    q := queen.New(driver)
    defer q.Close()

    // Register migrations
    q.MustAdd(queen.M{
        Version: "001",
        Name:    "create_users_table",
        UpSQL: `
            CREATE TABLE users (
                id SERIAL PRIMARY KEY,
                email VARCHAR(255) NOT NULL UNIQUE,
                created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
            )
        `,
        DownSQL: `DROP TABLE users`,
    })

    q.MustAdd(queen.M{
        Version: "002",
        Name:    "add_users_name",
        UpSQL:   `ALTER TABLE users ADD COLUMN name VARCHAR(255)`,
        DownSQL: `ALTER TABLE users DROP COLUMN name`,
    })

    // Apply all pending migrations
    ctx := context.Background()
    if err := q.Up(ctx); err != nil {
        log.Fatal(err)
    }

    log.Println("Migrations applied successfully!")
}

MySQL

package main

import (
    "context"
    "database/sql"
    "log"

    _ "github.com/go-sql-driver/mysql"

    "github.com/honeynil/queen"
    "github.com/honeynil/queen/drivers/mysql"
)

func main() {
    // Connect to MySQL (parseTime=true is required!)
    db, _ := sql.Open("mysql", "user:password@tcp(localhost:3306)/myapp?parseTime=true")
    defer db.Close()

    // Create Queen instance
    driver := mysql.New(db)
    q := queen.New(driver)
    defer q.Close()

    // Register migrations
    q.MustAdd(queen.M{
        Version: "001",
        Name:    "create_users_table",
        UpSQL: `
            CREATE TABLE users (
                id INT AUTO_INCREMENT PRIMARY KEY,
                email VARCHAR(255) NOT NULL UNIQUE,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
        `,
        DownSQL: `DROP TABLE users`,
    })

    q.MustAdd(queen.M{
        Version: "002",
        Name:    "add_users_name",
        UpSQL:   `ALTER TABLE users ADD COLUMN name VARCHAR(255)`,
        DownSQL: `ALTER TABLE users DROP COLUMN name`,
    })

    // Apply all pending migrations
    ctx := context.Background()
    if err := q.Up(ctx); err != nil {
        log.Fatal(err)
    }

    log.Println("Migrations applied successfully!")
}

SQLite

package main

import (
    "context"
    "database/sql"
    "log"

    _ "github.com/mattn/go-sqlite3"

    "github.com/honeynil/queen"
    "github.com/honeynil/queen/drivers/sqlite"
)

func main() {
    // Connect to SQLite (WAL mode recommended for better concurrency)
    db, _ := sql.Open("sqlite3", "myapp.db?_journal_mode=WAL&_foreign_keys=on")
    defer db.Close()

    // Create Queen instance
    driver := sqlite.New(db)
    q := queen.New(driver)
    defer q.Close()

    // Register migrations
    q.MustAdd(queen.M{
        Version: "001",
        Name:    "create_users_table",
        UpSQL: `
            CREATE TABLE users (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                email TEXT NOT NULL UNIQUE,
                created_at TEXT DEFAULT (datetime('now'))
            )
        `,
        DownSQL: `DROP TABLE users`,
    })

    q.MustAdd(queen.M{
        Version: "002",
        Name:    "add_users_name",
        UpSQL:   `ALTER TABLE users ADD COLUMN name TEXT`,
        DownSQL: `ALTER TABLE users DROP COLUMN name`,
    })

    // Apply all pending migrations
    ctx := context.Background()
    if err := q.Up(ctx); err != nil {
        log.Fatal(err)
    }

    log.Println("Migrations applied successfully!")
}

YandexDB (YDB)

package main

import (
    "context"
    "database/sql"
    "log"

    _ "github.com/ydb-platform/ydb-go-sdk/v3"

    "github.com/honeynil/queen"
    "github.com/honeynil/queen/drivers/ydb"
)

func main() {
    // Connect to YDB with special parameters for database/sql compatibility:
    // - go_query_mode=scripting: enables DDL+DML support
    // - go_fake_tx=scripting: transaction emulation
    // - go_query_bind=declare,numeric: auto-converts $1,$2 to YDB named params
    dsn := "grpc://localhost:2136/local?go_query_mode=scripting&go_fake_tx=scripting&go_query_bind=declare,numeric"
    db, _ := sql.Open("ydb", dsn)
    defer db.Close()

    // Create Queen instance
    driver, _ := ydb.New(db)
    q := queen.New(driver)
    defer q.Close()

    // Register migrations
    q.MustAdd(queen.M{
        Version: "001",
        Name:    "create_users_table",
        UpSQL: `
            CREATE TABLE users (
                id         Utf8,
                email      Utf8 NOT NULL,
                name       Utf8,
                created_at Timestamp,
                PRIMARY KEY (id)
            )
        `,
        DownSQL: `DROP TABLE users`,
    })

    q.MustAdd(queen.M{
        Version: "002",
        Name:    "add_users_bio",
        UpSQL:   `ALTER TABLE users ADD COLUMN bio Utf8`,
        DownSQL: `ALTER TABLE users DROP COLUMN bio`,
    })

    // Apply all pending migrations
    ctx := context.Background()
    if err := q.Up(ctx); err != nil {
        log.Fatal(err)
    }

    log.Println("Migrations applied successfully!")
}

MS SQL Server

package main

import (
    "context"
    "database/sql"
    "log"

    _ "github.com/microsoft/go-mssqldb"

    "github.com/honeynil/queen"
    "github.com/honeynil/queen/drivers/mssql"
)

func main() {
    // Connect to SQL Server
    db, _ := sql.Open("sqlserver", "sqlserver://user:password@localhost:1433?database=myapp")
    defer db.Close()

    // Create Queen instance
    driver := mssql.New(db)
    q := queen.New(driver)
    defer q.Close()

    // Register migrations
    q.MustAdd(queen.M{
        Version: "001",
        Name:    "create_users_table",
        UpSQL: `
            CREATE TABLE users (
                id INT IDENTITY(1,1) PRIMARY KEY,
                email NVARCHAR(255) NOT NULL UNIQUE,
                created_at DATETIME2 DEFAULT GETUTCDATE()
            )
        `,
        DownSQL: `DROP TABLE users`,
    })

    q.MustAdd(queen.M{
        Version: "002",
        Name:    "add_users_name",
        UpSQL:   `ALTER TABLE users ADD name NVARCHAR(255)`,
        DownSQL: `ALTER TABLE users DROP COLUMN name`,
    })

    // Apply all pending migrations
    ctx := context.Background()
    if err := q.Up(ctx); err != nil {
        log.Fatal(err)
    }

    log.Println("Migrations applied successfully!")
}

Usage Examples

Modular Migrations (Registry Pattern)

For large projects, organize migrations by domain:

// users/migrations.go
package users

func Register(q *queen.Queen) {
    q.MustAdd(queen.M{
        Version: "users_001",
        Name:    "create_users",
        UpSQL:   `CREATE TABLE users (...)`,
        DownSQL: `DROP TABLE users`,
    })
}

// posts/migrations.go
package posts

func Register(q *queen.Queen) {
    q.MustAdd(queen.M{
        Version: "posts_001",
        Name:    "create_posts",
        UpSQL:   `CREATE TABLE posts (...)`,
        DownSQL: `DROP TABLE posts`,
    })
}

// main.go
func main() {
    q := queen.New(driver)

    users.Register(q)
    posts.Register(q)

    q.Up(ctx)
}

Go Function Migrations

For complex migrations that need programmatic logic:

q.MustAdd(queen.M{
    Version:        "003",
    Name:           "normalize_emails",
    ManualChecksum: "v1", // Important: track function changes!
    UpFunc: func(ctx context.Context, tx *sql.Tx) error {
        // Fetch users
        rows, err := tx.QueryContext(ctx, "SELECT id, email FROM users")
        if err != nil {
            return err
        }
        defer rows.Close()

        // Process each user
        for rows.Next() {
            var id int
            var email string
            if err := rows.Scan(&id, &email); err != nil {
                return err
            }

            // Normalize email
            normalized := strings.ToLower(strings.TrimSpace(email))

            _, err = tx.ExecContext(ctx,
                "UPDATE users SET email = $1 WHERE id = $2",
                normalized, id)
            if err != nil {
                return err
            }
        }

        return rows.Err()
    },
    DownFunc: func(ctx context.Context, tx *sql.Tx) error {
        // Rollback logic (if possible)
        return nil
    },
})

Testing Migrations

Queen makes it easy to test your migrations:

func TestMigrations(t *testing.T) {
    // Setup test database
    db := setupTestDB(t)
    driver := postgres.New(db)

    // Create test instance (auto-cleanup on test end)
    q := queen.NewTest(t, driver)

    // Register migrations
    q.MustAdd(queen.M{
        Version: "001",
        Name:    "create_users",
        UpSQL:   `CREATE TABLE users (id INT)`,
        DownSQL: `DROP TABLE users`,
    })

    // Test both up and down migrations
    q.TestUpDown()
}

Migration Operations

// Apply all pending migrations
q.Up(ctx)

// Apply next N migrations
q.UpSteps(ctx, 3)

// Rollback last migration
q.Down(ctx, 1)

// Rollback last N migrations
q.Down(ctx, 3)

// Rollback all migrations
q.Reset(ctx)

// Get migration status
statuses, _ := q.Status(ctx)
for _, s := range statuses {
    fmt.Printf("%s: %s (%s)\n", s.Version, s.Name, s.Status)
}

// Validate migrations
if err := q.Validate(ctx); err != nil {
    log.Fatal(err)
}

Philosophy

Queen follows the principle: migrations are code, not files. This approach enables:

• Type safety and IDE support
• Easier testing and refactoring
• No file organization overhead
• Full programmatic control when needed

Queen is designed for developers who want clean, testable migrations without the ceremony.

Configuration

config := &queen.Config{
    TableName:   "custom_migrations", // Default: "queen_migrations"
    LockTimeout: 30 * time.Minute,    // Default: 30 minutes
    SkipLock:    false,               // Default: false (recommended)
}

q := queen.NewWithConfig(driver, config)

Naming Pattern Enforcement

Queen can enforce naming conventions to prevent migration versioning mistakes. This is especially useful for teams to maintain consistency.

Sequential Padded (Recommended)

config := &queen.Config{
    Naming: &queen.NamingConfig{
        Pattern: queen.NamingPatternSequentialPadded,
        Padding: 3,       // Generates: 001, 002, 003, ...
        Enforce: true,    // Reject non-conforming versions
    },
}

q := queen.NewWithConfig(driver, config)

q.MustAdd(queen.M{Version: "001", Name: "create_users"})  // ✅ OK
q.MustAdd(queen.M{Version: "002", Name: "add_email"})     // ✅ OK
q.MustAdd(queen.M{Version: "1", Name: "invalid"})         // ❌ Error: wrong format

Sequential

config := &queen.Config{
    Naming: &queen.NamingConfig{
        Pattern: queen.NamingPatternSequential, // Generates: 1, 2, 3, ...
        Enforce: true,
    },
}

Semantic Versioning

config := &queen.Config{
    Naming: &queen.NamingConfig{
        Pattern: queen.NamingPatternSemver, // Enforces: 1.0.0, 1.1.0, 2.0.0
        Enforce: true,
    },
}

q.MustAdd(queen.M{Version: "1.0.0", Name: "initial"})  // ✅ OK
q.MustAdd(queen.M{Version: "1.1.0", Name: "feature"})  // ✅ OK

CLI Integration

When using the CLI with .queen.yaml, the naming pattern is automatically applied:

# .queen.yaml
naming:
  pattern: sequential-padded
  padding: 3
  enforce: true

development:
  driver: postgres
  dsn: postgres://localhost/dev

# CLI automatically uses the pattern
migrate create add_users
# Creates: migrations/001_add_users.go with Version: "001"

migrate create add_posts
# Creates: migrations/002_add_posts.go with Version: "002"

Logging

Queen supports structured logging compatible with Go's slog package. By default, no logging is performed (noop logger).

Basic Logging with slog

import (
    "log/slog"
    "os"
    "github.com/honeynil/queen"
    "github.com/honeynil/queen/drivers/postgres"
)

// Use default slog logger
logger := slog.Default()
q := queen.New(driver, queen.WithLogger(logger))

// Or create custom logger
handler := slog.NewJSONHandler(os.Stdout, &slog.HandlerOptions{
    Level: slog.LevelInfo,
})
logger = slog.New(handler)
q := queen.New(driver, queen.WithLogger(logger))

Custom Logger

Implement the queen.Logger interface to use your own logging library:

type Logger interface {
    InfoContext(ctx context.Context, msg string, args ...any)
    WarnContext(ctx context.Context, msg string, args ...any)
    ErrorContext(ctx context.Context, msg string, args ...any)
}

// Example with zerolog
type ZerologAdapter struct {
    logger zerolog.Logger
}

func (z *ZerologAdapter) InfoContext(ctx context.Context, msg string, args ...any) {
    z.logger.Info().Fields(args).Msg(msg)
}

func (z *ZerologAdapter) WarnContext(ctx context.Context, msg string, args ...any) {
    z.logger.Warn().Fields(args).Msg(msg)
}

func (z *ZerologAdapter) ErrorContext(ctx context.Context, msg string, args ...any) {
    z.logger.Error().Fields(args).Msg(msg)
}

logger := &ZerologAdapter{logger: zerolog.New(os.Stdout)}
q := queen.New(driver, queen.WithLogger(logger))

Logged Events

Queen logs the following events:

• Migration lifecycle: start, completion, errors, and duration
• Lock operations: acquisition and release
• Warnings: checksum mismatches, naming pattern violations
• Validation errors: migration validation failures

Example log output (JSON format):

{"time":"2024-01-31T10:00:00Z","level":"INFO","msg":"lock acquired","table":"queen_migrations"}
{"time":"2024-01-31T10:00:01Z","level":"INFO","msg":"migration started","version":"001","name":"create_users","direction":"up"}
{"time":"2024-01-31T10:00:02Z","level":"INFO","msg":"migration completed","version":"001","name":"create_users","direction":"up","duration_ms":1234}
{"time":"2024-01-31T10:00:02Z","level":"INFO","msg":"lock released","table":"queen_migrations"}

Transaction Isolation Levels

Control transaction isolation levels for migrations to prevent race conditions and optimize performance.

Global Configuration

Set a default isolation level for all migrations:

import "database/sql"

config := &queen.Config{
    IsolationLevel: sql.LevelSerializable, // Strongest isolation
}
q := queen.NewWithConfig(driver, config)

Per-Migration Configuration

Override the global setting for specific migrations:

q.MustAdd(queen.M{
    Version:        "003",
    Name:           "critical_update",
    IsolationLevel: sql.LevelSerializable, // Override for this migration
    UpSQL:          "UPDATE accounts SET balance = balance - 100 WHERE id = 1",
    DownSQL:        "UPDATE accounts SET balance = balance + 100 WHERE id = 1",
})

Isolation Levels

Use Cases

High Concurrency (READ COMMITTED):

// Fast bulk updates - allows other transactions to proceed
q.MustAdd(queen.M{
    Version:        "004",
    Name:           "bulk_data_migration",
    IsolationLevel: sql.LevelReadCommitted,
    UpSQL:          "UPDATE users SET migrated = true WHERE created_at < '2024-01-01'",
})

Critical Operations (SERIALIZABLE):

// Prevents race conditions in financial operations
q.MustAdd(queen.M{
    Version:        "005",
    Name:           "transfer_funds",
    IsolationLevel: sql.LevelSerializable,
    UpFunc: func(ctx context.Context, tx *sql.Tx) error {
        // Complex multi-table update requiring full isolation
        return nil
    },
})

API Documentation

See pkg.go.dev for complete API documentation.

Key Types

Migration / M

type Migration struct {
    Version        string        // Unique version identifier
    Name           string        // Human-readable name
    UpSQL          string        // SQL to apply migration
    DownSQL        string        // SQL to rollback migration
    UpFunc         MigrationFunc // Go function to apply
    DownFunc       MigrationFunc // Go function to rollback
    ManualChecksum string        // Manual checksum for Go functions
}

type M = Migration // Convenient alias

Queen

type Queen struct { /* ... */ }

func New(driver Driver) *Queen
func NewWithConfig(driver Driver, config *Config) *Queen
func NewTest(t *testing.T, driver Driver) *TestHelper

func (q *Queen) Add(m M) error
func (q *Queen) MustAdd(m M)
func (q *Queen) Up(ctx context.Context) error
func (q *Queen) UpSteps(ctx context.Context, n int) error
func (q *Queen) Down(ctx context.Context, n int) error
func (q *Queen) Reset(ctx context.Context) error
func (q *Queen) Status(ctx context.Context) ([]MigrationStatus, error)
func (q *Queen) Validate(ctx context.Context) error
func (q *Queen) Close() error

Supported Databases

See the drivers directory for database-specific documentation and examples.

CLI

Queen includes a command-line interface for managing migrations. Create your own binary that imports your migrations:

// cmd/migrate/main.go
package main

import (
    _ "github.com/jackc/pgx/v5/stdlib"

    "github.com/honeynil/queen/cli"
    "myapp/migrations"
)

func main() {
    cli.Run(migrations.Register)
}

Then use it:

# Build
go build -o migrate cmd/migrate/main.go

# Configure via environment variables
export QUEEN_DRIVER=postgres
export QUEEN_DSN="postgres://localhost/myapp?sslmode=disable"

# Or create .queen.yaml
cat > .queen.yaml <<EOF
naming:
  pattern: sequential-padded
  padding: 3
  enforce: true

development:
  driver: postgres
  dsn: postgres://localhost/myapp_dev?sslmode=disable

production:
  driver: postgres
  dsn: postgres://localhost/myapp?sslmode=disable
  require_confirmation: true
  require_explicit_unlock: true
EOF

# Use
./migrate up                       # Apply all pending migrations
./migrate down                     # Rollback last migration
./migrate status                   # Show migration status
./migrate create add_foo           # Create new migration (auto-numbered: 001, 002, ...)
./migrate validate                 # Validate checksums
./migrate --env production status  # Use production environment from config

License

MIT License - see LICENSE for details.

Author

Created by honeynil