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:

• migrate:up : Builds the migration binary and runs migrations against the existing database.

• 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).