Databases Best Practices

Migrations in CI; never manual prod DDL. Use this checklist for Postgres, Prisma, Drizzle, and MongoDB on Node.js teams.

How to Use This List

• Gate production deploys on migrate deploy success
• Review slow query logs weekly in staging
• Pair with Connection Pool Tuning before scaling replicas
• Apply same rules to workers and cron jobs, not only HTTP APIs

A - Schema and Migrations

• All DDL is versioned migration files. No manual ALTER in prod consoles.
• Migrations run in CI/CD pipeline before new app revision serves traffic.
• Backward-compatible migrations for zero-downtime deploys (add column nullable first).
• Seed data scripts are idempotent for dev/staging only - never auto-seed prod.
• Index additions reviewed for lock duration - use CONCURRENTLY on Postgres when needed.

B - Connection Management

• One connection pool per process per database URL.
• pool.max sized with replica count formula - stays under 70% of max_connections.
• PgBouncer or RDS Proxy when using serverless or >20 API replicas.
• pool.end() / $disconnect() on SIGTERM in graceful shutdown.
• Readiness probe runs SELECT 1 not just TCP socket open.

C - Query Safety and Performance

• Parameterized queries everywhere - $1 for pg, ORM APIs for Prisma/Drizzle.
• No unbounded SELECT * list endpoints - pagination and column selection required.
• N+1 checks in staging with query logging or APM.
• Statement timeout configured on Postgres role (e.g. 30s) as safety net.
• Transactions kept short - no outbound HTTP inside open transactions.

D - Application Architecture

• Repository or data access module - routes do not embed SQL strings.
• Domain types separate from ORM models when boundaries matter.
• Idempotency keys on write endpoints that external systems retry.
• Outbox or queue for side effects after commit, not before.
• Integration tests use Testcontainers or dedicated staging DB - not dev laptop only.

E - Operations and Resilience

• Automated backups with tested restore quarterly drill documented.
• Point-in-time recovery enabled on managed Postgres.
• Database outage runbook linked from on-call - see Database Outage Mode.
• Secrets in vault/SSM - not in connection strings in git.
• Monitoring: connections, CPU, disk, replication lag, slow queries.

F - MongoDB-Specific (When Used)

• Indexes on every production query filter.
• Document size budget - no unbounded embedded arrays.
• Write concern majority for critical data.
• Schema versioning field on documents for rolling migrations.

FAQs

Related

• Databases Basics - introductory examples
• Connection Pool Tuning - sizing math
• node-postgres (pg) - driver patterns
• Prisma - schema migrations
• Database Outage Mode - incident response

Stack versions: This page was written for Node.js 24.18.0 (Active LTS), npm 10+, TypeScript 5.6+, Express 5, Fastify 5, and NestJS 11.