d1-backup (Cloudflare tools)

This script helps you to create a SQL backup file of a Cloudflare D1 SQLite database. Based on the same idea as Cretezy/cloudflare-d1-backup, it uses the HTTP D1 API to query for table definitions and data, then outputs SQL commands to allow recreation of an equivalent/same database.

Specific features

• This script first exists because of troubles with SQLite "generated columns".
• And now, it doesn't use anymore "LIMIT" & "OFFSET" to avoid generating a lot of reads!! But be careful, it needs "rowid"!

Usage

To run the CLI, you need to prepare:

• Your Cloudflare account ID.
  This can be found as the ID in the URL on the dashboard after dash.cloudflare.com/, or in the sidebar as "Account ID".
• Your Cloudflare D1 database ID.
  This can be found with each D1 page as "Database ID".
• And your Cloudflare API key. This can be created as an "API token" (User icon / "My Profile" / "API Tokens") with 2 permissions: Account/D1 write access (the CLI will never write to your database) & Account.Analytics.

These keys can be set in a .env file or as command line parameters.

CLOUDFLARE_API_TOKEN = "<token>"
CLOUDFLARE_ACCOUNT_ID = "<account_id>"
CLOUDFLARE_DB_ID = "<D1DB_id>"

Usage: @tigersway/d1-backup [options] [file]

Cloudflare D1 SQL backup downloader.

Arguments:
  file                 file (default: "./backup.sql")

Options:
  --token <token>            Cloudflare API Token
  --account <account>        Cloudflare account ID
  --d1 <database>            Cloudflare D1 uuid
  -l, --limit <limit>        number of values in each insert (default: 100)
  -a, --apiLimit <apiLimit>  number of fetched rows per API call (default: 5000)
  -v, --version              output the version number
  -h, --help                 display help for command

Restoring a backup

npx wrangler d1 execute <database> --file=<backup.sql> [--local]