Database migrations

[semohr]

I've been looking into the current state of the Beets SQL(ite) database, and it’s clear that the schema could benefit from some refactoring and normalization. There are inconsistencies in the structure, and we’re missing indexes on columns that are frequently used in joins, both of which impact performance and maintainability.

To address this, I’d like to propose the introduction of a migration system.

A migration system would allow us to:

• Make incremental improvements to the database schema without breaking existing installations.
• Normalize and clean up the current schema.
• Add indexes or modify structures in a way that's safe and reversible.
• Better document and track changes to the database over time.

That said, I’m not yet certain what our specific requirements would be, or even if we all agree that such a system is necessary. This discussion is intended to explore whether others see value in this approach, and if so, what features or constraints we should consider when designing such a system.

At the very least, I believe we are currently lacking a documented and reliable method for applying schema changes across different versions without risking data integrity.

Would love to hear thoughts, concerns, or alternatives.

[semohr]

I have the following proposal for the migration system.

Migration system proposal (forward only, relative simple):

We could introduce a migration mechanism by adding a dedicated table (e.g., schema_version) to track the current database schema version. Upon startup, the system checks the recorded version against the latest known schema version and, if needed, sequentially applies migration steps to bring the database up to date.

Key aspects:

• Migrations are forward-only for simplicity. Rollbacks are not initially supported, under the assumption that downgrades are rare or unnecessary in practice.
• Before applying any migration, the system automatically creates a database backup, ensuring recovery in case something goes wrong.
• Each migration script or function is associated with a specific version and is applied in order.

This mechanism would give us a safer and more structured way to:

• Apply schema changes.
• Normalize and optimize the database incrementally.
• Introduce indexes and constraints as needed.
• Avoid breaking existing user databases without a clear path for updates.

Shortcomings & Considerations

1. No Rollback Support:
   • Forward-only migrations are simpler but limit recovery options if a migration is faulty.
   • Supporting bidirectional (reversible) migrations would require extra effort (backups should be good enough)
2. Migration Complexity Over Time:
   • As the number of migrations grows, the system must ensure that all steps remain compatible and deterministic across environments.
   • Ordering and dependency management could become complex.
3. Backup Reliability:
   • Relying on backups before each migration assumes the backup/restore mechanism is robust, fast, and cross-platform (e.g., for SQLite).
   • We should define where backups are stored and how users can restore them manually if needed.

[rbelew]

I've posted some code that is working for me, migrating an early database to be consistent with a newer one, here:
https://github.com/rbelew/rikHak/blob/master/updateBeetDB.py

[rbelew]

One thing that comes to mind already is this bit of your spec:

Each migration script or function is associated with a specific version and is applied in order.

I don't find any version numbering over the database specs?

[semohr]

Creating the migration system itself is not really a big issue in my opinion. It is more a thing about aligning our expectations and requirements. From an dx perspective it should be relative easy to use and adaptive to our needs.

Regarding your spec comment, I would store migration versioning in a new database table meta. Having created similar (and more complex) systems in the past, I found adding these informations to the database itself the easiest approach. Although I have also seen other file based approaches which can definitely work.

The migration mechanism would check this meta table for the current version and applies all missing/new migrations in order. Migrations don't even have to be python scripts but can be simple sql files for the time being.

Ofc, we would need a versioning schema for these migration files. Versioning could be anything from simple numeric series or semver to a full dependency graph. As I expect us not having too many of these migrations and especially not working on many in parallel, having a simple numeric schema should be good enough.