3 ms·

But you can decouple the database logic from the app logic anyway, without using stored procedures. They don't actually help you do this since you still need co

But you can decouple the database logic from the app logic anyway, without using stored procedures. They don't actually help you do this since you still need code that knows what stored procedures to call. Also, I'm not sure how this makes security easier? It seems like security would be the same or maybe a little harder since you now have to track the stored procedures you're currently using as well. I'm not really sure what you mean by "you can deploy schema changes independently" and "you can change everything and the app should not notice". The stored procedures are basically just an extension of the apps logic right? So you can deploy them at any time, sure, but that isn't different from an app that doesn't use stored procedures, because you could also deploy changes to any part of that app at any time. I do think stored procedures can be more efficient, because you have a lot more control. But it's not like they are clearly superior from an organizational standpoint. If you write an ORM using stored procedures, it's still an ORM.

zkomp 9y ago
As far as I know, in my experience. Stored procedures in postgres are good when you really use the database and care about the data, you need transactions, need to handle races and concurrency etc. Whereas ORMs break down at this point or prevent you even getting to a point when you can use your database as a database. Why pretend your SQL database is about objects? It is not... (it is about data) A stored procedure can act like a view or a query, or use procedural logic. Point is: your app can call it and get a concistent result, no matter what refactoring has been going on. A direct query needs to know too much about the database (orm generated or otherwise) which prevent refactoring and couples app to database harder... You can rename or merge tables, views functions in the database but the interface the app use (stored procedures/DAL) will stay the same and work the same way. As for app logic... I prefer bussiness logic in the database, not the app when the data is important. Application logic stay in your application, data dependent bussiness logic stay with the data.
- scarface74 9y ago
  Every single implementation where I've seen "business logic in the database" has been an unmitigated disaster. On the other hand, having well factored microservices (out of process) or in process modules have worked out really well with modern devops and software engineering principals - easy push button deployments and rollbacks, unit testing, A/B deployments, etc.
  - zkomp 9y ago
    I think bussiness logic in the database has prevented disasters in the projects I have worked on. I honestly dont see how it could have been solved better... It probably depends on the domain/problems. My experience is with transaction heavy financial systems or similar, with web frontends, microservices sprinkled around in different languages... The web app or java worker should be allowed to focus in its problems, the bussiness logic needs to live in one central place, which happens to be in the database accessed through thightly controled interface in the form of stored procedures.
    - scarface74 9y ago
      And what's stopping you from having a tightly controlled interface with a REST Api that is easily deployed, rolled back, unit tested, source controlled and deployed?
      - zkomp 9y ago
        I like data. A database is created to handle it, give you tools to query, modify, scale, secure the data. A rest api... how and why should it be responsible for your data? It solved a different problem. You might not even need a database I guess, and then anything goes. I need and like my database, and have suffered trying to get along with different ORMs. SQL is so good at what it is designed to do if you just let it. (And why just one rest api? How about 100 restapis, some microservices, some web apps, some background workers, many different languages. One database. No ORM)
        scarface74 9y ago
        One database is still an issue. When you have a clear slice with one microservice being in charge of one set of data, it's easier to scale, slice, rewrite, and you can deploy and iterate faster without interdependencies. And you lose all of the benefits of microservices if there is still a tight coupling between unrelated (from a domain perspective) to tables.
        zkomp 9y ago

zkomp 9y ago

As far as I know, in my experience. Stored procedures in postgres are good when you really use the database and care about the data, you need transactions, need to handle races and concurrency etc. Whereas ORMs break down at this point or prevent you even getting to a point when you can use your database as a database. Why pretend your SQL database is about objects? It is not... (it is about data) A stored procedure can act like a view or a query, or use procedural logic. Point is: your app can call it and get a concistent result, no matter what refactoring has been going on. A direct query needs to know too much about the database (orm generated or otherwise) which prevent refactoring and couples app to database harder... You can rename or merge tables, views functions in the database but the interface the app use (stored procedures/DAL) will stay the same and work the same way. As for app logic... I prefer bussiness logic in the database, not the app when the data is important. Application logic stay in your application, data dependent bussiness logic stay with the data.

scarface74 9y ago
Every single implementation where I've seen "business logic in the database" has been an unmitigated disaster. On the other hand, having well factored microservices (out of process) or in process modules have worked out really well with modern devops and software engineering principals - easy push button deployments and rollbacks, unit testing, A/B deployments, etc.
- zkomp 9y ago
  I think bussiness logic in the database has prevented disasters in the projects I have worked on. I honestly dont see how it could have been solved better... It probably depends on the domain/problems. My experience is with transaction heavy financial systems or similar, with web frontends, microservices sprinkled around in different languages... The web app or java worker should be allowed to focus in its problems, the bussiness logic needs to live in one central place, which happens to be in the database accessed through thightly controled interface in the form of stored procedures.
  - scarface74 9y ago
    And what's stopping you from having a tightly controlled interface with a REST Api that is easily deployed, rolled back, unit tested, source controlled and deployed?
    - zkomp 9y ago
      I like data. A database is created to handle it, give you tools to query, modify, scale, secure the data. A rest api... how and why should it be responsible for your data? It solved a different problem. You might not even need a database I guess, and then anything goes. I need and like my database, and have suffered trying to get along with different ORMs. SQL is so good at what it is designed to do if you just let it. (And why just one rest api? How about 100 restapis, some microservices, some web apps, some background workers, many different languages. One database. No ORM)
      - scarface74 9y ago
        One database is still an issue. When you have a clear slice with one microservice being in charge of one set of data, it's easier to scale, slice, rewrite, and you can deploy and iterate faster without interdependencies. And you lose all of the benefits of microservices if there is still a tight coupling between unrelated (from a domain perspective) to tables.
      - zkomp 9y ago