Database Facility

About

Databases play an integral role in storing and aggregating data in distributed environments. The Database Management Facility implemented in omni.services.facilities.database.manager offers APIs to manage the lifecycle of databases, abstracting away the low-level concerns of instantiation and connection pooling in order to simplify queries.

Configuration

Database configuration options for SQLite, MySQL or PostgreSQL are expected to be found in the application settings, under the dbs property of the Extension which is initiating the Facility. The following TOML settings provide an example of configuration for a MySQL database to be used by an Extension initializing the Facility:

extension.toml
1# [...]
2
3[settings.exts."my.extension.name".dbs.database-handle]
4connection_string = "mysql:///host.name"
5min_size = 4
6max_size = 10

Note that the connection_string property is required for all databases, but any other property supplied will be forwarded to databases, the underlying Python package powering the database abstraction layer. See https://www.encode.io/databases for additional information about available configuration options.

Any additional arguments can also be supplied as querystring arguments to the connection string. For example, the following is functionally equivalent to the settings above:

extension.toml
1# [...]
2
3[settings.exts."my.extension.name".dbs.database-handle]
4connection_string = "mysql:///host.name?min_size=4&max_size=10"

Usage

Once provided, a connection to a database can be obtained by referring to its handle, provided as key to the dbs property of the Extension settings. For example, using the configuration sample provided above:

sample.py
 1database_facility = DatabaseManagerFacility("my.extension.name")
 2async with database_facility.get("database-handle") as db:
 3    # Create a table:
 4    query = "CREATE TABLE IF NOT EXISTS HighScores (id INTEGER PRIMARY KEY, name VARCHAR(100), score INTEGER)"
 5    await db.execute(query=query)
 6
 7    # Insert some data:
 8    query = "INSERT INTO HighScores(name, score) VALUES (:name, :score)"
 9    values = [
10        {"name": "Daisy", "score": 92},
11        {"name": "Neil", "score": 87},
12        {"name": "Carol", "score": 43},
13    ]
14    await database.execute_many(query=query, values=values)
15
16    # Execute a database query:
17    query = "SELECT * FROM HighScores"
18    rows = await database.fetch_all(query=query)
19    print("High scores:", rows)

Implementation References

For examples of the Database Facility being used in the Omniverse Farm ecosystem, consult the following extensions:

  • omni.services.facilities.database.manager: Reference implementation of Database management features

  • omni.services.farm.facilities.store.db: Database storage for Omniverse Farm tasks