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.
For a more realistic use of GORM with CockroachDB, see our examples-orms repository.
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. Install the GORM ORM
To install GORM, run the following commands:
$ go get -u github.com/lib/pq # dependency
$ go get -u github.com/jinzhu/gorm
Step 2. Create the maxroach user and bank database
Start the built-in SQL client:
$ 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. The code samples will run as this user.
$ 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.
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:
$ 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:
$ 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:
$ 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. Create the maxroach user and bank database
Start the built-in SQL client:
$ 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
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.
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:
$ 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:
$ 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:
