Add a system to perform database migrations #1343

jpdillingham · 2025-05-03T21:09:55Z

A couple of unfortunate decisions have led me to need to change how the Transfers database is laid out, specifically due to #1280 (database/C# enums), which is symptomatic in #1291 (OOM issue with Transfers).

I have been putting this off, hoping that it wouldn't be needed, but the time has come.

This will be a "major" version update and might cause significant headaches for folks on low-spec hardware and a lot of historical transfer data.

Testing

Successfully migrates an existing database (with ~250k records)

[17:38:16 WRN] 1 outstanding database migration(s) to apply. This operation must be completed before the application can start.
[17:38:16 WRN] --> The ID for this migration is 052525_103816. Use it to locate pre-migration database backups, should manual cleanup be needed. <--
[17:38:16 INF] Backing up existing databases...
[17:38:16 INF] Backed database C:\Users\JP\AppData\Local\slskd\data\search.db up to C:\Users\JP\AppData\Local\slskd\data\backups\search.pre-migration-backup.052525_103816.db
[17:38:16 INF] Backed database C:\Users\JP\AppData\Local\slskd\data\transfers.db up to C:\Users\JP\AppData\Local\slskd\data\backups\transfers.pre-migration-backup.052525_103816.db
[17:38:16 INF] Backed database C:\Users\JP\AppData\Local\slskd\data\messaging.db up to C:\Users\JP\AppData\Local\slskd\data\backups\messaging.pre-migration-backup.052525_103816.db
[17:38:16 INF] Backed database C:\Users\JP\AppData\Local\slskd\data\events.db up to C:\Users\JP\AppData\Local\slskd\data\backups\events.pre-migration-backup.052525_103816.db
[17:38:16 INF] Databases backed up successfully
[17:38:16 WRN] Beginning migration(s)
[17:38:16 WRN] This may take some time. Avoid stopping the application before the process is complete. If the process is interrupted it may be necessary to manually revert to backups.
[17:38:16 WRN] Applying migration Z04012025_TransferStateMigration (1 of 1)
[17:38:16 INF] > Schema changes applied, copying data...
[17:38:19 INF] > Data copied, adjusting columns...
[17:38:19 INF] > Updating column 1 of 1. This may take some time...
[17:38:20 INF] > Done!
[17:38:20 INF] Migration Z04012025_TransferStateMigration was applied successfully (elapsed: 4346ms)
[17:38:20 INF] 1 migration(s) applied successfully (elapsed: 4347ms)
[17:38:20 INF] Migration(s) complete!

Backs up (and leaves) databases

Migrated database has the right data layout (State is now a number, StateDescription contains the text)

Newly downloaded/uploaded records have the right layout
App creates the correct file if one doesn't exist at startup (starting from scratch)

Canary migration

Updated my canary deployment and it went fine. Logs:

[slskd.Program] [20:43:02 DBG] Running Migrate()...                                                                                              [slskd.Migrator] [20:43:02 DBG] Migration Z04012025_TransferStateMigration needs to be applied                                                   [slskd.Migrator] [20:43:02 DBG] Migrations: { migrationsToApply = System.Collections.Generic.List`1[System.String], migrationsToSkip = System.Co>[slskd.Migrator] [20:43:02 WRN] 1 outstanding database migration(s) to apply. This operation must be completed before the application can start. [slskd.Migrator] [20:43:02 WRN] --> The ID for this migration is 052625_084302. Use it to locate pre-migration database backups, should manual c>[slskd.Migrator] [20:43:02 INF] Backing up existing databases...                                                                                 [slskd.Migrator] [20:43:03 INF] Backed database /app/data/search.db up to /app/data/backups/search.pre-migration-backup.052625_084302.db         [slskd.Migrator] [20:43:03 INF] Backed database /app/data/transfers.db up to /app/data/backups/transfers.pre-migration-backup.052625_084302.db   [slskd.Migrator] [20:43:03 INF] Backed database /app/data/messaging.db up to /app/data/backups/messaging.pre-migration-backup.052625_084302.db   [slskd.Migrator] [20:43:03 INF] Backed database /app/data/events.db up to /app/data/backups/events.pre-migration-backup.052625_084302.db         [slskd.Migrator] [20:43:03 INF] Databases backed up successfully                                                                                 [slskd.Migrator] [20:43:03 WRN] Beginning migration(s)                                                                                           [slskd.Migrator] [20:43:03 WRN] This may take some time. Avoid stopping the application before the process is complete. If the process is interr>[slskd.Migrator] [20:43:03 WRN] Applying migration Z04012025_TransferStateMigration (1 of 1)                                                     [slskd.Migrations.Z04012025_TransferStateMigration] [20:43:03 DBG] Fetching states and mapping to integers...                                    [slskd.Migrations.Z04012025_TransferStateMigration] [20:43:03 DBG] State -> int map: {"Completed, Succeeded":48,"Completed, Errored":272,"Comple>[slskd.Migrations.Z04012025_TransferStateMigration] [20:43:03 INF] > Schema changes applied, copying data...                                     [slskd.Migrations.Z04012025_TransferStateMigration] [20:43:05 INF] > Data copied, adjusting columns...                                           [slskd.Migrations.Z04012025_TransferStateMigration] [20:43:06 INF] > Updating column 1 of 4. This may take some time...                          [slskd.Migrations.Z04012025_TransferStateMigration] [20:43:06 DBG] Setting Completed, Succeeded to 48                                            [slskd.Migrations.Z04012025_TransferStateMigration] [20:43:06 DBG] Completed, Succeeded to 48 in 0                                               [slskd.Migrations.Z04012025_TransferStateMigration] [20:43:06 INF] > Updating column 2 of 4. This may take some time...                          [slskd.Migrations.Z04012025_TransferStateMigration] [20:43:06 DBG] Setting Completed, Errored to 272                                             [slskd.Migrations.Z04012025_TransferStateMigration] [20:43:07 DBG] Completed, Errored to 272 in 0                                                [slskd.Migrations.Z04012025_TransferStateMigration] [20:43:07 INF] > Updating column 3 of 4. This may take some time...                          [slskd.Migrations.Z04012025_TransferStateMigration] [20:43:07 DBG] Setting Completed, Cancelled to 80                                            [slskd.Migrations.Z04012025_TransferStateMigration] [20:43:07 DBG] Completed, Cancelled to 80 in 0                                               [slskd.Migrations.Z04012025_TransferStateMigration] [20:43:07 INF] > Updating column 4 of 4. This may take some time...                          [slskd.Migrations.Z04012025_TransferStateMigration] [20:43:07 DBG] Setting Queued, Remotely to 4098                                              [slskd.Migrations.Z04012025_TransferStateMigration] [20:43:07 DBG] Queued, Remotely to 4098 in 0                                                 [slskd.Migrations.Z04012025_TransferStateMigration] [20:43:07 DBG] Committing transation...                                                      [slskd.Migrations.Z04012025_TransferStateMigration] [20:43:07 DBG] Transaction committed!                                                        [slskd.Migrations.Z04012025_TransferStateMigration] [20:43:07 INF] > Done!                                                                       [slskd.Migrator] [20:43:07 INF] Migration Z04012025_TransferStateMigration was applied successfully (elapsed: 4701ms)
[slskd.Migrator] [20:43:07 INF] 1 migration(s) applied successfully (elapsed: 4702ms)
[slskd.Migrator] [20:43:07 INF] Migration(s) complete!

Result of migration:

Seems ready to go!?

wburklund · 2025-05-11T15:55:23Z

docs/README.md

+### [Build](https://github.com/slskd/slskd/blob/master/docs/build.md)
+
+## Troubleshooting
+
+### [Migrations](https://github.com/slskd/slskd/blob/master/docs/migrations.md)


praise: I like that detailed documentation for migrations has been written

wburklund · 2025-05-11T15:57:11Z

src/slskd/Core/Data/Migrations/Z04012025_TransferStateMigration.cs

+    private Dictionary<string, int> GetStateMapping(SqliteConnection connection)
+    {
+        Log.Debug("Fetching states and mapping to integers...");
+
+        using var command = new SqliteCommand("SELECT DISTINCT State FROM Transfers;", connection);
+        using var reader = command.ExecuteReader();
+
+        var dict = new Dictionary<string, int>();
+
+        while (reader.Read())
+        {
+            var state = reader.GetString(0);
+            dict[state] = (int)Enum.Parse(typeof(TransferStates), state);
+        }
+
+        Log.Debug("State -> int map: {Map}", dict);
+        return dict;
+    }


note: If enum values for transfer states are ever changed, this migration will be affected. I like to make migrations independent of the current codebase whenever possible.

great point. i was cringing at needing to generate a list of all possible enum combinations but it should actually be pretty straightforward since i can just increment an integer and loop

there are 8191 possible combinations, and only a couple of dozen make sense. i'm afraid i'll miss one and break somebody, so what i've done instead is copy/paste the enum into the migration so it will remain stable.

appreciate you pointing this out!

wburklund · 2025-05-11T16:00:14Z

src/slskd/Core/Data/Migrations/Z04012025_TransferStateMigration.cs

+            var rename = @"
+                ALTER TABLE Transfers RENAME TO Transfers_old;
+            ";
+
+            using var renameCommand = new SqliteCommand(rename, connection, transaction);
+            renameCommand.ExecuteNonQuery();
+
+            /*
+                create the new table with StateDescription appearing after State
+            */
+            var create = @"
+                CREATE TABLE Transfers (
+                    Id TEXT NOT NULL,
+                    Username TEXT,
+                    Direction TEXT NOT NULL,
+                    Filename TEXT,
+                    Size INTEGER NOT NULL,
+                    StartOffset INTEGER NOT NULL,
+                    State INTEGER NOT NULL,
+                    StateDescription TEXT, -- new column
+                    RequestedAt TEXT NOT NULL,
+                    EnqueuedAt TEXT,
+                    StartedAt TEXT,
+                    EndedAt TEXT,
+                    BytesTransferred INTEGER NOT NULL,
+                    AverageSpeed REAL NOT NULL,
+                    PlaceInQueue INTEGER,
+                    Exception TEXT,
+                    Removed INTEGER NOT NULL,
+                    CONSTRAINT PK_Transfers PRIMARY KEY(Id)
+                );
+            ";
+
+            using var createCommand = new SqliteCommand(create, connection, transaction);
+            createCommand.ExecuteNonQuery();
+
+            Log.Information("Schema changes applied, copying data...");
+
+            /*
+                copy the existing data into the new table
+            */
+            var copy = @"
+                INSERT INTO Transfers (
+                    Id, Username, Direction, Filename, Size, StartOffset, State, 
+                    RequestedAt, EnqueuedAt, StartedAt, EndedAt, BytesTransferred, 
+                    AverageSpeed, PlaceInQueue, Exception, Removed
+                )
+                SELECT 
+                    Id, Username, Direction, Filename, Size, StartOffset, State, 
+                    RequestedAt, EnqueuedAt, StartedAt, EndedAt, BytesTransferred, 
+                    AverageSpeed, PlaceInQueue, Exception, Removed
+                FROM Transfers_old;";
+
+            using var copyCommand = new SqliteCommand(copy, connection, transaction);
+            copyCommand.ExecuteNonQuery();
+
+            Log.Information("Data copied, adjusting columns...");
+
+            /*
+                we no longer need the old table, so drop it
+            */
+            using var dropCommand = new SqliteCommand("DROP TABLE Transfers_old;", connection, transaction);
+            dropCommand.ExecuteNonQuery();


suggestion: Add a column to Transfers instead of replacing the whole table.

note: SQLite's support of transactional DDL limits the corner cases that could crop up

the new table is so that the State and StateDescription columns will be next to each other. i'm thinking maybe it's not worth the extra effort (and file i/o)

wburklund · 2025-05-11T16:02:52Z

src/slskd/Core/Data/Migrator.cs

+        /*
+            load migration history from the history file in the root of the data directory. this file contains a key/value
+            pair for each migration that's been applied, along with the migration date.
+            if force=true, we're going to disregard history and overwrite it.
+        */


suggestion: Store migration history in a database table like most migration systems (EF, Alembic, Liquibase, ...)

i think i will; originally i didn't because Entity Framework would make it a hassle, but i don't have to use EF for this

Since this topic came up, would it save you a lot of (ongoing) effort to use something like Flyway, or the above mentioned? I've used Flyway in a commercial setting and it worked fine, though it was heavy. I've heard https://github.com/golang-migrate/migrate is much better but I can't say I've tried it myself.

These sorts of tools are nice because you can usually stay away from your codebase and keep the separation of concerns very clean.

it's possible eventually. i've actually removed the history file entirely just to simplify this PR a bit; the 1 existing migration can easily tell if it has been applied already so it's not a big deal.

…stable

This PR contains the following updates: | Package | Update | Change | |---|---|---| | [slskd/slskd](https://slskd.org) ([source](https://github.com/slskd/slskd)) | minor | `0.22.5` -> `0.23.1` | --- ### Release Notes <details> <summary>slskd/slskd (slskd/slskd)</summary> ### [`v0.23.1`](https://github.com/slskd/slskd/releases/tag/0.23.1) [Compare Source](slskd/slskd@0.23.0...0.23.1) #### What's Changed - Fix regression in scripts and webhooks causing json to be missing detail by [@jpdillingham](https://github.com/jpdillingham) in slskd/slskd#1389 **Full Changelog**: slskd/slskd@0.23.0...0.23.1 ### [`v0.23.0`](https://github.com/slskd/slskd/releases/tag/0.23.0) [Compare Source](slskd/slskd@0.22.5...0.23.0) ### 🎉 Database Migrations (that preserve data!) This release introduces a new system that performs database migrations on existing data ([#1343](slskd/slskd#1343)), which was necessary to migrate the Transfers database to correct an out of memory issue at startup for users with a large number of transfer records (see [#1291](slskd/slskd#1291)). Upon first start after upgrading to 0.23.0 the application will apply the initial migration to the Transfers database (`transfers.db`), if there is one. Depending on the system and the amount of data present, this may take a while, and the UI will be inaccessible during the process. Backups of existing databases are taken prior to the start of the process (you can find them in `/data/backups`) and they aren't deleted automatically, so **your historical data is safe**. A feature to add auto deletion after a period of time will come later. In the unlikely event that the process fails to complete successfully, follow the directions in the new [migration docs](https://github.com/slskd/slskd/blob/master/docs/migrations.md), which explain what to do if something goes wrong, and how to get the application back to a working state if the migration can't be completed. If you find that your database can't be migrated successfully, please file an issue and I can work on providing a standalone migration tool. #### What's Changed - Add a system to perform database migrations by [@jpdillingham](https://github.com/jpdillingham) in slskd/slskd#1343 - Fix remaining transfter State queries using HasFlag() by [@jpdillingham](https://github.com/jpdillingham) in slskd/slskd#1356 - Extend health check startup to 60 minutes, set $SHELL for docker container, and bump the GC memory limit from 500MiB to 2GiB by [@jpdillingham](https://github.com/jpdillingham) in slskd/slskd#1359 - Bump Soulseek.NET to 7.1.0 by [@jpdillingham](https://github.com/jpdillingham) in slskd/slskd#1363 - Update documentation for scripts by [@jpdillingham](https://github.com/jpdillingham) in slskd/slskd#1384 - Bump http-proxy-middleware from 2.0.6 to 2.0.9 in /src/web by [@dependabot](https://github.com/dependabot) in slskd/slskd#1329 **Full Changelog**: slskd/slskd@0.22.5...0.23.0 </details> --- ### Configuration 📅 **Schedule**: Branch creation - At any time (no schedule defined), Automerge - At any time (no schedule defined). 🚦 **Automerge**: Disabled by config. Please merge this manually once you are satisfied. ♻ **Rebasing**: Whenever PR is behind base branch, or you tick the rebase/retry checkbox. 🔕 **Ignore**: Close this PR and you won't be reminded about this update again. --- - [ ] If you want to rebase/retry this PR, check this box --- This PR has been generated by [Renovate Bot](https://github.com/renovatebot/renovate).  Reviewed-on: https://gitea.alexlebens.dev/alexlebens/infrastructure/pulls/860 Co-authored-by: Renovate Bot <renovate-bot@alexlebens.net> Co-committed-by: Renovate Bot <renovate-bot@alexlebens.net>

jpdillingham added 18 commits April 1, 2025 19:14

wip

e08b711

migrate if migrator exists

47ea184

add option to force

3004a42

run migrations when non-volatile

571617d

finish migrator implementation

5e99dd5

update signature, throw on missing database

bc50265

document force migrations flag

e22ee31

stop msbuild from complaining about analyzers being newer

8f4dea8

wip

534d882

more wip

6b1e7db

formatting. make state updates work

a219038

more wip

8369342

make a dedicated backup directory

e4d5e2c

split schema inspection, tweak paths

69bceab

document migrations

8d348b0

delete seed migration, tweak reference to schema inspector

d43c1ab

adjust logs

207a645

Merge branch 'master' into migrations

a87b58a

jpdillingham marked this pull request as ready for review May 3, 2025 22:53

jpdillingham added 5 commits May 3, 2025 18:01

docs, rename migration

3c70698

Merge branch 'migrations' of github.com:slskd/slskd into migrations

44cf086

add user docs

e2f814f

remove early exit

7477d42

fix header

96e716e

jpdillingham force-pushed the migrations branch from 6e3ad21 to 96e716e Compare May 10, 2025 20:10

jpdillingham added 5 commits May 10, 2025 16:55

adjust transfers db

4172568

migrator wip

646446c

force rename to fix git

b504ac1

rename back

27e2007

refactor migrator to respect NeedsToBeApplied()

3fed167

final tweaks?

9d2050a

jpdillingham force-pushed the migrations branch from 221b1f2 to 9d2050a Compare May 11, 2025 14:14

wburklund reviewed May 11, 2025

View reviewed changes

jpdillingham added 5 commits May 17, 2025 15:43

copy the TransferStates enum into the migration to ensure it remains …

5d5a79e

…stable

remove migration history file

85ba3a7

tweak logs

4efad20

migrations -> backups

7a44729

final tweaks

930df71

jpdillingham merged commit f9cc90d into master May 25, 2025
4 checks passed

jpdillingham deleted the migrations branch May 25, 2025 22:56

This was referenced May 26, 2025

OOM when launching (0.22.2) #1291

Closed

Fix remaining transfter State queries using HasFlag() #1356

Merged

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

Add a system to perform database migrations #1343

Add a system to perform database migrations #1343

Uh oh!

jpdillingham commented May 3, 2025 •

edited

Loading

Uh oh!

wburklund May 11, 2025

Uh oh!

wburklund May 11, 2025

Uh oh!

jpdillingham May 17, 2025

Uh oh!

jpdillingham May 17, 2025

Uh oh!

wburklund May 11, 2025

Uh oh!

jpdillingham May 17, 2025

Uh oh!

wburklund May 11, 2025

Uh oh!

jpdillingham May 17, 2025

Uh oh!

aaronjwood May 19, 2025

Uh oh!

jpdillingham May 25, 2025

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

4 participants

Add a system to perform database migrations #1343

Add a system to perform database migrations #1343

Uh oh!

Conversation

jpdillingham commented May 3, 2025 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Testing

Canary migration

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

4 participants

jpdillingham commented May 3, 2025 •

edited

Loading