> ## Documentation Index
> Fetch the complete documentation index at: https://docs.meshagent.com/llms.txt
> Use this file to discover all available pages before exploring further.

# SQLite

> Room SQLite databases, atomic SQL batches, and configurable replication.

Use `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, and `INSERT ... 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](https://docs.meshagent.com/room_api/sqlite-recovery.md) 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.

```python theme={null}
from meshagent.api import SqliteSqlCommand, SqliteSqlQuery

results = await db.execute_sql_batch(commands=[
    SqliteSqlCommand("CREATE TABLE IF NOT EXISTS users(id INTEGER PRIMARY KEY, name TEXT)"),
    SqliteSqlCommand("INSERT INTO users VALUES (?, ?)", [1, "Ada"]),
    SqliteSqlCommand("SELECT * FROM users WHERE id=:id", {"id": 1}),
])

query = results[2]
assert isinstance(query, SqliteSqlQuery)
try:
    async for table in room.sqlite.read_sql_query(query_id=query.query_id):
        print(table.to_pylist())
finally:
    await room.sqlite.close_sql_query(query_id=query.query_id)
```

Commands run in order; later commands see earlier writes. The result list preserves command order. Non-row commands return `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:

| SDK | Method | Command model |
| - | - | - |
| Python | `execute_sql_batch` | `SqliteSqlCommand(query, params=None)` |
| Rust | `execute_sql_batch` | `SqliteSqlCommand { query, params }` |
| TypeScript | `executeSqlBatch` | `{ query, params? }` (`SqliteSqlCommand`) |
| Dart | `executeSqlBatch` | `SqliteSqlCommand(query: ..., params: ...)` |
| .NET | `ExecuteSqlBatch` | `new SqliteSqlCommand(query, @params)` |

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](https://docs.meshagent.com/room_api/sqlite-recovery.md) 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.

| Setting | Default | Accepted values | Behavior |
| - | - | - | - |
| `replication_mode` | `"synchronous"` | `"synchronous"`, `"asynchronous"` | Synchronous commits wait for remote publication. Asynchronous commits return before remote publication and publish committed changes in the background. |
| `flush_interval_ms` | `1000` | Integer from `1` through `3600000` | Background replication interval in milliseconds for asynchronous mode. |

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.

```python theme={null}
await room.sqlite.create_database(
    name="app",
    replication_mode="asynchronous",
    flush_interval_ms=1000,
)
```

An existing database opened with `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

```python theme={null}
settings = await room.sqlite.get_database_settings(name="app")
print(settings.replication_mode, settings.flush_interval_ms)

# Keep the current replication mode and change only the interval.
settings = await room.sqlite.update_database_settings(
    name="app", flush_interval_ms=250
)

# Switch back to waiting for remote publication on every commit.
await room.sqlite.update_database_settings(
    name="app", replication_mode="synchronous"
)
```

Pass the same `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

```python theme={null}
await room.sqlite.flush_database(name="app")
```

Flush waits for pending committed changes to reach remote storage without changing the replication mode or interval. Use it when an asynchronous workload reaches a point that must be replicated before continuing. A storage failure is returned to the caller, and flushing during an active transaction is rejected. Subsequent asynchronous writes still need their own publication.

### SDK and wire APIs

The TypeScript and Dart clients expose `replicationMode` 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:

```json theme={null}
{
  "replication_mode": "asynchronous",
  "flush_interval_ms": 1000
}
```

### CLI

Use `settings` 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.

```bash theme={null}
meshagent room sqlite database create --room my-room --database app \
  --replication-mode asynchronous --flush-interval-ms 1000
meshagent room sqlite database settings --room my-room --database app
meshagent room sqlite database settings --room my-room --database app \
  --replication-mode synchronous
meshagent room sqlite database settings --room my-room --database app \
  --flush-interval-ms 250
meshagent room sqlite database flush --room my-room --database app
```

### 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. Use `execute_sql_batch` to group related commands into one request and transaction.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.