bqb

Basic Query Builder

Hard fork of bqb with a lot of breaking changes.

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

Basic

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

Produces:

SELECT * FROM places WHERE id = ?

PARAMS: [1234]

Escaping ?

Use the double question mark ?? value to escape the ? in Postgres queries. For example:

q := bqb.New("SELECT * FROM places WHERE json_obj_column ?? 'key'")
sql, params, err := q.ToPgSQL()

This query uses the ? operator for jsonb types in Postgres to test an object for the presence of a key. It should not be interpreted as an escaped value by bqb.

SELECT * FROM places WHERE json_obj_column ? 'key'

PARAMS: []

Dialects

bqb supports following dialects to build queries with:

• SQL — uses '?' placeholders, expands arrays (default)
• SQLite — uses '?NNN' placeholders, expands arrays
• PostgreSQL — uses '$NNN' placeholders, doesn't expand arrays

It is possible to define and use custom dialect:

var Custom = &Dialect{
	Placeholder: func(w *bytes.Buffer, i int) {
		w.WriteByte('@')
		w.WriteString(strconv.Itoa(i))
	},
	ExpandArrays: true,
}
q := bqb.New("SELECT * FROM pets WHERE name = ?", "garfield")
sel, params, err := q.Build(Custom)

Produces:

SELECT * FROM pets WHERE name = @1

PARAMS: ["garfield"]

Postgres - ToPgSQL()

The ToPgSQL method wraps Build(bqb.PostgreSQL):

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

Produces:

DELETE FROM users WHERE id = $1 OR name = ANY($2) LIMIT $3

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

SQLite - ToSQLite()

The ToSQLite method wraps Build(bqb.SQLite)

q := bqb.New("INSERT INTO animals (id, name) VALUES").
	Space("(?, ?)", 1, "dolphin").
	Comma("(?, ?)", 2, "duck").
	Comma("(?, ?)", 3, "elephant")
sql, params, err := q.ToSQLite()

Produces:

INSERT INTO animals (id, name) VALUES (?1, ?2), (?3, ?4), (?5, ?6)

PARAMS: [1, "dolphin", 2, "duck", 3, "elephant"]

Types

driver.Valuer

The driver.Valuer interface is supported for types that are able to convert themselves to a sql driver value.

Embedded

It is possible to directly replace ? with a string by converting it to Embedded type:

q := bqb.New("SELECT * FROM ?", bqb.Embedded("table"))

Produces:

SELECT * FROM table

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.

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

Query IN

Array and slice arguments are automatically expanded for Dialects with ExpandArrays: true, except for byte arrays and byte slices.

q := bqb.New("SELECT * FROM animals WHERE name IN (?)", []string{"dolphin", "duck", "elephant"})
sql, params, _ := q.ToSQL()

Produces:

SELECT * FROM animals WHERE name IN (?, ?, ?)

PARAMS: ["dolphin", "duck", "elephant"]

You can force bqb to expand/not expand array and slice arguments regardless of the ExpandArrays settings by using Unfolded/Folded types.

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},
).ToSQL()

Produces:

INSERT INTO my_table (json_map, json_list) VALUES (?, ?)

PARAMS: [`{"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 Q() function returns an empty Query, that resolves to an empty string if nothing have been added via methods on the query builder. For example q := Q() will resolve to an empty string unless something have been added by one of the methods, but q.Comma("name") would make q.ToSQL() resolve to name.

The Optional(string) functions acts the same way as the Q() function, except that it prepends a prefix and a space to the text if something have been added by one of the methods. For example q := Optional("SELECT") will resolve to an empty string if nothing have been added to the query, but q.Space("* FROM my_table") would make q.ToSQL() resolve to SELECT * FROM my_table.

targets := bqb.Q()
if getName {
	targets.Comma("name")
}
if getId {
	targets.Comma("id")
}
if !getName && !getId {
	targets.Comma("*")
}

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("SELECT ? FROM my_table ?", targets, 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