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 a single SQL statement. For multiple statements in one call, separate them with a semicolon — SQLite's exec mode handles multi-statement strings.

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

Return value

Returns { count: number, duration: number } — the number of rows affected and wall-clock time in milliseconds. 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 a new statement with the values attached. Chainable:

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

Supported value types

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           // 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, meta } = await env.DB.prepare(
  "SELECT * FROM posts ORDER BY created_at DESC LIMIT ?"
).bind(50).all();

// results: [{ id: 5, title: "...", body: "..." }, ...]
// meta.rows_read: number of rows scanned
// meta.duration: query time in milliseconds

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[], meta }.

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. All statements run in one atomic transaction — either all succeed and are committed, or an error in any statement rolls back all of them.

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.

Error handling

If any statement fails, the entire batch is rolled back and batch() rejects with an error. No partial commits occur.

try {
  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),
  ]);
} catch (err) {
  return Response.json({ error: "Transfer failed" }, { status: 500 });
}

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 (simple LIKE)

FTS5 is not available. Use LIKE for simple substring search:

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, maintain a separate normalized text column or use an external search service.

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();

See also

• c3 Overview — data model, schema management, transactions
• c3 Limitations — constraints and known limits