# How Music Assistant Handles Database Schema Versioning and Migration

> Learn how Music Assistant manages database schema versioning and migration with an incremental system across isolated SQLite databases. Automatic SQL transformations ensure seamless upgrades from historic versions.

- Repository: [Music Assistant/server](https://github.com/music-assistant/server)
- Tags: internals
- Published: 2026-06-16

---

**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`](https://github.com/music-assistant/server/blob/main/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`](https://github.com/music-assistant/server/blob/main/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`](https://github.com/music-assistant/server/blob/main/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`](https://github.com/music-assistant/server/blob/main/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.

```python

# 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`](https://github.com/music-assistant/server/blob/main/auth.py), `__migrate_database` in [`music.py`](https://github.com/music-assistant/server/blob/main/music.py), and `_migrate_database` in [`cache/controller.py`](https://github.com/music-assistant/server/blob/main/cache/controller.py)) contain sequential version checks that apply minimal SQL transformations:

```python

# 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:

```python
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:

```python
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:

```python

# 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`](https://github.com/music-assistant/server/blob/main/music_assistant/controllers/webserver/auth.py) | Auth DB creation and migration logic (`DB_SCHEMA_VERSION = 5`) |
| [`music_assistant/controllers/music.py`](https://github.com/music-assistant/server/blob/main/music_assistant/controllers/music.py) | Library DB migrations (`DB_SCHEMA_VERSION = 41`) |
| [`music_assistant/controllers/cache/controller.py`](https://github.com/music-assistant/server/blob/main/music_assistant/controllers/cache/controller.py) | Cache DB handling (`DB_SCHEMA_VERSION = 8`) |
| [`music_assistant/helpers/database.py`](https://github.com/music-assistant/server/blob/main/music_assistant/helpers/database.py) | Low-level async SQLite wrapper used by all migrations |
| [`music_assistant/constants.py`](https://github.com/music-assistant/server/blob/main/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:

```python
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`](https://github.com/music-assistant/server/blob/main/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).