SQL Builder

Chainable SQL builder that makes hand-rolled SQL impossible. Every table and column name is quoted by construction through b.safeSql; every value is a bound ? placeholder, never string-interpolated. The builder emits BARE logical table names and ? placeholders - b.clusterStorage rewrites bare framework tables to their cluster-prefixed names and translates ? to $N for Postgres at execute time - so one query text runs unchanged against the local SQLite single-node backend and the operator-supplied external Postgres / MySQL in cluster mode.

The terminal call is .toSql() returning { sql, params }. Pass that straight to b.clusterStorage.execute(sql, params). The builder never touches the database itself - it is a pure SQL-string composer, which keeps it free of the residency / sealed-column write-path concerns that db.from(...) (the executing query builder, lib/db-query.js) owns.

Only upsert emits dialect-final syntax (Postgres / SQLite ON CONFLICT ... DO UPDATE, MySQL ON DUPLICATE KEY UPDATE); every other verb stays ?-placeholder + double-quote and defers the dialect rewrite to b.clusterStorage. Joins, common-table expressions, scalar and IN/EXISTS subqueries, grouping, aggregates, and RETURNING are all composable. DDL builders (createTable / createIndex / alterTable / dropTable) reuse the framework's own type map so operator app-schema tables get the same quote-by-construction guarantee the framework tables get.

Safety defaults are not opt-in: update and delete THROW without a where() unless allowNoWhere is set; a column-membership gate refuses unknown columns; LIKE auto-escapes % / _ / \ and emits the matching ESCAPE; raw fragments pass through b.guardSql (strict by default on the request path) plus the placeholder-count and embedded-literal scanners.

b.sql.SqlBuilderError #

Error thrown by every b.sql builder on a bad call shape - an unknown dialect, an invalid identifier, an unconditional update/delete, a placeholder-count mismatch, an empty value set, a conflicting upsert action, and so on. Extends FrameworkError and is always permanent: these are programming / config errors caught at SQL-composition time, well before the query reaches a driver, so retrying never makes them valid. The throw IS the security signal.

Carries a stable .code with a sql-builder/ prefix (sql-builder/bad-dialect, sql-builder/no-where, sql-builder/placeholder-mismatch, sql-builder/empty-values, sql-builder/conflict-action, sql-builder/unknown-column, ...) - the slash style mirrors SafeSqlError's codes and stays distinct from the dot-style codes b.guardSql raises.

var b = require("@blamejs/core");
try {
  b.sql.update("users").set({ active: false }).toSql();
} catch (e) {
  e instanceof b.sql.SqlBuilderError;   // -> true
  e.code;                               // -> "sql-builder/no-where"
}

b.sql.table(name, opts?) #

{
  schema:  string,   // schema qualifier, quoted at build time
  prefix:  string,   // operator app-table namespace, prepended then quoted
  alias:   string,   // table alias, used to disambiguate joins
}

Build a table reference. A bare default logical name (b.sql.table("audit_log")) stays UNQUOTED in the emitted SQL so b.clusterStorage can rewrite it to the cluster-prefixed name. A schema qualifier ({ schema: "public" } or the dotted form "public.users") or an operator app-table prefix is validated and quoted at build time - a bad identifier throws immediately. The prefix here is operator app-table namespacing, distinct from the framework's internal _blamejs_ prefix; it is prepended to the table name and the whole result is quoted as one identifier. At most two segments (schema.table). An alias is quoted and appended for joins.

var b = require("@blamejs/core");
b.sql.table("audit_log").toString("sqlite");
// -> "audit_log"               (bare default - clusterStorage rewrites)

b.sql.table("users", { schema: "public" }).toString("postgres");
// -> '"public"."users"'

b.sql.table("orders", { prefix: "shopX_" }).toString("sqlite");
// -> '"shopX_orders"'

b.sql.fn(name) #

Wrap an allowlisted, nullary, side-effect-free SQL function token for use as an INSERT values() / UPDATE set() right-hand side - a value position that must emit a keyword the engine evaluates server-side (a NOW() timestamp) rather than a bound ? parameter. The allowlist is exactly NOW / CURRENT_TIMESTAMP / CURRENT_DATE / CURRENT_TIME; an unknown name throws, so no arbitrary expression reaches a VALUES / SET position. The token is dialect-checked at emit (NOW() is Postgres / MySQL; CURRENT_TIMESTAMP is portable). The wrapped function consumes no ? and contributes no param.

var b = require("@blamejs/core");
b.sql.insert("events")
  .values({ topic: "x", at: b.sql.fn("CURRENT_TIMESTAMP") })
  .toSql();
// -> { sql: 'INSERT INTO events ("topic", "at") VALUES (?, CURRENT_TIMESTAMP)',
//     params: ["x"] }

b.sql.cast(value, type) #

Wrap a value so it binds as a single ? placeholder carrying a dialect-correct cast - ?::jsonb on Postgres, CAST(? AS json) on MySQL. The cast TYPE is matched against a fixed allowlist (jsonb / json / interval / uuid / text / int / bigint / timestamptz / boolean); an unknown type, or one with no portable form on the target dialect (interval / uuid are Postgres-only), throws at build. Use it for an INSERT values() / UPDATE set() cell that must coerce a bound string into a typed column (a JSON string into a jsonb column, a duration string into an interval).

var b = require("@blamejs/core");
b.sql.insert("docs", { dialect: "postgres" })
  .values({ id: 1, meta: b.sql.cast('{"a":1}', "jsonb") })
  .toSql();
// -> { sql: 'INSERT INTO docs ("id", "meta") VALUES (?, ?::jsonb)',
//     params: [1, '{"a":1}'] }

b.sql.toExternalSql(builtOrBuilder, dialect) #

Translate a built statement to a driver's positional placeholder form for code that hands the SQL to an operator-supplied driver DIRECTLY (no b.clusterStorage in the path to rewrite). Accepts either a chainable builder (any b.sql.select / insert / update / delete / upsert, via its own .toExternalSql() method) OR a plain { sql, params } result from a DDL builder (createTable / createIndex / alterTable / dropTable / the RLS + catalog builders). Postgres gets $1..$N; SQLite and MySQL keep ?. The ?-by-construction invariant is unchanged - only the emitted text differs at the last step.

var b = require("@blamejs/core");
var ddl = b.sql.toExternalSql(
  b.sql.createIndex("idx_pending", "outbox", ["next_attempt_at"],
    { dialect: "postgres", where: "status = 'pending'" }),
  "postgres");
// -> { sql: 'CREATE INDEX IF NOT EXISTS "idx_pending" ON outbox ' +
//          '("next_attempt_at") WHERE status = \'pending\'', params: [] }

b.sql.createTable(name, columns, opts?) #

{
  dialect:       string,   // postgres | sqlite | mysql (default sqlite)
  ifNotExists:   boolean,  // default true
  primaryKey:    array,    // composite PK column list (table-level)
}

Build a CREATE TABLE statement with every identifier quoted by construction and every column type drawn from the framework's own type map (so an operator app-schema table is portable across the same dialects the framework tables are). columns is an array of column specs; each { name, type, constraints?, primaryKey?, notNull?, unique?, default? }. The type is a logical name (int / text / blob / boolean / real / numeric / timestamp / json) mapped to the dialect token, or a verbatim dialect type string. Emits IF NOT EXISTS by default so re-running is idempotent.

var b = require("@blamejs/core");
b.sql.createTable("widget", [
  { name: "id",   type: "int",  primaryKey: true },
  { name: "name", type: "text", notNull: true },
], { dialect: "postgres" }).sql;
// -> 'CREATE TABLE IF NOT EXISTS widget ("id" BIGINT PRIMARY KEY, "name" TEXT NOT NULL)'
//   (the bare default table name is the clusterStorage rewrite
//    target; pass a prefix or schema to quote it)

b.sql.createIndex(name, tableName, columns, opts?) #

{
    dialect:      string,   // postgres | sqlite | mysql (default sqlite)
    unique:       boolean,  // default false
    ifNotExists:  boolean,  // default true
    where:        string,   // partial-index predicate (guarded raw fragment)
    whereParams:  Array,    // bound params for the partial-index predicate

  A partial index (`opts.where`) narrows the index to rows matching a
  boolean predicate - the publisher's pending-row index
  (`WHERE status = 'pending'`) is the canonical case. The predicate rides
  the same `b.guardSql`-gated raw-fragment path as `whereRaw` (a static
  operator-controlled literal opts in via `allowLiterals`); MySQL has no
  partial index, so it throws there.
}

Build a CREATE INDEX statement, identifiers quoted by construction, IF NOT EXISTS by default. columns is the indexed column list (each quoted); opts.unique emits a UNIQUE INDEX.

var b = require("@blamejs/core");
b.sql.createIndex("idx_widget_name", "widget", ["name"],
  { dialect: "sqlite", unique: true }).sql;
// -> 'CREATE UNIQUE INDEX IF NOT EXISTS "idx_widget_name" ON widget ("name")'
//   (the index name is quoted; the bare default table stays the
//    clusterStorage rewrite target)

b.sql.alterTable(name, change, opts?) #

{
  dialect:  string,   // postgres | sqlite | mysql (default sqlite)
}

Build an ALTER TABLE statement. change is one of { addColumn: { name, type, ... } }, { dropColumn: "name" }, or { renameColumn: { from, to } } - each identifier quoted, the add-column type drawn from the framework type map.

var b = require("@blamejs/core");
b.sql.alterTable("widget", { addColumn: { name: "active", type: "boolean" } },
  { dialect: "postgres" }).sql;
// -> 'ALTER TABLE widget ADD COLUMN "active" BOOLEAN'
//   (bare default table name; the added column is quoted)

b.sql.dropTable(name, opts?) #

{
  dialect:   string,   // postgres | sqlite | mysql (default sqlite)
  ifExists:  boolean,  // default true
  cascade:   boolean,  // default false (Postgres CASCADE)
}

Build a DROP TABLE statement, identifier quoted, IF EXISTS by default so dropping a missing table is a no-op.

var b = require("@blamejs/core");
b.sql.dropTable("widget", { dialect: "postgres", cascade: true }).sql;
// -> 'DROP TABLE IF EXISTS widget CASCADE'
//   (bare default table name; the clusterStorage rewrite target)

b.sql.createVirtualTable(name, opts) #

{
  columns:      Array,    // FTS5 columns: "name" | { name, unindexed }
  tokenize:     string,   // "unicode61 remove_diacritics 2" (built-in + allowlisted args)
  ifNotExists:  boolean,  // default true
}

Build a sqlite CREATE VIRTUAL TABLE ... USING fts5(...) statement for a full-text index - the construct b.sql.createTable has no form for. opts.columns is the FTS5 column list; each entry is a column name (a searched column) or { name, unindexed: true } (a stored-but-not- searched column, the join key). opts.tokenize names a built-in FTS5 tokenizer (unicode61 / ascii / porter / trigram) and optional allowlisted arguments (remove_diacritics 2); a custom / loadable tokenizer is refused. Every column name is quoted by construction and every tokenizer token is allowlisted, so no operator-supplied token reaches the DDL raw. IF NOT EXISTS by default. sqlite-only (FTS5 is a sqlite extension); a non-sqlite dialect throws at build.

var b = require("@blamejs/core");
b.sql.createVirtualTable("mail_fts", {
  columns:  [{ name: "objectid", unindexed: true }, "subject_toks", "body_toks"],
  tokenize: "unicode61 remove_diacritics 2",
}).sql;
// -> 'CREATE VIRTUAL TABLE IF NOT EXISTS "mail_fts" USING fts5(' +
//    '"objectid" UNINDEXED, "subject_toks", "body_toks", ' +
//    "tokenize = 'unicode61 remove_diacritics 2')"

b.sql.enableRowLevelSecurity(table, opts?) #

{
  schema:  string,   // schema qualifier, quoted at build time
  force:   boolean,  // default false - emit FORCE ROW LEVEL SECURITY
}

Build a Postgres ALTER TABLE ... ENABLE ROW LEVEL SECURITY statement, the table identifier quoted by construction (schema-qualified via { schema } or the dotted "schema.table" form). Postgres has no IF NOT EXISTS for this verb; the declarative migration in b.db.declareRowPolicy checks pg_class.relrowsecurity and skips the ALTER when already enabled, so re-running a partially-applied migration set does not fail. Refuses a non-Postgres dialect at build time.

var b = require("@blamejs/core");
b.sql.enableRowLevelSecurity("sessions",
  { schema: "public" }).sql;
// -> 'ALTER TABLE "public"."sessions" ENABLE ROW LEVEL SECURITY'

b.sql.disableRowLevelSecurity(table, opts?) #

{
  schema:  string,   // schema qualifier, quoted at build time
}

Build a Postgres ALTER TABLE ... DISABLE ROW LEVEL SECURITY statement (the inverse of enableRowLevelSecurity), the table identifier quoted by construction. Refuses a non-Postgres dialect at build time.

var b = require("@blamejs/core");
b.sql.disableRowLevelSecurity("sessions", { schema: "public" }).sql;
// -> 'ALTER TABLE "public"."sessions" DISABLE ROW LEVEL SECURITY'

b.sql.createPolicy(name, table, spec, opts?) #

{
  schema:        string,   // schema qualifier for the table
  guardProfile:  string,   // raw-fragment guard profile (default "strict")
}

Build a Postgres CREATE POLICY statement in canonical clause order: name -> table -> AS PERMISSIVE|RESTRICTIVE -> FOR -> TO -> USING () -> WITH CHECK (). The policy / table / role identifiers are quoted by construction; the using and withCheck boolean predicates ride the SAME b.guardSql-gated raw-fragment path as whereRaw (strict profile by default, embedded-literal + placeholder- count scanners), so an operator-influenced predicate cannot smuggle a stacked statement or a dangerous primitive. Refuses a non-Postgres dialect at build time.

spec.command is one of ALL (default) / SELECT / INSERT / UPDATE / DELETE; spec.permissive defaults true (a PERMISSIVE policy OR-combines with peers; false emits RESTRICTIVE, which AND-combines). spec.role is optional (omitted -> the policy applies to every role). The predicates default to binding no params - an RLS predicate references session GUCs / row columns - but a usingParams / withCheckParams array binds values for a parameterized predicate.

var b = require("@blamejs/core");
b.sql.createPolicy("tenant_isolation", "sessions", {
  role:      "app_user",
  command:   "ALL",
  using:     "tenant_id = current_setting('app.tenant_id')::uuid",
  withCheck: "tenant_id = current_setting('app.tenant_id')::uuid",
}, { schema: "public" }).sql;
// -> 'CREATE POLICY "tenant_isolation" ON "public"."sessions" ' +
//    'AS PERMISSIVE FOR ALL TO "app_user" ' +
//    "USING (tenant_id = current_setting('app.tenant_id')::uuid) " +
//    "WITH CHECK (tenant_id = current_setting('app.tenant_id')::uuid)"
//   (the static current_setting literal opts in via allowLiterals)

b.sql.dropPolicy(name, table, opts?) #

{
  schema:    string,   // schema qualifier for the table
  ifExists:  boolean,  // default true
}

Build a Postgres DROP POLICY statement, the policy + table identifiers quoted by construction, IF EXISTS by default so dropping a missing policy is a no-op (the migration down-path is idempotent). Refuses a non-Postgres dialect at build time.

var b = require("@blamejs/core");
b.sql.dropPolicy("tenant_isolation", "sessions", { schema: "public" }).sql;
// -> 'DROP POLICY IF EXISTS "tenant_isolation" ON "public"."sessions"'

b.sql.catalog.listTables() #

Build the sqlite catalog query that lists every user table - SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%'. This is the ONLY general path that emits an sqlite_master reference; the framework's b.safeSql.quoteIdentifier refuses an sqlite_-prefixed identifier for every other caller, so a sqlite_master scan cannot be hand-built through the normal builder. The sqlite_% LIKE pattern is a builder-emitted static literal (not operator input). sqlite-internal; no dialect option.

var b = require("@blamejs/core");
var q = b.sql.catalog.listTables();
// -> { sql: "SELECT name FROM sqlite_master WHERE type = 'table' " +
//          "AND name NOT LIKE 'sqlite_%'", params: [] }

b.sql.catalog.tableExists(name) #

Build the sqlite catalog existence probe for one table - SELECT name FROM sqlite_master WHERE type='table' AND name = ?, the table name BOUND as a ? parameter (never interpolated). Returns one row when the table exists, none otherwise.

var b = require("@blamejs/core");
b.sql.catalog.tableExists("audit_log");
// -> { sql: "SELECT name FROM sqlite_master WHERE type = 'table' AND name = ?",
//     params: ["audit_log"] }

b.sql.catalog.tableInfo(name) #

Build a PRAGMA table_info("

var b = require("@blamejs/core");
b.sql.catalog.tableInfo("audit_log").sql;
// -> 'PRAGMA table_info("audit_log")'

b.sql.catalog.sampleRandom(table, columns?, opts?) #

{
  limit:  number,   // bound LIMIT (required > 0)
}

Build a SELECT FROM "

var b = require("@blamejs/core");
b.sql.catalog.sampleRandom("sessions", ["_id", "email"], { limit: 50 });
// -> { sql: 'SELECT "_id", "email" FROM "sessions" ORDER BY RANDOM() LIMIT ?',
//     params: [50] }

b.sql.catalog.changes() #

Build SELECT changes() AS c - the sqlite scalar that reports the row count of the most recent INSERT / UPDATE / DELETE on the current connection. changes() is a sqlite-internal function with no table to select from, so the general builder (which requires a FROM table) has no form for it; this audited builder emits the exact zero-parameter probe the inbox sweep uses to learn how many rows a preceding DELETE removed. sqlite-only; the column alias is c.

var b = require("@blamejs/core");
b.sql.catalog.changes().sql;   // -> "SELECT changes() AS c"

b.sql.pragma(verb, arg?) #

{
  (none - the second positional is the allowlisted argument token)
}

Build a sqlite PRAGMA statement from a NARROW allowlist of verbs: journal_mode (set PRAGMA journal_mode=WAL or read PRAGMA journal_mode), synchronous (PRAGMA synchronous=NORMAL), and wal_checkpoint (PRAGMA wal_checkpoint(TRUNCATE)). The argument is matched against a fixed per-verb vocabulary - a journal mode / sync level / checkpoint mode - so no operator-influenced token reaches the PRAGMA argument position. A verb not on the allowlist throws; this is the audit boundary the at-rest key-rotation pipeline routes its PRAGMA statements through. Pass no arg to a set-or-read verb to read the current value. sqlite-only.

var b = require("@blamejs/core");
b.sql.pragma("journal_mode", "WAL").sql;      // -> 'PRAGMA journal_mode=WAL'
b.sql.pragma("synchronous", "NORMAL").sql;    // -> 'PRAGMA synchronous=NORMAL'
b.sql.pragma("wal_checkpoint", "TRUNCATE").sql; // -> 'PRAGMA wal_checkpoint(TRUNCATE)'
b.sql.pragma("journal_mode").sql;             // -> 'PRAGMA journal_mode'  (read)

b.sql.defineTable(name, spec, opts?) #

{
  dialect:           string,   // postgres | sqlite | mysql (default sqlite)
  prefix:            string,   // operator app-table namespace prefix
  schema:            string,   // schema qualifier
  autoPrimaryKey:    boolean,  // default true
  primaryKeyColumn:  string,   // default "id"
  autoForeignKeys:   boolean,  // default true (naming-convention inference)
  autoIndex:         boolean,  // default true
  indexes:           array,    // [{ columns: [...], unique?, name? }]
}

Declarative schema with built-in PK / FK / index optimization. Returns an ordered { statements: [{ sql, params }, ...] } bundle (the CREATE TABLE first, then each CREATE INDEX) to run in sequence. Three automation layers, each on by default and individually disablable:

- **Primary key** - if no column declares primaryKey / autoIncrement and opts.primaryKey is unset, an identity PK column (opts.primaryKeyColumn, default id) is auto-added in the dialect-correct form (BIGSERIAL / INTEGER AUTOINCREMENT / BIGINT AUTO_INCREMENT). Disable: autoPrimaryKey: false. - **Foreign keys** - a column named Id / _id infers a REFERENCES () constraint. Override one column with an explicit references ("table" or { table, column?, onDelete?, onUpdate? }) or opt it out with references: false. Disable all inference: autoForeignKeys: false. - **Indexes** - every FK column is auto-indexed (databases do not index FK columns for you), as is any column flagged index: true (unique: true is enforced inline). Add composite / custom indexes via opts.indexes. Disable auto-indexing: autoIndex: false.

Every index / FK column is gated against the table's declared column set - the same column-namespace discipline the query builder applies with allowedColumns - and every generated index name is bounded to the dialect identifier limit.

var b = require("@blamejs/core");
var ddl = b.sql.defineTable("orders", [
  { name: "userId", type: "int" },         // -> FK users(id) + index
  { name: "total",  type: "numeric" },
  { name: "email",  type: "text", index: true },
], { dialect: "postgres" });
ddl.statements.length;
// -> 3  (CREATE TABLE orders; CREATE INDEX on userId; CREATE INDEX on email)

b.sql.select(table, opts?) #

{
  dialect:         string,   // postgres | sqlite | mysql (default sqlite)
  schema:          string,   // schema qualifier for the table
  prefix:          string,   // operator app-table namespace prefix
  alias:           string,   // table alias (for joins)
  allowedColumns:  array,    // column-membership gate set
  columnGateMode:  string,   // reject | warn | off
}

Start a SELECT builder over table (a name, a "schema.table", or a b.sql.table(...) reference). Chain columns / aggregates / join family / where family / groupBy / having / orderBy / limit / offset, then call toSql() for { sql, params }. Emits bare default table names + ? placeholders so b.clusterStorage applies the cluster prefix + Postgres $N translation at execute time.

var b = require("@blamejs/core");
b.sql.select("users")
  .columns(["id", "email"])
  .where("status", "active")
  .orderBy("createdAt", "desc")
  .limit(10)
  .toSql();
// -> { sql: 'SELECT "id", "email" FROM users WHERE "status" = ? ORDER BY "createdAt" DESC LIMIT 10',
//     params: ["active"] }

b.sql.insert(table, opts?) #

{
  dialect:         string,   // postgres | sqlite | mysql (default sqlite)
  schema:          string,   // schema qualifier
  prefix:          string,   // operator app-table namespace prefix
  allowedColumns:  array,    // column-membership gate set
}

Start an INSERT builder. Provide rows via columns([...]) + values([...]) (positional), values({ ... }) (one row object), or values([{...}, {...}]) (multi-row). Optional returning(cols). The value set is fully bound - every value becomes a ? placeholder.

var b = require("@blamejs/core");
b.sql.insert("users")
  .values({ id: 1, email: "a@b.c" })
  .returning(["id"])
  .toSql();
// -> { sql: 'INSERT INTO users ("id", "email") VALUES (?, ?) RETURNING "id"',
//     params: [1, "a@b.c"] }

b.sql.insertSelectWhere(table, opts?) #

{
  dialect:         string,   // postgres | sqlite | mysql (default sqlite)
  schema:          string,   // schema qualifier
  prefix:          string,   // operator app-table namespace prefix
  allowedColumns:  array,    // column-membership gate set
}

Start a conditional INSERT ... SELECT ... WHERE builder - a row written ONLY when a guard derived from the table itself holds. Emits INSERT INTO t (cols) SELECT WHERE : the value-less SELECT is a single computed candidate row the WHERE either admits (one row inserted) or rejects (zero rows). It is the race-free append-only-ledger debit - a store-credit / gift-card / wallet / points / metered-quota / seat-counter balance that lives only on the latest row, with no mutable counter row to increment(). The guard's correlated subquery / EXISTS is evaluated atomically inside the INSERT, so two concurrent debits cannot both pass the same balance check.

Supply the row via columns([...]) + values([...]) (positional), values({ ... }) (one row object, inferring the column list from its keys), then the guard via the full where family (whereExists / whereSub / whereOp / whereGroup / whereRaw all compose - the balance fence is typically an EXISTS against the same table). Each SELECT cell routes through the same choke-point INSERT values() uses, so a cell may be a bound ?, a b.sql.cast(...) (?::type), or a b.sql.fn(...) allowlisted server function (NOW(), no param). Standard SQL across sqlite / Postgres / MySQL; only RETURNING diverges (Postgres / SQLite - refused on MySQL, run an explicit read).

Safety default: an INSERT...SELECT with no WHERE is just an INSERT...VALUES, so the verb THROWS without a where() unless allowNoWhere() opts in - the same discipline update / delete apply.

// Append a -25 debit ONLY if the wallet's balance row still covers it -
// a race-free conditional insert with no read-modify-write. The guard is
// an EXISTS over a same-dialect sub-builder (no raw statement verb).
var covered = b.sql.select("wallet", { dialect: "postgres" })
  .selectRaw("1")
  .whereRaw('"id" = ? AND "balance" >= ?', ["w-1", 25]);
b.sql.insertSelectWhere("wallet_ledger", { dialect: "postgres" })
  .values({ wallet_id: "w-1", amount: -25, at: b.sql.fn("NOW") })
  .whereExists(covered)
  .returning(["id"])
  .toSql();
// -> { sql: 'INSERT INTO wallet_ledger ("wallet_id", "amount", "at") ' +
//          'SELECT ?, ?, NOW() WHERE EXISTS (SELECT 1 FROM wallet ' +
//          'WHERE ("id" = ? AND "balance" >= ?)) RETURNING "id"',
//     params: ["w-1", -25, "w-1", 25] }

b.sql.update(table, opts?) #

{
  dialect:         string,   // postgres | sqlite | mysql (default sqlite)
  schema:          string,   // schema qualifier
  prefix:          string,   // operator app-table namespace prefix
  allowedColumns:  array,    // column-membership gate set
}

Start an UPDATE builder. Set assignments via set({ ... }) / set(col, val) / setRaw(col, expr, params); filter via the where family. An update with no where() THROWS unless allowNoWhere() is called - a deliberate full-table write must opt in. Optional returning(cols).

var b = require("@blamejs/core");
b.sql.update("users")
  .set({ status: "inactive" })
  .where("id", 1)
  .toSql();
// -> { sql: 'UPDATE users SET "status" = ? WHERE "id" = ?', params: ["inactive", 1] }

b.sql.guardedUpdate(table, opts?) #

{
  dialect:         string,   // postgres | sqlite | mysql (default sqlite)
  schema:          string,   // schema qualifier
  prefix:          string,   // operator app-table namespace prefix
  allowedColumns:  array,    // column-membership gate set
}

Start a compare-and-swap UPDATE builder - the cross-instance-safe way to advance a status / version on a single-statement-per-request backend (D1 over an HTTP bridge, or any autocommit-only adapter without interactive transactions). It is b.sql.update plus a required guardWhere(col, expected) fence: the statement lands ONLY when the row is STILL in the expected value, so two racing transitions cannot both win. Refuses to render without at least one guardWhere(...) / guardWhereOp(...) - an unfenced one would just be a plain update.

Read the winner from the result's rowCount with b.sql.casWon(result): exactly one row matched (won: true) means this caller made the transition; zero (won: false) means it lost the race and must no-op / refuse. The sibling of b.sql.insertSelectWhere (the conditional-INSERT debit) for the conditional-UPDATE case, and the b.fsm composition partner (resolve the destination side-effect-free with instance.target(event), then guard on the from-state here).

var b = require("@blamejs/core");
// advance order id=7 from "paid" -> "shipped" iff still "paid"
var q = b.sql.guardedUpdate("orders")
  .set({ status: "shipped" })
  .where("id", 7)
  .guardWhere("status", "paid")
  .toSql();
// -> { sql: 'UPDATE orders SET "status" = ? WHERE "id" = ? AND "status" = ?',
//      params: ["shipped", 7, "paid"] }
// var res = await b.db.raw(q.sql, q.params);
// if (!b.sql.casWon(res).won) { return refuse(); }   // lost the race

b.sql.casWon(result) #

Interpret a compare-and-swap result's affected-row count into a won/lost verdict, owning the Number(rowCount) === 1 check and the cross-adapter field-name divergence (b.db / b.externalDb normalize to rowCount; raw sqlite reports changes, raw mysql affectedRows / rowsAffected). Returns { won, rowCount } where won is true only when exactly one row was affected. Throws when the result carries no recognizable numeric row-count field - an indeterminate result must surface, never be silently read as a win (a phantom win on a CAS is a double-spend).

var v = b.sql.casWon(await b.db.raw(q.sql, q.params));
if (v.won) { applyTransition(); } else { refuseLostRace(v.rowCount); }

b.sql.delete(table, opts?) #

{
  dialect:         string,   // postgres | sqlite | mysql (default sqlite)
  schema:          string,   // schema qualifier
  prefix:          string,   // operator app-table namespace prefix
  allowedColumns:  array,    // column-membership gate set
}

Start a DELETE builder. Filter via the where family. A delete with no where() THROWS unless allowNoWhere() is called. Optional returning(cols).

var b = require("@blamejs/core");
b.sql.delete("sessions")
  .where("expiresAt", "<", 1700000000)
  .toSql();
// -> { sql: 'DELETE FROM sessions WHERE "expiresAt" < ?', params: [1700000000] }

b.sql.upsert(table, opts?) #

{
  dialect:         string,   // postgres | sqlite | mysql (default sqlite)
  schema:          string,   // schema qualifier
  prefix:          string,   // operator app-table namespace prefix
  allowedColumns:  array,    // column-membership gate set
}

Start an UPSERT builder - the one verb that emits dialect-final conflict syntax. Supply the row via columns + values({...}), the conflict key via onConflict(keys), and one conflict action: doUpdate(cols | { col: expr }), doUpdateFromExcluded(cols), or doNothing(). Optional conflictWhere(rawGuard, params, opts?) fences the update - pass { guardColumn: "

On Postgres / SQLite toSql() returns { sql, params } emitting ON CONFLICT (keys) DO UPDATE SET col = EXCLUDED.col [WHERE ...] [RETURNING ...]. On MySQL it returns { sql, params, readbackSql } emitting ON DUPLICATE KEY UPDATE col = VALUES(col) (or IF(guard, VALUES(col), col) when conflictWhere is set); MySQL evaluates the SET list left to right, so when the fenced guard column is itself a SET target it must be assigned last (each IF must see the guard column's pre-update value) - name it via conflictWhere(..., { guardColumn }) and the fold reorders it to the end. MySQL has no per-statement WHERE / RETURNING on the conflict action, so a readback SELECT keyed on the conflict columns is returned for the caller to fetch the upserted row.

var b = require("@blamejs/core");
b.sql.upsert("audit_tip", { dialect: "postgres" })
  .values({ id: 1, counter: 42 })
  .onConflict(["id"])
  .doUpdateFromExcluded(["counter"])
  .toSql();
// -> { sql: 'INSERT INTO audit_tip ("id", "counter") VALUES (?, ?) ' +
//          'ON CONFLICT ("id") DO UPDATE SET "counter" = EXCLUDED."counter"',
//     params: [1, 42] }

Last updated 2026-08-08T16:39:49.652Z by seeder.