README
¶
pgxx
A library that faciliatates sql queries and struct mappings with pgx
Features
- Unified support for singular connections, pools and transactions via
pgxx.DBinterface - Inserts that assign id field using returning clause
- Support for generics
- Database column mapping via
dbtags - 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
- type DB
- type FK
- type Field
- type Fields
- type ID
- type PaginationOptions
- type PaginationResults
- type Record
- type Schema
- 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 ¶
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 CollectRows ¶
CollectRows scans a T from each row
func CollectStrings ¶
CollectStrings scans rows for string values
func First ¶
First returns the first row encountered for a given table. It is equivalent to One with an empty sql string
func Many ¶
Many returns all rows encountered that satisfy the sql condition. The sql string is placed immediately after the select statement
func One ¶
One returns the first row encountered that satisfies the sql condition. The sql string is placed immediately after the select statement
func RunScript ¶
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 ¶
SetScriptFS sets the underlying fs for reading sql scripts with RunScript
Types ¶
type Columns ¶
type Columns []string
Columns is an alias for []string that contains methods that faciliate formatting sql strings
func (Columns) AssignmentList ¶
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) PrefixedList ¶
PrefixedList returns a List where each column is prefixed by the given argument. A '.' is automatically added to the prefix
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 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) IsWriteable ¶
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 ¶
Columns returns all database columns for the given fields it goes recursively through fields
func (Fields) ForeignKeys ¶
ForeignKeys are fields representing foreign keys
type ID ¶
type ID int64
ID represents a serial id
func CollectIDs ¶
CollectIDs scans each row for id value
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 ¶
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
Source Files