Skip to main content

Of Factories and Databases

While working on MachBlog we debated how best to handle the multiple database conundrum. When creating an application such as this that needs to support potentially numerous databases there is a fork in the road. Either a single single set of database access code is created that has conditionals throughout to handle database-specific functionality where needed, or database access code unique to each database platform can be created, but this runs the risk of having redundant code where the more mundane SQL is identical across database platforms.

In the end we decided that for the sake of maximum flexibility and future extensibility we would have a set of data objects (DAOs and gateways) for each database platform. Out of the box MachBlog will have two complete sets of data objects, one for Microsoft SQL Server, and another for MySQL. Make that three, actually, since for the sake of object typing we have a set of base classes that are extended by the database-specific objects. The base classes are never instantiated directly. They exist only to provide a common object type that the rest of the application will use, and they also provide a bit of generic documentation for future database implementations.

Either way you go there's some potential maintenance headaches involved. In our case since we have separate objects for each database platform we do have a fair amount of SQL code that is identical between MSSQL and MySQL. The tradeoff here is maximum flexibility. For example, right now views and stored procedures aren't used in the objects for either platform, but let's say I get the crazy notion I want to implement stored procs for SQL Server. MySQL 5 has stored procs but MySQL 4.x doesn't, so to make the MySQL objects compatible with both versions I decide not to implement stored procs for MySQL. No problem, I can change my SQL Server objects all I want and the MySQL objects won't know or care, and I don't have to create potentially messy conditional logic throughout my database objects to do so. Maximum isolation, maximum flexibility, with the downside of potentially having to make the "generic" changes that aren't specific to either platform in multiple places.

The specifics of how this is achieved is through the factory pattern. It's a simple enough concept, but extremely powerful in practice. In short, rather than instantiating specific objects for MSSQL or MySQL directly when the application starts up, the application simply asks the DAO and Gateway factories to return the appropriate objects for the database type that is indicated in the application's configuration file. Because the data objects for both databases extend the same base class, the rest of the application can expect to receive an object of the "generic" type even though the specific instance is suited to a particular database platform.

This also makes it trivial to add support for additional database platforms in the future. In fact a new datbaase platform could be added simply by creating data objects for the platform in question and that's it, other than literally a one-line edit to the application's XML configuration file. The rest of the application's code is happily unaware of this entire process.

There are of course pros and cons to any architectural decision, and this is no exception. As my music theory teacher always used to say, "You pay your money and go either way." Personally I think the independent data objects that are platform specific give maximum flexibility while making the code far easier to deal with and maintain, not to mention extend through the addition of new database platforms in the future. Each database can take advantage of its individual and potentially unique strengths and any weaknesses of particular platforms can be avoided. Meanwhile the rest of the application isn't concerned with what database is being used so long as the data it asks for is returned.


Hello Matt,

I'm probably missing the subtleties, but I'm doing something similar. The only difference is that any methods that are either identical between children (in your case MySQL vs. MSSQL, in my simpler case it is between different entities - UserDAO, PageDAO, ProductDAO, etc.) or have only simply parameterizable differences, I put those methods in the base class with the option to overload in the children if necessary. So if MySQl.Delete() and MSSQL.Delete() are identical (or the only differences can be parameteized in the extending childrens variable scope), you simply have a Base.Delete(), but then when you want a more sophisticated MSSQL.Delete(), you can then add it to that one child class.

Not sure if this matches the details of the problem you were solving . . .

Best Wishes,

Thanks Peter--we actually do have things like EntryDAO, EntryGateway, etc., with a base object, MSSQL, and MySQL version for each. They're separated into different directories but the file names are the same between them. I've done things in the past as you describe with the common SQL in the base object but decided to take a bit different approach here and so far I like it. At this point anyway I prefer having fully implemented objects for each type and not really relying on anything from the base object in terms of specific implementation. I'll see if this opinion holds up as we add a few more databases to the mix.


Popular posts from this blog

Installing and Configuring NextPVR as a Replacement for Windows Media Center

If you follow me on Google+ you'll know I had a recent rant about Windows Media Center, which after running fine for about a year suddenly decided as of January 29 it was done downloading the program guide and by extension was therefore done recording any TV shows.

I'll spare you more ranting and simply say that none of the suggestions I got (which I appreciate!) worked, and rather than spending more time figuring out why, I decided to try something different.

NextPVR is an awesome free (as in beer, not as in freedom unfortunately ...) PVR application for Windows that with a little bit of tweaking handily replaced Windows Media Center. It can even download guide data, which is apparently something WMC no longer feels like doing.

Background I wound up going down this road in a rather circuitous way. My initial goal for the weekend project was to get Raspbmc running on one of my Raspberry Pis. The latest version of XBMC has PVR functionality so I was anxious to try that out as a …

Running a Django Application on Windows Server 2012 with IIS

This is a first for me since under normal circumstances we run all our Django applications on Linux with Nginx, but we're in the process of developing an application for another department and due to the requirements around this project, we'll be handing the code off to them to deploy. They don't have any experience with Linux or web servers other than IIS, so I recently took up the challenge of figuring out how to run Django applications on Windows Server 2012 with IIS.

Based on the dated or complete lack of information around this I'm assuming it's not something that's very common in the wild, so I thought I'd share what I came up with in case others need to do this.

This work is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License.

Assumptions and CaveatsThe operating system is Windows Server 2012 R2, 64-bit. If another variant of the operating system is being used, these instructions may not work properly.All of the soft…

Setting Up Django On a Raspberry Pi

This past weekend I finally got a chance to set up one of my two Raspberry Pis to use as a Django server so I thought I'd share the steps I went through both to save someone else attempting to do this some time as well as get any feedback in case there are different/better ways to do any of this.

I'm running this from my house (URL forthcoming once I get the real Django app finalized and put on the Raspberry Pi) using I don't cover that aspect of things in this post but I'm happy to write that up as well if people are interested.

General Comments and Assumptions

Using latest Raspbian “wheezy” distro as of 1/19/2013 (’lll be using Nginx ( as the web server/proxy and Gunicorn ( as the WSGI serverI used heavily as I was creating this, so many thanks to the author of that tutorial. If you’re looking for more details on …