Hacker News Reader

The main problem I've encountered with complaints surrounding ORMs usually tend to be the result of trying to overfit the ORM in a certain way.

ORMs are, for the most part, good at the CRUD operations - that is to say, they easily translate SELECT, UPDATE, INSERT and DELETE operations between conventional class objects and database rows.

Things they usually aren't very good at are when you start trying to do things that require a lot of optimization - it's very easy to have an ORM accidentally retrieve way more data than you need or to have it access a bunch of foreignkey data too many times (in Django you can thankfully preload the latter by specifying it in a queryset). That's less an issue for basic CRUD, but is an issue if you're doing say, mass calculation and only need one column and none of the foreignkey data for speed reasons.

Basically - an ORM is good but don't let yourself feel suffocated by it. If it's not a good fit for an ORM, then don't do it in the ORM, either use SQL code to do it in the DB server or do a simpler SELECT (in the ORM) and do the complex operation in your regular application before INSERTing it back in the db (if that's a goal for the operation anyway). If it's outside of the CRUD types of DB access already, the extra maintenance overhead you get from having non-ORM database code (if you're doing the SQL approach) in the application would be there anyway, you'd just get a very slow application instead of a hard error, and the latter is easier to troubleshoot (and often fix), while with the former you need to start pulling up profiling tools.

>Things they usually aren't very good at are when you start trying to do things that require a lot of optimization

I find this ends up being, like, 1 or 2% of queries. It's also very hard if not impossible to guess which queries will end up in that group.

You're better off building it with the ORM first and breaking out SQL later when you are trying to performance optimize.

There is also a small % of queries which use some feature of your database engine which the ORM won't support.

I find this ends up being 90% of SELECT queries. Usually when selecting data you want to retrieve a bunch of related objects too. Often with complex criteria for which objects to pick. And doubly so for "list" type endpoints where you're selecting many records.

I tend to use the ORM function CUD operations, and just write raw SQL for SELECTs unless they're super-simple.

> It's also very hard if not impossible to guess which queries will end up in that group. > You're better off building it with the ORM first and breaking out SQL later when you are trying to performance optimize.

I disagree on this. It's almost no extra work to just write these optimised in the first place (if you're not trying to squash everything into an ORM workflow). So it makes sense just to write them all optimised. Ditto for doing batch inserts and updates rather than looped inserts/updates (although you can usually use the ORM for this).

> It's almost no extra work to just write these optimised in the first place

Not everyone is capable of quickly optimizing SQL, and I don't think it's an absolutely necessary skill to build a decent application. Junior devs can pick up on this skill over time and as long as they can manage to avoid any obvious footguns, using the ORM is fine most of the time.

Writing queries that are optimized in the first place just means now you have to maintain a bunch of SQL and you have to rely on anyone modifying that SQL later understanding those optimizations. Sometimes it's necessary, but if it's not, I think it's much nicer to stay in ORM-land even if the query might not be optimal.

> Not everyone is capable of quickly optimizing SQL

I mean that's true, but equally not everyone is capable of using an ORM. I don't think SQL is inherently any harder to learn.

At my last job, I had juniors who had never used SQL at all productive in SQL within a couple of weeks, and using "complex" SQL like JSON aggregation and windows function with a few months. They were a little intimidated by it when they started, but didn't find it too hard to learn in the end.

Honestly, I think I could get someone with no raw SQL experience writing code that’s at least 50% faster within a few hours. There are so many footguns that ORMs completely ignore, and never warn you about.

Using a case-sensitive filter (default for Django) in a DB with case-sensitive collation (default in Postgres)? Django will helpfully cast the tuple and your query to UPPER to match it for you, and the former wrecks indexing.

Checking if a string ends with something else? Goodbye, index.

I _think_ the latter can be worked around in PG with a GIN index, but I’m not positive (I work with MySQL much more). And in any case, you’d have to know to create that, and I imagine most devs won’t.

Fixing seemingly tiny things like that have a massive impact on large table query speed.

With your example of optimization, I think anyone who is able to make this optimization could certainly also implement it on top of an ORM.

>I find this ends up being 90% of SELECT queries. Usually when selecting data you want to retrieve a bunch of related objects too. Often with complex criteria for which objects to pick.

What is it that you do here that can't be handled by, say, django's workhorses - filter and select_related?

If it's impossible to write 90% of your queries in an ORM my suspicion would be that you're either not using the ORM correctly or you're using a crappy ORM.

I'm unfamiliar with Django's ORM specifically. But the problem wasn't that it couldn't be done with the ORM, but that the ORM code quickly became unreadable. Things like:

- Complex joins

- Complex WHERE clauses with mixes of AND and OR (with parentheses)

- JSON aggregation

- Window functions

tend to require quite heavyweight syntax in ORMs (e.g. nested lambda functions). Whereas the corresponding SQL tends to introduce much less noise.

It's basically just another case of a dedicated language being nicer to use than a DSL embedded into a general purpose language. Normally it's not worth creating a whole language just for nicer syntax, but in the case of SQL the language already exists! So why not use it.

>But the problem wasn't that it couldn't be done with the ORM, but that the ORM code quickly became unreadable.

This is the problem with not using an ORM. If you cut it out and move everything to parameterized SQL queries the SLOC explodes which massively inhibits readability as well as introducing bugs.

If your issue with ORMs is just that you're familiar with SQL and you don't like how ORMs look then I think the issue is just about becoming more familiar with a decent ORM.

> If you cut it out and move everything to parameterized SQL queries the SLOC explodes

My experience has been the opposite: that raw SQL queries end up much shorter (and consequently more readable) than the equivalent ORM code. The exception to that is INSERT/UPDATE queries, where I do tend to use some kind of ORM/query builder. I have used both, and I prefer raw SQL for anything beyond very simple queries.

I have never seen an ORM in my life that didn't reduce the total amount of code written. Not even the Java monstrosities increased the SLOC.

Perhaps it depends what you're doing?

IMO:

    .where('column_a', '=', 'value1')
    .and(q => q.isNull('column_b').orWhere('column_b', '=', 'value2')))

is a lot less readable than:

    WHERE
       column_a = 'value1'
       AND (column_b IS NULL OR column_b = 'value2')

In Django that would be .filter(column_a='value1', Q(column_b__isnull=True)|Q(column_b='value2'))

And obviously you can use whatever indentations you like.

Ah, that's quite a bit nicer. You can't do that in JavaScript on two counts:

- No keyword arguments

- No operator overloading (so you can't override | to get the nice "or" syntax)

I actually messed it up a little because I'm not sure you can mix positional and kwargs in filter, and you definitely can't use kwargs first. Still, the idea is there.

In JS, theoretically you could design an API like

.where({column_a: 1}, Q(column_b__isnull=True).or({column_b: 2}))

Which really isn't bad IMO

It's been a bit since I've used Django, but I believe you can just swap the order so the kwarg comes last.

    filter(     Q(column_b__isnull=True)|Q(column_b='value2'),
      column_a='value1',
    )

Or just turn it into another Q

    .filter(Q(column_a='value1'), Q(column_b__isnull=True)|Q(column_b='value2'))

The docs show both of those as working examples, but Q supports using & for "and", and can be combined in any way - you could also do this:

  .filter(Q(column_a='value1') & (Q(column_b__isnull=True) | Q(column_b='value2')))

One of these my IDE can typecheck and apply code hightlighting, the other is just a blob of text.

On my case that would be SQL, as I use nice SQL aware IDEs for Oracle and SQL Server.

Separate chain for a different Django example, it only natively supports joins on explicit foreign keys, but because it has that extra information in the model the syntax for using it is extremely compressed. Let's say you have a "Dog" table with a foreign key to "Owner", and "Owner" has a foreign key to "City". Getting all the dogs that live in New York would be:

   Dog.objects.filter(owner__city__name = 'New York')

The double-underscore follows the foreign keys until the last one, which is a field on the last model.

It's a ton of extra work. It's like 10x slower than using the Django ORM. Let's say it takes you ten seconds to write that query. I wrote it in one second with the ORM and my IDE.

That adds up, with almost no downside most of the time.

Nah, ORMs just encourage a lot of bad behavior, and come with edge cases and code bloat. You're better off using an API generator such as postgrest/hasura for the simple cases, and hand crafted queries for anything more complex than basic crud.

Or you use something much simpler and more light weight, like query builders or a tool that generates code from sql queries.

This approach is more bottom up. You end up with uni directional data flow, better separation of concerns, data coupling instead of object dependencies and better performance right out of the bat.

The cost? In my experience just some basic familiarity with SQL.

The problem is that what you typically get is one group of people who have no idea how to do anything outside of the ORM and play off the problems because hey, its only a problem query or two that use up 100% of the system resources of the databases and bring them crashing down, but might as well throw more system resources at it because we've spent no time understanding queries the last N years of building.

I realized too late that objects, as of now, are not capable of synthetizing a new class/type based on joins.

It's strange that this point doesn't get more attention.

ORMs by their nature tend to be built around a 1:1 mapping between fields on some object type and columns in some table. Bulk queries get you multiple objects corresponding to multiple rows. Relationships get you multiple objects with some of the fields being references to the other objects. Obviously I'm simplifying here and there have also been some attempts to do things in other ways but this is basically how most of the popular ORMs work today.

However in reality a lot of useful queries return a list of flat data structures or even just a single flat data structure with some subset of the columns of all of the relevant tables and maybe a few extra columns that are calculated on demand and not stored directly in any database table. If that's the data I've read then what I really want is something like a properly-typed dataclass with exactly those fields/columns and nothing else that might add confusion or ambiguity.

Unfortunately that doesn't really fit the classic ORM and OO model. Instead we often have to work with multiple objects with some form of nesting to follow the relationships, ambiguity about which fields have actually been read from the database and can safely be accessed, possibly some inaccuracy with the types such as nullable fields that have just been read from not null columns in the database, and a lottery to see what happens if we try to access fields on those objects that might not have been read by any previous database query anywhere in the system at any point since that particular ORM-backed object was created.

I find it's one of those things where the popular approach - using an ORM in this case - works for relatively simple needs and in practice a lot of work does only have relatively simple needs so that's OK. But when I start doing more complicated things it can become a pain to work with because the whole model fundamentally doesn't fit what I'm actually doing.

Something like this is implemented in russian 1C system. They use an extended query language (bilingual as the whole system) and the query executor returns a special dataset object with all values wrapped into regular business-logic classes. So when you “select …, agent, … from … join …”, you can access record.agent.manager.phone in your code later. Everyone knows the difference between selecting it in query and in code. All primitive types get wrapped too: dates to dates, bools to bools. It has no static typing, but the query result fields are all of “platform” types, not raw values like ids or json/int dates.

Don’t get me wrong, 1C products are regular enterprise crap on top of shitty language that stuck in the last century. The latest attempt to refit it as-is to www was a paradigmal disaster. But the platform (the runtime) itself may teach Django a volume or two about query integration. They do it since the '90s and 1C developers who traditionally weren’t even considered developers had no issues with programming these systems without any deep stack knowledge. There’s no stack basically, it’s all homogeneous once you learn the fundamentals.

Edit: yes, I find it very strange too. There’s no popular/generic and simple open source platform which could bind it all together into a nice runtime. The whole ORM vs SQL and pitfalls feels so strange, as it’s not something hard in my book.

Django lets you define an abstract model for the resulting set of columns, then you can use raw SQL on that model to get something that looks like a normal model to the rest of the code. As long as the raw query has the right number of columns, and of the right data type, Django doesn't care how it's populated. Then you can just stick the query behind a classmethod on the abstract model so you don't have to worry about the columns not matching up wherever it's used.

how is it called in the docs ?

If I understand GP correctly, it's 'performing raw SQL queries', or specifically here, 'mapping query fields to model fields': https://docs.djangoproject.com/en/4.2/topics/db/sql/#mapping...

Basically you call `.raw("...")` from some model's queryset, but there's no requirement you actually query that model's table at all.

    class SomeModel:
        name = models.CharField()

    class OtherModel:
        foobar = models.CharField()

    SomeModel.objects.raw("select foobar as name from thisapp_othermodel")

will yield `SomeModel`s with `name`s that are actual `OtherModel` `foobar` values.

Yes, but also it's been a long time since I've had any reason to do this, and had gotten "managed = False" mixed up with abstract classes. Abstract classes won't let you do this, but you probably want "managed = False" to prevent migrations from doing stuff in the database, if it's going to be a reporting-only query that doesn't have a backing table.

Also you need to return an "id" column since Django needs a primary key.

On the flipside, you can put that query in the database as a VIEW and point the model at it, also with "managed = False".

Oh yes I forgot about that, that can be annoying. But of course, when it's annoying because it doesn't matter, it doesn't matter and you can pass anything back `as id`.

I occasionally do this with views managed by https://github.com/xelixdev/django-pgviews-redux/.

very nice

The most stupid issues with "active record" type ORMs is the implicit queries on member access, especially in collections - leading to the N queries problem. But in SQLAlchemy one can actually turn that off - that is, make it throw an exception when undeclared table dependencies are attempted to be accessed. This restores sanity. And one gets to keep goodies you mention, plus Alembic migrations (mentioned by another). Also one can write direct SQL too with SQLAlchemy, or use the "core" layer to keep a DSL but avoid ORM.

> I am curious as to what a larger codebase with "just SQL queries all over" ends up looking like. I have to imagine they all end up with some (granted, specialized) query builder pattern. But I think my bias is influenced by always working on software where there are just so many columns per table that it would be way too much busywork to not use something.

You end up writing a query per usecase, rather than writing generic queries that can be stitched together however in the business logic

So what happens is that you have the one query that runs for a specific page and fetches the data and the relevant fields? I could definitely see that working for many projects, at least while your objects don't have too many tiny little details to pull out of thhe DB

I think it ends up being hybrid, like seen in CQRS and especially with DDD. Toss in "Vertical Slice Architecture" as well. What abstractions you want can decided on a per command or query basis and it feels natural.

The first agency I worked at did this on their Java projects. They should have just used a fully baked ORM. Basically, they ended up creating a massive query layer in the program which contained all the different queries organized into different interfaces. To edit a simple API endpoint you would have to open like 5 different files at a minimum. And because queries were usually tailored to logic in a specific controller, they were not typically reusable. It was always a relief to go back to Django after dealing with that.

Have this right now. Fortunately no custom query builder. It's enough of a hell as is. Cleanup will take years.

What problems are you finding with it?

Injection potential through the roof, copypasta galore, refactoring the same join pattern in a hundred different queries gets old rather fast… the usual suspects.

Oh god, good luck

That is what stored procedures are for.

> issues with Django's ORM due to the query laziness making performance tricky to reason about

It's infuriating that this is still not a thing you can disable (https://code.djangoproject.com/ticket/30874). Pretty much my only gripe with the Django ORM which I'm a huge fan of (and I also write lots of SQL).

There's a library that solves this for you, thankfully https://github.com/charettes/django-seal.

I read this title and immediately thought "...but why wouldn't you just use Django?"

Having written the sort of SQL-inline code the author talks about, then refactored the whole thing to use Django: Django's ORM solves waaaay more problems than it creates in this regard.

There are plenty of advantages of using a dataclass, being the most obvious the fact that behaves like a pure data object (aka it doesn't have underlying associated resources). Serialization/deserialization of data is dead simple, and a dataclass is a construct you can use as a data object when building 3-tier applications. Having pure data objects also gives way more flexibility when implementing cache strategies.

While this separation isn't common in the Django ecosystem, it is very common in enterprise application design (regardless of usage of an ORM). On complex applications, Django models are often a leaky abstraction (not only because the mentioned resource connection problem, but also issues like for relations they require the inclusion of the target model, it cannot be lazy-loaded; a good example is a nullable foreign key to an optional module that may or may not be loaded), and they actually behave like a variation of the ActiveRecord pattern, that mixes two different scopes - data and operation on data. In many cases this is ok, but in many others this is a problem.

I personally use a repository pattern, coupled with a query builder and something vaguely similar to dataclasses (its a bit more complex in the sense that the data object attribute name can be different from the database field name). It is basically an object mapper with a non-related repository class.

Yeah I can understand wanting to split out the ORM model from a separate class that holds data. I just think absolving oneself of an ORM or query builder entirely for a DB schema that doesn't (glibly) fit on a postcard feels like a good way to generate a lot of busy work.

I somewhat disagree about your point on caching. If you're working with models (that, namely, are 1:1 with DB rows) stale object problems are a reality no matter what, and having the ID be put into a pure data object generates the same issues. But these are things that are not very interesting to discuss outside of specific contexts.

I am a bit of a functional programming nerd, but I've just found that for Python stuff in particular, swimming upstream is its own bug generator relative to writing concise stuff in a very imperative fashion. Using the fat models directly is a part of that calculus for me, but YMMV and every team has different strengths.

> I just think absolving oneself of an ORM or query builder entirely for a DB schema that doesn't (glibly) fit on a postcard feels like a good way to generate a lot of busy work

True, that's why I built mine as a library I can reuse in my projects :) I'm still eating my own dogfood, but doing it with a framework approach.

> If you're working with models (that, namely, are 1:1 with DB rows) stale object problems

One of my common patterns is to implement cache at the service layer, not the data layer - and all data operations are performed via services. This allows caching of actual business-domain computed values, not necessarily just db rows (in fact, more often than not, caching just db rows is just a waste of memory with little to no advantage). As a quick example, imagine a purchase order with a header, a list of products and a state associated with each line - it is trivial to cache the whole purchase order info, including runtime calculations such as lead time per product, and invalidate the cache at each update operation on the different tables that may represent this purchase order. Services would have methods manipulating "purchase order" (and keeping cache state) and not ad-hoc code messing with OrderHeaderModel, OrderDetailModel, OrderProductStatusModel, etc.

> I am curious as to what a larger codebase with "just SQL queries all over" ends up looking like. I have to imagine they all end up with some (granted, specialized) query builder pattern.

One successful pattern I’ve seen treats the database as its own service layer. Service code does not send arbitrary SQL to the database—instead, all of the SQL queries are set as stored procedures in the DB. People sometimes freak out when I say this, so I should clarify that the stored procedures, table schemas and other things of that nature were version controlled and deployed with some minimal build tooling we’d developed in house.

I really liked this pattern. I think anything that you need to talk to across a network should be treated as a service in itself, with a well defined interface. This simplifies testing and monitoring as well. The big risk with an ORM, architecturally, is that you end up treating your database as a sidekick to your service code, or even a dumb data store—some shameful implementation detail your service keeps locked in the basement—when they’re capable of much more than that.

The only problem I have with using the django ORM is that it relies on the django project structure. While there are ways to use the ORM independently, they are full of hacks and trade-offs.

Granted, this problem goes away if you are building a web app or a REST API, but if I just want an ORM for a command line application, I am using django's management command functionality which is OK, but it doesn't really scale easily.

Yuck indeed. The way it dictates the order of imports, forcing you to import settings before any models can be imported, is reason enough not to use it. This problem spreads to any of your other files, leaving you in the end with everything depending on being launched in a full Django context. Shame, given it’s otherwise very user friendly.

Add refactoring, migrations and testing to all the reasons you mentioned, and it quickly becomes an adapted case of Greenspun's Tenth Rule for ORMs.

Migration tooling and SQL testing predate ORMs, it is a matter of actually caring about their existence.