How to connect the Go database/sql package to a PostgreSQL database

Introduction

If you’re a Go developer and you’re planning to interact with PostgreSQL in your scripts, it’s important to know how to connect to the database. With the help of the Go database/sql package, getting connected is a simple task. In this article, we’ll show you how to connect the Go database/sql package to a PostgreSQL database so that you can perform database operations and queries from your script.

Prerequisites

Before we jump into our code examples, it’s important to review the prerequisites needed for this tutorial:

  • PostgreSQL server must be properly installed and configured, and it should be running in the background.

  • Golang must be properly installed on your machine. To check if it’s already installed and to find out what version you’re running, use the following command:

go version

The output of this command will look like the following:

go version go1.13 darwin/amd64
  • You’ll need to have some basic SQL knowledge in order to follow along with the examples shown in this tutorial.

How to Install the Golang Package pq

Now that we’ve gone over the prerequisites, let’s install the Go PostgreSQL driver as shown below:

go get -u github.com/lib/pq

PostgreSQL Database Information

The third-party library for Golang that we just installed will enable us to connect to any PostgreSQL database in a Go script. Before we can connect to a database, however, we need to know the necessary details about the database. Shown below is a basic example of Golang connection details:

hostname = "localhost"
host_port = 5432
user = "postgres"
password = "your-password"
databasename = "your-database-name"

Let’s take a closer look at each of these details:

  • The hostname: As the name implies, this value represents the location of the database. If the database is running on the same machine as your script, the value will be localhost.
  • The host_port: This value represents the port of the machine to which you’ll be connecting. In most cases, the value will be 5432.
  • The user and password: These two parameters are self-explanatory. The login credentials were either created during the installation of the PostgreSQL application, or they may have been provided to you by your database administrator.
  • The databasename: The value represents the database to which we’ll be connecting.

Connecting Golang to a PostgreSQL database

Now that we have a good understanding of the connection details needed to connect to a PostgreSQL database, we’ll try writing some code using those details:

package main

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

const (
hostname = "localhost"
host_port = 5432
username = "postgres"
password = ""
database_name = "testdatabase"
)

Before we continue, let’s take a moment to discuss each part of this code:

First, we import any necessary packages; in this case, we’re going to import the database/sql package. The fmt packages will be used to construct the connection string for our database connection.

Then, we use the code _ "github.com/lib/pq" to import the package and register its required drivers together with the package database/sql. The (_) underscore identifier informs Golang to include this package, even if we’re not going to use the package within our code.

After we finish our imports, we declare our constants using const. In this code, the constants are the details needed for a successful connection to the database. Simply change the details in the example to reflect your own information and login credentials.

Golang Connection String to PostgreSQL Database

Now that we have our constants set up, we’ll create our connection string. In main(), we will build our string as follows:

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

We used the following details as parameters for the connection string:

  • hostname – The host where the database is located, which is the localhost in this case.
  • host_port – This is the port to which we are going to bind and connect.
  • username – This is the name that we use for signing in to our database.
  • password – This is the password for the specified user name.
  • databasename – As the name implies, this simply represents the name of the database.
  • sslmode – The sslmode option will tell Go whether SSL will be used or not.

Golang Open Connection to PostgreSQL Database

With our connection string set up, we’re ready to write the code that will open a Golang connection to the PostgreSQL database using the function sql.Open():

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

The function sql.Open() requires you to pass in the name of the driver and the connection string that we set up earlier.

We also add some error handling syntax that will deal with any errors we may encounter; for example, we’d get an error if we failed to import the github.com/lib/pq package.

Testing the Code

At this point, our database connection code is ready. Now, let’s test it by running the code. To do this, navigate to your project directory; in this example, our file is located at /Users/risa/projects/GO_PROJECTS/src/pg_go/. Then, we execute the commands in sequence:

go build main.go

After that, we can run the script:

./main

The output from the script will look like the following:

You are Successfully connected!

Conclusion

If you’re planning to create scripts in Golang that interact with PostgreSQL, the first thing you need to learn is how to connect to your database. Fortunately, the database/sql package makes this task a simple one. This tutorial demonstrated how to connect the Go database/sql package to a PostgreSQL database. With these instructions, you’ll be able to develop your own database applications using Golang and PostgreSQL.

The Code

Shown below is complete Go script we walked through in this article:

package main

import (
"database/sql"
"fmt"

_ "github.com/lib/pq"
)

const (
hostname = "localhost"
host_port = 5432
username = "postgres"
password = ""
databasename = "testdatabase"
)

func main() {
pg_con_string := fmt.Sprintf("host=%s port=%d user=%s "+
"password=%s dbname=%s sslmode=disable",
hostname, host_port, username, password, databasename)
if err != nil {
panic(err)
}
defer db.Close()

// We can also ping our connection which will let us know if our connection is correct /// or not then we put an error-handling code right after that.
error = db.Ping()
if err != nil {
panic(err)
}

fmt.Println("You are Successfully connected!")
}

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.