README
¶
pg
PostgreSQL Helper
pg leverages the following packages to interact with a PostgreSQL database:
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
- type 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 (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
Constants ¶
This section is empty.
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 Exec ¶
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 ¶
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 ¶
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 ¶
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
Source Files