Talking to a database

Why it matters

An API that keeps data in a JavaScript array forgets everything when it restarts, and it cannot be shared by two server instances. Real APIs store data in a database. Most of the work in a typical route is: validate the input, run one or two queries, and shape the rows into JSON. Doing that safely, without letting the caller inject SQL, is one of the most important habits a back-end developer can build.

The shape of a database-backed route

request -> validate params/body -> query the database -> shape rows -> response

Your route code talks to the database through a driverA library that lets your code talk to one kind of database, such as pg for PostgreSQL or better-sqlite3 for SQLite. (a library for one database) or a query builder / ORM on top of it. Some common choices:

ORMs such as Prisma, Drizzle and Sequelize generate SQL for you. They are useful, but you will debug them far better if you can read the SQL they produce.

Worked example: SQLite in a route

With npm install better-sqlite3:

import express from "express";
import Database from "better-sqlite3";

const db = new Database("courses.db");
db.exec(`CREATE TABLE IF NOT EXISTS lessons (
  id INTEGER PRIMARY KEY,
  course_id INTEGER NOT NULL,
  slug TEXT NOT NULL UNIQUE,
  title TEXT NOT NULL,
  position INTEGER NOT NULL
)`);

const listLessons = db.prepare(
  "SELECT slug, title FROM lessons WHERE course_id = ? ORDER BY position"
);

const app = express();

app.get("/courses/:id/lessons", (req, res) => {
  const courseId = Number(req.params.id);
  if (!Number.isInteger(courseId) || courseId < 1) {
    return res.status(400).json({ error: "Course id must be a positive integer." });
  }
  const rows = listLessons.all(courseId);
  res.json({ items: rows });
});

The ? is a placeholderA marker such as ? or $1 in prepared SQL whose value is sent separately, so input can never change the query's structure.. The SQL text is fixed when the statement is prepared, and the value is sent separately. Different drivers spell placeholders differently (? in SQLite and MySQL, $1, $2 in PostgreSQL), but the idea is the same.

SQL injection: why placeholders matter

Compare that with building the query from a string:

// DANGEROUS: never do this
const sql = "SELECT * FROM users WHERE email = '" + req.query.email + "'";

If the caller sends email=' OR '1'='1, the query becomes:

SELECT * FROM users WHERE email = '' OR '1'='1'

That returns every user. Worse payloads can read other tables or delete data. This is SQL injectionAn attack where input pasted into SQL text changes the query, for example to return every user or delete data., and it has been near the top of every web security list for over twenty years. The fix is not to "escape quotes carefully"; it is to always pass values as parameters:

const user = db.prepare("SELECT id, email FROM users WHERE email = ?").get(req.query.email);

Placeholders work for values only. You cannot use them for table or column names. If a caller chooses a sort column, map their input to a fixed list:

const SORTS = { title: "title", newest: "created_at DESC" };
const orderBy = SORTS[req.query.sort] ?? "title";

Joins and shaping rows

APIs often return data from more than one table. A lesson list that also shows the course title uses a JOIN:

SELECT l.slug, l.title, c.title AS course_title
FROM lessons AS l
JOIN courses AS c ON c.id = l.course_id
WHERE c.slug = ?
ORDER BY l.position;

Two shaping rules:

• Select only the columns you need. SELECT * on a users table can leak a password hash into a JSON response the day someone adds a column.
• Rename for the client. Database columns are often snake_case; JSON APIs often use camelCase. Do the mapping in one place.

Connections, pooling and N+1

Opening a database connection is slow. Servers create a connection poolA set of open database connections created once at start-up and reused across requests. once at start-up and reuse it, rather than connecting per request. Keep the pool (or the SQLite handle) at module scope, as in the example.

A classic performance bug is the N+1 queryA performance bug that runs one query for a list and then one more per item, instead of a join or a single IN query.: one query for a list of courses, then one more query per course for its lessons. For 50 courses that is 51 round trips. A JOIN, or one WHERE course_id IN (...) query, does it in one or two.

Common mistakes

• Concatenating user input into SQL. Use placeholders every time, even for "trusted" values.
• SELECT * straight into a response, exposing private columns.
• Connecting inside the handler instead of reusing a pool.
• N+1 queries in a loop.
• Missing indexes on columns you filter by (course_id, email), so queries get slower as data grows.
• Committing the database file or its credentials. The connection string goes in an environment variable.

Quick recap

• A route validates input, runs parameterised queries and shapes the rows.
• Placeholders (?, $1) keep values separate from SQL and prevent injection. Map any user-chosen identifiers to a fixed list.
• Select only the columns the client needs, and join instead of querying in a loop.
• Create the connection or pool once and keep credentials out of code.

Try it: the Do tab gives you a small SQLite database. Write the query that GET /courses/:slug/lessons would run.