goql

goql

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):

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

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

And/Or

AND/OR can be chained and nested.

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)

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"`
}

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"`
}

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