Documentation
¶
Overview ¶
Package mssql provides a MS SQL Server driver for Queen migrations.
This driver supports SQL Server 2012+ and Azure SQL Database. It uses SQL Server's sp_getapplock/sp_releaseapplock stored procedures for distributed locking to prevent concurrent migrations.
Basic Usage ¶
import (
"database/sql"
_ "github.com/microsoft/go-mssqldb"
"github.com/honeynil/queen"
"github.com/honeynil/queen/drivers/mssql"
)
db, _ := sql.Open("sqlserver", "sqlserver://user:password@localhost:1433?database=dbname")
driver := mssql.New(db)
q := queen.New(driver)
Locking Mechanism ¶
SQL Server doesn't have advisory locks like PostgreSQL. Instead, this driver uses sp_getapplock() which creates an application lock that's automatically released when the session ends or sp_releaseapplock() is called.
The lock name is derived from the migrations table name to ensure different migration tables use different locks.
Compatibility ¶
- SQL Server 2012+
- Azure SQL Database
- Azure SQL Managed Instance
Example ¶
Example demonstrates basic usage of the MS SQL Server driver.
package main
import (
"context"
"database/sql"
"fmt"
"log"
"github.com/honeynil/queen"
"github.com/honeynil/queen/drivers/mssql"
_ "github.com/microsoft/go-mssqldb"
)
func main() {
// Connect to SQL Server
db, err := sql.Open("sqlserver", "sqlserver://user:password@localhost:1433?database=myapp")
if err != nil {
log.Fatal(err)
}
defer db.Close()
// Create MS SQL Server driver
driver := mssql.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 INT IDENTITY(1,1) PRIMARY KEY,
email NVARCHAR(255) NOT NULL UNIQUE,
name NVARCHAR(255),
created_at DATETIME2 DEFAULT GETUTCDATE()
)
`,
DownSQL: `DROP TABLE users`,
})
q.MustAdd(queen.M{
Version: "002",
Name: "add_users_bio",
UpSQL: `ALTER TABLE users ADD bio NVARCHAR(MAX)`,
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/mssql"
_ "github.com/microsoft/go-mssqldb"
)
func main() {
db, _ := sql.Open("sqlserver", "sqlserver://user:password@localhost:1433?database=myapp")
defer db.Close()
// Use custom table name
driver := mssql.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/mssql"
_ "github.com/microsoft/go-mssqldb"
)
func main() {
db, _ := sql.Open("sqlserver", "sqlserver://user:password@localhost:1433?database=myapp")
defer db.Close()
driver := mssql.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 INT IDENTITY(1,1) PRIMARY KEY,
email NVARCHAR(255) 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 INT IDENTITY(1,1) PRIMARY KEY,
user_id INT NOT NULL,
title NVARCHAR(255),
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
CREATE INDEX idx_posts_user_id ON posts(user_id)
`,
// 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 (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/mssql"
_ "github.com/microsoft/go-mssqldb"
)
func main() {
db, _ := sql.Open("sqlserver", "sqlserver://user:password@localhost:1433?database=myapp")
defer db.Close()
driver := mssql.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 (Status) ¶
Example_status demonstrates checking migration status.
Note: This example requires a running SQL Server instance. It will be skipped in CI if SQL Server is not available.
package main
import (
"context"
"database/sql"
"fmt"
"log"
"github.com/honeynil/queen"
"github.com/honeynil/queen/drivers/mssql"
_ "github.com/microsoft/go-mssqldb"
)
func main() {
db, err := sql.Open("sqlserver", "sqlserver://user:password@localhost:1433?database=myapp")
if err != nil {
fmt.Println("SQL Server not available")
return
}
defer db.Close()
// Check if SQL Server is actually available
if err := db.Ping(); err != nil {
fmt.Println("SQL Server not available")
return
}
driver := mssql.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 INT)`,
DownSQL: `DROP TABLE users`,
})
q.MustAdd(queen.M{
Version: "002",
Name: "create_posts",
UpSQL: `CREATE TABLE posts (id INT)`,
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)
}
// Example output (when SQL Server is available):
// 001: create_users (applied)
// 002: create_posts (pending)
}
Example (WithConfig) ¶
Example_withConfig demonstrates using custom configuration.
package main
import (
"database/sql"
"github.com/honeynil/queen"
"github.com/honeynil/queen/drivers/mssql"
_ "github.com/microsoft/go-mssqldb"
)
func main() {
db, _ := sql.Open("sqlserver", "sqlserver://user:password@localhost:1433?database=myapp")
defer db.Close()
driver := mssql.New(db)
// Create Queen with custom config
config := &queen.Config{
TableName: "custom_migrations",
LockTimeout: 10 * 60, // 10 minutes in seconds
}
q := queen.NewWithConfig(driver, config)
defer q.Close()
// Your migrations here
}
Index ¶
Examples ¶
Constants ¶
This section is empty.
Variables ¶
This section is empty.
Functions ¶
This section is empty.
Types ¶
type Driver ¶
Driver implements the queen.Driver interface for MS SQL Server.
The driver is thread-safe and can be used concurrently by multiple goroutines. However, Queen already handles locking to prevent concurrent migrations.
IMPORTANT: SQL Server sp_getapplock() is bound to a database session/connection. Therefore this driver keeps a dedicated *sql.Conn while the lock is held, to ensure that both sp_getapplock() and sp_releaseapplock() are executed on the same underlying connection from the pool.
func New ¶
New creates a new MS SQL Server driver.
The database connection should already be open and configured. The default migrations table name is "queen_migrations".
Example:
db, err := sql.Open("sqlserver", "sqlserver://user:pass@localhost:1433?database=mydb")
if err != nil {
log.Fatal(err)
}
driver := mssql.New(db)
func NewWithTableName ¶
NewWithTableName creates a new MS SQL Server driver with a custom table name.
Use this when you need to manage multiple independent sets of migrations in the same database, or when you want to customize the table name for organizational purposes.
Example:
driver := mssql.NewWithTableName(db, "my_custom_migrations")
func (*Driver) Init ¶
Init creates the migrations tracking table if it doesn't exist.
The table schema:
- version: NVARCHAR(255) PRIMARY KEY - unique migration version
- name: NVARCHAR(255) NOT NULL - human-readable migration name
- applied_at: DATETIME2 - when the migration was applied
- checksum: NVARCHAR(64) - hash of migration content for validation
This method is idempotent and safe to call multiple times.
func (*Driver) Lock ¶
Lock acquires an application lock to prevent concurrent migrations.
SQL Server uses sp_getapplock() which creates an application lock bound to the current database session. The lock is automatically released when the session ends or when Unlock() is called.
The lock name is based on the migrations table name, so different migration tables will use different locks.
Because database/sql uses a connection pool, this method explicitly acquires and holds a dedicated *sql.Conn to guarantee that both sp_getapplock() and sp_releaseapplock() are executed on the same underlying connection.
If the lock cannot be acquired within the timeout, returns queen.ErrLockTimeout.
func (*Driver) Unlock ¶
Unlock releases the migration lock.
This should be called in a defer statement after acquiring the lock. It's safe to call even if the lock wasn't acquired.
This method releases the application lock using the same database connection that was used in Lock(). After releasing the lock, the connection is closed and returned back to the pool.
Source Files