[DrewADesign]

I’m glad you’re head-over-heels in love with Postgres— it’s really cool, and I’ve occasionally had projects that really benefited from it… but most of those incredible features just aren’t useful for run-of-the-mill projects. Learning how to profile your ORM queries is a lot easier than maintaining a bunch of code from a different language embedded into your code base. If you’re writing articles about Postgres, you probably have no idea how much of a PITA that context switch is in practice. It’s funny how getting expertise in something can make it more difficult to understand why it’s useful to other people, and how they use it.

[manuelabeledo]

There are projects, like SQLC, that cover most of the perceived advantages of ORMs, without the downsides.

One of these downsides is, in my opinion, the fact that they hide the very details of the implementation one necessarily needs to understand, in order to debug it.

[DrewADesign]

Let me just say that I wrote my first (professional) SQL queries about 25 years ago and at various post points have worked extensively with Postgres, a bit less so with Oracle, and occasionally with MySQL and MSSQL. (And also some of the JSON object store databases before switching to Postgres for that stuff.) The only ones I’ve used ORMs with are Postgres and MySQL.

SQLC does not address most of the perceived advantages to ORMs. Sure it addresses some of the concerns of hand-writing and sending SQL to databases from various languages, but that’s not what most people I’ve spoken to in the past couple of decades most valued about ORMs. What most projects really need databases for is some place to essentially store context-sensitive variable values. Like what email address to send something to if the user ID is 12345. I’ve never, ever had to debug ORM’s SQL when doing things like that. Rarely have I needed to with more complex chains of filters or whatnot, and that usually involved taking a slightly different approach with the given ORM tools rather than modifying them or writing my own SQL. When I’ve had more complex needs that required using some of the more exotic Postgres features, writing my own queries has been trivial. It’s of paramount importance for developers to understand the frameworks and libraries, such as ORMs, they’re using because those implementation details touch everything in your code. Once you understand that, the code your ORM composes to make your queries is an IDE-click away.

Not having to context switch between writing SQL and whatever native language you’re working in, especially for simple tasks, has yielded so so so much more to my time and mental space than being exactly 100% sure that my code is using that left join in exactly the way I want it to.

[manuelabeledo]

First of all, congrats on your career.

Second, an ORM is just a translation layer, i.e. it does not compile to any binary format the database understands, and instead it gets translated to SQL, which is the standard, minus extensions. SQL is ubiquitous. It’s the closest to a lingua franca that we have in the context of software engineering. And I’m going to be blunt here and say that purposefully avoid learning and understanding SQL if it is part of the job, should disqualify anyone from it.

I’ve been around for some decades too, and to me, ORMs haven’t worked out. They are vastly different one from another and they often create issues that are clear as day when the query is written as SQL. If I go from a Typescript codebase to Python to Java, then, according to you, I should learn the intricacies of Sequelize, SQLAlchemy, and JPA/Hibernate, instead of just SQL. And granted, different SQL dialects have different quirks, but more often than not, the pitfalls are more apparent than when switching between ORMs.

And I can guarantee that someone equipped with a good foundation in SQL will be more successful debugging a Sequelize based application, than someone who has relied on SQLAlchemy.

What most people I know and worked with need, is types. Types help glue SQL and any other language together. If I can run any SQL query and the result comes back as an object, I’m good.

Now, if your point is that ORMs are OK for toying around, I may agree, but still, why would I go through that trouble when I know SQL.

[hectormalot]

SQLC for me has been able to replace most cases of use an ORM for. It made most of the boilerplate of using plain SQL go away, I get type safe responses, and it forces me to be more mindful of the queries I write.

In an app where we do use an ORM (Prisma), we sometimes have weird database spikes and it’s almost always an unintended heavy ORM query.

The only two things I miss in solutions like sqlc are dynamic queries (filters, partial inserts) and the lack of a way to add something to every query by default (e.g., always filtering by tenant_id.)