To ORM or Not to ORM

Is there a solution in Python/Postgres where I don't have to have ORM'd tables, but that much of the boilerplate SQL CRUD-type things can be reduced? Basically just beyond writing raw sql.

I've used many ORM's in multiple languages and on multiple teams and have never had a good experience with any of them, once the complexity grew beyond trivial.

I've been tasked with various database optimisation tasks over the years and ORM's have always gotten in my way when it came to understanding a queries indexing and locking and improving/fixing it. I always end up just looking at the logged SQL, fixing it in SQL and then trying to convert it back to ORM code, which has always been tedious. In my experience, in multiple otherwise talented teams, ORM's seem to encourage less-than-ideal database modelling and overfetching data because its too easy to just make an ORM class do it for you rather than really thinking about your data and the data model you need. This isn't ORM's fault of course, but rather the teams using them, but its happened so frequently that I don't think "discipline" or "better developers" is a valid answer. [1] I used to hate databases and dread working with them for the above reasons.

I recently started loving databases by working with Clojure and the HugSQL library. The reason I love this approach is that Clojure isn't OO, so there's no Object mapping: I deal with plain old data instead, in whatever format I make the query return it (I can then easily transform that data further if I need) and HugSQL lets me write (composable and parameterised) raw SQL. Its fantastic and has been super productive for me. I can think in pure data and model the database how it makes sense and still have a good understanding of the indexing and locking of my queries.

[1] As jandrewrogers said: "in almost every place I've seen them used they've become a way for developers to avoid understanding how databases work, inevitably leading to inexplicable data models and poor performance. In practice, ORMs tend to end up creating crippling technical debt" and this has been my experience too. Sure, you can use an ORM well, but in reality, this seems to happen so rarely that in my opinion, ORM's are a net negative to be avoided if possible. Saying that you just gotta use them right (when few people seem to actually do so) seems like a bit of a no true scotsman argument to me.

What a great article, this paragraph really reasonated with me:

> Any situation where complex functionality is wrapped in another layer runs the risk of increasing the overall complexity when the wrapping layer is itself complicated. This often comes along with leaky abstractions - wherin the wrapping layer can't do a perfect job wrapping the underlying functionality, and forces programmers to fight with both layers simultaneously.

I think we can all agree that a mapping layer from a relational DB to your language is needed in almost every application, so the question is whether to build it yourself or use an off-the-shelf solution.

I work at prisma.io and we've set out to make database workflows as easy as possible for developers, this includes database access (= ORM) and schema migrations.

With polyglot persistence becoming a standard pattern in modern applications, today's ORMs fall short since they only map from relational databases. Our goal at Prisma is to provide a type-safe mapping layer to all your databases at once. This will allow developers to focus on their application logic and not worry about how to access the data from the various data sources.

Fine grained control over locks, transaction levels, JSON indexing, etc. In fact, some databases (older versions of Postgres, Oracle, etc.) do not actually have true serializability so explicit locks may be the only way to implement certain logic correctly. I picked on explicit locks since it is quite useful as well as being generally poorly supported by ORMs.

Have you consider posting this as Show HN?