Database Migration Management

Creating New Migrations

1. •Always create migrations from the ecommerce-backend/ directory
2. •Use sequential numbering (check existing migrations in migrations/ folder)
3. •Create both .up.sql and .down.sql files

cd ecommerce-backend
# Check the last migration number
ls migrations/ | Sort-Object | Select-Object -Last 1
# Create new migration with next number (e.g., 000009)

1. •Migration file naming: {number}_{description}.{up|down}.sql
   • •Example: 000009_create_reviews_table.up.sql

Running Migrations

ALWAYS run from ecommerce-backend/ directory:

cd ecommerce-backend

# Run migrations
make migrate-up
# OR
go run cmd/migrate/main.go up

# Verify migrations applied
$env:Path += ";D:\Program Files\PostgreSQL\18\bin"
$env:PGPASSWORD = "postgres"
psql -U postgres -d ecommerce -c "\dt"

Rolling Back Migrations

cd ecommerce-backend
make migrate-down
# OR
go run cmd/migrate/main.go down

Common Migration Issues

"relation already exists"

• •Check if table already exists: psql -U postgres -d ecommerce -c "\d table_name"
• •Add IF NOT EXISTS clause to CREATE TABLE statements

"relation does not exist"

• •Run migrations: cd ecommerce-backend && make migrate-up
• •Verify DATABASE_URL in .env file points to correct database

"connect: connection refused"

• •Start PostgreSQL: docker compose up -d (if using Docker)
• •Or verify PostgreSQL service is running on Windows

Migration stuck or failed

• •Check schema_migrations table: SELECT * FROM schema_migrations;
• •Manual rollback if needed, then re-run

Migration Best Practices

1. •Always include IF NOT EXISTS/IF EXISTS clauses
2. •Always create indexes on foreign keys
3. •Always test rollback (down migration) before committing
4. •Use transactions when multiple operations are related
5. •Never modify existing migrations - create new ones to fix issues
6. •Include helpful comments in migration files

Example Migration Structure

-- 000009_create_reviews_table.up.sql
CREATE TABLE IF NOT EXISTS reviews (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    product_id INTEGER NOT NULL REFERENCES products(id) ON DELETE CASCADE,
    rating INTEGER NOT NULL CHECK (rating >= 1 AND rating <= 5),
    comment TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_reviews_user_id ON reviews(user_id);
CREATE INDEX IF NOT EXISTS idx_reviews_product_id ON reviews(product_id);

-- 000009_create_reviews_table.down.sql
DROP TABLE IF EXISTS reviews CASCADE;