Derby Datasource: Embedded or Client

I attended CFUnited 2009 last week and I had a really great time. The last presentation that I attended was "Using Apache Derby, the Open Source Database Embedded in ColdFusion 8" by Charlie Arehart. I went to this presentation partly because I am interested in learning more about Apache Derby and partly because I try to catch at least one Charlie Arehart presentation per ColdFusion conference.

As usual for Charlie, the whole presentation was well delivered and packed with useful information. Most significant for me, however, was the difference between the two kinds of Derby datasources.

ColdFusion offers two options when creating a Derby datasource: "Apache Derby Client" and "Apache Derby Embedded". Prior to the presentation, I didn't really get the difference. I figured I wouldn't be the only person to find this information valuable.

In short, "Apache Derby Client" works like most other datasources - you create the database externally and can use it from your ColdFusion instance or elsewhere, whereas with "Apache Derby Embedded" you can create the database directly from ColdFusion but only that instance of ColdFusion has access to it. So, why when would you choose one over the other?

Apache Derby Client

If you choose this option, you will need to create the database separately with a tool such as Aqua Data Studio. Use this option in any of the following situations:

• You need to access your database from multiple instances of ColdFusion.
• You want to manage your database with a visual tool.
• You need to access your database from any other tool or process outside of ColdFusion.

Apache Derby Embedded

If you choose this option, you can have ColdFusion create the database for you but you will have to create the database schema from code with no GUI. Use this option if all the following are true:

• You don't need any of the options list for "Apache Derby Client"
• You are going to use a tool such as DataMgr or ColdFusion 9 ORM to create your database schema (or you don't mind writing the DDL SQL yourself)

As a side note, ColdFusion 9 uses version 10.4 which includes a fix for a nasty BLOB/CLOB bug. So if you found Apache Derby unusable in ColdFusion 8 because of that bug, it is now worth another look.

Charlie's presentation also had plenty of other great information about Apache Derby. You can also watch the "Using Apache Derby" presentation that Charlie did at Max NA 2008 which will probably be pretty similar to the one he gave at CFUnited 2009.