using-relational-databases

Prisma offers the best developer experience with auto-generated types and built-in migrations, while Drizzle provides better performance with SQL-like syntax and zero abstraction overhead. Choose Prisma for rapid development and Drizzle when query control and performance matter most.

Use a multi-phase migration: first make the column nullable and deploy code that no longer uses it, then drop constraints, and finally drop the column after confirming no running code references it. Dropping directly breaks running application instances during deployment.

Use 10-20 connections for a single-instance web API, following the formula (CPU cores × 2) + disk count. Serverless functions should use 1-2 connections with a pooler like pgBouncer, since many concurrent function instances can exhaust database connections.

Yes, SQLx validates SQL queries against your actual database schema at compile time using the query! and query_as! macros. This requires DATABASE_URL to be set during builds and catches schema mismatches before runtime.

PlanetScale (MySQL/Vitess) fits when you need non-blocking schema changes and built-in read replicas, but lacks foreign key constraints. Neon (PostgreSQL) offers scale-to-zero compute, database branching, and full PostgreSQL features like JSONB and extensions.

A plain CREATE INDEX blocks writes while the index builds. In PostgreSQL, use CREATE INDEX CONCURRENTLY to build without blocking writes, noting it cannot run inside a transaction; MySQL uses ALGORITHM=INPLACE, LOCK=NONE instead.