Feature Request: Rust migration scripts

[D1plo1d]

It would be helpful to have the option to define migrations in Rust instead of SQL, eg:

sqlx migrate add --rust default_materials
Creating migrations/20210314225352_default_materials.rs

Creating a file something like this:

pub async fn migrate_default_materials_20210314225352(
    db: sqlx::sqlite::SqlitePool
) -> Result<()> {
  // Create tables and data
  Ok(())
}

Is this something that could be added to sqlx migrations?

Edit: Adjusting the format of the generated rust file.

[abonander]

I'll be honest, when I first read the title of this issue in the Github notification email, I was absolutely disgusted at the idea. You have to admit, it's kind of ridiculous at face value. After thinking on it for a bit it's started to grow on me, however still the implementation wouldn't exactly be trivial.

For embedded migrations it would be relatively straightforward as we could just read the migration file and spit out the Rust code at the site of the sqlx::migrate!() call. Voila, procedural migrations.

However, to make it work with sqlx migrate run is another matter entirely. You can't just ask Cargo to compile an arbitrary Rust file in the context of a crate. We would probably have to do one of the following to compile Rust migrations on the fly:

1. Synthesize a rustc invocation the same way Cargo would for a binary target in the current crate (hard, but least hacky).
2. Append a [[bin]] target section for each Rust migration file to the existing Cargo.toml (easy but inflates the Cargo.toml over time)
3. Copy the Cargo.toml to Cargo.toml.bak and emit a new Cargo.toml with the migration scripts added as binary targets, and then put the old Cargo.toml back after running the migrations (somewhere between 1 and 2 in difficulty; would need to be careful not to leave the Cargo.toml in an invalid state if the process dies unexpectedly).

I'm leaning towards 2 as it's simple and easy to implement, and it would allow CI to compile-check migrations without needing sqlx-cli installed. However, it would mean that cargo check and cargo build would always compile migrations which might get annoying (although it shouldn't have to rebuild them very often).

The other issue is that the current migration API isn't designed to invoke a function as written and the function would probably need to generate and return a Migration instead of running the SQL directly, which reduces its utility.

So we'd probably have to move a bunch of stuff around in migrations in order to accommodate this feature to actually have the desired flexibility of just running arbitrary commands on the database procedurally.

[Diggsey]

Append a [[bin]] target section for each Rust migration file to the existing Cargo.toml (easy but inflates the Cargo.toml over time)

Couldn't you just generate a single migrator binary that compiles in all the migrations, and runs one specified via the command-line? Then you'd only need one [[bin]] entry, and the sqlx CLI can just invoke the same binary multiple times.

[abonander]

That is certainly an option, yeah, although the binary code would need to be modified every time a new migration is added which would still be causing a file to inflate over time, just a different one. The Cargo.toml would be easier to append to, though.

[mehcode]

// 20210314225352_name.rs
#[sqlx::migration] // generates fn main() that calls this migration function
pub async fn migrate_default_materials(
    txn: &mut SqliteTransaction,
) -> Result<()> {
  // Create tables and data
  Ok(())
}

[DanielJoyce]

Not all conversions can happen in SQL, especially those involving multiple tables, complex filtering, etc. Rust code migrations would be awesome. I've used similar techniques in other languages/frameworks.

[tamasfe]

I've made an experimental library that explores this idea with build-time code generation instead of macros.

[kikijiki]

Bumping for interest.
If during a migration you want to transform data, doing it from Rust can be much more ergonomic (and in some case the only way, unless you manage to replicate your app logic in sql).

Some libraries that allow a similar approach:

• https://github.com/SeaQL/sea-orm/tree/master/sea-orm-migration
• https://github.com/rust-db/refinery

[SimenB]

https://github.com/iamsauravsharma/sqlx_migrator is kinda this, I believe? Would be awesome to have it work with sqlx migrate run, sqlx::test etc, tho