Deploy a Python To-Do App with Flask, Kubernetes, and CockroachDB Cloud

On this page

This tutorial shows you how to run a sample To-Do app in Kubernetes with CockroachDB Dedicated as the datastore. The app is written in Python with Flask as the web framework and SQLAlchemy for working with SQL data, and the code is open-source and forkable.

Before you begin

If you haven't already, sign up for a CockroachDB Cloud account.

Install the following tools, if you do not already have them:

Tool	Purpose
pip	You'll need pip to install SQLAlchemy and a CockroachDB Python package that accounts for some differences between CockroachDB and PostgreSQL.
Docker	You'll dockerize your application for running in Kubernetes.
minikube	This is the tool you'll use to run Kubernetes locally, for your OS. This includes installing a hypervisor and `kubectl`, the command-line tool used to manage Kubernetes from your local workstation.

If you haven't already, create a CockroachDB Dedicated cluster.

Prepare your cluster

Step 1. Authorize your local workstation's network
Step 2. Create a SQL user
Step 3. Generate the CockroachDB client connection string
Step 4. Create the CockroachDB Dedicated database
Step 5. Generate the application connection string

Step 1. Authorize your local workstation's network

Before you connect to your CockroachDB Dedicated cluster, you need to authorize your network (i.e., add the public IP address of the workstation to the allowlist). Otherwise, connections from this workstation will be rejected.

Once you are logged in, you can use the Console to authorize your network:

In the left navigation bar, click Networking.
Click the Add Network button in the right corner. The Add Network dialog displays.
(Optional) Enter a descriptive name for the network.
From the Network dropdown, select Current Network. Your local machine's IP address will be auto-populated in the box.
Select both networks: DB Console to monitor the cluster and CockroachDB Client to access the databases.

The DB Console refers to the cluster's DB Console, where you can observe your cluster's health and performance. For more information, see DB Console Overview.
Click Apply.

Step 2. Create a SQL user

Note:

Only organization administrators and Cluster Administrators can create SQL users and issue credentials.

Navigate to your cluster's SQL Users page.
Click the Add User button in the top right corner. The Add User dialog displays.
Enter a Username and Password.
Click Save.

Currently, all new SQL users are created with admin privileges. For more information and to change the default settings, see Managing SQL users on a cluster.

Step 3. Generate the CockroachDB client connection string

In the top right corner of the Console, click the Connect button. The Connection info dialog displays.
From the User dropdown, select the user you created in Step 2.
Select a Region to connect to.
From the Database dropdown, select defaultdb.
Run the following command to create a new certs directory on your local machine and download the CA certificate to that directory:
```
curl --create-dirs -o ~/Library/CockroachCloud/certs/<cluster-name>-ca.crt -O https://cockroachlabs.cloud/clusters/<cluster-id>/cert
```
Your cert file will be downloaded to ~/Library/CockroachCloud/certs/<cluster-name>-ca.crt.
```
curl --create-dirs -o ~/Library/CockroachCloud/certs/<cluster-name>-ca.crt -O https://cockroachlabs.cloud/clusters/<cluster-id>/cert
```
Your cert file will be downloaded to ~/Library/CockroachCloud/certs/<cluster-name>-ca.crt.
```
mkdir -p $env:appdata/CockroachCloud/certs/<cluster-name>-ca.crt; Invoke-WebRequest -Uri https://cockroachlabs.cloud/clusters/<cluster-id>/cert -OutFile $env:appdata/CockroachCloud/certs/<cluster-name>-ca.crt
```
Your cert file will be downloaded to %APPDATA%/CockroachCloud/certs/<cluster-name>-ca.crt.
On the Command Line tab, copy the connection string.

Edit the connection string to include your SQL user's password, then save the string in an accessible location since you'll need it to use the built-in SQL client later.

Step 4. Create the CockroachDB Cloud database

On your local workstation's terminal:

If you haven't already, Download the CockroachDB binary and copy it into the PATH:
If you have not done so already, run the first command in the dialog to install the CockroachDB binary in /usr/local/bin, which is usually in the system PATH. To install it into a different location, replace /usr/local/bin/.
```
curl https://binaries.cockroachdb.com/cockroach-v23.1.13.darwin-10.9-amd64.tgz | tar -xJ && sudo cp -i cockroach-v23.1.13.darwin-10.9-amd64/cockroach /usr/local/bin/
```
If you have not done so already, run the first command in the dialog to install the CockroachDB binary in /usr/local/bin, which is usually in the system PATH. To install it into a different location, replace /usr/local/bin/.
```
curl https://binaries.cockroachdb.com/cockroach-v23.1.13.linux-amd64.tgz | tar -xz && sudo cp -i cockroach-v23.1.13.linux-amd64/cockroach /usr/local/bin/
```
If you have not done so already, use PowerShell to run the first command in the dialog, which is a PowerShell script that installs the CockroachDB binary and adds its location in the system PATH:
```
$ErrorActionPreference = "Stop"; [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12;$ProgressPreference = 'SilentlyContinue'; $null = New-Item -Type Directory -Force $env:appdata/cockroach; Invoke-WebRequest -Uri https://binaries.cockroachdb.com/cockroach-v23.1.13.windows-6.2-amd64.zip -OutFile cockroach.zip; Expand-Archive -Force -Path cockroach.zip; Copy-Item -Force "cockroach/cockroach-v23.1.13.windows-6.2-amd64/cockroach.exe" -Destination $env:appdata/cockroach; $Env:PATH += ";$env:appdata/cockroach"
```
We recommend adding ;$env:appdata/cockroach to the PATH variable for your system environment so you can run cockroach commands from any shell. See Microsoft's environment variable documentation for more information.
Use the connection string generated in Step 3 to connect to CockroachDB's built-in SQL client:
```
cockroach sql --url 'postgresql://<user>@<cluster-name>-<short-id>.<region>.cockroachlabs.cloud:26257/<database>?sslmode=verify-full&sslrootcert='$HOME'/Library/CockroachCloud/certs/<cluster-name>-ca.crt'
```
```
cockroach sql --url 'postgresql://<user>@<cluster-name>-<short-id>.<region>.cockroachlabs.cloud:26257/<database>?sslmode=verify-full&sslrootcert='$HOME'/Library/CockroachCloud/certs/<cluster-name>-ca.crt'
```
```
cockroach sql --url "postgresql://<user>@<cluster-name>-<short-id>.<region>.cockroachlabs.cloud:26257/<database>?sslmode=verify-full&sslrootcert=$env:appdata\CockroachCloud\certs\$<cluster-name>-ca.crt"
```
Where:
- <user> is the SQL user. By default, this is your CockroachDB Cloud account username.
- <cluster-name>-<short-id> is the short name of your cluster plus the short ID. For example, funny-skunk-3ab.
- <cluster-id> is a unique string used to identify your cluster when downloading the CA certificate. For example, 12a3bcde-4fa5-6789-1234-56bc7890d123.
- <region> is the region in which your cluster is running. If you have a multi-region cluster, you can choose any of the regions in which your cluster is running. For example, aws-us-east-1.
- <database> is the name for your database. For example, defaultdb.
You can find these settings in the Connection parameters tab of the Connection info dialog.
Create a database todos:
```
> CREATE DATABASE todos;
```
Use database todos:
```
> USE todos;
```

Create a table todos:

> CREATE TABLE todos (
    todo_id INT8 NOT NULL DEFAULT unique_rowid(),
    title VARCHAR(60) NULL,
    text VARCHAR NULL,
    done BOOL NULL,
    pub_date TIMESTAMP NULL,
    CONSTRAINT "primary" PRIMARY KEY (todo_id ASC),
    FAMILY "primary" (todo_id, title, text, done, pub_date)
  );

Step 5. Generate the application connection string

In the top right corner of the Console, click the Connect button.

The Connect dialog displays.
From the User dropdown, select the SQL user you created in Step 2.
Select a Region to connect to.
From the Database dropdown, select todos.
On the Connection String tab, click Copy connection string.

Copy the application connection string to an accessible location. You will update the password and certificate path in the next step.

Step 1. Configure the sample Python app

In a new terminal:

Clone the examples-python repository to your local machine:
```
$ git clone https://github.com/cockroachdb/examples-python
```
Navigate to the flask-alchemy folder:
```
$ cd examples-python/flask-sqlalchemy
```
In the hello.cfg file, replace the value for the SQLALCHEMY_DATABASE_URI with the application connection string you generated in Step 5. Generate the application connection string and save the file.
```
SQLALCHEMY_DATABASE_URI = 'cockroachdb://<username>@<host>:26257/todos?sslmode=verify-full&sslrootcert=$Home/Library/CockroachCloud/certs/<cluster-name>-ca.crt'
```
Note:

You must use the cockroachdb:// prefix in the URL passed to sqlalchemy.create_engine to make sure the cockroachdb dialect is used. Using the postgres:// URL prefix to connect to your CockroachDB cluster will not work.

Copy the application connection string to an accessible location since you need it to configure the sample application in the next step.

Step 2. Test the application locally

Install SQLAlchemy, as well as a CockroachDB Python package that accounts for some differences between CockroachDB and PostgreSQL:
```
$ pip install flask sqlalchemy sqlalchemy-cockroachdb Flask-SQLAlchemy
```
For other ways to install SQLAlchemy, see the official documentation.
Run the hello.py code:
```
$ python hello.py
```
The application should run at http://localhost:5000.
Enter a new to-do item.
Verify that the user interface reflects the new to-do item added to the database.
Use Ctrl+C to stop the application.

Deploy the app

Note:

These steps focus on deploying your app locally. For production Kubernetes deployments, use a service like GKE.

Step 1. Start a local Kubernetes cluster
Step 2. Create a Kubernetes secret
Step 3. Change certificate directory path in configuration file
Step 4. Dockerize the application
Step 5. Deploy the application

Step 1. Start a local Kubernetes cluster

On your local workstation's terminal:

$ minikube start

The startup procedure might take a few minutes.

Step 2. Create a Kubernetes secret

Create a Kubernetes secret to store the CA certificate you downloaded earlier:

$ kubectl create secret generic <username>-secret --from-file $Home/Library/CockroachCloud/certs/<cluster-name>-ca.crt

Verify the Kubernetes secret was created:

$ kubectl get secrets

NAME                  TYPE                                  DATA   AGE
default-token-875zk   kubernetes.io/service-account-token   3      75s
<username>-secret       Opaque                                1      10s

Step 3. Change certificate directory path in configuration file

In the hello.cfg file in the flask-alchemy folder, replace the certificate directory path from the default location to /data/certs and save the file.

SQLALCHEMY_DATABASE_URI = 'cockroachdb://<username>@<host>:26257/todos?sslmode=verify-full&sslrootcert=$Home/Library/CockroachCloud/certs/<cluster-name>-ca.crt'

Note:

You must use the cockroachdb:// prefix in the URL passed to sqlalchemy.create_engine to make sure the cockroachdb dialect is used. Using the postgres:// URL prefix to connect to your CockroachDB cluster will not work.

Step 4. Dockerize the application

In the flask-sqlalchemy folder, create a file named Dockerfile and copy the following code into the file:

FROM python:3.7-slim

WORKDIR /app

ADD . /app

RUN apt-get update && apt-get install -y libpq-dev gcc
# need gcc to compile psycopg2
RUN pip3 install psycopg2~=2.6
RUN apt-get autoremove -y gcc
RUN pip install --trusted-host pypi.python.org -r requirements.txt

EXPOSE 80

CMD ["python", "hello.py"]

Set the environment variable:
```
$ eval $(minikube docker-env)
```
Create the Docker image:
```
$ docker build -t appdocker .
```

Verify the image was created:

$ docker image ls

REPOSITORY         TAG             IMAGE ID            CREATED             SIZE
appdocker          latest          cfb155afed03        3 seconds ago       299MB

Step 5. Deploy the application

In the flask-alchemy folder, create a file named app-deployment.yaml and copy the following code into the file. Replace the <username> placeholder with the SQL user's username that you created while preparing the cluster:

apiVersion: apps/v1
kind: Deployment
metadata:
  name: appdeploy
  labels:
    app: flask
spec:
  selector:
    matchLabels:
      app: flask
  replicas: 3
  strategy:
    type: RollingUpdate
  template:
    metadata:
      labels:
        app: flask
    spec:
      containers:
      - name: appdeploy
        image: appdocker
        imagePullPolicy: Never
        ports:
        - containerPort: 80
        volumeMounts:
        - mountPath: "/data/certs"
          name: ca-certs
          readOnly: true
      volumes:
      - name: ca-certs
        secret:
          secretName: <username>-secret
---
apiVersion: v1
kind: Service
metadata:
  name: appdeploy
  labels:
    app: flask
spec:
  ports:
  - port: 80
    protocol: TCP
    name: flask
  selector:
    app: flask
  type: LoadBalancer

Create the deployment with kubectl:

$ kubectl apply -f app-deployment.yaml

deployment.apps/appdeploy created
service/appdeploy created

Verify that the deployment and server were created:

$ kubectl get deployments

NAME           READY   UP-TO-DATE   AVAILABLE   AGE
appdeploy      3/3     3            3           27s

$ kubectl get services

NAME         TYPE           CLUSTER-IP      EXTERNAL-IP   PORT(S)        AGE
appdeploy    LoadBalancer   10.96.154.104   <pending>     80:32349/TCP   42s

Start the app:

$ minikube service appdeploy

The application will open in the browser. If you get a refused to connect message, use port-forwarding to reach the application:

Get the name of one of the pods:

$ kubectl get pods

NAME                         READY   STATUS              RESTARTS   AGE
appdeploy-577f66b4c8-46s5r   0/1     ErrImageNeverPull   0          23m
appdeploy-577f66b4c8-9chjx   0/1     ErrImageNeverPull   0          23m
appdeploy-577f66b4c8-cnhrg   0/1     ErrImageNeverPull   0          23m

Port-forward from your local machine to one of the pods:

$ kubectl port-forward appdeploy-5f5868f6bf-2cjt5 5000:5000

Forwarding from 127.0.0.1:5000 -> 5000
Forwarding from [::1]:5000 -> 5000

Go to http://localhost:5000/ in your browser.

Monitor the app

Step 1. Access the DB Console
Step 2. Monitor cluster health, metrics, and SQL statements

Step 1. Access the DB Console

On the Console, navigate to the cluster's Tools page and click Open DB Console.

You can also access the DB Console by navigating to https://<cluster-name>crdb.io:8080/#/metrics/overview/cluster. Replace the <cluster-name> placeholder with the name of your cluster.
Enter the SQL user's username and password you created while preparing the cluster.

Warning:

PostgreSQL connection URIs do not support special characters. If you have special characters in your password, you will have to URL encode them (e.g., password! should be entered as password%21) to connect to your cluster.
Click Log In.

Step 2. Monitor cluster health, metrics, and SQL statements

On the Cluster Overview page, view essential metrics about the cluster's health:

Number of live, dead, and suspect nodes
Number of unavailable and under-replicated ranges
Queries per second
Service latency across the cluster

Monitor the hardware metrics

Click Metrics on the left, and then select Dashboard > Hardware.
On the Hardware dashboard, view metrics about CPU usage, disk throughput, network traffic, storage capacity, and memory.

Monitor inter-node latencies

Click Network Latency in the left-hand navigation to check latencies between all nodes in your cluster.

Identify frequently executed or high latency SQL statements

Click Statements on the left.
The Statements page helps you identify frequently executed or high latency SQL statements. The Statements page also allows you to view the details of an individual SQL statement by clicking on the statement to view the Statement Details page.

Was this helpful?

Cockroach
University

Docs Hub

Deploy a Python To-Do App with Flask, Kubernetes, and CockroachDB Cloud

Before you begin

Prepare your cluster

Step 1. Authorize your local workstation's network

Step 2. Create a SQL user

Step 3. Generate the CockroachDB client connection string

Step 4. Create the CockroachDB Cloud database

Step 5. Generate the application connection string

Build the app

Step 1. Configure the sample Python app

Step 2. Test the application locally

Deploy the app

Step 1. Start a local Kubernetes cluster

Step 2. Create a Kubernetes secret

Step 3. Change certificate directory path in configuration file

Step 4. Dockerize the application

Step 5. Deploy the application

Monitor the app

Step 1. Access the DB Console

Step 2. Monitor cluster health, metrics, and SQL statements

Monitor the hardware metrics

Monitor inter-node latencies

Identify frequently executed or high latency SQL statements

Cockroach University

Docs Hub

Cockroach University

Docs Hub

Deploy a Python To-Do App with Flask, Kubernetes, and CockroachDB Cloud

Before you begin

Prepare your cluster

Step 1. Authorize your local workstation's network

Step 2. Create a SQL user

Step 3. Generate the CockroachDB client connection string

Step 4. Create the CockroachDB Cloud database

Step 5. Generate the application connection string

Build the app

Step 1. Configure the sample Python app

Step 2. Test the application locally

Deploy the app

Step 1. Start a local Kubernetes cluster

Step 2. Create a Kubernetes secret

Step 3. Change certificate directory path in configuration file

Step 4. Dockerize the application

Step 5. Deploy the application

Monitor the app

Step 1. Access the DB Console

Step 2. Monitor cluster health, metrics, and SQL statements

Monitor the hardware metrics

Monitor inter-node latencies

Identify frequently executed or high latency SQL statements

Cockroach
University

Cockroach
University