Postgres Is Your Friend. ORM Is Not(hypha.pub) |
Postgres Is Your Friend. ORM Is Not(hypha.pub) |
>“Roughly” because Django ORM doesn’t support the JSONB `?` operator.
The `has_key` [lookup](https://docs.djangoproject.com/en/6.0/topics/db/queries/#has...) does exactly that.
> And if you need real SQL intervals, Django pushes you towards raw expressions or `Func()` wrappers.
It's possible to use a very similar construct to SQL Alchemy here by using the `Now` [function](https://docs.djangoproject.com/en/6.0/ref/models/database-fu...) (it uses `STATEMENT_TIMESTAMP` which is likely more correct than `NOW()` here alternatively there is `TransactionNow`) by doing `Now() - timedelta(days=30)`.
The result is the following `filter` call
filter(
metadata__tags__has_key="python",
created_at__gte=(
Now() - timedelta(days=30)
),
)
("app_video"."metadata" -> 'tags') ? 'python'
AND "app_video"."created_at" >= (
STATEMENT_TIMESTAMP() - '30 days'::interval
)
- Your friendly local pentester
query = """
SELECT * from tasks
WHERE id = $1
AND state = $2
FOR UPDATE SKIP LOCKED
"""
rec = await self.db.fetchone(query=query, args=[task_id, TaskState.PENDING], connection=connection)
SELECT $1, $2($3) FROM $4
WHERE $5 $6 $7
GROUP BY $1
ORDER BY $8 $9
Anyway, I agree that ORMs are pretty terrible. I like writing SQL or using a lightweight builder like Kysely. Was a huge Dapper fan back in my C# days.
There are plenty of reasonable alternatives to ORMs that don’t open you to SQL injection attacks.
Relying solely on raw SQL and manual mapping certainly eliminates "ORM magic," but it replaces it with a significant maintenance burden.
For specialized, high-performance systems like video transcoding, this level of hand-tuning is a superpower; however, for the average CRUD-heavy SaaS app, the "boilerplate tax" of writing eighty lines of repository code for a simple related insert might eventually cost more in development velocity than the performance gains are worth.
Perhaps, but IME this kind of thing is much more often the cause of poor performance in CRUD apps than the frontend frameworks that are usually blamed. I have been able to make very snappy SaaS apps by minimizing the number of queries that my API endpoints need to perform.
I've also found that the ORM mainly reduces boilerplate for Insert/Update operations, but often adds a very significant amount of boilerplate for read queries. We ended up using a very lightweight orms for simple inserts / upserts and writing raw SQL for anything else.
Where does SQL fall in the video transcoding pipeline?
No, just because raw SQL queries work great for your toy blog/todo app with 3 tables and simple relationships, doesn't mean they work great for real world business applications with 100 tables and complex networks of relationships. Try maintaining the latter before you make blanket claims like "ORM bad".
In my experience, ORMs work well for toy projects, but become cumbersome to maintain in enterprise ones, especially where performance matters. There is a large overlap between engineers who refuse to learn SQL because it's not "convenient", and those who prefer ORMs because they are "easier", resulting in cohorts that don't know how to use either.
But also, I don't see how ORMs make managing large databases any easier, other than those with embedded migration capabilities, which can be very well extracted to their own tools.
It's also important to note that not all ORMs are created equal. Some are more restrictive than others, and that should also be taken into account.
I’ve once tried a "type-safe" SQL extension and it was pretty neat.
Imho something like this is much more useful than a lot of ORM-overhead.
I oscillate on being tired or amused by just how common tech people make this basic error. But I don’t believe it’s ever in bad faith. I think people in general suffer from perceiving their context as the context even though they’ve experienced maybe 1% of what there is out there.
But I would caution against adding too much business logic to the database, and tying message passing to your database doesn’t sound like the best of ideas.
Also, this whole point predicates upon the assumption that ORMs are infallible when translating queries into SQL, which most definitely are not.
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.
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.
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.
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.)
I personally rely on views to reuse base queries and then add filters on top of them.
Anything you can do with query builders, you can do with SQL of course, it's just more difficult / verbose (to me).
Let's say I have a lot of entities that are tied to domains, and I have users that have roles on those domains. I do a lot of reports, and for the vast majority, I don't have to worry about whether a user has the necessary access on the domain, I just call a central service, pass my query builder, the user, and the required access level, and it figures out whether the query builder already contains the necessary parts or not, which joins are required (e.g. if I'm selecting from subdomain, I need to join with domain and then join domain to user_domain) and add all of that transparently. I can treat service accounts differently, and I can just ignore all of that noise if a super admin is running the report.
I could absolutely do all of that in SQL, the SQL doesn't really change (if anything, query builders tend to produce worse SQL in my experience since you're not writing DB-specific code), just how I interact with it. The abstraction allows me to compose the query instead of writing it, giving me more flexibility.