Working with D1 and Cloudflare Pages in local development

[robinmetral]

I’m building a Cloudflare Pages site. It has a few workers built directly as Pages Functions.

In my site’s functions settings (https://dash.cloudflare.com/<id>/pages/view/<name>/settings/functions) I’ve added a binding to a D1 database:

Production seems to work as expected. However, I’m not sure how to work with this setup locally. The docs have the following to say:

Interact with your D1 databases locally

While developing locally, interact with a D1 database by adding --d1=<BINDING_NAME> to your run command.

By default, data in local development is not persisted. This means if you create a schema and/or insert data into a D1 table, the next time you start local development, it will no longer exist.

You can enable persistence with the --persist flag.

Specifically:

• If your database is bound to NORTHWIND_DB, access this database in local development by running npx wrangler pages dev <OUTPUT_DIR> --d1=NORTHWIND_DB.
• Interact with this binding by using context.env - for example, context.env.NORTHWIND_DB

Refer to the D1 client API documentation for the API methods available on your D1 binding.

I have many questions.

The local database seems to be empty when running the site with wrangler (command in the docs above). Is this expected, or should it be populated the data from production? This is the output:

Your worker has access to the following bindings:
- D1 Databases:
  - D1_NOTES: local-D1_NOTES ()

Since the database name is generated from the argument that I’m passing wrangler (--d1=D1_NOTES “local-D1_NOTES”), I assume it’s meant to be local only.

In that case, how to populate local data? I’ve tried to use d1 via wrangler directly (wrangler d1 execute notes ...), but this only affects the production DB.

Passing the --local flag to the same command throws this error:

Can't find a DB with name/binding 'notes' in local config. Check info in wrangler.toml...

…but there’s no wrangler.toml, since this is a Pages site with Pages Functions, not a Worker.

I might be missing something, but so far I don’t really understand how to work with D1 in Cloudflare Pages locally. Any pointers/docs I might have missed would be very welcome!

[robinmetral]

So here’s what I’ve found over the past 24 hours, in part thanks to BenSeitz on Discord:

• Don’t forget the --persist flag. I run the project locally with wrangler pages dev src --d1=<BINDING> --persist. You should see a sqlite file popping up at .wrangler/state/d1/<BINDING>.sqlite3. Without --persist, nothing will be persisted at all so data is lost between the wrangler d1 execute and wrangler pages dev
• Once you have a .sqlite3 file locally, you can use it in two different ways:
  • manage it like any other SQLite DB. From a Cloudflare blog post: “You can then use a local GUI program for managing it, like SQLiteFlow or Beekeeper. Or you can simply use SQLite directly with the SQLite command line by running sqlite3 .wrangler/state/d1/DB.sqlite3”
  • execute commands against it using wrangler. You can use the same command as for production D1 DBs, wrangler d1 execute ..., but pass it the --local flag. The trick here is that you’ll need to define the DB binding in wrangler.toml, even though this is not officially supported/documented for Cloudflare Pages Functions (this is a Workers thing). You can then run SQLite commands against the local DB, e.g. wrangler d1 execute <BINDING> --local --command "SELECT Name FROM Users LIMIT 100;"