mysql

package
v0.1.0 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: Jan 10, 2026 License: MIT Imports: 6 Imported by: 0

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. 

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, _ := 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()

	// 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)
	}

}

Output:

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 

type Driver struct {
	// contains filtered or unexported fields
}

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.

func New 

func New(db *sql.DB) *Driver

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 

func NewWithTableName(db *sql.DB, tableName string) *Driver

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) Close 

func (d *Driver) Close() error

Close closes the database connection.

Any locks held by this connection will be automatically released.

func (*Driver) Exec 

func (d *Driver) Exec(ctx context.Context, fn func(*sql.Tx) error) error

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.

func (*Driver) GetApplied 

func (d *Driver) GetApplied(ctx context.Context) ([]queen.Applied, error)

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.

func (*Driver) Init 

func (d *Driver) Init(ctx context.Context) error

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 

func (d *Driver) Lock(ctx context.Context, timeout time.Duration) error

Lock acquires a named lock to prevent concurrent migrations.

MySQL uses GET_LOCK() which creates a named lock. 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.

If the lock cannot be acquired within the timeout, returns queen.ErrLockTimeout.

func (*Driver) Record 

func (d *Driver) Record(ctx context.Context, m *queen.Migration) error

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.

func (*Driver) Remove 

func (d *Driver) Remove(ctx context.Context, version string) error

Remove removes a migration record from the database.

This should be called after successfully rolling back a migration's down function.

func (*Driver) Unlock 

func (d *Driver) Unlock(ctx context.Context) error

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.

Source Files

View all Source files

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.