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" = ? 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: "trips",
ColumnAliases: 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]
TODO
- Publish!
- Full tests with 100% code coverage
- Ability to add custom operators
- Manipulate the conditions? Such as
HasCondition(),UpdateCondition(),RemoveCondition()?
License
© Dat Ngo, 2019~time.Now()
Released under the MIT License
Documentation
¶
Index ¶
Constants ¶
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), } )
var Utils = utilsCollection{}
Utils list of predefined functions to make our life easier
Functions ¶
This section is empty.
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
// contains filtered or unexported fields
}
Config defines the config for planner
type CustomBuildFn ¶
CustomBuildFn represents the function to build SQL string for the operator
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
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 WhereMySQL ¶
func WhereMySQL(cond interface{}, vars ...interface{}) *Plan
WhereMySQL returns a plan with given conditions for MySQL, using default configurations
func WherePostgreSQL ¶
func WherePostgreSQL(cond interface{}, vars ...interface{}) *Plan
WherePostgreSQL returns a plan with given conditions for PostgreSQL, using default configurations
func WithConfig ¶
WithConfig returns an empty plan using the given configs. Zero value will be replaced by default config.
Source Files