DAL / Persistence Layer Comparisons

I was curious yesterday about which databases were supported by which ColdFusion Data Access Layers (DALs). I went to check on that and before I knew it, I had compiled quite a few comparisons on each.

Perhaps I should back up at this point and explain what I mean by a DAL. I DAL is any application that manages access to data (usually a database). These are often referred to as an ORM. An ORM, however, is one type of DAL (one that maps objects to a database). An ORM is a DAL, but a DAL need not be an ORM.

The table below represents all of the major ColdFusion DALs and the information that I was able to find about them. If any of my information is incorrect or if I am missing anything, please let me know.

In the interest of fair disclosure, I am the author of DataMgr. That being said, the point of this is just to provide a handy reference.

DataFaucet* DataMgr Reactor Squidhead Transfer
Current Version 1.1 RC 2.2 ??? 2.0 1.1
First Blog Entry 2008-06-10* 2005-08-03 2005-09-26 2007-01-31 2005-11-15
Cost Free Free Free Free Free
Documentation HTML HTML HTML HTML HTML
License BSD LGPL ??? Apache, V2 Common Public
ORM? Yes No Yes No Yes
Need File Write Access? Yes*** No Yes Yes Yes
Database Introspection? Yes Yes Yes Yes No
Database Modification? Yes Yes No No No
Query Caching Yes CF8 ??? ??? No
Object Caching Yes N/A ??? N/A Yes
Download Size 666K 67K 1.90M 243K 2.30M
Expanded Size 1.5M 356K 4.72M 982K 3.34M
Databases
MS Access Yes '97+
MS SQL Server Yes 2000+ 2000+ 2000+ 2000+
MySQL Yes 4.1+ 4+ 5+ 4.1+
Oracle 9i+ 9i+ 9i+ 10g+ 9i+
PostgreSQL 8+ 8+ 8.1+
DB2 Yes
Derby Yes
CFML Servers
ColdFusion Yes 6.1+ Yes 7+** 7+
Open BD 1.0+ 1.0+
Railo 3.0.1+ 3.0.1+ 3.0+

* Previously part of onTap framework.

** Squidhead requires CF8, but the generated code can run on CF7 or better.

*** Generates configuration files from objects, rather than generating the objects themselves (for persistence service only).

If you can think of anything else I should add to this table (or find any information that needs correction), let me know.

Comments
It might be worth adding a "supports caching" column as I know a lot of people who look for this in a DAL. Especially as DataMgr now has a smartcache :)
# Posted By John Whish | 12/19/08 10:16 AM
actually with regard to caching, there are at least 2 different types of cache to consider - object caching (I know transfer does this, but DataFaucet won't until the next release) and Query Caching (DataFaucet currently supports this, but I don't know about the others)
# Posted By ike | 12/19/08 3:54 PM
Two other data access layers also come to mind - FourQ which is part of FarCry Core and SquidHead http://squidhead.riaforge.org/
# Posted By ike | 12/19/08 3:57 PM
First blog entry for DataFaucet was June 10, 2008 - http://datafaucet.riaforge.org/blog/index.cfm/2008...
# Posted By ike | 12/19/08 3:59 PM
Sorry for posting a bunch of comments. ;)

The current version of DataFaucet does not generate code (files) -- and that's my preference in general. Whatever it does is done at run-time.

You might want to add a couple of links or foot notes on "database introspection" and "database modification", maybe to wiki articles like you did for DAL. Modification could link to the wiki article for DDL http://en.wikipedia.org/wiki/Data_Definition_Langu...

PostgreSQL support (lower case G) is unknown with DataFaucet - I've heard that the syntax is mostly the same as Oracle, so in theory the Oracle agent might work, but I haven't tested it. There's a similarly unknown status with regard to support for Sybase because SQL Server was originally based on an older build of Sybase.
# Posted By ike | 12/19/08 4:18 PM
The ActiveSchema page seems like an odd choice for that link... given that it doesn't seem to really describe the way that table creation works in DataMgr. Certainly describing similar concepts tho.
# Posted By ike | 12/19/08 4:43 PM
John and Ike,

Thanks for the suggestions and corrections.

John,

In fairness, I suspect caching is one of the weaker points of DataMgr relative to some of the others.

Ike,

Do you know if FourQ can work independently of FarCry?

I agree that the link on ActiveSchema isn't optimal, but I haven't found one I really like yet. I will probably do an entry on the concept soon. In the meantime, that seems like a decent explanation. I thought about using the DDL page, but that covers what SQL is used for Active Schema, but not what it is.

Let me know of anything else that needs modification.
# Posted By Steve Bryant | 12/19/08 4:53 PM
FourQ is not separable in the current release, no. Not real sure what the other specs are on it either.

I do know that it does the ActiveSchema stuff -- and nothing else -- it won't allow you to use a pre-existing schema, which is one of the ways it differs from these others, so ... I'm not sure how you would label that, but if you're going to add FourQ it might be worth also adding a row to indicate that a given tool is able to use pre-existing entities or schemas (or not in the case of FourQ).

I think right now FourQ is the only one that won't utilize externally generated schemas.

The up-side is that although it limits your control of the schema (actually it seems fairly a lot), for those who dislike working with relational stuff, FourQ is a pretty "Berlin Wall" style abstraction. It's all over their documentation even that basically as far as FarCry is concerned, if you're *thinking* about the database, you're doing something wrong. Whether that's a good thing or a bad thing may be a matter of opinion. :)
# Posted By ike | 12/19/08 5:55 PM
Actually there is already object caching in the BER of DataFaucet -- but I'm waiting until I have time to document it before I push a 1.1 version release.
# Posted By ike | 12/19/08 5:58 PM
Ike,

I think I won't include FourQ since it is not separable from FarCry.

Just let me know when you want me to include object caching in DataFaucet and I will.
# Posted By Steve Bryant | 12/20/08 8:02 AM
FourQ: seems reasonable. Anyway, folks who're interested can read the comments.

Object Caching in DF: I'm not real particular about it -- anyone who's interested can read the comments to see that it's in the BER (and it's mentioned on the DF blog), but if you want to change it to "yes (BER)" you can.
# Posted By ike | 12/20/08 12:30 PM
Just saw this link in a cfdev discussion and wanted to let you know that Reactor runs on Railo out of the box. I've been using it for a few months now on Railo 3.0.1.
# Posted By JAlpino | 1/30/09 11:23 AM
JAlpino,

Thanks for the information. I hadn't seen that support listed anywhere. I have that added to the table now.
# Posted By Steve Bryant | 1/30/09 11:42 AM
Thanks Steve. Just a couple of minor things I noticed. You've got an * note at the bottom that says "previously a part of the onTap framework", but there's no * up next to the name of the project at the top. Not that it matters to me in particular, I'm fine with the note staying or going. File sizes are out of date -- and humorously, the zip archive is now 666KB. :) The expanded size is now 1.5MB. And lastly, this new version is now doing something that I try very hard not to do, which is generate files. Although it may deserve another subtext note that says the files it generates are config files, rather than objects -- most systems that generate files are going the other direction, generating objects from configs. This one is taking the object (your object actually) and analyzing the cfproperty tags and generating a config from that (about the same time that it's installing tables into your db).
# Posted By ike | 10/9/09 10:49 PM
Ike,

I think I have all of those corrections in. Thanks for letting me know about those.

Is the creation of configuration files required or just available as a convenience? Perhaps I should change that line to "Requires File Write Access"?
# Posted By Steve Bryant | 10/12/09 9:12 AM
Thanks Steve. In the current version in order to use the persistence service, you have to install each class you want to use -- so basically you get your DI Factory setup and then you call persistenceService.install("a,b,c") where a, b and c are different classes you're going to use. If it needs tables, it creates those. Otherwise it just creates the config file for each class.

I may find a way to eliminate those config files in a future version, I think it would be nice, but for now that's how they work.

So basically yeah, "requires file write access" would be a good description. They're just files with a WDDX packet in them, so it's not something you're likely to create by hand -- but they're generated from the metadata in your CFCs, so you are actually writing config, you're just writing it in-context.
# Posted By ike | 10/13/09 12:09 AM
Although technically, the file write access is only required for the persistence service, which is just the one big new feature (the one that includes object caching) -- the rest of DF works (as far as I know) without it.
# Posted By ike | 10/13/09 12:12 AM
BlogCFC was created by Raymond Camden. This blog is running version 5.8.001.