Design REST routes, validate JSON, build Node and Express APIs on a SQL database, secure them, test them and ship them.

Back-End Web APIs

Install the current LTS version of Node.js from nodejs.org (or with a version manager such as nvm or fnm). Run `node --version` and `npm --version` and note both numbers.

Create an empty folder called `hello-api`, open a terminal in it and run `npm init -y`. Open the new `package.json` and read each field.

Add `"type": "module"` to `package.json`, and a `scripts` entry `"start": "node hello.js"`.

Create `hello.js` that prints a greeting using `process.argv[2]` (with a default) and `process.version`. Run it with `node hello.js YourName`, then with `npm start`.

Run `npm install express`. Check that `express` appears under `dependencies` in `package.json`, and that `node_modules/` and `package-lock.json` now exist.

Create a `.gitignore` containing `node_modules/` and `.env`. Create a `.env` file with a fake value such as `GREETING=hi` and a `.env.example` with `GREETING=`.

Run `git init` and `git status`. Confirm that `node_modules/` and `.env` are NOT listed, while `package.json`, `package-lock.json`, `.gitignore`, `.env.example` and `hello.js` are.

Delete the `node_modules` folder and run `npm ci`. Confirm the script still runs: the lock file rebuilt exactly the same dependencies.

## Why it matters

JavaScript started in the browser, but **Node.js** lets you run it anywhere: on your laptop, on a server, or as a serverless function. It is one of the most common back-end runtimes, so a front-end developer who knows JavaScript can write an API without learning a new language. **npm** (the Node package manager) comes with Node and gives you access to millions of open-source packages, including the Express framework you will use in the next lesson.

## What Node is (and is not)

Node is a JavaScript **runtime**: the V8 engine from Chrome plus libraries for files, networking and processes. Compared with the browser:

- There is no `document` or `window`. There is `process` (arguments, environment variables, exit codes), `fs` (files), `http` (servers) and more.
- Node runs on **one main thread** with an **event loop**. While it waits for a database or a file, it handles other requests. That is why most Node APIs are asynchronous (callbacks, promises, `async`/`await`). Code that blocks the thread, such as a huge synchronous loop, blocks every request.

## Installing Node

Install an **LTS** (long-term support) release, which is the version most companies run. Use the installer from [nodejs.org](https://nodejs.org/), or a version manager such as `nvm` (macOS/Linux) or `nvm-windows`/`fnm` (Windows) so you can switch versions per project:

```bash
node --version   # e.g. v22.x
npm --version
```

## Running a script

Create `hello.js`:

```javascript
const name = process.argv[2] ?? "world";
console.log(`Hello, ${name}! Node ${process.version} on ${process.platform}`);
```

Run it:

```bash
node hello.js Ada
# Hello, Ada! Node v22.11.0 on darwin
```

`process.argv` holds the command-line arguments: the Node binary, the script path, then yours.

## A project with package.json

Every Node project has a `package.json` describing it. Create a folder and initialise it:

```bash
mkdir hello-api && cd hello-api
npm init -y
```

The generated file is plain JSON. Two fields are worth adding straight away:

```json
{
  "name": "hello-api",
  "version": "1.0.0",
  "type": "module",
  "scripts": {
    "start": "node index.js",
    "dev": "node --watch index.js"
  }
}
```

- `"type": "module"` lets you use `import`/`export` (ES modules) instead of `require`.
- `scripts` are shortcuts: `npm start` runs `node index.js`, `npm run dev` restarts on every save.

## Installing a package

```bash
npm install express
```

This does three things:

1. Downloads the package and everything it depends on into `node_modules/`.
2. Adds `"express": "^5.x.x"` under `dependencies` in `package.json`.
3. Writes `package-lock.json`, which records the **exact** version of every package installed.

Tools you only need while developing (test runners, linters) go in `devDependencies`: `npm install --save-dev vitest`.

The `^` in `^5.1.0` means "any 5.x version from 5.1.0 up". That follows **semantic versioning**: `MAJOR.MINOR.PATCH`, where a major bump may break your code. The lock file is what keeps every machine on the same versions, so **commit `package-lock.json`** and use `npm ci` in CI and deployments; it installs exactly what the lock file says.

## Environment variables and secrets

Configuration that differs between machines, such as a port, a database URL or an API key, goes in **environment variables**, not in code:

```javascript
const port = Number(process.env.PORT ?? 3000);
```

Locally, many projects keep these in a `.env` file. Newer Node versions can load one with `node --env-file=.env index.js`. The `.env` file holds secrets, so it must **never** be committed.

## What to commit

Create a `.gitignore` before your first commit:

```text
node_modules/
.env
```

- `node_modules/` can be rebuilt from the lock file and can hold tens of thousands of files.
- `.env` holds secrets. If a secret is ever pushed, deleting the file later is not enough; it is in the history. Rotate (replace) the secret immediately.

A common pattern is to commit a `.env.example` with the variable **names** and fake values, so teammates know what to set.

## Choosing packages carefully

Every dependency is code you run with full access to your machine and your server. Before you install one, check that it is maintained, widely used, and spelled correctly (attackers publish "typosquat" packages with near-identical names). `npm audit` reports known vulnerabilities in what you have installed.

## Common mistakes

- **Committing `node_modules/` or `.env`.**
- **Not committing `package-lock.json`**, so teammates and servers get different versions.
- **Using `npm install` in CI** instead of `npm ci`.
- **Installing globally** (`npm install -g`) for project tools. Use `npx <tool>` or a local devDependency.
- **Blocking the event loop** with heavy synchronous work in a request handler.

## Quick recap

- Node runs JavaScript outside the browser on a single-threaded event loop.
- `package.json` describes the project and its scripts; `package-lock.json` pins exact versions. Commit both.
- `npm install <pkg>` adds a dependency, `npm ci` reproduces the lock file exactly.
- Configuration and secrets live in environment variables; `.env` and `node_modules/` go in `.gitignore`.

**Try it:** the **Do** tab walks you through installing Node, creating a project, adding a package and running your first script.


Run JavaScript on your computer with Node.js and manage packages, scripts and secrets with npm.

Node.js and npm

Node.js Ultimate Beginner’s Guide in 7 Easy Steps

npm for absolute beginners

## Why it matters

Node's built-in `http` module can run a server, but it leaves you to parse URLs, read bodies and route requests by hand. **Express** is the most widely used Node web framework. It adds two ideas that almost every back-end framework shares, in any language: **routes** (which code handles which method and path) and **middleware** (functions that run on the way to a route, such as logging, parsing JSON or checking a login). Learn them once and you will recognise them in Fastify, Koa, Django, Flask, Rails and ASP.NET.

## A minimal server

After `npm install express`, create `index.js`:

```javascript
import express from "express";

const app = express();
app.use(express.json()); // parse JSON request bodies into req.body

const courses = [
  { id: 1, title: "SQL Fundamentals" },
  { id: 2, title: "Back-End Web APIs" },
];

app.get("/courses", (req, res) => {
  res.json({ items: courses });
});

app.get("/courses/:id", (req, res) => {
  const id = Number(req.params.id);
  const course = courses.find((c) => c.id === id);
  if (!course) return res.status(404).json({ error: "Course not found." });
  res.json(course);
});

const port = Number(process.env.PORT ?? 3000);
app.listen(port, () => console.log(`Listening on http://localhost:${port}`));
```

Run `node index.js`, then in another terminal:

```bash
curl -i http://localhost:3000/courses/2
curl -i http://localhost:3000/courses/99
```

## Routes and handlers

`app.get(path, handler)` registers a **route**: when a `GET` request matches `path`, Express calls `handler(req, res)`. There is a method for each verb: `app.post`, `app.patch`, `app.delete`, and so on.

- `req.params` holds **path parameters**. In `/courses/:id`, a request to `/courses/2` gives `req.params.id === "2"`. It is always a **string**, so convert and validate it.
- `req.query` holds the query string: `/courses?level=beginner` gives `req.query.level`.
- `req.body` holds the parsed body, but only if a body-parsing middleware such as `express.json()` ran first.
- `res.status(code)` sets the status; `res.json(value)` sends JSON and ends the response. Send exactly **one** response per request. `return res.status(404)...` is a common pattern so the code below does not run too.

Routes are checked **in the order you register them**. Put `/courses/new` before `/courses/:id`, or `:id` will match the word `new`.

## How route matching works

Under the hood, matching a pattern is simple string work. Split both paths on `/`, compare segment by segment, and treat any segment starting with `:` as a named parameter:

```javascript
// pattern "/courses/:id/lessons", path "/courses/7/lessons"
// segments: ["courses", ":id", "lessons"] vs ["courses", "7", "lessons"]
// -> match, params = { id: "7" }
```

If the segment counts differ, or a fixed segment differs, there is no match. You will write this function yourself in the activity.

## Middleware

A **middleware** is a function `(req, res, next)`. It can read or change `req` and `res`, end the response itself, or call `next()` to pass control to the next function in the chain. `app.use(fn)` runs `fn` for every request; you can also attach middleware to one route.

```javascript
// Logs every request, then continues.
function logRequests(req, res, next) {
  const started = Date.now();
  res.on("finish", () => {
    console.log(`${req.method} ${req.originalUrl} ${res.statusCode} ${Date.now() - started}ms`);
  });
  next();
}

// Stops the chain unless an API key header is present.
function requireApiKey(req, res, next) {
  if (req.get("x-api-key") !== process.env.API_KEY) {
    return res.status(401).json({ error: "Missing or invalid API key." });
  }
  next();
}

app.use(logRequests);
app.post("/courses", requireApiKey, (req, res) => {
  res.status(201).json({ id: 3, title: req.body.title });
});
```

The order is the order of registration. `logRequests` runs first for everything; `requireApiKey` runs only for `POST /courses`, and if it sends a `401`, the handler never runs.

## Error-handling middleware

A middleware with **four** arguments `(err, req, res, next)` is an error handler. Express calls it when a handler throws or calls `next(err)`. Register it **last**:

```javascript
app.use((err, req, res, next) => {
  console.error(err); // full details go to your logs only
  res.status(500).json({ error: "Something went wrong." });
});
```

In Express 5, an `async` handler that rejects is passed to this handler automatically. In Express 4 you had to wrap async handlers or call `next(err)` yourself.

## Common mistakes

- **Forgetting `express.json()`**, so `req.body` is `undefined`.
- **Forgetting to call `next()`** in a middleware that does not respond, so the request hangs until it times out.
- **Sending two responses** ("Cannot set headers after they are sent"): usually a missing `return`.
- **Route order**: a broad pattern registered before a specific one swallows it.
- **Treating `req.params.id` as a number** without converting and validating it.
- **Sending error details to the client** instead of logging them.

## Quick recap

- A route pairs a method and a path with a handler `(req, res)`.
- `req.params`, `req.query` and `req.body` hold the input; all of it must be validated.
- Middleware `(req, res, next)` runs in order and either responds or calls `next()`.
- A four-argument middleware registered last handles errors.

**Try it:** in the **Do** tab you will write the route matcher and a tiny middleware runner, the two ideas at the heart of Express.


Write Express route handlers and middleware, and understand how requests flow through them.

Routes and middleware

Learn Express JS In 35 Minutes

Learn Express Middleware In 14 Minutes

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

```text
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:

| Database | Node driver | Notes |
|---|---|---|
| SQLite | `better-sqlite3`, or built-in `node:sqlite` in recent Node versions | A single file; great for learning and small apps |
| PostgreSQL | `pg` | The most common relational database for new APIs |
| MySQL | `mysql2` | Widely hosted |
| MongoDB | `mongodb` | A document database (not SQL) |

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

```javascript
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:

```javascript
// 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:

```sql
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**:

```javascript
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:

```javascript
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`:

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


Query a SQL database from a route with parameterised queries, joins and only the columns you need.

Talking to a database

Hacking Websites with SQL Injection - Computerphile

SQLite3 in Node with better-sqlite3

Set up a Node.js project with npm, write Express routes and middleware, and query a SQL database safely from a route.