Code generators vs. ORMs vs. Stored Procedures

Question

In what domains do each of these software architectures shine or fail?

Which key requirements would prompt you to choose one over the other?

Please assume that you have developers available who can do good object oriented code as well as good database development.

Also, please avoid holy wars :) all three technologies have pros and cons, I'm interested in where is most appropriate to use which.

Answer 1

I agree that there are pros and cons to everything and a lot depends on your architecture. That being said, I try to use ORM's where it makes sense. A lot of the functionality is already there and usually they help prevent SQL Injection (plus it helps avoid re-inventing the wheel).

Please see these other two posts on the topic (Dynamic SQL vs StoreProcs vs ORM) for more information

Dynamic SQL vs. Stored Procs http://stackoverflow.com/questions/22907/which-is-better-ad-hoc-queries-or-stored-procedures

ORMs vs. Stored Procs http://stackoverflow.com/questions/50346/why-is-parameterized-sql-generated-by-nhibernate-just-as-fast-as-a-stored-proce

Answer 2

ORMs and code generators are kind of on one side of the field, and stored procedures are on another. Typically, it's easier to use ORMs and code generators in greenfield projects, because you can tailor your database schema to match the domain model you create. It's much more difficult to use them with legacy projects, because once software is written with a "data-first" mindset, it's difficult to wrap it with a domain model.

That being said, all three of the approaches have value. Stored procedures can be easier to optimize, but it can be tempting to put business logic in them that may be repeated in the application itself. ORMs work well if your schema matches the concept of the ORM, but can be difficult to customize if not. Code generators can be a nice middle ground, because they provide some of the benefits of an ORM but allow customization of the generated code -- however, if you get into the habit of altering the generated code, you then have two problems, because you will have to alter it each time you re-generate it.

There is no one true answer, but I tend more towards the ORM side because I believe it makes more sense to think with an object-first mindset.

Answer 3

Stored Procedures

• Pros: Encapsulates data access code and is application-independent
• Cons: Can be RDBMS-specific and increase development time

ORM

At least some ORMs allow mapping to stored procedures

• Pros: Abstracts data access code and allows entity objects to be written in domain-specific way
• Cons: Possible performance overhead and limited mapping capability

Code generation

• Pros: Can be used to generate stored-proc based code or an ORM or a mix of both
• Cons: Code generator layer may have to be maintained in addition to understanding generated code

Answer 4

Every one of these tools provides differing layers of abstraction, along with differing points to override behavior. These are architecture choices, and all architectural choices depend on trade-offs between technology, control, and organization, both of the application itself and the environment where it will be deployed.

• If you're dealing with a culture where DBAs 'rule the roost', then a stored-procedure-based architecture will be easier to deploy. On the other hand, it can be very difficult to manage and version stored procedures.

• Code generators shine when you use statically-typed languages, because you can catch errors at compile-time instead of at run-time.

• ORMs are ideal for integration tools, where you may need to deal with different RDBMSes and schemas on an installation-to-installation basis. Change one map and your application goes from working with PeopleSoft on Oracle to working with Microsoft Dynamics on SQL Server.

I've seen applications where Generated Code is used to interface with Stored Procedures, because the stored procedures could be tweaked to get around limitations in the code generator.

Ultimately the only correct answer will depend upon the problem you're trying to solve and the environment where the solution needs to execute. Anything else is arguing the correct pronunciation of 'potato'.

Answer 5

I'll add my two cents:

Stored procedures

• Can be easily optimized
• Abstract fundamental business rules, enhancing data integrity
• Provide a good security model (no need to grant read or write permissions to a front facing db user)
• Shine when you have many application accessing the same data

ORMs

• Let you concentrate only on the domain and have a more "pure" object oriented approach to development
• Shine when your application must be cross db compatible
• Shine when your application is mostly driven by behaviour instead of data

Code Generators

• Provide you similar benefits as ORMs, with higher maintenance costs, but with better customizability.
• Are generally superior to ORMs in that ORMs tend to trade compile-time errors for runtime errors, which is generally to be avoided

Answer 6

You forgot a significant option that deserves a category of its own: a hybrid data mapping framework such as iBatis.

I have been pleased with iBatis because it lets your OO code remain OO in nature, and your database remain relational in nature, and solves the impedance mismatch by adding a third abstraction (the mapping layer between the objects and the relations) that is responsible for mapping the two, rather than trying to force fit one paradigm into the other.