DEV Community

O/RM is like adding brain damage to your database

Thomas Hansen on August 25, 2022

O/RM is the art of fitting a square peg into a round hole. Your SQL database is based upon records and relations, and objects are based upon OOP. O...

Read full post

JoelBonetR 🥇 • Aug 25 '22 • Edited

I can imagine the following situation (with or without ORM, it doesn't matter):

The manager tells John to update the user name to "Pepe" and the boss tells Jane to update the user name to "Juan", both proceed at the same time and hit the button at the same time (that's unlikely to happen due to the same time meaning the same absolute time but let's imagine it's possible)

Both hit the button at the same time "SUBMIT".

Which name will the user have? You can't know without looking at the DB.

Let's imagine the difference between John and Jane hitting the button is of 1ms, being John the first one and Jane the second one.

Which name will the user have? Again you can't know without looking at the DB. Maybe the request of Jane took more time to process than the one of John. Which you can solve with transactions but even then, maybe the request of Jane took more time to reach the server and thus the result will be "Pepe" and not "Juan".

The solution you propose against ORMs is pretty common when using ORMs (the pattern I mean) as well and it's simply send the update request for the property that changed regarding the fetched object you already have.

Imagine John and Jane working on the same object at the same time. John updates the customer's Address, and Jane updates the customer's PhoneNo. They both start working on the object at the same time, and have therefor "fetched" the object from the database. John updates the Address field, and clicks "save". Jane updates the PhoneNo field, and clicks "save". Since Jane got her customer object from the "repository" before John saved it, the Address is now reverted back to its old value, the value it had before John updated it. This is referred to as a "race condition". Because the one to save the object last "wins".

Let's convert this following example you shared into something less moronic and more reallistic:

Imagine John and Jane working on the same object at the same time. John updates the customer's Address, and Jane updates the customer's PhoneNo. They both start working on the object at the same time, and have therefor "fetched" the object from the database.
John updates the Address field, and clicks "save". Jane updates the PhoneNo field, and clicks "save".

Since Jane just updated the phone number, the request is only meant to change the phone number, while since John just updated the address so it sends a request to update just the address.

This deals to a better pattern in which you reduce the network traffic, get more specific requests, faster and lighter to process.

No drama, everybody wins, no race conditions affecting different fields on the same schema and the most important, Noting to do with ORMs but on validations.

When you validate an object in the update process you set the values as optional (except of course the primary key). If there's something on any field, validate it with the data type, length and whatever. Then send the update to the ORM.

const customer = {
   Id: id,
   Name: name,
   Address: address,
   PhoneNo: phone,
}

validate against:

const customerSchemaForUpdate = {
   Id: number().min(1).required(),
   Name: string().min(2),
   Address: string().min(3),
   PhoneNo: string.min(9),
}

Update requests get:

const customer = {
   Id: id,
   Address: address,
}
// and
const customer = {
   Id: id,
   PhoneNo: phone,
}

Then the request to the ORM looks like:

// customer = { id: 1, address: 'my Address 64' }
Customer.update( customer, { where: { id: customer.Id } }).catch((error) => console.error('err', error));

and the next req will be

// customer = { id: 1, PhoneNo: '666222787' }
Customer.update( customer, { where: { id: customer.Id } }).catch((error) => console.error('err', error));

Now everything put together:

const customer = req.body; // customer { Id: 1, PhoneNo: '666222787' }

const customerSchemaForUpdate = {
   Id: number().min(1).required(),
   Name: string().min(2),
   Address: string().min(3),
   PhoneNo: string.min(9),
}

const isDataOk = validate(customer, customerSchemaForUpdate) // ok | error

if(isDataOk) await Customer.update( customer, { where: { id: customer.Id } }).catch((error) => console.error('err', error));

And there you go! Both requests can be executed at the same time, asynchronously etc without the issue mentioned in the post and using an ORM, which brings some extra benefits in security and development speed and maintenance.

I do not use OOP in most situations, I just use Objects for convenience hence you can create a new post about using half OOP in those situations and I'll probably agree but it's just that I don't care. I use a multi-paradigm language (JavaScript) so I use the paradigm that's more convenient for each task.

The same way when you read something from a model, you got an object (that's ok) with the fields you actually need, not with everything. e.g. you may not need the created_at, updated_at etc of each register to show movies from Matthew McConaughey on a public view so, why asking for them in the first place?

Having a user without information on optional fields doesn't make the "user" be less "user" and threating required fields (on create) as optional (on update or get) also doesn't harm the overall ORM philosophy.

The only thing I can say generically against ORMs is that If you are a master at SQL, you can probably get more performant queries by writing them yourself.

this is fundamentally incompatible with best practice O/RM design patterns, such as Active Records, Repository Pattern, etc, etc, etc.

In repository pattern, mapping is not the responsibility of the repository. It’s the responsibility of your controllers 🤷🏻‍♀️

It's not the tool (ORM) but how you use it so again, the premise IMO is incorrect, hence it is the conclusion as well.

Hope it bring some light to the topic 😉

Thomas Hansen • Aug 25 '22

The solution you propose against ORMs is pretty common when using ORMs as well and it's simply send the update request for the property that changed

It is not very common, it's extremely rare. Most people implement object.Save(), which updates everything.

Since Jane just updated the phone number, the request is only meant to change the phone number, while since John just updated the address so it sends a request to update just the address.

This is the correct way to do things, and the way Hyperlambda does it. But it is fundamentally incompatible with ORM libs without violating the entire "philosophy" of how an ORM lib should be used. Most who actually do implement it, implements it by retrieving the object, applying the changes, and (sigh!) invokes updatedObject.Save(). I don't even need to tell you why that's bad I presume ...

Noting to do with ORMs but on validations

There doesn't exists a single ORM that implements this. Because it is a violation of the "Design Patterns" ORMs' are built on. Don't get me wrong, some ORMs do implement this, but the devs using this feature of ORMs are no longer using their ORM as an ORM, but rather as "something else". Regardless, once you start using your ORM like this, you've got no use for your ORM, and you might as well throw it out. EF, the most popular ORM for .Net had existed for more than a decade before they even implemented the ability to do this ...

Then the request to the ORM looks like:

Your code is fundamentally incompatible with OOP. If you're using such code, you're not using OOP, and you're not using ORM, since the latter implies an OOP model ...

Hope it bring some light to the topic 😉

JoelBonetR 🥇 • Aug 25 '22 • Edited

It is not very common, it's extremely rare. Most people implement object.Save(), which updates everything. ... it is fundamentally incompatible with ORM libs without violating the entire "philosophy" of how an ORM lib should be used. Most who actually do implement it, implements it by retrieving the object, applying the changes, and (sigh!) invokes updatedObject.Save(). I don't even need to tell you why that's bad I presume ...

We may have very different experience on projects, languages, tools and overall but only saw this on a couple of "dinosaur" projects in which they handled those things on various abstractions (non-sense) and sometimes even wrong.

I've always worked like that explained on the comment above for reasons I feel obvious.

Your code is fundamentally incompatible with OOP. If you're using such code, you're not using OOP, and you're not using ORM, since the latter implies an OOP model

As far as I'm concerned I don't use classes in JS, just Objects (as everything is an Object and the Object Object itself is a convenient data structure to use) and also don't think there's a single thing to rule them all so If some thing needs a paradigm for any reason good! go for it but I won't stick to a single thing.

I may need to take a think on that to see why is it incompatible with OOP.
AFAIK there are optional props in most languages that implement OOP, i.e.

class State {
   private Integer something; 

   public Optional<Integer> getSomething(){
      return Optional.ofNullable(something);
   }
}

Maybe @codenameone can bring some light here as I'm not using Java since some years ago.

At first glance I think those are different topics, on one side we have Object Mapping (to a model) and on the other side we have object property propagation (how many of those properties you need to propagate and/or use on each place).

Shai Almog • Aug 25 '22

I don't see any way in which this is incompatible with OOP. SQL itself isn't OOP and we use ORMs to bridge the gap. They aren't ideal OOP and one of the big mistakes people make with such tools is to build them as if they are (excessive inheritance in object model, etc.).

There's no need to be religious about OOP or functional programming. Each has its strengths and we each find the balance in the languages best suited for the job.

I'm good with null personally even though we have Optional. That maps directly to the way the DB stores the data.

Save doesn't update everything in tools like hibernate. It instruments the object and knows the specific fields we set. You don't need any optional or anything for this to work seamlessly. It generates relatively long and verbose queries but they are more efficient as a result.

Thomas Hansen • Aug 25 '22

As far as I'm concerned I don't use classes in JS, just Objects (as everything is an Object and the Object Object itself is a convenient data structure to use)

Bingo; ORM is an OOP pattern. No OOP, no ORM. JavaScript is not OOP. You're not using an ORM, you never have been using an ORM, and assuming you don't start typing in a strict typed programming language any time soon, you probably never will use an ORM either - What you're doing is using the good parts from JavaScript to circumvent the problem of optimistic/pessimistic database locks ...

JoelBonetR 🥇 • Aug 25 '22 • Edited

I took the sentence of ORM being an OOP pattern as true at first sight but something was crackling in my head.

Take that definition instead: ORM is a programming technique for converting data between incompatible type systems in relational databases and objects in programming languages. This creates, in effect, a "virtual object database" that can be used from within the programming language.

You have a RDB with it's types and it's tuples and so on, you create Objects with properties that map those.
As long as the programming language accepts Objects it does not need to live in an object-oriented implementation, and even objects didn't exist you could use any data structure to replicate the same.

Also nothing to do with strict typed programming languages; a varchar(180) will never map to a programming language type, that's why you add validations and logic in between, to tackle certain security concerns and ensure your data is consistent in the DB and to prevent errors or your data being cut down when trying to store it.

String property; // just maps to "String" type, regardless of the length
Char myProp; // can also be stored in a varchar or text DB type, but is not the same than String

I get my Model mapped to Objects, hence the Object-Relation Mapping is fulfilled. So yes, I'm using an ORM.

Thomas Hansen • Aug 25 '22 • Edited

O in ORM implies “Objects”, as in strongly and strict typed OOP. No strict types, no ORM. Many will disagree with me here, but ORM is fundamentally a design pattern for OOP languages. No (strictly typed) OOP, no ORM …

You’re using something else. Don’t believe me, check out the Java guy commenting here … 😉

JoelBonetR 🥇 • Aug 25 '22

If you invent something like Array Oriented Programming and ArrayHandlers instead classes, while adding functions to Array indexes instead methods it will not make Array exclusive of this weird thingy.

Objects are not exclusive of OOP.
OOP does not require strict typing, those are different concepts.
An ORM also does not need that you implement everything in OOP, the implementation details of the ORM will use OOP themselves.
There are FRM as well as ORM, the purpose is -allegedly- similar but the implementation is -obviously- different, as SQL is a declarative language itself you don't get as many benefits as you do with ORMs. It has more sense to use ORM than FRM -usually- even when dealing with FP implementations.

If I'm using JavaScript (imagine I don't use TS) and without implementing OOP as paradigm for everything will not make my ORM be less ORM. The OOP implementation is in the ORM itself and I don't need to follow the same, I just need to use what it provides.

Thomas Hansen • Aug 25 '22

about Array programming languages? It's already been with ys for 6 decades. It's called Lisp 😉

You're using the "good parts" from JS. JS is not OOP. I'll probably have 95% of the workd disagreeing with me here, but the truth is the truth regardless of how many who argue against it ...

JoelBonetR 🥇 • Aug 25 '22 • Edited

JS is a multi-paradigm language, and OOP fits inside this "multi". It not being "Pure" doesn't mean it's not capable of handling OOP. Neither you need Pure OO to provide an ORM.
Also Lisp has the code represented as linked lists that does not mean is array oriented.

Thomas Hansen • Aug 25 '22

Bring it up with Martin Fowler 😉

JoelBonetR 🥇 • Aug 25 '22

Maybe you should ask Alan Kay instead, don't you feel it more appropriate?

“OOP to me means only messaging, local retention and protection and hiding of state-process, and extreme late-binding of all things.” ~ Alan Kay

😉

Thomas Hansen • Aug 25 '22

I agree. Unfortunately 99% of the world still believe OOP is equivalent with C++ and Java ... 😕

I built Hyperlambda by drawing inspiration from Lisp and Alan Kay in fact. You should watch Alan Kay's speech at OOPSLA 97 BTW ...

JoelBonetR 🥇 • Aug 25 '22

Unfortunately 99% of the world still believe OOP is equivalent with C++ and Java

By the rest of the post and comments, you seem to be in this 99% isn't it?

Again those are different concepts.

On one side there is OOP (a programming paradigm) and on the other side we have programming language implementations which can be more or less accurate to fulfill the paradigm and can or cannot be a re-interpretation of the original paradigm.

I'm not picky with this things, as far as I'm concerned, if Java and C++ did an OOP implementation that's not aligned with the original idea of OOP the people behind that should have good reasons to proceed that way (and they had in fact).

You should watch Alan Kay's speech at OOPSLA 97 BTW

I'll search that in youtube and save it for later 😁 can't recall if I watched it but even if I did it was long time ago.

Thomas Hansen • Aug 25 '22

His main thesis is that we ate still in the stoneage. The name is "The computer revolution has not happened yet" ... 😉

JoelBonetR 🥇 • Aug 25 '22

Well there's always iterations.
Look at the ML niche, analog computers are getting stronger on that field of study for good reasons.

Shai Almog • Aug 25 '22

Update is a problematic statement and most of us avoid it so the chance of a race like that is pretty rare. This is true regardless of what you use, immutable data is better and much easier to optimize at scale.

Sometimes we do need it though and in this case ORMs handle locking almost seamlessly. No 25% of code or anything really. You can enjoy optimistic or pessimistic locking in ORMs.

About performance. Since caching is the mother of all performance optimizations by giving up on ORM you give up on Level-2 distributed caching. There goes 90% of your performance down the drain when compared to ORM.

I get that you don't like OOP. I disagree but that's subjective. But ORMs can be far superior to the DB on which they are situated. They are much easier to refactor and adapt to new SQL features and can be more performant. That isn't the case with coded SQL.

There are valid cases to be made that the abstraction leads to ignorance and people who don't benchmark the resulting SQL. But that can be said about almost any technology.

Thomas Hansen • Aug 25 '22

immutable data is better and much easier to optimize at scale

So you're proposing for me to not being able to update records in my database? With anything but an ORM, updates are not particularly problematic, they're really quite simple in fact. I added an SQL update statement in my OP to illustrate how easy in fact ...

Update is a problematic statement and most of us avoid it so the chance of a race like that is pretty rare

i rest my case ... ;)

Sometimes we do need it though and in this case ORMs handle locking almost seamlessly. No 25% of code or anything really. You can enjoy optimistic or pessimistic locking in ORMs.

Both of these implies code propagating into the UI layer such as I illustrate with the; "We couldn't save your object, do you want to force it, reload from storage, or cancel" modal dialogue box. Either that, or silently failing the update ...

by giving up on ORM you give up on Level-2 distributed caching

Not true. Not having an ORM doesn't imply anything else besides really "not having an ORM". Everything that an ORM does, is just as easily implemented in everything that's not an ORM ...

Shai Almog • Aug 25 '22

Updates are easier with an ORM but updates are inherently bad since they are mutable state and can fail (regardless of ORM). They should be minimized in a good DB strategy. This scales better and provides logging for recovery/tracking.

Everything will end up failing somewhere if you have two users updating the same row. This was true when I was writing fat client SQL in the 90s and it's true now. You seem to gloss over these inherent facts as if races don't happen. Two users send a bad update, then who wins?

ORM fails and solves that dilemma for us. In your approach the failure will be in bad data. A user will have his work overwritten and lost forever. That's why we don't do updates.

Not true. Not having an ORM doesn't imply anything else besides really "not having an ORM". Everything that an ORM does, is just as easily implemented in everything that's not an ORM ...

You can't cache at scale without an abstraction. You either create your own abstraction and deal with all the bugs/complexity/scale. Distributed caching is one of the hardest problems in our industry. Doing it without an existing framework is a whole lot of work and is very likely to include many serious bugs.

Thomas Hansen • Aug 25 '22

Updates are easier with an ORM but updates are inherently bad since they are mutable state and can fail

No, updates are inheritingly more difficult with ORMs, and the reason is originating from OOP being an inheritingly bad idea. ORMs just further emphasise how bad of an idea OOP was in the first place.

They should be minimized in a good DB strategy

I don't disagree, I like immutable data. However, I've got a client_account table. I've got 11,000 log items referencing one of my client accounts. I need to update one field on my client_account field. What do you propose I do? Tell my users "Sorry mate, we can't update the phone number on client accounts because we're using O/RM" ...?

Everything will end up failing somewhere if you have two users updating the same row

Not true. This is an "ORM only problem". If you disagree, feel free to explain shy, with an example use case. Most database systems today are ACID compatible, and there are never any problems with such things in ACID ...

You can't cache at scale without an abstraction

You don't need an ORM to abstract. Below is an example from Hyperlambda.

cache.try-get:some-cache-key-value
   expiration:5000
   .lambda
      sleep:10000
      .result
         result:succees
      lambda2hyper:x:-/*
      return:x:-

Adding Redis to the mix makes it distributed. And yes, the above Hyperlambda is thread safe ...

Shai Almog • Aug 25 '22

I think you had a bad experience with a specific ORM tool. Hibernate does have edge cases that you need to understand to leverage it properly but it's pretty amazing and lets you just invoke native SQL whenever you want. So even if you do find such an edge case, you can always do an update with SQL.

I do disagree though. Hibernate updates are fast and generate code that would be inconvenient to handcode.

I need to update one field on my client_account field. What do you propose I do? Tell my users "Sorry mate, we can't update the phone number on client accounts because we're using O/RM" ...?

Again. Nothing to do with ORM.
Personally I have a separate table of user properties which is where we add meta data and delete old entries. But if you want you can use an update. This is one of the cases where update makes sense. It's a rare operation that will happen once in a blue moon. The likelihood of collision is very low.

You don't need an ORM to abstract. Below is an example from Hyperlambda.

That's key-value caching. Are you comparing this to ORM level-2 cache?

Say you pulled an SQL value out of the DB and placed it into Redis. Now every single place where you do that handcoded update you need to remember to invalidate the Redis key or you might have a serious problem. Are you using the same transactional context between SQL and Redis?
What if the transaction fails? Do things go into Redis or not?

All of those things are handled seamlessly with an ORM and a transaction manager.

Redis is great. I use it a lot. But it's a key-value store. It isn't your SQL database.

Thomas Hansen • Aug 25 '22

You have a lot of valid points. You’re still fundamentally wrong, because you don’t see the fundamentally bad idea of treating an RDBMS things within the context of a strictly typed OOP language, AKA ORM…

Shai Almog • Aug 25 '22

That's an argument I can agree with. ORMs are a leaky abstraction that marries SQL with OOP. Two very different things. This causes most of the problems with ORMs.

Having said that the alternatives for OOP programmers are worse:

Use SQL directly
Use a less OOP abstraction
Use an OOP DB - these are terrible

I'm not an OOP purist. I think pragmatism trumps everything. Java isn't pure OOP either which is one of the things I like about it.

Thomas Hansen • Aug 25 '22

Check out Dapper for .Net ... 😉

Shai Almog • Aug 25 '22

I had you pinned as more of a jooq person...

Thomas Hansen • Aug 25 '22

Hyperlambda is my choice of kicks ...

Luis Mesa • Aug 26 '22

You're probably using outdated tech... in the past 15 years I've been in Novell, IBM, Amazon... Never had an issue with race conditions by using ORMs. We've had thousands to millions of requests per second. Nobody updates the whole row on every property change, so I suspect you're using outdated tech or a poorly implemented homegrown solution.

Thomas Hansen • Aug 26 '22

Entity Framework, "best practices" typically results in this. EF is the by far most popular ORM on the .Net platform, but the problem isn't related to any specific ORM, it's related to "best practice design patterns" a lot of us have been taught over decades of software development, such as repository pattern and active records, using view models and db models. I have seen such issues resulting from nHibernate, Entity Framework, even Dapper, and tons of other ORM libs.

As to outdated; CosmosDB, the arguably newest contribution to NoSQL database systems doesn't even have partial record updates. With NoSQL database systems the problem grows exponentially ...

NoSQL is much "newer" than SQL - Just sayin' ... ;)

When I say race conditions though, I don't mean literal race conditions in code. To understand these race conditions, look at the use case of John and Jane updating the same record.

Thomas Broyer • Aug 25 '22

I tend to agree with @joelbonetr that this isn't actually about ORMs.

Let's just look at your video which looks at the HTTP requests, and not SQL queries. A decade ago, Google create RequestFactory in GWT, which would make HTTP requests basically looking like yours (much more complex because it models a true object graph, allowing object reuse, reference cycles, etc. and it's not just CRUD, a bit like GraphQL if you squint; anyway) but on the server side it would load the entity, "patch" it, and then save, so it would totally fit with ORMs.

Now let me imagine a different situation: John and Jane make adjustments to "data", and some fields' value depend on other fields (it could just be the traditional cascading dropdowns or similar filters like "this field cannot have this value if that other field has that value"; or it could be more "functional"/business, without hard rules encoded in the software: the user sees the data and adjusts one field based on what it sees on the screen in the other fields). Now if you only "patch" one field at a time, losing the context in which that modification was made, what guarantee do you have that the data still is meaningful? how about validation rules across fields?

Users don't look at the actual data, they look at a snapshot that was fetched moments ago. If their intention is to only modify one field, then sure your solution will work ; if they check each field and fill in others based on that context, then they'd rather either have a lock on the record preventing any modification by another user, or have to deal with "optimistic locking", i.e. "someone made a change to that record since you fetched it", with a conflict resolution screen or whatever (offer to save as-is and have the user look at the audit log to see what had changed and possibly redo the changes)

If you really want to solve the issue of concurrent modifications (while still allowing them), then you'll need changes reflected in real time on the screen, similar to how you can collaborate in a Google Doc or Google Spreadsheet.

Thomas Hansen • Aug 25 '22

What you are describing is intelligent and interesting, but does not invalidate the primary argument, or change anything. ORMs are still garbage tech, solving the wrong problem, with the wrong solution ...

Luis Mesa • Aug 26 '22

"Damn kids, get out of my lawn!"
"Music in my times was better"
I've implemented ORMs before Hibernate and EclipseLink existed. There's .umtiple ways of avoiding updating the whole object or race conditions. You can even have distributed caching in the middle with row versioning for a seamless experience. After over 20 years, I've started making things simpler, most of my microservices are dumb CRUDs. I see your point, but I understand the other side as well. Use whatever you want, and let others use whatever they want... nobody can learn in the head of another.

Thomas Hansen • Aug 26 '22

but I understand the other side as well

I do too, but don't tell anyone ;)

Really, my point with these articles, is to give people "forgiveness" for creating simple code. Simple code is almost always superior to complex code ...

Luis Mesa • Aug 26 '22

Not almost always... I'd say always, period.

Thomas Hansen • Aug 26 '22

Hehe, agree! But yet again, don't tell anyone, I still want people to believe they can make me change my mind by arguing ... ;)

Paweł Świątkowski • Aug 25 '22

With today's javascriptization of the dev world (at least web dev), databases are mostly used as a fancy typed document storage with magical querying capabilities. And in document storage you always read everything and write everything. ORMs map pretty well to this.

I'm not saying you are wrong though, just that for many people it might be hard to understand the issue.