bqb

README

Basic Query Builder

Compatibility

This has been tested with sqlite, PostGres, and MySQL, using database/sql, pq, pgx, and sqlx. By the nature of how it works it should be fully compatible with any DB interface and database that uses ? or $ parameter syntax.

Note: Go v1.20+ is required for BQB >= v1.4.0. Go v1.17+ is required for BQB <= v1.3.0.

Why

1. Simple, lightweight, and fast
2. Supports any and all syntax by the nature of how it works
3. Doesn't require learning special syntax or operators
4. 100% test coverage

Examples

Basic

q := bqb.New("SELECT * FROM places WHERE id = ?", 1234)
sql, params, err := q.ToSql()

Produces

SELECT * FROM places WHERE id = ?

PARAMS: [1234]

Postgres - ToPgsql()

Just call the ToPgsql() method instead of ToSql() to convert the query to Postgres syntax

q := bqb.New("DELETE FROM users").
    Space("WHERE id = ? OR name IN (?)", 7, []string{"delete", "remove"}).
    Space("LIMIT ?", 5)
sql, params, err := q.ToPgsql()

Produces

DELETE FROM users WHERE id = $1 OR name IN ($2, $3) LIMIT $4

PARAMS: [7, "delete", "remove", 5]

Raw - ToRaw()

Obvious warning: You should not use this for user input

The ToRaw() call returns a string with the values filled in rather than parameterized

q := New("a = ?, b = ?, c = ?", "my a", 1234, nil)
sql, err := q.ToRaw()

Produces

a = 'my a', b = 1234, c = NULL

Types

q := bqb.New(
    "int:? string:? []int:? []string:? Query:? JsonMap:? nil:? []intf:?",
    1, "2", []int{3, 3}, []string{"4", "4"}, bqb.New("5"), bqb.JsonMap{"6": 6}, nil, []interface{}{"a",1,true},
)
sql, _ := q.ToRaw()

Produces

int:1 string:'2' []int:3,3 []string:'4','4' Query:5 JsonMap:'{"6":6}' nil:NULL []intf:'a',1,true

driver.Valuer

The driver.Valuer interface is supported for types that are able to convert themselves to a sql driver value. See examples/main.go:valuer.

q := bqb.New("?", valuer)

Embedder

BQB provides an Embedder interface for directly replacing ? with a string returned by the RawValue method on the Embedder implementation.

This can be useful for changing sort direction or embedding table and column names. See examples/main.go:embedder for an example.

Note: Since this is a raw value, special attention should be paid to ensure user-input is checked and sanitized.

Query IN

Arguments of type []string,[]*string, []int,[]*int, or []interface{} are automatically expanded.

    q := bqb.New(
        "strs:(?) *strs:(?) ints:(?) *ints:(?) intfs:(?)",
        []string{"a", "b"}, []*string{}, []int{1, 2}, []*int{}, []interface{}{3, true},
    )
    sql, params, _ := q.ToSql()

Produces

SQL: strs:(?,?) *strs:(?) ints:(?,?) *ints:(?) intfs:(?,?)
PARAMS: [a b <nil> 1 2 <nil> 3 true]

Json Arguments

There are two helper structs, JsonMap and JsonList to make JSON conversion a little simpler.

sql, err := bqb.New(
    "INSERT INTO my_table (json_map, json_list) VALUES (?, ?)",
    bqb.JsonMap{"a": 1, "b": []string{"a","b","c"}},
    bqb.JsonList{"string",1,true,nil},
).ToRaw()

Produces

INSERT INTO my_table (json_map, json_list)
VALUES ('{"a": 1, "b": ["a","b","c"]}', '["string",1,true,null]')

Query Building

Since queries are built in an additive way by reference rather than value, it's easy to mutate a query without having to reassign the result.

Basic Example

sel := bqb.New("SELECT")

...

// later
sel.Space("id")

...

// even later
sel.Comma("age").Comma("email")

Produces

SELECT id,age,email

Advanced Example

The Optional(string) function returns a query that resolves to an empty string if no query parts have been added via methods on the query instance, and joins with a space to the next query part. For example q := Optional("SELECT") will resolve to an empty string unless parts have been added by one of the methods, e.g q.Space("* FROM my_table") would make q.ToSql() resolve to SELECT * FROM my_table.

sel := bqb.Optional("SELECT")

if getName {
    sel.Comma("name")
}

if getId {
    sel.Comma("id")
}

if !getName && !getId {
    sel.Comma("*")
}

from := bqb.New("FROM my_table")

where := bqb.Optional("WHERE")

if filterAdult {
    adultCond := bqb.New("name = ?", "adult")
    if ageCheck {
        adultCond.And("age > ?", 20)
    }
    where.And("(?)", adultCond)
}

if filterChild {
    where.Or("(name = ? AND age < ?)", "youth", 21)
}

q := bqb.New("? ? ?", sel, from, where).Space("LIMIT ?", 10)

Assuming all values are true, the query would look like:

SELECT name,id FROM my_table WHERE (name = 'adult' AND age > 20) OR (name = 'youth' AND age < 21) LIMIT 10

If getName and getId are false, the query would be

SELECT * FROM my_table WHERE (name = 'adult' AND age > 20) OR (name = 'youth' AND age < 21) LIMIT 10

If filterAdult is false, the query would be:

SELECT name,id FROM my_table WHERE (name = 'youth' AND age < 21) LIMIT 10

If all values are false, the query would be:

SELECT * FROM my_table LIMIT 10

Methods

Methods on the bqb Query struct follow the same pattern.

All query-modifying methods take a string (the query text) and variable length interface (the query args).

For example q.And("abc") will add AND abc to the query.

Take the following

q := bqb.Optional("WHERE")
q.Empty() // returns true
q.Len() // returns 0
q.Space("1 = 2") // query is now WHERE 1 = 2
q.Empty() // returns false
q.Len() // returns 1
q.And("b") // query is now WHERE 1 = 2 AND b
q.Or("c") // query is now WHERE 1 = 2 AND b OR c
q.Concat("d") // query is now WHERE 1 = 2 AND b OR cd
q.Comma("e") // query is now WHERE 1 = 2 AND b OR cd,e
q.Join("+", "f") // query is now WHERE 1 = 2 AND b OR cd,e+f

Valid args include string, int, floatN, *Query, []int, Embedder, Embedded, driver.Valuer or []string.

Frequently Asked Questions

Is there more documentation?

It's not really necessary because the API is so tiny and public methods are documented in code, see godoc. However, you can check out the tests and examples to see the variety of usages.

Why not just use a string builder?

Bqb provides several benefits over a string builder:

For example let's say we use the string builder way to build the following:

var params []interface{}
var whereParts []string
q := "SELECT * FROM my_table "
if filterAge {
    params = append(params, 21)
    whereParts = append(whereParts, fmt.Sprintf("age > $%d ", len(params)))
}

if filterBobs {
    params = append(params, "Bob%")
    whereParts = append(whereParts, fmt.Sprintf("name LIKE $%d ", len(params)))
}

if len(whereParts) > 0 {
    q += "WHERE " + strings.Join(whereParts, " AND ") + " "
}

if limit != nil {
    params = append(params, limit)
    q += fmt.Sprintf("LIMIT $%d", len(params))
}

// SELECT * FROM my_table WHERE age > $1 AND name LIKE $2 LIMIT $3

Some problems with that approach

1. You must perform a string join for the various parts of the where clause
2. You must remember to include a trailing or leading space for each clause
3. You have to keep track of parameter count (for Postgres anyway)
4. It's kind of ugly

The same logic can be achieved with bqb a bit more cleanly

q := bqb.New("SELECT * FROM my_table")
where := bqb.Optional("WHERE")
if filterAge {
    where.And("age > ?", 21)
}

if filterBobs {
    where.And("name LIKE ?", "Bob%")
}

q.Space("?", where)

if limit != nil {
    q.Space("LIMIT ?", limit)
}

// SELECT * FROM my_table WHERE age > $1 AND name LIKE $2 LIMIT $3

Both methods will allow you to remain close to the SQL, however the bqb approach will

1. Easily adapt to MySQL or Postgres without changing parameters
2. Hide the "WHERE" clause if both filterBobs and filterAge are false

Why not use a full query builder?

Take the following typical query example:

q := qb.Select("*").From("users").Where(qb.And{qb.Eq{"name": "Ed"}, qb.Gt{"age": 21}})

Vs the bqb way:

q := bqb.New("SELECT * FROM users WHERE name = ? AND age > ?", "ed", 21)

Okay, so a simple query it might make sense to use something like bqb, but what about grouped queries?

A query builder can handle this in multiple ways, a fairly common pattern might be:

q := qb.Select("name").From("users")

and := qb.And{}

if checkAge {
    and = append(and, qb.Gt{"age": 21})
}

if checkName {
    or := qb.Or{qb.Eq{"name":"trusted"}}
    if nullNameOkay {
        or = append(or, qb.Is{"name": nil})
    }
    and = append(and, or)
}

q = q.Where(and)

// SELECT name FROM users WHERE age > 21 AND (name = 'trusted' OR name IS NULL)

Contrast that with the bqb approach:

q := bqb.New("SELECT name FROM users")

where := bqb.Optional("WHERE")

if checkAge {
    where.And("age > ?", 21)
}

if checkName {
    or := bqb.New("name = ?", "trusted")
    if nullNameOkay {
        or.Or("name IS ?", nil)
    }
    where.And("(?)", or)
}

q.Space("?", where)

// SELECT name FROM users WHERE age > 21 AND (name = 'trusted' OR name IS NULL)

It seems to be a matter of taste as to which method appears cleaner.

Documentation

Index

• type Dialect
• type Embedded
• type Embedder
• type JsonList
• type JsonMap
• type Query
  • func New(text string, args ...any) *Query
  • func Optional(prefix string) *Query
  • func Q() *Query
  • func (q *Query) And(text string, args ...any) *Query
  • func (q *Query) Comma(text string, args ...any) *Query
  • func (q *Query) Concat(text string, args ...any) *Query
  • func (q *Query) Empty() bool
  • func (q *Query) Join(sep, text string, args ...any) *Query
  • func (q *Query) Len() int
  • func (q *Query) Or(text string, args ...any) *Query
  • func (q *Query) Print()
  • func (q *Query) Space(text string, args ...any) *Query
  • func (q *Query) ToMysql() (string, []any, error)
  • func (q *Query) ToPgsql() (string, []any, error)
  • func (q *Query) ToRaw() (string, error)
  • func (q *Query) ToSql() (string, []any, error)
• type QueryPart

Types

type Dialect

type Dialect string

Dialect holds the Query dialect

const (
	// PGSQL postgres dialect
	PGSQL Dialect = "postgres"
	// MYSQL MySQL dialect
	MYSQL Dialect = "mysql"
	// RAW dialect uses no parameter conversion
	RAW Dialect = "raw"
	// SQL generic dialect
	SQL Dialect = "sql"
)

type Embedded

type Embedded string

Embedded is a string type that is directly embedded into the query. Note: Like Embedder, this is not to be used for untrusted input.

type Embedder

type Embedder interface {
	RawValue() string
}

Embedder embeds a value directly into a query string. Note: Since this is embedded and not bound, attention must be paid to sanitizing this input.

type JsonList

type JsonList []interface{}

JsonList is a type that tells bqb to convert the parameter to a JSON list without requiring reflection.

type JsonMap

type JsonMap map[string]interface{}

JsonMap is a custom type which tells bqb to convert the parameter to a JSON object without requiring reflection.

type Query

type Query struct {
	Parts          []QueryPart
	OptionalPrefix string
}

Query contains all the QueryParts for the query and is the primary struct of the bqb package.

func New

func New(text string, args ...any) *Query

New returns an instance of Query with a single QueryPart.

func Optional

func Optional(prefix string) *Query

Optional returns a query object that has a conditional prefix which only resolves when at least one QueryPart has been added.

func Q

func Q() *Query

Q returns a new empty Query

func (*Query) And

func (q *Query) And(text string, args ...any) *Query

And joins the current QueryPart to the previous QueryPart with ' AND '.

func (*Query) Comma

func (q *Query) Comma(text string, args ...any) *Query

Comma joins the current QueryPart to the previous QueryPart with a comma.

func (*Query) Concat

func (q *Query) Concat(text string, args ...any) *Query

Concat concatenates the current QueryPart to the previous QueryPart with a zero space string.

func (*Query) Empty

func (q *Query) Empty() bool

Empty returns true if the Query is nil or has a length > 0.

func (*Query) Join

func (q *Query) Join(sep, text string, args ...any) *Query

Join joins the current QueryPart to the previous QueryPart with `sep`.

func (*Query) Len

func (q *Query) Len() int

Len returns the length of Query.Parts

func (*Query) Or

func (q *Query) Or(text string, args ...any) *Query

Or joins the current QueryPart to the previous QueryPart with ' OR '.

func (*Query) Print

func (q *Query) Print()

Print outputs the sql, parameters, and errors of a Query.

func (*Query) Space

func (q *Query) Space(text string, args ...any) *Query

Space joins the current QueryPart to the previous QueryPart with a space.

func (*Query) ToMysql

func (q *Query) ToMysql() (string, []any, error)

ToMysql returns the sql placeholders with SQL (?) format used by MySQL

func (*Query) ToPgsql

func (q *Query) ToPgsql() (string, []any, error)

ToPgsql returns the sql placeholders with dollarsign format used by postgres.

func (*Query) ToRaw

func (q *Query) ToRaw() (string, error)

ToRaw returns a string which the parameters have been resolved added as correctly as possible.

func (*Query) ToSql

func (q *Query) ToSql() (string, []any, error)

ToSql returns the placeholders with question (?) format used by most databases such as sqlite, mysql, and others.

type QueryPart

type QueryPart struct {
	Text   string
	Params []any
	Errs   []error
}

QueryPart holds a section of a Query.