c3 API Reference

The c3 binding is available as env.<BINDING> inside any Cell that declares a [[bindings]] block with type = "c3". All methods return Promises.

exec

Run a raw SQL string and discard the result. Use for DDL statements, schema initialization, and any SQL that does not return rows.

await env.DB.exec("CREATE TABLE IF NOT EXISTS items (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL)");
await env.DB.exec("DROP TABLE IF EXISTS old_cache");
await env.DB.exec("CREATE INDEX IF NOT EXISTS idx_items_name ON items(name)");

exec accepts multi-statement SQL: pass a semicolon-separated string and every statement runs, in order — convenient for schema initialization:

await env.DB.exec(`
  CREATE TABLE IF NOT EXISTS items (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL);
  CREATE INDEX IF NOT EXISTS idx_items_name ON items(name);
`);

Multi-statement strings work only when no bind parameters are given. With parameters, exec runs a single prepared statement — parameters and multi-statement strings do not mix. (From the CLI, ribo db exec <db> --file script.sql handles multi-statement files too.)

Do not interpolate user input into exec strings. Use prepare().bind() for any value that comes from outside your code.

Return value

Returns { results, meta } where meta is { duration_ms, rows_affected, last_insert_rowid } — wall-clock time in milliseconds, rows changed, and the rowid of the most recent insert. For a multi-statement string, rows_affected is the total across all statements. These are rarely needed for DDL.

prepare

Create a parameterized statement. Placeholders are positional ? characters.

const stmt = env.DB.prepare("SELECT * FROM items WHERE category = ? AND price < ?");

Returns a statement object. Call .bind(...values) before executing to supply parameter values, then call a terminal method (run, all, first, or raw).

prepare itself does not execute the query — it only compiles the statement. The query runs when you call a terminal method.

bind

Bind values to a prepared statement's ? placeholders, in order.

stmt.bind(value1, value2, ...)

Returns the same statement with the values attached (bind mutates the statement and returns it). Chainable:

await env.DB.prepare("INSERT INTO items (name, price) VALUES (?, ?)").bind("widget", 9.99).run();

Supported value types

JavaScript type	SQLite storage class
`number` (integer)	`INTEGER`
`number` (float)	`REAL`
`string`	`TEXT`
`null`	`NULL`
`boolean`	`INTEGER` (1 or 0)

Binary values (ArrayBuffer, Uint8Array) are not supported as parameters — values travel as JSON, so raw bytes cannot be bound and would not be stored as a BLOB. Store binary data as base64-encoded TEXT, or use g7 object storage for binary payloads. BLOB columns read back as arrays of byte numbers, not ArrayBuffer.

Pass null explicitly to store a SQL NULL:

await env.DB.prepare("INSERT INTO users (name, bio) VALUES (?, ?)").bind("Alice", null).run();

run

Execute a statement that does not return rows. Returns a result object with a meta property containing execution metadata.

const { meta } = await env.DB.prepare(
  "INSERT INTO items (name, price) VALUES (?, ?)"
).bind("gadget", 14.99).run();

meta.last_insert_rowid  // integer — rowid of the inserted row (INSERT only)
meta.rows_affected      // integer — rows changed (UPDATE or DELETE)
meta.duration_ms        // number  — query execution time in milliseconds

Use run() for INSERT, UPDATE, and DELETE.

Example: INSERT and return the new row

const { meta } = await env.DB.prepare(
  "INSERT INTO posts (title, body) VALUES (?, ?)"
).bind(title, body).run();

const post = await env.DB.prepare(
  "SELECT * FROM posts WHERE id = ?"
).bind(meta.last_insert_rowid).first();

return Response.json(post);

Example: UPDATE with rows_affected check

const { meta } = await env.DB.prepare(
  "UPDATE items SET price = ? WHERE id = ?"
).bind(newPrice, id).run();

if (meta.rows_affected === 0) {
  return new Response("Not found", { status: 404 });
}
return Response.json({ updated: true });

all

Execute a SELECT statement and return all matching rows as an array of plain objects.

const { results } = await env.DB.prepare(
  "SELECT * FROM posts ORDER BY created_at DESC LIMIT ?"
).bind(50).all();

// results: [{ id: 5, title: "...", body: "..." }, ...]

Each row is a plain JavaScript object with column names as keys. Column types are mapped to JavaScript types as described in the bind section above.

Returns { results: object[] } — there is no meta property on all() results.

Empty results

When no rows match, results is an empty array — not null. Check results.length rather than the array itself:

const { results } = await env.DB.prepare(
  "SELECT * FROM items WHERE category = ?"
).bind("unknown").all();

if (results.length === 0) {
  return Response.json({ items: [] });
}

first

Execute a SELECT statement and return the first matching row, or null if there are no results.

// Return the full row as an object
const item = await env.DB.prepare(
  "SELECT * FROM items WHERE id = ?"
).bind(42).first();
// → { id: 42, name: "widget", price: 9.99 } or null

// Return a single column value
const name = await env.DB.prepare(
  "SELECT name FROM items WHERE id = ?"
).bind(42).first("name");
// → "widget" or null

When a column name is passed, first() returns the raw column value rather than a row object.

first is equivalent to appending LIMIT 1 — it is more efficient than all() when you only need one row.

Existence check

const exists = await env.DB.prepare(
  "SELECT 1 FROM users WHERE email = ?"
).bind(email).first() !== null;

Aggregate values

const count = await env.DB.prepare(
  "SELECT COUNT(*) as n FROM posts WHERE published = 1"
).first("n");
// → 42 (number)

const latest = await env.DB.prepare(
  "SELECT MAX(created_at) as ts FROM events"
).first("ts");
// → "2024-05-01T14:30:00" (string) or null if table is empty

raw

Execute a SELECT statement and return rows as arrays of values rather than objects. Column names are omitted.

const rows = await env.DB.prepare(
  "SELECT id, name, price FROM items WHERE active = 1"
).bind(1).raw();
// → [[1, "widget", 9.99], [2, "gadget", 14.99], ...]

Use raw() when you need to minimize response size or when the column names are redundant (for example, when building a CSV export).

To include column names as the first row:

const { results } = await env.DB.prepare(
  "SELECT id, name, price FROM items"
).all();

const columnNames = Object.keys(results[0] ?? {});
const rows = [columnNames, ...results.map(r => Object.values(r))];

batch

Execute multiple statements in a single round-trip, as a single transaction. The whole batch is wrapped in BEGIN IMMEDIATE … COMMIT on one connection: statements run in order, and if any statement errors, everything is rolled back — no partial commits.

const results = await env.DB.batch([
  env.DB.prepare("INSERT INTO orders (user_id, total) VALUES (?, ?)").bind(userId, total),
  env.DB.prepare("UPDATE users SET order_count = order_count + 1 WHERE id = ?").bind(userId),
  env.DB.prepare("INSERT INTO audit_log (action, ref_id) VALUES (?, ?)").bind("order_placed", orderId),
]);
// results: array of { results?, meta } — one entry per statement

Returns an array of result objects, one per statement, in the same order as the input array. SELECT statements within a batch return their rows in results[i].results, and they see the writes made by earlier statements in the same batch.

Error handling

If a statement fails, batch() rejects with an error, no further statements run, and the entire batch is rolled back — statements that ran before the error are not retained. The database is left exactly as it was before the batch started.

This makes batch() the tool for operations that need all-or-nothing semantics. A balance transfer is the classic example — either both updates commit, or neither does:

// Atomic: if either UPDATE fails, both are rolled back
// and no money is lost.
await env.DB.batch([
  env.DB.prepare("UPDATE accounts SET balance = balance - ? WHERE id = ?").bind(amount, fromId),
  env.DB.prepare("UPDATE accounts SET balance = balance + ? WHERE id = ?").bind(amount, toId),
]);

Mixed SELECT and write in one batch

const [selectResult, _insertResult] = await env.DB.batch([
  env.DB.prepare("SELECT COUNT(*) as total FROM items"),
  env.DB.prepare("INSERT INTO log (action) VALUES (?)").bind("counted"),
]);

const total = selectResult.results[0].total;

Common patterns

Pagination

const PAGE_SIZE = 20;
const page = Math.max(0, parseInt(new URL(request.url).searchParams.get("page") ?? "0"));

const { results } = await env.DB.prepare(
  "SELECT * FROM posts ORDER BY created_at DESC LIMIT ? OFFSET ?"
).bind(PAGE_SIZE, page * PAGE_SIZE).all();

const countRow = await env.DB.prepare(
  "SELECT COUNT(*) as n FROM posts"
).first("n");

return Response.json({
  posts: results,
  page,
  total: countRow,
  pages: Math.ceil(countRow / PAGE_SIZE),
});

Upsert (insert or update)

await env.DB.prepare(`
  INSERT INTO settings (key, value, updated_at)
  VALUES (?, ?, datetime('now'))
  ON CONFLICT(key) DO UPDATE SET
    value      = excluded.value,
    updated_at = excluded.updated_at
`).bind("theme", "dark").run();

Soft delete

await env.DB.prepare(
  "UPDATE posts SET deleted_at = datetime('now') WHERE id = ?"
).bind(id).run();

// Exclude soft-deleted rows in queries:
const { results } = await env.DB.prepare(
  "SELECT * FROM posts WHERE deleted_at IS NULL ORDER BY created_at DESC"
).all();

JSON values

// Store a JSON object in a TEXT column
await env.DB.prepare(
  "INSERT INTO events (type, payload) VALUES (?, ?)"
).bind("user.signup", JSON.stringify({ email, plan: "free" })).run();

// Retrieve and parse
const row = await env.DB.prepare(
  "SELECT payload FROM events WHERE id = ?"
).bind(id).first("payload");
const payload = JSON.parse(row);

Full-text search

For simple substring search, LIKE works with no setup:

const query = `%${searchTerm.replace(/[%_\\]/g, "\\$&")}%`;
const { results } = await env.DB.prepare(
  "SELECT * FROM posts WHERE title LIKE ? OR body LIKE ? ESCAPE '\\'"
).bind(query, query).all();

For heavier search requirements, SQLite's FTS5 extension is available — create a virtual table with CREATE VIRTUAL TABLE ... USING fts5(...) and query it with MATCH:

await env.DB.exec("CREATE VIRTUAL TABLE IF NOT EXISTS posts_fts USING fts5(title, body)");

const { results } = await env.DB.prepare(
  "SELECT rowid, title FROM posts_fts WHERE posts_fts MATCH ? ORDER BY rank"
).bind(searchTerm).all();

Date range queries

const { results } = await env.DB.prepare(`
  SELECT * FROM events
  WHERE created_at >= datetime('now', '-7 days')
  ORDER BY created_at DESC
`).all();