Create a new database migration for PostgreSQL. Covers file naming, SQL patterns, running, and updating dependent code.
Check existing migrations:
ls migrations/ | sort -n | tail -5
Use the next sequential number, zero-padded to 3 digits.
Create migrations/{NNN}_$ARGUMENTS.sql:
-- Migration: {NNN} $ARGUMENTS
-- Date: $(date +%Y-%m-%d)
BEGIN;
-- Your DDL here
CREATE TABLE IF NOT EXISTS my_table (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
value NUMERIC NOT NULL DEFAULT 0,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_my_table_name ON my_table(name);
COMMIT;
Wrap all DDL in BEGIN/COMMIT so failures roll back cleanly.
Use IF NOT EXISTS / IF EXISTS so migrations can be re-run safely:
CREATE TABLE IF NOT EXISTS ...
CREATE INDEX IF NOT EXISTS ...
ALTER TABLE my_table ADD COLUMN IF NOT EXISTS new_col TEXT;
DROP INDEX IF EXISTS old_index;
Always index foreign key columns:
ALTER TABLE child_table ADD COLUMN parent_id INTEGER REFERENCES parent_table(id);
CREATE INDEX IF NOT EXISTS idx_child_parent_id ON child_table(parent_id);
psql "$DATABASE_URL" -f migrations/{NNN}_$ARGUMENTS.sql
If DDL requires superuser privileges (creating extensions, altering roles):
psql "$SUPER_DATABASE_URL" -f migrations/{NNN}_$ARGUMENTS.sql
/add-endpoint)# Confirm migration applied
psql "$DATABASE_URL" -c "\d+ my_table"
# Restart app to pick up any schema changes
docker compose restart hello-world
# Run tests
python -m pytest tests/ -v