# How Database Repository Functions Handle SurrealDB Transactions in Open-Notebook

> Learn how Open-Notebook's repository functions manage SurrealDB transactions. Discover automatic connection management and transaction conflict handling via context managers.

- Repository: [Luis Novo/open-notebook](https://github.com/lfnovo/open-notebook)
- Tags: how-to-guide
- Published: 2026-06-18

---

**Open-Notebook encapsulates all SurrealDB interactions behind an async repository layer that uses a context manager to isolate each operation in its own transaction scope, automatically managing connection lifecycle and propagating transaction conflicts as RuntimeErrors.**

The [`open_notebook/database/repository.py`](https://github.com/lfnovo/open-notebook/blob/main/open_notebook/database/repository.py) module implements a thin **repository pattern** over **SurrealDB**, ensuring that every database operation executes within a clean transaction boundary. This pattern guarantees that each function call manages its own **connection lifecycle**—from authentication to closure—preventing connection leaks and providing consistent error handling for **transaction conflicts**.

## Isolated Transaction Scopes via Async Context Managers

### The `db_connection` Context Manager

At lines 47-62 of [`open_notebook/database/repository.py`](https://github.com/lfnovo/open-notebook/blob/main/open_notebook/database/repository.py), the `db_connection` **async context manager** establishes the foundation for all SurrealDB transaction handling. This function creates an `AsyncSurreal` client, authenticates with the configured credentials, selects the appropriate namespace and database, and yields the active connection.

When the context exits, the connection closes automatically. This ensures that every repository operation—from queries to deletions—runs inside its own dedicated transaction scope without relying on global connection state.

## CRUD Operations and SurrealQL Execution

Each repository function leverages the `db_connection` context manager to execute **SurrealQL** commands while normalizing return data and handling errors consistently.

### Query Execution with `repo_query`

The `repo_query` function (lines 65-82) serves as the low-level interface for executing SurrealQL strings. It accepts a query string and optional variables, opens a connection via the context manager, and executes `connection.query()`. After execution, it normalizes SurrealDB `RecordID` objects into Python dictionaries before returning the result set.

### Creating Records

`repo_create` and `repo_insert` (lines 85-103) handle document insertion with automatic timestamp management. The functions inject creation and update timestamps into the payload before calling `connection.insert()`. If SurrealDB returns a string error message, the repository translates it into a Python `RuntimeError` to standardize exception handling across the codebase.

### Updating and Upserting Documents

For modifications, `repo_update` (lines 134-155) constructs proper record IDs and executes an `UPDATE ... MERGE` query to merge the supplied payload into existing documents. The `repo_upsert` function (lines 123-132) provides similar functionality using `UPSERT ... MERGE`, allowing atomic create-or-update operations.

### Relating Records

The `repo_relate` function (lines 106-119) manages graph relationships by issuing `RELATE ... CONTENT` commands. Like other operations, it utilizes `repo_query` internally, inheriting the same connection lifecycle management and transaction isolation.

## Transaction Conflict Detection and Error Handling

SurrealDB may raise transaction conflicts during concurrent write operations. The repository catches these errors at debug level (lines 77-79 in `repo_query` and lines 84-89 in insert operations) to prevent log noise, then re-raises them as `RuntimeError` exceptions. This allows higher-level workflows—such as LangGraph graphs—to implement retry logic or surface errors to users.

## Implementation Examples

The following examples demonstrate how to interact with SurrealDB transactions through the repository API.

Create a new notebook record:

```python
from open_notebook.database.repository import repo_create

async def create_notebook():
    data = {"title": "My First Notebook", "owner": "alice"}
    notebook = await repo_create("notebook", data)
    return notebook

```

Handle potential transaction conflicts during updates:

```python
from open_notebook.database.repository import repo_update, RuntimeError

async def safe_update_notebook(notebook_id, payload):
    try:
        updated = await repo_update("notebook", notebook_id, payload)
        return updated
    except RuntimeError as exc:
        # SurrealDB transaction conflict detected

        raise

```

Relate two records with a graph edge:

```python
from open_notebook.database.repository import repo_relate

async def link_source_to_notebook(source_id, notebook_id):
    await repo_relate(source_id, "belongs_to", notebook_id, {"added": True})

```

Perform an atomic upsert with automatic timestamping:

```python
from open_notebook.database.repository import repo_upsert

async def upsert_user(user_id, attrs):
    await repo_upsert("user", user_id, attrs, add_timestamp=True)

```

## Summary

- The repository pattern in [`open_notebook/database/repository.py`](https://github.com/lfnovo/open-notebook/blob/main/open_notebook/database/repository.py) encapsulates all SurrealDB interactions behind async context managers.
- Each operation creates its own isolated connection and transaction scope via the `db_connection` context manager defined at lines 47-62.
- CRUD functions like `repo_query`, `repo_create`, `repo_update`, and `repo_relate` handle RecordID normalization and automatic timestamp insertion.
- Transaction conflicts are caught, logged at debug level, and re-raised as `RuntimeError` for upstream handling.
- There is no global transaction state; atomic multi-operation workflows must handle sequencing and retries at the application layer.

## Frequently Asked Questions

### Does Open-Notebook support multi-statement atomic transactions?

No, the repository does not expose explicit transaction begin/commit APIs. Each repository function executes in its own independent transaction scope. To achieve atomicity across multiple operations, you must orchestrate calls within a single async function and implement retry logic at the application level, as SurrealDB automatically commits each statement individually.

### How does the repository handle database connection failures?

The `db_connection` context manager manages the full lifecycle of `AsyncSurreal` clients, including authentication and namespace selection. If connection establishment fails or the server rejects authentication, the exception propagates immediately. When operations complete or fail, the connection closes automatically to prevent resource leaks.

### What is the difference between `repo_create` and `repo_insert`?

Both functions insert documents into SurrealDB, but `repo_create` specifically adds automatic timestamp fields (`created_at` and `updated_at`) to the payload before insertion. `repo_insert` provides a lower-level interface that may not include automatic timestamping, depending on the specific implementation in the repository layer.

### How are SurrealDB RecordIDs normalized in Python?

The `repo_query` function automatically converts SurrealDB `RecordID` objects into Python dictionaries after query execution. This normalization ensures that record identifiers are serializable and consistent across the application, preventing issues with custom SurrealDB types leaking into higher-level business logic.