pgxx

README

pgxx

A library that faciliatates sql queries and struct mappings with pgx

Features

• Unified support for singular connections, pools and transactions via pgxx.DB interface
• Inserts that assign id field using returning clause
• Support for generics
• Database column mapping via db tags
• Support for pagination
• ReadOnly fields
• Support for embeded structs

Installation

go get -u github.com/cristosal/pgxx

Documentation

View godoc documentation here

https://pkg.go.dev/github.com/cristosal/pgxx

Usage

Define the struct which will map to your postgres table.

• If tags are omited, the fields are mapped to columns matching their snake_cased values.
• If TableName() method is not implemented then the snake_cased struct name is used.

type User struct {
    ID          pgxx.ID `db:"id"`
    Username    string  `db:"username"`
    Password    string  `db:"password"`
    Confirmed   bool    `db:"confirmed_at"`
}

func (*User) TableName() string {
    return "users"
}

Insert

Inserts a row into table. Insert ID is automatically assigned to struct.

u := User{
    Username: "admin",
    Password: "changeme",
}

err := pgxx.Insert(db, &u)

One

Collect one row. Takes sql argument which is placed after the select statement.

var u User

err := pgxx.One(db, &u, "WHERE id = $1", 1)

This executes the following sql query:

SELECT id, username, password FROM users WHERE id = $1

First

Same as pgxx.One but without an sql argument. Returns first row found from table.

var u User

err := pgxx.First(db, &u)

Update

Updates an entity by it's id field. The following will change the username from admin to superuser.

var u User

err := pgxx.One(db, &u, "WHERE username = $1", "admin")

u.Username = "superuser"

err = pgxx.Update(db, &u)

Many

Returns all rows which satisfy the query. Takes an sql argument which is placed after select.

var users []User

err := pgxx.Many(db, &users, "WHERE confirmed = TRUE")

Full Example

Here is a simple example of insert, update, and find one.
Please Note: Error checking has been omitted for brevity

package main

import (
    "os"
    "fmt"
    "context"

    "github.com/cristosal/pgxx"
    "github.com/jackc/pgx/v5"
)

type Person struct {
    ID      pgxx.ID `db:"id"`
    Name    string  `db:"name"`
    Age     int     `db:"age"`
}

// TableName tells pgxx which table to use for the given struct
// if not implemented pgxx will use the snake-cased version of the struct name ie) person
func (p *Person) TableName() string {
    return "person"
}

func main() {
    conn, _ := pgx.Connect(context.Background(), os.Getenv("CONNECTION_STRING"))

    p := Person{Name: "John Doe", Age: 29}

    _ = pgxx.Insert(conn, &p)

    // p.ID is now set to autgenerated id
    fmt.Printf("successfully added person with id %d\n", p.ID)
    p.Age++

    _ = pgxx.Update(conn, &p)

    var found Person
    _ = pgxx.One(conn, &found, "where name = $1", "John Doe")

    fmt.Printf("%s is %d years old\n", found.Name, found.Age) // John Doe is 30 years old 
}

Documentation

Overview

Package pgxx contains helper functions for pgx

Index

• Constants
• Variables
• func All[T any](db DB, v *[]T) error
• func ClearCache()
• func CollectRows[T any](rows pgx.Rows) (items []T, err error)
• func CollectStrings(rows pgx.Rows) ([]string, error)
• func Exec(db DB, sql string, args ...any) error
• func First(db DB, v any) error
• func Insert(db DB, v any) error
• func Many[T any](db DB, v *[]T, sql string, args ...any) error
• func One(db DB, v any, sql string, args ...any) error
• func RunScript(conn DB, script string, tdata any) error
• func SetScriptFS(dir fs.FS)
• func Update(db DB, v any) error
• type Columns
  • func (c Columns) AssignmentList(start int) string
  • func (c Columns) List() string
  • func (c Columns) PrefixedList(prefix string) string
  • func (c Columns) ValueList(start int) string
• type DB
• type FK
• type Field
  • func (f *Field) HasSchema() bool
  • func (f *Field) IsWriteable() bool
• type Fields
  • func (fields Fields) Columns() (columns Columns)
  • func (fields Fields) ForeignKeys() Fields
  • func (fields Fields) Identity() (*Field, []int, error)
  • func (fields Fields) Writeable() Fields
• type ID
  • func CollectIDs(rows pgx.Rows) ([]ID, error)
  • func ParseID(str string) (ID, error)
  • func (id ID) String() string
• type PaginationOptions
• type PaginationResults
  • func Paginate[T any](db DB, opts *PaginationOptions) (*PaginationResults[T], error)
• type Record
• type Schema
  • func Analyze(v interface{}) (sch *Schema, err error)
  • func MustAnalyze(v interface{}) *Schema
  • func (s *Schema) IsRoot() bool
• type SortDirection

Constants

const (
	SortAscending  = SortDirection("asc")
	SortDescending = SortDirection("desc")
)

Variables

var (
	ErrNoForeignKeys     = errors.New("no foreign keys were found")
	ErrNoForeignKeyMatch = errors.New("no foreign key matched")
	ErrNotFound          = fmt.Errorf("not found: %w", pgx.ErrNoRows)
)

var (
	ErrInvalidType = errors.New("invalid type")
	ErrNoIdentity  = errors.New("identity not found")
)

Functions

func All

func All[T any](db DB, v *[]T) error

All is the same as Many with an empty sql string. It will return all rows from the table deduced by v and is equivalent to a select from table.

func ClearCache

func ClearCache()

ClearCache clears the schema cache

func CollectRows

func CollectRows[T any](rows pgx.Rows) (items []T, err error)

CollectRows scans a T from each row

func CollectStrings

func CollectStrings(rows pgx.Rows) ([]string, error)

CollectStrings scans rows for string values

func Exec

func Exec(db DB, sql string, args ...any) error

Exec executes a given sql statement and returns any error encountered

func First

func First(db DB, v any) error

First returns the first row encountered for a given table. It is equivalent to One with an empty sql string

func Insert

func Insert(db DB, v any) error

Insert inserts v into it's designated table. ID is set on v if available

func Many

func Many[T any](db DB, v *[]T, sql string, args ...any) error

Many returns all rows encountered that satisfy the sql condition. The sql string is placed immediately after the select statement

func One

func One(db DB, v any, sql string, args ...any) error

One returns the first row encountered that satisfies the sql condition. The sql string is placed immediately after the select statement

func RunScript

func RunScript(conn DB, script string, tdata any) error

RunScript executes a script from the underlying fs set using SetScriptFS. scripts are run as template so it is possible to pass data onto the scripts

func SetScriptFS

func SetScriptFS(dir fs.FS)

SetScriptFS sets the underlying fs for reading sql scripts with RunScript

func Update

func Update(db DB, v any) error

Update updates v by its identity (ID field). If no id is found, Update return ErrNoIdentity

Types

type Columns

type Columns []string

Columns is an alias for []string that contains methods that faciliate formatting sql strings

func (Columns) AssignmentList

func (c Columns) AssignmentList(start int) string

AssignmentList returns an assignment list in the format of column1 = $1, column2 = $2, ... The start argument determines the initial number for the parameters.

func (Columns) List

func (c Columns) List() string

List returns a comma seperated string of columns

func (Columns) PrefixedList

func (c Columns) PrefixedList(prefix string) string

PrefixedList returns a List where each column is prefixed by the given argument. A '.' is automatically added to the prefix

func (Columns) ValueList

func (c Columns) ValueList(start int) string

ValueList returns a postgres parameter list in the format of $1, $2, ... The start value determines when counting starts.

type DB

type DB interface {
	Begin(ctx context.Context) (pgx.Tx, error)
	Exec(ctx context.Context, sql string, args ...any) (pgconn.CommandTag, error)
	QueryRow(ctx context.Context, sql string, args ...any) pgx.Row
	Query(ctx context.Context, sql string, args ...any) (pgx.Rows, error)
}

DB represents the underlying pgx connection. It can be a single conn, pool or transaction

type FK

type FK struct {
	Table  string // Foreign table name
	Column string // Foreign table column
}

FK represents foreign key field metadata

type Field

type Field struct {
	Name     string  // Name of the field in the struct
	Column   string  // Name of the database column
	Index    int     // Index of the field within a struct
	Identity bool    // Is an ID field
	ReadOnly bool    // Is only for select queries
	FK       *FK     // Foreign key meta data
	Schema   *Schema // Embeded schema
}

Field contains mapping information between struct field and database column

func (*Field) HasSchema

func (f *Field) HasSchema() bool

HasSchema returns true when the field contains an embeded schema

func (*Field) IsWriteable

func (f *Field) IsWriteable() bool

IsWriteable is true when the fields value can be included in an insert or update statement

type Fields

type Fields []Field

Fields faciliatates collection methods over fields

func (Fields) Columns

func (fields Fields) Columns() (columns Columns)

Columns returns all database columns for the given fields it goes recursively through fields

func (Fields) ForeignKeys

func (fields Fields) ForeignKeys() Fields

ForeignKeys are fields representing foreign keys

func (Fields) Identity

func (fields Fields) Identity() (*Field, []int, error)

Identity returns the first identity field found

func (Fields) Writeable

func (fields Fields) Writeable() Fields

Writeable returns all writeable fields A field is writeable if it is not marked as readonly or is an identity field

type ID

type ID int64

ID represents a serial id

func CollectIDs

func CollectIDs(rows pgx.Rows) ([]ID, error)

CollectIDs scans each row for id value

func ParseID

func ParseID(str string) (ID, error)

ParseID attempts to parse str into postgres serial id

func (ID) String

func (id ID) String() string

String is the string representation of a serial id

type PaginationOptions

type PaginationOptions struct {
	Record        Record
	Query         string
	QueryColumns  []string
	Page          int
	PageSize      int
	SortBy        string
	SortDirection SortDirection
}

PaginationOptions for configuring paginate query

type PaginationResults

type PaginationResults[T any] struct {
	Total   int64
	Items   []T
	Page    int
	Start   int
	End     int
	HasNext bool
}

PaginationResults contain results and stats from pagination query

func Paginate

func Paginate[T any](db DB, opts *PaginationOptions) (*PaginationResults[T], error)

Paginate returns paginated data for T

type Record

type Record interface{ TableName() string }

Record is the interface implemented by structs that want to explicitly specify their table name

type Schema

type Schema struct {
	Parent *Schema      // Not nil if schema represents an embeded type
	Table  string       // Database table name
	Type   reflect.Type // Underlying reflect type
	Fields Fields       // Field maps
}

Schema contains the database mapping information for a given type

func Analyze

func Analyze(v interface{}) (sch *Schema, err error)

Analyze returns a schema representing the mapping between the go type and database row. Schemas are cached by table name so as not to repeat analisis unnecesarily.

func MustAnalyze

func MustAnalyze(v interface{}) *Schema

MustAnalyze panics if schema analisis fails. See Analyze for further information

func (*Schema) IsRoot

func (s *Schema) IsRoot() bool

IsRoot is true when the schema is not embeded

type SortDirection

type SortDirection = string

SortDirection represents the sql sort direction