5 ms·

We built a version control tool in-house to help with this problem. It would examine the database and generate files (one per object) on disk which could be com

We built a version control tool in-house to help with this problem. It would examine the database and generate files (one per object) on disk which could be committed into version control. The tool has a -merge mode that examines scripts in files against the objects in the database and spits out sql commands that modify the DB to match the versioned files. It has a few drawbacks but mostly works ok.

jasonkester 15y ago
That sounds like a bit of a bandaid fix for a history of bad practice. Why not, instead of forensically trying to rebuild scripts for changes, just save your change scripts as you change things? You end up with the equivalent set of scripts in source control, but it seems like they'd be a lot more readable (they won't be all lumped on top of eachother and out of order, etc.). Is there another advantage to what you're doing that makes it worth deviating from the "best practice" way of doing things? Is, for example, the -merge functionality something that you use on a regular basis to roll backward?
- statictype 15y ago
  Why not, instead of forensically trying to rebuild scripts for changes, just save your change scripts as you change things? Well, DB objects were put into version control much later than other source files, so I wanted a way to guarantee that what's there in source control matches what we actually have in the database server. You're suggestion is that basically developers maintain the discipline of having changes in the files reflect what they run in the DB, right? I thought its significantly cleaner to have the database as the single source-of-truth and take everything from there. but it seems like they'd be a lot more readable Didn't quite get that part. The generated scripts are saved in one-per-file and reflect the name of the table/procedure/etc... that it contains. Isn't that basically what the 'best practice' way of doing it is? Merging is just to take the latest set of source control and bring your DB up to date with it. How do you handle version control? I'm certainly interested in a better way to do it.

jasonkester 15y ago

That sounds like a bit of a bandaid fix for a history of bad practice. Why not, instead of forensically trying to rebuild scripts for changes, just save your change scripts as you change things? You end up with the equivalent set of scripts in source control, but it seems like they'd be a lot more readable (they won't be all lumped on top of eachother and out of order, etc.). Is there another advantage to what you're doing that makes it worth deviating from the "best practice" way of doing things? Is, for example, the -merge functionality something that you use on a regular basis to roll backward?

statictype 15y ago
Why not, instead of forensically trying to rebuild scripts for changes, just save your change scripts as you change things? Well, DB objects were put into version control much later than other source files, so I wanted a way to guarantee that what's there in source control matches what we actually have in the database server. You're suggestion is that basically developers maintain the discipline of having changes in the files reflect what they run in the DB, right? I thought its significantly cleaner to have the database as the single source-of-truth and take everything from there. but it seems like they'd be a lot more readable Didn't quite get that part. The generated scripts are saved in one-per-file and reflect the name of the table/procedure/etc... that it contains. Isn't that basically what the 'best practice' way of doing it is? Merging is just to take the latest set of source control and bring your DB up to date with it. How do you handle version control? I'm certainly interested in a better way to do it.