pgtools

README

pgtools

pgtools contains features HATCH Studio developed and rely upon to use PostgreSQL more effectively with Go.

Please see the official documentation or source code for more details.

Features

pgtools.Wildcard

Use the Wildcard function to generate expressions for SELECT queries.

Given a table user:

CREATE TABLE user {
	username text PRIMARY KEY,
	fullname text NOT NULL,
	email text NOT NULL,
	id text NOT NULL,
	Theme jsonb NOT NULL,
}

You might want to create a struct to map it like the following for use with scany.

type User struct {
	Username string
	FullName string
	Email    string
	Alias    string    `db:"id"`
	Theme    Theme     `db:"theme,json"`
	LastSeen time.Time `db:"-"`
}

type Theme struct {
	PrimaryColor       string
	SecondaryColor     string
	TextColor          string
	TextUppercase      bool
	FontFamilyHeadings string
	FontFamilyBody     string
	FontFamilyDefault  string
}

The db struct tag follows the same pattern of other SQL libraries, besides scany.

• A field without a db tag is mapped to its equivalent form in snake_case instead of CamelCase.
• Fields with db:"-" are ignored and no mapping is done for them.
• A field with db:"name" maps that field to the name SQL column.
• A field with db:",json" or db:"something,json" maps to a JSON datatype column named something.

Therefore, you can use:

sql := "SELECT " + pgtools.Wildcard(User{}) + " WHERE id = $1"

instead of

sql := "SELECT username,full_name,email,theme WHERE id = $1"

This works better than using SELECT * for the following reasons:

• Performance: you only query data that your struct can map.
• Correctness: no mismatch.
• If you add a new field in a struct, you don't need to change your queries.
• scany fails when reading unmapped columns with SELECT *, but this solves it.
• If you delete a field, you don't need to change your queries.

Limitations

Using pgtools.Wildcard() on a JOIN is tricky, and not generally recommended – at least for now.

To see why, take the following example:

sql := `SELECT ` + postgres.Wildcard(Entity{}) + `
	FROM entity
	LEFT JOIN
sister_entity on sister_entity.entity_id = entity.id`

This will be roughly translated to:

SELECT id, name, ...

Which is not guaranteed to be correct due to ambiguity. What we want is to have the following instead:

SELECT table.field1, table.field2...

In this case, we want to write everything manually so that PostgreSQL doesn't try to fetch each field in each joined table, as this might lead to conflicts, extra data, bugs, or eventually an error.

For now, it's better to avoid using pgtools.Wildcard() for JOINs altogether, even when it seems to work fine.

pgtools/sqltest package

You can use sqltest.Migration to write integration tests using PostgreSQL more effectively.

Check the example package for usage.

ctx := context.Background()
	migration := sqltest.New(t, sqltest.Options{
		Force: force,
		Path:  "testdata/migrations",
	})
	conn := migration.Setup(ctx, "")

The path indicates where your SQL migration files created for use with tern live.

Example of a tern migration file 003_posts.sql:

CREATE TABLE posts (
	id text PRIMARY KEY,
	name text NOT NULL,
	message text NOT NULL,
	created_at timestamp with time zone NOT NULL DEFAULT now(),
	modified_at timestamp with time zone NOT NULL DEFAULT now()
);

---- create above / drop below ----
DROP TABLE IF EXISTS posts;

To effectively work with tests that use PostgreSQL, you'll want to run your tests with a command like:

go test -v -race -count 1 -tags=integration ./...

• -race to pro-actively avoid race conditions
• -count 1 to disable test caching
• -tags=integration or a build environment variable to opt-in for Postgres-related tests (there are different advantages and fallbacks)

Multiple packages might have test functions with the same name, which might result in clashes if you're executing go test with list mode (example: go test ./...). Using t.Parallel() doesn't have an effect in this case, and you have two choices:

• Set the field Options.TemporaryDatabasePrefix to a unique value.
• Limit execution to one test at a time for multiple packages with -p 1.

If you use environment variables to connect to the database with tools like psql or tern, you're already good to go once you create a database for testing starting with the prefix test.

We use GitHub Actions for running your integration tests with Postgres in a Continuous Integration (CI) environment. You can find our workflow in .github/workflows/integration.yml.

Acknowledgements

HATCH Studio uses the following Postgres-related software, and this work is in direct relation to them.

• pgx is a PostgreSQL driver and toolkit for Go.
• tern is a standalone migration tool for PostgreSQL and part of the pgx toolkit.
• scany is a library for scanning data from a database into Go structs.

Documentation

Overview

Package pgtools contains features HATCH Studio developed and rely upon to use PostgreSQL more effectively with Go. It was designed to be used alongside github.com/jackc/pgx and github.com/georgysavva/scany.

Index

• func Fields(v interface{}) []string
• func Wildcard(v interface{}) string

Examples

• Fields
• Wildcard

Functions

func Fields

func Fields(v interface{}) []string

Fields returns column names for a SQL table that can be queried by a given Go struct. Only use this function to list fields on a struct.

To avoid ambiguity issues, it's important to use the Wildcard function instead of calling strings.Join(pgtools.Field(v), ", ") to generate the query expression.

Example

package main

import (
	"fmt"
	"strings"
	"time"

	"github.com/hatch-studio/pgtools"
)

type User struct {
	Username string
	FullName string
	Email    string
	Alias    string    `db:"id"`
	Theme    Theme     `db:"theme,json"`
	LastSeen time.Time `db:"-"`
}

type Theme struct {
	PrimaryColor       string
	SecondaryColor     string
	TextColor          string
	TextUppercase      bool
	FontFamilyHeadings string
	FontFamilyBody     string
	FontFamilyDefault  string
}

func main() {
	fmt.Println(strings.Join(pgtools.Fields(User{}), "\n"))
}

Output:

username
full_name
email
id
theme

func Wildcard

func Wildcard(v interface{}) string

Wildcard returns an expression that can be used for querying a SQL database.

To make usage simpler in the happy path, Wildcard doesn't return an error. However, this means you should check if the value generated by the function is valid (panic is not used as it would introduce unwanted risk).

The "db" key in the struct field's tag can specify the "json" option when a JSON or JSONB data type is used in PostgreSQL.

It is useful to ensure scany works after adding a field to the databsase, and for performance reasons too by reducing the number of places where a wildcard (*) is used for convenience in SELECT queries.

See example for usage. It was envisioned to use with github.com/georgysavva/scany, but you can use it without it too.

If you're curious about doing this "in the other direction", see https://github.com/golang/pkgsite/blob/2d3ade3c90634f9afed7aa772e53a62bb433447a/internal/database/reflect.go#L20-L46

Example

package main

import (
	"fmt"
	"time"

	"github.com/hatch-studio/pgtools"
)

type User struct {
	Username string
	FullName string
	Email    string
	Alias    string    `db:"id"`
	Theme    Theme     `db:"theme,json"`
	LastSeen time.Time `db:"-"`
}

type Theme struct {
	PrimaryColor       string
	SecondaryColor     string
	TextColor          string
	TextUppercase      bool
	FontFamilyHeadings string
	FontFamilyBody     string
	FontFamilyDefault  string
}

func main() {
	sql := "SELECT " + pgtools.Wildcard(User{}) + " WHERE id = $1"
	fmt.Println(sql)
}

Output:

SELECT "username","full_name","email","id","theme" WHERE id = $1