The Pain of Async ORMs

Recently at work, I've been using Object-Relational Mappers (ORMs) to interact with our databases. ORMs offer a much nicer way of interacting with your underlying database. Instead of having to execute arbitrary SQL strings, you get to work with well defined models and some level of type safety. Compare the following two snippets:

result = session.execute(select(Song.song_id))

result = session.execute("SELECT song_id from song;")

It's not hard to tell why programmers prefer the safety and flexibility of ORMs, not to mention the QOL that comes with having autocompletion in a text editor. I have nothing against the idea of ORMs, and if anything they have probably saved countless hours in my development that would have otherwise been spent tinkering with SQL snippets, writing tests, and being confused why I suddenly had a regression after a teammate removed an existing column. However, I have found in practice that there are some ugly sides to using an ORM.

Relationships

Before I dive into what I find painful, I'll give a slight background on relationships in ORMs. You might want to implicitly tie two tables together in a database without having an explicit third table to link identifiers in the two together. For example, suppose you have Song and Artist tables. It would make sense for these to have one-to-one and one-to-many relationships respectively. You can quite easily express this in an ORM:

class Artist(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str

    songs: list["Song"] = Relationship(back_populates="artist")

class Song(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    title: str
    artist_id: int | None = Field(default=None, foreign_key="artist.id")

    artist: Artist | None = Relationship(back_populates="songs")

This relationship could then be leveraged to write code like this:

song = session.execute(select(Song).where(Song.song_id == song_id))
print(f"Artist: {song.artist.name}")

That's pretty cool - the ORM manages the relationships for us automatically. In practice, the ORM creates implicit keys on each of the tables, and whenever we do any operations on the database, it updates these keys so that it can resolve relationships at runtime for the user by linking the two sides of the relationship together.

Loading

You can imagine for complex data models, this implicit linkage of relationships gets quite interesting. When we load a Song, do we always load all of its relationships recursively until the object is fully populated? Totally not - that's doing way more work than we have to, and we definitely should not have the default behavior be to load all relationships on an object. In the previous code snippet, we only load the top level attributes of the song from the ORM, and only when we try to access the artist field does the ORM go and execute another query to make the link and fetch the associated artist. In a synchronous world this works great - however things really start to break down when we enter the land of async.

Async

Async is good. Even with the GIL present in Python, single threaded models of async are still incredibly powerful, letting you kick off concurrent streams of work. It should be no surprise that ORMs in Python also support async execution. This is pretty trivial in most places, but gets interesting for handling relationships. If we take the sync model from before, it doesn't quite work as well in async. Why?

1. When we try to fetch the artist property on a Song, we realize it hasn't been loaded yet.
2. Now we need to make another query to load the relationship onto the object.
3. However in Python you can't await a property on an object.
4. Therefore the ORM will throw an error that the relationship was not loaded.

The solution to this is to either manually specify everything you want to load during (1) the initial execution or (2) before each usage. One of the reasons ORMs are even used in the first place is to treat DB rows as regular Python objects, and I've found in practice most developers prefer loading everything on the initial query. This would look something like this:

song = await session.execute(
    select(Song)
    .where(Song.song_id == song_id)
    .selectinload(Song.artist)
)
print(f"Artist: {song.artist.name}")

Of course this pattern quickly gets dirty when have lots of relationships, so instead of manually writing every new one out, you can instead say to load everything:

song = await session.execute(
    select(Song)
    .where(Song.song_id == song_id)
    .selectinload("*")
)
print(f"Artist: {song.artist.name}")

It's common to see a mix of both of these in development.

Problem

The core of the problem is that once you have enough relationships linking your tables together in an async world, you essentially have to permanently live in a state of paranoia. Whenever you work with an async ORM object, you have to be careful that any functions you pass the object into are aware of what attributes are loaded onto it, lest you run into a runtime error of not having loaded the right attributes. As a developer, you have to either build great guardrails or accept that you are going to run into bugs sooner or later. And the error is very scary - there is no way to tell from the stack trace what went wrong. The only thing you have to work with is where the initial erroring call was made, and what the attribute was that you didn't load. There's no way to know whether your code should have loaded it prior to the call, whether you had a regression and stopped loading it, or if there was a new relationship that someone added which implicitly adds an extra function call to any ORM operations.

There's also the fact that you don't have to load relationships. You can choose not to load them - after all they aren't actually real links in the table, they're just a layer of convenience that the ORM manages for us. This means sometimes a client can get back two Song object that have completely different shapes depending on what relationships were loaded. This makes working with complex relationships in large codebases very difficult, and often times chasing down resulting bugs makes you wonder whether it's worth using an ORM in the first place. As of now, my takeaways is that the integration ORMs offer with programming languages far outweights any inconveniences. However, it remains a large pain point my team and I have to deal with constantly.