README
¶
Queryfilter
Construct parameterized SQL where clauses using Go structs.
Installing
import "github.com/tmw/queryfilter"
go get github.com/tmw/queryfilter
Usage
// define a struct with fields to filter on
type Filter struct {
Sizes []string `filter:"size,op=in"`
PriceMin int `filter:"price,op=gte"`
PriceMax int `filter:"price,op=lte"`
}
// pass the right values
f := Filter{
Sizes: []string{"L", "XL"},
PriceMin: 15,
PriceMax: 35,
}
// turn into parameterized SQL statement
query, params, err := queryfilter.ToSQL(f)
if err != nil {
log.Fatal(fmt.Errorf("error building query: %w", err)))
}
// Results in:
// query = sizes IN(?, ?) AND price > ? AND price < ?
// params = []any{"L", "XL", 15, 35}
// passing it to the DB layer:
query = fmt.Sprintf("SELECT * FROM tshirts WHERE %s", query)
rows, err := db.Query(query, params...)
Example implementation
For an example implementation of a T-shirt store API, head over here.
Running examples
# to run the sqlite example:
make example-sqlite
# to run the mysql example: (required docker & docker compose)
make example-mysql
# to run the postgres example: (required docker & docker compose)
make example-postgres
Built-in operators
Out of the box QueryFilter comes with a few operators built-in. However adding your own custom operators is quite trivial. See examples in operator.go.
The built-in operators are:
op name |
SQL equivalent | Notes |
|---|---|---|
eq |
= |
|
gt |
> |
|
gte |
>= |
|
lt |
< |
|
lte |
<= |
|
in |
IN(?) |
Works on slices/arrays |
not-in |
NOT IN(?) |
works on slices/arrays |
between |
BETWEEN ? AND ? |
Works on slices/arrays of length 2 |
is-null |
IS NULL / IS NOT NULL |
Works on boolean types. Uses null/not null when passing true/false respectively |
not-null |
IS NOT NULL / IS NULL |
Works on boolean types. Uses not null/null when passing true/false respectively |
Other commands
make test # run the testsuite once
make watch # run tests continually (watch mode; requires gotestsum)
make coverage # generate test coverage report
License
Documentation
¶
Index ¶
Constants ¶
This section is empty.
Variables ¶
var ( // TagName defines the struct tag we look for in the structs we're parsing, // eg: the `filter` in `filter:"name,op=eq"`. It can be configured by setting // `queryFilter.TagName`, eg: `queryFilter.TagName = "qf"` to the value you desire. TagName = "filter" // Operators is a globally defined map of available operators. // See the Operator type for more info. Operators = map[string]Operator{} // DefaultChainingStrategy defines how clauses are glued together. Eg: using an `OR` statement // or an `AND` statement. Default to using `AND` but can be configured either globally or // on an individual basis when calling `ToSQL`. DefaultChainingStrategy = ChainingStrategyAnd // DefaultPlaceholderStrategy configures how we define placeholders in the resulting query. // // This can be configured globally or on an individual basis when calling `ToSQL`. // It defaults to PlaceholderStategyQuestionmark but is configurable either globally or // on an individual basis when calling `ToSQL`. DefaultPlaceholderStrategy = PlaceholderStrategyQuestionmark // Some of the placeholder stategies are indexed (eg: $1, $2, $3, etc..) // To avoid clashes with the rest of your query, the starting index is configurable using this option. // // It defaults to 1 but is configurable either globally or on an individual basis when calling `ToSQL`. DefaultPlaceholderStategyIndexOffset = 1 )
Functions ¶
func PlaceholderList ¶
PlaceholderList generates a list of n placeholder symbols (?) as a comma separated string. eg: PlaceholderList(3) => "?,?,?".
note that these placeholders are internal only and will be replaced by the placeholders configured by the PlaceholderStrategy when calling ToSQL.
func RegisterOperator ¶
Note that calling this function multiple times with the same name will overwrite the function previously registered to the operator without warning.
Example:
RegisterOperator("eq", func()...)
type Filter struct {
Price int `filter:"price,op=eq"`
^^--- operator name
}
Types ¶
type ChainingStrategy ¶
type ChainingStrategy string
ChainingStrategy defines how clauses are glued together in the resulting querystring. Eg: using an `OR` statement or an `AND` statement. possible options are ChainStrategyOr and ChainStrategyAnd.
const ( ChainingStrategyOr ChainingStrategy = "OR" ChainingStrategyAnd ChainingStrategy = "AND" )
type Clause ¶
type Clause struct {
// Col describes the database column the operation works on.
Col string
// Op describes what operation to use on the column (eg: eq, gt, lt, etc)
Op string
// Val holds the value the operation is performed with
Val any
// contains filtered or unexported fields
}
Clause holds the fields that are parsed from the original QueryFilter struct fields.
The only outside use of this type is when defining a custom operator, as an operator is defined as a function that takes a Clause and returns the query segment, the values to be used in the query, and optionally an error.
func (*Clause) AssertTypeOneOf ¶
AssertTypeOneOf checks if the Clause's reflected value is one of the provided kinds.
This function is used in custom operators to check if the provided field in the QueryFilter struct is of a type that the operator can work on. For example for the use of the `in` or `between` operator, a slice or array type is expected.
the function returns an error if there's a mismatch in the types.
type Operator ¶
Operator is a function that receives a clause and returns the query segment as a string and a slice of values.
Custom operators can be defined by assigning them by name to the global Operators map, eg:
queryfilter.Operators["my-operator"] = func(c Clause) (string, []any, error) {...} which can then be used in a filter struct:
type filter struct {
Age *int `filter:"age,op=my-operator"`
}
func SimpleOperator ¶
SimpleOperator is a shorthand function for creating operators with a one-to-one matching between column and value. Examples of these are eq, gt, gte without any custom logic.
eg: SimpleOperator("> ?") will return a function that will return the query segment "> ?" and the value of the Clause struct as the argument.
type OptFn ¶
type OptFn = func(o *Opts)
func WithChainingStrategy ¶
func WithChainingStrategy(typ ChainingStrategy) OptFn
func WithPlaceholderOffset ¶
func WithPlaceholderStrategy ¶
func WithPlaceholderStrategy(strategy PlaceholderStrategy) OptFn
type Opts ¶
type Opts struct {
ChainingStrategy ChainingStrategy
PlaceholderStrategy PlaceholderStrategy
PlaceholderOffset int
}
Opts defines the options that are used when running `ToSQL`. the opts are constructed everytime `ToSQL` is called and can be configured through the defaults defined globally on this module or overwritten on a case-by-case basis when calling `ToSQL` through the use of the `OptFn` type.
eg: to set the chaining strategy to `OR` for this call only:
_, _, _ := ToSQL(filter, WithChainingStrategy(ChainingStrategyOr))
func DefaultOpts ¶
func DefaultOpts() *Opts
type PlaceholderStrategy ¶
type PlaceholderStrategy int
PlaceholderStrategy defines how placeholders are defined in the resulting querystring. for example working with MySQL based databases you would want to use PlaceholderStrategyQuestionmark as the resulting querystring would include a single questionmark as a placeholder
const ( // PlaceholderStrategyQuestionmark will insert a single questionmark as a placeholder. // this option is best suited for MySQL / MariaDB / SQLite databases. PlaceholderStrategyQuestionmark PlaceholderStrategy = iota // PlaceholderStrategyColon will insert a positional placeholder using a colon (:1, :2, etc) PlaceholderStrategyColon // PlaceholderStrategyDollar will insert a positional placeholder using a dollar sign ($1, $2, etc). // Most commonly used with PostgreSQL databases. PlaceholderStrategyDollar )
Source Files
Directories