Skip to main content

Overview

The DatasetsClient is the Room API for room-scoped structured data. Use it to create tables, insert and update rows, build indexes, and run text or vector search without provisioning a separate dataset. Datasets are designed and optimized for batch writes, indexing, scans, search, and retrieval. They are not a transactional database replacement: avoid using them for high-frequency row-by-row mutations, cross-table transactions, locks, or workloads that require immediate transactional consistency.

CLI commands

Start with the CLI help, then use a few common commands:
bash

Why use the Datasets API?

  • Keep structured room data close to the agents and services that use it.
  • Support filtering, analytics, semantic search, and retrieval workflows in one place.
  • Manage schemas and indexes through the same Room API surface you already use for messaging, storage, and sync.

How it works

Each room dataset contains named tables. You can create tables from a schema or from raw data, update rows, create scalar/full-text/vector indexes, and run searches that combine filters with text or vector similarity. Use it when your room needs structured records instead of only files or chat history.
Current implementation: MeshAgent room dataset is currently backed directly by Lance datasets, which provide the table, vector, and full-text primitives used by the room dataset toolkit.
Typed values: MeshAgent also supports json, uuid, list, and struct dataset types. In the typed SDKs, use the wrapper classes for those values, such as DatasetJson, DatasetStruct, DatasetUuid / UuidValue, and DatasetExpression where the client requires them.

External Lance datasets

A room can register an existing Lance dataset stored in GCS, Amazon S3, or Azure Blob Storage. Registration adds a table name to the room catalog; the data stays in the external storage account. In Studio, open the room’s Storage connections… menu next to Volumes…. Room admins can create a connection by choosing a provider, entering the bucket or container, an optional prefix, and the provider’s credentials. For public datasets, select Public access (anonymous) instead; no credentials are stored. When editing, credentials are kept unless Replace credentials is checked; saved values are never displayed. In a connected room, choose Register dataset on a connection. Enter a catalog name and a dataset path relative to the connection prefix, with an optional namespace and catalog branch. Registration checks access to the Lance dataset. Open Datasets… and refresh to browse the registered table. For public storage, set "anonymous": true in the connection’s config and omit credentials. This explicitly disables request signing instead of using ambient cloud credentials. The bucket/container must permit anonymous reads and any listing operations required by Lance. For example:
Changing an authenticated connection to anonymous deletes its saved secret. Changing it back requires new credentials. Connections can also be created using the management API. For example, with an authenticated Python API client named api:
Python
Here service_account_json is the credential document supplied by the room administrator. The backend stores credentials in Vault; public connection responses and catalog entries do not contain them. The connection belongs to the room and does not depend on the creating user’s identity at query time. Connection management uses /accounts/projects/{project_id}/rooms/{room_name}/storage-connections: GET lists connections and POST creates one. PUT and DELETE on /{connection_id} update or remove a connection. Create/update bodies contain name, config, and credentials; update can omit credentials to preserve the existing value. A connection’s provider cannot be changed. Managing connections requires room administrator access; querying registered tables uses the normal dataset grants. Registering a table requires its alter permission. TypeScript registration uses the same connection ID:
TypeScript
The dataset path is relative to the connection’s bucket/container and prefix. Registration validates that the room can open the dataset. The room server resolves credentials again for subsequent external dataset opens; existing queries retain their already-open handles. Rotate credentials by updating the connection. Supply replacements before temporary S3 credentials or Azure SAS tokens expire. External datasets are read-only: inserts, updates, deletes, schema changes, index creation, optimization, and restore are rejected. Search, inspection, and SQL reads use the existing dataset APIs. Renaming changes the catalog alias. Dropping a table only unregisters it. Creating a room catalog branch copies the external reference and does not create a branch in the source dataset; reads continue to use the source’s main branch, with an optional source version. Deleting a connection makes remaining registrations unavailable on subsequent opens. None of these operations deletes external dataset files. The CLI exposes the same workflow. connection.json contains the name, config, and credentials object described above:
bash
Use connection update --connection-id CONNECTION_ID --file connection.json to rotate credentials, and connection delete --connection-id CONNECTION_ID to remove access. Both commands also take --room.

Permissions and grants

The Datasets API is controlled by the dataset grant on the participant token. In practice:
  • list_tables controls whether the participant can list tables
  • table grants control read, write, and alter access per table
  • if no table list is supplied, access is broad across the room dataset
Room storage transfers also require a storage grant: import_storage requires read access to the source path, and export_storage requires write access to the destination path. These checks apply in addition to the table permissions and use the same path rules and exclusions as the Storage API. Local storage paths must use the exact stored spelling; descendant symbolic links cannot be used as file aliases. See API Scopes and Service YAML.

API reference

Use the methods below to manage room tables, rows, indexes, and search workflows. Most methods are asynchronous calls that you await; streaming helpers such as sql_stream(), search_stream(), and watch_table() return async iterators that you consume with async for.

Python SDK signatures

The examples below focus on common cross-SDK workflows. The Python SDK also exposes namespace, branch, version, streaming, SQL, and maintenance helpers directly on room.datasets:
Python

list_tables()

Description Retrieves a list of all table names currently present in the dataset. Returns:
  • A promise that resolves to an array of table name strings.

create_table_with_schema(...)

  • Description: Creates a new table with an optional schema and initial data. You can specify how the table should be created through the mode parameter.
    • modes:
      • "create": Creates the table; fails if it already exists.
      • "overwrite": Drops the existing table (if any) and creates a new one.
      • "create_if_not_exists": Creates the table only if it does not already exist.
  • Parameters:
    • name: The name of the new table.
    • schema: An optional record defining column names and their data types.
    • data: An optional array of initial records to populate the table. Prefer batching records together rather than issuing many single-row writes.
    • mode: The creation mode (default is "create").
  • Returns: A promise that resolves once the table is created.
Example:

create_table_from_data(...)

  • Description: Creates a table using only data and an optional mode.
  • Parameters:
    • name: The table name to create.
    • data: An array of records to initialize the table with.
    • mode: Table creation mode (default "create").
  • Returns: A promise that resolves once the table is created.
Example:

drop_table(name, ...)

  • Description: Drops (deletes) a table by name, optionally ignoring if it does not exist.
  • Parameters:
    • name: The name of the table to drop.
    • ignoreMissing: If true, no error is thrown if the table does not exist.
  • Returns: A promise that resolves once the table is dropped.
Example:

add_columns(...)

  • Description: Adds one or more columns to an existing table, specifying default value expressions.
  • Parameters:
    • table: Name of the target table.
    • newColumns: A record mapping column names to default value expressions (SQL or literal).
  • Returns: A promise that resolves once the columns are added.
For Dart, the expression-based helper is addColumnWithExpression(...). If you want to add columns by explicit Arrow schema instead, use addColumnsWithSchema(...). Example:

drop_columns(...)

  • Description: Drops (removes) one or more columns from an existing table.
  • Parameters:
    • table: Name of the target table.
    • columns: An array of column names to remove.
  • Returns: A promise that resolves once the columns are dropped.
Example:

insert(table, records)

  • Description: Inserts one or more new records into a table.
  • Parameters:
    • table: The name of the table to insert into.
    • records: An array of objects, each containing column-value pairs.
  • Returns: A promise that resolves once the records are inserted.
Example:

update(table, where, ...)

  • Description: Updates existing records in a table.
  • Parameters:
    • table: Name of the table to update.
    • where: A SQL WHERE clause specifying which records to update (e.g. "id = 123").
    • values: A record of key-value pairs for direct assignment or expressions (e.g. { age: 30 } or { age: new DatasetExpression("age + 1") } in typed clients).
  • Returns: A promise that resolves once the update is complete.
Example:

delete(table, where)

  • Description: Deletes records from a table that match a specified condition.
  • Parameters:
    • table: The target table.
    • where: A SQL WHERE clause for filtering which records to delete.
  • Returns: A promise that resolves once the records are deleted.
Example:

merge(table, records, ...)

  • Description: Performs an upsert (update/insert) by merging incoming records into an existing table. Records matching the on column are updated; otherwise, new rows are inserted.
  • Parameters:
    • table: The target table.
    • on: The column name used to match existing records.
    • records: The record(s) to merge/upsert.
  • Returns: A promise that resolves once the operation is complete.
Example:

search(table, ...)

  • Description: Searches for records in a table. This can be used for plain text search, vector similarity search, or simple SQL filtering.
  • Parameters:
    • table: The target table name.
    • text: An optional search string (if using full-text indexes).
    • vector: An optional numeric array for vector-based similarity queries.
    • where: SQL WHERE clause string or an object representing key-value equals conditions. Object values are encoded as SQL literals. Object keys (including dotted field paths) and raw SQL strings must come from trusted application code. TypeScript and Dart reject unsupported filter value types, including lists, binary values and DatasetExpression; use a trusted SQL string for expressions.
    • offset: Optional offset for pagination in Python and Dart.
    • limit: Maximum number of matching records to return.
    • select: An array of column names to be returned.
  • Returns: Python returns a pyarrow.Table. JavaScript, TypeScript, Dart, and .NET return arrays/lists of matching records.
Example:

optimize(table, ...)

  • Description: Optimizes a table (e.g., compacts its storage or rebuilds indexes if required).
  • Parameters:
    • table: Name of the table to optimize.
    • config: Optional optimization configuration using Lance option names, including compact_files, optimize_indices, cleanup_old_versions, target_rows_per_fragment, max_rows_per_group, max_bytes_per_file, materialize_deletions, materialize_deletions_threshold, defer_index_remap, num_threads, batch_size, compaction_mode, binary_copy_read_batch_bytes, num_indices_to_merge, index_names, retrain, older_than_seconds, retain_versions, delete_unverified, error_if_tagged_old_versions, and delete_rate_limit.
  • Returns: Optimization result details for compaction, index optimization, and cleanup.
Example:

stats(table, ...)

  • Description: Returns Lance dataset and data statistics for a table.
  • Parameters:
    • table: Name of the table.
    • max_rows_per_group: Optional row-group threshold used by Lance dataset stats.
  • Returns: Parsed dataset and data statistics.
Example:

create_index(...)

  • Description: Creates a Lance index on a dataset table.
  • Parameters:
    • table: The target table name.
    • config: Index configuration. Uses Lance option names such as column, index_type, name, replace, metric, num_partitions, num_sub_vectors, target_partition_size, filter_nan, train, fragment_ids, index_uuid, skip_transpose, num_bits, index_file_version, max_level, m, ef_construction, with_position, memory_limit, num_workers, skip_merge, base_tokenizer, language, max_token_length, lower_case, stem, remove_stop_words, custom_stop_words, and ascii_folding.
  • Returns: A promise that resolves once the index is created.
Supported index_type values include vector indexes (IVF_PQ, IVF_HNSW_PQ, IVF_HNSW_SQ, IVF_RQ) and scalar/text indexes (BTREE, BITMAP, LABEL_LIST, NGRAM, ZONEMAP, INVERTED, FTS, BLOOMFILTER, RTREE). Example:

drop_index(...)

  • Description: Drop an index by name.
  • Parameters:
    • table: Table name.
    • name: Index name to drop.
  • Returns: None.
  • Availability: Python, JavaScript/TypeScript, Dart, and .NET expose helpers today.

list_indexes(table)

  • Description: Lists the indexes currently defined on a table.
  • Parameters:
    • table: The name of the table for which to list indexes.
  • Returns: A list of index entries. Entries include name, columns, type, fields, type_url, num_rows_indexed, num_segments, total_size_bytes, details, and statistics.
Example:

list_versions(table)

  • Description: List historical versions of a table. Reads can target a specific version directly, and restore creates a new head version from an older snapshot.
  • Methods:
    • list_versions(table, branch=None) → list of versions (version, timestamp, metadata) for the selected branch.
    • search(table, branch=None, version=None) → read a branch head or a specific historical version without any checkout step.
    • restore(table, version, branch=None) → restore a table branch to a prior version by creating a new head commit.
  • Availability: Python exposes list_versions, search(..., version=...), restore, and dataset branch operations. TypeScript exposes listVersions, restore, and branch helpers. .NET exposes ListVersions and Restore. Dart currently exposes listVersions.

list_branches()

  • Description: List, create, and delete dataset branches for a namespace. Table reads and writes can then target a branch directly with branch=....
  • Methods:
    • list_branches() → list available branches.
    • create_branch(branch, from_branch=None) → create a branch from the head of main or another branch.
    • delete_branch(branch) → delete a non-main branch.

Additional dataset operations

The current SDKs and CLI include a few operations that are useful once tables move beyond basic CRUD:
  • rename_table(...) renames a table without copying its data.
  • sql(...), open_sql_query(...), execute_sql(...), sql_stream(...), and execute_sql_statement(...) run SQL against room datasets.
  • watch_table(...) streams table changes.
  • count(...) returns the matching row count for a table query.
  • restore(...) restores a table to a previous version.
  • meshagent room dataset import imports local data into a room table.
  • meshagent room dataset sql runs SQL from the CLI.