<!-- Modified by Ryuz Corp (Cello): d1 export and import; d1 execute --batch. -->
# D1

D1 is a SQL database that a Worker reaches through a binding. Cello gives each
database its own SQLite file inside one cell. Read the
[Cloudflare D1 documentation](https://developers.cloudflare.com/d1/worker-api/d1-database/)
for the API.

## Example

The [D1 example](https://github.com/ryuzcorp/cello/tree/main/examples/d1) implements a guestbook with a D1 database.

<!-- cello-example: d1 -->

## API

- `prepare(sql)` returns a statement, and `bind(...values)` returns a new
  statement that carries the values.
- `all()` returns `success`, a `results` array of row objects, and `meta`.
  `run()` is the same method under a second name.
- `first()` returns the first row or `null`, and `first(column)` returns one
  column of that row.
- `raw()` returns the rows as arrays, and `raw({ columnNames: true })` puts the
  column names in the first array.
- `batch(statements)` runs prepared statements in one transaction and returns
  one result for each statement.
- `exec(sql)` runs one or more statements without parameters, such as a schema.

## Configuration and limits

A `d1_databases` entry gives a `binding`, a `database_name`, and a
`database_id`. Cello uses the `database_id` as the identity, or the
`database_name` when the entry has no id. Two Workers that name one identity
reach one database. Cello creates a database on first use, so a typo in the
name opens an empty database and does not cause an error.

`bind()` checks each value. A number and a string pass, a boolean becomes `1`
or `0`, and an `ArrayBuffer`, a typed array, or an array of byte numbers becomes
a `BLOB`. Any other value throws `D1_TYPE_ERROR`. Cello binds the positional
parameters `?` and `?NNN`.

Cello enforces these
[D1 limits](https://developers.cloudflare.com/d1/platform/limits/): a statement
of at most 100,000 bytes, at most 100 bound parameters, at most 100 columns in a
table, and a string, a `BLOB`, or a row of at most 2,200,000 bytes.

A prepared statement that holds a second statement fails with
`A prepared SQL statement must contain only one statement.` Use `exec()` or
`batch()` for more than one statement. Both run inside one transaction, so a
failure rolls back the whole call.

Every statement goes to the node that owns the database cell, so each call costs
one cell dispatch. Group related statements in one `batch()`, which costs one
dispatch for all of them. The 100,000-row result cap does not apply to
`first()`. An acknowledged write survives the loss of the owner
node.

`withSession()` always selects the primary. `getBookmark()` returns
`cello:primary`, `meta.served_by_primary` is always `true`, and
`meta.served_by_region` is always `local`.

Application SQL cannot use `BEGIN`, `COMMIT`, `ROLLBACK`, `SAVEPOINT`, `ATTACH`,
`DETACH`, `VACUUM`, `load_extension()`, or a temporary table, index, trigger, or
view. Cello allows the `fts5` and `fts5vocab` virtual tables, and it accepts the
`PRAGMA` list in the
[D1 SQL statement reference](https://developers.cloudflare.com/d1/sql-api/sql-statements/).

`cello d1 execute` and `cello d1 migrations apply` run SQL from the command line
through a live node. Append `--local` to any `cello d1` subcommand to target
the `cello dev` node instead of the fleet: the command reads the database
identity from the project as usual, then reaches the running dev node through
`PROJECT/.cello/dev`, which carries that node's leases and secret. Pass the
same `--state DIR` (or `CELLO_DEV_STATE`) when the node runs with an explicit
state directory. The dev node must be running, and `--local` takes no
`--bucket`, `--endpoint`, `CELLO_BUCKET`, or `S3_ENDPOINT`. `cello d1 execute
--batch` reads a JSON array of statements from
stdin and runs it as one transactional batch -- D1's
`batch()` semantics, one result per statement -- printing one JSON array; a
caller issuing many reads pays one round trip instead of one subprocess per
query. `cello d1 export` writes the database as SQL — schema
beside data — read under one transaction on the owning node, so the dump is a
single snapshot even while the database takes writes. It prints to stdout, or
to `--output`; `--table` (repeatable), `--no-schema`, and `--no-data` narrow
it, as `wrangler d1 export` does. Import a dump with
`cello d1 execute DATABASE --file dump.sql`, which replays it in one
transaction: a failure rolls the whole import back. One dump carries at most
32 MiB; export fewer tables at a time past that. An export builds the whole
dump inside one cell call, so a database near the cap holds the owner
isolate's event loop for the seconds the dump takes; export fewer tables at
a time when tail latency matters.

## Differences from Cloudflare

- A binding result can contain at most 100,000 rows or 32 MiB.
- A SQLite `TEXT` value that is not valid UTF-8 decodes with the replacement
  character U+FFFD, as workerd decodes it, and the stored bytes do not change.
  Store arbitrary bytes in a `BLOB`.
- A database has one writer. Use more databases to increase write capacity.
- Cello has no read replica. `withSession()` always selects the primary, and
  `getBookmark()` returns `cello:primary`.
- A read-only statement reports `last_row_id` and `changes` as `0`. Cloudflare
  reports the values that the previous write left on the connection.
- `prepare()` accepts exactly one statement.
- `exec()` counts each statement. Cloudflare counts each input line.
- `dump()` throws. The database is a SQLite file in the operator's own bucket.
- Application SQL cannot use `BEGIN`, `SAVEPOINT`, `ATTACH`, `VACUUM`,
  `load_extension()`, or a temporary table.
- Cello has no D1 REST API and no Time Travel. Use `cello d1` for SQL, for
  migrations, and for exports.
- A large export stalls the owner isolate's event loop while it builds the
  dump, like a large exec already does.

The [Cloudflare compatibility](/cloudflare-compat#services) page lists the
runtime APIs and the unsupported services.
