阅读中文版本

Build a Short Link Service with Node.js and SQLite

A dependency-light URL shortener using better-sqlite3 with WAL mode, code collision retries, click analytics, and integration tests, all in one process.

sqlite-shortlink/
├── src/
│   ├── server.js
│   ├── routes.js
│   ├── service.js
│   ├── repo.js
│   └── db.js
├── test/
│   └── api.test.js
├── scripts/
│   └── migrate.js
├── package.json
└── README.md

const Database = require('better-sqlite3');

function openDb(file) {
  const db = new Database(file);
  db.pragma('journal_mode = WAL');
  db.exec(`
    CREATE TABLE IF NOT EXISTS links (
      code TEXT PRIMARY KEY,
      destination TEXT NOT NULL,
      clicks INTEGER NOT NULL DEFAULT 0,
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      expires_at TEXT
    );
  `);
  return db;
}

function createLink(repo, destination, ttlDays) {
  const expiresAt = ttlDays ? new Date(Date.now() + ttlDays * 86400000).toISOString() : null;
  for (let attempt = 0; attempt < 5; attempt++) {
    const code = nanoid(6);
    try {
      return repo.insert({ code, destination, expiresAt });
    } catch (err) {
      if (!/UNIQUE constraint failed/.test(err.message)) throw err;
    }
  }
  throw new Error('could not allocate a unique code');
}

function redirectTo(code) {
  const link = repo.findByCode(code);
  if (!link) return { status: 404, body: 'not found' };
  if (link.expires_at && Date.parse(link.expires_at) <= Date.now()) {
    return { status: 410, body: 'link expired' };
  }
  repo.incrementClicks(code);
  return { status: 301, headers: { location: link.destination }, body: '' };
}

test('expired links return 410 and do not count clicks', async () => {
  const app = startServer({ dbFile: tmpFile() });
  const created = await post(app, '/api/links', { destination: 'https://example.com', ttlDays: -1 });
  const hit = await get(app, '/' + created.code);
  expect(hit.status).toBe(410);
  const stats = await get(app, '/api/links/' + created.code + '/stats');
  expect(stats.clicks).toBe(0);
});

Project overview

Most short link tutorials reach for an ORM, a message queue, and Redis before the first redirect works. This project deliberately stays on one process with one SQLite file, because that stack is enough for tens of thousands of redirects a day and it keeps every concurrency question answerable by reading one page of code.

The interesting problems survive the simplification: codes must stay unique under retries, expired links must not pollute analytics, and redirects must stay fast while writes happen. Solving them with WAL mode and one transaction boundary teaches more than wiring five services together.

The finished service delivers five capabilities:

• POST /api/links validates the destination and returns a 6-character code with the full short URL.
• GET /:code answers 301 for live links, 410 for expired ones, and 404 for unknown codes — each pinned by tests.
• GET /api/links/:code/stats returns clicks, creation time, and expiry status.
• SQLite WAL mode sustains concurrent readers during writes without blocking redirects.
• A seed and migration script recreates the schema from scratch in one command.

The architecture is a single Node process serving HTTP and owning the SQLite file through better-sqlite3, whose synchronous API removes connection-pool concerns. Prepared statements live in a repository module, link policy lives in a service module, and the HTTP layer only maps results to status codes. WAL mode is the concurrency answer: readers never block on the single writer.

Setup

Verify the prerequisites before starting: Node.js 20 LTS or later, basic SQL and HTTP status code knowledge, and a C toolchain available for better-sqlite3 native builds — or a prebuilt binary for your platform.

The dependency footprint is two runtime packages plus the test runner:

Create the project and install:

mkdir sqlite-shortlink && cd sqlite-shortlink
npm init -y
npm install better-sqlite3@^11.0.0 nanoid@^5.0.0
npm install -D vitest@^2.1.0

Configure the environment in package.json — the server entry, the migrate script, and the test script; the database path arrives as the DB_FILE environment variable with a ./links.db default:

{
  "name": "sqlite-shortlink",
  "scripts": {
    "start": "node src/server.js",
    "migrate": "node scripts/migrate.js",
    "test": "vitest run"
  }
}

The directory structure mirrors the layers: db.js owns the connection and pragmas, repo.js owns prepared statements, service.js owns link policy, and routes.js maps results to status codes — each module with exactly one reason to change.

Phase 1: Schema and database bootstrap

The bootstrap creates the links table with a unique code index, enables WAL mode, and exposes prepared statements from a single db module.

// src/db.js
import Database from 'better-sqlite3';

export function openDb(file) {
  const db = new Database(file);
  db.pragma('journal_mode = WAL');
  db.pragma('busy_timeout = 5000');
  db.exec(`
    CREATE TABLE IF NOT EXISTS links (
      code TEXT PRIMARY KEY,
      destination TEXT NOT NULL,
      clicks INTEGER NOT NULL DEFAULT 0,
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      expires_at TEXT
    );
  `);
  return db;
}

The pragma ordering matters more than it looks: WAL mode is enabled before any table exists, and the busy_timeout gives the single writer a grace window instead of an immediate SQLITE_BUSY under parallel test writes. The code TEXT PRIMARY KEY is the uniqueness guarantee the collision retry in phase 2 depends on — an index that enforces, rather than an application-level check that races.

created_at defaults to SQLite’s own datetime('now'), which keeps timestamps consistent regardless of application clock skew, and expires_at is nullable because most links are permanent — null means live forever, a distinct state from an expired timestamp in the past.

Acceptance for this phase: running migrate.js twice succeeds; the database file exists with WAL sidecar files and the pragma reports wal.

Phase 2: Link creation with collision retry

The service implements destination validation, nanoid code generation, and an insert loop that retries a bounded number of times when the unique constraint rejects a code.

// src/service.js
import { nanoid } from 'nanoid';

export function createLink(repo, destination, ttlDays) {
  if (!/^https?:\/\//i.test(destination)) {
    throw new ValidationError('destination must be an http(s) URL');
  }
  const expiresAt = ttlDays
    ? new Date(Date.now() + ttlDays * 86400000).toISOString()
    : null;

  for (let attempt = 0; attempt < 5; attempt++) {
    const code = nanoid(6);
    try {
      return repo.insert({ code, destination, expiresAt });
    } catch (err) {
      if (!/UNIQUE constraint failed/.test(err.message)) throw err;
    }
  }
  throw new Error('could not allocate a unique code');
}

The collision strategy is retry-because-it-is-cheap: a 6-character nanoid over the URL-safe alphabet is 64^6 combinations, so a collision at small scale is a rare event, and catching the unique-constraint error and regenerating is simpler and faster than pre-checking (which races) or a longer code (which wastes characters). The bounded loop is the discipline — five attempts, then an explicit error, because an unbounded retry loop on a saturated table is a hang that looks like latency.

Validation is deliberate minimalism: an http(s) scheme check rejects the javascript-URL injection and the relative-path confusion, without pretending to a full URL parser. The test that forces collisions — by seeding the table with every code the generator will produce, via a stubbed generator — proves the retry loop terminates with the named error rather than spinning.

Acceptance for this phase: creating links returns distinct codes; a forced collision in a test exhausts retries and raises the explicit error instead of looping.

Phase 3: Redirect and stats

The route resolves a code to its live destination inside a read, answers 301 with a Location header, and increments clicks only for live links.

// src/routes.js
export function redirectTo(repo, code) {
  const link = repo.findByCode(code);
  if (!link) return { status: 404, body: 'not found' };

  if (link.expires_at && Date.parse(link.expires_at) <= Date.now()) {
    return { status: 410, body: 'link expired' };
  }

  repo.incrementClicks(code);
  return { status: 301, headers: { location: link.destination }, body: '' };
}

The ordering inside the handler is the analytics contract: expiry is checked before the increment, so a link that dies between two requests never counts its own death, and unknown codes short-circuit to 404 without any write at all. The increment lives in exactly one code path — the double-counting bug in the troubleshooting section is what happens when a route and an error handler both call the redirect function.

Status codes are part of the product, not decoration: 301 because short links are permanent by policy — clients and caches stop asking — 410 for expired so caches and crawlers stop retrying rather than treating the link as temporarily missing, and 404 only for codes that never existed. Integration tests pin each of these so a refactor cannot silently downgrade a 410 into a 200 error page, which is precisely the degradation nobody notices in review.

Acceptance for this phase: a live code redirects once and bumps clicks exactly once per request; expired and unknown codes never touch the counter.

Phase 4: Integration tests

The final phase starts the server on an ephemeral port against a temp SQLite file and drives create, redirect, stats, expiry, and validation paths end to end.

// test/api.test.js
test('expired links return 410 and do not count clicks', async () => {
  const app = startServer({ dbFile: tmpFile() });
  const created = await post(app, '/api/links', {
    destination: 'https://example.com', ttlDays: -1,
  });
  const hit = await get(app, '/' + created.code);
  expect(hit.status).toBe(410);
  const stats = await get(app, '/api/links/' + created.code + '/stats');
  expect(stats.clicks).toBe(0);
});

test('fifty parallel redirects count exactly fifty clicks', async () => {
  const app = startServer({ dbFile: tmpFile() });
  const { code } = await post(app, '/api/links', { destination: 'https://example.com' });
  await Promise.all(Array.from({ length: 50 }, () => get(app, '/' + code)));
  const stats = await get(app, '/api/links/' + code + '/stats');
  expect(stats.clicks).toBe(50);
});

Integration tests boot the real server on an ephemeral port with a real temp database, then drive it over HTTP — this tier catches everything the pure units cannot: header serialization, body parsing, status code mapping, and transaction interleaving. Each test gets a fresh file, so suites run in parallel without shared state and never need teardown rituals beyond deleting a temp directory.

The concurrency test — fifty parallel redirects, counter lands on exactly fifty — is the cheapest proof that WAL mode and the single write path behave as designed, because it exercises the actual contention the deployment section promises to sustain.

Acceptance for this phase: vitest run passes the full suite including the expiry and validation branches, with a fresh database per test.

Design trade-offs

SQLite over a client-server database. Postgres would scale horizontally and survive multi-process writes; it also costs a service, a connection layer, and an ops story for what is fundamentally a single-process workload. The trade is explicit — one writer, one file, zero services — and the migration path out is honest because the repository module is the only SQL in the codebase.

301 over 302. A 302 would let destinations change invisibly; a 301 caches aggressively and makes codes immutable by policy. The limitation section says so directly: treat codes as immutable, because clients that cached the first answer never ask again.

Synchronous better-sqlite3 over an async driver. Synchronous database calls sound like a liability until the pool exhaustion, connection storms, and callback-shaped bugs they eliminate are counted. Redirect latency is one prepared statement over an indexed read; the event loop is not the bottleneck at this scale.

Bounded retry over longer codes. A 7-character code makes collisions astronomically rare and costs 17% more characters on every redirect URL shared by humans. Six characters with five retries is the right exchange at this scale, and the forced-collision test keeps the bound honest.

Testing strategy

Run npx vitest. Every test boots the server on port 0 with a per-test temp database file — hermetic, parallel-safe, no global fixtures. The assertions pin the exact status codes (201, 301, 404, 410, 422), the Location header value, click counters after repeated redirects, and schema-level guarantees by reopening the file and inspecting the unique index — the last one catches a schema drift that no HTTP-level test can see.

The concurrency test is the capstone: fifty parallel redirects, exactly fifty clicks. It exercises WAL mode under the read-mostly contention the service actually faces, and it fails with a number — 47, 49, 51 — that names the interleaving bug class rather than a stack trace pointing nowhere.

One test earns its keep quietly: the redirect followed immediately by stats on the same connection. It catches the most common real-world regression — an increment moved outside the single write path so it lands twice, or a stats read that observes a pre-increment snapshot. Both bugs pass every sequential test and both fail this one, which is why it stays in the suite even though it looks redundant next to its neighbors.

Troubleshooting playbook

better-sqlite3 fails to install with a node-gyp error. The install dies compiling native code. The cause is a missing C toolchain or a Node release without matching prebuilt binaries — the module’s major version tracks Node’s ABI. The fix is installing the platform build tools, or pinning a better-sqlite3 version with prebuilt binaries for your runtime; the prerequisites section names this before the first npm install fails.

SQLITE_BUSY under parallel test writes. Tests intermittently fail with a database-locked error. The cause is WAL mode not actually enabled — the pragma must run before any table is created — or two writers in flight without a busy timeout. The fix is the phase 1 ordering plus busy_timeout, which gives the single writer its grace window while readers proceed under WAL.

Clicks increment twice for one redirect. Stats show two clicks for one browser hit. The cause is a double response path — the route and an error handler both calling the redirect function, each running the increment. The fix is the single-code-path rule from phase 3, and the per-request click assertion in the tests fails with the exact count the moment a second path appears.

Extending the project

The repository seam is the growth surface: moving analytics writes to a queue or a separate file is a repo module swap that neither the service nor the routes observe. Custom aliases (user-chosen codes) are a validation branch plus the same unique-constraint retry. Expiry sweeps — deleting long-expired rows to reclaim space — are a scheduled statement against expires_at, and analytics already survive the sweep because expired links stopped counting at death. QR rendering is a presentation layer over the same code.

For deployment, run as a single container or systemd service with the SQLite file on a persistent volume. Take backups with the sqlite3 .backup command while online rather than copying the file mid-write. Put a reverse proxy in front for TLS and rate limiting, and set trust proxy so client IPs survive — the single-writer constraint is honored by the deployment shape rather than pretended away. The 301-permanence decision has an operational consequence worth making explicit for whoever owns this service after you: browsers and intermediary caches remember a 301 essentially forever, so the redirect you ship is the redirect the internet has. That immutability is a feature when codes are treated as names — a printed QR code on packaging must keep meaning the same product page for the life of the packaging — and a trap when someone asks to “just repoint” a code after a marketing campaign. The escape hatches are deliberate and documented: issue a new code and 301 the old one to the new destination only once, or start with 302 for campaigns whose destinations are expected to move, paying the re-request cost on every click. The stats table is what makes the question answerable after the fact — a code with sustained click traffic years after creation is a name, and a code that went quiet last quarter is safe to let expire.

Backup verification completes the deployment story the same way restore drills complete any storage system: the .backup command produces a consistent snapshot regardless of concurrent writes, and the weekly drill is to restore that snapshot into a scratch directory, run the test suite’s status-code assertions against it, and compare the row counts with the live file. The drill catches the failure nobody tests for — a backup job that quietly began failing after a volume remount — while the data is still recoverable from the previous snapshot.