The database client serves as a high-level interface for interacting with a relational-like database.

​
Overview

The database interface is designed to simplify common database operations in a room-based system. Each method in database client 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.

​
API Methods

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

​
1. 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", ...]

​
2. 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": "alice@example.com" },
    {"id": 2, "username": "bob", "email": "bob@example.com" }
  ],
  mode="create"
)

​
3. 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"
)

​
4. 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
)

​
5. 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",
  }
)

​
6. 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"]
)

​
7. 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": "charlie@example.com" },
    { "id": 4, "username": "dana", "email": "data@example.com" },
  ],
)

​
8. 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": "newcharlie@example.com" },
  values_sql={ "loginCount": "loginCount + 1" },
)

​
9. 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"
)

​
10. Merge

  • 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": "alice_new@example.com" },
    { "id": 5, "username": "eric", "email": "eric@example.com" },
  ],
)
  • 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: Either an SQL WHERE clause string or an object representing key-value equals conditions.
    • 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": "alice@example.com"}]

​
12. 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")

​
13. 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",
)

​
14. 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",
)

​
15. 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",
)

​
16. List Indexes

  • Description: Lists the indexes currently defined on a table.
  • Parameters:
    • table: The name of the table for which to list indexes.
  • Returns: A record object containing index information.
Example: 
indexes = await room.database.list_indexes(table="users")

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

​
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.