📊 Languages' Popularity on DEV

More fully, it's the "object-relational impedance mismatch". This refers to the central problem of using an object-relational mapper (O/RM) framework to handle data access and manipulation. O/RMs require you to implement a parallel data model in your application code: if you have a users table, you'll have to write a User class which represents records in that table, and so forth.

The issue is that relational databases work in a fundamentally different manner from application code. A relational database cares about two things: records, and relationships between records as denoted by foreign key constraints. Application code works with all manner of more complicated data structures and techniques: hierarchies, iteration, graph traversal. So when you design a second, sort-of-but-not-completely parallel data model that works for your application code, it's not so easy to translate that into the existing data model expressed by your database schema.

If your users table (and hence your User model) has a visits field and you realize that you'd been double-counting visits, you might want to amend the data you've collected so far. With SQL, you'd UPDATE users SET visits = visits / 2; -- fairly simple. However, if you're using an O/RM and don't want to write SQL, you'll likely have to load each User into memory, halve its visits, and persist it back. This takes much, much more time and makes two round-trips to the database for each record in the users table. It's much quicker to abandon the O/RM and go with the SQL query, and indeed most O/RMs offer the ability to run raw SQL because the impedance mismatch is not a problem you can beat head-on.

The impedance mismatch is a problem characteristic of O/RMs specifically. There's nothing special about NoSQL databases which eliminates it, and in fact it's quite possible to work with a relational database without running into it: you just have to choose a data access framework which isn't an O/RM. Note that the opposite is also true: if you use an "O/RM" for a NoSQL database, like Mongoose for MongoDB, you can still run into the impedance mismatch. Look into data mappers if you still want to use a relational database -- I highly recommend at least investigating it unless you know that your data architecture requires specialized storage, since relational databases are much more generally useful than NoSQL stores.

Ted Neward's blog post The Vietnam of Computer Science goes into more detail, and if you have a SkillsMatter account I talked about the impedance mismatch in the course of describing why my own project Massive.js isn't an O/RM at FullStack London last week.

The class then translates, to the best of its ability, SQL queries that relate to that table. But first, must connect to them, which could have been done much more quickly by straight-up SQL and much earlier in the process.

I feel this is not part of the impedence mismatch. ORMs are usually backed by connection pools and lazy connections are definitely a pro (most of the times you don't want your code to connect to the DB until the data is asked for)

Then, as with all processes that are meant to be easy-to-use, the SQLAlchemy class uses an over-generalized method of connecting, aggregating, and updating data from the db, before then reorganizing it into the form we expect when the round trip completes

Sure, there's some overhead, because data has to be translated and converted and objects have to be created and so on

A common (and easily solvable) issue with ORMs is the N+1 which roughly translates to something like select all users, and for each user select their comments, this can be solved by what ORMs call eager loading which translates to something like select all users, join with the comments table and load the comments at the same time

Another common issue with ORMs is the update example Dian gives. If you have to update a single column in bulk the naive and very slow way to do it is to load the objects in memory, iterate through them, change the column and save the object. A better way to do it, in SQLAlchemy, is through bulk methods which are much faster (but not as fast as using the core which basically generates a single SQL UPDATE). In Rails you probably have to use something like activerecord-import

Same goes for the combination of the two issues above (N+1 update? don't know the name :D)

I've just never thought about what was going on under the hood with an ORM and want to make sure I understand what is being explained to me here :D