This tutorial shows you how build a simple Go application with CockroachDB and the Go pgx driver.

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

You can now run the code sample (main.go) provided in this tutorial to do the following:

  • Create a table in the bank database.
  • Insert some rows into the table you created.
  • Read values from the table.
  • Execute a batch of statements as an atomic transaction.

    Note that CockroachDB may require the client to retry a transaction in the case of read/write contention. The CockroachDB Go client includes a generic retry function (ExecuteTx()) that runs inside a transaction and retries it as needed. The code sample shows how you can use this function to wrap SQL statements.

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"

    "github.com/cockroachdb/cockroach-go/v2/crdb/crdbpgx"
    "github.com/jackc/pgx/v4"
)

func transferFunds(ctx context.Context, tx pgx.Tx, from int, to int, amount int) error {
    // Read the balance.
    var fromBalance int
    if err := tx.QueryRow(ctx,
        "SELECT balance FROM accounts WHERE id = $1", from).Scan(&fromBalance); err != nil {
        return err
    }

    if fromBalance < amount {
        return fmt.Errorf("insufficient funds")
    }

    // Perform the transfer.
    if _, err := tx.Exec(ctx,
        "UPDATE accounts SET balance = balance - $1 WHERE id = $2", amount, from); err != nil {
        return err
    }
    if _, err := tx.Exec(ctx,
        "UPDATE accounts SET balance = balance + $1 WHERE id = $2", amount, to); err != nil {
        return err
    }
    return nil
}

func main() {
    config, err := pgx.ParseConfig("postgres://{username}:{password}@{hostname}:{port}/bank?sslmode=require")
    if err != nil {
        log.Fatal("error configuring the database: ", err)
    }

    config.TLSConfig.ServerName = "localhost"

    // Connect to the "bank" database.
    conn, err := pgx.ConnectConfig(context.Background(), config)
    if err != nil {
        log.Fatal("error connecting to the database: ", err)
    }
    defer conn.Close(context.Background())

    // Create the "accounts" table.
    if _, err := conn.Exec(context.Background(),
        "CREATE TABLE IF NOT EXISTS accounts (id INT PRIMARY KEY, balance INT)"); err != nil {
        log.Fatal(err)
    }

    // Insert two rows into the "accounts" table.
    if _, err := conn.Exec(context.Background(),
        "INSERT INTO accounts (id, balance) VALUES (1, 1000), (2, 250)"); err != nil {
        log.Fatal(err)
    }

    // Print out the balances.
    rows, err := conn.Query(context.Background(), "SELECT id, balance FROM accounts")
    if err != nil {
        log.Fatal(err)
    }
    defer rows.Close()
    fmt.Println("Initial balances:")
    for rows.Next() {
        var id, balance int
        if err := rows.Scan(&id, &balance); err != nil {
            log.Fatal(err)
        }
        fmt.Printf("%d %d\n", id, balance)
    }

    // Run a transfer in a transaction.
    err = crdbpgx.ExecuteTx(context.Background(), conn, pgx.TxOptions{}, func(tx pgx.Tx) error {
        return transferFunds(context.Background(), tx, 1 /* from acct# */, 2 /* to acct# */, 100 /* amount */)
    })
    if err == nil {
        fmt.Println("Success")
    } else {
        log.Fatal("error: ", err)
    }
}

Update the connection parameters

Edit the connection string passed to pgx.ParseConfig() 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:

Initial balances:
1 1000
2 250
Success

To verify that the SQL statements were executed, run the following query from inside the SQL shell:

copy
icon/buttons/copy
> USE bank;
copy
icon/buttons/copy
> SELECT id, balance FROM accounts;

The output should be:

  id | balance
-----+----------
   1 |     900
   2 |     350
(2 rows)

What's next?

Read more about using the Go pgx driver.

You might also be interested in the following pages:



YesYes NoNo