Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

We use JSF over Tomcat along with a backing proprietary DB.

I want to know how the addition of new database fields or/and stored procedures should be handled when we deploy a new version of the application.

We deploy by sending a WAR file to the hosting Team, as such our WAR should be able to setup the stored procedure when it starts.

What are the possible appropriate strategies to be able to incrementally update the database?

share|improve this question
I guess my requirement itself is not a good way todo it is a bit insecure. – Archan Mishra Jan 11 '12 at 16:50
up vote 4 down vote accepted

Jelayn's answer is pretty good, and represents roughly standard practice, esp in the Rails world where a similar solution is baked into the framework. However, the state of the art, as practiced by Facebook, Flickr, Heroku, and IMVU - a pioneer in this - is a bit more advanced.

The problem with the standard practice is that it assumes you can write 2 versions of code, and switch from one to the other with a quick migration as you deploy. There's lots of problems with this:

  • If you have a lot of data, a migration will take time. It takes months for Facebook to migrate data, but unless you have very little data, it's rare for the migration to be instant. The solution to this is to migrate slowly. IMVU pioneered the strategy here:

    • instead of migrating the table, make a separate table with the new schema
    • when a row is read that isn't present in the new table, migrate only that row and save it to the new table. You can then delete the old row.
    • also run a background job that runs the migrations in batches (watch out for conflicts!)
  • during a migration, your code needs to support the old schema and the new one. So you should write the code that supports both, then migrate, then remove the old code in a separate deploy.

  • upon a failed migration, you should decide whether you can migrate in both directions or just forwards. IMVU goes forward only I think - so if they have a bug in their migration, they fix it instead of rolling it back. I personally prefer fixing, but there are good arguments for rolling it back too.

This stuff is all highly related to continuous deployment as well, and a lot of those strategies can help. For example, a common CD technique is selectively enable new code only for a small user base to give it more testing, and only then enable it for larger and larger audiences (typically, you use a DB setting for each feature). This practice allows you gain more confidence in your migrations, esp when they are more complicated than just adding a field, and without betting all your data on a relatively untested migration.

Source: market research and talking to customers while making my Continuous Integration and Deployment start-up.

share|improve this answer
thanks for such a deep insight and detailed plan .My best wishes for your startup. – Archan Mishra Jan 12 '12 at 16:05

I agree with @FrustratedWithFormsDesigner in that it is quite possible, AND that the script for updating your database structure should not be brought along with your application code. Because of this your application would require other permissions allowing to create tables, alter tables, etc. which you most probably don't need during the "normal" usage of your application. So, in terms of security, not a good thing, since your application should only have the permissions needed to work properly.

Anyway, back to the main point. It is quite possible to properly do that by creating a special table in your database, let's say X_VERSION, in which you will simply have the current version of the database schema (it may be a timestamp too). Whenever you alter your database schema, you put that change into a SQL script named [Timestamp-XXX-WhateverItIsYouAreDoing.sql], where:

  • Timestamp is the timestamp, for example 20110111-165500
  • XXX is a number, which will be useful later to know the order of execution of the scripts
  • Whatever, etc. is some text that will help you remember what you do in that script

Whether your database updating program is in your application or outside does not matter for the following steps; whenever you deliver a new version of your application, your update program should do this:

  • Look up the current timestamp in X_VERSION
  • Check all the scripts that must be executed between the version found in X_VERSION and the new version
  • Loop over the scripts, and for each script

    • Execute the script updating your database/schema
    • Update the timestamp in X_VERSION to the timestamp of the executed script

When all scripts are done, you know your application can safely start.

Bonus: If you want to have a safe way to go back to a previous version, whenever you create a script altering your database, always create somewhere a script that will allow you to go back to the previous version. It may sometimes be a bit hard (like removing a column for example, not all DBMS allow this), but always try. You will then be prepared to revert to a previous version if necessary.

share|improve this answer

I usually have db changes deployed outside the main application, usually via a script that is executed by the DBA.

I suppose you could do the changes from inside your application, if your application has permission to modify the database structure. I guess what would have to happen is your application would start up, some new procedure would run at startup, make all the necessary database changes and then probably restart. The trick is to make sure the changes only happen once. This could probably be done by checking a version number in a special table and using that to decide if the database should be modified.

It also adds db maintenance activities to the main application, which IMO is not a good idea because the application is probably designed to do something else (bloggin, banking, medical health records, online hotel reservations, etc...). Sometimes you may want to keep your database deployments completely separate from the core application.

From a technical point of view, it's quite possible to do this. Should it be done? That's a different question.

share|improve this answer
@FrustatedWithF thanks for the quick reply, however I want a CodingStrategy, as in how will i handle instantiating multiple stored procs for the first time.. – Archan Mishra Jan 11 '12 at 16:43
@FrustatedWithF jalayn builds up on your answer but does specify coding strategy, which was the actual ask of the question. Hence i am going to accept his answer. – Archan Mishra Jan 11 '12 at 16:49
@ArchanMishra: No problem. I just got back to this now. Yeah, he says pretty much what I wanted to say but much better. Clearly we know who had their coffee first this morning! ;) – FrustratedWithFormsDesigner Jan 11 '12 at 16:57
@FrustratedWithFormsDesigner OK, I admit, at that moment it was late in the afternoon for me :-) – Jalayn Jan 12 '12 at 7:09

The two standard approaches for database updates that I've seen applied are:

  1. The deployment team running sql scripts provided by the DBA team. The DBA group would provide scripts to update the schema and load any data that were required for that particular release.
  2. Using an automated tool like Liquibase. This system adds a table similar to what @Jalayn proposes, which tracks the changesets (deltas) that should be run for each table. Most standard operations are supported.

In either case, the end result should be the same. However, the approach you take should reflect the principles that your development group prefers - automated vs manual, etc.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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