README

Query Parser for REST

Query Parser is a library for easy building dynamic SQL queries to Database. It provides a simple API for web-applications which needs to do some filtering throught GET queries. It is a connector between the HTTP handler and the DB engine, and manages validations and translations for user inputs.

GoDoc Coverage Status

Installation

go get -u github.com/timsolov/rest-query-parser

Fast start

See cmd/main.go and tests for more examples.

    package main
    
    import (
        "errors"
        "fmt"
        "net/url"
    
        rqp "github.com/timsolov/rest-query-parser"
    )
    
    func main() {
        url, _ := url.Parse("http://localhost/?sort=+name,-id&limit=10&id=1&i[eq]=5&s[eq]=one&email[like]=*tim*|name[like]=*tim*")
        q, _ := rqp.NewParse(url.Query(), rqp.Validations{
            "limit:required": rqp.MinMax(10, 100),  // limit must present in the Query part and must be between 10 and 100 (default: Min(1))
            "sort":           rqp.In("id", "name"), // sort could be or not in the query but if it is present it must be equal to "in" or "name"
            "s":      rqp.In("one", "two"), // filter: s - string and equal
            "id:int": nil,                  // filter: id is integer without additional validation
            "i:int": func(value interface{}) error { // filter: custom func for validating
                if value.(int) > 1 && value.(int) < 10 {
                    return nil
                }
                return errors.New("i: must be greater then 1 and lower then 10")
            },
            "email": nil,
            "name":  nil,
        })

        fmt.Println(q.SQL("table")) // SELECT * FROM table WHERE id = ? AND i = ? AND s = ? AND (email LIKE ? OR name LIKE ?) ORDER BY name, id DESC LIMIT 10
        fmt.Println(q.Where())      // id = ? AND i = ? AND s = ? AND (email LIKE ? OR name LIKE ?)
        fmt.Println(q.Args())       // [1 5 one %tim% %tim%]

        q.AddValidation("fields", rqp.In("id", "name"))
        q.SetUrlString("http://localhost/?fields=id,name&limit=10")
        q.Parse()

        fmt.Println(q.SQL("table")) // SELECT id, name FROM table ORDER BY id LIMIT 10
        fmt.Println(q.FieldsSQL())  // id, name
        fmt.Println(q.Args())       // []
    }

Top level fields:

  • fields - fields for SELECT clause separated by comma (",") Eg. &fields=id,name. If nothing provided will use "*" by default. Attention! If you want to use this filter you have to define validation func for it. Use rqp.In("id", "name") func for limit fields for your query.
  • sort - sorting fields list separated by comma (","). Must be validated too. Could include prefix +/- which means ASC/DESC sorting. Eg. &sort=+id,-name will print ORDER BY id, name DESC. You have to filter fields in this parameter by adding rqp.In("id", "name").
  • limit - is limit for LIMIT clause. Should be greater then 0 by default. Definition of the validation for limit is not required. But you may use rqp.Max(100) to limit top threshold.
  • offset - is offset for OFFSET clause. Should be greater then or equal to 0 by default. Definition of the validation for offset is not required.

Validation modificators:

  • :required - parameter is required. Must present in the query string. Raise error if not.
  • :int - parameter must be convertable to int type. Raise error if not.
  • :bool - parameter must be convertable to bool type. Raise error if not.

Supported types

  • string - the default type for all provided filters if not specified another. Could be compared by eq, ne, gt, lt, gte, lte, like, ilike, nlike, nilike, in, nin, is, not methods (nlike, nilike means NOT LIKE, NOT ILIKE respectively, in, nin means IN, NOT IN respectively, is, not for comparison to NULL IS NULL, IS NOT NULL).
  • int - integer type. Must be specified with tag ":int". Could be compared by eq, ne, gt, lt, gte, lte, in, nin methods.
  • bool - boolean type. Must be specified with tag ":bool". Could be compared by eq method.

Date usage

This is simple example to show logic which you can extend.

    import (
        "fmt"
        "net/url"
        validation "github.com/go-ozzo/ozzo-validation/v4"
    )

    func main() {
        url, _ := url.Parse("http://localhost/?create_at[eq]=2020-10-02")
        q, _ := rqp.NewParse(url.Query(), rqp.Validations{
            "created_at": func(v interface{}) error {
                s, ok := v.(string)
                if !ok {
                    return rqp.ErrBadFormat
                }
                return validation.Validate(s, validation.Date("2006-01-02"))
            },
        })

        q.ReplaceNames(rqp.Replacer{"created_at": "DATE(created_at)"})

        fmt.Println(q.SQL("table")) // SELECT * FROM table WHERE DATE(created_at) = ?
    }

Documentation

Index

Constants

View Source
const NULL = "NULL"

    NULL constant

    Variables

    View Source
    var (
    	ErrRequired           = NewError("required")
    	ErrBadFormat          = NewError("bad format")
    	ErrEmptyValue         = NewError("empty value")
    	ErrUnknownMethod      = NewError("unknown method")
    	ErrNotInScope         = NewError("not in scope")
    	ErrSimilarNames       = NewError("similar names of keys are not allowed")
    	ErrMethodNotAllowed   = NewError("method are not allowed")
    	ErrFilterNotAllowed   = NewError("filter are not allowed")
    	ErrFilterNotFound     = NewError("filter not found")
    	ErrValidationNotFound = NewError("validation not found")
    )

      Errors list:

      Functions

      This section is empty.

      Types

      type Error

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

        Error special rqp.Error type

        func NewError

        func NewError(msg string) *Error

          NewError constructor for internal errors

          func (*Error) Error

          func (e *Error) Error() string

          type Filter

          type Filter struct {
          	Key    string // key from URL (eg. "id[eq]")
          	Name   string // name of filter, takes from Key (eg. "id")
          	Method Method // compare method, takes from Key (eg. EQ)
          	Value  interface{}
          	OR     StateOR
          }

            Filter represents a filter defined in the query part of URL

            func (*Filter) Args

            func (f *Filter) Args() ([]interface{}, error)

              Args returns arguments slice depending on filter condition

              func (*Filter) Where

              func (f *Filter) Where() (string, error)

                Where returns condition expression

                type Method

                type Method string

                  Method is a compare method type

                  var (
                  	EQ     Method = "EQ"
                  	NE     Method = "NE"
                  	GT     Method = "GT"
                  	LT     Method = "LT"
                  	GTE    Method = "GTE"
                  	LTE    Method = "LTE"
                  	LIKE   Method = "LIKE"
                  	ILIKE  Method = "ILIKE"
                  	NLIKE  Method = "NLIKE"
                  	NILIKE Method = "NILIKE"
                  	IS     Method = "IS"
                  	NOT    Method = "NOT"
                  	IN     Method = "IN"
                  	NIN    Method = "NIN"
                  )

                    Compare methods:

                    type Query

                    type Query struct {
                    	Fields  []string
                    	Offset  int
                    	Limit   int
                    	Sorts   []Sort
                    	Filters []*Filter
                    
                    	Error error
                    	// contains filtered or unexported fields
                    }

                      Query the main struct of package

                      func New

                      func New() *Query

                        New creates new instance of Query

                        func NewParse

                        func NewParse(q url.Values, v Validations) (*Query, error)

                          NewParse creates new Query instance and Parse it

                          func NewQV

                          func NewQV(q url.Values, v Validations) *Query

                            NewQV creates new Query instance with parameters

                            func (*Query) AddField

                            func (q *Query) AddField(field string) *Query

                              AddField adds field to SELECT statement

                              func (*Query) AddFilter

                              func (q *Query) AddFilter(name string, m Method, value interface{}) *Query

                                AddFilter adds a filter to Query

                                func (*Query) AddFilterRaw

                                func (q *Query) AddFilterRaw(condition string) *Query

                                  AddFilterRaw adds a filter to Query as SQL condition. This function supports only single condition per one call. If you'd like add more then one conditions you should call this func several times.

                                  func (*Query) AddSortBy

                                  func (q *Query) AddSortBy(by string, desc bool) *Query

                                    AddSortBy adds an ordering rule to Query

                                    func (*Query) AddValidation

                                    func (q *Query) AddValidation(NameAndTags string, v ValidationFunc) *Query

                                      AddValidation adds a validation to Query

                                      func (*Query) Args

                                      func (q *Query) Args() []interface{}

                                        Args returns slice of arguments for WHERE statement

                                        func (*Query) FieldsString

                                        func (q *Query) FieldsString() string

                                          FieldsString returns elements list separated by comma (",") for querying in SELECT statement or a star ("*") if nothing provided

                                          Return example:

                                          When "fields" empty or not provided: `*`.

                                          When "fields=id,email": `id, email`.

                                          func (*Query) GetFilter

                                          func (q *Query) GetFilter(name string) (*Filter, error)

                                            GetFilter returns filter by name

                                            func (*Query) HaveField

                                            func (q *Query) HaveField(field string) bool

                                              HaveField returns true if request asks for specified field

                                              func (*Query) HaveFilter

                                              func (q *Query) HaveFilter(name string) bool

                                                HaveFilter returns true if request contains some filter

                                                func (*Query) HaveSortBy

                                                func (q *Query) HaveSortBy(by string) bool

                                                  HaveSortBy returns true if request contains sorting by specified in by field name

                                                  func (*Query) IgnoreUnknownFilters

                                                  func (q *Query) IgnoreUnknownFilters(i bool) *Query

                                                    IgnoreUnknownFilters set behavior for Parser to raise ErrFilterNotAllowed to undefined filters or not

                                                    func (*Query) LIMIT

                                                    func (q *Query) LIMIT() string

                                                      LIMIT returns word LIMIT with number

                                                      Return example: ` LIMIT 100`

                                                      func (*Query) OFFSET

                                                      func (q *Query) OFFSET() string

                                                        OFFSET returns word OFFSET with number

                                                        Return example: ` OFFSET 0`

                                                        func (*Query) ORDER

                                                        func (q *Query) ORDER() string

                                                          ORDER returns words ORDER BY with list of elements for sorting you can use +/- prefix to specify direction of sorting (+ is default, apsent is +)

                                                          Return example: ` ORDER BY id DESC, email`

                                                          func (*Query) Order

                                                          func (q *Query) Order() string

                                                            Order returns list of elements for ORDER BY statement you can use +/- prefix to specify direction of sorting (+ is default) return example: `id DESC, email`

                                                            func (*Query) Parse

                                                            func (q *Query) Parse() (err error)

                                                              Parse parses the query of URL as query you can use standart http.Request query by r.URL.Query()

                                                              func (*Query) RemoveFilter

                                                              func (q *Query) RemoveFilter(name string) error

                                                                RemoveFilter removes the filter by name

                                                                func (*Query) RemoveValidation

                                                                func (q *Query) RemoveValidation(NameAndOrTags string) error

                                                                  RemoveValidation remove a validation from Query You can provide full name of filter with tags or only name of filter: RemoveValidation("id:int") and RemoveValidation("id") are equal

                                                                  func (*Query) ReplaceNames

                                                                  func (q *Query) ReplaceNames(r Replacer)

                                                                    ReplaceNames replace all specified name to new names Sometimes we've to hijack properties, for example when we do JOINs, so you can ask for filter/field "user_id" but replace it with "users.user_id". Parameter is a map[string]string which means map[currentName]newName. The library provide beautiful way by using special type rqp.Replacer. Example:

                                                                      rqp.ReplaceNames(rqp.Replacer{
                                                                    	   "user_id": "users.user_id",
                                                                      })
                                                                    

                                                                    func (*Query) SELECT

                                                                    func (q *Query) SELECT() string

                                                                      SELECT returns word SELECT with fields from Filter "fields" separated by comma (",") from URL-Query or word SELECT with star ("*") if nothing provided

                                                                      Return examples:

                                                                      When "fields" empty or not provided: `SELECT *`.

                                                                      When "fields=id,email": `SELECT id, email`.

                                                                      func (*Query) SQL

                                                                      func (q *Query) SQL(table string) string

                                                                        SQL returns whole SQL statement

                                                                        func (*Query) Select

                                                                        func (q *Query) Select() string

                                                                          Select returns elements list separated by comma (",") for querying in SELECT statement or a star ("*") if nothing provided

                                                                          Return examples:

                                                                          When "fields" empty or not provided: `*`

                                                                          When "fields=id,email": `id, email`

                                                                          func (*Query) SetDelimiterIN

                                                                          func (q *Query) SetDelimiterIN(d string) *Query

                                                                            SetDelimiterIN sets delimiter for values of filters

                                                                            func (*Query) SetDelimiterOR

                                                                            func (q *Query) SetDelimiterOR(d string) *Query

                                                                              SetDelimiterOR sets delimiter for OR filters in query part of URL

                                                                              func (*Query) SetUrlQuery

                                                                              func (q *Query) SetUrlQuery(query url.Values) *Query

                                                                                SetUrlQuery change url in the Query for parsing uses when you need provide Query from http.HandlerFunc(w http.ResponseWriter, r *http.Request) you can do q.SetUrlValues(r.URL.Query())

                                                                                func (*Query) SetUrlString

                                                                                func (q *Query) SetUrlString(Url string) error

                                                                                  SetUrlString change url in the Query for parsing uses when you would like to provide raw URL string to parsing

                                                                                  func (*Query) SetValidations

                                                                                  func (q *Query) SetValidations(v Validations) *Query

                                                                                    SetValidations change validations rules for the instance

                                                                                    func (*Query) WHERE

                                                                                    func (q *Query) WHERE() string

                                                                                      WHERE returns list of filters for WHERE SQL statement with `WHERE` word

                                                                                      Return example: ` WHERE id > 0 AND email LIKE 'some@email.com'`

                                                                                      func (*Query) Where

                                                                                      func (q *Query) Where() string

                                                                                        Where returns list of filters for WHERE statement return example: `id > 0 AND email LIKE 'some@email.com'`

                                                                                        type Replacer

                                                                                        type Replacer map[string]string

                                                                                          Replacer struct for ReplaceNames method

                                                                                          type Sort

                                                                                          type Sort struct {
                                                                                          	By   string
                                                                                          	Desc bool
                                                                                          }

                                                                                            Sort is ordering struct

                                                                                            type StateOR

                                                                                            type StateOR byte
                                                                                            const (
                                                                                            	NoOR StateOR = iota
                                                                                            	StartOR
                                                                                            	InOR
                                                                                            	EndOR
                                                                                            )

                                                                                            type ValidationFunc

                                                                                            type ValidationFunc func(value interface{}) error

                                                                                              ValidationFunc represents validator for Filters

                                                                                              func In

                                                                                              func In(values ...interface{}) ValidationFunc

                                                                                                In validation if values contatin value

                                                                                                func Max

                                                                                                func Max(max int) ValidationFunc

                                                                                                  Max validation if value lower or equal then max

                                                                                                  func Min

                                                                                                  func Min(min int) ValidationFunc

                                                                                                    Min validation if value greater or equal then min

                                                                                                    func MinMax

                                                                                                    func MinMax(min, max int) ValidationFunc

                                                                                                      MinMax validation if value between or equal min and max

                                                                                                      func Multi

                                                                                                      func Multi(values ...ValidationFunc) ValidationFunc

                                                                                                        Multi multiple validation func usage: Multi(Min(10), Max(100))

                                                                                                        func NotEmpty

                                                                                                        func NotEmpty() ValidationFunc

                                                                                                          NotEmpty validation if string value length more then 0

                                                                                                          type Validations

                                                                                                          type Validations map[string]ValidationFunc

                                                                                                            Validations type replacement for map. Used in NewParse(), NewQV(), SetValidations()

                                                                                                            Directories

                                                                                                            Path Synopsis