room.sqlite.database("app", namespace=["team"]) to bind a database and namespace. Create the database with await db.create_database() before using it.
Table permissions
SQL execution checks every table SQLite resolves, including sources in subqueries, views, triggers, andINSERT ... SELECT. The same checks apply to raw SQL, atomic batches, Arrow insertion, and structured search, count, update, and delete filters. A database execute grant does not override table read, write, or alter grants. A failed batch authorization rolls back earlier commands and publishes no query handles.
Write-only grants permit ordinary updates and deletes with predicates on the table being changed. Reading other tables, SELECT subqueries, and RETURNING require read permission. Creating a table may populate its initial Arrow data; create_if_not_exists requires write permission to add data to an existing table.
Raw SQL that mixes DDL with SELECT, including CREATE TABLE ... AS SELECT and some complex ALTER operations, also requires explicit reads of the SQLite schema tables used by that statement. The structured schema methods handle SQLite’s internal schema maintenance separately. Temporary tables, virtual-table creation, extension loading, and unrecognized authorization actions are rejected. Raw PRAGMA access is restricted to supported read-only operations; table metadata requires read permission on its target, and table-valued PRAGMAs also require permission on their virtual table. Use the settings API for supported configuration changes.
Native builds must compile bundled SQLite with SQLITE_OMIT_XFER_OPT. Cargo configuration supplies this flag when building from the repository root or the rust workspace. Custom packaging must preserve it: SQL operations fail closed when the linked SQLite library lacks this protection. This disables a bulk-copy optimization that otherwise skips source-table authorization; ordinary INSERT ... SELECT remains supported.
SQL runs within the selected database. Connections reject ATTACH, including in-memory databases and filesystem URIs. VACUUM and VACUUM INTO are also unavailable because SQLite implements them using an attachment. Use the SQLite recovery APIs to download snapshots and restore databases. Normal transactions and PRAGMA optimize remain available.
Atomic SQL batches
execute_sql_batch sends multiple parameterized commands in one request and executes them in one transaction. Each command contains exactly one SQL statement and its own positional or named parameters.
SqliteSqlStatement with rows_affected. Row-returning commands, including RETURNING, produce SqliteSqlQuery with an Arrow schema and a query handle. Query rows are materialized when their command executes; handles become available only after the transaction commits. Close every returned query handle when finished.
If a command fails, the entire batch rolls back, later commands do not run, and no query handles are published. The error retains its error code and identifies the failing command by its zero-based index. Commit failures are reported separately and roll back the transaction. A lost response after commit does not establish that the batch failed; automatically retrying mutations can apply them twice.
A batch targets one database and namespace. Each command undergoes the same permission checks as individual SQL execution. Empty batches return an empty result list without opening a database. Transaction-control statements, PRAGMA, ATTACH, DETACH, and VACUUM are rejected. A nonempty batch is rejected if the shared connection already has an active transaction; that transaction is left intact. Parameters belong to individual commands; results cannot be referenced as parameters by later commands.
SDK methods
All SDKs expose the operation on both the SQLite client and the bound database wrapper:
The wire tool is
sqlite.execute_sql_batch. Its JSON request contains database, optional namespace, and commands. Its JSON response contains ordered results: statement entries carry kind: "statement" and rows_affected; query entries carry kind: "query", query_id, and schema_base64 containing an Arrow IPC schema. SDKs decode schemas and reuse read_sql_query and close_sql_query for query results.
See SQLite recovery for persistent storage recovery.
Replication settings
Replication settings belong to each database, including its namespace. They control when a write is acknowledged relative to publication to remote storage.
Use synchronous mode when acknowledged writes must already be in remote storage. Asynchronous mode reduces commit latency by allowing multiple commits to accumulate before publication. Unpublished writes can be lost if the process or its local state is lost. The flush interval is a scheduling interval, not a guaranteed maximum loss window: active transactions or storage failures can delay publication.
Local databases without a remote VFS retain normal local SQLite commit behavior.
Create a database
Set either option when creating a database. Omitted options use the defaults above.mode="create_if_not_exists" retains its settings, even if creation options are supplied. Use update_database_settings to change an existing database.
Read and update settings
namespace to creation, settings, and flush calls when the database is in a namespace, for example namespace=["team"].
Updates preserve omitted fields. Save waits for pending writes to replicate before switching policy. When it succeeds, the settings are persisted and active on the open connection; no reconnect or room restart is required. A failed flush fails the save and retains the old policy. A policy change is rejected while the connection has an active transaction.
Settings are stored in replication.json alongside the database in room storage and loaded again when the database is reopened. Databases without a saved settings file use the defaults. Use the settings API or CLI to update the policy so the running connection is updated as well.
Flush pending writes
SDK and wire APIs
The TypeScript and Dart clients exposereplicationMode and flushIntervalMs creation options and getDatabaseSettings, updateDatabaseSettings, and flushDatabase. Rust exposes create_database_with_settings, get_database_settings, update_database_settings, and flush_database, using SqliteReplicationSettings and SqliteReplicationMode. Reading settings requires database inspect permission; changing settings or explicitly flushing requires database management permission (the existing drop grant). Use these APIs rather than connection-level replication pragmas.
The wire tools are sqlite.get_database_settings, sqlite.update_database_settings, and sqlite.flush_database. Each accepts name and optional namespace. Update also accepts either or both settings fields. All three return the current settings as JSON:
CLI
Usesettings without setting flags to read the policy, or supply flags to save changes immediately. Add --project-id if you do not have an activated project, and use repeatable -n options for a namespace.
Studio
The New SQLite Database dialog includes Replication Mode and Flush Interval (ms). For an existing database, open it and select Settings, edit either value, then select Save. Save waits for pending replication and applies the settings immediately. The interval controls background publication when asynchronous mode is selected.Batching and latency
Batching remains useful in either mode. Synchronous batches amortize remote publication across multiple commands without weakening acknowledgement durability. Asynchronous replication does not enable concurrent SQLite writers; writes still serialize for a database. A client request to a remote room also includes network and request-processing latency. Asynchronous replication removes the wait for remote storage publication from each commit, but a separate request per statement still pays the client-to-room round trip. Useexecute_sql_batch to group related commands into one request and transaction.