1) Relational folks who feel that ORMs don't really enable good relational design, and
2) OO folks who feel that ORM's cause a lot of headache regarding relational to operation data structure conversion.
The folks in camp 1 are usually absolutely right. Most ORMs encourage the developer to design the database around the ORM which has to be an antipattern when it comes to relational systems. This isn't always the case. DBIx::Class has done some awesome work on some things like composite primary keys and the like. But generally in most cases it is true.
On the OO side I am less convinced of the costs. So my complaints come from the relational side. Yes object to operational data structure mapping is hard to automate, and it's even harder to automate well. So maybe we shouldn't try. Maybe ORM's should be disregarded in favor of ways of designing the data so that abstractions occur at different points.
In the LedgerSMB project we decided to use stored procedures to accomplish this abstraction. We don't put "all the logic" in the database, far from it. But database operations go through the stored procedures, and these are often well tuned for performance (or as well tuned as they can be). The stored procedures thus create an API for addressing relational data, and this allows an abstraction layer to be formed which maps these into object methods. We'll probably extend this further in the future to tie object properties to complex database types, but we haven't decided the best way to do this yet.
ORMs get a lot of hate because they tackle a very difficult problem and consequently tend to produce results that are unsatisfactory particularly to db folks. They are the product of app designers looking at the db, more often than not, rather than db designers looking at the app.
> In the LedgerSMB project we decided to use stored procedures to accomplish this abstraction.
Why? Stored procedures are nothing more than a bolted-on way to store SQL queries on the database server. Yes, you've create an "API" and an "abstraction" but you've done nothing you couldn't have done in any other language.
> The stored procedures thus create an API
I consider stored procedures to be little more than a shitty "API builder" thrown in for free with your database.
* The programming language generally sucks
* Passing in complex data always sucks
* Source control is non-existent (or sucks)
You've probably had to write an API layer within your code anyway, to deal with the remaining logic requirements, why not store those queries within that layer? Or put them in a further API layer within your own source code?
With PostgreSQL the programming language can be almost anything you want (I have some stored procedures written in Python), the data structures can be reasonably arbitrary (you can build your own data types easily, the better database libraryes already support arrays and dictionaries, and in 9.2 JSON is being shipped by default), and the source control problem is easily solved by having pg_dump output your stored procedures to a file.
The key advantages are then that 1) if you need to take the output of one query and use it in another--especially if they need to be in the same transaction--you can bring the round-trip time down to nothing and 2) this part of your abstraction can now be modified as part of a transaction, making changes to table schemas (which PostgreSQL supports as parts of transactions) something to do painlessly and without downtime on production.
Most of our stored procedures are currently in PL/PGSQL. I don't know that I would say the language sucks. In fact where one is doing set operations, the language is wonderfully expressive and flexible, and one can get more done in it faster than any alternative, again where the operations are set operations.
SQL has some very serious downsides and these are persent in PL/PGSQL, but these aren't that hard to avoid with good db design. Don't get me wrong, I have seen some really horrible PL/PGSQL stored procs in my day, but it doesn't have to be bad code.
As for an additional API layer, yes there is one but it is pretty thin and most of it is pretty heavily automated. I would say that at least 75% of perl module code is just identifying methods to map to stored procedures, so there isn't a lot of code there (figure three lines to map, a sub name, a sproc name, and closing the sub).
What this allows us to do is support multiple applications in multiple languages while offering consistent security and database functionality to these applications. The DB isn't just a data store, it's essentially a meeting point potentially of a larger number of applications.
We have considered adding PL/Perl and PL/Python stored procedures but since most of these are set operations and so they are things that belong in db queries.
As for source control I don't know what really would separate this from a compiled language. You can think of loading stored procedures into the database as essentially like compiling a program. As we move to future versions this will become an even closer comparison, as we start bundling these using PostgreSQL 9.1's extension framework.
Edit: Our stored procs are defined in text files that are checked into a source repository. They are also designed to be rebuildable, and there are utilities for rebuilding stored procs included in the software.
1) Relational folks who feel that ORMs don't really enable good relational design, and
2) OO folks who feel that ORM's cause a lot of headache regarding relational to operation data structure conversion.
The folks in camp 1 are usually absolutely right. Most ORMs encourage the developer to design the database around the ORM which has to be an antipattern when it comes to relational systems. This isn't always the case. DBIx::Class has done some awesome work on some things like composite primary keys and the like. But generally in most cases it is true.
On the OO side I am less convinced of the costs. So my complaints come from the relational side. Yes object to operational data structure mapping is hard to automate, and it's even harder to automate well. So maybe we shouldn't try. Maybe ORM's should be disregarded in favor of ways of designing the data so that abstractions occur at different points.
In the LedgerSMB project we decided to use stored procedures to accomplish this abstraction. We don't put "all the logic" in the database, far from it. But database operations go through the stored procedures, and these are often well tuned for performance (or as well tuned as they can be). The stored procedures thus create an API for addressing relational data, and this allows an abstraction layer to be formed which maps these into object methods. We'll probably extend this further in the future to tie object properties to complex database types, but we haven't decided the best way to do this yet.
ORMs get a lot of hate because they tackle a very difficult problem and consequently tend to produce results that are unsatisfactory particularly to db folks. They are the product of app designers looking at the db, more often than not, rather than db designers looking at the app.