Sqlx: Introduce cargo-sqlx and define a subset of migration commands

[mehcode]

References:

• https://github.com/golang-migrate/migrate
• https://github.com/flyway/flyway

---

| Option | Default | Description |
|-|-|-|
| url | $DATABASE_URL | The connection string used to connect to the database |
| table | _migrations | The name of the schema history table.
| dry-run | No | Echo all SQL output to stdout and do not connect. |

Questions:

• What other things should the CLI do?
• We know that we want a crate interface for this at sqlx::migrate::_. I'm thinking of it being similar to the CLI interface for commands that make sense. E.g., sqlx::migrate::run(conn, migrations).await? -- How should this look? I think that embedding should be a top use case but should we export a more dynamic migration utility in the module as well?

[abonander]

migrate create sounds weird. migration [run, new] maybe? cargo sqlx migrate could be a shorthand for migration run

[abonander]

Or just make it migrate new

[mehcode]

Generate a new migration file of a recommended format

cargo sqlx migrate create NAME

• What is the command named: new, add, create, generate ?
• This should not be a necessary command for the user and we should depend on very basic format like {}_{}.sql where the first chunk is an ordered "version" and the second chunk is a name.

Run all necessary migrations

cargo sqlx migrate run

Revert the latest migration

cargo sqlx migrate undo

• My plan is when we migrate, we back-up the database. If there is a back-up available, the revert operation succeeds. Not all databases need to support this. I know we can at least cheaply support this in PostgreSQL and SQLite.

Redo the latest migration

Intended for development when you're iterating on a migration and testing

cargo sqlx migrate redo

Print details about db version, local potential version, etc.

cargo sqlx migrate version

Create database at URL

cargo sqlx db create
cargo sqlx database create

Drop database at URL

cargo sqlx db drop
cargo sqlx database drop

---

@JesperAxelsson
@abonander

Thoughts on the above direction? I'd like to get us aligned on _where_ we are going so that we can work asynchronously on getting to that point eventually.

[JesperAxelsson]

Generate a new migration file of a recommended format

cargo sqlx migrate create NAME

* What is the command named: `new`, `add`, `create`, `generate` ?

* This should not be a necessary command for the user and we should depend on very basic format like `{}_{}.sql` where the first chunk is an ordered "version" and the second chunk is a name.

I am biased toward migration add <name>. EF core uses dotnet ef migrations add InitialCreate. Create for me kinda feels like starting from scratch.

Run all necessary migrations

cargo sqlx migrate run

I like the idea about a dry run. Could make a normal and one verbose version of the dry-runs. With the normal version you just get which migrations is already applied and which will be applied. The verbose version could print all the sql as well. Could get a bit spammy ^^

Revert the latest migration

cargo sqlx migrate undo

* My plan is when we migrate, we back-up the database. If there is a back-up available, the revert operation succeeds. Not all databases need to support this. I know we can at least cheaply support this in PostgreSQL and SQLite.

Undo with backup can be tricky if the db large or is a place where the user doesn't have right to the filesystem. But leaving it in as an option would be nice. But should it be considered default or an extra? cargo sqlx migrate run --with-backup vs cargo sqlx migrate run --no-backup

Redo the latest migration

Intended for development when you're iterating on a migration and testing

cargo sqlx migrate redo

Would be nice to have. The laravel podcast your linked in my earlier PR mentioned a refresh command that resets the db and runs all migrations from the start. This might be useful if you have good seed.

On the topic of seeding, this might be something to look into as well. I don't have a clear vision how a good seed tool would look but it would be useful for development/testing.

Print details about db version, local potential version, etc.

cargo sqlx migrate version

Create database at URL

cargo sqlx db create
cargo sqlx database create

I like this better then my implicit create.

Drop database at URL

cargo sqlx db drop
cargo sqlx database drop

@JesperAxelsson
@abonander

Thoughts on the above direction? I'd like to get us aligned on _where_ we are going so that we can work asynchronously on getting to that point eventually.

I like it! The migration tool need to be abstracted a bit as there kinda is none at the moment. A first step would be to break of the parts that are not specific for the CLI. Got a decent idea on how to do this.

[amy-keibler]

Sorry if this has already been discussed elsewhere and I missed it, but I'm curious what everyone's thoughts on the proposed cargo sqlx migrate undo using a backup. The migration tools that I'm more familiar with have paired up and down script concepts and it appears that both of the linked references in the initial post do as well (other examples: Diesel, Elixir's Ecto, Ruby's ActiveRecord).

In my experience, when rolling back a migration, I've wanted to remove the changes (added columns, tables, etc.) but keep any new rows that had been inserted post-migration. Is there a use-case that I'm unfamiliar with where rolling back all of the data is the better approach?

I do think it would be a good idea to optionally take a backup before migrations and have the ability to then apply the backup with the same tool as needed.

[mehcode]

The undo/redo stuff using backups is just an idea. I'm not entirely convinced that it makes sense but I think it might be worth exploring it. My logic is generally the only reason I've _ever_ wanted a down migration is to iterate on a migration script in development and run something similar to "redo" from "diesel-cli". Automatic back-up/restore would fulfill a similar need without needing to actually write the down migration.

---

The migration tools that I'm more familiar with have paired up and down script [...]

I would love to discuss more here. Design decisions are definitely open for discussion.

I believe that _required_ down migrations are actively harmful. It has been my experience ( as a CTO for the better part of a decade ) that cost for the time spent on _writing_ down migrations significantly outweighs the benefit. In a world where our software is increasingly deployed at a distance ( for example, using kubernetes, docker swarm, PaaS tools like Heroku, etc. ) and delivered in packages, the down migration is effectively useless in production. If you introduce a migration that hurts production in some way, you cannot actually invoke that down migration using a recovery tool you or your system administrator would have access to.

With that said, I don't have an objection to introducing optionally undo-able migrations. The idea is instead of always generating a _paired_ set you have an annotation on the migration file. Perhaps we use prefixes like Flyway where "V" means normal version and "U" means an undo for that version.

v1_create_the_thing.sql
u1_create_the_thing.sql  

And we can make migrate undo / migrate redo utilize these when present and even migrate new generate both files with configuration / an option.

Another somewhat neat thing you can do with annotated files like that is _repeatable_ migrations. In other words, a bit of SQL that is _always_ run on server boot.

r1_always_run.sql

Another idea would be suffixes as in 21391_create_the_thing.undo.sql and 324_create_the_thing_always.always.sql

[amy-keibler]

I believe that required down migrations are actively harmful. It has been my experience ( as a CTO for the better part of a decade ) that cost for the time spent on writing down migrations significantly outweighs the benefit.

That's totally fair. My experiences have been at a much smaller scale, where some applications have been deployed in clients' internal environments and having the ability to perform "down" migrations would correspond with rolling back to a previous code version in order to allow the client to continue their work. I've only ever needed that ability on rare occasions, but I've been glad it was there when I did. I think that making them optional would be a good approach if it doesn't require too much additional effort to support "down" migrations. I like the suffixes approach you mentioned compared to the prefix approach, since migration files would be listed next to each other in a file listing.

[JesperAxelsson]

Always migrations

If I understand always migrations correct it's meant for seed data? In ef core you actually seed in the code instead of in sql files. Easier to create logic there.

Naming scheme

Regarding naming I would suggest this scheme:

• Up migrations is not marked, this is the default.
• Down migrations is in <rev>_<name>.down.sql, undo would as well. So you don't need to hunt after them in an editor.
• Always migrations is in always_<rev>_<name>.sql, this will keep all the always migrations grouped.

Even though naming is a bit uneven, it's still not much to keep track of.

On down/undo migrations

I agree on the optional down migrations. I think the backup and restore would be tricky to get right. As it can potentially remove data, and someone will use this in production, I would rather be on the safe side.

Some random thoughts on optional down migrations:

• Toggleable setting to force/auto create down migrations, in the env file for example.
• How do we handle gaps? Like if a user has two migrations, rev 1 and rev 2. But only rev 2 has a down migration? A preflight check and a force override might be a way forward.
• Do we allow always-down migrations?

Some suggestions for command arguments

Normal (up) migration:
cargo sqlx migrate create <name>

Down migration:
cargo sqlx migrate create -<d|down> <name>

Always migration:
cargo sqlx migrate create -<a|always> <name>

[audunhalland]

Hi, I just started experimenting with sqlx and I'm really excited!

I started looking around for a migration tool, and here we are! I'm also a Flyway user so let me just chime in about my current workflow with it:

I use TDD and write integration tests against some local dummy/dockerized db. Before each test run, flyway will clean data and then migrate my schema to the latest version. In case it fails to clean my db completely, there are some optional "hook" scripts that it can run (in test mode only), like beforeClean.sql or beforeMigrate.sql.

The _test migration procedure_ applies migrations by sourcing versioned schema migrations intended for production, interleaved with versioned "seed" data files only intended for testing. This way I can develop a new migration, verify it onto existing seed data, and test that it works by running the tests. I think something along these lines should be doable with Rust integration tests.

So I guess the real reason for commenting is that I didn't immediately understand the purpose of cargo sqlx run|undo, and how that will fit into an automated test-driven workflow.

[kevlarr]

I don't think I've ever run a down migration in prod. I've spent a lot of hours writing down migrations that only get used during development and only while iterating on the up migration, and they only exist to restore data to (hopefully but quite often not) the same state it existed in previously, so I think the built-in down-migration-as-backup tool that @mehcode is suggesting is a really cool idea that would save me a lot of time.

Off by default, opt-in with flag or env var... It would be great to just say "hey, roll me back 5 migrations to version x" and knowing everything is as-it-was, indexes, constraints, etc. included.

I'm curious about having more potential for the "dry run" option, though. Rather than printing the SQL to std. out, would anyone else find it helpful to have the SQL actually executed and then just not committed? I generally start my alembic migrations by adding raise Exception() at the end of the up migration just so it won't, just in case the statement I think will work but not sure about actually does before I'm ready for it to.

[kevlarr]

I'm also wondering if anyone has given though on how to handle migration versioning, especially across multiple developers and multiple branches? Python's alembic migrations individually specify what version they follow, so if two different devs (or me on two different branches) add new migrations, they both point to the same "down" migration and it breaks the history.

I personally haven't seen much advantage from that and it's a royal pain to work with most of the time. Especially because you can't inspect the database to see which migrations have been applied - it only shows the latest 'version' hash id.

How does flyway handle situations like that? I've only really used alembic, active record, and diesel, and so far I like that diesel lists the versions applied and when they were applied.

[mehcode]

How does flyway handle situations like that? I've only really used alembic, active record, and diesel, and so far I like that diesel lists the versions applied and when they were applied.

Flyway doesn't define this actually. It just looks at the filename and orders them. A best practice is to generate files using a UNIX timestamp (which I believe our alpha tool on master does now) so if two developers make new migrations they'll merge together in the folder fine. The only caveat is obviously you don't want to add new migrations _earlier_ after a deployment to production.

One thing I really like about Flyway that I hope to mimic is it checks and errors for things like adding a new migration "earlier" in the chain or editing a migration file after migrations have been run (it does this by check-summing the files).

[bjeanes]

I don't think I've ever run a down migration in prod

I have a few times. However, unless my memory is failing me, I think it has always been related to re-trying a migration that creates an index concurrently (which can fail). In those cases, I have redone the migration and taken advantage of the down migration (after auditing it for potential unintended data loss).

However, as a feature, down migrations are more valuable for dev, in my own experience.

Undo with backup can be tricky if the db large or is a place where the user doesn't have right to the filesystem

Agreed, I would actively be terrified of a migration tool which tried to interact with backups. That simply would not work consistently across different DB sizes or backup strategies. Also, it has potential privacy or security implications in cases where backups need to be handled extra sensitively and it feels beyond the scope of a migration tool to contend with that adequately.

I also strongly agree that the the risk of data loss by rolling back a backup as well as the requisite downtime would be unacceptable for most deployments. If the migration needs to be undone quickly enough after running it that there isn't new data, having the migration wrapped in transactions by default should already protect against common errors in migrations there. It seems better to me that there is instead a strong voice in the documentation to make sure that critical data is covered by regular and ongoing backup strategy.

[rezmuh]

Hi, I just recently experimented with sqlx and tried to build a simple todo app using sqlx + refinery + barrel. The proposal here seemed to refer to flyway (refinery's design seems to be based on flyway as well).

Since migration tool exists (such as refinery), why build another one?

[alun]

Hi, is it expected that sqlx-cli is yanked? https://crates.io/crates/sqlx-cli/versions.

Wanted to generate JSON file to support compile time type check in CI build. Is there a newer way of doing that? Thanks.

UPD: I guess it's only added in 0.4 so not supported for current stable of 0.3.5 on crates.io and was yanked to avoid confusion.

[bjeanes]

UPD: I guess it's only added in 0.4 so not supported for current stable of 0.3.5 on crates.io and was yanked to avoid confusion.

Correct. sqlx-cli is not yet published, but it looks likely that it will be with the next release.