Node.js Backend Fundamentals with Express · Data, Auth, Tests and Production
Persisting Data with SQLite and Parameterised Queries
Store data with Node's built-in SQLite module, use parameterised queries, and keep SQL out of route handlers.
What you'll build
A notes API backed by a database, using node:sqlite (built into Node.js) so the example needs no server. Queries live in a small repository module; handlers only call it. The same structure applies with PostgreSQL drivers (pg, postgres) or an ORM.
The idea
- Keep data access in one module (a repository) with functions like
create,findById,list. - Always use parameterised queries (
?placeholders): the database receives values separately from SQL, so input can't change the query. - Map database rows to API responses explicitly; don't leak columns such as
deleted_ator internal IDs by accident. - Use transactions for multi-step changes.
The code
import express from "express";
import { DatabaseSync } from "node:sqlite";
const db = new DatabaseSync(":memory:");
db.exec(`CREATE TABLE notes (id INTEGER PRIMARY KEY, owner TEXT NOT NULL, text TEXT NOT NULL, created TEXT NOT NULL)`);
const notes = {
create: (owner, text) => {
const { lastInsertRowid } = db.prepare("INSERT INTO notes (owner, text, created) VALUES (?, ?, ?)")
.run(owner, text, "2026-01-15T10:00:00Z");
return notes.findById(Number(lastInsertRowid));
},
findById: (id) => db.prepare("SELECT id, owner, text FROM notes WHERE id = ?").get(id),
listFor: (owner) => db.prepare("SELECT id, text FROM notes WHERE owner = ? ORDER BY id").all(owner),
};
const app = express();
app.use(express.json());
app.post("/users/:user/notes", (req, res) => res.status(201).json(notes.create(req.params.user, req.body.text)));
app.get("/users/:user/notes", (req, res) => res.json(notes.listFor(req.params.user)));
const server = app.listen(0);
const base = `http://127.0.0.1:${server.address().port}`;
const post = (user, text) => fetch(`${base}/users/${user}/notes`, {
method: "POST", headers: { "Content-Type": "application/json" }, body: JSON.stringify({ text }) });
await post("ana", "buy milk");
await post("ana", "call Ben");
await post("ben", "fix bug");
console.log(JSON.stringify(await (await fetch(`${base}/users/ana/notes`)).json()));
// an injection attempt is just a strange user name:
console.log(JSON.stringify(await (await fetch(`${base}/users/${encodeURIComponent("x' OR '1'='1")}/notes`)).json()));
server.close();
Output
[{"id":1,"text":"buy milk"},{"id":2,"text":"call Ben"}]
[]Run this on your computer — see “Get Started” for setup.
Common mistakes
- Building SQL with template strings:
`... WHERE owner = '${user}'`is SQL injection. - Opening a new database connection per request instead of using a pool (for network databases).
- Returning raw rows with every column, including private ones.
Key points
- Put queries in a repository module; handlers stay small.
- Parameterised queries for every value, always.
- Choose returned fields explicitly.
Further reading: Node.js SQLite · OWASP SQL Injection Prevention
Practice questions
Why do parameterised queries prevent SQL injection?
Answer
Values are sent separately from the SQL text, so the database never interprets user input as SQL.
Why keep queries in a repository module rather than in handlers?
Answer
It separates HTTP concerns from data access, keeps handlers small, and makes queries easy to test, reuse and change.
What should you use instead of opening a database connection per request?
Answer
A connection pool created at startup and shared by all requests (for network databases like PostgreSQL).
Open in the app to mark lessons complete, take notes and track your progress.