==================================
Enabling JSON1 extension on SQLite
==================================

To use **django.db.models.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 check if the extension is enabled on your installation, you can
do a query with one of the functions included in the extension, e.g. ``JSON()``. For example:

.. code-block:: python

    >>> import sqlite3
    >>> conn = sqlite3.connect(':memory:')
    >>> cursor = conn.cursor()
    >>> cursor.execute('SELECT JSON(\'{"a": "b"}\')')

If the query doesn't throw any errors, then the JSON1 extension is already enabled. Otherwise,
follow the instructions below according to your operating system to set it up correctly.

.. __: https://www.sqlite.org/json1.html
.. __: https://docs.python.org/3/library/sqlite3.html#module-sqlite3

Linux
=====

On most major Linux distributions, the JSON1 extension is included in their SQLite and/or Python
packages and enabled by default. If that's not the case on your installation, then do the following:

- Download the `SQLite amalgamation`_, with or without the configuration script.
- Extract the source code archive and enter the directory of the result.
- Compile the source code using the ``-DSQLITE_ENABLE_JSON1`` flag to enable the JSON1
  extension. For example::

    gcc -DSQLITE_ENABLE_JSON1 -c -fPIC sqlite3.c

  To enable other extensions, see the `compilation instructions`__.
- Create a shared library. For example::

    gcc -shared -o libsqlite3.so -fPIC sqlite3.o -ldl -lpthread

- Place the resulting file (``libsqlite3.so``) in a desired directory, e.g. ``/usr/lib/sqlite3/``.
- Set the ``LD_PRELOAD`` environment variable to use your compiled SQLite every time you run
  Django. For example::

    export LD_PRELOAD=/usr/lib/sqlite3/libsqlite3.so

- Now, the JSON1 extension should be ready to be used in Python and Django.

.. _SQLite amalgamation: https://www.sqlite.org/download.html
.. __: https://www.sqlite.org/howtocompile.html

macOS
=====

`As of Python 3.7`__, the official Python installer on macOS already includes the JSON1
extension by default. If you're using an earlier version of Python or unofficial installers,
you can follow the instructions for Linux above, but instead of setting the ``LD_PRELOAD``
environment variable, use ``DYLD_LIBRARY_PATH``. For example::

    export DYLD_LIBRARY_PATH=/usr/lib/sqlite3

.. __: https://github.com/python/cpython/commit/9625bf520e08828e36bc3b1d043af679eb5f993d

Windows
=======

`As of Python 3.9`__, the official Python installer on Windows already includes the JSON1
extension by default. If you're using an earlier version of Python or unofficial installers,
you can do the following:

.. __: https://github.com/python/cpython/commit/58d6f2ee3aeb699156d4784acccd2910d27982e7

- Download the `precompiled DLL`__ that matches your Python installation (32-bit or 64-bit).
- Locate your Python installation. By default, it should be in
  ``%localappdata%\Programs\Python\PythonXX``, where ``XX`` is the Python version. For
  example, it's located in ``C:\Users\<username>\AppData\Local\Programs\Python\Python37``.
  If you added Python installation directory to your ``PATH`` environment variable, you can run the
  command ``where python`` on a command prompt to locate it.
- Enter the ``DLLs`` directory in your Python installation.
- Rename (or delete) ``sqlite3.dll`` inside the ``DLLs`` directory.
- Extract ``sqlite3.dll`` from the downloaded DLL archive and put it in the ``DLLs`` directory.
- Now, the JSON1 extension should be ready to be used in Python and Django.

.. __: https://www.sqlite.org/download.html

Other workarounds
=================

Load the JSON1 extension dynamically
------------------------------------

The following workaround works by compiling the JSON1 extension as a loadable extension
and loading it when the database connection is created by utilizing Django's |connection_created|__
signal. It hasn't been tested, but it should work on Linux, macOS, and Windows.

.. |connection_created| replace:: ``connection_created``
.. __: https://docs.djangoproject.com/en/3.1/ref/signals/#connection-created

- Download the `SQLite amalgamation`_, with or without the configuration script.
- Extract the source code archive and enter the directory of the result.
- Follow the instructions to `compile a loadable extension`__ according to your operating system,
  but replace the ``YourCode.c`` placeholder to point to ``ext/misc/json1.c``.

  For example, on Linux it would be::

    gcc -g -fPIC -shared ext/misc/json1.c -o json1.so

  On macOS, it would be::

    gcc -g -fPIC -dynamiclib ext/misc/json1.c -o json1.dylib

  On Windows with MSVC, it would be::

    cl ext/misc/json1.c -link -dll -out:json1.dll

  On Windows with MinGW, it would be::

    gcc -g -shared ext/misc/json1.c -o json1.dll

.. __: https://www.sqlite.org/loadext.html#compiling_a_loadable_extension

- Place the compiled JSON1 extension somewhere desirable.
- Create a signal handler for the ``connection_created`` signal. For example:

  .. code-block:: python

    def load_json1(connection, **kwargs):
        if connection.vendor != 'sqlite':
            return
        connection.connection.enable_load_extension(True)
        connection.connection.load_extension('./json1')

  You should replace ``'./json1'`` if your compiled extension is stored under a different directory.

- Connect the receiver function in one of your apps' ``ready()`` function.

  .. code-block:: python

    class MyAppConfig(AppConfig):
        # ...

        def ready(self):
            connection_created.connect(load_json1)

  You can also connect the function using the ``@receiver`` decorator. For more information,
  read the docs on how to `connect receiver functions`__.

.. __: https://docs.djangoproject.com/en/3.1/topics/signals/#connecting-receiver-functions
Last modified 4 years ago Last modified on Aug 14, 2020, 2:32:54 AM
Note: See TracWiki for help on using the wiki.
Back to Top