There's a couple of fundamental rules to follow. First, don't put something that will have insane impact into the application deploy changes. 99% of the DB changes are very cheap, and very minor. If the deploy is going to be very expensive, then just don't do it, we'll do it out of band. This has not been a problem in practice with our 20ish person team.
Second, it was kind of like double entry accounting. Once you committed the change, you can not go back and "fix it". If you did something really wrong (i.e. see above), then sure, but if not, you commit a correcting entry instead. Because you don't know who has recently downloaded your commit, and run it against their database.
The changes are a list of incremental steps that the system applies in order, if they had not been applied before. So, they are treated as, essentially, append only.
And it has worked really well for us, keeping the diverse developers who deploy again local databases in sync with little drama.
I've incorporated the same concept in my GUI programs that stand up their own DB. It's a very simple system.
The main challenge I have noticed with that approach is maintaining the sequencing across different branches being worked upon by different developers - solvable by allocating/locking the numbers from a common place. The other is rolling back multiple changes for a given view/stored proc where, say, each change added a separate column - if only one is rolled back, how do you automate that? Easily done manually though.
I will say that stored procs are specifically messy, and we did not have many of those. They had a tendency to really explode the change file. With DDL, you can fix a table column in isolation. Fixing a typo in a 100 line stored proc is another 100 lines. And we certainly didn't have multiple people working on the same proc at the same time.
We had no real need to address that aspect, and I would do something more formal with those if I had to, such as having a separate file with the store proc, and simply a note that it has changed in the change file. I mean, that's a bit of a trick.
There's a couple of fundamental rules to follow. First, don't put something that will have insane impact into the application deploy changes. 99% of the DB changes are very cheap, and very minor. If the deploy is going to be very expensive, then just don't do it, we'll do it out of band. This has not been a problem in practice with our 20ish person team.
Second, it was kind of like double entry accounting. Once you committed the change, you can not go back and "fix it". If you did something really wrong (i.e. see above), then sure, but if not, you commit a correcting entry instead. Because you don't know who has recently downloaded your commit, and run it against their database.
The changes are a list of incremental steps that the system applies in order, if they had not been applied before. So, they are treated as, essentially, append only.
And it has worked really well for us, keeping the diverse developers who deploy again local databases in sync with little drama.
I've incorporated the same concept in my GUI programs that stand up their own DB. It's a very simple system.