Connecting a database

Every route so far has returned hardcoded data. This part connects a real Postgres database using a connection pool, and rewrites the user route from part two to query it.

Why a pool, not a single connection

Opening a new database connection per request is slow and will exhaust your database's connection limit under load. A pool keeps a small set of connections open and hands them out as requests need them.

const { Pool } = require('pg');

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
});

Set DATABASE_URL in a .env file locally — never commit it. Most free Postgres hosts (including the one you'll deploy to in part six) give you this connection string directly.

Querying from a route

Replace the hardcoded user route with a real query, wrapped in the asyncHandler from part three so failures reach your error middleware:

app.get('/api/users/:id', asyncHandler(async (req, res) => {
  const { rows } = await pool.query(
    'SELECT id, name, email FROM users WHERE id = $1',
    [req.params.id]
  );

  if (rows.length === 0) {
    return res.status(404).json({ error: 'User not found' });
  }

  res.json(rows[0]);
}));

Try it yourself

Create a users table with id, name, and email columns, insert a couple of rows, and confirm the route above returns real data. Next, you'll protect routes like this one behind authentication.

FAQ

Do I need an ORM like Prisma or Sequelize instead of raw SQL? Not to follow this series — raw pg queries make exactly what's sent to the database explicit, which is the point here. An ORM adds real value on a larger project (migrations, model definitions, less boilerplate for common queries), but it's a layer on top of the same connection-pooling and parameterized-query concepts this part covers, not a replacement for understanding them.

How many connections should the pool hold? The Pool constructor's default (10) is a reasonable starting point for a small API — increasing it doesn't help once your database's own max-connections limit becomes the actual bottleneck. Tuning this only matters once real production load makes it a measured issue, not something to guess at upfront.

What happens if DATABASE_URL is missing at startup? The Pool constructor won't error immediately — the failure surfaces on the first actual query, as a connection error. Checking that process.env.DATABASE_URL exists before calling new Pool(...) fails faster and with a clearer message than waiting for the first request to hit it.