ORMs are nice but they are the wrong abstraction

> It should not map onto objects

? To me, this is the main use of an ORM: don't let it do query building for you (unless it's fairly simple LINQ stuff), but just have something map the rows returned to typesafe objects for you.

Mapping rows returned from a query onto typesafe objects is okay, but doesn't require an ORM. It's quite literally just making sure each row:

1) Has the correct native type

2) Can be accessed by name

Many normal database APIs do that themselves. I guess there's a little bit more for writes.

The point of ORMs is that they generally map objects onto tables. You create a class, and the ORM will create the database table for you based on what you have in the class. For example, for Django, you write:

   class Blog(models.Model):
       name = models.CharField(max_length=100)
       tagline = models.TextField()

       def __str__(self):
           return self.name

(source: https://docs.djangoproject.com/en/5.0/topics/db/queries/)

And it will make the table for you. If you change the class, it will make the migration for you. That's clean, simple, and easy if you're not doing anything complex. It saves a ton of work.

However, this is a Very Bad Idea for complex systems, since exactly as you point out, there shouldn't be a 1:1 mapping between tables and classes. There may very well be a 1:1 or many:1 mapping between queries and classes. More sophisticated ORMs can do a bit of that, but at some point, you run into the equivalent of Greenspun's Tenth Rule, where the ORM turns into a more complex, buggy version of SQL.

The most interesting/fresh approach I've seen to this problem is Permazen.

https://github.com/permazen/permazen/

It starts by asking what the most natural way is to integrate persistence with a programming language (Java in this case, but the concepts are generic), and then goes ahead and implements the features of an RDBMS as an in-process library that can be given different storage backends as long as they implement a sorted K/V store. So it can sit on top of a simple in-process file based K/V store, RocksDB, FoundationDB, or any SQL database like PostgreSQL, SQLite, Spanner, etc (it just uses the RDBMS to store sorted key/value pairs in that case).

Essentially it's a way to map object graphs to key/value pairs but with the usual features you'd want like indexing, validation, transactions, and so on. The design is really nice and can scale from tiny tasks you'd normally use JSON or object serialization for, all the way up to large distributed clusters.

Because the native object model is mapped directly to storage there's no object/relational mismatch.

Too much ORM hate in this thread

They are really useful for like 90% of the work

The rest can be made with raw sql

Combine strengths of those two powerful tools, dont be religious

DPP (Deep Persistent Proxy Objects) is a javascript library starting from the same question:

https://github.com/robtweed/DPP