CockroachDB and Koyeb

CockroachDB is a cloud-native distributed SQL database built for speed, durability, and scale. It scales horizontally; survives disk and datacenter failures; and supports strongly-consistent ACID transactions all while providing a familiar SQL API for querying data.

This guide explains how to connect a CockroachDB serverless database to an application running on Koyeb. To successfully follow this documentation, you will need to have:

A Koyeb account to deploy the application. If you prefer to follow this guide without leaving the terminal, you can optionally install the Koyeb CLI
A CockroachDB account to deploy the CockroachDB database

Create a CockroachDB

To create a CockroachDB, sign into your CockroachDB account.

On the CockroachDB control panel, start by clicking the Create Cluster button to provision a cluster and access the database creation page.

Choose Serverless as the plan type. This creates a managed CockroachDB cluster for us
Select the Cloud provider for your CockroachDB between AWS or GCP. For this guide, we use AWS
Pick the Region to run the database in. Here, we selected Frankfurt as our compute will be deployed in this region on Koyeb
Give your cluster a name, e.g. cockroachdb-koyeb

When you are done configuring your database, click the Create your free cluster button. Your cluster will be provisioned.

Prepare the database on your machine

The application we will deploy on Koyeb that connects to the CockroachDB uses Prisma as an ORM. Prisma is an open-source ORM. We use it to synchronize our database schema with our Prisma schema.

Before deploying the application, we will need to perform a migration to create the database schema and seed data.

Clone the example application

Start by cloning the application that will connect to the CockroachDB database on your machine and navigate to the app directory by running the following commands:

git clone git@github.com:koyeb/example-express-prisma.git
cd example-express-prisma
git checkout cockroachdb

Retrieve your CockroachDB connection string

On your CockroachDB cluster page, click on the Connection tab to create a new user and password. Save that information in a safe place. Under General connection string you will find the connection string you can use to connect to your cluster.

Since all connections to CockroachDB serverless require SSL encryption, you must have a valid CA certificate located on your machine at ~/.postgresql/root.crt. Follow the instructions under Download CA Cert to obtain your CA certificate.

Initialize and seed the database

To synchronize our database schema with our Prisma schema and seed data, run the following commands in your terminal:

DATABASE_URL="<YOUR_COCKROACHDB_CONNECTION_STRING>" npx prisma db push
DATABASE_URL="<YOUR_COCKROACHDB_CONNECTION_STRING>" npx prisma db seed

Make sure to replace <YOUR_COCKROACHDB_CONNECTION_STRING> with your connection string.

Deploy the application on Koyeb

You can deploy on Koyeb using the control panel or via the Koyeb CLI.

Via the Koyeb control panel

To deploy the example application using the control panel, follow these steps:

Create a new Koyeb App.
Setgithub.com/koyeb/example-express-prisma as the GitHub repository to deploy and select the cockroachdb branch
Add a DATABASE_URL environment variable to indicate the application how to connect to the CockroachDB. Give the environment variable a name, DATABASE_URL and enter the connection string from earlier as the value
Name your service, for instance, express-cockroachdb
Click the Create service button

Koyeb is now building the application. Once the build has finished, you will be able to access your application running on Koyeb by clicking the URL ending with .koyeb.app.

The example application exposes a /planets endpoint that you can use to list planets from the database we prepared earlier. Once your deployment is live, you should see the following results when navigating to https://<YOUR_APP_URL>.koyeb.app/planets:

[
  {
    "id": "827696777012871169",
    "name": "Mercury"
  },
  {
    "id": "827696777225666561",
    "name": "Venus"
  },
  {
    "id": "827696777389539329",
    "name": "Mars"
  },
]

Via the Koyeb CLI

To deploy the example application using the Koyeb CLI, run the following command in your terminal:

koyeb app init express-cockroachdb \
--git github.com/koyeb/example-express-prisma \
--git-branch cockroachdb \
--ports 8080:http \
--routes /:8080 \
--env PORT=8080 \
--env DATABASE_URL="<YOUR_COCKROACHDB_CONNECTION_STRING>"

Make sure to replace <YOUR_COCKROACHDB_CONNECTION_STRING> with your connection string.

Access deployment logs

To track the app deployment and visualize build logs, execute the following command:

koyeb service logs express-cockroachdb/express-cockroachdb -t build

Access your app

Once the deployment of your application has finished, you can retrieve the public domain to access your application by running the following command:

$ koyeb app get express-cockroach
ID      	NAME                  	STATUS  	DOMAINS                                      	CREATED AT
b60982b7	express-cockroachdb   	STARTING	["express-cockroachdb-ded.koyeb.app"]	        01 Dec 22 15:06 UTC

[
  {
    "id": "827696777012871169",
    "name": "Mercury"
  },
  {
    "id": "827696777225666561",
    "name": "Venus"
  },
  {
    "id": "827696777389539329",
    "name": "Mars"
  },
]

Delete the example application and Cockroach database

To delete the example application and the Cockroach database and avoid incurring any charges, follow these steps:

From the CockroachDB dashboard, go to the Actions tab and click Delete cluster. Follow the instructions
From the Koyeb control panel, select the App to delete. Under the Settings tab, click the Delete button and follow the instructions