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

Related explainers

python 
import time
import threading
from flask import Flask, request, jsonify, g
 

A token-bucket rate limiter in Flask

rate-limiting token-bucket middleware
Intermediate 7 steps
javascript 
const express = require('express');
const router = express.Router();
const db = require('../db');
 

Building a paginated orders page in Express

pagination routing sql-queries
Intermediate 7 steps
go 
package middleware
 
import (
	"context"

Per-tenant daily rate limiting in Gin

rate-limiting middleware redis
Intermediate 8 steps
ruby 
class Document < ApplicationRecord
  class StaleObjectError < StandardError
    def initialize(id)
      super("Document ##{id} was modified by another process")

Optimistic locking with retries in Rails

optimistic-locking concurrency transactions
Advanced 8 steps
javascript 
import { Suspense } from 'react';
import { searchProducts } from '@/lib/products';
import SearchInput from './search-input';
 

Streaming search results in a Next.js Server Component

server-components suspense streaming
Intermediate 8 steps
rust 
use axum::{
    body::{Body, Bytes},
    extract::Request,
    http::StatusCode,

Logging request and response sizes in Axum

middleware http streaming-bodies
Advanced 8 steps

Share this explainer

Here's the card — post it anywhere.

Per-request Postgres connections in Express — share card
Made with highlit — turn any snippet into a walkthrough like this in about a minute.
Explain your code