
Refinery: SQL Migration Toolkit for Rust - agluszak
https://github.com/rust-db/refinery
======
sly010
Can I just say I have a problem with these kind of SQL migration tools. For
efficiency and flexibility we write out schemas in SQL and these migration
tools would have us stagger our schema across a bunch of files and would make
it very had to look at the current state of things, especially after a few
years of evolution. I generally prefer to write my schema as-it-should-be
instead of writing diffs. I know this is the industry standard, I am not
picking on refinery, this is a general complaint. Please don't say "use an
ORM".

~~~
mjw1007
I recommend the following broad approach:

\- Maintain your current SQL schema in one place, using normal version
control.

\- Use that form to set up new development versions and so on.

\- Also maintain migrations (maybe using a migration tool, maybe just write
them by hand).

\- Make your system for applying migrations guarantee that the result of
applying the migration to the previous schema exactly matches the new schema.

~~~
0xffff2
Any advice on how to implement that last bullet point? I've got an
experimental project that currently does the first two things. I'm getting
close to a point where it actually needs to be deployed and I have to figure
out the last two. Keeping both a complete up-to-date schema and a set of
migrations seems like a good idea in theory, but I don't have any idea how I
would automate a step to make sure they stay in sync in my CI workflow.

~~~
mjw1007
Something like this simpleminded approach:

Use VCS tags to identify schema versions, and a naming convention including
those tags to track the migration files.

Have a script that validates a migration in the obvious sort of way (check out
the project that contains the schema at tag a; add some data; apply the
migration; dump the schema; compare that against a dump made directly from tag
b).

Maintain a store of migrations that have been validated in that way; the way
to add a migration to that store goes through the checking script.

Store the tag corresponding to each database's current schema in the database
itself (and have something automated to check it regularly if you like; that's
basically the same code as the migration-checking script).

Make the only way to change an important database's schema be to run a script
that takes the migration from that store, reading the starting tag from the
database.

------
couchand
The docs seem a bit light on the details of what this is doing. It seems like
it's keeping metadata about applied migrations with their versions and
checksums... somewhere. What is this doing under the hood?

Also, the docs seem a bit light on the usage details. Can you apply particular
migrations? How about a facility to roll back a migration (doesn't seem like
it?)

~~~
asdkhadsj
> How about a facility to roll back a migration (doesn't seem like it?)

Ack, I assumed it was building the rollback based on an understanding of what
was applied. If it can't rollback (unconfirmed yet), that is a problem. Bummer

~~~
vmind
Personally, beyond development, I haven't found rollback migrations
particularly useful. Once you've run migrations on a live database, it's often
much simpler operationally to treat any rollback as a new migration, to keep
things append-only.

From that perspective, while rollbacks are nice, the technical investment
needed to auto-generate sound rollbacks for all DDL operations is probably
vastly outsized compared to the benefit, so I can see why it wouldn't be a
high priority, especially if targeting multiple databases. If you're writing
things by hand, there's not a whole lot of difference between the two.

~~~
asdkhadsj
That's fair. It never occurred to me honestly, but it's reasonable -
especially since I don't think humans can reliably make `down.sql`

~~~
shantly
Those _down_ files probably rarely receive as much implicit or explicit
testing as the _up_ files, too. I sure wouldn't trust them, in most cases.

------
royjacobs
The design looks quite similar to Flyway[0]. I can confirm that this design
has been working quite well for us in a large-scale production environment.

[0] [https://flywaydb.org/](https://flywaydb.org/)

~~~
jxs
yes! it is in fact inspired by flyway, I am going to update and mention that,
along trying to be more detailed on it's details

------
HugoDaniel
How does it compare to sqitch[0] ?

[0] [https://sqitch.org/](https://sqitch.org/)

------
BrightOne
This, together with Barrel, looks like a cleaner alternative to Diesel for me.

~~~
willglynn
Barrel and Refinery are intended to specify and to execute DDL respectively,
while Diesel is intended to specify and execute queries and DML. They target
complimentary portions of the SQL problem space and are not alternatives to
each other.

------
robsinatra
Rust doesn't seem to lend any advantages for this kind of tool, but I'm sure
it took weeks to write (months?) rather than just a couple of hours. What can
this do that bash scripts couldn't? This migration tool uses plain sql files.
I'm not hating but questioning the use of a complicated language to do no more
than what native shell scripts can, with ease.

~~~
0xffff2
Just based on a quick skim of the readme, it looks to me like it supports, but
does not require sql files. Presumably this tool works better for someone who
already has a Rust application and wants to write Rust and not deal with SQL
directly if possible.

------
migueloller
Reminds me a lot of Postgrator [1], which we use in our NodeJS codebase.

[1]
[https://github.com/rickbergfalk/postgrator](https://github.com/rickbergfalk/postgrator)

------
janpot
I don't see the point of these migration tools unless you are running many
different versions of your schema at the same time.

~~~
setr
One instance this occurs is when your website is open source, in which case
people are upgrading from arbitrarily old versions.

I suppose it happens when you ship any kind of application with db included
(probably more common/useful would be sqlite support)

