lyspg

package
v0.1.10 Latest Latest
Warning

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

 Go to latest Published: Jun 21, 2024 License: MIT Imports: 13 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 (
	DataUpdateViewSuffix = "_data_update"
	DataUpdateColTags    = []string{"data_update_id", "affected_id", "affected_at", "affected_by", "affected_old_values", "affected_new_values"}
)
View Source 
var (
	DeletedTableSuffix = "_deleted"
	DeletedTableCols   = []string{"deleted_at", "deleted_by_cascade"}
)

Functions

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 CheckEnumValue 

func CheckEnumValue(ctx context.Context, db PoolOrTx, dbEnum, testVal, enumName string) (err 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) (stmt string, err 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) (stmt string, err 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, stmt string, err error)

Exists returns true if 1+ records exist given the supplied criterion (columnName + val)

func ExistsConditions 

func ExistsConditions(ctx context.Context, db PoolOrTx, schemaName, tableName, match string, colValMap map[string]any) (ret bool, stmt string, 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

func GetRowCount 

func GetRowCount(ctx context.Context, db PoolOrTx, schemaName, tableName string) (rowCount int64, stmt string, 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, stmt string, 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, stmt string, 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, stmt string, err error)

func GetTableColumnNames 

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

func GetTableComment 

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

func GetTableShortName 

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

func Insert 

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

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

func Restore 

func Restore(ctx context.Context, tx pgx.Tx, schemaName, tableName, pkColName string, pkVal any, isCascaded bool) (stmt string, err error)

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

func SelectByArray 

func SelectByArray[inputT, itemT any](ctx context.Context, db PoolOrTx, schema, view, column string, ar []inputT) (items []itemT, stmt string, 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, stmt 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, stmt string, err error)

SelectT is a basic wrapper for db.Query / pgx.CollectRows

func SelectUnique 

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

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

func SoftDelete 

func SoftDelete(ctx context.Context, tx pgx.Tx, schemaName, tableName, pKColName string, pkVal any, isCascaded bool) (stmt string, err error)

SoftDelete moves record(s) to the corresponding deleted table using the supplied tx the deleted table must have the same columns as the source table and also the DeletedTableCols defined above the deleted table name must be the source table name plus DeletedTableSuffix

func Update 

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

Update changes a single record with the values contained in input T must be a struct with "db" tags pkVal is type "any" so that both int and string PKs can be used

func UpdatePartial 

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

UpdatePartial updates only the supplied columns of the record assignmentsMap is a map of k = column name, v = new value pkVal is type "any" so that both int and string PKs can be used

Types

type Column 

type Column struct {
	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, stmt string, 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, stmt string, err error)

GetChildForeignKeys returns the child FKs of the supplied table

func GetForeignKeys 

func GetForeignKeys(ctx context.Context, db PoolOrTx, schemaName, tableName string) (fks []ForeignKey, stmt string, 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
}

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 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, stmt string, 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.