4 ms·

I personally have found that how to approach data access has been a real bone of contention on projects and quite damaging. It seems half the team want to use a

I personally have found that how to approach data access has been a real bone of contention on projects and quite damaging. It seems half the team want to use an ORM and have a long list of reasons not to use SQL/Stored Procs (slower to develop, out dated, not testable, business logic in the wrong place). The other half of the team wants to avoid ORMs and would rather use SQL/Stored Procs (performance, ORMs start off okay but soon aren't up to the job, more control and power with direct SQL). In fact, you can see these two standpoints in this very discussion. I find both have valid points and there isn't really a compromise. Whichever approach is taken you end up with half the team feeling not listened to and disenfranchised. I have found few things to be more divisive than the ORM vs No ORM debate and I am not sure what the answer is.

davidgl 6y ago
Agreed. As ever, it depends a lot on the type of work and how big the system is. We use a lot of SQL (not sprocs), but are moving back to LINQ for reasons of composition and maintainability. I love writing SQL, but in our system which is north of 300 tables, on a project over 10 years old now, the lack of compatibility and general lack of static dependencies in SQL is hurting us more and more
sjwright 6y ago
The problem comes when anyone thinks the ORM vs No ORM debate has a single answer. For me it's a simple test: • Your data makes sense as objects; • Your data is small enough to fit in memory; • Most of your operations are CRUD. If you can answer all three with yes, then use an ORM or some other kind of SQL abstraction layer. Otherwise don't.
- TobiasA 6y ago
  Per your last point, why don't you think ORMs work well with complex domains?
  - murgindrag 6y ago
    ORMs completely fail for many complex domains. If your data represents an arbitrary tree hierarchy, a graph, or otherwise, relational databases have nice data architectures, but those completely misalign with how ORMs handle data. You don't even need to go that complex. Even moderately complex JOINs start to look better in SQL than in ORMs. If you have an employees database, an inventory database, and a clients database, an ORM is perfect. If you have a fixed hierarchy, ORM works great too. It maps objects to the database. That's 90% of web apps. If you're building e.g. an online CAD system with a complex data model for storing hierarchical layers of objects with complex relations, and where you need to perform complex operations on that data for e.g. simulations and optimizations, SQL will actually handle that just fine. You'll just be doing SQL beyond what fits into an ORM. At that point, if you use an ORM, you'll be doing complex contortions. Think back to your data structures class. Then to the grad-level data structures class. Most of those structures, SQL will handle fine, but ORMs won't. SQL has also turned out to be surprisingly resilient to different programming paradigms. SQL came out just around a half-century ago, before OO was common, and did fine with OO, functional, structured, and a whole range of other paradigms which have moved into and out of vogue over that time. ORMs, as the name implies, are specific to OO. A few posts up, the poster is correct that there are design patterns around ORMs which work well, and design patterns around SQL which work well. You want to pick one and stick to it. The mess comes in when you mix layers of abstractions. One or two SQL procedures might not kill you, but when you have big chunks of code using ORM and big chunks NOT using ORM, you'll crash-and-burn. Footnote: "Complex domains" is also about the database layer, and the type of complexity. Right now, I'm working on a very algorithmically complex system, but that complexity isn't in the data layer. Most of the data engineering is about moving GB of data around in realtime. All the database needs to handle are simple things like auth/auth. That's an ideal use-case for an ORM. I've also built systems with ORMs where the database layer was complex, but complex in ways which aligned well with ORMs. So this shouldn't be read as "dumb programmers who can't handle functional use ORMS." Footnote to posterity: Should someone stumble upon this in a web search: If you grew up in OO and Java, and never ventured beyond, none of the above will make sense to you. You only learned the programming paradigm ORMs were designed for.
  - sjwright 6y ago
    The intent of my third point is that you should expect a decent proportion of the operations to be handled well by the ORM such that you can reap efficiency benefits from the abstraction. If you're spending a significant amount of time jumping out of the ORM's sandpit and into custom queries, the benefit of the ORM may be limited. And to the extent that it dictates/influences your table structures, it might be a hindrance.
- jbjohns 6y ago
  But if your answer to all those questions is yes, why are you using an RDMS to begin with? Wouldn't some other kind of data store map better to those use cases, and then you wouldn't need a bit abstraction to make it look like something it isn't.
  - sjwright 6y ago
    I completely agree, because ORMs are literally jamming a metaphorical round peg in the square hole. But enterprise gonna enterprise. Shrug.
sjwright 6y ago
Stored procedures are a really bad hack—in any practical sense they function as a mediocre API server which comes bundled "for free" with your SQL database server. If you need an API layer at all, it's much better to write this in a real programming language. Just about anything you can do with stored procedures, you can do within an SQL query block in your favourite language. And when all your queries are fully parameterised, your database server can even use the same execution plan cache as "real" stored procedures.
UK-Al05 6y ago
Don't put business logic in stored procs. They're great for storing complicated queries, but the actual buiness logic? No. Have some stored procs for updating and querying data, then a lightweight abstraction around them in the application.
- taffer 6y ago
  What does "business logic" mean to you? According to the Wikipedia definition [1], I find it very difficult to imagine SQL that does not contain business logic. [1] "business logic or domain logic is the part of the program that encodes the real-world business rules that determine how data can be created, displayed, stored, and changed.", https://en.wikipedia.org/wiki/Business_logic https://en.wikipedia.org/wiki/Business_logic
  - UK-Al05 6y ago
    Business Logic is calculating tax, working out price, adding something to a bag, populating an order object with infomation according to some rules. Business logic isn't pulling data from database, how to map an object to a table, or the specific SQL query to grab some data. Those are technical details. SQL should just be pulling, saving and querying from the store without much maniplation. Not changing the data. It's the rules that a business stakeholder might be interested in creating. When you start putting the tax calculation rules into the stored proc, thats where the problems begin. If you create a CTE to pull the information required to perform the calulation then return that info without doing the calculation thats fine.
    - monadic2 6y ago
      How about a state machine encoding cart state? There are many advantages to encoding state changes in sql, but the database logic isn't strictly tied to the specifics of the cart logic. However trying to decouple these domains fully will create many more problems. There's plenty of fuzzy problems like this that interleave the technical and business. I fail to see how ANY part of a project would fail to be of interest to a stakeholder. They don't much care whether a failure emanated from code you have labeled of interest to them or not. You certainly can't make much money without supporting the business logic with—apparently—non-business storage. Even if you can patch up the leaks in this semantic distinction between these two domains the use of such a distinction is not clear in any general sense. These abstractions are to help the coder reason about complex systems and they don't always work. You then need to invent better abstractions.
BenoitP 6y ago
I have that that kind of divisive discussion. But we did compromise. What we ended up doing was using an ORM for CRUD operations, which were mostly user-facing ('row' operations); and SQL for reports / data monitoring ('column', bulky operations). And doing our most to avoid procstocs (limit them to executing REFRESH MATERIALIZED VIEW and COPY operations to the best of our ability), and trigger processing. We do manage the SQL code (view definitions, mviews, and the remaining procstocs) right next to the application code with Flyway [1]. We also have SQL-ORM integration tests, spinning a database container with testcontainers [2]. We've had some issues with business rule duplication (updating the ORM and forgetting to do the same in SQL), but so far I'd say it's successful. This being said, I'm on the SQL boat; and I remain convinced that the CRUD could be done comfortably with an SQL query builder such as jOOQ [3]; and that it would help solving the business rule duplication issue. But hey it's working right now and everybody is happy about it, so why change it? [1] https://flywaydb.org/ https://flywaydb.org/ [2] https://www.testcontainers.org/ https://www.testcontainers.org/ [3] https://www.jooq.org/ https://www.jooq.org/

davidgl 6y ago

Agreed. As ever, it depends a lot on the type of work and how big the system is. We use a lot of SQL (not sprocs), but are moving back to LINQ for reasons of composition and maintainability. I love writing SQL, but in our system which is north of 300 tables, on a project over 10 years old now, the lack of compatibility and general lack of static dependencies in SQL is hurting us more and more

sjwright 6y ago

The problem comes when anyone thinks the ORM vs No ORM debate has a single answer. For me it's a simple test: • Your data makes sense as objects; • Your data is small enough to fit in memory; • Most of your operations are CRUD. If you can answer all three with yes, then use an ORM or some other kind of SQL abstraction layer. Otherwise don't.

TobiasA 6y ago
Per your last point, why don't you think ORMs work well with complex domains?
- murgindrag 6y ago
  ORMs completely fail for many complex domains. If your data represents an arbitrary tree hierarchy, a graph, or otherwise, relational databases have nice data architectures, but those completely misalign with how ORMs handle data. You don't even need to go that complex. Even moderately complex JOINs start to look better in SQL than in ORMs. If you have an employees database, an inventory database, and a clients database, an ORM is perfect. If you have a fixed hierarchy, ORM works great too. It maps objects to the database. That's 90% of web apps. If you're building e.g. an online CAD system with a complex data model for storing hierarchical layers of objects with complex relations, and where you need to perform complex operations on that data for e.g. simulations and optimizations, SQL will actually handle that just fine. You'll just be doing SQL beyond what fits into an ORM. At that point, if you use an ORM, you'll be doing complex contortions. Think back to your data structures class. Then to the grad-level data structures class. Most of those structures, SQL will handle fine, but ORMs won't. SQL has also turned out to be surprisingly resilient to different programming paradigms. SQL came out just around a half-century ago, before OO was common, and did fine with OO, functional, structured, and a whole range of other paradigms which have moved into and out of vogue over that time. ORMs, as the name implies, are specific to OO. A few posts up, the poster is correct that there are design patterns around ORMs which work well, and design patterns around SQL which work well. You want to pick one and stick to it. The mess comes in when you mix layers of abstractions. One or two SQL procedures might not kill you, but when you have big chunks of code using ORM and big chunks NOT using ORM, you'll crash-and-burn. Footnote: "Complex domains" is also about the database layer, and the type of complexity. Right now, I'm working on a very algorithmically complex system, but that complexity isn't in the data layer. Most of the data engineering is about moving GB of data around in realtime. All the database needs to handle are simple things like auth/auth. That's an ideal use-case for an ORM. I've also built systems with ORMs where the database layer was complex, but complex in ways which aligned well with ORMs. So this shouldn't be read as "dumb programmers who can't handle functional use ORMS." Footnote to posterity: Should someone stumble upon this in a web search: If you grew up in OO and Java, and never ventured beyond, none of the above will make sense to you. You only learned the programming paradigm ORMs were designed for.
- sjwright 6y ago
  The intent of my third point is that you should expect a decent proportion of the operations to be handled well by the ORM such that you can reap efficiency benefits from the abstraction. If you're spending a significant amount of time jumping out of the ORM's sandpit and into custom queries, the benefit of the ORM may be limited. And to the extent that it dictates/influences your table structures, it might be a hindrance.
jbjohns 6y ago
But if your answer to all those questions is yes, why are you using an RDMS to begin with? Wouldn't some other kind of data store map better to those use cases, and then you wouldn't need a bit abstraction to make it look like something it isn't.
- sjwright 6y ago
  I completely agree, because ORMs are literally jamming a metaphorical round peg in the square hole. But enterprise gonna enterprise. Shrug.

sjwright 6y ago

Stored procedures are a really bad hack—in any practical sense they function as a mediocre API server which comes bundled "for free" with your SQL database server. If you need an API layer at all, it's much better to write this in a real programming language. Just about anything you can do with stored procedures, you can do within an SQL query block in your favourite language. And when all your queries are fully parameterised, your database server can even use the same execution plan cache as "real" stored procedures.

UK-Al05 6y ago

Don't put business logic in stored procs. They're great for storing complicated queries, but the actual buiness logic? No. Have some stored procs for updating and querying data, then a lightweight abstraction around them in the application.

taffer 6y ago
What does "business logic" mean to you? According to the Wikipedia definition [1], I find it very difficult to imagine SQL that does not contain business logic. [1] "business logic or domain logic is the part of the program that encodes the real-world business rules that determine how data can be created, displayed, stored, and changed.", https://en.wikipedia.org/wiki/Business_logic https://en.wikipedia.org/wiki/Business_logic
- UK-Al05 6y ago
  Business Logic is calculating tax, working out price, adding something to a bag, populating an order object with infomation according to some rules. Business logic isn't pulling data from database, how to map an object to a table, or the specific SQL query to grab some data. Those are technical details. SQL should just be pulling, saving and querying from the store without much maniplation. Not changing the data. It's the rules that a business stakeholder might be interested in creating. When you start putting the tax calculation rules into the stored proc, thats where the problems begin. If you create a CTE to pull the information required to perform the calulation then return that info without doing the calculation thats fine.
  - monadic2 6y ago
    How about a state machine encoding cart state? There are many advantages to encoding state changes in sql, but the database logic isn't strictly tied to the specifics of the cart logic. However trying to decouple these domains fully will create many more problems. There's plenty of fuzzy problems like this that interleave the technical and business. I fail to see how ANY part of a project would fail to be of interest to a stakeholder. They don't much care whether a failure emanated from code you have labeled of interest to them or not. You certainly can't make much money without supporting the business logic with—apparently—non-business storage. Even if you can patch up the leaks in this semantic distinction between these two domains the use of such a distinction is not clear in any general sense. These abstractions are to help the coder reason about complex systems and they don't always work. You then need to invent better abstractions.

BenoitP 6y ago

I have that that kind of divisive discussion. But we did compromise. What we ended up doing was using an ORM for CRUD operations, which were mostly user-facing ('row' operations); and SQL for reports / data monitoring ('column', bulky operations). And doing our most to avoid procstocs (limit them to executing REFRESH MATERIALIZED VIEW and COPY operations to the best of our ability), and trigger processing. We do manage the SQL code (view definitions, mviews, and the remaining procstocs) right next to the application code with Flyway [1]. We also have SQL-ORM integration tests, spinning a database container with testcontainers [2]. We've had some issues with business rule duplication (updating the ORM and forgetting to do the same in SQL), but so far I'd say it's successful. This being said, I'm on the SQL boat; and I remain convinced that the CRUD could be done comfortably with an SQL query builder such as jOOQ [3]; and that it would help solving the business rule duplication issue. But hey it's working right now and everybody is happy about it, so why change it? [1] https://flywaydb.org/ https://flywaydb.org/ [2] https://www.testcontainers.org/ https://www.testcontainers.org/ [3] https://www.jooq.org/ https://www.jooq.org/