How to build a complete web app with Django and CockroachDB

Last edited on May 3, 2022

0 minute read

    CockroachDB is a distributed relational database that enables users to scale their application and production software without hardware limitations. CockroachDB Serverless is the fastest way to get started using this relational database. It only takes two steps: sign up using your GitHub account, then connect your cluster using the connection information relevant to your system’s operating system.

    Integrating CockroachDB into a Django application is seamless thanks to its feature-rich object-relational mapping (ORM) that lets us interact with the database using a fluent object-oriented API.

    In this article, we’ll explore how well CockroachDB and Django work together by building a simple application using the Django ORM to communicate with CockroachDB via CockroachCloud. Then we’ll deploy our application to Heroku.

    The final application will be a game leaderboard that enables users to enter information about their gameplay, then store that data in a database. The user interface then retrieves this data to display information about which games the user played, for how many hours, and more.

    It helps to be familiar with Python and Django when following along with this tutorial. It’s also important to note that the article uses Python 3.8 or Python 3.9 as the recommended Python versions. And, if you’d like to see the final files, the application’s source code is available via GitHub.

    How to set up CockroachDB & install DjangoCopy Icon

    For this tutorial, we need to ensure we’re registered for a CockroachDB account.

    To start our application, we’ll begin by installing pip in our system to be able to install python packages. This documentation provides steps on how to install pip depending on the operating system being used.

    Next, we create a project directory called cockroachDB. While inside this directory, we create a virtual environment, venv, and activate it. Then we install Django, as shown below:

    mkdir cockroachDB cd cockroachDB python3 -m venv venv source venv/bin/activate pip install django==3.2.9

    Note that when creating a virtual environment, we need to use one of the following commands to activate the environment depending on the operating system we’re using:

    • Linux/macOS: use source venv/bin/activate

    • Windows: use \venv\Scripts\activate' if you’re using a cmd-based terminal, or '.\venv\Scripts\activate.ps1' if you’re running PowerShell

    After setting up the application, we proceed to create a Django project called game and a Django app called core:

    django-admin startproject game . python3 startapp core

    We need to remember to add the Django app to the installed apps list in the project’s game/ file, as shown below:

    INSTALLED_APPS = [ 'django.contrib.admin', 'django.contrib.auth', 'django.contrib.contenttypes', 'django.contrib.sessions', 'django.contrib.messages', 'django.contrib.staticfiles', 'core.apps.CoreConfig',       #Add ]

    We first run python migrate to apply migrations. Then, we execute the application using python runserver, which serves the default Django application, like in the screenshot below:

    How to add CockroachDB (instead of SQLite)Copy Icon

    Django uses SQLite by default, and even generates a new SQLite database when we create a new application. However, we want to use CockroachDB so we need to set up a cluster and create a database for our application to use.

    First, in our CockroachDB Serverless account, we go to the create your cluster page and select CockroachDB Serverless as our preferred type. Then, we create a cluster by clicking Create your free cluster on the clusters page.

    The other plan available is Dedicated, which is a premium-based plan with added functionalities that allow users to have multi-region capabilities. When creating a free cluster, we can choose to leave the default parameters as they are. These include Cloud provider, Regions, Spend limit, and Cluster name.

    After creating our cluster, CockroachDB Serverless pops up a Connection Info box. There, we create a cluster connection with the default SQL user based on our operating system. We select the appropriate option from the Choose your OS dropdown menu.

    Note that the Connection Info contains a password that will be provided only once. We need to be sure to save the password from this string, as we’ll use it later:

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

    Next, we download the CockroachDB client and CA certificates using the commands provided. It should look like the following example:

    curl --create-dirs -o ~/.postgresql/root.crt -O<cluster-id>/cert

    We use the command cockroach sql --url to connect to our database using the connection string we saved earlier. An example of the connection string is shown below:

    cockroach sql --url 'postgresql://<username>:<password>@<serverless-host>:26257/defaultdb?sslmode=verify-full&sslrootcert='$HOME'/.postgresql/root.crt&options=--cluster=<cluster-name>-<tenant-id>'

    We should see an output similar to this:

    Now, we create a database called game for our application in the prompt SQL shell. We do this with the following command:


    Exit using the command using:

    > \q.

    Next, we create the certs folder in the root of our CockroachDB application with the following command:

    mkdir certs

    Then, we customize the root.crt file’s path by copying the file into the certs folder.

    cp -p $HOME/.postgresql/root.crt /path/to/cockroachDB/certs

    Next, we need to add the certs directory to a .gitignore file so our certificates won’t end up on GitHub.

    First, we create the .gitignore file by entering the touch command in the project’s root directory via terminal:

    touch .gitignore

    Then, we add certs/ to the .gitignore file.

    How to install dependencies for using CockroachDB with DjangoCopy Icon

    To use CockroachDB with Django, we need to install a few dependencies using pip: django, psycopg2, dj-database-url, and django-cockroachdb. We also need to install additional dependencies we’ll use when deploying our application to Heroku: django-heroku, gunicorn, and whitenoise . We install them all at once with the command shown below:

    pip install psycopg2-binary dj-database-url django-cockroachdb==3.2.1 django-crispy-forms django-on-heroku django-environ whitenoise gunicorn

    After installing the dependencies, we use the command pip freeze > requirements.txt to create the requirement.txt file and list our application’s dependencies.

    Our requirements.txt file is as follows:

    asgiref==3.4.1 dj-database-url==0.5.0 Django==3.2.9 django-cockroachdb==3.2.1 django-crispy-forms==1.14.0 django-on-heroku==1.1.2 gunicorn==20.1.0 psycopg2-binary==2.9.3 pytz==2021.3 sqlparse==0.4.2 whitenoise==5.3.0

    Ensure the major django-cockroachdb and django versions correspond. For example, django-cockroachdb 3.2.x is compatible with django 3.2.x.

    Note that for this to work, we need to ensure we’ve created the virtual environment and activated it, just as we did earlier in this tutorial.

    How to configure CockroachDBCopy Icon

    There are three ways to connect our applications to CockroachDB: command line, connection string, or connection parameters. We’ll use connection parameters for this application.

    Then, in the game/, we add the CockcoachDB database:

    import os import environ import dj_database_url # reading .env file env = environ.Env() environ.Env.read_env() …………………. DATABASES = {     'default': {         'ENGINE': 'django_cockroachdb',         'NAME': env('DATABASE_NAME'),         'USER': env('DATABASE_USER'),         'PASSWORD': env('DATABASE_PASS'),         'HOST': env('DATABASE_HOST'),         'PORT': env('DATABASE_PORT'),     }, }

    In the code above, we first import os, environ, and dj_database_url, as the database_url needs them. Then install env() by pip install django-environ and make it readable by creating an instance.

    The code places all database credentials in the game/.env file.

    • The SECRET_KEY is a requirement for Django when deploying to Heroku.

    • The DATABASE_NAME is game, the database we created in the SQL shell. We copy the exact combination of the database name from the Connection info in the Connection parameters tab in our CockroachDB account. We first need to select the database name and change it from defaultdb to game in the Set up section in the Connection info dialogue box, like the screenshot below:

    • The DATABASE_USER and DATABASE_PASS come from the connection string we saved earlier (the string we used to connect to the database).

    • The DATABASE_HOST we’ll use is

    • The DATABASE_PORT for CockroachDB is 26257.

    The database port in the connection parameters is shown below:

    Here’s an example of the game/.env file:

    SECRET_KEY="<secret_key>" DATABASE_NAME="" # xxx-xxx is the combination from Connection parameter DATABASE_USER="<database_username>" DATABASE_PASS="<database_password>" DATABASE_PORT="26257" DATABASE_HOST=""

    How to build the game leaderboard web appCopy Icon

    Now, we’ll implement a simple web application with a game leaderboard. The application has models, views, and templates. Models contain the fields and information about their contents. Views handle the logic for manipulating the models using requests and responses. The application renders the logic to templates that can output the result to the browser when the server runs.

    Setting up the modelsCopy Icon

    First, in the app’s cockroachDB/core/ file, we add a Game class:

    from django.db import models class Game(models.Model):     """     Game model class.     """     name = models.CharField(max_length=100)     viewer_hour = models.PositiveIntegerField()     hours_streamed = models.PositiveIntegerField()     acv_num = models.PositiveIntegerField()     creators = models.PositiveIntegerField()     streams_num = models.PositiveIntegerField()     def __str__(self):         return f'{}'

    The magic string __str__() returns the game’s name instead of the class name when the user views the admin panel.

    This humble class is the key to all of our application’s interactions with CockroachDB. Using the Game class, Django can create a migration that connects to our CockroachDB database and creates a table to store leaderboard data.

    Setting up the viewsCopy Icon

    In the cockroachDB/core/ file, we import the Game model first, then create the views using Django class-based views. We create two views: one view lists the entries from the database and entries users submitted via the form, and the other is a create-view enabling users to enter some data using a form:

    from django.shortcuts import render from django.views.generic import ListView, CreateView from .models import Game class GameListView(ListView):     """     Class-Based View showing the user information and game details.     """     model = Game     template_name = 'core/index.html'     context_object_name = 'games'     paginate_by = 10 class GameCreateView(CreateView):     """     Class-Based view detailing how to create user's information.     """     model = Game     fields = ['name', 'viewer_hour', 'hours_streamed', 'acv_num', 'creators', 'streams_num']

    The GameListView code above defines the template name and the context object name used to iterate template data. GameCreateView doesn’t have a defined template name. Instead, we use the default class-based views naming convention <app>/<model>_<viewtype>.html. The code above also specifies the form’s fields.

    Setting up routingCopy Icon

    Before creating the templates, we create a cockroachDB/core/ file inside the app to route the views path. In the cockroachDB/core/, we import the views and implement them, like this:

    from django.urls import path from .views import GameListView, GameCreateView urlpatterns = [     path('', GameListView.as_view(), name='home'),     path('new/', GameCreateView.as_view(), name='game-create'), ]

    In the project’s game/, we pass the path of the apps by importing include and implementing it like this:

    from django.contrib import admin from django.urls import path, include urlpatterns = [     path('admin/',,     path('', include('core.urls')), ]

    Creating the templatesCopy Icon

    To create the templates, we create a directory called cockroachDB/core/templates with a directory called core inside it.

    Note that this architecture is a Django convention. We can make the templates with or without it, but it’s recommended to use it.

    We start by creating the cockroachDB/core/templates/core/base.html template so other templates can inherit from it.

    In the cockroachDB/core/templates/core/base.html file, we can use Bootstrap 5, Font Awesome, and CSS for quick styling by importing them as a content delivery network (CDN). Then, we create the navbar and pass the template blocks to make it extensible. The code implementation is as follows:

    {% load static %} <!DOCTYPE html> <html lang="en" dir="ltr">   <head>     <meta charset="utf-8">     <link href="" rel="stylesheet" integrity="sha384-EVSTQN3/azprG1Anm3QDgpJLIm9Nao0Yz1ztcQTwFspd3yD65VohhpuuCOmLASjC" crossorigin="anonymous">       <link rel="stylesheet" href=""/>     <link rel="stylesheet" href="{% static 'css/core.css'%}">     <title>Top Games LeadBoard</title>   </head>   <body>     <!-- Navbar -->     <nav class="navbar navbar-expand-lg bg-light navbar-light py-3 mb-5 fixed-top">       <div class="container">         <a href="{% url 'home'%}" class="navbar-brand" style="color: #4c7f99;">Top Games Leaderboard</a>         <button           class="navbar-toggler"           type="button"           data-bs-toggle="collapse"           data-bs-target="#navmenu" >         <span class="navbar-toggler-icon"></span>         </button>         <div class="collapse navbar-collapse" id="navmenu">         </div>       </div>     </nav>     <br>     <!-- Showcase -->     <div class="game-table mt-5">       {% block content %}{% endblock content %}     </div>   </body> </html>

    The code above loads static so the application can access CSS that’s in the static folder.

    Displaying the dataCopy Icon

    To display the user entries and database data, we create a table in the cockroachDB/core/templates/core/index.html file and iterate over the context class object. First, we need to extend the base.html file and pass the table in the template block, like this:

    {% extends 'core/base.html' %} {% block content %} <table class="table table-hover">   <thead>     <tr>       <th scope="col">#</th>       <th scope="col">Name</th>       <th scope="col">Viewer Hours</th>       <th scope="col">Hours Streamed</th>       <th scope="col">ACV</th>       <th scope="col">Creators</th>       <th scope="col">Streams</th>     </tr>   </thead>   {% for game in games %}     <tbody>       <tr class="list-color">         <th scope="row">{{page_obj.start_index|add:forloop.counter0}}</th>         <td>{{}}</td>         <td>{{game.viewer_hour}}</td>         <td>{{game.hours_streamed}}</td>         <td>{{game.acv_num}}</td>         <td>{{game.creators}}</td>         <td>{{game.streams_num}}</td>       </tr>     </tbody>   {% endfor %} </table> {% endblock content %}

    Creating a formCopy Icon

    We need to create a template form to add data to the application directly. The django-crispy-forms application helps make the form look great. First, we use the command pip install django-crispy-forms to install the form-controlling application via the terminal. Then, we add crispy_forms to the list of installed apps in the cockroachDB/game/ file as shown below:

    ……. # Application definition INSTALLED_APPS = [     'django.contrib.admin',     'django.contrib.auth',     'django.contrib.contenttypes',     'django.contrib.sessions',     'django.contrib.messages',     'django.contrib.staticfiles',     'core.apps.CoreConfig',     'crispy_forms',       #Add ] …….

    We also add the setting template pack below to the cockroachDB/game/ file:

    #setting template pack CRISPY_TEMPLATE_PACK = 'bootstrap4'

    Next, we add the following code to cockroachDB/core/templates/core/game_form.html to create the form:

    {% extends 'core/base.html' %} {% load crispy_forms_tags %} {% block content %}   <div class="content-section">     <form method="post">       {% csrf_token %}       <fieldset class="form-group">         <legend class="border-bottom mb-4">Game Form</legend>         {{ form | crispy}}       </fieldset>       <div class="form-group mt-2">         <button class="btn btn-outline-info" type="submit">Submit</button>       </div>     </form>   </div> {% endblock %}

    The code extends cockroachDB/core/templates/core/base.html as before, then loads crispy-forms tags and creates the form.

    In a browser, the form looks like this:

    Improving the UICopy Icon

    An exemplary user interface (UI) design should have an icon on the navbar to add data to the application. The entries also need to be paginated, so we make these changes to the cockroachDB/core/templates/core/base.html file:

    {% load static %} <!DOCTYPE html> <html lang="en" dir="ltr">   <head>     <meta charset="utf-8">     <link href="" rel="stylesheet" integrity="sha384-EVSTQN3/azprG1Anm3QDgpJLIm9Nao0Yz1ztcQTwFspd3yD65VohhpuuCOmLASjC" crossorigin="anonymous">       <link rel="stylesheet" href=""/>     <link rel="stylesheet" href="{% static 'css/core.css'%}">     <title>Top Games Leaderboard</title>   </head>   <body>     <!-- Navbar -->     <nav class="navbar navbar-expand-lg bg-light navbar-light py-3 mb-5 fixed-top">       <div class="container">         <a href="{% url 'home'%}" class="navbar-brand" style="color: #4c7f99;">Top Games Leaderboard</a>         <button           class="navbar-toggler"           type="button"           data-bs-toggle="collapse"           data-bs-target="#navmenu" >         <span class="navbar-toggler-icon"></span>         </button>         <!--Paginator & Icon -->         <div class="collapse navbar-collapse" id="navmenu">           <ul class="navbar-nav ms-auto">             <li class="nav-item">               {% if page_obj.has_previous %}               <a href="?page={{ page_obj.previous_page_number }}" class="nav-link"><i class="fa fa-chevron-left"></i></a>               {% endif %}             </li>             <li class="nav-item">               <a href="#" class="nav-link">Page {{ page_obj.number }} of {{ page_obj.paginator.num_pages }}</a>             </li>             <li class="nav-item">               {% if page_obj.has_next %}                 <a href="?page={{ page_obj.next_page_number }}" class="nav-link"><i class="fa fa-chevron-right"></i></a>               {% endif %}             </li>             <li class="nav-item">               <a href="{% url 'game-create'%}" class="nav-link"><i class="fa fa-plus-circle"></i></a>             </li>           </ul>         </div>       </div>     </nav>     <br>     <!-- Showcase -->     <div class="game-table mt-5">       {% block content %}{% endblock content %}     </div>   </body> </html>

    A user can now add information using the form, and the application stores that information in the database.

    To further improve the UI, let’s create some CSS. In the app directory core, we create a folder called static. Inside this folder, we create another directory called css, then create a file called core.css:

    mkdir static && cd static mkdir css && cd css touch core.css

    In the core.css file we add the following CSS:

    /* Google Fonts */ @import url(''); /* Navbar styles */ body {   font-family: 'Abel', sans-serif;   background: #fafafa;   color: #333333; } .navbar-brand {   font-weight: bold; } .game-table { margin-bottom: 10%; } /* Game form */ .content-section {   background: #ffffff;   padding: 10px 20px;   border: 1px solid #dddddd;   border-radius: 3px;   margin-bottom: 20px;   font-size: 17px;   margin-top: 20px;   padding-bottom: 30px; } {   color: #a2afb8; }

    Redirecting the userCopy Icon

    After entering the data through the form, we want to redirect the user to the index page, where all the data is displayed. To implement redirection, we go to the core/ file and import the reverse function. Then, we add the function below to handle the redirect:

    from django.urls import reverse # ...existing code # Handles redirect     def get_absolute_url(self):             return reverse('home')

    Migrating the databaseCopy Icon

    We’ve added all the code our application needs to run. All that’s left is to generate and run a migration that will make a table for our game data in CockroachDB.

    Let’s migrate our database by navigating to our project’s root directory and running:

    python makemigrations core   python migrate

    If we check in our database in the CockroachDB account, we can see our tables:

    That’s it! We’ve created the database table and our app is ready. Start the development server by running the following command:

    python runserver

    We navigate to http://localhost:8000 in our web browser, where we’ll see an empty table. This is because we haven’t added any games to our database yet. Let’s do that now.

    We load http://localhost:8000/new to open the game creation form we added earlier. We add a few games, then navigate back to http://localhost:8000. We should see something like this:

    How to deploy the appCopy Icon

    To deploy your application to Heroku, we first need to ensure Heroku CLI is installed in our local system. Our application also needs a few dependencies to host it in Heroku: django-heroku, gunicorn, and whitenoise, which we installed earlier. Django doesn’t serve static files in production, so we’ll use whitenoise to enable this support.

    Configuring dependenciesCopy Icon

    We create a file called Procfile in our project’s root directory and add web: gunicorn game.wsgi to the file (`game` is the project name). This specification shows that our application is a web application using gunicorn, a web server getaway.

    In our game/ file, we then add an import of django-heroku in the imports section at the top of our file:

    #...existing imports import django_on_heroku 

    We also need to add django_on_heroku.settings(locals()) to activate Django-Heroku and STATIC_ROOT = os.path.join(BASE_DIR, 'staticfiles') to configure our static assets. At this point, the file looks like this:

    import os import environ import django_on_heroku import dj_database_url from pathlib import Path # Build paths inside the project like this: BASE_DIR / 'subdir'. BASE_DIR = Path(__file__).resolve().parent.parent # reading .env file env = environ.Env() environ.Env.read_env() # Quick-start development settings - unsuitable for production # See # SECURITY WARNING: keep the secret key used in production secret! SECRET_KEY = env('SECRET_KEY') # SECURITY WARNING: don't run with debug turned on in production! # DEBUG = os.environ['DEBUG'] DEBUG = True ALLOWED_HOSTS = ['*'] # Application definition INSTALLED_APPS = [     'django.contrib.admin',     'django.contrib.auth',     'django.contrib.contenttypes',     'django.contrib.sessions',     'django.contrib.messages',     'django.contrib.staticfiles',     'core.apps.CoreConfig',     'crispy_forms', ] MIDDLEWARE = [     '',     'django.contrib.sessions.middleware.SessionMiddleware',     'django.middleware.common.CommonMiddleware',     'django.middleware.csrf.CsrfViewMiddleware',     'django.contrib.auth.middleware.AuthenticationMiddleware',     'django.contrib.messages.middleware.MessageMiddleware',     'django.middleware.clickjacking.XFrameOptionsMiddleware', ] ROOT_URLCONF = 'game.urls' TEMPLATES = [     {         'BACKEND': 'django.template.backends.django.DjangoTemplates',         'DIRS': [],         'APP_DIRS': True,         'OPTIONS': {             'context_processors': [                 'django.template.context_processors.debug',                 'django.template.context_processors.request',                 'django.contrib.auth.context_processors.auth',                 'django.contrib.messages.context_processors.messages',             ],         },     }, ] WSGI_APPLICATION = 'game.wsgi.application' DATABASES = {     'default': {         'ENGINE': 'django_cockroachdb',         'NAME': env('DATABASE_NAME'),         'USER': env('DATABASE_USER'),         'PASSWORD': env('DATABASE_PASS'),         'HOST': env('DATABASE_HOST'),         'PORT': env('DATABASE_PORT'),     }, } # Password validation # AUTH_PASSWORD_VALIDATORS = [     {         'NAME': 'django.contrib.auth.password_validation.UserAttributeSimilarityValidator',     },     {         'NAME': 'django.contrib.auth.password_validation.MinimumLengthValidator',     },     {         'NAME': 'django.contrib.auth.password_validation.CommonPasswordValidator',     },     {         'NAME': 'django.contrib.auth.password_validation.NumericPasswordValidator',     }, ] # Internationalization # LANGUAGE_CODE = 'en-us' TIME_ZONE = 'UTC' USE_I18N = True USE_L10N = True USE_TZ = True USE_THOUSAND_SEPARATOR = True THOUSAND_SEPARATOR=',' DECIMAL_SEPARATOR='.' NUMBER_GROUPING=3 # Static files (CSS, JavaScript, Images) # STATIC_ROOT = os.path.join(BASE_DIR, 'staticfiles') STATIC_URL = '/static/' #setting template pack CRISPY_TEMPLATE_PACK = 'bootstrap4' # Default primary key field type # DEFAULT_AUTO_FIELD = 'django.db.models.BigAutoField' django_on_heroku.settings(locals())

    Creating the application in HerokuCopy Icon

    Now that we’ve configured our dependencies, we head to the terminal and type the following commands to initialize Git and create our application:

    git init heroku login heroku create djangocockroachdb

    Creating the application in Heroku also makes a remote called heroku, where we need to push the application. To add and commit the changes, and push the application to Heroku, we type the following commands:

    git add -A && git commit -m "feat: initial commit" git push heroku main

    By default, Git creates a branch named master, but you can change the name by using git branch -M main to a preferred name like main.

    We add the deployed application’s link to the ALLOWED_HOSTS in the game/ file, like this:

    ALLOWED_HOSTS = ['']

    Pushing the file to HerokuCopy Icon

    Next, we add the file, commit, and push it to the remote heroku. An example of this is shown below:

    git add -A && git commit -m "feat: update commit" git push heroku main

    First, we need to disable collectstatic by typing the command heroku config:set DISABLE_COLLECTSTATIC=1 and adding the variables declared in the .env file to Heroku using the heroku config:set option like this:

    heroku config:set SECRET_KEY=<secret_key> heroku config:set DATABASE_NAME=<database_name> heroku config:set DATABASE_USER=<database_user> heroku config:set DATABASE_PASS=<database_password> heroku config:set DATABASE_PORT=26257 heroku config:set DEBUG=True heroku config:set

    We can also add the variables directly to Heroku through the Config var tab by going to Settings in the Heroku dashboard. After adding our .env values, the Config var should look like this image:

    Since we did not use CockroachDB’s Connection string when configuring the database, we need to perform database migrations to register our database schema, like this:

    heroku run python makemigrations core heroku run python migrate

    To confirm if the app deployed without any errors, we run the command heroku open in the terminal. The output should be like this:

    CockroachDB Serverless saves our data in the cloud so we can access our game leaderboard information from anywhere.

    Next stepsCopy Icon

    We’ve now created a simple Django application using CockroachDB — via CockroachDB Serverless — as the database and deployed the application to Heroku. We don’t need to migrate our database when using CockroachDB’s connection string.

    Now that you know how well Django and CockroachDB work together, you can improve your application by adding more fields or displaying information from multiple users. This code can also provide the foundation for any unique new application that stores and accesses information in a serverless database.

    Sign up for a CockroachDB account to begin building your own Django web app backed by the CockroachDB Serverless database.

    how to build an application