Using ORM with stored procedures, a contradiction?

Question

I've inherited a web application which strictly uses stored procedures to do its job. I like the approach of making it impossible for frontend developers to break the database, but I've been tired of writing calls to SPs with plain SQLs and wanted to have something saner. While I've been looking for a decent ORM (for Perl in this case, but that isn't relevant for the question) with support for stored procedures, I've realized that ORM could be a direct contradiction to SPs.

My thinking is that SPs are, like the name already tells us, procedures, i.e. representatives of procedural Pascal-style of programming, in fact, that one web application looks exactly like Pascal on the SQL-Server side -- many functions, no real namespacing. Contrasting to that, we are trying to do most of our programming OOP-style (or functional, which is a yet another topic), so actually procedural SPs are not really a good fit for clean object hierarchies. At the same time, relational logic can be converted to objects cleanly (via an ORM), but not the procedural, which is probably why most ORMs don't support SPs very well (but I'm not an expert in that field). In some sense, SPs are ORMs.

So the two questions are:

1. Am I right assuming we are better off using plain tables when running an ORM?
2. Are there any "object-oriented stored procedures" on the market, building up from relational model? Clearly, there are object-oriented databases, but I'm interested in "ORM on the server side".

Answer 1

"Am I right assuming we are better off using plain tables when running an ORM?"

Yes.

The RDBMS should be focused on persistent storage, and nothing more.

If you do this, you will find that you can -- easily -- build an access layer in your OO language. All the "front-end" developers must use the access layer and cannot break the database.

"object-oriented stored procedures?"

Oracle has some OO-like features of PL/SQL.

Don't waste any time on it. Focus on a clean separation between the persistence (in the RDBMS) and the application processing (not in the RDBMS).

Many, many people will send you hate mail saying things like "the vendor put all those features in there, that means you should use them" and "what's wrong with stored procedures?" and "a good DBA is better than a room full of front-end developers" etc. etc.

I don't know why folks claim stored procedures are "better", yet many system eventually reaches the wall where the stored procedures and triggers got so burdensome that it had to be rewritten.

I've never seen anyone complain that they had too much application software outside the database.

Continue to follow your thoughts here -- use ORM -- avoid stored procedures.

Answer 2

Most ORM tools (that I've used. I'm in the .NET world) provide a mechanism for using stored procedures. Because ORM tools (again, the ones I've used) like to select all columns by default so they're all loaded into object graphs, you generally have to write your SPROCs to select all columns that are part of your object graph. This is the only major oddity I've run into when using an ORM package.

There are usually ways to optimize your SPROC calls in ORM tools so they don't need to select all columns, however that's usually more advanced.

I'd say it's safe to do, but generally you'll only want/need to do it when you need to optimize something that would be slow through normal ORM methods.

Answer 3

In .NET, LINQ Data Class will generate a strongly typed class for a procedure. You call the procedure like:

foreach (var customer in db.GetCustomers())
    Console.WriteLine(customer.firstName);

Where GetCustomers() is a stored procedure in the database. But you can't update the returned customer and submit the changes to the database. An ORM can only do that with plain tables.

My experience is opposite to S.Lot's, a stored procedure layer is keeping my database consistent, clean and fast. I guess it depends on the size and complexity of the application, and how well you know SQL.

Answer 4

This issue does not have a clear cut black and white answer. There are numerous conflicting arguments on both sides, and I, (imho), do not as yet see a clear consensus emerging. For an opposing view, with rational arguments pro-con, see ORM - Vietnam of CS, or another ORM link.