Connecting a database

Every route you've read so far calls into a db object without asking where it came from. Time to build it. Here's the whole thing, reference/linkstash/src/db.ts in full:

import Database from "better-sqlite3";
 
export type { Database } from "better-sqlite3";
 
/** Opens a database and ensures the schema exists. Defaults to in-memory, which
 *  is what the tests use: every test gets a pristine database with no cleanup. */
export function openDb(file = ":memory:") {
  const db = new Database(file);
  db.pragma("journal_mode = WAL");
  db.pragma("foreign_keys = ON");
  db.exec(`
    CREATE TABLE IF NOT EXISTS users (
      id           INTEGER PRIMARY KEY AUTOINCREMENT,
      email        TEXT    NOT NULL UNIQUE,
      passwordHash TEXT    NOT NULL,
      createdAt    TEXT    NOT NULL DEFAULT (datetime('now'))
    );
    CREATE TABLE IF NOT EXISTS links (
      id        INTEGER PRIMARY KEY AUTOINCREMENT,
      userId    INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
      url       TEXT    NOT NULL,
      title     TEXT    NOT NULL,
      createdAt TEXT    NOT NULL
    );
    CREATE INDEX IF NOT EXISTS links_user_created ON links (userId, id DESC);
  `);
  return db;
}

better-sqlite3 is a synchronous SQLite driver, no await needed for queries, which is unusual if you've used other database libraries and turns out to be exactly right for SQLite: it's an embedded database, there's no network round trip to a separate server process to wait on, so pretending it's async would just add overhead for nothing.

Why openDb takes a filename, with a default

openDb(":memory:") is the default, and it's not a placeholder value, it's what Linkstash's entire test suite runs against. Every test in app.test.ts does db = openDb() in a beforeEach, gets a completely fresh, empty database, and throws it away when the test ends. No test cleanup step, no shared state between tests, no risk that test 12 sees a row test 3 left behind. That's a real design decision this app makes on purpose, testable-by-default, not testable-if-you-remember-to-mock-something.

Production passes a real path instead: openDb(process.env.DATABASE_FILE ?? "linkstash.db"), from server.ts. Same function, same schema, same code path, just pointed at a file instead of memory.

Prepared statements: the SQL injection fix, not a bonus feature

Every single query in Linkstash uses ? placeholders and binds values as separate arguments, never string interpolation. Here's listLinks, straight from reference/linkstash/src/links.ts:

/** Every value is bound with `?`, never interpolated. This is the function the
 *  Security series' SQL-injection lesson holds up as the fixed version. */
export function listLinks(db: Database, userId: number, opts: ListOptions = {}): Link[] {
  const { limit = 20, offset = 0, q } = opts;
  const where = q ? "WHERE userId = ? AND title LIKE ?" : "WHERE userId = ?";
  const params: (string | number)[] = q ? [userId, `%${q}%`] : [userId];
  return db
    .prepare(`SELECT id, userId, url, title, createdAt FROM links ${where} ORDER BY id DESC LIMIT ? OFFSET ?`)
    .all(...params, limit, offset) as Link[];
}

Look closely at what gets interpolated into the SQL string and what doesn't. The where clause text, "WHERE userId = ? AND title LIKE ?", is a fixed string chosen from two hardcoded options, never built from user input. The actual values, userId, the search term, limit, offset, are every one of them a ? placeholder, filled in through .all(...params, limit, offset). better-sqlite3 sends the query and the values to SQLite separately. SQLite parses the query structure first, then substitutes the values as data, never as code. A search term of ' OR 1=1 -- can't restructure the query, because by the time it reaches SQLite, it's already been assigned a role: it's a value, bound to a parameter, not a fragment of SQL syntax.

This is the same pattern in createLink:

export function createLink(db: Database, userId: number, input: { url: string; title: string }): Link {
  const createdAt = new Date().toISOString();
  const { lastInsertRowid } = db
    .prepare("INSERT INTO links (userId, url, title, createdAt) VALUES (?, ?, ?, ?)")
    .run(userId, input.url, input.title, createdAt);
  return { id: Number(lastInsertRowid), userId, url: input.url, title: input.title, createdAt };
}

Four columns, four ? placeholders, four bound arguments in the same order. It's a habit worth building until it's automatic: the moment you catch yourself writing a template string or + concatenation to build a query, stop and turn it into a placeholder instead, no exceptions for "just this one internal value."

Reading the schema like a sentence

Two more lines from openDb earn a closer look, because they're doing more than they appear to.

db.pragma("foreign_keys = ON") matters because SQLite ships with foreign key enforcement off by default, for backward-compatibility reasons that go back decades. Without this line, links.userId REFERENCES users(id) ON DELETE CASCADE would be documentation, not a rule, letting you insert a link with a userId that matches no user at all. With it, that same insert fails, and deleting a user cascades to delete their links automatically instead of leaving orphaned rows behind. One line, and a whole category of "how did this row end up pointing at nothing" bugs stops being possible.

CREATE INDEX ... ON links (userId, id DESC) is the other one worth noticing, because it's shaped around one specific query, not added generically. listLinks always filters by userId and always orders by id DESC. An index on exactly those two columns, in that order, lets SQLite satisfy both the filter and the sort from the index directly, instead of scanning every link in the table and sorting the results afterward. Indexes aren't free (they cost write speed and storage), so this one earning its place by matching the app's single most common query is the right way to think about when to add one, not "index everything defensively."

Reading and deleting: getLink and deleteLink

The last two functions in links.ts round out the CRUD picture:

export function getLink(db: Database, id: number): Link | undefined {
  return db
    .prepare("SELECT id, userId, url, title, createdAt FROM links WHERE id = ?")
    .get(id) as Link | undefined;
}
 
export function deleteLink(db: Database, id: number): boolean {
  return db.prepare("DELETE FROM links WHERE id = ?").run(id).changes > 0;
}

Notice getLink doesn't filter by userId at all, it just fetches by id. That's not a bug, the ownership check happens one layer up, in the route handler you saw two lessons back (!link || link.userId !== req.userId). Keeping the query dumb and the authorization logic in one visible place, right where the response gets built, is easier to audit than burying an owner check inside a database function where it's easy to assume it's already handled and forget to check again somewhere else.

deleteLink returns a plain boolean by reading .changes, the count of rows the statement actually touched. Delete an id that doesn't exist and changes is 0, deleteLink returns false, and the route handler folds that into the same 404 path as everything else. .run() versus .get() versus .all() is the other pattern worth internalizing here: .run() for statements that don't return rows (INSERT, UPDATE, DELETE), .get() for one row, .all() for many. better-sqlite3 picks the shape of what you get back based on which method you call, not based on the SQL itself.

The schema and the queries both hold. What's missing is stopping bad data before it ever reaches these functions, which is exactly what validating input before it ruins your day covers next. For the SQL fundamentals underneath all of this, why learn SQL and SQL joins are worth a refresher if WHERE, LIKE, and ORDER BY feel rusty.