4 ms·

> I have absolutely no idea what a "SQL migration" Transforming data to conform to a new schema. It's a tedious process that could use some automation, but dit

> I have absolutely no idea what a "SQL migration" Transforming data to conform to a new schema. It's a tedious process that could use some automation, but ditching schemas because data migrations are tedious is like ignoring traffic rules because you're in a hurry. > I assume it's just a bogeyman made up by the MongoDB snake-oil salesmen. It's just like dynamic type marketing: “If you never define the structure of your data, you never need to worry about it!” The only problem is that it isn't true.

gaius 10y ago
Oh, that doesn't happen much, because serious organizations have dozens of apps connected to a DB and no-one would be crazy enough to big-bang change them all at once to use a different schema. Adding columns and tables tho' is easy, populating new tables from existing, replacing tables with views, no problem.
- joncrocks 10y ago
  I'd argue the inverse. Serious organisations shouldn't have lots of different apps reading from each other's databases, for the reason you suggest above. It makes changes difficult and the application landscape fragile. It also encourages not having well-documented interdependencies between your apps (and consequently no way of knowing what the impact of any given change to an app will have). It's not uncommon however in large organisations with mature applications, without strict IT policies. Complex applications that are undergoing active changes shouldn't be constrained in this manner, and in that context a SQL migration is more of an in-place upgrade of the data structures that hold the application data between different versions of the application. Adding columns, adding tables, replacing columns with other columns and managing how the data should look in the new schema, based on the 'old' data structures.
  - gaius 10y ago
    They are not "each others" databases tho'. It is "the database", deliberately chosen as the integration point. The alternative is a horrific tangle of replication, or a vast undiscoverable landscape of tiny APIs.
    - NateDad 10y ago
      That sounds horrible. No wonder you've never done a migration - it's been made impossible. Why would you not hide the database behind an API, so that you CAN migrate it, change it, etc? This is like not having a separate data layer in your application, except it's across many applications all at once.
      - gaius 10y ago
        What API do you suggest? Bear in mind it needs to provide the same results and enforce the same constraints for everything from COBOL to Java to Excel to ColdFusion to Python to R and any other future language. I mean you could write your business logic in all of them, and maintain it in all of them, but that would be insane. These are solved problems, have been for decades.
        NateDad 10y ago
        Yes, and the way you solve it is by having an API that holds your business logic. That's how you avoid duplicating business logic in all those applications. Is your database just chock full of insane triggers and functions? Business logic doesn't belong in a database. data does.
        gaius 10y ago
        That API is called ODBC. Or you can rewrite all the third-party apps your organisation uses now and in the future to use your own.
    - alkonaut 10y ago
      Doing large incompatible migrations is unavoidable eventually, so avoiding them doesn't solve the problem either it just postpones the problem until the entire enterprise has to move to an entirely "new system" at fantastic cost. For some reason the old system never really dies so after the multimillion dollar migration the old zombie system still supports some business functions, leading to that replication anyway. Both solutions are unpalatable: "the database is the integration point we can't ever change" is bad and "no apps have a central ground truth database" is also painful. I'd prefer the latter with small APIs and more duplication, than the common enterprise solution with a massive holy unchangable db directly serving apps.
  - PeterisP 10y ago
    In such a scenario, none of the apps "own" the data - the DB is a primary source of truth and consistency is enforced there instead of relying that the code in the many apps will match. Application logic can undergo active changes and refactoring without changes to the data permanent storage, indeed, a 1-to-1 correspondence between DB and application data structures isn't expected. It's just as with backwards compatibility with file formats, changing the application version shouldn't require incompatible in-place changes to the backing data persistence layer - you can need to add some new features, but the old version of the app should work fine with the updated DB.

gaius 10y ago

Oh, that doesn't happen much, because serious organizations have dozens of apps connected to a DB and no-one would be crazy enough to big-bang change them all at once to use a different schema. Adding columns and tables tho' is easy, populating new tables from existing, replacing tables with views, no problem.

joncrocks 10y ago
I'd argue the inverse. Serious organisations shouldn't have lots of different apps reading from each other's databases, for the reason you suggest above. It makes changes difficult and the application landscape fragile. It also encourages not having well-documented interdependencies between your apps (and consequently no way of knowing what the impact of any given change to an app will have). It's not uncommon however in large organisations with mature applications, without strict IT policies. Complex applications that are undergoing active changes shouldn't be constrained in this manner, and in that context a SQL migration is more of an in-place upgrade of the data structures that hold the application data between different versions of the application. Adding columns, adding tables, replacing columns with other columns and managing how the data should look in the new schema, based on the 'old' data structures.
- gaius 10y ago
  They are not "each others" databases tho'. It is "the database", deliberately chosen as the integration point. The alternative is a horrific tangle of replication, or a vast undiscoverable landscape of tiny APIs.
  - NateDad 10y ago
    That sounds horrible. No wonder you've never done a migration - it's been made impossible. Why would you not hide the database behind an API, so that you CAN migrate it, change it, etc? This is like not having a separate data layer in your application, except it's across many applications all at once.
    - gaius 10y ago
      What API do you suggest? Bear in mind it needs to provide the same results and enforce the same constraints for everything from COBOL to Java to Excel to ColdFusion to Python to R and any other future language. I mean you could write your business logic in all of them, and maintain it in all of them, but that would be insane. These are solved problems, have been for decades.
      - NateDad 10y ago
        Yes, and the way you solve it is by having an API that holds your business logic. That's how you avoid duplicating business logic in all those applications. Is your database just chock full of insane triggers and functions? Business logic doesn't belong in a database. data does.
        gaius 10y ago
        That API is called ODBC. Or you can rewrite all the third-party apps your organisation uses now and in the future to use your own.
  - alkonaut 10y ago
    Doing large incompatible migrations is unavoidable eventually, so avoiding them doesn't solve the problem either it just postpones the problem until the entire enterprise has to move to an entirely "new system" at fantastic cost. For some reason the old system never really dies so after the multimillion dollar migration the old zombie system still supports some business functions, leading to that replication anyway. Both solutions are unpalatable: "the database is the integration point we can't ever change" is bad and "no apps have a central ground truth database" is also painful. I'd prefer the latter with small APIs and more duplication, than the common enterprise solution with a massive holy unchangable db directly serving apps.
- PeterisP 10y ago
  In such a scenario, none of the apps "own" the data - the DB is a primary source of truth and consistency is enforced there instead of relying that the code in the many apps will match. Application logic can undergo active changes and refactoring without changes to the data permanent storage, indeed, a 1-to-1 correspondence between DB and application data structures isn't expected. It's just as with backwards compatibility with file formats, changing the application version shouldn't require incompatible in-place changes to the backing data persistence layer - you can need to add some new features, but the old version of the app should work fine with the updated DB.