Databases with Postgres
Talk to a Postgres database through a pool using parameterised queries and transactions.
- Create one Pool and reuse it for every query.
- Use $1 placeholders instead of string interpolation.
- Wrap multi-step writes in begin, commit and rollback.
- Handle connection errors instead of letting them crash the server.
In plain English
A connection pool keeps a small number of open database connections and hands one out for each query, so your server does not open a new connection for every request. A parameterised query sends values separately from the SQL text, which keeps user input from being executed as commands. A transaction groups several changes so they either all succeed or all roll back together. Connection errors are normal and should be caught and retried rather than crashing the process.
Step by step
- 1Create a Pool once at startup with a connection limit.
- 2Run queries with $1 placeholders and a values array.
- 3Grab a dedicated client for transactions and release it in finally.
- 4Roll back on any error and rethrow so the caller can respond.
import { Pool } from 'pg'
const pool = new Pool({
connectionString: process.env.DATABASE_URL,
max: 10,
})
type Course = {
id: string
title: string
minutes: number
}
async function findCourse(id: string): Promise<Course | null> {
const result = await pool.query<Course>(
'select id, title, minutes from courses where id = $1',
[id],
)
return result.rows[0] ?? null
}
async function createCourse(course: Course): Promise<Course> {
const client = await pool.connect()
try {
await client.query('begin')
await client.query(
'insert into courses (id, title, minutes) values ($1, $2, $3)',
[course.id, course.title, course.minutes],
)
await client.query('commit')
return course
} catch (error) {
await client.query('rollback')
throw error
} finally {
client.release()
}
}Common mistakes
- Building a query with template literals allows a user to inject their own SQL commands.
- Forgetting client.release() slowly drains the pool until the server hangs.
- Catching a connection error and swallowing it hides a database outage from your logs.
- Opening a new Pool per request exhausts connections and defeats the point of pooling.
Use a parameter in a query
Write findById(pool, id) that selects a row by id using a $1 placeholder and returns the first row or null.
async function findById(pool, id) {
const result = await pool.query('select * from users where id = ' + id)
return result.rows[0] ?? null
}Why are parameterised queries safer than string interpolation?
Make it yours.
Keep a private note for this lesson, then turn the key ideas into flashcards you can revisit.
Lock in what you just learned.
Mark this lesson complete to save your place and move one step closer to the certificate.