Connecting to PostgreSQL

Set up a pg connection pool in a Koa 2 application, share the client through ctx.state or a module singleton, and run async queries safely inside handlers.

4 min read Level 2/5 #koa#data#postgres

What you'll learn

Create a pg Pool and connect it before the app starts accepting requests
Share a database client via ctx.state or a module-level singleton
Write async handlers that query Postgres and return JSON responses

Most production Koa apps talk to a relational database. The pg package provides a battle-tested Postgres client for Node and ships a built-in Pool that manages multiple connections automatically.

Installing pg

npm install pg

Creating a Pool

Create a dedicated module so the pool is initialised once and reused everywhere.

// db.js
import pg from 'pg';
const { Pool } = pg;

export const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 10,          // maximum connections in the pool
  idleTimeoutMillis: 30_000,
});

Call pool.query() directly for one-off queries; it borrows a connection, runs the query, and returns it automatically.

The simplest approach is importing the pool singleton directly into any handler file — no middleware needed.

// routes/users.js
import Router from '@koa/router';
import { pool } from '../db.js';

const router = new Router({ prefix: '/users' });

router.get('/', async (ctx) => {
  const { rows } = await pool.query('SELECT id, name, email FROM users ORDER BY id');
  ctx.body = rows;
});

router.get('/:id', async (ctx) => {
  const { rows } = await pool.query(
    'SELECT id, name, email FROM users WHERE id = $1',
    [ctx.params.id]
  );
  if (!rows.length) ctx.throw(404, 'User not found');
  ctx.body = rows[0];
});

export default router;

Always use parameterised queries ($1, $2, …) to prevent SQL injection. Never interpolate user input directly into a query string.

Attaching the Pool via Middleware

For larger apps you can attach the pool to ctx.state so every downstream middleware has access without explicit imports.

// app.js
import Koa from 'koa';
import { pool } from './db.js';

const app = new Koa();

app.use(async (ctx, next) => {
  ctx.state.db = pool;
  await next();
});

Handlers then read ctx.state.db instead of importing the pool — useful when you want to swap in a test database without touching individual route files.

Graceful Shutdown

Release pool resources when the process exits to avoid connection leaks.

process.on('SIGTERM', async () => {
  await pool.end();
  process.exit(0);
});

Up Next

Explore Prisma and Drizzle — ORM layers that give you type-safe queries and automatic migrations on top of the raw pg pool.

Using an ORM with Koa →