While I learn towards not using an ORM, the productivity gains (at the very least early on in development) are undeniable. What I've always looked for are frameworks that give you an ORM but also make lower level queries very easy, normally via a query builder, allowing you to go back and forth between levels of abstraction. If I had to choose, I prefer libraries that give you the lower level of abstractions first and then build upon those to offer an OOP-based ORM approach (which is what I believe most people think of when they say "ORM").
One of the best libraries I've ever seen get this right was TypeORM[0]. It was easy to get started with, includes consideration for migrations, allows you to use both the query building and annotated-class approaches where appropriate, also allowing for use of the repository pattern if you're comfortable with that, and has pretty great support for lots of different backends (I've used postgres the most though). All of this from a F/OSS project (~2 years ago I was also involved in a C# project during the switch from .NET 4.x to .NET core/standard and was very very annoyed that things I could easily do with TypeORM weren't available/worked out yet in EF core at the time).
I loved TypeORM initially, but came to the conclusion that it was written by people who really got TypeScript, but didn't really get SQL.
For example, we were converting from Sequelize, where we were catching unique constraint violations and responding appropriately, and we got quite confused that in TypeORM those errors never got thrown ... until we discovered that TypeORM decides unilaterally (and almost unbelievably) to replace your original record in that situation.
We have since ripped out all third-party ORMs and migrated to a system of typed helper functions and tagged templates (where all the typings are generated automatically at compile-time by inpecting the database). This is really rather wonderful — we now have full access to all sorts of goodies like native UPSERT, we know exactly what SQL we're going to get every time we touch the database, and yet the ergonomics are excellent, because everything going in and out of the DB is fully typed, including even column and table names in basically-raw SQL. A blog post (and perhaps eventually a library) is in the works ...
I couldn't agree with this more. I've just started using TypeORM and the TypeScript aspect is great. However, here I am trying to do a simple distinct left join, but it's extremely difficult to write because the query APIs consistently get in the way.
We're regularly receiving invalid SQL errors, which from a typed language/API seems particularly odd.
We're doing this exact same query on the exact same DB in ActiveRecord/Ruby and it's trivial to both write and understand.
This is where I land also. ORM's are excellent for the repetitive stuff, especially when integrated with frameworks but I don't want the presence of an ORM to prevent me from accessing the more advanced aspects of my database. I'm a heavy Postgres user and there's just...so much that it's capable of that hiding it behind an ORM to pretend it's "just a database" is a bit like a tragedy.
One of the better balances I ever came across from multiple languages was actually ActiveRecord because of the Scopes functionality. The scopes let you abstract certain parts of queries and name them, reuse them, include parameters, combine multiples of them back together, switch parts out, etc. You can combine raw snippets with fully abstracted parts.
It's extremely flexible and probably the feature of Rails that I miss most when I don't have it.
You can do this with most any ORM by mapping tables on top of views defined in SQL. I regularly use this pattern with Django's ORM to make complex aggregations only a foreign key away. I can write detailed performant SQL that it is impossible to make an ORM output this way.
The DataMapper pattern is very much included in the normal discussion of what an ORM is expected to provide -- so much so that almost most larger ORMs (that I know of at least) is listed on the Wiki[0], including TypeORM.
That said, you're right, the DataMapper pattern isn't quite identical, and it is a piece of fully OOP-based ORMs that I think are on the right side of the spectrum. I'd say that the DataMapper pattern fits in the middle -- I call it Serialization/Deserialization, and assuming the syncing of data is not constant (as in triggering action on another thread or something), then it can be anything from a function (ex. db::insert_user(user), user.write_to_db(db) or Adapter.save(obj)) to a dedicated object (Adapter.update(db, obj)).
If I had to try and point to a difference between the two, I'd say that DataMapper-ish or DataMapper-and-below levels of abstraction expose more internals by default (which is obvious) -- I find that ORMs on the right side of the spectrum I sketched up top try to never expose their internals, which quickly falls apart as soon as you step off the paved/well-traveled path.
[EDIT] I want to note that dealing with this syncing behavior in EF (Entity Framework) was really annoying. The constant management of a pool of objects that "reflected" the objects in the DB and had to be saved all together/flushed before they were manipulated or whatever was really annoying. That said, I am now aware that C# is one of my greatest weaknesses, so take this opinion with a grain of salt, it's entirely possible that I just didn't have the skill to do it right and keep it in my mind the right way. Just to make sure I'm not unfairly bashing EF I went and found a relevant SO post to show the kind of stuff I didn't like having to look up/deal with [1]. I'm picking on EF but this is likely an issue with any ORMs that handle it this way (w/ the object pooling & syncing).
There's another big aspect of ORMs a lot of people tend to skip in discussion: Security.
Raw SQL can be dangerous, and given enough people and code somebody will eventually make a mistake (as is human) and introduce a vector for a SQL injection attack or some other DB specific vulnerability.
A good ORM can be a fairly effective layer of safety.
IMO it is a humongous red flag if someone on your team talks about "raw SQL" and does not mean parametrized queries. I guess I could say I was fairly fortunate in my education/early career but this is a lesson you have to learn early when working with user input and databases.
often they don't mean that - they really do just mean "run any sql you want". just worked on a project that explicitly wrote their own bolted on 'db layer' and avoided the built-in ORM which had parameterized query support. i now know what 1200 sql injection-capable queries look like...
It can be, but it is hardly the only solution to that problem. I've also seen it solved, for example, with a git commit hook that just bounced any non-parameterized queries.
It's a spectrum to me:
raw (parametrized) queries ------ query builder + serialiation/deseralization ---------------- OOP-based ORM
One of the best libraries I've ever seen get this right was TypeORM[0]. It was easy to get started with, includes consideration for migrations, allows you to use both the query building and annotated-class approaches where appropriate, also allowing for use of the repository pattern if you're comfortable with that, and has pretty great support for lots of different backends (I've used postgres the most though). All of this from a F/OSS project (~2 years ago I was also involved in a C# project during the switch from .NET 4.x to .NET core/standard and was very very annoyed that things I could easily do with TypeORM weren't available/worked out yet in EF core at the time).
[0]: https://github.com/typeorm/typeorm