Handling SQL updates for production deployments

Question

I'm researching for quite a long time now (about two months) and haven't found a good approach for this, so I'll ask you experts out there in the hope of some enlightenment.

I run a pretty traditional LAMP web application in the cloud. The source code is VCS'ed with Git, as well as database schema (split SQL files for structure and default data).

Deploying a new environment is quite a joy, development or production. Dev environments are deployed with Vagrant plus some nice shell provisioning script I wrote. Production environments are deployed using a subset of the Vagrant provisioning script, which is also very easy to do.

When the development cycle imposes a change to the SQL schema (structure or data), things starts to break. In that case, developers changes the core SQL scripts so all the changes gets into the VCS, which is totally fine for brand new environments.

The problem sits within already deployed production environments: for those, I have to manually get all the patches for the SQL changes since the revision actually running in all production servers first so I can apply them manually after. This takes a long time to happen, is a quite fragile task and error prone.

Initially, I thought I can move the SQL changes away from the core SQL files smaller subsets, letting the core SQL files as a starting point. When there is something to change at SQL structure, I could just tell devs to create a new SQL file with only the changes from that development cycle.

So, for instance:

- structure_begin.sql: the SQL schema as it is now.
|- structure_devcycle1.sql: first set of changes to the structure
|- structure_devcycle2.sql: second set of changes to the structure, and so forth...

Then, by using Git hooks, I can selectively and automatically deploy them on production. But I don't know if this is a good approach. And here I can ask:

1. Does somebody out there have solved this puzzle?
2. What are the best practices for release management and deploy automation for SQL changes in production environments?
3. Are there any open source tools that can help in this process? (I'm aware of Sqitch, but I'm not quite sure if it is the right one for the job.)

Answer 1

I am not sure if you still need an answer, but I had the same problem with managing and deploying schema changes. Sqitch was the first thing I tried to mechanize migrations, but I did not really like it.

Just as you described, we have separate .sql files with CREATE TABLE and other instructions reflecting our database schema. We created simple tool to generate SQL patches when schema changes. Take a look at it: https://github.com/condograde/sqlibrist. There is detailed tutorial, how to use it.