# How SurrealDB Handles Automatic Schema Migrations on API Startup in Open Notebook

> Learn how Open Notebook uses SurrealDB for automatic schema migrations on API startup. Discover seamless database updates before your app accepts requests.

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

---

**Open Notebook automatically executes SurrealDB schema migrations during FastAPI startup using an `AsyncMigrationManager` that reads the current version from a `_sbl_migrations` table, compares it against numbered SurrealQL scripts in the migrations directory, and runs pending upgrades sequentially before the application accepts requests.**

The `lfnovo/open-notebook` repository implements a fully asynchronous migration system that ensures the SurrealDB database schema stays synchronized with application code. By hooking into the FastAPI lifespan coroutine defined in [`api/main.py`](https://github.com/lfnovo/open-notebook/blob/main/api/main.py), the system guarantees schema consistency every time the API service launches, preventing runtime errors caused by outdated database structures.

## Core Migration Architecture

The migration system centers on three primary components working together during the startup sequence:

- **`AsyncMigrationManager`** – Orchestrates the detection and execution logic
- **Numbered SurrealQL files** – Store schema changes in `open_notebook/database/migrations/`
- **`_sbl_migrations` table** – Tracks the current schema version in SurrealDB

According to the source code in [`open_notebook/database/async_migrate.py`](https://github.com/lfnovo/open-notebook/blob/main/open_notebook/database/async_migrate.py), the manager loads migration scripts automatically and determines which ones need execution based on version comparison logic.

## Step-by-Step Migration Process

When the API starts, the lifespan coroutine triggers a three-phase migration workflow:

### Step 1: Detect Current Schema Version

The `get_latest_version()` function queries the hidden `_sbl_migrations` table via `repo_query` to determine the database's current state. As implemented in `open_notebook/database/async_migrate.py:199‑205`, the function returns the stored version number or defaults to `0` if the table does not exist yet.

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

current = await get_latest_version()
print(f"Current DB version: {current}")  # Returns 0 on fresh database

```

### Step 2: Determine Migration Requirements

The `needs_migration()` method compares the detected version against the number of available migration scripts loaded into `self.up_migrations`. If the stored version is less than the total available migrations, the system identifies which scripts require execution.

```python

# Internally implemented at async_migrate.py:175‑179

if current_version < len(self.up_migrations):
    return True  # Migration required

```

### Step 3: Execute Pending Migrations

The `run_migration_up()` method calls `self.runner.run_all()`, which iterates through pending migrations and executes each `AsyncMigration.run()` coroutine. Each migration:

1. Opens a temporary SurrealDB connection using `db_connection()` from `open_notebook/database/repository.py:47‑62`
2. Executes the raw SurrealQL via `await connection.query(self.sql)`
3. Calls `bump_version()` to atomically update the version tracking table upon success

This sequential execution ensures schema changes apply in order, with the version bumping only occurring after successful query completion.

## Migration File Structure and Discovery

Migration files reside in `open_notebook/database/migrations/` and follow a strict naming convention:

- **Up migrations**: `1.surrealql`, `2.surrealql`, `3.surrealql` (sequential integers)
- **Down migrations** (optional): `1_down.surrealql`, `2_down.surrealql`

The `AsyncMigration.from_file()` class method (lines 22‑35 in [`async_migrate.py`](https://github.com/lfnovo/open-notebook/blob/main/async_migrate.py)) reads these files, strips comments, and stores the cleaned SQL for execution. Adding a new migration requires no code changes—simply create the next numbered file:

```sql
-- open_notebook/database/migrations/15.surrealql
CREATE table podcast_episode;
DEFINE FIELD title ON podcast_episode TYPE string;

```

## FastAPI Lifespan Integration

The migration trigger lives in the `lifespan` coroutine within [`api/main.py`](https://github.com/lfnovo/open-notebook/blob/main/api/main.py). During startup (lines 178‑190), the code:

1. Instantiates `AsyncMigrationManager`
2. Calls `await manager.get_latest_version()` to query the current state
3. Checks `await manager.needs_migration()` to determine if work is required
4. Executes `await manager.run_migration_up()` if migrations are pending
5. Logs the final version or "already up-to-date" status

If any migration fails, the system raises a `RuntimeError` that aborts API startup, ensuring the service never runs against an incompatible schema.

## Error Handling and Safety Guarantees

The migration system implements strict safety measures:

- **Atomic version tracking**: The `bump_version()` function updates `_sbl_migrations` only after successful query execution
- **Startup blocking**: Failures in `run_migration_up()` propagate to the lifespan coroutine, preventing the FastAPI application from initializing
- **Connection isolation**: Each migration uses a fresh connection via `db_connection()`, preventing connection state pollution between migrations

## Practical Usage Examples

### Running Migrations Manually

You can trigger migrations outside of the standard startup flow using the 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")

```

### Adding a New Schema Change

1. Create `open_notebook/database/migrations/16.surrealql`:

```sql
-- Add indexes for performance
DEFINE INDEX idx_note_created ON note FIELDS created_at;

```

2. Optionally create `16_down.surrealql` for rollback support
3. Restart the API—the `AsyncMigrationManager` automatically detects and executes the new script

## Summary

- **Automatic detection**: The `AsyncMigrationManager` queries the `_sbl_migrations` table on startup to determine current schema version
- **Sequential execution**: Migrations run in order from `1.surrealql` through the latest numbered file, with each query executing in its own database connection
- **Zero-downtime safety**: The API aborts startup if migrations fail, preventing code/schema mismatches
- **File-based management**: Simply add numbered SurrealQL files to `open_notebook/database/migrations/` without modifying Python code
- **Async-first design**: All migration logic uses `async`/`await` patterns compatible with FastAPI's asynchronous architecture

## Frequently Asked Questions

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

The system raises a `RuntimeError` in the lifespan coroutine at [`api/main.py`](https://github.com/lfnovo/open-notebook/blob/main/api/main.py), which prevents the FastAPI application from completing startup. This ensures the service never runs with a partially applied or outdated schema. You must fix the failing SurrealQL syntax or database issue before the API can start.

### How do I add a new schema migration to Open Notebook?

Create a new SurrealQL file in `open_notebook/database/migrations/` using the next sequential number (e.g., `17.surrealql`). Write your schema changes using standard SurrealQL syntax. Optionally create a matching `17_down.surrealql` file for rollback support. The `AsyncMigrationManager` automatically detects new files on the next API startup.

### Where does SurrealDB store the current migration version?

Open Notebook tracks versions in a hidden table named `_sbl_migrations` within your SurrealDB database. The `get_latest_version()` function queries this table via `repo_query`, and `bump_version()` updates it after each successful migration. If the table is missing, the system assumes version 0 and creates the table during the first migration run.

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

Yes. Import `AsyncMigrationManager` from `open_notebook.database.async_migrate` and call `await manager.run_migration_up()` within an async context. This uses the same code path as the automatic startup sequence, querying the current version and executing any pending migrations from the `open_notebook/database/migrations/` directory.