Use sqlx `migrate!` to bootstrap DB schema
amaury1093 opened this issue · 3 comments
sqlx supports a migrate! macro, which can read from a migrations folder, and apply all migrations in that folder at startup.
We should use this, as it allows a smoother onboarding for users starting with reacher.
https://github.com/launchbadge/sqlx, CTRL+F migrate!
Currently the db schema is not idempotent. It drops tables and recreates them. The first thing would be to make them idempotent like IF NOT EXISTS .... But that would also mean changing the sqlqmx migrations which may be tricky.
I also wanted to check if having this as a separate binary would be a better experience like /src/bin/migrate.rs. This would allow the app be restarted, without any fear of making changes to the db. End users could choose to apply migrations rather than auto apply them as soon as the main started.
I think the ideal scenario would still be an idempotent migrate! bundled in the single binary. The 2nd best is a separate migrate.rs.
Could we explore the 1st solution first? i.e. can we try submitting a PR on upstream? If it doesn't get merged in a timely manner, we can fallback to our own migrate.rs.
The first thing would be to make them idempotent
Turns out we don't need to make them idempotent! sqlx tracks the migrations that are already run, so we can easily re-run the same migrations, and sqlx will skip the ones already run.
I also wanted to check if having this as a separate binary would be a better experience like /src/bin/migrate.rs.
I think I agree with you this might be a desirable feature, and I'm open to consider this. But for now let's just embed the migrations as it allows one simple binary.