Build a Python App with CockroachDB and Django

This tutorial shows you how build a simple Python application with CockroachDB and the Django framework.

CockroachDB supports Django versions 2.2, 3.0, and 3.1.


The example code and instructions on this page use Python 3 and Django 3.1.

Step 1. Install Django and the CockroachDB backend for Django

Install Django:

$ python3 -m pip install 'django>=3.1.*'

Before installing the CockroachDB backend for Django, you must install one of the following psycopg2 prerequisites:

  • psycopg2, which has some prerequisites of its own. This package is recommended for production environments.

  • psycopg2-binary. This package is recommended for development and testing.

After you install the psycopg2 prerequisite, install the CockroachDB Django backend:

$ python3 -m pip install 'django-cockroachdb>=3.1.*'

The major version of django-cockroachdb must correspond to the major version of django. The minor release numbers do not need to match.

Step 2. Start CockroachDB

Choose whether to run a temporary local cluster or a free CockroachDB cluster on CockroachCloud. The instructions below will adjust accordingly.

Create a free cluster

  1. If you haven't already, sign up for a CockroachCloud account.
  2. Log in to your CockroachCloud account.
  3. On the Clusters page, click Create Cluster.
  4. On the Create your cluster page, select the Free Plan.


    This cluster will be free forever.

  5. (Optional) Select a cloud provider (GCP or AWS) in the Additional configuration section.

  6. Click Create your free cluster.

Your cluster will be created in approximately 20-30 seconds.

Set up your cluster connection

Once your cluster is created, the Connection info dialog displays. Use the information provided in the dialog to set up your cluster connection for the SQL user that was created by default:

  1. Click the name of the cc-ca.crt to download the CA certificate to your local machine.
  2. Create a certs directory on your local machine:

    $ mkdir certs
  3. Move the downloaded cc-ca.crt file to the certs directory:

    $ mv <path>/<to>/cc-ca.crt <path>/<to>/certs

    For example:

    $ mv Users/maxroach/Downloads/cc-ca.crt Users/maxroach/certs
  4. Copy the connection string provided, which will be used in the next steps (and to connect to your cluster in the future).


    This connection string contains your password, which will be provided only once. If you forget your password, you can reset it by going to the SQL Users page.

  1. If you haven't already, download the CockroachDB binary.
  2. Run the cockroach demo command:

    $ cockroach demo \

    This starts a temporary, in-memory cluster and opens an interactive SQL shell to the cluster. Any changes to the database will not persist after the cluster is stopped.

  3. Take note of the (sql/tcp) connection string in the SQL shell welcome text:

    # Connection parameters:
    #   (console)
    #   (sql)     postgres://demo:demo11762@?host=%2Fvar%2Ffolders%2Fc8%2Fb_q93vjj0ybfz0fz0z8vy9zc0000gp%2FT%2Fdemo382139081&port=26257
    #   (sql/tcp) postgres://demo:demo11762@

Step 3. Create a database

  1. In the SQL shell, create the bank database that your application will use:

  2. Create a SQL user for your app:

    > CREATE USER <username> WITH PASSWORD <password>;

    Take note of the username and password. You will use it in your application code later.

  3. Give the user the necessary permissions:

    > GRANT ALL ON DATABASE bank TO <username>;
  1. If you haven't already, download the CockroachDB binary.
  2. Start the built-in SQL shell using the connection string you got from the CockroachCloud Console earlier:

    $ cockroach sql \
    --url='postgres://<username>:<password>@<global host>:26257/<cluster_name>.defaultdb?sslmode=verify-full&sslrootcert=<certs_dir>/cc-ca.crt'

    In the connection string copied from the CockroachCloud Console, your username, password and cluster name are pre-populated. Replace the <certs_dir> placeholder with the path to the certs directory that you created earlier.

  3. In the SQL shell, create the bank database that your application will use:


Step 4. Create a Django project

In the directory where you'd like to store your code, use the django-admin command-line tool to create an application project:

$ django-admin startproject cockroach_example

This creates a new project directory called cockroach_example. cockroach_example contains the script and a subdirectory, also named cockroach_example, that contains some .py files.

Open cockroach_example/cockroach_example/, and add to the ALLOWED_HOSTS, so that it reads as follows:


Then add cockroach_example to the list of INSTALLED_APPS, so that it reads as follows:


Next, change DATABASES to reads as follows:

    'default': {
        'ENGINE' : 'django_cockroachdb',
        'PORT' : <port>,
        'USER' : '<user>',
        'PASSWORD': '<password>',
        'HOST' : 'localhost',
        'NAME' : 'bank',


  • <user> is the username that you created earlier.
  • <password> is the password that you created for the <user>.
  • <port> is the port listed in the (sql/tcp) connection string in the SQL shell welcome text. For example, for the connection string postgres://demo:demo11762@, the port is 26257.
    'default': {
        'ENGINE' : 'django_cockroachdb',
        'USER' : '<user>',
        'PASSWORD': '<password>',
        'HOST' : '<globalhost>',
        'PORT' : 26257,
        'NAME' : '<cluster_name>.bank',
        'OPTIONS': {
            'sslmode': 'verify-full',
            'sslrootcert': '<certs_dir>/cc-ca.crt',


  • <user> is the username that you created earlier.
  • <password> is the password that you created for the <user>.
  • <globalhost> is the name of the CockroachCloud Free (beta) host (e.g.,
  • <cluster_name> is the name of your cluster.
  • <certs_dir> is the path to the cc-ca.crt file that you downloaded from the CockroachCloud Console.

If you are using the connection string that you copied from the Connection info dialog, your username, password, hostname, and cluster name will be pre-populated.

Step 5. Write the application logic

After you generate the initial Django project files and edit the project's configuration settings, you need to build out the application with a few .py files in cockroach_example/cockroach_example.


Start by building some models, defined in a file called You can copy the sample code below and paste it into a new file, or you can download the file directly.

from django.db import models

class Customers(models.Model):
    id = models.AutoField(primary_key=True)
    name = models.CharField(max_length=250)

class Products(models.Model):
    id = models.AutoField(primary_key=True)
    name = models.CharField(max_length=250)
    price = models.DecimalField(max_digits=18, decimal_places=2)

class Orders(models.Model):
    id = models.AutoField(primary_key=True)
    subtotal = models.DecimalField(max_digits=18, decimal_places=2)
    customer = models.ForeignKey(
        Customers, on_delete=models.CASCADE, null=True)
    product = models.ManyToManyField(Products)

In this file, we define some simple classes that map to the tables in the example database bank.


Next, build out some class-based views for the application in a file called You can copy the sample code below and paste it into a new file, or you can download the file directly.

from django.http import JsonResponse, HttpResponse
from django.utils.decorators import method_decorator
from django.views.generic import View
from django.views.decorators.csrf import csrf_exempt
from django.db import Error, OperationalError
from django.db.transaction import atomic
from psycopg2 import errorcodes
from functools import wraps
import json
import sys
import time

from .models import *

# Warning: Do not use retry_on_exception in an inner nested transaction.

def retry_on_exception(view, num_retries=3, on_failure=HttpResponse(status=500), delay_=0.5, backoff_=1.5):
    def retry(*args, **kwargs):
        delay = delay_
        for i in range(num_retries):
                return view(*args, **kwargs)
            except OperationalError as ex:
                if i == num_retries - 1:
                    return on_failure
                elif getattr(ex.__cause__, 'pgcode', '') == errorcodes.SERIALIZATION_FAILURE:
                    delay *= backoff_
                    return on_failure
            except Error as ex:
                return on_failure
    return retry

class PingView(View):
    def get(self, request, *args, **kwargs):
        return HttpResponse("python/django", status=200)

@method_decorator(csrf_exempt, name='dispatch')
class CustomersView(View):
    def get(self, request, id=None, *args, **kwargs):
        if id is None:
            customers = list(Customers.objects.values())
            customers = list(Customers.objects.filter(id=id).values())
        return JsonResponse(customers, safe=False)

    def post(self, request, *args, **kwargs):
        form_data = json.loads(request.body.decode())
        name = form_data['name']
        c = Customers(name=name)
        return HttpResponse(status=200)

    def delete(self, request, id=None, *args, **kwargs):
        if id is None:
            return HttpResponse(status=404)
        return HttpResponse(status=200)

    # The PUT method is shadowed by the POST method, so there doesn't seem
    # to be a reason to include it.

@method_decorator(csrf_exempt, name='dispatch')
class ProductView(View):
    def get(self, request, id=None, *args, **kwargs):
        if id is None:
            products = list(Products.objects.values())
            products = list(Products.objects.filter(id=id).values())
        return JsonResponse(products, safe=False)

    def post(self, request, *args, **kwargs):
        form_data = json.loads(request.body.decode())
        name, price = form_data['name'], form_data['price']
        p = Products(name=name, price=price)
        return HttpResponse(status=200)

    # The REST API outlined in the github does not say that /product/ needs
    # a PUT and DELETE method

@method_decorator(csrf_exempt, name='dispatch')
class OrdersView(View):
    def get(self, request, id=None, *args, **kwargs):
        if id is None:
            orders = list(Orders.objects.values())
            orders = list(Orders.objects.filter(id=id).values())
        return JsonResponse(orders, safe=False)

    def post(self, request, *args, **kwargs):
        form_data = json.loads(request.body.decode())
        c = Customers.objects.get(id=form_data['customer']['id'])
        o = Orders(subtotal=form_data['subtotal'], customer=c)
        for p in form_data['products']:
            p = Products.objects.get(id=p['id'])
        return HttpResponse(status=200)

This file defines the application's views as classes. Each view class corresponds to one of the table classes defined in The methods of these classes define read and write transactions on the tables in the database.

Importantly, the file defines a transaction retry loop in the decorator function retry_on_exception(). This function decorates each view method, ensuring that transaction ordering guarantees meet the ANSI SERIALIZABLE isolation level. For more information about how transactions (and retries) work, see Transactions.

URL routes

Lastly, define some URL routes in a file called The django-admin command-line tool generated this file when you created the Django project, so it should already exist in cockroach_example/cockroach_example. You can copy the sample code below and paste it into the existing file, or you can download the file directly and replace the existing one.

"""cockroach_example URL Configuration

The `urlpatterns` list routes URLs to views. For more information please see:
Function views
    1. Add an import:  from my_app import views
    2. Add a URL to urlpatterns:  path('', views.home, name='home')
Class-based views
    1. Add an import:  from other_app.views import Home
    2. Add a URL to urlpatterns:  path('', Home.as_view(), name='home')
Including another URLconf
    1. Import the include() function: from django.urls import include, path
    2. Add a URL to urlpatterns:  path('blog/', include('blog.urls'))
from django.contrib import admin
from django.conf.urls import url

from .views import CustomersView, OrdersView, PingView, ProductView

urlpatterns = [

    url('ping/', PingView.as_view()),

    # Endpoints for customers URL.
    url('customer/', CustomersView.as_view(), name='customers'),
    url('customer/<int:id>/', CustomersView.as_view(), name='customers'),

    # Endpoints for customers URL.
    url('product/', ProductView.as_view(), name='product'),
    url('product/<int:id>/', ProductView.as_view(), name='product'),

    url('order/', OrdersView.as_view(), name='order'),

Step 6. Set up and run the Django app

In the top cockroach_example directory, use the script to create Django migrations that initialize the database for the application:

$ python3 makemigrations cockroach_example
$ python3 migrate

This initializes the bank database with the tables defined in, in addition to some other tables for the admin functionality included with Django's starter application.

To verify that the migration succeeded, open the terminal with the SQL shell to the temporary CockroachDB cluster, and issue the following statements:

> USE bank;
  schema_name |            table_name            | type  | estimated_row_count
  public      | auth_group                       | table |                   0
  public      | auth_group_permissions           | table |                   0
  public      | auth_permission                  | table |                  36
  public      | auth_user                        | table |                   0
  public      | auth_user_groups                 | table |                   0
  public      | auth_user_user_permissions       | table |                   0
  public      | cockroach_example_customers      | table |                   0
  public      | cockroach_example_orders         | table |                   0
  public      | cockroach_example_orders_product | table |                   0
  public      | cockroach_example_products       | table |                   0
  public      | django_admin_log                 | table |                   0
  public      | django_content_type              | table |                   9
  public      | django_migrations                | table |                   1
  public      | django_session                   | table |                   0
(14 rows)

In a different terminal, navigate to the top of the cockroach_example directory, and start the app:

$ python3 runserver

To perform simple reads and writes to the database, you can send HTTP requests to the application.

For example, in a new terminal, you can use curl to send a POST request to the application that inserts a new row into the customers table:

$ curl --header "Content-Type: application/json" \
--request POST \
--data '{"name":"Carl"}'

You can then send a GET request to read from that table:

$ curl
[{"id": 523377322022797313, "name": "Carl"}]

You can also query the tables directly in the SQL shell to see the changes:

> SELECT * FROM cockroach_example_customers;
          id         | name
  523377322022797313 | Carl
(1 row)

What's next?

Read more about writing a Django app.

You might also be interested in the following pages:

YesYes NoNo