JSON field type in SQLAlchemy (Flask / Python)

In our Flask app we have several models where instead of storing certain attributes in separate database fields, we prefer to keep these as JSON-serialised objects in a single database field. 

Since a JSON data type is only available in some database versions, we had been dealing with this by using the json package to convert json objects into / from strings to store as a text database field. For example:

import json

# eg storing new values
my_attributes = json.dumps({'attr1': 123, 'attr2': 'something'})
my_object.my_attributes = my_attributes

# eg reading values from database
my_attributes = json.loads(my_object.my_attributes)

We finally got sick of doing this :)

Our solution was to introduce a type decorator, which acts as a pre-processor / post-processor when reading / writing from the database for selected field types. Here is what it looks like:

# Create a custom JsonEncodedDict class in a file accessed by your models
import json
from flask.ext.sqlalchemy import SQLAlchemy
from sqlalchemy.ext import mutable
from flask import Flask

app = Flask(__name__, template_folder="../templates")
db = SQLAlchemy(app, session_options={'autocommit': True})

class JsonEncodedDict(db.TypeDecorator):
    """Enables JSON storage by encoding and decoding on the fly."""
    impl = db.Text

    def process_bind_param(self, value, dialect):
        if value is None:
            return '{}'
        else:
            return json.dumps(value)

    def process_result_value(self, value, dialect):
        if value is None:
            return {}
        else:
            return json.loads(value)


mutable.MutableDict.associate_with(JsonEncodedDict)

Then in your models, use the JsonEncodedDict column type in your model declarations.

class MyObject(db.Model):
    __tablename__ = 'my_objects'
    object_id = db.Column(db.Integer, primary_key=True)
    object_args = db.Column(JsonEncodedDict)

And finally, the JsonEncodedDict type will be used automatically. You can treat the field as a native database JSON field type.

my_obj = MyObject(object_args={'attr1': 123, 'attr2': 'something'})

another_obj = MyObject.query.first()
print another_obj.object_args  # eg {'attr1': 123, 'attr2': 'something'}