Build a Go App with CockroachDB and GORM

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


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

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


    This cluster will be free forever.

  5. (Optional) Select a cloud provider (GCP or AWS) in the Additional configuration section.

  6. 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:

  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<cluster-id>/cert

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

    curl --create-dirs -o ~/.postgresql/root.crt -O<cluster-id>/cert

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

    mkdir -p $env:appdata\.postgresql\; Invoke-WebRequest -Uri<cluster-id>/cert -OutFile $env:appdata\.postgresql\root.crt

    Your cert file will be downloaded to %APPDATA%/.postgres/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.

  1. If you haven't already, download the CockroachDB binary.
  2. Run the cockroach demo command:

    $ cockroach demo \

    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.


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

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

    # Connection parameters:
    #   (webui)
    #   (sql)      postgres://demo:demo76950@
    #   (sql/unix) postgres://demo:demo76950@?host=%2Fvar%2Ffolders%2Fc8%2Fb_q93vjj0ybfz0fz0z8vy9zc0000gp%2FT%2Fdemo070856957&port=26257

Step 2. Create a database

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

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

  3. Give the user the necessary permissions:

    > GRANT ALL ON DATABASE bank TO <username>;
  1. If you haven't already, download the CockroachDB binary.
  2. 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.

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


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 (

    // Import GORM-related packages.
    _ ""

// 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 {
    defer db.Close()

    // Set to `true` and GORM will print out all DB queries.

    // Automatically create the "accounts" table based on the Account
    // model.

    // 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.

    // 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:

    // Print balances after transfer to ensure that it worked.

    // Delete accounts so we can start fresh when we want to run this
    // program again.

func printBalances(db *gorm.DB) {
    var accounts []Account
    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) 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}"


  • {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.,
  • {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.

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:

YesYes NoNo