You can use Pydantic in SQLAlchemy fields
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_serializedfor the engine.
Here’s the entire snippet.