Go has very rich standard library which also contains database/sql module with generic interface over SQL databases. Further, lib/pq PostgreSQL driver is fully compatible with database/sql and provides all basic database operations. However, since this is generic implementation over all supported databases, using it is a trade-off not supporting some PostgreSQL specific features. Fans of PostgreSQL (in further text pg) may want more and here comes pgx in help. pgx is a very rich pg driver supporting LISTEN / NOTIFY, COPY, type mapping between pg and Go, all specific pg types, wire protocol and much more.

In the latest version v5, maintainer of pgx starting using generics in the project and introduced many new features including:

  • new tracing logger with hooks for tools like OpenTelemetry
  • CollectRows function to collect results into a slice using RowTo* functions
  • CollectOneRow function to collect one row also using RowTo* functions
  • RowToStructByName and RowToStructByPos functions to scan rows into Go struct types
  • ForEachRow function to iterate over resulting rows and perform an arbitrary function
  • QueryRewriter interface and NamedArgs implementation for named query parameters
  • improved SendBatch function

It this series we are going to cover all of these changes and try to disclose some of the pgx secrets, specially the new ones. In order to make our code able to access the pg database, we are going to create the following docker-compose.yml file in our project:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

version: '3.9'
services:
  postgres:
    image: postgres:15-alpine
    restart: always
    volumes:
      - .docker:/docker-entrypoint-initdb.d
    environment:
      - POSTGRES_DB=test
      - POSTGRES_HOST_AUTH_METHOD=trust
    ports:
      - 5432:5432

And also some initial database schema in .docker/schema.sql:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

BEGIN;

CREATE DOMAIN zip_code AS TEXT
CHECK(
   VALUE ~ '^\d{5}$'
OR VALUE ~ '^\d{5}-\d{4}$'
);

CREATE TYPE role AS ENUM ('admin', 'editor', 'user');

CREATE TABLE accounts (
	id CHAR(20) PRIMARY KEY CHECK (length(id) = 20),
	username TEXT UNIQUE NOT NULL CHECK (username != ''),
    role role NOT NULL,
    address TEXT,
    zip_code zip_code
);

COMMIT;

As you can see in the last snippet, we are going to use some fancy pg features like domain and enum types, constraint checks and xid as a primary key.

To initialize Go project, just type go mod init following by the name by your choice, e.g. go mod init pgx5 and create main.go file with the following contents:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

package main

import (
	"context"
	"fmt"
	"os"

	"github.com/jackc/pgx/v5/pgxpool"
)

func main() {
	ctx := context.Background()

	dbpool, err := pgxpool.New(ctx, "postgres://postgres@localhost:5432/test")
	if err != nil {
		exit("failed creating pgx pool", err)
	}
	defer dbpool.Close()
}

func exit(msg string, err error) {
	fmt.Fprintf(os.Stderr, "%s: %v\n", msg, err)
	os.Exit(1)
}

In this snippet we created the pgx connection pool and now we are ready for the next part of the series where we will discuss on QueryRewriter interface and NamedArgs implementation. Stay tuned.