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.

1
  • 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. Mar 12, 2012 at 10:43

5 Answers 5

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.

8
  • 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. Mar 12, 2012 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
    – NoChance
    Mar 12, 2012 at 12:35
  • 2
    Stored procedures have virtually no impact on the vulnerability to sql injection attacks. Old myths die hard.
    – Rig
    Apr 4, 2012 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
    – NoChance
    Apr 4, 2012 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, 2012 at 23:08
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.

2
  • 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, 2012 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, 2019 at 10:46
-2

Simple query --> ORM

Complex query --> Stored Procedure

1
  • 3
    Where Does a complex segregate from a simple Query, Mar 5, 2013 at 17:48
-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).

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 states: "real questions have answers, not items or ideas or opinions..."
    – gnat
    Mar 5, 2013 at 21:26

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