# How Open Notebook Handles SurrealDB Async Migration and Schema Setup at API Startup

> Learn how Open Notebook uses FastAPI lifespan to handle SurrealDB async migration and schema setup seamlessly ensuring your API starts with the correct database structure.

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

---

**The FastAPI lifespan coroutine triggers `AsyncMigrationManager` to detect the current schema version, compare it against available migration scripts, and execute pending SurrealQL migrations before the API accepts requests, aborting startup if any migration fails.**

The `lfnovo/open-notebook` repository implements a robust asynchronous migration system for SurrealDB that runs automatically during application initialization. When the FastAPI service starts, the **lifespan** context manager in [`api/main.py`](https://github.com/lfnovo/open-notebook/blob/main/api/main.py) orchestrates schema validation and migration execution, ensuring the database schema matches the application's expectations before handling traffic.

## The Lifespan Startup Hook

Every API startup begins in [`api/main.py`](https://github.com/lfnovo/open-notebook/blob/main/api/main.py) where the `lifespan` coroutine serves as the central orchestrator. This hook executes after environment variables load and security checks complete, but before the application accepts incoming requests.

The lifespan sequence:
1. Creates an `AsyncMigrationManager` instance
2. Queries the current schema version from SurrealDB
3. Determines if migrations are required
4. Executes pending migrations sequentially
5. Logs the final version or aborts startup on failure

If any step fails, the API raises a `RuntimeError` and prevents the service from starting with an outdated schema, eliminating data inconsistency risks.

## AsyncMigrationManager Core Logic

The migration engine resides in [`open_notebook/database/async_migrate.py`](https://github.com/lfnovo/open-notebook/blob/main/open_notebook/database/async_migrate.py). This module provides the `AsyncMigrationManager` class that coordinates version detection, comparison, and execution.

### Schema Version Detection

The manager identifies the current database state through `get_latest_version()` (lines 199‑205). This function queries the hidden `_sbl_migrations` table using `repo_query` to retrieve the stored version number.

If the `_sbl_migrations` table does not exist—such as during initial deployment—the system defaults to version `0`. This zero-based default ensures that fresh installations automatically run all available migrations from the beginning.

### Migration Execution Flow

When `needs_migration()` determines that the stored version differs from the number of available `up_migrations` scripts, the manager triggers `run_migration_up()` (lines 66‑73). This method delegates to `self.runner.run_all()`, which iterates through pending migrations and executes each `AsyncMigration.run` coroutine.

Individual migrations follow this strict sequence:
- Open a temporary SurrealDB connection via `db_connection()` from `open_notebook/database/repository.py:47‑62`
- Execute the raw SurrealQL using `await connection.query(self.sql)`
- Increment the version counter via `bump_version()` upon successful completion

This transactional approach ensures that each schema change commits independently before the next migration begins.

## Migration File Structure and Format

Migration scripts reside in `open_notebook/database/migrations/` as plain SurrealQL files following the naming convention `{version}.surrealql` (for example, `1.surrealql`, `2.surrealql`). The system also supports optional rollback scripts named `{version}_down.surrealql`.

The `AsyncMigration.from_file` static method (lines 22‑35) handles file ingestion by:
- Reading the SurrealQL file contents
- Stripping comments from the SQL
- Storing the cleaned query for execution

This design allows developers to add new schema changes by simply creating numbered files without modifying application code.

## Error Handling and Safety Guarantees

The migration system implements fail-fast semantics to protect data integrity. If any migration query fails during execution, the error propagates to the lifespan coroutine in [`api/main.py`](https://github.com/lfnovo/open-notebook/blob/main/api/main.py), which immediately raises a `RuntimeError` and halts API startup.

This guarantee ensures that:
- The service never runs against a partially migrated schema
- Administrators receive immediate feedback about database configuration issues
- Failed migrations block traffic until the schema discrepancy resolves

## Code Examples

### Running Migrations Manually

For administrative scripts or maintenance tasks, you can invoke the migration manager directly:

```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("Migrations applied successfully")
    else:
        print("Database already at latest version")

```

### Creating a New Migration

To add a schema change, create a numbered SurrealQL file:

```sql
-- 15.surrealql: add podcast episodes table
CREATE table podcast_episode;

```

Optionally create a rollback script:

```sql
-- 15_down.surrealql: remove podcast episodes table
REMOVE table podcast_episode;

```

The manager automatically detects and sequences any new numbered files on the next startup.

### Checking Current Version

Inspect the database version programmatically:

```python
from open_notebook.database.async_migrate import get_latest_version

current = await get_latest_version()
print(f"Current DB version: {current}")

```

## Summary

- **Lifespan orchestration**: [`api/main.py`](https://github.com/lfnovo/open-notebook/blob/main/api/main.py) triggers migrations during FastAPI startup before accepting requests
- **Version tracking**: `get_latest_version()` queries the `_sbl_migrations` table, defaulting to `0` for new databases
- **Sequential execution**: `AsyncMigrationManager` runs migrations from `open_notebook/database/migrations/` in numeric order
- **Connection handling**: Each migration opens a temporary connection via `db_connection()` from the repository module
- **Safety mechanism**: Startup aborts with `RuntimeError` if any migration fails, preventing service initialization with inconsistent schemas

## Frequently Asked Questions

### What happens if the SurrealDB migrations table is missing?

If the `_sbl_migrations` table does not exist, `get_latest_version()` returns `0` as the default version. This causes `AsyncMigrationManager` to execute all available migration scripts sequentially, effectively performing a fresh installation of the entire schema history.

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

Yes, you can instantiate `AsyncMigrationManager` and call `needs_migration()` followed by `run_migration_up()` in any async context, such as administrative scripts or maintenance tasks. The migration engine operates independently of the FastAPI lifespan once imported.

### How does the system handle failed migrations?

If a migration query fails, the `AsyncMigration.run` method raises an exception that propagates to the lifespan coroutine in [`api/main.py`](https://github.com/lfnovo/open-notebook/blob/main/api/main.py). This triggers a `RuntimeError` that aborts API startup, ensuring the service never initializes with a partially applied schema. You must fix the migration file or database state before the API will start.

### What is the difference between the async and sync migration files?

The repository includes [`open_notebook/database/async_migrate.py`](https://github.com/lfnovo/open-notebook/blob/main/open_notebook/database/async_migrate.py) for modern asynchronous operations and [`open_notebook/database/migrate.py`](https://github.com/lfnovo/open-notebook/blob/main/open_notebook/database/migrate.py) as a synchronous wrapper for backward compatibility. The FastAPI application exclusively uses the async version during startup, while the sync version remains available for legacy tooling or blocking contexts.