Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

I am using PetaPoco micro-ORM. It is indeed very easy and secure to work with databases using ORM tools, but the only thing I hate is extra code. I used to put most of the code in the database itself and use all the RDBMS features like Stored Procedures, Triggers etc., which it is built to handle better.

I want to know when not to use ORM over Stored Procedures/Triggers and vice-versa.

share|improve this question
5  
Personally my problem with triggers (in particular, does not apply to stored procedures) is that they try to "guess" what "business action" happened from how the data on the DB is manipulated. If you modify the PRICE column in a "ARTICLES" table, then you don't really know why that is. Is the user just correcting a mistyped value? Is that a mark-down? Is it a special offer that only lasts for a day? Triggers have to guess all that. –  Joachim Sauer Mar 12 '12 at 10:43
add comment

5 Answers

up vote 10 down vote accepted

ORMs are not mutually exclusive with Stored Procedures. Most ORMs can utilize stored procedures. Most ORMs generate Stored Procedures if you so choose. So it the issue is not either or.

ORMs may generate unacceptable SQL (in terms of performance) and you may sometimes want to override that SQL with hand-crafted SQL. One of the ways to accomplish this is by using SPs.

In DotNet, Don't use stored procedures if:

  • If you are not familiar with stored procedures (not your case, but included for completeness).

  • If you don't want to introduce a layer of complexity and versifying to your project.

  • You are creating an application that should work with different databases or that would have to be replicated across several database servers (this last restriction may apply for some databases only).

Note that triggers are not to be compared wiht ORMs. Triggers do functions that are better not be in your application code (such as logging or synchronizing data across databases).

Some people prefer the use of Stored Procedures over SQL in code for different reasons such as security (for example to prevent SQL injection) and for their claimed speed. However, this is somewhat debatable and needs detailed discussion.

If your ORM can't generate Stored Procedures, and you have to write a large system, then you need to weight of the extra hand coding based on your case.

share|improve this answer
2  
I believe that the security argument is not related to SQL injection, but rather to the permissions (i.e. it's mostly straightforward to manage permissions for a specific stored procedure for the users which have access to the database, but managing those permissions on tables, columns and rows is either harder or impossible). Still, the security argument remains debatable. –  MainMa Mar 12 '12 at 11:15
    
@MainMa, thanks for your comment. My understanding is that using SPs, one could reduce the risk of SQL injection by the use of parameterized stored procedure with embedded parameters as this article suggests: palpapers.plynt.com/issues/2006Jun/injection-stored-procedures –  Emmad Kareem Mar 12 '12 at 12:35
2  
Stored procedures have virtually no impact on the vulnerability to sql injection attacks. Old myths die hard. –  Rig Apr 4 '12 at 20:55
    
@Rig 1, thank you for your comment, I wish to learn more about what you think of this. My understanding comes from this text (at least):"You can thwart SQL Server injection attacks by using stored procedures and parameterized commands, avoiding dynamic SQL, and restricting permissions on all users." that appears in msdn.microsoft.com/en-us/library/bb669057.aspx –  Emmad Kareem Apr 4 '12 at 22:49
    
@EmmadKareem Parameterized sql is a big step in making it safe. I think this guy makes a reasonable case palpapers.plynt.com/issues/2006Jun/injection-stored-procedures . A search on it will "Stored Procedure SQL Injection" will turn up a lot of hits. It's always good to sanitize your inputs and a lot of platforms provide a built in way to do it reasonably well. –  Rig Apr 4 '12 at 23:08
show 3 more comments

ORMs often assume that the database exists to serve the ORM. But usually the database exists to serve the company, which might have hundreds and hundreds of apps written in multiple languages hitting it.

But it's only a case of "ORM vs. Stored Procedures" if you're using an ORM that can't call a stored procedure. Otherwise, it's a case of deciding where to code the business logic.

Wherever you code the business logic, its job is to make sure the database changes from one consistent state to another consistent state regardless of which application makes the change. So you really only have two practical choices--code it once in the database, or code it once in an "impenetrable" data access layer.

Beware of the dbms command-line interface if you use an "impenetrable" DAL.

share|improve this answer
1  
I've run into more than one case where old SP's and triggers had to be used with new apps because of existing legacy apps. The advantage is that this business logic only has to be maintained in one place. –  jfrankcarr Mar 12 '12 at 13:52
add comment

Trigger should be used as invariant of record or consist of vital business rules, IMHO.

The problems of orms:

  1. You should set permissions per tables, not per "Action"( i mean SP)
  2. To change the logic of your solution you need to change the code inside of your app and then redistribute it over network for clients
share|improve this answer
add comment

Simple query --> ORM

Complex query --> Stored Procedure

share|improve this answer
2  
Where Does a complex segregate from a simple Query, –  Independent Mar 5 '13 at 17:48
add comment

Disagree. ORM query only simpler if you know ORM better than you know SQL. ORM results in far more code, far more-difficult to maintain IMO. The only people who benefit from ORM are the shareholders of the company selling the ORM (e.g. Microsoft).

share|improve this answer
1  
quite a pity that opinion expressed in this answer isn't backed up neither by references nor by experience. As a result, it will be useless for a reader who may stumble upon an "inverse" claim like stored procedures benefit only DB vendors. Makes one realize why guidance in Real Questions Have Answers states: "real questions have answers, not items or ideas or opinions..." –  gnat Mar 5 '13 at 21:26
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.