Raw SQL safely

The typed helpers (readList, aggregate, updateWhere, …) cover most of what a handler needs, and they do a lot for you: org scoping, column redaction, table locks, uids at the edge. When you need SQL they cannot express (a join, a window function, a report), use raw SQL. It is powerful because it skips all of that, so it comes with two rules: it must be parameterized, and you must scope it yourself.

Both sdk.sql and sdk.query need the sdk:query capability (deny by default, high risk). See Capabilities & governance.

Use sdk.sql

sdk.sql is a tagged template. Every ${…} becomes a bound parameter, so a value can never become part of the statement, whatever it contains:

const rows = await sdk.sql`
  SELECT uid, invoice_number, created_at
    FROM invoices
   WHERE status = ${data.status}
     AND created_at >= ${data.since}
   ORDER BY created_at DESC
   LIMIT ${50}
`;

Awaiting the template runs it and resolves to the rows. Without await, it is a fragment you can compose:

// A dynamic sort column, an IN list, and an optional condition.
const sortColumn = sdk.sql.id(data.sortBy === "total" ? "total_cents" : "created_at");
const openOnly = data.openOnly ? sdk.sql`AND status = ${"open"}` : sdk.sql``;

const rows = await sdk.sql`
  SELECT uid, status, total_cents
    FROM orders
   WHERE uid IN (${data.orderUids})
     ${openOnly}
   ORDER BY ${sortColumn} DESC
`;

sdk.sql.id validates the name; it does not decide whether the caller should be able to sort by that column. Map user input onto names you chose, as above, rather than passing it straight in.

sdk.query takes only a static string

sdk.query(sql, params) runs a fixed statement with ? placeholders. Its first argument must be a string literal, and the call must be written as sdk.query(…) itself. A handler that builds the statement any other way, or reaches query indirectly, does not save:

// Fine: a literal, values bound.
await sdk.query("SELECT uid FROM invoices WHERE status = ? AND spend_category = ?", [status, category]);

// Refused at save: HANDLER_UNSAFE_RAW_SQL.
await sdk.query(`SELECT uid FROM invoices WHERE status = '${status}'`);
await sdk.query("SELECT uid FROM invoices WHERE status = '" + status + "'");
await sdk.query(data.sql);
const sql = "SELECT uid FROM invoices WHERE status = '" + status + "'";
await sdk.query(sql);

// Also refused: each reaches the same method without a literal the check can read.
const { query } = sdk;
await sdk["query"](sql);
await sdk.query.call(sdk, sql);

The check runs on every save, dry run, approval and publish, for code written by a person and by AI alike, so no stored version contains an injectable query. Anything dynamic belongs in sdk.sql.

Each call runs one statement: stacked statements separated by ; are refused by the database connection.

Raw SQL is inside the database

The typed helpers present rows as they look from outside: uid, <name>_uid references, no ids, no org column. Raw SQL sees rows as stored: id, raw foreign keys, org_id. That is what makes it fast (joins on id use the primary key), and it means three things are now yours:

Scope it. The caller's organization arrives as a uid (ctx.user.orgUid); join through the app's orgs table to use it:

const rows = await sdk.sql`
  SELECT i.uid, i.invoice_number, i.status
    FROM invoices i
    JOIN orgs o ON o.id = i.org_id
   WHERE o.uid = ${ctx.user.orgUid}
     AND i.status = ${"received"}
`;

Return uids, never ids. Select uid, and a referenced row's uid through its join, for anything that leaves the handler:

const rows = await sdk.sql`
  SELECT i.uid, i.status, v.uid AS vendor_uid
    FROM invoices i
    JOIN vendors v ON v.id = i.vendor_id
   WHERE i.status = ${"received"}
`;
return rows; // uid, status, vendor_uid: no id anywhere

Mind what you select. A column you would have seen as redacted through readList comes back in clear through raw SQL. Select only the columns you mean to use.

Schema changes

A statement that changes schema (CREATE, ALTER, DROP, RENAME, TRUNCATE) needs sdk:query_ddl, the critical-risk capability, which implies sdk:query. DDL commits on its own and cannot be rolled back, so it never runs in a dry run (DDL_NOT_DRY_RUNNABLE). Changing schema from a handler is rarely right; the console's table editor applies schema changes, foreign keys included, as one atomic step.

Errors

Next