Skip to content

Latest commit

 

History

History
428 lines (296 loc) · 10.4 KB

README.md

File metadata and controls

428 lines (296 loc) · 10.4 KB
Raw

Basic Query Builder

Tests Status GoDoc code coverage Go Report Card Mentioned in Awesome Go

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]

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: []

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.