[Enhancement]: Database migrations for new API versions

[nichwall]

One of the things that was discussed in Discord the other day was figuring out how to handle changes to the database and versioning the API to make changes easier. This discussion is about how to manage that and is not about what the new data model should be.

Problem

Some changes to the structure will break backwards compatibility with previous server versions (for example my People table suggestion #2561), which will cause problems if someone tries to roll back for any reason.

Current Behavior

Currently, the ABS database is stored at /config/absdatabase.sqlite. There is a field in the JSON column of server settings which shows which server version the database corresponds to, which is useful for forward patches (updating the database), but does not work for backwards migration because the old server version might not know how to read the new database type. The existing method works for server versions 2.3.0 through at least 2.7.2 because there have only been new columns added to existing tables so old server versions just ignore those columns.

Proposed Solution

To solve the table structure or fields within columns changing, I propose changing the naming scheme of the database file.

• For any server version of 2.2.23 and below, just keep doing what it's doing
• for any server version between 2.3.0 and 2.7.2 (and more if it takes a few versions to implement this), use the absdatabase.sqlite file as is
• for any new server, make a copy of the newest old database file and name the copy absdatabase_SERVERVERSION.sqlite, then perform the migration to the current version. This is similar to how the initial SQLite migration worked, making a new database but keeping the old one around. For example, if this change was implemented in 2.9.0, make a copy of the most recent server file and name the copy absdatabase_2.9.0.sqlite and perform the migration in this new file.

Issues

This will require an extra step for the user when changing server versions, where if rolling back the user needs to make sure to rename or delete the "bad migration" file before upgrading again, and periodically clean up old database files from previous server versions.

If anyone can think of other issues with this method, that would be great.

[Weldawadyathink]

I really like this proposal. I think its good to have the version of the database viewable from the filename, so that users who are not familiar with sqlite (which is probably most users) have the information they need.

I have an idea that could address your issue. We could have the server detect all database files /config/ that are for future versions. From there, we could automatically move it to a /config/failed_migrations folder. Or notify the user that a failed migration may have occurred, and allow them to delete the newer database file from the UI.

[nichwall]

Thanks for taking the time to read through.

I like the idea of having a place to automatically put "failed migrations" after rolling back. That wouldn't work for existing installs (2.3.0 through 2.7.2) since they are already released and being ran on various servers, but could be introduced going forward. That could be handled in the documentation (would need to update the Backups and Migrations page).

With that in mind, there could also be a folder for "old databases", but that will have the same problem as existing installs.