SQL support

Know the supported SQLite surface

SQL support states the exact application and protected-table SQL boundary.

FFDB intentionally supports a constrained subset so parsing, authorization, limits, and RLS rewriting remain enforceable.

Check it before adopting a SQLite feature, query builder output, trigger, or migration pattern.

Requirements for SQL support

• Prerequisite — The final SQL emitted by the application or migration tool.
• Prerequisite — Knowledge of whether the target table is RLS protected.
• Required value — Statement class, target objects, parameter count, and expected result bound.
• Required value — Any required SQLite feature such as STRICT or RETURNING.

Application statements

• SELECT and constrained WITH queries
• INSERT, UPDATE, and DELETE through generated policy triggers
• Explicit transactions through the SDK transaction method
• Schema and policy DDL only through trusted migration mode

Current protected-table restrictions

• UPSERT against an RLS view is rejected; use a bounded UPDATE then conditional INSERT transaction.
• Callers must provide values for columns whose backing defaults they intend to use.
• Generated columns are readable but excluded from generated INSERT and UPDATE assignments.
• Unsupported syntax returns an error and never falls back to unprotected execution.

Use a transaction for dependent writes

Statements execute in order and commit atomically. Every value remains a tagged parameter; do not interpolate identifiers or user input into SQL. If a statement uses unsupported syntax or violates RLS, the transaction rolls back.

const [updated, audited] = await ffdb.transaction({
  statements: [
    {
      sql: "update documents set title = ?1 where id = ?2",
      parameters: [
        { type: "text", value: nextTitle },
        { type: "text", value: documentId },
      ],
    },
    {
      sql: "insert into document_events (id, document_id, kind) values (?1, ?2, ?3)",
      parameters: [
        { type: "text", value: eventId },
        { type: "text", value: documentId },
        { type: "text", value: "renamed" },
      ],
    },
  ],
});

SQL support workflow

• 1. Classify the statement as application SQL, migration DDL, or operator work.
• 2. Compare its constructs with the supported matrix.
• 3. Test parsing and authorization in a non-production project.
• 4. Measure row and resource bounds.
• 5. Fail the release if unsupported syntax is security-significant.

Verify sql support

The chosen SQL either executes through the documented boundary or is rejected before reaching production.

Troubleshoot sql support

• A library emits hidden unsupported syntax — configure or replace its dialect output.
• A proposal needs raw PRAGMA, ATTACH, or extension loading — redesign; those are not application escape hatches.

Continue from SQL support

• Implement the query with tagged parameters.
• Add a migration fixture for accepted and rejected forms.