gowhere

README

SQL WHERE clause builder for Go

gowhere is neither an ORM package nor full-featured SQL builder. It only provides a flexible and powerful way to build SQL WHERE string from a simple array/slice or a map.

The goal of this package is to create an adapter for frontend app to easily "query" the given data. In the other way around, backend app can "understand" the request from frontend app to correctly build the SQL query. With minimum configurations and coding!

This package can also be used together with standard "database/sql" package or other ORM packages for better experience, if any :)

Install

go get -u github.com/imdatngo/gowhere

This package is dependency-free!

Usages

The simple way:

import "github.com/imdatngo/gowhere"

plan := gowhere.Where(map[string]interface{}{
    "name__contains": "Gopher",
    "budget__gte": 1000,
    "date__between": []interface{}{"2019-04-13", "2019-04-15"},
})

plan.SQL()
// ("name" LIKE ? AND "budget" >= ? and "date" BETWEEN ? AND ?)

plan.Vars()
// [%Gopher% 1000 2019-04-13 2019-04-15]

The advanced way:

import "github.com/imdatngo/gowhere"

// initialize with all available configurations
plan := gowhere.WithConfig(gowhere.Config{
    Separator: "__",
    Dialect: gowhere.DialectPostgreSQL,
    Strict: true,
    Table: "",
    ColumnAliases: map[string]string{},
    CustomConditions: map[string]CustomConditionFn{
        "search": func(key string, val interface{}, cfg *gowhere.Config) interface{} {
            val = "%" + val.(string) + "%"
            return []interface{}{"lower(full_name) like ? OR lower(title) like ?", val, val}
        },
    },
})

// modify the config on the demands
plan.SetTable("trips").SetColumnAliases(map[string]string{"name": "full_name"})

// a map will be translated to "AND" conditions
plan.Where(map[string]interface{}{
    "name__contains": "Gopher",
    "budget__gte": 1000,
})

// a slice will be "OR" conditions
plan.Where([]map[string]interface{}{
    {"started_at__date": "2019-04-13"},
    {"started_at__date": "2019-04-15"},
    {"started_at__gte": time.Date(2019, 4, 19, 0, 0, 0, 0, time.Local)},
})

// same as `Where`, `Not` receives either map, slice or raw SQL string. Then it simply wraps the conditions with "NOT" keyword
plan.Not("members < ? AND members > ?", 2, 10)

// `Or` be like: ((all_current_conditions) OR (new_conditions))
plan.Or("anywhere = TRUE")

// In "Strict" mode, any invalid conditions/operators if given will cause `InvalidCond` error. Not to mention the not-tested runtime errors :)
if err := plan.Build().Error; err != nil {
    panic(err)
}

plan.SQL()
// ((("trips"."full_name" LIKE ? AND "trips"."budget" >= ?) AND ((DATE("trips"."started_at") = ?) OR (DATE("trips"."started_at") = ?) OR ("trips"."started_at" >= ?)) AND NOT (members < ? AND members > ?)) OR (anywhere = TRUE))

plan.Vars()
// [%Gopher% 1000 2019-04-13 2019-04-15 2019-04-19 2 10]

Operator

For example: "name__startswith", name is the field(column) and startswith is the operator. Django developer might find this familiar ;)

Operator is an user friendly name for a specific SQL operator. It's a suffix which added into the field to reduce the complexity from input schema, yet flexible enough to generate complex conditions.

Operator is optional. If not given, it's set to:

• isnull if the value is nil. E.g: {"name": nil} => sql, vars: name IS NULL, []
• in if the value is slice or array. E.g: {"id": []int{1, 2, 3}} => id in (?), [[1 2 3]]
• exact if otherwise. E.g: {"name": "Gopher"} => name = ?, [Gopher]

Built-in operators:

• exact: Exact match, using = operator.
• iexact: Case-insensitive exact match, wrap both column and value with lower() function.
• notexact: Opposite of exact
• notiexact: Opposite of iexact
• gt: Greater than
• gte: Greater than or equal to
• lt: Less than
• lte: Less than or equal to
• startswith: Case-sensitive starts-with, auto cast value to string with % suffix
• istartswith: Case-insensitive starts-with
• endswith: Case-sensitive ends-with, auto cast value to string with % prefix
• iendswith: Case-insensitive ends-with
• contains: Case-insensitive containment test, auto cast value to string with both % suffix, prefix
• icontains: Case-sensitive containment test
• in: In a given slice, array
• date: For datetime fields, casts the value as date
• between: For datetime string fields, range test
• isnull: Takes either True or False, which correspond to SQL queries of IS NULL and IS NOT NULL, respectively.
• datebetween: For query datetime range fields

TODO

• Publish!
• Ability to add custom operators
• Ability to add custom conditions
• Full tests with 100% code coverage
• Manipulate the conditions? Such as HasCondition(), UpdateCondition(), RemoveCondition()?

License

© Dat Ngo, 2019~time.Now()

Released under the MIT License

Documentation

Index

• Constants
• Variables
• type Config
• type CustomBuildFn
• type CustomConditionFn
• type Dialect
• type InvalidCond
  • func (e *InvalidCond) Error() string
• type ModValueFn
• type Operator
  • func (o *Operator) Build(field string, value interface{}, cfg *Config) (string, []interface{})
• type Plan
  • func Where(cond interface{}, vars ...interface{}) *Plan
  • func WithConfig(conf Config) *Plan
  • func (p *Plan) And(cond interface{}, vars ...interface{}) *Plan
  • func (p *Plan) Build() (rp *Plan)
  • func (p *Plan) Not(cond interface{}, vars ...interface{}) *Plan
  • func (p *Plan) Or(cond interface{}, vars ...interface{}) *Plan
  • func (p *Plan) SQL() string
  • func (p *Plan) SetColumnAliases(aliases map[string]string, mode ...rune) *Plan
  • func (p *Plan) SetCustomConditions(aliases map[string]CustomConditionFn, mode ...rune) *Plan
  • func (p *Plan) SetTable(value string) *Plan
  • func (p *Plan) Vars() []interface{}
  • func (p *Plan) Where(cond interface{}, vars ...interface{}) *Plan

Constants

const (
	OverwriteMode = 'o'
	AppendMode    = 'a'
	WriteMode     = 'w'
)

Modes to set configurations

const (
	// DialectMySQLName defines the MySQL dialect name
	DialectMySQLName = "mysql"
	// DialectPostgreSQLName defines the PostgreSQL dialect name
	DialectPostgreSQLName = "postgres"
)

Variables

var (
	// DialectMySQL predefines the MySQL dialect
	DialectMySQL = &mysqlDialect{}
	// DialectPostgreSQL predefines the PostgreSQL dialect
	DialectPostgreSQL = &postgresqlDialect{}
)

var (
	// DefaultConfig is the default configuration of the planner
	DefaultConfig = Config{
		Separator:        "__",
		Dialect:          DialectPostgreSQL,
		ColumnAliases:    make(map[string]string),
		CustomConditions: make(map[string]CustomConditionFn),
	}
)

var (

	// OperatorsList defines the list of built-in operators
	OperatorsList = map[string]*Operator{
		"exact":     defaultOperator,
		"iexact":    &Operator{Template: "LOWER(%s) %s LOWER(?)"},
		"notexact":  &Operator{Operator: "<>"},
		"inotexact": &Operator{Operator: "<>", Template: "LOWER(%s) %s LOWER(?)"},

		"gt":  &Operator{Operator: ">"},
		"lt":  &Operator{Operator: "<"},
		"gte": &Operator{Operator: ">="},
		"lte": &Operator{Operator: "<="},

		"startswith": &Operator{
			Operator: "LIKE",
			ModValue: func(value interface{}) interface{} {
				return Utils.ToString(value) + "%"
			},
		},
		"istartswith": &Operator{
			Operator: "LIKE",
			Template: "LOWER(%s) %s LOWER(?)",
			ModValue: func(value interface{}) interface{} {
				return Utils.ToString(value) + "%"
			},
		},
		"endswith": &Operator{
			Operator: "LIKE",
			ModValue: func(value interface{}) interface{} {
				return "%" + Utils.ToString(value)
			},
		},
		"iendswith": &Operator{
			Operator: "LIKE",
			Template: "LOWER(%s) %s LOWER(?)",
			ModValue: func(value interface{}) interface{} {
				return "%" + Utils.ToString(value)
			},
		},
		"contains": &Operator{
			Operator: "LIKE",
			ModValue: func(value interface{}) interface{} {
				return "%" + Utils.ToString(value) + "%"
			},
		},
		"icontains": &Operator{
			Operator: "LIKE",
			Template: "LOWER(%s) %s LOWER(?)",
			ModValue: func(value interface{}) interface{} {
				return "%" + Utils.ToString(value) + "%"
			},
		},
		"in": &Operator{
			Operator: "IN",
			Template: "%s %s (?)",
			ModValue: func(value interface{}) interface{} {
				return Utils.ToSlice(value)
			},
		},
		"date": &Operator{
			Template: "DATE(%s) %s ?",
			ModValue: func(value interface{}) interface{} {
				return Utils.ToDate(value)
			},
		},
		"between": &Operator{
			CustomBuild: func(field string, value interface{}, cfg Config) (string, []interface{}) {
				var from interface{}
				var to interface{}

				if vi, ok := value.([]interface{}); ok && len(vi) >= 2 {
					from = vi[0]
					to = vi[1]
				} else if vs, ok := value.([]string); ok && len(vs) >= 2 {
					from = vs[0]
					to = vs[1]
				} else if vt, ok := value.([]time.Time); ok && len(vt) >= 2 {
					from = vt[0]
					to = vt[1]
				} else {
					return "", []interface{}{}
				}

				return fmt.Sprintf("%s BETWEEN ? AND ?", field), []interface{}{Utils.ToDateTime(from), Utils.ToDateTime(to)}
			},
		},
		"isnull": &Operator{
			CustomBuild: func(field string, value interface{}, cfg Config) (string, []interface{}) {
				operator := "IS NULL"
				if null, ok := value.(bool); ok && !null {
					operator = "IS NOT NULL"
				}
				return fmt.Sprintf("%s %s", field, operator), []interface{}{}
			},
		},
		"datebetween": &Operator{
			CustomBuild: func(field string, value interface{}, cfg Config) (string, []interface{}) {
				var from interface{}
				var to interface{}

				if vi, ok := value.([]interface{}); ok && len(vi) >= 2 {
					from = vi[0]
					to = vi[1]
				} else if vs, ok := value.([]string); ok && len(vs) >= 2 {
					from = vs[0]
					to = vs[1]
				} else if vt, ok := value.([]time.Time); ok && len(vt) >= 2 {
					from = vt[0]
					to = vt[1]
				} else {
					return "", []interface{}{}
				}

				return fmt.Sprintf("DATE(%s) BETWEEN ? AND ?", field), []interface{}{Utils.ToDate(from), Utils.ToDate(to)}
			},
		},
	}
)

var Utils = utilsCollection{}

Utils list of predefined functions to make our life easier

Types

type Config

type Config struct {
	// The separator between field and operation. Default to "__" which requires the condition format as: field__operator
	Separator string
	// Collections of methods to build correct SQL clause for specific dialect. Only support MySQL and PostgreSQL (default) for the moment
	Dialect Dialect
	// Whether to report error or silently skip anomalies in the conditions schema. Default to false
	Strict bool
	// Table name to add before the columns in SQL clause, i.e: table_name.column_name. Default to empty which will keep the column unchanged
	Table string
	// The map of column aliases to be replaced when build the SQL clause. Use cases:
	// Example: {"name": "foo.name", "bname": "bar.name"}
	ColumnAliases map[string]string
	// Custom conditions allow full access on the condition generating
	CustomConditions map[string]CustomConditionFn
	// contains filtered or unexported fields
}

Config defines the config for planner

type CustomBuildFn

type CustomBuildFn func(field string, value interface{}, cfg Config) (string, []interface{})

CustomBuildFn represents the function to build SQL string for the operator

type CustomConditionFn

type CustomConditionFn func(key string, val interface{}, cfg *Config) interface{}

CustomConditionFn represents the func signature which provide full access on the condition generating. Return value should be in form of condition, i.e a map or slice Return nil will exclude the condition from result

type Dialect

type Dialect interface {
	GetName() string
	QuoteIdentifier(string) string
}

Dialect represents the interface for a dialect

type InvalidCond

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

InvalidCond represents the error when invalid condition is given

func (*InvalidCond) Error

func (e *InvalidCond) Error() string

type ModValueFn

type ModValueFn func(value interface{}) interface{}

ModValueFn represents the function to modify only the value before actually build the SQL

type Operator

type Operator struct {
	// Reference to an existing operator
	AliasOf string
	// The actual SQL operator. Default to "=" if empty
	Operator string
	// The SQL template. Default to "%s %s ?"
	// Note: This must contains 2 "%s" placeholders for the column & operator, and 1 "?" for the value
	Template string
	// The function to build the SQL condition in your own way. Ignored if AliasOf is provided, ignores Operator & Template.
	CustomBuild CustomBuildFn
	// Instead of customize the whole build func, you probably only want to modify the value a litle bit
	ModValue ModValueFn
}

Operator represents an alias for the SQL operator

func (*Operator) Build

func (o *Operator) Build(field string, value interface{}, cfg *Config) (string, []interface{})

Build returns the SQL string & vars for a single condition.

type Plan

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

Plan contains information to build WHERE clause

func Where

func Where(cond interface{}, vars ...interface{}) *Plan

Where is shortcut to create new plan with default configurations

func WithConfig

func WithConfig(conf Config) *Plan

WithConfig returns an empty plan using the given configs. Zero value will be replaced by default config.

func (*Plan) And

func (p *Plan) And(cond interface{}, vars ...interface{}) *Plan

And is the alias of Where

func (*Plan) Build

func (p *Plan) Build() (rp *Plan)

Build builds the SQL clause and vars with given conditions

func (*Plan) Not

func (p *Plan) Not(cond interface{}, vars ...interface{}) *Plan

Not works similar to Where but reverses the condition operator(s)

func (*Plan) Or

func (p *Plan) Or(cond interface{}, vars ...interface{}) *Plan

Or wraps all current conditions and ties with the new "cond" by OR operator

func (*Plan) SQL

func (p *Plan) SQL() string

SQL returns the built SQL clause

func (*Plan) SetColumnAliases

func (p *Plan) SetColumnAliases(aliases map[string]string, mode ...rune) *Plan

SetColumnAliases updates the `ColumnAliases` config value

func (*Plan) SetCustomConditions

func (p *Plan) SetCustomConditions(aliases map[string]CustomConditionFn, mode ...rune) *Plan

SetCustomConditions updates the `CustomConditions` config values

func (*Plan) SetTable

func (p *Plan) SetTable(value string) *Plan

SetTable updates the `Table` config value

func (*Plan) Vars

func (p *Plan) Vars() []interface{}

Vars returns the list of vars for the built SQL clause

func (*Plan) Where

func (p *Plan) Where(cond interface{}, vars ...interface{}) *Plan

Where adds more condition(s) to the current Plan, using AND operator