@fastify/postgres

The @fastify/postgres plugin wraps the node-postgres pool and decorates both the app and each request, including helpers for per-request transactions.

4 min read Level 2/5 #fastify#postgres#pg

What you'll learn

Install @fastify/postgres and node-postgres
Register with a connection string or pool options
Use app.pg.query and per-request transactions

@fastify/postgres is the official wrapper for pg. It manages a pool, decorates the app with a query interface, and gives each request a transaction helper.

Install & Register

npm install @fastify/postgres pg
npm install -D @types/pg

import postgres from '@fastify/postgres'

await app.register(postgres, {
  connectionString: app.config.DATABASE_URL,
  max: 20,
})

Behind the scenes this creates a pg.Pool and closes it via onClose.

Query From a Handler

app.get('/users/:id', async (req) => {
  const { id } = req.params as { id: string }
  const { rows } = await app.pg.query(
    'SELECT id, email FROM users WHERE id = $1',
    [id],
  )
  return rows[0] ?? null
})

Parameterised queries are mandatory — never interpolate user input into SQL strings.

Per-Request Transactions

The plugin can also decorate request with a transaction helper:

app.post('/transfer', async (req) => {
  return req.pg.transact(async (client) => {
    await client.query('UPDATE accounts SET balance = balance - $1 WHERE id = $2', [100, 'a'])
    await client.query('UPDATE accounts SET balance = balance + $1 WHERE id = $2', [100, 'b'])
    return { ok: true }
  })
})

If the callback throws, the transaction rolls back automatically.

Prisma With Fastify →