An UPDATE returns { changes: 0 }. What does that usually mean in a PATCH route?

No row had that ID, so answer 404

Why use a transaction when placing an order?

So the order, its items and the stock change all together or not at all

To hide it from other users forever

So the order, its items and the stock change all together or not at all

Working with a Database

By Syncfusion®, Inc. · Last updated November 12, 2026

SQLite with node:sqlite, prepared statements and SQL injection, CRUD routes on a database, joins, transactions, repositories and migrations, and where PostgreSQL and ORMs fit.

What you will learn

Create tables and query them from Express
Use parameters to stop SQL injection
Build CRUD routes on a database
Join tables and use transactions
Organise database code in repositories

Working with a Database

Until now, the canteen's data lived in JavaScript arrays. Restart the server, and every order is gone.

Database = a program that stores data safely on disk and finds it fast.
SQL = the language most databases speak: SELECT, INSERT, UPDATE, DELETE.
Table = rows and columns, like a spreadsheet: one row per menu item.
SQLite = a small database that lives in one file. No separate server is needed.
Node.js 22.13 and later include SQLite as node:sqlite. There is nothing to install.
The same ideas work with PostgreSQL, MySQL and the tools built on them.

Simple lesson: A database keeps data safe when the server stops, and finds it quickly.

A Real-World Example

The canteen used to write orders on a whiteboard.

At the end of the day, someone wiped the board. All orders were lost.
Now they use a register book with fixed columns: token, student, item, quantity.
Every line follows the same columns, so totals are easy.
The book stays in the cupboard overnight.

The whiteboard is an array. The register book is a database table.

In this lesson, you will learn:

How to create tables and run SQL from Express.
How prepared statements keep queries safe from SQL injection.
How to build CRUD routes on a real database.
How to link tables with foreign keys and JOINs.
How transactions keep data correct.
How to organise database code, and where ORMs and other databases fit.

Your First Table

node:sqlite gives you DatabaseSync: a database you call without await.

new DatabaseSync("canteen.db") opens (or creates) the database file.
db.exec(sql) runs SQL that returns nothing, such as CREATE TABLE.
db.prepare(sql) makes a statement you can run many times.
statement.all() returns every row as an object. statement.get() returns one row or undefined.
statement.run() changes data and returns changes and lastInsertRowid.

The terminal shows an ExperimentalWarning. Real Node.js prints it too, because node:sqlite is still new.
? marks a parameter. The value is passed separately, never pasted into the SQL.
SQLite has no true/false type, so veg is stored as 1 or 0.

In this lab, the database lives in memory and resets when you press Run. On your computer, canteen.db is a real file that survives restarts.

Simple lesson: exec for setup, prepare + all, get or run for everything else.

Parameters and SQL Injection

SQL injection = an attacker types SQL into an input, and your code runs it.

It happens when you build SQL by joining strings with user input.
"... WHERE name = '" + req.query.name + "'" is unsafe.
An input like ' OR '1'='1 changes the meaning of the query.
Parameters (?, :name or $name) keep data and SQL apart. Always use them.
Parameters work for values only. Never use user input as a table or column name.

The attack on /unsafe returns every student's phone number.
The same text on /safe finds nothing, because it is treated as a plain name.

Never build SQL with + or template strings from request data. One unsafe query can leak or delete a whole database.

Simple lesson: Values always go in parameters, never in the SQL text.

CRUD Routes on a Database

The REST routes from Building REST APIs map straight onto SQL:

GET list: SELECT ... ORDER BY ... LIMIT ? OFFSET ?.
GET one: SELECT ... WHERE id = ?, and 404 when get() returns undefined.
POST: INSERT, then use lastInsertRowid as the new ID.
PATCH: UPDATE ... WHERE id = ?, and 404 when changes is 0.
DELETE: DELETE ... WHERE id = ?, then 204.
Constraint errors: UNIQUE and CHECK failures become 409 or 422.

The second POST breaks the UNIQUE rule, so the route answers 409.
changes === 0 tells you the row did not exist.

Simple lesson: Each REST route is one SQL statement, with parameters and a check on the result.

Linking Tables

Real data is connected. An order belongs to a student and holds many items.

Primary key = the ID column that names each row.
Foreign key = a column that points to a row in another table, such as order_items.order_id.
One-to-many = one order has many order items.
JOIN = a query that combines rows from linked tables.
Foreign keys stop orphans, such as an item for an order that does not exist.

Simple lesson: Keys link tables. JOIN brings linked rows back together.

Transactions

Some changes must happen all together, or not at all.

Placing an order: add the order, add its items, and reduce the stock.
If the stock check fails halfway, the half-made order must disappear.
Transaction = BEGIN, then your statements, then COMMIT. On any error, ROLLBACK.
After a rollback, the database is exactly as it was before BEGIN.
Keep transactions short. Never wait for a slow outside call inside one.

Rahul's order fails on the dosa. His idli is not taken from stock, and no order row stays.
/api/stock shows exactly one order: Meera's.

Simple lesson: Wrap changes that belong together in a transaction.

Organising Database Code

db.js opens the database and creates tables once. Every file imports db from it.
Repository = a module of named functions for one table, such as menuRepo.findById(id).
Routes call repositories. Only repositories contain SQL.
Migrations = numbered SQL files that change the schema step by step: 001-create-menu.sql, 002-add-veg.sql.
Seed data = starting rows for development, kept apart from migrations.
Add an index to columns you search often: CREATE INDEX idx_orders_student ON orders(student).

// repositories/menu.js
import { db } from "../db.js";

const byId = db.prepare("SELECT * FROM menu WHERE id = ?");
const list = db.prepare("SELECT * FROM menu ORDER BY name LIMIT ? OFFSET ?");

export const menuRepo = {
  findById: (id) => byId.get(id),
  list: (limit, offset) => list.all(limit, offset),
};

Simple lesson: SQL lives in repositories. Routes only call named functions.

Other Databases and ORMs

PostgreSQL and MySQL run as separate servers. Use a connection pool, and every query is awaited.
ORM (Object-Relational Mapper) = a library that writes SQL for you from JavaScript objects.
Prisma and Drizzle are popular ORMs. They also handle migrations and TypeScript types.
MongoDB stores JSON-like documents instead of tables. Mongoose is its common library.
Learn plain SQL first. ORMs are easier to use well when you know what they generate.

// PostgreSQL with the pg package: a pool and parameters ($1, $2 ...)
import pg from "pg";
const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL });

app.get("/api/menu/:id", async (req, res) => {
  const { rows } = await pool.query("SELECT * FROM menu WHERE id = $1", [req.params.id]);
  if (!rows[0]) return res.status(404).json({ error: "No such item" });
  res.json(rows[0]);
});

Easy way to remember: SQLite for learning and small apps, PostgreSQL for most production APIs, an ORM when the team wants one.

Simple lesson: The SQL ideas stay the same. Only the library and await change.

Common mistakes

Mistake	Wrong idea	Correct idea	What goes wrong
Building SQL with `+` and user input	Users only type names	Always use parameters	SQL injection leaks or deletes data
No `UNIQUE` or `CHECK` rules	The code checks everything	Let the database enforce rules too	Bad rows sneak in through bugs
Several related writes without a transaction	Each write is safe alone	Group them in a transaction	Half-finished orders and wrong stock
`get()` result used without a check	The row always exists	`get()` may return `undefined`	Crashes instead of 404
`SELECT *` sent to the client	All columns are public	Pick the columns to send	Private fields leak
SQL spread across route files	It is quicker	Keep SQL in repositories	Hard to change the schema
Opening a new database per request	It is simpler	Open once and share it	Slow requests, too many connections

Check Yourself

Exercises

What to Remember

A database keeps data safe across restarts. node:sqlite is built into Node.js 22.13+.
exec for setup, prepare with all, get and run for queries.
Always pass values as parameters. Never build SQL from user input.
Check get() for undefined and run().changes for 0, and answer 404.
Use foreign keys and JOINs for linked data, and transactions for changes that belong together.
Keep SQL in repositories. PostgreSQL and ORMs use the same ideas with await.

Next: Building REST APIs. You will design a complete JSON API for the canteen.