News for hackers. Stuff that matters.
Re:What ORMs Have Taught Me: Just Learn SQL (2014) by unklefolk on Wednesday October 21, 2020 @05:25AM (#24846116)
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.

Re:What ORMs Have Taught Me: Just Learn SQL (2014) by BenoitP on Wednesday October 21, 2020 @08:44AM (#24847244)
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/

[2] https://www.testcontainers.org/

[3] https://www.jooq.org/

Re:What ORMs Have Taught Me: Just Learn SQL (2014) by davidgl on Wednesday October 21, 2020 @05:31AM (#24846142)
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
Re:What ORMs Have Taught Me: Just Learn SQL (2014) by sjwright on Wednesday October 21, 2020 @05:34AM (#24846152)
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.

Re:What ORMs Have Taught Me: Just Learn SQL (2014) by jbjohns on Wednesday October 21, 2020 @09:31AM (#24847687)
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.
Re:What ORMs Have Taught Me: Just Learn SQL (2014) by sjwright on Wednesday October 21, 2020 @08:28PM (#24853788)
I completely agree, because ORMs are literally jamming a metaphorical round peg in the square hole. But enterprise gonna enterprise. Shrug.
Re:What ORMs Have Taught Me: Just Learn SQL (2014) by TobiasA on Wednesday October 21, 2020 @06:01AM (#24846270)
Per your last point, why don't you think ORMs work well with complex domains?
Re:What ORMs Have Taught Me: Just Learn SQL (2014) by murgindrag on Wednesday October 21, 2020 @08:13AM (#24846992)
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.

Re:What ORMs Have Taught Me: Just Learn SQL (2014) by sjwright on Wednesday October 21, 2020 @09:24AM (#24847616)
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.
Re:What ORMs Have Taught Me: Just Learn SQL (2014) by UK-Al05 on Wednesday October 21, 2020 @05:41AM (#24846180)
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.

Re:What ORMs Have Taught Me: Just Learn SQL (2014) by taffer on Wednesday October 21, 2020 @06:49AM (#24846525)
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

Re:What ORMs Have Taught Me: Just Learn SQL (2014) by UK-Al05 on Wednesday October 21, 2020 @06:54AM (#24846540)
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.

Re:What ORMs Have Taught Me: Just Learn SQL (2014) by taffer on Thursday October 22, 2020 @04:48AM (#24856025)
Let's assume there is a business requirement that an order belongs to a customer. By definition, this is business logic. If you implement this requirement by creating a foreign key constraint, then you have added business logic to the database. If there is a business requirement to calculate the sum of all wages in a department, and you do this via an aggregation in SQL, then you have processed business logic in the database.

If you are not allowed to do this kind of things, then what is left? Do you interact with your database as if it were just a bunch of VSAM files?

Re:What ORMs Have Taught Me: Just Learn SQL (2014) by UK-Al05 on Thursday October 22, 2020 @04:52AM (#24856044)
Tbh a lot of NoSQL databases don't use foreign key constraints. If you have a SQL system with messaging and eventual consistency you have to turn them off anyway.

So in a lot of ways, yes people use databases like they're just persistent memory with indexes.

Re:What ORMs Have Taught Me: Just Learn SQL (2014) by sjwright on Wednesday October 21, 2020 @05:37AM (#24846169)
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.