4 ms·

There are very, very common cases where this is the right thing to do - development. You'll want to be able to check out a branch, apply the migrations, test t

There are very, very common cases where this is the right thing to do - development. You'll want to be able to check out a branch, apply the migrations, test the code, and roll back the migrations before you switch off to another branch all the dang time. When you're limiting yourself to discussion of production environments, absolutely you're correct, rollbacks should be essentially prevented, ideally through tooling that makes them nearly impossible to run, especially without performing a backup first.

mmt 8y ago
I think this idea of development environment exceptionalism is one of the "walls" that the original DevOps movement hoped to remove. Although I agree that for something like rapid prototyping or experimentation or proof-of-concept, it could make sense to have a mechanism for database changes that is even vastly different from what might ever exist in production. However, for any routine development, it's important that those procedures match what happens in production, as a hedge against the day when it's necessary to troubleshoot a production issue involving database interaction and to avoid some of the pitfalls inherent in mismatches in the first place. Coming from the Ops side, I think we Ops folks (including DBAs, to the extent that they exist in smaller environments) need to do more to provide developers with useable, production-like databases that actually match their routine workflows. We may not have quite the same breadth of tools or be able to provide quite same speed/agility in switching branches, but we can get much closer than the status quo.
- pmontra 8y ago
  One way to see it is that the database is a microservice that manages data for all the other microservices and its API is (for example) SQL. After that it should be clear why any of the other microservices should not have the privilege to include the tools to define the data. Unfortunately Rails or Django or any other framework's migrations are so much more convenient compared to maintaining a schema manually. Any database centric migration tool would have to update the models for all the projects using it. That's not easy AFAIK. I'm working at a project in which different teams are writing services in Elixir (Phoenix) and C# (no idea what's in there.) We're using different databases and an HTTP API, so the databases and their schemas are hidden inside every (not so micro) service. We didn't share the database because it would have been a nightmare to mirror changes in both codebases.
  - mmt 8y ago
    > One way to see it is that the database is a microservice that manages data for all the other microservices and its API is (for example) SQL. That's a good point, especially since, in the common (or at least initial) situation, that's how it's approached. However, I'm wary of taking the concept to an extreme [1] in that many modern RDBMSes are very much not "micro". Besides the storage optimizations that the article mentions, modern RDBMSses are capable embedding programmatic business logic (e.g. stored procedures, triggers), but there seems to be a categorical avoidance of using these features by most devs, even if it means moving huge quantities of data to a different microservice (presumably across a network) to perform the processing. > Unfortunately Rails or Django or any other framework's migrations are so much more convenient compared to maintaining a schema manually. The article addresses this by recommending code generation, which is, essentially, the converse of SQL-generation. Is either one more manual than the other? Perhaps the generated SQL seems less manual, because the database, in that situation, isn't a first-class citizen, as it were. A dropped table is, essentially, invisible to the developer. That is, I believe, the author's point, though.. the appearance of convenience is at the expense of actively ignoring pitfalls. > Any database centric migration tool would have to update the models for all the projects using it. That's not easy AFAIK. I'm not sure I follow.. Are you describing one database with multiple "client" applications? If so, it's not clear how an app-centric model would be any easier, with multiple apps all wanting to perform duplicate migrations (or.. duplicated/conflicting rollbacks?). [1] Granted, this could be a critique of the "microservice" model in general
    - pmontra 8y ago
      I try to explain better that last point. Client applications should not migrate the database. That's up to the database, with any tool for for the purpose. I'm ignorant in this matter. Then there is code generation to keep the models up to date. The generation tool should know all the ORMs or whatever used by the clients and modify their models. Saying that this is not mainstream is an understatement. I know some reverse engineering gems for Rails but nothing like that.
      - mmt 8y ago
fjsolwmv 8y ago
Rolling back on dev only is still bad. That is developing and testing against a version of the database that you know will never be prodlike. The dev database should be created by snapshots or forward migrations from scratch.
megaman22 8y ago
I don't think I would be comfortable doing database schema related work in a branch unless I was also essentially branching my database at the same time. Even for my mostly garbage personal developer database that's filled to the brim with nonsense names and keyboard mashing. The chance of breaking something and wasting a large amount of time and data are too great. Of course, Thou Shalt Have Backups, but it's not exactly a frictionless experience if you fry things and have to go back to them. Unfortunately it's not quite as quick and easy to clone off a copy every time you want to run some tests or fiddle with a spike as it is to jump between Git branches.
- paloaltokid 8y ago
  > branching my database This sounds like a great product idea! Someone please do this. :)
  - isotropy 8y ago
    Check out Delphix (https://www.delphix.com https://www.delphix.com). It provides a ZFS-backed copy of your production database per developer using snapshots, with rollback. Disclaimer: a friend worked there, I haven’t used it personally, and it’s not free.
- mmt 8y ago
  > Of course, Thou Shalt Have Backups, but it's not exactly a frictionless experience if you fry things and have to go back to them. I do wonder how much of this is because of, specifically, PostgreSQL not having enough "market" penetration. For a very long time, it's had very good unclean-shutdown resilience with the way it does its write-ahead logs and has even had point-in-time-recovery (using those same WALs, just stored longer) for several years. Combine that with a snapshotting filesystem, and that would seem like a pretty low amount of friction for recovery, without even having formal backups. If your database is big enough, you need a certain amount of performance out of it, or you need it to be a recent enough copy [1] of the production data (which likely means "all of the above"), then it does start to get legitimately challenging. [1] or, worse, something ETL'ed (anonymization comes to mind)
- 8y ago

mmt 8y ago

I think this idea of development environment exceptionalism is one of the "walls" that the original DevOps movement hoped to remove. Although I agree that for something like rapid prototyping or experimentation or proof-of-concept, it could make sense to have a mechanism for database changes that is even vastly different from what might ever exist in production. However, for any routine development, it's important that those procedures match what happens in production, as a hedge against the day when it's necessary to troubleshoot a production issue involving database interaction and to avoid some of the pitfalls inherent in mismatches in the first place. Coming from the Ops side, I think we Ops folks (including DBAs, to the extent that they exist in smaller environments) need to do more to provide developers with useable, production-like databases that actually match their routine workflows. We may not have quite the same breadth of tools or be able to provide quite same speed/agility in switching branches, but we can get much closer than the status quo.

pmontra 8y ago
One way to see it is that the database is a microservice that manages data for all the other microservices and its API is (for example) SQL. After that it should be clear why any of the other microservices should not have the privilege to include the tools to define the data. Unfortunately Rails or Django or any other framework's migrations are so much more convenient compared to maintaining a schema manually. Any database centric migration tool would have to update the models for all the projects using it. That's not easy AFAIK. I'm working at a project in which different teams are writing services in Elixir (Phoenix) and C# (no idea what's in there.) We're using different databases and an HTTP API, so the databases and their schemas are hidden inside every (not so micro) service. We didn't share the database because it would have been a nightmare to mirror changes in both codebases.
- mmt 8y ago
  > One way to see it is that the database is a microservice that manages data for all the other microservices and its API is (for example) SQL. That's a good point, especially since, in the common (or at least initial) situation, that's how it's approached. However, I'm wary of taking the concept to an extreme [1] in that many modern RDBMSes are very much not "micro". Besides the storage optimizations that the article mentions, modern RDBMSses are capable embedding programmatic business logic (e.g. stored procedures, triggers), but there seems to be a categorical avoidance of using these features by most devs, even if it means moving huge quantities of data to a different microservice (presumably across a network) to perform the processing. > Unfortunately Rails or Django or any other framework's migrations are so much more convenient compared to maintaining a schema manually. The article addresses this by recommending code generation, which is, essentially, the converse of SQL-generation. Is either one more manual than the other? Perhaps the generated SQL seems less manual, because the database, in that situation, isn't a first-class citizen, as it were. A dropped table is, essentially, invisible to the developer. That is, I believe, the author's point, though.. the appearance of convenience is at the expense of actively ignoring pitfalls. > Any database centric migration tool would have to update the models for all the projects using it. That's not easy AFAIK. I'm not sure I follow.. Are you describing one database with multiple "client" applications? If so, it's not clear how an app-centric model would be any easier, with multiple apps all wanting to perform duplicate migrations (or.. duplicated/conflicting rollbacks?). [1] Granted, this could be a critique of the "microservice" model in general
  - pmontra 8y ago
    I try to explain better that last point. Client applications should not migrate the database. That's up to the database, with any tool for for the purpose. I'm ignorant in this matter. Then there is code generation to keep the models up to date. The generation tool should know all the ORMs or whatever used by the clients and modify their models. Saying that this is not mainstream is an understatement. I know some reverse engineering gems for Rails but nothing like that.
    - mmt 8y ago

fjsolwmv 8y ago

Rolling back on dev only is still bad. That is developing and testing against a version of the database that you know will never be prodlike. The dev database should be created by snapshots or forward migrations from scratch.

megaman22 8y ago

I don't think I would be comfortable doing database schema related work in a branch unless I was also essentially branching my database at the same time. Even for my mostly garbage personal developer database that's filled to the brim with nonsense names and keyboard mashing. The chance of breaking something and wasting a large amount of time and data are too great. Of course, Thou Shalt Have Backups, but it's not exactly a frictionless experience if you fry things and have to go back to them. Unfortunately it's not quite as quick and easy to clone off a copy every time you want to run some tests or fiddle with a spike as it is to jump between Git branches.

paloaltokid 8y ago
> branching my database This sounds like a great product idea! Someone please do this. :)
- isotropy 8y ago
  Check out Delphix (https://www.delphix.com https://www.delphix.com). It provides a ZFS-backed copy of your production database per developer using snapshots, with rollback. Disclaimer: a friend worked there, I haven’t used it personally, and it’s not free.
mmt 8y ago
> Of course, Thou Shalt Have Backups, but it's not exactly a frictionless experience if you fry things and have to go back to them. I do wonder how much of this is because of, specifically, PostgreSQL not having enough "market" penetration. For a very long time, it's had very good unclean-shutdown resilience with the way it does its write-ahead logs and has even had point-in-time-recovery (using those same WALs, just stored longer) for several years. Combine that with a snapshotting filesystem, and that would seem like a pretty low amount of friction for recovery, without even having formal backups. If your database is big enough, you need a certain amount of performance out of it, or you need it to be a recent enough copy [1] of the production data (which likely means "all of the above"), then it does start to get legitimately challenging. [1] or, worse, something ETL'ed (anonymization comes to mind)
8y ago