pgxutil

README

pgxutil

pgxutil is a collection of utilities for working with pgx. They are things I personally find useful and experiments that may eventually be incorporated into pgx.

It includes higher level functions such as Select, Insert, InsertReturning, Update, and UpdateReturning. It also includes *Row variants of these functions that require exactly one row to be selected or modified. Queue* variants work queue into a *pgx.Batch instead of directly executing the SQL.

It also includes interfaces Queryer, Execer, and DB. Queryer and Execer are satisfied by the Query and Exec methods respectively while DB is the common methods implemented by *pgx.Conn, *pgxpool.Pool, and pgx.Tx. These interfaces allow for more generic code that can work with any of these types.

Package Status

The API may change at any time or the package may be abandoned. It may be better to fork or copy the code into your own projects rather than directly depending on this package.

Documentation

Index

• func ExecRow(ctx context.Context, db Queryer, sql string, args ...any) (pgconn.CommandTag, error)
• func Insert(ctx context.Context, db Queryer, tableName any, rows []map[string]any) (pgconn.CommandTag, error)
• func InsertReturning[T any](ctx context.Context, db Queryer, tableName any, rows []map[string]any, ...) ([]T, error)
• func InsertRow(ctx context.Context, db Queryer, tableName any, values map[string]any) error
• func InsertRowReturning[T any](ctx context.Context, db Queryer, tableName any, values map[string]any, ...) (T, error)
• func QueueExecRow(batch *pgx.Batch, sql string, args ...any)
• func QueueInsert(batch *pgx.Batch, tableName any, rows []map[string]any)
• func QueueInsertReturning[T any](batch *pgx.Batch, tableName any, rows []map[string]any, returningClause string, ...)
• func QueueInsertRow(batch *pgx.Batch, tableName any, values map[string]any)
• func QueueInsertRowReturning[T any](batch *pgx.Batch, tableName any, values map[string]any, returningClause string, ...)
• func QueueSelect[T any](batch *pgx.Batch, sql string, args []any, scanFn pgx.RowToFunc[T], result *[]T)
• func QueueSelectRow[T any](batch *pgx.Batch, sql string, args []any, scanFn pgx.RowToFunc[T], result *T)
• func QueueUpdate(batch *pgx.Batch, tableName any, setValues, whereValues map[string]any, ...)
• func QueueUpdateReturning[T any](batch *pgx.Batch, tableName any, setValues, whereValues map[string]any, ...)
• func QueueUpdateRow(batch *pgx.Batch, tableName any, setValues, whereValues map[string]any)
• func QueueUpdateRowReturning[T any](batch *pgx.Batch, tableName any, setValues, whereValues map[string]any, ...)
• func Select[T any](ctx context.Context, db Queryer, sql string, args []any, ...) ([]T, error)
• func SelectAllMap(ctx context.Context, db Queryer, sql string, args ...any) ([]map[string]any, error)
• func SelectAllStringMap(ctx context.Context, db Queryer, sql string, args ...any) ([]map[string]string, error)
• func SelectAllStruct(ctx context.Context, db Queryer, dst any, sql string, args ...any) error
• func SelectColumn[T any](ctx context.Context, db Queryer, sql string, args ...any) ([]T, error)
• func SelectMap(ctx context.Context, db Queryer, sql string, args ...any) (map[string]any, error)
• func SelectRow[T any](ctx context.Context, db Queryer, sql string, args []any, ...) (T, error)
• func SelectStringMap(ctx context.Context, db Queryer, sql string, args ...any) (map[string]string, error)
• func SelectStruct(ctx context.Context, db Queryer, dst any, sql string, args ...any) error
• func SelectValue[T any](ctx context.Context, db Queryer, sql string, args ...any) (T, error)
• func Update(ctx context.Context, db Queryer, tableName any, ...) (pgconn.CommandTag, error)
• func UpdateReturning[T any](ctx context.Context, db Queryer, tableName any, ...) ([]T, error)
• func UpdateRow(ctx context.Context, db Queryer, tableName any, ...) error
• func UpdateRowReturning[T any](ctx context.Context, db Queryer, tableName any, ...) (T, error)
• type DB
• type Execer
• type Queryer
• type SQLValue

Functions

func ExecRow

func ExecRow(ctx context.Context, db Queryer, sql string, args ...any) (pgconn.CommandTag, error)

ExecRow executes SQL with args on db. It returns an error unless exactly one row is affected.

func Insert

func Insert(ctx context.Context, db Queryer, tableName any, rows []map[string]any) (pgconn.CommandTag, error)

Insert inserts rows into tableName. tableName must be a string or pgx.Identifier. rows can include SQLValue to use a raw SQL expression as a value.

func InsertReturning

func InsertReturning[T any](ctx context.Context, db Queryer, tableName any, rows []map[string]any, returningClause string, scanFn pgx.RowToFunc[T]) ([]T, error)

InsertReturning inserts rows into tableName with returningClause and returns the []T produced by scanFn. tableName must be a string or pgx.Identifier. rows can include SQLValue to use a raw SQL expression as a value.

func InsertRow

func InsertRow(ctx context.Context, db Queryer, tableName any, values map[string]any) error

InsertRow inserts values into tableName. tableName must be a string or pgx.Identifier. values can include SQLValue to use a raw SQL expression as a value.

func InsertRowReturning

func InsertRowReturning[T any](ctx context.Context, db Queryer, tableName any, values map[string]any, returningClause string, scanFn pgx.RowToFunc[T]) (T, error)

InsertRowReturning inserts values into tableName with returningClause and returns the T produced by scanFn. tableName must be a string or pgx.Identifier. values can include SQLValue to use a raw SQL expression as a value.

func QueueExecRow

func QueueExecRow(batch *pgx.Batch, sql string, args ...any)

QueueExecRow queues sql with args. The response is considered an error unless exactly one row is affected.

func QueueInsert

func QueueInsert(batch *pgx.Batch, tableName any, rows []map[string]any)

QueueInsert queues the insert of rows into tableName. tableName must be a string or pgx.Identifier. rows can include SQLValue to use a raw SQL expression as a value.

func QueueInsertReturning

func QueueInsertReturning[T any](batch *pgx.Batch, tableName any, rows []map[string]any, returningClause string, scanFn pgx.RowToFunc[T], result *[]T)

QueueInsertReturning queues the insert of rows into tableName with returningClause. tableName must be a string or pgx.Identifier. rows can include SQLValue to use a raw SQL expression as a value. scanFn will be used to populate result when the response to this query is received.

func QueueInsertRow

func QueueInsertRow(batch *pgx.Batch, tableName any, values map[string]any)

QueueInsertRow queues the insert of values into tableName. tableName must be a string or pgx.Identifier. values can include SQLValue to use a raw SQL expression as a value.

func QueueInsertRowReturning

func QueueInsertRowReturning[T any](batch *pgx.Batch, tableName any, values map[string]any, returningClause string, scanFn pgx.RowToFunc[T], result *T)

QueueInsertRowReturning queues the insert of values into tableName with returningClause. tableName must be a string or pgx.Identifier. values can include SQLValue to use a raw SQL expression as a value. scanFn will be used to populate result when the response to this query is received.

func QueueSelect

func QueueSelect[T any](batch *pgx.Batch, sql string, args []any, scanFn pgx.RowToFunc[T], result *[]T)

QueueSelect queues sql with args into batch. scanFn will be used to populate result when the response to this query is received.

func QueueSelectRow

func QueueSelectRow[T any](batch *pgx.Batch, sql string, args []any, scanFn pgx.RowToFunc[T], result *T)

QueueSelectRow queues sql with args into batch. scanFn will be used to populate result when the response to this query is received.

func QueueUpdate

func QueueUpdate(batch *pgx.Batch, tableName any, setValues, whereValues map[string]any, commandTag *pgconn.CommandTag)

QueueUpdate queues the update of rows matching whereValues in tableName with setValues. tableName must be a string or pgx.Identifier. setValues and whereValues can include SQLValue to use a raw SQL expression as a value. commandTag will be populated when the response to this query is received. commandTag may be nil.

func QueueUpdateReturning

func QueueUpdateReturning[T any](batch *pgx.Batch, tableName any, setValues, whereValues map[string]any, returningClause string, scanFn pgx.RowToFunc[T], result *[]T)

QueueUpdateReturning queues the update of rows matching whereValues in tableName with setValues. tableName must be a string or pgx.Identifier. setValues and whereValues can include SQLValue to use a raw SQL expression as a value. scanFn will be used to populate the result when the response to this query is received.

func QueueUpdateRow

func QueueUpdateRow(batch *pgx.Batch, tableName any, setValues, whereValues map[string]any)

QueueUpdateRow queues the update of a row matching whereValues in tableName with setValues. tableName must be a string or pgx.Identifier. setValues and whereValues can include SQLValue to use a raw SQL expression as a value. The response is considered an error unless exactly one row is affected.

func QueueUpdateRowReturning

func QueueUpdateRowReturning[T any](batch *pgx.Batch, tableName any, setValues, whereValues map[string]any, returningClause string, scanFn pgx.RowToFunc[T], result *T)

QueueUpdateRowReturning queues the update of a row matching whereValues in tableName with setValues. tableName must be a string or pgx.Identifier. setValues and whereValues can include SQLValue to use a raw SQL expression as a value. scanFn will be used to populate the result when the response to this query is received. The response is considered an error unless exactly one row is affected.

func Select

func Select[T any](ctx context.Context, db Queryer, sql string, args []any, scanFn pgx.RowToFunc[T]) ([]T, error)

Select executes sql with args on db and returns the []T produced by scanFn.

func SelectAllMap

func SelectAllMap(ctx context.Context, db Queryer, sql string, args ...any) ([]map[string]any, error)

SelectAllMap selects rows into a map slice.

Deprecated: Prefer Select with pgx.RowToMap.

func SelectAllStringMap

func SelectAllStringMap(ctx context.Context, db Queryer, sql string, args ...any) ([]map[string]string, error)

SelectAllStringMap selects rows into a map slice where all values are strings.

Deprecated: Prefer Select.

func SelectAllStruct

func SelectAllStruct(ctx context.Context, db Queryer, dst any, sql string, args ...any) error

SelectAllStruct selects rows into dst. dst must be a slice of struct or pointer to struct. The values are assigned positionally to the exported struct fields.

Deprecated: Prefer Select with pgx.RowToAddrOfStruct.

func SelectColumn

func SelectColumn[T any](ctx context.Context, db Queryer, sql string, args ...any) ([]T, error)

SelectColumn selects a column of T.

Deprecated: Prefer Select with pgx.RowTo.

func SelectMap

func SelectMap(ctx context.Context, db Queryer, sql string, args ...any) (map[string]any, error)

SelectMap selects a single row into a map. An error will be returned if no rows are found.

Deprecated: Prefer SelectRow with pgx.RowToMap.

func SelectRow

func SelectRow[T any](ctx context.Context, db Queryer, sql string, args []any, scanFn pgx.RowToFunc[T]) (T, error)

SelectRow executes sql with args on db and returns the T produced by scanFn. The query should return one row. If no rows are found returns an error where errors.Is(pgx.ErrNoRows) is true. Returns an error if more than one row is returned.

func SelectStringMap

func SelectStringMap(ctx context.Context, db Queryer, sql string, args ...any) (map[string]string, error)

SelectStringMap selects a single row into a map where all values are strings. An error will be returned if no rows are found.

Deprecated: Prefer SelectRow.

func SelectStruct

func SelectStruct(ctx context.Context, db Queryer, dst any, sql string, args ...any) error

SelectStruct selects a single row into struct dst. An error will be returned if no rows are found. The values are assigned positionally to the exported struct fields.

Deprecated: Prefer SelectRow with pgx.RowToAddrOfStruct.

func SelectValue

func SelectValue[T any](ctx context.Context, db Queryer, sql string, args ...any) (T, error)

SelectValue selects a single T. An error will be returned if no rows are found.

Deprecated: Prefer SelectRow with pgx.RowTo.

func Update

func Update(ctx context.Context, db Queryer, tableName any, setValues, whereValues map[string]any) (pgconn.CommandTag, error)

Update updates rows matching whereValues in tableName with setValues. tableName must be a string or pgx.Identifier. setValues and whereValues can include SQLValue to use a raw SQL expression as a value.

func UpdateReturning

func UpdateReturning[T any](ctx context.Context, db Queryer, tableName any, setValues, whereValues map[string]any, returningClause string, scanFn pgx.RowToFunc[T]) ([]T, error)

UpdateReturning updates rows matching whereValues in tableName with setValues. It includes returningClause and returns the []T produced by scanFn. tableName must be a string or pgx.Identifier. setValues and whereValues can include SQLValue to use a raw SQL expression as a value.

func UpdateRow

func UpdateRow(ctx context.Context, db Queryer, tableName any, setValues, whereValues map[string]any) error

UpdateRow updates a row matching whereValues in tableName with setValues. Returns an error unless exactly one row is updated. tableName must be a string or pgx.Identifier. setValues and whereValues can include SQLValue to use a raw SQL expression as a value.

func UpdateRowReturning

func UpdateRowReturning[T any](ctx context.Context, db Queryer, tableName any, setValues, whereValues map[string]any, returningClause string, scanFn pgx.RowToFunc[T]) (T, error)

UpdateRowReturning updates a row matching whereValues in tableName with setValues. It includes returningClause and returns the T produced by scanFn. Returns an error unless exactly one row is updated. tableName must be a string or pgx.Identifier. setValues and whereValues can include SQLValue to use a raw SQL expression as a value.

Types

type DB

type DB interface {
	// Begin starts a new pgx.Tx. It may be a true transaction or a pseudo nested transaction implemented by savepoints.
	Begin(ctx context.Context) (pgx.Tx, error)

	CopyFrom(ctx context.Context, tableName pgx.Identifier, columnNames []string, rowSrc pgx.CopyFromSource) (int64, error)
	Exec(ctx context.Context, sql string, arguments ...any) (pgconn.CommandTag, error)
	Query(ctx context.Context, sql string, optionsAndArgs ...interface{}) (pgx.Rows, error)
	QueryRow(ctx context.Context, sql string, args ...any) pgx.Row
	SendBatch(ctx context.Context, b *pgx.Batch) (br pgx.BatchResults)
}

DB is the interface pgxutil uses to access the database. It is satisfied by *pgx.Conn, pgx.Tx, *pgxpool.Pool, etc.

type Execer

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

type Queryer

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

type SQLValue

type SQLValue string

SQLValue is a SQL expression intended for use where a value would normally be expected. It is not escaped or sanitized.