Introduction

In the modern era, Golang has been very popular as a programming language. Often developers building software need to connect to a PostgreSQL database from their Golang project making the use of the Go's database package. This article will show how we can establish a connection to a PostgreSQL database using the Go's database package.

Prerequisites

Before starting with the connection establishment, developers need to have their PostgreSQL connection details available. These are the values you have provided during the installation of the PostgreSQL server in your local environment. The information should look something like this:

Just make sure that you have the right password filled in/handy and the appropriate database name (here referred as dbname) ready.

Note: In case you haven't provided any password during the installation, leave this value blank.

Establishing a Connection

Golang provides developers with the database/sql package that is very helpful to interact with the backend database, however the standard libraries do not include drivers for different SQL database variants. Rather this is left up to the community. In my experience, I would say that lib/pq package is the best driver for Postgres. We can install this third party package by running the code below:

go get -u github.com/lib/pq

As soon as you have the package installed, you can start writing your code. You can start to try establishing a successful connection from your Golang application to your PostgreSQL server.  We start by importing the database/sql package because you will use it to connect to your database. Next, you import the fmt package because you would like to use it to construct a connection string that has all of the information required to connect to your backend database.

The last import, which is _"github.com/lib/pq", might look a bit weird. What this depicts is that you are importing the package so that it can register its drivers with the database/sql package. You also use the _ identifier to tell Golang that you still want this to be included even though you might never directly reference the package in your code.

Here is all the code together:

package main
import (
  "database/sql"
  "fmt"
  _ "github.com/lib/pq"
)
const (
  host     = "localhost"
  port     = 5432
  user     = "postgres"
  password = "Test@12344"
  dbname   = "golangdb"
)

In the second half of the above code, you have just declared the constants which is just the data you have collected as a part of pre-requisite step regarding your PostgreSQL database connections.

Creating the Connection String

Inside of your main() function you are going to create a string, called postgresqDbInfo. This will contain all information required to connect to your Postgres database. Here is the structure of this string:

func main() {
  postgresqlDbInfo := fmt.Sprintf("host=%s port=%d user=%s "+
    "password=%s dbname=%s sslmode=disable",
    host, port, user, password, dbname)
}

In this example you have used the following connection string parameters:

• host - the host of the PostgreSQL database
• port - the port on which the PostgreSQL server is listening
• user - your username for the PostgreSQL database
• password - Your password for the user specified
• dbname - the database name of the PostgreSQL database
• sslmode - Whether or not to use SSL. This can be disable or enable.

Depending on your situation, you may want to use a different value for sslmode. You can set it to disabled because that isn’t the default for lib/pq. Without disabling this, you might experience the error: pq: SSL is not enabled on the server.

Opening a Connection

Now, you are ready to write the piece of code that connects to your database. To do this we are going to use the sql.Open() function. The sql.Open() function takes two arguments: the driver name and the string that tells the driver how to connect to your database. It then returns a pointer to a sql.DB and an error.

In case the error is not nil you did something wrong.  Else it means that you are going to go ahead. The most common mistake is that you didn’t import the github.com/lib/pq package.

db, err := sql.Open("postgres", psqlInfo)
if err != nil {
  panic(err)
}
defer db.Close()

Finally, you need to call the Ping() method on the sql.DB() object you got back from the open function call earlier.

You should call the Ping() method because the sql.Open() function call never creates a connection to the database. Instead, it simply validates the arguments provided.

By calling db.Ping() you force your code to actually open up a connection to the database. This will validate whether or not your connection string was 100% correct.

err = db.Ping()
if err != nil {
  panic(err)
}

The final code should look something like this:

import (
  "database/sql"
  "fmt"
  _ "github.com/lib/pq"
)
const (
  host     = "localhost"
  port     = 5432
  user     = "postgres"
  password = "Test@12344"
  dbname   = "golangdb"
)
func main() {
  postgresqlDbInfo := fmt.Sprintf("host=%s port=%d user=%s "+
    "password=%s dbname=%s sslmode=disable",
    host, port, user, password, dbname)
  db, err := sql.Open("postgres", psqlInfo)
  if err != nil {
    panic(err)
  }
  defer db.Close()
  err = db.Ping()
  if err != nil {
    panic(err)
  }
  fmt.Println("Established a successful connection!")
}

Probable developer errors

It might happen that even after following these steps, you as a developer might encounter errors. In that case, this section might help you out. Following are a list of probable error which developers might encounter:

psql: FATAL: database “golangdb” does not exist:

This will happen when you specify a dbname that doesn’t exist. To resolve this, you likely just need to connect to your Postgres database and create the database.

pq: SSL is not enabled on the server:

The SSL is not enabled on the server error occurs when you try to connect to a database with sslmode set to required in your code while the server doesn’t have it enabled. By default, sslmode is set to required with lib/pq, so you need to actually specify another setting to fix this.

Update your connection string to include sslmode = disable.

pq: role “postgres” does not exist

The terms role and user are pretty synonymous when it comes to PostgreSQL. So this error says that your username (user in our code) is incorrect.

Conclusion

Golang being an important programming language in the modern times need to establish connections to the backend database and hence this document, which is a step by step guide with explanation might help the developers to get it done.