SQLAlchemy and JSON values

In every project, there comes a moment when I need a column in my Postgres database with a JSON type.

Why would I mix structured SQL with the chaos of JSON? Maybe I don’t want to index the results, maybe I have a lot of inner documents, or maybe it’s coming from an external server, meaning I can’t guarantee its structure.

Whatever the reason, I just need to store it. So let’s slap it into my SQLAlchemy entity and move on.

First Solution

Good news! SQLAlchemy has a built-in JSON type. That was my first discovery. It’s easy to implement, no surprises.

I create a row in the table, insert my JSON data, and—boom—it works. That’s it, move on to the next problem, right? Well… not so fast.

amount = Column(JSONB)

The Problem

Let’s say I need to update a single value inside my JSON document. You’d think SQLAlchemy would notice the change and trigger an update. Nope! SQLAlchemy sees the column itself as unchanged because, technically, I didn’t modify the entire column, just a small part of the JSON document. And what does that mean? No write, no update, no joy.

The quick fix? Just rewrite the whole JSON object into the column every time I change something. But let’s be real, I’ll remember to do that next week, but three months from now? Not a chance. And that’s how bugs are born.

Real Solution

What I actually need to do is configure the column as mutable. This way, SQLAlchemy will keep an eye on every tiny modification inside the JSON document. Every change triggers an update in the database.

Now, I can treat the column as a true JSON document without constantly worrying about manually forcing updates.

amount = Column(MutableDict.as_mutable(JSONB), nullable=False, default={})

Now, SQLAlchemy properly tracks changes inside my JSON field, and my updates work as expected. Finally!

entity.amount['key'] = 'value'

Conclusion

Using JSON in Python is practically second nature. Mapping it to a database is just the next logical step. So, let’s make SQLAlchemy speak JSON natively, because the last thing I need is another debugging session where I wonder why my data isn’t saving.