9 ms·

Refinery: SQL Migration Toolkit for Rust

107 points by agluszak 7y ago

BrightOne 7y ago
This, together with Barrel, looks like a cleaner alternative to Diesel for me.
- willglynn 7y ago
  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.
royjacobs 7y ago
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 7y ago
  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
couchand 7y ago
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?)
- couchand 7y ago
  Found the answer to my first question: https://github.com/rust-db/refinery/blob/3096aceb3bf16f90a511edcfa3b5da084025ae7a/refinery_migrations/src/traits.rs#L25 https://github.com/rust-db/refinery/blob/3096aceb3bf16f90a51...
  - yebyen 7y ago
    I was going to suggest that, but couldn't find it myself... If this is the same rust migration tool that my Rust friend was telling me about last week, he was saying that it's probably inspired by Rails, as the author came from a Rails background. I'm not certain it is the same one though, as he was describing a system where each migration is both an "up" and a "down" operation, described in separate files, and I don't see that in any of these examples. Rails stores the list of applied migrations in a table like this one. (It could be inspired by Rails, but I'm not seeing any suggestive evidence of that as I'm browsing through the author's other published github repos...) It could also be that I'm misattributing the original idea, and Rails migrations are inspired by migrations from another framework, in another language (maybe Django?)
    - thaunatos 7y ago
      Your friend was probably talking about Diesel migrations: https://github.com/diesel-rs/diesel https://github.com/diesel-rs/diesel
    - amarshall 7y ago
      > he was saying that it's probably inspired by Rails, as the author came from a Rails background You may be thinking instead of Diesel and its associated migration system. Its creator is a Rails ActiveRecord core contributor. http://diesel.rs/ http://diesel.rs/
    - shantly 7y ago
      AFAIK the reason one does this is because one can (in most non-shitty databases cough cough) apply most or all transformations to schema inside a transaction, and also write the current version in that transaction, such that if the transaction completes the risk that the version you think the DB is on and the version it's actually on don't match is minimized, if not eliminated. You can't really get that unless you tie it to the transaction on the DB level, which means writing a version somewhere in the DB, in some fashion.
- asdkhadsj 7y ago
  > 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 7y ago
    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 7y ago
      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 7y ago
        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.
  - jxs 7y ago
    Hi! to roll back a migration you have to build a new one which undo's what you want to rollback. Refinery is very inspired on flyway, which only recently started offering undo migrations, but still explains why that is problematic in it's/our vision: https://flywaydb.org/documentation/command/undo#important-notes https://flywaydb.org/documentation/command/undo#important-no...
HugoDaniel 7y ago
How does it compare to sqitch[0] ? [0] https://sqitch.org/ https://sqitch.org/
migueloller 7y ago
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
sly010 7y ago
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".
- kbenson 7y ago
  I was always under the impression that you would both have diffs, and also a full schema dump. I can't imagine you're expected to start from scratch and apply every diff in sequence every time you want to spin up a test box for the DB, so it makes sense to have a full dump done and saved after every diff. Occasionally, you might want to actually apply all the migration stuff from scratch to see if someone screwed up, but I see no reason you wouldn't also always have a full dump of what's the expected current schema.
  - samatman 7y ago
    I have worked in a Django shop where spinning up a new instance involved starting with the initial version of the database schema and applying all the migrations. Don't know if that's standard practice, but I don't have any reason to think that it isn't.
    - shantly 7y ago
      It's a good way to be sure that the migrations lead to the same state as whatever you consider "current". Otherwise you risk diverging without noticing. Though you could probably safely sum up older migrations periodically by running them then grabbing the CREATEs and such for the resulting DB, after proving the output's identical and once you're very certain you have no running copies of code older than that point in the wild anymore.
    - kbenson 7y ago
deleted 7y ago
[deleted]
janpot 7y ago
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 7y ago
  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)
- mhd 7y ago
  Who doesn't? Sure, not everyone runs the same software on different premises or shards by customers, but even just staging/production does benefit from it. And these days, each dev having their own db is quite common.
robsinatra 7y ago
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 7y ago
  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.

107 points by agluszak 7y ago

BrightOne 7y ago

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

willglynn 7y ago
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.

royjacobs 7y ago

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 7y ago
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

couchand 7y ago

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?)

couchand 7y ago
Found the answer to my first question: https://github.com/rust-db/refinery/blob/3096aceb3bf16f90a511edcfa3b5da084025ae7a/refinery_migrations/src/traits.rs#L25 https://github.com/rust-db/refinery/blob/3096aceb3bf16f90a51...
- yebyen 7y ago
  I was going to suggest that, but couldn't find it myself... If this is the same rust migration tool that my Rust friend was telling me about last week, he was saying that it's probably inspired by Rails, as the author came from a Rails background. I'm not certain it is the same one though, as he was describing a system where each migration is both an "up" and a "down" operation, described in separate files, and I don't see that in any of these examples. Rails stores the list of applied migrations in a table like this one. (It could be inspired by Rails, but I'm not seeing any suggestive evidence of that as I'm browsing through the author's other published github repos...) It could also be that I'm misattributing the original idea, and Rails migrations are inspired by migrations from another framework, in another language (maybe Django?)
  - thaunatos 7y ago
    Your friend was probably talking about Diesel migrations: https://github.com/diesel-rs/diesel https://github.com/diesel-rs/diesel
  - amarshall 7y ago
    > he was saying that it's probably inspired by Rails, as the author came from a Rails background You may be thinking instead of Diesel and its associated migration system. Its creator is a Rails ActiveRecord core contributor. http://diesel.rs/ http://diesel.rs/
  - shantly 7y ago
    AFAIK the reason one does this is because one can (in most non-shitty databases cough cough) apply most or all transformations to schema inside a transaction, and also write the current version in that transaction, such that if the transaction completes the risk that the version you think the DB is on and the version it's actually on don't match is minimized, if not eliminated. You can't really get that unless you tie it to the transaction on the DB level, which means writing a version somewhere in the DB, in some fashion.
asdkhadsj 7y ago
> 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 7y ago
  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 7y ago
    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 7y ago
      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.
- jxs 7y ago
  Hi! to roll back a migration you have to build a new one which undo's what you want to rollback. Refinery is very inspired on flyway, which only recently started offering undo migrations, but still explains why that is problematic in it's/our vision: https://flywaydb.org/documentation/command/undo#important-notes https://flywaydb.org/documentation/command/undo#important-no...

HugoDaniel 7y ago

How does it compare to sqitch[0] ? [0] https://sqitch.org/ https://sqitch.org/

migueloller 7y ago

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

sly010 7y ago

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".

kbenson 7y ago
I was always under the impression that you would both have diffs, and also a full schema dump. I can't imagine you're expected to start from scratch and apply every diff in sequence every time you want to spin up a test box for the DB, so it makes sense to have a full dump done and saved after every diff. Occasionally, you might want to actually apply all the migration stuff from scratch to see if someone screwed up, but I see no reason you wouldn't also always have a full dump of what's the expected current schema.
- samatman 7y ago
  I have worked in a Django shop where spinning up a new instance involved starting with the initial version of the database schema and applying all the migrations. Don't know if that's standard practice, but I don't have any reason to think that it isn't.
  - shantly 7y ago
    It's a good way to be sure that the migrations lead to the same state as whatever you consider "current". Otherwise you risk diverging without noticing. Though you could probably safely sum up older migrations periodically by running them then grabbing the CREATEs and such for the resulting DB, after proving the output's identical and once you're very certain you have no running copies of code older than that point in the wild anymore.
  - kbenson 7y ago

deleted 7y ago

[deleted]

janpot 7y ago

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 7y ago
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)
mhd 7y ago
Who doesn't? Sure, not everyone runs the same software on different premises or shards by customers, but even just staging/production does benefit from it. And these days, each dev having their own db is quite common.

robsinatra 7y ago

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 7y ago
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.