# Database Migration Strategy Using AsyncMigrationManager on Open Notebook API Startup

> Learn the database migration strategy for Open Notebook API startup. AsyncMigrationManager automatically runs SurrealDB schema migrations before server requests, updating your database seamlessly.

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

---

**The Open Notebook API automatically executes SurrealDB schema migrations at startup through the `AsyncMigrationManager`, which detects the current schema version, compares it against available migration files, and runs pending changes within the FastAPI lifespan context before the server accepts requests.**

The `lfnovo/open-notebook` repository implements an asynchronous, fail-fast database migration strategy using `AsyncMigrationManager` to ensure the SurrealDB schema stays synchronized with the application code. This mechanism runs automatically every time the FastAPI server starts, verifying schema version integrity before the API becomes available to handle requests.

## How AsyncMigrationManager Orchestrates Startup Migrations

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

FastAPI calls the `@asynccontextmanager` decorated `lifespan` function in [`api/main.py`](https://github.com/lfnovo/open-notebook/blob/main/api/main.py) during application startup and shutdown. Inside the startup block (lines 98-105), the code instantiates `AsyncMigrationManager` and triggers the migration check sequence to prepare the database before loading API routers.

### Version Detection via _sbl_migrations

The `AsyncMigrationManager.get_current_version()` method queries the `_sbl_migrations` table through `get_latest_version()` in [`open_notebook/database/async_migrate.py`](https://github.com/lfnovo/open-notebook/blob/main/open_notebook/database/async_migrate.py) (lines 71-80). If the table does not exist, the system defaults to version 0, ensuring fresh databases start from the beginning of the migration chain.

### Detecting Pending Schema Changes

The `needs_migration()` method compares the current database version against the number of upward migrations bundled in the manager. The constructor pre-loads all migration files (e.g., `1.surrealql`, `2.surrealql`) from `open_notebook/database/migrations/` (lines 96-108), creating a complete inventory of required schema versions.

### Executing Migrations with AsyncMigrationRunner

When migrations are required, `run_migration_up()` delegates to `AsyncMigrationRunner.run_all()`, which iterates from the current version upward and executes each `AsyncMigration.run()` (lines 66-73). Each migration:

- Opens a SurrealDB connection via `db_connection()`
- Executes the cleaned SQL statement using `await connection.query(self.sql)`
- Updates the version tracking via `bump_version()` or `lower_version()`

### Fail-Fast Error Handling

Any exception during migration execution is logged and re-raised as a `RuntimeError`, causing immediate API startup abortion. This guarantees the server never runs with an outdated or partially migrated schema, enforcing strict consistency between code and database state.

## Running Migrations Manually and Inspection

### Triggering Migrations in Scripts

You can manually trigger the migration strategy outside the API lifespan:

```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())

```

### Checking Current Schema Version

Inspect the 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 New Migration Files

To extend the schema:

1. Create a new file in `open_notebook/database/migrations/` (e.g., `15.surrealql`)
2. The `AsyncMigrationManager` automatically includes it when the class loads the migration directory (note: you may need to update the hard-coded last index if extending beyond the existing range)

## Summary

- **Automatic Execution**: The `AsyncMigrationManager` runs inside the FastAPI lifespan context in [`api/main.py`](https://github.com/lfnovo/open-notebook/blob/main/api/main.py), ensuring migrations complete before the server accepts traffic.
- **Version Tracking**: Schema state is stored in the `_sbl_migrations` table, defaulting to version 0 if uninitialized.
- **Ordered Processing**: Migrations execute sequentially from the current version through `AsyncMigrationRunner.run_all()`, handling each `.surrealql` file in `open_notebook/database/migrations/`.
- **Atomic Connections**: Each migration opens its own SurrealDB connection via `db_connection()` and executes SQL via `await connection.query()`.
- **Fail-Fast Safety**: Startup aborts immediately if any migration fails, preventing the API from running against an incompatible schema.

## Frequently Asked Questions

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

`AsyncMigrationManager` compares the current version stored in the `_sbl_migrations` table (retrieved via `get_current_version()`) against the total number of migration files loaded from `open_notebook/database/migrations/` (lines 96-108). If the database version is lower than the number of available "up" migrations, `needs_migration()` returns true and `run_migration_up()` executes the pending changes.

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

The migration strategy implements fail-fast behavior. If `AsyncMigration.run()` raises an exception during execution in `AsyncMigrationRunner.run_all()`, the error is logged and re-raised as a `RuntimeError` in [`open_notebook/database/async_migrate.py`](https://github.com/lfnovo/open-notebook/blob/main/open_notebook/database/async_migrate.py). This aborts the API startup process immediately, ensuring the server never runs with an incomplete or corrupted schema.

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

Yes. You can instantiate `AsyncMigrationManager` directly in scripts or tests, call `await manager.needs_migration()` to check for pending changes, and execute `await manager.run_migration_up()` to apply them. This is useful for command-line maintenance tools or CI/CD pipelines that need to prepare the database before deployment.

### Where are the migration SQL files stored?

Migration files reside in `open_notebook/database/migrations/` with numeric naming (e.g., `1.surrealql`, `2.surrealql`). The `AsyncMigrationManager` constructor loads these files automatically, though you may need to update the internal index constants if adding migrations beyond the current hard-coded range.