17 ms·

> If you're not using an ORM, then you ultimately end up writing one. I disagree with this. A lot of things people use ORMs for are rather easily solved with s

by PeCaN 10y ago

> If you're not using an ORM, then you ultimately end up writing one. I disagree with this. A lot of things people use ORMs for are rather easily solved with stored procedures, especially in Postgres where you can write stored procedures in Perl, Ruby, etc. Validations, “fat models”, etc are all managed with SQL easily (and this means you get that functionality from _anywhere you access the database_, not just from your framework with an ORM). For convenient access you can roll 30 lines of Perl to wrap DBI or whatever (I use Perl for most web backends these days) and call your stored procedures in normal syntax with a little metaprogramming. Maybe this sort of scheme (heavy usage of stored procedures and offload almost everything to the DB) doesn't work for everyone, but I like databases and it works for me.

titanomachy 10y ago
How do you manage version-control on stored procedures? Can they be checked in with the rest of the application logic?
- sitharus 10y ago
  Yes, there are tools to do this. It varies by database server and may need custom integration with your deployment system but they definitely exist. The alternative is the old StoredProc_v1, StoredProc_v2 etc in the database and have the application check what's available. This allows you to roll the parts independently. The big advantage with stored procedures is they can benefit from the DBMS caching and applying additional optimisations (depending on the DBMS of course).
- sopooneo 10y ago
  I myself am wary of stored procedures except in very specific and uncommon circumstances. That said, you could absolutely version control your stored procedures by creating them from within database migration files that are version controlled by default.
  - platz 10y ago
    What is the source of the wariness?
    - Udik 10y ago
      Don't know about the parent, but I always felt that stored procedures end up by incorporating a good share of the business logic, extracting it from the main code of the application. This creates a messy situation in which you have your business logic split up between two completely separate and different systems, one of which (the stored procedures) is much harder to read, write, maintain and test. That said, I also deeply hate ORMs, I much prefer to use thinner layer to interface with a database (query builders and row mappers).
      - platz 10y ago
        I also do think putting too much logic in stored procs is a danger, and was/is a scourge for some shops. But was curious if that is the primary objection.
        deleted 10y ago
        [deleted]
        snuxoll 10y ago
        I try to avoid anything but mapping or querying data in sprocs, but sometimes they are a better place to put logic that is needed to ensure consistency. Your database will outlive your application, or you will eventually need to integrate data from a third party system - as a result your database engine is always the last line of defense to protect your data, and if that means using a sproc than so be it. I'll admit, I've written some business logic into stored functions in PostgreSQL, but it's mostly a background job to update cached data because it is much faster to do it in the database than pulling a bunch of records down to a service and sending them back up.
      - PeCaN 10y ago
        It's fat models vs thin models, just with fat database vs thin database. You have to be careful.
- programmarchy 10y ago
  That's the way I've done things in the past -- save stored procs in a file in a project repo, along with deploy and rollback scripts.
- IndianAstronaut 10y ago
  Yes. Data warehousing projects I have worked on have lots of stored procedures in with the code. One way that works is make sure you make a simultaneous tag of your code and stored procedure that works with it. You really want development to run hand in hand with stored procedure development, not as two parallel processes.
- gfody 10y ago
  In Microsoft land we have SSDT and stored procedures live in source control right next to the schema and any CLR functions. The whole thing is built, compile-time checked, and deployed. It's all pretty wonderful.
- bigpeopleareold 10y ago
  I have found Sqitch (http://sqitch.org http://sqitch.org) to be a useful solution.
craigvn 10y ago
But once you have run the stored proc how do you display it to the user? How do you get the data from the UI to the stored proc to execute? You write some code or it happens by magic? If you write some code, then you have just written an ORM.
- deleted 10y ago
  [deleted]
- dhd415 10y ago
  It's overly reductive to consider any code that interacts with a database an ORM. Broadly speaking, the big claims ORMs have made are 1) the ability to automatically map between an object model and a relational data model and 2) portability between database vendors. #1 falls apart when the object model becomes complex enough to suffer from object-relational impedance mismatch. #2 may work for smaller or simpler applications, but you're generally going to have to take advantage of vendor-specific database features to achieve optimal performance with applications that have anything more than trivial scale or performance requirements. Going with stored procedures generally means giving up on both #1 and #2 in exchange for a more flexible design (relational model can be very different from your object model) and higher performance (at the cost of having to hand-write all your own SQL, of course).
- PeCaN 10y ago
  > If you write some code, then you have just written an ORM. What? No, I've written code to query a database. Not an Object-Relational Mapper. my $q = $db->prepare("get_my_stuff_by_name(?);"); $q->execute($name); or with fancy metaprogramming: use DBMagicStuff 'postgres://…'; get_my_stuff_by_name($name); That's not really an ORM.
  - intrasight 10y ago
    You are both partially right. An ORM deals with the black magic of connections, parameters, transactions, etc. If you don't use an ORM, then you still have to deal with those semantics. But I do agree that you aren't writing an ORM.
manigandham 10y ago
> A lot of things people use ORMs for are rather easily solved with stored procedures More like 1 thing. ORMs are meant to make interfacing through the object/relational impedance mismatch easier and through the regular code in your application. Stored procedures do not come anywhere close to this and are usually the same as just calling any other SQL query as you would when not using an ORM. If you think any majority of what an ORM is used for can be replaced by stored procedures, then you're not really using an ORM for much at all.
- kerkeslager 10y ago
  I agree with this, but I'd also add that if your ORM is doing a lot beyond the capabilities of your database, it's an indicator of a hacky design in your application. If there's a large object/relational impedance mismatch, then either the objects or the relational DB are a poor fit for the problem you're trying to solve, and an ORM can't really fix that. If your ORM is just providing a mapping between select/map, where/filter, join/zip, etc., you have a fairly list-of-records-ish functional application and your objects are only nominally objects. The ORM is only providing an interface that lines your types up (and the benefits of that are not to be underestimated--it allows, for example, running your app on different database engines). But if your ORM is doing more than that, it's usually because your objects have a lot of complicated graph vertices that are poorly represented by tables. ORMs can reduce the difficulty of this object/relational impedance mismatch, but ultimately they can't provide a general solution for it, because the data structure they're sitting on top of doesn't have the capability. ORMs can make your code simpler, but they can't make SQL performant on, for example, directed graph or deep tree structures. Ultimately, they only mitigate the object/relational impedance mismatch, they don't solve it. Again, as I parenthesized earlier, the benefits of ORMs still aren't to be underestimated. But I think a lot of those benefits can be realized with a simple wrapper around SQL (i.e. LINQ). Beyond that, all ORMs provide is a little fudge factor which lets you get away with some things that SQL doesn't support well, but ultimately ORMs aren't a general solution to those kinds of problems.
  - manigandham 10y ago
    > If there's a large object/relational impedance mismatch, then either the objects or the relational DB are a poor fit for the problem you're trying to solve. Why? You're just making that assumption but the fact is that relational databases and the normalized storage of data is completely different from the way OO languages deal with rich nested objects. And there's nothing wrong with that mismatch because there will always be a mismatch. It's just 2 different paradigms of handling data. All an ORM is doing is giving you a tool to make that translation easier, if you want it. If you really can't have the mismatch then there are document-stores available but in most cases, the O/R mapping is just not a big deal. > But I think a lot of those benefits can be realized with a simple wrapper around SQL (i.e. LINQ). That's basically still an ORM. Again why the assumption that just because you have an ORM that anything and everything must be piped through it? The modern ones let you use ORM methods in your code and chain them with custom raw SQL too. ORM usage is on a spectrum, it's not binary and there's definitely no "right" way to use them.
BurningFrog 10y ago
One problem with the stored procedure approach is that it scales terribly. If your logic is in app servers and your state in a DB, you can add more app servers, and it will be a long time until your DB get overwhelmed. When your DB is doing both, the choking point is much earlier.
- PeCaN 10y ago
  That's a valid point. Seeing as I don't build anything that has to scale well I can't really comment on this, but I don't think it scales too badly. With stored procedures it's more or less just your model in the database, not your whole application logic. Using a database to replace your controllers wouldn't scale so well, but replacing fat models would still scale fine.
  - btilly 10y ago
    It significantly depends on the application, but it shouldn't be more than a factor of 5-10 or so... A factor of 5-10 can matter. A lot.
- peschkaj 10y ago
  I've yet to see empirical proof that stored procedures don't scale well. If you write an application in your SQL dialect of choice, that likely won't scale well, but putting data access behind an API should scale well no matter if that API is in Rails, Spring, or a stored procedure.
  - btilly 10y ago
    I don't know what you consider "empirical proof", but it seems obvious to me. And should be obvious to anyone competent who has ever had to scale stuff. A system fails to scale when it has a bottleneck, and that bottleneck gets overwhelmed. You make it scale better by scaling the bottleneck, which can be done by moving work out of the bottleneck, or by parallelizing it in some way. The natural bottleneck for any system that has to synchronize data is the locking around synchronizing that data. That is because things that do not need synchronization can easily be parallelized. You therefore scale that bottleneck until the more fundamental one emerges. In a standard database driven website, that bottleneck is always in the database. And therefore your scaling limit is the capacity of your database. As follows normal scaling advice, you need to move work out of the database, or remove the database as a scaling limit. Moving work from stored procedures to the application is an example of moving work out of the database. So is having queries run against read-only replicas instead of the read/write master. Sharding your database and moving to a distributed NoSQL architecture are examples of removing the bottleneck. Of the two approaches, the much simpler and safer one is to move work out of the database. Going NoSQL is cool, but unless you really know what you're doing, it is both unlikely to buy you what you wanted, and leaves you open to obscure data consistency problems.
snaky 10y ago
> 30 lines of Perl to wrap DBI In Java it's even easier with JPA @NamedStoredProcedureQuery annotations http://www.thoughts-on-java.org/call-stored-procedures-jpa/ http://www.thoughts-on-java.org/call-stored-procedures-jpa/

by PeCaN 10y ago

titanomachy 10y ago

How do you manage version-control on stored procedures? Can they be checked in with the rest of the application logic?

sitharus 10y ago
Yes, there are tools to do this. It varies by database server and may need custom integration with your deployment system but they definitely exist. The alternative is the old StoredProc_v1, StoredProc_v2 etc in the database and have the application check what's available. This allows you to roll the parts independently. The big advantage with stored procedures is they can benefit from the DBMS caching and applying additional optimisations (depending on the DBMS of course).
sopooneo 10y ago
I myself am wary of stored procedures except in very specific and uncommon circumstances. That said, you could absolutely version control your stored procedures by creating them from within database migration files that are version controlled by default.
- platz 10y ago
  What is the source of the wariness?
  - Udik 10y ago
    Don't know about the parent, but I always felt that stored procedures end up by incorporating a good share of the business logic, extracting it from the main code of the application. This creates a messy situation in which you have your business logic split up between two completely separate and different systems, one of which (the stored procedures) is much harder to read, write, maintain and test. That said, I also deeply hate ORMs, I much prefer to use thinner layer to interface with a database (query builders and row mappers).
    - platz 10y ago
      I also do think putting too much logic in stored procs is a danger, and was/is a scourge for some shops. But was curious if that is the primary objection.
      - deleted 10y ago
        [deleted]
      - snuxoll 10y ago
        I try to avoid anything but mapping or querying data in sprocs, but sometimes they are a better place to put logic that is needed to ensure consistency. Your database will outlive your application, or you will eventually need to integrate data from a third party system - as a result your database engine is always the last line of defense to protect your data, and if that means using a sproc than so be it. I'll admit, I've written some business logic into stored functions in PostgreSQL, but it's mostly a background job to update cached data because it is much faster to do it in the database than pulling a bunch of records down to a service and sending them back up.
    - PeCaN 10y ago
      It's fat models vs thin models, just with fat database vs thin database. You have to be careful.
programmarchy 10y ago
That's the way I've done things in the past -- save stored procs in a file in a project repo, along with deploy and rollback scripts.
IndianAstronaut 10y ago
Yes. Data warehousing projects I have worked on have lots of stored procedures in with the code. One way that works is make sure you make a simultaneous tag of your code and stored procedure that works with it. You really want development to run hand in hand with stored procedure development, not as two parallel processes.
gfody 10y ago
In Microsoft land we have SSDT and stored procedures live in source control right next to the schema and any CLR functions. The whole thing is built, compile-time checked, and deployed. It's all pretty wonderful.
bigpeopleareold 10y ago
I have found Sqitch (http://sqitch.org http://sqitch.org) to be a useful solution.

craigvn 10y ago

But once you have run the stored proc how do you display it to the user? How do you get the data from the UI to the stored proc to execute? You write some code or it happens by magic? If you write some code, then you have just written an ORM.

deleted 10y ago
[deleted]
dhd415 10y ago
It's overly reductive to consider any code that interacts with a database an ORM. Broadly speaking, the big claims ORMs have made are 1) the ability to automatically map between an object model and a relational data model and 2) portability between database vendors. #1 falls apart when the object model becomes complex enough to suffer from object-relational impedance mismatch. #2 may work for smaller or simpler applications, but you're generally going to have to take advantage of vendor-specific database features to achieve optimal performance with applications that have anything more than trivial scale or performance requirements. Going with stored procedures generally means giving up on both #1 and #2 in exchange for a more flexible design (relational model can be very different from your object model) and higher performance (at the cost of having to hand-write all your own SQL, of course).
PeCaN 10y ago
> If you write some code, then you have just written an ORM. What? No, I've written code to query a database. Not an Object-Relational Mapper. my $q = $db->prepare("get_my_stuff_by_name(?);"); $q->execute($name); or with fancy metaprogramming: use DBMagicStuff 'postgres://…'; get_my_stuff_by_name($name); That's not really an ORM.
- intrasight 10y ago
  You are both partially right. An ORM deals with the black magic of connections, parameters, transactions, etc. If you don't use an ORM, then you still have to deal with those semantics. But I do agree that you aren't writing an ORM.

manigandham 10y ago

> A lot of things people use ORMs for are rather easily solved with stored procedures More like 1 thing. ORMs are meant to make interfacing through the object/relational impedance mismatch easier and through the regular code in your application. Stored procedures do not come anywhere close to this and are usually the same as just calling any other SQL query as you would when not using an ORM. If you think any majority of what an ORM is used for can be replaced by stored procedures, then you're not really using an ORM for much at all.

kerkeslager 10y ago
I agree with this, but I'd also add that if your ORM is doing a lot beyond the capabilities of your database, it's an indicator of a hacky design in your application. If there's a large object/relational impedance mismatch, then either the objects or the relational DB are a poor fit for the problem you're trying to solve, and an ORM can't really fix that. If your ORM is just providing a mapping between select/map, where/filter, join/zip, etc., you have a fairly list-of-records-ish functional application and your objects are only nominally objects. The ORM is only providing an interface that lines your types up (and the benefits of that are not to be underestimated--it allows, for example, running your app on different database engines). But if your ORM is doing more than that, it's usually because your objects have a lot of complicated graph vertices that are poorly represented by tables. ORMs can reduce the difficulty of this object/relational impedance mismatch, but ultimately they can't provide a general solution for it, because the data structure they're sitting on top of doesn't have the capability. ORMs can make your code simpler, but they can't make SQL performant on, for example, directed graph or deep tree structures. Ultimately, they only mitigate the object/relational impedance mismatch, they don't solve it. Again, as I parenthesized earlier, the benefits of ORMs still aren't to be underestimated. But I think a lot of those benefits can be realized with a simple wrapper around SQL (i.e. LINQ). Beyond that, all ORMs provide is a little fudge factor which lets you get away with some things that SQL doesn't support well, but ultimately ORMs aren't a general solution to those kinds of problems.
- manigandham 10y ago
  > If there's a large object/relational impedance mismatch, then either the objects or the relational DB are a poor fit for the problem you're trying to solve. Why? You're just making that assumption but the fact is that relational databases and the normalized storage of data is completely different from the way OO languages deal with rich nested objects. And there's nothing wrong with that mismatch because there will always be a mismatch. It's just 2 different paradigms of handling data. All an ORM is doing is giving you a tool to make that translation easier, if you want it. If you really can't have the mismatch then there are document-stores available but in most cases, the O/R mapping is just not a big deal. > But I think a lot of those benefits can be realized with a simple wrapper around SQL (i.e. LINQ). That's basically still an ORM. Again why the assumption that just because you have an ORM that anything and everything must be piped through it? The modern ones let you use ORM methods in your code and chain them with custom raw SQL too. ORM usage is on a spectrum, it's not binary and there's definitely no "right" way to use them.

BurningFrog 10y ago

One problem with the stored procedure approach is that it scales terribly. If your logic is in app servers and your state in a DB, you can add more app servers, and it will be a long time until your DB get overwhelmed. When your DB is doing both, the choking point is much earlier.

PeCaN 10y ago
That's a valid point. Seeing as I don't build anything that has to scale well I can't really comment on this, but I don't think it scales too badly. With stored procedures it's more or less just your model in the database, not your whole application logic. Using a database to replace your controllers wouldn't scale so well, but replacing fat models would still scale fine.
- btilly 10y ago
  It significantly depends on the application, but it shouldn't be more than a factor of 5-10 or so... A factor of 5-10 can matter. A lot.
peschkaj 10y ago
I've yet to see empirical proof that stored procedures don't scale well. If you write an application in your SQL dialect of choice, that likely won't scale well, but putting data access behind an API should scale well no matter if that API is in Rails, Spring, or a stored procedure.
- btilly 10y ago
  I don't know what you consider "empirical proof", but it seems obvious to me. And should be obvious to anyone competent who has ever had to scale stuff. A system fails to scale when it has a bottleneck, and that bottleneck gets overwhelmed. You make it scale better by scaling the bottleneck, which can be done by moving work out of the bottleneck, or by parallelizing it in some way. The natural bottleneck for any system that has to synchronize data is the locking around synchronizing that data. That is because things that do not need synchronization can easily be parallelized. You therefore scale that bottleneck until the more fundamental one emerges. In a standard database driven website, that bottleneck is always in the database. And therefore your scaling limit is the capacity of your database. As follows normal scaling advice, you need to move work out of the database, or remove the database as a scaling limit. Moving work from stored procedures to the application is an example of moving work out of the database. So is having queries run against read-only replicas instead of the read/write master. Sharding your database and moving to a distributed NoSQL architecture are examples of removing the bottleneck. Of the two approaches, the much simpler and safer one is to move work out of the database. Going NoSQL is cool, but unless you really know what you're doing, it is both unlikely to buy you what you wanted, and leaves you open to obscure data consistency problems.

snaky 10y ago

> 30 lines of Perl to wrap DBI In Java it's even easier with JPA @NamedStoredProcedureQuery annotations http://www.thoughts-on-java.org/call-stored-procedures-jpa/ http://www.thoughts-on-java.org/call-stored-procedures-jpa/