In a post Don’t let dicts spoil your code I wrote that it’s better to avoid raw data structures such as dicts and lists. Instead, I suggest converting them as soon as possible to objects representing your domain.

In a few places of my code, I found that raw dicts appear as attributes of SQLAlchemy models JSON fields. It feels dirty: you have a well-defined model with a field storing unstructured data.

Fortunately, you can define your own column type and convert seamlessly between SQLAlchemy and JSON. It requires defining your column type and changing how the SQLAlchemy engine is initialized. Not difficult, but looks a bit hacky.

• Subclass TypeDecorator to define a Pydantic-specific column type. The “impl” should be sqlalchemy.types.JSON, and the decorator has to define two methods: converting from pydantic to a dict (or any other JSON-encodable-type) and vice versa.
• Define custom JSON serializer, and pass it as an argument json_serialized for the engine.

Here’s the entire snippet.

See more:

• My GitHub gist with an example
• SQLAlchemy Custom Types tutorial
• sqlalchemy.create_engine() reference
• Stackoverflow: Writing a pydantic object into a sqlalchemy json column