Documentation
¶
Overview ¶
Package sqlite provides a SQLite driver for Queen migrations.
This driver supports SQLite 3.8+ and is ideal for embedded databases, development, testing, and single-server applications.
Basic Usage ¶
import (
"database/sql"
_ "github.com/mattn/go-sqlite3"
"github.com/honeynil/queen"
"github.com/honeynil/queen/drivers/sqlite"
)
db, _ := sql.Open("sqlite3", "myapp.db")
driver := sqlite.New(db)
q := queen.New(driver)
Database File ¶
SQLite stores the database in a single file. Common patterns:
- Persistent: "myapp.db" or "/path/to/database.db"
- In-memory: ":memory:" (lost when connection closes)
- Temporary: "" (empty string, deleted when closed)
For production use, always use a persistent file path.
Locking Mechanism ¶
Unlike PostgreSQL and MySQL, SQLite is a file-based database with different locking characteristics:
- SQLite uses database-level locks, not connection-level locks
- This driver uses BEGIN EXCLUSIVE transaction for migration locking
- The lock is automatically released when the transaction commits/rolls back
- Only one writer can access the database at a time (by design)
WAL Mode (Recommended) ¶
For better concurrent read/write performance, enable WAL (Write-Ahead Logging):
db, _ := sql.Open("sqlite3", "myapp.db?_journal_mode=WAL")
WAL mode allows readers to access the database while a migration is running, though only one migration can run at a time.
Compatibility ¶
- SQLite 3.8+ (uses WITHOUT ROWID optimization where available)
- Works on all platforms (Linux, macOS, Windows)
- Single file, no server required
Example ¶
Example demonstrates basic usage of the SQLite driver.
package main
import (
"context"
"database/sql"
"fmt"
"log"
"github.com/honeynil/queen"
"github.com/honeynil/queen/drivers/sqlite"
_ "github.com/mattn/go-sqlite3"
)
func main() {
// Connect to SQLite database file
db, err := sql.Open("sqlite3", "myapp.db")
if err != nil {
log.Fatal(err)
}
defer db.Close()
// Create SQLite driver
driver := sqlite.New(db)
// Create Queen instance
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,
name TEXT,
created_at TEXT DEFAULT (datetime('now'))
)
`,
DownSQL: `DROP TABLE users`,
})
q.MustAdd(queen.M{
Version: "002",
Name: "add_users_bio",
UpSQL: `ALTER TABLE users ADD COLUMN bio TEXT`,
DownSQL: `ALTER TABLE users DROP COLUMN bio`,
})
// Apply all pending migrations
ctx := context.Background()
if err := q.Up(ctx); err != nil {
log.Fatal(err)
}
fmt.Println("Migrations applied successfully!")
}
Example (CustomTableName) ¶
Example_customTableName demonstrates using a custom table name for migrations.
package main
import (
"database/sql"
"github.com/honeynil/queen"
"github.com/honeynil/queen/drivers/sqlite"
_ "github.com/mattn/go-sqlite3"
)
func main() {
db, _ := sql.Open("sqlite3", "myapp.db")
defer db.Close()
// Use custom table name
driver := sqlite.NewWithTableName(db, "my_custom_migrations")
q := queen.New(driver)
defer q.Close()
// The migrations will be tracked in "my_custom_migrations" table
// instead of the default "queen_migrations"
}
Example (ForeignKeys) ¶
Example_foreignKeys demonstrates handling foreign keys properly.
package main
import (
"context"
"database/sql"
"log"
"github.com/honeynil/queen"
"github.com/honeynil/queen/drivers/sqlite"
_ "github.com/mattn/go-sqlite3"
)
func main() {
// Enable foreign keys in connection string
db, _ := sql.Open("sqlite3", "myapp.db?_foreign_keys=on")
defer db.Close()
driver := sqlite.New(db)
q := queen.New(driver)
defer q.Close()
// First migration: create parent table
q.MustAdd(queen.M{
Version: "001",
Name: "create_users",
UpSQL: `
CREATE TABLE users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
email TEXT NOT NULL UNIQUE
)
`,
DownSQL: `DROP TABLE users`,
})
// Second migration: create child table with foreign key
q.MustAdd(queen.M{
Version: "002",
Name: "create_posts",
UpSQL: `
CREATE TABLE posts (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER NOT NULL,
title TEXT,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
)
`,
// Important: child table must be dropped first
DownSQL: `DROP TABLE posts`,
})
ctx := context.Background()
if err := q.Up(ctx); err != nil {
log.Fatal(err)
}
// When rolling back, Queen will execute down migrations in reverse order:
// 1. DROP TABLE posts (child)
// 2. DROP TABLE users (parent)
// This ensures foreign key constraints are satisfied
}
Example (FullConnectionString) ¶
Example_fullConnectionString demonstrates a production-ready connection string.
package main
import (
"database/sql"
"github.com/honeynil/queen"
"github.com/honeynil/queen/drivers/sqlite"
_ "github.com/mattn/go-sqlite3"
)
func main() {
// Recommended connection string for production
dsn := "myapp.db?_journal_mode=WAL&_busy_timeout=5000&_foreign_keys=on&_synchronous=NORMAL"
db, _ := sql.Open("sqlite3", dsn)
defer db.Close()
driver := sqlite.New(db)
q := queen.New(driver)
defer q.Close()
// This configuration provides:
// - WAL mode: better concurrency
// - Busy timeout: 5 seconds to wait for locks
// - Foreign keys: enabled
// - Synchronous: balanced safety/performance
}
Example (GoFunctionMigration) ¶
Example_goFunctionMigration demonstrates using Go functions for complex migrations.
package main
import (
"context"
"database/sql"
"log"
"github.com/honeynil/queen"
"github.com/honeynil/queen/drivers/sqlite"
_ "github.com/mattn/go-sqlite3"
)
func main() {
db, _ := sql.Open("sqlite3", "myapp.db")
defer db.Close()
driver := sqlite.New(db)
q := queen.New(driver)
defer q.Close()
// Migration using Go function for complex 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 all users
rows, err := tx.QueryContext(ctx, "SELECT id, email FROM users")
if err != nil {
return err
}
defer rows.Close()
// Normalize each email
for rows.Next() {
var id int
var email string
if err := rows.Scan(&id, &email); err != nil {
return err
}
// Convert to lowercase
normalized := normalizeEmail(email)
// Update the email
_, err = tx.ExecContext(ctx,
"UPDATE users SET email = ? WHERE id = ?",
normalized, id)
if err != nil {
return err
}
}
return rows.Err()
},
DownFunc: func(ctx context.Context, tx *sql.Tx) error {
// Rollback is not possible for this migration
return nil
},
})
ctx := context.Background()
if err := q.Up(ctx); err != nil {
log.Fatal(err)
}
}
// Helper function for email normalization
func normalizeEmail(email string) string {
return email
}
Example (InMemory) ¶
Example_inMemory demonstrates using an in-memory database for testing.
package main
import (
"context"
"database/sql"
"fmt"
"log"
"github.com/honeynil/queen"
"github.com/honeynil/queen/drivers/sqlite"
_ "github.com/mattn/go-sqlite3"
)
func main() {
// Use in-memory database (perfect for testing)
db, _ := sql.Open("sqlite3", ":memory:")
defer db.Close()
driver := sqlite.New(db)
q := queen.New(driver)
defer q.Close()
// Migrations work exactly the same with in-memory databases
q.MustAdd(queen.M{
Version: "001",
Name: "create_users",
UpSQL: `CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)`,
DownSQL: `DROP TABLE users`,
})
ctx := context.Background()
if err := q.Up(ctx); err != nil {
log.Fatal(err)
}
fmt.Println("In-memory migrations applied!")
}
Example (Indexes) ¶
Example_indexes demonstrates creating indexes for better query performance.
package main
import (
"context"
"database/sql"
"log"
"github.com/honeynil/queen"
"github.com/honeynil/queen/drivers/sqlite"
_ "github.com/mattn/go-sqlite3"
)
func main() {
db, _ := sql.Open("sqlite3", "myapp.db")
defer db.Close()
driver := sqlite.New(db)
q := queen.New(driver)
defer q.Close()
// Create table
q.MustAdd(queen.M{
Version: "001",
Name: "create_users",
UpSQL: `
CREATE TABLE users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
email TEXT NOT NULL,
name TEXT,
created_at TEXT DEFAULT (datetime('now'))
)
`,
DownSQL: `DROP TABLE users`,
})
// Add indexes in a separate migration
q.MustAdd(queen.M{
Version: "002",
Name: "add_user_indexes",
UpSQL: `
CREATE UNIQUE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_created_at ON users(created_at);
`,
DownSQL: `
DROP INDEX IF EXISTS idx_users_email;
DROP INDEX IF EXISTS idx_users_created_at;
`,
})
ctx := context.Background()
if err := q.Up(ctx); err != nil {
log.Fatal(err)
}
}
Example (Status) ¶
Example_status demonstrates checking migration status.
package main
import (
"context"
"database/sql"
"fmt"
"log"
"github.com/honeynil/queen"
"github.com/honeynil/queen/drivers/sqlite"
_ "github.com/mattn/go-sqlite3"
)
func main() {
db, _ := sql.Open("sqlite3", ":memory:")
defer db.Close()
driver := sqlite.New(db)
q := queen.New(driver)
defer q.Close()
// Register migrations
q.MustAdd(queen.M{
Version: "001",
Name: "create_users",
UpSQL: `CREATE TABLE users (id INTEGER PRIMARY KEY)`,
DownSQL: `DROP TABLE users`,
})
q.MustAdd(queen.M{
Version: "002",
Name: "create_posts",
UpSQL: `CREATE TABLE posts (id INTEGER PRIMARY KEY)`,
DownSQL: `DROP TABLE posts`,
})
ctx := context.Background()
// Apply first migration only
if err := q.UpSteps(ctx, 1); err != nil {
log.Fatal(err)
}
// Check status
statuses, err := q.Status(ctx)
if err != nil {
log.Fatal(err)
}
for _, s := range statuses {
fmt.Printf("%s: %s (%s)\n", s.Version, s.Name, s.Status)
}
}
Output: 001: create_users (applied) 002: create_posts (pending)
Example (Testing) ¶
Example_testing demonstrates best practices for testing migrations.
package main
import (
"context"
"database/sql"
"fmt"
"log"
"github.com/honeynil/queen"
"github.com/honeynil/queen/drivers/sqlite"
_ "github.com/mattn/go-sqlite3"
)
func main() {
// Use in-memory database for fast, isolated tests
db, err := sql.Open("sqlite3", ":memory:")
if err != nil {
log.Fatal(err)
}
defer db.Close()
driver := sqlite.New(db)
// Note: In actual tests, use queen.NewTest(t, driver)
q := queen.New(driver)
defer q.Close()
// Register migrations
q.MustAdd(queen.M{
Version: "001",
Name: "create_users",
UpSQL: `CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)`,
DownSQL: `DROP TABLE users`,
})
ctx := context.Background()
// Test up migration
if err := q.Up(ctx); err != nil {
log.Fatal(err)
}
// Verify table exists
var tableName string
err = db.QueryRow("SELECT name FROM sqlite_master WHERE type='table' AND name='users'").Scan(&tableName)
if err != nil {
log.Fatal(err)
}
fmt.Println("Table created:", tableName)
// Test down migration
if err := q.Reset(ctx); err != nil {
log.Fatal(err)
}
// Verify table is gone
err = db.QueryRow("SELECT name FROM sqlite_master WHERE type='table' AND name='users'").Scan(&tableName)
if err != sql.ErrNoRows {
log.Fatal("table should be gone")
}
fmt.Println("Table dropped successfully")
}
Output: Table created: users Table dropped successfully
Example (WalMode) ¶
Example_walMode demonstrates using WAL mode for better concurrency.
package main
import (
"database/sql"
"github.com/honeynil/queen"
"github.com/honeynil/queen/drivers/sqlite"
_ "github.com/mattn/go-sqlite3"
)
func main() {
// Enable WAL mode for better concurrent read/write performance
db, _ := sql.Open("sqlite3", "myapp.db?_journal_mode=WAL")
defer db.Close()
driver := sqlite.New(db)
q := queen.New(driver)
defer q.Close()
// Your migrations here
}
Example (WithConfig) ¶
Example_withConfig demonstrates using custom configuration.
package main
import (
"database/sql"
"github.com/honeynil/queen"
"github.com/honeynil/queen/drivers/sqlite"
_ "github.com/mattn/go-sqlite3"
)
func main() {
db, _ := sql.Open("sqlite3", "myapp.db")
defer db.Close()
driver := sqlite.New(db)
// Create Queen with custom config
config := &queen.Config{
TableName: "custom_migrations",
LockTimeout: 5 * 60, // 5 minutes in seconds
}
q := queen.NewWithConfig(driver, config)
defer q.Close()
// Your migrations here
}
Index ¶
- type Driver
- func (d *Driver) Close() error
- func (d *Driver) Exec(ctx context.Context, fn func(*sql.Tx) error) error
- func (d *Driver) GetApplied(ctx context.Context) ([]queen.Applied, error)
- func (d *Driver) Init(ctx context.Context) error
- func (d *Driver) Lock(ctx context.Context, timeout time.Duration) error
- func (d *Driver) Record(ctx context.Context, m *queen.Migration) error
- func (d *Driver) Remove(ctx context.Context, version string) error
- func (d *Driver) Unlock(ctx context.Context) error
Examples ¶
Constants ¶
This section is empty.
Variables ¶
This section is empty.
Functions ¶
This section is empty.
Types ¶
type Driver ¶
type Driver struct {
// contains filtered or unexported fields
}
Driver implements the queen.Driver interface for SQLite.
The driver is thread-safe for concurrent reads, but SQLite's database-level locking means only one write operation (migration) can occur at a time. This is handled automatically by the BEGIN EXCLUSIVE transaction.
func New ¶
New creates a new SQLite driver.
The database connection should already be open and configured. The default migrations table name is "queen_migrations".
Example:
db, err := sql.Open("sqlite3", "myapp.db")
if err != nil {
log.Fatal(err)
}
driver := sqlite.New(db)
For better performance with concurrent reads, use WAL mode:
db, err := sql.Open("sqlite3", "myapp.db?_journal_mode=WAL")
func NewWithTableName ¶
NewWithTableName creates a new SQLite driver with a custom table name.
Use this when you need to manage multiple independent sets of migrations in the same database file, or when you want to customize the table name for organizational purposes.
Example:
driver := sqlite.NewWithTableName(db, "my_migrations")
func (*Driver) Close ¶
Close closes the database connection.
If you're using a file-based database (not :memory:), the database file persists after closing. For in-memory databases, all data is lost.
func (*Driver) Exec ¶
Exec executes a function within a transaction.
If the function returns an error, the transaction is rolled back. Otherwise, the transaction is committed.
This provides ACID guarantees for migration execution.
Note: SQLite supports nested transactions using SAVEPOINT, but this driver uses simple transactions for compatibility and simplicity.
func (*Driver) GetApplied ¶
GetApplied returns all applied migrations sorted by applied_at in ascending order.
This is used by Queen to determine which migrations have already been applied and which are pending.
Note: SQLite stores timestamps as TEXT in ISO8601 format. We parse them back to time.Time for consistency with other drivers.
func (*Driver) Init ¶
Init creates the migrations tracking table if it doesn't exist.
The table schema:
- version: TEXT PRIMARY KEY - unique migration version
- name: TEXT NOT NULL - human-readable migration name
- applied_at: TEXT - ISO8601 timestamp when migration was applied
- checksum: TEXT - hash of migration content for validation
This method is idempotent and safe to call multiple times.
Note: SQLite doesn't have a native TIMESTAMP type. We use TEXT with ISO8601 format (YYYY-MM-DD HH:MM:SS) which sorts correctly and is human-readable.
func (*Driver) Lock ¶
Lock acquires an exclusive database lock to prevent concurrent migrations.
SQLite uses database-level locking. This driver uses BEGIN EXCLUSIVE to acquire an exclusive lock on the entire database. This prevents any other connections from writing to the database until the lock is released.
The lock is held in a separate transaction (d.lockTx) and is released when Unlock() is called.
Important: Unlike PostgreSQL and MySQL where locks are separate from migration transactions, SQLite's locking transaction must remain open for the entire migration process.
If the lock cannot be acquired within the timeout, returns queen.ErrLockTimeout.
func (*Driver) Record ¶
Record marks a migration as applied in the database.
This should be called after successfully executing a migration's up function. The checksum is automatically computed from the migration content.
The timestamp is automatically set by SQLite to the current time.
func (*Driver) Remove ¶
Remove removes a migration record from the database.
This should be called after successfully rolling back a migration's down function.
Source Files