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

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 2

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 applications 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