database-design

Choose based on deployment context: PostgreSQL for full relational features, Neon for serverless PostgreSQL with branching, Turso for edge deployment with low latency, and SQLite for simple embedded or local apps. The decision tree in the reference files walks through these trade-offs.

Drizzle fits edge deployments and small bundle sizes with SQL-like syntax, while Prisma offers better developer experience with schema-first migrations and tooling. Kysely is an option when you want a type-safe SQL query builder with maximum control.

Solve N+1 queries by using JOINs to fetch related data in one query, eager loading through your ORM, DataLoader for batching in GraphQL, or subqueries. First run EXPLAIN ANALYZE to confirm the query pattern before optimizing.

Index columns used in WHERE clauses, JOIN conditions, ORDER BY, and foreign keys. Avoid over-indexing write-heavy tables, low-cardinality columns, and columns rarely queried, since indexes slow down inserts.

Make changes in multiple steps: add columns as nullable then backfill before adding NOT NULL, create indexes with CREATE INDEX CONCURRENTLY, and rename columns by adding the new one, migrating data, then dropping the old. Always test on a data copy first.