Work with Go & PostgreSQL using pgx

An introduction on how to work with PostgreSQL database from Go programs using pgx library

Photo by Chris Liverani on Unsplash

Introduction

As a programmer, when you try to develop any product, using a database to store data is an implicit assumption. Depending on the use case, you may choose either relational database or non-relational database (a.k.a NoSQL). If your use case needs relational database, there are quite a few options available. PostgreSQL is one of the common open source choices available at the moment.

In this post, we’ll discuss on how to connect to PostgreSQL database from Go programs using pgx library.

What’s pgx ?

pgx is a PostgreSQL driver and toolkit written in Go.

When to use ?

As per the developers of pgx, it is recommended,

if the application only targets PostgreSQL and no other libraries which needs database/sql are used.

If these two conditions are satisfied, you are good to go with pgx. If not, it is better not to use pgx and look out for other libraries.

pgx Family

pgx provides a set of libraries which can be used independently as well. Following are a few of them:

• pgconn : a lower-level PostgreSQL database driver
• pgxpool : a connection pool for pgx.
• stdlib : a database/sql compatibility layer for pgx.
• pgtype : an implementation of Go types for over 70 PostgreSQL types

Note that, current version (v4) expects Go module support enabled. More details can be found here.

Our Use Case

For our demonstration purpose, let’s consider a highly sophisticated use case. Just kidding. Let’s keep it as simple as possible. :-)

We would like to store details about a person in our database and would like to retrieve the same. That’s all what we’ll attempt here. Simple :-)

Install & Configure PostgreSQL

Before we get started with pgx, let’s quickly revisit how to install and configure PostgreSQL for your development environment.

You can download latest version of PostgreSQL from here. Select an installation option based on your Operating System.

On an Ubuntu machine, you can follow below steps:

Install

Execute the following commands to install PostgreSQL and a few additional modules.

$ sudo apt update
$ sudo apt install postgresql postgresql-contrib

Start

Once the installation is complete, you can start the database using the following command:

$ pg_ctlcluster 12 main start

Login to default account

Login to the default account (postgres) using following command:

$ sudo -i -u postgres

Reset default password

Initially, the default account would not have any password associated with it. It is recommended to set a password post initial login. You can do so by using the following SQL command.

ALTER USER postgres PASSWORD 'mypassword';

(Replace ‘mypassword’ with a more secure password.)

Note: For production grade system, it is better to create separate db (other than the default postgres db) and users. For simplicity sake, we’ll continue with the above setup.

Create a Table and a Function

Now, our PostgreSQL installation is ready to use. In order to develop our project, let’s start creating sample tables.

Schema

The default account comes with a default schema named public. Let’s use that for now. (Note that, for production grade systems, it is better to create a a different schema.)

Table

As mentioned above, we plan to store details about persons in our table.

The Person table needs to have following fields:

person table structure

We can create Person table using following SQL script.

SQL DDL statement to person table

You may not need to add the prefix public before the table name since it is the default one. Still, it is a good practice to keep that habit in order to state the intent explicit. That will help us to keep it consistent when we use a different dedicated schema.

Data

Now, let’s insert a few rows as follows:

insert statements for person table

After the insert, table would be like this:

data in person table

Function

Now, let’s create a simple database function to retrieve details about the person based on input id. We can use the following SQL code to create the function.

Now, let’s use the function as follows:

select * from public.get_person_details(1);

Once executed, we’ll get the following result.

function result

With that, our database side coding is complete. Let’s shift focus to Go layer.

Create Go Project

Let’s create Go project and call it go-postgresql-pgx-example.

Initialise the modules by executing following command:

$ go mod init go-postgresql-pgx-example

Install pgx and pgxpool

Now, we need to install pgx and pgxpool.

$ go get github.com/jackc/pgx/v4
$ go get github.com/jackc/pgx/v4/pgxpool

Create connection pool

To connect to the database, we can either use pgx or pgxpool. For our example, let’s create a connection pool. Following is the sample Go code to do so.

Execute Query

Now, let’s add code to call a simple select statement. It can be done as follows:

Execute Function

Let’s add code to call the DB function as well. It is similar to the query execution with minor differences.

Now, when we execute our Go program, we’ll get data from database as follows:

program result

That’s all.

The complete source code for this example can be found at the following GitHub repo:

https://github.com/bijeshos/go-postgresql-pgx-example

Summary

In this post , we discussed briefly about pgx library and how to connect to PostgreSQL from Go code using the same.

Thank you for taking time to read. Till we meet next time, happy coding!

References

https://github.com/jackc/pgx

https://www.postgresql.org/docs/

https://github.com/bijeshos/go-postgresql-pgx-example