database – VS ORM Stored Procedures

Question:

I saw in some applications they mainly use stored procedures, that is, the application does not make SELECT queries to the database at all, but receives everything through the HP in the JSON format.

What are the benefits of this approach? Always used ORM ActiveRecord and was happy.

Answer:

  • Easy to reuse

    Stored procedures just need to be defined once at the base level, after which they can be used from anywhere: from a naked SQL client, from clients in other languages. All clients will be guaranteed to use the same request code, knowing only the name.

  • Defined in the "native" language for the base

    The railed ActiveRecord provides its own query-writing capabilities, but many complex or engine-specific queries can be difficult to cram into them. Of course, there are other ORMs that are much more capable, but stored procedures are initially "native" for the database, and the code on whatever ORM is derived from it.


Disadvantages. for my taste, still more:

  • Harder to change

    Because they change at the base level, all clients are forced to use the same code when upgrading, even old ones. Therefore, changing the interface / signature of already deployed procedures is a bad idea. To make changes, a new procedure is needed, even if the most convenient name for it is already taken.

  • Another language

    The product itself is probably not written in procedural SQL. And voila, suddenly the developers need to know something else. And in my personal subjective opinion, all these procedural SQL dialects are syntactically very inconvenient. Ruby is better.

  • Requires porting between different DBMS

    So to write in this style some generalized plugins for Rails that require working with the base, the idea is so-so: you have to implement procedures for each type of RDBMS that you intend to support.

  • Require naming and documentation, even one-off

    Even if a request is used only once in a project, it is necessary to come up with a name for it and prepare internal documentation for it, as it should be for the entire internal API. Well, in an amicable way.

    And if the request had been placed "at the place of use", it would have been possible to restrict ourselves to the documentation for the module that this request uses, if outside this module this request cannot be called (if you do not take into account the possibility of copying, of course – but we are talking about where the code hosted).


And neutrality:

  • Not more efficient in terms of performance

    ActiveRecord uses prepared statements wherever it can, so even huge requests are sent exactly once per connection and then named in a stored procedure-like manner. But whether the stored procedure will use prepared statements, whether query plans will be cached … depends on many things.

Scroll to Top
AllEscort