javascript 48 lines · 8 steps

Per-request Postgres connections in Express

Give every Express request its own pooled Postgres client that is reliably released when the response ends.

Explained by highlit

Express Intermediate connection-pooling middleware transactions resource-cleanup postgres

1const { Pool } = require('pg');
2 
3const pool = new Pool({
4  connectionString: process.env.DATABASE_URL,
5  max: 20,
6  idleTimeoutMillis: 30000,
7  connectionTimeoutMillis: 5000,
8});
9 
10function attachDb(app) {
11  app.locals.pool = pool;
12 
13  app.use(async (req, res, next) => {
14    const client = await pool.connect();
15    req.db = client;
16 
17    let released = false;
18    const release = () => {
19      if (released) return;
20      released = true;
21      client.release();
22    };
23 
24    res.on('finish', release);
25    res.on('close', release);
26 
27    next();
28  });
29}
30 
31async function withTransaction(req, work) {
32  const { db } = req;
33  await db.query('BEGIN');
34  try {
35    const result = await work(db);
36    await db.query('COMMIT');
37    return result;
38  } catch (err) {
39    await db.query('ROLLBACK');
40    throw err;
41  }
42}
43 
44async function shutdown() {
45  await pool.end();
46}
47 
48module.exports = { attachDb, withTransaction, shutdown };

01 / 01

STEP 01

Walkthrough

Space play ←→ step click any line

Three takeaways

1A connection pool caps concurrent database clients and hands them out on demand instead of opening a socket per query.
2Tying client release to response lifecycle events prevents leaks even when a handler forgets to clean up.
3Wrapping work in BEGIN/COMMIT with a ROLLBACK on error keeps multi-step database changes atomic.

Related explainers

php

<?php
 
namespace App\Services\Checkout;

Validating coupons with Laravel's Pipeline

pipeline chain of responsibility transactions

Intermediate 7 steps

python

import time
import uuid
 
from django.utils.deprecation import MiddlewareMixin

Attaching per-request context in Django

middleware request lifecycle multi-tenancy

Intermediate 7 steps

javascript

function evaluate(expression) {
  const tokens = tokenize(expression);
  let pos = 0;

Building a recursive descent calculator

parsing recursion operator-precedence

Intermediate 8 steps

javascript

import { useState, useEffect, useCallback, useRef } from 'react';
 
const cache = new Map();
const inflight = new Map();

Building a stale-while-revalidate hook in React

caching request-deduplication custom-hooks

Advanced 10 steps

typescript

import { Injectable, Scope, Inject, NotFoundException } from '@nestjs/common';
import { REQUEST } from '@nestjs/core';
import { Request } from 'express';
import { DataSource } from 'typeorm';

Per-tenant database connections in NestJS

multi-tenancy connection-pooling dependency-injection

Advanced 8 steps

javascript

import { useEffect, useRef, useState } from 'react';
 
export function useDelayedFlag(active, delay = 300) {
  const [visible, setVisible] = useState(false);