Queries and transactions

Execute trusted application queries

Queries and transactions show how to send bounded, parameterized SQL and decode ordered tagged values.

Parameters preserve types and prevent SQL text from becoming an authorization or injection boundary.

Use it for application reads/writes after a project schema and identity policy exist.

Requirements for Queries and transactions

• Prerequisite — A ready project, an authenticated end-user session or trusted developer credential, and existing tables.
• Prerequisite — Knowledge of the documented SQLite SQL subset.
• Required value — SQL text, tagged parameters, optional max_rows, project ID, and AbortSignal when cancellation matters.
• Required value — For transactions, an ordered statement list and an idempotency strategy.

Parameterized queries

Parameters use the tagged null, integer, real, text, or base64 blob representation. Ordered rows are arrays so duplicate column names and column order remain intact.

const result = await ffdb.query({
  sql: "select id, title from documents where title like ?1 order by title",
  parameters: [{ type: "text", value: search + "%" }],
  options: { max_rows: 100 },
});

for (const row of result.rows) {
  console.log(row[0], row[1]);
}

Result values

• Safe integers are JavaScript numbers.
• Integers outside JavaScript's safe range remain decimal strings.
• BLOB values use the object form { $blob: "base64..." }.
• Use options.max_rows and cancellation to bound application work.

Queries and transactions workflow

• 1. Write SQL with numbered placeholders instead of interpolated values.
• 2. Encode each parameter with the matching tagged type.
• 3. Set an explicit row bound for reads.
• 4. Issue the query or transaction and decode rows by the returned column order.
• 5. Handle FFDBError by stable code and request ID.

Verify queries and transactions

The server returns ordered columns and rows, and RLS constrains the result without client-authored owner predicates.

Troubleshoot queries and transactions

• A statement includes interpolated user text — replace it with a tagged parameter.
• The server rejects unsupported SQL — use SQL support to redesign rather than bypassing the parser.

Continue from Queries and transactions

• Generate schema types with the CLI.
• Add RLS tests for two users.