How AsyncMigrationManager Handles SurrealDB Schema Migrations on API Startup

TLDR: The AsyncMigrationManager class automatically detects and executes pending SurrealDB schema migrations during FastAPI startup by comparing the current version stored in _sbl_migrations against ordered SQL files in open_notebook/database/migrations/, ensuring the database schema is always current before the API serves requests.

In the lfnovo/open-notebook repository, database schema consistency is enforced through an automated migration system that runs before the API accepts any requests. The AsyncMigrationManager orchestrates this process within the FastAPI lifespan context manager defined in api/main.py, guaranteeing that the SurrealDB schema matches the application's expectations on every startup.

The Migration Lifecycle on API Startup

When the FastAPI application initializes, the lifespan async context manager triggers a four-step migration sequence. This executes exactly once during startup, blocking route initialization until the schema is verified or updated.

Step 1: Instantiating the Manager

The process begins by creating an instance of AsyncMigrationManager, which constructs two ordered lists of AsyncMigration objects from the migration files located in open_notebook/database/migrations/.

from open_notebook.database.async_migrate import AsyncMigrationManager

async def lifespan(app: FastAPI):
    migration_manager = AsyncMigrationManager()
    # ... additional steps

According to the source code in open_notebook/database/async_migrate.py, the constructor builds ordered lists for both upgrade and downgrade paths, parsing each .surrealql file into an AsyncMigration object.

Step 2: Detecting the Current Version

The manager queries the _sbl_migrations table to determine the existing schema version. If the table does not exist, it returns version 0.

current_version = await migration_manager.get_current_version()
logger.info(f"Current database version: {current_version}")

The get_current_version method delegates to get_latest_version, which reads the version record from SurrealDB.

Step 3: Checking Migration Status

Before executing any SQL, the manager compares the current version against the total number of available migrations.

if await migration_manager.needs_migration():
    logger.warning("Database migrations are pending. Running migrations...")

The needs_migration method simply checks if the current version is less than the length of the up_migrations list, indicating pending upgrades.

Step 4: Executing Pending Migrations

When migrations are required, run_migration_up() processes each pending migration sequentially. The AsyncMigrationRunner iterates over the pending AsyncMigration objects, executing SQL statements via the SurrealDB client and incrementing the version after each successful operation.

await migration_manager.run_migration_up()
logger.success(
    f"Migrations completed successfully. Database is now at version "
    f"{await migration_manager.get_current_version()}"
)

The run_migration_up method calls self.runner.run_all(), which fetches the current version, loops through pending migrations, and invokes AsyncMigration.run() for each file.

Core Migration Components

The migration system relies on three coordinated classes defined in open_notebook/database/async_migrate.py.

AsyncMigrationManager

The AsyncMigrationManager serves as the primary interface, coordinating version detection and migration execution. It maintains two ordered lists—up_migrations and down_migrations—containing AsyncMigration objects parsed from the filesystem.

self.up_migrations = [
    AsyncMigration.from_file("open_notebook/database/migrations/1.surrealql"),
    AsyncMigration.from_file("open_notebook/database/migrations/2.surrealql"),
    # ... through migration 14

]

AsyncMigrationRunner

This class handles the execution logic. The run_all() method iterates through pending migrations, calling AsyncMigration.run() for each file and managing transaction boundaries.

AsyncMigration

Each AsyncMigration object represents a single schema change file. The run method executes the SurrealQL against the database and updates the version tracker.

async def run(self, bump: bool = True) -> None:
    async with db_connection() as connection:
        await connection.query(self.sql)  # Execute SurrealQL

    if bump:
        await bump_version()  # Increment version in _sbl_migrations

    else:
        await lower_version()

Migration File Organization

Schema changes are stored as numbered SurrealQL files in open_notebook/database/migrations/. The naming convention uses sequential integers (e.g., 1.surrealql, 2.surrealql, 14.surrealql) to determine execution order. The AsyncMigrationManager constructor loads these files into ordered lists, ensuring migrations apply in the correct sequence.

Error Handling and Safety Guarantees

The migration system includes strict safety mechanisms to prevent serving requests with an outdated schema. If any migration fails, the startup process aborts immediately with a RuntimeError, preventing the FastAPI application from completing initialization.

# From api/main.py

try:
    await migration_manager.run_migration_up()
except Exception as e:
    logger.error(f"Migration failed: {e}")
    raise RuntimeError("Database migration failed, aborting startup")

Because migrations run inside the lifespan context manager before any routes are mounted, the API cannot serve traffic until the schema is fully updated. The bump_version function updates the _sbl_migrations table only after successful SQL execution, ensuring atomic version tracking.

Summary

• Automatic execution: The AsyncMigrationManager runs automatically within the FastAPI lifespan context in api/main.py, requiring no manual intervention.
• Version tracking: It queries the _sbl_migrations table to detect the current schema state and compares it against ordered migration files.
• Sequential processing: Migrations execute in order via AsyncMigrationRunner, with each AsyncMigration object executing SurrealQL through the db_connection client.
• Startup blocking: Any migration failure raises a RuntimeError that halts API startup, preventing schema mismatches in production.

Frequently Asked Questions

How does AsyncMigrationManager know which migrations to run?

The AsyncMigrationManager constructs ordered lists of AsyncMigration objects from files in open_notebook/database/migrations/. It compares the current version (stored in the _sbl_migrations table) against the length of the up_migrations list. Any migration with a higher sequence number than the current version is considered pending and executed via run_migration_up().

What happens if a migration fails during API startup?

If a migration throws an exception, the error propagates to the lifespan handler in api/main.py, which logs the failure and raises a RuntimeError. This aborts the FastAPI startup process entirely, preventing the application from serving requests with an incomplete or failed schema update.

Where are the SurrealDB migration files stored in the repository?

Migration files are stored in open_notebook/database/migrations/ with the extension .surrealql. The AsyncMigrationManager constructor specifically loads files named sequentially (e.g., 1.surrealql, 2.surrealql) to build the ordered migration lists.

Can migrations run while the API is serving requests?

No. Migrations execute within the FastAPI lifespan async context manager, which runs exactly once before the application accepts any HTTP requests. This design ensures the database schema is fully updated before traffic hits the endpoints, eliminating race conditions between schema changes and query execution.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}