# Database Patterns

> Database design. Schema normalization, indexing, migrations, query optimization. SQL and NoSQL.

- Skill: `aselimc/database-patterns` (Agent Skill)
- Install (CLI): `npx skillmds@latest add aselimc/database-patterns`
- Raw SKILL.md: https://api.skillmd.com/api/skills/aselimc/database-patterns/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Data & Analytics
- Author: aselimc (https://skillmd.com/u/aselimc)
- Updated: 2026-09-10
- Page: https://skillmd.com/skills/aselimc/database-patterns

---


# Database Patterns

## Schema Design (SQL)
- Normalize to 3NF, denormalize intentionally for read performance
- Foreign keys with ON DELETE CASCADE/SET NULL as appropriate
- Use UUIDs for public-facing IDs, serial for internal

## Indexing
- B-tree: equality and range queries (default)
- GIN: JSONB, full-text search, array containment
- Covering index: include all SELECT columns to avoid table lookup
- Rule: index columns in WHERE, JOIN, ORDER BY clauses

## Migrations
```python
# Alembic
alembic revision --autogenerate -m "add users table"
alembic upgrade head
```
- Always make migrations reversible (include downgrade)
- Never drop columns in production without deprecation period

## Query Optimization
- Use `EXPLAIN ANALYZE` to identify slow queries
- Watch for sequential scans on large tables
- Connection pooling: PgBouncer or SQLAlchemy pool

## Key Libraries
SQLAlchemy, Prisma, TypeORM, Alembic, Drizzle