Setting Up SurrealDB with Automatic Schema Migrations in Open Notebook

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

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.

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:

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, the application creates an AsyncMigrationManager and awaits run_migration_up() during startup.

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:

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:

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.

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