Refinery: SQL Migration Toolkit for Rust
github.com
github.com
Don't know if that's standard practice, but I don't have any reason to think that it isn't.
Well, one reason it might not be is that it's somewhat equivalent to checking out the initial version of the code, and applying all diffs to get to the current version every time you want to see the latest commit/HEAD.
On one hand, it's good to know, on the other hand, other than the last few steps needed to roll back a change, I would argue it's much more important to make sure that the current full schema represented in dev/testing (including static/dictionary tables) is as accurate as possible to the current live schema when the production branch is tested, and if you're already doing a schema dump and diff, you might as well be saving that as well.
A bit like flying a plane to your destination with a compass and stopwatch rather than simply looking out the window or at the GPS.
Migrations should be checked against the current production schema directly, not by retracing historical steps.
There are two states you care about: current production, and desired production.
There is one migration you care about: the one between production and desired.
Involving anything else is pointless complexity for no reason.
You could make it easier to see the current schema by having a pre-commit hook or similar which runs all the migrations and then saves a snapshot of the final database state to a file.
I wrote a tool for this exactly this purpose, and there are an increasing number of similar tools to support working this way.
Another common problem is migration tooling tied to an ORM as rails/django do (it looks like refinery avoids this, which is good). Working with migrations directly on the database level means you can support multiple (or zero) ORMs and apps with less lock-in, support more database features, and confirm the structure of the database directly.
Isn't that exactly what most migration tools do? How do you get from schema version 1 to schema version 2 to schema version N without a "chain" of transformations? different from a "migration 'chain'"?
While Rails' Migrations are part of/use parts of ActiveRecord, Nothing about them forces you to use AR (or any other ORM) for application code. Heck, I've used Rails Migrations in Node.js projects because they work so well.
My preferred alternative to reconstructing prod from a historical chain of changes is to compare the preferred state to the production state directly. In most cases you can generate the script you need completely automatically from a diff tool. Direct comparison also allows you to directly test that your changes have been applied correctly and that prod schema explicitly matches.
If it's possible to use rails migrations without an ORM that's great, although that's always going to be secondary to the ORM use case. Django migrations are extremely heavily coupled to the ORM, and it's almost impossible to use one without the other.
I'd like to know what most cases is here, a diff tool isn't going to know whether adding a column and removing a column is a rename (assuming the right types), or whether they really are different columns. A diff tool isn't going to know what the value needs to be for your new non-NULL, non-DEFAULT column (nor is it going to know that for a NULL column existing data does need a value, but that new data needs a different value, if there is even a singular value that it can use).
SQLAlchemy does something like what I think you're describing (and is tightly coupled with the application-level ORM): you update attributes on the model classes, and then it generates a migration for you. But I've always had to manually edit those migrations by hand.
The Rails "migration first, model later" method feels much cleaner to me, but maybe that's just because I learned it that way.
Migrations are one of the most dangerous things I run on prod, so I'm always interested in better/safer ways of doing them.
It's still a "migration", the difference is it's generated from a direct comparison to production rather than a crude reconstruction of the database's history.
Checking production directly is much more robust (more testable, changes made outside the context of the migration tooling are no problem) and simpler because it you don't need to maintain a historical migration chain - there's only ever one migration script to consider.
And using a diff tool directly on the database means you can support database features that aren't directly supported by your ORM.
- 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.
So to test from any given migration:
- Checkout the code at that point
- Apply the migration
- Apply tests
And repeat as necessary.
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.
Facebook has been using the declarative approach (repo of CREATE statements, pure SQL) company-wide for nearly a decade and it works extremely well. Conceptually, this approach allows you to treat database schemas just like code: request a schema change by pull request; CI validates and sanity-checks; review the PR within your own team just like code; merge to kick off the change using a CD pipeline.
I liked this approach so much that I started an open source project in early 2016 to help enable this workflow for MySQL and MariaDB, https://www.skeema.io. It has a number of validations and safety checks built in, including the one described in your 4th bullet.
I've never found any, though it's a few years since I last looked. (I use Postgres, which has a fairly rich set of possible schema objects, and adds new bits frequently; I'd be reluctant to rely on a project that wasn't actively maintained and starting from 100% support).
The principle here is that in this case (as often), it's much easier to teach a computer to check that you've done manual work correctly than it is to teach the computer to solve the general problem.
This has been working well for me for many years, but I'm typically only dealing with a few dozen schema changes (in production) a year; I can imagine that Facebook sees the world just slightly differently.
If you maintain your schema like source code, you can use comments, organise it into files in a way that makes sense to you, maybe do a bit of light templating, and so on.
> If you maintain your schema like source code, you can use comments, organise it into files in a way that makes sense to you, maybe do a bit of light templating, and so on.
As usual—for the comments part, at least—PostgreSQL has your back.
The former can be made visible to people using the database; the latter are for people maintaining the schema.
In any case, I don't think I'd get on with an auto-formatter that thought « COMMENT ON TABLE foo IS » was a friendly comment-introducing string. « -- » takes up rather less space.
0. https://packagist.org/packages/orisintel/laravel-migration-s...
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?)
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?)
You may be thinking instead of Diesel and its associated migration system. Its creator is a Rails ActiveRecord core contributor.
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
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.
I suppose it happens when you ship any kind of application with db included (probably more common/useful would be sqlite support)