# D1

D1 is a SQL database that a Worker reaches through a binding. celld 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](../../examples/d1) implements a guestbook with a D1 database.

<!-- celld-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`. celld 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. celld 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`. celld binds the positional
parameters `?` and `?NNN`.

celld 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
`celld: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. celld 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/).

`celld d1 execute` and `celld d1 migrations apply` run SQL from the command line
through a live node.

## 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.
- celld has no read replica. `withSession()` always selects the primary, and
  `getBookmark()` returns `celld: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.
- celld has no D1 REST API and no Time Travel. Use `celld d1` for SQL and for
  migrations.

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