Skip to content

Database and schema changes

The whole database is defined in one file, schema.sql, in hand-written SQL for D1 (SQLite). There’s no migration tool and no ORM.

Terminal window
npm run db:schema # local database
npm run db:schema:remote # deployed database
npm run deploy # applies it remotely, then deploys

schema.sql is idempotent: every table and index is CREATE … IF NOT EXISTS, and the only seed data (the Blog and News categories) is INSERT OR IGNORE. Running it again never changes or loses anything, which is why npm run deploy can apply it every time.

Area Tables
CMS users users, sessions, activity_log
Readers reader_accounts, reader_sessions, reader_activity_log, notification_preferences, blog_subscriptions
Security login_attempts (login lockouts by IP), spam_throttle (public form rate limit by IP)
Pages pages
Articles articles, categories, series, article_series, tags, article_tags
People people
Community comments, comment_replies, corrections
History revisions (up to 50 JSON copies per page or article)
Design theme_tokens, site_config, emoticons
Settings site_settings, email_templates

A few design choices to know about:

  • Pages store their blocks as a JSON array ([{ "type": …, "props": … }]) in pages.blocks. Articles store their body as HTML in articles.content.
  • Published versions are frozen as JSON in published_snapshot on both tables; the public site renders from that. See Architecture.
  • An article’s category is a slug in articles.category, not a foreign key. Authors and reviewers are JSON arrays of people slugs.
  • theme_tokens and email_templates only hold overrides. The defaults live in code (src/tokens.js and src/email.js), and a missing row means the default. “Reset” in the admin deletes the row.
  • site_config holds JSON blobs by key (nav, footer, fonts, surfaces, media_folders, emoticon_categories) and the generated session secrets. site_settings holds the Settings page’s plain values. Toggles count as on when their row is missing.
  • Foreign keys cascade: deleting an article deletes its comments, tags and series memberships; deleting a reader account keeps their comments but unlinks them.

The query helpers in src/db.js (getSiteSettings, getSiteConfig, getThemeTokens, saveRevision, logActivity and so on) are the usual way in from code.

Because schema.sql uses CREATE TABLE IF NOT EXISTS, re-running it adds new tables and indexes, but it can’t add new columns to a table that already exists. So npm run deploy handles a new table for you, but a new column needs one more step.

When a column is added, schema.sql lists the SQL for existing databases in a comment beside it, marked Migration (pre-existing databases), for example:

-- ALTER TABLE articles ADD COLUMN scheduled_for TEXT;

After pulling an update, look for new migration comments (git diff on schema.sql shows them) and run each one against your database:

Terminal window
npx wrangler d1 execute DB --remote --command "ALTER TABLE articles ADD COLUMN scheduled_for TEXT;"

Run it with --local for your local database too. If a column already exists, SQLite refuses with a “duplicate column” error, which is harmless. To check first:

Terminal window
npx wrangler d1 execute DB --remote --command "PRAGMA table_info(articles);"

Some code copes with a missing column (the Team grid block, for example, falls back if people.blurb is missing), but don’t rely on it: run the migrations.

A database created from the current schema.sql already has every column, so a fresh install never needs them.

Any SQL can be run with wrangler d1 execute against the DB binding:

Terminal window
npx wrangler d1 execute DB --remote --command "SELECT email, role, active FROM users"

Two handy ones:

Terminal window
# Stop one CMS user getting the admin notification emails
npx wrangler d1 execute DB --remote --command "UPDATE users SET notify_new_comment = 0, notify_new_correction = 0, notify_new_registration = 0 WHERE email = 'person@example.com'"
# Clear a login lockout
npx wrangler d1 execute DB --remote --command "DELETE FROM login_attempts"

To export the whole database as SQL:

Terminal window
npx wrangler d1 export DB --remote --output=backup.sql

D1’s Time Travel can also restore a database to an earlier point; see Cloudflare’s D1 documentation. Media isn’t in the database: it’s in the R2 bucket, which you back up separately.