# Working with a Database

Course: [Express.js](https://www.devtutorials.ai/learn/expressjs) · Section: Data Handling
Source: https://www.devtutorials.ai/learn/expressjs/data-handling/working-with-a-database

> 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.

## 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.

1. At the end of the day, someone wiped the board. All orders were lost.
2. Now they use a register book with fixed columns: token, student, item, quantity.
3. Every line follows the same columns, so totals are easy.
4. 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:

1. How to create tables and run SQL from Express.
2. How prepared statements keep queries safe from SQL injection.
3. How to build CRUD routes on a real database.
4. How to link tables with foreign keys and JOINs.
5. How transactions keep data correct.
6. 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)`.

```js
// 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 `await`ed.
- **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.

```js
// 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

<div className="tscroll">

| 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 |

</div>

## 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.