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.

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

    copy
    icon/buttons/copy
    $ 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.

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

Create a free cluster

  1. Log in to your CockroachCloud account.
  2. On the Clusters page, click Create Cluster.
  3. On the Create your cluster page, select the Free Plan.

    Note:

    This cluster will be free forever.

  4. 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 modal displays. Use the information provided in the modal to set up your cluster connection for the SQL user that was created by default:

  1. Click the name of the cc-ca.crt to download the CA certificate to your local machine.
  2. Create a certs directory on your local machine:

    copy
    icon/buttons/copy
    $ mkdir certs
    
  3. Move the downloaded cc-ca.crt file to the certs directory:

    copy
    icon/buttons/copy
    $ mv <path>/<to>/cc-ca.crt <path>/<to>/certs
    

    For example:

    copy
    icon/buttons/copy
    $ mv Users/maxroach/Downloads/cc-ca.crt Users/maxroach/certs
    
  4. 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.

Step 2. Create a database

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

    copy
    icon/buttons/copy
    > CREATE DATABASE bank;
    
  2. Create a SQL user for your app:

    copy
    icon/buttons/copy
    > 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:

    copy
    icon/buttons/copy
    > 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:

    copy
    icon/buttons/copy
    $ cockroach sql \
    --url='postgres://<username>:<password>@free-tier.gcp-us-central1.cockroachlabs.cloud: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:

    copy
    icon/buttons/copy
    > 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:

copy
icon/buttons/copy
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(*gorm.DB) error {
            return transferFunds(db, 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.

Run the code

Initialize the module:

copy
icon/buttons/copy
$ go mod init basic-sample

Then run the code:

copy
icon/buttons/copy
$ 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