Build a Hello World App with CockroachDB and SQLAlchemy

This tutorial shows you how build a simple Hello World Python application with CockroachDB and the SQLAlchemy ORM.

Step 1. Install SQLAlchemy

To install SQLAlchemy, as well as a CockroachDB Python package that accounts for some differences between CockroachDB and PostgreSQL, run the following command:

$ pip install sqlalchemy sqlalchemy-cockroachdb psycopg2

Tip:

You can substitute psycopg2 for other alternatives that include the psycopg python package.

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

Step 2. Start CockroachDB

Choose whether to run a temporary local cluster or a free CockroachDB cluster on CockroachCloud Free (beta). The instructions below will adjust accordingly.

Create a free cluster

If you haven't already, sign up for a CockroachCloud account.
Log in to your CockroachCloud account.
On the Clusters page, click Create Cluster.
On the Create your cluster page, select CockroachCloud Free.

Note:

This cluster will be free forever.
(Optional) Select a cloud provider (GCP or AWS) in the Additional configuration section.
(Optional) Select a region in the Additional configuration section. For optimal performance, select the cloud provider region closest to the region in which you are running your application.
Click Create your free cluster.

Your cluster will be created in approximately 20-30 seconds.

Set up your cluster connection

Once your cluster is created, the Connect to cluster-name dialog displays. Use the information provided in the dialog to set up your cluster connection for the SQL user that was created by default:

In your terminal, run the second command from the dialog to create a new certs directory on your local machine and download the CA certificate to that directory:
```
curl --create-dirs -o ~/.postgresql/root.crt -O https://cockroachlabs.cloud/clusters/<cluster-id>/cert
```
Your cert file will be downloaded to ~/.postgres/root.crt.
```
curl --create-dirs -o ~/.postgresql/root.crt -O https://cockroachlabs.cloud/clusters/<cluster-id>/cert
```
Your cert file will be downloaded to ~/.postgres/root.crt.
```
mkdir -p $env:appdata\.postgresql\; Invoke-WebRequest -Uri https://cockroachlabs.cloud/clusters/<cluster-id>/cert -OutFile $env:appdata\.postgresql\root.crt
```
Your cert file will be downloaded to %APPDATA%/.postgres/root.crt.

Copy the connection string provided, which will be used in the next steps (and to connect to your cluster in the future).

Warning:

This connection string contains your password, which will be provided only once. If you forget your password, you can reset it by going to the SQL Users page.

cockroach sql --url 'postgresql://<user>@<free-tier-host>.<region>.cockroachlabs.cloud:26257/defaultdb?sslmode=verify-full&sslrootcert='$HOME'/.postgresql/root.crt&options=--cluster=<cluster-name>-<tenant-id>'

cockroach sql --url 'postgresql://<user>@<free-tier-host>.<region>.cockroachlabs.cloud:26257/defaultdb?sslmode=verify-full&sslrootcert='$HOME'/.postgresql/root.crt&options=--cluster=<cluster-name>-<tenant-id>'

cockroach sql --url "postgresql://<user>@<free-tier-host>.<region>.cockroachlabs.cloud:26257/defaultdb?sslmode=verify-full&sslrootcert=$env:appdata/.postgresql/root.crt&options=--cluster=<cluster-name>-<tenant-id>"

If you haven't already, download the CockroachDB binary.
Run the cockroach demo command:
```
$ cockroach demo \
--no-example-database
```
This starts a temporary, in-memory cluster and opens an interactive SQL shell to the cluster. Any changes to the database will not persist after the cluster is stopped.

Note:

If cockroach demo fails due to SSL authentication, make sure you have cleared any previously downloaded CA certificates from the directory ~/.postgresql.

Take note of the (sql) connection string in the SQL shell welcome text:

# Connection parameters:
#   (webui)    http://127.0.0.1:8080/demologin?password=demo76950&username=demo
#   (sql)      postgres://demo:demo76950@127.0.0.1:26257?sslmode=require
#   (sql/unix) postgres://demo:demo76950@?host=%2Fvar%2Ffolders%2Fc8%2Fb_q93vjj0ybfz0fz0z8vy9zc0000gp%2FT%2Fdemo070856957&port=26257

Step 3. Get the code

Clone the code's GitHub repo:

$ git clone https://github.com/cockroachlabs/hello-world-python-sqlalchemy/

The main.py file contains all of the code for the sample Hello World app:

from sqlalchemy import create_engine
import os

def connect(db_uri):
    engine = create_engine(db_uri)
    engine.connect()
    print('Hey! You successfully connected to your CockroachDB cluster.')

if __name__ == '__main__':

    conn_string = input('Enter your node\'s connection string:\n')
    # For cockroach demo:
    # cockroachdb://demo:<demo_password>@127.0.0.1:26257/defaultdb?sslmode=require
    # For CockroachCloud:
    # cockroachdb://<username>:<password>@<globalhost>:26257/<cluster_name>.defaultdb?sslmode=verify-full&sslrootcert=<certs_dir>/<ca.crt>

    try:
        connect(os.path.expandvars(conn_string))
    except Exception as e:
        print('Failed to connect to database.')
        print('{0}'.format(e))

The main method of this program does the following:

Attempts to connect to a running cluster, given a connection string.
Prints a message to the terminal about the connection status.

Step 4. Run the code

To run the app:

$ python3 main.py

The terminal will prompt you for a connection string.

Copy and paste the connection string provided in the (sql) connection string from SQL shell welcome text, and replace the postgres prefix with cockroachdb.

For example:

Enter your node's connection string:
cockroachdb://demo:demo4276@127.0.0.1:26257?sslmode=require

Copy and paste the connection string from the CockroachCloud Console, and replace the postgres prefix with cockroachdb. Make sure that the right username, password, and certificate are specified as well.

For example:

Enter your node's connection string:
cockroachdb://<username>:<password>@<globalhost>:26257/<cluster-name>.bank?sslmode=verify-full&sslrootcert=<certs_directory>/cc-ca.crt

Where you update the connection string as follows:

Replace <username> and <password> with a SQL username and password.
Replace <globalhost> with the name of the CockroachCloud Free (beta) host (e.g., free-tier.gcp-us-central1.cockroachlabs.cloud).
Replace <cluster-name> with the name of your cluster.
Replace <certs_directory> with the path to the cc-ca.crt file that you downloaded from the CockroachCloud Console.

Tip:

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.

After entering the connection string, the program will execute.

The output should look like this:

Hey! You successfully connected to your CockroachDB cluster.

The Most Highly Evolved Database on the Planet

CockroachDB

CockroachCloud

Get to know CockroachDB