Back to basics: Writing an application using Go and PostgreSQL with pgx
Tags: go, postgres
Published: 2022-01-19 17:04:07

By reading this tutorial, you'll learn how to use [PostgreSQL](https://www.postgresql.org/) with the [Go](https://www.golang.org/) programming language using the [pgx](https://github.com/jackc/pgx) driver and toolkit in a very productive manner.
Furthermore, with the provided source code, you'll be able to learn how to write efficient and sound unit and integration tests, ready to be run locally or on a Continuous Integration environment, such as GitHub Actions.
Use the Table of Contents to skip to a specific part of this long post.
Don't forget to check out the accompanying repository [github.com/henvic/pgxtutorial](https://github.com/henvic/pgxtutorial).

## Context
PostgreSQL, also known as Postgres, is an extendible feature-rich [Object-Relational Database Management System](https://arctype.com/blog/postgres-ordbms-explainer/) that is almost 100% [SQL standards-compliant](https://en.wikipedia.org/wiki/SQL_compliance) and released as open source software under a permissive license.

Much of the content in this tutorial is based on experience I acquired working for [HATCH Studio](https://hatchstudio.co/), even though I'm just shy of ten months there.
My first assignments involved improving some data structures we used internally and led to a team discussion about moving from a document-based database to a more traditional relational database in the face of some challenges.
Next, we held a brainstorming session where we assembled our backend team to analyze our situation and discuss options. I already had some limited experience using PostgreSQL with pgx for a pet project and was pleased to discover that the rest of the team also saw PostgreSQL as an excellent choice for meeting our needs: great developer experience, performance, reliability, and scalability.

For our search infrastructure, we started using [Amazon OpenSearch Service](https://aws.amazon.com/opensearch-service/).
We listen to PostgreSQL database changes via its [Logical Streaming Replication Protocol](https://www.postgresql.org/docs/current/protocol-logical-replication.html) and ingest data into our [OpenSearch](https://opensearch.org/)/[Elasticsearch](https://www.elastic.co/elastic-stack/) search engine through a lightweight connector built in-house.

It works similar to hooking [Apache Kafka](https://kafka.apache.org/), but is easier to use and allows us to move faster without breaking things: running integration tests on a developer machine takes only seconds: much of which is an overkill `time.Sleep()` so we never waste time with flaky tests caused by the eventual consistency characteristics of the search engine.
This solution will not be presented now but in a future opportunity.

## tl;dr
To play with it install [Go](https://go.dev/) on your system.
You'll need to connect to a [PostgreSQL](https://www.postgresql.org/) database.
You can check if a connection is working by calling `psql`.

```sh
# Clone my repository with any of the following commands:
$ gh repo clone henvic/pgxtutorial
$ git clone https://github.com/henvic/pgxtutorial.git
$ git clone git@github.com:henvic/pgxtutorial.git
# then:
$ cd pgxtutorial
# Create a database
$ psql -c "CREATE DATABASE pgxtutorial;"
# Set the environment variable PGDATABASE
$ export PGDATABASE=pgxtutorial
# Run migrations
$ tern migrate -m ./migrations
# Run all tests passing INTEGRATION_TESTDB explicitly
$ INTEGRATION_TESTDB=true go test -v ./...
# Execute application
$ go run ./cmd/pgxtutorial
2021/11/22 07:21:21 HTTP server listening at localhost:8080
2021/11/22 07:21:21 gRPC server listening at 127.0.0.1:8082
```

The blog post continues on my website at [henvic.dev/posts/go-postgres](https://henvic.dev/posts/go-postgres/).