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
- 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?
Image Source
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.
As the ability of businesses to collect data explodes, data teams have a crucial role to play in fueling data-driven decisions. Yet, they struggle to consolidate the scattered data in their warehouse to build a single source of truth. Broken pipelines, data quality issues, bugs and errors, and lack of control and visibility over the data flow make data integration a nightmare.
1000+ data teams rely on Hevo’s Data Pipeline Platform to integrate data from over 150+ sources in a matter of minutes. Billions of data events from sources as varied as SaaS apps, Databases, File Storage, and Streaming sources can be replicated in near real-time with Hevo’s fault-tolerant architecture. What’s more – Hevo puts complete control in the hands of data teams with intuitive dashboards for pipeline monitoring, auto-schema management, and custom ingestion/loading schedules.
All of this combined with transparent pricing and 24×7 support makes us the most loved data pipeline software on review sites.
Take our 14-day free trial to experience a better way to manage data pipelines.
Get started for Free with Hevo!
Golang PostgreSQL Overview
You can go through the following steps to 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:
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")
}
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:
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.