Data & Migrations

Two companion modules give PRISM apps a real data layer over bext's in-process SQLite:

@bext-stack/framework/migrate — a schema builder and a tracked, idempotent migration runner. This is the piece bext was missing: instead of lazy CREATE TABLE IF NOT EXISTS on every request (or an out-of-band db push), you define ordered migrations and a _bext_migrations ledger records what has run.
@bext-stack/framework/orm — an Eloquent-lite typed model / query-builder. Injection-safe by construction (every value flows through the sql tagged template) and it returns real typed objects — it zips the positional-array rows the bridge hands back, so you never touch .columns/.rows again.

When To Use It#

Any app that owns tables (not just calling the SDK stores).
You want schema changes to be reviewable, ordered, and applied exactly once.
You want typed reads/writes without hand-writing SQL and hand-zipping rows.

For a one-off query, the sql tagged template is still there underneath. Reach for the model when the table is a first-class thing your app reads and writes.

Migrations#

import { migrate, type Migration } from "@bext-stack/framework/migrate";

const migrations: Migration[] = [
  {
    id: "0001_create_notes",                 // unique, sortable — runs in id order
    up: (db) => db.schema.createTable("notes", (t) => {
      t.id();                                 // INTEGER PRIMARY KEY AUTOINCREMENT
      t.text("body").notNull();
      t.integer("author_id").references("users");
      t.timestamps();                         // created_at + updated_at (epoch ms)
    }),
    down: (db) => db.schema.dropTable("notes"),
  },
];

migrate({ db: ".bext/data/app.db" }, migrations);   // applies pending only — idempotent

migrate() is safe to call at the top of a loader/action or at boot: it creates the ledger, then runs only the migrations whose id isn't recorded yet.

Function	Does
`migrate(source, migrations)`	apply pending migrations in `id` order; returns `{ applied, skipped }`
`rollback(source, migrations, steps?)`	reverse the last `steps` (default 1), newest first, via each `down`
`migrationStatus(source, migrations)`	`{ id, applied, appliedAt? }[]` for every migration

source is { db } (a SQLite path → the native bridge) or { executor } (see Testing).

Schema builder#

createTable(name, build) and dropTable(name) return SQL strings (used by db.schema.* inside a migration, or standalone). Column builders chain:

t.id();                                  // auto-increment PK
t.text("email").notNull().unique();
t.integer("age");
t.integer("org_id").references("orgs");  // FK → orgs(id)
t.real("price").default(0);
t.boolean("active").default(true);       // stored INTEGER 0/1
t.json("meta");                          // TEXT
t.text("created").defaultRaw("CURRENT_TIMESTAMP");
t.timestamps();                          // created_at + updated_at

Models & queries#

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

interface Note { id: number; body: string; author_id: number; created_at: number; updated_at: number }

const Notes = defineModel<Note>({ table: "notes", db: ".bext/data/app.db", timestamps: true });

Notes.create({ body: "hello", author_id: 1 });   // INSERT → returns the row (id + timestamps filled)
Notes.find(3);                                    // by primary key, or null
Notes.all();                                      // every row
Notes.count();

Notes.where("author_id", "=", 1)                  // chainable query builder
  .where("body", "like", "%draft%")
  .orderBy("created_at", "desc")
  .limit(20)
  .all();

Notes.update(3, { body: "edited" });              // by PK; bumps updated_at when timestamps:true
Notes.delete(3);
Notes.query().whereIn("id", [1, 2, 3]).delete();  // bulk

Member	Returns
`create(data)`	the inserted row (re-read by PK)
`find(id)` / `first()`	`T \| null`
`all()` / `where(col, op, val)` / `query()`	rows / a `QueryBuilder<T>`
`count()`	`number`
`update(id, data)` / `delete(id)`	affected count
`raw(sql\`…`)`	escape hatch, rows typed `T`

Query builder: .where(col, op, val), .whereIn(col, vals), .orderBy(col, dir), .limit(n), .offset(n), then .all() / .first() / .count() / .update(data) / .delete(). Operators are a fixed allowlist (=, !=, <, <=, >, >=, like, not like); .toSql() shows the compiled query. Every value is a bound parameter — Notes.where("body", "=", userInput) can never inject.

Testing#

Execution is pluggable via an Executor, so the identical model/migration code runs in the isolate (default, native bridge) or against any SQLite in a unit test — e.g. bun:sqlite:

import { Database } from "bun:sqlite";
const db = new Database(":memory:");
const executor = {
  rows: (text, params) => db.query(text).all(...params),
  run:  (text, params) => { const r = db.run(text, ...params); return { changes: r.changes, lastInsertRowid: Number(r.lastInsertRowid) }; },
};
migrate({ executor }, migrations);
const Notes = defineModel<Note>({ table: "notes", executor });

Try It#

The live SQLite CRUD demo runs a real migration and a typed model — add and delete notes and watch it persist. Source in sites/demo/src/app/examples/db-crud/page.tsx.