Add SQL migration binary

[omid]

Also lowered the size of the current docker image from 2.7GB to 0.4GB (uncompressed).

[fmeringdal]

Nice draft @omid! I wonder if it is possible to structure migrations like this:

migrations
│
└─── 0
│   │   01_domain_types.up.sql
│   │   02_iam.up.sql  // Users and account
│   │   03_calendar.up.sql  // Calendars, events etc
│   │   04_booking.up.sql  // Service, service users, reservations etc
│   │   05_integrations.up.sql  // Account and user integrations
│   │   ...
│
└─── 1
│   │   01_iam.up.sql  // Changes to users or account
│
└─── 2
    │   ...

I think this folder structure is really neat, I found it from this project.

What do you think?

[omid]

I don't know any library in Rust that can do this. I used the default sqlx migration library. And I only could find another library which is https://github.com/rust-db/refinery. We cannot have a different structure that they support.

Diesel migration is also similar, but a little better, since it has "down" queries.

If you know a better library, we can switch to that.

[fmeringdal]

I think sqlx migration is good enough, but there will need to be some more code in the migration binary to perform the migration. This is how I think the migration binary will work:

1. Lock (Using pg_advisory_lock()) to ensure there are no other migration processes running at the same time
2. Begin migration transaction
3. Ensure nettu_scheduler_schema_version table exists (otherwise create it)
4. Find the current schema version
5. Check if there are newer versions in migrations folder and apply them from oldest to newest. For example if current_schema_version is 2 and migrations folder contains folders 0, 1, 2, 3, 4 then apply the sql scripts in folder 3 and 4
6. Update version number in nettu_scheduler_schema_version table
7. Commit transaction
8. Release lock

About down queries I don't think it is necessary, I think it is better to roll forwards instead of rolling backwards if a bug is released.

[omid]

I didn't get the requirement you are mentioning. I think the current code does the migration. And the reason for this task for being a draft, is to fix the CI/CD.

[fmeringdal]

I think it is necessary to store which version the DB schema is in. The current code seems to apply all sql scripts when doing a migration?

[omid]

It does all of this.

https://github.com/launchbadge/sqlx/tree/master/sqlx-cli#create-and-run-migrations

The library will do what you need. It will create a table with the name of _sqlx_migrations and keep versions there. You can find it here: https://github.com/launchbadge/sqlx/blob/f0d0dce8e25c40dffd1431776b6a38510a70bde0/sqlx-core/src/postgres/migrate.rs#L93

[fmeringdal]

Yup, after looking at the sqlx migrations it seems to do all of the steps I mentioned above. I have been thinking about wether to implement the migrations steps ourself or just use the sqlx migrations. I really like the nested folder structure as mentioned above, but that will not be supported by sqlx unfortunately. I guess we should just go with sqlx migrations for now and implement a custom migration binary later if it is needed. @omid Do you have any changes you would like to do before merging this PR?

[omid]

Agree, that's another project, like Refinery (but better :P ) Nope, I'm done with the PR, we can merge it if you are fine.

[fmeringdal]

I think it might be useful to split up the sql scripts into several files so that it is conceptually easier to understand the nettu-scheduler domain model. I will make a proposal on how to do this in a new PR. Good job with this PR as always @omid ;)