Databases Highlights Summary

Every highlight bullet from the 8 pages in this section, gathered on one page and grouped by the page it came from.

Node.js Database Connectivity

• A database connection is expensive to open (TCP + auth) and cheap to reuse - pooling exists to amortize that cost
• A driver talks wire protocol directly; an ORM sits on top of a driver, it never replaces it
• The event loop makes queries non-blocking, but a database connection is still a single-file channel per socket
• Pool size is a concurrency limit you choose, not a performance setting to maximize

Databases Basics

• Pick SQL for relational integrity; documents for flexible schemas
• Use connection pools in every production Node process
• Parameterized queries prevent SQL injection
• Migrations are versioned code, not manual DDL
• One typed data access layer per service

node-postgres (pg)

• One Pool per process; checkout clients only for transactions
• Parameterized queries ($1) block SQL injection
• Keep transactions short; no external HTTP inside
• Use pool.query for single statements; connect for multi-step tx
• Log slow queries with duration and statement name, not raw secrets

Prisma

• Schema in schema.prisma drives client types and migrations
• Watch include and nested writes for N+1 queries
• Use $transaction for multi-step consistency
• Prisma Client is generated - commit migrations, not generated output debates
• Raw queries escape hatch for reporting SQL

Drizzle ORM

• Schema is TypeScript tables you can read as SQL
• Queries compile to parameterized SQL via pg
• drizzle-kit generates migrations from schema diffs
• Relations API mirrors SQL joins - fewer surprises than magic loaders
• Thin runtime suits performance-sensitive APIs

MongoDB Driver

• Official mongodb driver - no Mongoose required for new services
• Design around access patterns; embed vs reference consciously
• Aggregation pipelines power analytics in-database
• Transactions exist but keep them short like SQL
• Index every query filter and sort path you ship

Connection Pool Tuning

• Total connections = processes x pool max x replicas
• PgBouncer transaction mode suits serverless and high replica counts
• Idle timeout prevents holding dead connections
• Alert on pool wait time, not only query duration
• Separate pools for read replicas when routing SELECTs

Databases Best Practices

• Migrations run in CI/CD before traffic shifts
• Parameterized queries only at the application layer
• One pool per process; size against Postgres limits
• Repositories isolate SQL/ORM from routes
• Backups and restore drills are scheduled, not hoped for