# Database Migration Process at API Startup Using AsyncMigrationManager

> Learn the API database migration process at startup using AsyncMigrationManager. Open Notebook API automatically runs SurrealDB schema migrations via FastAPI lifespan hooks for an up-to-date database.

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

---

**When the Open Notebook API starts, the AsyncMigrationManager automatically detects and executes pending SurrealDB schema migrations through the FastAPI lifespan hook, ensuring the database schema is always current before serving requests.**

The `lfnovo/open-notebook` repository implements an asynchronous, fail-fast migration system for SurrealDB that runs automatically during API startup. This **database migration process at API startup using AsyncMigrationManager** is orchestrated by classes defined in [`open_notebook/database/async_migrate.py`](https://github.com/lfnovo/open-notebook/blob/main/open_notebook/database/async_migrate.py) and invoked from the application's lifespan handler in [`api/main.py`](https://github.com/lfnovo/open-notebook/blob/main/api/main.py).

## How the AsyncMigrationManager Orchestrates Startup Migrations

The migration process integrates deeply with FastAPI's application lifecycle, running before the server accepts any traffic.

### Lifespan Hook Integration in api/main.py

The entry point for migrations is the `@asynccontextmanager` decorator named `lifespan` in [`api/main.py`](https://github.com/lfnovo/open-notebook/blob/main/api/main.py). FastAPI calls this context manager during startup and shutdown events. Inside the startup block, the code instantiates `AsyncMigrationManager` and triggers the migration check:

```python

# Conceptual excerpt from api/main.py

@asynccontextmanager
async def lifespan(app: FastAPI):
    # Startup phase

    migration_manager = AsyncMigrationManager()
    if await migration_manager.needs_migration():
        await migration_manager.run_migration_up()
    # ... continue to yield and startup

```

This ensures migrations complete successfully before the API begins serving requests.

### Version Detection and Schema State

The `AsyncMigrationManager.get_current_version()` method queries the `_sbl_migrations` table via `get_latest_version()` to determine the current schema version. If the table does not exist, the version defaults to `0`. This version detection happens in [`open_notebook/database/async_migrate.py`](https://github.com/lfnovo/open-notebook/blob/main/open_notebook/database/async_migrate.py) at lines 71-80, providing the baseline for determining which migrations must run.

## The Migration Execution Flow

Once the current version is established, the manager executes a coordinated sequence to bring the schema up to date.

### Loading Migration Files from the File System

The `AsyncMigrationManager` constructor pre-loads all migration files from `open_notebook/database/migrations/` (e.g., `1.surrealql`, `2.surrealql`, etc.) during instantiation. The `needs_migration()` method compares the current database version against the count of available *up* migrations bundled in the manager. This check occurs at lines 96-108 in [`async_migrate.py`](https://github.com/lfnovo/open-notebook/blob/main/async_migrate.py).

### Executing Migrations with AsyncMigrationRunner

When migrations are required, `run_migration_up()` delegates to `AsyncMigrationRunner.run_all()`. The runner iterates from the current version upward, executing each `AsyncMigration.run()` method in sequence. Each individual migration performs three operations:

1. Opens a SurrealDB connection using `db_connection()` from [`open_notebook/database/repository.py`](https://github.com/lfnovo/open-notebook/blob/main/open_notebook/database/repository.py)
2. Executes the cleaned SQL statement via `await connection.query(self.sql)`
3. Updates the version table using `bump_version()` (or `lower_version()` for rollbacks)

This process ensures **ordered, idempotent schema evolution** for SurrealDB.

### Error Handling and Fail-Fast Behavior

Any exception during migration execution is logged and re-raised as a `RuntimeError`. This fail-fast design guarantees that the API startup aborts immediately rather than running with an outdated or partially migrated schema. Once `run_migration_up()` completes without error, the lifespan handler logs the new version (lines 124-132 in [`api/main.py`](https://github.com/lfnovo/open-notebook/blob/main/api/main.py)) and proceeds to load routers.

## Practical Implementation Examples

### Manually Triggering Migrations

Use this pattern in scripts or tests to run migrations outside the normal API startup flow:

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

async def migrate():
    manager = AsyncMigrationManager()
    if await manager.needs_migration():
        await manager.run_migration_up()
    print("Database is now at version:", await manager.get_current_version())

```

### Inspecting the Current Schema Version

To check the database version without running migrations:

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

async def show_version():
    version = await get_latest_version()
    print(f"Current DB schema version: {version}")

```

### Adding a New Migration File

To extend the schema:

1. Create a new file in `open_notebook/database/migrations/` (e.g., `15.surrealql`) containing your SurrealQL DDL/DML statements.
2. The `AsyncMigrationManager` automatically detects this file on the next startup because the constructor loads all files matching the expected pattern.

Note: If you extend beyond the existing hard-coded range, you may need to add the entry to the manager's internal list.

## Summary

- **Lifespan integration**: The `AsyncMigrationManager` is instantiated and executed inside the FastAPI `lifespan` context manager in [`api/main.py`](https://github.com/lfnovo/open-notebook/blob/main/api/main.py).
- **Version tracking**: Current schema version is stored in the `_sbl_migrations` table and defaults to `0` if missing.
- **Automatic detection**: The manager loads all `.surrealql` files from `open_notebook/database/migrations/` and compares them against the current version.
- **Fail-fast execution**: Any migration error raises `RuntimeError`, aborting API startup to prevent running on an invalid schema.
- **Asynchronous coordination**: The `AsyncMigrationRunner` handles connection management via `db_connection()` and ensures atomic version updates.

## Frequently Asked Questions

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

If any migration throws an exception, the `AsyncMigrationRunner` catches the error, logs it, and re-raises a `RuntimeError`. This aborts the API startup process entirely, preventing the server from running with an incomplete or corrupted schema. You must fix the migration script or restore the database before the API can start.

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

The manager calls `get_current_version()` to read the latest version from the `_sbl_migrations` table, then compares this value against the number of *up* migrations loaded from `open_notebook/database/migrations/`. It executes every migration file with a version number higher than the current database version, running them sequentially via `AsyncMigrationRunner.run_all()`.

### Where are migration files stored in the Open Notebook repository?

Migration files are stored in `open_notebook/database/migrations/` as numbered SurrealQL files (e.g., `1.surrealql`, `2.surrealql`). The `AsyncMigrationManager` automatically discovers and loads these files during instantiation, mapping each file to an `AsyncMigration` instance for execution.

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

Yes. You can instantiate `AsyncMigrationManager` directly in Python scripts or test suites, then call `needs_migration()` followed by `run_migration_up()`. This manual execution pattern is useful for database maintenance tasks, CI/CD pipelines, or testing environments where you need to migrate the schema without starting the full FastAPI application.