Database independence - payback time

By Andrew Clifford

With a little foresight and discipline it is possible to create database-independent applications.

We provide our Metrici Advisor software as a service on the web, rather than as software that our customers install. This is easier and more cost effective for both us and our customers.

You might think that this frees us from many of the constraints of a traditional software vendor. We do not have to support the preferred hardware, operating systems and databases of different customers. We can just pick some technologies and stick with them.

Tempting though this may be, it would be short sighted. We need to protected ourselves against imposed technical changes and supplier failure. We have always insisted that the software is independent of the hardware, operating system and database on which it runs.

This decision comes at a cost. We develop and test the software on both Windows and Linux, we test it against both the HSQLDB database and MySQL. This adds time to development and testing, and means that we can not take advantage of special features such as database stored procedures.

Is this worthwhile?

We currently run servers in the UK. Our customers are mostly EU-based, and the UK is as good a place as any for the servers. We can support customers globally from our UK-based servers.

However, a larger opportunity has recently cropped up in the US. Operational and legal considerations mean that we may need to run US-based servers. The business we are talking to could host the service and it makes sense for us to have a second instance, just for this opportunity.

Which brings us back to databases. Our production servers run on MySQL, but our potential partner has a very strong preference for PostgreSQL, and getting our software working on PostgreSQL is an important part of the arrangement.

Now it is payback time.

Because we have worked hard to make our software database independent, migration to PostgreSQL has been simple, and we managed to get a PostgreSQL version up and running in a few hours.

The migration could have been difficult. We have had to change table names (our "user" table is a reserved word in PostgreSQL), and make minor changes to some of our SQL. However, years of discipline have paid off. We were able to make these changes without changing any program code, and we are in a good position to take forward this opportunity because of it.

On a technical level, we have not had to do that much to achieve database independence. We use standard SQL, and no stored procedures. We use only fixed SQL statements, rather than building SQL in code. We hold the SQL in an external file, which lets us change the SQL without changing program code.

These things are not technically hard. But many organisations make this harder than it should be. They think that because they have "invested" in a "strategic" database they should use database-specific features. They do not enforce database standards for development and maintenance. And of course database vendors are keen to promote features which make their database special, and which make changing databases harder for you.

Not everyone needs database independence. But it can be achieved. It just takes a little foresight and discipline.

Subscription