How to Setup Go pq Driver for CockroachDB
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:
The results should resemble the following:
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:
How to create a sample database
The following statements will create the raizel user and userdatabase database:
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:
Enter user q to exit the SQL shell.
Once the database has been created a simple table can be created with the following statement:
id INT PRIMARY KEY,
name STRING,
age INT,
accessLevel STRING,
department STRING,
title STRING
);
Next, add some records in the tblusers as follows:
(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:
// 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”:
The results should resemble the following:
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:
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.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started