# Setting Up SurrealDB with Automatic Schema Migrations in Open Notebook

> Discover how to set up SurrealDB with automatic schema migrations using Open Notebooks async-first migration system. Synchronize your database schema effortlessly.

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

---

**Open Notebook provides an async-first migration system that automatically synchronizes your SurrealDB schema with application code using numbered SurrealQL files and a built-in migration manager.**

The `lfnovo/open-notebook` repository ships with a complete database migration framework designed specifically for SurrealDB. This system handles schema evolution without downtime, tracking versions in a dedicated `_sbl_migrations` table and executing migrations asynchronously to keep the FastAPI server non-blocking.

## How the Async Migration System Works

The migration architecture in [`open_notebook/database/async_migrate.py`](https://github.com/lfnovo/open-notebook/blob/main/open_notebook/database/async_migrate.py) consists of three core components working together to provide safe, reversible schema changes.

**AsyncMigration** loads individual SurrealQL files from disk. Each migration lives as a numbered file in `open_notebook/database/migrations/` (e.g., `1.surrealql`, `2.surrealql`), where the class strips comments and stores the raw SQL for execution.

**AsyncMigrationRunner** executes migrations sequentially, providing methods like `run_all()`, `run_one_up()`, and `run_one_down()` to apply or revert specific steps. It wraps each operation in proper error handling and transaction management.

**AsyncMigrationManager** serves as the high-level coordinator. It constructs the migration lists, checks current schema versions against available migrations, and provides the `run_migration_up()` entry point used during application startup.

## Configuring the SurrealDB Connection

Before migrations can run, the system establishes an async connection using environment variables defined in [`open_notebook/database/repository.py`](https://github.com/lfnovo/open-notebook/blob/main/open_notebook/database/repository.py).

The `db_connection()` context manager reads:
- `SURREAL_URL` (or constructs from `SURREAL_ADDRESS` and `SURREAL_PORT`)
- `SURREAL_USER` and `SURREAL_PASSWORD`
- `SURREAL_NAMESPACE` and `SURREAL_DATABASE`

Helper functions like `get_database_url()` and `get_database_password()` provide backward-compatible defaults for local development, ensuring the service works out of the box against a local SurrealDB instance.

```python
from open_notebook.database.repository import db_connection

async with db_connection() as connection:
    await connection.query("SELECT * FROM example_table")

```

## Creating and Storing Migration Files

Migrations follow a simple convention: each schema change is a plain SurrealQL file stored in `open_notebook/database/migrations/` with a sequential numeric prefix.

The `AsyncMigration.from_file()` class method handles loading:

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

# Load a specific migration

migration = AsyncMigration.from_file("open_notebook/database/migrations/1.surrealql")

```

When adding new schema changes, create a file like `16.surrealql` containing valid SurrealQL statements (table definitions, indexes, or data transformations), then register it in the `AsyncMigrationManager` initialization.

## Automatic Migration at Startup

The system integrates with FastAPI's lifespan events to ensure the database schema is current before handling requests. In [`api/main.py`](https://github.com/lfnovo/open-notebook/blob/main/api/main.py), the application creates an `AsyncMigrationManager` and awaits `run_migration_up()` during startup.

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

async def start_migrations():
    manager = AsyncMigrationManager()
    if await manager.needs_migration():
        await manager.run_migration_up()
    else:
        print("SurrealDB schema is up-to-date")

# In the FastAPI lifespan or startup event

@app.on_event("startup")
async def on_startup():
    await start_migrations()

```

If the database is new, the manager executes all pending migrations sequentially, creating tables, indexes, and type definitions. If already current, it logs "Database is already at the latest version" and proceeds immediately.

## Managing Schema Versions

Version tracking relies on the `_sbl_migrations` table, which stores the current integer version and timestamp. The `AsyncMigrationManager` uses helper functions `get_latest_version()`, `bump_version()`, and `lower_version()` to manipulate this state.

Check the current schema version programmatically:

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

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

```

When `run_migration_up()` executes, it applies each pending migration within the async context and updates the version table after each successful step, ensuring crash-safety and consistency.

## Manual Migration Control and Debugging

For development or debugging scenarios, you can instantiate `AsyncMigrationRunner` directly with specific migration subsets:

```python
from open_notebook.database.async_migrate import AsyncMigrationRunner, AsyncMigration

# Load specific migrations manually

up_migrations = [
    AsyncMigration.from_file("open_notebook/database/migrations/1.surrealql"),
    AsyncMigration.from_file("open_notebook/database/migrations/2.surrealql"),
]

runner = AsyncMigrationRunner(up_migrations, down_migrations=[])
await runner.run_one_up()  # Applies only the first pending migration

```

This approach is useful when testing individual schema changes or recovering from specific error states without running the full migration suite.

## Error Handling and Concurrency Safety

The migration system handles SurrealDB transaction conflicts gracefully. When concurrent writes cause conflicts, the system catches these exceptions, logs them at debug level, and re-raises them to allow caller retry logic.

Unexpected SurrealQL errors bubble up as `RuntimeError` instances containing the raw database message, providing developers with exact failure details without masking underlying issues.

All operations use `AsyncSurreal` for non-blocking I/O, ensuring migration execution doesn't block the FastAPI event loop or incoming HTTP requests.

## Summary

- **Environment-based configuration**: Set `SURREAL_URL`, `SURREAL_USER`, `SURREAL_PASSWORD`, `SURREAL_NAMESPACE`, and `SURREAL_DATABASE` in your environment to configure the database connection.
- **File-based migrations**: Create numbered `.surrealql` files in `open_notebook/database/migrations/` and register them in `AsyncMigrationManager`.
- **Automatic execution**: The FastAPI startup event triggers `AsyncMigrationManager.run_migration_up()` to apply pending changes automatically.
- **Version tracking**: The `_sbl_migrations` table tracks schema state via `get_latest_version()` and `bump_version()`.
- **Async safety**: All operations leverage `AsyncSurreal` and handle transaction conflicts for zero-downtime deployments.

## Frequently Asked Questions

### How do I add a new schema migration to the project?

Create a new SurrealQL file in `open_notebook/database/migrations/` with the next sequential number (e.g., `16.surrealql` after `15.surrealql`). Add your table definitions or alter statements, then append the file path to the `up_migrations` list in `AsyncMigrationManager.__init__`. The next application startup will automatically detect and apply the new migration.

### Can I roll back a migration if something goes wrong?

Yes. The `AsyncMigrationRunner` provides `run_one_down()` and related methods for reversing migrations. To enable rollbacks, populate the `down_migrations` list when constructing the runner with corresponding SurrealQL files that revert your changes. You can then trigger these manually or integrate them into a CLI command for database versioning.

### What happens if two application instances try to migrate simultaneously?

The system catches transaction conflicts during concurrent writes, logs them at the debug level, and re-raises the exception. This prevents race conditions from corrupting the `_sbl_migrations` version table. In production deployments, ensure only one instance runs migrations during startup, or implement distributed locking outside the migration system.

### Does the migration system work with existing SurrealDB databases?

Yes. When `AsyncMigrationManager` initializes, it checks the current version against available migrations using `needs_migration()`. If the database already contains schema objects but no `_sbl_migrations` table, you should manually create the version table and set the appropriate version number to match your existing schema state before enabling automatic migrations.