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
  • 43
  • 66

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
NoChance
  • 12,532
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.

Mike Sherrill 'Cat Recall'
  • 1,712
  • 10
  • 14
-2

Simple query --> ORM

Complex query --> Stored Procedure

Darknight
  • 12,159
-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
Yuriy Vikulov
  • 95
-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