sqld

README

sqld

A Go library to build and manage dynamic queries. It tries to remain as simple as possible, leveraging sqlx parameter annotation.

Legazy version

See the legacy module and the corresponding README.

Scope of the project

The scope of sqld is to provide an easy way to organize your dynamic queries, not to validate said queries. I suggest to use other tools like SQLParser and a lot of e2e tests!

Usage

func testQuery(db sqlx.ExtContext, args QueryArgs) ([]TestModel, error) {
	dynquery := `
	SELECT *
	FROM table
	`

	params := make(slqd.Params)	// it's just a map
	dynquery += sqld.Where(	// returns empty string if all predicates don't evaluate
		sqld.IfNotNil(args.Name, params, sqld.Contains("table.name"))
	)
	dynquery += sqld.IfNotNil(args.Limit, params, sqld.Limit)
	dynquery += sqld.IfNotNil(args.Offset, params, sqld.Offset)

	query, args, err := sqlx.Named(dynquery, params)
	if err != nil {
		return nil, err
	}

	rows, err := db.QueryxContext(ctx, db.Rebind(query), args...)	// use Rebind() with postgres
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var items []TestModel
	for rows.Next() {
		var item TestModel
		if err := rows.StructScan(&item); err != nil {
			return nil, err
		}

		items = append(items, item)
	}

	return items, nil
}

Documentation

Index

• func And(filters ...string) string
• func Asc(column string) string
• func Cond(op Op, filters ...string) string
• func Desc(column string) string
• func FmtContains[S string | *string](val S) S
• func FmtEndsWith[S string | *string](val S) S
• func FmtStartsWith[S string | *string](val S) S
• func Having(pred string) string
• func If[T any](pred PredicateFn[T], val T, params *Params, printer PrinterFn) string
• func IfNotNil[T any](val *T, params *Params, printer PrinterFn) string
• func IfNotZero[T comparable](val T, params *Params, printer PrinterFn) string
• func Not(filter string) string
• func Or(filters ...string) string
• func OrderBy(sorts ...string) string
• func Section(sect Sect, cond string) string
• func Sort(column string, sorting Sorting) string
• func Where(pred string) string
• type Op
• type Params
• type PredicateFn
• type PrinterFn
  • func Eq(target string) PrinterFn
  • func Gt(target string) PrinterFn
  • func Gte(target string) PrinterFn
  • func ILike(target string) PrinterFn
  • func In(target string) PrinterFn
  • func Like(target string) PrinterFn
  • func Lt(target string) PrinterFn
  • func Lte(target string) PrinterFn
• type Sect
• type Sorting

Functions

func And

func And(filters ...string) string

And builds a condition concatenating the filters with the AND operator. If the filters are all empty, the returned string is also empty.

func Asc

func Asc(column string) string

Asc produces an ASC sorting statement on the column, with the given direction

func Cond

func Cond(op Op, filters ...string) string

Cond builds a condition concatenating the filters with the given operator. If the filters are all empty, the returned string is also empty.

func Desc

func Desc(column string) string

Desc produces a DESC sorting statement on the column, with the given direction

func FmtContains

func FmtContains[S string | *string](val S) S

FmtContains maps the parameter with the desired pattern. Skips the mapping if the value is empty or nil

func FmtEndsWith

func FmtEndsWith[S string | *string](val S) S

FmtEndsWith maps the parameter with the desired pattern. Skips the mapping if the value is empty or nil

func FmtStartsWith

func FmtStartsWith[S string | *string](val S) S

FmtStartsWith maps the parameter with the desired pattern. Skips the mapping if the value is empty or nil

func Having

func Having(pred string) string

Having builds an HAVING filtering section. If the condition is empty, the returned string is also empty.

func If

func If[T any](pred PredicateFn[T], val T, params *Params, printer PrinterFn) string

If is used to build the query dynamically, based on runtime conditions.

If the predicate is true, the value is pushed in the parameter map and the printed filter is returned. If the predicate is false, the parameter map is untouched, and an empty string is returned.

func IfNotNil

func IfNotNil[T any](val *T, params *Params, printer PrinterFn) string

IfNotNil is a proxy for If with a predicate that checks if the pointer is not nil

func IfNotZero

func IfNotZero[T comparable](val T, params *Params, printer PrinterFn) string

IfNotZero is a proxy for If with a predicate that checks if the value is not equal to the zero value of its type

func Not

func Not(filter string) string

Not negates the given string. If the filter is empty, the returned string is also empty.

func Or

func Or(filters ...string) string

Or builds a condition concatenating the filters with the OR operator. If the filters are all empty, the returned string is also empty.

func OrderBy

func OrderBy(sorts ...string) string

OrderBy builds an ORDER BY section. If the sortings are all empty, the returned string is also empty.

func Section

func Section(sect Sect, cond string) string

Section builds a filtering section. If the condition is empty, the returned string is also empty.

func Sort

func Sort(column string, sorting Sorting) string

Sort produces a sorting statement on the column, with the given direction

func Where

func Where(pred string) string

Where builds a WHERE filtering section. If the condition is empty, the returned string is also empty.

Types

type Op

type Op string

Op is a boolean operator

const (
	OR  Op = "OR"
	AND Op = "AND"
)

type Params

type Params map[string]any

Params is just an alias for a map containing the query parameters

type PredicateFn

type PredicateFn[T any] func(T) bool

Predicate is a callback that validates a condition on a value

type PrinterFn

type PrinterFn func(string) string

PrinterFn is a callback that applies a parameter to the given statement (usually a filter)

func Eq

func Eq(target string) PrinterFn

Eq produces a PrinterFn that equates the target with the given parameter

func Gt

func Gt(target string) PrinterFn

Gt produces a PrinterFn that checks if the target is greater than the given parameter

func Gte

func Gte(target string) PrinterFn

Gte produces a PrinterFn that checks if the target is greater or equal the given parameter

func ILike

func ILike(target string) PrinterFn

ILike produces a PrinterFn that checks if the target text respects the given pattern, ignoring the casing

func In

func In(target string) PrinterFn

In produces a PrinterFn that checks if the target is contained in the given parameter slice

func Like

func Like(target string) PrinterFn

Like produces a PrinterFn that checks if the target text respects the given pattern

func Lt

func Lt(target string) PrinterFn

Lt produces a PrinterFn that checks if the target is smaller than the given parameter

func Lte

func Lte(target string) PrinterFn

Lte produces a PrinterFn that checks if the target is smaller or equal the given parameter

type Sect

type Sect string

Sect is a filtering section of the query

const (
	WHERE  Sect = "WHERE"
	HAVING Sect = "HAVING"
)

type Sorting

type Sorting string

Sorting is the direction in which you want to sort a query

const (
	ASC  Sorting = "ASC"
	DESC Sorting = "DESC"
)