pgxtras

README

pgxtras - Extra functionality that compliments pgx

A few extra functions to extend functionality in the excellent github.com/jackc/pgx/v5 library.

CollectOneRowOK()

In a psql session, whether one row is expected or many rows are expected, getting back 0 rows is not a SQL error.

It has always bothered me that myPgxConnection.QueryRow() returns an error when no row is found (pgx.ErrNoRows) instead of an empty result. After all, rows, err := myPgxConnection.Query() returns no rows (strictly speaking, the first call to rows.Next() is false)

The same is true of the pgx.CollectOneRow() convenience method: when pgx.CollectOneRow() finds no rows, it returns an error (ErrNoRows). But when pgx.CollectRows() finds no rows, it does not return an error; it's just that the returned slice is length 0.

pgxtras.CollectOneRowOK() provides a way to also return without error when no rows are found. Whereas a caller of pgx.CollectRows() can check if the returned slice is length > 0 to determine if rows were found, a caller of pgxtras.CollectOneRowOK() can check the second return value (usually named ok) to see if a row was found.

This follows the "comma-OK" idiom found in other Go libraries. For instance, in the os package of the standard library, os.GetEnv() returns an empty string if an env var is not found; but os.LookupEnv() returns a second boolean value (by convention named ok) that is set to true if the env var was present but set to the empty string, or false if the env var truly was not present. Also, when getting a value from a map, the "comma-OK" idiom can be used for the same purpose.

pgxtras.RowToStructBySnakeToCamelName() and pgxtras.RowToAddrOfStructBySnakeToCamelName()

The pgx library has convenience functions pgx.RowToStructByName() and pgx.RowToAddrOfStructByName(), which ignore case when assigning column results to struct fields.

The pgextras convenience functions, pgxtras.RowToStructBySnakeToCamelName() and pgxtras.RowToAddrOfStructBySnakeToCamelName(), cover the common case of database columns being named in snake_case and Go struct fields being named in CamelCase. These are both rather common naming conventions, and these two convenince methods translate between them to relieve the user of having to use any special tags in Go structs, or any as column aliasing in SQL.

pgxtras.RowToStructBySimpleName() and pgxtras.RowToAddrOfStructBySimpleName()

pgxtras.RowToStructBySnakeToCamelName() and pgxtras.RowToAddrOfStructBySnakeToCamelName(), above, didn't do a perfect job of capturing the way people really name columns in SQL nor struct fields in Go.

Here is an obvious example:

In translating SQL column names to Go struct field names, one would expect

name ==> Name city ==> City

But following strict camel-casing rules, we get this translation:

id ==> Id

whereas surely we would prefer

id ==> ID

(To get CamelCase struct field ID from a snake-case column name, the column would have to be named i_d. Yuk.)

Clearly pgxtras.RowToStructBySnakeToCamelName() and pgxtras.RowToAddrOfStructBySnakeToCamelName() did not capture the subtleties of translating common SQL column names to common Go struct field names.

So pgxtras.RowToStructBySimpleName() and pgxtras.RowToAddrOfStructBySimpleName() take a different approach. SQL column names and Go struct fields are compared with each other by lowercasing and stripping all underscores, like so:

SQL column name   "simple" name    Go struct field name
---------------------------------------------------------
first_name    ==> firsname     <== FirstName
last_name     ==> lastname     <== LastName
name          ==> name         <== Name
city          ==> city         <== City
id            ==> id           <== ID
http_address  ==> httpaddress  <== HTTPAddress

This way of determining which SQL column names go with which Go struct field names should cover a lot more of the standard naming conventions of both languages.

pgxtras.RowToMapStrStr()

pgx has pgx.RowToMap() which returns a map[string]any.

pgxtras has pgxtras.RowToMapStrStr() which returns a map[string]string. Internally, RowToMapStrStr() just uses fmt.Sprintf("%v", val). If you want to control the string representation of your column, I recommend doing so in your SQL statement: if every column in your SQL result set is already of Postgres type text, then you know what you will be getting when Go does fmt.Sprintf("%v", val) to it.

Interfaces Querier, Execer, and QuerierExecer

I end up using these interfaces a lot in my code: instead of using concrete pgx.Conn, pgxpool.Pool, pgx.Tx, and pgxpool.Tx arguments in my functions and methods, I just use one of Querier, Execer, or QuerierExecer instead. This way, my data access functions and methods are not restricted to just using one of pgx.Conn, pgxpool.Pool, pgx.Tx, or pgxpool.Tx.

If you only intend to use these interfaces, don't bother importing this whole module; just cut and paste these interfaces into your own project; the license on this module is quite permissive.

Documentation

Overview

A few extra functions to extend functionality in the excellent github.com/jackc/pgx/v5 library.

`CollectOneRowOK()`

In a `psql` session, whether one row is expected or many rows are expected, getting back 0 rows is not a SQL error.

It has always bothered me that `myPgxConnection.QueryRow()` returns an error when no row is found (`pgx.ErrNoRows`) instead of an empty result. After all, `rows, err := myPgxConnection.Query()` returns no rows (strictly speaking, the first call to `rows.Next()` is false)

The same is true of the `pgx.CollectOneRow()` convenience method: when `pgx.CollectOneRow()` finds no rows, it returns an error (`ErrNoRows`). But when `pgx.CollectRows()` finds no rows, it does not return an error; it's just that the returned slice is length 0.

`pgxtras.CollectOneRowOK()` provides a way to also return without error when no rows are found. Whereas a caller of `pgx.CollectRows()` can check if the returned slice is length > 0 to determine if rows were found, a caller of `pgxtras.CollectOneRowOK()` can check the second return value (usually named `ok`) to see if a row was found.

This follows the "comma-OK" idiom found in other Go libraries. For instance, in the `os` package of the standard library, `os.GetEnv()` returns an empty string if an env var is not found; but `os.LookupEnv()` returns a second boolean value (by convention named `ok`) that is set to true if the env var was present but set to the empty string, or false if the env var truly was not present. Also, when getting a value from a map, the "comma-OK" idiom can be used for the same purpose.

`pgxtras.RowToStructBySnakeToCamelName()` and `pgxtras.RowToAddrOfStructBySnakeToCamelName()`

The pgx library has convenience functions `pgx.RowToStructByName()` and `pgx.RowToAddrOfStructByName()`, which ignore case when assigning column results to struct fields.

The pgextras convenience functions, `pgxtras.RowToStructBySnakeToCamelName()` and `pgxtras.RowToAddrOfStructBySnakeToCamelName()`, cover the common case of database columns being named in `snake_case` and Go struct fields being named in `CamelCase`. These are both rather common naming conventions, and these two convenince methods translate between them to relieve the user of having to use any special tags in Go structs, or any `as` column aliasing in SQL.

## `pgxtras.RowToStructBySimpleName()` and `pgxtras.RowToAddrOfStructBySimpleName()` `pgxtras.RowToStructBySnakeToCamelName()` and `pgxtras.RowToAddrOfStructBySnakeToCamelName()`, above, didn't do a perfect job of capturing the way people really name columns in SQL nor struct fields in Go.

Here is an obvious example:

In translating SQL column names to Go struct field names, one would expect

name ==> Name
city ==> City

But following strict camel-casing rules, we get this translation:

id ==> Id

whereas surely we would prefer

id ==> ID

(To get CamelCase struct field `ID` from a snake-case column name, the column would have to be named `i_d`. Yuk.)

Clearly `pgxtras.RowToStructBySnakeToCamelName()` and `pgxtras.RowToAddrOfStructBySnakeToCamelName()` did not capture the subtleties of translating common SQL column names to common Go struct field names.

So `pgxtras.RowToStructBySimpleName()` and `pgxtras.RowToAddrOfStructBySimpleName()` take a different approach. SQL column names and Go struct fields are compared with each other by lowercasing and stripping all underscores, like so:

SQL column name   "simple" name      Go struct field name
---------------------------------------------------------
first_name`   ==> `firsname`    <== `FirstName`
last_name`    ==> `lastname`    <== `LastName`
name`         ==> `name`        <== `Name`
city`         ==> `city`        <== `City`
id`           ==> `id`          <== `ID`
http_address` ==> `httpaddress` <== `HTTPAddress`

This way of determining which SQL column names go with which Go struct field names should cover a lot more of the standard naming conventions of both languages.

Index

• func CollectOneRowOK[T any](rows pgx.Rows, fn pgx.RowToFunc[T]) (T, bool, error)
• func RowToAddrOfStructBySimpleName[T any](row pgx.CollectableRow) (*T, error)
• func RowToAddrOfStructBySnakeToCamelName[T any](row pgx.CollectableRow) (*T, error)
• func RowToMapStrStr(row pgx.CollectableRow) (map[string]string, error)
• func RowToStructBySimpleName[T any](row pgx.CollectableRow) (T, error)
• func RowToStructBySnakeToCamelName[T any](row pgx.CollectableRow) (T, error)
• func SnakeToCamel(s string) string
• type Execer
• type Querier
• type QuerierExecer

Functions

func CollectOneRowOK

func CollectOneRowOK[T any](rows pgx.Rows, fn pgx.RowToFunc[T]) (T, bool, error)

CollectOneRowOK is CollectOneRow with the "comma OK idiom": think 'val, ok := myMap["foo"]' or os.GetEnv (no comma OK idiom) versus os.LookupEnv (follows comma OK idiom). If no rows are found, the second return value is false (instead of returning error ErrNoRows as CollectOneRow does). If a row is found, the second return value is true.

func RowToAddrOfStructBySimpleName

func RowToAddrOfStructBySimpleName[T any](row pgx.CollectableRow) (*T, error)

RowToStructBySimpleName returns the address of a T scanned from row. T must be a struct. T must have the same number of named public fields as row has columns. The row columns and T fields will by matched by name. The matching will be done on the "simple" names of each row column and each struct field. A "simple" name is lowercased with all underscores removed.

func RowToAddrOfStructBySnakeToCamelName

func RowToAddrOfStructBySnakeToCamelName[T any](row pgx.CollectableRow) (*T, error)

RowToAddrOfStructBySnakeToCamelName returns the address of a T scanned from row. T must be a struct. T must have the same number of named public fields as row has fields. The row and T fields will by matched by name, converting snake case column names to camel case struct field names.

func RowToMapStrStr

func RowToMapStrStr(row pgx.CollectableRow) (map[string]string, error)

RowToMapStrStr returns a map scanned from row.

func RowToStructBySimpleName

func RowToStructBySimpleName[T any](row pgx.CollectableRow) (T, error)

RowToStructBySimpleName returns a T scanned from row. T must be a struct. T must have the same number of named public fields as row has columns. The row columns and T fields will by matched by name. The matching will be done on the "simple" names of each row column and each struct field. A "simple" name is lowercased with all underscores removed.

func RowToStructBySnakeToCamelName

func RowToStructBySnakeToCamelName[T any](row pgx.CollectableRow) (T, error)

RowToStructBySnakeToCamelName returns a T scanned from row. T must be a struct. T must have the same number of named public fields as row has fields. The row and T fields will by matched by name, converting snake case column names to camel case struct field names.

func SnakeToCamel

func SnakeToCamel(s string) string

SnakeToCamel takes a_typical_db_col in snake case and translates it to TheCamelCase found in public fields of Go structs.

Types

type Execer

type Execer interface {
	Exec(ctx context.Context, sql string, args ...any) (pgconn.CommandTag, error)
}

Execer is an interface that wraps the Exec method used by pgx.Conn, pgxpool.Pool, pgx.Tx, and pgxpool.Tx.

Feel free to copy this interface directly into your code instead of importing this module just to use this interface: The licence is quite permissive.

Write your database querying functions and methods with this interface as one of the args. By doing so, your function or method can run a query using a pgx.Conn, or a pgxpool.Pool , or a pgx.Tx, or a pgxpool.Tx.

This makes your databae query function work in the following scenarios with no code changes: 1) a command-line tool (a single database connection pgx.Conn) 2) a web server (a pool pgxpool.Pool) 3) as part of a larger transaction (pgx.Tx or pgxpool.Tx),

type Querier

type Querier interface {
	Query(ctx context.Context, sql string, args ...any) (pgx.Rows, error)
}

Querier is an interface that wraps the Query method used by pgx.Conn, pgxpool.Pool, pgx.Tx, and pgxpool.Tx.

Feel free to copy this interface directly into your code instead of importing this module just to use this interface: The licence is quite permissive.

Write your database querying functions and methods with this interface as one of the args. By doing so, your function or method can run a query using a pgx.Conn, or a pgxpool.Pool , or a pgx.Tx, or a pgxpool.Tx.

This makes your databae query function work in the following scenarios with no code changes: 1) a command-line tool (a single database connection pgx.Conn) 2) a web server (a pool pgxpool.Pool) 3) as part of a larger transaction (pgx.Tx or pgxpool.Tx),

type QuerierExecer

type QuerierExecer interface {
	Querier
	Execer
}

QuerierExecer combines the Querier and Execer interfaces for situations where you write a function or method that performs both select statements and inserts/updates/DDL, etc.

Feel free to copy this interface directly into your code instead of importing this module just to use this interface: The licence is quite permissive.