This tutorial shows you how to run a sample To-Do app in Kubernetes with CockroachDB Standard as the datastore. The app is written in Python and uses the Flask web framework and SQLAlchemy for working with SQL data. You can view and download the code from GitHub.
Before you begin
Create a CockroachDB Cloud account and log in.
Create a CockroachDB Standard cluster for the sample To-Do app.
On your local system, be sure the following tools are installed:
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.
Prepare your cluster
Follow the steps in this section to prepare your cluster for the example To-DO app.
Step 1. Authorize your local workstation's network
Before you can connect to your CockroachDB Standard cluster, you need to authorize your network by adding the public IP address of the workstation to the allowlist. Otherwise, connections from this workstation will be rejected.
- In the left navigation bar of the CockroachDB Cloud Console, click Networking.
- Click Add Network. The Add Network dialog displays.
- (Optional) Enter a descriptive name for the network.
- From the Network selector, choose Current Network. Your local machine's IP address will be auto-populated in the box.
- Select both DB Console to monitor the cluster and CockroachDB Client to access the databases. The first option allows connections from this IP to the cluster's DB Console, where you can observe your cluster's health and performance. The second option allows SQL clients to connect from this IP.
- Click Apply.
Step 2. Create a SQL user
Only Org Administrators and Cluster Administrators can create SQL users and issue credentials.
To connect from a SQL client, your cluster must have at least one SQL user. To create a SQL user:
- In the left navigation bar, click SQL Users.
- Click Add User. The Add User dialog displays.
- Enter a username and click Generate & Save Password.
- Copy the generated password to a secure location, such as a password manager.
Click Close.
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. Find the cluster's connection details
In this step, you find the connection details that allow both your application and your local system to connect to the cluster.
In the top right corner of the CockroachDB Cloud Console, click the Connect button.
The Setup page of the Connect to cluster dialog displays.
Select the SQL User you created in Step 2. Create a SQL user.
For Database, select
defaultdb
. You will change this after you follow the instructions in Step 4. Create the database.Click Next.
The Connect page of the Connection info dialog displays.
Select the Command Line tab to find the details you can use to connect from your local workstation's terminal.
If CockroachDB is not installed locally, copy the command to download and install it. In your terminal, run the command.
Copy the
cockroach sql
command. In the command, add a:
character after the username and before the@
character, then paste the password for the SQL user you created earlier. To connect to your cluster from the command line, you can run this command.Select the Connection string tab to find the details you can use to connect from your application.
If the CA certificate for the cluster is not downloaded locally, copy the command to download it. In your terminal, run the command.
Copy the connection string, which begins with
postgresql://
. You will add this string to the example To-Do app so that it can connect to your cluster. In the string, add a:
character after the username and before the@
character, then paste the password for the SQL user you created earlier.Click Close.
Step 4. Create the database
On your local workstation's terminal:
Use the Command Line
cockroach sql
command to connect to the cluster from a terminal: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.
After connecting to the cluster, create a database named
todos
:> CREATE DATABASE todos;
Select the
todos
database:> USE todos;
Create a table
todos
with the following schema:> 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) );
Build the app
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 theSQLALCHEMY_DATABASE_URI
with the application connection string you generated in Step 3. Find the cluster's connection details. Replacedefaultdb
withtodos
to connect to thetodos
database. Save the file.SQLALCHEMY_DATABASE_URI = 'cockroachdb://{username}:{password}@{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 tosqlalchemy.create_engine
to make sure thecockroachdb
dialect is used. Using thepostgres://
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 the following Python packages to satisfy the example app's dependencies. The
sqlalchemy-cockroachdb
package 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
These steps show how to deploy your app locally using Kubernetes.
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'
Step 4. Create a container with the application
In the
flask-sqlalchemy
folder, create a file namedDockerfile
with the following contents: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 namedapp-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 is ready and the load balancer service is pending:
$ 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
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<cluster-name>
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 aspassword%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.