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:

The output should look something like this:

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.

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:

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:

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

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

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:

Importing Dependencies

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

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

Connecting to the CockroachDB Database using database/sql

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

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:

The output should look like the following:

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: