Week 7 database integration

Why this matters

Your Week 6 API kept equipment in an array and forgot everything on restart. Today that ends. By the end of the hour you restart your server, request /equipment, and the same rows come back — because they were never in the server at all.

You will also do something more interesting: send your own API a piece of equipment whose serial number is '; DROP TABLE equipment; -- and watch it get stored, harmlessly, as text.

The connection pool

Talking to PostgreSQL requires a connection: an open TCP conversation the server keeps state for. Opening one costs real time — a handshake and authentication on every request would dominate your response times — and a server that opens one per request will exhaust PostgreSQL's connection limit under load.

A connection pool solves both. It opens a small number of connections once, keeps them alive, and lends them out. Your code asks for a connection, uses it, and returns it. The Node driver for PostgreSQL is the pg package, and its Pool does this for you.

npm install pg
npm install --save-dev @types/pg

// src/db/pool.ts
import { Pool } from 'pg';

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

# .env  — never commit this file
DATABASE_URL=postgres://localhost:5432/maintenance

One pool for the whole process, created once and imported everywhere. Creating a pool per request is the same mistake as one connection per request, wearing a better hat.

pool.query() takes a connection from the pool, runs one statement, and returns it automatically. That covers almost everything. You only check a connection out by hand — pool.connect() — when several statements must run on the same connection, which is exactly the case for a transaction, because BEGIN and COMMIT are meaningless on different connections.

Parameterized queries and SQL injection

Here is the wrong way to put a value into a query — the way that reads most naturally in JavaScript:

// NEVER do this.
const sql = `SELECT id, name FROM equipment WHERE serial_number = '${userInput}'`;
await pool.query(sql);

If userInput is PMP-0003, that works. If a user sends PMP-0003'; DROP TABLE equipment; --, the string you build is:

SELECT id, name FROM equipment WHERE serial_number = 'PMP-0003'; DROP TABLE equipment; --'

The user's quote closed your quote. What followed stopped being a value and became a second statement, and -- commented out the remains of yours. The database receives two valid commands and obeys both. The table is gone.

> to_regclass('public.equipment') → null      // the table no longer exists

That is SQL injection: untrusted text escaping the value slot and being executed as SQL. It is not exotic. It is what happens by default when you build queries with string concatenation.

The fix is the $1 placeholders you met on Day 46, now sent from Node. You pass the SQL structure and the values as two separate arguments, and the driver sends them to PostgreSQL separately:

const result = await pool.query(
  'SELECT id, name FROM equipment WHERE serial_number = $1',
  [userInput],
);

[]   // zero rows; the table is untouched

The database planned the statement from the structure alone, then bound the value in afterwards. Nothing inside a value can change the shape of a statement it was never part of. That is why parameterized queries separate SQL structure from untrusted values, and it is the only acceptable way to get a value into a query.

Note what parameters are not: they carry values only. $1 cannot be a table or column name. Sorting by a user-supplied column means checking that value against a fixed list you wrote — never pasting it into the SQL.

The repository layer

Your Express routes should not contain SQL. Put every query for one resource in one module — a repository — that exposes functions in your domain's language and returns plain objects.

// src/db/equipment-repository.ts
import { pool } from './pool';

export async function findAll() {
  const result = await pool.query(
    'SELECT id, name, serial_number, status FROM equipment ORDER BY id',
  );
  return result.rows;
}

export async function findById(id: number) {
  const result = await pool.query(
    'SELECT id, name, serial_number, status FROM equipment WHERE id = $1',
    [id],
  );
  return result.rows[0];   // undefined when nothing matched
}

export async function create(input: { name: string; serialNumber: string; installedOn: string }) {
  const result = await pool.query(
    `INSERT INTO equipment (name, serial_number, installed_on)
     VALUES ($1, $2, $3)
     RETURNING id, name, serial_number, status`,
    [input.name, input.serialNumber, input.installedOn],
  );
  return result.rows[0];
}

Three things this buys you. Every query lives in one file, so an audit for string-built SQL is one file to read. Routes stay about HTTP — status codes, validation, shapes — which is the boundary Week 8 builds on. And when you decide to rename the database's serial_number to your API's serialNumber, there is exactly one place to do it rather than one per route.

result.rows is always an array. rows[0] is undefined when nothing matched, and that undefined is what your route turns into a 404 — the Day 46 lesson that UPDATE 0 means not-found, now in TypeScript. result.rowCount gives the number affected, which is what you check after an UPDATE or DELETE.

Database errors and cleanup

A rejected write arrives in Node as a thrown error carrying a SQLSTATE code in err.code. Codes are stable across versions; error messages are not, so never match on text.

try {
  await create(input);
} catch (err: any) {
  if (err.code === '23505') {
    // 23505 = unique_violation
    return res.status(409).json({ error: 'serial number already exists' });
  }
  throw err;
}

code: 23505 | constraint: equipment_serial_number_key

23505 is a duplicate key, 23503 a foreign key violation, 23502 a not-null violation, 23514 a check violation. Map the ones your API can explain to a 4xx; re-throw the rest so your central error handler from Day 40 returns a 500 — and never send the raw database message to the client, because it names your tables and constraints.

Cleanup matters most when you check a connection out by hand for a transaction. The release() must happen whether the work succeeded or failed, which is what finally is for:

export async function logMaintenance(equipmentId: number, notes: string, hours: number) {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    await client.query(
      `INSERT INTO maintenance_records (equipment_id, performed_on, hours_spent, notes)
       VALUES ($1, CURRENT_DATE, $2, $3)`,
      [equipmentId, hours, notes],
    );
    await client.query('UPDATE equipment SET status = $1 WHERE id = $2', ['maintenance', equipmentId]);
    await client.query('COMMIT');
  } catch (err) {
    await client.query('ROLLBACK');
    throw err;
  } finally {
    client.release();
  }
}

Forget release() and that connection never returns to the rack. It is invisible in testing and fatal in production: after a few failures the pool is empty and every request hangs waiting for a connection that is never coming back.

Walkthrough: replace one route

Convert GET /equipment only, and prove it before touching anything else.

1. npm install pg and npm install --save-dev @types/pg; create src/db/pool.ts as above.
2. Create src/db/equipment-repository.ts with findAll and findById.
3. In the route, delete the array lookup and await findAll() instead. The handler becomes async, and its await must be inside try/catch so a database failure reaches your error handler rather than crashing the process.
4. Start the server and request it:

curl -s localhost:3000/equipment

[{"id":1,"name":"Feed Pump 3","serial_number":"PMP-0003","status":"operational"}, ...]

5. Stop the server with Ctrl+C, start it again, and request it again. Same rows. That is the whole point of the week, visible in one command.

Your turn

Replace in-memory equipment CRUD with PostgreSQL.

1. Move POST, PATCH, and DELETE /equipment/:id into the repository as create, updateStatus, and remove. Every value goes through $1-style placeholders — grep your own code for backticks inside pool.query and remove every one.
2. Use RETURNING on create and updateStatus so the route can send back the saved row without a second query.
3. Make GET /equipment/:id return 404 when rows[0] is undefined, and DELETE return 404 when rowCount is 0.
4. Map 23505 to a 409 with a clear message. Re-throw everything else.
5. Seed with psql maintenance -f db/seed.sql, then rerun the endpoint tests you wrote in Week 6. They should pass unchanged — the API contract did not move, only the storage behind it.
6. Restart the server and rerun the tests. Record in db/notes.md that the data survived.
7. POST a piece of equipment whose name is '; DROP TABLE equipment; --. Then run psql maintenance -c '\dt' and SELECT name FROM equipment ORDER BY id DESC LIMIT 1;. The table is still there and the name is stored verbatim. Paste both outputs into db/notes.md.
8. Add logMaintenance from above and call it from one route, so at least one business operation is a real transaction.

Common pitfalls

• Quoting the placeholder. WHERE name = '$1' searches for the literal text $1. Placeholders are never quoted.
• Building a WHERE clause with string concatenation "just for the filter". That is the same hole with a smaller doorway. Build the parameter array alongside the clause.
• Missing client.release(). Put it in finally, always. A pool that empties makes every request hang.
• Committing .env. It holds your connection string. Add it to .gitignore before the first commit, not after.
• Expecting count(*) to be a number. pg returns PostgreSQL bigint as a string, so rows[0].count is '5'. Wrap it in Number().

Verify it yourself

Open today's reference, the PostgreSQL tutorial, and find where it discusses passing values into statements rather than writing them inline.

1. Look up SQLSTATE class 23 in the PostgreSQL error codes appendix. Find one code this lesson did not list and write down which constraint raises it.
2. This lesson claimed a parameter cannot be a table or column name. Confirm or challenge that from the documentation, and note what you would do instead for user-chosen sorting.

Add both to db/notes.md. You now have a full stack from HTTP to disk — Week 8 makes it secure.