July 3, 2011 at 13:55Is ORM needed in a large and complex Enterprise project?
Recently a question flashed on Habré - is ORM really needed in a large and complex project? After all, it is often slow, cumbersome, supports only a subset of SQL, does not support a specific and very convenient syntax, for example, Oracle (the same connect_by for hierarchical queries) and so on and so forth.
It was suggested that ORM is actually needed only in primitive projects, to reduce the size of the code, and in a really large and complex project it is better to do without it. I will say for large projects - let others say for small ones :) I will make a reservation that I build arguments and examples on Java / Oracle, a classic bunch.
ORM itself, just like mapping, in large projects is needed very much.
Imagine - I have a very large system, and it has a table of orders, say, 50 columns on it (in fact, we have 150, but oh well. Normalizers, keep quiet! I also know about normal forms). And now you need to imagine that you select one order and show it on the screen. Suppose you write a selection, the question is - what next to do with its results, in the middle layer, in server-side Java? You do not call the stored procedure or request directly from, say, a JSP page (I hope), you still need to get the data and transfer it somehow, in the form of some kind of data structure.
So, pass them as an array, ArrayList-a, an associative array as [column name: value]? It is bulky, uncomfortable, and very easy to make mistakes. And if you need several orders, then what, create nested collections to convert the results? This in its harmful effect will already resemble the well-known antipattern "String typing." Use a collection of vectors or associative arrays where you can use a collection of objects of type Order.
We found an example of a case, we need an Order object that has all the necessary properties, and therefore, we need a code that can convert the results of an SQL query into objects (a collection of such objects).
Further, it is obvious that writing with your hands all queries is difficult and tedious, it is easy to make mistakes, because in Java, they will be represented either in the code as strings (which means that there is no static typing and compile-time checks), and they must be kept either in Java code (if they are small, and it still garbage Java code), or if larger, export to separate XML files.
Here is a slight digression. The debate is whether to keep the SQL code of short queries directly in Java code, or whether to export them to separate files in Enterprise-e has been going on for a long time.
The argument is to keep from inside the Java code - the code used together is nearby, the SQL query code is easier to find and see than if it lies somewhere separately, in case of changes in VCS, you will need to remember to change only one file, not two.
The argument for splitting into different classes - multi-line SQL queries litter Java code (including because Java does not fully support multi-line string literals, unlike, say, the same Groovy) + if they are defined as class constants, then when the content changes To make this constant java hotswap, for example, will fail. I usually look at the circumstances myself :)
In general, ORM in large projects is needed to simplify the routine. Nowhere without it :)
Of course, ORM cannot be dispensed ONLY. We have a lot of places where complex logic is written in PL / SQL stored procedure queries of 500-1000 lines, because written through ORM / Java it would take up 10 times more space and work 2 times slower (at the same time, it was would be even less understandable, because there is such a logic that is described in terms of relational algebra easier than in terms of OOP :), and such logic rests on ORM with a creak). Any complicated queries with subqueries, unions, tricky joins are also cumbersome to write through pure ORM. It is also extremely difficult to optimize queries that work with tables where at least several hundred million records are used, without access to the SQL optimizer plans and statistics / DBMS level monitoring tools (for Oracle, Explain Plan, tkprof, Grid Control). So without SQL, too - nowhere :)
You can talk about how to nevertheless slightly sweeten working with pure SQL queries in a large project.
One option is to use macros. It writes its own framework for writing SQL macros (in essence, an engine for executing moderately primitive parsers that allows people to write their own macros and parsers for them). Macros can help solve the following problems:
and much more.
Yes, and finally. I didn’t say a word here about compatibility with different DBMSs, because this is often an unnecessary requirement. Large and complex systems actively use DBMS-specific features (SQL syntax extensions, the syntax of procedural extensions such as PL-SQL, T-SQL, pgplsql generally differs very much, partition schemes and so on and so forth), and the requirements to write SQL code simultaneously compatible with Oracle / MSSQL / DB2 is often not installed (I have listed the classic recognized Enterprise-level DBMSs here, please do not be offended by fans of the rest). Everyone understands that this is a huge complication and appreciation of the system, far from always justified. True independence from a serious vendor, such as Oracle or IBM, is expensive, very expensive. But how often is it really needed?
It was suggested that ORM is actually needed only in primitive projects, to reduce the size of the code, and in a really large and complex project it is better to do without it. I will say for large projects - let others say for small ones :) I will make a reservation that I build arguments and examples on Java / Oracle, a classic bunch.
Why ORM is needed
ORM itself, just like mapping, in large projects is needed very much.
Imagine - I have a very large system, and it has a table of orders, say, 50 columns on it (in fact, we have 150, but oh well. Normalizers, keep quiet! I also know about normal forms). And now you need to imagine that you select one order and show it on the screen. Suppose you write a selection, the question is - what next to do with its results, in the middle layer, in server-side Java? You do not call the stored procedure or request directly from, say, a JSP page (I hope), you still need to get the data and transfer it somehow, in the form of some kind of data structure.
So, pass them as an array, ArrayList-a, an associative array as [column name: value]? It is bulky, uncomfortable, and very easy to make mistakes. And if you need several orders, then what, create nested collections to convert the results? This in its harmful effect will already resemble the well-known antipattern "String typing." Use a collection of vectors or associative arrays where you can use a collection of objects of type Order.
We found an example of a case, we need an Order object that has all the necessary properties, and therefore, we need a code that can convert the results of an SQL query into objects (a collection of such objects).
Further, it is obvious that writing with your hands all queries is difficult and tedious, it is easy to make mistakes, because in Java, they will be represented either in the code as strings (which means that there is no static typing and compile-time checks), and they must be kept either in Java code (if they are small, and it still garbage Java code), or if larger, export to separate XML files.
Here is a slight digression. The debate is whether to keep the SQL code of short queries directly in Java code, or whether to export them to separate files in Enterprise-e has been going on for a long time.
The argument is to keep from inside the Java code - the code used together is nearby, the SQL query code is easier to find and see than if it lies somewhere separately, in case of changes in VCS, you will need to remember to change only one file, not two.
The argument for splitting into different classes - multi-line SQL queries litter Java code (including because Java does not fully support multi-line string literals, unlike, say, the same Groovy) + if they are defined as class constants, then when the content changes To make this constant java hotswap, for example, will fail. I usually look at the circumstances myself :)
In general, ORM in large projects is needed to simplify the routine. Nowhere without it :)
Why you need pure SQL
Of course, ORM cannot be dispensed ONLY. We have a lot of places where complex logic is written in PL / SQL stored procedure queries of 500-1000 lines, because written through ORM / Java it would take up 10 times more space and work 2 times slower (at the same time, it was would be even less understandable, because there is such a logic that is described in terms of relational algebra easier than in terms of OOP :), and such logic rests on ORM with a creak). Any complicated queries with subqueries, unions, tricky joins are also cumbersome to write through pure ORM. It is also extremely difficult to optimize queries that work with tables where at least several hundred million records are used, without access to the SQL optimizer plans and statistics / DBMS level monitoring tools (for Oracle, Explain Plan, tkprof, Grid Control). So without SQL, too - nowhere :)
You can talk about how to nevertheless slightly sweeten working with pure SQL queries in a large project.
One option is to use macros. It writes its own framework for writing SQL macros (in essence, an engine for executing moderately primitive parsers that allows people to write their own macros and parsers for them). Macros can help solve the following problems:
- Removing the reusable parts of queries (usually subqueries) into separate, let's say, named SQL components, and then including them in queries using a macro
- Unification of some annoying differences in SQL syntax, for example, when a query parameter can be a scalar number, or it can be an array, then you write a macro that abstracts you from the difference between t.property = x and t.property in (x1, x2)
- Adding predicates to where clause for security reasons
- Wrapping the entire query in SELECT * FROM (query body) t order by ... for unified sorting and paging-a support
and much more.
On portability between different DBMS
Yes, and finally. I didn’t say a word here about compatibility with different DBMSs, because this is often an unnecessary requirement. Large and complex systems actively use DBMS-specific features (SQL syntax extensions, the syntax of procedural extensions such as PL-SQL, T-SQL, pgplsql generally differs very much, partition schemes and so on and so forth), and the requirements to write SQL code simultaneously compatible with Oracle / MSSQL / DB2 is often not installed (I have listed the classic recognized Enterprise-level DBMSs here, please do not be offended by fans of the rest). Everyone understands that this is a huge complication and appreciation of the system, far from always justified. True independence from a serious vendor, such as Oracle or IBM, is expensive, very expensive. But how often is it really needed?