1 of 33

Writing a Go application with PostgreSQL using pgx

Henrique Vicente

2 of 33

Trajectory to PostgreSQL

Migration from a document-oriented database to�PostgreSQL (RDS) + OpenSearch (Amazon's fork of Elasticsearch)

3 of 33

Problems with previous database

Poor developer experience
Reliability and performance issues
High cost: support, licensing, and operational
Vendor lock-in
Steep learning curve

4 of 33

Why PostgreSQL

Object-Relational Database Management System
Almost 100% SQL standards-compliant
Excellent for client/server architecture
JSON types
Custom data types
Inheritance between tables

5 of 33

6 of 33

PostgreSQL advantages

Great developer experience
Reliability
Performance
Open-source software without vendor lock-in
Community and multiple support providers
Works everywhere: even on a new M1 MacBook

7 of 33

PostgreSQL drawbacks

Plan schema up-front (wait, isn't this an advantage?)
Full-text search capabilities is limited (but we've got it covered).

8 of 33

Environment variables for configuring PostgreSQL�https://www.postgresql.org/docs/current/libpq-envars.html

9 of 33

Environment variables might be problematic due to security concerns, so be careful.

Globals
Easy to leak to child processes or metrics probes

Also:

Process --flags are as bad!
Can you point to secrets, rather than expose them?

Tip: direnv makes it very convenient to use it on a development environment.

10 of 33

database/sql vs. pgx interface

Talking to SQL databases in Go, you almost always want to use a database through database/sql.

It provides a nice common interface, and makes supporting different databases easier.

PostgreSQL drivers for database/sql:

github.com/jackc/pgx
github.com/lib/pq (discontinued in favor of pgx)

11 of 33

database/sql vs. pgx interface

Among better performance, by using pgx interface you've:

Support for approximately 70 different PostgreSQL types
Automatic statement preparation and caching
Batch queries
Conversion of PostgreSQL arrays to Go slice mapping
JSON and JSONB support
COPY protocol support

database/sql is limited to int64, float64, bool, []byte, string, time.Time, or nil

12 of 33

Concurrency: pgxpool

pgx.Conn is a low-level implementation
Use pgxpool.Conn to access the database concurrently

Default:

conf.MaxConns = runtime.NumCPU()

Now, you should know that you must handle concurrency yourself in Go.

When using net/http request handlers, you must use a database connection pool.

In pgx, we've pgxpool, which manages a dynamic number of individual connections to the DB.

It lets you write code that acquires them efficiently and in a thread-safe manner.

By default, one TCP connection per core is used.

Don't worry about trying to find a sweet spot number for this value. It's probably this.

You probably don't need to worry that there might be only a few TCP connections to the database either. Even if your server has thousands of concurrent access.

Because each request only needs to acquire a database connection for one or more tiny fractions of time.

However, if your application has a lot of concurrent requests, it might be worth trying other values to find what is your sweet spot.

13 of 33

Database migrations: 001_initial_schema.sql, 002_…

14 of 33

tern: standalone migration tool�https://asciinema.org/a/450576

15 of 33

Database layer

It might be useful to define an interface, even if bothersome.

What about mocks?

16 of 33

Testing strategy

Get superior returns from your tests:

Favor writing integration test with a real implementation
Use test doubles to test scenarios of database failure.

Inspiration: Software Engineering at Google: Lessons Learned from Programming Over Time�https://amzn.to/3uqZWP7

Real implementation	Test doubles (mocks, fakes…)
SELECT returning data or not found.	Database connection issue.
INSERT, UPDATE, etc.	Simulation of unexpected DB error.
Constraints checks.	Expensive operation (ask why first)

17 of 33

Just like Theranos' blood tests…

Testing interfaces for the sake of it is overrated.

18 of 33

Some tools

GoMock github.com/golang/mock
go-cmp github.com/google/go-cmp
scany github.com/georgysavva/scany
pgtools github.com/hatch-studio/pgtools
squirrel github.com/Masterminds/squirrel (I prefer plain SQL, though)

19 of 33

GoMock

github.com/golang/mock

20 of 33

Comparing values with go-cmp

github.com/google/go-cmp

A more powerful and safer alternative to reflect.DeepEqual for comparing whether two values are semantically equal.

21 of 33

22 of 33

scany

23 of 33

pgtools

github.com/hatch-studio/pgtools contains:

Package to make creating SELECT for writing queries easier.
Package sqltest, which makes writing integration tests very efficient.

24 of 33

Tests with sqltest

Auto-discovery recognizes PostgreSQL configuration automatically.
Creates a database with prefix test_ (safety) + normalized test names.
Your tests can run in parallel just as fine.

DON'T

25 of 33

sqltest + tern = easy tests https://asciinema.org/a/450576

For that, we use tern programmatically. Each time this Setup function runs, it runs the database migrations on a new database it creates on the fly, and once the tests are over, we clean everything up so that next time it works just as well (unless you kill the tests, in which case, you can use the -force flag to recreate the database).

You can see we run tests in parallel.

If you've too many tests, you might run into errors due to the limit of connections to your database.

The simplest way to solve it? Increase the number of connections.

We should update this library by adding a maximum concurrency control soon just to be safe. Without changing its API.

But so far, so good, even using a -count flag.

It's just something that will happen, no matter what, so we better add a control anyways!

Go's pkgsite, which powers pkg.go.dev has such control if you want to take a look. They also use pgx, although limitedly (originally, it used the lib/pq driver).

26 of 33

27 of 33

Then you discover running its integration tests is hard.

28 of 33

$ go test ./…

From zero to running integration tests in few seconds. Try it:

Install or configure PostgreSQL environment variables so that psql works.
$ export INTEGRATION_TESTDB=true�$ go test -v ./...

29 of 33

Continuous Integration

Run your DB on any CI/CD system.

For example, with a pull request workflow on GitHub Actions, stateless.

30 of 33

31 of 33

Proof it works https://asciinema.org/a/450580

32 of 33

Takeaways

PostgreSQL is good
There are many tools to help you
Balance between integration and unit tests
Continuous Integration is easier than you think

33 of 33

Thanks. Questions?

Henrique Vicente, HATCH Studio

Article: https://henvic.dev/posts/go-postgres/
Repository: https://github.com/henvic/pgxtutorial