Web App with Go and PostgreSQL - How to Retrieve Records in PostgreSQL using GoLang

Introduction

This tutorial will explain how to use the web app with Go and PostgreSQL to retrieve a record in a PostgreSQL database and will also provide an explanation on how to create “routes” and “listeners.” Being able to use the Golang script to retrieve a PostgreSQL record is a very useful function when retrieving records for update purposes.

Prerequisites to use the Web App with Go and PostgreSQL to Retrieve a Record in a PostgreSQL

  • The PostgreSQL server must be properly installed, configured and running.

  • Golang must be properly installed. Execute the following command to determine the currently installed version of Go:

go version

The results should resemble the following:

go version go1.13 darwin/amd64

*A basic working knowledge of SQL and an understanding of the GoLang’s “nethttp” package.

How to Create a Sample Database

Create a sample database, that will be used for executing the examples used in this tutorial, with the following command in the PostgreSQL shell.

CREATE DATABASE retrieveTest;

Now connect to the newly created database via the meta-command ‘c’ followed by the name of the database as shown here:

\c retrieveTest;

Now execute the following command to create the table columns within the database:

CREATE TABLE tblretrieveTest (
id SERIAL PRIMARY KEY,
Firstname TEXT,
Lastname TEXT,
Age INT
);

Now add some records with the following commands:

INSERT INTO tblretrieveTest (Firstname, Lastname, Age) VALUES
('abishai','galisanao', 2),
('yeshua', 'galisanao', 8),
('raizel','galisanao', 16),
('risa','galisanao', 37);

Now perform a basic SELECT operation to see if the INSERT operation was successful. As shown below, the records should be visible within the tblretrievetest:

id | firstname | lastname | age
----+-----------+-----------+-----
1 | abishai | galisanao | 2
2 | yeshua | galisanao | 8
3 | raizel | galisanao | 16
4 | risa | galisanao | 37
(4 ROWS)

How to connect Golang to the PostgreSQL database

Connect Golang to PostgreSQL database by first creating a file named main.go. Code the connection details inside this file using the following Golang script:

package main

import (
"fmt"
"database/sql"
"net/http"
"log"
_ "github.com/lib/pq"
)

var db *sql.DB

// This function will make a connection to the database only once.
func init() {
var err error

connStr := "postgres://postgres:password@localhost/retrieveTest?sslmode=disable"
db, err = sql.Open("postgres", connStr)

if err != nil {
panic(err)
}

if err = db.Ping(); err != nil {
panic(err)
}
// this will be printed in the terminal, confirming the connection to the database
fmt.Println("Connected to database")
}

The above Golang script connects Golang to the PostgreSQL database named retrievetest. For a detailed discussion of the above script, consult the link: Web App with Go and PostgreSQL : Connecting GoLang to PostgreSQL

How to Retrieve All the Records in PostgreSQL using Golang Script

This section will explain how to retrieve all PostgreSQL records with Golang.

The GoLang struct{} comand

The struct{} command is used to create a compilation of fields that match the fields within a given table. These fields will be used as a placeholder for the values of the fields of the table.

type sandbox struct {
id int
Firstname string
Lastname string
Age int
}

The package main command

The following Golang package main command instructs the Golang compiler to compose the file as an executable file:

package main

How to import the Golang dependencies

The following dependencies must be imported in order to access the methods that will assist in interacting with the database. Execute the following command to import the dependencies:

import (

"database/sql"
_ "github.com/lib/pq"
"fmt"
"net/http"

)

Following is a breakdown for importing the Golang dependencies:

  • Importing the database/sql enables it to interact with the database idiomatically.
  • The pq package, the Golang PostgreSQL driver, is prefixed with an underscore _ that tells Golang to import the package whether or not it is being used directly in the code.
  • The I/O formatting must be used in order to execute any type of formatting in the string displays. This is why the fmt package must be imported.
  • Finally, import the net/http package for client/server communication.

NOTE: In order to keep the driver from becoming code dependent, only the types defined in the database/sql should be used.

How to use the Golang script to retrieve a PostgreSQL record

The following Golang script will retrieve all of the PostgreSQL records within the table and display the results in the browser:

func retrieveRecord(w http.ResponseWriter, r *http.Request) {

// checks if the request is a "GET" request
if r.Method != "GET" {
http.Error(w, http.StatusText(405), http.StatusMethodNotAllowed)
return
}

// We assign the result to 'rows'
rowsRs, err := db.Query("SELECT * FROM tblretrieveTest")

if err != nil {
http.Error(w, http.StatusText(500), http.StatusInternalServerError)
return
}
defer rowsRs.Close()


// creates placeholder of the sandbox
snbs := make([]sandbox, 0)


// we loop through the values of rows
for rows.Next() {
snb := sandbox{}
err := rowsRs.Scan(&snb.id, &snb.Firstname, &snb.Lastname, &snb.Age)
if err != nil {
log.Println(err)
http.Error(w, http.StatusText(500), 500)
return
}
snbs = append(snbs, snb)
}

if err = rowsRs.Err(); err != nil {
http.Error(w, http.StatusText(500), 500)
return
}

// loop and display the result in the browser
for _, snb := range snbs {
fmt.Fprintf(w, "%d %s %s %d\n", snb.id, snb.Firstname, snb.Lastname, snb.Age)
}

}

The Golang entry point and the func main( ).

This section will describes the entry point of the program via the func main() command. The following code will create “routes” and “listeners:”

func main() {
http.HandleFunc("/retrieve", retrieveRecord) // (1)
http.ListenAndServe(":8080", nil) // (2)
}
  • Note that the router http.HandleFunc() will point the program to the functions that are to be executed after it receives a valid parameter in the browser.

The http.ListenAndServe() will indicate what port the server will listen to.

To test the script, navigate to the project directory and execute the following command:

go run main.go

The results should resemble the following:

You are now connected to the database.

The terminal will confirm the connection to the database, that it is listening to port “:8080” and waiting for request. An argument can then be sent through the browser to retrieve all records using the following URL: http://localhost:8080/retrieve

The results should resemble the following:

Cool, your connected to the browser 1 abishai galisanao 2 2 yeshua galisanao 8 3
raizel galisanao 16 4 risa galisanao 37

The image shows a browser showing all the records in the PostgreSQL database using GoLang script

Conclusion

This tutorial explained how to use the web app with Go and PostgreSQL to retrieve a record in a PostgreSQL database. The article specifically covered the prerequisites needed to retrieve a record in a PostgreSQL using Go, how to create sample database, how to connect Golang to the PostgreSQL database and how to retrieve all the records in PostgreSQL using the Golang Script. The tutorial also covered the GoLang struct{} command and explained how to import the Golang dependencies. Remember that the Golang dependencies must be imported in order to access the methods to interact with the database. Only the types defined in the database/sql should be used to prevent the driver from becoming code dependent.

Just the Code

Following is the working code in its entirety:

package main

import (
"database/sql"
_ "github.com/lib/pq"
"fmt"
"net/http"
)

var db *sql.DB
var err error

func init() {

connStr := "postgres://postgres:password@localhost/retrievetest?sslmode=disable"
db, err = sql.Open("postgres", connStr)

if err != nil {
panic(err)
}

if err = db.Ping(); err != nil {
panic(err)
}
// this will be printed in the terminal, notifying that we successfully connected to our database
fmt.Println("You are now connected to the database.")
}


func main() {
http.HandleFunc("/retrieve", retrieveRecord)
http.ListenAndServe(":8080", nil)
}

func retrieveRecord(w http.ResponseWriter, r *http.Request)
{

fmt.Fprintf(w, "Cool, your connected to the browser \n")

if r.Method != "GET" {
http.Error(w, http.StatusText(405), http.StatusMethodNotAllowed)
return
}

rows, err := db.Query("SELECT * FROM tblretrieveTest")

if err != nil {
http.Error(w, http.StatusText(500), http.StatusInternalServerError)
return
}
defer rows.Close()

snbs := make([]sandbox, 0)

for rows.Next() {
snb := sandbox{}
err := rows.Scan(&snb.id, &snb.Firstname, &snb.Lastname, &snb.Age)
if err != nil {
log.Println(err)
http.Error(w, http.StatusText(500), 500)
return
}
snbs = append(snbs, snb)
}

if err = rows.Err(); err != nil {
http.Error(w, http.StatusText(500), 500)
return
}

for _, snb := range snbs {
fmt.Fprintf(w, "%d %s %s %d\n", snb.id, snb.Firstname, snb.Lastname, snb.Age)
}

}

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.