Connecting multiple PostgreSQL pools

To reach three domain DBs directly, you need three pools. The pg driver alone is enough.

1. Pool singletons

import { Pool } from 'pg';

function requireEnv(name: string): string {
  const v = process.env[name];
  if (!v) throw new Error(`env ${name} missing`);
  return v;
}

export const blogPool = new Pool({
  host: requireEnv('BLOG_DB_HOST'),
  port: Number(process.env.BLOG_DB_PORT ?? 5432),
  database: requireEnv('BLOG_DB_NAME'),
  user: requireEnv('BLOG_DB_USER'),
  password: requireEnv('BLOG_DB_PASSWORD'),
  max: 10,
});

export const marketPool = new Pool({ /* MARKET_DB_* */ });

requireEnv throws at module load, surfacing typos immediately.

2. Thin helpers

export async function queryBlog<T>(
  sql: string, params: unknown[] = []
): Promise<T[]> {
  const { rows } = await blogPool.query<T>(sql, params);
  return rows;
}

Callers:

const posts = await queryBlog<Post>(
  `SELECT id, title FROM posts WHERE published = true ORDER BY created_at DESC LIMIT $1`,
  [20]
);

3. Transactions

const client = await blogPool.connect();
try {
  await client.query('BEGIN');
  const { rows } = await client.query(
    `INSERT INTO posts (title, body) VALUES ($1, $2) RETURNING id`, [...]
  );
  await client.query('COMMIT');
} catch (e) {
  await client.query('ROLLBACK');
  throw e;
} finally {
  client.release();
}

Missing release() is the #1 cause of pool exhaustion.

4. Graceful shutdown

const pools = [blogPool, marketPool];
process.once('SIGTERM', async () => {
  await Promise.all(pools.map((p) => p.end()));
});

5. Example — posts page

export default async function Page() {
  const posts = await queryBlog<Post>(
    `SELECT id, title, published, created_at FROM posts
     ORDER BY created_at DESC LIMIT 50`
  );
  return <PostsView initialRows={posts} />;
}

Server Component talks to the pool directly and hands rows to a 'use client' view.

6. Gotchas

Typoed env names → requireEnv
Oversized max → exceed PG max_connections
Long-held transactions → starve other queries
SSL config mismatch across local / Docker / cloud

Closing

Pool singletons + thin helpers survive well without an ORM. Drizzle / TypeORM start paying off when joins grow or migrations need automation — for 3–5 tables, raw SQL is usually faster and easier to read.

04-resource-table-ssot