goql

package module
v0.0.2 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: Jul 1, 2022 License: BSD-3-Clause Imports: 13 Imported by: 0

README

goql Go Reference

Parse query string to Postgres SQL operators. This library does not generate the SQL statements, but it can be paired with other ORMs to generate dynamic SQL.

Requires go 1.18+.

for strongly-type approach, see rough implementation here.

Features

  • customizable field names, field ops, query string fields, as well as struct tags
  • supports many operators used by Postgres
  • handles conjunction (and/or)
  • handles type conversion from query string to designated struct field's type
  • handles limit/offset
  • handles sorting
  • handles parsing slices for array operators like IN, NOT IN, LIKE, ILIKE

Installation

go get github.com/alextanhongpin/goql

Basic example

package main

import (
	"fmt"
	"net/url"

	"github.com/alextanhongpin/goql"
)

type Book struct {
	Author      string
	Title       string
	PublishYear int `q:"publish_year" sort:"true"`
}

func main() {
	// Register a new decoder for the type Book.
	// Information are extracted from the individual struct
	// fields, as well as the struct tag.
	dec := goql.NewDecoder[Book]()

	/*
		Query: Find books that are published by 'Robert
		Greene' which has the keyword 'law' and 'master'
		published after 2010. The results should be ordered
		descending nulls last, limited to 10.


		SELECT *
		FROM books
		WHERE author = 'Robert Greene'
		AND publish_year > 2010
		AND title ilike any(array['law', 'master'])
		ORDER BY publish_year desc nullslast
		LIMIT 10
	*/

	v := make(url.Values)
	v.Set("author.eq", "Robert Greene")
	v.Set("publish_year.gt", "2010")
	v.Add("title.ilike", "law")
	v.Add("title.ilike", "master")
	v.Add("sort_by", "publish_year.desc.nullslast")
	v.Add("limit", "10")

	f, err := dec.Decode(v)
	if err != nil {
		panic(err)
	}

	for _, and := range f.And {
		fmt.Println("and:", and)
	}

	fmt.Println("limit:", *f.Limit)
	for _, sort := range f.Sort {
		fmt.Println("sort:", sort)
	}
}

Output:

and: author eq "Robert Greene"
and: publish_year gt 2010
and: title ilike []interface {}{"law", "master"}
limit: 10
sort: publish_year desc nullslast

Operators

Basic datatypes (int, float, string, bool, time):

op querystring sql
eq name.eq=john appleseed name = 'john appleseed'
neq name.neq=john appleseed name <> 'john appleseed'
lt age.lt=17 age < 17
lte age.lte=17 age <= 17
gt age.gt=17 age > 17
gte age.gte=17 age >= 17
like title.like=programming% title like 'programming%'
ilike title.ilike=programming% title ilike 'programming%'
notlike title.notlike=programming% title not like 'programming%'
notilike title.notilike=programming% title not ilike 'programming%'
in hobbies.in=programming&hobbies.in=music hobbies in ('programming', 'music')
notin hobbies.notin=programming&hobbies.notin=music hobbies not in ('programming', 'music')
is married.is=true married is true
isnot married_at.isnot=null married_at is not null

Some operators such as IN, LIKE, ILIKE and their negation NOT supports multiple values:

op querystring sql
like title.like=programming%&title.like=music% title like any(array['programming%', 'music%'])
ilike title.ilike=programming%&title.ilike=music% title ilike any(array['programming%', 'music%'])
notlike title.notlike=programming%&title.notlike=music% title not like all(array['programming%', 'music%'])
notilike title.notilike=programming%&title.notilike=music% title not ilike all(array['programming%', 'music%'])
in hobbies.in=programming&hobbies.in=music hobbies in ('programming', 'music')
notin hobbies.notin=programming&hobbies.notin=music hobbies not in ('programming', 'music')

If the target type is an array [^1], then multiple values are accepted too:

op querystring sql
eq hobbies.eq=swimming&hobbies.eq=dancing hobbies = array['swimming', 'dancing']
neq hobbies.neq=swimming&hobbies.neq=dancing hobbies <> array['swimming', 'dancing']
lt scores.lt=50&scores.lt=100 scores < array[10, 100]
lte scores.lte=50&scores.lte=100 scores <= array[10, 100]
gt scores.gt=50&scores.gt=100 scores >= array[10, 100]
gte scores.gte=50&scores.gte=100 scores >= array[10, 100]

And/Or

AND/OR can be chained and nested.

op querystring sql
and and=age.gt:13&and=age.lt:30&or=and.(name.ilike:alice%,name.notilike:bob%) AND age > 13 AND age < 30 OR (name ilike 'alice%' AND name not ilike 'bob%'))
or or=and.(height.isnot:null,height.gte:170) OR (height is not null AND height >= 170)
or or=height.isnot:null&or=height.gte:170 OR height is not null OR height >= 170

Limit/Offset

The default naming for the limit/offset in querystring is limit and offset. The query string name can changed by calling:

dec.SetQueryLimitName("_limit")
dec.SetQueryOffsetName("_offset")

The default min/max for the limit is 1 and 20 respectively. To change the limit:

dec.SetLimitRange(5, 100)
op querystring sql
limit limit=20 LIMIT 20
offset offset=42 OFFSET 42
limit/offset limit=20&offset=42 LIMIT 20, OFFSET 42

Sort

To enable sorting for a field, set the struct tag sort:"true". E.g.

type User struct {
	Age int `sort:"true"`
}

The sort struct name can be changed:

dec.SetSortTag("sortable")

And the example above will then be:

type User struct {
	Age int `sortable:"true"`
}
op querystring sql
sort sort=age ORDER BY AGE ASC NULLSLAST
sort=age.asc ORDER BY age ASC NULLSLAST
sort=age.desc ORDER BY age DESC NULLSFIRST
sort=age.asc.nullsfirst ORDER BY age ASC NULLSFIRST
sort=age.desc.nullslast ORDER BY age DESC NULLSLAST
sort=id.desc&sort=age ORDER BY id DESC NULLSFIRST, age ASC NULLSLAST

Tags

type User struct {
	Age int `q:"age"`
}

The default tag name is q. To change it:

dec.SetFilterTag("filter")

And the example above will be:

type User struct {
	Age int `filter:"age"`
}
field tag description
name string q:"name" name modifies the query string name
MarriedAt time.Time q:"," name if no name is specified, it defaults to lower common initialism of the name
ID uuid.UUID q:",type:uuid" type: specifies the type that would be used by the parser
IDs []string q:",type:[]uuid" type:[] specifies an array type. array types have special operators
ID *string q:",type:*uuid" type:* specifies a null type. null types have special operators
ID string q:",null" null another approach of specifying null types
ID string q:",ops:eq,neq" ops specifies the list of supported ops. In this example, only id.eq=v and id.neq=v is valid. This can be further overwritten by dec.SetOps.

Examples of valid tags:

q:",null"
q:"custom_name,null"
q:"custom_name,type:uuid"
q:"custom_name,ops:eq,neq"
q:"custom_name,type:uuid,ops:eq,neq"
q:"custom_name,type:[]uuid"
q:"custom_name,type:[]*uuid"
q:"custom_name,type:[]*uuid,ops:eq,neq,in,notin"

Ops

To customize ops for a specific field, either set the struct tag ops:<comma-separate-list-of-ops>, or set it through the method SetOps:

// To allow only `eq` and `neq` for the field `name`:
dec.SetOps("name", goql.OpEq | goql.OpNeq)

Parsers

Query string parameters are string (or list of string). Parsers are responsible for parsing the string to the desired types that are either

  1. inferred through the struct field's type through reflection
  2. set at the struct tag through type:"yourtype"

Custom parsers can be registered as follow:

	type Book struct {
		ID uuid.UUID `q:"id,type:uuid"` // Register a new type `uuid`.
	}

	id := uuid.New()

	v := make(url.Values)
	v.Set("id.eq", id.String())
	v.Add("id.in", id.String()) // Automatically handles conversion for a list of values.
	v.Add("id.in", id.String())

	dec := goql.NewDecoder[Book]()
	dec.SetParser("uuid", parseUUID)

	f, err := dec.Decode(v)
	if err != nil {
		t.Fatal(err)
	}

where parseUUID fulfills the goql.ParseFn method definition:

func parseUUID(in string) (any, error) {
	return uuid.Parse(in)
}

FAQ

What if I need to filter some fields from url.Values?

Filter it manually before passing to Decode(v url.Values).

What if I need to add validation to the values?

Create a new type (aka `value object), and add a parser for that type which includes validation. Parsers are type-specific.

// You can edit this code!
// Click here and start typing.
package main

import (
	"errors"
	"fmt"
	"net/url"
	"strings"

	"github.com/alextanhongpin/goql"
)

var ErrInvalidEmail = errors.New("bad email format")

type Email string

func (e Email) Validate() error {
	if !strings.Contains(string(e), "@") {
		return ErrInvalidEmail
	}

	return nil
}

type User struct {
	Email Email `q:",type:email"` // Register a new type "email"
}

func main() {
	dec := goql.NewDecoder[User]()
	dec.SetParser("email", parseEmail)

	v := make(url.Values)
	v.Set("email.eq", "bad email") // Register a parser for type "email"
	_, err := dec.Decode(v)
	fmt.Println(err)
	fmt.Println(errors.Is(err, ErrInvalidEmail))
}

func parseEmail(in string) (any, error) {
	email := Email(in)
	return email, email.Validate()
}

Why is there no support for keyset/cursor pagination, only limit/offset?

Because you can construct the cursor pagination directly.

Reference

[^1]: List of array operators

Documentation

Index

Constants

View Source 
const (
	// Tag name for struct parsing, customizable.
	TagFilter = "q"
	TagSort   = "sort"

	// Reserved query string fields, customizable, since 'sort_by' or 'limit'
	// could be a valid field name.
	QuerySort   = "sort_by"
	QueryLimit  = "limit"
	QueryOffset = "offset"
	QueryAnd    = "and"
	QueryOr     = "or"

	// Pagination limit.
	LimitMin = 1
	LimitMax = 20
)
View Source 
const (
	// OpsComparable represents comparable operators.
	// Most (or all) datatypes in SQL are comparable.
	OpsComparable = OpEq | OpNeq | OpLt | OpLte | OpGt | OpGte | OpIn | OpNotIn

	// OpsNull represents nullable operators.
	OpsNull = OpIs | OpIsNot

	// OpsIn represents the variation of `in`.
	OpsIn = OpIn | OpNotIn

	// OpsLike represents the variation of `like`/`not like`.
	OpsLike = OpLike | OpNotLike | OpIlike | OpNotIlike

	// OpsFullTextSearch represents full-text-search operators.
	OpsFullTextSearch = OpFts | OpPlFts | OpPhFts | OpWFts

	// OpsRange operators supports range operations.
	OpsRange = OpCs | OpCd | OpOv | OpSl | OpSr | OpNxr | OpNxl | OpAdj

	// OpsMany operators supports multiple values.
	OpsMany = OpsIn | OpsLike
)

Variables

View Source 
var (
	ErrUnknownOperator    = errors.New("goql: unknown op")
	ErrInvalidOp          = errors.New("goql: invalid op")
	ErrUnknownField       = errors.New("goql: unknown field")
	ErrUnknownParser      = errors.New("goql: unknown parser")
	ErrInvalidConjunction = errors.New("goql: invalid conjunction")
	ErrBadValue           = errors.New("goql: bad value")
	ErrTooManyValues      = errors.New("goql: too many values")
)
View Source 
var (
	ErrInvalidSortDirection = errors.New("goql: invalid sort direction")
	ErrInvalidSortOption    = errors.New("goql: invalid sort option")
)
View Source 
var (
	SortDirectionAscending  SortDirection = "asc"
	SortDirectionDescending SortDirection = "desc"
	SortOptionNullsFirst    SortOption    = "nullsfirst" // Default
	SortOptionNullsLast     SortOption    = "nullslast"  // Default
)
View Source 
var QueryDelimiter = ','

Functions

func FilterValues added in v0.0.2 

func FilterValues(values url.Values, excludes ...string) url.Values

FilterValues filters the keys from the url.Values.

func JoinCsv 

func JoinCsv(val []string) string

func JoinCsvDeprecated 

func JoinCsvDeprecated(ss []string) (string, error)

func LowerCommonInitialism 

func LowerCommonInitialism(field string) string

func Map 

func Map[T, K any](in []T, fn func(T) (K, error)) ([]K, error)

Map applies a function to the list of values.

func NewParsers 

func NewParsers() map[string]ParserFn

NewParsers returns a list of default parsers. This can be extended, and set back to the Decoder.

func ParseBool 

func ParseBool(in string) (any, error)

ParseBool parses string to bool.

func ParseByte 

func ParseByte(in string) (any, error)

ParseByte parses string to byte slice.

func ParseFloat32 

func ParseFloat32(in string) (any, error)

ParseFloat32 parses string to float32.

func ParseFloat64 

func ParseFloat64(in string) (any, error)

ParseFloat64 parses string to float64.

func ParseInt 

func ParseInt(in string) (any, error)

ParseInt parses string to int.

func ParseInt16 

func ParseInt16(in string) (any, error)

ParseInt16 parses string to int16.

func ParseInt32 

func ParseInt32(in string) (any, error)

ParseInt32 parses string to int32.

func ParseInt64 

func ParseInt64(in string) (any, error)

ParseInt64 parses string to int64.

func ParseJSON 

func ParseJSON(in string) (any, error)

ParseJSON parses string to json.

func ParseNop 

func ParseNop(in string) (any, error)

ParseNop is a nop parser.

func ParseNumericPointer 

func ParseNumericPointer[T int | int16 | int32 | int64 | float32 | float64](in string) (any, error)

ParseNumericPointer parses json string to pointer type.

func ParseString 

func ParseString(in string) (any, error)

ParseString returns the string as it is.

func ParseStringPointer 

func ParseStringPointer[T string | bool | time.Time](in string) (any, error)

ParseStringPointer parses json string to pointer type.

func ParseStruct 

func ParseStruct(unk any, filterTag, sortTag string) (map[string]*Tag, error)

func ParseTime 

func ParseTime(in string) (any, error)

ParseTime parses string to time with the format RFC3339.

func SortQuery added in v0.0.2 

func SortQuery(queries []Query)

func Split2 

func Split2(str, by string) (string, string)

func Split3 

func Split3(str, by string) (string, string, string)

func SplitCsv 

func SplitCsv(val string) []string

func SplitCsvDeprecated 

func SplitCsvDeprecated(s string) ([]string, error)

func SplitOutsideBrackets 

func SplitOutsideBrackets(val string) []string

func Unique added in v0.0.2 

func Unique[T comparable](values []T) []T

Unique returns a unique items in the same order.

func Unquote 

func Unquote(str string, l, r rune) (string, bool)

Types

type Decoder 

type Decoder[T any] struct {
	// contains filtered or unexported fields
}

func NewDecoder 

func NewDecoder[T any]() *Decoder[T]

func (*Decoder[T]) Decode 

func (d *Decoder[T]) Decode(u url.Values) (*Filter, error)

func (*Decoder[T]) SetFilterTag 

func (d *Decoder[T]) SetFilterTag(filterTag string) *Decoder[T]

func (*Decoder[T]) SetLimitRange 

func (d *Decoder[T]) SetLimitRange(min, max int) *Decoder[T]

func (*Decoder[T]) SetOps 

func (d *Decoder[T]) SetOps(field string, ops Op) *Decoder[T]

func (*Decoder[T]) SetParser 

func (d *Decoder[T]) SetParser(name string, parserFn ParserFn) *Decoder[T]

func (*Decoder[T]) SetParsers 

func (d *Decoder[T]) SetParsers(parsers map[string]ParserFn) *Decoder[T]

func (*Decoder[T]) SetQueryLimitName 

func (d *Decoder[T]) SetQueryLimitName(name string) *Decoder[T]

func (*Decoder[T]) SetQueryOffsetName 

func (d *Decoder[T]) SetQueryOffsetName(name string) *Decoder[T]

func (*Decoder[T]) SetQuerySortName 

func (d *Decoder[T]) SetQuerySortName(name string) *Decoder[T]

func (*Decoder[T]) SetSortTag 

func (d *Decoder[T]) SetSortTag(sortTag string) *Decoder[T]

func (*Decoder[T]) Validate 

func (d *Decoder[T]) Validate() error

Validate checks if the parser exists for all the inferred types. This is called internally before decode is called.

type FieldSet 

type FieldSet struct {
	Tag    *Tag
	Name   string
	Value  any
	Values []string
	Op     Op

	Or  []FieldSet
	And []FieldSet
}

func (FieldSet) String 

func (f FieldSet) String() string

type Filter 

type Filter struct {
	Sort   []Order
	And    []FieldSet
	Or     []FieldSet
	Limit  *int
	Offset *int
}

type Op 

type Op int

Op represents a SQL operator. 

const (
	OpEq       Op = 1 << iota // =, equals, e.g. name.eq=john appleseed
	OpNeq                     // <> or !=, not equals, e.g. name.neq=john appleseed
	OpLt                      // <, less than
	OpLte                     // <=, less than equals
	OpGt                      // >, greater than
	OpGte                     // >=, greater than equals
	OpLike                    // like, multi-values, e.g. name.like=john*
	OpIlike                   // ilike, multi-values, same as like, but case insensitive, e.g. name.ilike=john%
	OpNotLike                 // not like, multi-values, e.g. name.notlike=john*
	OpNotIlike                // not ilike, multi-values, e.g. name.notilike=john*
	OpIn                      // in, multi-values, name.in=alice&name.in=bob
	OpNotIn                   // not in, multi-values, name.notin=alice&name.notin=bob
	OpIs                      // is, checking for exact equality (null,true,false,unknown), e.g. age.is=null
	OpIsNot                   // is not, e.g. age.isnot=null
	OpFts                     // Full-Text search using to_tsquery
	OpPlFts                   // Full-Text search using plain to tsquery
	OpPhFts                   // Full-Text search using phrase to tsquery
	OpWFts                    // Full-Text search using word.
	OpCs                      // @>, contains, e.g. ?tags.cs=apple&tags.cs=orange
	OpCd                      // <@, contained in e.g. ?values.cd=1&values.cd=2
	OpOv                      // &&, overlap
	OpSl                      // <<, strictly left of
	OpSr                      // >>, strictly right of
	OpNxr                     // &<
	OpNxl                     // &>
	OpAdj                     // -|-
	OpNot                     // not
	OpOr                      // or, e.g. or=(age.gt:10,age.lt:100)
	OpAnd                     // and, e.g. and=(or.(married_at.isnot:null, married_at.gt:now))
)

Useful reference: functions: https://www.postgresql.org/docs/14/functions.html range operators: https://www.postgresql.org/docs/14/functions-range.html array operators: https://www.postgresql.org/docs/current/functions-array.html

func NewOps 

func NewOps(t Type) Op

func ParseOp 

func ParseOp(unk string) (Op, bool)

func (Op) Has 

func (op Op) Has(tgt Op) bool

Has checks if the ops is part of the rule.

func (Op) Is 

func (op Op) Is(tgt Op) bool

Is checks if the op is equal the other value.

func (Op) String 

func (op Op) String() string

func (Op) Valid 

func (op Op) Valid() bool

Valid returns true if the ops is set. Ops can be compound bits, so checking individual ones would not make sense.

type Order 

type Order struct {
	Field     string
	Direction SortDirection
	Option    SortOption
}

func NewOrder 

func NewOrder(s string) (*Order, error)

func ParseOrder 

func ParseOrder(orders []string) ([]Order, error)

func (Order) String 

func (o Order) String() string

type ParserFn 

type ParserFn func(s string) (any, error)

ParserFn handles conversion of the querystring `string` input to the designated types.

type Query 

type Query struct {
	Field  string
	Op     Op
	Values []string
}

Query represents the parsed query string with operators.

func NewQuery added in v0.0.2 

func NewQuery(query string, values []string) *Query

func ParseQuery 

func ParseQuery(values url.Values) ([]Query, error)

ParseQuery parses the query with operators.

func (Query) String 

func (q Query) String() string

func (*Query) Validate added in v0.0.2 

func (q *Query) Validate() error

type SortDirection 

type SortDirection string

func (SortDirection) DefaultOption 

func (o SortDirection) DefaultOption() SortOption

func (SortDirection) Valid 

func (o SortDirection) Valid() bool

type SortOption 

type SortOption string

func (SortOption) Valid 

func (o SortOption) Valid() bool

type Tag 

type Tag struct {
	Type Type
	Name string
	Tag  string
	Sort bool
	Ops  Op
}

func ParseTag 

func ParseTag(tag string) (*Tag, error)

type Type 

type Type struct {
	Name  string
	Null  bool
	Array bool
}

Type represents the type of the field.

func TypeOf 

func TypeOf(t reflect.Type) Type

TypeOf handles conversion for the following: 

type Struct {
	field1 *Type
	field2 Type
	field3 []Type
	field4 []*Type
	field5 *[]Type
	field6 *[]*Type
}

func (*Type) Valid 

func (t *Type) Valid() bool

Valid returns true if the type is not empty.

Source Files

View all Source files

Directories

Path Synopsis

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.