For building scalable and efficient applications, integrating with a reliable and robust database system is crucial. PostgreSQL is one such popular relational database management system that forms a formidable duo with the programming language Golang(Go).

In this comprehensive guide, we will take you through the Golang PostgreSQL setup, along with advanced topics such as CRUD(Create, Read, Update, Delete) operations and also performing complex queries. Read on to understand the need to connect PostgreSQL with Golang and harness the power of PostgreSQL into your Golang applications.

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.

Golang PostgreSQL Overview

Here is a Golang PostgreSQL tutorial with a step-by-step process to help you understand the implementation of the Golang PostgreSQL connection.

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 connection 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)
}

}

Why connect PostgreSQL with Golang?

Here are some key reasons to connect PostgreSQL with Golang:

  1. Golang’s performance in building fast and scalable network applications makes it a great fit to take advantage of PostgreSQL’s reliability and scalability as a database server.
  2. PostgreSQL’s ACID compliance provides reliable transactions critical for data integrity. Paired with Golang’s built-in concurrency primitives, both ensure robust data applications.
  3. PostgreSQL’s native JSON and JSONB column types mesh well with Go’s great JSON support to build modern data-driven REST APIs faster.
  4. The golang database/sql package along with postgresql driver provide a simple database/sql interface for programmatic access to PostgreSQL databases avoiding complex querying.
  5. Golang compiles into standalone machine code making lightweight and portable applications that simplify DevOps deployment whether on-premise or cloud (Kubernetes etc).

Conclusion

This article illustrated the need to connect 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:

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.

Visit our Website to Explore Hevo

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.

Manisha Jena
Research Analyst, Hevo Data

Manisha Jena is a data analyst with over three years of experience in the data industry and is well-versed with advanced data tools such as Snowflake, Looker Studio, and Google BigQuery. She is an alumna of NIT Rourkela and excels in extracting critical insights from complex databases and enhancing data visualization through comprehensive dashboards. Manisha has authored over a hundred articles on diverse topics related to data engineering, and loves breaking down complex topics to help data practitioners solve their doubts related to data engineering.

No-code Data Pipeline for PostgreSQL

Try for free