7 ms·

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 wou

by 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
    > 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.
  - frenchyatwork 7y ago
    Having worked with Django a bunch, I'd recommend squashing your migrations once you have a lot of them. It will speed up the time it takes to make a new database, including testing time if you're testing your database.
  - djrobstep 7y ago
    I find this sort of practice absolutely bizarre. 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.
    - zerd 7y ago
      I disagree. If you have test environments only tracking "current" and "desired" doesn't hold up, then you would need different diffs for each environment. Making sure they all apply the same migrations in the same order is important to avoid drift and ensuring consistency. Also an issue if you have multiple production databases, e.g. sharding. When you have lots of migrations and you're sure all environments are up to date to a certain point you can "squash" the initial ones to make it apply faster.
      - djrobstep 7y ago
        I don't see the problem. Each branch has its own desired state as necessary. A different database version is going to need a different migration script, no matter what your approach. Only difference is whether you generate those scripts by direct comparison with prod directly or with a crude reconstruction of it.
RussianCow 7y ago
Sorry if I'm misunderstanding, but if the only thing you have is the "current state" SQL file, how do you make automated changes to the schema? The point of migration tools is that making a change is simply a matter of making a new file that lays out that change, and then running the tool. Do you alter the DB manually?
Diggsey 7y ago
Given two snapshots of the schema, there are multiple paths from one to the other. This makes it impossible to generate perfect migrations from the snapshots, you always end up needing manual changes. 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.
djrobstep 7y ago
Yes, we need to move away from the notion of a migration "chain", it's not a very useful way to think about schema changes. Most people have an ideal schema in mind - they should be free to simply craft this directly, with tooling to make the changes necessary to evolve the schema accordingly. 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.
- bradstewart 7y ago
  > with tooling to make the changes necessary to evolve the schema accordingly 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.
  - djrobstep 7y ago
    Yeah most migration tools work this way, although refinery doesn't. 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.
    - irishsultan 7y ago
mjw1007 7y ago
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.
- samatman 7y ago
  For extra credit, have the last step involve the version control system, so that each migration contains a commit of the last version which contains the old schema, and each version contains a (database | data seed) sufficient to verify the validity of the migration step. So to test from any given migration: - Checkout the code at that point - Apply the migration - Apply tests And repeat as necessary.
- 0xffff2 7y ago
  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.
  - genuine_smiles 7y ago
    If you were using rails you would run migrations and then with git check if the schema file has any changes.
  - mjw1007 7y ago
    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.
shantly 7y ago
You can derive the current state (in CREATE statements and such) as part of your pre-commit build process if you really want it, no? Or interrogate any active copy of the DB to get it at any time.
- mjw1007 7y ago
  That's a bit like retrieving source code by pretty-printing a minimised form. It's not terrible, but it's not going to be as pleasant as the original source. 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.
  - shantly 7y ago
    I find the format of code that comes from tools deriving CREATEs from live databases to be totally acceptable, usually. Then again I'm the kind of person who prefers team-wide auto-formatting and a strict style linter, so maybe that's just me. > 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. https://www.postgresql.org/docs/9.1/sql-comment.html https://www.postgresql.org/docs/9.1/sql-comment.html
    - mjw1007 7y ago
      I use SQL-level COMMENTs and comments in the schema source for different things. 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.
deleted 7y ago
[deleted]
paulryanrogers 7y ago
Laravel migrations too are diff-only, so I created a package [0] to automatically dump as migrations are run. 0. https://packagist.org/packages/orisintel/laravel-migration-snapshot https://packagist.org/packages/orisintel/laravel-migration-s...

by sly010 7y ago

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
  > 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.
- frenchyatwork 7y ago
  Having worked with Django a bunch, I'd recommend squashing your migrations once you have a lot of them. It will speed up the time it takes to make a new database, including testing time if you're testing your database.
- djrobstep 7y ago
  I find this sort of practice absolutely bizarre. 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.
  - zerd 7y ago
    I disagree. If you have test environments only tracking "current" and "desired" doesn't hold up, then you would need different diffs for each environment. Making sure they all apply the same migrations in the same order is important to avoid drift and ensuring consistency. Also an issue if you have multiple production databases, e.g. sharding. When you have lots of migrations and you're sure all environments are up to date to a certain point you can "squash" the initial ones to make it apply faster.
    - djrobstep 7y ago
      I don't see the problem. Each branch has its own desired state as necessary. A different database version is going to need a different migration script, no matter what your approach. Only difference is whether you generate those scripts by direct comparison with prod directly or with a crude reconstruction of it.

RussianCow 7y ago

Sorry if I'm misunderstanding, but if the only thing you have is the "current state" SQL file, how do you make automated changes to the schema? The point of migration tools is that making a change is simply a matter of making a new file that lays out that change, and then running the tool. Do you alter the DB manually?

Diggsey 7y ago

Given two snapshots of the schema, there are multiple paths from one to the other. This makes it impossible to generate perfect migrations from the snapshots, you always end up needing manual changes. 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.

djrobstep 7y ago

Yes, we need to move away from the notion of a migration "chain", it's not a very useful way to think about schema changes. Most people have an ideal schema in mind - they should be free to simply craft this directly, with tooling to make the changes necessary to evolve the schema accordingly. 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.

bradstewart 7y ago
> with tooling to make the changes necessary to evolve the schema accordingly 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.
- djrobstep 7y ago
  Yeah most migration tools work this way, although refinery doesn't. 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.
  - irishsultan 7y ago

mjw1007 7y ago

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.

samatman 7y ago
For extra credit, have the last step involve the version control system, so that each migration contains a commit of the last version which contains the old schema, and each version contains a (database | data seed) sufficient to verify the validity of the migration step. So to test from any given migration: - Checkout the code at that point - Apply the migration - Apply tests And repeat as necessary.
0xffff2 7y ago
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.
- genuine_smiles 7y ago
  If you were using rails you would run migrations and then with git check if the schema file has any changes.
- mjw1007 7y ago
  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.

shantly 7y ago

You can derive the current state (in CREATE statements and such) as part of your pre-commit build process if you really want it, no? Or interrogate any active copy of the DB to get it at any time.

mjw1007 7y ago
That's a bit like retrieving source code by pretty-printing a minimised form. It's not terrible, but it's not going to be as pleasant as the original source. 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.
- shantly 7y ago
  I find the format of code that comes from tools deriving CREATEs from live databases to be totally acceptable, usually. Then again I'm the kind of person who prefers team-wide auto-formatting and a strict style linter, so maybe that's just me. > 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. https://www.postgresql.org/docs/9.1/sql-comment.html https://www.postgresql.org/docs/9.1/sql-comment.html
  - mjw1007 7y ago
    I use SQL-level COMMENTs and comments in the schema source for different things. 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.

deleted 7y ago

[deleted]

paulryanrogers 7y ago

Laravel migrations too are diff-only, so I created a package [0] to automatically dump as migrations are run. 0. https://packagist.org/packages/orisintel/laravel-migration-snapshot https://packagist.org/packages/orisintel/laravel-migration-s...