Opinion survey about ORM / DAL tools

[progmars]

Hi all.

I am writing some study work about Object Relational Mapping / Data Access Layer development technologies and tools (like Microsoft ADO/DAO, LINQ to SQL, Entity Framework, (N)Hibernate, Doctrine, Apache iBatis and other SQL / CRUD generators and mappers). I would like to know other programmers' thoughts about the subject so my work is not based only on my own subjective opinion.

If someone here has experience with ORM / DAL tools (or have created own ORM / DAL) then I would be grateful to know about your findings. I can suggest some factors that you can take as a basis:

learning curve - how hard / easy it was to get into this technology, how long did it take; was the quality of the documentation appropriate?

introducing the technology into an existing project - how long did it take, was it hard, what problems did you encounter? Where there any problems - "deal breakers" that made you choose not to use some certain ORM ?DAL development tool?

starting a new project and choosing the ORM / DAL technology - how much the ORM ?DAL dictates the rules for an architecture of the new project? where there any compromises needed just to adapt your project architecture to the ORM / DAL technology?

porting some project to another data base / working environment - how did ORM / DAL tools help or create additional issues?

flexibility - if project specifications changed, how the ORM / DAL reacted? Was there any need to throw away existing ORM / DAL and choose another or create your own layer from scratch?

And considering all the above, what would be your ideal ORM / DAL tool, what features should it have and what should it avoid? Do you prefer high abstraction from SQL or maybe a tool that generates modifiable SQL / CRUD that you can tweak later and also the changes do not get lost when you use the tool to regenerate something?

And other useful ideas about this topic are welcome.

Thanks.

[Jorgon]

My day job is working with databases and working in .Net. I have also participated in a couple Give Camps where we had to create large data layers on extreme time limits. I will be referring to .Net in my post.

I have dealt with a couple ORM libs and manual CRUD. Of the ORMs, I have used Castle Activerecord the most, which uses/simplifies NHibernate. I have also played with Telrik's ORM, but not enough to comment on.

In my experience, the main thing you want to think about is speed of development vs speed of execution. If you need it done on short order, the ORM tools are great, and let you just create objects and let it handle the rest. It always seems that an ORM adds a couple orders of magnitude to execution time. Don't take my word for it though, I haven't benchmarked anything.

I have written my own ORM, which was just an easy way to generate CRUD and update members, and I did not run into the same speed issues from activerecord.

Addressing your questions specifically...

Learning Curve: Most are pretty easy in my experience. Most just inherit from one of their base classes, and add decorators to the class for which members to manipulate.

introducing the technology into an existing project: This should be very easy if the project was designed with a set of classes as the data model already. All you have to do is subclass/decorate and they are useable as is.

starting a new project and choosing the ORM / DAL technology: If you need speed, CRUD is necessary. If you don't, any of the ORMs will work good enough for how much work they save. As for which ORM, they are all very similar, but I don't have in depth experience with many of them. Pretty much any data project you make will need a data object model, which is the basis for all ORMs.

porting some project to another data base / working environment: ORMs make this easy. You can just change a few lines in a config file and it just works, assuming you are not directly accessing the database anywhere.

flexibility: Again, ORMs are great about this. Just add a new member, decorate it, and update your data structure. Sometimes you have to do things outside the ORM because they do not support it directly. Last project I used activerecord on, I encountered a couple of these situations. I was able to write a manual way of doing what I needed to (I cannot remember what the specific problem was, but I know I was able to work around it). Speed is the only reason you would need to throw an ORM out.

Ideal: I would probably look for an ORM tool that statically generated CRUD, to get the best of both worlds. Also what all of the ORMs to my knowledge lack, is a way of automatically keeping a database structure updated. Most can create the structure initially, but they cannot do much past that.

I don't have a ton of experience with these tools, but I feel confident I have enough to make an informed opinion.

If I am wrong about any of these points, or anybody has some more information, I would gladly be corrected.

[progmars]

Thanks, Jorgon, for your post, it is really useful. And it also confirms some of my own opinions .

[progmars]

Maybe someone else has something more to tell about the experience with ORM?