pj

README

pj

thin opiniated layer over postgresql database queries and json

Documentation

Overview

Package pj offers a very simple and thin opiniated layer over postgresql database queries.

Therefor it makes several assumptions.

1. Each query returns a single row and a single column that is the result of a calls of a postgres function that gets one parameter that is a json string and returns a json string. This is most easily achieved by using the plv8 extension to define the functions.

2. All validation occurs inside the postgresql function.

3. The parameter to the function is a json map created from the url query.

4. The returned json will be returned to the client.

5. The returned json may have a property "http_status_code" to indicate errors. If it does the corresponding status code is sent to the client in addition to the json.

6. The returned json may have a property "http_headers" that must be convertible to a map[string]string. If it does, the http headers will be set accordingly.

7. Authentication and authorization will be handled by middleware surrounding the http.Handler returned from pj.New

Benefits

- no mapping server<->database necessary for rows and tables

- single point of truth for structures/tables

- validation near the data

- easier schema migration: start with a function that returns static testdata and change the function to query tables when the schema has settled

- fast development of client and database without having to restart or recompile server

Disadvantages

- you are bound to postgres

- need pg users for access roles

- need different connections for different access roles

- learn postgres

Index

• func QueryRow(q Queryer, qFn string, jsonParam string, target interface{}) error
• func Sql(meth, fname string, fbody []byte) string
• type DB
• type Execer
• type Muxer
• type PJ
  • func New(db Queryer, m map[string]string, errTracker func(error, *http.Request)) *PJ
  • func (p *PJ) ServeHTTP(w http.ResponseWriter, r *http.Request)
• type QueryCollection
  • func LoadQueries(rootDir string, mux Muxer, db DB, maxBodySize int64, ...) (*QueryCollection, error)
  • func NewQueryCollection(rootDir string, errTracker func(error, *http.Request)) (*QueryCollection, error)
  • func (q *QueryCollection) AddQuery(mux Muxer, db DB, relpath string) error
  • func (q *QueryCollection) EachFile(fn func(filepath, funcname, meth string))
  • func (q *QueryCollection) RegisterHTTPHandlers(mux Muxer, db Queryer, maxBodySize int64) (err error)
  • func (q *QueryCollection) RegisterQueryFuncs(db DB) (err error)
  • func (q *QueryCollection) RemoveQuery(mux Muxer, db DB, relpath string) error
  • func (q *QueryCollection) UpdateQuery(mux Muxer, db DB, relpath string) error
• type Queryer
• type SyntaxError
  • func (e *SyntaxError) Error() string

Functions

func QueryRow

func QueryRow(q Queryer, qFn string, jsonParam string, target interface{}) error

QueryRow requeries the given function qFn with the given parameter jsonParam and scans the result into the target

func Sql

func Sql(meth, fname string, fbody []byte) string

Types

type DB

type DB interface {
	QueryRow(sql string, args ...interface{}) *sql.Row
	Exec(query string, args ...interface{}) (sql.Result, error)
}

type Execer

type Execer interface {
	Exec(query string, args ...interface{}) (sql.Result, error)
}

type Muxer

type Muxer interface {
	// Handle registers a http.Handler for a path
	// If called twice for the same path, it must update the handler for the path
	Handle(path string, h http.Handler)

	// RemoveHandler unregisters the http.Handler for a given path
	RemoveHandler(path string)
}

type PJ

type PJ struct {
	Map     map[string]string
	Queryer Queryer

	MaxBodySize int64 // max size of the body, defaults to 2KB
	// contains filtered or unexported fields
}

func New

func New(db Queryer, m map[string]string, errTracker func(error, *http.Request)) *PJ

New creates a new http.Handler that dispatches to the given queries based on the queryMap. Queries are expected to be postgresql functions in PL/v8 that receive a stringified json object as input and return a stringified json object as output. These functions are expected to do validation etc. If such a function needs to return an error, the returned json object must have a property "http_status_code" that has the appropriate http status code that should be returned, as a number. then this status code will be returned alongside the json of the response. It is important to return an error status code, if an error occurs. If a result should be returned, a property "result" for a single row or "results" for multiple rows must be set. If a result has a "http_headers" property, this headers will be set the rest is a contract between the receiving javascript and the returning sql function The map maps request methods ("GET", "POST", "PUT", "PATCH", "DELETE") to postgres function names that are defined like above. If errTracker is not nil, all errors will be passed to it in addition to the normal error handling

func (*PJ) ServeHTTP

func (p *PJ) ServeHTTP(w http.ResponseWriter, r *http.Request)

type QueryCollection

type QueryCollection struct {
	RootDir  string
	Queries  map[string]map[string]string
	Handlers map[string]*PJ

	*sync.Mutex
	// contains filtered or unexported fields
}

func LoadQueries

func LoadQueries(rootDir string, mux Muxer, db DB, maxBodySize int64, errTracker func(error, *http.Request)) (*QueryCollection, error)

LoadQueries loads queries from a filesystem and registers http handlers for them It expects the following directory structure of rootDir:

[mountpath]/[method]/[queryfn].sql

for example: persons/get/all_persons.sql

[mountpath] must be a single path segment (directory) and match the regexp [a-z][a-z_0-9]+. [method] must be the http request method, i.e. one of "get", "put", "patch", "delete", "post" [queryfn] must match the regexp [a-z][a-z_0-9]+ and is the name of the postgresql function the content of [queryfn].sql is the sql that is transferred to the database when the query is registered E.g.: If the filename is all_persons.sql the content must be something like

    CREATE OR REPLACE FUNCTION all_persons(params json) RETURNS text AS $function$
	   var o = {};
    /* do your thing */
    return JSON.stringify(o);
    $function$ LANGUAGE plv8 IMMUTABLE STRICT;

mux is the Muxer that is used to register the http.Handlers serving the queries NewQueryCollection(rootDir string, errTracker func(error, *http.Request)) (*QueryCollection, error)

func NewQueryCollection

func NewQueryCollection(rootDir string, errTracker func(error, *http.Request)) (*QueryCollection, error)

func (*QueryCollection) AddQuery

func (q *QueryCollection) AddQuery(mux Muxer, db DB, relpath string) error

AddQuery adds a query that is a file located in the path relative to the rootdir

func (*QueryCollection) EachFile

func (q *QueryCollection) EachFile(fn func(filepath, funcname, meth string))

func (*QueryCollection) RegisterHTTPHandlers

func (q *QueryCollection) RegisterHTTPHandlers(mux Muxer, db Queryer, maxBodySize int64) (err error)

func (*QueryCollection) RegisterQueryFuncs

func (q *QueryCollection) RegisterQueryFuncs(db DB) (err error)

RegisterQueryFuncs reads the content of all query function files and execs them on the db

func (*QueryCollection) RemoveQuery

func (q *QueryCollection) RemoveQuery(mux Muxer, db DB, relpath string) error

func (*QueryCollection) UpdateQuery

func (q *QueryCollection) UpdateQuery(mux Muxer, db DB, relpath string) error

type Queryer

type Queryer interface {
	QueryRow(sql string, args ...interface{}) *sql.Row
}

type SyntaxError

type SyntaxError struct {
	Offset int64 // error occurred after reading Offset bytes
	// contains filtered or unexported fields
}

A SyntaxError is a description of a JSON syntax error.

func (*SyntaxError) Error

func (e *SyntaxError) Error() string