Connecting to Postgres DB in Go

This is a follow up from https://sennasemakula.medium.com/running-a-postgres-database-from-scratch-3409ec93905b

Often you will be communicating with a database using code rather than directly running SQL commands inside the database.

Getting started

In order for Go applications to connect to databases, we will need to use the package database/sql from the standard library. This contains interfaces/functions that we will be using to interact with our database.

Database driver

If you look at the code for database/sql you may observe that is quite scarce. Due to their being a myriad of databases, the core team couldn’t possibly define the implementations for each one. Each database will have its own set of nuances. Instead, the sql package defines a set of interfaces where database drivers can satisfy the interfaces with their own implementation. The abstraction makes it incredibly easy to connect to different types of databases whilst exerting minimal effort

We will be using the github.com/lib/pq package

If you’re interested in looking at others, have a glance at: https://github.com/golang/go/wiki/SQLDrivers

Getting started

Open a new folder and create a new golang app.

go mod init postgres-playground

You should now have a go.mod file in your directory. Install the driver with the following command:

go get -u github.com/lib/pq

Create a new file called main.go and add the following content:

Above we are first importing the sql driver package. This will be used later on to initiate a connection to our database. The second import line it’s quite magical. The underscore before the package informs the compiler that we want to keep this package but we will not be using it in our code. The incantation it does under the hood is that before our main function is executed, it will run the init() function. The init function of pq registers a new driver for postgres. See details here: https://github.com/lib/pq/blob/ae8357db35d721c58dcdc911318b55bef6b1b001/conn.go#L48

Sequentially we define constant values that will be used as configuration to connect to our database

• host — the address where our postgres database is running
• port — the port the database server is running on. 5432 is the default port of postgrest
• user — the database user we will be connecting with
• dbName — the name of our database

We next create a formatted string that will be used to pass into sql.Open(). As you can see we have disabled ssl mode because our database is not running with it. For production, you will want to enable SSL to enhance your security.

We next call sql.Open specifying the driver name first “postgres” and passing in our configuration we defined above. This function returns a DB instance that we can use to execute queries on our database.

Afterwards, we call db.Ping() to check that a connection to the database is still alive. If it’s not, then we will make our application panic.

There you have it. This article covered how to connect to a postgres instance in Go. Next time we’ll cover how to execute queries from our application layer!