Golang PostgreSQL Example

PostgreSQL is as much popular as MySQL and provides similar features. It also comes with some of the best techniques readily available out of the box. In this post, we are going to discuss PostgreSQL in Golang and create simple CRUD operations in Go.

Installing PostgreSQL

Installing PostgreSQL is pretty straightforward, clicking on the download will lead to the distributions list for different OSes. Then you can choose what fits your OS. When the installation is done, you can confirm it using the terminal.

postgres -V

This will output the version like shown below:

Now, the software will come with both the GUI and the command-line version. For the GUI open the pgAdmin software and the passwords that you have set will need to be entered.

To open the CLI tool:

psql -U postgres

Here the Postgres is the default user already created before. A prompt will appear for password and after entering the password, it will look like this:

Now, in the CLI or the GUI we can start creating tables and insert data and all that there is.

Create a database in Postgres

Typing \l in the prompt will show all the databases Postgres currently have. To create a database, we can simply use the following command from the CLI:

create database <dbname>;

Now, we can create tables using the create table command.

To create database from the GUI here are the steps:

Then after right clicking and selecting the table option a prompt will appear where we can create table.

Connecting to PostgreSQL Database using Go

Here is the code that will check if we have connected successfully. Now, after checking that we can move on to inserting data into table.

We, need to install the pq package using go package manager:

go get github.com/lib/pq

package main

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

const (
	host     = "localhost"
	port     = 5432
	user     = "postgres"
	password = "<password>"
	dbname   = "<dbname>"
)

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

1. Insert data into table

Here, first, we create a simple table consisting of only 2 columns name and roll. Now, to insert or update data we need to use a prepared statement. This is what makes the database operations secure.

package main

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

const (
	host     = "localhost"
	port     = 5432
	user     = "postgres"
	password = "rusho1234"
	dbname   = "MyDB"
)

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") values('John', 1)`
	_, e := db.Exec(insertStmt)
	CheckError(e)

	// dynamic
	insertDynStmt := `insert into "Students"("Name", "Roll") values($1, $2)`
	_, e = db.Exec(insertDynStmt, "Jane", 2)
	CheckError(e)
}

func CheckError(err error) {
	if err != nil {
		panic(err)
	}
}

After the program runs, we get the result:

2. Update data in table

Updating data takes the update statement to be executed. Here is the prepared statement:

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

Now, to check update worked:

3. Delete data from table

Deleting data is also straightforward as it was with update:

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

We can see the delete operation worked perfectly as well.

4. Getting data via select

Now, we will query data from the table. We simply execute for what we need just like previous operations. Then we iterate for each rows returned and check the data. Here is how we do that.

rows, err := db.Query(`SELECT "Name", "Roll" FROM "Students"`)
CheckError(err)

defer rows.Close()
for rows.Next() {
	var name string
	var roll int

	err = rows.Scan(&name, &roll)
	CheckError(err)

	fmt.Println(name, roll)
}

CheckError(err)

As it can be seen, the data is returned prefectly.

This is how we can create CRUD operations with PostgreSQL in Golang.