This tutorial shows you how to run a sample Python To-Do app on a Managed CockroachDB cluster using a local Kubernetes cluster.

Before you begin

  1. Install Docker.
  2. Install minikube, the tool used to run Kubernetes locally, for your OS. This includes installing a hypervisor and kubectl, the command-line tool used to managed Kubernetes from your local workstation.

Prepare your cluster

Step 1. Authorize your local workstation's network

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

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

  1. Navigate to your cluster's Networking page.
  2. Click the Add Network button in the top right corner.

    The Add Network modal displays.

    Add network

  3. Enter the public IPv4 address of your local workstation in the Network field.

    You can use 0.0.0.0/0, which allows all networks. Use this with caution; anybody who uses your password will be able to access the database, and your cluster will be more exposed if there's ever a security bug. The firewall is an extra layer of defense.

    Note:

    IPv6 addresses are currently not supported.

  4. Select both networks: UI and SQL client.

    The 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.

  5. Click Save.

Step 2. Create a SQL user

Note:

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.

  1. Navigate to your cluster's SQL Users page.
  2. Click the Add User button in the top right corner.

    The Add User modal displays.

    Add user

  3. In the Username field, enter maxroach.

  4. In the Password field, enter Q7gc8rEdS.

  5. Click Create.

    Currently, all new users are created with admin privileges. For more information and to change the default settings, see Granting privileges and Using roles.

Step 3. Generate the CockroachDB client connection string

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

    The Connect modal displays.

    Connect to cluster

  2. From the User dropdown, select maxroach.

  3. Select a Region to connect to.

  4. From the Database dropdown, select defaultdb.

  5. Create a certs directory on your local workstation.

  6. Click the Download ca.crt button.

  7. Move the downloaded ca.crt file to the certs directory.

  8. On the Connect from Shell tab, click Copy connection string.

    Replace the <certs_dir> placeholders with the path to your certs directory. 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 Managed CockroachDB database

On your local workstation's terminal:

  1. Download the CockroachDB binary:

    copy
    icon/buttons/copy
    $ curl https://binaries.cockroachdb.com/cockroach-v19.1.4.darwin-10.9-amd64.tgz \
    | tar -xJ
    
    copy
    icon/buttons/copy
    $ wget -qO- https://binaries.cockroachdb.com/cockroach-v19.1.4.linux-amd64.tgz \
    | tar  xvz
    
  2. Copy the binary into the PATH so it's easy to run the SQL client from any location:

    copy
    icon/buttons/copy
    $ cp -i cockroach-v19.1.4.darwin-10.9-amd64/cockroach /usr/local/bin
    
    copy
    icon/buttons/copy
    $ sudo cp -i cockroach-v19.1.4.linux-amd64/cockroach /usr/local/bin
    
  3. Use the connection string generated in Step 3 to connect to CockroachDB's built-in SQL client:

    copy
    icon/buttons/copy
    $ cockroach sql --url 'postgres://maxroach@<region>.<cluster_name>:26257/defaultdb?sslmode=verify-full&sslrootcert=<certs_dir>/<ca.crt>'
    
  4. Enter the password you created for maxroach:

    copy
    icon/buttons/copy
    Q7gc8rEdS
    
  5. Create a database todos:

    copy
    icon/buttons/copy
    > CREATE DATABASE todos;
    
  6. Use database todos:

    copy
    icon/buttons/copy
    > USE todos;
    
  7. Create a table todos:

    copy
    icon/buttons/copy
    > 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

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

    The Connect modal displays.

    Connect from app

  2. From the User dropdown, select maxroach.

  3. Select a Region to connect to.

  4. From the Database dropdown, select todos.

  5. 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 6. Configure the sample Python app

In a new terminal:

  1. Clone the examples-python repository to your local machine:

    copy
    icon/buttons/copy
    $ git clone https://github.com/cockroachdb/examples-python
    
  2. Navigate to the flask-alchemy folder:

    copy
    icon/buttons/copy
    $ cd examples-python/flask-sqlalchemy
    
  3. 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.

    copy
    icon/buttons/copy
    SQLALCHEMY_DATABASE_URI = 'cockroachdb://maxroach:Q7gc8rEdS@<region>.<cluster_name>: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_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 7. Test the application locally

  1. Install SQLAlchemy, as well as a CockroachDB Python package that accounts for some differences between CockroachDB and PostgreSQL:

    copy
    icon/buttons/copy

      $ pip install flask sqlalchemy cockroachdb Flask-SQLAlchemy
    

    For other ways to install SQLAlchemy, see the official documentation.

  2. Run the hello.py code:

    copy
    icon/buttons/copy

      $ python hello.py
    

    The application should run at http://localhost:5000

  3. Enter a new to-do item.

  4. Verify that the user interface reflects the new to-do item added to the database.

  5. 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 8. Start a local Kubernetes cluster

On your local workstation's terminal:

copy
icon/buttons/copy
$ minikube start

The startup procedure might take a few minutes.

Step 9. Create a Kubernetes secret

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

copy
icon/buttons/copy
$ kubectl create secret generic maxroach-secret --from-file <absolute path to the CA certificate>

Verify the Kubernetes secret was created:

copy
icon/buttons/copy
$ kubectl get secrets
NAME                  TYPE                                  DATA   AGE
default-token-875zk   kubernetes.io/service-account-token   3      75s
maxroach-secret       Opaque                                1      10s

Step 10. Change certificate directory path in configuration file

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

copy
icon/buttons/copy
SQLALCHEMY_DATABASE_URI = 'cockroachdb://maxroach:Q7gc8rEdS@<region>.<cluster_name>:26257/todos?sslmode=verify-full&sslrootcert=/data/certs/<ca-cert file>'
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 11. Dockerize your application

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

    copy
    icon/buttons/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"]
    
  2. Set the environment variable:

    copy
    icon/buttons/copy
    $ eval $(minikube docker-env)
    
  3. Create the Docker image:

    copy
    icon/buttons/copy
    $ docker build -t appdocker .
    
  4. Verify the image was created:

    copy
    icon/buttons/copy
    $ docker image ls
    
    REPOSITORY         TAG             IMAGE ID            CREATED             SIZE
    appdocker          latest          cfb155afed03        3 seconds ago       299MB
    

Step 12. Deploy the application

  1. In the flask-alchemy folder, create a file named app-deployment.yaml and copy the following code into the file:

    copy
    icon/buttons/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: maxroach-secret
    ---
    apiVersion: v1
    kind: Service
    metadata:
      name: appdeploy
      labels:
        app: flask
    spec:
      ports:
      - port: 80
        protocol: TCP
        name: flask
      selector:
        app: flask
      type: LoadBalancer
    
  2. Create the deployment with kubectl:

    copy
    icon/buttons/copy
    $ kubectl apply -f app-deployment.yaml
    
    deployment.apps/appdeploy created
    service/appdeploy created
    
  3. Verify that the deployment and server were created:

    copy
    icon/buttons/copy
    $ kubectl get deployments
    
    NAME           READY   UP-TO-DATE   AVAILABLE   AGE
    appdeploy      3/3     3            3           27s
    
    copy
    icon/buttons/copy
    $ kubectl get services
    
    NAME         TYPE           CLUSTER-IP      EXTERNAL-IP   PORT(S)        AGE
    appdeploy    LoadBalancer   10.96.154.104   <pending>     80:32349/TCP   42s
    
  4. Start the app:

    copy
    icon/buttons/copy
    $ 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:

    1. Get the name of one of the pods:

      copy
      icon/buttons/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  
      
    2. Port-forward from your local machine to one of the pods:

      copy
      icon/buttons/copy
      $ kubectl port-forward appdeploy-5f5868f6bf-2cjt5 5000:5000
      
      Forwarding from 127.0.0.1:5000 -> 5000
      Forwarding from [::1]:5000 -> 5000
      
    3. Go to http://localhost:5000/ in your browser.

Monitor the app

Step 13. Access the Admin UI

  1. 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.

  2. In the Username field, enter maxroach.

  3. In the Password field, enter Q7gc8rEdS.

  4. Click Log In.

Step 14. 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

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

Monitor inter-node latencies

  1. Click the gear icon on the left-hand navigation bar.
  2. In the Reports section, click Network Latency to check latencies between all nodes in your cluster.

Identify frequently executed or high latency SQL statements

  1. Click Statements on the left.
  2. 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.


Yes No