Using JsonField(in models) with Django and SQLite

Photo by Caspar Camille Rubin on Unsplash

SQLite is an light-weight, Relational and Embedded database(Server-less). It is widely used in stand-alone apps and testing. Django by default creates a SQLite database for your project. But Django(3.0.8) does not seem to have any built-in library to use list as a model fieldtype when used with SQLite. To overcome this limitation we can use JsonField.

But there is no JsonField support too. So we use a library called jsonfield. The jsonfield is a reusable model field that allows you to store validated JSON, automatically handling serialization to and from the database.

Below is the guide to use the jsonfield library efficiently.

1. Installation

Install the jsonfield package using pip in your project’s environment.

pip install jsonfield

2. Importing and Usage

Create the model you want to create with the jsonfield using the below snippet in your project’s models.py file.

3. Storing

The object to the model can be created using django’s create method. The json field can be written as in below.

$#Storing the model in db
$MyModel.objects.create(name = 'First_model', desc = 'First', 
json = {'id': 1})

There is no list field or array field in django. Instead we can use this json field to store array in the models field.

$#Storing a list as Json
$MyModel.objects.create(name = 'Second_model', json = {'id': {1,2}})

4. Querying

JSONField is not intended to provide extended querying capabilities. That said, you may perform the same basic lookups provided by regular text fields.

$#filter using exact field lookup
$var_1 = MyModel.objects.filter(json__exact = {'id': 1})
$var_2 = MyModel.objects.get(json__exact = {'id': {1,2}})

Update

JSONField is supported on MariaDB 10.2.7+, MySQL 5.7.8+, Oracle, PostgreSQL, and SQLite 3.9.0+ by default. But still To use JSONField on SQLite, you need to enable the JSON1 extension on Python’s sqlite3 library.

If the extension is not enabled on your installation, a system error (fields.E180) will be raised.

To enable the JSON1 extension you can follow the instruction on the wiki page.

Note: The JSON1 extension can be used only in Django 3.1

Go to https://github.com/rpkilby/jsonfield to see the github repository of the creator(Ryan P Kilby @rpkilby) . Also read about Handling null values to avoid exceptions.

— Manoj Kumar

Thanks for reading this article! Leave a comment below if you have any questions. Happy coding