Back to basics: Writing an application using Go and PostgreSQL with pgx

Henrique Vicente — Wed, 19 Jan 2022 17:04:07 +0000

By reading this tutorial, you'll learn how to use PostgreSQL with the Go programming language using the 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.

Context

PostgreSQL, also known as Postgres, is an extendible feature-rich Object-Relational Database Management System that is almost 100% SQL standards-compliant 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, 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.
We listen to PostgreSQL database changes via its Logical Streaming Replication Protocol and ingest data into our OpenSearch/Elasticsearch search engine through a lightweight connector built in-house.

It works similar to hooking Apache Kafka, 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 on your system.
You'll need to connect to a PostgreSQL database.
You can check if a connection is working by calling psql.

# 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.

Homelab: Intel NUC with the ESXi hypervisor

Henrique Vicente — Mon, 20 Apr 2020 22:29:10 +0000

In this blog post, I’m going to talk a little about my experience running multiple operating systems with an Intel NUC I recently bought and the ESXi 7 hypervisor. My main idea was to use this homelab for:

Network Area Storage (NAS)
Kubernetes cluster
FreeBSD playground
Managing backup
Stage and development machine for Go projects

The blog post continues on my website at henvic.dev/posts/homelab.

Forem: Henrique Vicente

Back to basics: Writing an application using Go and PostgreSQL with pgx

Context

tl;dr

Homelab: Intel NUC with the ESXi hypervisor