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.
Applying the schema
Section titled “Applying the schema”npm run db:schema # local databasenpm run db:schema:remote # deployed databasenpm run deploy # applies it remotely, then deploysschema.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.
What’s in it
Section titled “What’s in it”| 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": … }]) inpages.blocks. Articles store their body as HTML inarticles.content. - Published versions are frozen as JSON in
published_snapshoton 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_tokensandemail_templatesonly hold overrides. The defaults live in code (src/tokens.jsandsrc/email.js), and a missing row means the default. “Reset” in the admin deletes the row.site_configholds JSON blobs by key (nav,footer,fonts,surfaces,media_folders,emoticon_categories) and the generated session secrets.site_settingsholds 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.
Upgrading an existing database
Section titled “Upgrading an existing database”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:
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:
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.
Running queries
Section titled “Running queries”Any SQL can be run with wrangler d1 execute against the DB binding:
npx wrangler d1 execute DB --remote --command "SELECT email, role, active FROM users"Two handy ones:
# Stop one CMS user getting the admin notification emailsnpx 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 lockoutnpx wrangler d1 execute DB --remote --command "DELETE FROM login_attempts"Backing up
Section titled “Backing up”To export the whole database as SQL:
npx wrangler d1 export DB --remote --output=backup.sqlD1’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.
