Cloudflare D1
D1 is Cloudflare's database, and the only one a site on Cloudflare Workers can use. This page is for whoever deploys the site to Cloudflare. The one thing to remember from it: on Cloudflare you apply the database schema, at install and after every update. Nothing does it for you.
How the Worker finds its database
Not through a setting. The Worker uses whatever database is bound to it under the name DB in wrangler.toml:
[[d1_databases]]
binding = "DB"
database_name = "choir-db"
database_id = "YOUR_D1_DATABASE_ID"
binding must be exactly DB. database_name is your own choice and is the name you give to Wrangler's commands; database_id is printed when you create the database:
npx wrangler d1 create choir-db
DB_PROVIDER, DATABASE_URL, DATABASE_SSL, SQLITE_PATH and DB_AUTO_MIGRATE are not read on Cloudflare. Setting them changes nothing.
D1 speaks the SQLite dialect, so it uses the same schema file as a SQLite site: server/db/schema/sqlite.sql.
Apply the schema
Run this from the folder that holds wrangler.toml:
npx wrangler d1 execute choir-db --remote --file=./server/db/schema/sqlite.sql
--remote matters. Without it Wrangler writes to a local practice copy on your computer and your real database is untouched.
Do this:
- When you install, before the first deploy. A Worker with an empty database cannot load the site.
- After every update, before
npx wrangler deploy. A new version may need tables the old one did not have. - Before switching on a feature that needs tables added since you installed, such as a login for each admin.
The file is safe to run as often as you like. Every statement in it only creates what is missing, and none changes or removes anything. How the database schema is kept up to date explains why.
Check the result. There should be 36 tables of the site's own:
npx wrangler d1 execute choir-db --remote --command "SELECT name FROM sqlite_master WHERE type = 'table' ORDER BY name"
The list also shows a few tables that belong to SQLite and to Cloudflare, with names beginning sqlite_ or _cf_.
Run a statement
The helper scripts (npm run admin:add, npm run db:migrate) work on Node only and cannot reach D1. Anything they would do is done with a statement instead:
npx wrangler d1 execute choir-db --remote --command "SELECT email, active FROM admins"
Adding the first admin this way is in Add the first admin, and get back in when locked out.
Take care with anything that is not a SELECT. There is no undo, and the tables depend on one another. Cloudflare's own tools for looking back in time are covered in Back up PostgreSQL, MySQL, S3 storage and Cloudflare.
Sessions can live in D1 too
By default a Cloudflare site keeps login sessions in a KV namespace. With SESSION_STORE = "database", or with no SESSIONS binding, they go in the sessions table of this database instead. See Sessions.
Limits
D1 allows a limited number of values in one statement and of statements in one batch. The site is written to stay inside them, by doing large jobs such as imports in groups, so there is nothing to set. The figures, and the other limits of Workers, are in Cloudflare: limits and logs.
If something goes wrong
| What you see | Cause |
|---|---|
Every page fails, and npx wrangler tail shows no such table: … | The schema has not been applied to the remote database, or not since an update. Apply it. |
| The command succeeds but the site does not change | --remote was left out, or the command went to a different database from the one in wrangler.toml. |
Every page fails with an error about prepare being read from something undefined | There is no binding called DB. Check the [[d1_databases]] block. |