@clawnify/db

Dual-binding SQLite adapter for Clawnify apps. Auto-detects D1 (production WfP) or the Clawnify Storage capability binding (Dynamic Workers preview tier) and exposes a single API to user code.

Install

pnpm add @clawnify/db

That's it. drizzle-orm is a regular dependency of this package, so it's pulled in transitively — no separate install to remember. Apps that need to import from drizzle-orm directly can still pnpm add drizzle-orm to pin a specific version; pnpm dedupes.

Two APIs

Typed Drizzle (preferred for new apps)

Define your schema once in TypeScript. The query builder is typed against it, JSON columns auto-serialize, and D1_TYPE_ERROR: Type 'object' becomes structurally impossible.

// src/server/schema.ts
import { sqliteTable, text, integer } from "@clawnify/db";

export const users = sqliteTable("users", {
  id: integer("id").primaryKey({ autoIncrement: true }),
  email: text("email").notNull().unique(),
  prefs: text("prefs", { mode: "json" }).$type<{ theme: string }>(),
  createdAt: text("created_at").notNull().default("CURRENT_TIMESTAMP"),
});

// src/server/index.ts
import { getDB, eq } from "@clawnify/db";
import * as schema from "./schema";

app.get("/api/users/:id", async (c) => {
  const db = getDB(c.env, { schema });
  const user = await db
    .select()
    .from(schema.users)
    .where(eq(schema.users.id, Number(c.req.param("id"))))
    .get();
  return c.json(user);
});

app.post("/api/users", async (c) => {
  const db = getDB(c.env, { schema });
  await db.insert(schema.users).values({
    email: "[email protected]",
    prefs: { theme: "dark" },   // ← JSON column auto-serialized
  });
  return c.json({ ok: true });
});

The common Drizzle surface — operators (eq, and, sql, asc, desc, ...) and the SQLite schema DSL (sqliteTable, text, integer, ...) — is re-exported from @clawnify/db, so one import line covers a typical app. drizzle-orm is still a peer dep and you can import from it directly for anything not re-exported (custom types, relational query builder helpers, etc.).

Raw SQL (escape hatch)

For schema-as-data patterns (open-cms-style apps that let end users define collections at runtime), Drizzle can't model the dynamic tables. Use the raw API:

import { initDB, query, get, run, json } from "@clawnify/db";

initDB(c.env);

// Reads: identical signatures
const rows = await query<User>("SELECT * FROM users WHERE org_id = ?", [orgId]);
const row = await get<User>("SELECT * FROM users WHERE id = ?", [id]);

// Writes: wrap JSON columns with json()
await run(
  "INSERT INTO content_types (uid, info) VALUES (?, ?)",
  [uid, json(info)],
);

json(value) is JSON.stringify(value) but reads as intent at the call site. It returns null for null/undefined so the column stores NULL instead of the literal string "null".

Safety: assertBindable

Every raw query / get / run call walks the params array and throws a TypeError if any param isn't a scalar (string, number, boolean, bigint, null, undefined, Uint8Array, ArrayBuffer):

TypeError: @clawnify/db: param [3] is object — D1/SQLite only binds
scalars. If this is a JSON column, wrap with json(value).
Got: {"singularName":"post","pluralName":"posts",...}

This catches the class of bug where an app forgets to JSON.stringify a column. The native errors are unhelpful here:

• D1: D1_TYPE_ERROR: Type 'object' not supported for value 'X' (where 'X' is often the wrong param — D1 reports the first one it stringified, not the offending object)
• SQLite via Facet: Wrong number of parameter bindings for SQL query when the binding is silently dropped

The same assertBindable runs on the supervisor side as defense in depth, so direct StorageBinding.query consumers get the same actionable error.

Backend selection

getDB(env) and initDB(env) auto-pick the binding:

The Facet path uses sqlite-proxy and converts our object-row format to the positional-row shape Drizzle expects via Object.values(row). Works because Drizzle generates the SELECT column order itself; the object's insertion order matches what Drizzle reads back.

Migrations

Use drizzle-kit (installed by the consumer) to generate migrations:

pnpm drizzle-kit generate

This emits numbered SQL files in drizzle/ and a schema snapshot in drizzle/meta/. The build pipeline applies them transactionally and records state in __clawnify_migrations. Agents author migrations implicitly by editing schema.ts — no hand-written SQL.

A drizzle.config.ts belongs in the app root pointing at schema.ts. The Clawnify build harness picks it up automatically.

API surface

Typed Drizzle

• getDB(env, { schema?, logger? }) → DB<TSchema>, the query builder both backends share. Type helpers with DB<typeof schema>; never cast to a driver type.

Raw SQL

• initDB(env | D1Database) — call once per request before query/get/run
• query<T>(sql, params?) → T[]
• get<T>(sql, params?) → T | undefined
• run(sql, params?) → { changes, lastInsertRowid }
• json(value) → string | null
• assertBindable(params) — throws TypeError on non-scalar; called implicitly by query/get/run

Types

• StorageBinding — the RPC contract the supervisor implements
• StorageResult — { rows: Record<string, unknown>[]; meta?: { changes?, last_row_id? } }
• DB<TSchema> — the client getDB returns, named without its backend
• DBEnv — { DB?: D1Database; STORAGE?: StorageBinding }
• RunResult — { changes: number; lastInsertRowid: number }

Versioning

See also

• docs/internal/packages.md — overall Clawnify SDK strategy
• docs/internal/app-template.md — gold-standard app layout
• docs/internal/preview-tier.md — how the supervisor uses StorageBinding