pgrst

README

Golang PostgREST library

Golang library to communicate with an existing PostgREST instance. This library crafts the necessary request structure needed.

This library implements the most commonly used features. Oher features will be added as time goes by.

Example

Creating new instance of database

// the address can be direct access to the `PostgREST` or behind a reverse proxy.
address := "http://example.com:3000"
role := "webuser"
secretkey := "supersecretkey"
duration := time.Duration(time.Second*30)

db, err := pgrst.New(address, role, secretkey, duration)
if nil!=err{
    log.Println(err)
    os.Exit(1)
}

the parameters for creating a new instance of db are as follows

Variables	Description
address	Address of the postgrest instance, this could be directly exposed on a local network or behind a reverse proxy on the public internet.
role	Name of the role whihc has permission to act on the database.
secretkey	The key used to create a signed request sent to the postgrest instance to prevent unauthorized accesss.
duration	Maximum validity of the signed request, recommend keeping it between 30 - 45 seconds.

Select

// users - slice of []User structure to store multiple values
// the returned values are mapped to the structure using the json field name
err := db.NewSelect().Table("users").Limit(25).Offset(0).Output(&users).Exec()

// users - slice of []User structure to store multiple values
// the returned values are mapped to the structure using the json field name
// the where condition requires the key name to compare and the value, even for an numeric value, it must be sent as a string
dbSelect := db.NewSelect().Table("users").Where("id",pgrst.Equal,"1").Output(&users)
err := dbSelect.Exec()
if nil != err {
    log.Println(err)
    os.Exit(1)
}

if !dbSelect.IsSuccess() {
    log.Println("no result found with given criteria")
    os.Exit(1)
}

Insert

// user - structure storing user details
// the json field of user must match the table field names
err := db.NewInsert().Table("users").Input(user).Exec()

Update

// similar to insert but requires a where condition to update
// the where condition requires the key name to compare and the value, even for an numeric value, it must be sent as a string
err := db.NewUpdate().Table("users").Where("id", pgrst.Equal,"1").Input(user).Exec()

Call stored procedure to select data

// usersaddrs - slice of []UserAddr structure to store multiple values
// this calls the 
// the returned values are mapped to the structure using the json field name
err := db.NewSelect().RPC("getuserwithaddress").SetQuery("email", "user@example.com").Output(&usersaddrs).Exec()

Call stored procedure to insert data

err := db.NewSelect().RPC("setuseraddress").Input(usersaddrs).Exec()

Constants

The following constants are used for Where conditions

Constants	Description
Equal	Search for values that match exactly the input.
GreaterThan	Search for values greater than the input. Used for numberic values.
GreaterThanEqual	Search for values greater or equal the input. Used for numberic values.
LessThan	Search for values less than the input. Used for numberic values.
LessThanEqual	Search for values less than the or equal the input. Used for numberic values.
NotEqual	Search for values that don't match the input.

The following constants are used for WithCount conditions

Constants	Description
CountTypeExact	Returns the total size of the table based on the query and parameters. This could be slower to run on larger databases.
CountTypePlanned	Returns the total size of the table based on the query and parameters using PostgreSQL statistics. Fairly accurate and fast.
CountTypeEstimated	Similar as above but uses exact count until a threshold and get the planned count after.

Functions

The following functions are available in this library

Function	Description
New	creates a new instance of database with the address, role, secretkey and duration.
NewSelect	Sets the request method to GET to search through the database. Used with Table and RPC.
NewInsert	Sets the request method to POST to insert data to the database. Used with Table and RPC.
NewUpsert	Sets the request method to POST to update if exist otherwise update data in the database. Sets the necessary headers requird by PostgREST.
NewUpdate	Sets the request method to PATCH to update an existing data in the database.
NewDelete	Sets the request method to DELETE to delete items from the database.
SetRole	overrides the role configured in New.
Input	sets the input for inserting, upserting, or updating data. The json fields MUST match the column names in the related tables.
Output	sets the pointer to a structure to store the result. The type can be a slice, struct or string.
Debug	prints the request being sent and the response received to the console.
Exec	Executes the chained requet.
Select	define the fields to be returned from the NewSelect request.
Where	sets the condition for the request. Refer to teh Constants section for futher explanation.
WhereIn	like above but compares against a list of values.
Limit	number of results to be returned.
Offset	skips that many rows, used with Limit.
WithCount	returns number of items in a given Table. Refer to the Constants section for further explanation.
SetQuery	sets custom URL query parameters, allowing support of other PostgREST features.
SetHeader	sets custom HTTP headers, allowing support of other PostgREST features.
IsSuccess	determines if the request is successful. Requests with HTTP code 2xx are considered successful.
GetCount	Gets the total count of items in the given Table. Requires WithCount.
GetRange	Gets the range of the items for this request.

Documentation

Index

• func NewReader(i any) (r io.Reader, err error)
• type CountType
  • func (ct CountType) String() (str string)
• type DB
  • func New(address, role, secret string, duration time.Duration) (db *DB, err error)
  • func (db *DB) Debug() *DB
  • func (db *DB) Exec() (err error)
  • func (db *DB) GetCount() (count int)
  • func (db *DB) GetDBResponse() (dbResponse *DBResponse)
  • func (db *DB) GetRange() (start, end int)
  • func (db *DB) Input(input any) *DB
  • func (db *DB) IsSuccess() (success bool)
  • func (db *DB) Limit(limit int) *DB
  • func (db *DB) NewDelete() *DB
  • func (db *DB) NewInsert() *DB
  • func (db *DB) NewSelect() *DB
  • func (db *DB) NewUpdate() *DB
  • func (db *DB) NewUpsert() *DB
  • func (db *DB) Offset(offset int) *DB
  • func (db *DB) Output(output any) *DB
  • func (db *DB) RPC(funcName string) *DB
  • func (db *DB) Select(col ...string) *DB
  • func (db *DB) SetHeader(key, value string) *DB
  • func (db *DB) SetQuery(key, value string) *DB
  • func (db *DB) SetRole(role string) *DB
  • func (db *DB) Table(tableName string) *DB
  • func (db *DB) Where(field string, ft FilterType, value string) *DB
  • func (db *DB) WhereIn(field string, values []string) *DB
  • func (db *DB) WithCount(ct CountType) *DB
• type DBResponse
  • func (dbResponse *DBResponse) HasCode() (b bool)
  • func (dbResponse *DBResponse) String() (str string)
• type FilterType
  • func (ft FilterType) String() (str string)

Functions

func NewReader

func NewReader(i any) (r io.Reader, err error)

NewReader - create new instance of reader from a given structure

Types

type CountType

type CountType uint8

func (CountType) String

func (ct CountType) String() (str string)

type DB

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

DB - remote DB information

func New

func New(address, role, secret string, duration time.Duration) (db *DB, err error)

New - creates new instance of Database

func (*DB) Debug

func (db *DB) Debug() *DB

Debug - prints the HTTP Status code and any messages received to console

func (*DB) Exec

func (db *DB) Exec() (err error)

Exec - executes this query

func (*DB) GetCount

func (db *DB) GetCount() (count int)

GetCount - returns the number of items found, for SELECT requests

func (*DB) GetDBResponse

func (db *DB) GetDBResponse() (dbResponse *DBResponse)

GetDBResponse - returns the database response

func (*DB) GetRange

func (db *DB) GetRange() (start, end int)

GetRange - returns the range of items found, for SELECT requests

func (*DB) Input

func (db *DB) Input(input any) *DB

Input - structure to send to PostgREST instance

func (*DB) IsSuccess

func (db *DB) IsSuccess() (success bool)

IsSuccess - indicates if the `Exec` request was a success

func (*DB) Limit

func (db *DB) Limit(limit int) *DB

Limit - limits the returned results

func (*DB) NewDelete

func (db *DB) NewDelete() *DB

NewDelete - returns a new instance for deleting data

func (*DB) NewInsert

func (db *DB) NewInsert() *DB

NewInsert - returns a new instance for inserting data

func (*DB) NewSelect

func (db *DB) NewSelect() *DB

NewSelect - returns a new instance for selecting data

func (*DB) NewUpdate

func (db *DB) NewUpdate() *DB

NewUpdate - returns a new instance for updating data

func (*DB) NewUpsert

func (db *DB) NewUpsert() *DB

NewUpsert - returns a new instance for inserting or updating data (if exists)

func (*DB) Offset

func (db *DB) Offset(offset int) *DB

Offset - returns result from the given offset

func (*DB) Output

func (db *DB) Output(output any) *DB

Output - structure to save a single result from PostgREST instance, remember to pass a pointer to the 'output'

func (*DB) RPC

func (db *DB) RPC(funcName string) *DB

RPC - executes a remote function on the database

func (*DB) Select

func (db *DB) Select(col ...string) *DB

Select - specify fields to be returned from the table

func (*DB) SetHeader

func (db *DB) SetHeader(key, value string) *DB

SetHeader - sets custom header for PostgREST to support features not immediately implemented by this library

func (*DB) SetQuery

func (db *DB) SetQuery(key, value string) *DB

SetQuery - sets custom query parameters for PostgREST to support features not immediately implemented by this library

func (*DB) SetRole

func (db *DB) SetRole(role string) *DB

SetRole - sets the name of the role to use for this request

func (*DB) Table

func (db *DB) Table(tableName string) *DB

Table - sets the name of the table to operate on

func (*DB) Where

func (db *DB) Where(field string, ft FilterType, value string) *DB

Where - filter result rows by adding conditions on columns

func (*DB) WhereIn

func (db *DB) WhereIn(field string, values []string) *DB

WhereIn - filter result rows by adding conditions on columns, one of a list of values

func (*DB) WithCount

func (db *DB) WithCount(ct CountType) *DB

WithCount - sets the correct header to get actual count of items from the database

type DBResponse

type DBResponse struct {
	Code           string `json:"code,omitempty"`
	Details        string `json:"details,omitempty"`
	Hint           string `json:"hint,omitempty"`
	Message        string `json:"message,omitempty"`
	HTTPStatusCode int    `json:"httpStatusCode,omitempty"`
}

DBResponse - database response

func (*DBResponse) HasCode

func (dbResponse *DBResponse) HasCode() (b bool)

func (*DBResponse) String

func (dbResponse *DBResponse) String() (str string)

type FilterType

type FilterType uint8

const (
	Equal FilterType = iota + 1
	GreaterThan
	GreaterThanEqual
	LessThan
	LessThanEqual
	NotEqual
)

const (
	CountTypeExact FilterType = iota + 1
	CountTypePlanned
	CountTypeEstimated
)

func (FilterType) String

func (ft FilterType) String() (str string)