Every once in a while, a developer finds they need to write an application that uses multiple different database languages. In this article I take a look at the pros and cons of writing a Database Abstraction Layer.

A Legacy Client-Server Application

Many years ago I found myself taking over development of a client-server application that supported multiple databases. Chief among the supported databases were MS-SQL and Oracle. The application did not have a separation between the three traditional layer: GUI, Business Logic, and Database. Instead they were all tightly knit together, primarily in the name of efficiency. There was a very short path between a call to the database and the output reaching the screen. While there may be some advantages to this approach, I found they were largely outweighed by the disadvantages they cause. Amongst these was every time I needed to change a database call, I was hand crafting the same query in several different languages.

A Chance to Start Again

A short while after taking over development of this client-server application, the subject of giving it a major overhaul and redesign came up. The application had begun to show its age. Firstly I wanted to split the design into the three traditional layers. But secondly I also wanted to ensure that I never had to key multiple versions of the same query again.

Writing The Database Abstraction Layer

I began designing an object model that would allow me to define a query through an object oriented structure. I had my four top level objects:

• Select
• Insert
• Update
• Delete

Each object had child objects that allowed me to configure the query I was building. Many of the child objects, such as where clause objects, were shared. Without even realising it, I had begun writing a Database Abstraction Layer.

The key principle of the design was to be modular. The object that defined the queries was to know nothing of the underlying database language that would be used. Each object just described a query in a common way. A plugin module then took the objects and translated those into the language that module’s designated language. Need MS-SQL and Oracle, then start with those two modules. Need to add MySQL at a later date (and we did), just create a new translation module. Needless to say, the translation module also handled the database connection. The application never needed to be changed if we wanted to add another database language. The application had become Database Agnostic.

The Pros of a Database Abstraction Layer

There are many benefits to writing a Database Abstraction Layer and these outweigh the drawbacks. To the extent that I wouldn’t hesitate to use one again. Although next time I might try to find one that has already been written. The benefits I had from using one were:

• I and my colleagues could write code without having to think about how to achieve a database query in multiple languages.
• Making changes to the database structure were easy cater for in the application.
• I could add a new database language without having to modify large swathes of code.
• The structured nature of the object model, forced me to write better queries.

The Cons of a Database Abstraction Layer

The implementation of a Database Abstraction Layer wasn’t without its downsides:

• Performance. The added overhead over building a query through an object model slowed some operations down. I also spent quite a lot of time finding the fastest way to get a result set to where it was needed.
• Restricted SQL. I could only use the most common aspects of SQL. Anything too fancy and there was a risk that one of the languages I wanted to implement could not support it. In some cases, where I really needed a piece of functionality, I had to create a stored procedure to achieve in one database what another one could do out of the box.

Summary

For the purposes of the project I was working on, the pros of writing a Database Abstraction Layer outweighed the cons. I would recommend at least spending some time at the start of a new project to determine if a Database Abstraction Layer is suitable for your needs.

There is an added benefit for a Database Abstraction Layer too. It makes it easier to implement Object-relational mapping (more on that in my next article).

SAMWare UK creates bespoke solutions and websites. Contact us to arrange a chat to discuss your needs and for a free no obligation quote.