16

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.

RPK
  • 4,378
  • 11
  • 41
  • 65
  • 6
    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

5 Answers5

18

ORMs(Object-relational mapping) 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(stored procedures).

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 with 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.

Hemant
  • 3
  • 2
NoChance
  • 12,412
  • 1
  • 22
  • 39
  • 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. – Arseni Mourzenko 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: http://palpapers.plynt.com/issues/2006Jun/injection-stored-procedures/ – NoChance 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 04 '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 http://msdn.microsoft.com/en-us/library/bb669057.aspx – NoChance Apr 04 '12 at 22:49
  • @EmmadKareem Parameterized sql is a big step in making it safe. I think this guy makes a reasonable case http://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 04 '12 at 23:08
  • @Rig 1, thanks for sharing this information. – NoChance Apr 05 '12 at 00:54
  • @EmmadKareem -- one of the biggest sql injections I've ever seen was a stored proc taking parameters and then generating dynamic SQL internally. This was a *very* common practice back in the early part of the century when the mantra was "always do data access via sprocs". – Wyatt Barnett Mar 05 '13 at 17:24
  • @WyattBarnett, thanks for your comment. Personally, dynamic SQL generation still scares me in large applications for many reasons. – NoChance Mar 06 '13 at 02:56
14

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.

  • 4
    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
  • 1
    I'm definitely not denying that "one database to serve them all" has been used, but it is mostly a thing of the past specifically because maintenance becomes pure hell, especially when multiple applications need to maintain their own versioning of stored procs. Having the database exist to serve the application that owns it is a much more modern approach as it enforces looser coupling between applications and services. – Flater Oct 17 '19 at 10:46
-2

Simple query --> ORM

Complex query --> Stored Procedure

Darknight
  • 12,209
  • 1
  • 38
  • 58
-3

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
-6

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).

phantom
  • 1
  • 1
  • 2
    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](http://blog.stackoverflow.com/2011/01/real-questions-have-answers/ "Stack Exchange blog article by Jeff Atwood") states: "real questions have *answers*, __not *items* or *ideas* or *opinions*...__" – gnat Mar 05 '13 at 21:26