The Only Pattern for Data Access is - There Are No Patterns for Data Access

Over the years of software development, one thing that has eluded most developers is Data Access. Yes, I mean writing code that accesses a database from your application. It is an age old problem since the days when data storage and computing were invented.

We would have thought that by now we would have been able to come up with a clean universal pattern for data access such as the M-V-C pattern for User Interfaces or the Singleton pattern or the Factory pattern. Yet, today we find ourselves with a plethora of technologies and patterns that are constantly struggling to survive to the next release. Just ask yourself, how many iterations of the ORM paradigm have come and gone?

J2EE with EJBs and Hibernate must be the answer!

In the JAVA world the creation of EJBs, Hibernate and its adoption into the J2EE spec surely would have put an end to the Data Access Layer misery developers faced. But it soon got notorious for being overly complicated, bloated and expensive to develop and maintain. Developers found that it was easy to run queries or stored procedures straight against their databases and get their data directly. Many of them did so in shame and said to themselves, this is hack but ORM way is really the true way. It became obvious when they ran their applications that that their apps ran better, faster and were easier to read without using the full EJB functionality.

The notoriously heavy datasets

At one time it seemed like Microsoft with their DataSets had won over the Database access world by providing simple disconnected objects that were easily bound to UI’s and were able to do queries to the database or even in memory when they were loaded. But alas they became notoriously heavy, hard to maintain and transport and had to be revamped. Microsoft has dabbed with many iterations of Data Access Layers including Object Spaces, Active Recordsets, LINQ to SQL and Entity Framework. Some of these are still in the market trying to survive like LINQ to SQL, some have become part of history like Active Recordsets and some never made it into sight like Object Spaces and some claim to take over the future like Entity Framework!

Why do none of the Data Access Layer Patterns work?

In my opinion after having used and worked on many projects that use the above mentioned technologies, the only pattern I see is a pattern of failure. The reason is that these technologies try to mimic the database. They make it look like the user is abstracted from the database by doing what a database does instead of abstracting what a database should do. They completely miss the concept of abstraction. When I abstract something in my code, the job of that abstraction is to hide the internal complexity of something, not repeat the actual work of what I am abstracting.

Failed data access layers abstract the database by repeating the work of databases. That is the number one rule of failure. How so you maybe asking. You may say, I love writing my LINQ queries and not having to worry about SQL. The problem begins right there. ORM data access implementations have to implement their own query language! If you have to implement your own query language you are venturing into the complex world of set theory and relational tuples. You better be as good and as fast as a database engine on that or else you will soon be history.

The Pattern of Redundancy instead of Abstraction

Caching database data in memory is another database copy-cat feature. You better provide better caching, latency, and redundancy algorithms than the database or you are going to face the wrath of the Database Engine pretty soon! I doubt any Data Access technology can beat the caching of query execution plans and caching recently used data provided by the leading database engines such as the commercial engines like Oracle and SQL Server or even the free ones like MySql. Database caches work better even over the network in the long term than the most powerful Object caching providers I have worked with.

These features of database access patterns or technologies do not do justice to the database engines they try to tame and therefore soon get bypassed and blown away to their competitor: the no data access layer pattern.

What is the no data access layer pattern?

The no data access layer pattern is simple; access or persist your data through the native database provider, using the native query language of database engine (SQL). If you stick to this pattern you will be more successful than not in your applications! The only rule of abstraction that you should follow is use data access to populate only application objects that need to be populated or persisted from your application and you should be good. Nothing more nothing less!

Let’s take an oath to ourselves and say, “Thou shall not be fooled by another Data Access Pattern or technology!”. It is like looking for the Abominable snowman. If there was any truth to it we would have found it by now.

| Shout it!