Database Evolve: Managing Database Versions Programmatically

Posted on Posted in Java

Database Evolve: Managing Database Versions Programmatically

I find it hard to believe that there are very few articles online talking about database evolve. To think that database is the lifeblood of many application, and yet, very few seem concerned about efficiently managing different versions of a database… while many manage their database manually through their DBA.

But how can you ensure that:

  1. during development, all developers works on the latest database schema to reduce code conflicts?
  2. during production roll-out, applications on different servers are compatible and consistent with their respective database schema?

If you are doing this manually, then perhaps its time to reconsider your approach by asking yourself – “Is there a way to automate this?“.

Our solution is one that we’ve used for the past three years and has been very effective. It’s a simple approach with just few Java classes. The concept is to keep track of the database version  from the application and keep the database up to date every time the application starts.

For example, if the database is in version 21 and application is in version 24, then the application will apply evolve scripts 22,23 and 24 to get the database into version 24. It’s a fairly simple concept but saves a lot of coordination effort and headache.

I’m posting below the snippets of database evolve we have. You can find the complete set of codes in Open-tides(http://code.google.com/p/open-tides/).

Below is the DBEvolve interface that should be extended by all evolve scripts. The evolve scripts contain SQL scripts to update the database from the previous version to the current script’s version.

public interface DBEvolve {
	/**
	 * Actual database evolve script operations.
	 */
	@Transactional
	public void execute();
	/**
	 * Returns the description of evolve script.
	 * @return
	 */
	public String getDescription();
	/**
	 * Returns the version of this evolve script.
	 * Ensures that execution of evolve script is in proper
	 * sequence.
	 * @return
	 */
	public int getVersion();
}

Below is the main logic that compares the database version with the list of evolve script. evolveList is a collection of DBEvolve to keeps the database updated. It is assumed that evolveList contains a sequential list of evolve scripts.

                // get current db version
		SystemCodes version = systemCodesDAO.loadBySystemCodesByKey("DB_VERSION");
		if (version==null) {
			// no version available yet, lets create one
			version = new SystemCodes();
			version.setKey("DB_VERSION");
			version.setNumberValue(0l);
			systemCodesDAO.saveEntityModel(version);
		}

		// skip evolve if there is nothing in the evolve list
		if (evolveList.isEmpty()) {
			_log.info("No evolve scripts found.");
			return;
		}

		// sort the evolve list
		Collections.sort(evolveList, new VersionComparator());
		// check for duplicate version numbers
		for (int i=0; i<(evolveList.size()-1); i++) { if (evolveList.get(i).getVersion() == evolveList.get(i+1).getVersion()) { // we have a duplicate version... exit throw new InvalidImplementationException( "Duplicate version number [" + evolveList.get(i).getVersion() + "] detected on evolve script for " + evolveList.get(i).getClass().getName() + " and " + evolveList.get(i+1).getClass().getName()); } } // get number of latest evolve script int currVersion = version.getNumberValue().intValue(); int latestVersion = evolveList.get(evolveList.size()-1).getVersion(); if (currVersion>=latestVersion) {
			_log.info("Database is updated at version " + currVersion);
			return;
		} else {
			_log.info("Updating database from version " + currVersion +" to version " + latestVersion );
		}

		// execute new evolve scripts
		for (DBEvolve evolve:evolveList) {
			if (evolve.getVersion() > currVersion) {
				// let's execute this evolve script
				_log.info("Executing evolve version ["+evolve.getVersion()+"] - "+evolve.getDescription());
				evolve.execute();
				// if successful, update current db version
				version.setNumberValue(new Long(evolve.getVersion()));
				systemCodesDAO.saveEntityModel(version);
				_log.info("Success.");
			}
		}
		// as precaution let's update db version again
		version.setNumberValue(new Long(latestVersion));
		systemCodesDAO.saveEntityModel(version);

		_log.info("Database is now updated to version "+latestVersion);
	}

A few more things to note:

  1. The snippet uses Spring and Hibernate. The main evolve should be under Spring transaction to ensure proper rollback in case of failed updates.
  2. When application failed to update, it should not start-up to alert the developer of the failure.
  3. Due to its simplicity, the code above does not support multiple branches of application and database.

 

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.