Provide a migration script for older Torrust versions

[mickvandijke]

There have been quite some database changes between V1 and V2 of the backend and this renders the database of a backend running V1 incompatible with backend V2. The biggest change we made to the database is the way torrents are stored. In V1, torrents were stored as actual .torrent files in a dedicated uploads folder on the backend server. As of V2, all torrents are completely stored in the database.

To make sure all V1 users can move to V2 of the backend, we want to provide some kind of migration script. This script would take a V1 database (dump) and torrent uploads folder as input and should be able to convert this data to a new database dump that is suitable for the backend V2.

We will start working on this script when we believe V2 is in a usable state.

[shadowrowan]

Is there any progress or future plans on this? Would be super helpful. Thanks.

[mickvandijke]

Hey @shadowrowan ,

Sorry for the late reply. A member of the team will look into the migration script in a couple of days from now, but I can not tell how long it will take. We will make sure it is done before releasing the production ready V2 update, so that V1 users don't miss out :)

[josecelano]

hi @WarmBeer @da2ce7. I want to define the scope of this issue and propose an implementation.

I've been taking a look at these two commits:

• v.1.0.0.
• develop. Current HEAD of development which contains the DB schema that is going to be used in release v2.0.0`

Migrations

This is the list of migrations in v1.0.0:

20210831113004_torrust_users.sql
20210904135524_torrust_tracker_keys.sql
20210905160623_torrust_categories.sql
20210907083424_torrust_torrent_files.sql
20211208143338_torrust_users.sql
20220308083424_torrust_torrents.sql
20220308170028_torrust_categories.sql

And this is the current list of migrations (develop):

20220721205537_torrust_users.sql
20220721210530_torrust_user_authentication.sql
20220727213942_torrust_user_profiles.sql
20220727222313_torrust_tracker_keys.sql
20220730102607_torrust_user_public_keys.sql
20220730104552_torrust_user_invitations.sql
20220730105501_torrust_user_invitation_uses.sql
20220801201435_torrust_user_bans.sql
20220802161524_torrust_categories.sql
20220810192613_torrust_torrents.sql
20220810201538_torrust_torrent_files.sql
20220810201609_torrust_torrent_announce_urls.sql
20220812181520_torrust_torrent_info.sql
20220812184806_torrust_torrent_tracker_stats.sql

The list of migrations is different. Migrations have been rebuilt from scratch, so they only work if you set up a new installation.

V1 only has SQLite migrations.

Tables

The v1 contains these tables:

_sqlx_migrations
torrust_categories
torrust_torrent_files
torrust_torrents
torrust_tracker_keys
torrust_users

The v2 contains these tables:

_sqlx_migrations
torrust_categories
torrust_torrent_announce_urls
torrust_torrent_files
torrust_torrent_info
torrust_torrent_tracker_stats
torrust_torrents
torrust_tracker_keys
torrust_user_authentication
torrust_user_bans
torrust_user_invitation_uses
torrust_user_invitations
torrust_user_profiles
torrust_user_public_keys
torrust_users

Migration manipulation

Migrations were changed for both for SQLite and MySQL at least once.

Solution

I'm going to start focusing only on a solution for a concrete case. I think we can make the solution more generic later.

What I think we could do is:

• Add a new binary to the crate to export the current in V1 to database to V2.
• You can run the command with cargo run --bin db_migrate
• The first version will only work for SQLite.
• You have you run the command in production (although you have to test it before in a local copy of the production system).
• The command will get data from file data in the root folder and torrent from uploads folder, creating new records in a new database data2.
• After the data transfer, you can test the new database. You can use it in production and remove the uploads folder with torrents if it works.
• The command could run the migrations in v2 in the empty new DB.

Once we have this script I think we can easily change it to migrate from any commit to any other as long as we know the schema used in both commits.

Improvements

• I assume people are using only releases and not intermediary commits. That means only SQLite should be used in production. I think MySQL migrations were also introduced at some point (probably in this commit 6456c54) and changed later (in this commit d96c56f). We would have to build a custom migration command if someone uses an intermediary case (commit).
• The command has to be executed manually. If we want to execute it automatically, we could do it before executing migrations, but I do not know how we could check which version was the previous one and which is the current one. I think the only way to do it is by using the "migrations" table and the current list of migrations. Anyway, I would prefer to execute it manually. But even if we execute manually, I would double-check that you are migrating from v1 to v2.

Notes

• I do not know how easy it could be to export data from DB to DB due to foreign key constraints.

Alternatives

• Using the REST API. That would be useful to transfer data from one app to another, but we do not expose all the data (like user info).
• Try to find out the DB schema used in each commit and rewrite the migrations git history. Although it may be feasible, I think it would take too much time.

[mickvandijke]

Hey @josecelano , I agree with the scope and your approach 👍 .