← Back to Blog

How to build a complete web app with Python and CockroachDB

How to build a complete web app with Python and CockroachDB

Cockroach University

Event-Driven Architecture for Java Developers

Start Learning

In this article, we’re building a full-stack web app that simulates a game leaderboard. The idea is to make it as simple as possible — one page to display the leaderboard listing and another to add new leaderboard entries.

As a Python developer, you are likely familiar with the language’s simplicity. You can choose from various web development frameworks, but if you’re aiming for an uncomplicated Python web app, then Flask is your friend.

Flask is a lightweight web application micro-framework that enables you to quickly get your application up and running with minimal code and configuration. But don’t let this simplicity deceive you: many major companies develop their enterprise websites using Flask, so remain assured that you can gradually extend your app’s functions as needed with unit testing and REST APIs.

The Flask web framework works in your application’s backend. It relies on Jinja2 as a rich templating engine for the frontend to render HTML when users request web pages.

We’re setting up a CockroachDB Serverless account to publish our CockroachDB database to the cloud in this tutorial. However, we’re not accessing it directly from Python. Instead, we’re using SQLAlchemy, an open-source object-relational mapper for the Python language. This ORM enables us to communicate with, manipulate, and query the database data using the object-oriented paradigm. SQLAlchemy uses the psycopg2 database driver (the same we use to access PostgreSQL) to access the CockroachDB database.

Finally, we’re registering at Heroku using a free account. We’ll deploy our web app on the Heroku web server, where everything will work exactly as in the local machine.

Let’s dive in and explore hands-on how to create our leaderboard web app. If you’d like a preview of where we’ll end up, you can find the complete application code in this GitHub repository.

Creating a Minimal Flask Application

You may already be familiar with Flask’s capabilities. Let’s install it on our development machine to get a simple Flask application running.

The first step is to use pip (the package manager for Python) to install Flask:

pip install flask

In this project, we’re trying to achieve a friendly look-and-feel web application with minimal effort. So, we use Bootstrap, the favored open-source, responsive CSS framework aimed at frontend web development.

We can use Flask-Bootstrap to install Bootstrap dependency on a Flask application with the pip package manager:

pip install flask_bootstrap

To install SQLAlchemy, as well as a CockroachDB Python package (that has some differences between CockroachDB and PostgreSQL), we run the following all-in-one command:

pip install sqlalchemy sqlalchemy-cockroachdb psycopg2

In the project root directory, we then create a new app.py file to work as the entry point for our Python application:

WEB_APP

+-- app.py

Then, we open the app.py file and include the following code:

from flask import Flask

app = Flask(__name__)

@app.route("/")
def hello_world():
   return "<p>Hello, World!</p>"

In the first line, we import the Flask class, which will instantiate our application. The route decorator defines the endpoint URL that will execute the hello_world function, which returns the greeting message in the user’s browser. Note that we’re returning a plain text string without mentioning it’s in HTML format, which is fine because Flask renders the content in the browser as HTML by default.

To start the development server, we can use the flask run CLI command. However, this command requires us first to define the FLASK_APP environment variable to point the command at our app. To set the FLASK_APP variable, we use one of the alternatives below:

  • Use Bash within Linux, or Terminal in macOS to set the FLASK_APP variable:

export FLASK_APP=app

  • In Windows, we can set FLASK_APP via CMD:

set FLASK_APP=app

  • Also in Windows, we can set FLASK_APP via PowerShell:

$env:FLASK_APP = "app"

Now, we run our Flask web app with the flask run CLI command:

flask run

* Serving Flask app 'app' (lazy loading)
* Environment: production
    WARNING: This is a development server. Do not use it in a production deployment.   
    Use a production WSGI server instead.
* Debug mode: off
* Running on http://127.0.0.1:5000/ (Press CTRL+C to quit)

Creating Pages with Flask Templates

We already have a minimal web app running on Flask, so let’s build and render the web pages for the leaderboard and the entry form.

Building HTML pages directly with Python in the back-end code would be cumbersome. So, we must move any HTML code to a dedicated file. Better yet, we can build two HTML files for those two different pages, then make them inherit from the same base HTML page.

First, let’s create a structure to hold the HTML templates for the Flask web app:

WEB_APP

+-- templates
|-- base.html
|-- index.html
  +-- player.html

But Flask doesn’t know how to render HTML. Instead, it delegates this task to Jinja2, the default templating engine for Flask apps. Jinja2 renders HTML when the user requests the web pages in the frontend.

Now, we open the base.html file and include the following markup code, which has blocks that the Jinja2 template engine for Python uses:

{%- extends "bootstrap/base.html" %}

{% block title %}Web App with Python and CockroachDB{% endblock %}

{% import "bootstrap/fixes.html" as fixes %}

{% block head %}
{{super()}}

{{fixes.ie8()}}
{%- endblock %}

{% block styles -%}
  {{super()}}  {# do not forget to call super or Bootstrap's own stylesheets will disappear! #}
  <link rel="stylesheet"   href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css"   integrity="sha384-Gn5384xqQ1aoWXA+058RXPxPg6fy4IWvTNh0E263XmFcJlSAwiGgFAW/dAiS6JXm" crossorigin="anonymous">
{% endblock %}

Note that the code above is not the HTML content you would typically expect to see. The code in the base.html file uses the {% %} blocks, which allow the Jinja2 template engine to determine logical and visual sections within the base page.

Also, the base.html itself inherits from the bootstrap/base.html page, so the cascade style sheet (CSS) or JavaScript dependency related to Bootstrap or other frontend frameworks should go in the base.html file.

Now, we open the index.html file and include the following code:

{%- extends "base.html" %}


{% import "bootstrap/utils.html" as utils %}


{% block content %}


<div class="row mt-4">
  <div class="col col-md-6 offset-md-3 alert alert-primary bg-danger text-white text-center">
    <span class="h1">GAME LEADERBOARD</span>
  </div>
</div>



<div class="row">
  <div class="col col-md-6 offset-md-3">
    <div class="row alert alert-warning bg-dark text-white">
        <span class="col col-1 mt-0 h1 text-center">#</span>
        <span class="col col-7 mt-0 h1 border-left border-light">Player</span>
        <span class="col col-4 mt-0 h1 border-left border-light text-right">Points</span>
      </div>
      {% for score in scores %}
      <div class="row alert alert-warning">
        <input type="hidden" value={{ score.id }}></input>
        <span class="col col-1 mt-0 h1 text-center">{{ score.ranking }}</span>
        <span class="col col-7 mt-0 border-left border-dark h1 text-truncate">{{ score.avatar }}&nbsp;{{ score.playername }}</span>
        <span class="col col-4 mt-0 border-left border-dark h1 text-right">{{ score.points }}</span>
    </div>
    {% endfor %}
  </div>
</div>



<div class="row">
  <div class="col col-md-3 offset-md-6">
    <input type="button" class="h1 mt-0 float-right text-center"
    value="➕ Add New Entry" onclick="location.href='/player'"/>
  </div>
</div>
{%- endblock %}

As you can see, the first line in the index.html file declares that it inherits from base.html. This method keeps our web pages as simple as possible and enables visual and functional consistency across our website’s various derived pages.

Also, note that the index.html code presents the leaderboard page’s rows and columns in a “tableless” grid using plenty of Bootstrap classes.

Another interesting point is the {% for score in scores %} Jinja2 block that allows our Python app to iterate over the scores collection and render each leaderboard line. Within that block, Flask renders the player’s ranking, avatar, name, and points. 

The second page is where users type in the player data to display on the leaderboard. To build that page, open the player.html file and include the following code:

{%- extends "base.html" %}


{% import "bootstrap/utils.html" as utils %}


{% block content %}


<div class="row mt-4">
  <div class="col col-md-6 offset-md-3 alert alert-primary bg-danger text-white text-center">
    <span class="h1">Add New Entry</span>
  </div>
</div>



<form method="POST">
  <div class="row">
    <div class="col col-md-6 offset-md-3">
      <div class="row alert alert-warning bg-dark text-white">
        <span class="col col-md-3 mt-0 h1">Avatar</span>
        <span class="col col-md-6 mt-0 h1 border-left">Player Name</span>
        <span class="col col-md-3 mt-0 h1 border-left text-right">Points</span>
      </div>
      <div class="row">
        <input type="hidden" name="id" value={{ score.id }}></input>
        <select name="avatar" id="avatars" class="col col-md-3 mt-0 h1">
          {% for a in avatars %}
          <option value="{{ a }}" 
          {% if a == score.avatar %}
          selected
          {% endif %}
>{{ avatars\[a] }}</option>
          {% endfor %}
        </select>
        <input type="text" class="col col-md-6 mt-0 h1" name="playername" value={{ score.playername }}></input>
        <input type="number" class="col col-md-3 mt-0 h1 text-right" name="points" value={{ score.points }}></input>
      </div>
    </div>
  </div>
  <div class="row mt-0">
    <div class="col col-md-4 offset-md-5">
      <input type="submit" class="h1 mt-0 float-right text-center" value="✔️ Confirm"/>
      <input type="button" class="h1 mt-0 mr-4 button float-right text-center"
        value="❌ Cancel" onclick="location.href='/'"/>
    </div>
  </div>
</form>
  

  {%- endblock %}

We’re applying the same tableless design in the player.html code as we did in the index.html file here, but the main difference is that we’re using a <form> tag and input HTML elements along with a submit button. This form enables users to enter and submit the player data.

We still must provide data for the Jjinja2 template engine to render. So, we create a Python class for each rendered entity. Let’s make the models.py to hold the Score class representing each of the leaderboard lines:

WEB_APP

+-- models.py


class Score():
    def __init__(self, id, avatar, playername, points):
      self.id = id
      self.avatar = avatar
      self.playername = playername
      self.points = points

Next, let’s implement a backend class to provide data to the views. We still don’t have a database connection, but we can create some fake data to test our web app routes and workflow to see if the data renders as expected.

We create a new leaderboard.py file under the root folder with the following code:

WEB_APP

+-- leaderboard.py


from models import Score


class Leaderboard:
    def get_scores(self):
        return self.prepare_scores()


    def prepare_scores(self):
        scores = []
        scores.append(Score(1, 8, "Marlene F. Martell", 1298))
        scores.append(Score(2, 1, "Curtis D. Torres", 800))
        scores.append(Score(3, 7, "Sandy D. Martinez", 765))
        scores.append(Score(4, 3, "James O. Ewing", 721))

        scores.sort(reverse=True, key=lambda e: e.points)

        result = list(map(lambda score, i: {'id': score.id,
                                            'ranking': i + 1,
                                            'avatar': self.get_avatar_dic()\[str(score.avatar)],
                                            'playername': score.playername,
                                            'points': score.points
                                            },
                        scores,
                        list(range(len(scores)))))

        return result

    def get_avatar_dic(self):
        return {
        "0":"not set",
        "1":"👨🏻",
        "2":"👨🏼",
        "3":"👨🏽",
        "4":"👨🏾",
        "5":"👨🏿",
        "6":"👩🏻",
        "7":"👩🏼",
        "8":"👩🏽",
        "9":"👩🏾",
        "10":"👩🏿"
        }

To consume the data and pass it to the views, let’s modify the app.py file to create an instance of the Leaderboard class and get the scores:

from flask import Flask, render_template, url_for
from flask_bootstrap import Bootstrap
from leaderboard import Leaderboard
from models import Score


app = Flask(__name__)
Bootstrap(app)


leaderboard = Leaderboard()


@app.route("/")
def index():



    scores = leaderboard.get_scores()
    return render_template("index.html",
                            scores=scores)



@app.route("/player", methods=["GET", "POST"])
def player():
    avatars = leaderboard.get_avatar_dic()
    score = Score(0, 0, "", 0)


    return render_template("player.html", score = score, avatars = avatars)

Finally, we rerun the app to check the results:

flask run

We click Add New Entry to test if the second page displays to collect the new player’s data:

We have successfully created an app to gather and display player scores.

Setting Up a CockroachDB Serverless Account

We still need to replace the fake data with leaderboard information from an actual database. Follow the steps below to set up a new CockroachDB database cluster:

  1. If you haven’t already, sign up for a CockroachDB Serverless account.
  2. Log in to your CockroachDB Serverless account.
  3. On the Clusters page, click Create Cluster.
  4. On the Create your cluster page, select CockroachDB Serverless.
  5. Click Create your free cluster.

Note that once you complete your account registration and create a new cluster, CockroachDB presents you with a connection string.

Note that CockroachDB only provides your password once, so make note of it after clicking REVEAL_PASSWORD. Copy the code and save it in a secure location.

Also note that the connection string above has some properties that will vary depending on your particular cluster. See how to identify them in your connection string:

| Sample connection string | "postgresql://johndoe:xpto123abc999@free-tier7.aws-eu-west-1.cockroachlabs.cloud:26257/defaultdb?sslmode=require&options=--cluster%3Dmild-cat-1092" |   |   |   |
|--------------------------|-----------------------------------------------------------------------------------------------------------------------------------------------------|---|---|---|
| <<YOUR-USER>>            | johndoe                                                                                                                                             |   |   |   |
| <<PASSWORD>>             | xpto123abc999                                                                                                                                       |   |   |   |
| <<TIER-NAME>>            | free-tier7                                                                                                                                          |   |   |   |
| <<CLOUD-REGION>>         | aws-eu-west-1                                                                                                                                       |   |   |   |
| <<CLUSTER-NAME>>         | mild-cat                                                                                                                                            |   |   |   |
| <<CLUSTER-NUMBER>>       | 1092

Since we’re using placeholders instead of actual values for these properties, remember to replace them with your configuration accordingly.

Let’s create a .env file to store that data in an environment variable:

WEB_APP

+-- .env

DB_URI = "cockroachdb://<<YOUR-USER>>:<<PASSWORD>>@<<TIER-NAME>>.<<CLOUD-REGION>>.cockroachlabs.cloud:26257/game?sslmode=require&options=--cluster%3D<<CLUSTER-NAME>>-<<CLUSTER-NUMBER>>"

To read the DB_URI key-value pair we just added to the environment variables in the .env file, install the python-dotenv package:

pip install python-dotenv

Initializing the CockroachDB Leaderboard Table

Let’s define the structure of the scores table representing each leaderboard entry. Note the ID is a universally unique identifier (UUID) acting as the primary key, and an avatar is a number from one to ten, each representing an emoji face.

One of the most common ways to create a table is to run an SQL script. Fortunately, running SQL scripts in CockroachDB is no different from in PostgreSQL, so if you’re familiar with one, you’ll be comfortable using the other.

Let’s add a new file named dbinit.sql to create and initialize our database with the scores table and populate it with some player data:

WEB_APP

+-- dbinit.sql

SET sql_safe_updates = FALSE;

USE defaultdb;
DROP DATABASE IF EXISTS game CASCADE;
CREATE DATABASE IF NOT EXISTS game;

USE game;

CREATE TABLE scores (
    id uuid PRIMARY KEY NOT NULL DEFAULT gen_random_uuid(),
    avatar INT8,
    playername STRING,
    points INT8,
    UNIQUE INDEX scores_playername (playername ASC)
);

INSERT INTO scores (avatar, playername, points)
  VALUES (8, 'Marlene F. Martell', 1298);

INSERT INTO scores (avatar, playername, points)
  VALUES (1, 'Curtis D. Torres', 800);

INSERT INTO scores (avatar, playername, points)
  VALUES (7, 'Sandy D. Martinez', 765);

INSERT INTO scores (avatar, playername, points)
  VALUES (3, 'James O. Ewing', 721);

Let’s install the CockroachDB client interface, which allows users to run SQL commands and browse the CockroachDB cluster objects:

pip install cockroachdb

Now we must run a command to read dbinit.sql and pass it to the CockroachDB client. There, CockroachDB can connect to our cluster at CockroachDB Serverless, create the database, create the table, and populate the table with initial data:

cat dbinit.sql | cockroach sql --url "postgresql://<<YOUR-USER>>-<<PASSWORD>>@<<TIER-NAME>>.<<CLOUD-REGION>>.cockroachlabs.cloud:26257/defaultdb?sslmode=require&options=--cluster%3D<<CLUSTER-NAME>-<<CLUSTER-NUMBER>>"

SET
Time: 149.5532ms
SET
Time: 160.0248ms
DROP DATABASE
Time: 155.4707ms
CREATE DATABASE
Time: 177.0301ms
SET
Time: 158.1115ms
CREATE TABLE
Time: 168.1392ms
INSERT 1
Time: 172.3194ms
INSERT 1
Time: 155.4479ms
INSERT 1
Time: 155.9486ms
INSERT 1
Time: 155.4596ms

Note that the cat command reads the dbinit.sql contents, then the cockroach sql command uses the connection string to run the script on the cloud database.

To confirm our database structure and data were created, we connect to the game database at our CockroachDB Serverless cluster:

cockroach sql --url "postgresql://<<YOUR-USER>>-<<PASSWORD>>@<<TIER-NAME>>.<<CLOUD-REGION>>.cockroachlabs.cloud:26257/game?sslmode=require&options=--cluster%3D<<CLUSTER-NAME>-<<CLUSTER-NUMBER>>"

#
# Welcome to the CockroachDB SQL shell.
# All statements must be terminated by a semicolon.
# To exit, type: \q.
#
# Client version: CockroachDB CCL v20.1.17 (x86_64-w64-mingw32, built 2021/05/17 16:42:38, go1.13.9)
# Server version: CockroachDB CCL v21.1.7 (x86_64-unknown-linux-gnu, built 2021/08/09 
17:55:28, go1.15.14)
# Cluster ID: c0854300-2c35-44b4-a7d1-2af71acd3e4c
#
# Enter ? for a brief introduction.
#

This dialog prompts us to enter commands to query our database. Let’s run the SHOW DATABASES; statement to list databases within the cluster:

marcelooliveira@******.cockroachlabs.cloud:26257/game>

/game> SHOW DATABASES;

  database_name |      owner      | primary_region | regions | survival_goal
----------------+-----------------+----------------+---------+----------------        
  defaultdb     | root            | NULL           | {}      | NULL
  game          | marcelooliveira | NULL           | {}      | NULL
  postgres      | root            | NULL           | {}      | NULL
  system        | node            | NULL           | {}      | NULL

(4 rows)

Now, we execute the SHOW TABLES; command:

/game> SHOW TABLES;

  schema_name | table_name | type  |      owner      | estimated_row_count | locality
--------------+------------+-------+-----------------+---------------------+-----------
  public      | scores     | table | marcelooliveira |                   4 | NULL     

(1 row)

Finally, we use the SQL syntax to query the scores table in the game database. Note that you can emit SQL queries just like with any relational database:

/game> SELECT * FROM scores;

                   id                  | avatar |     playername     | points
---------------------------------------+--------+--------------------+---------       
  5d462286-fd12-4b82-a2c9-72c50f1bed29 |      7 | Sandy D. Martinez  |    765
  9781bf36-f651-4b19-a51a-47a165969a91 |      8 | Marlene F. Martell |   1298
  b5ddbc46-20ef-4742-80cf-59f451319994 |      1 | Curtis D. Torres   |    800
  c4550926-1cb5-488b-a935-6daa66b9e016 |      3 | James O. Ewing     |    721

(4 rows)

And when you’re done using CockroachDB SQL, type CTRL+C to close the current connection, exit the interactive SQL shell, and go back to your terminal:

/game> \[TYPE CTRL+C]

PS C:....\MY-PROJ> 

Setting Up SQLAlchemy ORM to Work with CockroachDB

Remember the models.py file where we put the class for the Score model?

  1. We now modify the file so it can map the exact structure of the scores table in the game database:
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, String
from sqlalchemy.dialects.postgresql import UUID
from sqlalchemy.sql.sqltypes import INT

Base = declarative_base()

class Score(Base):
    __tablename__ = 'scores'
    id = Column(UUID, primary_key=True)
    avatar = Column(INT)
    playername = Column(String)
    points = Column(INT)

Note that the Score class now inherits the Base class that the SQLAlchemy package provides. So, SQLAlchemy can track changes made to objects in memory and later translate them into SQL commands to execute against the CockroachDB database.

  1. Now, we create a new transactions.py file, where we’ll put the functions for accessing the database session, getting leaderboard data, and adding new entries to the leaderboard:
WEB_APP

+-- transactions.py

from models import Score
import uuid

def get_scores_txn(session):
    query = session.query(Score)
    return query.all()

def add_score_txn(session, avatar, playername, points):
    score = Score(
        id=str(
            uuid.uuid4()),
        avatar=avatar,
        playername=playername,
        points=points
    )
    session.add(score)

The code above uses the session instance SQLAlchemy provides to query and add data to the database. One advantage of using an ORM like SQLAlchemy instead of direct SQL commands is that we can perform database operations while maintaining our code’s object-oriented programming (OOP) paradigm.

  1. Next, we open the leaderboard.py file and replace its contents with the following:
from cockroachdb.sqlalchemy import run_transaction
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from transactions import get_scores_txn, add_score_txn

class Leaderboard:
    def __init__(self, conn_string):
        self.engine = create_engine(conn_string, convert_unicode=True)
        self.sessionmaker = sessionmaker(bind=self.engine)

    def get_scores(self):
        return run_transaction(self.sessionmaker,
                               lambda session: self.prepare_scores(session))

    def add_score(self, score):
        return run_transaction(self.sessionmaker,
                               lambda session: add_score_txn(session, score.avatar, score.playername, score.points))

    def prepare_scores(self, session):
        scores = get_scores_txn(session)
        scores.sort(reverse=True, key=lambda e: e.points)

        result = list(map(lambda score, i: {'id': score.id,
                                            'ranking': i + 1,
                                            'avatar': self.get_avatar_dic()[str(score.avatar)],
                                            'playername': score.playername,
                                            'points': score.points
                                            },
                        scores,
                        list(range(len(scores)))))
        return result

    def get_avatar_dic(self):
        return {
        "0":"not set",
        "1":"👨🏻",
        "2":"👨🏼",
        "3":"👨🏽",
        "4":"👨🏾",
        "5":"👨🏿",
        "6":"👩🏻",
        "7":"👩🏼",
        "8":"👩🏽",
        "9":"👩🏾",
        "10":"👩🏿"
        }

We modified the functions to read leaderboard data and add new entries using a SQLAlchemy session. Also, we changed the code to sort scores with a lambda function, so the top scores always come first in the leaderboard. 

  1. Finally, we open the app.py file and replace its contents with the following code snippet:
import flask
from flask import Flask, render_template, url_for
from flask_bootstrap import Bootstrap
from werkzeug.utils import redirect
from leaderboard import Leaderboard
from os import environ
from models import Score 

DEFAULT_ROUTE_LEADERBOARD = "index"
DEFAULT_ROUTE_PLAYER = "player"

app = Flask(__name__)
Bootstrap(app)

conn_string = environ.get("DB_URI")
leaderboard = Leaderboard(conn_string)

@app.route("/")
def index():

    scores = leaderboard.get_scores()
    return render_template("index.html",
                            scores=scores)

@app.route("/player", methods=["GET", "POST"])
def player():
    if flask.request.method == "POST":
        id = flask.request.values.get("id")
        avatar = flask.request.values.get("avatar")
        playername = flask.request.values.get("playername")
        points = flask.request.values.get("points")
        leaderboard.add_score(
            Score(id=id, avatar=avatar, playername=playername, points=points)
        )

        return redirect(url_for(DEFAULT_ROUTE_LEADERBOARD))
    else:
        avatars = leaderboard.get_avatar_dic()
        score = Score(avatar="0", playername="", points=0)
        return render_template("player.html", score = score, avatars = avatars)

Here, we modified the player function to take action based on the request method. If the user hits the /player route via a browser (using HTTP GET), we tell the Flask engine to render the player.html page. If the user requests the /player route by clicking the Confirm button (via HTTP POST), we get the score information from the request form data, update the leaderboard, and redirect the user to the home page. 

  1. Finally, we rerun the app to confirm we successfully integrated our web app with the CockroachDB database:

flask run

  1. Now, we click the Add New Entry button.

  2. We choose an avatar and type in the other fields for name and points.

  3. Now we click Confirm to see the new entry on the leaderboard page.

Deploying the CockroachDB App

At this point, our web app is already running locally, which is what we set out to do. Deploying our web application to the cloud, where it can run from anywhere in the world, is the icing on the cake.

One of our options is Heroku, a platform as a service (PaaS) enabling developers to build, run, and operate applications entirely in the cloud. Heroku service supports several programming languages, including Python.

  1. First, register for a free Heroku account if you haven’t yet. Or log in with your existing account.

  2. When we are logged in, we click the New button then click Create new app.

  3. We name our app (for example, web-app-python-cockroachdb) and click Create app.

  4. Next, we select the Settings tab.

  5. Now let’s add the DB_URI environment variable with the same value we’re using in the .env local file, so our new Heroku app can access the database URI from CockroachDB Serverless.

  6. To serve our Flask application at Heroku, we’ll need a Web Server Gateway Interface (WSGI)  like Gunicorn to enable Heroku to forward requests to our Python web app. We install Gunicorn using the following command:

pip install gunicorn

  1. Now we create a file named Procfile (without extension) in the project root folder and add the following line:

web: gunicorn app:app

The first app is the name of the Python file that runs our application or the module’s name. The second app is our application’s name.

  1. We’ll also need a requirements file to enable Heroku to detect our app as a Python project. We run the following command at the root folder to generate the requirements.txt file at the root folder:

pip freeze >requirements.txt

  1. Then, we download and install the Heroku CLI. We can use the Heroku command-line interface (CLI) to manage, scale, and view application logs. After installing the CLI, we can use the heroku command from our shell.

  2. Next, we enter the following command to log into our Heroku account, then follow the prompts to create a new SSH public key:

heroku login

  1. The heroku login command opens our web browser and directs us to the Heroku login page. If you haven’t already logged into your browser, simply click the Log in button on the Heroku login page.

This authentication is required for both the heroku and git commands to work correctly.

  1. Next, we’ll deploy our application to Heroku. If you already have a repository, skip this first step. Otherwise, go to your local repository folder and run the following command to initialize a Git repository:

git init

  1. Since we already have a Heroku app, we can add a remote to our local repository with the following command:

heroku git:remote -a YOUR-HEROKU-APP-NAME

  1. Then, we stage the project files, commit our code to the repository, and deploy it to Heroku using Git:

git add .

git commit -am "this is a heroku commit"

  1. Next, we push the changes from our local main branch to Heroku’s main branch:

git push heroku main

  1. Now we go to our Heroku dashboard and click Open app to open the web application in a new browser tab.

You’ll notice that the web app deployed to Heroku displays exactly like in your local development machine:

  1. If you have a problem with your Heroku installation, you may want to troubleshoot your web app’s deployment, execution, or configuration. Fortunately, Heroku tracks all your application’s activities and enables you to browse logs that your app and Heroku components generated. To view the most activities about your app, use the following Heroku logging commands:

heroku logs --tail:

Next Steps

Cloud database as a service (DBaaS) is a critical technology for companies wanting to achieve the advantages of a modern development stack, including improved security, faster deployment, rapid provisioning, and business agility. And now that you’ve learned how to write Python code to quickly create a Flask web app, relying on a CockroachDB and CockroachDB Serverless cloud database, some of those advantages become even more evident. CockroachDB’s serverless offering greatly simplifies database operations, even from the developer’s perspective.

As we’ve seen, it’s pretty easy to use CockroachDB as part of a simple yet complete, full-stack web application with Python, Flask, and Heroku. Using CockroachDB with Python is straightforward since familiar Postgres tools and libraries work out-of-the-box.

Also, it’s easy to integrate the powerful object-relational mapper tool SQLAlchemy with CockroachDB, which is a delightful surprise for developers already familiar with the PostgreSQL database.

To keep learning about the technologies we introduced in this article and improve your Python developer skills, check out these resources:

Use your new skills to build an app displaying customer contact information to your organization’s salespeople, a shopping app listing your friend’s handcrafted creations, or anything you can imagine that relies on retrieving information from a database.

You can sign up for a free CockroachDB Serverless account right here to quickly get started building your own CockroachDB-backed Python web app.

About the author

Rain Leander github link linkedin link

K Rain Leander is a systematic, slightly psychic, interdisciplinary community liaison with a Bachelor’s in dance and a Master’s in IT. An epic public speaker, they have disappeared within a box stuffed with swords, created life, and went skydiving with the Queen. Seriously. Rain is an active technical contributor with OpenStack, RDO, TripleO, Fedora, and DjangoGirls.

Keep Reading

Build a complete Jamstack app with CockroachDB and Netlify Functions

To help people get outdoors and improve their physical well-being, we’ll create an outdoor activity tracker. Although we …

Read more
Build a complete Jamstack app with CockroachDB and Netlify Functions

To help people get outdoors and improve their physical well-being, we’ll create an outdoor activity tracker. Although we …

Read more
Application architecture: A quick guide for startups

When you’ve got a great idea for a startup, application architecture is probably one of the last things on your mind. …

Read more