Using ORM with stored procedures, a contradiction?

Question

I have inherited a web application that strictly uses stored procedures to do its job. I like the approach that allows frontend developers to break up the database, but I'm tired of writing calls to SP using simple SQL queries and wanted to have something sensible. Although I was looking for a decent ORM (for this in Perl, but this does not apply to the issue) with support for stored procedures, I realized that ORM could be a direct contradiction with SP.

My thinking is that SPs, like the name, already tells us the procedures, that is, representatives of Pascal procedural programming, in fact, that one web application looks exactly like Pascal on the SQL-Server side - many functions, no real namespace. In contrast, we are trying to do most of our OOP style programming (or functional, which is another topic), which is why procedural SPs are not really suitable for pure object hierarchies. At the same time, relational logic can be easily converted to objects (via ORM), but not procedural, so probably most ORMs do not support SPs very well (but I'm not an expert in this area). In a sense, SPs are ORMs.

So, two questions:

• Do I believe that we are better off using simple tables when running ORM?
• Are there any “object-oriented stored procedures" in the market based on the relational model? Clearly, there are object-oriented databases, but I'm interested in server-side ORM.

Answer 1

"Am I right in believing that we are better off using simple tables when working with ORM?"

Yes.

RDBMS should be focused on persistent storage and nothing more.

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

"object oriented stored procedures?"

Oracle has some OO-like PL / SQL functions.

Do not waste time on this. Focus on a clear separation between persistence (in the RDBMS) and application processing (rather than in the DBMS).

Many, many people will send you letters of hate saying things like "the seller put all these functions there, so you have to use them" and "what happened to the stored procedures?" and "a good database administrator is better than a room with full developers," etc. etc.

I don’t know why people claim that stored procedures are “better,” but many systems end up reaching the wall where stored procedures and triggers become so burdensome that they need to be rewritten.

I have never seen anyone complain that they have too much application software outside the database.

Keep following your thoughts here - use ORM - avoid stored procedures.

Answer 2

There is no clear black and white answer in this release. There are many conflicting arguments on both sides, and I, (imho), until I see a clear consensus. For the opposite view, with rational pro-con arguments, see ORM - Vietnam CS , or another ORM link .

Answer 3

Most ORM tools (which I used. I'm in the .NET world) provide a mechanism for using stored procedures. Since ORM tools (again, the ones I used) select all columns by default, so they are all loaded into object graphs, you usually have to write your SPROC to select all columns that are part of your object graph. This is the only seriousness I encountered while using the ORM package.

There are usually ways to optimize your SPROC calls in ORM tools, so they don’t have to select all the columns, but they are usually more advanced.

I would say that it is safe, but usually you need or need to do this only when you need to optimize something slow using the usual ORM methods.

Answer 4

In .NET, the LINQ data class will generate a strongly typed class for the procedure. You call the procedure as follows:

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

Where GetCustomers () is the stored procedure in the database. But you cannot update the returned client and make changes to the database. ORM can only do this with regular tables.

My experience is the opposite of S.Lot, the stored procedure level keeps my database consistent, clean and fast. I think it depends on the size and complexity of the application, and how well you know SQL.