database-design

Choose Drizzle for edge deployments and small bundle sizes since it is lightweight and SQL-like. Choose Prisma when you want the best developer experience with schema-first design, built-in migrations, and Prisma Studio. Kysely fits when you want a type-safe SQL builder with maximum control.

Start by deciding normalization versus denormalization based on read patterns and update frequency. Use one-to-many with a foreign key on the child table, many-to-many with a junction table, and pick primary keys like UUID for distributed systems or auto-increment for simple apps.

Use PostgreSQL for full relational features and complex queries, SQLite for simple embedded or local apps, and Turso for edge deployment with ultra-low latency. For serverless PostgreSQL with branching, consider Neon. Match the choice to your deployment environment and query complexity.

N+1 happens when one query fetches parents and N additional queries fetch related records. Fix it with JOINs to load everything in one query, ORM eager loading, DataLoader batching for GraphQL, or subqueries. Use EXPLAIN ANALYZE first to confirm the bottleneck.

Never make breaking changes in one step. Add columns as nullable, backfill data, then add NOT NULL constraints. Create indexes with CREATE INDEX CONCURRENTLY to avoid blocking. For renames, add the new column, migrate data, deploy, then drop the old column.

Index columns used in WHERE clauses, JOIN conditions, ORDER BY, and foreign keys. Avoid over-indexing write-heavy tables, low-cardinality columns, and rarely queried fields. For composite indexes, put equality columns first and range columns last.