Using PostgreSQL JSONField in SQLite
I think we can all agree that PostgreSQL is an efficient, solid, production-grade RDBMS. As of PostgreSQL 9.4, JSON support has been improved by having extractor/constructor methods and storage as JSONB. It is almost having both RDBMS and NoSQL in one DB system. SQLite, on the other hand, is a simple RDBMS which saves data either in file or memory. It is almost always used as a throw away DB for running local development server or tests. Unlike PostgreSQL, JSON is not natively supported in SQLite.
I am so used to running my integration tests in SQLite via memory. I don’t have to create “setup” and “teardown” functions because the test data are gone after the tests run. I hit this block when I started using Django PostgreSQL specific JSONField. What if I save JSON as “text” in SQLite and “json.loads” the value when I fetch?
Django ORM, or any other ORM, provides a way to create a custom Model field. Basic functions to implement are “to_python()” which converts DB value to Python object and “get_prep_value()” which converts Python object to DB value. See implementation in Peewee ORM.
Below is our implementation for Django ORM.
We can use the script by saving as "<app_name>/models/fields.py”. In here, we specify same class name for our custom SQLite Model fields and Django PostgreSQL fields. It’s important that we define only one of these depending on which DB Engine is configured. Otherwise, the migration file will import the exact class for that DB and make the migrations work only for that specific DB Engine.
Also note that filtering by JSON key is not supported in this implementation (ie. .filter(<json_column>__<json_key>=<value>)). But may be possible by registering a custom lookup.
This implementation of a custom JSONField for SQLite is very raw. It works on basic purposes. For advance requirements, always see the documentation of your favorite ORM. RTFM! There should be more things you can tweak.
Have fun hacking!