Hacker News new | ask | show | jobs
by dontJudge 3241 days ago
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.
1 comments
statictype 3241 days ago
If you have different customers on different versions of your application how do you create that patch?
link
dontJudge 3240 days ago
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?

link
statictype 3240 days ago
>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!

link