Skip to main content

Cloudflare D1

Connect a Cloudflare D1 database to copy its tables into your organization as a dataset you can query alongside the rest of your data. D1 is Cloudflare's serverless SQLite database. The copy only reads from D1. It never creates, changes or deletes anything there.

Create an API token​

OSO reads D1 with a Cloudflare API token that you create and store as an environment secret.

  1. In the Cloudflare dashboard, open My Profile, then API Tokens, and create a custom token.
  2. Give it one permission: Account → D1 → Read.
  3. Under Account Resources, include only the one account that holds your database.
  4. In OSO, add the token as a secret in one of your environments. The dialog lets you pick the secret by name, and the value is never shown again.
warning

A D1 token applies to every D1 database in the account. Cloudflare has no way to limit a token to one database. If other databases in the account must stay private, use a separate Cloudflare account for the data you want to copy.

important

OSO cannot see whether a token also has write permission. The copy never writes, but you should still create a Read-only token, so that a leaked token cannot change your data.

Find your account id and database id​

  • Account id. Open the Cloudflare dashboard and select your account. The account id is the 32-character hex string in the page URL, and it is also shown on the account's Overview page.
  • Database id. In the dashboard, open Storage & Databases, then D1 SQL Database, and click your database. The database id is a UUID such as 00000000-0000-0000-0000-000000000000.

Connect a database​

  1. From your organization, open Integrations in the sidebar. Find Cloudflare D1 (in the Dev category) and click Connect.
  2. Pick the environment and the secret that holds the token, then enter the account id and the database id.
  3. Click Check and list tables. OSO checks the token and lists the tables in the database. If a check fails, the dialog shows the reason. See Troubleshooting.

Pick tables and a read method​

Every table starts unchecked. Tick the tables you want to copy. For each table the dialog shows the approximate number of rows, the key, and a read method. It suggests the safest method the table allows, and offers only the methods the table can use. A table with a column named rowid, oid or _rowid_ hides SQLite's real row id, so it can only use UPDATED_AT_CURSOR. A table that no method fits cannot be ticked.

Read methodWhat it readsRules
UPDATED_AT_CURSOROnly rows that are new or changed since the last runThe table needs a key. The cursor column and every key column must be NOT NULL.
ROWIDOnly rows added since the last run. Edits to old rows are not seen.The table needs an INTEGER PRIMARY KEY.
FULL_REPLACEEvery row, every run. The table in OSO is replaced with what it read.A WITHOUT ROWID table needs a key. A table too big for one run fails instead of being replaced.

For UPDATED_AT_CURSOR, choose the cursor column: usually a change-time column such as updated_at. An INTEGER PRIMARY KEY counts as NOT NULL.

Why the cursor and key columns cannot be nullable​

Each run asks D1 for rows after the last one it saw, comparing the cursor column and the key together. When one of those values is NULL, SQLite cannot compare it, and the row would be skipped on every run. OSO refuses such a table when you connect, and checks again before each run, so a nullable change-time or key column is never used as a cursor.

Add an index on the cursor column​

Add an index that starts with the cursor column, for example CREATE INDEX idx_notes_updated_at ON notes (updated_at, id). Without it, each page of rows scans the whole table. The dialog shows a note when the suggested cursor column has no index. It does not check a cursor column that you pick yourself, so create the index on that column in D1 before the first run.

Columns that may hold personal data​

The dialog marks columns whose names suggest personal data or secrets, such as email, phone, password or token. They are only marked. OSO still copies them if you pick the table, so check each table before you tick it.

Choose a schedule​

Choose how often to copy. The default is daily. With UPDATED_AT_CURSOR and ROWID, each run reads only new or changed rows and merges them into the dataset by key, with the newest version winning, so a re-run is safe. With FULL_REPLACE, each run reads the whole table again and replaces the table in OSO.

Load on your live database​

D1 runs one query at a time, and each query blocks your app while it runs. To keep the load small, a copy:

  • sends one query at a time;
  • reads small pages of 1000 rows, with a short pause between pages;
  • stops after a fixed number of rows per table in each run (200,000).

A first copy of a big table can take several runs. A table that stops at the row cap continues from where it stopped on the next run, except FULL_REPLACE, which fails instead of leaving a partial copy.

D1 bills for rows read. Pick a schedule that matches how fresh you need the data to be. A cursor read method reads far fewer rows than FULL_REPLACE.

Deleted rows​

  • Soft deletes flow through. If your app marks a row as deleted with a column such as deleted_at, the change is copied like any other update.
  • Hard deletes are not copied by UPDATED_AT_CURSOR or ROWID: the rows stay in OSO. Only FULL_REPLACE removes them, because it replaces the whole table on each run.

Troubleshooting​

When you connect a database, OSO runs its checks in order and shows the first one that fails.

MessageCause and fix
The API token is not activeCloudflare reports the token as expired, disabled or deleted. Create a new token and update the secret.
No D1 database with this id in this accountThe database id is wrong, or the database is in another account. Check both ids.
The token cannot read D1 in this accountThe token lacks Account → D1 → Read, or it is limited to another account. Fix the token's permissions.

The other connect errors name the field or table that failed:

  • The secret is missing. The named secret does not exist in the chosen environment. Add it, or pick another environment.
  • A table or column does not exist. The table or column was renamed or dropped since the list was loaded. Click Check and list tables again.
  • The key does not match. The primary key you gave differs from the key the table declares in D1.
  • A cursor or key column is nullable. Pick another cursor column, or use ROWID or FULL_REPLACE.
  • ROWID needs an INTEGER PRIMARY KEY. The table has none. Pick another read method.
  • FULL_REPLACE on a WITHOUT ROWID table needs a key. Pick another table or add a key in D1.

A run can also fail after connecting. A message such as "Table X is too big for full copy; pick a cursor read method" tells you which table and what to change. If the token was deleted or expired, update the secret and the next run picks it up.

Change an ingestion​

Click Manage on the Cloudflare D1 row and use the pencil icon to change the token or the tables. The account and the database cannot change. To copy another database, add a new ingestion.

Remove an ingestion​

Click Manage on the Cloudflare D1 row and use the trash icon on the ingestion. This deletes the dataset in OSO and stops future copies. Nothing changes in D1. To stop OSO's access completely, also delete the API token in Cloudflare.