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.
- In the Cloudflare dashboard, open My Profile, then API Tokens, and create a custom token.
- Give it one permission: Account → D1 → Read.
- Under Account Resources, include only the one account that holds your database.
- 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.
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.
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
- From your organization, open Integrations in the sidebar. Find Cloudflare D1 (in the Dev category) and click Connect.
- Pick the environment and the secret that holds the token, then enter the account id and the database id.
- 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 method | What it reads | Rules |
|---|---|---|
UPDATED_AT_CURSOR | Only rows that are new or changed since the last run | The table needs a key. The cursor column and every key column must be NOT NULL. |
ROWID | Only rows added since the last run. Edits to old rows are not seen. | The table needs an INTEGER PRIMARY KEY. |
FULL_REPLACE | Every 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_CURSORorROWID: the rows stay in OSO. OnlyFULL_REPLACEremoves 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.
| Message | Cause and fix |
|---|---|
| The API token is not active | Cloudflare reports the token as expired, disabled or deleted. Create a new token and update the secret. |
| No D1 database with this id in this account | The database id is wrong, or the database is in another account. Check both ids. |
| The token cannot read D1 in this account | The 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
ROWIDorFULL_REPLACE. ROWIDneeds anINTEGER PRIMARY KEY. The table has none. Pick another read method.FULL_REPLACEon aWITHOUT ROWIDtable 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.