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

> Learn how Open Notebook API uses AsyncMigrationManager for automatic database migrations on startup. Ensure your SurrealDB schema is always up-to-date before requests.

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

---

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

When the FastAPI application in the lfnovo/open-notebook repository starts, it orchestrates automatic database migrations to ensure the SurrealDB schema always matches the codebase expectations. This process 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) and invoked through the application's lifespan context manager in [`api/main.py`](https://github.com/lfnovo/open-notebook/blob/main/api/main.py).

## The Lifespan Hook: Triggering Migrations on Startup

In [`api/main.py`](https://github.com/lfnovo/open-notebook/blob/main/api/main.py), the `@asynccontextmanager` decorator defines a `lifespan` function (lines 98-105) that FastAPI calls during startup and shutdown events. Inside the startup block, the code instantiates `AsyncMigrationManager` to check for and execute any pending schema changes before the application begins accepting requests.

This design ensures the database schema is fully migrated before any API routes become available, preventing runtime errors caused by missing tables or columns.

## Version Detection and Migration Discovery

The **AsyncMigrationManager** follows a two-step discovery process to determine if migrations are required.

### Reading the Current Schema Version

The `get_current_version()` method queries the `_sbl_migrations` table using `get_latest_version()` (lines 71-80 in [`open_notebook/database/async_migrate.py`](https://github.com/lfnovo/open-notebook/blob/main/open_notebook/database/async_migrate.py)) to retrieve the last applied migration sequence. If the table does not exist or contains no records, the version defaults to `0`, indicating a fresh database state.

This version check represents the single source of truth for the database's current schema state.

### Scanning Available Migration Files

The `needs_migration()` method compares the current version against the total number of available migration files. The manager pre-loads all SurrealQL migration files (such as `1.surrealql`, `2.surrealql`, etc.) from the `open_notebook/database/migrations/` directory during initialization (lines 96-108).

If the current version is lower than the number of available migration files, the system identifies pending migrations that must be applied before the API can safely start.

## Executing Pending Migrations

When `needs_migration()` returns `True`, the manager initiates the execution workflow to bring the database schema up to date.

### The Migration Runner Workflow

The `run_migration_up()` method delegates to `AsyncMigrationRunner.run_all()` (lines 66-73), which iterates from the current version upward through each pending migration. For every migration, `AsyncMigration.run()` performs three critical operations:

1. **Opens a connection** to SurrealDB using the `db_connection()` helper from [`open_notebook/database/repository.py`](https://github.com/lfnovo/open-notebook/blob/main/open_notebook/database/repository.py).
2. **Executes the SQL** by sending the cleaned-up SurrealQL statement via `await connection.query(self.sql)`.
3. **Updates the version record** by calling `bump_version()` (or `lower_version()` for rollbacks) to maintain consistency in the `_sbl_migrations` table.

This sequence ensures each migration executes atomically and in correct order, maintaining schema integrity throughout the process.

### Fail-Fast Error Handling

If any exception occurs during migration execution, the system logs the error and raises a `RuntimeError`, causing the API startup to abort immediately. This "fail-fast" design prevents the server from running with an outdated or partially migrated schema, which could lead to data corruption or unpredictable application behavior.

## Post-Migration Confirmation

After successful migration completion, the lifespan handler logs the new schema version (lines 124-132 in [`api/main.py`](https://github.com/lfnovo/open-notebook/blob/main/api/main.py)) and proceeds to load FastAPI routers. This confirmation step provides visibility into the applied changes before the application enters its ready state.

## Practical Examples

### Triggering Migrations Manually

For scripts, tests, or administrative tasks, you can manually invoke the migration manager outside of the standard 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())

```

### Checking Current Schema Version

To inspect the current 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

To extend the schema with new changes:

1. Create a new file in `open_notebook/database/migrations/` following the numeric sequence (e.g., `15.surrealql` if the last file is `14.surrealql`).
2. Add the corresponding DDL/DML statements to the file.
3. Restart the API; the `AsyncMigrationManager` automatically detects and applies the new migration on startup.

## Core Files and Components

The automatic migration system relies on these key files:

- **[`open_notebook/database/async_migrate.py`](https://github.com/lfnovo/open-notebook/blob/main/open_notebook/database/async_migrate.py)** – Contains the core async migration classes (`AsyncMigration`, `AsyncMigrationRunner`, `AsyncMigrationManager`) and version bookkeeping logic.
- **[`api/main.py`](https://github.com/lfnovo/open-notebook/blob/main/api/main.py)** – Defines the FastAPI entry point and the `lifespan` context manager that triggers migrations on startup.
- **`open_notebook/database/migrations/*.surrealql`** – Individual SurrealQL migration scripts (e.g., `1.surrealql`, `2.surrealql`) containing schema changes.
- **[`open_notebook/database/repository.py`](https://github.com/lfnovo/open-notebook/blob/main/open_notebook/database/repository.py)** – Provides the `db_connection` and `repo_query` utilities used by the migration runner to communicate with SurrealDB.

## 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 versions are stored in the `_sbl_migrations` table, with `get_current_version()` defaulting to `0` when uninitialized.
- **Sequential processing**: The `AsyncMigrationRunner` applies migrations in order from the current version through the latest available file in `open_notebook/database/migrations/`.
- **Fail-fast safety**: Any migration error raises `RuntimeError` and aborts startup, preventing operation against an outdated schema.
- **Idempotent design**: The system checks `needs_migration()` before execution, allowing safe restarts without re-running applied migrations.

## Frequently Asked Questions

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

The migration system implements a fail-fast strategy. If any `AsyncMigration.run()` call raises an exception, the error is logged and re-raised as a `RuntimeError`, causing the API startup process to abort immediately. This prevents the server from running with an inconsistent or partially migrated schema.

### How does AsyncMigrationManager track schema versions?

The manager queries the `_sbl_migrations` table via `get_latest_version()` to determine the current schema version. If the table does not exist, it defaults to version `0`. After each successful migration, `bump_version()` updates this table to reflect the new state, creating a durable record of applied changes.

### Can migrations be run manually outside of the automatic startup process?

Yes, you can instantiate `AsyncMigrationManager` directly in scripts or test suites. Call `await manager.needs_migration()` to check for pending changes, then `await manager.run_migration_up()` to apply them. This is useful for deployment scripts or database maintenance tasks that require explicit migration control.

### Where should new SurrealQL migration files be placed?

New migration files belong in `open_notebook/database/migrations/` and should follow the numeric naming convention (e.g., `15.surrealql`). The `AsyncMigrationManager` automatically loads these files during initialization and includes them in the pending migration check when the API restarts.