3 ms·

Ignoring the rather bonkers anecdote at the start of the article, I'd also argue against one of the other recommendations here, even though it is quite common i

Ignoring the rather bonkers anecdote at the start of the article, I'd also argue against one of the other recommendations here, even though it is quite common in practice: > The command in array index n upgrades the schema from version n-1 to n. > Thus, no matter which version is found, the software can bring the > database to the required schema version. In fact, if an uninitialized > database is found (for example, in a testing environment), > it might loop through dozens of schema changes until it gets to the > newest version. There are 3 major problems with traditional incremental migration strategies: 1. Once you've accumulated years of migrations, it's unnecessarily slow to bring up a new dev/test db. The tool is potentially running many ALTERs against each new table, vs a declarative approach which just runs a single CREATE with the desired final state. 2. The migration tool does not have a way of handling databases that aren't in one of the well-defined versioned states. This can happen easily on dev databases, where a developer just wants to experiment before landing on the final desired migration. 3. Most of these tools require a developer to code (in a specific programming language, or a proprietary DSL) the migration logic, as well as its rollback. There's plenty of room for human error there, especially since most of these tools don't automatically validate that the rollback actually correctly reverts the migration. In contrast, a declarative approach simply tracks the final desired state (a repo containing a bunch of CREATE statements), and the tool figures out how to properly transition from any arbitrary state to the new desired state. This permits the state to be expressed in pure SQL, with no need for a DSL or custom programming, nor any need to define rollback logic. A declarative tool can also be used in an automated reconciliation loop, which can handle situations like a master failing mid-schema-change in a sharded environment. This is analogous to other declarative tools, such as k8s. There are several recent open source projects using the declarative approach for schema management. I'm the author of one, Skeema [1], for MySQL/MariaDB. Others include migra [2] for Postgres, and sqldef [3] for MySQL or Postgres. Although declarative schema management is only recently picking up adoption, it isn't a new approach. Facebook has been handling their schema changes using a declarative approach for over 8 years now. [1] https://github.com/skeema/skeema https://github.com/skeema/skeema [2] https://github.com/djrobstep/migra https://github.com/djrobstep/migra [3] https://github.com/k0kubun/sqldef https://github.com/k0kubun/sqldef

tracker1 8y ago
1. This burden is only for new ground-up environments... you can turn to snapshots/backups at specific points and start from that instead of running all migrations from the start. 2. This is where having a ground-up script is helpful. If you can destroy/recreate a local dockerized db (as an example, if not specifically) that's far less of an issue. 3. Developers writing code against a SQL database, should understand the variant of SQL being used. This is also where things like code reviews can minimize the issues. I'd also suggest that issues would be caught in QA or UAT before production providing decent test coverage prior to production release. As for your contrasting approach, what about the example in TFA where you go from a composite Name field into separate FirstName, LastName fields?
- evanelias 8y ago
  1. True, but it depends on the size of your data set. If you just want the tables but not the full prod data, backups may not be usable as-is for this purpose. 2. What if you want to capture the set of local dev changes, rather then destroying and starting over? Most migration tools that I've seen don't have diff logic built-in, so they have no way of saying "take whatever I did to the dev db, and capture that state in the filesystem, so that I can turn it into a pull request". Whereas in Skeema you can simply run `skeema pull development` and then `git diff` / commit / etc. 3. Agreed; but the more automated tooling, the better IMO. Otherwise it's easy for a code review to overlook something like the wrong table name or column name being used in a migration rollback, due to bad copypasta. And in an environment where schema changes occur quite frequently, I'd be surprised if QA always involves testing and confirming every single rollback. re: your last question, that operation is a mix of DDL and DML... I'd agree that the declarative approach doesn't natively handle it, since DML isn't declarative. My personal advice as a database professional is to treat that example as a data migration, managed separately from pure schema changes. Migrations of this sort should be less common -- as well as riskier to execute due to deploy-order considerations. FWIW, I'm approaching all this from a background of consumer-facing web/mobile tech companies, where schema changes need to occur quite often, and making them automated/self-service is game-changing. Processes/requirements will certainly be a bit different for traditional enterprise, finance, etc. On a side note, it's really good to run into you here tracker1! It's been a long time. Are you still involved in the BBS scene?

tracker1 8y ago

1. This burden is only for new ground-up environments... you can turn to snapshots/backups at specific points and start from that instead of running all migrations from the start. 2. This is where having a ground-up script is helpful. If you can destroy/recreate a local dockerized db (as an example, if not specifically) that's far less of an issue. 3. Developers writing code against a SQL database, should understand the variant of SQL being used. This is also where things like code reviews can minimize the issues. I'd also suggest that issues would be caught in QA or UAT before production providing decent test coverage prior to production release. As for your contrasting approach, what about the example in TFA where you go from a composite Name field into separate FirstName, LastName fields?

evanelias 8y ago
1. True, but it depends on the size of your data set. If you just want the tables but not the full prod data, backups may not be usable as-is for this purpose. 2. What if you want to capture the set of local dev changes, rather then destroying and starting over? Most migration tools that I've seen don't have diff logic built-in, so they have no way of saying "take whatever I did to the dev db, and capture that state in the filesystem, so that I can turn it into a pull request". Whereas in Skeema you can simply run `skeema pull development` and then `git diff` / commit / etc. 3. Agreed; but the more automated tooling, the better IMO. Otherwise it's easy for a code review to overlook something like the wrong table name or column name being used in a migration rollback, due to bad copypasta. And in an environment where schema changes occur quite frequently, I'd be surprised if QA always involves testing and confirming every single rollback. re: your last question, that operation is a mix of DDL and DML... I'd agree that the declarative approach doesn't natively handle it, since DML isn't declarative. My personal advice as a database professional is to treat that example as a data migration, managed separately from pure schema changes. Migrations of this sort should be less common -- as well as riskier to execute due to deploy-order considerations. FWIW, I'm approaching all this from a background of consumer-facing web/mobile tech companies, where schema changes need to occur quite often, and making them automated/self-service is game-changing. Processes/requirements will certainly be a bit different for traditional enterprise, finance, etc. On a side note, it's really good to run into you here tracker1! It's been a long time. Are you still involved in the BBS scene?