c3 — Structured Data/Overview

c3 — Structured Data

c3 is the SQL database service built into tissue.systems. Each account gets access to a persistent, full-featured SQLite database per database name. Cells interact with c3 through a binding declared in ribo.toml — no connection strings, no credentials, no network setup.

The name comes from the C3 carbon fixation pathway, which converts carbon into stable, structured molecules. c3 does the same for your data.

The data model

c3 uses SQLite as its storage engine. Data is organized in the standard relational model:

  • A database is a named container for tables. You create databases with ribo db create. Each database is isolated from all others.
  • A table holds rows of structured data. You define tables in SQL with CREATE TABLE.
  • Columns have a declared type: INTEGER, TEXT, REAL, BLOB, or NULL.

Your account can have as many databases as you need. Two users can both have a database named myapp — they are completely separate.

Data types

SQLite uses a flexible type system with five storage classes:

Type Description JS equivalent
INTEGER Signed integer, up to 8 bytes number (integer)
REAL 8-byte IEEE 754 floating point number (float)
TEXT UTF-8 string string
BLOB Raw bytes not bindable — see below
NULL Absence of value null

Column type declarations like TEXT NOT NULL are recommendations, not hard constraints — SQLite stores whatever value you insert. Use NOT NULL and CHECK constraints to enforce types at the database level.

Binary values cannot be bound as parameters: query parameters travel as JSON, so there is no way to send raw bytes through the binding (an ArrayBuffer or Uint8Array would be stringified, not stored as a BLOB). Store binary data as base64-encoded TEXT, or keep it in g7 object storage and store a reference in c3. Existing BLOB columns read back as arrays of byte numbers, not ArrayBuffer.

For structured data like JSON objects and arrays, use TEXT and serialize with JSON.stringify / JSON.parse.

For dates and timestamps, use TEXT in ISO 8601 format (2024-05-01T14:30:00Z) or INTEGER as Unix epoch seconds. SQLite's built-in datetime() functions understand both.

Creating a database

ribo db create myapp

Database names must be lowercase, alphanumeric, and may contain hyphens (my-database). Create as many as you need — one per application, or one per concern within an application.

Binding a database to a Cell

In ribo.toml, declare a [[bindings]] block for each database the Cell needs:

[cell]
name = "my-cell"
js   = "./cell.js"

[[bindings]]
type     = "c3"
binding  = "DB"
database = "myapp"

The binding value becomes a property on the env object inside your Cell. Multiple databases get separate bindings:

[[bindings]]
type     = "c3"
binding  = "USERS"
database = "users"

[[bindings]]
type     = "c3"
binding  = "EVENTS"
database = "event-log"

Any name is valid for binding. Convention is ALL_CAPS.

Schema initialization

c3 databases start empty — there are no tables until you create them. A common pattern is to run your CREATE TABLE IF NOT EXISTS statements at the top of every fetch call. exec() accepts a multi-statement SQL string — every semicolon-separated statement runs, in order — so the whole schema fits in one call:

const SCHEMA = `
  CREATE TABLE IF NOT EXISTS posts (
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    title      TEXT    NOT NULL,
    body       TEXT    NOT NULL,
    created_at TEXT    NOT NULL DEFAULT (datetime('now'))
  );

  CREATE TABLE IF NOT EXISTS tags (
    id      INTEGER PRIMARY KEY AUTOINCREMENT,
    post_id INTEGER NOT NULL REFERENCES posts(id),
    name    TEXT    NOT NULL
  );
`;

export default {
  async fetch(request, env) {
    await env.DB.exec(SCHEMA);
    // your handler logic
  },
};

IF NOT EXISTS makes this safe to call on every request — SQLite checks the schema catalog and skips creation when the table already exists. Multi-statement strings work only when no bind parameters are given — with parameters, exec() runs a single prepared statement. That's fine for schema DDL, which never carries parameters. (From the CLI, ribo db exec <db> --file schema.sql handles multi-statement files too.)

Writing and reading data

export default {
  async fetch(request, env) {
    await env.DB.exec(
      "CREATE TABLE IF NOT EXISTS notes (id INTEGER PRIMARY KEY AUTOINCREMENT, text TEXT NOT NULL)"
    );

    const url = new URL(request.url);

    if (request.method === "POST") {
      const { text } = await request.json();
      const { meta } = await env.DB.prepare(
        "INSERT INTO notes (text) VALUES (?)"
      ).bind(text).run();
      return Response.json({ id: meta.last_insert_rowid });
    }

    if (url.pathname.startsWith("/notes/")) {
      const id = parseInt(url.pathname.slice(7));
      const note = await env.DB.prepare(
        "SELECT * FROM notes WHERE id = ?"
      ).bind(id).first();
      if (!note) return new Response("Not found", { status: 404 });
      return Response.json(note);
    }

    const { results } = await env.DB.prepare(
      "SELECT * FROM notes ORDER BY id DESC LIMIT 50"
    ).all();
    return Response.json(results);
  },
};

Transactions

batch() runs its statements as a single transaction: the whole batch is wrapped in BEGIN IMMEDIATE … COMMIT on one connection. Use it whenever a group of writes must succeed or fail together:

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, user_id) VALUES (?, ?)").bind("order_created", userId),
]);

Statements run in order, and a SELECT inside a batch sees the writes made by earlier statements in the same batch. If any statement errors, the entire batch is rolled back — nothing is committed, including the statements that ran before the error. The binding does not expose raw BEGIN / COMMIT statements; batch() is the transaction mechanism.

A batch cannot span multiple fetch invocations. Submit all related statements together within a single handler call.

Indexes

SQLite uses a table scan by default. Add indexes for columns you query often:

CREATE INDEX IF NOT EXISTS idx_posts_created_at ON posts(created_at);
CREATE INDEX IF NOT EXISTS idx_tags_post_id ON tags(post_id);
CREATE UNIQUE INDEX IF NOT EXISTS idx_users_email ON users(email);

Include these in your schema initialization block and they will be created alongside the tables.

Schema migrations

For apps that evolve over time, a simple version-tracked migration pattern works well:

const MIGRATIONS = [
  "CREATE TABLE IF NOT EXISTS schema_version (version INTEGER NOT NULL)",
  `CREATE TABLE IF NOT EXISTS posts (
     id INTEGER PRIMARY KEY AUTOINCREMENT,
     title TEXT NOT NULL,
     body TEXT NOT NULL,
     created_at TEXT NOT NULL DEFAULT (datetime('now'))
   )`,
];

async function migrate(db) {
  await db.exec("CREATE TABLE IF NOT EXISTS schema_version (version INTEGER NOT NULL)");
  const row = await db.prepare("SELECT MAX(version) as v FROM schema_version").first();
  const current = row?.v ?? -1;

  for (let i = current + 1; i < MIGRATIONS.length; i++) {
    await db.batch([
      db.prepare(MIGRATIONS[i]),
      db.prepare("INSERT INTO schema_version (version) VALUES (?)").bind(i),
    ]);
  }
}

export default {
  async fetch(request, env) {
    await migrate(env.DB);
    // ...
  },
};

Each migration runs once and is recorded. New migrations added to the array run on the next request.

Multiple databases per Cell

You can bind more than one database to a single Cell:

[[bindings]]
type     = "c3"
binding  = "USERS_DB"
database = "users"

[[bindings]]
type     = "c3"
binding  = "ANALYTICS_DB"
database = "analytics"
export default {
  async fetch(request, env) {
    const user = await env.USERS_DB.prepare(
      "SELECT * FROM users WHERE id = ?"
    ).bind(userId).first();

    await env.ANALYTICS_DB.prepare(
      "INSERT INTO page_views (user_id, path, ts) VALUES (?, ?, datetime('now'))"
    ).bind(userId, path).run();

    return Response.json(user);
  },
};

Each binding targets a separate, independent database.

Managing databases with ribo

ribo db create myapp          # create a new database
ribo db list                  # list all databases for your account
ribo db exec myapp "SELECT * FROM posts LIMIT 5"   # run SQL directly
ribo db exec myapp --file schema.sql                # load a .sql file (schema, seed data, migrations)
ribo db drop myapp            # permanently delete a database and all data

ribo db exec is useful for inspecting data, running one-time migrations, or debugging during development. Use --file for multi-statement SQL files — it handles schemas and bulk data loads of any size in a single request.

See also