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 driver (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 placeholder. 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 injection, 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 pool 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 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.