Skip to content

Modules Storage

Logan Fick edited this page Feb 13, 2026 · 4 revisions

Modules - Storage

Each module gets its own SQLite database file, isolated from other modules. Storage is accessed through ctx.storage. The API is async and handles connection pooling, WAL mode, and transaction management automatically. Queries are written in standard SQL.

Schema Setup

Tables should be created in an @on_setup function. Use CREATE TABLE IF NOT EXISTS so the module is safe to restart:

from owlbot.api import ModuleContext, on_setup

@on_setup
async def setup(ctx: ModuleContext) -> None:
    await ctx.storage.execute("""
        CREATE TABLE IF NOT EXISTS scores (
            user_id TEXT PRIMARY KEY,
            display_name TEXT NOT NULL,
            score INTEGER NOT NULL DEFAULT 0
        )
    """)

The database file lives at <storage_dir>/<module_name>.db (default: data/<module_name>.db). The directory is created automatically if it doesn't exist.

Query Methods

All methods are available on ctx.storage:

Method Returns Description
execute(sql, parameters=()) aiosqlite.Cursor Run any SQL statement.
execute_many(sql, parameters) aiosqlite.Cursor Run the same statement with multiple parameter sets.
fetch_one(sql, parameters=()) aiosqlite.Row | None Return a single row.
fetch_all(sql, parameters=()) list[aiosqlite.Row] Return all matching rows.
fetch_value(sql, parameters=()) Any | None Return the first column of the first row.

Writing

execute() runs any SQL statement and returns a cursor with lastrowid and rowcount:

cursor = await ctx.storage.execute(
    "INSERT INTO scores (user_id, display_name, score) VALUES (?, ?, ?)",
    (user.id, user.display_name, 100),
)
new_id = cursor.lastrowid

execute_many() is the batch equivalent, running the same statement across multiple parameter sets:

await ctx.storage.execute_many(
    "INSERT OR IGNORE INTO scores (user_id, display_name, score) VALUES (?, ?, ?)",
    [(u.id, u.display_name, 0) for u in users],
)

Reading

fetch_one() returns a single row, or None if there are no results:

row = await ctx.storage.fetch_one(
    "SELECT score FROM scores WHERE user_id = ?", (user.id,)
)
if row:
    score = row["score"]

fetch_all() returns all matching rows as a list:

rows = await ctx.storage.fetch_all(
    "SELECT display_name, score FROM scores ORDER BY score DESC LIMIT 10"
)
for row in rows:
    print(f"{row['display_name']}: {row['score']}")

fetch_value() returns the first column of the first row, useful for aggregates like COUNT(*). Returns None if there are no results:

count = await ctx.storage.fetch_value(
    "SELECT COUNT(*) FROM scores WHERE score > ?", (1000,)
)

Row Access

Rows are returned as aiosqlite.Row objects, which support both dict-style and index access:

row = await ctx.storage.fetch_one("SELECT user_id, score FROM scores LIMIT 1")

# Dict-style:
user_id = row["user_id"]
score = row["score"]

# Index-style:
user_id = row[0]
score = row[1]

Parameterized Queries

Use ? placeholders with tuple parameters, or :name with dict parameters:

# Positional (tuple):
await ctx.storage.execute(
    "UPDATE scores SET score = ? WHERE user_id = ?",
    (new_score, user_id),
)

# Named (dict):
await ctx.storage.execute(
    "UPDATE scores SET score = :score WHERE user_id = :uid",
    {"score": new_score, "uid": user_id},
)

Never use f-strings or string formatting for SQL parameters. This prevents SQL injection.

Transactions

Each handler invocation runs inside a single transaction. All storage operations within the handler share that transaction. If the handler completes normally, everything is committed together. If it raises an exception, everything is rolled back. No manual commit or rollback is needed.

For finer-grained control within a handler, use the transaction() context manager to group operations that should succeed or fail together independently of the rest of the handler:

async with ctx.storage.transaction():
    await ctx.storage.execute("UPDATE balances SET amount = amount - ?", (cost,))
    await ctx.storage.execute("INSERT INTO purchases (item) VALUES (?)", (item,))
    # Both committed together on success, or both rolled back if either fails

Connection Pooling

Storage uses a connection pool with lazy creation. Connections are created on-demand up to pool_size (default 4, configurable in config.yaml under owlbot.pool_size). WAL (Write-Ahead Logging) mode is enabled on all connections so concurrent readers and a single writer don't block each other.

The pool is per-module; each module's database has its own pool. Connection management is fully automatic.

Error Handling

Storage operations raise StorageError on failure:

from owlbot.api import StorageError

try:
    await ctx.storage.execute("INSERT INTO scores VALUES (?, ?, ?)", (id, name, 0))
except StorageError as e:
    ctx.logger.error(f"Failed to insert score: {e}")

StorageError wraps the underlying aiosqlite.Error so the message is readable without needing to catch the low-level exception.

Clone this wiki locally