Work with Go & PostgreSQL using pgx
An introduction on how to work with PostgreSQL database from Go programs using pgx library
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:
We can create Person table using following SQL script.
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:
After the insert, table would be like this:
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.
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:
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!