Best practice in ColdFusion ORM

A question of ColdFusion ORM

We are using ColdFusion 9 for the past 6 months and while we've used some of the new features, ORM is something we've avoided because we usually work on the same very large website. Over the years we've used Apache OBJ but then we moved back to CF and used our own DAO objects generated from tables to handle basic CRUD. These objects are basic and need to be regenerated manually for schema changes and do not model table relationships at all. To supplement these we have a set of gateway classes for multi-table queries. While all this is very bespoke, these DAOs and gateways do, however, give us great control over the SQL we execute, for example using locking hints and optimiser hints. Our site is busy but our database is very efficient. 

So much for the history lesson: the point of this is that we have a new site upcoming soon that will be written from scratch to handle financial transactions. We would normally use our aforementioned  DAO objects to handle CRUD and then the usual set of gateway objects for multi-table joins but I thought we might investigate using baked-in CF ORM...

So with that in mind, I'd love to hear of the lessons, tips and tricks others might have to share in regard to using ORM on开发者_运维问答 a busy financial site. For example:

What's the best way to flush your SQL ? What tips do you have for transactional ORM? How do you setup development and live sites for ORM? What about HQL? When should we just do the SQL ourselves by hand?!

Thanks in advance! 

Short answer, YES! go for ORM!!!

What's the best way to flush your SQL ?

ormflush()

What tips do you have for transactional ORM?

<cftransaction> or transaction {} in CFScript works fine, and even work across DSN's, much improved in 9.0.1! watch http://tv.adobe.com/watch/max-2010-develop/coldfusion-undocumented/

How do you setup development and live sites for ORM?

same as without ORM, but you may look into this.ormSettings.dbCreate if u mean the DB creation part

What about HQL?

What about it? :) Use it when u want an array of objects, but nothing stopping you from using the good old <cfquery>

When should we just do the SQL ourselves by hand?!

When you feel like HQL doesn't do what you want.

Check out: Things to watch out for in ColdFusion 9 with CF-ORM however keep in mind that some points are no longer true for 9.0.1