# How Open Notebook Manages Async Migrations with AsyncMigrationManager

> Learn how the AsyncMigrationManager in open-notebook handles SurrealDB async migrations. Discover version tracking and asynchronous execution for seamless schema evolution.

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

---

**The `AsyncMigrationManager` class in the open-notebook repository orchestrates SurrealDB schema evolution by scanning SurrealQL migration files, tracking versions in a dedicated `_sbl_migrations` table, and executing changes asynchronously through `AsyncMigrationRunner`.**

Open Notebook uses SurrealDB as its primary data store, requiring a reliable pipeline to apply schema changes without blocking the event loop. The `AsyncMigrationManager`—implemented in [`open_notebook/database/async_migrate.py`](https://github.com/lfnovo/open-notebook/blob/main/open_notebook/database/async_migrate.py)—transforms raw SurrealQL scripts into a version-controlled, fault-tolerant migration system that runs entirely with async-compatible database calls.

## Migration Discovery and Ordering

The manager initializes its migration catalog during instantiation, building two ordered lists that map directly to files stored in `open_notebook/database/migrations/`.

### Building the Migration Lists

In the constructor, `AsyncMigrationManager` populates:

- **`self.up_migrations`** – A list of `AsyncMigration` objects created from `*_surrealql` files representing forward schema changes.
- **`self.down_migrations`** – Matching rollback scripts (e.g., `1_down.surrealql`) for reversing changes.

By loading these lists at startup, the manager knows every migration step ahead of time, enabling deterministic ordering regardless of when files were added to the directory.

## Version Tracking in SurrealDB

Schema state persistence relies on a lightweight bookkeeping table called `_sbl_migrations` that lives inside SurrealDB itself.

### Reading and Updating Versions

- **`get_latest_version()`** queries the table for the highest `version` entry, returning `0` if the table does not yet exist.
- **`bump_version()`** appends a new entry immediately after a successful *up* migration completes.
- **`lower_version()`** removes the latest entry following a successful *down* migration rollback.

This approach ensures atomic version tracking that stays synchronized with the actual database schema.

## Executing Migrations with AsyncMigrationRunner

The core execution logic delegates to an `AsyncMigrationRunner` instance (defined in lines 66‑89 of [`async_migrate.py`](https://github.com/lfnovo/open-notebook/blob/main/async_migrate.py)) that handles the actual SQL execution.

### Running Pending Migrations

When `run_migration_up()` is called, the manager first checks `needs_migration()` to determine if the current database version is less than the total number of available up scripts. If migrations are required, it invokes `self.runner.run_all()`, which iterates from the current DB version to the end of the `up_migrations` list.

Each migration executes via `await connection.query(self.sql)`, and the version is bumped immediately after successful completion. Errors are logged via **loguru** and re-raised to ensure FastAPI startup fails fast if a migration cannot be applied.

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

async def initialise_db():
    migration_mgr = AsyncMigrationManager()
    await migration_mgr.run_migration_up()   # applies any pending up‑migrations

# Usually called from the FastAPI startup event

# app.add_event_handler("startup", lambda: asyncio.create_task(initialise_db()))

```

### Checking Migration Status

You can inspect the current state without applying changes by comparing the database version against the discovered migration files:

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

async def show_migration_state():
    mgr = AsyncMigrationManager()
    current = await mgr.get_current_version()
    pending = len(mgr.up_migrations) - current
    print(f"DB version: {current}, pending migrations: {pending}")

# asyncio.run(show_migration_state())

```

### Rolling Back Changes

For disaster recovery or testing, the runner supports downward migrations through the `run_one_down()` method, which executes the corresponding down script and calls `lower_version()` to update the tracking table:

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

async def rollback_one():
    mgr = AsyncMigrationManager()
    await mgr.runner.run_one_down()   # runs the last down‑script and lowers version

```

## Synchronous Compatibility Layer

Legacy startup code that has not been converted to async/await can still trigger migrations through [`open_notebook/database/migrate.py`](https://github.com/lfnovo/open-notebook/blob/main/open_notebook/database/migrate.py). This module instantiates `AsyncMigrationManager` and exposes a synchronous API by running the async methods inside an event loop, allowing existing FastAPI startup hooks to call `migration_manager.run_migration_up()` without being async-aware.

## FastAPI Integration

The migration manager serves as a gatekeeper during application initialization. In [`api/main.py`](https://github.com/lfnovo/open-notebook/blob/main/api/main.py), the `AsyncMigrationManager` is instantiated at API startup, guaranteeing that the SurrealDB schema is always up-to-date before any endpoint handles requests. This integration ensures that deployment rollouts automatically apply schema changes before the service accepts traffic.

## Summary

- **File Discovery**: `AsyncMigrationManager` scans `open_notebook/database/migrations/` for SurrealQL files at initialization, creating ordered lists of up and down migrations.
- **Version Control**: A `_sbl_migrations` table tracks the current schema version, with `get_latest_version()`, `bump_version()`, and `lower_version()` managing state transitions.
- **Async Execution**: `AsyncMigrationRunner` executes pending SQL asynchronously, handling errors through loguru and failing fast on migration errors.
- **Legacy Support**: [`migrate.py`](https://github.com/lfnovo/open-notebook/blob/main/migrate.py) provides a synchronous wrapper for non-async startup code.
- **Startup Safety**: The manager runs automatically during FastAPI startup in [`api/main.py`](https://github.com/lfnovo/open-notebook/blob/main/api/main.py), ensuring schema consistency before request handling begins.

## Frequently Asked Questions

### How does AsyncMigrationManager know which migrations to run?

The manager compares the current version stored in the `_sbl_migrations` table against the length of `self.up_migrations`. If the database version is less than the total number of discovered up scripts, `needs_migration()` returns true and `run_all()` executes every pending migration sequentially.

### What happens if a migration fails during startup?

The migration runner catches exceptions, logs them via loguru, and re-raises the error immediately. This causes the FastAPI startup process to halt, preventing the application from serving requests against an incomplete or inconsistent schema.

### Can I run migrations manually outside of the API startup?

Yes. You can instantiate `AsyncMigrationManager` in any async context and call `await mgr.run_migration_up()` to apply pending changes, or use `await mgr.runner.run_one_down()` to roll back the most recent migration. The synchronous wrapper in [`migrate.py`](https://github.com/lfnovo/open-notebook/blob/main/migrate.py) also supports manual execution from synchronous scripts.

### Where are the actual SurrealQL migration files stored?

Migration scripts reside in `open_notebook/database/migrations/` within the repository. Up migrations follow the naming pattern `*.surrealql` while corresponding down migrations use the `*_down.surrealql` suffix, allowing the manager to pair related forward and rollback scripts automatically.