rql

package module
v1.11.0 Latest Latest
Warning

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

 Go to latest Published: Dec 7, 2022 License: MIT Imports: 17 Imported by: 0

README

RQL
GoDoc LICENSE

NOTE: this is a for of original RQL project, with multiple fixes and added search feature. It's currently diverged from original project to the point, that it's unlikely to be merged back.

RQL is a resource query language for REST. It provides a simple and light-weight API for adding dynamic querying capabilities to web-applications that use SQL-based database. It functions as the connector between the HTTP handler and the DB engine, and manages all validations and translations for user inputs.

rql diagram

Motivation

In the last several years I have found myself working on different web applications in Go, some of them were small and some of them were big with a lot of entities and relations. In all cases I never found a simple and standard API to query my resources.

What do I mean by query? Let's say our application has a table of orders, and we want our users to be able to search and filter by dynamic parameters. For example: select all orders from today with price greater than 100.
In order to achieve that I used to pass these parameters in the query string like this: created_at_gt=X&price_gt=100.
But sometimes it became complicated when I needed to apply a disjunction between two conditions. For example, when I wanted to select all orders that canceled or created last week and still didn't ship. And in SQL syntax:

SELECT * FROM ORDER WHERE canceled = 1 OR (created_at < X AND created_at > Y AND shipped = 0)

I was familiar with the MongoDB syntax and I felt that it was simple and robust enough to achieve my goals, and decided to use it as the query language for this project. I wanted it to be project agnostic in the sense of not relying on anything that related to some specific application or resource. Therefore, in order to embed rql in a new project, a user just needs to import the package and add the desired tags to his struct definition. Follow the Getting Started section to learn more.

Getting Started

rql uses a subset of MongoDB query syntax. If you are familiar with the MongoDB syntax, it will be easy for you to start. Although, it's pretty simple and easy to learn.
In order to embed rql you simply need to add the tags you want (filter or sort) to your struct definition, and rql will manage all validations for you and return an informative error for the end user if the query doesn't follow the schema. Here's a short example of how to start using rql quickly, or you can go to API for more expanded documentation.

// An example of an HTTP handler that uses gorm, and accepts user query in either the body
// or the URL query string.
package main

var (
	db *gorm.DB
	// QueryParam is the name of the query string key.
	QueryParam = "query"
	// MustNewParser panics if the configuration is invalid.
	QueryParser = rql.MustNewParser(rql.Config{
		Model:    User{},
		FieldSep: ".",
	})
)

// User is the model in gorm's terminology.
type User struct {
	ID          uint      `gorm:"primary_key" rql:"filter,sort"`
	Admin       bool      `rql:"filter"`
	Name        string    `rql:"filter"`
	AddressName string    `rql:"filter"`
	CreatedAt   time.Time `rql:"filter,sort"`
}


// GetUsers is an http.Handler that accepts a db query in either the body or the query string.
func GetUsers(w http.ResponseWriter, r *http.Request) {
	var users []User
	p, err := getDBQuery(r)
	if err != nil {
		io.WriteString(w, err.Error())
		w.WriteHeader(http.StatusBadRequest)
		return
	}
	err = db.Where(p.FilterExp, p.FilterArgs).
		Offset(p.Offset).
		Limit(p.Limit).
		Order(p.Sort).
		Find(&users).Error
	if err != nil {
		w.WriteHeader(http.StatusInternalServerError)
		return
	}
	if err := json.NewEncoder(w).Encode(users); err != nil {
		w.WriteHeader(http.StatusInternalServerError)
		return
	}
	w.Header().Set("Content-Type", "application/json")
}

// getDBQuery extract the query blob from either the body or the query string
// and execute the parser.
func getDBQuery(r *http.Request) (*rql.Params, error) {
	var (
		b   []byte
		err error
	)
	if v := r.URL.Query().Get(QueryParam); v != "" {
		b, err = base64.StdEncoding.DecodeString(v)
	} else {
		b, err = ioutil.ReadAll(io.LimitReader(r.Body, 1<<12))
	}
	if err != nil {
		return nil, err
	}
	return QueryParser.Parse(b)
}

Go to examples/simple to see the full working example.

API

In order to start using rql, you need to configure your parser. Let's go over a basic example of how to do this. For more details and updated documentation, please checkout the godoc.
There are two options to build a parser, rql.New(rql.Config), and rql.MustNew(rql.Config). The only difference between the two is that rql.New returns an error if the configuration is invalid, and rql.MustNew panics.

// we use rql.MustPanic because we don't want to deal with error handling in top level declarations.
var Parser = rql.MustNew(rql.Config{
	// User if the resource we want to query.
	Model: User{},
	// Since we work with gorm, we want to use its column-function, and not rql default.
	// although, they are pretty the same.
	ColumnFn: gorm.ToDBName,
	// Use your own custom logger. This logger is used only in the building stage.
	Log: logrus.Printf,
	// Default limit returned by the `Parse` function if no limit provided by the user.
	DefaultLimit: 100,
	// Accept only requests that pass limit value that is greater than or equal to 200.
	LimitMaxValue: 200,
})

rql uses reflection in the build process to detect the type of each field, and create a set of validation rules for each one. If one of the validation rules fails or rql encounters an unknown field, it returns an informative error to the user. Don't worry about the usage of reflection, it happens only once when you build the parser. Let's go over the validation rules:

  1. int (8,16,32,64), sql.NullInt6 - Round number
  2. uint (8,16,32,64), uintptr - Round number and greater than or equal to 0
  3. float (32,64), sql.NullFloat64: - Number
  4. bool, sql.NullBool - Boolean
  5. string, sql.NullString - String
  6. time.Time, and other types that convertible to time.Time - The default layout is time.RFC3339 format (JS format), and parsable to time.Time. It's possible to override the time.Time layout format with custom one. You can either use one of the standard layouts in the time package, or use a custom one. For example: 
    type User struct {
 	T1 time.Time `rql:"filter"`                         // time.RFC3339
 	T2 time.Time `rql:"filter,layout=UnixDate"`         // time.UnixDate
 	T3 time.Time `rql:"filter,layout=2006-01-02 15:04"` // 2006-01-02 15:04 (custom)
}

Note that all rules are applied to pointers as well. It means, if you have a field Name *string in your struct, we still use the string validation rule for it.

User API

We consider developers as the users of this API (usually FE developers). Let's go over the JSON API we export for resources.
The top-level query accepts JSON with 4 fields: offset, limit, filter and sort. All of them are optional.

offset and limit

These two fields are useful for paging and they are equivalent to OFFSET and LIMIT in a standard SQL syntax.

  • offset must be greater than or equal to 0 and its default value is 0
  • limit must be greater than and less than or equal to the configured LimitMaxValue. The default value for LimitMaxValue is 100
sort

Sort accepts a slice of strings ([]string) that is translated to the SQL ORDER BY clause. The given slice must contain only columns that are sortable (have tag rql:"sort"). The default order for column is ascending order in SQL, but you can control it with an optional prefix: + or -. + means ascending order, and - means descending order. Let's see a short example:

For input - ["address.name", "-address.zip.code", "+age"]
Result is - address_name, address_zip_code DESC, age ASC
select

Select accepts a slice of strings ([]string) that is joined with comma (",") to the SQL SELECT clause.

For input - ["name", "age"]
Result is - "name, age"
filter

Filter is the one who is translated to the SQL WHERE clause. This object that contains filterable fields or the disjunction ($or) operator. Each field in the object represents a condition in the WHERE clause. It contains a specific value that matched the type of the field or an object of predicates. Let's go over them:

  • Field follows the format: field: <value>, means the predicate that will be used is =. For example: 
    For input:
{
  "admin": true
}

Result is: admin = ?
    You can see that RQL uses placeholders in the generated WHERE statement. Follow the examples section to see how to use it properly.
  • If the field follows the format: field: { <predicate>: <value>, ...}, For example: 
    For input:
{
  "age": {
    "$gt": 20,
    "$lt": 30
  }
}

Result is: age > ? AND age < ?
    It means that the logical AND operator used between the two predicates. Scroll below to see the full list of the supported predicates.
  • $or is a field that represents the logical OR operator, and can be in any level of the query. Its type need to be an array of condition objects and the result of it is the disjunction between them. For example: 
    For input:
{
  "$or": [
    { "city": "TLV" },
    { "zip": { "$gte": 49800, "$lte": 57080 } }
  ]
}

Result is: city = ? OR (zip >= ? AND zip <= ?)

To simplify that, the rule is AND for objects and OR for arrays. Let's go over the list of supported predicates and then we'll show a few examples.

Predicates
  • $eq and $neq - can be used on all types
  • $gt, $lt, $gte and $lte - can be used on numbers, strings, and timestamp
  • $like - can be used only on type string

If a user tries to apply an unsupported predicate on a field it will get an informative error. For example:

For input:
{
  "age": {
    "$like": "%0"
  }
}

Result is: can not apply op "$like" on field "age"

Examples

Assume this is the parser for all examples.


var QueryParser = rql.MustNewParser(rql.Config{
	Model:    	User{},
	FieldSep: 	".",
	LimitMaxValue:	25,
})
	
type User struct {
	ID          uint      `gorm:"primary_key" rql:"filter,sort"`
	Admin       bool      `rql:"filter"`
	Name        string    `rql:"filter,search"`
	Address     string    `rql:"filter,search"`
	CreatedAt   time.Time `rql:"filter,sort"`
}
Simple Example
params, err := QueryParser.Parse([]byte(`{
  "limit": 25,
  "offset": 0,
  "filter": {
    "admin": false
  }
  "sort": ["+name"]
}`))
must(err, "parse should pass")
fmt.Println(params.Limit)	// 25
fmt.Println(params.Offset)	// 0
fmt.Println(params.Sort)	// "name ASC"
fmt.Println(params.FilterExp)	// "name = ?"
fmt.Println(params.FilterArgs)	// [true]

In this case you've a valid generated rql.Param object and you can pass its to your favorite package connector.

var users []*User

// entgo.io (A type-safe entity framework)
users, err = client.User.Query().
    Where(func(s *sql.Selector) {
        s.Where(sql.ExprP(p.FilterExp, p.FilterArgs...))
    }).
    Limit(p.Limit).
    Offset(p.Offset).
    All(ctx)
must(err, "failed to query ent")

// gorm
err = db.Where(p.FilterExp, p.FilterArgs).
	Offset(p.Offset).
	Limit(p.Limit).
	Order(p.Sort).
	Find(&users).Error
must(err, "failed to query gorm")

// xorm
err = engine.Where(p.FilterExp, p.FilterArgs...).
	Limit(p.Limit, p.Offset).
	OrderBy(p.Sort).
	Find(&users)
must(err, "failed to query xorm")

// go-pg/pg
err = db.Model(&users).
	Where(p.FilterExp, p.FilterArgs).
	Offset(p.Offest).
	Limit(p.Limit).
	Order(p.Sort).
	Select()
must(err, "failed to query pg/orm")

// Have more example? feel free to add.
Medium Example
params, err := QueryParser.Parse([]byte(`{
  "limit": 25,
  "filter": {
    "admin": false,
    "created_at": {
      "$gt": "2018-01-01T16:00:00.000Z",
      "$lt": "2018-04-01T16:00:00.000Z"
    }
    "$or": [
      { "address": "TLV" },
      { "address": "NYC" }
    ]
  }
  "sort": ["-created_at"]
}`))
must(err, "parse should pass")
fmt.Println(params.Limit)	// 25
fmt.Println(params.Offset)	// 0
fmt.Println(params.Sort)	// "created_at DESC"
fmt.Println(params.FilterExp)	// "admin = ? AND created_at > ? AND created_at < ? AND (address = ? OR address = ?)"
fmt.Println(params.FilterArgs)	// [true, Time(2018-01-01T16:00:00.000Z), Time(2018-04-01T16:00:00.000Z), "TLV", "NYC"]

Future Plans and Contributions

If you want to help with the development of this package, here is a list of options things I want to add

  • JS library for query building
  • Option to ignore validation with specific tag
  • Add $not and $nor operators
  • Automatically (or by config) filter and sort gorm.Model fields
  • benchcmp for PRs
  • Support MongoDB. Output need to be a bison object. here's a usage example
  • Right now rql assume all fields are flatted in the db, even for nested fields. For example, if you have a struct like this: 
    type User struct {
    Address struct {
        Name string `rql:"filter"`
    }
}
    rql assumes that the address_name field exists in the table. Sometimes it's not the case and address exists in a different table. Therefore, I want to add the table= option for fields, and support nested queries.
  • Code generation version - low priority

Performance and Reliability

The performance of RQL looks pretty good, but there is always a room for improvement. Here's the current bench result:

Test Time/op B/op allocs/op
Small 1809 960 19
Medium 6030 3100 64
Large 14726 7625 148

I ran fuzzy testing using go-fuzz and I didn't see any crashes. You are welcome to run by yourself and find potential failures.

LICENSE

I am providing code in the repository to you under MIT license. Because this is my personal repository, the license you receive to my code is from me and not my employer (Facebook)

Documentation

Index

Constants

View Source 
const (
	EQ   = Op("eq")   // =
	NEQ  = Op("neq")  // <>
	LT   = Op("lt")   // <
	GT   = Op("gt")   // >
	LTE  = Op("lte")  // <=
	GTE  = Op("gte")  // >=
	LIKE = Op("like") // LIKE "PATTERN"
	OR   = Op("or")   // disjunction
	AND  = Op("and")  // conjunction
	IN   = Op("in")   // IN ()
	NIN  = Op("nin")  // NOT IN ()
)

Operators that support by rql.

View Source 
const (
	DefaultTagName  = "rql"
	DefaultOpPrefix = "$"
	DefaultFieldSep = "_"
	DefaultLimit    = 25
	DefaultMaxLimit = 100
	Offset          = "offset"
	Limit           = "limit"
)

Default values for configuration.

Variables

This section is empty.

Functions

func Column 

func Column(s string) string

Column is the default function that converts field name into a database column. It used to convert the struct fields into their database names. For example: 

Username => username
FullName => full_name
HTTPCode => http_code

func Value 

func Value(columnKey string) func(interface{}) interface{}

Types

type Config 

type Config struct {
	// TagName is an optional tag name for configuration. t defaults to "rql".
	TagName string
	// Model is the resource definition. The parser is configured based on the its definition.
	// For example, given the following struct definition:
	//
	//	type User struct {
	//		Age	 int	`rql:"filter,sort"`
	// 		Name string	`rql:"filter"`
	// 	}
	//
	// In order to create a parser for the given resource, you will do it like so:
	//
	//	var QueryParser = rql.MustNewParser(
	// 		Model: User{},
	// 	})
	//
	Model interface{}
	// OpPrefix is the prefix for operators. it defaults to "$". for example, in order
	// to use the "gt" (greater-than) operator, you need to prefix it with "$".
	// It similar to the MongoDB query language.
	OpPrefix string
	// FieldSep is the separator for nested fields in a struct. For example, given the following struct:
	//
	//	type User struct {
	// 		Name 	string	`rql:"filter"`
	//		Address	struct {
	//			City string `rql:"filter"“
	//		}
	// 	}
	//
	// We assume the schema for this struct contains a column named "address_city". Therefore, the default
	// separator is underscore ("_"). But, you can change it to "." for convenience or readability reasons.
	// Then you will be able to query your resource like this:
	//
	//	{
	//		"filter": {
	//			"address.city": "DC"
	// 		}
	//	}
	//
	// The parser will automatically convert it to underscore ("_"). If you want to control the name of
	// the column, use the "column" option in the struct definition. For example:
	//
	//	type User struct {
	// 		Name 	string	`rql:"filter,column=full_name"`
	// 	}
	//
	FieldSep string
	// ColumnFn is the function that translate the struct field string into a table column.
	// For example, given the following fields and their column names:
	//
	//	FullName => "full_name"
	// 	HTTPPort => "http_port"
	//
	// It is preferred that you will follow the same convention that your ORM or other DB helper use.
	// For example, If you are using `gorm` you want to se this option like this:
	//
	//	var QueryParser = rql.MustNewParser(
	// 		ColumnFn: gorm.ToDBName,
	// 	})
	//
	ColumnFn func(string) string
	// ValueFn is the function that translate the struct field value into table value.
	// for example, given the following fields and their column values:
	//	status => 0
	//
	// which is a protobuf value, you want to convert it to how the data is stored in the DB, that is:
	//	status => "pending"
	//
	ValueFn func(string) func(interface{}) interface{}
	// Log the the logging function used to log debug information in the initialization of the parser.
	// It defaults `to log.Printf`.
	Log func(string, ...interface{})
	// DefaultLimit is the default value for the `Limit` field that returns when no limit supplied by the caller.
	// It defaults to 25.
	DefaultLimit int
	// LimitMaxValue is the upper boundary for the limit field. User will get an error if the given value is greater
	// than this value. It defaults to 100.
	LimitMaxValue int
	// DefaultSort is the default value for the 'Sort' field that returns when no sort expression is supplied by the caller.
	// It defaults to an empty string slice.
	DefaultSort []string
}

Config is the configuration for the parser.

type ConvertFn 

type ConvertFn func(v interface{}) interface{}

type Op 

type Op string

Op is a filter operator used by rql.

func (Op) SQL 

func (o Op) SQL() string

SQL returns the SQL representation of the operator.

type Params 

type Params struct {
	// Limit represents the number of rows returned by the SELECT statement.
	Limit int
	// Offset specifies the offset of the first row to return. Useful for pagination.
	Offset int
	// Select contains the expression for the `SELECT` clause defined in the Query.
	Select []string
	// Sort used as a parameter for the `ORDER BY` clause. For example, "age desc, name".
	Sort string
	// Search is used as a parameter for doing multi-column, case-insensitive searches.
	// Search and SearchArgs come together and used as a parameters for the `WHERE` clause.
	Search     string
	SearchArgs []interface{}
	// FilterExp and FilterArgs come together and used as a parameters for the `WHERE` clause.
	//
	// examples:
	// 	1. Exp: "name = ?"
	//	   Args: "a8m"
	//
	//	2. Exp: "name = ? AND age >= ?"
	// 	   Args: "a8m", 22
	FilterExp  string
	FilterArgs []interface{}
}

Params is the parser output after calling to `Parse`. You should pass its field values to your query tool. For example, Suppose you use `gorm`: 

params, err := p.Parse(b)
if err != nil {
	return nil, err
}
var users []User
err := db.Where(params.FilterExp, params.FilterArgs...).
	Order(params.Sort).
	Find(&users).
	Error
if err != nil {
	return nil, err
}
return users, nil

type ParseError 

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

ParseError is type of error returned when there is a parsing problem.

func (ParseError) Error 

func (p ParseError) Error() string

type Parser 

type Parser struct {
	Config
	// contains filtered or unexported fields
}

A Parser parses various types. The result from the Parse method is a Param object. It is safe for concurrent use by multiple goroutines except for configuration changes.

func MustNewParser 

func MustNewParser(c Config) *Parser

MustNewParser is like NewParser but panics if the configuration is invalid. It simplifies safe initialization of global variables holding a resource parser.

func NewParser 

func NewParser(c Config) (*Parser, error)

NewParser creates a new Parser. it fails if the configuration is invalid.

func (*Parser) Parse 

func (p *Parser) Parse(b []byte) (pr *Params, err error)

Parse parses the given buffer into a Param object. It returns an error if the JSON is invalid, or its values don't follow the schema of rql.

func (*Parser) ParseQuery 

func (p *Parser) ParseQuery(q *Query) (pr *Params, err error)

ParseQuery parses the given struct into a Param object. It returns an error if one of the query values don't follow the schema of rql.

type Query 

type Query struct {
	// Limit must be > 0 and <= to `LimitMaxValue`.
	Limit int `json:"limit,omitempty"`
	// Offset must be >= 0.
	Offset int `json:"offset,omitempty"`
	// Select contains the list of expressions define the value for the `SELECT` clause.
	// For example:
	//
	//	params, err := p.Parse([]byte(`{
	//		"select": ["name", "age"]
	//	}`))
	//
	Select []string `json:"select,omitempty"`
	// Sort contains list of expressions define the value for the `ORDER BY` clause.
	// In order to return the rows in descending order you can prefix your field with `-`.
	// For example:
	//
	//	params, err := p.Parse([]byte(`{
	//		"sort": ["name", "-age", "+redundant"]
	//	}`))
	//
	Sort []string `json:"sort,omitempty"`
	// Filter is the query object for building the value for the `WHERE` clause.
	// The full documentation of the supported operators is writtern in the README.
	// An example for filter object:
	//
	//	params, err := p.Parse([]byte(`{
	//		"filter": {
	//			"account": { "$like": "%github%" },
	//			"$or": [
	//				{ "city": "TLV" },
	//				{ "city": "NYC" }
	//			]
	//		}
	//	}`))
	//
	Filter map[string]interface{} `json:"filter,omitempty"`

	// Search is an object that allows to do cross-column searches on a specific model.
	// The same field can be used to run search queries against search engines, such as ElasticSearch without specifying a concrete column.
	// An example of a search object
	//	params, err := p.Parse([]byte(`{
	//		"search": {
	//			"query": "golang"
	//		}
	//	}`))
	Search Search `json:"search,omitempty"`
}

Query is the decoded result of the user input.

func (Query) MarshalEasyJSON 

func (v Query) MarshalEasyJSON(w *jwriter.Writer)

MarshalEasyJSON supports easyjson.Marshaler interface

func (Query) MarshalJSON 

func (v Query) MarshalJSON() ([]byte, error)

MarshalJSON supports json.Marshaler interface

func (*Query) UnmarshalEasyJSON 

func (v *Query) UnmarshalEasyJSON(l *jlexer.Lexer)

UnmarshalEasyJSON supports easyjson.Unmarshaler interface

func (*Query) UnmarshalJSON 

func (v *Query) UnmarshalJSON(data []byte) error

UnmarshalJSON supports json.Unmarshaler interface 

type Search struct {
	Query string `json:"query,omitempty"`
}

Search holds all search-related fields, namely the string the user queried for, but potentially other configuration variables as well.

type ValidateFn 

type ValidateFn func(v interface{}) error

type ValueFn 

type ValueFn func(v interface{}) interface{}

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.