Build a Python App with CockroachDB and Django

On this page

Warning:

As of November 18, 2022, CockroachDB v21.1 is no longer supported. For more details, refer to the Release Support Policy.

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

CockroachDB supports Django versions 3.1+.

Note:

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

Step 1. Start CockroachDB

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

Create a free cluster

Note:

Organizations without billing information on file can only create one CockroachDB Serverless cluster.

If you haven't already, sign up for a CockroachDB Cloud account.
Log in to your CockroachDB Cloud account.
On the Clusters page, click Create Cluster.
On the Select a plan page, select Serverless.
On the Cloud & Regions page, select a cloud provider (GCP or AWS) in the Cloud provider section.
In the Regions section, select a region for the cluster. Refer to CockroachDB Cloud Regions for the regions where CockroachDB Serverless clusters can be deployed. To create a multi-region cluster, click Add region and select additional regions. A cluster can have at most six regions.
Click Next: Capacity.
On the Capacity page, select Start for free. Click Next: Finalize.
On the Finalize page, click Create cluster.

Your cluster will be created in a few seconds and the Create SQL user dialog will display.

Set up your cluster connection

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

In your terminal, run the second command from the dialog to create a new certs directory on your local machine and download the CA certificate to that directory:
```
curl --create-dirs -o ~/.postgresql/root.crt -O https://cockroachlabs.cloud/clusters/<cluster-id>/cert
```
Your cert file will be downloaded to ~/.postgresql/root.crt.
```
curl --create-dirs -o ~/.postgresql/root.crt -O https://cockroachlabs.cloud/clusters/<cluster-id>/cert
```
Your cert file will be downloaded to ~/.postgresql/root.crt.
```
mkdir -p $env:appdata\.postgresql\; Invoke-WebRequest -Uri https://cockroachlabs.cloud/clusters/<cluster-id>/cert -OutFile $env:appdata\.postgresql\root.crt
```
Your cert file will be downloaded to %APPDATA%/.postgresql/root.crt.
Copy the connection string provided, which will be used in the next steps (and to connect to your cluster in the future).

Warning:

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 for the cluster, found at https://cockroachlabs.cloud/cluster/<CLUSTER ID>/users.
```
cockroach sql --url 'postgresql://<username>:<password>@<serverless-host>:26257/defaultdb?sslmode=verify-full&sslrootcert='$HOME'/.postgresql/root.crt'
```
```
cockroach sql --url 'postgresql://<username>:<password>@<serverless-host>:26257/defaultdb?sslmode=verify-full&sslrootcert='$HOME'/.postgresql/root.crt'
```
```
cockroach sql --url "postgresql://<username>:<password>@<serverless-host>:26257/defaultdb?sslmode=verify-full&sslrootcert=$env:appdata/.postgresql/root.crt"
```
Where:
- <username> is the SQL user. By default, this is your CockroachDB Cloud account username.
- <password> is the password for the SQL user. The password will be shown only once in the Connection info dialog after creating the cluster.
- <serverless-host> is the hostname of the CockroachDB Serverless cluster.
- <cluster-id> is a unique string used to identify your cluster when downloading the CA certificate. For example, 12a3bcde-4fa5-6789-1234-56bc7890d123.
You can find these settings in the Connection parameters tab of the Connection info dialog.

If you haven't already, download the CockroachDB binary.
Run the cockroach demo command:
```
$ cockroach demo \
--no-example-database
```
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.

Note:

If cockroach demo fails due to SSL authentication, make sure you have cleared any previously downloaded CA certificates from the directory ~/.postgresql.

Take note of the (sql) connection string in the SQL shell welcome text:

# Connection parameters:
#   (webui)    http://127.0.0.1:8080/demologin?password=demo76950&username=demo
#   (sql)      postgres://demo:demo76950@127.0.0.1:26257?sslmode=require
#   (sql/unix) postgres://demo:demo76950@?host=%2Fvar%2Ffolders%2Fc8%2Fb_q93vjj0ybfz0fz0z8vy9zc0000gp%2FT%2Fdemo070856957&port=26257

Step 2. Create a database

In the SQL shell, create the bank database that your application will use:
```
> CREATE DATABASE bank;
```
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.
Give the user the necessary permissions:
```
> GRANT ALL ON DATABASE bank TO <username>;
```

If you haven't already, download the CockroachDB binary.
Start the built-in SQL shell using the connection string you got from the CockroachDB Cloud 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 CockroachDB Cloud 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.
In the SQL shell, create the bank database that your application will use:
```
> CREATE DATABASE bank;
```
Exit the SQL shell:
```
> \q
```

Step 3. Get the sample code

Clone the code's GitHub repo:

$ git clone https://github.com/cockroachlabs/example-app-python-django/

The project directory structure should look like this:

├── Dockerfile
├── README.md
├── cockroach_example
│   ├── cockroach_example
│   │   ├── __init__.py
│   │   ├── asgi.py
│   │   ├── migrations
│   │   │   ├── 0001_initial.py
│   │   │   └── __init__.py
│   │   ├── models.py
│   │   ├── settings.py
│   │   ├── urls.py
│   │   ├── views.py
│   │   └── wsgi.py
│   └── manage.py
└── requirements.txt

Clone the code's GitHub repo:
```
$ git clone https://github.com/cockroachlabs/example-app-python-django/
```

Create a new folder named certs at the top level of the example-app-python-django project, and then copy the root certificate that you downloaded for your cluster to the new folder.

The project directory structure should look like this:

├── Dockerfile
├── README.md
├── certs
│   └── root.crt
├── cockroach_example
│   ├── cockroach_example
│   │   ├── __init__.py
│   │   ├── asgi.py
│   │   ├── migrations
│   │   │   ├── 0001_initial.py
│   │   │   └── __init__.py
│   │   ├── models.py
│   │   ├── settings.py
│   │   ├── urls.py
│   │   ├── views.py
│   │   └── wsgi.py
│   └── manage.py
└── requirements.txt

Step 4. Install the application requirements

To use CockroachDB with Django, the following modules are required:

django
psycopg2 (recommended for production environments) or psycopg2-binary (recommended for development and testing).
django-cockroachdb

Note:

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

The requirements.txt file at the top level of the example-app-python-django project directory contains a list of the requirements needed to run this application:

psycopg2-binary
django==5.0.1
django-cockroachdb==5.0

Note:

The requirements.txt file also lists the dj_database_url module, which is not a strict requirement. The sample app uses this module to configure the database connection from a connection URL.

At the top level of the app's project directory, create and then activate a virtual environment:
```
$ virtualenv env
```
```
$ source env/bin/activate
```
Install the modules listed in requirements.txt to the virtual environment:
```
$ pip install -r requirements.txt
```

Step 5. Configure the database connection

The cockroach_example/cockroach_example/settings.py file defines database connection information for the application, in the DATABASES dictionary:

DATABASES = {}
DATABASES['default'] = dj_database_url.config(default=os.path.expandvars(
    os.environ['DATABASE_URL']), engine='django_cockroachdb')

Note that, rather than using discrete connection parameters, the sample settings.py passes a single variable (the DATABASE_URL environment variable) to the dj_database_url module.

Set the DATABASE_URL environment variable to the connection string:

$ export DATABASE_URL="<connection_string>"

Where <connection_string> is the sql connection URL provided in the cluster's welcome text.

Where <connection_string> is the connection string provided in the Connection info window of the CockroachDB Cloud Console, but with the root certificate located in the local certs directory.

Note that you also need to provide a SQL user password in order to securely connect to a CockroachDB Cloud cluster. The connection string should have a placeholder for the password (<ENTER-PASSWORD>).

Step 6. Build out the application

After you have configured the app's database connection, you can start building out the application.

Models

Start by building some models, defined in a file called models.py. 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
import uuid


class Customers(models.Model):
    id = models.UUIDField(
        primary_key=True,
        default=uuid.uuid4,
        editable=False)
    name = models.CharField(max_length=250)


class Products(models.Model):
    id = models.UUIDField(
        primary_key=True,
        default=uuid.uuid4,
        editable=False)
    name = models.CharField(max_length=250)
    price = models.DecimalField(max_digits=18, decimal_places=2)


class Orders(models.Model):
    id = models.UUIDField(
        primary_key=True,
        default=uuid.uuid4,
        editable=False)
    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.

Views

Next, build out some class-based views for the application in a file called views.py. 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, IntegrityError
from django.db.transaction import atomic
from psycopg2 import errorcodes
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(num_retries=3, on_failure=HttpResponse(status=500), delay_=0.5, backoff_=1.5):
    def retry(view):
        def wrapper(*args, **kwargs):
            delay = delay_
            for i in range(num_retries):
                try:
                    return view(*args, **kwargs)
                except IntegrityError as ex:
                    if i == num_retries - 1:
                        return on_failure
                    elif getattr(ex.__cause__, 'pgcode', '') == errorcodes.SERIALIZATION_FAILURE:
                        time.sleep(delay)
                        delay *= backoff_
                except Error as ex:
                    return on_failure
        return wrapper
    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())
        else:
            customers = list(Customers.objects.filter(id=id).values())
        return JsonResponse(customers, safe=False)

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

    @retry_on_exception(3)
    @atomic
    def delete(self, request, id=None, *args, **kwargs):
        if id is None:
            return HttpResponse(status=404)
        Customers.objects.filter(id=id).delete()
        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())
        else:
            products = list(Products.objects.filter(id=id).values())
        return JsonResponse(products, safe=False)

    @retry_on_exception(3)
    @atomic
    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)
        p.save()
        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())
        else:
            orders = list(Orders.objects.filter(id=id).values())
        return JsonResponse(orders, safe=False)

    @retry_on_exception(3)
    @atomic
    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)
        o.save()
        for p in form_data['products']:
            p = Products.objects.get(id=p['id'])
            o.product.add(p)
        o.save()
        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 models.py. 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 urls.py. You can copy the sample code below and paste it into the existing urls.py file, or you can download the file directly and replace the existing one.

from django.contrib import admin
from django.urls import path

from .views import CustomersView, OrdersView, PingView, ProductView

urlpatterns = [
    path('admin/', admin.site.urls),

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

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

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

    path('order/', OrdersView.as_view(), name='order'),
]

Step 7. Initialize the database

In the top cockroach_example directory, use the manage.py script to create Django migrations that initialize the database for the application:
```
$ python3 manage.py makemigrations cockroach_example
```
```
$ python3 manage.py migrate
```
This initializes the bank database with the tables defined in models.py, 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;

> SHOW TABLES;

  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)

Step 8. Run the app

In a different terminal, navigate to the top of the cockroach_example directory, and start the app:
```
$ python3 manage.py runserver 0.0.0.0:8000
```
The output should look like this:
```
...
Starting development server at http://0.0.0.0:8000/
Quit the server with CONTROL-C.
```
To perform simple reads and writes to the database, you can send HTTP requests to the application server listening at http://0.0.0.0:8000/.
In a new terminal, use curl to send a POST request to the application:
```
$ curl --header "Content-Type: application/json" \
--request POST \
--data '{"name":"Carl"}' http://0.0.0.0:8000/customer/
```
This request inserts a new row into the cockroach_example_customers table.

Send a GET request to read from the cockroach_example_customers table:

$ curl http://0.0.0.0:8000/customer/

[{"id": "bb7d6c4d-efb3-45f8-b790-9911aae7d8b2", "name": "Carl"}]

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

> SELECT * FROM bank.cockroach_example_customers;

                   id                  | name
---------------------------------------+-------
  bb7d6c4d-efb3-45f8-b790-9911aae7d8b2 | Carl
(1 row)

Enter Ctrl+C to stop the application.

Cockroach
University

Docs Hub

Build a Python App with CockroachDB and Django

Step 1. Start CockroachDB

Create a free cluster

Set up your cluster connection

Step 2. Create a database

Step 3. Get the sample code

Step 4. Install the application requirements

Step 5. Configure the database connection

Step 6. Build out the application

Models

Views

URL routes

Step 7. Initialize the database

Step 8. Run the app

What's next?

Cockroach University

Docs Hub

Cockroach University

Docs Hub

Build a Python App with CockroachDB and Django

Step 1. Start CockroachDB

Create a free cluster

Set up your cluster connection

Step 2. Create a database

Step 3. Get the sample code

Step 4. Install the application requirements

Step 5. Configure the database connection

Step 6. Build out the application

Models

Views

URL routes

Step 7. Initialize the database

Step 8. Run the app

What's next?

Cockroach
University

Cockroach
University