
How we do version control for our database - statictype
https://www.iviva.com/blog/database-version-control/
======
dontJudge
You will save a lot of headache if you embrace the fact DB deployment scripts
are "patches", not "rebuilds". Write your sql scripts as a serious of patches
to deploy against a version of the DB.

Generation of the deployment script using a diff has so many holes. Renames
can be misinterpreted as drop/create. It falls apart with data to
insert/deleted. Ordering can't always be calculated.

~~~
statictype
If you have different customers on different versions of your application how
do you create that patch?

~~~
dontJudge
Presumably each "version" was created by you. Version A is your starting
point, no new sql scripts executed yet. You might start with a DB backup
restore of version A. Create sql scripts to advance to version "B". There's
your patch.

If your versions are a straight chronological sequence it's straight forward
to advance from any past version to any future version. Run each patch till
you arrive at the desired version.

If you branch out in multiple forks it can be a little tricky. I've never had
this situation, but I would treat each branch as a separate chronological
sequence of patches. Unfortunately merging doesn't work with "patch" style
development, so this could be a duplication nightmare. I guess nothing is
perfect.

If the customer can make unrestricted modifications to their DB (unknown to
you), you're out of luck. Versions are no longer under your control and
generating with a diff is the way to go. But in that case the diff seems even
more dangerous. Will it destroy their custom modifications?

~~~
statictype
>But in that case the diff seems even more dangerous. Will it destroy their
custom modifications?

So customers don't modify the database directly. However we do have custom
changes for certain customers - those all go into the data dictionary.

The tool is very careful not to drop any columns or do any destructive
alterations without manual intervention.

The other problem we have is our platform supports multiple applications and
not all customers use all applications.

For example, one customer may only be interested in Room Booking and Visitor
Management.

Another would be interested in Room Booking, Work Requests and Asset
Management.

Some customers may have a custom version of the Work Requests system with
slightly different database structures. That further complicates the
situation.

The infrastructure we have in place is far from perfect, but so far has neatly
done the job for us.

Thanks for your feedback!

