2023’s Ultimate Guide to Using Golang PostgreSQL

Ever wondered if you could build web applications with PostgreSQL? Yeah, you read it right; even using database applications, you can achieve that. You would just need a helping hand that will increase your efficiency. That is the Go Language or Golang. Using Golang, you can build applications backed by the most powerful open-source Database Management System, i.e., PostgreSQL.

This article will help you comprehensively understand the need for connecting PostgreSQL with Golang. You will also come across a comprehensive overview of all operations involved while implementing the Golang PostgreSQL connection.

Table of Contents

• Prerequisites
• What is Golang?
• Why connect PostgreSQL with Golang?
• Golang PostgreSQL Overview
  • Golang PostgreSQL: Creating a Database
  • Golang PostgreSQL: Connecting Golang to PostgreSQL
  • Golang PostgreSQL: Insert data into Table
  • Golang PostgreSQL: Update data into Table
  • Golang PostgreSQL: Delete data from Table
  • Golang PostgreSQL: Performing Queries
  • Golang PostgreSQL: Retrieving Records
• Conclusion

Prerequisites

• Basic Working knowledge of SQL.
• Understanding of Golang’s “nethttp” package.
• The PostgreSQL server must be installed, configured, and running.
• Golang must be installed properly. To find out what version of Go is currently installed, you can use the following command:

What is Golang?

Go, often known as Golang, is an open-source, compiled, and statically typed programming language developed by Google. It is designed to be simple, fast, readable, and efficient.

Go was created in 2007 at Google to boost programming efficiency in an era of multicore, networked devices, and enormous codebases. The creators aimed to respond to criticism of existing languages used at Google while retaining their desirable characteristics:

• Run-time efficiency and static typing (like C)
• Usability and readability (like Python or JavaScript)
• Multiprocessing and high-performance networking

You can also execute CRUD operations of PostgreSQL using GO.

For further information about Golang, you can visit here.

Why connect PostgreSQL with Golang?

You can create web applications with PostgreSQL by implementing the database in Go (Go Language). It has a package called GORM which communicates with the database created in PostgreSQL thus increasing your efficiency.

GORM is a powerful Object Relational Mapper (ORM) built-in Go that connects to PostgreSQL using the “lib/pq” package.

Golang PostgreSQL Overview

You can go through the following steps to understand the implementation of the Golang PostgreSQL connection.

• Golang PostgreSQL: Creating a Database
• Golang PostgreSQL: Connecting Golang to PostgreSQL
• Golang PostgreSQL: Insert data into Table
• Golang PostgreSQL: Update data into Table
• Golang PostgreSQL: Delete data from Table
• Golang PostgreSQL: Performing Queries
• Golang PostgreSQL: Retrieving Records

1) Golang PostgreSQL: Creating a database

You can create a database in the PostgreSQL shell. In this database, you can further create and execute tables and all other functionalities after connecting Golang PostgreSQL.

CREATE DATABASE DB_1;

Connect to the freshly created database using the meta-command ‘c’ followed by the database name, as illustrated here:

\c DB_1;

Now, after moving to the database, you can create a table within the database using the following command:

CREATE TABLE Students (
Name TEXT,
Roll_number INT PRIMARY KEY,
);

2) Golang PostgreSQL: Connecting Golang to the PostgreSQL Database

Create a file called main.go that can be used to connect Golang to the PostgreSQL database. You can refer to the following Golang script to code the connection information into this file:

• Example 1

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/DB_1?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("The database is connected")
}

• Example 2

package main
 
import (
    "database/sql"
    "fmt"
    _ "github.com/lib/pq"
)
 
const (
    host     = "localhost"
    port     = 5400
    user     = "postgres"
    password = "<password>"
    dbname   = "DB_1"
)
 
func main() {
        // connection string
    psqlconn := fmt.Sprintf("host=%s port=%d user=%s password=%s dbname=%s sslmode=disable", host, port, user, password, dbname)
         
        // open database
    db, err := sql.Open("postgres", psqlconn)
    CheckError(err)
     
        // close database
    defer db.Close()
 
        // check db
    err = db.Ping()
    CheckError(err)
 
    fmt.Println("Connected!")
}
 
func CheckError(err error) {
    if err != nil {
        panic(err)
    }
}

3) Golang PostgreSQL: Insert data into Table

Using Golang along with PostgreSQL, you can create a table and then insert the records into the table. The code written to insert data into the table is as follows:

package main
 
import (
    "database/sql"
    "fmt"
    _ "github.com/lib/pq"
)
 
const (
    host     = "localhost"
    port     = 5400
    user     = "postgres"
    password = "man1234"
    dbname   = "DB_1"
)
 
func main() {
    psqlconn := fmt.Sprintf("host=%s port=%d user=%s password=%s dbname=%s sslmode=disable", host, port, user, password, dbname)
 
    db, err := sql.Open("postgres", psqlconn)
    CheckError(err)
 
    defer db.Close()
 
    // insert
    // hardcoded
    insertStmt := `insert into "Students"("Name", "Roll_Number") values('Jacob', 20)`
    _, e := db.Exec(insertStmt)
    CheckError(e)
 
    // dynamic
    insertDynStmt := `insert into "Students"("Name", "Roll_Number") values($1, $2)`
    _, e = db.Exec(insertDynStmt, "Jack", 21)
    CheckError(e)
}
 
func CheckError(err error) {
    if err != nil {
        panic(err)
    }
}

4) Golang PostgreSQL: Update data into Table

In Golang, you can use the following command to update your data in the table you have created.

// update
updateStmt := `update "Students" set "Name"=$1, "Roll_Number"=$2 where "id"=$3`
_, e := db.Exec(updateStmt, "Rachel", 24, 8)
CheckError(e)

You can check your updated table by using the following command in the PostgreSQL shell.

Select * from Students;

The output will show all the records in the Students table.

5) Golang PostgreSQL: Update data into Table

In Golang, you can use the following command to delete any row in the table you have created.

// Delete
deleteStmt := `delete from "Students" where id=$1`
_, e := db.Exec(deleteStmt, 1)
CheckError(e)

You can check your updated table by using the following command in the PostgreSQL shell.

Select * from Students;

The output will show all the records in the Students table.

6) Golang PostgreSQL: Performing Queries

In Golang, you can use the following command to query the records from your table.

rows, err := db.Query(`SELECT "Name", "Roll_Number" FROM "Students"`)
CheckError(err)
 
defer rows.Close()
for rows.Next() {
    var name string
    var roll_number int
 
    err = rows.Scan(&name, &roll_number)
    CheckError(err)
 
    fmt.Println(name, roll_number)
}
 
CheckError(err)

7) Golang PostgreSQL: Retrieving Records

The different sections while retrieving records using Golang PostgreSQL are as follows:

A) The GoLang struct{} comand

The struct command is used to build a collection of fields that correspond to the fields in a given table. These fields will be used as placeholders for the values of the columns in the table.

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

B) The package main command

The Golang package main command that follows directs the Golang compiler to create the file as an executable file:

package main

C) Importing the Golang dependencies

The following dependencies must be imported to access the methods that will aid with database interaction.  To import the dependencies, you can use the following command:

import (

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

)

Following is a breakdown for importing the Golang dependencies:

• Importing the database/sql allows it to interact idiomatically with the database.
• The pq package, the Golang PostgreSQL driver, is preceded by an underscore _, which instructs Golang to import the package whether or not it is explicitly utilized in the code.
• To perform any sort of formatting in the string displays, the I/O formatting must be employed. This is why the fmt package is required.
• Finally, for client/server communication, import the net/http package.

D) Retrieving a PostgreSQL Record

The code to retrieve a PostgreSQL record is as follows:

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 Students")

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.name, &snb.roll_number)
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.name, snb.roll_number)
}

}

Conclusion

This article illustrated the need of connecting Golang PostgreSQL. You had an in-depth understanding of all the steps involved in implementing the Golang PostgreSQL connection and the different operations executed.

Now, you can move forward and create your application in Golang backed by PostgreSQL.

You can also deep-dive extensively into PostgreSQL by going through the following:

• Django PostgreSQL Connection: 5 Easy Steps
• PostgreSQL Schema Basic Operations

Ingesting and transferring data into your desired warehouse using ETL for Data Analysis may be a time-consuming procedure with PostgreSQL as your data source. The problem gets much more overwhelming when you consider how much money and resources are necessary to hire data engineers and analysts to make sense of this data.

However, with Hevo at your fingertips, you won’t have to worry about your PostgreSQL Data Replication demands. Loading your data into your selected data warehouse will only take a few minutes.

Hevo Data’s excellent integration with 150+ Data Sources (including 40+ Free Sources) such as PostgreSQL allows you to export data from your chosen data sources and load it to your desired destination. It also assists you in transforming and enriching your data so that it is suitable for analysis. You can readily save time and focus on getting insights and doing in-depth research of your data using BI tools.

Now, you can replicate data from PostgreSQL to any data warehouse of your choice such as Amazon Redshift, Snowflake, Google BigQuery, or Firebolt.

Why don’t you give Hevo a try? Sign Up here for a 14-day full feature access trial and experience the feature-rich Hevo suite first hand. You can also check our unbeatable pricing and make a decision on your best-suited plan. 

Share your thoughts on learning about Golang PostgreSQL connection in the comments section below. If you have any questions, do let us know. We’d be happy to help.