dev-database-patterns

Database Patterns — Software Complexo

Migration Safety Protocol

Nunca aplicar migration sem este protocolo:

# 1. Snapshot — estado antes
pg_dump $DATABASE_URL --schema-only > backups/schema-$(date +%Y%m%d-%H%M%S).sql

# 2. Dry-run — verificar sem commitar (BEGIN + ROLLBACK)
psql $DATABASE_URL <<'EOF'
BEGIN;
\i migrations/001_add_users_table.sql
-- Verificar resultado sem commitar
SELECT COUNT(*) FROM users;
\d users
ROLLBACK;  -- Desfaz tudo — apenas verificação
EOF

# 3. Apply — executar de verdade
psql $DATABASE_URL -f migrations/001_add_users_table.sql

# 4. Smoke test — verificar integridade após apply real
psql $DATABASE_URL -c "SELECT COUNT(*) FROM users;"
psql $DATABASE_URL -c "\d users"

# 5. Rollback disponível (executar se smoke test falhar)
psql $DATABASE_URL -f migrations/001_add_users_table.rollback.sql

Nota: PostgreSQL não tem flag --dry-run nativo. O dry-run correto é sempre via BEGIN/ROLLBACK — executa a migration em transação e faz rollback sem commitar.

Migration Structure

Cada migration tem arquivo de rollback correspondente:

migrations/
├── 001_create_users.sql
├── 001_create_users.rollback.sql
├── 002_add_user_roles.sql
└── 002_add_user_roles.rollback.sql

Template de migration

-- migrations/001_create_users.sql
-- Migration: create users table
-- Author: dev-data-engineer
-- Date: 2026-04-21

BEGIN;

CREATE TABLE users (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email       VARCHAR(255) NOT NULL UNIQUE,
  name        VARCHAR(100) NOT NULL,
  role        VARCHAR(50) NOT NULL DEFAULT 'member',
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  deleted_at  TIMESTAMPTZ  -- soft delete
);

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_role ON users(role) WHERE deleted_at IS NULL;

COMMENT ON TABLE users IS 'Core user accounts';

COMMIT;

-- migrations/001_create_users.rollback.sql
BEGIN;
DROP TABLE IF EXISTS users;
COMMIT;