Database Migration Strategy Using AsyncMigrationManager on Open Notebook API Startup

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 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 (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:

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:

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, 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. 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.

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