pg

README

pg

PostgreSQL Helper

pg leverages the following packages to interact with a PostgreSQL database:

• Masterminds/squirrel
• georgysavva/scany/v2
• jackc/pgx/v5

Documentation

Overview

Pagination We're using two popular kinds of pagination methods: 1. Offset/Limit pagination --> OffsetPagination (i. Paginator) 2. Seek/Keyset/Cursor pagination --> SeekPagination (i. SeekPaginator)

Reference: - https://blog.jooq.org/2013/10/26/faster-sql-paging-with-jooq-using-the-seek-method/

Index

• Variables
• func DB() *pgxpool.Pool
• func Exec(ctx context.Context, query sq.Sqlizer) (int64, error)
• func Get[T any](ctx context.Context, v *T, query sq.SelectBuilder) (*T, error)
• func Init(ctx context.Context, connString string) (err error)
• func IsPaginationOption(opt ListOption) bool
• func IsSortingOption(opt ListOption) bool
• func ReturnsNilWhenNotFound[T any](v *T, err error) (*T, error)
• type ListOption
  • func CategorizedListOptions(opts ...ListOption) (filtering, paging, sorting []ListOption)
  • func With[T any](columnName string, value ...T) ListOption
  • func WithOffsetPagination(pagination *OffsetPagination) ListOption
  • func WithSortBy(columnName, direction string) ListOption
  • func Without[T any](columnName string, value ...T) ListOption
• type ListOptionFunc
  • func (f ListOptionFunc) Apply(sb sq.SelectBuilder) sq.SelectBuilder
• type OffsetPagination
  • func List[T any](ctx context.Context, vs T, query sq.SelectBuilder, opts ...ListOption) (*OffsetPagination, error)
  • func NewOffsetPagination(defaultPerPage int64) *OffsetPagination
  • func (p *OffsetPagination) CurrentPage() int64
  • func (p *OffsetPagination) Limit() int64
  • func (p *OffsetPagination) LinkHeader(theURL *url.URL) string
  • func (p *OffsetPagination) Offset() int64
  • func (p *OffsetPagination) PageSize() int64
  • func (p *OffsetPagination) SetCountRecords(total int64)
  • func (p *OffsetPagination) SetDefaultPerPage(defaultPerPage int64) int64
  • func (p *OffsetPagination) SetResponseHeaders(rw http.ResponseWriter, r *http.Request)
  • func (p *OffsetPagination) String() string
  • func (p *OffsetPagination) XPaginationHeader() string
• type SeekPagination
  • func NewSeekPagination(defaultLimit int64) *SeekPagination
  • func (p *SeekPagination) Cursor() string
  • func (p *SeekPagination) Limit() int64
  • func (p *SeekPagination) LinkHeader(theURL *url.URL) string
  • func (p *SeekPagination) SetCursor(newCursor string) string
  • func (p *SeekPagination) SetLimit(newLimit int64) int64
  • func (p *SeekPagination) SetResponseHeaders(rw http.ResponseWriter, r *http.Request)
  • func (p *SeekPagination) XPaginationHeader() string

Variables

var (
	// SQL is a statement builder with PostgreSQL dialect enabled.
	// Usage:
	//    query := SQL.Select("*").From("users")....
	//    query := SQL.Update("users").Set("name", "John")....
	SQL = sq.StatementBuilder.PlaceholderFormat(sq.Dollar)
)

Functions

func DB

func DB() *pgxpool.Pool

DB returns the database connection pool.

func Exec

func Exec(ctx context.Context, query sq.Sqlizer) (int64, error)

Exec simplifies running a INSERT/UPDATE/DELETE query. Returns the number of rows affected on success and execution error on failure.

Example:

query := pg.SQL.Delete("users").Where(sq.Eq{"id": 1})
rowsAffected, err := pg.Exec(ctx, query)

func Get

func Get[T any](ctx context.Context, v *T, query sq.SelectBuilder) (*T, error)

Get simplifies running a SELECT query which aims to find only one row of record. Returns nil if no matches found.

Usage: query a user by email, query a document by id, etc.

Example:

var user = new(User)
var err error
query := pg.SQL.Select("*").From("users").Where(sq.Eq{"email": "john@example"})
user, err = pg.Get(ctx, user, query)

func Init

func Init(ctx context.Context, connString string) (err error)

Init initializes the database connection pool, using the given connection string. See `pgxpool.New` for more details about the format of the connection string.

func IsPaginationOption

func IsPaginationOption(opt ListOption) bool

IsPaginationOption returns true if the given ListOption is used for pagination.

func IsSortingOption

func IsSortingOption(opt ListOption) bool

IsSortingOption returns true if the given ListOption is used for limiting the result.

func ReturnsNilWhenNotFound

func ReturnsNilWhenNotFound[T any](v *T, err error) (*T, error)

ReturnsNilWhenNotFound swallows the `pgxscan.NotFound` error and returns nil.

Types

type ListOption

type ListOption interface {
	Apply(sq.SelectBuilder) sq.SelectBuilder
}

ListOption is a function that applies a condition to a query.

func CategorizedListOptions

func CategorizedListOptions(opts ...ListOption) (filtering, paging, sorting []ListOption)

CategorizedListOptions categorizes the given ListOptions into types of filtering, paging, and sorting.

func With

func With[T any](columnName string, value ...T) ListOption

With returns a ListOption that applies the given condition to the query.

When the given value is empty, the returned ListOption is a no-op.

When the given value is a single value, it will be equivalent to a simple equality condition.

When the value is a slice, it will be expanded to a list of OR conditions. Which is equivalent to an IN statement.

func WithOffsetPagination

func WithOffsetPagination(pagination *OffsetPagination) ListOption

WithOffsetPagination returns a ListOption that limits the result to the given page.

func WithSortBy

func WithSortBy(columnName, direction string) ListOption

WithSortBy returns a ListOption that sorts the result by the given column name and sort direction. The direction must be either "asc" or "desc".

func Without

func Without[T any](columnName string, value ...T) ListOption

Without works like With, but it negates the condition. See With for more details.

type ListOptionFunc

type ListOptionFunc func(sq.SelectBuilder) sq.SelectBuilder

ListOptionFunc is an adapter to allow the use of ordinary functions as ListOption.

func (ListOptionFunc) Apply

func (f ListOptionFunc) Apply(sb sq.SelectBuilder) sq.SelectBuilder

type OffsetPagination

type OffsetPagination struct {
	Page         int64 `json:"page" in:"query=page" `
	PerPage      int64 `json:"per_page" in:"query=per_page"`
	CountPages   int64 `json:"count_pages"`
	CountRecords int64 `json:"count_records"`
	// contains filtered or unexported fields
}

OffsetPagination holds paging info in offset pagination method.

func List

func List[T any](ctx context.Context, vs T, query sq.SelectBuilder, opts ...ListOption) (*OffsetPagination, error)

List simplifies running a SELECT query which aims to get a list of resources (rows).

NOTE: `vs` is a slice value, not a pointer to a slice.

Example:

var users []*User
pagination, err := pg.List(ctx, users, pg.SQL.Select("*").From("users"))

func NewOffsetPagination

func NewOffsetPagination(defaultPerPage int64) *OffsetPagination

NewOffsetPagination creates a new `Pagination` with a default page size.

func (*OffsetPagination) CurrentPage

func (p *OffsetPagination) CurrentPage() int64

CurrentPage returns the current page index.

func (*OffsetPagination) Limit

func (p *OffsetPagination) Limit() int64

Limit returns the page size.

func (*OffsetPagination) LinkHeader

func (p *OffsetPagination) LinkHeader(theURL *url.URL) string

LinkHeader compose a Link Header for the HTTP response. See: https://www.w3.org/wiki/LinkHeader e.g. Link: <https://api.example.com/users?page=1>; rel="first", <https://api.example.com/users?page=2>; rel="next"

func (*OffsetPagination) Offset

func (p *OffsetPagination) Offset() int64

Offset returns the size of skipped items of current page.

func (*OffsetPagination) PageSize

func (p *OffsetPagination) PageSize() int64

PageSize returns the page size.

func (*OffsetPagination) SetCountRecords

func (p *OffsetPagination) SetCountRecords(total int64)

SetCountRecords update the `Records` field.

func (*OffsetPagination) SetDefaultPerPage

func (p *OffsetPagination) SetDefaultPerPage(defaultPerPage int64) int64

SetDefaultPerPage sets the default page size.

func (*OffsetPagination) SetResponseHeaders

func (p *OffsetPagination) SetResponseHeaders(rw http.ResponseWriter, r *http.Request)

SetResponseHeaders write paging info headers to the HTTP response.

func (*OffsetPagination) String

func (p *OffsetPagination) String() string

func (*OffsetPagination) XPaginationHeader

func (p *OffsetPagination) XPaginationHeader() string

XPaginationHeader compose a text in format: {Page},{Size},{CountPages},{CountRecords} providing the information of this pagination. e.g. X-Pagination: 1,20,10,200

type SeekPagination

type SeekPagination struct {
	// contains filtered or unexported fields
}

SeekPagination holds paging info in seek pagination method.

func NewSeekPagination

func NewSeekPagination(defaultLimit int64) *SeekPagination

NewSeekPagination creates a new SeekPagination with default limit value.

func (*SeekPagination) Cursor

func (p *SeekPagination) Cursor() string

Cursor returns the cursor string.

func (*SeekPagination) Limit

func (p *SeekPagination) Limit() int64

Limit returns a valid limit (>0) number.

func (*SeekPagination) LinkHeader

func (p *SeekPagination) LinkHeader(theURL *url.URL) string

LinkHeader compose a Link Header for the HTTP response. See: https://www.w3.org/wiki/LinkHeader

func (*SeekPagination) SetCursor

func (p *SeekPagination) SetCursor(newCursor string) string

SetCursor updates cursor to a new value and returns the new value.

func (*SeekPagination) SetLimit

func (p *SeekPagination) SetLimit(newLimit int64) int64

SetLimit updates limit to a new value and returns the new value.

func (*SeekPagination) SetResponseHeaders

func (p *SeekPagination) SetResponseHeaders(rw http.ResponseWriter, r *http.Request)

SetResponseHeaders write paging info headers to the HTTP response.

func (*SeekPagination) XPaginationHeader

func (p *SeekPagination) XPaginationHeader() string

XPaginationHeader compose a text in format: {Cursor},{Limit} providing the information of this pagination. e.g. X-Pagination: dXNlcjoxMCwz,20