Skip to content

d1.md

Latest commit

 

History

History
90 lines (72 loc) · 4.15 KB

d1.md

File metadata and controls

90 lines (72 loc) · 4.15 KB
Raw

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 for the API.

Example

The D1 example implements a guestbook with a D1 database.

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: 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.

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 page lists the runtime APIs and the unsupported services.