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.