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

Music Assistant automatically migrates its SQLite music library database on startup using a versioned system defined in 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. 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 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, passing the previous version number and a callback to recreate tables if necessary.

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

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 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.
2. Add a new conditional block if prev_version <= <new_version>: in the migrate_database coroutine in 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 if the new columns need to be present on fresh installs.

Because the migration logic is isolated in 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:

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:

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

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