3 ms·

Certainly, but none of the things you mention seem all that scary. versioning stored procedures and triggers? Procedures and the like get their own .sql files

Certainly, but none of the things you mention seem all that scary. versioning stored procedures and triggers? Procedures and the like get their own .sql files and live under source control like everything else. At build time, they all get blown away and rebuilt. What if your dev envs don't use partitioning but your QA / prod envs do? SQL still works though, right? How do you rollback? Nobody has ever needed to rollback. It's the scary ghost story that convinces people to build big ugly database build systems. In short, most shops just plain won't ever need any of the stuff they're paying for (in dev hours) to run these big tools. Hopefully somebody will build this small thing as a tool, so that people can download it and therefore be "using a tool" and thus not running up against Invented Here Syndrome.

jbman223 6y ago
“Nobody ever needed rollback” is a very naive statement. Nobody needs rollback, until they do. And the one time you need it, having it will make up for ALL of the time spent on getting it set up correctly. Properly having rollback and other safeguards in place allows you to move faster and make mistakes with less consequences. Everything is a trade off.
mattzito 6y ago
As someone who built (and sold) a company in this space, your solution works for the vast majority of relatively simple databases. However, lots of legacy/enterprise/custom apps have freakishly complicated schemas running on databases that have been so over-engineered as to create problems for themselves (cough Oracle *cough). In practice, you end up having lots of complex restrictions - you have to change your schema definition slightly between dev and prod, because your dev box is linux and your prod machine is AIX and there's a bug in your dbms on AIX. You can't just drop stored procedures, because rebuilding them takes 30 minutes of downtime you can't allow for. Or you have security policies on data in prod that changes the table definition. In an ideal world you wouldn't need these things, but once you have a complicated enough system, you basically have to have a tool that does your database release management.
dexen 6y ago
Nobody has ever needed to rollback. This is at the same the truest and the wrongest sentiment. True, because experience teaches that db rollbacks are exceedingly rare for production. The cost of maintaining them is larger than the costs of doing it by hand. Moreover, since the rollback path typically is much less tested and validated, you run significant risk of a blind rollback messing up your schema or data even further. With GP programming we typically choose to hide new functionality behind a feature flag and toggle it instead of relying on downgrading the software version. Wrong, because there should be ability to perform provably lossless schema and data transformation. A lossless transformation would by its nature be amenable to a safe rollback. Just like a unified patch(1) patch file is a lossless transformation of a file and thus can be reversed, a lossless database change could be. The database engine could even offer an explicit support for checking, validating, and enforcing lossless (reversible) transformations, in similar spirit to the foreign key constraints guarding against mistaken deletion of relational data. Come to think of it, it would be very similar to a long-running transaction that encompasses both data and schema change, and that has side-effects visible to other transactions before it is closed and has its metadata discarded.
- cmiles74 6y ago
  In my opinion, the best place to implement the ability to rollback a schema change is in the database engine itself. In terms of how that would actually be implemented that's outside my wheelhouse. I think the ability to have some _standard_ method of rolling back schema changes across all the popular database engines would be significant. Some engines implement some support for some things. Many DDL commands can be run in a transaction in SQL Server. I believe Oracle creates a savepoint of some kind after every commit and DDL commands typically imply a commit. But it varies across vendors and it's a lot to untangle. In practice a lot of rollback scripts tend to make the assumption that the change script succeeded without issue. When the change script fails for an unknown reason often the rollback script will fail as well, in the worst case also having partially succeeded.
ciceryadam 6y ago
On the project I supported we did, but due to nonexistent database version control I had to do restores instead. Thankfully this was on Dev/Test envs, so things got ironed out before production releases. Thanks to no rollbacks, I cannot imagine running a full fledged CICD pipeline on neither of those envs.
krab 6y ago
> Nobody has ever needed to rollback. I did a rollback several times, but not a DB schema rollback. The potential to lose data is too big. I try to write code and migrations that support rolling back at least one deployment without rolling back the migration. Most of the time you need this anyway to support zero downtime deployments. Writing and testing backward migrations is ... problematic.

jbman223 6y ago

“Nobody ever needed rollback” is a very naive statement. Nobody needs rollback, until they do. And the one time you need it, having it will make up for ALL of the time spent on getting it set up correctly. Properly having rollback and other safeguards in place allows you to move faster and make mistakes with less consequences. Everything is a trade off.

mattzito 6y ago

As someone who built (and sold) a company in this space, your solution works for the vast majority of relatively simple databases. However, lots of legacy/enterprise/custom apps have freakishly complicated schemas running on databases that have been so over-engineered as to create problems for themselves (cough Oracle *cough). In practice, you end up having lots of complex restrictions - you have to change your schema definition slightly between dev and prod, because your dev box is linux and your prod machine is AIX and there's a bug in your dbms on AIX. You can't just drop stored procedures, because rebuilding them takes 30 minutes of downtime you can't allow for. Or you have security policies on data in prod that changes the table definition. In an ideal world you wouldn't need these things, but once you have a complicated enough system, you basically have to have a tool that does your database release management.

dexen 6y ago

Nobody has ever needed to rollback. This is at the same the truest and the wrongest sentiment. True, because experience teaches that db rollbacks are exceedingly rare for production. The cost of maintaining them is larger than the costs of doing it by hand. Moreover, since the rollback path typically is much less tested and validated, you run significant risk of a blind rollback messing up your schema or data even further. With GP programming we typically choose to hide new functionality behind a feature flag and toggle it instead of relying on downgrading the software version. Wrong, because there should be ability to perform provably lossless schema and data transformation. A lossless transformation would by its nature be amenable to a safe rollback. Just like a unified patch(1) patch file is a lossless transformation of a file and thus can be reversed, a lossless database change could be. The database engine could even offer an explicit support for checking, validating, and enforcing lossless (reversible) transformations, in similar spirit to the foreign key constraints guarding against mistaken deletion of relational data. Come to think of it, it would be very similar to a long-running transaction that encompasses both data and schema change, and that has side-effects visible to other transactions before it is closed and has its metadata discarded.

cmiles74 6y ago
In my opinion, the best place to implement the ability to rollback a schema change is in the database engine itself. In terms of how that would actually be implemented that's outside my wheelhouse. I think the ability to have some _standard_ method of rolling back schema changes across all the popular database engines would be significant. Some engines implement some support for some things. Many DDL commands can be run in a transaction in SQL Server. I believe Oracle creates a savepoint of some kind after every commit and DDL commands typically imply a commit. But it varies across vendors and it's a lot to untangle. In practice a lot of rollback scripts tend to make the assumption that the change script succeeded without issue. When the change script fails for an unknown reason often the rollback script will fail as well, in the worst case also having partially succeeded.

ciceryadam 6y ago

On the project I supported we did, but due to nonexistent database version control I had to do restores instead. Thankfully this was on Dev/Test envs, so things got ironed out before production releases. Thanks to no rollbacks, I cannot imagine running a full fledged CICD pipeline on neither of those envs.

krab 6y ago

> Nobody has ever needed to rollback. I did a rollback several times, but not a DB schema rollback. The potential to lose data is too big. I try to write code and migrations that support rolling back at least one deployment without rolling back the migration. Most of the time you need this anyway to support zero downtime deployments. Writing and testing backward migrations is ... problematic.