阅读中文版本

Build a Contact Book REST API with SQLite

A small REST API for contacts backed by SQLite, with validated creation, case-insensitive search, pagination, and a migration step that creates the schema on first boot.

contact-api/
├── src/
│   ├── db.js
│   ├── migrations.js
│   ├── contacts.repo.js
│   ├── validation.js
│   ├── routes.js
│   └── server.js
├── test/
│   ├── repo.test.js
│   └── api.test.js
└── package.json

import { DatabaseSync } from 'node:sqlite';

export function openDatabase(path = ':memory:') {
  const db = new DatabaseSync(path);
  db.exec('PRAGMA foreign_keys = ON;');
  return {
    run: (sql, params = []) => db.prepare(sql).run(...params),
    all: (sql, params = []) => db.prepare(sql).all(...params),
    get: (sql, params = []) => db.prepare(sql).get(...params),
    close: () => db.close(),
  };
}

const MIGRATIONS = [
  {
    id: 1,
    up: `CREATE TABLE contacts (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL,
      email TEXT NOT NULL UNIQUE,
      phone TEXT,
      created_at TEXT NOT NULL
    );`,
  },
];

export function migrate(db) {
  db.run('CREATE TABLE IF NOT EXISTS migrations (id INTEGER PRIMARY KEY, applied_at TEXT)');
  const applied = new Set(db.all('SELECT id FROM migrations').map((r) => r.id));
  for (const m of MIGRATIONS) {
    if (applied.has(m.id)) continue;
    db.run(m.up);
    db.run('INSERT INTO migrations (id, applied_at) VALUES (?, ?)', [m.id, new Date().toISOString()]);
  }
  return MIGRATIONS.length;
}

export function createContactsRepo(db) {
  return {
    create(c) {
      const info = db.run(
        'INSERT INTO contacts (name, email, phone, created_at) VALUES (?, ?, ?, ?)',
        [c.name, c.email, c.phone ?? null, new Date().toISOString()],
      );
      return this.find(info.lastInsertRowid);
    },
    find(id) {
      return db.get('SELECT * FROM contacts WHERE id = ?', [id]) ?? null;
    },
    search(q, { limit = 20, offset = 0 } = {}) {
      const like = '%' + q.toLowerCase() + '%';
      return db.all(
        'SELECT * FROM contacts WHERE lower(name) LIKE ? OR lower(email) LIKE ? ORDER BY id LIMIT ? OFFSET ?',
        [like, like, limit, offset],
      );
    },
    remove(id) {
      return db.run('DELETE FROM contacts WHERE id = ?', [id]).changes;
    },
  };
}

const EMAIL = /^[^@\s]+@[^@\s]+\.[^@\s]+$/;

export function validateContact(input) {
  const errors = {};
  const name = typeof input.name === 'string' ? input.name.trim() : '';
  const email = typeof input.email === 'string' ? input.email.trim().toLowerCase() : '';
  const phone = typeof input.phone === 'string' ? input.phone.trim() : undefined;
  if (name.length < 2) errors.name = 'name must be at least 2 characters';
  if (!EMAIL.test(email)) errors.email = 'email is not valid';
  if (Object.keys(errors).length) return { ok: false, errors };
  return { ok: true, value: { name, email, phone } };
}

import { validateContact } from './validation.js';

export const routes = [
  {
    method: 'POST',
    pattern: /^\/contacts$/,
    async handler({ repo, body }) {
      const result = validateContact(body ?? {});
      if (!result.ok) return { status: 422, body: { error: 'validation_failed', fields: result.errors } };
      try {
        return { status: 201, body: await repo.create(result.value) };
      } catch (err) {
        if (String(err.message).includes('UNIQUE')) {
          return { status: 409, body: { error: 'email_exists' } };
        }
        throw err;
      }
    },
  },
  {
    method: 'GET',
    pattern: /^\/contacts$/,
    async handler({ repo, query }) {
      const q = query.get('q') ?? '';
      const limit = Math.min(100, Number(query.get('limit') ?? 20));
      const offset = Number(query.get('offset') ?? 0);
      return { status: 200, body: { items: repo.search(q, { limit, offset }) } };
    },
  },
];

import { openDatabase } from './db.js';
import { migrate } from './migrations.js';
import { createContactsRepo } from './contacts.repo.js';
import { createApi } from './api.js';

const db = openDatabase(process.env.DB_PATH ?? './contacts.db');
migrate(db);
const repo = createContactsRepo(db);
const server = createApi({ repo });

server.listen(process.env.PORT ?? 3000, () => {
  console.log('listening on ' + (process.env.PORT ?? 3000));
});

export { server };

Project overview

A contact book is small enough to build in an afternoon and complete enough to demonstrate every decision a real API makes: validation, persistence, search, pagination, and consistent errors. SQLite keeps the persistence real without asking the reader to run a database server.

The API opens one connection, applies migrations on boot, and keeps all SQL inside a repository so handlers stay thin. Validation happens before the repository is ever called.

The project is deliberately sized so that every stage ships something you can run: the first phase produces a working command or endpoint, and each later phase adds one capability without forcing a rewrite of what came before. That property — always runnable, never half-migrated — is what makes the tutorial usable as a weekend build rather than a sketch that only works once the final commit lands.

The finished tool carries no hidden services and no hosted dependencies. Everything it needs lives in the repository and a machine with the stated runtime, so a reader can clone, install, run the tests, and trust that the behaviour they see locally is the behaviour the tutorial promised. Where a real deployment needs an external system, that seam is called out explicitly rather than smuggled in through a default.

It is written for a working developer who wants the whole shape of a real tool in one readable pass rather than a tour of a framework. Each phase states the decision it makes and why, then shows the code that encodes it, so the reader can stop after any phase with something that runs and resume later without relearning the whole design. The prose is deliberately honest about trade-offs: where a simpler approach would break at scale, that ceiling is named instead of hidden behind a benchmark.

The HTTP layer stays a handful of lines because route matching and handlers are the only thing it owns:

import { createServer } from 'node:http';
import { routes } from './routes.js';

export function createApi({ repo }) {
  return createServer(async (req, res) => {
    const url = new URL(req.url, 'http://localhost');
    const route = routes.find(
      (r) => r.method === req.method && r.pattern.test(url.pathname),
    );
    if (!route) return send(res, 404, { error: 'not found' });
    const params = url.pathname.match(route.pattern).slice(1);
    const body = await readJson(req);
    const result = await route.handler({ repo, params, query: url.searchParams, body });
    return send(res, result.status ?? 200, result.body);
  });
}

Returning a result object from each handler rather than writing to the response keeps the handlers testable without a socket.

Setup

This project targets Node 22 because node:sqlite ships in the standard library, which removes the only common native dependency from the stack.

Set up the workspace before writing any logic. The table below pins every dependency to a version so the behaviour you observe matches the tutorial; floating versions are the most common reason a reader’s output diverges from the text. Install exactly what the table lists, then confirm the toolchain reports the expected version before continuing.

Two configuration choices deserve attention up front. First, the project keeps its runtime entry and its test entry as separate scripts so that running tests never starts a server or writes to a data directory. Second, all tunables are read from one place — a small config module or environment variables — so that nothing depends on the working directory a command happens to be launched from.

Create the project and install:

mkdir contact-api && cd contact-api
npm init -y
npm install -D vitest@^2.1.0

Configure package.json with the server entry and the test runner:

{
  "name": "contact-api",
  "type": "module",
  "scripts": {
    "start": "node src/server.js",
    "test": "vitest run"
  }
}

The database file path comes from an environment variable so tests open an in-memory database and never touch your development data.

Phase 1: Database connection

Open a SQLite database from a configurable path, enable foreign keys, and expose a thin query helper. Scoped this tightly, the phase ends with a runnable artifact and a test that proves it, so the next one starts from a known-good base.

The first hard decision is where the boundary between parsing and doing sits. Input arrives as raw text — argv, environment variables, a file on disk — and this phase converts it exactly once into a typed object that the rest of the program trusts. Confining that conversion to a single module means every later layer receives clean, already-validated input and never re-checks a string, which removes a whole class of bugs where two modules quietly disagree about what a flag or a field means. The second decision is failure visibility: invalid input must produce an actionable message and a non-zero exit rather than a stack trace surfacing from three layers down. Getting these two boundaries right early is what lets the remaining phases stay small.

import { DatabaseSync } from 'node:sqlite';

export function openDatabase(path = ':memory:') {
  const db = new DatabaseSync(path);
  db.exec('PRAGMA foreign_keys = ON;');
  return {
    run: (sql, params = []) => db.prepare(sql).run(...params),
    all: (sql, params = []) => db.prepare(sql).all(...params),
    get: (sql, params = []) => db.prepare(sql).get(...params),
    close: () => db.close(),
  };
}

Acceptance for this phase: Opening an in-memory database and running a trivial query works, and opening a file path creates the file on first use.

Phase 2: Migrations

Apply ordered migrations exactly once, tracking which have run in a migrations table so boot is idempotent. Scoped this tightly, the phase ends with a runnable artifact and a test that proves it, so the next one starts from a known-good base.

This phase defines the data model that every other module speaks. The choice of shape matters more than it looks: flat records are easy to serialize and diff, nested structures model relationships but complicate equality checks, and identifiers must be stable across edits so that references never silently rebind. The code here stays pure — functions take data and return data, with no clocks, no disk, and no printing — because pure functions are the only kind that can be tested exhaustively without a fixture harness. When the model is right the surrounding code almost writes itself; when it is wrong every later phase pays interest.

const MIGRATIONS = [
  {
    id: 1,
    up: `CREATE TABLE contacts (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL,
      email TEXT NOT NULL UNIQUE,
      phone TEXT,
      created_at TEXT NOT NULL
    );`,
  },
];

export function migrate(db) {
  db.run('CREATE TABLE IF NOT EXISTS migrations (id INTEGER PRIMARY KEY, applied_at TEXT)');
  const applied = new Set(db.all('SELECT id FROM migrations').map((r) => r.id));
  for (const m of MIGRATIONS) {
    if (applied.has(m.id)) continue;
    db.run(m.up);
    db.run('INSERT INTO migrations (id, applied_at) VALUES (?, ?)', [m.id, new Date().toISOString()]);
  }
  return MIGRATIONS.length;
}

Acceptance for this phase: Calling migrate twice applies each migration only once, and a fresh database ends up with both the migrations table and the contacts table.

Phase 3: Contacts repository

Implement CRUD and search as parameterised SQL inside one module so handlers never construct queries. Scoped this tightly, the phase ends with a runnable artifact and a test that proves it, so the next one starts from a known-good base.

The core operation is where the project delivers its value, and the temptation is to cram every option into one function. Resist it. The main path should read as a short, obvious sequence — load, transform, emit — with the branching behaviour factored into small decisions that can be tested on their own. This phase also fixes the error contract: which failures are fatal, which are recoverable, and how a partial result is reported. A tool that can explain what it did and why it stopped is far easier to trust in a pipeline than one that only signals success or silence.

export function createContactsRepo(db) {
  return {
    create(c) {
      const info = db.run(
        'INSERT INTO contacts (name, email, phone, created_at) VALUES (?, ?, ?, ?)',
        [c.name, c.email, c.phone ?? null, new Date().toISOString()],
      );
      return this.find(info.lastInsertRowid);
    },
    find(id) {
      return db.get('SELECT * FROM contacts WHERE id = ?', [id]) ?? null;
    },
    search(q, { limit = 20, offset = 0 } = {}) {
      const like = '%' + q.toLowerCase() + '%';
      return db.all(
        'SELECT * FROM contacts WHERE lower(name) LIKE ? OR lower(email) LIKE ? ORDER BY id LIMIT ? OFFSET ?',
        [like, like, limit, offset],
      );
    },
    remove(id) {
      return db.run('DELETE FROM contacts WHERE id = ?', [id]).changes;
    },
  };
}

Acceptance for this phase: Insert then find returns the same record, search matches on a case-insensitive substring, and deleting a missing id reports zero changes.

Phase 4: Validation

Normalise incoming contact payloads, reject missing or malformed fields with a structured error, and enforce a valid email shape. Scoped this tightly, the phase ends with a runnable artifact and a test that proves it, so the next one starts from a known-good base.

Integration is where the project stops being a toy. Input may arrive in a format an external system produced, output may need to match a contract another system consumes, and the two rarely line up without adjustment. This phase owns the adapters — the small translation functions between the outside world’s shape and the internal model — and it owns the boundary decisions about what to do when the two disagree: coerce, reject, or warn and continue. Keeping adapters thin and explicit means a change to an external format touches one file, and the internal model stays stable as the ecosystem around it moves.

const EMAIL = /^[^@\s]+@[^@\s]+\.[^@\s]+$/;

export function validateContact(input) {
  const errors = {};
  const name = typeof input.name === 'string' ? input.name.trim() : '';
  const email = typeof input.email === 'string' ? input.email.trim().toLowerCase() : '';
  const phone = typeof input.phone === 'string' ? input.phone.trim() : undefined;
  if (name.length < 2) errors.name = 'name must be at least 2 characters';
  if (!EMAIL.test(email)) errors.email = 'email is not valid';
  if (Object.keys(errors).length) return { ok: false, errors };
  return { ok: true, value: { name, email, phone } };
}

Acceptance for this phase: A payload missing a name returns ok false with a name error and no email error, and a valid payload normalises the email to lowercase.

Phase 5: Routes and error envelope

Map HTTP methods and paths to handlers and return one consistent JSON error shape with correct status codes. Scoped this tightly, the phase ends with a runnable artifact and a test that proves it, so the next one starts from a known-good base.

The user-facing surface determines whether the project feels finished. A command-line tool owes its caller predictable stdout, meaningful stderr, and honest exit codes; a service owes a stable request and response schema plus consistent status handling. This phase wires the internal modules to that surface and nothing else — it contains no business logic of its own, only translation between the outside contract and the internal calls. That separation is what lets the same core serve a CLI today and an HTTP endpoint tomorrow without duplication, and it is why the tests for this layer assert process-level behaviour rather than poking at internals.

import { validateContact } from './validation.js';

export const routes = [
  {
    method: 'POST',
    pattern: /^\/contacts$/,
    async handler({ repo, body }) {
      const result = validateContact(body ?? {});
      if (!result.ok) return { status: 422, body: { error: 'validation_failed', fields: result.errors } };
      try {
        return { status: 201, body: await repo.create(result.value) };
      } catch (err) {
        if (String(err.message).includes('UNIQUE')) {
          return { status: 409, body: { error: 'email_exists' } };
        }
        throw err;
      }
    },
  },
  {
    method: 'GET',
    pattern: /^\/contacts$/,
    async handler({ repo, query }) {
      const q = query.get('q') ?? '';
      const limit = Math.min(100, Number(query.get('limit') ?? 20));
      const offset = Number(query.get('offset') ?? 0);
      return { status: 200, body: { items: repo.search(q, { limit, offset }) } };
    },
  },
];

Acceptance for this phase: A duplicate email returns 409, an invalid payload returns 422 with field errors, and a valid create returns 201 with the stored record.

Phase 6: Server wiring and tests

Boot the server, run migrations, wire routes, and test the whole API against a temporary database. Scoped this tightly, the phase ends with a runnable artifact and a test that proves it, so the next one starts from a known-good base.

The final phase turns a working program into a dependable one. Tests are the visible part, but the discipline behind them is deeper: every claim the earlier phases made is pinned by an executable check, and every failure mode the tutorial mentioned has a test that reproduces it before the fix. Edge cases — empty input, malformed records, concurrent invocations, interrupted writes — are enumerated and asserted rather than assumed away. The payoff is a suite that fails loudly and precisely when someone changes a decision, which is exactly the signal a maintainer wants the moment they touch the code.

import { openDatabase } from './db.js';
import { migrate } from './migrations.js';
import { createContactsRepo } from './contacts.repo.js';
import { createApi } from './api.js';

const db = openDatabase(process.env.DB_PATH ?? './contacts.db');
migrate(db);
const repo = createContactsRepo(db);
const server = createApi({ repo });

server.listen(process.env.PORT ?? 3000, () => {
  console.log('listening on ' + (process.env.PORT ?? 3000));
});

export { server };

Acceptance for this phase: Starting the server with DB_PATH pointed at a temporary file boots cleanly, applies migrations, and serves a 201 on the first contact create.

Design trade-offs

Minimal dependencies over framework convenience. A framework would hand over plumbing for free, but it would also own the interesting decisions this project exists to teach. Keeping the dependency surface small means the lifecycle is visible end to end, the install is instant, and the tool runs anywhere the stated runtime does. The cost is owning the edge cases yourself — which is precisely why each of them gets a test.

Explicit contracts over clever conventions. Where a convention would infer behaviour from a filename or a folder, the project asks for a named export or a declared field instead. It reads as slightly more verbose and is far easier to review: a reader sees the intent on the line that owns it rather than reconstructing it from directory layout. Debugging also gets cheaper, because the failure points at the declaration instead of the convention that silently produced it.

Deterministic behaviour over convenience defaults. Anything that could vary between machines — time, ordering, encoding, locale — is injected or pinned rather than read from the ambient environment. That choice costs a little ceremony and buys reproducible output, which is the property that makes the output diffable in review and the tests trustworthy across the team’s machines.

Testing strategy

Run npx vitest. Repository tests open an in-memory database, apply migrations, and assert CRUD and search behaviour directly against SQL. API tests start the server on an ephemeral port, exercise each route including error cases, and assert both status codes and JSON bodies.

Tests are organised by layer so a failure points at the decision that broke. Pure unit tests cover the transformation logic without touching disk or network and run in milliseconds; integration tests exercise the real entry point against a temporary working directory and assert the observable contract; a small end-to-end tier proves the whole path produces the documented output. Keeping the fast tiers large and the slow tier thin is what lets the suite run on every save while still catching integration mistakes.

Every test asserts a specific promise from the phases above rather than merely covering lines. A passing suite therefore means the tool kept its word: filters compose, identifiers stay stable, failures exit non-zero with a readable message, and the output matches the format a downstream consumer expects. When someone changes a contract the matching test fails with an exact diff, turning a silent regression into a line in the pull request.

Running the suite in CI is the last line of defence, but the same command runs locally before every commit, which is what keeps the feedback loop short. A green run means the documented behaviour still holds; a red run names the exact promise that broke. Treat the suite as executable documentation and it stays current, because a stale assertion fails the moment the code and the text disagree.

Search and pagination get explicit tests because they are the two endpoints most likely to return subtly wrong results that pass a naive happy-path check.

Extending the project

Adding a field is a migration plus a validation rule; adding an endpoint is a route plus a repository method. If the app ever needs a second writer process, the SQLite file is the shared point and a server-side queue is where write serialisation would move.

The structure leaves obvious seams for growth. Each capability that could be added — a new output format, an extra filter, an interactive mode — lands in one layer because the boundaries between parsing, logic, and presentation are already drawn. Extensions that would break the current design are equally visible: the moment a feature needs cross-process coordination or a database, the storage interface is where the change belongs, and its tests tell you immediately whether the surrounding code survives the swap.