Deploy a CockroachDB Cloud Application with Google Cloud Run

This tutorial shows you how to use Google Cloud Run to deploy a containerized Django application that communicates with a CockroachDB Standard cluster.

Before you begin

Before starting the tutorial, do the following:

1. Create a CockroachDB Cloud account.
2. Create a Google Cloud account.
3. Install the Google Cloud SDK.
4. Install Docker Desktop.

Step 1. Create a CockroachDB Standard cluster

1. . If this is your first CockroachDB Cloud organization, it will be credited with $400 in to get you started.

2. On the Get Started page, click Create cluster.

3. On the Select a plan page, select Standard.

4. On the Cloud & Regions page, select a cloud provider (GCP or AWS).

5. In the Regions section, select a region for the cluster. Refer to for the regions where CockroachDB Standard clusters can be deployed. To create a multi-region cluster, click Add region and select additional regions.

6. Click Next: Capacity.

7. On the Capacity page, keep the at the default value of 2 vCPUs.

   Click Next: Finalize.

8. On the Finalize page, name your cluster. If an active free trial is listed in the right pane, you will not need to add a payment method, though you will need to do this by the to maintain your organization’s clusters.

   Click Create cluster.

   Your cluster will be created in a few seconds and the Create SQL user dialog will display.

Step 2. 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:

1. 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 ~/.postgresql/root.crt.

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

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 for the cluster, found at https://cockroachlabs.cloud/cluster/<CLUSTER ID>/users.

cockroach sql --url 'postgresql://<username:<password@<cluster-host:26257/defaultdb?sslmode=verify-full&sslrootcert='$HOME'/.postgresql/root.crt'

Where:

• <username is the SQL user. By default, this is your CockroachDB Cloud account username.

• <password is the password for the SQL user. The password will be shown only once in the Connection info dialog after creating the cluster.

• <cluster-hostname is the hostname of your CockroachDB Cloud cluster.

• <cluster-id is a unique string used to identify your cluster when downloading the CA certificate. For example, 12a3bcde-4fa5-6789-1234-56bc7890d123.

  You can find these settings in the Connection parameters tab of the Connection info dialog.

curl --create-dirs -o ~/.postgresql/root.crt -O https://cockroachlabs.cloud/clusters/{cluster-id}/cert

Your cert file will be downloaded to ~/.postgresql/root.crt.

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

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 for the cluster, found at https://cockroachlabs.cloud/cluster/<CLUSTER ID>/users.

cockroach sql --url 'postgresql://<username:<password@<cluster-host:26257/defaultdb?sslmode=verify-full&sslrootcert='$HOME'/.postgresql/root.crt'

Where:

• <username is the SQL user. By default, this is your CockroachDB Cloud account username.

• <password is the password for the SQL user. The password will be shown only once in the Connection info dialog after creating the cluster.

• <cluster-hostname is the hostname of your CockroachDB Cloud cluster.

• <cluster-id is a unique string used to identify your cluster when downloading the CA certificate. For example, 12a3bcde-4fa5-6789-1234-56bc7890d123.

  You can find these settings in the Connection parameters tab of the Connection info dialog.

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%/.postgresql/root.crt.

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

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 for the cluster, found at https://cockroachlabs.cloud/cluster/<CLUSTER ID>/users.

cockroach sql --url "postgresql://<username:<password@<cluster-host:26257/defaultdb?sslmode=verify-full&sslrootcert=$env:appdata/.postgresql/root.crt"

Where:

• <username is the SQL user. By default, this is your CockroachDB Cloud account username.

• <password is the password for the SQL user. The password will be shown only once in the Connection info dialog after creating the cluster.

• <cluster-hostname is the hostname of your CockroachDB Cloud cluster.

• <cluster-id is a unique string used to identify your cluster when downloading the CA certificate. For example, 12a3bcde-4fa5-6789-1234-56bc7890d123.

  You can find these settings in the Connection parameters tab of the Connection info dialog.

Step 3. Create a database

1. If you haven’t already, .

2. Start the using the connection string you got from the CockroachDB Cloud Console earlier:

   $ cockroach sql \
   --url='postgres://<username:<password@<global host:26257/defaultdb?sslmode=verify-full&sslrootcert=<certs_dir/cc-ca.crt'
   

   In the connection string copied from the CockroachDB Cloud Console, your username, password and cluster name are pre-populated. Replace the <certs_dir placeholder with the path to the certs directory that you created earlier.

3. In the SQL shell, create the bank database that your application will use:

   > CREATE DATABASE bank;
   

4. Exit the SQL shell:

   > \q
   

Step 4. Get the application code

1. Clone the code’s GitHub repo:

   $ git clone https://github.com/cockroachlabs/example-app-python-django/
   

2. Create a new folder named certs at the top level of the example-app-python-django project, and then copy the root certificate that you downloaded for your cluster to the new folder.

   The project directory structure should look like this:

   ├── Dockerfile
   ├── README.md
   ├── certs
   │   └── root.crt
   ├── cockroach_example
   │   ├── cockroach_example
   │   │   ├── __init__.py
   │   │   ├── asgi.py
   │   │   ├── migrations
   │   │   │   ├── 0001_initial.py
   │   │   │   └── __init__.py
   │   │   ├── models.py
   │   │   ├── settings.py
   │   │   ├── urls.py
   │   │   ├── views.py
   │   │   └── wsgi.py
   │   └── manage.py
   └── requirements.txt
   

Step 5. Initialize the database and test the app locally

1. At the top level of the app’s project directory, create and then activate a virtual environment:

   $ virtualenv env
   
   $ source env/bin/activate
   

2. Install the required modules to the virtual environment:

   $ pip install -r requirements.txt
   

3. Set the DATABASE_URL environment variable to the connection string provided in the Connection info window of the CockroachDB Cloud Console, but with the root certificate located in the local certs directory:

   $ export DATABASE_URL="postgresql://$USER:$[e-mail]:26257/defaultdb?sslmode=verify-full&sslrootcert=root.crt"
   

   This Django app uses the dj_database_url module to configure the database connection from a connection URL. The module uses the value assigned to the DATABASE_URL environment variable for the connection.

In the Cloud Run deployment, we use the Google Cloud Secret Manager to define the DATABASE_URL environment variable for the deployment.

4. Execute the initial database schema migration:

   $ python3 cockroach_example/manage.py migrate
   

   This migration initializes the bank database with the tables defined in models.py, in addition to some other tables for the admin functionality included with Django’s starter application.

5. Run the app:

   $ python3 cockroach_example/manage.py runserver 0.0.0.0:8000
   

   The output should look like this:

   ...
   Starting development server at http://0.0.0.0:8000/
   Quit the server with CONTROL-C.
   

   To perform simple reads and writes to the database, you can send HTTP requests to the application at http://0.0.0.0:8000/.

6. In a new terminal, use curl to send a POST request to the application:

   $ curl --header "Content-Type: application/json" \
   --request POST \
   --data '{"name":"Carl"}' http://0.0.0.0:8000/customer/
   

   This request inserts a new row into the cockroach_example_customers table.

7. Send a GET request to read from the cockroach_example_customers table:

   $ curl http://0.0.0.0:8000/customer/
   
   [{"id": "bb7d6c4d-efb3-45f8-b790-9911aae7d8b2", "name": "Carl"}]
   

   You can also query the table directly in the to see the changes:

   > SELECT * FROM bank.cockroach_example_customers;
   
                      id                  | name
   ---------------------------------------+-------
     bb7d6c4d-efb3-45f8-b790-9911aae7d8b2 | Carl
   (1 row)
   

8. Enter Ctrl+C to stop the application.

Step 6. Configure GCP

1. In the terminal, authenticate the gcloud command-line tool with your Google Cloud account:

gcloud is included with the Google Cloud SDK installation.

$ gcloud auth login

Follow the prompts to authenticate.

2. Create a Google Cloud project for the application deployment:

   $ gcloud projects create <gcp_project_id>
   

You can specify a location for the project within your Google Cloud resources with the --organization or --folder flags.

3. Configure the CLI to use your Google Cloud account and the new project ID by default:

   $ gcloud init
   

4. Set the PROJECT_ID environment variable:

   $ export PROJECT_ID=<gcp_project_id
   

   For the rest of the tutorial, we use PROJECT_ID to refer to the project ID.

Step 7. Containerize the application and push it to the registry

1. Build the Docker image locally:

   $ docker build -t gcr.io/$PROJECT_ID/crdb-sample:v1 .
   

   If there are no errors, the container built successfully.

2. Authenticate Docker with GCP’s Container Registry:

   $ gcloud auth configure-docker
   

3. Enable the Container Registry API for the project:

   $ gcloud services enable containerregistry.googleapis.com
   

4. Push the Docker image to the project’s registry.

   $ docker push gcr.io/$PROJECT_ID/crdb-sample:v1
   

Step 8. Create a secret for the database connection URI

1. Create a service account to manage the secrets for your project:

   $ gcloud iam service-accounts create cockroach-labs
   

2. Enable the Secret Manager API for the project:

   $ gcloud services enable secretmanager.googleapis.com
   

3. Create a secret for the connection string stored locally in the DATABASE_URL environment variable, and bind the new service account to the secret.

   $ echo $DATABASE_URL | gcloud secrets create cockroach-connection-uri --data-file=- --replication-policy=automatic
   
   $ gcloud secrets add-iam-policy-binding cockroach-connection-uri \
       --member=serviceAccount:cockroach-labs@${PROJECT_ID}.iam.gserviceaccount.com \
       --role=roles/secretmanager.secretAccessor
   

Step 9. Deploy the application on Cloud Run

1. Enable the Cloud Run API for the project:

   $ gcloud services enable run.googleapis.com
   

2. Create a Cloud Run service for the application:

   $ gcloud alpha run deploy crl-app --region us-central1 --allow-unauthenticated \
    --service-account=cockroach-labs@${PROJECT_ID}.iam.gserviceaccount.com \
      --set-secrets="DATABASE_URL=cockroach-connection-uri:latest" \
      --image=gcr.io/${PROJECT_ID}/crdb-sample:v1
   

   Note the following:

   • The --region flag specifies the region of the CockroachDB node targeted in the connection string.

   • The --service-account flag specifies the cockroach-labs service account that you created earlier for the app deployment.

   • The --set-secrets flag sets the DATABASE_URL environment variable to the cockroach-connection-uri secret that you created earlier.

   • The --image flag specifies the container image URL for the crdb-sample image that you pushed to the container registry.

     If prompted, select Cloud Run (fully managed).

After the revision is deployed, you should be able to send requests to the application from a browser, or using a REST client (e.g., curl). For example:

$ curl https://<GCR_HOST>/customer/

[{"id": "bb7d6c4d-efb3-45f8-b790-9911aae7d8b2", "name": "Carl"}]

By default, the sample application allows all hosts/domain names to serve the application.

After testing, we recommend that you update the ALLOWED_HOSTS property in settings.py to allow only a local testing URL and the Cloud Run service URL to serve the application.

See also

You might also be interested in the following pages: