Design the SQLite schema

We saw the problem in the first module: restart the server and the books are gone. An array in memory also can’t be shared between two server processes. Time to move the data into SQLite.

SQLite is a full SQL database that lives in one file, with no server to run. For a local API it is the simplest durable storage there is, and Node.js ships a driver in the node:sqlite module. If SQLite is new to you, the SQLite course covers the basics.

The table

Here is the schema for our books:

CREATE TABLE books (
  id TEXT PRIMARY KEY,
  title TEXT NOT NULL CHECK (length(title) > 0),
  author TEXT NOT NULL CHECK (length(author) > 0),
  published_year INTEGER,
  created_at TEXT NOT NULL
);

Every line here mirrors a rule from the contract. The ID is text because we generate UUIDs. Title and author are required and non-empty, the same rule our Zod schema enforces. The year is optional. created_at is text holding an ISO 8601 timestamp in UTC, like 2026-09-08T16:20:00.000Z. Pick one timestamp format and never mix it.

Why repeat the validation

You might ask why the database checks length(title) > 0 when the HTTP layer already rejects empty titles. Because the HTTP handler is not the only thing that writes. A seed script, a test helper, a future admin route, a one-off fix run by hand: all of them talk to the table directly. The constraints are the last line of defense, and they protect every path, not just the one you remembered.

The two layers also have different jobs. Validation gives the client a friendly 422 with field names. The constraint guarantees that an impossible row never exists, no matter who tried to insert it.

Create it and break it

Save the statement in migrations/001-books.sql and apply it from a small script:

import { DatabaseSync } from 'node:sqlite'
import { readFileSync } from 'node:fs'

const db = new DatabaseSync('books.db')
db.exec(readFileSync('migrations/001-books.sql', 'utf8'))

Now insert one good row and one bad row from the sqlite3 shell:

INSERT INTO books VALUES ('1', 'Dune', 'Frank Herbert', 1965, '2026-09-08T16:20:00.000Z');
INSERT INTO books VALUES ('2', '', 'Nobody', NULL, '2026-09-08T16:21:00.000Z');

The first succeeds. The second fails with Runtime error: CHECK constraint failed: length(title) > 0 (19). That message is exactly what we want the database to say, and exactly what the client must never see raw. The next lesson maps it to a problem response.

Migrations, not guesses

Treat the schema as versioned files, applied in order, with a record of which ones ran. The same files run in development, in tests and in production. Don’t let the app “create the table if missing” at startup; that hides drift between environments.

Two more habits for later. When one API operation needs several statements, wrap them in a transaction so all of them commit or none do. And keep transactions short: SQLite allows many readers at once but only one writer, so a long write blocks everyone else and surfaces as a SQLITE_BUSY error you must handle without leaking database text.