This tutorial shows you how to run a sample To-Do app in Kubernetes with CockroachCloud 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
Install the following tools, if you don't already have them:
|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
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 CockroachCloud database
- Step 5. Generate the application connection string
Step 1. Authorize your local workstation's network
Before you connect to your CockroachCloud 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 modal 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: Admin UI to monitor the cluster and CockroachDB Client to access the databases.
The Admin UI refers to the cluster's Admin UI, where you can observe your cluster's health and performance. For more information, see Admin UI Overview.
Step 2. Create a SQL user
Only Console Admins can create SQL users. If you are a Developer, you need to ask your Console Admin for the credentials of a SQL user to access the database. To find out who's your Console Admin, sign in to the Console and navigate to Cluster Overview > Access.
- Navigate to your cluster's SQL Users page.
- Click the Add User button in the top right corner. The Add User modal displays.
- Enter a Username and Password.
Step 3. Generate the CockroachDB client connection string
- In the top right corner of the Console, click the Connect button. The Connect modal displays.
- From the User dropdown, select the user you created in Step 2.
- Select a Region to connect to.
- From the Database dropdown, select
- Create a
certsdirectory on your local workstation.
- Click the Download ca.crt button.
- Move the downloaded
ca.crtfile to the
On the Connect from Shell tab, click Copy connection string.
<certs_dir>placeholders with the path to your
certsdirectory. Copy the client connection string to an accessible location since you need it to use the built-in SQL client later.
Step 4. Create the CockroachCloud database
On your local workstation's terminal:
$ curl https://binaries.cockroachdb.com/cockroach-v20.1.3.darwin-10.9-amd64.tgz \ | tar -xJcopy
$ wget -qO- https://binaries.cockroachdb.com/cockroach-v20.1.3.linux-amd64.tgz \ | tar xvz
Copy the binary into the
PATHso it's easy to run the SQL client from any location:copy
$ cp -i cockroach-v20.1.3.darwin-10.9-amd64/cockroach /usr/local/bin/copy
$ sudo cp -i cockroach-v20.1.3.linux-amd64/cockroach /usr/local/bin/
Use the connection string generated in Step 3 to connect to CockroachDB's built-in SQL client:copy
$ cockroach sql --url 'postgres://<username>@<host>:26257/defaultdb?sslmode=verify-full&sslrootcert=<certs_dir>/<ca.crt>'
Enter the password you created for the SQL user in Step 2.
Create a database
> CREATE DATABASE todos;
> USE todos;
Create a table
> 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 modal 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
On the Connect Your App 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.
Build the app
Step 1. Configure the sample Python app
In a new terminal:
examples-pythonrepository to your local machine:copy
$ git clone https://github.com/cockroachdb/examples-python
Navigate to the
$ cd examples-python/flask-sqlalchemy
hello.cfgfile, replace the value for the
SQLALCHEMY_DATABASE_URIwith the application connection string you generated in Step 5. Generate the application connection string and save the file.copy
SQLALCHEMY_DATABASE_URI = 'cockroachdb://<username>:<password>@<host>:26257/todos?sslmode=verify-full&sslrootcert=<absolute path to CA certificate>'Note:
You must use the
cockroachdb://prefix in the URL passed to
sqlalchemy.create_engineto make sure the
cockroachdbdialect 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:copy
$ pip install flask sqlalchemy sqlalchemy-cockroachdb Flask-SQLAlchemy
For other ways to install SQLAlchemy, see the official documentation.
$ 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.
Ctrl+Cto stop the application.
Deploy the app
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 <absolute path to the CA certificate>
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
hello.cfg file in the
flask-alchemy folder, replace the certificate directory path from the
certs dir to
/data/certs and save the file.
SQLALCHEMY_DATABASE_URI = 'cockroachdb://<username>:<password>@<host>:26257/todos?sslmode=verify-full&sslrootcert=/data/certs/<ca-cert file>'
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
flask-sqlalchemyfolder, create a file named
Dockerfileand copy the following code into the file:copy
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:copy
$ eval $(minikube docker-env)
Create the Docker image:copy
$ docker build -t appdocker .
Verify the image was created:copy
$ docker image ls
REPOSITORY TAG IMAGE ID CREATED SIZE appdocker latest cfb155afed03 3 seconds ago 299MB
Step 5. Deploy the application
flask-alchemyfolder, create a file named
app-deployment.yamland copy the following code into the file. Replace the
<username>placeholder with the SQL user's username that you created while preparing the cluster:copy
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 apply -f app-deployment.yaml
deployment.apps/appdeploy created service/appdeploy created
Verify that the deployment and server were created:copy
$ kubectl get deployments
NAME READY UP-TO-DATE AVAILABLE AGE appdeploy 3/3 3 3 27scopy
$ 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:copy
$ minikube service appdeploy
The application will open in the browser. If you get a
refused to connectmessage, use port-forwarding to reach the application:
Get the name of one of the pods:copy
$ 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:copy
$ kubectl port-forward appdeploy-5f5868f6bf-2cjt5 5000:5000
Forwarding from 127.0.0.1:5000 -> 5000 Forwarding from [::1]:5000 -> 5000
http://localhost:5000/in your browser.
Monitor the app
Step 1. Access the Admin UI
On the Console, navigate to the cluster's Monitoring page and click Open Admin UI.
You can also access the Admin UI 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.
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.