Node.js Project: Build a Notes REST API with Express and SQLite

npm init -y
npm pkg set type=module
npm install express zod
npm install -D supertest

notes-api/
├── package.json
├── src/
│   ├── db.js        database connection and schema
│   ├── app.js       the Express app (routes, validation, errors)
│   └── server.js    starts the server
└── test/
    └── notes.test.js

import { DatabaseSync } from "node:sqlite";

export function openDatabase(file = "notes.db") {
  const db = new DatabaseSync(file);
  db.exec(`
    CREATE TABLE IF NOT EXISTS notes (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      title TEXT NOT NULL,
      body TEXT NOT NULL DEFAULT '',
      tags TEXT NOT NULL DEFAULT '[]',
      pinned INTEGER NOT NULL DEFAULT 0
    )
  `);
  return db;
}

export function toNote(row) {
  return { id: row.id, title: row.title, body: row.body, tags: JSON.parse(row.tags), pinned: row.pinned === 1 };
}

import express from "express";
import { z } from "zod";
import { toNote } from "./db.js";

const NoteInput = z.object({
  title: z.string().trim().min(1).max(120),
  body: z.string().max(10_000).default(""),
  tags: z.array(z.string().trim().toLowerCase().min(1).max(30)).max(10).default([]),
  pinned: z.boolean().default(false),
});

class HttpError extends Error {
  constructor(status, message, details) {
    super(message);
    this.status = status;
    this.details = details;
  }
}

function parse(schema, data) {
  const result = schema.safeParse(data);
  if (!result.success) {
    throw new HttpError(400, "Validation failed", result.error.issues.map((i) => `${i.path.join(".")}: ${i.message}`));
  }
  return result.data;
}

export function createApp(db) {
  const app = express();
  app.use(express.json({ limit: "50kb" }));

  const getRow = (id) => {
    const row = db.prepare("SELECT * FROM notes WHERE id = ?").get(id);
    if (!row) throw new HttpError(404, "Note not found");
    return row;
  };

  // List: ?q=search&tag=work&page=1&limit=10 (pinned notes first)
  app.get("/api/notes", (req, res) => {
    const { q = "", tag = "" } = req.query;
    const page = Math.max(1, Number(req.query.page) || 1);
    const limit = Math.min(50, Math.max(1, Number(req.query.limit) || 10));
    const where = "WHERE (title LIKE :q OR body LIKE :q) AND (:tag = '' OR EXISTS (SELECT 1 FROM json_each(tags) WHERE value = :tag))";
    const params = { q: `%${q}%`, tag: tag.toLowerCase() };
    const total = db.prepare(`SELECT COUNT(*) AS n FROM notes ${where}`).get(params).n;
    const rows = db
      .prepare(`SELECT * FROM notes ${where} ORDER BY pinned DESC, id DESC LIMIT :limit OFFSET :offset`)
      .all({ ...params, limit, offset: (page - 1) * limit });
    res.json({ data: rows.map(toNote), page, limit, total });
  });

  app.get("/api/notes/:id", (req, res) => {
    res.json({ data: toNote(getRow(Number(req.params.id))) });
  });

  app.post("/api/notes", (req, res) => {
    const note = parse(NoteInput, req.body);
    const { lastInsertRowid } = db
      .prepare("INSERT INTO notes (title, body, tags, pinned) VALUES (?, ?, ?, ?)")
      .run(note.title, note.body, JSON.stringify(note.tags), note.pinned ? 1 : 0);
    res.status(201).json({ data: toNote(getRow(lastInsertRowid)) });
  });

  app.patch("/api/notes/:id", (req, res) => {
    const current = toNote(getRow(Number(req.params.id)));
    const changes = parse(NoteInput.partial(), req.body);
    const next = { ...current, ...Object.fromEntries(Object.entries(changes).filter(([key]) => key in req.body)) };
    db.prepare("UPDATE notes SET title = ?, body = ?, tags = ?, pinned = ? WHERE id = ?")
      .run(next.title, next.body, JSON.stringify(next.tags), next.pinned ? 1 : 0, current.id);
    res.json({ data: next });
  });

  app.delete("/api/notes/:id", (req, res) => {
    getRow(Number(req.params.id));
    db.prepare("DELETE FROM notes WHERE id = ?").run(Number(req.params.id));
    res.sendStatus(204);
  });

  app.use((req, res) => res.status(404).json({ error: "Route not found" }));

  app.use((err, req, res, next) => {
    if (err.type === "entity.parse.failed") return res.status(400).json({ error: "Invalid JSON" });
    const status = err.status ?? 500;
    if (status >= 500) console.error(err);
    res.status(status).json({ error: status >= 500 ? "Internal server error" : err.message, ...(err.details && { details: err.details }) });
  });

  return app;
}

import { createApp } from "./app.js";
import { openDatabase } from "./db.js";

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

node src/server.js

curl -s -X POST localhost:3000/api/notes -H "Content-Type: application/json" -d '{"title":"Groceries","body":"Rice, dal, tomatoes","tags":["Home","shopping"]}'
curl -s -X POST localhost:3000/api/notes -H "Content-Type: application/json" -d '{"title":"Sprint review","body":"Demo the notes API","tags":["work"],"pinned":true}'
curl -s -X POST localhost:3000/api/notes -H "Content-Type: application/json" -d '{"title":"Book list","body":"The Guide, Godan","tags":["reading"]}'
curl -s -X POST localhost:3000/api/notes -H "Content-Type: application/json" -d '{"title":"","tags":"work"}'

curl -s "localhost:3000/api/notes?limit=2"
curl -s "localhost:3000/api/notes?q=dal"
curl -s "localhost:3000/api/notes?tag=WORK"
curl -s -X PATCH localhost:3000/api/notes/1 -H "Content-Type: application/json" -d '{"pinned":true}'
curl -s -o /dev/null -w "%{http_code}\n" -X DELETE localhost:3000/api/notes/3
curl -s localhost:3000/api/notes/3

import assert from "node:assert/strict";
import { beforeEach, test } from "node:test";
import request from "supertest";
import { createApp } from "../src/app.js";
import { openDatabase } from "../src/db.js";

let app;
beforeEach(() => {
  app = createApp(openDatabase(":memory:"));        // a fresh, empty database for every test
});

const create = (note) => request(app).post("/api/notes").send(note);

test("creates a note with defaults", async () => {
  const res = await create({ title: "Hello" });
  assert.equal(res.status, 201);
  assert.deepEqual(res.body.data, { id: 1, title: "Hello", body: "", tags: [], pinned: false });
});

test("validates input", async () => {
  const res = await create({ title: "", tags: "work" });
  assert.equal(res.status, 400);
  assert.equal(res.body.details.length, 2);
});

test("searches title and body, and filters by tag", async () => {
  await create({ title: "Groceries", body: "rice and dal", tags: ["home"] });
  await create({ title: "Dal recipe", tags: ["cooking"] });
  await create({ title: "Taxes", tags: ["home"] });
  const search = await request(app).get("/api/notes?q=dal");
  assert.equal(search.body.total, 2);
  const home = await request(app).get("/api/notes?tag=home");
  assert.deepEqual(home.body.data.map((n) => n.title), ["Taxes", "Groceries"]);
});

test("pinned notes are listed first", async () => {
  await create({ title: "Old but pinned", pinned: true });
  await create({ title: "Newer" });
  const res = await request(app).get("/api/notes");
  assert.equal(res.body.data[0].title, "Old but pinned");
});

test("patch changes only the given fields", async () => {
  await create({ title: "Draft", body: "keep me", tags: ["x"] });
  const res = await request(app).patch("/api/notes/1").send({ title: "Final" });
  assert.deepEqual(res.body.data, { id: 1, title: "Final", body: "keep me", tags: ["x"], pinned: false });
});

test("deleted notes are gone", async () => {
  await create({ title: "Temp" });
  assert.equal((await request(app).delete("/api/notes/1")).status, 204);
  assert.equal((await request(app).get("/api/notes/1")).status, 404);
});

node --test