ColdFusion ORM can be used to create new tables into an existing database but is not without challenges.

Introducing ColdFusion ORM

In this 3 part series; I will show you how to create new tables into an existing database and how to populate the records into the new tables using ColdFusion ORM. I will be using live examples and show you the steps that I used in Galaxie Blog when converting the original BlogCfc database into a new set of tables. Here we will introduce CF-ORM, cover alternatives, and discuss solutions that were used to overcome some common pitfalls when using CF-ORM.

When should ColdFusion ORM be used?

CF-ORM may be a viable solution when you want to have the widest support of databases for your application. I am incorporating CF-ORM into Galaxie Blog as I want the end-users to be able to use their own database of choice. Since version 9, CF-ORM is already included in ColdFusion, there is nothing new needed to be loaded, and CF-ORM should support any modern database. Another benefit of using CF-ORM, or any ORM product, is that the database logic is decoupled from the business logic of the application. I can't understate this benefit enough- this approach is vastly more efficient than having to rewrite the SQL queries in multiple areas of the business logic every time you add or remove a column from the database. I created my own CF-ORM package for SQL Server for this very reason.

ColdFusion ORM Supported Databases

Apache Derby Derby
Informix Informix
IBM DB2 AS400 DB2AS400
IBM DB2 OS390 DB2OS390
Postgre SQL PostgreSQL
Microsoft Access MicrosoftSQLServer
Microsoft SQL Server MicrosoftSQLServer
MySQL InnoDB MySQLwithInnoDB
Oracle 8i Oracle8i
Oracle 9i Oracle9i
Oracle 10g Oracle10g
Sybase Sybase
Sybase Anywhere SybaseAnywhere

ColdFusion ORM Supported Databases with a Custom Dialect

The following databases should be supported as long as you provide the custom dialect using the fully qualified class name.

Oracle (any version) org.hibernate.dialect.OracleDialect
Oracle 11g org.hibernate.dialect.Oracle10gDialect
Microsoft SQL Server 2000 org.hibernate.dialect.SQLServerDialect
Microsoft SQL Server 2005 org.hibernate.dialect.SQLServer2005Dialect
Microsoft SQL Server 2008 org.hibernate.dialect.SQLServer2008Dialect
SAP DB org.hibernate.dialect.SAPDBDialect
Informix org.hibernate.dialect.InformixDialect
Hypersonic SQL org.hibernate.dialect.HSQLDialect
H2 Database org.hibernate.dialect.H2Dialect
Ingres org.hibernate.dialect.IngresDialect
Progress org.hibernate.dialect.ProgressDialect
Mckoi SQL org.hibernate.dialect.MckoiDialect
Interbase org.hibernate.dialect.InterbaseDialect
Pointbase org.hibernate.dialect.PointbaseDialect
FrontBase org.hibernate.dialect.FrontbaseDialect
Firebird org.hibernate.dialect.FirebirdDialect

However, ColdFusion ORM is not without its challenges

While having CF-ORM support all database platforms is terrific; using CF-ORM may lock you into using a particular ColdFusion version. CF-ORM functionality changes depending upon the version of ColdFusion. CF-ORM on ColdFusion 9 is quite a bit different than CF-ORM on ColdFusion 2016. So, while you may be supporting a wide variety of database platforms, you may be stuck on a particular ColdFusion version. There are also differences between CF-ORM between ColdFusion, and its open-source sister, Lucee.

Also, CF-ORM has a slew of other challenges. Error reporting is confusing. Since CF-ORM is built upon Hibernate, it does not have the error messages that we typically expect out of a ColdFusion product. Error messages are not always available and are often confusing. The documentation is sparse, and at times misleading. Take for example the cfproperty documentation on the Adobe site. Most of the properties are not documented at all. Two-thirds of the description columns are blank.

Additionally, if you dig around and find documentation for a certain feature, it may not work at all! For example, I wanted to eliminate the database constraint for some of the database columns using the constrained="false" argument. Adobe's own cfproperty documentation mentioned the constrained property, but gave no description. Finally, I was able to find some documentation on the constrained property on another site, however, no matter what I did, I could not get the argument to work. I also went to the ORM channel on cfslack to ask for help, and no one knew how to get it to work.

Finally, another issue is that just like ColdFusion UI, CF-ORM is tied to Adobe ColdFusion and you're limited in what changes you can make. You'll have to wait until a new ColdFusion version to come out before you can upgrade to a new version of Hibernate.

ColdFusion ORM alternatives

While I have not used them yet myself, I have heard good things about Quick and cbOrm. Quick is written in ColdFusion and it handles errors much better than CF-ORM. cbOrm is an extension of CF-ORM, and Ortus has added a lot of functionality and flexibility to the underlying Hibernate engine that CF-ORM uses. I won't cover either Quick or cbOrm here, but they are both worth recommending if you want to avoid CF-ORM, especially if you're already using the ColdBox framework.

Since my project requires that I support as many database platforms possible, and I don't want to include another library, such as ColdBox, I am using CF-ORM and will show you some of the solutions that I used to overcome common CF-ORM pitfalls.

In the next article, we'll jump in and look at the code....