PostgreSQL with Node.js and Application Architecture

Connect PostgreSQL to applications safely, use pools and transactions, and map database types to application types.

Connecting from Node.js with a Connection Pool

Applications typically use a connection pool to reuse database connections rather than creating a new connection for every query. Pool size should be considered across all application instances, not just one process: several replicas each configured with a large pool can exhaust the database's connection limit. Set timeouts, handle errors, and release checked-out clients reliably.

Keep database credentials in environment configuration or a secret manager. A connection pool manages connectivity, not authorization; the database role still needs appropriately scoped privileges.

Use node-postgres safely

import pg from "pg";

const pool = new pg.Pool({
  connectionString: process.env.DATABASE_URL,
  max: 10,
  idleTimeoutMillis: 30_000,
  connectionTimeoutMillis: 5_000
});

const result = await pool.query(
  "SELECT id, name FROM customers WHERE id = $1",
  [customerId]
);

💡 This example uses the pg package. Choose a pool size based on total application replicas and database capacity, and ensure the application closes the pool during graceful shutdown.

Key Takeaways

Use a pool for typical server applications.
Calculate total connections across all replicas.
Use bound parameters and graceful shutdown handling.

Knowledge Check

Why can a pool size of 20 per process be dangerous with many replicas?

Application Transactions

A transaction involving multiple statements generally requires a single checked-out connection so all statements execute in the same transaction. Calling pool.query separately for each statement does not guarantee that the same connection is used. Acquire a client, issue BEGIN, perform statements, COMMIT on success, ROLLBACK on failure, and release the client in a finally block.

Avoid making network calls to unrelated services while holding a database transaction open unless the design explicitly requires it. Long transactions hold resources and can worsen contention. For distributed workflows, consider idempotency and patterns such as an outbox rather than assuming a database transaction can atomically commit a remote API request.

Transaction structure

const client = await pool.connect();
try {
  await client.query("BEGIN");
  await client.query(
    "UPDATE inventory SET quantity = quantity - $1 WHERE product_id = $2",
    [quantity, productId]
  );
  await client.query("COMMIT");
} catch (error) {
  await client.query("ROLLBACK");
  throw error;
} finally {
  client.release();
}

💡 All statements use the same client. Production code should validate the requested quantity, verify affected row counts, handle rollback errors appropriately, and ensure the operation is idempotent where needed.

Key Takeaways

Use one checked-out client for a multi-statement transaction.
Rollback on failure and always release the client.
Keep transactions short and avoid treating remote calls as part of a local DB transaction.

Knowledge Check

Why should a transaction use the same checked-out client for all statements?

Type Mapping, Dates, Big Numbers, and Errors

Database types do not always map one-to-one to JavaScript types. PostgreSQL BIGINT values may exceed JavaScript's safe integer range, so drivers often return them as strings unless configured otherwise. NUMERIC values are frequently returned as strings to avoid precision loss. TIMESTAMPTZ values need deliberate handling of time zones, and database errors should be mapped to safe application responses without leaking sensitive SQL or data.

Treat driver behavior as part of your application's contract. Test boundary values and avoid silently converting large IDs or exact decimals to JavaScript number when precision matters.

Handle a numeric value deliberately

const { rows } = await pool.query(
  "SELECT id, price FROM products WHERE id = $1",
  [productId]
);

// Depending on driver configuration, BIGINT/NUMERIC may be strings.
const product = rows[0];
const priceAsText = product.price;

💡 Keep exact decimal values as strings or use a suitable decimal library when arithmetic must remain exact. Convert only when the target type's precision limits are acceptable.

Key Takeaways

BIGINT and NUMERIC may be returned as strings by drivers.
JavaScript number has finite integer precision.
Map database errors to safe API errors without exposing secrets.

Knowledge Check

Why should a monetary NUMERIC value not be blindly converted to JavaScript number?

Connecting from Node.js with a Connection Pool

Application Transactions

Type Mapping, Dates, Big Numbers, and Errors

STAY UPDATED WITH BYTES!