PostgreSQL + Drizzle ORM Best Practices

Type-safe database applications with PostgreSQL 18 and Drizzle ORM.

Directory Structure

src/db/
├── schema/
│   ├── index.ts      # Re-export all tables
│   ├── users.ts      # User table + relations
│   └── posts.ts      # Post table + relations
├── db.ts             # Database connection
└── migrate.ts        # Migration runner
drizzle/
└── migrations/       # Generated SQL migrations
drizzle.config.ts     # drizzle-kit configuration

Essential Commands

npx drizzle-kit generate   # Generate migration from schema
npx drizzle-kit migrate    # Apply migrations
npx drizzle-kit push       # Push schema directly (dev only)
npx drizzle-kit studio     # Database browser

PostgreSQL 18 Highlights

Performance Checklist

• Use uuidv7() for primary keys (PG18+) or defaultRandom()
• Create indexes on foreign keys
• Use partial indexes for filtered subsets
• Use relational queries API to avoid N+1
• Configure connection pooling in production
• Run EXPLAIN (ANALYZE, BUFFERS) for slow queries

Code Examples

Complete, runnable examples in the examples/ directory:

TypeScript (Drizzle ORM)

SQL (PostgreSQL)

Reference Documentation

Detailed explanations in references/:

• SCHEMA.md - Column types, constraints, patterns
• QUERIES.md - Operators, joins, aggregations
• RELATIONS.md - One-to-many, many-to-many, relational API
• MIGRATIONS.md - drizzle-kit workflows
• POSTGRES.md - PostgreSQL 18 features, RLS, partitioning
• PERFORMANCE.md - Indexing, optimization, pooling
• CHEATSHEET.md - Quick reference

Resources

• Drizzle Docs: https://orm.drizzle.team
• PostgreSQL Docs: https://www.postgresql.org/docs/18/
• Drizzle Studio: npx drizzle-kit studio