Create Migration Skill

Use this skill when making database schema changes.

Scope

• Adding new tables
• Modifying columns or constraints
• Creating or dropping indexes
• Any DDL operation

Creating a New Migration

Step 1: Generate Migration File

task migrate:create -- your_migration_name

Naming conventions:

• Use snake_case
• Be descriptive but concise
• Use prefixes: add_, create_, drop_, modify_, update_

Examples:

task migrate:create -- add_weapon_stats_table
task migrate:create -- add_index_on_trader_offers
task migrate:create -- modify_item_properties_column

This creates a timestamped file in migrations/:

migrations/YYYYMMDDHHMMSS_your_migration_name.go

Step 2: Write the Migration

The generated file will have this structure:

package migrations

import (
    "database/sql"
    "github.com/pressly/goose/v3"
)

func init() {
    goose.AddMigrationContext(upYourMigrationName, downYourMigrationName)
}

func upYourMigrationName(ctx context.Context, tx *sql.Tx) error {
    // This code is executed when the migration is applied.
    return nil
}

func downYourMigrationName(ctx context.Context, tx *sql.Tx) error {
    // This code is executed when the migration is rolled back.
    return nil
}

In the up function:

• Write SQL to apply the change
• Use tx.ExecContext(ctx, "SQL HERE")

In the down function:

• Write SQL to reverse the change
• Make it possible to rollback safely

Example:

func upAddWeaponStatsTable(ctx context.Context, tx *sql.Tx) error {
    _, err := tx.ExecContext(ctx, `
        CREATE TABLE weapon_stats (
            id SERIAL PRIMARY KEY,
            weapon_id VARCHAR(255) NOT NULL,
            recoil_vertical INT NOT NULL,
            recoil_horizontal INT NOT NULL,
            ergonomics INT NOT NULL,
            created_at TIMESTAMP DEFAULT NOW()
        );
        CREATE INDEX idx_weapon_stats_weapon_id ON weapon_stats(weapon_id);
    `)
    return err
}

func downAddWeaponStatsTable(ctx context.Context, tx *sql.Tx) error {
    _, err := tx.ExecContext(ctx, `DROP TABLE IF EXISTS weapon_stats;`)
    return err
}

Step 3: Apply the Migration

# Recommended for DevContainer (assuming database is running)
task migrate:up

# Use if database needs to be started via Docker Compose
task migrate:up:docker

What it does:

1. migrate:up: Builds the migration binary and runs migrations against the existing database.
2. migrate:up:docker: Ensures PostgreSQL is running via Docker Compose, then applies migrations.

Verify migration applied:

docker compose exec postgres psql -U $POSTGRES_USER -d $POSTGRES_DB -c "\dt"

Step 4: Test the Migration

Test the migration works:

# Apply migration
task migrate:up

# Run integration tests
task test:integration

Test rollback works:

# Rollback the migration
task migrate:down

# Verify database is in previous state
docker compose exec postgres psql -U $POSTGRES_USER -d $POSTGRES_DB -c "\dt"

# Reapply
task migrate:up

Migration Best Practices

DO:

• ✅ Keep migrations small and focused (one logical change per migration)
• ✅ Provide a down function that reverses the change when feasible
• ✅ Test both up and down migrations before merging
• ✅ goose wraps migrations in transactions automatically
• ✅ Add indexes for foreign keys and frequently queried columns
• ✅ Use IF NOT EXISTS / IF EXISTS for safety when appropriate

DON’T:

• ❌ Modify existing migration files after they’re merged (create a new migration instead)
• ❌ Use application code in migrations (keep them SQL-only)
• ❌ Make data changes that can’t be reversed in down
• ❌ Forget to handle the error return value
• ❌ Create huge migrations that change many things at once

Common Migration Patterns

Add a Table

func upCreateTableName(ctx context.Context, tx *sql.Tx) error {
    _, err := tx.ExecContext(ctx, `
        CREATE TABLE table_name (
            id SERIAL PRIMARY KEY,
            field1 VARCHAR(255) NOT NULL,
            field2 INTEGER DEFAULT 0,
            created_at TIMESTAMP DEFAULT NOW()
        );
    `)
    return err
}

func downCreateTableName(ctx context.Context, tx *sql.Tx) error {
    _, err := tx.ExecContext(ctx, `DROP TABLE IF EXISTS table_name;`)
    return err
}

Add a Column

func upAddColumnToTable(ctx context.Context, tx *sql.Tx) error {
    _, err := tx.ExecContext(ctx, `
        ALTER TABLE table_name 
        ADD COLUMN new_column VARCHAR(255);
    `)
    return err
}

func downAddColumnToTable(ctx context.Context, tx *sql.Tx) error {
    _, err := tx.ExecContext(ctx, `
        ALTER TABLE table_name 
        DROP COLUMN IF EXISTS new_column;
    `)
    return err
}

Add an Index

func upAddIndexOnTable(ctx context.Context, tx *sql.Tx) error {
    _, err := tx.ExecContext(ctx, `
        CREATE INDEX idx_table_column ON table_name(column_name);
    `)
    return err
}

func downAddIndexOnTable(ctx context.Context, tx *sql.Tx) error {
    _, err := tx.ExecContext(ctx, `
        DROP INDEX IF EXISTS idx_table_column;
    `)
    return err
}

Troubleshooting

Migration fails with “docker”: executable file not found:

• This happens if you try to run task migrate:up:docker in an environment without Docker (like a devcontainer).
• Use task migrate:up instead if your database is already running.

Migration fails to apply:

• Check SQL syntax
• Verify table/column names exist
• Check if migration was already partially applied
• View database logs: docker compose logs postgres

Can’t rollback migration:

• Check if down function properly reverses the up function
• Some operations (like dropping columns with data) might need manual intervention
• Consider if rollback is safe with existing data

Migration applied but tests fail:

• Verify the schema change matches your model expectations
• Check if indexes are created correctly
• Ensure foreign key constraints are correct

“goose: no migrations to run” but migration file exists:

• Ensure the file is in migrations/ directory
• Check the filename format: YYYYMMDDHHMMSS_name.go
• Verify the file has package migrations at the top
• Rebuild: task migrate:build

Viewing Migration Status

# See applied migrations in database
docker compose exec postgres psql -U $POSTGRES_USER -d $POSTGRES_DB -c "SELECT * FROM goose_db_version;"

# See migration files
ls -la migrations/

CI/CD

In CI, migrations run via:

task migrate:ci

This skips the compose:postgres:up dependency (database already running in CI).