5 ms·

This is a really important piece for developers to read. We get very used to the ability to blow away everything and start from scratch, and that's very often

by ben509 8y ago

This is a really important piece for developers to read. We get very used to the ability to blow away everything and start from scratch, and that's very often the right answer for build tools, but the database is fundamentally different from that, and you need to internalize this. A database, in most cases, is the business. It's essentially a collection of all the contracts made with customers, and your mode of thinking "I can blow this away and start over" that you've learned from all sorts of build tools is completely contrary to that. An infamous example of this is in Active Record migrations[1], and possibly some other migration tools. > Note that we define the change that we want to happen moving forward in time. Before this migration is run, there will be no table. After, the table will exist. Active Record knows how to reverse this migration as well: if we roll this migration back, it will remove the table. These are smart people, and indeed there are some rare cases where this is the right thing to do. But only our strongly ingrained belief that data is ephemeral would cause us to think this should be the normal behavior, that we'd want to blow away customer data to force the database to track what version X of the code thinks it should. [1] http://edgeguides.rubyonrails.org/active_record_migrations.html http://edgeguides.rubyonrails.org/active_record_migrations.h...

garmaine 8y ago
Well, in an ideal world applying an active record migration backward and then forward again should be a no-op to database contents. This would require storing removed columns or tables somewhere, but there's no reason that couldn't be done by the ORM. Almost nobody does this, but perhaps they should and ORMs should make it easy to do or even the default.
- ben509 8y ago
  > This would require storing removed columns or tables somewhere... Simpler, you have an underlying set of base tables, and each "migration" is simply creating a new schema with a set of views / stored procedures over the base tables. Then your migrations will be forward only for the base tables, and constructing a new schema of views over them for the new version. Older versions of the app continue reading their original views. And once you've decided a version has been deprecated long enough and any legacy data or indices can be safely removed, you'd move the earliest valid version forward. That approach, of course, means you have to be able to come up with a view of the data consistent with the existing base tables, of course, and the base table migrations have to be compatible with the oldest valid schema version. And you run into other sticky questions like adding constraints: if v1 doesn't have a constraint, but v2 does, do we: 1. apply the constraint to both and v1 is now going to have to deal with errors 2. apply the constraint only to v2, and it's sometimes going to see invalid data from v1 3. apply the constraint only to v2, and invalid data will be hidden But it's still doable.
  - ams6110 8y ago
    It's great in theory, but in most databases I've used, if you get too crazy with views your performance goes to crap. The optimizer just can't handle it. Views are great for specific use cases, but if your interaction with the underlying tables is entirely through views I've never had that work really well.
    - garmaine 8y ago
      Chicken and egg problem. It's inefficient because no one does it, not because of fundamental computer science problems. If Active Record et al adopted it, I guarantee you database vendors would make it efficient.
    - ako 8y ago
      I used this approach, views on top of tables for all data access, quite often on oracle 7,8 & 9. Oracle query optimizer can handle it perfectly fine.
- pc86 8y ago
  We should just store the database in a database. Easy peasy.
DanHulton 8y ago
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.
gaius 8y ago
I remember vividly a conversation with a developer whose proposed rollback was that we just move the data to a temporary table. And when our customers call us and ask where their money's gone, do we tell them it's safely in a temporary table?
jchanimal 8y ago
> A database, in most cases, is the business. It's essentially a collection of all the contracts made with customers, and your mode of thinking "I can blow this away and start over" that you've learned from all sorts of build tools is completely contrary to that. Thanks for this metaphor. One of the other big benefits to a database-first design is that as your business becomes more complex, there will inevitably be other applications querying the database. If you don't model your constraints in the database you'll end up with a microservices architecture where everything has to call through facades to talk to the database. If you model your constraints correctly in the database, it's much easier to open access to the data to other applications. The idea of code generating the database schema seems absurd because in all but the first steps of a greenfield app, the code is clueless about what the data means. It's the database's job. One reason I think we are seeing a renaissance of database level access control is the popularity of connecting mobile applications and web clients directly to the database. Even if you don't think it's a good practice, the pressure mobile and single-page-app developers are putting on APIs like Firebase, Cloudant, and my employer FaunaDB to offer rich access control features means developers have more options. Here is a write up about how FaunaDB's security features can be used for common app paradigms. By using the database's security features, you can model things like user consent that are relevant for GDPR. https://blog.fauna.com/data-security-in-the-age-of-cloud-native-apps https://blog.fauna.com/data-security-in-the-age-of-cloud-nat... My point being that if you let the database, not the code, be the source of truth, the benefits multiply.
- paloaltokid 8y ago
  > My point being that if you let the database, not the code, be the source of truth, the benefits multiply. I know it seems like a revelation to many but this should be something beyond an axiom. Code is ephemeral, data is the only thing that has any kind of longevity. But you're right!
  - he0001 8y ago
    But business’ change and then so does your models? And it can change radically. Or different laws forces you to change the whole database, like with GDPR. I do agree with the OP just this reasoning doesn’t resonate with my experience in regards with modeling. Quite frankly, because the data is a result of the business and if it changes the data and the models have to do it too.
dyeje 8y ago
I think you've misunderstood what ActiveRecord migration rollbacks are for. Nobody is 'blowing away' customer data. A couple use cases are: 1. During development. Named that column wrong? Included a column you realize you don't need before you merge that branch? No problem! Just rollback, fix the migration, and do it again. 2. During deployment. It gives you a sane way to rollback a botched deployed.
- nurettin 8y ago
  Not to mention that there are enterprise tools for rolling back version changes including database migrations, and enterprises which enforce their use, so the rollback scripts are essential.
- mmt 8y ago
  > what ActiveRecord migration rollbacks are for Whatever their intended purpose (by the developers, I assume), that has no bearing on how they're actually used. > Nobody is 'blowing away' customer data. That's a remarkable claim. Although it wasn't ActiveRecord specifically, I've seen this happen with a similar migration/rollback mechanism. Granted, it was only a few hours of customer data, and it would not have existed in the first place were it not for the initial upgrade/migration (subsequently rolled back), but the data was accepted and subsequently lost. Good thing there weren't any SLAs (yet). > 2. During deployment. It gives you a sane way to rollback a botched deployed. Even this use case can fall into the data loss trap in my example above. What if nobody realizes the deployment was botched until hours later?

by ben509 8y ago

garmaine 8y ago

Well, in an ideal world applying an active record migration backward and then forward again should be a no-op to database contents. This would require storing removed columns or tables somewhere, but there's no reason that couldn't be done by the ORM. Almost nobody does this, but perhaps they should and ORMs should make it easy to do or even the default.

ben509 8y ago
> This would require storing removed columns or tables somewhere... Simpler, you have an underlying set of base tables, and each "migration" is simply creating a new schema with a set of views / stored procedures over the base tables. Then your migrations will be forward only for the base tables, and constructing a new schema of views over them for the new version. Older versions of the app continue reading their original views. And once you've decided a version has been deprecated long enough and any legacy data or indices can be safely removed, you'd move the earliest valid version forward. That approach, of course, means you have to be able to come up with a view of the data consistent with the existing base tables, of course, and the base table migrations have to be compatible with the oldest valid schema version. And you run into other sticky questions like adding constraints: if v1 doesn't have a constraint, but v2 does, do we: 1. apply the constraint to both and v1 is now going to have to deal with errors 2. apply the constraint only to v2, and it's sometimes going to see invalid data from v1 3. apply the constraint only to v2, and invalid data will be hidden But it's still doable.
- ams6110 8y ago
  It's great in theory, but in most databases I've used, if you get too crazy with views your performance goes to crap. The optimizer just can't handle it. Views are great for specific use cases, but if your interaction with the underlying tables is entirely through views I've never had that work really well.
  - garmaine 8y ago
    Chicken and egg problem. It's inefficient because no one does it, not because of fundamental computer science problems. If Active Record et al adopted it, I guarantee you database vendors would make it efficient.
  - ako 8y ago
    I used this approach, views on top of tables for all data access, quite often on oracle 7,8 & 9. Oracle query optimizer can handle it perfectly fine.
pc86 8y ago
We should just store the database in a database. Easy peasy.

DanHulton 8y ago

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.

gaius 8y ago

I remember vividly a conversation with a developer whose proposed rollback was that we just move the data to a temporary table. And when our customers call us and ask where their money's gone, do we tell them it's safely in a temporary table?

jchanimal 8y ago

> A database, in most cases, is the business. It's essentially a collection of all the contracts made with customers, and your mode of thinking "I can blow this away and start over" that you've learned from all sorts of build tools is completely contrary to that. Thanks for this metaphor. One of the other big benefits to a database-first design is that as your business becomes more complex, there will inevitably be other applications querying the database. If you don't model your constraints in the database you'll end up with a microservices architecture where everything has to call through facades to talk to the database. If you model your constraints correctly in the database, it's much easier to open access to the data to other applications. The idea of code generating the database schema seems absurd because in all but the first steps of a greenfield app, the code is clueless about what the data means. It's the database's job. One reason I think we are seeing a renaissance of database level access control is the popularity of connecting mobile applications and web clients directly to the database. Even if you don't think it's a good practice, the pressure mobile and single-page-app developers are putting on APIs like Firebase, Cloudant, and my employer FaunaDB to offer rich access control features means developers have more options. Here is a write up about how FaunaDB's security features can be used for common app paradigms. By using the database's security features, you can model things like user consent that are relevant for GDPR. https://blog.fauna.com/data-security-in-the-age-of-cloud-native-apps https://blog.fauna.com/data-security-in-the-age-of-cloud-nat... My point being that if you let the database, not the code, be the source of truth, the benefits multiply.

paloaltokid 8y ago
> My point being that if you let the database, not the code, be the source of truth, the benefits multiply. I know it seems like a revelation to many but this should be something beyond an axiom. Code is ephemeral, data is the only thing that has any kind of longevity. But you're right!
- he0001 8y ago
  But business’ change and then so does your models? And it can change radically. Or different laws forces you to change the whole database, like with GDPR. I do agree with the OP just this reasoning doesn’t resonate with my experience in regards with modeling. Quite frankly, because the data is a result of the business and if it changes the data and the models have to do it too.

dyeje 8y ago

I think you've misunderstood what ActiveRecord migration rollbacks are for. Nobody is 'blowing away' customer data. A couple use cases are: 1. During development. Named that column wrong? Included a column you realize you don't need before you merge that branch? No problem! Just rollback, fix the migration, and do it again. 2. During deployment. It gives you a sane way to rollback a botched deployed.

nurettin 8y ago
Not to mention that there are enterprise tools for rolling back version changes including database migrations, and enterprises which enforce their use, so the rollback scripts are essential.
mmt 8y ago
> what ActiveRecord migration rollbacks are for Whatever their intended purpose (by the developers, I assume), that has no bearing on how they're actually used. > Nobody is 'blowing away' customer data. That's a remarkable claim. Although it wasn't ActiveRecord specifically, I've seen this happen with a similar migration/rollback mechanism. Granted, it was only a few hours of customer data, and it would not have existed in the first place were it not for the initial upgrade/migration (subsequently rolled back), but the data was accepted and subsequently lost. Good thing there weren't any SLAs (yet). > 2. During deployment. It gives you a sane way to rollback a botched deployed. Even this use case can fall into the data loss trap in my example above. What if nobody realizes the deployment was botched until hours later?