Database Patterns

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: 10 rules across 5 categories

This skill is a wrap around Alembic and PostgreSQL, not a replacement for their docs. Read references/ork-delta.md first: it holds the version floors, corrections and house conventions that upstream does not carry. Everything in the table below was removed on purpose.

Upstream coverage (do not restate)

These topics are vendor documentation. Fetch them from the source instead of re-teaching them here.

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.

Autogenerate setup is upstream. Our one deviation from Alembic's async env.py template (the in_greenlet() guard) is in references/ork-delta.md.

Schema Design

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

Versioning

Database version control and change management across environments.

Rollback testing lives in references/migration-testing.md; the docstring convention for lossy downgrades is in references/ork-delta.md.

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