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:
1 | go version |
The output should look something like this:
1 | 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.
1 | 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:
1 2 3 4 | -- 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:
1 | 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:
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 ); |
Then we’ll put some records in the tblusers
table:
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'); |
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:
1 | package main |
Importing Dependencies
We’ll need to import a few necessary dependencies, as shown below:
1 2 3 4 5 6 7 | import ( "database/sql" "fmt" "log" _ "github.com/lib/pq" ) |
Importing
database/sql
allows us to use SQL or any SQL-like database in Go.The
fmt
package provides I/O formatting functionality like the Println() function.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.
- The
github.com/lib/pq
packagepq
is Go’s Postgres driver for the aforementioneddatabase/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
1 2 3 4 5 6 | 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
1 2 3 4 5 6 7 8 9 10 | 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:
The first argument passed into
sql.Open()
is the driver name"postgres"
. This allows the driver to register itself withdatabase/sql
.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 theuserdatabase
within thepostgresql
instance.The code that follows handles the errors that our database operation may return.
Finally, the
defer db.Close()
method ensures that connections will be terminated cleanly when the program exits.
Retrieving All Data From the Database
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | 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:
- We use
db.Query()
to send our query to the database, then we check for errors. - We defer
rows.Close()
to ensure that resources are deallocated. - After that, we iterate over the rows using
rows.Next()
. This reads the table columns in each row into variables viarows.Scan()
. - We check for errors again after iterating over the rows.
- 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:
1 | go run retrieve.go |
The output should look like 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 |
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:
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 | 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 CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started