用 SQLite 构建通讯录 REST API

项目目标

本项目用 SQLite 构建一个通讯录 REST API，验证、持久化、搜索、分页与一致的错误响应一应俱全。SQLite 让持久化保持真实，又无需读者运行数据库服务；启动时自动执行迁移，重启即可完成升级。

核心特性

• 联系人的增删改查与服务端校验
• 对姓名与邮箱做大小写不敏感的子串搜索
• limit 与 offset 分页并返回总数
• 首次启动自动执行有序迁移
• 统一的 JSON 错误信封与状态码

架构与分层

HTTP 层把路由映射到处理器；仓储封装全部 SQL，处理器从不拼查询；校验模块规范化并检查输入；迁移运行器按有序语句执行并在迁移表中记账。连接只打开一次并共享。

实施阶段

六个阶段为：数据库连接、迁移、联系人仓储、校验、路由与错误信封、服务接线与测试。启动幂等是贯穿始终的约束。

测试与验收

用 vitest 运行。仓储测试打开内存库执行迁移并断言增删改查与搜索；API 测试在临时端口启动服务，逐个路由覆盖正常与错误路径，断言状态码与响应体。

延伸方向

新增字段是一次迁移加一条校验规则，新增端点是一条路由加一个仓储方法。若将来出现第二个写入进程，SQLite 文件就是共享点，写串行化应移入服务端队列。

本译文由 AI 依据英文原文完整重写，覆盖目标、特性、架构、分阶段实现、测试与扩展方向，供中文读者对照英文原稿阅读。

代码示例

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);
  });
}

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

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

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