# Understanding the Async-First Database Design with SurrealDB in Open Notebook

> Explore the async-first database design in Open Notebook using SurrealDB. Learn how Python async primitives ensure non-blocking event loop operations for every database interaction.

- Repository: [Luis Novo/open-notebook](https://github.com/lfnovo/open-notebook)
- Tags: deep-dive
- Published: 2026-06-30

---

**Open Notebook implements an async-first persistence layer where every SurrealDB operation—from connection establishment to schema migrations—uses Python async primitives to prevent blocking the event loop.**

Open Notebook treats its database layer as a fully asynchronous component, leveraging SurrealDB's native support for concurrent operations through Python's `asyncio` patterns. This design ensures that FastAPI endpoints and long-running workflows remain responsive under heavy I/O loads. Every interaction with SurrealDB is wrapped in async context managers and helper functions that handle connection lifecycle, error recovery, and data serialization automatically.

## Async Connection Management with SurrealDB

The foundation of the persistence layer is the `db_connection` context manager defined in [`open_notebook/database/repository.py`](https://github.com/lfnovo/open-notebook/blob/main/open_notebook/database/repository.py). This `@asynccontextmanager` creates a single `AsyncSurreal` client, authenticates the session, selects the appropriate namespace and database, and guarantees proper cleanup via `await db.close()` upon exit [【/open_notebook/database/repository.py#L62-L75】.

Because the context manager yields a fully configured connection, any coroutine can acquire a database handle without blocking other tasks:

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

async def check_health():
    async with db_connection() as conn:
        result = await conn.query("SELECT 1 FROM notebook LIMIT 1;")
        return result

```

The connection parameters—including URL, namespace, database, and credentials—are loaded from environment variables with sensible defaults, enabling seamless deployment across development, staging, and production environments [【/open_notebook/database/repository.py#L17-L41】.

## Centralized CRUD Operations

All database operations delegate to centralized repository helpers that wrap the underlying SurrealDB calls inside `async with db_connection()`. These functions—`repo_query`, `repo_create`, `repo_upsert`, `repo_update`, `repo_delete`, and `repo_insert`—automatically parse `RecordID` objects into plain strings for easier downstream handling [【/open_notebook/database/repository.py#L78-L165】.

### Error Handling for Transaction Conflicts

Under high concurrency, SurrealDB may raise transaction-conflict errors. The repository layer catches these runtime exceptions, logs them at the **debug** level to avoid noisy production logs, and bubbles the exception to the caller for retry logic or graceful degradation [【/open_notebook/database/repository.py#L85-L92】. This approach prevents a single conflicting transaction from crashing the entire service while maintaining visibility for debugging.

## Async Schema Migration System

Schema migrations are handled by the `AsyncMigration` class in [`open_notebook/database/async_migrate.py`](https://github.com/lfnovo/open-notebook/blob/main/open_notebook/database/async_migrate.py). Migrations are stored as plain `.surrealql` files and executed through an async connection established by `AsyncMigration.run()`. This method executes the raw SQL and then atomically bumps or lowers the version counter using helper functions [【/open_notebook/database/async_migrate.py#L36-L46】.

The `AsyncMigrationManager` orchestrates the migration lifecycle. It discovers the current version via `get_latest_version`, determines if migrations are required, and sequentially runs all pending "up" migrations in order [【/open_notebook/database/async_migrate.py#L86-L92】【【/open_notebook/database/async_migrate.py#L91-L101】].

### Version Tracking in SurrealDB

Migration state persists in a dedicated SurrealDB table named `_sbl_migrations`. Helper functions including `get_latest_version`, `bump_version`, and `lower_version` query or manipulate this table, ensuring the migration process remains idempotent even if the migration runner is interrupted [【/open_notebook/database/async_migrate.py#L4-L38】.

## Practical Implementation Examples

The following patterns demonstrate how to interact with the async-first repository layer in application code.

### Querying Records

Use `repo_query` to fetch data with automatic connection handling:

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

async def list_notebooks():
    sql = "SELECT * FROM notebook ORDER BY created DESC;"
    notebooks = await repo_query(sql)
    return notebooks

```

The `repo_query` helper opens a connection, executes the SurrealQL, and returns a list of dictionaries [【/open_notebook/database/repository.py#L78-L89】.

### Creating Records

The `repo_create` helper injects timestamp fields and sanitizes input data:

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

async def create_source(data: dict):
    # Automatically adds created/updated timestamps and strips stray `id` fields

    source = await repo_create("source", data)
    return source

```

This returns the created record with a proper SurrealDB ID formatted as a string [【/open_notebook/database/repository.py#L98-L110】.

### Upserting Data

For update-or-insert operations, use `repo_upsert` with timestamp refreshing:

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

async def upsert_notebook(notebook_id: str, data: dict):
    # `add_timestamp=True` refreshes the `updated` field automatically

    await repo_upsert("notebook", notebook_id, data, add_timestamp=True)

```

The `repo_upsert` function issues a SurrealQL UPSERT statement, merging the payload with existing records [【/open_notebook/database/repository.py#L136-L144】.

### Running Migrations at Startup

Execute pending migrations during application initialization:

```python
from open_notebook.database.async_migrate import AsyncMigrationManager

async def run_startup_migrations():
    manager = AsyncMigrationManager()
    if await manager.needs_migration():
        await manager.run_migration_up()

```

The manager checks the current version against the `_sbl_migrations` table, then runs all missing `.surrealql` files in sequence [【/open_notebook/database/async_migrate.py#L86-L100】.

## Summary

- **Async Connection Context**: The `db_connection` context manager in [`open_notebook/database/repository.py`](https://github.com/lfnovo/open-notebook/blob/main/open_notebook/database/repository.py) provides non-blocking access to SurrealDB with automatic cleanup.
- **Centralized Repository Pattern**: Helper functions like `repo_query` and `repo_upsert` handle connection acquisition, `RecordID` serialization, and debug-level error logging for transaction conflicts.
- **Idempotent Migration System**: `AsyncMigrationManager` tracks schema versions in the `_sbl_migrations` table and executes `.surrealql` files atomically.
- **Environment-Based Configuration**: Database credentials and connection parameters load from environment variables with sensible defaults.
- **Scalability Benefits**: The async-first design prevents I/O blocking in FastAPI endpoints and long-running background tasks.

## Frequently Asked Questions

### How does the async connection context manager ensure proper resource cleanup?

The `db_connection` function is decorated with `@asynccontextmanager` and explicitly calls `await db.close()` in a `finally` block, ensuring that `AsyncSurreal` connections are released back to the pool even if exceptions occur during query execution [【/open_notebook/database/repository.py#L62-L75】.

### What is the purpose of the `_sbl_migrations` table in SurrealDB?

The `_sbl_migrations` table stores the current schema version and migration history. Helper functions like `get_latest_version` and `bump_version` interact with this table to make the migration process idempotent, preventing duplicate migrations if the runner is restarted mid-process [【/open_notebook/database/async_migrate.py#L4-L38】.

### How does Open Notebook handle transaction conflicts when multiple requests access SurrealDB concurrently?

When SurrealDB raises a transaction-conflict error under high concurrency, the repository helpers catch the runtime exception, log it at the **debug** level to avoid cluttering production logs, and re-raise the exception to allow the caller to implement retry logic or return an appropriate error response [【/open_notebook/database/repository.py#L85-L92】.

### Why does the repository layer convert RecordID objects to plain strings?

SurrealDB returns record identifiers as `RecordID` objects, but the repository helpers automatically serialize these into plain strings before returning data to the application layer. This simplifies downstream handling in JSON APIs and prevents serialization errors in FastAPI responses [【/open_notebook/database/repository.py#L78-L165】.