Step 3
Connecting multiple PostgreSQL pools
30 min
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 PGmax_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.
Next
- 04-resource-table-ssot