Build a Go App with CockroachDB the Go pq Driver

This tutorial shows you how build a simple Go application with CockroachDB and the Go pq 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.

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

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:

package main

import (
    "context"
    "database/sql"
    "fmt"
    "github.com/cockroachdb/cockroach-go/crdb"
    _ "github.com/lib/pq"
    "log"
)

func printBalances(db *sql.DB) {
    // Print out the balances.
    rows, err := db.Query("SELECT id, balance FROM accounts")
    if err != nil {
        log.Fatal(err)
    }
    defer rows.Close()
    fmt.Println("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)
    }
}

func transferFunds(tx *sql.Tx, from int, to int, amount int) error {
    // Read the balance.
    var fromBalance int
    if err := tx.QueryRow(
        "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(
        "UPDATE accounts SET balance = balance - $1 WHERE id = $2", amount, from); err != nil {
        return err
    }
    if _, err := tx.Exec(
        "UPDATE accounts SET balance = balance + $1 WHERE id = $2", amount, to); err != nil {
        return err
    }
    return nil
}

func main() {
    db, err := sql.Open("postgres", "postgres://{username}:{password}@{hostname}:{port}/bank?sslmode=require")
    if err != nil {
        log.Fatal("error connecting to the database: ", err)
    }

    defer db.Close()

    // Create the "accounts" table.
    if _, err := db.Exec(
        "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 := db.Exec(
        "INSERT INTO accounts (id, balance) VALUES (1, 1000), (2, 250)"); err != nil {
        log.Fatal(err)
    }

    // Print out the balances before an account transfer (below).
    printBalances(db)

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

    // Print out the balances after an account transfer.
    printBalances(db)
}

Update the connection parameters

Edit the connection string passed to sql.Open() 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 passed to sql.Open() with the connection string that you copied earlier from the Connection info dialog.

The function call should look similar to the following:

db, err := sql.Open("postgres", "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:

Balances:
1 1000
2 250
Success
Balances:
1 900
2 350

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

> USE bank;

> SELECT id, balance FROM accounts;

The output should be:

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

The Most Highly Evolved Database on the Planet

CockroachDB

CockroachCloud

Get to know CockroachDB