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

## Before you begin

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

## Step 1. Install the Go pq driver

To install the Go pq driver, run the following command:

    $ go get -u github.com/lib/pq

## Step 2. 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 3. 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 4. Run the Go code

Now that you have a database and a user, you'll run code to create a table and insert some rows, and then you'll run code to read and update values as an atomic transaction.

Tip:

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

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

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

### Basic statements

First, use the following code to connect as the `maxroach` user and execute some basic SQL statements, creating a table, inserting rows, and reading and printing the rows.

Download the `basic-sample.go` file, or create the file yourself and copy the code into it.

    package main

    import (
        "database/sql"
        "fmt"
        "log"

        _ "github.com/lib/pq"
    )

    func main() {
        // Connect to the "bank" database.
        db, err := sql.Open("postgres",
            "postgresql://maxroach@localhost:26257/bank?ssl=true&sslmode=require&sslrootcert=certs/ca.crt&sslkey=certs/client.maxroach.key&sslcert=certs/client.maxroach.crt")
        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.
        rows, err := db.Query("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)
        }
    }

Then run the code:

    $ go run basic-sample.go

The output should be:

    Initial balances:
    1 1000
    2 250

### Transaction (with retry logic)

Next, use the following code to again connect as the `maxroach` user but this time will execute a batch of statements as an atomic transaction to transfer funds from one account to another, where all included statements are either committed or aborted.

Download the `txn-sample.go` file, or create the file yourself and copy the code into it.

    package main

    import (
        "context"
        "database/sql"
        "fmt"
        "log"

        "github.com/cockroachdb/cockroach-go/crdb"
    )

    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",
            "postgresql://maxroach@localhost:26257/bank?ssl=true&sslmode=require&sslrootcert=certs/ca.crt&sslkey=certs/client.maxroach.key&sslcert=certs/client.maxroach.crt")
        if err != nil {
            log.Fatal("error connecting to the database: ", err)
        }
        defer db.Close()

        // 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)
        }
    }

CockroachDB may require the client to retry a transaction in case of read/write contention. CockroachDB provides a generic retry function that runs inside a transaction and retries it as needed. For Go, the CockroachDB retry function is in the `crdb` package of the CockroachDB Go client. To install, clone the library into your `$GOPATH` as follows:

    $ mkdir -p $GOPATH/src/github.com/cockroachdb

    $ cd $GOPATH/src/github.com/cockroachdb

    $ git clone git@github.com:cockroachdb/cockroach-go.git

Then run the code:

    $ go run txn-sample.go

The output should be:

    Success

To verify that funds were transferred from one account to another, use the built-in SQL client:

    $ cockroach sql --certs-dir=certs -e 'SELECT id, balance FROM accounts' --database=bank

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

## Step 2. 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 3. Run the Go code

Now that you have a database and a user, you'll run code to create a table and insert some rows, and then you'll run code to read and update values as an atomic transaction.

Tip:

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

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

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

### Basic statements

First, use the following code to connect as the `maxroach` user and execute some basic SQL statements, creating a table, inserting rows, and reading and printing the rows.

Download the `basic-sample.go` file, or create the file yourself and copy the code into it.

    package main

    import (
        "database/sql"
        "fmt"
        "log"

        _ "github.com/lib/pq"
    )

    func main() {
        // Connect to the "bank" database.
        db, err := sql.Open("postgres", "postgresql://maxroach@localhost:26257/bank?sslmode=disable")
        if err != nil {
            log.Fatal("error connecting to the database: ", err)
        }

        // 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.
        rows, err := db.Query("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)
        }
    }

Then run the code:

    $ go run basic-sample.go

The output should be:

    Initial balances:
    1 1000
    2 250

### Transaction (with retry logic)

Next, use the following code to again connect as the `maxroach` user but this time will execute a batch of statements as an atomic transaction to transfer funds from one account to another, where all included statements are either committed or aborted.

Download the `txn-sample.go` file, or create the file yourself and copy the code into it.

    package main

    import (
        "context"
        "database/sql"
        "fmt"
        "log"

        "github.com/cockroachdb/cockroach-go/crdb"
    )

    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", "postgresql://maxroach@localhost:26257/bank?sslmode=disable")
        if err != nil {
            log.Fatal("error connecting to the database: ", err)
        }

        // 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)
        }
    }

CockroachDB may require the client to retry a transaction in case of read/write contention. CockroachDB provides a generic retry function that runs inside a transaction and retries it as needed. For Go, the CockroachDB retry function is in the `crdb` package of the CockroachDB Go client.

To install the CockroachDB Go client, run the following command:

    $ go get -d github.com/cockroachdb/cockroach-go

Then run the code:

    $ go run txn-sample.go

The output should be:

    Success

To verify that funds were transferred from one account to another, use the built-in SQL client:

    $ cockroach sql --insecure -e 'SELECT id, balance FROM accounts' --database=bank

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

## What's next?

Read more about using the Go pq driver.

You might also be interested in the following pages:

- Client Connection Parameters
- Data Replication
- Fault Tolerance & Recovery
- Replication & Rebalancing
- Cross-Cloud Migration
- Automated Operations