Andrew's ramblings

Should you use pure CF-ORM or a mixture of CF-ORM and SQL

Tags: General, Coldfusion, ColdFusion 9, Hibernate, Database, CFORM

I have been meaning to write this up for some time now, and haven't had the time to do so. One of the things that I found when learning CF-ORM for the first time, was it was very over whelming  to say the least and a lot of questions would come from trying to use it.

Now 2 years down the track and I can say that there are times to use it and times not to use it, and it all boils down to whether you want the application to be scalable or you just don't care, because you know the amount of traffic will never get that high to matter.

Either way I want to touch on some things that I did discover, where I quickly changed my thinking as I went along.

Since ColdFusion introduced hibernate under the hood, I was very keen and eager to get down and use it to its max. Now a few years down the track a lot of the code that I had written for CF-ORM or HQL has been re-written to normal SQL, and it wasn't because of performance or scalability issues, but purely that HQL was just so limiting.

If you have seen my blogs over the last few days, you will have seen me talking about OVER PARTITION in MS SQL, in which I tried to achieve the same results in CF-ORM and HQL that I just couldn't get to work reliably, and if I did then trying to make modifications to it was just a pain in the rear end.

But that didn't mean that I abandoned CF-ORM altogether either, no way in hell was I going to give in and not use this technology, which leads me to this post, and what I ended up doing.

First I want to take the opportunity to talk about the pros of using CF-ORM, the first and foremost is the ability to make changes to an entity and know that this will be mapped straight to the database, the fact that I can define default properties and know that when I create the entity I get defaults. But the most important of all is the added benefit of creating code that will do further logic in your getters and setters, not to mention events that can take further action of logging and other such things with minimal effort.

These sort of things tend to make our day as a developer easier, because we end up writing less code.

But the most important of all this, is that we can also have these versioned controlled with greater ease. Now I know not everyone uses a revision control system, and if you did each time you made a change and released or applied that revision to your application, you are then having a snapshot of that database schema.

I think most people can see that the benefits of having and using ORM at this point is fairly good, and that it can and does save us some time and effort at the database level. Granted it takes a bit of getting used to and understanding, but you will find it very rewarding.

Ok but what about using this in my application?

Well after completing a major rewrite of an application, where I wanted to use ColdBox and CF-ORM to make the process as quick an easy as possible. I soon hit brick walls with the CF-ORM side of things, mainly because of HQL and not of CF-ORM in general.

So the solution that I took when I sat back and began looking at the problem, was that there is no reason that I actually have to use HQL in these cases, and once I took that approach I had finished this work in about 2 weeks.

The approach that I took was that any time I needed complex queries, like to aggregated data I simply just wrote normal queries. So what it boiled down to if we take another look at this, was that I ended up using Entites for basic crud stuff, now that might seem like over kill to some or most of you. But think of it this way, if you are aware of DAO and gateways, then you will appreciate the work that these Entities save in coding.

Even when I was using HQL to do the work for most of what I had written, the majority of these were using the feature of turning them into normal queries anyway. But in the end it wasn't hard to rewrite these back to normal queries, and continue on my way.

The downside to using normal queries, or even turning you HQL into normal queries is that you do loose the extra functionality inside of you Entity, but for me that was not a big deal as I wasn't using much of that anyway. However when deciding for yourself you will need to take the time to work out what it is you plan from your application, and how your application is going to use CF-ORM.

I really wanted to just write this because I have read and seen how people can be overwhelmed by CF-ORM, and that there really isn't anything to be afraid off. And that it is very acceptable to mix the new with the old to get your job done.

In this example I am talking about an application that gets roughly 45K hits a month, not a high traffic site, but a site that was struggling with the way it had been written and maintained. The site would require to be rebooted each day sometimes 3 times a day, and this was just not acceptable for such a low site.

So when I eventually finished the majority of this site in a few weeks, I gave it a test to see how well it would perform. And the site is now capable, without making any modifications to IIS, of hitting 5000 concurrent users without breaking a sweat. Which made me very happy because I wasn't sure how the mixing of CF-ORM and normal SQL would work out, and I think the results speak for themselves.

In conclusion if you walk away from this blog post with anything, it should be that it is very much ok to mix n match and still get the job done. Especially if you really want to get the hang of CF-ORM, and just want to make baby steps into the world. But more importantly that if you want and need to use your database to take full advantage of its way to scale, by all means make sure you do. Because one of the downsides to hibernate is that it has to be as generic as possible, and that means that it is not optimised for one database, and that it requires to be able to run across all that it supports.