How to use CockroachDB with your Django application on Ubuntu

Last edited on February 10, 2020

0 minute read

    Django is a high-level flexible framework for building Python applications quickly. Applications run on Django store data, by default, into a SQLite database file, but lots of Django users find themselves needing to switch to a more performant database in production, one with better availability or scalability.

    This is a short tutorial on using Django with CockroachDB as your database. At the time of writing, django-cockroachdb library is available in two versions, (2 and 3). This post focuses on version 2 specifically. This tutorial is based on the Digital Ocean tutorial "How to Use PostgreSQL with your Django Application on Ubuntu 14.04." There's one important difference between this and the PostgreSQL tutorial (which I'll highlight again, below): this tutorial uses Python3. For everything else, we'll follow the Digital Ocean PostgreSQL/Django tutorial as is.

    Since we’re going to need role-based access management (RBAC) features of CockroachDB, we'll require an enterprise license. Feel free to request a trial or try this tutorial in a cockroach demo environment. Using the cockroach demo command will enable enterprise features for 60 minutes---more than enough time to complete this tutorial.

    Install the Components from the Ubuntu RepositoriesCopy Icon

    sudo apt-get update sudo apt-get install python3-pip python3-dev libpq-dev

    Create a Database and Database UserCopy Icon

    My CockroachDB instance is running on in insecure mode on port 26257, but if you're playing along at home, feel free to run it locally. These details will be necessary when we’re going to configure Django. You can validate connectivity by accessing CRDB SQL shell with the following:

    ./cockroach sql --insecure --host= --port=26257

    First things first, enable enterprise features by passing your trial license and organization to the following properties:

    SET CLUSTER SETTING cluster.organization = 'Acme Company'; SET CLUSTER SETTING enterprise.license = 'xxxxxxxxxxxx';

    CREATE DATABASE myproject;

    Set the desired database as default.

    SET DATABASE = myproject;

    Since we’re using CRDB in insecure mode, WITH PASSWORD command will be ignored.

    CREATE USER myprojectuser;

    Create a role for our Django app.

    CREATE ROLE django;

    Verify that the role has been created.

    > SHOW roles; role_name +-----------+ admin django (2 rows)

    Grant privileges to the Django role you created:


    Next, verify the grants exist on the database.

    > SHOW GRANTS ON DATABASE myproject; database_name | schema_name | grantee | privilege_type +---------------+--------------------+---------+----------------+ myproject | crdb_internal | admin | ALL myproject | crdb_internal | django | CREATE myproject | crdb_internal | django | SELECT myproject | crdb_internal | root | ALL myproject | information_schema | admin | ALL myproject | information_schema | django | CREATE myproject | information_schema | django | SELECT myproject | information_schema | root | ALL myproject | pg_catalog | admin | ALL myproject | pg_catalog | django | CREATE myproject | pg_catalog | django | SELECT myproject | pg_catalog | root | ALL myproject | public | admin | ALL myproject | public | django | CREATE myproject | public | django | SELECT myproject | public | root | ALL (16 rows)

    Add myprojectuser to Django role:

    GRANT django TO myprojectuser;

    Now, you can verify access to CockroachDB using the newly created user.

    ./cockroach sql --insecure --host= --port=26257 --database=myproject --user=myprojectuser

    Install Django within a Virtual EnvironmentCopy Icon

    As a reminder, I’m using Python3, so the only difference between this and the Digital Ocean Django tutorial is that we're using pip3, not pip.

    pip3 install virtualenv

    Now, we’re ready to install Django and CockroachDB library.

    pip3 install django psycopg2 django-cockroachdb

    Configure the Django Database SettingsCopy Icon

    Here, we'll set CockroachDB as the database engine.

    DATABASES = { 'default': { 'ENGINE': 'django_cockroachdb', 'NAME': 'myproject', 'USER': 'myprojectuser', 'PASSWORD': '', 'HOST': '', 'PORT': '26257', } }

    Migrate the Database and Test your ProjectCopy Icon

    python3 makemigrations python3 migrate python3 createsuperuser python3 runserver'

    To download the CockroachDB backend for Django project, and to learn more about common gotchas and FAQs, head to PyPI.

    This article was originally posted on Artem Ervits' personal blog.