How Music Assistant Handles Database Schema Versioning and Migration

Music Assistant implements a linear, incremental migration system across three isolated SQLite databases, storing version metadata in settings tables and applying sequential SQL transformations to upgrade automatically from any historic schema version.

The music-assistant/server repository manages persistent data across three distinct SQLite databases. Understanding its database schema versioning and migration process reveals a robust, deterministic approach that ensures seamless upgrades without manual intervention.

The Three-Database Architecture

Music Assistant separates concerns into three database files, each with its own schema version constant:

• auth.db (Web authentication): Stores users, tokens, and join codes. Schema version constant defined in music_assistant/controllers/webserver/auth.py – DB_SCHEMA_VERSION = 5
• library.db (Core media library): Stores tracks, albums, artists, and playlists. Schema version constant defined in music_assistant/controllers/music.py – DB_SCHEMA_VERSION = 41
• cache.db (Cached data): Stores image thumbnails and temporary data. Schema version constant defined in music_assistant/controllers/cache/controller.py – DB_SCHEMA_VERSION = 8

Each database follows the identical version-check-migrate-store workflow independently.

The Version-Check-Migrate-Store Workflow

The migration sequence executes eight deterministic steps at controller initialization:

Step 1: Database Initialization

The DatabaseConnection.setup() method in music_assistant/helpers/database.py opens the SQLite file and configures performance PRAGMAs. Each controller then calls a private _create_*_tables() method to execute CREATE TABLE IF NOT EXISTS statements, ensuring the base schema exists before version checking begins.

Step 2: Reading the Stored Schema Version

The system queries the settings table to retrieve the current schema version. In auth.db, the key is schema_version; in library.db and cache.db, the key is version. If the key is absent, the version defaults to 0.

# From music_assistant/controllers/webserver/auth.py

if db_row := await self.database.get_row("settings", {"key": "schema_version"}):
    prev_version = int(db_row["value"])
else:
    prev_version = DB_SCHEMA_VERSION  # fresh install

Step 3: Executing Incremental Migrations

When prev_version < DB_SCHEMA_VERSION, the controller logs a warning and invokes its migration routine. The migration functions (_migrate_database in auth.py, __migrate_database in music.py, and _migrate_database in cache/controller.py) contain sequential version checks that apply minimal SQL transformations:

# Migration to version 3 in auth.py

if from_version < 3:
    with contextlib.suppress(OperationalError):
        await self.database.execute(
            "ALTER TABLE users ADD COLUMN player_filter json NOT NULL DEFAULT '[]'")
        await self.database.execute(
            "ALTER TABLE users ADD COLUMN provider_filter json NOT NULL DEFAULT '[]'")
    await self.database.commit()

This incremental approach allows jumping from any old version to the current one by executing version blocks in ascending order.

Step 4: Persisting the New Version

After successful migration, the code writes the updated version back to the settings table:

await self.database.insert_or_replace(
    "settings",
    {"key": "schema_version", "value": str(DB_SCHEMA_VERSION), "type": "int"},
)

Step 5: Index and Trigger Creation

Each controller creates indexes and triggers that depend on the finalized schema structure, ensuring optimal query performance after structural changes.

Step 6: Optional Maintenance

The library controller runs VACUUM if the reclaimable ratio exceeds VACUUM_MIN_RECLAIM_RATIO (0.2), optimizing storage after migrations that may have fragmented the database file.

Migration Implementation Details

Defensive Programming with OperationalError

The migration code suppresses OperationalError when adding columns that might already exist. This prevents failures during partial upgrades or interrupted migrations, making schema changes effectively idempotent:

with contextlib.suppress(OperationalError):
    await self.database.execute("ALTER TABLE ...")

Isolation per Database Concern

Separate databases keep migration logic independent. A failure in cache.db does not compromise user authentication in auth.db or the media library in library.db, following the principle of least impact.

Error Recovery and Fallback Mechanisms

If a migration crashes in the music controller, the system falls back to a fresh database, logs the error, and schedules a full rescan. This guarantees the server remains usable even after migration failures, though it may require re-scanning media files.

Adding a New Schema Migration

To add a new schema version (example: adding last_login to the users table):

1. Increment DB_SCHEMA_VERSION in the respective controller file.
2. Add a new version block in the migration function:

# In music_assistant/controllers/webserver/auth.py

DB_SCHEMA_VERSION = 6

async def _migrate_database(self, from_version: int) -> None:
    # existing migrations...

    if from_version < 6:
        await self.database.execute(
            "ALTER TABLE users ADD COLUMN last_login TEXT DEFAULT NULL"
        )
        await self.database.commit()

Key Files for Database Schema Management

File	Purpose
music_assistant/controllers/webserver/auth.py	Auth DB creation and migration logic (DB_SCHEMA_VERSION = 5)
music_assistant/controllers/music.py	Library DB migrations (DB_SCHEMA_VERSION = 41)
music_assistant/controllers/cache/controller.py	Cache DB handling (DB_SCHEMA_VERSION = 8)
music_assistant/helpers/database.py	Low-level async SQLite wrapper used by all migrations
music_assistant/constants.py	Central constants including DB_TABLE_SETTINGS

Summary

• Music Assistant uses three separate SQLite databases (auth.db, library.db, cache.db) with independent version constants to isolate concerns.
• Schema versions are stored in settings tables and compared against hardcoded constants (DB_SCHEMA_VERSION) at startup to determine if migration is required.
• Incremental migrations apply sequential SQL transformations using if from_version < X blocks, allowing upgrades from any historic version without manual intervention.
• Defensive coding suppresses OperationalError for idempotent operations, ensuring partially completed migrations can resume safely.
• Automatic recovery triggers full rescans if library migrations fail, ensuring the server remains operational even after schema errors.

Frequently Asked Questions

What happens if a migration is interrupted halfway through?

The system tolerates partial migrations because each SQL statement is wrapped in contextlib.suppress(OperationalError). When the server restarts, it re-reads the schema version from the settings table and continues from the last successful migration block, effectively making migrations idempotent and resumable.

How does Music Assistant handle a fresh installation versus an upgrade?

For fresh installs, the _create_*_tables() methods establish the current schema immediately. Since no version key exists in the settings table initially, the code defaults to DB_SCHEMA_VERSION, skipping all migration blocks and proceeding directly to index creation. During upgrades, the stored version triggers the appropriate migration chain.

Can I manually check the current schema version of my library database?

Yes, query the settings table directly using the database helper:

row = await music_controller._database.get_row(
    DB_TABLE_SETTINGS, {"key": "version"}
)
current_version = int(row["value"])
print(f"Library DB schema version = {current_version}")

This uses the DB_TABLE_SETTINGS constant defined in music_assistant/constants.py.

Why does Music Assistant use three separate databases instead of one?

The separation isolates concerns: authentication data (auth.db), core media metadata (library.db), and ephemeral cache data (cache.db). This isolation ensures that corruption or migration failures in the cache do not affect user credentials or the music library, and allows different maintenance schedules (such as vacuuming only the library DB when the reclaimable ratio exceeds 0.2).

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