Database

Every app has its own SQLite database, reached through sdk.db. The database is durable and scoped to your app: no other app can read it or write to it (see the createSdk client in the SDK Overview). There is nothing to provision: the database is created on the first call.

API

import { createSdk } from "@bext-stack/framework/sdk";

const sdk = createSdk("<app-id>");

const posts = await sdk.db.all<{ id: number; title: string }>(
  "SELECT id, title FROM posts WHERE status = ? ORDER BY id DESC",
  ["published"],
);
const post = await sdk.db.get("SELECT * FROM posts WHERE id = ?", [42]);
const { changes, lastInsertId } = await sdk.db.exec(
  "INSERT INTO posts (title, status) VALUES (?, ?)",
  ["Hello", "draft"],
);

changes is the number of rows modified, lastInsertId the rowid of the last insert. Call sdk.db on the server: a loader, an action, a route.ts or a task (inside a task the same client is available as ctx.db, see Tasks & Scheduler).

On failure, every method throws an Error carrying SQLite's message (unknown table, constraint violation, syntax error…). A query that matches nothing is not an error: all returns [] and get returns null.

Values come back with their SQLite type: an integer or a real becomes a number, text a string, NULL becomes null. A BLOB is returned base64-encoded.

Parameters

Write ? placeholders in the SQL text and pass the values in the params array, in order. A value passed this way is bound as data: it is never concatenated into the SQL. Do not build queries by concatenating strings with user-supplied values.

In practice:

• Declare column types (INTEGER, REAL, TEXT). An INTEGER column converts "42" to an integer on write and on comparison, so WHERE id = ? with [42] works. A column with no declared type keeps the string as is, and WHERE n = 42 no longer finds it.
• For a null value, write NULL in the SQL instead of passing null: UPDATE posts SET published_at = NULL WHERE id = ?.
• Store booleans as 1 / 0, and objects with JSON.stringify in a TEXT column.

One statement per call

Each call runs a single SQL statement. If the text holds several, separated by ;, only the first one runs. Create your tables and indexes with one exec call each.

No transaction spans several calls: each call is independent, and a BEGIN left open is rolled back when the call ends. To write several rows atomically, do it in one statement:

await sdk.db.exec(
  "INSERT INTO tags (post_id, name) VALUES (?, ?), (?, ?), (?, ?)",
  [postId, "prism", postId, "sqlite", postId, "sdk"],
);

A write that fails midway (constraint, deadline exceeded) leaves no partially written rows.

Writes with RETURNING

exec rejects a statement that returns rows: an INSERT … RETURNING fails there before writing anything. Pass it to all (or get), which runs it in a transaction and returns the rows:

const created = await sdk.db.get<{ id: number }>(
  "INSERT INTO posts (title, status) VALUES (?, ?) RETURNING id",
  ["Hello", "draft"],
);

Limits

A result over a limit fails the whole call: it is not truncated. get reads every row before keeping the first one: add LIMIT 1 to a query that can return many. Page through large reads with LIMIT ? OFFSET ? or, better, with a condition on the key (WHERE id < ? ORDER BY id DESC LIMIT ?).

Setting up the schema

sdk.db has no migration tool: the schema is created from your code, with idempotent statements. The usual pattern is an ensureSchema function memoized as a promise: it runs once per isolate, and concurrent requests share the same initialization. Each isolate has its own module state, so the statements must be safe to replay:

// src/lib/db.ts
import { createSdk } from "@bext-stack/framework/sdk";

export const sdk = createSdk("<app-id>");

let schema: Promise<void> | null = null;

export function ensureSchema(): Promise<void> {
  schema ??= (async () => {
    await sdk.db.exec(
      "CREATE TABLE IF NOT EXISTS reservations (id INTEGER PRIMARY KEY, email TEXT NOT NULL, day TEXT NOT NULL, people INTEGER NOT NULL, created_at TEXT NOT NULL)",
    );
    await sdk.db.exec(
      "CREATE INDEX IF NOT EXISTS reservations_day ON reservations (day)",
    );
    // Added later: ALTER TABLE fails if the column already exists.
    try {
      await sdk.db.exec("ALTER TABLE reservations ADD COLUMN status TEXT DEFAULT 'pending'");
    } catch {}
  })();
  return schema;
}

// src/app/api/reservations/route.ts
import { ensureSchema, sdk } from "../../../lib/db";

export async function POST(request: Request) {
  await ensureSchema();
  const { email, day, people } = await request.json();
  const { lastInsertId } = await sdk.db.exec(
    "INSERT INTO reservations (email, day, people, created_at) VALUES (?, ?, ?, ?)",
    [email, day, people, new Date().toISOString()],
  );
  return Response.json({ id: lastInsertId });
}

Append changes at the end of ensureSchema, without editing the statements already in place. ALTER TABLE … ADD COLUMN throws when the column already exists: wrap it in try/catch, as above, or check the columns first with SELECT name FROM pragma_table_info('reservations').

sdk.db and entities

sdk.db holds your app's own state: bookings, drafts, logs, counters. Tenant business data (contacts, invoices, articles…) stays in the Platform API, which the screens generated by defineEntity use (see Entities).

The framework's sql tagged template, covered on the Entities page, can also prepare a query for sdk.db: its compile() method returns { text, params } with ? placeholders.

import { sql } from "@bext-stack/framework";

const q = sql`SELECT id, title FROM posts WHERE id IN (${ids}) ORDER BY id`.compile();
const rows = await sdk.db.all(q.text, q.params);

The parameters then follow the same rules as above: they are sent as strings.

Where to next