10 ms·

The pedantic checklist for changing your data model in a web application

310 points by rtpg 5y ago

YesThatTom2 5y ago
This is called The McHenry Technique in the book The Practice of Cloud System Administration by Limoncelli/Chalup/Hogan. That chapter lists many techniques for live upgrades.
- aero142 5y ago
  This comment is the top Google result for this term.
janpot 5y ago
With complex migrations, like spanning multiple tables or depending on external APIs, I sometimes use a variant of this. Where instead of migrating the underlying database in "Migration 2", my version 3 would migrate on read: - Read from new representation - If not found: - read X from old representation - migrate X to new representation (complex business logic here) - write migrated datum to new representation - return migrated datum - Either wait long enough until all data has been read at least once or systematically call your API for all records you want migrate (depending on the logic, you might want to throttle this)
eyelovewe 5y ago
It’s a very good topic. I currently use Ansible for deployment. I typically run the equivalent of a db migration as a custom application based upon a proven core, that, after extensive testing on copies of the production db, eventually runs against the production dB, as part of an orchestrated deployment. Verifying individual app nodes behaviors prior to moving onto the next is important, but also knowing the difference between data migrations that break old code vs ones that are agnostic, ak just make new data structures and slowly prune old ones, slowly meaning one or two deployments in the future, but before forgotten
plasma 5y ago
My advice whenever you have large, complex, or slow to perform (eg hours/days) migrations, that will take some time to process, don't put yourself into a position of "we can't go back", until the very last moment, after you have been assured as much as possible things are fine. Things to keep in mind include: * If migrating data to a new column, keep the old data for a while * After migrating the data (with new code writing old/new data), you may want to do an audit query to validate everything was migrated correctly * If you need to mutate data inline in the column, add a new column and just copy the (pre-migrated) data to it as a backup (so worst case you have a pristine copy from before), then drop that column some time later * Runtime config flag in your app that lets you read the new column/truth source as authoritative for a decision, or put the flag back to false to revert it. This could include a percentage so that you slowly creep load onto a new service and build trust in it functioning correctly. * If the data migrated is quite complicated, help yourself to easily switch between old/new data sources for your own manual review in your apps code paths at runtime (eg, render this analytics from Source A or Source B via a runtime flag, query string parameter etc)
- cletus 5y ago
  Did you even read this article? That’s literally what it said.
  - jameshart 5y ago
    The article didn’t mention having a rollback strategy. This post is a useful addition to the discussion.
    - hackerfromthefu 5y ago
      The authors reply to this comment also assumed that the data schema migration will always succeed - by saying the new schema usually 'has more information' that you can reconstruct the original from. All in all the authors writeup and reply is only covering the happy path, not the real world nuance that mistakes or errors can happen!!
amelius 5y ago
Can't this be automated? It seems like such a basic transformation, that a computer should be able to do it.
- biggerfisch 5y ago
  Sure, but automating this requires a lot more tooling. In the case described with multiple, widely spread instances, you'll need to be able to a) Reliably determine which step you are on - this includes both migration steps and application deployments b) Fully automate your deployments from a list of staged code changes ("normal" CD will have issue with this) c) Fully automate migrations _after_ all deployments are complete Those are all totally possible steps, but they are all going to vary based on your setup, making a drop in solution for this process unlikely to be easy. Additionally, these are only the steps if everything works! What happens if there's a bug halfway through? You probably want to stop the deployment process and either revert or quickly hotfix. A human-based system can handle this quite easily, but an automated system will need to be able to measure errors and reliably determine if an issue exists. So its not that this can't be automated, in fact, I know that it has for several large organizations, but the hurdle to get to the full level of automation is rather high and fraught with secondary issues to consider.
- Silhouette 5y ago
  Systematic database migrations do feel complicated, but how would you automate the process in the general case? You need to coordinate changes to two systems: your application code and your database. You can be doing anything with either. You can't update all relevant instances/records instantaneously, so you need to allow for intermediate states where both old and new code or data are in use while the update process itself is running. I think the article here does a pretty good job of describing this and a systematic way of dealing with it. At least you only need that many steps if you're making a breaking change to your existing data model rather than just extending it, which still requires care but is quite a bit simpler IME.
- 0xbadcafebee 5y ago
  Yes. For each step, write code that makes the change, and at the end of the change, updates a record (somewhere; different table, let's say) to note it is compete. Write a test that check if the record is updated, validates the change, and upon validation, updates the record a second time. Make each step depend on the validated value of the previous step. Put them all into cron jobs. They will churn until all the steps are complete and validated. If a step fails, the rest of the steps won't happen. You can also write rollback steps that depend on yet another value written if a step or test fails. And if you're paranoid, you can force the steps to wait for another value to be flipped which you would flip manually, so you can inspect everything before allowing the next step to fire. All of this is complicated, so, try to only write backwards compatible code where the data model never changes, or only ever adds fields, or use feature flags, or some other strategy. There's a book on this stuff somewhere...
globular-toast 5y ago
This is actually really useful to see written down. Perhaps a more complicated example would be useful, like changing the relationships between two or more models. I feel like this kind of thing isn't really taught. I mean, it was a long time ago that I went to uni, so I don't know for sure. But it seems to me like a lot of developers either learn from someone else because they're lucky enough to get a position somewhere where it's already known, or they have to invent it again for themselves. But it seems important enough that it should be an expected part of professional training. Also, the fact the author felt it necessary to call it "pedantic" speaks volumes. Imagine an electrician calling it pedantic to check that the mains has been disconnected or a capacitor has been discharged. There's nothing pedantic about good, formal practice.
- 0xbadcafebee 5y ago
  Our whole field is both constantly changing and (AFAIK) has no formal trade organization like electricians/plumbers. We also aren't licensed like electricians and plumbers. It's just a bunch of academics, and a bunch of haggard people who've been in the trenches, and a lot of random books with great lessons that nobody reads.
- cdelsolar 5y ago
  I agree. On a similar vein I was trying to figure out how best to model something complex recently, what kind of data structures to use, etc, and Google searches don't really help. Where are the books on this kind of stuff for our field? In the end I just invented something decent, but it seems like a missed opportunity.
bouke 5y ago
I believe the checklist is missing adding any optional constraints on the new field. This should be done after deploying version 2 (writing to new field) and backfilling existing records.
wdb 5y ago
I hope the next blog article would discuss how to support multiple versions of APIs like Stripe does :)
cletus 5y ago
One thing I’d add to this is that your database schema and your UI data model and/or API are decoupled so you can update your front end to the new representation before you do any database changes. One thing I liked was the chosen example is changing Booleans to enums. This almost always happens. Your field is almost never a Boolean. This goes for code too. We’ve all read code like: foo(false, false, true, null); This leads to coding standards that all such parameters must be commented and breakages when the parameter list is changed. It also often results in hacks to squeeze a third value into your Boolean (eg Optional<Boolean>). Or, my favorite: @nullable Optional<Boolean> As I like to say: for when three values for your Boolean just aren’t enough. So yeah, my tip for the day is just make any Boolean an enumeration from the outset.
- KptMarchewa 5y ago
  One of my favourites features in Python are named parameters. I even wish Java could be more verbose in that manner.
  - vbsteven 5y ago
    If the project permits it, Kotlin fills that gap in Java well. Named parameters + defaults + nullability is a nice combination to have when it comes to evolving functions or types with backwards compatibility in mind.
  - cletus 5y ago
    I go back and forth on Python in this regard, specifically the named vs positional arguments in that an argument can be one or the other. Part of me prefers just using an anonymous object in Javascript. Typescript is also cleaner than that. Day to day I use Hack (FB's PHP fork), which has a type system and shapes, which are essentially anonymous objects in the JS/TS sense but a little more awkward syntactically: function foo(shape( 'a' => int, 'b' => int, ?'c' => int, )): void; foo(shape('a' => 1, 'b' => 2)); But with Python you sort of have this awkward mix of args and kwargs.
    - 5y ago
zeckalpha 5y ago
Instead of an error prone human checklist, has anyone made frameworks with these sequenced structural migrations in mind? I’ve worked with both Rails migrations and Alembic, but they seem a layer of abstraction too low to only allow safe migrations.
- YesThatTom2 5y ago
  This is what the humans need to do. It works with any framework or database. You might find some software frameworks that make this easier than others but expecting to find a technology that replaces human methodology is like reading a book on Scrum and saying “can’t we make a software system that does this for us?”
  - zeckalpha 5y ago
    No. Then we shouldn’t be using programming languages. I have learned to trust humans to not get migrations right. The system can have guardrails for certain structures of migrations.
- 0xbadcafebee 5y ago
  Not a framework, but schemaless key-value stores allow you to play fast and loose and avoid a lot of the reason for migrations, with obvious downsides. Monorepos also allow you to change and deploy all of the code at once without having to coordinate versions of both dependencies and deployments.
  - dgb23 5y ago
    Also using a key value store or some other no-sql non-graph storage can sometimes be put between two structured migration endpoints. The advantage is that you have a clear way to first pull things apart before you put them together again and an intermediate step that is durable and easy to analyze. But I think the article doesn't talk about full data migrations or integrations of two very distinct models, but rather about incremental, on the fly migrations on production databases, so the above doesn't apply well I think.
  - 5y ago
davidkunz 5y ago
(Disclaimer: I work here) The SAP Cloud Application Programming Model (CAP) let's you derive your DB schema and UI from one semantic data model. The runtime framework interprets this and provides (deep) CRUD functionality out of the box. Business logic can then be inserted using different request handlers. Therefore you have (depending on additional business logic) usually only one place to define your model. https://cap.cloud.sap/docs/ https://cap.cloud.sap/docs/
- mustardo 5y ago
  Wow such spam, and SAP wow... What trash
- jaywalk 5y ago
  You're clearly not a spammer so I want to go easy on you, but nobody on HN is looking for a SAP sales pitch. I can promise you that.
  - davidkunz 5y ago
    I'm sorry, it wasn't meant to be a sales pitch (it's free to use anyway), I just wanted to show another way to deal with the "you change your database model, now you have to change many other places too" problem.
    - cromulent 5y ago
      I didn't think you were shilling, but I was a little disappointed not to get a deep link to the docs for that specific capability :)
      - davidkunz 5y ago
        It's all built around that feature. You define your model using the language CDS (core data services), after compilation, you'll get a JSON representing the model (used by the generic runtime, either Node.js or Java), table/view SQL statements for the databases SQLite/HANA and OData metadata used by generic UIs. That means you semantically define your model and get all the artifacts needed to run the application. With generic support for most "enterprise qualities" like authorization, localization, draft support, extensibility etc. https://cap.cloud.sap/docs/get-started/in-a-nutshell https://cap.cloud.sap/docs/get-started/in-a-nutshell gives a first glimpse. But again, I don't want to advertise. Like everything else, this approach has pros and cons, just wanted to give some insights how we dealt with that problem.
slver 5y ago
The fact the author was kidding by writing "you can't have (planned) downtime" in itself shows everything has downtime. So if your app can have downtime, why not have some planned downtime as well? Oh the users can't take it, they'll go away, yadda yadda. That's usually BS. I've witnessed giant sites with massive traffic that go offline every night. Apple's own web store goes offline before updates every time (most valuable brand, looks like this doesn't affect them huh?). Every marketing exchange also goes offline every day and weekend (except the fancy cryptocoin ones I guess). My overall point is, keep things simple. Planned downtime can vastly simplify your update deployment, and planned downtime can result in less unplanned downtime. So don't give it up for cargo cult reasons. You're not Google Search until proven otherwise. Also you can have planned downtime piecemeal. If your data is sharded in some way (for example by user) you can update the user when they log out and you'll seemingly have no global downtime. But downtime is inherent to well working systems. You sleep every night. If you don't things go haywire.
- 0xbadcafebee 5y ago
  I don't want to have to stay up late to do it. I don't want to have to deal with a rollback. I, and the developer, want to be able to make changes when we feel like it, not based on a schedule. The developer does their work at their pace, I go about my work without thinking about their deployment. Apps with planned downtime are often poorly designed and have other problems, usually due to nobody taking the time to make them work better or fix tech debt. Downtime deployments can lead to huge changes which, if not completed successfully, can lead to much longer downtimes in the event of a rollback. And, yeah, I don't want my customer to see any downtime or errors. Call me fussy, but I want our product to work better than everyone else's. It's 2021. We may not have flying cars, but we do have zero downtime deployments, sometimes. Let's not go back to the dark ages please.
  - slver 5y ago
    Unfortunately "it's CURRENT_YEAR" and "downtime means you're poorly designed" are very poor, circular arguments. The only thing you said back which I can trace back to an objective problem is that you don't want to stay late.
deleted 5y ago
[deleted]
alkonaut 5y ago
Before dropping the old data, what is the simplest way of making sure it's not used? As the article says "this is hard to roll back". Is it as simple as keeping the data around, but e.g. renaming columns to "columnname_deprecated_yyyy_MM_dd" and then in the unlikely event that something starts failing, you can at least rename it back to the old name while putting out that fire?
- athenot 5y ago
  That's a good precaution. Another one might be to log queries and check for use of that old column. 2 caveats: (1) ensure this logging doesn't cause performance bottleneck and (2) this might not catch queries that use globs (ie. "select * from ..."). For (1) you can do this one only some of your instances of your DB cluster and (2), try to always use explicit column references.
  - alkonaut 5y ago
    Are there any databases that support per-column security so you can fail reads (including globs)?
- mleonhard 5y ago
  This works for API field deprecation, too.
LukeEF 5y ago
Managing Databases in a CI/CD pipeline is frankly difficult. Applications are developed continuously, different versions of the application need to run in test, dev and staging environments, each of which has different data and a different schema. This makes for a difficult set of challenges in the set up of each environment, and the reliable promotion of software from one environment to the next. Staging environments need copies of production data in order to test things properly. They also need all the new data and schema changes that are required to make the new version of the software work. So now you’re moving data and schema changes back and forth, up and down the pipeline. But unlike with software source code, you do not have Git to clone, branch and merge your changes. As applications become more data-centric and designers create design systems with all kinds of amazing reactive components for devs to use in their applications, each of these components has fields and each of these fields have data, and this data needs to be stored in a database, and this database needs to updated every time new components are added, modified, or used in new ways. We should manage the revision of data the way we manage the revision of our source code. We should merge our latest changes into the production database. We should simply clone our production database for staging, we should just push changes from a developer’s local machines to a test environment!
- pottertheotter 5y ago
  I've not looked into it a ton, but would something like Pachyderm address this?
  - LukeEF 5y ago
    Yes - pachyderm is good for managing and running distributed data pipelines on flat files There are also versioned databases like TerminusDB and Dolt that try to do similar in the database
- jimpudar 5y ago
  Sometimes you can't use production data in your staging environment, like when prevented by privacy laws. Often times developers who would not have access to production data would have permissions to view data in a staging environment... Creating tools to produce realistic test data and also using fuzzing tools can be just as good and sometimes even better than using actual production data.
bthecohen 5y ago
This is a very useful guide. I'd add one additional step that can sometimes be useful if you're doing a more complex change than adding a field to an object. For example, sometimes you may want to read from an entirely new schema or data source, while ensuring that the user-facing behavior stays the same. In that case, you'd want to add a step between steps 2 and 3 where you double-read, either inline or within a shadow workload, and add telemetry to alert you if there are discrepancies between the two versions. Only when you're confident that the two data models are producing acceptably equivalent results would you cut over the primary reads to the new model. This pattern often comes to play when you break off a piece of state from a monolithic database into a dedicated service.
deleted 5y ago
[deleted]
jkingsbery 5y ago
For whatever it's worth, a pretty good book that covers this in a lot of detail is https://databaserefactoring.com/ https://databaserefactoring.com/. One of the authors had several notes on his website about this prior to putting them into a book: http://www.ambysoft.com/books/agileDatabaseTechniques.html http://www.ambysoft.com/books/agileDatabaseTechniques.html
candu 5y ago
As stated in the post: these steps make complete sense if you're aiming for a nice, smooth zero-downtime transition from the old data model to the new. However, if you can get away with some downtime, you can instead notify your users of a planned outage, shutdown, update everything, and startup again. This eliminates several steps of the process, which has the added benefit of reducing the number of places where something can go wrong. To put this in context: say you guarantee 99.9% uptime - that's still 43.2 min / month of downtime available to you! You can update quite a lot of machines / database rows in even 10-20 min, making this viable in many cases. (Obviously there is a scale threshold past which this is no longer viable, but if you're at that threshold you can probably also spare the engineering effort to go the zero-downtime route!)
- Tainnor 5y ago
  There's also some DB updates that, even if strictly speaking non-backwards compatible, run in just a matter of seconds. Additionally, in postgres you can set statement_timeout, which should abort any query that takes too long. If you have a reasonable deployment strategy, then the chance that someone will talk to your application in the exact moment that the app is in some invalid state, might be quite small.
- jchook 5y ago
  One upside to writing an intermediate version that supports both the old and new representation: your data migrations are not an urgent race against the clock, or mission critical to the release. You can decouple migrations from the release, and rest easy knowing your app is working without praying that a massive migration happens successfully and promptly. Also since your UI or admin Interface likely lets you inspect your data in either representation, you can more naturally verify up/down migrations work in staging.
- Sebb767 5y ago
  > This eliminates several steps of the process, which has the added benefit of reducing the number of places where something can go wrong. It actually also removes some advantages. The author fails to point that out, but the advantage of these small steps is that you can roll back to the previous version at any time without loosing writes. If you find a production bug that does not impact data consistency, you can go back one version without loosing the writes that happened while the new version was up. > To put this in context: say you guarantee 99.9% uptime - that's still 43.2 min / month of downtime available to you! On the other hand, there might be a fatal bug at some point and you will need 3 hours of leeway to get up & running again. Shutting down is surely the easier method, but the available downtime is usually not free real estate for updates.

310 points by rtpg 5y ago

This is called The McHenry Technique in the book The Practice of Cloud System Administration by Limoncelli/Chalup/Hogan. That chapter lists many techniques for live upgrades.

aero142 5y ago
This comment is the top Google result for this term.

janpot 5y ago

With complex migrations, like spanning multiple tables or depending on external APIs, I sometimes use a variant of this. Where instead of migrating the underlying database in "Migration 2", my version 3 would migrate on read: - Read from new representation - If not found: - read X from old representation - migrate X to new representation (complex business logic here) - write migrated datum to new representation - return migrated datum - Either wait long enough until all data has been read at least once or systematically call your API for all records you want migrate (depending on the logic, you might want to throttle this)

eyelovewe 5y ago

It’s a very good topic. I currently use Ansible for deployment. I typically run the equivalent of a db migration as a custom application based upon a proven core, that, after extensive testing on copies of the production db, eventually runs against the production dB, as part of an orchestrated deployment. Verifying individual app nodes behaviors prior to moving onto the next is important, but also knowing the difference between data migrations that break old code vs ones that are agnostic, ak just make new data structures and slowly prune old ones, slowly meaning one or two deployments in the future, but before forgotten

plasma 5y ago

My advice whenever you have large, complex, or slow to perform (eg hours/days) migrations, that will take some time to process, don't put yourself into a position of "we can't go back", until the very last moment, after you have been assured as much as possible things are fine. Things to keep in mind include: * If migrating data to a new column, keep the old data for a while * After migrating the data (with new code writing old/new data), you may want to do an audit query to validate everything was migrated correctly * If you need to mutate data inline in the column, add a new column and just copy the (pre-migrated) data to it as a backup (so worst case you have a pristine copy from before), then drop that column some time later * Runtime config flag in your app that lets you read the new column/truth source as authoritative for a decision, or put the flag back to false to revert it. This could include a percentage so that you slowly creep load onto a new service and build trust in it functioning correctly. * If the data migrated is quite complicated, help yourself to easily switch between old/new data sources for your own manual review in your apps code paths at runtime (eg, render this analytics from Source A or Source B via a runtime flag, query string parameter etc)

cletus 5y ago
Did you even read this article? That’s literally what it said.
- jameshart 5y ago
  The article didn’t mention having a rollback strategy. This post is a useful addition to the discussion.
  - hackerfromthefu 5y ago
    The authors reply to this comment also assumed that the data schema migration will always succeed - by saying the new schema usually 'has more information' that you can reconstruct the original from. All in all the authors writeup and reply is only covering the happy path, not the real world nuance that mistakes or errors can happen!!

amelius 5y ago

Can't this be automated? It seems like such a basic transformation, that a computer should be able to do it.

biggerfisch 5y ago
Sure, but automating this requires a lot more tooling. In the case described with multiple, widely spread instances, you'll need to be able to a) Reliably determine which step you are on - this includes both migration steps and application deployments b) Fully automate your deployments from a list of staged code changes ("normal" CD will have issue with this) c) Fully automate migrations _after_ all deployments are complete Those are all totally possible steps, but they are all going to vary based on your setup, making a drop in solution for this process unlikely to be easy. Additionally, these are only the steps if everything works! What happens if there's a bug halfway through? You probably want to stop the deployment process and either revert or quickly hotfix. A human-based system can handle this quite easily, but an automated system will need to be able to measure errors and reliably determine if an issue exists. So its not that this can't be automated, in fact, I know that it has for several large organizations, but the hurdle to get to the full level of automation is rather high and fraught with secondary issues to consider.
Silhouette 5y ago
Systematic database migrations do feel complicated, but how would you automate the process in the general case? You need to coordinate changes to two systems: your application code and your database. You can be doing anything with either. You can't update all relevant instances/records instantaneously, so you need to allow for intermediate states where both old and new code or data are in use while the update process itself is running. I think the article here does a pretty good job of describing this and a systematic way of dealing with it. At least you only need that many steps if you're making a breaking change to your existing data model rather than just extending it, which still requires care but is quite a bit simpler IME.
0xbadcafebee 5y ago
Yes. For each step, write code that makes the change, and at the end of the change, updates a record (somewhere; different table, let's say) to note it is compete. Write a test that check if the record is updated, validates the change, and upon validation, updates the record a second time. Make each step depend on the validated value of the previous step. Put them all into cron jobs. They will churn until all the steps are complete and validated. If a step fails, the rest of the steps won't happen. You can also write rollback steps that depend on yet another value written if a step or test fails. And if you're paranoid, you can force the steps to wait for another value to be flipped which you would flip manually, so you can inspect everything before allowing the next step to fire. All of this is complicated, so, try to only write backwards compatible code where the data model never changes, or only ever adds fields, or use feature flags, or some other strategy. There's a book on this stuff somewhere...

globular-toast 5y ago

This is actually really useful to see written down. Perhaps a more complicated example would be useful, like changing the relationships between two or more models. I feel like this kind of thing isn't really taught. I mean, it was a long time ago that I went to uni, so I don't know for sure. But it seems to me like a lot of developers either learn from someone else because they're lucky enough to get a position somewhere where it's already known, or they have to invent it again for themselves. But it seems important enough that it should be an expected part of professional training. Also, the fact the author felt it necessary to call it "pedantic" speaks volumes. Imagine an electrician calling it pedantic to check that the mains has been disconnected or a capacitor has been discharged. There's nothing pedantic about good, formal practice.

0xbadcafebee 5y ago
Our whole field is both constantly changing and (AFAIK) has no formal trade organization like electricians/plumbers. We also aren't licensed like electricians and plumbers. It's just a bunch of academics, and a bunch of haggard people who've been in the trenches, and a lot of random books with great lessons that nobody reads.
cdelsolar 5y ago
I agree. On a similar vein I was trying to figure out how best to model something complex recently, what kind of data structures to use, etc, and Google searches don't really help. Where are the books on this kind of stuff for our field? In the end I just invented something decent, but it seems like a missed opportunity.

bouke 5y ago

I believe the checklist is missing adding any optional constraints on the new field. This should be done after deploying version 2 (writing to new field) and backfilling existing records.

wdb 5y ago

I hope the next blog article would discuss how to support multiple versions of APIs like Stripe does :)

cletus 5y ago

One thing I’d add to this is that your database schema and your UI data model and/or API are decoupled so you can update your front end to the new representation before you do any database changes. One thing I liked was the chosen example is changing Booleans to enums. This almost always happens. Your field is almost never a Boolean. This goes for code too. We’ve all read code like: foo(false, false, true, null); This leads to coding standards that all such parameters must be commented and breakages when the parameter list is changed. It also often results in hacks to squeeze a third value into your Boolean (eg Optional<Boolean>). Or, my favorite: @nullable Optional<Boolean> As I like to say: for when three values for your Boolean just aren’t enough. So yeah, my tip for the day is just make any Boolean an enumeration from the outset.

KptMarchewa 5y ago
One of my favourites features in Python are named parameters. I even wish Java could be more verbose in that manner.
- vbsteven 5y ago
  If the project permits it, Kotlin fills that gap in Java well. Named parameters + defaults + nullability is a nice combination to have when it comes to evolving functions or types with backwards compatibility in mind.
- cletus 5y ago
  I go back and forth on Python in this regard, specifically the named vs positional arguments in that an argument can be one or the other. Part of me prefers just using an anonymous object in Javascript. Typescript is also cleaner than that. Day to day I use Hack (FB's PHP fork), which has a type system and shapes, which are essentially anonymous objects in the JS/TS sense but a little more awkward syntactically: function foo(shape( 'a' => int, 'b' => int, ?'c' => int, )): void; foo(shape('a' => 1, 'b' => 2)); But with Python you sort of have this awkward mix of args and kwargs.
  - 5y ago

zeckalpha 5y ago

Instead of an error prone human checklist, has anyone made frameworks with these sequenced structural migrations in mind? I’ve worked with both Rails migrations and Alembic, but they seem a layer of abstraction too low to only allow safe migrations.

YesThatTom2 5y ago
This is what the humans need to do. It works with any framework or database. You might find some software frameworks that make this easier than others but expecting to find a technology that replaces human methodology is like reading a book on Scrum and saying “can’t we make a software system that does this for us?”
- zeckalpha 5y ago
  No. Then we shouldn’t be using programming languages. I have learned to trust humans to not get migrations right. The system can have guardrails for certain structures of migrations.
0xbadcafebee 5y ago
Not a framework, but schemaless key-value stores allow you to play fast and loose and avoid a lot of the reason for migrations, with obvious downsides. Monorepos also allow you to change and deploy all of the code at once without having to coordinate versions of both dependencies and deployments.
- dgb23 5y ago
  Also using a key value store or some other no-sql non-graph storage can sometimes be put between two structured migration endpoints. The advantage is that you have a clear way to first pull things apart before you put them together again and an intermediate step that is durable and easy to analyze. But I think the article doesn't talk about full data migrations or integrations of two very distinct models, but rather about incremental, on the fly migrations on production databases, so the above doesn't apply well I think.
- 5y ago

davidkunz 5y ago

(Disclaimer: I work here) The SAP Cloud Application Programming Model (CAP) let's you derive your DB schema and UI from one semantic data model. The runtime framework interprets this and provides (deep) CRUD functionality out of the box. Business logic can then be inserted using different request handlers. Therefore you have (depending on additional business logic) usually only one place to define your model. https://cap.cloud.sap/docs/ https://cap.cloud.sap/docs/

mustardo 5y ago
Wow such spam, and SAP wow... What trash
jaywalk 5y ago
You're clearly not a spammer so I want to go easy on you, but nobody on HN is looking for a SAP sales pitch. I can promise you that.
- davidkunz 5y ago
  I'm sorry, it wasn't meant to be a sales pitch (it's free to use anyway), I just wanted to show another way to deal with the "you change your database model, now you have to change many other places too" problem.
  - cromulent 5y ago
    I didn't think you were shilling, but I was a little disappointed not to get a deep link to the docs for that specific capability :)
    - davidkunz 5y ago
      It's all built around that feature. You define your model using the language CDS (core data services), after compilation, you'll get a JSON representing the model (used by the generic runtime, either Node.js or Java), table/view SQL statements for the databases SQLite/HANA and OData metadata used by generic UIs. That means you semantically define your model and get all the artifacts needed to run the application. With generic support for most "enterprise qualities" like authorization, localization, draft support, extensibility etc. https://cap.cloud.sap/docs/get-started/in-a-nutshell https://cap.cloud.sap/docs/get-started/in-a-nutshell gives a first glimpse. But again, I don't want to advertise. Like everything else, this approach has pros and cons, just wanted to give some insights how we dealt with that problem.

slver 5y ago

The fact the author was kidding by writing "you can't have (planned) downtime" in itself shows everything has downtime. So if your app can have downtime, why not have some planned downtime as well? Oh the users can't take it, they'll go away, yadda yadda. That's usually BS. I've witnessed giant sites with massive traffic that go offline every night. Apple's own web store goes offline before updates every time (most valuable brand, looks like this doesn't affect them huh?). Every marketing exchange also goes offline every day and weekend (except the fancy cryptocoin ones I guess). My overall point is, keep things simple. Planned downtime can vastly simplify your update deployment, and planned downtime can result in less unplanned downtime. So don't give it up for cargo cult reasons. You're not Google Search until proven otherwise. Also you can have planned downtime piecemeal. If your data is sharded in some way (for example by user) you can update the user when they log out and you'll seemingly have no global downtime. But downtime is inherent to well working systems. You sleep every night. If you don't things go haywire.

0xbadcafebee 5y ago
I don't want to have to stay up late to do it. I don't want to have to deal with a rollback. I, and the developer, want to be able to make changes when we feel like it, not based on a schedule. The developer does their work at their pace, I go about my work without thinking about their deployment. Apps with planned downtime are often poorly designed and have other problems, usually due to nobody taking the time to make them work better or fix tech debt. Downtime deployments can lead to huge changes which, if not completed successfully, can lead to much longer downtimes in the event of a rollback. And, yeah, I don't want my customer to see any downtime or errors. Call me fussy, but I want our product to work better than everyone else's. It's 2021. We may not have flying cars, but we do have zero downtime deployments, sometimes. Let's not go back to the dark ages please.
- slver 5y ago
  Unfortunately "it's CURRENT_YEAR" and "downtime means you're poorly designed" are very poor, circular arguments. The only thing you said back which I can trace back to an objective problem is that you don't want to stay late.

deleted 5y ago

[deleted]

alkonaut 5y ago

Before dropping the old data, what is the simplest way of making sure it's not used? As the article says "this is hard to roll back". Is it as simple as keeping the data around, but e.g. renaming columns to "columnname_deprecated_yyyy_MM_dd" and then in the unlikely event that something starts failing, you can at least rename it back to the old name while putting out that fire?

athenot 5y ago
That's a good precaution. Another one might be to log queries and check for use of that old column. 2 caveats: (1) ensure this logging doesn't cause performance bottleneck and (2) this might not catch queries that use globs (ie. "select * from ..."). For (1) you can do this one only some of your instances of your DB cluster and (2), try to always use explicit column references.
- alkonaut 5y ago
  Are there any databases that support per-column security so you can fail reads (including globs)?
mleonhard 5y ago
This works for API field deprecation, too.

LukeEF 5y ago

Managing Databases in a CI/CD pipeline is frankly difficult. Applications are developed continuously, different versions of the application need to run in test, dev and staging environments, each of which has different data and a different schema. This makes for a difficult set of challenges in the set up of each environment, and the reliable promotion of software from one environment to the next. Staging environments need copies of production data in order to test things properly. They also need all the new data and schema changes that are required to make the new version of the software work. So now you’re moving data and schema changes back and forth, up and down the pipeline. But unlike with software source code, you do not have Git to clone, branch and merge your changes. As applications become more data-centric and designers create design systems with all kinds of amazing reactive components for devs to use in their applications, each of these components has fields and each of these fields have data, and this data needs to be stored in a database, and this database needs to updated every time new components are added, modified, or used in new ways. We should manage the revision of data the way we manage the revision of our source code. We should merge our latest changes into the production database. We should simply clone our production database for staging, we should just push changes from a developer’s local machines to a test environment!

pottertheotter 5y ago
I've not looked into it a ton, but would something like Pachyderm address this?
- LukeEF 5y ago
  Yes - pachyderm is good for managing and running distributed data pipelines on flat files There are also versioned databases like TerminusDB and Dolt that try to do similar in the database
jimpudar 5y ago
Sometimes you can't use production data in your staging environment, like when prevented by privacy laws. Often times developers who would not have access to production data would have permissions to view data in a staging environment... Creating tools to produce realistic test data and also using fuzzing tools can be just as good and sometimes even better than using actual production data.

bthecohen 5y ago

This is a very useful guide. I'd add one additional step that can sometimes be useful if you're doing a more complex change than adding a field to an object. For example, sometimes you may want to read from an entirely new schema or data source, while ensuring that the user-facing behavior stays the same. In that case, you'd want to add a step between steps 2 and 3 where you double-read, either inline or within a shadow workload, and add telemetry to alert you if there are discrepancies between the two versions. Only when you're confident that the two data models are producing acceptably equivalent results would you cut over the primary reads to the new model. This pattern often comes to play when you break off a piece of state from a monolithic database into a dedicated service.

deleted 5y ago

[deleted]

jkingsbery 5y ago

For whatever it's worth, a pretty good book that covers this in a lot of detail is https://databaserefactoring.com/ https://databaserefactoring.com/. One of the authors had several notes on his website about this prior to putting them into a book: http://www.ambysoft.com/books/agileDatabaseTechniques.html http://www.ambysoft.com/books/agileDatabaseTechniques.html

candu 5y ago

As stated in the post: these steps make complete sense if you're aiming for a nice, smooth zero-downtime transition from the old data model to the new. However, if you can get away with some downtime, you can instead notify your users of a planned outage, shutdown, update everything, and startup again. This eliminates several steps of the process, which has the added benefit of reducing the number of places where something can go wrong. To put this in context: say you guarantee 99.9% uptime - that's still 43.2 min / month of downtime available to you! You can update quite a lot of machines / database rows in even 10-20 min, making this viable in many cases. (Obviously there is a scale threshold past which this is no longer viable, but if you're at that threshold you can probably also spare the engineering effort to go the zero-downtime route!)

Tainnor 5y ago
There's also some DB updates that, even if strictly speaking non-backwards compatible, run in just a matter of seconds. Additionally, in postgres you can set statement_timeout, which should abort any query that takes too long. If you have a reasonable deployment strategy, then the chance that someone will talk to your application in the exact moment that the app is in some invalid state, might be quite small.
jchook 5y ago
One upside to writing an intermediate version that supports both the old and new representation: your data migrations are not an urgent race against the clock, or mission critical to the release. You can decouple migrations from the release, and rest easy knowing your app is working without praying that a massive migration happens successfully and promptly. Also since your UI or admin Interface likely lets you inspect your data in either representation, you can more naturally verify up/down migrations work in staging.
Sebb767 5y ago
> This eliminates several steps of the process, which has the added benefit of reducing the number of places where something can go wrong. It actually also removes some advantages. The author fails to point that out, but the advantage of these small steps is that you can roll back to the previous version at any time without loosing writes. If you find a production bug that does not impact data consistency, you can go back one version without loosing the writes that happened while the new version was up. > To put this in context: say you guarantee 99.9% uptime - that's still 43.2 min / month of downtime available to you! On the other hand, there might be a fatal bug at some point and you will need 3 hours of leeway to get up & running again. Shutting down is surely the easier method, but the available downtime is usually not free real estate for updates.