BQB (Basic Query Builder)

The Embedder interface allows you to directly replace a ? with a raw string returned by the RawValue method. This is useful for injecting identifiers like table names or column names that cannot be parameterized, or for changing sort directions (e.g., ASC/DESC).

Warning: Since this uses raw values, ensure any input used to implement Embedder is strictly sanitized to prevent SQL injection.

The bqb.Optional(string) function returns a query that resolves to an empty string if no parts have been added to it. This is ideal for building WHERE or ORDER BY clauses that should only appear if certain filters are applied.

where := bqb.Optional("WHERE")

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

// If filterAdult is false, 'where' resolves to ""
// If filterAdult is true, 'where' resolves to "WHERE (name = ? AND age > ?)"

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

Arguments of type []string, []*string, []int, []*int, and []any / []interface{} are automatically expanded into multiple placeholders. For example, passing a slice to a single ? will result in (?, ?, ...) in the generated SQL.

    q := bqb.New(
        "strs:(?) *strs:(?) ints:(?) *ints:(?) intfs:(?)",
        []string{"a", "b"}, []*string{}, []int{1, 2}, []*int{}, []any{3, true},
    )
    sql, params, _ := q.ToSql()
    // SQL: strs:(?,?) *strs:(?) ints:(?,?) *ints:(?) intfs:(?,?)
    // PARAMS: [a b <nil> 1 2 <nil> 3 true]

When using Postgres, the ? character is often a valid operator (e.g., for JSONB). To prevent BQB from treating a ? as a parameter placeholder, use the double question mark ?? in your query string. BQB will convert ?? to a single ? in the output SQL.

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

To create a basic query, use bqb.New with a SQL string containing ? placeholders and provide the corresponding arguments. Use .ToSql() to generate the parameterized SQL string and the slice of parameters.

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

Queries in BQB are built by reference. You can start with a base query and incrementally add parts using methods like .Space(), .Comma(), .And(), .Or(), etc. This allows for easy conditional query construction.

sel := bqb.New("SELECT")
sel.Space("id")
sel.Comma("age").Comma("email")
// Produces: SELECT id,age,email

The ToRaw() method returns a string where the values are directly embedded into the SQL instead of being parameterized.

Warning: Do not use ToRaw() with user-provided input as it is susceptible to SQL injection.

q := bqb.New("a = ?, b = ?, c = ?", "my a", 1234, nil)
sql, err := q.ToRaw()
// Produces: a = 'my a', b = 1234, c = NULL

BQB provides JsonMap and JsonList helper structs to simplify converting Go maps and slices into JSON strings for SQL queries.

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()

To generate Postgres-compatible queries using $ placeholders instead of ?, use the ToPgsql() method. This is useful for switching between database dialects without changing the query definition.

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

By default, BQB expands slices (e.g., `[]string{

strns := []string{"a", "b"}
q := bqb.New("Folded: ? - Default: ?", bqb.ToFolded(strns), strns)
// sql = Folded: ? - Default: ?,?
// params = { []string{"a", "b"}, "a", "b" }

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

• Space(text, ...args): Adds text preceded by a space.
• And(text, ...args): Adds AND text.
• Or(text, ...args): Adds OR text.
• Comma(text, ...args): Adds , text.
• Concat(text, ...args): Adds text (concatenates directly).
• Join(separator, text, ...args): Adds separator + text.
• Empty(): Returns true if the query has no parts.
• Len(): Returns the number of parts added.

Valid arguments for query methods include:

• string
• int
• floatN
• *Query
• []int
• Embedder
• Embedded
• driver.Valuer
• []string