Drivers¶

Queen supports multiple databases through a driver interface. All drivers implement the same API.

Supported Databases¶

Database	Package	Locking	Transactional DDL
PostgreSQL	`drivers/postgres`	Advisory locks	Yes
MySQL	`drivers/mysql`	GET_LOCK	Partial (InnoDB)
SQLite	`drivers/sqlite`	Exclusive locks	Yes
ClickHouse	`drivers/clickhouse`	Table-based with TTL	No
CockroachDB	`drivers/cockroachdb`	Table-based distributed	Yes
MS SQL Server	`drivers/mssql`	sp_getapplock	Partial

All drivers also support NewWithTableName(db, tableName) for custom migration table names.

Note

The CLI tool supports only PostgreSQL, MySQL, SQLite, and ClickHouse. CockroachDB and MS SQL Server are available as Go driver packages but cannot be used with the queen CLI directly.

PostgreSQL¶

go get github.com/jackc/pgx/v5/stdlib

Driver packages (drivers/postgres, drivers/mysql, etc.) are included with the main github.com/honeynil/queen module — no separate go get needed.

import (
    "database/sql"
    "github.com/honeynil/queen/drivers/postgres"
    _ "github.com/jackc/pgx/v5/stdlib"
)

db, _ := sql.Open("pgx", "postgres://localhost/mydb?sslmode=disable")
driver := postgres.New(db)
q := queen.New(driver)

Custom table name:

driver := postgres.NewWithTableName(db, "custom_migrations")

Connection String¶

postgres://user:password@host:port/database?sslmode=disable

Option	Values
`sslmode`	`disable`, `require`, `verify-full`
`connect_timeout`	seconds
`application_name`	string
`search_path`	`public,other`

Features¶

Transactional DDL -- All DDL is transactional. Failed index creation rolls back table creation.
Advisory locks -- pg_advisory_lock prevents concurrent migrations.
Schema support -- Custom schemas via search_path.
Concurrent indexes -- CREATE INDEX CONCURRENTLY supported (runs outside transaction).

PostgreSQL-Specific Patterns¶

// JSONB column
UpSQL: `ALTER TABLE users ADD COLUMN metadata JSONB DEFAULT '{}'::jsonb`

// Trigger
UpSQL: `
    CREATE OR REPLACE FUNCTION update_updated_at()
    RETURNS TRIGGER AS $$
    BEGIN NEW.updated_at = NOW(); RETURN NEW; END;
    $$ LANGUAGE plpgsql;

    CREATE TRIGGER users_updated_at
    BEFORE UPDATE ON users
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();
`

// Partitioning
UpSQL: `
    CREATE TABLE events (
        id BIGSERIAL, created_at TIMESTAMP NOT NULL, data JSONB
    ) PARTITION BY RANGE (created_at);
`

// Concurrent index (non-transactional)
UpSQL: `CREATE INDEX CONCURRENTLY idx_users_email ON users(email)`

MySQL¶

go get github.com/go-sql-driver/mysql

import (
    "database/sql"
    "github.com/honeynil/queen/drivers/mysql"
    _ "github.com/go-sql-driver/mysql"
)

db, _ := sql.Open("mysql", "user:password@tcp(localhost:3306)/mydb?parseTime=true")
driver := mysql.New(db)
q := queen.New(driver)

Connection String¶

user:password@tcp(host:port)/database?parseTime=true&charset=utf8mb4

Option	Values
`parseTime`	`true` (recommended)
`charset`	`utf8mb4` (recommended)
`collation`	`utf8mb4_unicode_ci`
`tls`	`true`, `false`, `skip-verify`

Features¶

GET_LOCK -- Named locks for migration coordination.
JSON columns -- MySQL 5.7+ JSON support.
InnoDB transactions -- Transactional DDL for some operations.

Limitations¶

Many DDL operations are not transactional. ALTER TABLE commits the current transaction. Queen handles each DDL statement separately.

MySQL-Specific Patterns¶

// Always use InnoDB
UpSQL: `CREATE TABLE users (...) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4`

// JSON column
UpSQL: `ALTER TABLE users ADD COLUMN metadata JSON`

// Online index creation (MySQL 8.0+)
UpSQL: `CREATE INDEX idx_posts_user_id ON posts(user_id) ALGORITHM=INPLACE, LOCK=NONE`

// Instant column add (MySQL 8.0+)
UpSQL: `ALTER TABLE users ADD COLUMN status VARCHAR(50) DEFAULT 'active', ALGORITHM=INSTANT`

SQLite¶

go get github.com/mattn/go-sqlite3

import (
    "database/sql"
    "github.com/honeynil/queen/drivers/sqlite"
    _ "github.com/mattn/go-sqlite3"
)

db, _ := sql.Open("sqlite3", "myapp.db")
db.SetMaxOpenConns(1)  // SQLite limitation
driver := sqlite.New(db)
q := queen.New(driver)

Connection String¶

myapp.db                                          # File database
:memory:                                          # In-memory (testing)
file:myapp.db?_foreign_keys=on&_journal_mode=WAL  # URI format

Option	Values
`_foreign_keys`	`on`, `off`
`_journal_mode`	`DELETE`, `WAL`, `MEMORY`
`_busy_timeout`	milliseconds
`mode`	`ro`, `rw`, `rwc`, `memory`

Features¶

Transactional DDL -- All DDL is transactional.
WAL mode -- Better concurrency with Write-Ahead Logging.
FTS5 -- Full-text search support.
JSON support -- SQLite 3.38+ JSON functions.

Recommended Setup¶

db.Exec("PRAGMA foreign_keys = ON")
db.Exec("PRAGMA journal_mode = WAL")
db.Exec("PRAGMA busy_timeout = 5000")
db.SetMaxOpenConns(1)

Limitations¶

Limited ALTER TABLE: no MODIFY COLUMN, no DROP CONSTRAINT. Workaround: create new table, copy data, drop old, rename new.

Only one writer at a time. Use WAL mode and busy timeout to mitigate.

ClickHouse¶

go get github.com/ClickHouse/clickhouse-go/v2

import (
    "database/sql"
    "github.com/honeynil/queen/drivers/clickhouse"
    _ "github.com/ClickHouse/clickhouse-go/v2"
)

db, _ := sql.Open("clickhouse", "clickhouse://localhost:9000/default")
driver, err := clickhouse.New(db)
if err != nil {
    log.Fatal(err)
}
q := queen.New(driver)

Warning

The ClickHouse constructor returns an error. Always check it.

Connection String¶

clickhouse://host:9000/database?compress=true&dial_timeout=10s

Features¶

MergeTree family -- MergeTree, ReplacingMergeTree, SummingMergeTree.
Distributed tables -- Cluster support with ON CLUSTER.
Materialized views -- Automatic aggregation.
TTL -- Automatic data expiration.
Table-based locking -- Distributed lock with TTL for migration coordination.

Limitations¶

No transactional DDL. No foreign keys. Eventual consistency in distributed setups. Each DDL statement commits immediately.

ClickHouse-Specific Patterns¶

// MergeTree with partitioning
UpSQL: `
    CREATE TABLE events (
        timestamp DateTime, user_id UInt64, data String
    ) ENGINE = MergeTree()
    ORDER BY (timestamp, user_id)
    PARTITION BY toYYYYMM(timestamp)
`

// TTL
UpSQL: `ALTER TABLE events MODIFY TTL timestamp + INTERVAL 90 DAY`

// Distributed table
UpSQL: `
    CREATE TABLE events ON CLUSTER '{cluster}' AS events_local
    ENGINE = Distributed('{cluster}', default, events_local, rand())
`

CockroachDB¶

CockroachDB uses the same pgx driver as PostgreSQL:

go get github.com/jackc/pgx/v5/stdlib

import (
    "database/sql"
    "github.com/honeynil/queen/drivers/cockroachdb"
    _ "github.com/jackc/pgx/v5/stdlib"
)

db, _ := sql.Open("pgx", "postgresql://root@localhost:26257/myapp?sslmode=disable")
driver, err := cockroachdb.New(db)
if err != nil {
    log.Fatal(err)
}
q := queen.New(driver)

Warning

The CockroachDB constructor returns an error. Always check it.

Features¶

Distributed SQL -- Automatic data distribution across nodes.
Transactional DDL -- Full transaction support.
Table-based locking -- Distributed lock mechanism (not advisory locks).
Multi-region -- Regional and global table locality.
JSONB and arrays -- PostgreSQL type compatibility.

Recommendations¶

Use UUID for primary keys (not SERIAL -- creates hotspots).
Use STRING instead of VARCHAR.
Set table locality for multi-region deployments.

UpSQL: `
    CREATE TABLE users (
        id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        email STRING UNIQUE NOT NULL
    )
`

MS SQL Server¶

go get github.com/microsoft/go-mssqldb

import (
    "database/sql"
    "github.com/honeynil/queen/drivers/mssql"
    _ "github.com/microsoft/go-mssqldb"
)

db, _ := sql.Open("sqlserver", "sqlserver://user:pass@localhost:1433?database=myapp")
driver := mssql.New(db)
q := queen.New(driver)

Connection String¶

sqlserver://user:password@host:1433?database=dbname&encrypt=true

Features¶

sp_getapplock -- Application-level locking.
Temporal tables -- System-versioned tables.
Columnstore indexes -- Analytics optimization.
Full-text search -- Built-in FTS.
Azure SQL -- Managed Identity support.

Recommendations¶

Use NVARCHAR for Unicode text.
Use DATETIME2 instead of DATETIME.
Use explicit schema names (dbo.users).

Mock Driver¶

For testing without a real database.

No extra dependencies needed — the mock driver is part of the Queen module.

import "github.com/honeynil/queen/drivers/mock"

driver := mock.New()
q := queen.New(driver)

The mock driver stores migrations in memory. Use it in unit tests to verify migration logic without database dependencies.

func TestMigrations(t *testing.T) {
    driver := mock.New()
    q := queen.NewTest(t, driver)

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

    q.TestUpDown()
}

Custom Driver¶

Implement the Driver interface:

type Driver interface {
    Init(ctx context.Context) error
    GetApplied(ctx context.Context) ([]Applied, error)
    Record(ctx context.Context, m *Migration, meta *MigrationMetadata) error
    Remove(ctx context.Context, version string) error
    Lock(ctx context.Context, timeout time.Duration) error
    Unlock(ctx context.Context) error
    Exec(ctx context.Context, isolationLevel sql.IsolationLevel, fn func(*sql.Tx) error) error
    Close() error
}

Init creates the migration table if it does not exist.
GetApplied returns all applied migration records.
Record inserts a migration record after successful execution.
Remove deletes a migration record after rollback.
Lock/Unlock provide migration concurrency control.
Exec runs a function within a transaction at the specified isolation level.

See API Reference for the full Applied and MigrationMetadata types.