# How Automatic Database Migrations Work with AsyncMigrationManager on API Startup in Open Notebook

> Discover how Open Notebook's API seamlessly handles automatic database migrations with AsyncMigrationManager on startup. Ensure your SurrealDB schema is always up-to-date before requests arrive.

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

---

**The Open Notebook API automatically executes pending SurrealDB schema migrations during FastAPI startup using an `AsyncMigrationManager` that detects the current version, compares it against available migration files, and runs them asynchronously before the server accepts requests.**

When the Open Notebook API boots, it ensures the SurrealDB schema is current without manual intervention. This is handled by the `AsyncMigrationManager` class defined in [`open_notebook/database/async_migrate.py`](https://github.com/lfnovo/open-notebook/blob/main/open_notebook/database/async_migrate.py), which integrates with FastAPI's lifespan context to provide asynchronous, ordered, and idempotent schema evolution.

## The Lifespan Hook: Triggering Migrations on Startup

In [`api/main.py`](https://github.com/lfnovo/open-notebook/blob/main/api/main.py), the application defines an `@asynccontextmanager` called `lifespan`. FastAPI invokes this during the startup and shutdown phases. Inside the startup block, the code instantiates `AsyncMigrationManager` and triggers the migration check before loading API routers.

```python

# Conceptual flow 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 load routers

```

## Detecting Schema Version and Pending Changes

The `AsyncMigrationManager` performs a three-step validation process to determine if work is required.

### Current Version Detection

`get_current_version()` queries the `_sbl_migrations` table via `get_latest_version()`. If the table does not exist (as in a fresh database), the version defaults to 0.

### Migration Inventory

The constructor pre-loads every migration file from `open_notebook/database/migrations/`. These files follow the naming convention `1.surrealql`, `2.surrealql`, etc., and represent sequential schema changes.

### Needs Migration Check

`needs_migration()` compares the current database version against the count of available "up" migrations bundled in the manager. If the database version is lower than the number of migration files, the system identifies pending work.

## Executing Migrations Asynchronously

When migrations are required, `run_migration_up()` delegates to `AsyncMigrationRunner.run_all()`. The runner iterates from the current version upward, executing each `AsyncMigration.run()` which performs the following:

1. Opens a SurrealDB connection using `db_connection()`
2. Executes the SQL statement via `await connection.query(self.sql)`
3. Updates the version table using `bump_version()` (or `lower_version()` for rollbacks)

## Fail-Fast Error Handling

Any exception during migration execution is logged and re-raised as a `RuntimeError`, causing the API startup to abort immediately. This "fail-fast" design guarantees that the server never runs with an outdated or partially migrated schema, preventing data model inconsistencies in production.

## Practical Code Examples

### Manually Triggering Migrations

Use this pattern in scripts, tests, or administrative tools to run migrations outside of the standard API startup:

```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 Version

To check the schema 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

1. Create a file in `open_notebook/database/migrations/` with the next sequential number (e.g., `15.surrealql`).
2. Write your SurrealQL DDL/DML statements in the file.
3. The `AsyncMigrationManager` automatically includes the new migration on the next startup because the constructor loads all files from the migrations directory.

## Summary

- **Lifespan Integration**: The `AsyncMigrationManager` runs inside the `lifespan` context manager in [`api/main.py`](https://github.com/lfnovo/open-notebook/blob/main/api/main.py), ensuring migrations complete before the API serves requests.
- **Version Tracking**: Uses the `_sbl_migrations` table with automatic fallback to version 0 for new databases.
- **Asynchronous Execution**: `AsyncMigrationRunner.run_all()` processes migrations sequentially via `AsyncMigration.run()`, handling database connections and versioning atomically.
- **Safety First**: Fail-fast behavior aborts startup on any migration error, preventing the API from running against an outdated schema.

## Frequently Asked Questions

### What happens if the `_sbl_migrations` table doesn't exist?

If the table is missing, `get_current_version()` defaults to version 0, and the migration manager treats all available migrations as pending. This allows the system to bootstrap itself on a fresh SurrealDB instance automatically.

### How does the migration manager determine which migrations to run?

`AsyncMigrationManager` compares the current database version (stored in `_sbl_migrations`) against the number of `.surrealql` files in `open_notebook/database/migrations/`. If the file count exceeds the stored version, `needs_migration()` returns `True`, and `run_migration_up()` executes the missing versions sequentially.

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

Yes. You can instantiate `AsyncMigrationManager` manually in scripts or tests and call `await manager.run_migration_up()` after checking `needs_migration()`. This is useful for CI/CD pipelines or administrative maintenance without starting the full FastAPI server.

### Where are migration files stored and how are they discovered?

Migration files are stored in `open_notebook/database/migrations/` with sequential numeric names like `1.surrealql`, `2.surrealql`, etc. The `AsyncMigrationManager` constructor automatically discovers these files when instantiated, so adding a new migration requires no registry updates—simply create the file with the next number in the sequence.