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. 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 the Free Plan.

Note:

This cluster will be free forever.
(Optional) Select a cloud provider (GCP or AWS) in the Additional configuration section.
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 Connection info dialog displays. Use the information provided in the dialog to set up your cluster connection for the SQL user that was created by default:

Click the name of the cc-ca.crt to download the CA certificate to your local machine.
Create a certs directory on your local machine:
```
$ mkdir certs
```
Move the downloaded cc-ca.crt file to the certs directory:
```
$ mv <path>/<to>/cc-ca.crt <path>/<to>/certs
```
For example:
```
$ mv Users/maxroach/Downloads/cc-ca.crt Users/maxroach/certs
```
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.

If you haven't already, download the CockroachDB binary.
Run the cockroach demo command:
```
$ cockroach demo \
--empty
```
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.

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

# Connection parameters:
#   (console) http://127.0.0.1:61009
#   (sql)     postgres://root:admin@?host=%2Fvar%2Ffolders%2Fk1%2Fr048yqpd7_9337rgxm9vb_gw0000gn%2FT%2Fdemo255013852&port=26257
#   (sql/tcp) postgres://root:admin@127.0.0.1:61011?sslmode=require

In this example, the port number is 61011. You will use the port number in your application code later.

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;
```

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 (
    "context"
    "fmt"
    "log"
    "time"

    // Import GORM-related packages.
    "github.com/cockroachdb/cockroach-go/crdb/crdbgorm"
    "github.com/jinzhu/gorm"
    _ "github.com/jinzhu/gorm/dialects/postgres"
)

// Account is our model, which corresponds to the "accounts" database
// table.
type Account struct {
    ID      int `gorm:"primary_key"`
    Balance int
}

// Functions of type `txnFunc` are passed as arguments to the
// `ExecuteTx` wrapper that handles transaction retries
type txnFunc func(*gorm.DB) error

func transferFunds(db *gorm.DB, fromID int, toID int, amount int) error {
    var fromAccount Account
    var toAccount Account

    db.First(&fromAccount, fromID)
    db.First(&toAccount, toID)

    if fromAccount.Balance < amount {
        return fmt.Errorf("account %d balance %d is lower than transfer amount %d", fromAccount.ID, fromAccount.Balance, amount)
    }

    fromAccount.Balance -= amount
    toAccount.Balance += amount

    if err := db.Save(&fromAccount).Error; err != nil {
        return err
    }
    if err := db.Save(&toAccount).Error; err != nil {
        return err
    }
    return nil
}

func main() {
    // Connect to the "bank" database as the "maxroach" user.
    const addr = "postgres://{username}:{password}@{hostname}:{port}/bank?sslmode=require"
    db, err := gorm.Open("postgres", addr)
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // Set to `true` and GORM will print out all DB queries.
    db.LogMode(false)

    // Automatically create the "accounts" table based on the Account
    // model.
    db.AutoMigrate(&Account{})

    // Insert two rows into the "accounts" table.
    var fromID = 1
    var toID = 2
    db.Create(&Account{ID: fromID, Balance: 1000})
    db.Create(&Account{ID: toID, Balance: 250})

    // The sequence of steps in this section is:
    // 1. Print account balances.
    // 2. Set up some Accounts and transfer funds between them inside
    // a transaction.
    // 3. Print account balances again to verify the transfer occurred.

    // Print balances before transfer.
    printBalances(db)

    // The amount to be transferred between the accounts.
    var amount = 100

    // Transfer funds between accounts.  To handle potential
    // transaction retry errors, we wrap the call to `transferFunds`
    // in `crdbgorm.ExecuteTx`, a helper function for GORM which
    // implements a retry loop
    if err := crdbgorm.ExecuteTx(context.Background(), db, nil,
        func(tx *gorm.DB) error {
            return transferFunds(tx, fromID, toID, amount)
        },
    ); err != nil {
        // For information and reference documentation, see:
        //   https://www.cockroachlabs.com/docs/stable/error-handling-and-troubleshooting.html
        fmt.Println(err)
    }

    // Print balances after transfer to ensure that it worked.
    printBalances(db)

    // Delete accounts so we can start fresh when we want to run this
    // program again.
    deleteAccounts(db)
}

func printBalances(db *gorm.DB) {
    var accounts []Account
    db.Find(&accounts)
    fmt.Printf("Balance at '%s':\n", time.Now())
    for _, account := range accounts {
        fmt.Printf("%d %d\n", account.ID, account.Balance)
    }
}

func deleteAccounts(db *gorm.DB) error {
    // Used to tear down the accounts table so we can re-run this
    // program.
    err := db.Exec("DELETE from accounts where ID > 0").Error
    if err != nil {
        return err
    }
    return nil
}

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/tcp) 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 (beta) 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 dialog, 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