builq

package module
v0.14.1 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: Mar 28, 2024 License: MIT Imports: 6 Imported by: 3

README

builq

build-img pkg-img reportcard-img coverage-img version-img

Easily build queries in Go.

Rationale

The simplest way to represent SQL query is a string. But the query arguments and their indexing ($1, $2 etc) require additional attention. This tiny library helps to build queries and handles parameter indexing.

Features

  • Simple and easy.
  • Safe and fast.
  • Tested.
  • Language-agnostic.
  • Dependency-free.

See docs or GUIDE.md for more details.

Install

Go version 1.19+

go get github.com/cristalhq/builq

Example

cols := builq.Columns{"foo, bar"}

q := builq.New()
q("SELECT %s FROM %s", cols, "users")
q("WHERE active IS TRUE")
q("AND user_id = %$ OR user = %$", 42, "root")

query, args, err := q.Build()
if err != nil {
	panic(err)
}

debug := q.DebugBuild()

fmt.Println("query:")
fmt.Println(query)
fmt.Println("\nargs:")
fmt.Println(args)
fmt.Println("\ndebug:")
fmt.Println(debug)

// query:
// SELECT foo, bar FROM users
// WHERE active IS TRUE
// AND user_id = $1 OR user = $2
//
// args:
// [42 root]
//
// debug:
// SELECT foo, bar FROM 'users'
// WHERE active IS TRUE
// AND user_id = 42 OR user = 'root'

See examples: example_test.go.

License

MIT License.

Documentation

Overview

Example (BatchMySQL) 
package main

import (
	"fmt"

	"github.com/cristalhq/builq"
)

func main() {
	params := [][]any{
		{42, true, "str"},
		{69, true, "noice"},
	}

	var b builq.Builder
	b.Addf("INSERT INTO table (id, flag, name)")
	b.Addf("VALUES %#?;", params)

	query, args, err := b.Build()
	if err != nil {
		panic(err)
	}

	fmt.Println("query:")
	fmt.Println(query)
	fmt.Println("args:")
	fmt.Println(args)

}

Output:

query:
INSERT INTO table (id, flag, name)
VALUES (?, ?, ?), (?, ?, ?);
args:
[42 true str 69 true noice]
Example (BatchPostgres) 
package main

import (
	"fmt"

	"github.com/cristalhq/builq"
)

func main() {
	params := [][]any{
		{42, true, "str"},
		{69, true, "noice"},
	}

	var b builq.Builder
	b.Addf("INSERT INTO table (id, flag, name)")
	b.Addf("VALUES %#$;", params)

	query, args, err := b.Build()
	if err != nil {
		panic(err)
	}

	fmt.Println("query:")
	fmt.Println(query)
	fmt.Println("args:")
	fmt.Println(args)

}

Output:

query:
INSERT INTO table (id, flag, name)
VALUES ($1, $2, $3), ($4, $5, $6);
args:
[42 true str 69 true noice]
Example (InsertReturn) 
package main

import (
	"fmt"

	"github.com/cristalhq/builq"
)

func main() {
	cols := builq.Columns{"id", "is_active", "name"}
	params := []any{true, "str"}

	var b builq.Builder
	b.Addf("INSERT INTO table (%s)", cols[1:]) // skip id column
	b.Addf("VALUES (%+$)", params)
	b.Addf("RETURNING %s;", cols)

	query, args, err := b.Build()
	if err != nil {
		panic(err)
	}

	fmt.Println("query:")
	fmt.Println(query)
	fmt.Println("args:")
	fmt.Println(args)

}

Output:

query:
INSERT INTO table (is_active, name)
VALUES ($1, $2)
RETURNING id, is_active, name;
args:
[true str]
Example (Query1) 
package main

import (
	"fmt"

	"github.com/cristalhq/builq"
)

func main() {
	cols := builq.Columns{"foo, bar"}

	var b builq.Builder
	b.Addf("SELECT %s FROM %s", cols, "users").
		Addf("WHERE active IS TRUE").
		Addf("AND user_id = %$ OR user = %$", 42, "root")

	query, args, err := b.Build()
	if err != nil {
		panic(err)
	}

	fmt.Println("query:")
	fmt.Println(query)
	fmt.Println("args:")
	fmt.Println(args)

}

Output:

query:
SELECT foo, bar FROM users
WHERE active IS TRUE
AND user_id = $1 OR user = $2
args:
[42 root]
Example (Query2) 
package main

import (
	"fmt"

	"github.com/cristalhq/builq"
)

func main() {
	var b builq.Builder
	b.Addf("SELECT %s FROM %s", "foo, bar", "users")
	b.Addf("WHERE")
	b.Addf("active = %$", true)
	b.Addf("AND user_id = %$", 42)
	b.Addf("ORDER BY created_at")
	b.Addf("LIMIT 100;")

	query, args, err := b.Build()
	if err != nil {
		panic(err)
	}

	fmt.Println("query:")
	fmt.Println(query)
	fmt.Println("args:")
	fmt.Println(args)

}

Output:

query:
SELECT foo, bar FROM users
WHERE
active = $1
AND user_id = $2
ORDER BY created_at
LIMIT 100;
args:
[true 42]
Example (Query3) 
package main

import (
	"fmt"

	"github.com/cristalhq/builq"
)

func main() {
	var b builq.Builder
	b.Addf("SELECT * FROM foo").
		Addf("WHERE active IS TRUE").
		Addf("AND user_id = %$", 42).
		Addf("LIMIT 100;")

	query, args, err := b.Build()
	if err != nil {
		panic(err)
	}

	fmt.Println("query:")
	fmt.Println(query)
	fmt.Println("args:")
	fmt.Println(args)

}

Output:

query:
SELECT * FROM foo
WHERE active IS TRUE
AND user_id = $1
LIMIT 100;
args:
[42]
Example (QueryWhere) 
package main

import (
	"fmt"
	"regexp"

	"github.com/cristalhq/builq"
)

func main() {
	filter := map[string]any{
		"name":     "the best",
		"category": []int{1, 2, 3},
		"pat":      regexp.MustCompile("pat+"),
		"prob":     0.42,
		"limit":    100.1,
	}

	q := builq.New()
	q("SELECT * FROM foo")
	q("WHERE active IS TRUE")

	if name, ok := filter["name"]; ok {
		q("AND name = %$", name)
	}
	if cat, ok := filter["category"]; ok {
		q("AND category IN (%+$)", cat)
	}
	if pat, ok := filter["pat"]; ok {
		q("AND page LIKE '%s'", pat)
	}
	if prob, ok := filter["prob"]; ok {
		q("AND prob < %s", prob)
	}
	if limit, ok := filter["limit"]; ok {
		q("LIMIT %d;", limit)
	}

	query, args, err := q.Build()
	if err != nil {
		panic(err)
	}

	fmt.Println("query:")
	fmt.Println(query)
	fmt.Println("args:")
	fmt.Println(args)

}

Output:

query:
SELECT * FROM foo
WHERE active IS TRUE
AND name = $1
AND category IN ($2, $3, $4)
AND page LIKE 'pat+'
AND prob < 0.42
LIMIT 100.1;
args:
[the best 1 2 3]
Example (QueryWithPercent) 
package main

import (
	"fmt"

	"github.com/cristalhq/builq"
)

func main() {
	var q builq.OnelineBuilder
	q.Addf("SELECT foo FROM bar WHERE shard = %? %% 10", 42)

	query, args, err := q.Build()
	if err != nil {
		panic(err)
	}

	fmt.Println("query:")
	fmt.Println(query)
	fmt.Println("args:")
	fmt.Println(args)

}

Output:

query:
SELECT foo FROM bar WHERE shard = ? % 10
args:
[42]
Example (SliceInBatch) 
package main

import (
	"fmt"

	"github.com/cristalhq/builq"
)

func main() {
	params := [][]any{
		{42, []any{1, 2, 3}},
		{69, []any{4, 5, 6}},
	}

	var b builq.Builder
	b.Addf("INSERT INTO table (id, flag, name)")
	b.Addf("VALUES %#?;", params)

	query, args, err := b.Build()
	if err != nil {
		panic(err)
	}

	fmt.Println("query:")
	fmt.Println(query)
	fmt.Println("args:")
	fmt.Println(args)

}

Output:

query:
INSERT INTO table (id, flag, name)
VALUES (?, ?), (?, ?);
args:
[42 [1 2 3] 69 [4 5 6]]
Example (SliceMySQL) 
package main

import (
	"fmt"

	"github.com/cristalhq/builq"
)

func main() {
	params := []any{42, true, "str"}

	var b builq.Builder
	b.Addf("INSERT INTO table (id, flag, name)")
	b.Addf("VALUES (%+?);", params)

	query, args, err := b.Build()
	if err != nil {
		panic(err)
	}

	fmt.Println("query:")
	fmt.Println(query)
	fmt.Println("args:")
	fmt.Println(args)

}

Output:

query:
INSERT INTO table (id, flag, name)
VALUES (?, ?, ?);
args:
[42 true str]
Example (SlicePostgres) 
package main

import (
	"fmt"

	"github.com/cristalhq/builq"
)

func main() {
	params := []any{42, true, "str"}

	var b builq.Builder
	b.Addf("INSERT INTO table (id, flag, name)")
	b.Addf("VALUES (%+$);", params)

	query, args, err := b.Build()
	if err != nil {
		panic(err)
	}

	fmt.Println("query:")
	fmt.Println(query)
	fmt.Println("args:")
	fmt.Println(args)

}

Output:

query:
INSERT INTO table (id, flag, name)
VALUES ($1, $2, $3);
args:
[42 true str]

Index

Examples

Constants

This section is empty.

Variables

This section is empty.

Functions

func Q added in v0.13.0 

func Q(format constString, args ...any) (query string, resArgs []any, err error)

Q is a handy helper. Works as [NewOnline] and [Build] in one call.

Example 
package main

import (
	"fmt"

	"github.com/cristalhq/builq"
)

func main() {
	cols := builq.Columns{"foo, bar"}

	query, args, err := builq.Q("SELECT %s FROM %s WHERE id = %$", cols, "users", 123)
	if err != nil {
		panic(err)
	}

	fmt.Println("query:")
	fmt.Println(query)
	fmt.Println("args:")
	fmt.Println(args)

}

Output:

query:
SELECT foo, bar FROM users WHERE id = $1
args:
[123]

Types

type BuildFn added in v0.11.0 

type BuildFn func(format constString, args ...any) *Builder

BuildFn represents Builder.Addf. Just for the easier BuilderFunc declaration.

func New added in v0.11.0 

func New() BuildFn

New returns a new query builder, same as Builder.

Example 
package main

import (
	"fmt"

	"github.com/cristalhq/builq"
)

func main() {
	cols := builq.Columns{"foo, bar"}

	q := builq.New()
	q("SELECT %s FROM %s", cols, "users")
	q("WHERE active IS TRUE")
	q("AND user_id = %$ OR user = %$", 42, "root")

	query, args, err := q.Build()
	if err != nil {
		panic(err)
	}

	debug := q.DebugBuild()

	fmt.Println("query:")
	fmt.Println(query)
	fmt.Println("\nargs:")
	fmt.Println(args)
	fmt.Println("\ndebug:")
	fmt.Println(debug)

}

Output:


query:
SELECT foo, bar FROM users
WHERE active IS TRUE
AND user_id = $1 OR user = $2

args:
[42 root]

debug:
SELECT foo, bar FROM 'users'
WHERE active IS TRUE
AND user_id = 42 OR user = 'root'

func NewOneline added in v0.11.0 

func NewOneline() BuildFn

New returns a new query builder, same as OnelineBuilder.

func (BuildFn) Build added in v0.11.0 

func (q BuildFn) Build() (query string, args []any, err error)

Build the query and arguments.

func (BuildFn) DebugBuild added in v0.11.0 

func (q BuildFn) DebugBuild() string

DebugBuild the query, good for debugging but not for REAL usage.

type Builder 

type Builder struct {
	// contains filtered or unexported fields
}

Builder for SQL queries.

Example 
package main

import (
	"fmt"

	"github.com/cristalhq/builq"
)

func main() {
	cols := builq.Columns{"foo", "bar"}

	var b builq.Builder
	b.Addf("SELECT %s FROM %s", cols, "table")
	b.Addf("WHERE id = %$", 123)

	// this WILL NOT complile
	// var orClause = "OR id = %$"
	// b.Addf(orClause, 42)

	// WILL compile
	const orClause2 = "OR id = %$"
	b.Addf(orClause2, 42)

	query, args, err := b.Build()
	if err != nil {
		panic(err)
	}

	fmt.Println("query:")
	fmt.Println(query)
	fmt.Println("args:")
	fmt.Println(args)

}

Output:

query:
SELECT foo, bar FROM table
WHERE id = $1
OR id = $2
args:
[123 42]

func (*Builder) Addf added in v0.5.0 

func (b *Builder) Addf(format constString, args ...any) *Builder

Addf formats according to a format specifier, writes to query and appends args. Format param must be a constant string.

func (*Builder) Build added in v0.3.0 

func (b *Builder) Build() (query string, args []any, err error)

Build the query and arguments.

func (*Builder) DebugBuild added in v0.10.0 

func (b *Builder) DebugBuild() (query string)

DebugBuild the query, good for debugging but not for REAL usage.

Example 
package main

import (
	"fmt"
	"time"

	"github.com/cristalhq/builq"
)

func main() {
	cols := builq.Columns{"foo", "bar"}

	ts := time.Date(2009, time.November, 10, 12, 13, 15, 16, time.UTC)
	d := 4 * time.Second

	var b builq.Builder
	b.Addf("SELECT %s FROM table", cols)
	b.Addf("WHERE id = %$", 123)
	b.Addf("OR id = %$ + %d", "42", 69.069)
	b.Addf("XOR created_at = %$", ts)
	b.Addf("MORE offset = %$", d)
	b.Addf("MAYBE IN arr = %$", []int{1, 2, 3})

	fmt.Println("debug:")
	fmt.Println(b.DebugBuild())

}

Output:

debug:
SELECT foo, bar FROM table
WHERE id = 123
OR id = '42' + 69.069
XOR created_at = '2009-11-10 12:13:15:999999'
MORE offset = '4s'
MAYBE IN arr = '[1 2 3]'

type Columns added in v0.5.0 

type Columns []string

Columns is a convenience wrapper for table columns.

Example 
package main

import (
	"fmt"

	"github.com/cristalhq/builq"
)

func main() {
	columns := builq.Columns{"id", "created_at", "value"}
	params := []any{42, "right now", "just testing"}

	var b builq.Builder
	b.Addf("INSERT INTO %s (%s)", "table", columns)
	b.Addf("VALUES (%?, %?, %?);", params...)

	query, args, err := b.Build()
	if err != nil {
		panic(err)
	}

	fmt.Println("query:")
	fmt.Println(query)
	fmt.Println("args:")
	fmt.Println(args)

}

Output:

query:
INSERT INTO table (id, created_at, value)
VALUES (?, ?, ?);
args:
[42 right now just testing]

func (Columns) Prefixed added in v0.6.1 

func (c Columns) Prefixed(p string) string

Prefixed acts the same as String but also prefixes each column with p.

func (Columns) String added in v0.5.0 

func (c Columns) String() string

String implements the fmt.Stringer interface.

type OnelineBuilder added in v0.9.0 

type OnelineBuilder struct {
	Builder
}

OnelineBuilder behaves like Builder but result is 1 line.

Example 
package main

import (
	"fmt"

	"github.com/cristalhq/builq"
)

func main() {
	cols := builq.Columns{"foo", "bar"}

	var b builq.OnelineBuilder
	b.Addf("SELECT %s FROM %s", cols, "table")
	b.Addf("WHERE id = %$", 123)

	query, _, err := b.Build()
	if err != nil {
		panic(err)
	}

	fmt.Print(query)

}

Output:

SELECT foo, bar FROM table WHERE id = $1

func (*OnelineBuilder) Addf added in v0.9.0 

func (b *OnelineBuilder) Addf(format constString, args ...any) *Builder

Addf formats according to a format specifier, writes to query and appends args. Format param must be a constant string.

Source Files

View all Source files

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.