Databases & ORMs

Database Connections

Use connection pools for SQL databases to reuse TCP connections under load. pg Pool and mysql2 pools handle concurrency limits and idle timeouts.

MongoDB clients connect once at startup and reuse the client across requests. Configure retry writes and read preferences for replica sets.

Load credentials from environment variables and TLS-encrypt connections to managed cloud databases.

• Close pools gracefully on shutdown
• Use parameterized queries exclusively—never string concat SQL
• Monitor pool waiting counts under load tests

import pg from 'pg';
const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL });
const { rows } = await pool.query('SELECT * FROM users WHERE id = $1', [id]);

ORMs and Query Builders

Sequelize, TypeORM, and Prisma map tables to classes or schemas with migrations. Mongoose schemas model MongoDB documents with validation hooks.

ORMs accelerate CRUD and migrations but hide SQL costs. Profile N+1 query patterns and add includes or joins where needed.

Knex offers a lighter query builder without full ORM magic when you want SQL control with composable JavaScript.

• Commit migration files to version control
• Generate types from Prisma schema for end-to-end safety
• Use transactions for multi-table business invariants

Transactions and Consistency

Wrap related writes in transactions so partial failures roll back. In pg, BEGIN, COMMIT, and ROLLBACK via client.query or ORM transaction APIs.

Implement idempotency keys on payment endpoints so retries do not double charge. Store keys in a unique index.

For distributed systems, local transactions plus outbox pattern often beat two-phase commit complexity.

• Keep transactions short to reduce lock contention
• Retry serialization failures with exponential backoff
• Test rollback paths explicitly

const client = await pool.connect();
try {
  await client.query('BEGIN');
  await client.query('INSERT INTO orders ...');
  await client.query('INSERT INTO order_items ...');
  await client.query('COMMIT');
} catch (e) {
  await client.query('ROLLBACK');
  throw e;
} finally {
  client.release();
}