pgh

package module
v1.0.1 Latest Latest
Warning

This package is not in the latest version of its module.

Go to latest
Published: Jan 19, 2025 License: MIT Imports: 10 Imported by: 0

README

PGH (PGX Helpers)

A Go package that provides helper functions to combine the power of:

  • Squirrel - SQL builder
  • pgx - PostgreSQL driver
  • scany - Scanning query results into Golang structs

Go Reference Go Report Card

Key Features

  • Generic SQL query execution helpers with proper error handling and context support
  • Batch operations with automatic splitting of large datasets
  • SQL builder integration with Squirrel
  • Scanning query results into structs using scany
  • PostgreSQL-specific error handling utilities

Installation

go get github.com/n-r-w/pgh

Requirements

  • Go 1.22 or higher
  • PostgreSQL database

Usage Examples

Using with SQL Builder
// Build and execute a query
import (
    "context"
    sq "github.com/n-r-w/squirrel"
    "github.com/n-r-w/pgh"
)

query := pgh.Builder().
    Select("id", "name", "email").
    From("users").
    Where(sq.Eq{"status": "active"})

var users []User
err := pgh.Select(ctx, db, query, &users)
Query Execution with plain SQL
import (
    "context"
    "github.com/n-r-w/pgh"
)

// Execute a simple query
tag, err := pgh.ExecPlain(ctx, db, "UPDATE users SET status = $1 WHERE id = $2",
    pgh.Args{"active", userID})

// Select multiple rows into a slice
var users []User
err := pgh.SelectPlain(ctx, db,
    "SELECT id, name, email FROM users WHERE status = $1",
    &users, pgh.Args{"active"})

// Select a single row
var user User
err := pgh.SelectOnePlain(ctx, db,
    "SELECT id, name, email FROM users WHERE id = $1",
    &user, pgh.Args{userID})
Batch Operations
import (
    "context"
    "github.com/jackc/pgx/v5"
    "github.com/n-r-w/pgh"
)

// Insert multiple rows in batches
values := []pgh.Args{
    {1, "John", "john@example.com"},
    {2, "Jane", "jane@example.com"},
}
rowsAffected, err := pgh.InsertSplitPlain(ctx, db,
    "INSERT INTO users (id, name, email) VALUES", // note: VALUES keyword at the end
    values, 100) // Split into batches of 100

// Execute multiple different queries in a batch
batch := &pgx.Batch{}
batch.Queue("UPDATE users SET status = $1 WHERE id = $2", "active", 1)
batch.Queue("INSERT INTO audit_log (user_id, action) VALUES ($1, $2)", 1, "status_update")

// Send batch and get total rows affected
rowsAffected, err := pgh.SendBatch(ctx, db, batch)
Error Handling
import "github.com/n-r-w/pgh"

// Error handling with PostgreSQL-specific error codes
if err != nil {
    if pgh.IsNoRows(err) {
        // Handle no rows found
        // Handles both pgx.ErrNoRows and PostgreSQL 'no_data_found' error code
    }
    if pgh.IsUniqueViolation(err) {
        // Handle unique constraint violation
        // Maps to PostgreSQL error code '23505'
    }
    if pgh.IsForeignKeyViolation(err) {
        // Handle foreign key violation
        // Maps to PostgreSQL error code '23503'
    }
}

For more PostgreSQL error codes, refer to: https://www.postgresql.org/docs/17/errcodes-appendix.html

License

This project is licensed under the MIT License - see the LICENSE file for details.

Documentation

Overview

Package pgh is a generated GoMock package.

Package pgh provides helpers for working with pgx

Package pgh is a generated GoMock package.

Index

Constants

This section is empty.

Variables

This section is empty.

Functions

func BeginFunc added in v1.0.1

func BeginFunc(ctx context.Context, conn ITransactionBeginner,
	f func(ctx context.Context, tx pgx.Tx) error,
) error

BeginFunc starts a transaction, executes the callback function, and handles commit/rollback automatically. If the callback returns an error, the transaction is rolled back. Otherwise, it is committed. If commit/rollback fails, the error is wrapped with the original error if any.

func Builder

func Builder() sq.StatementBuilderType

Builder creates a new instance of squirrel.StatementBuilderType for building queries

func Exec

func Exec(ctx context.Context, querier IQuerier, sqlizer sq.Sqlizer) (pgconn.CommandTag, error)

Exec executes a modification query. Querier can be either pgx.Tx or pg_types.Pool

func ExecBatch

func ExecBatch(ctx context.Context, queries []sq.Sqlizer, tx IBatcher) (rowsAffected int64, err error)

ExecBatch executes a batch of queries with error checking. tx can be either pgx.Tx or pg_types.Pool.

func ExecPlain

func ExecPlain(ctx context.Context, querier IQuerier, sql string, args Args) (pgconn.CommandTag, error)

ExecPlain executes a modification query. Querier can be either pgx.Tx or pg_types.Pool

func ExecSplit

func ExecSplit(ctx context.Context, tx IBatcher, queries []sq.Sqlizer, splitSize int) (rowsAffected int64, err error)

ExecSplit splits queries into groups of splitSize and executes them separately within a transaction. tx can be either pgx.Tx or pg_types.Pool

func ExecSplitPlain

func ExecSplitPlain(
	ctx context.Context,
	tx IBatcher,
	queries []string,
	args []Args,
	splitSize int,
) (rowsAffected int64, err error)

ExecSplitPlain splits queries into groups of splitSize and executes them separately within a transaction. tx can be either pgx.Tx or pg_types.Pool ExecSplitPlain splits queries into groups of splitSize and executes them separately within a transaction. tx can be either pgx.Tx or pg_types.Pool.

func InsertSplit

func InsertSplit(
	ctx context.Context,
	tx IBatcher,
	base sq.InsertBuilder,
	values []Args,
	splitSize int,
) (rowsAffected int64, err error)

InsertSplit splits queries into groups of splitSize and executes them separately within a transaction. values - rows with the same number of values in each. tx can be either pgx.Tx or pg_types.Pool. Use this when COPY cannot be used, for example, when ON CONFLICT is required.

func InsertSplitPlain

func InsertSplitPlain(
	ctx context.Context,
	tx IBatcher,
	sql string,
	values []Args,
	splitSize int,
) (rowsAffected int64, err error)

InsertSplitPlain splits queries into groups of splitSize and executes them separately within a transaction. values - rows with the same number of values in each. tx can be either pgx.Tx or pg_types.Pool. Use this when COPY cannot be used, for example, when ON CONFLICT is required.

func InsertSplitQuery

func InsertSplitQuery[T any](
	ctx context.Context,
	tx IBatcher,
	base sq.InsertBuilder,
	values []Args,
	splitSize int,
	dst *[]T,
) (err error)

InsertSplitQuery splits queries into groups of splitSize and executes them separately within a transaction. values - rows with the same number of values in each. tx can be either pgx.Tx or pg_types.Pool. Use this when COPY cannot be used, for example, when ON CONFLICT is required. Unlike InsertSplit, it allows using the RETURNING clause to get data into dst.

func InsertValuesPlain

func InsertValuesPlain(ctx context.Context, querier IQuerier, sql string, values []Args) error

InsertValuesPlain executes a query to insert a group of values. sql should be in the form "INSERT INTO table (col1, col2)" without VALUES. VALUES is added automatically.

func IsForeignKeyViolation

func IsForeignKeyViolation(err error) bool

IsForeignKeyViolation checks if the error is a foreign key constraint violation.

func IsNoRows

func IsNoRows(err error) bool

IsNoRows checks if the error is a "no rows" error.

func IsUniqueViolation

func IsUniqueViolation(err error) bool

IsUniqueViolation checks if the error is a unique constraint violation.

func Select

func Select[T any](ctx context.Context, querier IQuerier, sqlizer sq.Sqlizer, dst *[]T) error

Select executes a query. Querier can be either pgx.Tx or pg_types.Pool

func SelectBatch

func SelectBatch[T any](ctx context.Context, queries []sq.Sqlizer, tx IBatcher, dst *[]T) error

SelectBatch executes a batch of queries with error checking. tx can be either pgx.Tx or pg_types.Pool.

func SelectFunc

func SelectFunc(ctx context.Context, querier IQuerier, sqlizer sq.Sqlizer, f func(row pgx.Row) error) error

SelectFunc executes a query and passes each row to function f. Querier can be either pgx.Tx or pg_types.Pool.

func SelectFuncPlain

func SelectFuncPlain(ctx context.Context, querier IQuerier, sql string, args Args,
	f func(pgx.Row) error,
) error

SelectFuncPlain executes a query and passes each row to function f. Querier can be either pgx.Tx or pg_types.Pool.

func SelectOne

func SelectOne[T any](ctx context.Context, querier IQuerier, sqlizer sq.Sqlizer, dst *T) error

SelectOne executes a query. Querier can be either pgx.Tx or pg_types.Pool. dst must contain a variable, not a slice.

func SelectOnePlain

func SelectOnePlain[T any](ctx context.Context, querier IQuerier, sql string, dst *T, args Args) error

SelectOnePlain executes a query. Querier can be either pgx.Tx or pg_types.Pool. dst must contain a variable, not a slice.

func SelectPlain

func SelectPlain[T any](ctx context.Context, querier IQuerier, sql string, dst *[]T, args Args) error

SelectPlain executes a query. Querier can be either pgx.Tx or pg_types.Pool

func SendBatch

func SendBatch(ctx context.Context, tx IBatcher, batch *pgx.Batch) (rowsAffected int64, err error)

SendBatch executes a batch of queries with error checking. tx can be either pgx.Tx or pg_types.Pool.

func SendBatchQuery

func SendBatchQuery[T any](ctx context.Context, tx IBatcher, batch *pgx.Batch, dst *[]T) error

SendBatchQuery executes a batch of queries with error checking. tx can be either pgx.Tx or pg_types.Pool. Returns query results as a single slice.

Types

type Args

type Args []any

Args is a slice of values for binding. Used to explicitly separate query parameters from other arguments.

type IBatcher

type IBatcher interface {
	SendBatch(ctx context.Context, b *pgx.Batch) pgx.BatchResults
}

IBatcher is a subset of pgxpool.Pool, pgx.Conn and pgx.Tx interfaces for batches

type IQuerier

type IQuerier interface {
	Query(ctx context.Context, query string, args ...any) (pgx.Rows, error)
	Exec(ctx context.Context, sql string, arguments ...any) (commandTag pgconn.CommandTag, err error)
}

IQuerier is a subset of pgxpool.Pool, pgx.Conn and pgx.Tx interfaces for queries

type ITransactionBeginner added in v1.0.1

type ITransactionBeginner interface {
	Begin(context.Context) (pgx.Tx, error)
}

ITransactionBeginner is a subset of pgxpool.Pool, pgx.Conn for starting transactions.

type MockBatchResults

type MockBatchResults struct {
	// contains filtered or unexported fields
}

MockBatchResults is a mock of BatchResults interface.

func NewMockBatchResults

func NewMockBatchResults(ctrl *gomock.Controller) *MockBatchResults

NewMockBatchResults creates a new mock instance.

func (*MockBatchResults) Close

func (m *MockBatchResults) Close() error

Close mocks base method.

func (*MockBatchResults) EXPECT

EXPECT returns an object that allows the caller to indicate expected use.

func (*MockBatchResults) Exec

func (m *MockBatchResults) Exec() (pgconn.CommandTag, error)

Exec mocks base method.

func (*MockBatchResults) Query

func (m *MockBatchResults) Query() (pgx.Rows, error)

Query mocks base method.

func (*MockBatchResults) QueryRow

func (m *MockBatchResults) QueryRow() pgx.Row

QueryRow mocks base method.

type MockBatchResultsMockRecorder

type MockBatchResultsMockRecorder struct {
	// contains filtered or unexported fields
}

MockBatchResultsMockRecorder is the mock recorder for MockBatchResults.

func (*MockBatchResultsMockRecorder) Close

Close indicates an expected call of Close.

func (*MockBatchResultsMockRecorder) Exec

Exec indicates an expected call of Exec.

func (*MockBatchResultsMockRecorder) Query

Query indicates an expected call of Query.

func (*MockBatchResultsMockRecorder) QueryRow

func (mr *MockBatchResultsMockRecorder) QueryRow() *gomock.Call

QueryRow indicates an expected call of QueryRow.

type MockIBatcher

type MockIBatcher struct {
	// contains filtered or unexported fields
}

MockIBatcher is a mock of IBatcher interface.

func NewMockIBatcher

func NewMockIBatcher(ctrl *gomock.Controller) *MockIBatcher

NewMockIBatcher creates a new mock instance.

func (*MockIBatcher) EXPECT

EXPECT returns an object that allows the caller to indicate expected use.

func (*MockIBatcher) SendBatch

func (m *MockIBatcher) SendBatch(ctx context.Context, b *pgx.Batch) pgx.BatchResults

SendBatch mocks base method.

type MockIBatcherMockRecorder

type MockIBatcherMockRecorder struct {
	// contains filtered or unexported fields
}

MockIBatcherMockRecorder is the mock recorder for MockIBatcher.

func (*MockIBatcherMockRecorder) SendBatch

func (mr *MockIBatcherMockRecorder) SendBatch(ctx, b any) *gomock.Call

SendBatch indicates an expected call of SendBatch.

type MockIQuerier

type MockIQuerier struct {
	// contains filtered or unexported fields
}

MockIQuerier is a mock of IQuerier interface.

func NewMockIQuerier

func NewMockIQuerier(ctrl *gomock.Controller) *MockIQuerier

NewMockIQuerier creates a new mock instance.

func (*MockIQuerier) EXPECT

EXPECT returns an object that allows the caller to indicate expected use.

func (*MockIQuerier) Exec

func (m *MockIQuerier) Exec(ctx context.Context, sql string, arguments ...any) (pgconn.CommandTag, error)

Exec mocks base method.

func (*MockIQuerier) Query

func (m *MockIQuerier) Query(ctx context.Context, query string, args ...any) (pgx.Rows, error)

Query mocks base method.

type MockIQuerierMockRecorder

type MockIQuerierMockRecorder struct {
	// contains filtered or unexported fields
}

MockIQuerierMockRecorder is the mock recorder for MockIQuerier.

func (*MockIQuerierMockRecorder) Exec

func (mr *MockIQuerierMockRecorder) Exec(ctx, sql any, arguments ...any) *gomock.Call

Exec indicates an expected call of Exec.

func (*MockIQuerierMockRecorder) Query

func (mr *MockIQuerierMockRecorder) Query(ctx, query any, args ...any) *gomock.Call

Query indicates an expected call of Query.

type MockITransactionBeginner added in v1.0.1

type MockITransactionBeginner struct {
	// contains filtered or unexported fields
}

MockITransactionBeginner is a mock of ITransactionBeginner interface.

func NewMockITransactionBeginner added in v1.0.1

func NewMockITransactionBeginner(ctrl *gomock.Controller) *MockITransactionBeginner

NewMockITransactionBeginner creates a new mock instance.

func (*MockITransactionBeginner) Begin added in v1.0.1

func (m *MockITransactionBeginner) Begin(arg0 context.Context) (pgx.Tx, error)

Begin mocks base method.

func (*MockITransactionBeginner) EXPECT added in v1.0.1

EXPECT returns an object that allows the caller to indicate expected use.

type MockITransactionBeginnerMockRecorder added in v1.0.1

type MockITransactionBeginnerMockRecorder struct {
	// contains filtered or unexported fields
}

MockITransactionBeginnerMockRecorder is the mock recorder for MockITransactionBeginner.

func (*MockITransactionBeginnerMockRecorder) Begin added in v1.0.1

Begin indicates an expected call of Begin.

type MockRow

type MockRow struct {
	// contains filtered or unexported fields
}

MockRow is a mock of Row interface.

func NewMockRow

func NewMockRow(ctrl *gomock.Controller) *MockRow

NewMockRow creates a new mock instance.

func (*MockRow) EXPECT

func (m *MockRow) EXPECT() *MockRowMockRecorder

EXPECT returns an object that allows the caller to indicate expected use.

func (*MockRow) Scan

func (m *MockRow) Scan(arg0 ...any) error

Scan mocks base method.

type MockRowMockRecorder

type MockRowMockRecorder struct {
	// contains filtered or unexported fields
}

MockRowMockRecorder is the mock recorder for MockRow.

func (*MockRowMockRecorder) Scan

func (mr *MockRowMockRecorder) Scan(arg0 ...any) *gomock.Call

Scan indicates an expected call of Scan.

type MockRows

type MockRows struct {
	// contains filtered or unexported fields
}

MockRows is a mock of Rows interface.

func NewMockRows

func NewMockRows(ctrl *gomock.Controller) *MockRows

NewMockRows creates a new mock instance.

func (*MockRows) Close

func (m *MockRows) Close()

Close mocks base method.

func (*MockRows) CommandTag

func (m *MockRows) CommandTag() pgconn.CommandTag

CommandTag mocks base method.

func (*MockRows) Conn

func (m *MockRows) Conn() *pgx.Conn

Conn mocks base method.

func (*MockRows) EXPECT

func (m *MockRows) EXPECT() *MockRowsMockRecorder

EXPECT returns an object that allows the caller to indicate expected use.

func (*MockRows) Err

func (m *MockRows) Err() error

Err mocks base method.

func (*MockRows) FieldDescriptions

func (m *MockRows) FieldDescriptions() []pgconn.FieldDescription

FieldDescriptions mocks base method.

func (*MockRows) Next

func (m *MockRows) Next() bool

Next mocks base method.

func (*MockRows) RawValues

func (m *MockRows) RawValues() [][]byte

RawValues mocks base method.

func (*MockRows) Scan

func (m *MockRows) Scan(arg0 ...any) error

Scan mocks base method.

func (*MockRows) Values

func (m *MockRows) Values() ([]any, error)

Values mocks base method.

type MockRowsMockRecorder

type MockRowsMockRecorder struct {
	// contains filtered or unexported fields
}

MockRowsMockRecorder is the mock recorder for MockRows.

func (*MockRowsMockRecorder) Close

func (mr *MockRowsMockRecorder) Close() *gomock.Call

Close indicates an expected call of Close.

func (*MockRowsMockRecorder) CommandTag

func (mr *MockRowsMockRecorder) CommandTag() *gomock.Call

CommandTag indicates an expected call of CommandTag.

func (*MockRowsMockRecorder) Conn

func (mr *MockRowsMockRecorder) Conn() *gomock.Call

Conn indicates an expected call of Conn.

func (*MockRowsMockRecorder) Err

func (mr *MockRowsMockRecorder) Err() *gomock.Call

Err indicates an expected call of Err.

func (*MockRowsMockRecorder) FieldDescriptions

func (mr *MockRowsMockRecorder) FieldDescriptions() *gomock.Call

FieldDescriptions indicates an expected call of FieldDescriptions.

func (*MockRowsMockRecorder) Next

func (mr *MockRowsMockRecorder) Next() *gomock.Call

Next indicates an expected call of Next.

func (*MockRowsMockRecorder) RawValues

func (mr *MockRowsMockRecorder) RawValues() *gomock.Call

RawValues indicates an expected call of RawValues.

func (*MockRowsMockRecorder) Scan

func (mr *MockRowsMockRecorder) Scan(arg0 ...any) *gomock.Call

Scan indicates an expected call of Scan.

func (*MockRowsMockRecorder) Values

func (mr *MockRowsMockRecorder) Values() *gomock.Call

Values indicates an expected call of Values.

type MockTx added in v1.0.1

type MockTx struct {
	// contains filtered or unexported fields
}

MockTx is a mock of Tx interface.

func NewMockTx added in v1.0.1

func NewMockTx(ctrl *gomock.Controller) *MockTx

NewMockTx creates a new mock instance.

func (*MockTx) Begin added in v1.0.1

func (m *MockTx) Begin(arg0 context.Context) (pgx.Tx, error)

Begin mocks base method.

func (*MockTx) Commit added in v1.0.1

func (m *MockTx) Commit(arg0 context.Context) error

Commit mocks base method.

func (*MockTx) Conn added in v1.0.1

func (m *MockTx) Conn() *pgx.Conn

Conn mocks base method.

func (*MockTx) CopyFrom added in v1.0.1

func (m *MockTx) CopyFrom(arg0 context.Context, arg1 pgx.Identifier, arg2 []string, arg3 pgx.CopyFromSource) (int64, error)

CopyFrom mocks base method.

func (*MockTx) EXPECT added in v1.0.1

func (m *MockTx) EXPECT() *MockTxMockRecorder

EXPECT returns an object that allows the caller to indicate expected use.

func (*MockTx) Exec added in v1.0.1

func (m *MockTx) Exec(arg0 context.Context, arg1 string, arg2 ...any) (pgconn.CommandTag, error)

Exec mocks base method.

func (*MockTx) LargeObjects added in v1.0.1

func (m *MockTx) LargeObjects() pgx.LargeObjects

LargeObjects mocks base method.

func (*MockTx) Prepare added in v1.0.1

func (m *MockTx) Prepare(arg0 context.Context, arg1, arg2 string) (*pgconn.StatementDescription, error)

Prepare mocks base method.

func (*MockTx) Query added in v1.0.1

func (m *MockTx) Query(arg0 context.Context, arg1 string, arg2 ...any) (pgx.Rows, error)

Query mocks base method.

func (*MockTx) QueryRow added in v1.0.1

func (m *MockTx) QueryRow(arg0 context.Context, arg1 string, arg2 ...any) pgx.Row

QueryRow mocks base method.

func (*MockTx) Rollback added in v1.0.1

func (m *MockTx) Rollback(arg0 context.Context) error

Rollback mocks base method.

func (*MockTx) SendBatch added in v1.0.1

func (m *MockTx) SendBatch(arg0 context.Context, arg1 *pgx.Batch) pgx.BatchResults

SendBatch mocks base method.

type MockTxMockRecorder added in v1.0.1

type MockTxMockRecorder struct {
	// contains filtered or unexported fields
}

MockTxMockRecorder is the mock recorder for MockTx.

func (*MockTxMockRecorder) Begin added in v1.0.1

func (mr *MockTxMockRecorder) Begin(arg0 any) *gomock.Call

Begin indicates an expected call of Begin.

func (*MockTxMockRecorder) Commit added in v1.0.1

func (mr *MockTxMockRecorder) Commit(arg0 any) *gomock.Call

Commit indicates an expected call of Commit.

func (*MockTxMockRecorder) Conn added in v1.0.1

func (mr *MockTxMockRecorder) Conn() *gomock.Call

Conn indicates an expected call of Conn.

func (*MockTxMockRecorder) CopyFrom added in v1.0.1

func (mr *MockTxMockRecorder) CopyFrom(arg0, arg1, arg2, arg3 any) *gomock.Call

CopyFrom indicates an expected call of CopyFrom.

func (*MockTxMockRecorder) Exec added in v1.0.1

func (mr *MockTxMockRecorder) Exec(arg0, arg1 any, arg2 ...any) *gomock.Call

Exec indicates an expected call of Exec.

func (*MockTxMockRecorder) LargeObjects added in v1.0.1

func (mr *MockTxMockRecorder) LargeObjects() *gomock.Call

LargeObjects indicates an expected call of LargeObjects.

func (*MockTxMockRecorder) Prepare added in v1.0.1

func (mr *MockTxMockRecorder) Prepare(arg0, arg1, arg2 any) *gomock.Call

Prepare indicates an expected call of Prepare.

func (*MockTxMockRecorder) Query added in v1.0.1

func (mr *MockTxMockRecorder) Query(arg0, arg1 any, arg2 ...any) *gomock.Call

Query indicates an expected call of Query.

func (*MockTxMockRecorder) QueryRow added in v1.0.1

func (mr *MockTxMockRecorder) QueryRow(arg0, arg1 any, arg2 ...any) *gomock.Call

QueryRow indicates an expected call of QueryRow.

func (*MockTxMockRecorder) Rollback added in v1.0.1

func (mr *MockTxMockRecorder) Rollback(arg0 any) *gomock.Call

Rollback indicates an expected call of Rollback.

func (*MockTxMockRecorder) SendBatch added in v1.0.1

func (mr *MockTxMockRecorder) SendBatch(arg0, arg1 any) *gomock.Call

SendBatch indicates an expected call of SendBatch.

Directories

Path Synopsis
Package filter ...
Package filter ...

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL