lyspg

package
v0.1.34 Latest Latest
Warning

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

 Go to latest Published: Dec 2, 2024 License: MIT Imports: 16 Imported by: 2

README

lyspg

Structs and functions providing generic CRUD operations on a Postgres database.

Documentation

Overview

Package lyspg contains structs and functions providing generic CRUD operations on a Postgres database.

Index

Constants

View Source 
const (
	ErrDescInsertScanFailed      string = "db.QueryRow or Scan failed"
	ErrDescUpdateExecFailed      string = "db.Exec failed"
	ErrDescGetRowsAffectedFailed string = "sqlRes.RowsAffected() failed"
)

error strings used by internal errors

View Source 
const MaxStmtPrintChars int = 5000

max number of characters of a statement to print in error logs

Variables

View Source 
var (
	ArchivedTableSuffix = "_archived"
	ArchivedTableCols   = []string{"archived_at", "archived_by_cascade"}
)
View Source 
var (
	DataUpdateViewSuffix = "_data_update"
	DataUpdateColTags    = []string{"data_update_id", "affected_id", "affected_at", "affected_by", "affected_old_values", "affected_new_values"}
)

Functions

func Archive added in v0.1.16 

func Archive[pkT PrimaryKeyType](ctx context.Context, tx pgx.Tx, schemaName, tableName, pKColName string, pkVal pkT, isCascaded bool) error

Archive moves record(s) to a table's corresponding archived table using the supplied tx the archived table name must be the source table name plus ArchivedTableSuffix the archived table must have the same columns as the source table and also the ArchivedTableCols defined above the column order does not need to be the same in the source and archived tables if this func returns an error, rollback the tx

func BulkDelete added in v0.1.32 

func BulkDelete[T any](ctx context.Context, db PoolOrTx, schemaName, tableName, columnName string, vals []T) error

BulkDelete deletes multiple records in the same table based on a column value partial success possible: if some vals are not found, an error will be returned containing the failed vals, but the other rows will be deleted

func BulkInsert 

func BulkInsert[T any](ctx context.Context, db PoolOrTx, schemaName, tableName string, inputs []T) (rowsAffected int64, err error)

BulkInsert inserts multiple records using the postgres COPY protocol T must be a struct with "db" tags

func BulkUpdate added in v0.1.32 

func BulkUpdate[T any, pkT PrimaryKeyType](ctx context.Context, db PoolOrTx, schemaName, tableName, pkColName string, inputs []T, pkVals []pkT,
	options ...UpdateOption) error

BulkUpdate changes multiple records in the same table identified by pkVals with the values contained in inputs T must be a struct with "db" tags partial success possible: if some pkVals are not found, an error will be returned containing the failed pks, but the other rows will be updated

func CheckEnumValue 

func CheckEnumValue(ctx context.Context, db PoolOrTx, dbEnum, testVal, enumName string) error

CheckEnumValue returns an error if the suppied testVal is not found in the supplied pg enum type

func DeleteByValue 

func DeleteByValue(ctx context.Context, db PoolOrTx, schemaName, tableName, columnName string, val any) error

DeleteByValue deletes from the supplied database table using the value of any column must delete at least 1 row

func DeleteUnique 

func DeleteUnique(ctx context.Context, db *pgxpool.Pool, schemaName, tableName, columnName string, uniqueVal any) error

DeleteUnique is guaranteed to delete a single row from the supplied database table using the unique value supplied

func Exists 

func Exists(ctx context.Context, db PoolOrTx, schemaName, tableName, columnName string, val any) (ret bool, err error)

Exists returns true if 1+ records exist given the supplied criterion (columnName + val) pass val = nil to check for NULL

func ExistsConditions 

func ExistsConditions(ctx context.Context, db PoolOrTx, schemaName, tableName, match string, colValMap map[string]any) (ret bool, err error)

ExistsConditions returns true if 1+ records exist matching all/any the supplied criteria, depending on match param match must be AND or OR pass val = nil to check for NULL

func GetRowCount 

func GetRowCount(ctx context.Context, db PoolOrTx, schemaName, tableName string) (rowCount int64, err error)

GetRowCount returns a straight rowcount of the supplied table

func GetRowCountExplain 

func GetRowCountExplain(ctx context.Context, db PoolOrTx, qry string, paramValues []any) (rowCount int64, err error)

GetRowCountExplain returns the estimated rowcount of the supplied qry using the query planner EXPLAIN output qry must use placeholders for params, and paramValues must be supplied

func GetRowCountPlaceholderQry 

func GetRowCountPlaceholderQry(ctx context.Context, db PoolOrTx, qry string, paramValues []any) (rowCount int64, err error)

GetRowCountPlaceholderQry returns the exact rowcount of the supplied qry qry must use placeholders for params, and paramValues must be supplied

func GetStatsRowCount 

func GetStatsRowCount(ctx context.Context, db PoolOrTx, schemaName, tableName string) (rowCount int64, err error)

func GetTableColumnNames 

func GetTableColumnNames(ctx context.Context, db PoolOrTx, schemaName, tableName string) (colNames []string, err error)

func GetTableComment 

func GetTableComment(ctx context.Context, db PoolOrTx, schemaName, tableName string) (comment string, err error)

func GetTableShortName 

func GetTableShortName(ctx context.Context, db PoolOrTx, schemaName, tableName string) (shortName string, err error)

func Insert 

func Insert[inputT any, pkT PrimaryKeyType](ctx context.Context, db PoolOrTx, schemaName, tableName, pkColName string, input inputT) (newPk pkT, err error)

Insert inserts a single record and then returns the new primary key, whose type is pkT inputT must be a struct with "db" tags

func InsertSelect added in v0.1.28 

func InsertSelect[inputT any, itemT any](ctx context.Context, db PoolOrTx, schemaName, tableName, viewName, pkColName string, allFields []string,
	input inputT) (newItem itemT, err error)

InsertSelect inserts a single record and then returns it inputT must be a struct with "db" tags

func Restore 

func Restore[pkT PrimaryKeyType](ctx context.Context, tx pgx.Tx, schemaName, tableName, pkColName string, pkVal pkT, isCascaded bool) error

Restore moves a previously archived record to the corresponding table using the supplied tx if this func returns an error, rollback the tx

func SelectArray added in v0.1.12 

func SelectArray[T any](ctx context.Context, db PoolOrTx, selectStmt string, params ...any) (ar []T, err error)

SelectArray is a wrapper for selecting into a non-struct type T (db.Query / pgx.CollectRows with RowTo) T must be a primitive type such as int64 or string

func SelectByArray 

func SelectByArray[inputT, itemT any](ctx context.Context, db PoolOrTx, schema, view, column string, ar []inputT) (items []itemT, err error)

SelectByArray returns multiple rows from the db depending on the array supplied inputT must be a primitive type such as int64 or string

func SelectEnum 

func SelectEnum(ctx context.Context, db PoolOrTx, enumName string, includeVals, excludeVals []string, sortVal string) (vals []string, err error)

SelectEnum returns all values of the supplied enum type includeVals = enum values to include excludeVals = enum values to exclude sortVal = either "" (no sort), "val" or "-val"

func SelectT added in v0.1.10 

func SelectT[T any](ctx context.Context, db PoolOrTx, selectStmt string, params ...any) (items []T, err error)

SelectT is a wrapper for selecting into a struct type T (db.Query / pgx.CollectRows with RowToStructByNameLax)

func SelectUnique 

func SelectUnique[T any](ctx context.Context, db PoolOrTx, schema, view, column string, fields, allFields []string, uniqueVal any) (item T, err error)

SelectUnique returns a single row using the value of a unique column such as id

func Update 

func Update[T any, pkT PrimaryKeyType](ctx context.Context, db PoolOrTx, schemaName, tableName, pkColName string, input T, pkVal pkT, options ...UpdateOption) error

Update changes a single record with the values contained in input T must be a struct with "db" tags

func UpdatePartial 

func UpdatePartial[pkT PrimaryKeyType](ctx context.Context, db PoolOrTx, schemaName, tableName, pkColName string, allowedFields []string, assignmentsMap map[string]any, pkVal pkT) error

UpdatePartial updates only the supplied columns of the record assignmentsMap is a map of k = column name, v = new value

Types

type Column 

type Column struct {
	SchemaName  string
	TableName   string
	Name        string `db:"column_name"`
	DataType    string `db:"data_type"`
	IsNullable  bool   `db:"is_nullable"`
	IsIdentity  bool   `db:"is_identity"`
	IsGenerated bool   `db:"is_generated"`
	IsTracking  bool
}

func GetTableColumns 

func GetTableColumns(ctx context.Context, db PoolOrTx, schemaName, tableName string) (cols []Column, err error)

type Condition 

type Condition struct {
	Field    string
	Operator Operator // must be one of the Operator consts. if "IN" or "NOT IN", fill InValues, not Value
	Value    string
	InValues []string
}

Condition is a condition passed to a SELECT stmt

type DataUpdateCols 

type DataUpdateCols struct {
	DataUpdateId      int64            `db:"data_update_id" json:"data_update_id,omitempty"`
	AffectedId        int64            `db:"affected_id" json:"affected_id,omitempty"`
	AffectedAt        lystype.Datetime `db:"affected_at" json:"affected_at,omitempty"`
	AffectedBy        string           `db:"affected_by" json:"affected_by,omitempty"`
	AffectedOldValues string           `db:"affected_old_values" json:"affected_old_values,omitempty"`
	AffectedNewValues string           `db:"affected_new_values" json:"affected_new_values,omitempty"`
}

DataUpdateCols are the fields expected in a data update table

type ForeignKey 

type ForeignKey struct {
	ConstraintName string `db:"constraint_name"`
	ChildSchema    string `db:"child_schema"`
	ChildTable     string `db:"child_table"`
	ChildColumn    string `db:"child_column"`
	ParentSchema   string `db:"parent_schema"`
	ParentTable    string `db:"parent_table"`
	ParentColumn   string `db:"parent_column"`
}

func GetChildForeignKeys 

func GetChildForeignKeys(ctx context.Context, db PoolOrTx, schemaName, tableName string) (fks []ForeignKey, err error)

GetChildForeignKeys returns the child FKs of the supplied table requires table owner: GRANT SELECT is not enough

func GetForeignKeys 

func GetForeignKeys(ctx context.Context, db PoolOrTx, schemaName, tableName string) (fks []ForeignKey, err error)

GetForeignKeys returns the parent FKs of the supplied table caution: will return incomplete or empty result set if user is not table owner

type Operator 

type Operator string
const (
	OpEquals            Operator = "="
	OpNotEquals         Operator = "!="
	OpLessThan          Operator = "<"
	OpLessThanEquals    Operator = "<="
	OpGreaterThan       Operator = ">"
	OpGreaterThanEquals Operator = ">="
	OpIn                Operator = "IN"
	OpNotIn             Operator = "NOT IN"
	OpStartsWith        Operator = "StartsWith"
	OpEndsWith          Operator = "EndsWith"
	OpContains          Operator = "Contains"
	OpNotContains       Operator = "NotContains"
	OpContainsAny       Operator = "ContainsAny"
	OpEmpty             Operator = "Empty"
	OpNotEmpty          Operator = "NotEmpty"
	OpNull              Operator = "Null"
	OpNotNull           Operator = "NotNull"
)

Valid condition operators

type PoolOrTx 

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

PoolOrTx is an abstraction of a pgx connection pool or transaction, e.g. pgxpool.Pool, pgx.Conn or pgx.Tx adapted from Querier in https://github.com/georgysavva/scany/blob/master/pgxscan/pgxscan.go

type PrimaryKeyType added in v0.1.28 

type PrimaryKeyType interface {
	constraints.Integer | uuid.UUID | ~string
}

PrimaryKeyType defines the type constraint of DB primary keys

type SelectParams 

type SelectParams struct {
	Fields          []string
	Conditions      []Condition
	Sorts           []string
	Limit           int
	Offset          int
	GetUnpagedCount bool // if true, will estimate the total number of records returned by this query regardless of paging
}

SelectParams holds the fields needed to modify a SELECT query

type TotalCount 

type TotalCount struct {
	Value       int64 `db:"rowcount"`
	IsEstimated bool  `db:"estimated"`
}

TotalCount contains the total number of table records. If IsEstimated is true, the Value was estimated using db statistics rather than calculated using a record count

func Select 

func Select[T any](ctx context.Context, db PoolOrTx, schemaName, tableName, viewName, defaultOrderBy string, allFields []string,
	params SelectParams) (items []T, unpagedCount TotalCount, err error)

Select returns multiple rows from the db according to the params supplied

type UpdateOption 

type UpdateOption struct {
	OmitFields []string // db columns to exclude from update
}

Source Files

View all Source files

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.