Andrew's ramblings

Using ColdFusion ORM with Legacy Databases

Tags: Coldfusion, CFORM

One of the best things an ORM can introduce for us ColdFusion developers is the ability to, create mappings or persistent components for our legacy databases. Now don't get confused here, legacy is a term that is used for databases that were not created with an ORM.

However in ColdFusion their is a major trap that one needs to be very careful about, and although it shows another problem that ColdFusion has never addressed. This is something that we as developers need to be aware of when we begin migrating over to using the new ORM type features of ColdFusion

So first of all lets look at a typical table that can exist, and the problems that it can cause when we begin to use CF-ORM to map this into ColdFusion 9.

The following table has no primary key, and can exist in a lot of databases today.

To map this into ColdFusion we are going to need to do a couple of extra things, so be very aware that if you try to do it this way it will not work.

So even though we have defined this as exactly how it is in the database schema for our mapped persistent component, ColdFusion will throw an error. And the reason behind that is the fact that there is no primary key, so the thing is we have to manually also add a primary key as well.

So we would need to modify the code to make it look like this.

So even though it is possible to manually modify the component, it would also be very nice if the primary key part was handled under the hood by ColdFusion.

It was suggested that ColdFusion could automatically use by default something like this, and we could just go ahead and just map knowing that we don't have to go back and forth to edit the components each and every time.

And yet if we run our application, and was to then inspect the database, we would find that there is by default a new column created in the database. And the developer has had to not worry about mapping to a legacy database, and remember to add the primary key for each table mapped that has not got one.

With a very large amount of tables, the amount of time this would save if we didn't have to keep remembering to include it would be huge, as we would not be going back and forth from running the application, editing, for every mapping that we forgot to manually add the primary key into.

So let's hope that Adobe listens to us and the change for the ID field be created in the database auto, and allow us to not have to worry about it.