Documentation
¶
Overview ¶
Package mysql provides a MySQL driver for Queen migrations.
This driver supports MySQL 5.7+ and MariaDB 10.2+. It uses MySQL's GET_LOCK() function for distributed locking to prevent concurrent migrations.
Basic Usage ¶
import (
"database/sql"
_ "github.com/go-sql-driver/mysql"
"github.com/honeynil/queen"
"github.com/honeynil/queen/drivers/mysql"
)
db, _ := sql.Open("mysql", "user:password@tcp(localhost:3306)/dbname?parseTime=true")
driver := mysql.New(db)
q := queen.New(driver)
Connection String Requirements ¶
The connection string MUST include parseTime=true to properly handle TIMESTAMP columns:
"user:password@tcp(localhost:3306)/dbname?parseTime=true"
Locking Mechanism ¶
MySQL doesn't have advisory locks like PostgreSQL. Instead, this driver uses GET_LOCK() which creates a named lock that's automatically released when the connection closes or RELEASE_LOCK() is called.
The lock name is derived from the migrations table name to ensure different migration tables use different locks.
Compatibility ¶
- MySQL 5.7+ (uses GET_LOCK with timeout)
- MariaDB 10.2+ (uses GET_LOCK with timeout)
- Older versions may work but are not officially supported
Example ¶
Example demonstrates basic usage of the MySQL driver.
package main
import (
"context"
"database/sql"
"fmt"
"log"
_ "github.com/go-sql-driver/mysql"
"github.com/honeynil/queen"
"github.com/honeynil/queen/drivers/mysql"
)
func main() {
// Connect to MySQL
// IMPORTANT: parseTime=true is required for proper TIMESTAMP handling
db, err := sql.Open("mysql", "user:password@tcp(localhost:3306)/myapp?parseTime=true")
if err != nil {
log.Fatal(err)
}
defer db.Close()
// Create MySQL driver
driver := mysql.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 AUTO_INCREMENT PRIMARY KEY,
email VARCHAR(255) NOT NULL UNIQUE,
name VARCHAR(255),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
INDEX idx_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
`,
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/go-sql-driver/mysql"
"github.com/honeynil/queen"
"github.com/honeynil/queen/drivers/mysql"
)
func main() {
db, _ := sql.Open("mysql", "user:password@tcp(localhost:3306)/myapp?parseTime=true")
defer db.Close()
// Use custom table name
driver := mysql.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/go-sql-driver/mysql"
"github.com/honeynil/queen"
"github.com/honeynil/queen/drivers/mysql"
)
func main() {
db, _ := sql.Open("mysql", "user:password@tcp(localhost:3306)/myapp?parseTime=true")
defer db.Close()
driver := mysql.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 AUTO_INCREMENT PRIMARY KEY,
email VARCHAR(255) NOT NULL UNIQUE
) ENGINE=InnoDB
`,
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 AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
title VARCHAR(255),
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
INDEX idx_user_id (user_id)
) ENGINE=InnoDB
`,
// 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/go-sql-driver/mysql"
"github.com/honeynil/queen"
"github.com/honeynil/queen/drivers/mysql"
)
func main() {
db, _ := sql.Open("mysql", "user:password@tcp(localhost:3306)/myapp?parseTime=true")
defer db.Close()
driver := mysql.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 MySQL server. It will be skipped in CI if MySQL is not available.
package main
import (
"context"
"database/sql"
"fmt"
"log"
_ "github.com/go-sql-driver/mysql"
"github.com/honeynil/queen"
"github.com/honeynil/queen/drivers/mysql"
)
func main() {
db, err := sql.Open("mysql", "user:password@tcp(localhost:3306)/myapp?parseTime=true")
if err != nil {
fmt.Println("MySQL not available")
return
}
defer db.Close()
// Check if MySQL is actually available
if err := db.Ping(); err != nil {
fmt.Println("MySQL not available")
return
}
driver := mysql.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) ENGINE=InnoDB`,
DownSQL: `DROP TABLE users`,
})
q.MustAdd(queen.M{
Version: "002",
Name: "create_posts",
UpSQL: `CREATE TABLE posts (id INT) ENGINE=InnoDB`,
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 MySQL 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/go-sql-driver/mysql"
"github.com/honeynil/queen"
"github.com/honeynil/queen/drivers/mysql"
)
func main() {
db, _ := sql.Open("mysql", "user:password@tcp(localhost:3306)/myapp?parseTime=true")
defer db.Close()
driver := mysql.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 MySQL.
The driver is thread-safe and can be used concurrently by multiple goroutines. However, Queen already handles locking to prevent concurrent migrations.
IMPORTANT: MySQL GET_LOCK() is bound to a single database connection. Therefore this driver keeps a dedicated *sql.Conn while the lock is held, to ensure that both GET_LOCK() and RELEASE_LOCK() are executed on the same underlying connection from the pool.
func New ¶
New creates a new MySQL driver.
The database connection should already be open and configured. The default migrations table name is "queen_migrations".
Example:
db, err := sql.Open("mysql", "user:pass@tcp(localhost:3306)/db?parseTime=true")
if err != nil {
log.Fatal(err)
}
driver := mysql.New(db)
func NewWithTableName ¶
NewWithTableName creates a new MySQL 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 := mysql.NewWithTableName(db, "my_custom_migrations")
func (*Driver) Init ¶
Init creates the migrations tracking table if it doesn't exist.
The table schema:
- version: VARCHAR(255) PRIMARY KEY - unique migration version
- name: VARCHAR(255) NOT NULL - human-readable migration name
- applied_at: TIMESTAMP - when the migration was applied
- checksum: VARCHAR(64) - hash of migration content for validation
This method is idempotent and safe to call multiple times.
func (*Driver) Lock ¶
Lock acquires a named lock to prevent concurrent migrations.
MySQL uses GET_LOCK() which creates a named lock bound to the current database connection. The lock is automatically released when the connection closes 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 GET_LOCK() and RELEASE_LOCK() 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 named 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