pgxutil

README

pgxutil

A collection of helpers to deal with pgx toolkit.

Example:

package main

import (
	"context"
	"fmt"
	"log"

	"github.com/allisson/pgxutil/v2"
	"github.com/jackc/pgx/v5"
)

type Player struct {
	ID   int    `db:"id"`
	Name string `db:"name" fieldtag:"insert,update"`
	Age  int    `db:"age" fieldtag:"insert,update"`
}

func main() {
	ctx := context.Background()
	// Run a database with docker: docker run --name test --restart unless-stopped -e POSTGRES_USER=user -e POSTGRES_PASSWORD=password -e POSTGRES_DB=pgxutil -p 5432:5432 -d postgres:14-alpine
	// Connect to database
	conn, err := pgx.Connect(ctx, "postgres://user:password@localhost/pgxutil?sslmode=disable")
	if err != nil {
		log.Fatal(err)
	}
	defer conn.Close(ctx)

	// Create table
	_, err = conn.Exec(ctx, `
		CREATE TABLE IF NOT EXISTS players(
			id SERIAL PRIMARY KEY,
			name VARCHAR NOT NULL,
			age INTEGER NOT NULL
		)
	`)
	if err != nil {
		log.Fatal(err)
	}

	// Insert players
	r9 := Player{
		Name: "Ronaldo Fenômeno",
		Age:  44,
	}
	r10 := Player{
		Name: "Ronaldinho Gaúcho",
		Age:  41,
	}
	tag := "insert" // will use fields with fieldtag:"insert"
	if err := pgxutil.Insert(ctx, conn, tag, "players", &r9); err != nil {
		log.Fatal(err)
	}
	if err := pgxutil.Insert(ctx, conn, tag, "players", &r10); err != nil {
		log.Fatal(err)
	}

	// Get player
	findOptions := pgxutil.NewFindOptions().WithFilter("name", r10.Name)
	if err := pgxutil.Get(ctx, conn, "players", findOptions, &r10); err != nil {
		log.Fatal(err)
	}
	findOptions = pgxutil.NewFindOptions().WithFilter("name", r9.Name)
	if err := pgxutil.Get(ctx, conn, "players", findOptions, &r9); err != nil {
		log.Fatal(err)
	}

	// Select players
	players := []*Player{}
	findAllOptions := pgxutil.NewFindAllOptions().WithLimit(10).WithOffset(0).WithOrderBy("name asc")
	if err := pgxutil.Select(ctx, conn, "players", findAllOptions, &players); err != nil {
		log.Fatal(err)
	}
	for _, p := range players {
		fmt.Printf("%#v\n", p)
	}

	// Update player
	tag = "update" // will use fields with fieldtag:"update"
	r10.Name = "Ronaldinho Bruxo"
	if err := pgxutil.Update(ctx, conn, tag, "players", r10.ID, &r10); err != nil {
		log.Fatal(err)
	}

	// Delete player
	if err := pgxutil.Delete(ctx, conn, "players", r9.ID); err != nil {
		log.Fatal(err)
	}
}

Options for FindOptions and FindAllOptions:

package main

import (
	"github.com/allisson/pgxutil/v2"
)

func main() {
	findOptions := pgxutil.NewFindOptions().
		WithFields([]string{"id", "name"}). // Return only id and name fields
		WithFilter("id", 1).                // WHERE id = 1
		WithFilter("id", nil).              // WHERE id IS NULL
		WithFilter("id.in", "1,2,3").       // WHERE id IN (1, 2, 3)
		WithFilter("id.notin", "1,2,3").    // WHERE id NOT IN ($1, $2, $3)
		WithFilter("id.not", 1).            // WHERE id <> 1
		WithFilter("id.gt", 1).             // WHERE id > 1
		WithFilter("id.gte", 1).            // WHERE id >= 1
		WithFilter("id.lt", 1).             // WHERE id < 1
		WithFilter("id.lte", 1).            // WHERE id <= 1
		WithFilter("id.like", 1).           // WHERE id LIKE 1
		WithFilter("id.null", true).        // WHERE id.null IS NULL
		WithFilter("id.null", false)        // WHERE id.null IS NOT NULL

	findAllOptions := pgxutil.NewFindAllOptions().
		WithFields([]string{"id", "name"}). // Return only id and name fields
		WithFilter("id", 1).                // WHERE id = 1
		WithFilter("id", nil).              // WHERE id IS NULL
		WithFilter("id.in", "1,2,3").       // WHERE id IN (1, 2, 3)
		WithFilter("id.notin", "1,2,3").    // WHERE id NOT IN ($1, $2, $3)
		WithFilter("id.not", 1).            // WHERE id <> 1
		WithFilter("id.gt", 1).             // WHERE id > 1
		WithFilter("id.gte", 1).            // WHERE id >= 1
		WithFilter("id.lt", 1).             // WHERE id < 1
		WithFilter("id.lte", 1).            // WHERE id <= 1
		WithFilter("id.like", 1).           // WHERE id LIKE 1
		WithFilter("id.null", true).        // WHERE id.null IS NULL
		WithFilter("id.null", false).       // WHERE id.null IS NOT NULL
		WithLimit(10).                      // LIMIT 10
		WithOffset(0).                      // OFFSET 0
		WithOrderBy("name asc").            // ORDER BY name asc
		WithForUpdate("SKIP LOCKED")        // FOR UPDATE SKIP LOCKED
}

Documentation

Index

• func Delete(ctx context.Context, db Querier, tableName string, id interface{}) error
• func DeleteWithOptions(ctx context.Context, db Querier, tableName string, options *DeleteOptions) error
• func Get(ctx context.Context, db Querier, tableName string, options *FindOptions, ...) error
• func Insert(ctx context.Context, db Querier, tag, tableName string, ...) error
• func Select(ctx context.Context, db Querier, tableName string, options *FindAllOptions, ...) error
• func Update(ctx context.Context, db Querier, tag, tableName string, id interface{}, ...) error
• func UpdateWithOptions(ctx context.Context, db Querier, tableName string, options *UpdateOptions) error
• type DeleteOptions
  • func NewDeleteOptions() *DeleteOptions
• type FindAllOptions
  • func NewFindAllOptions() *FindAllOptions
• type FindOptions
  • func NewFindOptions() *FindOptions
• type Querier
• type UpdateOptions
  • func NewUpdateOptions() *UpdateOptions

Functions

func Delete

func Delete(ctx context.Context, db Querier, tableName string, id interface{}) error

Delete is a high-level function that calls sqlquery.DeleteQuery and pgx Exec.

func DeleteWithOptions

func DeleteWithOptions(ctx context.Context, db Querier, tableName string, options *DeleteOptions) error

DeleteWithOptions is a high-level function that calls sqlquery.DeleteWithOptionsQuery and pgx Exec.

func Get

func Get(ctx context.Context, db Querier, tableName string, options *FindOptions, dst interface{}) error

Get is a high-level function that calls sqlquery.FindQuery and scany pgxscan.Get function.

func Insert

func Insert(ctx context.Context, db Querier, tag, tableName string, structValue interface{}) error

Insert is a high-level function that calls sqlquery.InsertQuery and pgx Exec.

func Select

func Select(ctx context.Context, db Querier, tableName string, options *FindAllOptions, dst interface{}) error

Select is a high-level function that calls sqlquery.FindAllQuery and scany pgxscan.Select function.

func Update

func Update(ctx context.Context, db Querier, tag, tableName string, id interface{}, structValue interface{}) error

Update is a high-level function that calls sqlquery.UpdateQuery and pgx Exec.

func UpdateWithOptions

func UpdateWithOptions(ctx context.Context, db Querier, tableName string, options *UpdateOptions) error

UpdateWithOptions is a high-level function that calls sqlquery.UpdateWithOptionsQuery and pgx Exec.

Types

type DeleteOptions

type DeleteOptions = sqlquery.DeleteOptions

func NewDeleteOptions

func NewDeleteOptions() *DeleteOptions

NewDeleteOptions returns a DeleteOptions

type FindAllOptions

type FindAllOptions = sqlquery.FindAllOptions

func NewFindAllOptions

func NewFindAllOptions() *FindAllOptions

NewFindAllOptions returns a FindAllOptions.

type FindOptions

type FindOptions = sqlquery.FindOptions

func NewFindOptions

func NewFindOptions() *FindOptions

NewFindOptions returns a FindOptions.

type Querier

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

Querier is a abstraction over *pgxpool.Pool/*pgx.Conn/pgx.Tx.

type UpdateOptions

type UpdateOptions = sqlquery.UpdateOptions

func NewUpdateOptions

func NewUpdateOptions() *UpdateOptions

NewUpdateOptions returns a UpdateOptions