Migrations¶

Guide to creating and managing database migrations in Queen.

Creating Migrations¶

Every migration is a queen.M struct (alias for queen.Migration) with a version, name, and at least one up operation.

q.MustAdd(queen.M{
    Version: "001",
    Name:    "create_users",
    UpSQL:   `CREATE TABLE users (id SERIAL PRIMARY KEY, email VARCHAR(255) UNIQUE NOT NULL)`,
    DownSQL: `DROP TABLE users`,
})

Migration Struct¶

type Migration struct {
    Version        string                 // Required. Unique identifier.
    Name           string                 // Required. Lowercase, digits, underscores only. Max 63 chars.
    UpSQL          string                 // SQL to apply.
    DownSQL        string                 // SQL to rollback.
    UpFunc         MigrationFunc          // Go function to apply.
    DownFunc       MigrationFunc          // Go function to rollback.
    ManualChecksum string                 // Required for Go function migrations.
    IsolationLevel sql.IsolationLevel     // Per-migration isolation level override.
}

Required fields: Version, Name, and at least one of UpSQL or UpFunc.

Version rules: Letters, digits, dots, dashes, and underscores. Must be unique.

Name rules: Lowercase letters, digits, and underscores only. Maximum 63 characters.

Registration¶

// Panics on validation error (recommended for init)
q.MustAdd(queen.M{...})

// Returns error
err := q.Add(queen.M{...})

Add validates the migration and returns ErrVersionConflict if the version already exists, ErrInvalidMigration if required fields are missing, or ErrInvalidMigrationName if the name format is wrong.

SQL Migrations¶

The most common migration type. Write raw SQL for your target database.

q.MustAdd(queen.M{
    Version: "001",
    Name:    "create_users",
    UpSQL: `
        CREATE TABLE users (
            id SERIAL PRIMARY KEY,
            email VARCHAR(255) UNIQUE NOT NULL,
            name VARCHAR(255) NOT NULL,
            created_at TIMESTAMP DEFAULT NOW()
        )`,
    DownSQL: `DROP TABLE users`,
})

Multi-Statement Migrations¶

Include multiple SQL statements in one migration:

q.MustAdd(queen.M{
    Version: "002",
    Name:    "add_posts_and_index",
    UpSQL: `
        CREATE TABLE posts (
            id SERIAL PRIMARY KEY,
            user_id INTEGER REFERENCES users(id),
            title VARCHAR(255) NOT NULL,
            body TEXT
        );
        CREATE INDEX idx_posts_user_id ON posts(user_id);
    `,
    DownSQL: `DROP TABLE posts`,
})

Checksums¶

SQL migrations get automatic SHA-256 checksums based on UpSQL and DownSQL content. Checksums are whitespace-normalized to prevent false positives. If you modify a migration after applying it, Queen detects the mismatch.

Go Migrations¶

Use Go functions for complex data transformations, external API calls, or logic that cannot be expressed in SQL.

q.MustAdd(queen.M{
    Version:        "003",
    Name:           "normalize_emails",
    ManualChecksum: "v1",
    UpFunc: func(ctx context.Context, tx *sql.Tx) error {
        rows, err := tx.QueryContext(ctx, "SELECT id, email FROM users")
        if err != nil {
            return err
        }
        defer rows.Close()

        for rows.Next() {
            var id int
            var email string
            if err := rows.Scan(&id, &email); err != nil {
                return err
            }

            normalized := strings.ToLower(strings.TrimSpace(email))
            _, err = tx.ExecContext(ctx,
                "UPDATE users SET email = $1 WHERE id = $2",
                normalized, id)
            if err != nil {
                return err
            }
        }
        return rows.Err()
    },
})

ManualChecksum¶

Go function migrations require ManualChecksum because Queen cannot hash a function. Update this value when the function logic changes:

ManualChecksum: "v1",  // Initial version
ManualChecksum: "v2",  // After modifying the function

If ManualChecksum is empty, Queen marks the migration as having no checksum and skips checksum validation for it.

Execution Priority¶

If both UpFunc and UpSQL are set, UpFunc takes priority. The same applies to DownFunc and DownSQL. Only one is executed per direction.

Modular Organization¶

For larger projects, organize migrations by domain:

package migrations

func RegisterUserMigrations(q *queen.Queen) {
    q.MustAdd(queen.M{
        Version: "users_001",
        Name:    "create_users",
        UpSQL:   `CREATE TABLE users (id SERIAL PRIMARY KEY)`,
        DownSQL: `DROP TABLE users`,
    })
}

func RegisterPostMigrations(q *queen.Queen) {
    q.MustAdd(queen.M{
        Version: "posts_001",
        Name:    "create_posts",
        UpSQL:   `CREATE TABLE posts (id SERIAL PRIMARY KEY)`,
        DownSQL: `DROP TABLE posts`,
    })
}

// main.go
q := queen.New(driver)
migrations.RegisterUserMigrations(q)
migrations.RegisterPostMigrations(q)

Version Ordering¶

Migrations are sorted using natural ordering. 1 < 2 < 10 < 20. Zero-padded versions also sort correctly: 001 < 002 < 010.

Prefix-based versions (like users_001, posts_001) are compared lexicographically within each prefix group.

Testing Migrations¶

Queen provides a TestHelper for migration testing:

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.MustAdd(queen.M{
        Version: "002",
        Name:    "add_email",
        UpSQL:   `ALTER TABLE users ADD COLUMN email VARCHAR(255)`,
        DownSQL: `ALTER TABLE users DROP COLUMN email`,
    })

    q.TestUpDown()     // Apply all, then rollback all
    q.TestRollback()   // Apply all, rollback one-by-one, apply all again
}

NewTest automatically cleans up resources when the test ends. The mock driver works entirely in memory.

Best Practices¶

Make migrations small and focused. One concern per migration.
Always define DownSQL or DownFunc for rollback capability.
Use ManualChecksum for Go function migrations and update it when logic changes.
Never modify an applied migration. Create a new one instead.
Test migrations with TestUpDown and TestRollback before deploying.
Use descriptive names: create_users, add_posts_title_index, normalize_emails.