How the database schema is kept up to date
The schema is the set of tables the site keeps its data in. A new version of the site sometimes needs a table the old one did not have. This page explains how those tables come to exist on each platform, for whoever runs the server. On Node and Docker it normally needs no attention at all.
How it works
The site carries one schema file for each kind of database, in server/db/schema/:
| File | For |
|---|---|
sqlite.sql | SQLite and Cloudflare D1 |
postgres.sql | PostgreSQL |
mysql.sql | MySQL and MariaDB |
Each describes the same 36 tables. Every statement in them is of the form "create this table, or this index, if it does not exist yet". Two things follow:
- It is safe to run a schema file again, on a database that already has data, as often as you like. What exists is left alone.
- It only ever adds. Applying the schema never changes a table that exists, never removes a column and never deletes a row.
The second point is what lets an update be undone. If a new version fails to start and the site goes back to the one before, the older version finds the database with perhaps a table it does not know about, and nothing it does know changed. It carries on working. See How updates work.
There is no table of applied migrations and no version number in the database. The schema file of the version that is running is the whole story.
On Node and Docker: at every start
With DB_AUTO_MIGRATE at its default of true, the server applies the schema every time it starts, before it accepts any request, and logs:
[db] schema is up to date (sqlite)
The word in brackets is your DB_PROVIDER. After an update the first start of the new version creates anything new. There is nothing for you to do.
| Variable | What it does | Default |
|---|---|---|
DB_AUTO_MIGRATE | Apply the schema at every start. | true |
Applying it by hand
Set DB_AUTO_MIGRATE=false if the site's database user should not be allowed to create tables, or if you want schema changes to be a deliberate step. The server then starts without touching the schema and logs no [db] line. You apply it yourself, at install and before starting each new version:
node --env-file=.env scripts/migrate.js
or, with the settings already in the environment:
npm run db:migrate
It answers:
Schema applied to the sqlite database.
The script uses the same settings as the server (DB_PROVIDER, DATABASE_URL, SQLITE_PATH), so give it the same environment. Run without them, it creates a new SQLite file in ./data and reports success.
To use a more privileged database user for this step only, run the script with a DATABASE_URL of its own.
A new version started against an old schema fails wherever it meets a missing table. If you switch this off, apply the schema before every update. With one-click updates that is not possible in between, so leave DB_AUTO_MIGRATE on if you use them.
Two other scripts apply the schema before doing their own work, whatever DB_AUTO_MIGRATE says: npm run admin:add, which adds an admin, and npm run db:seed-demo, which fills a site with sample content.
On Cloudflare: by hand, always
A Worker never applies the schema. Run this at install, and after every update before you deploy:
npx wrangler d1 execute choir-db --remote --file=./server/db/schema/sqlite.sql
Cloudflare D1 has the details.
What the tables hold
For finding your way around a backup or a database console. Do not change these tables by hand while the site is running unless a page of this guide tells you to.
| What | Tables |
|---|---|
| Site settings saved in the admin panel | settings |
| Login sessions, when they are kept in the database | sessions |
| Admins, their kind, and their login links | admins, admin_roles, admin_login_links |
| Members, their extra details, and their login links | users, member_details, member_login_links |
| The public site | concerts, past_events, gallery_items, pages, contact_submissions |
| The member portal | announcements, announcement_sends, schedule_events, schedule_series, schedule_exceptions, member_files, member_links |
| Rehearsal tracks | songs, song_tracks, lineups, lineup_songs |
| Tickets | ticket_events, ticket_types, ticket_orders, tickets |
| Donors | donors, donor_campaigns, donor_gifts, donor_receipts, donations |
| Card payments | payment_events |
| Emails to groups, and who has asked not to receive them | mailings, email_optouts |
Members are in the table called users. Uploaded files are not in the database: it holds each file's key, and the file itself is in file storage.
platform.sql is not yours
A fourth file, platform.sql, sits in the same folder. It belongs to the mode in which one installation serves many choirs, which is how our hosted service runs. A self-hosted site never applies it and you should not either.