Warning:
This version of CockroachDB is no longer supported. Cockroach Labs supports the current stable release and two releases prior. Please use one of these supported versions.

This tutorial shows you how build a simple Go application with CockroachDB using a PostgreSQL-compatible driver or ORM.

We have tested the Go pq driver and the GORM ORM enough to claim beta-level support, so those are featured here. If you encounter problems, please open an issue with details to help us make progress toward full support.

Tip:

For a more realistic use of GORM with CockroachDB, see our examples-orms repository.

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 GORM ORM

To install GORM, run the following commands:

copy
icon/buttons/copy
$ go get -u github.com/lib/pq # dependency
copy
icon/buttons/copy
$ go get -u github.com/jinzhu/gorm

Step 2. Create the maxroach user and bank database

Start the built-in SQL client:

copy
icon/buttons/copy
$ cockroach sql --certs-dir=certs

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

copy
icon/buttons/copy
> CREATE USER IF NOT EXISTS maxroach;
copy
icon/buttons/copy
> CREATE DATABASE bank;

Give the maxroach user the necessary permissions:

copy
icon/buttons/copy
> GRANT ALL ON DATABASE bank TO maxroach;

Exit the SQL shell:

copy
icon/buttons/copy
> \q

Step 3. Generate a certificate for the maxroach user

Create a certificate and key for the maxroach user by running the following command. The code samples will run as this user.

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

Step 4. Run the Go code

The following code uses the GORM ORM to map Go-specific objects to SQL operations. Specifically, db.AutoMigrate(&Account{}) creates an accounts table based on the Account model, db.Create(&Account{}) inserts rows into the table, and db.Find(&accounts) selects from the table so that balances can be printed.

Copy the code or download it directly.

copy
icon/buttons/copy
package main

import (
    "fmt"
    "log"

    // Import GORM-related packages.
    "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
}

func main() {
    // Connect to the "bank" database as the "maxroach" user.
    const addr = "postgresql://maxroach@localhost:26257/bank?ssl=true&sslmode=require&sslrootcert=certs/ca.crt&sslkey=certs/client.maxroach.key&sslcert=certs/client.maxroach.crt"
    db, err := gorm.Open("postgres", addr)
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // Automatically create the "accounts" table based on the Account model.
    db.AutoMigrate(&Account{})

    // Insert two rows into the "accounts" table.
    db.Create(&Account{ID: 1, Balance: 1000})
    db.Create(&Account{ID: 2, Balance: 250})

    // Print out the balances.
    var accounts []Account
    db.Find(&accounts)
    fmt.Println("Initial balances:")
    for _, account := range accounts {
        fmt.Printf("%d %d\n", account.ID, account.Balance)
    }
}

Then run the code:

copy
icon/buttons/copy
$ go run gorm-basic-sample.go

The output should be:

Initial balances:
1 1000
2 250

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

copy
icon/buttons/copy
$ cockroach sql --certs-dir=certs -e 'SELECT id, balance FROM accounts' --database=bank
+----+---------+
| id | balance |
+----+---------+
|  1 |    1000 |
|  2 |     250 |
+----+---------+
(2 rows)

Step 2. Create the maxroach user and bank database

Start the built-in SQL client:

copy
icon/buttons/copy
$ cockroach sql --insecure

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

copy
icon/buttons/copy
> CREATE USER IF NOT EXISTS maxroach;
copy
icon/buttons/copy
> CREATE DATABASE bank;

Give the maxroach user the necessary permissions:

copy
icon/buttons/copy
> GRANT ALL ON DATABASE bank TO maxroach;

Exit the SQL shell:

copy
icon/buttons/copy
> \q

Step 2. Create the maxroach user and bank database

Start the built-in SQL client:

copy
icon/buttons/copy
$ cockroach sql --insecure

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

copy
icon/buttons/copy
> CREATE USER IF NOT EXISTS maxroach;
copy
icon/buttons/copy
> CREATE DATABASE bank;

Give the maxroach user the necessary permissions:

copy
icon/buttons/copy
> GRANT ALL ON DATABASE bank TO maxroach;

Exit the SQL shell:

copy
icon/buttons/copy
> \q

Step 3. Run the Go code

The following code uses the GORM ORM to map Go-specific objects to SQL operations. Specifically, db.AutoMigrate(&Account{}) creates an accounts table based on the Account model, db.Create(&Account{}) inserts rows into the table, and db.Find(&accounts) selects from the table so that balances can be printed.

Copy the code or download it directly.

copy
icon/buttons/copy
package main

import (
    "fmt"
    "log"

    // Import GORM-related packages.
    "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
}

func main() {
    // Connect to the "bank" database as the "maxroach" user.
    const addr = "postgresql://maxroach@localhost:26257/bank?sslmode=disable"
    db, err := gorm.Open("postgres", addr)
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // Automatically create the "accounts" table based on the Account model.
    db.AutoMigrate(&Account{})

    // Insert two rows into the "accounts" table.
    db.Create(&Account{ID: 1, Balance: 1000})
    db.Create(&Account{ID: 2, Balance: 250})

    // Print out the balances.
    var accounts []Account
    db.Find(&accounts)
    fmt.Println("Initial balances:")
    for _, account := range accounts {
        fmt.Printf("%d %d\n", account.ID, account.Balance)
    }
}

Then run the code:

copy
icon/buttons/copy
$ go run gorm-basic-sample.go

The output should be:

Initial balances:
1 1000
2 250

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

copy
icon/buttons/copy
$ cockroach sql --insecure -e 'SELECT id, balance FROM accounts' --database=bank
+----+---------+
| id | balance |
+----+---------+
|  1 |    1000 |
|  2 |     250 |
+----+---------+
(2 rows)

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 using a local cluster to explore the following CockroachDB benefits:



Yes No