BQB is a lightweight and easy to use query builder that works with sqlite, mysql, mariadb, postgres, and others.

Overview

Basic Query Builder

Tests Status GoDoc code coverage Go Report Card

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

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. 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.

That is the method name indicates how to join the new part to the existing query. And all 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.Space("1 = 2") // query is now WHERE 1 = 2
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, 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. Most of the documentation will be around how to use SQL. However, you can check out the tests 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.

You might also like...
Dumpling is a fast, easy-to-use tool written by Go for dumping data from the database(MySQL, TiDB...) to local/cloud(S3, GCP...) in multifarious formats(SQL, CSV...).

🥟 Dumpling Dumpling is a tool and a Go library for creating SQL dump from a MySQL-compatible database. It is intended to replace mysqldump and mydump

A MySQL-compatible relational database with a storage agnostic query engine. Implemented in pure Go.

go-mysql-server go-mysql-server is a SQL engine which parses standard SQL (based on MySQL syntax) and executes queries on data sources of your choice.

mysql to mysql 轻量级多线程的库表数据同步

goMysqlSync golang mysql to mysql 轻量级多线程库表级数据同步 测试运行 设置当前binlog位置并且开始运行 go run main.go -position mysql-bin.000001 1 1619431429 查询当前binlog位置,参数n为秒数,查询结

Mogo: a lightweight browser-based logs analytics and logs search platform for some datasource(ClickHouse, MySQL, etc.)
Mogo: a lightweight browser-based logs analytics and logs search platform for some datasource(ClickHouse, MySQL, etc.)

mogo Mogo is a lightweight browser-based logs analytics and logs search platform

RecordLite: a library (and executable) that declaratively maintains SQLite tables and views of semi-structured data

RecordLite RecordLite is a library (and executable) that declaratively maintains

Use SQL to query host, DNS and exploit information using Shodan. Open source CLI. No DB required.

Shodan Plugin for Steampipe Query Shodan with SQL Use SQL to query host, DNS and exploit information using Shodan. For example: select * from shod

Use SQL to instantly query file, domain, URL and IP scanning results from VirusTotal.
Use SQL to instantly query file, domain, URL and IP scanning results from VirusTotal.

VirusTotal Plugin for Steampipe Use SQL to query file, domain, URL and IP scanning results from VirusTotal. Get started → Documentation: Table definit

Use SQL to instantly query users, groups, applications and more from Okta. Open source CLI. No DB required.
Use SQL to instantly query users, groups, applications and more from Okta. Open source CLI. No DB required.

Okta Plugin for Steampipe Use SQL to query infrastructure including users, groups, applications and more from Okta. Get started → Documentation: Table

Use SQL to instantly query instances, networks, databases, and more from Scaleway. Open source CLI. No DB required.
Use SQL to instantly query instances, networks, databases, and more from Scaleway. Open source CLI. No DB required.

Scaleway Plugin for Steampipe Use SQL to query infrastructure servers, networks, databases and more from your Scaleway project. Get started → Document

Releases(v1.2.4)
Owner
Aaron M
Aaron M
WAL-G is an archival restoration tool for PostgreSQL, MySQL/MariaDB, and MS SQL Server (beta for MongoDB and Redis).

WAL-G is an archival restoration tool for PostgreSQL, MySQL/MariaDB, and MS SQL Server (beta for MongoDB and Redis).

null 2.4k Jan 1, 2023
Support MySQL or MariaDB for gopsql/psql and gopsql/db

mysql Support MySQL or MariaDB for github.com/gopsql/psql. You can make MySQL SELECT, INSERT, UPDATE, DELETE statements with this package. NOTE: Pleas

null 0 Dec 9, 2021
Package sqlite is a CGo-free port of SQLite.

sqlite Package sqlite is a CGo-free port of SQLite. SQLite is an in-process implementation of a self-contained, serverless, zero-configuration, transa

Joe 3 Nov 30, 2021
Go-postgres - go-postgres library provide NoSQL functionality which can execute queries with pool of connections

GO Postgres go-postgres library provide NoSQL functionality which can execute queries with pool of connections. What is this repository for? Establish

Damindu Lakmal 0 Dec 31, 2021
Go sqlite3 http vfs: query sqlite databases over http with range headers

sqlite3vfshttp: a Go sqlite VFS for querying databases over http(s) sqlite3vfshttp is a sqlite3 VFS for querying remote databases over http(s). This a

Peter Sanford 114 Dec 27, 2022
Sqlair - SQLite Query Layer With Golang

sqlair SQLite Query Layer Creates an abstract over the go sql package to provide

Simon Richardson 1 Feb 18, 2022
Experimental implementation of a SQLite backend for go-mysql-server

go-mysql-sqlite-server This is an experimental implementation of a SQLite backend for go-mysql-server from DoltHub. The go-mysql-server is a "frontend

MergeStat 11 Dec 23, 2022
Simple key-value store on top of SQLite or MySQL

KV Work in progress, not ready for prime time. A simple key/value store on top of SQLite or MySQL (Go port of GitHub's KV). Aims to be 100% compatible

Sergio Rubio 4 Dec 3, 2022
A Go SQL query builder and struct mapper.

godb - a Go query builder and struct mapper godb is a simple Go query builder and struct mapper, not a full-featured ORM. godb does not manage relatio

Samuel GAY 718 Dec 6, 2022