Build a Go App with CockroachDB and upper/db

On this page

Warning:

As of November 12, 2021, CockroachDB v20.1 is no longer supported. For more details, refer to the Release Support Policy.

This tutorial shows you how build a simple Go application with CockroachDB and the upper/db data access layer.

Before you begin

Install CockroachDB.
Start up a secure or insecure local cluster.
Choose the instructions that correspond to whether your cluster is secure or insecure:

Step 1. Create the `maxroach` user and `bank` database

Start the built-in SQL shell:

$ cockroach sql --certs-dir=certs

In the SQL shell, issue the following statements to create the maxroach user and bank database:

> CREATE USER IF NOT EXISTS maxroach;

> CREATE DATABASE bank;

Give the maxroach user the necessary permissions:

> GRANT ALL ON DATABASE bank TO maxroach;

Exit the SQL shell:

> \q

Step 2. Generate a certificate for the `maxroach` user

Create a certificate and key for the maxroach user by running the following command:

$ cockroach cert create-client maxroach --certs-dir=certs --ca-key=my-safe-directory/ca.key

The code samples will run with maxroach as the user.

Step 3. Run the Go code

The sample code shown below uses upper/db to map Go-specific objects to SQL operations. Specifically, the code:

Creates the accounts table, if it does not already exist.
Deletes any existing rows in the accounts table.
Inserts two rows into the accounts table.
Prints the rows in the accounts table to the terminal.
Deletes the first row in the accounts table.
Updates the rows in the accounts table within an explicit transaction.
Prints the rows in the accounts table to the terminal once more.

package main

import (
    "fmt"
    "log"
    "time"

    "github.com/upper/db/v4"
    "github.com/upper/db/v4/adapter/cockroachdb"
)

// The settings variable stores connection details.
var settings = cockroachdb.ConnectionURL{
    Host:     "localhost",
    Database: "bank",
    User:     "maxroach",
    Options: map[string]string{
        // Secure node.
         "sslrootcert": "certs/ca.crt",
         "sslkey":      "certs/client.maxroach.key",
         "sslcert":     "certs/client.maxroach.crt",
    },
}

// Accounts is a handy way to represent a collection.
func Accounts(sess db.Session) db.Store {
    return sess.Collection("accounts")
}

// Account is used to represent a single record in the "accounts" table.
type Account struct {
    ID      uint64 `db:"id,omitempty"`
    Balance int64  `db:"balance"`
}

// Collection is required in order to create a relation between the Account
// struct and the "accounts" table.
func (a *Account) Store(sess db.Session) db.Store {
    return Accounts(sess)
}

// createTables creates all the tables that are neccessary to run this example.
func createTables(sess db.Session) error {
    _, err := sess.SQL().Exec(`
        CREATE TABLE IF NOT EXISTS accounts (
            ID SERIAL PRIMARY KEY,
            balance INT
        )
    `)
    if err != nil {
        return err
    }
    return nil
}

// crdbForceRetry can be used to simulate a transaction error and
// demonstrate upper/db's ability to retry the transaction automatically.
//
// By default, upper/db will retry the transaction five times, if you want
// to modify this number use: sess.SetMaxTransactionRetries(n).
//
// This is only used for demonstration purposes and not intended
// for production code.
func crdbForceRetry(sess db.Session) error {
    var err error

    // The first statement in a transaction can be retried transparently on the
    // server, so we need to add a placeholder statement so that our
    // force_retry() statement isn't the first one.
    _, err = sess.SQL().Exec(`SELECT 1`)
    if err != nil {
        return err
    }

    // If force_retry is called during the specified interval from the beginning
    // of the transaction it returns a retryable error. If not, 0 is returned
    // instead of an error.
    _, err = sess.SQL().Exec(`SELECT crdb_internal.force_retry('1s'::INTERVAL)`)
    if err != nil {
        return err
    }

    return nil
}

func main() {
    // Connect to the local CockroachDB node.
    sess, err := cockroachdb.Open(settings)
    if err != nil {
        log.Fatal("cockroachdb.Open: ", err)
    }
    defer sess.Close()

    // Adjust this number to fit your specific needs (set to 5, by default)
    // sess.SetMaxTransactionRetries(10)

    // Create the "accounts" table
    createTables(sess)

    // Delete all the previous items in the "accounts" table.
    err = Accounts(sess).Truncate()
    if err != nil {
        log.Fatal("Truncate: ", err)
    }

    // Create a new account with a balance of 1000.
    account1 := Account{Balance: 1000}
    err = Accounts(sess).InsertReturning(&account1)
    if err != nil {
        log.Fatal("sess.Save: ", err)
    }

    // Create a new account with a balance of 250.
    account2 := Account{Balance: 250}
    err = Accounts(sess).InsertReturning(&account2)
    if err != nil {
        log.Fatal("sess.Save: ", err)
    }

    // Printing records
    printRecords(sess)

    // Change the balance of the first account.
    account1.Balance = 500
    err = sess.Save(&account1)
    if err != nil {
        log.Fatal("sess.Save: ", err)
    }

    // Change the balance of the second account.
    account2.Balance = 999
    err = sess.Save(&account2)
    if err != nil {
        log.Fatal("sess.Save: ", err)
    }

    // Printing records
    printRecords(sess)

    // Delete the first record.
    err = sess.Delete(&account1)
    if err != nil {
        log.Fatal("Delete: ", err)
    }

    startTime := time.Now()

    // Add a couple of new records within a transaction.
    err = sess.Tx(func(tx db.Session) error {
        var err error

        if err = tx.Save(&Account{Balance: 887}); err != nil {
            return err
        }

        if time.Now().Sub(startTime) < time.Second*1 {
            // Will fail continuously for 2 seconds.
            if err = crdbForceRetry(tx); err != nil {
                return err
            }
        }

        if err = tx.Save(&Account{Balance: 342}); err != nil {
            return err
        }

        return nil
    })
    if err != nil {
        log.Fatal("Could not commit transaction: ", err)
    }

    // Printing records
    printRecords(sess)
}

func printRecords(sess db.Session) {
    accounts := []Account{}
    err := Accounts(sess).Find().All(&accounts)
    if err != nil {
        log.Fatal("Find: ", err)
    }
    log.Printf("Balances:")
    for i := range accounts {
        fmt.Printf("\taccounts[%d]: %d\n", accounts[i].ID, accounts[i].Balance)
    }
}

Note that the sample code also includes a function that simulates a transaction error (crdbForceRetry()). Upper/db's CockroachDB adapter automatically retries transactions when transaction errors are thrown. As a result, this function forces a transaction retry.

To run the code, copy the sample above, or download it directly.

Tip:

To clone a version of the code below that connects to insecure clusters, run the following command:

git clone https://github.com/cockroachlabs/hello-world-go-upperdb/

Note that you will need to edit the connection string to use the certificates that you generated when you set up your secure cluster.

Step 1. Create the `maxroach` user and `bank` database

Start the built-in SQL shell:

$ cockroach sql --insecure

In the SQL shell, issue the following statements to create the maxroach user and bank database:

> CREATE USER IF NOT EXISTS maxroach;

> CREATE DATABASE bank;

Give the maxroach user the necessary permissions:

> GRANT ALL ON DATABASE bank TO maxroach;

Exit the SQL shell:

> \q

Step 2. Run the Go code

The sample code shown below uses upper/db to map Go-specific objects to SQL operations. Specifically, the code:

Creates the accounts table, if it does not already exist.
Deletes any existing rows in the accounts table.
Inserts two rows into the accounts table.
Prints the rows in the accounts table to the terminal.
Deletes the first row in the accounts table.
Updates the rows in the accounts table within an explicit transaction.
Prints the rows in the accounts table to the terminal once more.

package main

import (
    "fmt"
    "log"
    "time"

    "github.com/upper/db/v4"
    "github.com/upper/db/v4/adapter/cockroachdb"
)

// The settings variable stores connection details.
var settings = cockroachdb.ConnectionURL{
    Host:     "localhost",
    Database: "bank",
    User:     "maxroach",
    Options: map[string]string{
        // Insecure node.
        "sslmode": "disable",
    },
}

// Accounts is a handy way to represent a collection.
func Accounts(sess db.Session) db.Store {
    return sess.Collection("accounts")
}

// Account is used to represent a single record in the "accounts" table.
type Account struct {
    ID      uint64 `db:"id,omitempty"`
    Balance int64  `db:"balance"`
}

// Collection is required in order to create a relation between the Account
// struct and the "accounts" table.
func (a *Account) Store(sess db.Session) db.Store {
    return Accounts(sess)
}

// createTables creates all the tables that are neccessary to run this example.
func createTables(sess db.Session) error {
    _, err := sess.SQL().Exec(`
        CREATE TABLE IF NOT EXISTS accounts (
            ID SERIAL PRIMARY KEY,
            balance INT
        )
    `)
    if err != nil {
        return err
    }
    return nil
}

// crdbForceRetry can be used to simulate a transaction error and
// demonstrate upper/db's ability to retry the transaction automatically.
//
// By default, upper/db will retry the transaction five times, if you want
// to modify this number use: sess.SetMaxTransactionRetries(n).
//
// This is only used for demonstration purposes and not intended
// for production code.
func crdbForceRetry(sess db.Session) error {
    var err error

    // The first statement in a transaction can be retried transparently on the
    // server, so we need to add a placeholder statement so that our
    // force_retry() statement isn't the first one.
    _, err = sess.SQL().Exec(`SELECT 1`)
    if err != nil {
        return err
    }

    // If force_retry is called during the specified interval from the beginning
    // of the transaction it returns a retryable error. If not, 0 is returned
    // instead of an error.
    _, err = sess.SQL().Exec(`SELECT crdb_internal.force_retry('1s'::INTERVAL)`)
    if err != nil {
        return err
    }

    return nil
}

func main() {
    // Connect to the local CockroachDB node.
    sess, err := cockroachdb.Open(settings)
    if err != nil {
        log.Fatal("cockroachdb.Open: ", err)
    }
    defer sess.Close()

    // Adjust this number to fit your specific needs (set to 5, by default)
    // sess.SetMaxTransactionRetries(10)

    // Create the "accounts" table
    createTables(sess)

    // Delete all the previous items in the "accounts" table.
    err = Accounts(sess).Truncate()
    if err != nil {
        log.Fatal("Truncate: ", err)
    }

    // Create a new account with a balance of 1000.
    account1 := Account{Balance: 1000}
    err = Accounts(sess).InsertReturning(&account1)
    if err != nil {
        log.Fatal("sess.Save: ", err)
    }

    // Create a new account with a balance of 250.
    account2 := Account{Balance: 250}
    err = Accounts(sess).InsertReturning(&account2)
    if err != nil {
        log.Fatal("sess.Save: ", err)
    }

    // Printing records
    printRecords(sess)

    // Change the balance of the first account.
    account1.Balance = 500
    err = sess.Save(&account1)
    if err != nil {
        log.Fatal("sess.Save: ", err)
    }

    // Change the balance of the second account.
    account2.Balance = 999
    err = sess.Save(&account2)
    if err != nil {
        log.Fatal("sess.Save: ", err)
    }

    // Printing records
    printRecords(sess)

    // Delete the first record.
    err = sess.Delete(&account1)
    if err != nil {
        log.Fatal("Delete: ", err)
    }

    startTime := time.Now()

    // Add a couple of new records within a transaction.
    err = sess.Tx(func(tx db.Session) error {
        var err error

        if err = tx.Save(&Account{Balance: 887}); err != nil {
            return err
        }

        if time.Now().Sub(startTime) < time.Second*1 {
            // Will fail continuously for 2 seconds.
            if err = crdbForceRetry(tx); err != nil {
                return err
            }
        }

        if err = tx.Save(&Account{Balance: 342}); err != nil {
            return err
        }

        return nil
    })
    if err != nil {
        log.Fatal("Could not commit transaction: ", err)
    }

    // Printing records
    printRecords(sess)
}

func printRecords(sess db.Session) {
    accounts := []Account{}
    err := Accounts(sess).Find().All(&accounts)
    if err != nil {
        log.Fatal("Find: ", err)
    }
    log.Printf("Balances:")
    for i := range accounts {
        fmt.Printf("\taccounts[%d]: %d\n", accounts[i].ID, accounts[i].Balance)
    }
}

Copy the code or download it directly.

Tip:

To clone a version of the code below that connects to insecure clusters, run the following command:

git clone https://github.com/cockroachlabs/hello-world-go-upperdb/

Change to the directory where you cloned the repo and get the dependencies with go mod init:

$ go mod init hello-world-go-upperdb

Then run the code:

$ go run main.go

The output should look similar to the following:

go: finding module for package github.com/upper/db/v4
go: finding module for package github.com/upper/db/v4/adapter/cockroachdb
go: found github.com/upper/db/v4 in github.com/upper/db/v4 v4.0.0
2020/09/16 10:31:55 Balances:
    accounts[590467288222990337]: 1000
    accounts[590467288229576705]: 250
2020/09/16 10:31:55 Balances:
    accounts[590467288222990337]: 500
    accounts[590467288229576705]: 999
2020/09/16 10:31:55 upper/db: log_level=WARNING file=go/pkg/mod/github.com/upper/db/v4@v4.0.0/internal/sqladapter/session.go:642
    Session ID:     00006
    Transaction ID: 00005
    Query:          SELECT crdb_internal.force_retry('1ms'::INTERVAL)
    Error:          pq: restart transaction: crdb_internal.force_retry(): TransactionRetryWithProtoRefreshError: forced by crdb_internal.force_retry()
    Time taken:     0.00171s
    Context:        context.Background

2020/09/16 10:31:55 upper/db: log_level=WARNING file=go/pkg/mod/github.com/upper/db/v4@v4.0.0/internal/sqladapter/session.go:642
    Session ID:     00006
    Transaction ID: 00005
    Query:          INSERT INTO "accounts" ("balance") VALUES ($1) RETURNING "id"
    Arguments:      []interface {}{887}
    Error:          pq: current transaction is aborted, commands ignored until end of transaction block
    Time taken:     0.00065s
    Context:        context.Background

2020/09/16 10:31:56 Balances:
    accounts[590467288229576705]: 999
    accounts[590467288342757377]: 887
    accounts[590467288350064641]: 342

Note that the forced transaction errors result in errors printed to the terminal, but the transactions are retried until they succeed.

Cockroach
University

Docs Hub

Build a Go App with CockroachDB and upper/db

Before you begin

Step 1. Create the `maxroach` user and `bank` database

Step 2. Generate a certificate for the `maxroach` user

Step 3. Run the Go code

Step 1. Create the `maxroach` user and `bank` database

Step 2. Run the Go code

What's next?

Cockroach University

Docs Hub

Cockroach University

Docs Hub

Build a Go App with CockroachDB and upper/db

Before you begin

Step 1. Create the maxroach user and bank database

Step 2. Generate a certificate for the maxroach user

Step 3. Run the Go code

Step 1. Create the maxroach user and bank database

Step 2. Run the Go code

What's next?

Cockroach
University

Cockroach
University

Step 1. Create the `maxroach` user and `bank` database

Step 2. Generate a certificate for the `maxroach` user

Step 1. Create the `maxroach` user and `bank` database