7 ms·

No. You should really wind up with a DAL. Define some stored procedures for accessing and working on the data and use only stored procedures. No need for ORM,

by zkomp 9y ago

No. You should really wind up with a DAL. Define some stored procedures for accessing and working on the data and use only stored procedures. No need for ORM, and no inline sql logic in your application code.

blaisio 9y ago
I mean, stored procedures are fine, but I don't think that actually solves anything? Except maybe for reducing the amount of SQL code you have to send back and forth and (in some databases) allowing for a few more optimizations? If you use stored procedures, all you've done is move part of the model into the database, so you have to update the stored procedures as part of a deployment. You still need to have the SQL code written out somewhere, and you still need to have something in the application code that knows which procedures exist and how to use the data they return in business logic.
- zkomp 9y ago
  you solved isolation decoupled much of the db logic from app-logic and made security easier you can deploy schema changes independently you can change everything and the app should not notice
  - blaisio 9y ago
    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
    Why would you want to deploy schema changes separately? I would be horrified if someone changed my DB back end without running a full (hopefully automated) set of tests.
    - zkomp 9y ago
      The db is separate and the interfaces are defined and the test is for this interface (as part of the schema repository) You dont need an ORM for testing your code... But I think this varies from project to project. How many different applications, in different languages are using your db and do you tolerate downtime?
      - scarface74 9y ago
        Why downtime? A developer commits their code, the CI server builds the code, run non database dependent unit test, it gets deployed to the integration environment, automated integration tests get run - fewer in number somewhat slower - it gets deployed to the QA environment and goes through a round of manual testing (sometimes), QA signs off and the build gets deployed to the UAT environment and waits for the business owners sign off, then we turn off the A side of the load balanced farm and it gets to deployed to the A side of the load balanced production servers, it goes through a round of smoke testing (automated and/or manual) and once everyone is satisfied, we make A live, set the load balancer to use side B and deploy to B. All of the manual sign off steps are integrated with the automated release pipeline. As soon as the required approvals sign off, the next step of the pipeline is done. Rolling back is just redeploying the previous released version. Branching, source control, etc is also a lot easier when all of your business logic is in code and you don't have to sync up the "right" version of your source control with the right version of your stored procedures. Of course this is even easier when you're using a NoSql solution where your schema is also defined by your class models. But that's another discussion..... Of course this doesn't have to just apply to code. With things like Packer and Terraform you can do the same with infrastructure. Automated infrastructure deployment is not my expertise...yet
dizzystar 9y ago
In some flavors of SQL, the query planner can't see in a stored procedure. You can then start combining stored procedures. Great way to build a slow mess quickly.
scarface74 9y ago
Just use stored procedures? Then you lose the ability to do unit testing without a database dependency, it's a lot easier to rollback code than to rollback code and stored procedures as one and you don't get full visibility on what the code is doing just by looking at the source code.
- ZenoArrow 9y ago
  > "Then you lose the ability to do unit testing without a database dependency" Not really, you just mock the database calls in the code you're unit testing.
  - scarface74 9y ago
    If all of your business logic is in the stored procedures, what are you actually testing? And I realize that being able to test queries without database dependencies, only really applies to a few languages that treat queries as a first class citizen in the language like C# and Linq where you can mock out your actual Linq provider - replace the EF context with in memory List<T> - and still test your Linq queries.
    - ZenoArrow 9y ago
      > "If all of your business logic is in the stored procedures, what are you actually testing?" Depends on what you want to test. Can either write unit tests for the stored procedures or unit tests for the code that makes use of those stored procedures.
      - scarface74 9y ago
        And then when you write "unit tests" for stored procedures with a lot of developers you get slow "unit tests" that don't scale across multiple developers because of Comte toon issues.
        ZenoArrow 9y ago

by zkomp 9y ago

blaisio 9y ago

I mean, stored procedures are fine, but I don't think that actually solves anything? Except maybe for reducing the amount of SQL code you have to send back and forth and (in some databases) allowing for a few more optimizations? If you use stored procedures, all you've done is move part of the model into the database, so you have to update the stored procedures as part of a deployment. You still need to have the SQL code written out somewhere, and you still need to have something in the application code that knows which procedures exist and how to use the data they return in business logic.

zkomp 9y ago
you solved isolation decoupled much of the db logic from app-logic and made security easier you can deploy schema changes independently you can change everything and the app should not notice
- blaisio 9y ago
  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
  Why would you want to deploy schema changes separately? I would be horrified if someone changed my DB back end without running a full (hopefully automated) set of tests.
  - zkomp 9y ago
    The db is separate and the interfaces are defined and the test is for this interface (as part of the schema repository) You dont need an ORM for testing your code... But I think this varies from project to project. How many different applications, in different languages are using your db and do you tolerate downtime?
    - scarface74 9y ago
      Why downtime? A developer commits their code, the CI server builds the code, run non database dependent unit test, it gets deployed to the integration environment, automated integration tests get run - fewer in number somewhat slower - it gets deployed to the QA environment and goes through a round of manual testing (sometimes), QA signs off and the build gets deployed to the UAT environment and waits for the business owners sign off, then we turn off the A side of the load balanced farm and it gets to deployed to the A side of the load balanced production servers, it goes through a round of smoke testing (automated and/or manual) and once everyone is satisfied, we make A live, set the load balancer to use side B and deploy to B. All of the manual sign off steps are integrated with the automated release pipeline. As soon as the required approvals sign off, the next step of the pipeline is done. Rolling back is just redeploying the previous released version. Branching, source control, etc is also a lot easier when all of your business logic is in code and you don't have to sync up the "right" version of your source control with the right version of your stored procedures. Of course this is even easier when you're using a NoSql solution where your schema is also defined by your class models. But that's another discussion..... Of course this doesn't have to just apply to code. With things like Packer and Terraform you can do the same with infrastructure. Automated infrastructure deployment is not my expertise...yet

dizzystar 9y ago

In some flavors of SQL, the query planner can't see in a stored procedure. You can then start combining stored procedures. Great way to build a slow mess quickly.

scarface74 9y ago

Just use stored procedures? Then you lose the ability to do unit testing without a database dependency, it's a lot easier to rollback code than to rollback code and stored procedures as one and you don't get full visibility on what the code is doing just by looking at the source code.

ZenoArrow 9y ago
> "Then you lose the ability to do unit testing without a database dependency" Not really, you just mock the database calls in the code you're unit testing.
- scarface74 9y ago
  If all of your business logic is in the stored procedures, what are you actually testing? And I realize that being able to test queries without database dependencies, only really applies to a few languages that treat queries as a first class citizen in the language like C# and Linq where you can mock out your actual Linq provider - replace the EF context with in memory List<T> - and still test your Linq queries.
  - ZenoArrow 9y ago
    > "If all of your business logic is in the stored procedures, what are you actually testing?" Depends on what you want to test. Can either write unit tests for the stored procedures or unit tests for the code that makes use of those stored procedures.
    - scarface74 9y ago
      And then when you write "unit tests" for stored procedures with a lot of developers you get slow "unit tests" that don't scale across multiple developers because of Comte toon issues.
      - ZenoArrow 9y ago