Saturday, 16 August 2008

ORM Frameworks and DBA Friendliness

Database programming used to directly rely on tools provided by the database vendors, such as ESQL-C from Informix, Pro*C from Oracle and Sybase. Using these tools makes the business logic (C/C++ code) mingled with and dependent on the database design and logic (SQL code).

The emergence of Object-Relational Mapping (ORM) frameworks solved this problem by decoupling the code and the database assets. Nowadays, when designing software, it's not a matter of using ORM or not for the designer, but rather which ORM framework to use.

Most ORM or entity frameworks (Hibernate, JPA, Entity Framework) out there generate SQL statements automatically and hide the internals from the developers and rightly so. However, this creates another problem: to optimise the SQL statements, it is usually a joint effort between the developers and DBAs (or data architects) - the DBA should have the final say in how to shape the SQL queries and to design/modify the database schema to suit the usage pattern of the application(s). Now that the SQL statements are auto-generated by the framework and the DBA cannot design or examine the SQL code up front, the control of the DBA is diminished.

This loss of control may not be a problem for custom-built/one-time applications, or applications with a simply data model. However, if you are dealing with a large/complex product that has to be implemented in different environments, then it is crucial to have full control over the database deisgn and deployment and how it is to be used.

To have the visibility to the SQL statements up front, there seems to be two options:

  1. resort back to JDBC/ADO.NET... to mix the SQL statements with the business logic code - not advisable
  2. use a framework that exposes the SQL statements - iBATIS SQLMap

Strictly speaking, iBATIS is not an ORM framework - it maps the objects with SQL statement query input/output. There are two distinct advantages of using SQL Map:

  1. The database-specific code (SQL statements, connection strings, etc.) can be centralised and totally separated from the core (Java/C#...) code. This makes the code decoupled from the database and more portable across different databases;
  2. All database CRUD are specified in native SQL which are DBA friendly, making it easy for the DBA to review and participate in the design of these statements.

Because iBATIS does not map objects with tables, it makes it ideal for applicaitons that have to use legacy databases which did not adhere to object oriented design or have totally different structure from the object model.

Therefore, if you are involved in software product development (as opposed to custom-built one-time projects), DBA friendliness should be a factor to be considered when deciding an ORM framework.


W.Meints said...

I wonder why it is not advisable to use ADO.NET anymore these days? The framework is rock-solid and with proper attention to the code it will help both database administrators and developers to produce some really high-performance and well maintainable solutions.

I found that most frameworks today make work rather complicated as the performance is sometimes ridiculous. (With ADO.NET entity framework its stupid most of the time...)

I personally feel that with some good templates for e.g. mygeneration you can have a very good solution for data-access.

I'm interested in your view on this ;)

Romen said...

I believe that every tool has its place. You can certainly achieve the separation of concerns by writing your own data access layer using ADO.NET and isolate all the SQL codes from the business logic.

However, if there are frameworks out there that already does it (which may very well use ADO.NET under the hood), then I would definitely use it instead. These days software development productivity is very important to remain competitive.