5

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.)
  • PostgreSQL has a VERY cool feature for this. we can do transactional DDLs. this means that you can rollback any create table, create index, alter table, etc. this helps to avoid problems related to syntax errors and all that. but, this is not all. CREATE EXTENSION (postgresql.org/docs/9.4/static/sql-createextension.html). it allows you to version stuff. so you can directly jump from version to the other. no need to say that it is transactional as well. consider switching - mysql cannot do this kind of stuff. – Hans-Jürgen Schönig Nov 15 '14 at 9:29
  • Indeed, this is a very nice feature of PostgreSQL, @Hans-JürgenSchönig. Unfortunately, the application I manage is quite an old one, do not use a DB HAL, so just migrate it is quite a huge job for now and is not in the sight for at least the medium term (1~2y). I'm looking for a solution that could be implemented with the present stack, where MySQL/MariaDB is the main RDBMS. – Deny Dias Nov 16 '14 at 17:54
  • I would suggest reading this article on InfoQ it touches on a number of points brought up in this discussion – user3660241 Nov 23 '14 at 16:10
  • That article is gold, @user3660241! Thanks for sharing it! – Deny Dias Nov 24 '14 at 20:08
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.

  • Hi, Serj! First, sqlibrist is a hell of a name! Congratulations to the guy/gal that came with this up! Yes, the original situation is now under control. But sqlibrist could definitively improve it. I'm not going to hit the accepted answer button now because I have to investigate it further, which will take a while. But for sure your answer deserves my upvote. ;) – Deny Dias Mar 24 '16 at 19:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.