How to Setup Go pq Driver for CockroachDB

Written by Data Pilot
June 17, 2019
  • CockroachDB
  • Go pq driver
Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

This tutorial will demonstrate how to go about connecting Go language to CockroachDB via Go pq driver. Also referred to as Golang, Go language has a syntax similar to C, however, Go has memory safety and CSP-style concurrency. The main syntax changes make the Go code more succinct and easier to read and understand.

Prerequisites

  • CockroachDB must be properly installed and configured before beginning.

  • Go language must be properly installed and configured before beginning.

Execute the following command to determine if Go is currently installed on the system:

1
go version

The results should resemble the following:

1
go version go1.12.5 linux/amd64

The “go” command displays the currently installed version and identifies the OS Go was installed with. This may vary according to the version being used.

How to Install the Go pq Driver

Run the below command, in the terminal, to install the Go pq driver:

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

How to create a sample database

The following statements will create the raizel user and userdatabase database:

1
2
3
4
-- create user "yeshua"
CREATE USER IF NOT EXISTS yeshua;
-- create database "userdatabase"
CREATE DATABASE userdatabase;

To enable the database, use SELECT DATABASE = userdatabase;

For this tutorial the user “yeshua” will be granted read and write permission to the database with the following command:

1
GRANT ALL ON DATABASE userdatabase TO yeshua;

Enter user q to exit the SQL shell.

Once the database has been created a simple table can be created with the following statement:

1
2
3
4
5
6
7
8
CREATE TABLE tblusers(
id INT PRIMARY KEY,
name STRING,
age INT,
accessLevel STRING,
department STRING,
title STRING
);

Next, add some records in the tblusers as follows:

1
2
3
4
5
INSERT INTO tblusers (id, name, age, accessLevel, department, title) VALUES
(1, 'mark', 22, 'user', 'accounting', 'assistant'),
(2, 'gene', 24, 'user', 'research', 'rankfile'),
(3, 'don', 25, 'admin', 'ict', 'supervisor'),
(4, 'isaac', 20, 'user', 'marketing', 'assistant');

How to use the Go pq driver and CockroachDB

The example file in this section will demonstrate how to connect Go to a CockroachDB database using the Go pq driver.

Create a new file and then save it with a “.go” extension. In this example the file will be saved as sample.go, as shown here:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
package main
// imports necessary dependencies
import (
"database/sql"
"fmt"
"log"

_ "github.com/lib/pq"
)


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

// Selects eery record within the tblusers
rows, err := db.Query("SELECT * FROM tblusers")
if err != nil {
log.Fatal(err)
}
defer rows.Close()
fmt.Println("Users:")

// loop to the rows and display the records
for rows.Next() {
var id,age int
var name,accessLevel, department, title string
if err := rows.Scan(&id, &name, &age, &accessLevel, &department, &title ); err != nil {
log.Fatal(err)
}
fmt.Printf("%d %s %d %s %s %s\n", id, name, age, accessLevel, department, title)
}
}

Now navigate to the project directory the use the following command to test “sample.go”:

1
go run sample.go

The results should resemble the following:

1
2
3
4
1 mark 22 user accounting assistant
2 gene 24 user research rankfile
3 don 25 admin ict supervisor
4 isaac 20 user marketing assistant

The Entire Java Code

The entire java code for this tutorial is as follows:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
package main

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

_ "github.com/lib/pq"
)

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


rows, err := db.Query("SELECT * FROM tblusers")
if err != nil {
log.Fatal(err)
}
defer rows.Close()
fmt.Println("Users:")
for rows.Next() {
var id,age int
var name,accessLevel, department, title string
if err := rows.Scan(&id, &name, &age, &accessLevel, &department, &title ); err != nil {
log.Fatal(err)
}
fmt.Printf("%d %s %d %s %s %s\n", id, name, age, accessLevel, department, title)
}
}

Conclusion

This tutorial explained how to install the Go pq driver, create a sample database, navigate to the project directory and how to go about connecting Go language to CockroachDB via Go pq driver. Bear in mind that both CockroachDB and Go language must be properly installed and configured before attempting to execute the examples in this tutorial. Also remember the “go” command will display the currently installed Go language version and the operating system Go was installed with, however, result may vary slightly with the version being used.

Rate 1 Star2 Stars3 Stars4 Stars5 Stars

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started
Related Topics:
Additional Information:

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.