Dbmigrate, a SQL migration tool


#1

dbmigrate

I mentioned it before on reddit but since I just released a new version working with MySQL as well, might be worth putting it here.

This is a tool heavily inspired by a Go tool I’ve used: migrate and django migrations.

In short, a migration involves 2 sql files, a up and a down which are plain SQL and are executed depending on what you want do (running dbmigrate up will run all the up migrations not done yet in order for example).

This currently supports PostgreSQL and MySQL, adding a driver is not too hard so let me know if there’s another database that could use it.

Also looking for feedback on needed commands I don’t have implemented yet and general code review.


#2

Neat! Looks like we overlap a lot between this and Diesel CLI

Biggest thing that stands out to me is that your code has a hard dependency on both postgres and mysql, regardless of which one is used. You might also want to consider separating out the code that’s actually responsible for running the migrations into its own crate, so that users can just run migrations automatically as part of build.rs. You should also consider a more “private” name than migrations_table

I also personally shy away from explicitly numbered migrations, as it causes too much pain when two developers work on independent branches and both add a migration. Diesel uses timestamps for that reason (although that’s just the generator. We store the version as a string, so it could theoretically be whatever you want. Also has the benefit of making sure our ordering always matches that of the file system. We discussed a hilarious case on The Bike Shed where my cohost tried adding milliseconds to a few migrations that were generated by a script once, and how revert now always did the wrong thing.)


#3

Thanks!

I changed the name of the table to a safer one.
For the build.rs aspect, I can’t say I see the point of running migrations on compilation, seems like an easy way to mess up the schema on typo/errors and then you need to use the CLI to fix it. Unless I miss your point or missed something about build scripts.

For the hard dependency, it’s so someone can install it and have it working for all the supported databases out of the box, would adding features for individual db and keep all of them by default be better?

For the naming of the migration files, I see the point of timestamps but, coming from a Django background, I find the occasional conflict to be trivial to fix compared to the benefit of being able to refer to migration 12 when talking with a coworker from my experience. I might change that later on but not right now


#4

Nope, you didn’t miss anything. But generally you don’t want to run the app with pending migrations, which means that your choices boil down to either fail to compile/boot, or just automatically run them. I’ve opted for the latter, as I think most cases where you have a typo/error would result in the SQL failing to execute, and in the rare case where it doesn’t, as you mentioned you have the CLI to undo it. I completely agree with anyone going for “fail to compile/boot” instead, though. My point is more that you should have a way to run/check programatically.

If you don’t have anything other than the CLI, it probably doesn’t matter. If you do have something that can be accessed as a non-executable crate, I’d do neither by default.


#5

Ah thanks I see your point now.

I’ll add it if there is more interest for that as that’s not really something I’d use.

Looking forward for diesel btw!


#6

I’m very weirded out by this since Rails introduced them. The framework I used and was involved in (Padrino), still sports numbered migrations as an option.

The point being: if there is a collision in version control on these files, that is a condition to be handled, especially because both migrations could depend in non-trivial ways.

I don’t understand the attempt to avoid collisions there: I want them, they are important information.


#7

I’m very weirded out by this since Rails introduced them. The
framework I used and was involved in (Padrino), still sports numbered migrations as an option.

The point being: if there is a collision in version control on these files, that is a condition to be handled, especially because both migrations could depend in non-trivial ways.

I don’t understand the attempt to avoid collisions there: I want them, they are important information.

I actually very much agree, even on a medium project (~10 devs) you can’t review every single change so having a migration conflict forces you to check whether your change is still valid.


#8

The overwhelming majority of the time, migrations created independently by different developers on different branches do not interact with each other in any meaningful way. It’s a tradeoff, as it can be harder to debug when they do conflict. However in practice that has proven to be incredibly rare.


#9

Only in environments where migration and remodeling is not the norm and only additions are made. I’m pretty much against the mindset that favours clean and easy merges over clear heads-ups, but I know I’m an old grumpy person there.

Conflicts due to similarly numbered migrations are also rather rare if you integrate often. They do happen, but hey, what’s an “mv”?