How to Retrieve a Record in CockroachDB using the Go language

Introduction

If you’re a Go developer who’s working with CockroachDB, you may want to write a script to retrieve records from the database. Fortunately, the Go driver for CockroachDB makes this task a simple one. In this tutorial, we’ll show you how to fetch data from CockroachDB with Golang.

Prerequisites

Before we look at the code needed to retrieve a CockroachDB record with Golang, we need to review the prerequisites for this task:

  • First, you’ll need to confirm that CockroachDB is installed and configured.

  • You’ll also need to confirm that the Go language is installed and configured before proceeding.

To see if the Go language is installed on the system, run the following command:

go version

The output should look something like this:

go version go1.12.5 linux/amd64

Go displays both the currently installed version and the OS on which it was installed.

Installing Go pq Driver

Now that we’ve taken care of the system requirements, we’ll install the Go pq driver. Just run the command shown below in the terminal.

go get -u github.com/lib/pq

Creating The Sample Database

We’ll need to create a sample database to use in our code examples. Use the following statements to create the yeshua user and userdatabase database:

-- create user "yeshua"
CREATE USER IF NOT EXISTS yeshua;
-- create database "userdatabase"
CREATE DATABASE userdatabase;

To use the database, you’d use the SQL statement SELECT DATABASE = userdatabase;

For this tutorial, the user “yeshua” will be given read and write permission to the database:

GRANT ALL ON DATABASE userdatabase TO yeshua;

Once the database is created, then a simple table can be created as well. To do this, we’ll use the following SQL statement:

CREATE TABLE tblusers(
id INT PRIMARY KEY,
name STRING,
age INT,
accessLevel STRING,
department STRING,
title STRING
);

Then we’ll put some records in the tblusers table:

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');

Once you’re done setting everything up, use \q to exit the SQL shell.

Using Go pq driver in CockroachDB

In this section, we’ll create a Golang script to show how to connect Go to a CockroachDB database using the Go pq driver.

Let’s start by creating a file and saving it with a “.go” extension. In this example, our file will be saved as retrieve.go.

The package main

In a Golang script, package main simply tells the Go compiler that the package should be compiled as an executable program and not as a shared library:

package main

Importing Dependencies

We’ll need to import a few necessary dependencies, as shown below:

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

_ "github.com/lib/pq"
)
  1. Importing database/sql allows us to use SQL or any SQL-like database in Go.

  2. The fmt package provides I/O formatting functionality like the Println() function.

  3. The log package allows us to perform logging of standard errors .

NOTE: Try not use the driver packages directly in your code; instead, refer to types defined in database/sql to avoid making your code dependent on the driver you are using.

  1. The github.com/lib/pq package pq is Go’s Postgres driver for the aforementioned database/sql package. Note that we use the underscore _ as the package alias to avoid having to use the whole package name in our code.

Accessing the Database

At this point, we’re ready to create a database object (sql.db) to access the database.

We’ll need to use the sql.open() function to create our sql.db object.

Connecting to the CockroachDB Database using the Driver

func main() {

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

Connecting to the CockroachDB Database using database/sql

func main() {

dbConnStr := "user=yeshua dbname=userdatabase host=localhost port=26257 sslmode=disable"

db, err := sql.Open("postgres", dbConnStr)
if err != nil {
log.Fatal(err)
}

defer db.Close()

Let’s see what’s happening in the code shown above:

  1. The first argument passed into sql.Open() is the driver name "postgres". This allows the driver to register itself with database/sql.

  2. The second argument– "postgresql://raizel@localhost:26257/userdatabase?sslmode=disable"— is a driver-specific syntax that tells the driver how to access the given database. The syntax shown above will allow us to connect to the userdatabase within the postgresql instance.

  3. The code that follows handles the errors that our database operation may return.

  4. Finally, the defer db.Close() method ensures that connections will be terminated cleanly when the program exits.

Retrieving All Data From the Database

rows, err := db.Query("SELECT * FROM tblusers") // (1)
if err != nil {
log.Fatal(err)
}
defer rows.Close() // (2)

fmt.Println("Users:")

var id,age int
var name,accessLevel, department, title string

// loop to the rows and display the records
for rows.Next() { // (3)

if err := rows.Scan(&id, &name, &age, &accessLevel, &department, &title ); // (4)
err != nil { // (5)
log.Fatal(err)
}
fmt.Printf("%d %s %d %s %s %s\n", id, name, age, accessLevel, department, title)
}
}

There’s a lot going on in the code shown above. Let’s take a closer look at how it all works:

  1. We use db.Query() to send our query to the database, then we check for errors.
  2. We defer rows.Close() to ensure that resources are deallocated.
  3. After that, we iterate over the rows using rows.Next(). This reads the table columns in each row into variables via rows.Scan().
  4. We check for errors again after iterating over the rows.
  5. Finally, we display the results in a formatted manner using the fmt.Printf().

To test the “retrieve.go” script, navigate to the project directory, then use the following command:

go run retrieve.go

The output should look like the following:

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

Conclusion

If you plan to access CockroachDB from a Golang script, you’ll find that it’s quick and easy to retrieve a record from CockroachDB using Golang. Simply follow the step-by-step instructions provided in this article, and you’ll be able to create your own script to search CockroachDB and return records.

The Code

We’ve looked at our example Go script one section at a time so far. Here’s the code we reviewed in its entirety:

package main

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

_ "github.com/lib/pq"
)

func main() {

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

defer db.Close()

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

Pilot the ObjectRocket Platform Free!

Try Fully-Managed Redis,
MongoDB & Elasticsearch

Get Started

OR

Try CockroachDB
in Beta

Get Started

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.