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.

Note:

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:

copy
icon/buttons/copy
$ 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:

copy
icon/buttons/copy
$ python3 -m pip install 'django-cockroachdb>=3.1.*'
Note:

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.

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

    copy
    icon/buttons/copy
    $ cockroach demo \
    --empty
    

    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) http://127.0.0.1:61009
    #   (sql)     postgres://root:admin@?host=%2Fvar%2Ffolders%2Fk1%2Fr048yqpd7_9337rgxm9vb_gw0000gn%2FT%2Fdemo255013852&port=26257
    #   (sql/tcp) postgres://root:admin@127.0.0.1:61011?sslmode=require    
    

    In this example, the port number is 61011. You will use the port number in your application code later.

Create a free cluster

  1. Log in to your CockroachCloud account.
  2. On the Clusters page, click Create Cluster.
  3. On the Create your cluster page, select the Free Plan.

    Note:

    This cluster will be free forever.

  4. 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 modal displays. Use the information provided in the modal 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:

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

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

    For example:

    copy
    icon/buttons/copy
    $ 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).

    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.

Step 3. Create a database

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

    copy
    icon/buttons/copy
    > CREATE DATABASE bank;
    
  2. Create a SQL user for your app:

    copy
    icon/buttons/copy
    > 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:

    copy
    icon/buttons/copy
    > 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:

    copy
    icon/buttons/copy
    $ cockroach sql \
    --url='postgres://<username>:<password>@free-tier.gcp-us-central1.cockroachlabs.cloud: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:

    copy
    icon/buttons/copy
    > CREATE DATABASE bank;
    

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:

copy
icon/buttons/copy
$ django-admin startproject cockroach_example

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

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

copy
icon/buttons/copy
ALLOWED_HOSTS = ['0.0.0.0']

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

copy
icon/buttons/copy
INSTALLED_APPS = [
    'django.contrib.admin',
    'django.contrib.auth',
    'django.contrib.contenttypes',
    'django.contrib.sessions',
    'django.contrib.messages',
    'django.contrib.staticfiles',
    'cockroach_example',
]

Next, change DATABASES to reads as follows:

copy
icon/buttons/copy
DATABASES = {
    'default': {
        'ENGINE' : 'django_cockroachdb',
        'NAME' : 'bank',
        'USER' : '<user>',
        'PASSWORD': '<password>',
        'HOST' : 'localhost',
        'PORT' : <port>,
    }
}

Where:

  • <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 (sql/tcp) postgres://root:admin@127.0.0.1:61011?sslmode=require, the port is 61011.

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.

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.

copy
icon/buttons/copy
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.

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.

copy
icon/buttons/copy
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):
    @wraps(view)
    def retry(*args, **kwargs):
        delay = delay_
        for i in range(num_retries):
            try:
                return view(*args, **kwargs)
            except OperationalError as ex:
                if i == num_retries - 1:
                    return on_failure
                elif getattr(ex.__cause__, 'pgcode', '') == errorcodes.SERIALIZATION_FAILURE:
                    time.sleep(delay)
                    delay *= backoff_
                else:
                    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())
        else:
            customers = list(Customers.objects.filter(id=id).values())
        return JsonResponse(customers, safe=False)

    @retry_on_exception
    @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
    @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
    @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
    @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. 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 urls.py file, or you can download the file directly and replace the existing one.

copy
icon/buttons/copy
"""cockroach_example URL Configuration

The `urlpatterns` list routes URLs to views. For more information please see:
    https://docs.djangoproject.com/en/2.2/topics/http/urls/
Examples:
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('admin/', admin.site.urls),

    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 manage.py script to create Django migrations that initialize the database for the application:

copy
icon/buttons/copy
$ python3 manage.py makemigrations cockroach_example
copy
icon/buttons/copy
$ 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:

copy
icon/buttons/copy
> USE bank;
copy
icon/buttons/copy
> 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)

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

copy
icon/buttons/copy
$ python3 manage.py runserver 0.0.0.0:8000

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:

copy
icon/buttons/copy
$ curl --header "Content-Type: application/json" \
--request POST \
--data '{"name":"Carl"}' http://0.0.0.0:8000/customer/

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

copy
icon/buttons/copy
$ curl http://0.0.0.0:8000/customer/
[{"id": 523377322022797313, "name": "Carl"}]

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

copy
icon/buttons/copy
> 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