Connecting to a PostgreSQL database with Go's database/sql package

In this article we are going to explore using PostgreSQL with Go. We are going to start out pretty simple and will only be connecting to a database here, but if you are familiar with SQL you will discover that once you get connected to your database you can very quickly start utilizing it with your existing SQL knowledge.

Don’t fret if you don’t have any SQL experience. This post is part of a much larger series titled , and previous articles in the series not only cover how to use Postgres with raw SQL, but they also help walk you through installing Postgres and provide a little background information about why we are doing some of the things we do. I suggest you check the previous articles out :)

Gather your PostgreSQL database info

Before we can connect to our database we first need to make sure we have the correct information. If this info is wrong, it doesn’t matter what you try in your code; You won’t be able to interact with your database without the correct credentials!

Unfortunately, your credentials and other database information you are going to need isn’t something that is set in stone. You probably chose the name of the database you are going to use, the user (aka role) used to login to Postgres, and you likely set a custom password for that user.

Despite that, I am going to try to walk you through gathering this information, including giving you a few guesses as to what your information may be when working with a local database.

Before we proceed, let’s first look at the information we need to collect.

const (
  host     = "localhost"
  port     = 5432
  user     = "postgres"
  password = "your-password"
  dbname   = "calhounio_demo"
)

Most of these should look familiar. host is almost certainly going to be localhost and port is likely 5432 if you are connecting to a Postgres database that is running on the same computer you are developing from. If that isn’t the case, I am going to assume you have a way to locate that information.

The next two on the list, user and password, are likely either:

  1. Set by you while installing Postgres
  2. Provided to you (eg if you are using a Heroku database)
  3. The same as your OS username

The first two use cases are self explanatory; You will just need to recall what you used during your setup and use those values. If you have been connecting to your database with a string that looks like psql -U something ... then the something part that comes after the -U flag is your user, and the password you type afterwards is your password.

If you aren’t asked for a password then you won’t need it, but be sure to update the connection string we create later to remove it entirely.

If all else fails, try running id -un in a terminal and using that as your user and remove the password const and the password portion from the connection string we create a little later on.

Finally we have the dbname, which is just the database that you are using. Previous articles in this series have been using calhounio_demo which is what we will be using moving forward.

Install the github.com/lib/pq package

After gathering the information you need, we are going to install a third party package named lib/pq. Run the code below to install the package.

go get -u github.com/lib/pq

While Go provides us with the database/sql package that we will be utilizing to interact with our database, the standard libraries do not include drivers for every SQL database variant. Instead this is left up to the community, and from my experience the lib/pq package is the best driver for Postgres.

Putting it all together in code

Once you have the package installed, we are going to start writing our code.

package main

import (
  "database/sql"
  "fmt"

  _ "github.com/lib/pq"
)

const (
  host     = "localhost"
  port     = 5432
  user     = "postgres"
  password = "your-password"
  dbname   = "calhounio_demo"
)

We are importing the database/sql package because we will be using it to connect to our database, and we are importing the fmt package because we will want to use it to construct a connection string that has all of the information required to connect to our database.

The last import, _ "github.com/lib/pq", might look funny at first, but the short version is that we are importing the package so that it can register its drivers with the database/sql package, and we use the _ identifier to tell Go that we still want this included even though we will never directly reference the package in our code.

After the imports we declare our constants, which are essentially just the data we require to connect to our database. Your values may differ from mine, but you should likely have the same values for me, with the possible exception of the password.

Creating the connection string

Inside of our main() function we are going to create a string titles psqlInfo which contains all of the information required to connect to our Postgres database.

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

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

Depending on your situation, you may want to use a different value for sslmode. I am opting to set it to disabled because that isn’t the default for lib/pq, and without disabling it many users are likely to experience the error pq: SSL is not enabled on the server.

The lib/pq package accepts a few other connection string parameters and also has default values for several of the keys. You can check the full docs for these on godoc.org and update your string according to your needs.

If you DO NOT have a password, be sure to remove both the password from the provided parameters to the fmt.Sprintf() function call and also remove the password=%s portion of the string. If you provide an empty password you are likely to run into issues.

Opening a connection to our database

We are ready to write the code that connects to our database now that we have a connection string. To do this we are going to use the sql.Open() function.

The sql.Open() function takes two arguments - a driver name, and a string that tells that driver how to connect to our database - and then returns a pointer to a sql.DB and an error.

If the error is not nil we are going to go ahead and panic because this means that we did something wrong. Most likely, we didn’t import the github.com/lib/pq package.

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

Finally, we are going to call the Ping() method on the sql.DB object we got back from the open function call.

It is vitally important that you call the Ping() method becuase the sql.Open() function call does not ever create a connection to the database. Instead, it simply validates the arguments provided.

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

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

If we put that all together, our final code should be something like…

package main

import (
  "database/sql"
  "fmt"

  _ "github.com/lib/pq"
)

const (
  host     = "localhost"
  port     = 5432
  user     = "postgres"
  password = "your-password"
  dbname   = "calhounio_demo"
)

func main() {
  psqlInfo := 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("Successfully connected!")
}

If your code seems to match this and it isn’t working, head on the to the next section where we discuss some potential errors and ways to try to fix them. Otherwise you can skip that section.

Potential errors

Hopefully you won’t need this section, but below is a list of common errors that you might run across while trying to connect to your database along with an explanation of what likely caused each.

If your error isn’t listed below, feel free to get in touch so I can add it to the list.

psql: FATAL: database “calhounio_demo” 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.

I cover creating databases in this article. Once you create the database give your code another try to see if it helped.

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 and you should be back in action.

pq: role “postgres” does not exist

The terms role and user are pretty synonymous when it comes to PostgreSQL, so what this is really saying is that your username (user in our code) is incorrect.

The fix here varies, but if you can connect to your database the first thing I would try is doing that, and then running select current_user; to display the current user. If that user doesn’t work, you might require more 1 on 1 support :(

Up Next…

In the remaining articles in this series we are going to be utilizing the database connection that we created in this article to start interacting with data stored in the database. In some of the articles we will be writing SQL queries, while in others we will be letting GORM do the heavy lifting, but regardless of what you end up doing, the first step of connecting to your database is a requirement.

If you weren’t able to get your code working, feel free to reach out to ask for help - jon@calhoun.io. It is even better if you can include some code snippets for me to look at, and let me know what OS your are running on.

Up next we are going to discuss Inserting records into a PostgreSQL database with Go's database/sql package into your database, and once again we will be utilizing the database/sql package along with the lib/pq package to provide the PostgreSQL drivers. If you are just getting started with Go and SQL I suggest you check it out.

Alternatively, you can also jump to any other article in this series by checkout out the series overview where I discuss all of the topics covered by the Using PostgreSQL with Go series.

Learn Web Development with Go!

Sign up for my mailing list and I'll send you a FREE sample from my course - Web Development with Go. The sample includes three chapters from the book, and over 2.5 hours of screencasts.

You will also receive notifications when I release new articles, along with other freebies that I only share with my mailing list.

Avatar of Jon Calhoun
Written by
Jon Calhoun

Jon Calhoun is a full stack web developer who also teaches about Go, web development, algorithms, and anything programming related. He also consults for other companies who have development needs. (If you need some development work done, get in touch!)

Jon is a co-founder of EasyPost, a shipping API that many fortune 500 companies use to power their shipping infrastructure, and prior to founding EasyPost he worked at google as a software engineer.

More in this series

This post is part of the series, Using PostgreSQL with Go.

Spread the word

Did you find this page helpful? Let others know about it!

Vote on Hacker News

Sharing helps me continue to create both free and premium Go resources.

Want to discuss the article?

See something that is wrong, think this article could be improved, or just want to say thanks? I'd love to hear what you have to say!

You can reach me via email or via twitter.

Recent Articles Mini-Series Tags About Me Go Courses

©2018 Jonathan Calhoun. All rights reserved.