# Voicebox Database Migrations System: Automatic SQLite Schema Management for Desktop Apps > Automate SQLite schema management for desktop apps with Voicebox. Seamlessly upgrade schemas on startup without external tools. Simplify your database migrations. - Repository: [Jamie Pine/voicebox](https://github.com/jamiepine/voicebox) - Tags: internals - Published: 2026-04-14 --- **The Voicebox database migrations system automatically upgrades SQLite schemas on every application startup by inspecting existing tables, idempotently adding missing columns, and safely recreating tables when columns must be dropped, all without external tools like Alembic.** Voicebox persists data to a local SQLite file in the user-specific data directory and implements a lightweight, column-level migration pipeline that triggers before the FastAPI server begins accepting requests. Because the application ships as a bundled PyInstaller binary, it cannot rely on heavyweight migration frameworks, so developers created a self-contained system in [`backend/database/migrations.py`](https://github.com/jamiepine/voicebox/blob/main/backend/database/migrations.py) that detects schema drift and repairs it automatically. ## How the Migration System Initializes The migration process begins inside `init_db()` in [`backend/database/session.py`](https://github.com/jamiepine/voicebox/blob/main/backend/database/session.py), which creates the SQLAlchemy engine and immediately invokes the migration runner before declaring table objects. ```python # backend/database/session.py def init_db() -> None: """Initialize the database engine, run migrations, create tables, and seed data.""" global engine, SessionLocal, _db_path _db_path = config.get_db_path() _db_path.parent.mkdir(parents=True, exist_ok=True) engine = create_engine( f"sqlite:///{_db_path}", connect_args={"check_same_thread": False}, ) SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine) # Migration step --------------------------------------------------------- run_migrations(engine) # Inspects and repairs schema before table creation # ----------------------------------------------------------------------- Base.metadata.create_all(bind=engine) # Creates any missing tables ``` This guarantees that **every user receives the latest schema** regardless of which version they last ran, because the migration logic executes unconditionally on every startup. ## The Migration Entry Point and Table Inspection The `run_migrations()` function in [`backend/database/migrations.py`](https://github.com/jamiepine/voicebox/blob/main/backend/database/migrations.py) acts as a dispatcher. It uses SQLAlchemy’s inspector to enumerate existing tables, then delegates to specialized helpers for each domain entity. ```python # backend/database/migrations.py def run_migrations(engine) -> None: """Run all schema migrations. Safe to call on every startup.""" inspector = inspect(engine) tables = set(inspector.get_table_names()) _migrate_story_items(engine, inspector, tables) _migrate_profiles(engine, inspector, tables) _migrate_generations(engine, inspector, tables) _migrate_effect_presets(engine, inspector, tables) _migrate_generation_versions(engine, inspector, tables) _normalize_storage_paths(engine, tables) # Path portability fix ``` Each helper receives the engine, inspector, and set of table names so it can perform existence checks before attempting modifications. ## Idempotent Schema Changes: The Helper Pattern All migrations share a common safety pattern: check if a column exists, then add it only if absent. The `_add_column()` helper encapsulates this logic using raw SQL `ALTER TABLE` statements. ```python def _add_column(engine, table: str, column_sql: str, label: str) -> None: """Add a column if it doesn't already exist.""" with engine.connect() as conn: conn.execute(text(f"ALTER TABLE {table} ADD COLUMN {column_sql}")) conn.commit() logger.info("Added %s column to %s", label, table) ``` This pattern makes migrations **idempotent**—running them twice produces the same result as running them once, preventing errors during subsequent startups. ### Handling Complex Migrations: The story_items Example When a column must be removed (SQLite lacks native `DROP COLUMN` support), Voicebox migrates data to a new table schema. The `_migrate_story_items()` function demonstrates this by replacing the deprecated `position` column with `start_time_ms`. ```python def _migrate_story_items(engine, inspector, tables: set[str]) -> None: if "story_items" not in tables: return columns = _get_columns(inspector, "story_items") # Replace old `position` column with absolute `start_time_ms` if "position" in columns: logger.info("Migrating story_items: removing position column") with engine.connect() as conn: # 1. Add new column if missing if "start_time_ms" not in columns: conn.execute(text( "ALTER TABLE story_items ADD COLUMN start_time_ms INTEGER DEFAULT 0" )) # 2. Populate from existing rows using JOIN to get durations result = conn.execute(text(""" SELECT si.id, si.story_id, si.position, g.duration FROM story_items si JOIN generations g ON si.generation_id = g.id ORDER BY si.story_id, si.position """)) # ... iteration logic to calculate cumulative time ... conn.commit() # 3. Recreate table without the `position` column conn.execute(text("CREATE TABLE story_items_new ( ... )")) conn.execute(text("INSERT INTO story_items_new SELECT ... FROM story_items")) conn.execute(text("DROP TABLE story_items")) conn.execute(text("ALTER TABLE story_items_new RENAME TO story_items")) conn.commit() columns = _get_columns(inspector, "story_items") # Add new optional columns that may be missing if "track" not in columns: _add_column(engine, "story_items", "track INTEGER DEFAULT 0", "track") ``` This approach **preserves existing data** by calculating new values from joined tables before dropping the old column, then adds subsequent columns using the safe `_add_column()` helper. ### Normalizing Storage Paths Across Versions Early Voicebox versions stored absolute file paths for audio assets. The `_normalize_storage_paths()` helper in [`backend/database/migrations.py`](https://github.com/jamiepine/voicebox/blob/main/backend/database/migrations.py) rewrites these to relative paths during migration, ensuring portability when users move their data directory. ```python def _normalize_storage_paths(engine, tables: set[str]) -> None: """Normalize stored file paths to be relative to the configured data dir.""" from pathlib import Path from ..config import get_data_dir, to_storage_path, resolve_storage_path data_dir = get_data_dir() path_columns = [ ("generations", "audio_path"), ("generation_versions", "audio_path"), ("profile_samples", "audio_path"), ("profiles", "avatar_path"), ] # ... iterate rows and update absolute paths to relative ... ``` Located at lines 85-99 of [`migrations.py`](https://github.com/jamiepine/voicebox/blob/main/migrations.py), this function runs automatically alongside schema changes to maintain data integrity across different machines. ## Triggering Migrations on Application Startup The migration pipeline hooks into FastAPI’s lifecycle events in [`backend/app.py`](https://github.com/jamiepine/voicebox/blob/main/backend/app.py). When the server starts, it calls `database.init_db()`, which triggers the entire migration sequence before the first API request arrives. ```python # backend/app.py @application.on_event("startup") async def startup_event(): # ... database.init_db() # Triggers run_migrations() internally # ... ``` This architecture guarantees that **the database schema is always current** without requiring manual CLI commands or user intervention. ## Adding a New Migration to Voicebox When evolving the schema, follow this pattern to ensure compatibility with existing user databases: 1. Append a helper function to [`backend/database/migrations.py`](https://github.com/jamiepine/voicebox/blob/main/backend/database/migrations.py) 2. Register the helper in `run_migrations()` in dependency order 3. Check column existence with `_get_columns()` before adding 4. Use the table-recreation pattern only when dropping columns is unavoidable ```python def _migrate_new_feature(engine, inspector, tables: set[str]) -> None: if "new_feature" not in tables: # Create entire table if it never existed with engine.connect() as conn: conn.execute(text(""" CREATE TABLE new_feature ( id VARCHAR PRIMARY KEY, enabled BOOLEAN DEFAULT 0 ) """)) conn.commit() return columns = _get_columns(inspector, "new_feature") if "enabled" not in columns: _add_column(engine, "new_feature", "enabled BOOLEAN DEFAULT 0", "enabled") ``` Register the new migration in the dispatcher: ```python def run_migrations(engine): # ... existing calls ... _migrate_new_feature(engine, inspector, tables) ``` When users launch the updated application, the migration applies automatically. ## Summary - **Automatic execution**: `init_db()` in [`backend/database/session.py`](https://github.com/jamiepine/voicebox/blob/main/backend/database/session.py) calls `run_migrations()` on every startup, ensuring zero-downtime schema updates. - **Column-level safety**: The system inspects existing columns via `_get_columns()` and uses `_add_column()` for idempotent additions, preventing duplicate column errors. - **Table recreation**: For column removals, Voicebox migrates data to temporary tables and swaps them, working around SQLite’s `ALTER TABLE` limitations. - **Path normalization**: The `_normalize_storage_paths()` helper converts absolute paths to relative paths during migration, enabling data directory portability. - **Startup integration**: FastAPI’s startup event in [`backend/app.py`](https://github.com/jamiepine/voicebox/blob/main/backend/app.py) guarantees migrations complete before the API serves requests. ## Frequently Asked Questions ### How do I add a new column to an existing table in Voicebox? Create a helper function in [`backend/database/migrations.py`](https://github.com/jamiepine/voicebox/blob/main/backend/database/migrations.py) that checks `_get_columns()` for the column name, then calls `_add_column()` with the SQL type definition. Register the helper in `run_migrations()`. When the application restarts, it will detect the missing column and add it automatically without affecting existing data. ### Why doesn't Voicebox use Alembic for database migrations? Because Voicebox ships as a PyInstaller binary to end users, it cannot rely on external CLI tools or heavyweight dependencies. The custom migration system in [`backend/database/migrations.py`](https://github.com/jamiepine/voicebox/blob/main/backend/database/migrations.py) provides a lightweight, self-contained alternative that inspects the SQLite schema directly and performs safe, idempotent modifications using raw SQL executed through SQLAlchemy. ### What happens if a migration fails during startup? If a migration raises an exception, FastAPI’s startup event fails and the application will not begin serving requests. This prevents the API from running against an incompatible schema. The error logs in [`backend/database/migrations.py`](https://github.com/jamiepine/voicebox/blob/main/backend/database/migrations.py) indicate which specific table or column operation failed, allowing developers to inspect the database state or adjust the migration logic. ### How are file paths handled during database migrations? The `_normalize_storage_paths()` helper automatically runs during every migration cycle to convert absolute file paths to relative paths stored in the database. This ensures that if a user moves their Voicebox data directory to a new location or different machine, the application can still locate audio files and avatars by resolving them against the configured data directory.