Build a Go App with CockroachDB and GORM

This tutorial shows you how build a simple Go application with CockroachDB and the GORM ORM.

Tip:

For another use of GORM with CockroachDB, see our examples-orms repository.

Step 1. 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 2. Create a database

In the SQL shell, create the bank database that your application will use:
```
> CREATE DATABASE bank;
```
Create a SQL user for your app:
```
> CREATE USER <username> WITH PASSWORD <password>;
```
Take note of the username and password. You will use it in your application code later.
Give the user the necessary permissions:
```
> GRANT ALL ON DATABASE bank TO <username>;
```

If you haven't already, download the CockroachDB binary.
Start the built-in SQL shell using the connection string you got from the CockroachCloud Console earlier:
```
$ cockroach sql \
--url='postgres://<username>:<password>@<global host>:26257/<cluster_name>.defaultdb?sslmode=verify-full&sslrootcert=<certs_dir>/cc-ca.crt'
```
In the connection string copied from the CockroachCloud 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.
In the SQL shell, create the bank database that your application will use:
```
> CREATE DATABASE bank;
```
Exit the SQL shell:
```
> \q
```

Step 3. Run the Go code

The following code uses the GORM ORM (v1) to map Go-specific objects to SQL operations, and the crdbgorm package to handle transactions. Specifically:

db.AutoMigrate(&Account{}) creates an accounts table based on the Account model.
db.Create(&Account{}) inserts rows into the table.
db.Find(&accounts) selects from the table so that balances can be printed.
The funds transfer occurs in transferFunds(). To ensure that we handle retry errors, we wrap the function call in crdbgorm.ExecuteTx().

Get the code

You can copy the code below, download the code directly, or clone the code's GitHub repository.

Here are the contents of main.go:

package main

import (
    "bufio"
    "log"
    "os"

    "gorm.io/driver/postgres"
    "gorm.io/gorm"
)

func main() {
    // Connect to the "bank" database as the "maxroach" user.
    // Read in connection string
    scanner := bufio.NewScanner(os.Stdin)
    log.Println("Enter a connection string: ")
    scanner.Scan()
    connstring := os.ExpandEnv(scanner.Text())

    // Connect to the "bank" database
    _, err := gorm.Open(postgres.Open(connstring), &gorm.Config{})
    if err != nil {
        log.Fatal("error configuring the database: ", err)
    }
    log.Println("Hey! You successfully connected to your CockroachDB cluster.")

}

Update the connection parameters

Edit the addr constant so that:

{username} and {password} specify the SQL username and password that you created earlier.
{hostname} and {port} specify the hostname and port in the (sql) connection string from SQL shell welcome text.

Replace the string given for the addr constant definition with the connection string that you copied earlier from the Connection info dialog.

The constant definition should look similar to the following:

const addr = "postgresql://{user}:{password}@{globalhost}:26257/bank?sslmode=verify-full&sslrootcert={path to the CA certificate}&options=--cluster={cluster_name}"

Where:

{username} and {password} specify the SQL username and password that you created earlier.
{globalhost} is the name of the CockroachCloud free tier host (e.g., free-tier.gcp-us-central1.cockroachlabs.cloud).
{path to the CA certificate} is the path to the cc-ca.crt file that you downloaded from the CockroachCloud Console.
{cluster_name} is the name of your cluster.

Note:

If you are using the connection string that you copied from the Connection info modal, your username, password, hostname, and cluster name will be pre-populated.

Run the code

Initialize the module:

$ go mod init basic-sample

Then run the code:

$ go run main.go

The output should be:

Balance at '2020-12-01 17:31:01.499548 -0500 EST m=+0.092649542':
1 1000
2 250
Balance at '2020-12-01 17:31:01.570412 -0500 EST m=+0.163512523':
1 900
2 350

What's next?

Read more about using the GORM ORM, or check out a more realistic implementation of GORM with CockroachDB in our examples-orms repository.

You might also be interested in the following pages:

Was this page helpful?

Yes

The Most Highly Evolved Database on the Planet

CockroachDB

CockroachCloud

Get to know CockroachDB