# Music Assistant Music Controller Database Migrations: Schema Evolution and Version Management

> Learn how Music Assistant handles database migrations automatically on startup. Discover schema evolution and version management for your music library.

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

---

**Music Assistant automatically migrates its SQLite music library database on startup using a versioned system defined in [`migrations.py`](https://github.com/music-assistant/server/blob/main/migrations.py), incrementally upgrading from the stored schema version to the current `DB_SCHEMA_VERSION` (43) through idempotent ALTER TABLE operations and data transformations.**

The Music Assistant server (`music-assistant/server`) maintains your media library in a local SQLite database that evolves with each release. Understanding how **music controller database migrations** work is essential for developers contributing schema changes or administrators troubleshooting upgrade failures, as the system handles upgrades automatically without requiring manual SQL execution.

## How Database Migrations Are Triggered

### Version Tracking and Constants

The migration system centers on a single integer constant defined in [`music_assistant/controllers/music/constants.py`](https://github.com/music-assistant/server/blob/main/music_assistant/controllers/music/constants.py). The constant **`DB_SCHEMA_VERSION`** represents the current schema revision and is set to `43` in the latest codebase. This version is stored persistently in the `settings` table of the SQLite database, allowing the application to detect when an upgrade is required.

### The Entry Point

When the Music Assistant core starts, the **`MusicDatabaseSetupMixin._setup_database`** method in [`music_assistant/controllers/music/database.py`](https://github.com/music-assistant/server/blob/main/music_assistant/controllers/music/database.py) orchestrates the initialization process. It reads the stored version from the database and compares it against `DB_SCHEMA_VERSION`. If the stored version differs from the current constant, the mixin invokes the **`migrate_database`** coroutine from [`music_assistant/controllers/music/migrations.py`](https://github.com/music-assistant/server/blob/main/music_assistant/controllers/music/migrations.py), passing the previous version number and a callback to recreate tables if necessary.

```python

# From MusicDatabaseSetupMixin._setup_database()

await self.__create_database_tables()
if prev_version not in (0, DB_SCHEMA_VERSION):
    await migrate_database(
        self.mass,
        self.database,
        self.logger,
        prev_version,
        self.__create_database_tables,
    )
await self._database.insert_or_replace(
    DB_TABLE_SETTINGS,
    {"key": "version", "value": str(DB_SCHEMA_VERSION), "type": "str"},
)

```

## Migration Workflow and Schema Changes

The **`migrate_database`** coroutine in [`music_assistant/controllers/music/migrations.py`](https://github.com/music-assistant/server/blob/main/music_assistant/controllers/music/migrations.py) executes a series of conditional blocks that follow the pattern `if prev_version <= X:`, allowing the system to apply changes incrementally from any prior version.

### Key Schema Evolution Steps

The migration logic handles complex data transformations across 43 iterations, including:

- **Version 15** – Adds `search_name` and `search_sort_name` columns to all primary media tables (tracks, albums, artists, radios, playlists, audiobooks, podcasts) and populates them using `create_safe_string` for case- and diacritic-insensitive searching.
- **Version 28** – Executes the **genre migration**, creating the `genre` and `genre_alias` tables, seeding default genres, extracting unique genre names from every media table, and populating the many-to-many `genre_media_item_mapping` table using a CTE-based bulk insert.
- **Version 32** – Recreates `genre_media_item_mapping` with nullable `alias` and a new `is_derived` flag.
- **Version 36** – Permanently drops the legacy `smart_fades_analysis` table.
- **Version 38** – Migrates loudness data into the unified `audio_analysis` table and re-applies the smart-fades table drop for users upgrading from stable 2.8.9 to 2.9.0.
- **Version 42** – Adds `translation_key` and `translation_params` columns to the `playlists` table to preserve localized playlist names across sync cycles.

After processing all applicable migration blocks, the function commits the transaction and clears the global cache using `await mass.cache.clear()` to prevent stale lookups against the old schema.

### Idempotent ALTER TABLE Operations

Each migration step is designed to be **idempotent**. The code catches SQLite exceptions such as "duplicate column" or "no such column" errors, allowing the migration to be safely re-run if interrupted. For example, adding a new column follows this defensive pattern:

```python
if prev_version <= 44:
    await database.execute(
        f"ALTER TABLE {DB_TABLE_TRACKS} ADD COLUMN sample_rate INTEGER NOT NULL DEFAULT 44100"
    )

```

## Table Creation and Index Management

### Core Table Definitions

The **`__create_database_tables`** method in [`database.py`](https://github.com/music-assistant/server/blob/main/database.py) defines every table schema, including columns introduced by recent migrations. This method is invoked both for fresh installations and during migrations that require table recreation (such as version 21, which drops the `smart_fades_analysis` table and rebuilds core tables).

### Indexes and Triggers

Two additional private methods maintain database performance and integrity:

- **`__create_database_indexes`** – Builds indices on frequently queried columns including `favorite`, `name`, `search_name`, `search_sort_name`, `external_ids`, timestamps, and play counts, as well as foreign-key tables like `provider_mappings`.
- **`__create_database_triggers`** – Installs SQLite triggers that automatically update the `timestamp_modified` column on UPDATE operations for core media tables. These are recreated after migrations that may have removed them (such as version 17).

## Robustness and Error Handling

The migration system implements multiple safeguards to prevent data loss:

- **Automatic Backup** – Before any migration begins, the existing database file is copied to `library.db.backup`. If any step throws an exception, the system logs the error, deletes the corrupted database, restores from the backup, and falls back to a fresh database with a full library rescan.
- **Cache Invalidation** – Upon successful completion, the global cache is cleared to ensure the application uses the migrated schema immediately.
- **Transaction Safety** – All schema changes are committed atomically after the migration logic completes, preventing partial schema updates.

## Extending the Migration System

To add a new schema change to the Music Assistant codebase:

1. Increment **`DB_SCHEMA_VERSION`** in [`music_assistant/controllers/music/constants.py`](https://github.com/music-assistant/server/blob/main/music_assistant/controllers/music/constants.py).
2. Add a new conditional block `if prev_version <= <new_version>:` in the `migrate_database` coroutine in [`migrations.py`](https://github.com/music-assistant/server/blob/main/migrations.py).
3. Perform the required `ALTER TABLE`, `CREATE TABLE`, or data-migration steps using the `database.execute()` method.
4. Update **`__create_database_tables`** in [`database.py`](https://github.com/music-assistant/server/blob/main/database.py) if the new columns need to be present on fresh installs.

Because the migration logic is isolated in [`migrations.py`](https://github.com/music-assistant/server/blob/main/migrations.py), developers can safely test new steps by manually setting `prev_version` to a lower number in a development database.

### Debugging and Manual Execution

For testing or debugging purposes, you can invoke the migration manually:

```python
from music_assistant import MusicAssistant
from music_assistant.helpers.database import DatabaseConnection
import logging

async def run_migration():
    mass = MusicAssistant()
    db = DatabaseConnection("/tmp/library.db")
    await db.setup()
    logger = logging.getLogger("migration")
    await migrate_database(
        mass,
        db,
        logger,
        prev_version=30,               # Start from older version

        create_tables=lambda: None,    # Stub for table recreation

    )

```

To inspect the current schema version programmatically:

```python
async def get_schema_version():
    db = DatabaseConnection("/tmp/library.db")
    await db.setup()
    row = await db.get_row("settings", {"key": "version"})
    print("Current DB schema version:", row["value"] if row else "unknown")

```

## Summary

- **Music Assistant** uses an incremental migration system in [`music_assistant/controllers/music/migrations.py`](https://github.com/music-assistant/server/blob/main/music_assistant/controllers/music/migrations.py) that automatically upgrades the SQLite database from the stored version to `DB_SCHEMA_VERSION` (43) on startup.
- The **`MusicDatabaseSetupMixin._setup_database`** method in [`database.py`](https://github.com/music-assistant/server/blob/main/database.py) detects version mismatches and triggers the migration process, while `__create_database_tables` handles fresh installations.
- **Idempotent** conditional blocks ensure migrations can be re-run safely, with specific steps handling complex transformations like the version 28 genre migration and version 38 loudness data consolidation.
- **Robustness features** include automatic backup to `library.db.backup`, transaction-based commits, and automatic cache invalidation via `mass.cache.clear()`.

## Frequently Asked Questions

### What happens if a database migration fails during startup?

If any migration step throws an exception, Music Assistant logs the error, deletes the corrupted database, restores the backup from `library.db.backup`, and initiates a full library rescan. This ensures the application remains usable even if a migration encounters an unexpected edge case.

### How can I check my current database schema version?

Query the `settings` table for the key `"version"` using the database helper. The version is stored as a string representation of the integer schema version (e.g., `"43"`). You can inspect this via the Python API or by executing `SELECT value FROM settings WHERE key='version'` directly on the SQLite file.

### Can I run migrations manually without starting the full server?

Yes, you can import the `migrate_database` coroutine from [`music_assistant/controllers/music/migrations.py`](https://github.com/music-assistant/server/blob/main/music_assistant/controllers/music/migrations.py) and invoke it with a `DatabaseConnection`, a `MusicAssistant` instance, and the target `prev_version`. This is useful for development and testing new schema changes.

### Where is the database backup stored before migration?

Before attempting any migration, Music Assistant copies the existing `library.db` file to `library.db.backup` in the same directory. This backup is used for automatic rollback if the migration fails, but developers can also manually restore it to revert to a pre-migration state.