# How the Music Assistant Server Manages Database Migrations and Schema Versioning

> Discover how the Music Assistant server handles database migrations and schema versioning with its automatic, constant-driven system. Learn about data backups and sequential migration steps.

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

---

**The Music Assistant server stores its music library in SQLite and automatically manages schema evolution using a constant-driven versioning system that backs up existing data and applies sequential, idempotent migration steps on every startup.**

The `music-assistant/server` repository implements a robust database migration framework to handle schema changes across releases. By tracking a centralized version constant and executing targeted DDL statements through a dedicated migration module, the system ensures user libraries upgrade seamlessly without manual intervention or data loss.

## Schema Version Tracking and Storage

The foundation of the migration system rests on a single source of truth 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` (currently set to `41`) represents the expected schema level for the current codebase.

When the database initializes, the system records this version in a dedicated metadata store. Inside [`music_assistant/controllers/music/database.py`](https://github.com/music-assistant/server/blob/main/music_assistant/controllers/music/database.py), the `MusicDatabaseSetupMixin._setup_database` method inserts the version into the `settings` table using the key `"version"`:

```python
await self._database.insert_or_replace(
    DB_TABLE_SETTINGS,
    {"key": "version", "value": str(DB_SCHEMA_VERSION), "type": "str"},
)

```

This stored value acts as the checkpoint for determining whether a migration is required when the server restarts.

## Detecting Version Mismatches and Backup Safety

During startup, the `MusicDatabaseSetupMixin._setup_database` method reads the stored version from the database. If the retrieved value differs from `DB_SCHEMA_VERSION`, the system identifies an outdated schema and triggers the migration workflow.

Before executing any structural changes, the framework creates a backup of the existing database file. This safety mechanism ensures that if a migration is interrupted or fails, the user can restore their library from the backup copy. The migration only proceeds after this backup is secured.

## Sequential Migration Logic in migrations.py

The core migration engine lives in [`music_assistant/controllers/music/migrations.py`](https://github.com/music-assistant/server/blob/main/music_assistant/controllers/music/migrations.py). The `migrate_database` function accepts the previous version number, a database connection, a logger, and a callback to recreate the current schema tables. It executes a series of conditional blocks that bring the database forward one version at a time:

```python
await migrate_database(
    self.mass,
    self.database,
    self.logger,
    prev_version,
    self.__create_database_tables,
)

```

Each migration step follows a pattern of `if prev_version <= X:` blocks, where `X` represents the target version number. For example, when migrating from version 18 to 19, the code alters the `provider_mappings` table:

```python
if prev_version <= 18:
    # add in_library column to provider_mappings table

    await database.execute(
        f"ALTER TABLE {DB_TABLE_PROVIDER_MAPPINGS} ADD COLUMN in_library "
        "BOOLEAN NOT NULL DEFAULT 0;"
    )
    # set the flag for existing filesystem providers

    await database.execute(
        f"UPDATE {DB_TABLE_PROVIDER_MAPPINGS} SET in_library = 1 "
        "WHERE provider_domain in ('filesystem_local', 'filesystem_smb');"
    )

```

This sequential approach ensures that databases upgrading from any previous version execute every necessary intermediate step in the correct order.

### Idempotent Migration Steps

Every migration block includes defensive checks to prevent errors when re-running the script. Steps verify the existence of columns, indexes, or tables before attempting to modify them. This idempotency makes the migration safe to resume if the process is interrupted, as the system will skip steps already applied and continue from where it left off.

## Post-Migration Cleanup and Cache Invalidation

After all version-specific blocks complete, the `migrate_database` function commits the transaction and clears the in-memory cache. The cache invalidation step (`await mass.cache.clear()`) ensures that any schema-dependent cached data is discarded, forcing the next database access to read fresh metadata from the updated tables.

## Adding Future Schema Versions

Extending the schema requires two straightforward changes:

1. **Increment the constant**: Update `DB_SCHEMA_VERSION` in [`music_assistant/controllers/music/constants.py`](https://github.com/music-assistant/server/blob/main/music_assistant/controllers/music/constants.py) to the new version number (e.g., `42`).

2. **Add the migration step**: Append a new conditional block in [`music_assistant/controllers/music/migrations.py`](https://github.com/music-assistant/server/blob/main/music_assistant/controllers/music/migrations.py):

```python
if prev_version <= 42:
    # Example: add a new column `last_synced` to the playlists table

    await database.execute(
        f"ALTER TABLE {DB_TABLE_PLAYLISTS} ADD COLUMN last_synced INTEGER;"
    )

```

The framework automatically detects installations running the previous version and executes the new block during the next startup.

## Summary

- **Version Constants**: `DB_SCHEMA_VERSION` in [`constants.py`](https://github.com/music-assistant/server/blob/main/constants.py) defines the target schema level, stored in the `settings` table for comparison.
- **Automatic Detection**: The `MusicDatabaseSetupMixin._setup_database` method triggers migrations when stored and expected versions diverge.
- **Safety First**: The system creates database backups before applying any structural changes.
- **Sequential Execution**: [`migrations.py`](https://github.com/music-assistant/server/blob/main/migrations.py) processes steps using `if prev_version <= X:` blocks to ensure ordered upgrades from any legacy version.
- **Idempotent Design**: Each step checks for existing objects before modification, allowing safe resumption after interruptions.
- **Cache Clearing**: Post-migration cache invalidation ensures the application reads updated schema metadata immediately.

## Frequently Asked Questions

### What happens if a migration is interrupted?

The migration steps are designed to be **idempotent**, meaning each block checks for the existence of columns, tables, or indexes before attempting to create or modify them. If the process is interrupted, the server can safely restart and resume the migration from the beginning, skipping already-completed steps and continuing where it left off.

### How do I add a new database migration to the Music Assistant server?

First, 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). Then add a new conditional block in [`music_assistant/controllers/music/migrations.py`](https://github.com/music-assistant/server/blob/main/music_assistant/controllers/music/migrations.py) using the pattern `if prev_version <= NEW_VERSION:`. Inside this block, execute the necessary SQL statements to alter tables or migrate data. The framework automatically runs this code for any installation still on an older version.

### Why does the server use SQLite for the music library database?

SQLite provides a **serverless, file-based architecture** that eliminates the need for separate database server configuration while maintaining ACID compliance. This design simplifies backup procedures (the entire database is a single file) and allows the migration system to use raw SQL execution through the `DatabaseConnection` abstraction provided by [`music_assistant/helpers/database.py`](https://github.com/music-assistant/server/blob/main/music_assistant/helpers/database.py).

### Does the migration system support rollback to previous schema versions?

The framework does not implement automatic downgrades. Instead, it relies on the **pre-migration backup** created in `MusicDatabaseSetupMixin._setup_database`. If a critical failure occurs, administrators must manually restore the database from the backup file created before the migration began. The forward-only approach ensures data integrity as new features often depend on schema changes that cannot be safely reversed without data loss.