Stop 3 of 10 · Weekly

The inventory in SQLite

Twenty-five clients out of a spreadsheet into five tables, a numbered migration you read line by line before it runs, and a test that builds the database from nothing.

Four years of everything drawer

Dany's twenty-five clients live in one sheet with three tabs, merged cells in the header row, and a column called notes that holds renewal dates, a hosting password from 2023 and somebody's phone number. Every tool in the rest of this course needs to ask it questions. Spreadsheets do not answer questions, they answer lookups.

Five tables replace it.

No credentials column. No customer data column. Those never arrive, so they never leak.

The migration, read line by line

Migrations are numbered SQL files. 001-inventory.sql creates the first three tables and nothing else.

create table clients (
  id integer primary key,
  name text not null,
  status text not null check (status in ('active', 'paused', 'ended')),
  started_on text not null
);

create table sites (
  id integer primary key,
  client_id integer not null references clients(id),
  url text not null unique,
  platform text not null,
  repo text
);

Read the constraints, because they are the argument. status is checked against three values, so a typo becomes an error rather than a client who quietly stops appearing in reports. url is unique, so the same site cannot be added twice by two people. client_id references clients, so a site cannot belong to a client who is not there.

The runner is short enough to read whole.

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

const db = new DatabaseSync(process.env.STUDIO_DB);
db.exec('create table if not exists schema_migrations (name text primary key, applied_at text not null)');
const done = new Set(db.prepare('select name from schema_migrations').all().map(r => r.name));

for (const f of fs.readdirSync('db/migrations').sort()) {
  if (done.has(f)) continue;
  const sql = fs.readFileSync(`db/migrations/${f}`, 'utf8');
  if (process.argv.includes('--dry-run')) { console.log(`would apply ${f}:\n${sql}`); continue; }
  db.exec('begin');
  db.exec(sql);
  db.prepare('insert into schema_migrations values (?, ?)').run(f, new Date().toISOString());
  db.exec('commit');
}

Mo can read the whole thing in a minute. It records what it applied, skips what it already applied, and wraps each file in a transaction so a half-applied migration does not exist. node:sqlite is built in, and which Node versions need a flag for it is on the Node documentation page for the module, so check yours.

Here is the header row and five sample rows from my studio client sheet, with the login and form-submission columns already deleted: [paste the header row and five rows].

Propose a SQLite schema of no more than five tables for clients, sites, retainers, nightly checks and findings. For every column, say which sheet column it came from or that it is new. For every column that has no home, say so out loud rather than inventing a table for it.

Then write it as db/migrations/001-inventory.sql, with check constraints on any column that has a fixed set of values, and tell me which three constraints are most likely to reject a real row on import.

Write a node --test file that creates a temporary database file, runs every migration in db/migrations against it in order, and then asserts: the five tables exist with the expected columns, inserting a site with a client_id that does not exist fails, inserting a duplicate url fails, and running the whole runner a second time applies nothing and leaves the row count unchanged.

Delete the temporary file in a cleanup step whether the test passed or failed.

Mark stop 3 done