Skip to content
Tutorials Center
My learning

Topics

Lesson 36 of 39 ยท Projects and Practice

Project: A REST API with Bun and SQLite

Build a complete notes REST API: project layout, SQLite schema, Bun.serve routes, validation, pagination, error handling and tests, with the full logic running on a Node stand-in.

In this project you build a small but real notes API: create, list, read, update and delete notes, with validation, pagination, proper status codes, JSON errors and automated tests. It uses everything from the last chapters: Bun.serve routes, request bodies, bun:sqlite and bun test.

Bun is not available on this site, so the Bun project files are read-only. The runnable block at the end contains the same handler logic on Node 24 with node:sqlite, and drives it with real HTTP-style requests, so you can run, break and fix the behavior. Only the two database calls and the server start differ.

The API we want

Method and path Purpose Success
GET /api/notes?limit=&offset= list notes, newest first 200 + array
POST /api/notes create { title, body } 201 + note
GET /api/notes/:id read one 200 or 404
PATCH /api/notes/:id change title and/or body 200 or 404
DELETE /api/notes/:id delete 204 or 404
GET /health liveness check 200

Errors always look like { "error": "message" } with a fitting status: 400 for bad input, 404 for a missing note, 405 for a wrong method, 500 for anything unexpected.

Project layout

TEXT
notes-api/
  package.json
  bunfig.toml
  src/
    db.ts        # opens SQLite, creates the table
    notes.ts     # data access functions (the only file with SQL)
    app.ts       # builds the routes; exports createServer()
    index.ts     # reads PORT, starts the server, handles shutdown
  src/app.test.ts

Keeping SQL in one file makes the rest easy to test and to change later (say, to Postgres). Create it with bun init, then no dependencies are needed at all.

src/db.ts

TypeScript
import { Database } from "bun:sqlite";

export function openDatabase(path = Bun.env.DB_PATH ?? "notes.db") {
  const db = new Database(path);
  db.run("PRAGMA journal_mode = WAL;");
  db.run(`
    CREATE TABLE IF NOT EXISTS notes (
      id         INTEGER PRIMARY KEY AUTOINCREMENT,
      title      TEXT NOT NULL,
      body       TEXT NOT NULL DEFAULT '',
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      updated_at TEXT NOT NULL DEFAULT (datetime('now'))
    )
  `);
  return db;
}

src/notes.ts

TypeScript
import type { Database } from "bun:sqlite";

export type Note = { id: number; title: string; body: string; created_at: string; updated_at: string };

export function createNotes(db: Database) {
  const insert = db.query("INSERT INTO notes (title, body) VALUES ($title, $body) RETURNING *");
  const list = db.query("SELECT * FROM notes ORDER BY id DESC LIMIT $limit OFFSET $offset");
  const one = db.query("SELECT * FROM notes WHERE id = $id");
  const remove = db.query("DELETE FROM notes WHERE id = $id");
  const update = db.query(`
    UPDATE notes
       SET title = COALESCE($title, title),
           body = COALESCE($body, body),
           updated_at = datetime('now')
     WHERE id = $id
     RETURNING *`);

  return {
    create: (title: string, body: string) => insert.get({ $title: title, $body: body }) as Note,
    list: (limit: number, offset: number) => list.all({ $limit: limit, $offset: offset }) as Note[],
    find: (id: number) => (one.get({ $id: id }) as Note | null) ?? null,
    update: (id: number, title: string | null, body: string | null) =>
      (update.get({ $id: id, $title: title, $body: body }) as Note | null) ?? null,
    remove: (id: number) => remove.run({ $id: id }).changes > 0,
  };
}

RETURNING * gives back the saved row in one round trip, so no second SELECT is needed. Statements are created once and reused.

src/app.ts

TypeScript
import type { Database } from "bun:sqlite";
import { createNotes } from "./notes";

const json = (data: unknown, status = 200) => Response.json(data, { status });
const fail = (status: number, error: string) => json({ error }, status);

function parseId(raw: string): number | null {
  return /^\d+$/.test(raw) ? Number(raw) : null;
}

function clampInt(raw: string | null, fallback: number, min: number, max: number) {
  const n = Number(raw);
  return Number.isInteger(n) ? Math.min(max, Math.max(min, n)) : fallback;
}

async function readJson(req: Request): Promise<Record<string, unknown> | null> {
  try {
    const body = await req.json();
    return typeof body === "object" && body !== null && !Array.isArray(body) ? body : null;
  } catch {
    return null;
  }
}

export function createServer(db: Database, port = 0) {
  const notes = createNotes(db);

  return Bun.serve({
    port,
    maxRequestBodySize: 64 * 1024,
    routes: {
      "/health": () => json({ status: "ok" }),

      "/api/notes": {
        GET: (req) => {
          const url = new URL(req.url);
          const limit = clampInt(url.searchParams.get("limit"), 20, 1, 100);
          const offset = clampInt(url.searchParams.get("offset"), 0, 0, 1_000_000);
          return json(notes.list(limit, offset));
        },
        POST: async (req) => {
          const body = await readJson(req);
          if (!body) return fail(400, "Body must be a JSON object");
          const { title, body: text = "" } = body;
          if (typeof title !== "string" || title.trim() === "") return fail(400, "title is required");
          if (title.length > 200) return fail(400, "title must be at most 200 characters");
          if (typeof text !== "string") return fail(400, "body must be a string");
          return json(notes.create(title.trim(), text), 201);
        },
      },

      "/api/notes/:id": {
        GET: (req) => {
          const id = parseId(req.params.id);
          if (id === null) return fail(400, "id must be a number");
          return notes.find(id) ? json(notes.find(id)) : fail(404, "Note not found");
        },
        PATCH: async (req) => {
          const id = parseId(req.params.id);
          if (id === null) return fail(400, "id must be a number");
          const body = await readJson(req);
          if (!body) return fail(400, "Body must be a JSON object");
          const title = body.title ?? null;
          const text = body.body ?? null;
          if (title !== null && (typeof title !== "string" || title.trim() === "")) return fail(400, "title must be a non-empty string");
          if (text !== null && typeof text !== "string") return fail(400, "body must be a string");
          const updated = notes.update(id, title === null ? null : (title as string).trim(), text as string | null);
          return updated ? json(updated) : fail(404, "Note not found");
        },
        DELETE: (req) => {
          const id = parseId(req.params.id);
          if (id === null) return fail(400, "id must be a number");
          return notes.remove(id) ? new Response(null, { status: 204 }) : fail(404, "Note not found");
        },
      },
    },
    fetch: () => fail(404, "Route not found"),
    error(err) {
      console.error(JSON.stringify({ level: "error", msg: err.message }));
      return fail(500, "Internal server error");
    },
  });
}

Notice the structure: tiny helpers (json, fail, parseId, readJson) remove repetition, every route validates before touching the database, and the server takes its database as a parameter (dependency injection), so tests can pass an in-memory one.

src/index.ts

TypeScript
import { createServer } from "./app";
import { openDatabase } from "./db";

const db = openDatabase();
const server = createServer(db, Number(Bun.env.PORT ?? 3000));
console.log(`notes api on ${server.url}`);

process.on("SIGTERM", async () => {
  await server.stop();
  db.close();
  process.exit(0);
});

Run it with bun run src/index.ts and try curl -X POST localhost:3000/api/notes -H 'content-type: application/json' -d '{"title":"First"}'.

src/app.test.ts

TypeScript
import { afterAll, beforeAll, beforeEach, expect, test } from "bun:test";
import { Database } from "bun:sqlite";
import { createServer } from "./app";

let db: Database, server: ReturnType<typeof createServer>, base: string;

beforeAll(() => {
  db = new Database(":memory:");
  db.run(`CREATE TABLE notes (id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT NOT NULL,
    body TEXT NOT NULL DEFAULT '', created_at TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at TEXT NOT NULL DEFAULT (datetime('now')))`);
  server = createServer(db, 0);
  base = `http://localhost:${server.port}`;
});
beforeEach(() => db.run("DELETE FROM notes"));
afterAll(() => server.stop(true));

const post = (body: unknown) =>
  fetch(`${base}/api/notes`, { method: "POST", headers: { "content-type": "application/json" }, body: JSON.stringify(body) });

test("creates and reads a note", async () => {
  const res = await post({ title: "  Milk  ", body: "2 liters" });
  expect(res.status).toBe(201);
  const note = await res.json();
  expect(note.title).toBe("Milk");
  const again = await fetch(`${base}/api/notes/${note.id}`);
  expect(await again.json()).toMatchObject({ title: "Milk", body: "2 liters" });
});

test("rejects a missing title", async () => {
  expect((await post({ body: "no title" })).status).toBe(400);
});

test("404 for unknown note, 400 for bad id", async () => {
  expect((await fetch(`${base}/api/notes/999`)).status).toBe(404);
  expect((await fetch(`${base}/api/notes/abc`)).status).toBe(400);
});

Run the same logic here

The block below is the project's behavior on Node: the same validation helpers, the same SQL (RETURNING, COALESCE) on node:sqlite, and a mini router so you can call it. Try changing a validation rule and watch the status codes change.

Node.js The notes API logic end to end (Node stand-in)

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

Ideas to extend it

  • Add a tags table and GET /api/notes?tag=work using a join.
  • Add authentication: a users table, Bun.password, a session cookie and an owner_id column.
  • Add full-text search with SQLite FTS5 (available in bun:sqlite's SQLite build; check your Bun version).
  • Add ETag headers and answer 304 Not Modified.
  • Dockerize it with the Dockerfile from chapter 8.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why does the server factory take the database as a parameter?

    Injecting the dependency lets tests use :memory: while production uses a file.
  2. 2. Which status does a successful DELETE return in this API?

    204 No Content: the note is gone and there is nothing to send back.
  3. 3. What does RETURNING * save?

    The saved row comes back from the same statement.
  4. 4. Why clamp limit to 1..100 for the list endpoint?

    Bounding user-controlled sizes protects the server.

Summary

  • Keep SQL in one data-access module, validation at the edge of each route, and wiring in createServer(db, port).
  • Use correct status codes and one JSON error shape.
  • Bound everything the client controls: body size, page size, string length.
  • Test the real server on port 0 with an in-memory database.
  • The pieces from earlier lessons compose into a complete, dependency-free service.

Next: Project: A Link Shortener