Migration Files: SQL or Go files with Up/Down sections, timestamp prefix
Schema Changes: Additive, backward-compatible changes preferred
Data Migrations: SQL for simple, Go for complex logic
Safety: Idempotent operations, reversible when possible
Transactions: Proper boundaries for rollback safety
Best Practices: Small migrations, test on staging, document non-reversible ops
Examples: Complete, runnable migration code with goose annotations
Commands: goose CLI commands for running migrations

Focus on safe, reversible schema evolution that minimizes downtime and risk.

Edge Cases

If migration is non-rollbackable (DROP TABLE): Document in comments and create empty table structure in Down.

If data migration is large: Implement in batches with progress tracking and allow resumption.

If production data exists: Always test on copy of production data first.

If migration fails mid-operation: Check database state manually before retrying.

If schema change breaks existing queries: Coordinate with application deployment.

If multiple applications share database: Coordinate migration timing with all teams.

If performance impact is high: Schedule during maintenance window or use online DDL.

If migration involves sensitive data: Ensure proper encryption and access controls.

If conflict arises with existing indexes: Rename old index before creating new one.

If column type change is needed: Add new column, migrate data, then drop old column (multi-step process).

If complex business logic is required in data migration: Delegate to go-db skill for query patterns.

If application code changes are needed: Coordinate with go-code skill for implementation.

Examples

Example 1

Input: Add a new column email_verified with default value false, make it NOT NULL

Output:

-- +goose Up
ALTER TABLE users ADD COLUMN IF NOT EXISTS email_verified BOOLEAN DEFAULT false;
ALTER TABLE users ALTER COLUMN email_verified SET NOT NULL;

-- +goose Down
ALTER TABLE users ALTER COLUMN email_verified DROP NOT NULL;
ALTER TABLE users DROP COLUMN IF EXISTS email_verified;

Example 2

Input: Drop old_users table with cascade, handle non-reversible operation

Output:

-- +goose Up
DROP TABLE IF EXISTS old_users CASCADE;

-- +goose Down
-- Non-rollbackable - recreate empty table structure
CREATE TABLE IF NOT EXISTS old_users (
    id UUID PRIMARY KEY,
    email VARCHAR(255),
    created_at TIMESTAMPTZ
);

Example 3

Input: Migrate user data to set status based on email_verified column

Output:

-- +goose Up
UPDATE users
SET status = 'verified'
WHERE email_verified = true
  AND status != 'verified';

-- +goose Down
UPDATE users
SET status = 'pending'
WHERE email_verified = false
  AND status = 'verified';

Example 4

Input: Rename users table to accounts

Output:

-- +goose Up
ALTER TABLE users RENAME TO accounts;

-- +goose Down
ALTER TABLE accounts RENAME TO users;

Example 5

Input: Migrate user status based on account age using Go migration

Output: See references/rollback-patterns.md for complete rollback strategies including data migrations, non-rollbackable operations, and complex Go migrations with state preservation.

References

Constraints

go-migration

Role

Instructions

Response Format