database-design

Choose Drizzle for edge deployments and small bundle sizes since it is SQL-like and lightweight. Choose Prisma when you want the best developer experience with schema-first design, built-in migrations, and Prisma Studio, accepting its heavier runtime.

For serverless PostgreSQL, use Neon, which offers scale-to-zero, instant branching, and full PostgreSQL compatibility. For edge deployment with ultra-low latency, use Turso, an edge-distributed SQLite with a generous free tier.

N+1 occurs 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 for batching in GraphQL, or subqueries.

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

Add columns as nullable first, backfill data, then add NOT NULL constraints. Create indexes with CREATE INDEX CONCURRENTLY to avoid blocking, and rename columns by adding the new column, migrating data, deploying, then dropping the old one.

Use UUID for distributed systems and when exposing IDs publicly for security. Use ULID when you need UUIDs sortable by time. Auto-increment works fine for simple single-database applications.