How to Retrieve a Single Record in PostgreSQL Database using Go Lang's database/sql Package

Introduction

If you’re interacting with PostgreSQL using a Go script, it’s important to know how to perform certain basic operations such as retrieving records. With the help of the database/sql package in Golang, retrieving a record from PostgreSQL is a simple task. In this article, we’ll show you how to retrieve a single record from a PostgreSQL database using Go Lang’s database/sql package.

Prerequisites

Before we delve into the details of our script, we’ll need to review a few important prerequisites for this tutorial:

  • You’ll need to ensure that PostgreSQL server is properly installed, configured and running in the background.

  • You’ll also need to ensure that Golang is properly installed on your machine. To check if you have Golang installed and to see what version it is, use the following command:

go version

You’ll see output that looks like the following:

go version go1.13 darwin/amd64
  • It’s helpful to have some basic SQL knowledge in order to follow along with the examples in this article.

How to Install the Go Lang Package pq

Now that we’ve confirmed that all our prerequisites are in place, we can install the Go PostgreSQL driver using the following command:

go get -u github.com/lib/pq

Creating Sample Data Set

We’ll need to create a sample data set before we can attempt to retrieve a record using our Go script. First, we’ll create a table named team_personnel as shown below:

CREATE TABLE team_personnel (
id SERIAL PRIMARY KEY,
tp_age INT,
tp_firstname TEXT,
tp_lastname TEXT,
tp_email TEXT UNIQUE NOT NULL
);

Then, we insert some records in the table:

INSERT INTO team_personnel (tp_age, tp_email, tp_firstname, tp_lastname) VALUES
(37,'rommel@teamsolo.io','rommel','galisanao'),
(37,'risa@teamsolo.io','risa','galisanao'),
(16,'raizel@teamsolo.io','raizel','galisanao');

At this point, we have a table containing the following records:

id | tp_age | tp_firstname | tp_lastname | tp_email
----+--------+--------------+-------------+--------------------
1 | 37 | rommel | galisanao | rommel@teamsolo.io
2 | 37 | risa | galisanao | risa@teamsolo.io
3 | 16 | raizel | galisanao | raizel@teamsolo.io
(3 ROWS)

Retrieve One Record from a PostgreSQL Database

In this section, we’re going to retrieve a single record from our PostgreSQL database. This is something we normally do when we want to verify if an insert operation was successful. To accomplish this task, we’ll be using the QueryRow() method, since it returns one row when executed.

We expect to retrieve a single row, which means that we’ll get an ErrNoRows error if no rows are returned. Be aware that the Query() method doesn’t throw an error in cases where zero rows were returned.

Let’s look at the code shown below to see how this works:

// (1)
sqlSelect := `SELECT tp_firstname, tp_lastname, tp_email FROM team_personnel WHERE id=$1;`
var mail string
var firstname string
var lastname string


str1 := "first name:"
str2 := "email:"

// (2)
row := db.QueryRow(sqlSelect, 2)

// (3)
switch err := row.Scan(&firstname, &lastname,&mail,); err {
case sql.ErrNoRows:
fmt.Print("There is no retrieved rows, dummy!")
case nil:
fmt.Print(str1, firstname ," ", str2, mail, "\n")
default:
panic(err)

There’s quite a bit going on in this code, so let’s discuss it one part at a time:

  1. This part of the code creates a SQL statement that will select all the specified fields from the team_personnel table for the record having an id equal to the value passed in the variable $1.

  2. Next, we perform our query using the QueryRow() method against the variable of db. For the first parameter, we use the sqlSelect. After that, we pass in data as an additional argument; in this case, it’s 3. We expect to retrieve a row after the specified query is executed.

  3. This part of the code is intended to perform two actions:

  • It calls the row.Scan() method and passes in the variables as an argument. This instructs Scan() to have a copy of the data retrieved within memory and will return an error if it fails.

  • It enables us to implement cases by using the switch statement. We can see this demonstrated in the code below:

case sql.ErrNoRows:
fmt.Print("There are no retrieved rows, dummy!")
case nil:
fmt.Print(str1, firstname ," ", str2, mail, "\n")
default:
panic(err)

Let’s take a closer look at these lines of code:

  1. In the first case in the code, we anticipate that there will no retrieved rows, and we simply print out “There are no retrieved rows, dummy!”.

  2. The second case returns combined values of strings and retrieved values for visual presentation.

  3. Finally, we panic in the event we encounter an unexpected error. This isn’t the ideal way to handle errors, but it will suffice for the purposes of our tutorial.

Running the above code will return something like this:

You are Successfully connected!
first name: risa email: risa@teamsolo.io

The output shows that we have successfully connected and were able to retrieve the values of the record with an id of 2.

Conclusion

If you’re working with Golang and PostgreSQL, you’ll find that there are many database operations you can perform using a Go script. One common database task involves retrieving records. This tutorial showed how to retrieve a single record from a PostgreSQL database using Go Lang’s database/sql package. With the example code as a guide, you’ll be able to create a Go script for your own database applications.

The Code

Shown below is the complete script used to retrieve a single record from a PostgreSQL database using Go Lang’s database/sql package:

package main

import (
"database/sql"
"fmt"

_ "github.com/lib/pq"
)

const (
host = "localhost"
port = 5432
user = "postgres"
password = ""
dbname = "testdatabase"
)
var db *sql.DB

func main() {
var err error

psqlInfo := fmt.Sprintf("host=%s port=%d user=%s "+
"password=%s dbname=%s sslmode=disable",
host, port, user, password, dbname)
db, err := sql.Open("postgres", psqlInfo)



if err != nil {
panic(err)
}
defer db.Close()

err = db.Ping()
if err != nil {
panic(err)
}

fmt.Println("You are Successfully connected!")
sqlSelect := `SELECT tp_firstname, tp_lastname, tp_email FROM team_personnel WHERE id=$1;`
var mail string
var firstname string
var lastname string


str1 := "first name: "
str2 := "email: "

// (2)
row := db.QueryRow(sqlSelect, 2)

// (3)
switch err := row.Scan(&firstname, &lastname,&mail,); err {
case sql.ErrNoRows:
fmt.Print("There is no retrieved rows, dummy!")
case nil:
fmt.Print(str1, firstname ," ", str2, mail, "\n")
default:
panic(err)
}
}

Pilot the ObjectRocket Platform Free!

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

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.