SQL

​

The Surface

interface SqlStorage {
  exec<T = Record<string, SqlValue>>(query: string, ...bindings: SqlValue[]): SqlCursor<T>;
}

interface SqlCursor<T> extends Iterable<T> {
  toArray(): T[];
}

type SqlValue = null | number | string | ArrayBuffer;

import { StatefulActor } from "@telnyx/edge-runtime";

export class Inventory extends StatefulActor {
  async record(sku: string, cents: number) {
    this.ctx.storage.sql.exec(
      `CREATE TABLE IF NOT EXISTS orders(sku TEXT, cents INTEGER, ts INTEGER)`,
    );
    this.ctx.storage.sql.exec(
      `INSERT INTO orders(sku, cents, ts) VALUES (?, ?, ?)`,
      sku,
      cents,
      Date.now(),
    );
    // An aggregate the key/value store can't answer without scanning every key:
    return this.ctx.storage.sql
      .exec(
        `SELECT sku, SUM(cents) AS total FROM orders
         GROUP BY sku ORDER BY total DESC LIMIT 5`,
      )
      .toArray();
  }
}

​

Binding Parameters

const rows = this.ctx.storage.sql
  .exec(`SELECT * FROM orders WHERE sku = ? AND cents > ?`, sku, 1000)
  .toArray();

​

Reading Results: the Cursor

// Drain to an array (the common case):
const all = this.ctx.storage.sql.exec(`SELECT n FROM nums ORDER BY n`).toArray();

// Or stream row by row, without materializing the whole set:
for (const row of this.ctx.storage.sql.exec(`SELECT n FROM nums ORDER BY n`)) {
  handle(row.n);
}

​

Multiple Statements in One exec

const rows = this.ctx.storage.sql
  .exec(
    `CREATE TABLE IF NOT EXISTS t(x INTEGER);
     INSERT INTO t VALUES (1);
     SELECT x FROM t WHERE x >= ?;`,
    1,
  )
  .toArray(); // the final SELECT's rows; the CREATE and INSERT ran too

​

Transactions

async transfer(from: string, to: string, cents: number) {
  this.ctx.storage.transactionSync(() => {
    this.ctx.storage.sql.exec(`UPDATE accounts SET cents = cents - ? WHERE id = ?`, cents, from);
    this.ctx.storage.sql.exec(`UPDATE accounts SET cents = cents + ? WHERE id = ?`, cents, to);
    // throw here — e.g. on an overdraft check — and neither UPDATE is applied
  });
}

• transactionSync is the only transaction API. Raw transaction-control statements — BEGIN, COMMIT, ROLLBACK, SAVEPOINT — are rejected by exec() with SqlTransactionControlError, anywhere in a statement batch. (An open transaction left dangling across method calls would have no owner; the callback shape makes that impossible.)
• The callback must be synchronous. Passing an async function throws SqlAsyncTransactionError and commits nothing — the transaction commits when the callback returns, so statements after an await would run outside it. Do async work before or after, not inside.

​

Bulk Writes

this.ctx.storage.transactionSync(() => {
  for (const row of rows) this.ctx.storage.sql.exec(`INSERT INTO t(v) VALUES (?)`, row);
});

​

Indexes

this.ctx.storage.sql.exec(`CREATE INDEX IF NOT EXISTS orders_by_sku ON orders(sku)`);

​

Limits

• 1 GB per actor database. Enforced by the engine — writes past it fail.
• 2 MiB per bound value. A bind larger than 2 MiB (2,097,152 bytes) is rejected with SqlParameterTooLargeError before the statement runs. There is no cap on output row size.
• Up to 32,766 bound parameters in a single statement (SQLite’s variable limit).
• Integers come back as JavaScript number. SQLite stores 64-bit integers, but exec returns integer columns as JS numbers — the same design as Cloudflare’s SQLite storage and D1, neither of which returns BigInt from the query API. A value beyond Number.MAX_SAFE_INTEGER (2^53 − 1, i.e. 9007199254740991) can be written and compared in SQL, but reading that column throws RangeError: Value is too large to be represented as a JavaScript number — a loud failure rather than a silently less-precise number. Store large ids, bitmasks, or nanosecond timestamps as TEXT (or BLOB), or keep integers within the safe range.
• Foreign keys are ON — a REFERENCES violation raises FOREIGN KEY constraint failed.
• Failed statements (syntax errors, constraint violations) throw from exec; the actor keeps running, so catch and handle them like any other error.

​

SQL or Key/Value?

• Key/value (get/put/list): simple keyed state, prefix listing, atomic multi-key updates — reach for it by default when a lookup by key is all you need.
• SQL (ctx.storage.sql): relational shape, aggregates, GROUP BY, secondary indexes, and joins within one actor. Reach for it when a keyed lookup isn’t enough and you’d otherwise scan.

​

Related

• Key-Value storage — the get/put/list surface
• Actor Storage reference — the full ctx.storage contract
• Execution model — why exec needs no lock