D1 SQL notes

Fun fact: this blog is now powered by the D1 private beta.

Table of Contents

• Developing
• Disaster Recovery
  • Listing backups
  • Restoring the D1 database

Developing

Iterating on D1 is really hard with just a Worker. You're going to want to jump into the terminal.

On top of this, you know SQL, but chances are you don't know SQLite's dialect. For example, there's no native boolean type - you're going to want to use integers. Get comfortable jumping into sqlite's docs to solve issues.

My workflow: write a schema.sql, and keep executing and changing it until it doesn't error.

For example:

-- schema.sql
DROP TABLE IF EXISTS articles;
CREATE TABLE articles (slug TEXT, title TEXT, content TEXT, excerpt TEXT, date_published TIMESTAMP, show_date INTEGER, PRIMARY KEY (slug));

INSERT INTO articles (slug, title, content, excerpt, date_published, show_date)
VALUES ("test-article", "Hello World", "Why hello!", "Testing", "2022-09-29 00:00", 1);

and the wrangler command:

NO_D1_WARNING=true npx [email protected] d1 execute blog --file=./schema.sql

Once that command stops erroring, you can verify the data's actually there:

NO_D1_WARNING=true npx [email protected] d1 execute blog --command='SELECT * FROM articles;'

Disaster Recovery

If anything ever happens to the D1 service, there are always backups to restore from.

To get up and running, you'll need to:

1. List your backups to get the backup ID
2. Restore the DB using the backup ID

Listing backups

Using wrangler2, you can get your backups using the following command:

wrangler d1 backup list <db_name>

Restoring the D1 database

Using wrangler2 again, you can restore an individual backup using the following command:

wrangler d1 backup restore <db_name> <backup_id>