JSON field type in SQLAlchemy (Flask / Python)

Sep 8th, 2016 in Python by Michael Cho

← All articles

Want to store JSON-serialized data in your database? But sick of calling `json.loads()` and `json.dumps()` when dealing with this data in your Flask app? Read on!

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 '{}'
            return json.dumps(value)

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


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'}