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. Consider query complexity, edge requirements, and global distribution needs.

Drizzle is best for edge deployment and small bundle size with SQL-like syntax, while Prisma offers better developer experience with schema-first migrations and studio tooling. Kysely suits type-safe SQL building when you want maximum control.

Solve N+1 problems by using JOINs to fetch related data in a single 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 rarely queried columns since indexes slow down inserts.

Never make breaking changes in one step: add columns as nullable then backfill, use CREATE INDEX CONCURRENTLY for non-blocking indexes, and rename columns by adding the new one, migrating data, then dropping the old. Always test on a data copy first.

The schema_validator.py script checks Prisma schemas for PascalCase model naming, missing @id fields, missing createdAt timestamps, and suggests @@index additions for foreign key columns. It outputs a JSON report and treats findings as warnings rather than failures.