Database Patterns

Comprehensive patterns for database migrations, schema design, and version management. Each category has individual rule files in rules/ loaded on-demand.

Quick Reference

| Database Selection | 1 | HIGH | Choosing the right database, PostgreSQL vs MongoDB, cost analysis |

Total: 12 rules across 5 categories

Quick Start

# Alembic: Auto-generate migration from model changes
# alembic revision --autogenerate -m "add user preferences"

def upgrade() -> None:
    op.add_column('users', sa.Column('org_id', UUID(as_uuid=True), nullable=True))
    op.execute("UPDATE users SET org_id = 'default-org-uuid' WHERE org_id IS NULL")

def downgrade() -> None:
    op.drop_column('users', 'org_id')

-- Schema: Normalization to 3NF with proper indexing
-- PG18: prefer uuidv7() (time-ordered, better B-tree locality) over gen_random_uuid() (random v4)
CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT uuidv7(),
    customer_id UUID NOT NULL REFERENCES customers(id),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_orders_customer_id ON orders(customer_id);

Alembic Migrations

Migration management with Alembic for SQLAlchemy 2.0 async applications.

Schema Design

SQL and NoSQL schema design with normalization, indexing, and constraint patterns.

Versioning

Database version control and change management across environments.

Database Selection

Decision frameworks for choosing the right database. Default: PostgreSQL.

Key Decisions

Anti-Patterns (FORBIDDEN)

# NEVER: Add NOT NULL without default or two-phase approach
op.add_column('users', sa.Column('org_id', UUID, nullable=False))  # LOCKS TABLE!

# NEVER: Use blocking index creation on large tables
op.create_index('idx_large', 'big_table', ['col'])  # Use CONCURRENTLY

# NEVER: Skip downgrade implementation
def downgrade():
    pass  # WRONG - implement proper rollback

# NEVER: Modify migration after deployment - create new migration instead

# NEVER: Run migrations automatically in production
# Use: alembic upgrade head --sql > review.sql

# NEVER: Run CONCURRENTLY inside transaction
op.execute("BEGIN; CREATE INDEX CONCURRENTLY ...; COMMIT;")  # FAILS

# NEVER: Delete migration history
command.stamp(alembic_config, "head")  # Loses history

# NEVER: Skip environments (Always: local -> CI -> staging -> production)

Detailed Documentation

Zero-Downtime Migration

Safe database schema changes without downtime using expand-contract pattern and online schema changes.

Related Skills

• sqlalchemy-2-async - Async SQLAlchemy session patterns
• ork:testing-integration - Integration testing patterns including migration testing
• caching - Cache layer design to complement database performance
• ork:performance - Performance optimization patterns