Web App with Go and PostgreSQL - Connecting GoLang to PostgreSQL

Introduction

GoLang (also known as Go) is an easy-to-understand programming language that many developers appreciate. PostgreSQL, a database management system. It’s open source and integrates well with GoLang. When connected, you get fast querying and response times. The right steps must be applied, however, to enable communication between the two applications. Learn how to properly perform the web app with Go and PostgreSQL connection so that all of your transactions are successful.

If you’re already familiar with the detailed steps on how to use the web app with Go and PostgreSQL, you can skip these steps and go straight to Just the Code.

Prerequisites

  • Install PostgreSQL, and run it in the background.

  • Install the binary Go language for your operating system.

  • After that, check the version of GoLang by entering this command:

go version
  • The result should look something like this:
go version go1.13 darwin/amd64

Make a PostgreSQL database sample

Create a PostgreSQL sample database to test some of the examples given in this tutorial.

  • Use this command to create your database for the web app with Go and PostgreSQL:
CREATE DATABASE devdatabase;
  • Construct a simple table like this one below and call it user_info:
CREATE TABLE tbluserInfo (
id SERIAL PRIMARY KEY,
Age INT,
Email TEXT UNIQUE NOT NULL,
Firstname TEXT,
Lastname TEXT
);
  • Next, put a few records in your new user_info table:
INSERT INTO tbluserInfo (Age, Email, Firstname, Lastname) VALUES
(18,'raizel@example.io','raizel','mendex'),
(21,'dorian@startup.io','dorian','yates');

When finished, here is what your table named userInfo should look like:

id | userAge | userEmail | userFirstname | userLastname
----+---------+-------------------+---------------+---------------
1 | 18 | raizel@example.io | raizel | mendex
2 | 21 | dorian@startup.io | dorian | yates
(2 ROWS)

Make the PostgreSQL and GoLang connection and perform a query

  • In your project directory, make a new file. Name the file main.go when you save it.

NOTE: You want to use .go as the GoLang file extension to represent the programming language.

The purpose of the ‘main’ package

  • The main package communicates to the Go compiler that the script is a program that is executable. This is the opposite of a library that is shared.
package main

Perform an importation of dependencies for GoLang

  • Use the method import() to get the required dependencies that go along with GoLang:
import (
"fmt"
"database/sql"
_ "github.com/lib/pq"
"net/http"

)

Here are the explanations of the lines of code:

  • The package fmt makes it possible for you to use formatting I/O.

  • The package database/sql is the proper way to use any database in GoLang that is SQL-based.

  • The driver gets imported with the library package "github.com/lib/pq". Notice in the above script, the code doesn’t associate the importation automatically. Make an adjustment by putting an underscore ( _ ) before the package.

  • A net/http package importation is executed to use implementations of the server and HTTP client.

Make a database connection

  • First, create a package that is accessible globally. In this example, the sql.DB package is the object of the db dereferenced pointer.
var db *sql.DB
  • Construct a declaration string for the specifics needed for the web app with Go and PostgreSQL database connection:
connStr := "postgres://postgres:password@localhost/devdatabase?sslmode=disable"
  • Lastly, create a function init() to use only one time to set up the database:
var err error
// connection string
connStr := "postgres://postgres:password@localhost/devdatabase?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 devdatabase database.")

To explain the code above, the sql.open function is used to make the connection open. Afterward, db is assigned a variable. Check for any raised exceptions.

NOTE: You will receive this raised exception http: panic serving [::1]:52686: runtime error: invalid memory address or nil pointer dereference goroutine 35 [running]: if the variable you assigned to db isn’t global.

  • Go in the func main() and test out the connection to the web app with Go and PostgreSQL database like this:
func main(){
http.ListenAndServe(":8081", nil)
}
  • Save the file.

  • Use this command in the project directory:

go run main.go
  • The result should look like this:
You are now connected to the devdatabase database

Run a simple query

  • Test out the web app with Go and PostgreSQL connection like this:
func storeIndex(w http.ResponseWriter, r *http.Request) {

fmt.Fprintf(w, "Cool, you're connected to the browser n")

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

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

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

usrs := make([]userInformation, 0)

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

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

for _, usr := range usrs {
fmt.Fprintf(w, "%d %d %s %s %sn", usr.id, usr.Age, usr.Email, usr.Firstname, usr.Lastname)
}

}
  • Put the struct data type in to group the data. It goes ahead of the func Main():
type userInformation struct {
id int
Age int
Email string
Firstname string
Lastname string
}
  • Next, add the http.HandleFunc for the router’s range of operation. It goes in the func Main() like this:
http.HandleFunc("/store", storeIndex)

Here’s the type of response of which you should see a resemblance:

Image showing a successful retrieval of PostgreSQL record using GoLang

CONCLUSION

In this tutorial, you learned how to bridge the web app with Go and PostgreSQL so that they are connected. You created a sample database with a table that included data, and then you ran a basic query. The result is you were successful in retrieving a record.

PostgreSQL and GoLang are two open-source programs that get along like two peas in a proverbial pod. It’s up to you to take advantage of them. Refer to these instructions regularly to increase the percentage of your scripting language and database connections. Code on.

Just the Code

Here’s the entire sample code for the web app with Go and PostgreSQL connection.

package main

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

_ "github.com/lib/pq"
)


var db *sql.DB

func init() {
var err error

connStr := "postgres://postgres:password@localhost/devdatabase?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 devdatabase database.")
}

type userInformation struct {
id int
Age int
Email string
Firstname string
Lastname string
}

func main() {
http.HandleFunc("/store", storeIndex)
http.ListenAndServe(":8081", nil)
}

func storeIndex(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 tbluserInfo")

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

usrs := make([]userInformation, 0)

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

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

for _, usr := range usrs {
fmt.Fprintf(w, "%d %d %s %s %sn", usr.id, usr.Age, usr.Email, usr.Firstname, usr.Lastname)
}

}

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.