4 ms·

"Did anybody liked writing data migration? Try explaining value of table migration to some Product Manager of early startup :-)." People are so spoiled this da

by ssijak 9y ago

"Did anybody liked writing data migration? Try explaining value of table migration to some Product Manager of early startup :-)." People are so spoiled this days. What is so hard in writing table migration, it is like a few minutes of your time. And why would some non techy product manager even know that you wrote some migration sql file. It is a minuscule technical detail related to the chosen technology and he will not see any repercussion in the production. But try to explain to him why after some reckless sprints you got many inconsistencies in the user facing app which you will have if you abuse "schemaless" NoSQL but use implicit schemas which change over time without proper checks. And if my product manager will push just for initial speed all the time without 0 time for some basic architecture (and I mean really basic), engineering practices and such, you know really useful stuff, I would leave that place without second thought. It is digging it`s own hole from the beginning. And what is the problem of data migrations? Implicit schemas in MongoDB are much worse of a problem when your data requirements change. With relational databases you at least have integrity checks on the DB level that guard you from much of the possible problems when doing such migrations.

drinchev 9y ago
> What is so hard in writing table migration, it is like a few minutes of your time Wow, I wrote migration script for a couple of table rationalisation and it took me a week, to make it error-prone and to provide a revert option. Not to mention that you usually need to work with production data on your local to test if the migration will work without any errors. And If you happen to have a migration error on your production database. It is a terrible experience, which usually includes downtime. Maybe my lack of experience on writing SQL migrations increased that time, but isn't this the whole point of the OP in terms of dev first.
- ssijak 9y ago
  What you wrote is data migration. Table/database migrations are just DDL scripts which are usually really small on an ongoing project. For example you add 2 columns in a sprint so you have that in the SQL migration file for that sprint.
  - jbergens 9y ago
    A real project usually have a lot of things that changes over time, including how to store the data. Adding columns and tables are basically the only simple things and not even new tables are always easy if they are connected to other tables and you have contraints that means they have to be pre-filled with something from the old database. Other things that can change is splitting one table into multiple table while keeping all data, splitting or joining columns while keeping data and so on. For large datasets this may cause headaches. Any error will also cause a lot of extra work.
  - dorfsmay 9y ago
    Adding a column is easy! What about removing a column? What about if you've decided that you both current and next version of the code to work (then removing a column is a 3 step process, copy to new and use new, skip a version, remove old). Migration can and is often hard.
    - yoz-y 9y ago
      Migration is hard, but I fail to see how something like MongoDB makes this easier. Either you kick the can to the code, or do a migration script. Is there some magic style of coding that allows me to use the old data structures with the new code without having to do any special checks?
- pvg 9y ago
  it took me a week, to make it error-prone Well, there's your problem right there!
- mrighele 9y ago
  Writing SQL migrations is not easy, I agree with that. But I don't think the issue is with SQL: in my opinion migrations _in general_ are difficult to do properly , regardless of the technology, expecially Even when you use a schemaless db like MongoDB, you still have to manage your data (let's say the schema is implicit). If for example you decide that the "phone number" field is no longer a string but a list of objects (containing each the phone number and the kind of number), you will have to update the old data to adapt it to the new model, incurring in the risk of messing up production data. Of course you can (and it is what many people do) avoid the problem at the data level and "fix it" at the application level (for example, checking every time if you have a single string or the new list), but if you really want you can do the same with a SQL database (keep the "phone" field on the table, but add the new table for the list of numbers). A disadvantage of the SQL approach is that many are less versed in it than in the application language, but there are many tools available to handle SQL migrations in your framework's language.
  - candiodari 9y ago
    > Even when you use a schemaless db like MongoDB, you still have to manage your data (let's say the schema is implicit). Yes, the "secret" to MongoDB not having to write data migrations is simple: you get to simply have them as technical debt for later. What could go wrong ? Well, that's easy too: an accounting system where each method has a switch with 10+ cases, each case having 30+ lines of code inside of it, depending on something like "record version" or worse, on implicit tests. And what could be worse ? Well, having a switch with only 9 cases in one of those methods ...
  - rockostrich 9y ago
    > Of course you can (and it is what many people do) avoid the problem at the data level and "fix it" at the application level (for example, checking every time if you have a single string or the new list), but if you really want you can do the same with a SQL database (keep the "phone" field on the table, but add the new table for the list of numbers). This is essentially how my team handles things while using HBase as our main DB. At first it felt weird to not write a migration at the DB level and maintaining compatibility is certainly something that you have to keep in mind at all times, but we have a pretty solid internal library for handling de/serialization in Scala. > A disadvantage of the SQL approach is that many are less versed in it than in the application language, but there are many tools available to handle SQL migrations in your framework's language. I was surprised by this at my previous job. One of the first things I learned about when starting to do web development was database migrations (Miguel Grinberg's Flask tutorial). Sure, I had to learn Alembic when I started the job, but it's a pretty straight forward API and the main issue was the lack of transactional DDL in MySQL that we constantly had to keep in mind. Besides that it was just writing SQL statements wrapped in Python.
threeseed 9y ago
> What is so hard in writing table migration, it is like a few minutes of your time. I have worked on a number of large enterprise projects where there were hundreds and hundreds of migrations over the lifespan. I have never seen it take minutes of time. Or maybe you're just one of those fun developers who never properly tested their work. But I have definitely seen production outages and subsequent failures to rollback because of edge cases between various individual migrations.
- StavrosK 9y ago
  I have worked on tens of projects in the past decade, and I have very rarely seen migrations be anything other than automatic, taking one command to be generated.
  - threeseed 9y ago
    For basic schema changes maybe. But often you are adding to reference data or running a transform SQL in order to produce some new denormalised table or the huge array of changes needed to support new use cases. These can't be automated and are hugely error prone.
    - zaarn 9y ago
      If your Schema is properly setup after the initial changes to it, then it's not error prone at all, it's doing what it's supposed to do. Unless you migration involves "disable all checks, add new columns/tables, move and delete old data, enable all checks" If you are migrating to a denormalised table then it should obviously crash when your conversion tool is doing something wrong and the RDBMS just prevented it from succeeding in doing something wrong. If your migration is actually error prone as in "mangling and corrupting" data I suggest you are doing something wrong and you probably disabled the safety checks in your RDBMS.
    - StavrosK 9y ago
      How do schemaless databases make this easier?
  - 9y ago
candu 9y ago
Sure, it's only a few minutes... ...if you're making a relatively small change to a non-mission-critical, non-distributed system that can be taken offline briefly while you migrate; then you can just shut everything down, migrate, roll new versions, and start up again. If you need to keep everything running while you do a gradual rollout across thousands of DBs, this gets more complicated very quickly - and that's not even taking into account more complex migrations that involve more than just "add / remove this column". You're definitely right, however, to point out that implicit schemas don't make this better: you have all the same problems, but with less documentation / validation helping you not screw things up. It's also a good point that many businesses / applications never reach this latter stage where migrations become much, much more complex; when you can get away with the former approach, you definitely should.
dfsegoat 9y ago
"only a few minutes" - try explaining that to any DBA working in a regulated environment, like healthcare / cGMP: all of our ETL scripts require validation plans & independent review by QA -- nothing touching/persisting data is "a few minutes".
- krylon 9y ago
  But that is an overhead you would face regardless of the database engine. Or did I miss something?
michaelcampbell 9y ago
> What is so hard in writing table migration, it is like a few minutes of your time. The company I work for has 10's of TB's of data on most customer systems, and migration is either impossible as they have no place to store it, or too costly in time to move. So while writing the code is not hard, the difficulty (or even possibility) of field upgrades can vastly influence whether or not a feature is considered.

by ssijak 9y ago

drinchev 9y ago

> What is so hard in writing table migration, it is like a few minutes of your time Wow, I wrote migration script for a couple of table rationalisation and it took me a week, to make it error-prone and to provide a revert option. Not to mention that you usually need to work with production data on your local to test if the migration will work without any errors. And If you happen to have a migration error on your production database. It is a terrible experience, which usually includes downtime. Maybe my lack of experience on writing SQL migrations increased that time, but isn't this the whole point of the OP in terms of dev first.

ssijak 9y ago
What you wrote is data migration. Table/database migrations are just DDL scripts which are usually really small on an ongoing project. For example you add 2 columns in a sprint so you have that in the SQL migration file for that sprint.
- jbergens 9y ago
  A real project usually have a lot of things that changes over time, including how to store the data. Adding columns and tables are basically the only simple things and not even new tables are always easy if they are connected to other tables and you have contraints that means they have to be pre-filled with something from the old database. Other things that can change is splitting one table into multiple table while keeping all data, splitting or joining columns while keeping data and so on. For large datasets this may cause headaches. Any error will also cause a lot of extra work.
- dorfsmay 9y ago
  Adding a column is easy! What about removing a column? What about if you've decided that you both current and next version of the code to work (then removing a column is a 3 step process, copy to new and use new, skip a version, remove old). Migration can and is often hard.
  - yoz-y 9y ago
    Migration is hard, but I fail to see how something like MongoDB makes this easier. Either you kick the can to the code, or do a migration script. Is there some magic style of coding that allows me to use the old data structures with the new code without having to do any special checks?
pvg 9y ago
it took me a week, to make it error-prone Well, there's your problem right there!
mrighele 9y ago
Writing SQL migrations is not easy, I agree with that. But I don't think the issue is with SQL: in my opinion migrations _in general_ are difficult to do properly , regardless of the technology, expecially Even when you use a schemaless db like MongoDB, you still have to manage your data (let's say the schema is implicit). If for example you decide that the "phone number" field is no longer a string but a list of objects (containing each the phone number and the kind of number), you will have to update the old data to adapt it to the new model, incurring in the risk of messing up production data. Of course you can (and it is what many people do) avoid the problem at the data level and "fix it" at the application level (for example, checking every time if you have a single string or the new list), but if you really want you can do the same with a SQL database (keep the "phone" field on the table, but add the new table for the list of numbers). A disadvantage of the SQL approach is that many are less versed in it than in the application language, but there are many tools available to handle SQL migrations in your framework's language.
- candiodari 9y ago
  > Even when you use a schemaless db like MongoDB, you still have to manage your data (let's say the schema is implicit). Yes, the "secret" to MongoDB not having to write data migrations is simple: you get to simply have them as technical debt for later. What could go wrong ? Well, that's easy too: an accounting system where each method has a switch with 10+ cases, each case having 30+ lines of code inside of it, depending on something like "record version" or worse, on implicit tests. And what could be worse ? Well, having a switch with only 9 cases in one of those methods ...
- rockostrich 9y ago
  > Of course you can (and it is what many people do) avoid the problem at the data level and "fix it" at the application level (for example, checking every time if you have a single string or the new list), but if you really want you can do the same with a SQL database (keep the "phone" field on the table, but add the new table for the list of numbers). This is essentially how my team handles things while using HBase as our main DB. At first it felt weird to not write a migration at the DB level and maintaining compatibility is certainly something that you have to keep in mind at all times, but we have a pretty solid internal library for handling de/serialization in Scala. > A disadvantage of the SQL approach is that many are less versed in it than in the application language, but there are many tools available to handle SQL migrations in your framework's language. I was surprised by this at my previous job. One of the first things I learned about when starting to do web development was database migrations (Miguel Grinberg's Flask tutorial). Sure, I had to learn Alembic when I started the job, but it's a pretty straight forward API and the main issue was the lack of transactional DDL in MySQL that we constantly had to keep in mind. Besides that it was just writing SQL statements wrapped in Python.

threeseed 9y ago

> What is so hard in writing table migration, it is like a few minutes of your time. I have worked on a number of large enterprise projects where there were hundreds and hundreds of migrations over the lifespan. I have never seen it take minutes of time. Or maybe you're just one of those fun developers who never properly tested their work. But I have definitely seen production outages and subsequent failures to rollback because of edge cases between various individual migrations.

StavrosK 9y ago
I have worked on tens of projects in the past decade, and I have very rarely seen migrations be anything other than automatic, taking one command to be generated.
- threeseed 9y ago
  For basic schema changes maybe. But often you are adding to reference data or running a transform SQL in order to produce some new denormalised table or the huge array of changes needed to support new use cases. These can't be automated and are hugely error prone.
  - zaarn 9y ago
    If your Schema is properly setup after the initial changes to it, then it's not error prone at all, it's doing what it's supposed to do. Unless you migration involves "disable all checks, add new columns/tables, move and delete old data, enable all checks" If you are migrating to a denormalised table then it should obviously crash when your conversion tool is doing something wrong and the RDBMS just prevented it from succeeding in doing something wrong. If your migration is actually error prone as in "mangling and corrupting" data I suggest you are doing something wrong and you probably disabled the safety checks in your RDBMS.
  - StavrosK 9y ago
    How do schemaless databases make this easier?
- 9y ago

candu 9y ago

Sure, it's only a few minutes... ...if you're making a relatively small change to a non-mission-critical, non-distributed system that can be taken offline briefly while you migrate; then you can just shut everything down, migrate, roll new versions, and start up again. If you need to keep everything running while you do a gradual rollout across thousands of DBs, this gets more complicated very quickly - and that's not even taking into account more complex migrations that involve more than just "add / remove this column". You're definitely right, however, to point out that implicit schemas don't make this better: you have all the same problems, but with less documentation / validation helping you not screw things up. It's also a good point that many businesses / applications never reach this latter stage where migrations become much, much more complex; when you can get away with the former approach, you definitely should.

dfsegoat 9y ago

"only a few minutes" - try explaining that to any DBA working in a regulated environment, like healthcare / cGMP: all of our ETL scripts require validation plans & independent review by QA -- nothing touching/persisting data is "a few minutes".

krylon 9y ago
But that is an overhead you would face regardless of the database engine. Or did I miss something?

michaelcampbell 9y ago

> What is so hard in writing table migration, it is like a few minutes of your time. The company I work for has 10's of TB's of data on most customer systems, and migration is either impossible as they have no place to store it, or too costly in time to move. So while writing the code is not hard, the difficulty (or even possibility) of field upgrades can vastly influence whether or not a feature is considered.