Skip to main content

​
Overview

The DatabaseClient serves as a high-level interface for interacting with a relational-like database. It lets you manage room-scoped tables and perform structured queries. Each method in the DatabaseClient corresponds to a particular request sent to the server, handling tasks like table creation, updates, deletions, and searches. The client leverages JsonResponse objects and typed parameters to streamline the process. Use it to:
  • List tables and create/drop them (with schema or from data, with create/overwrite/create_if_not_exists modes).
  • Modify schemas (add/drop columns) and data (insert, update, delete, merge/upsert).
  • Search with text filters, vector similarity, and indexed lookups; create indexes (scalar, vector, full-text) and drop them.
  • Optimize storage and (Python) inspect/restore table versions.

​
API Methods

Below is a summary of the primary methods. Each method is asynchronous, so you should await the call.

​
List tables

Description Retrieves a list of all table names currently present in the database. Returns:
  • A promise that resolves to an array of table name strings.
tables = await room.database.list_tables()

print(tables)  # ["users", "orders", "products", ...]

​
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.
    • mode: The creation mode (default is "create").
  • Returns: A promise that resolves once the table is created.
Example: 
await room.database.create_table_with_schema(
  name="users",
  schema={
    "id": IntDataType(),
    "username": TextDataType(),
    "email": TextDataType(),
  },
  data=[
    {"id": 1, "username": "alice", "email": "[email protected]" },
    {"id": 2, "username": "bob", "email": "[email protected]" }
  ],
  mode="create"
)

​
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: 
await room.database.create_table_from_data(
  name="orders",
  data=[
    {"id": 1, "product": "Laptop", "quantity": 2},
    {"id": 2, "product": "Phone", "quantity": 5},
  ],
  mode="overwrite"
)

​
Drop table

  • 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: 
await room.database.drop_table(
  name="temp_table",
  ignore_missing=True
)

​
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.
Example: 
await room.database.add_columns(
  table="users",
  new_columns={
    "isActive": "true",
    "createdAt": "CURRENT_TIMESTAMP",
  }
)

​
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: 
await room.database.drop_columns(
  table="users",
  columns=["deprecatedColumn1", "deprecatedColumn2"]
)

​
Insert

  • 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: 
await room.database.insert(
  table="users",
  records=[
    { "id": 3, "username": "charlie", "email": "[email protected]" },
    { "id": 4, "username": "dana", "email": "[email protected]" },
  ],
)

​
Update

  • 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 (e.g. { age: 30 }).
    • valuesSql: A record of key-value pairs for SQL expressions (e.g. { age: "age + 1" }).
  • Returns: A promise that resolves once the update is complete.
Example: 
await room.database.update(
  table="users",
  where="id = 3",
  values={ "email": "[email protected]" },
  values_sql={ "loginCount": "loginCount + 1" },
)

​
Delete

  • 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: 
await room.database.delete(
  table="users",
  where="id = 4"
)

​
Merge (upsert)

  • 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: 
await room.database.merge(
  table="users",
  on="id",
  records=[
    { "id": 1, "username": "alice", "email": "[email protected]" },
    { "id": 5, "username": "eric", "email": "[email protected]" },
  ],
)
  • 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.
    • offset: Optional offset for pagination.
    • limit: Maximum number of matching records to return.
    • select: An array of column names to be returned.
  • Returns: An array of matching records.
Example: 
results = await room.database.search(
  table="users",
  where={ "username": "alice" },
  limit=1
)

print(results)  # [{"id": 1, "username": "alice", "email": "[email protected]"}]

​
Optimize

  • Description: Optimizes a table (e.g., compacts its storage or rebuilds indexes if required).
  • Parameters:
    • table: Name of the table to optimize.
  • Returns: A promise that resolves once the operation is complete.
Example: 
await room.database.optimize(table="users")

​
Create vector index

  • Description: Creates a vector index on a specified column for vector similarity searches.
  • Parameters:
    • table: The target table name.
    • column: The column that holds vector data.
  • Returns: A promise that resolves once the index is created.
Example: 
await room.database.create_vector_index(
  table="documents",
  column="embedding",
)

​
Create scalar index

  • Description: Creates a scalar index (typical database index) on a specified column.
  • Parameters:
    • table: The target table name.
    • column: The column for which to create the scalar index.
  • Returns: A promise that resolves once the scalar index is created.
Example: 
await room.database.create_scalar_index(
  table="users",
  column="email",
)

​
Create full text search index

  • Description: Creates a full-text search index on a text column, useful for text-based queries.
  • Parameters:
    • table: The table name.
    • column: The text column to index.
  • Returns: A promise that resolves once the index is created.
Example: 
await room.database.create_full_text_search_index(
  table="articles",
  column="content",
)

​
Drop index

  • Description: Drop an index by name.
  • Parameters:
    • table: Table name.
    • name: Index name to drop.
  • Returns: None.
  • Availability: Python SDK/server today; JS/TS/.NET may add a helper in a future release.
await room.database.drop_index(table="users", name="email_idx")

​
List indexes

  • Description: Lists the indexes currently defined on a table.
  • Parameters:
    • table: The name of the table for which to list indexes.
  • Returns:
    • Python: list[TableIndex] (name, columns, type).
    • JS/TS: JsonResponse.json object with index metadata.
Example: 
indexes = await room.database.list_indexes(table="users")

print(indexes)
# Example output:
# {
#   "scalarIndexes": ["email"],
#   "fullTextIndexes": ["username"],
#   "vectorIndexes": ["profile_embedding"]
# }

​
Table versions (Python)

  • Description: List historical versions of a table and restore/checkout a specific version.
  • Methods:
    • list_versions(table) → list of versions (version, timestamp, metadata).
    • restore(table, version) → restore table to a prior version.
    • checkout(table, version) → load a version as the current working copy.
  • Availability: Python SDK/server today.
versions = await room.database.list_versions(table="users")
await room.database.restore(table="users", version=versions[-1].version)

​
Conclusion

The database interface provides a convenient abstraction for managing a database within a room server environment. It covers a broad set of operations—ranging from table creation and schema management to complex operations like vector-based and full-text searches. By handling the request/response flow through RoomClient, it keeps your database interactions modular and maintainable.