Why object-relational mapping makes me sad

I’ve spent a lot of time lately dealing with SQLAlchemy, which is an object-relational mapper for Python. As you might guess from the title of this post, it makes me kind of sad.

SA’s job is to be an abstraction layer over the database, so that you can write queries as Python functions and get back your results as Python objects. The SQLAlchemy abstraction barrier does act as a barrier in some ways:

• You have no idea what queries your code might be generating.

• And if you see a raw query (e.g. because it’s flagged as running slowly), you have no idea what code generated it.

• Generated queries are massive and hard to read.

• SA has no idea if your database actually conforms to its schema. It just tries to guess and throws hard-to-debug errors if something’s wrong.

• You have no idea when queries are emitted, or how many of them, if you’re traversing relationships.

• You can’t easily connect database processes to the code that’s running them.

On the other hand, it totally punts on some other things:

• You need to explicitly declare how every relationship is loaded, every time you query.

• You’re still a slave to the inexpressiveness of SQL—for instance, having one column default to being equal to the primary key is randomly really hard.

• Cache invalidation is left entirely up to you.

• There’s some attempt at handling complex object relationships like polymorphism, but the way it works depends a lot on a choice of underlying relational representation (“joined” vs “single-table” vs “multi-table” inheritance).

I’m not claiming that ORMs are necessarily bad on net. We’re almost certainly more productive using SQLAlchemy than if we were writing raw SQL. But I can’t help but feel like there must be something better.