README
¶
Squint - An interpolating SQL builder
Overview
The database/sql package is powerful but somewhat tedious to use. You must hand-write full SQL queries with bind placeholders and provide a matching ordered list of variables. It's familiar, but inconvenient and repetitive. Squint makes things easier by allowing SQL and bind variables to be intermixed in their natural order. It also interpolates the variables into the proper bind placeholders and values, including complex types like structs and maps. Squint is not an ORM, though. It's merely a pleasant query building assistant.
Builder
The Builder is the heart of Squint. It accepts a list of SQL fragments and variables and returns the resulting SQL statement and bind variables.
import "github.com/mwblythe/squint"
// a simple example
idList := []int{10, 20, 30}
b := squint.NewBuilder()
sql, binds := b.Build("select * from users where id in", idList, "and active = 1")
rows, err := db.Query(sql, binds...)
The sql and binds returned are ready to pass into the standard database/sql query functions, such as Exec, Query and QueryRow.
💡 See driver for an easier way.
Basic Variables
A string by default is treated as SQL. Other variables of the basic types will transform into a SQL bind placeholder (?) and an accompanying bind value. To have a string treated as a bind variable, you can pass a reference (&myString) or cast it as squint.Bind(myString).
// treated as binds
b.Build("select * from users where id =", 10) // good
b.Build("select * from users where is_active =", true) // good
// strings are sql without special handling
name := "Frank"
b.Build("select * from users where name =", name) // bad
b.Build("select * from users where name =", &name) // good
b.Build("select * from users where name =", squint.Bind(name)) // good
Arrays and Slices
By default, arrays and slices will be flattened and treated as if their contents had been passed inline. However, as part of a SQL IN clause, they will be expanded into appropriate placeholders and binds (even strings).
bits := []string{"select *", "from", "crew"}
b.Build(bits, "where id =", 10) // slice is flattened
names := []string{"jim", "spock", "uhura"}
b.Build("select * from crew where name in", names) // magic
Note that only arrays of basic types are supported for IN clause.
Structs and Maps
By default, these will be expanded in the style of a WHERE clause (column = ?) and joined with AND.
// easily build where clauses from structs
type Query struct {
Origin string
Completed bool
}
q := Query{Origin: "online", Completed: false}
b.Build("select * from orders where", q)
// or maps
type M map[string]interface{}
q := M{"origin": "online", "completed": false }
b.Build("select * from orders where", q)
There is special handling for INSERT statements:
// simple user struct
type User struct {
Id int
Name string
}
// fill structure, likely from user input
newUser := User{10, "Frank"}
// build our query
b.Build("insert into users", newUser)
// multi-row insert
users := []User{
{ 10, "Frank"},
{ 20, "Hank"},
}
b.Build("insert into users", users)
// also handles maps
type Item map[string]interface{}
b.Build("insert into users", Item{"id": 10, "name": "Frank"})
Also for UPDATE statements:
// use a structure
type Updates struct {
Role string
Department string
}
updates := Updates{"captain", "housewares"}
b.Build("UPDATE user SET", updates, "WHERE id =", id)
// or a map
type Updates map[string]interface{}
updates := Updates{"role": "captain", "department": "housewares"}
b.Build("UPDATE user SET", updates, "WHERE id =", id)
Pointers
Generally, pointers are dereferenced and their values used as if they were passed directly. If the pointer is nil, it will map to a NULL value. Pointers can be useful in a struct as discussed below under "Empty Values".
Conditions
When crafting a complex query, you sometimes need to build it up in bits while checking various conditions. Was an ID specified? Was extra information requested? While you can do this by carefully filling an array that you then pass to Build(), Squint has another option.
b.Build(
"SELECT u.* FROM users u",
b.If(boolCondition, "JOIN employees e ON u.id = e.id"),
"WHERE u.id IN", ids,
)
You can include any number of arguments in If(), and they will only be processed by Build() if the condition is true. This can also be called as squint.If()
Field Mapping
When mapping struct fields into database columns, by default the names are used verbatim. You can change the mapping by using the db struct field.
type User struct {
Id int // column is Id
FirstName string `db:"first_name"` // column is first_name
Username string `db:"-"` // skip this one
ManagerId int `db:"mgr_id,omitempty"` // skip if empty (0)
}
A custom mapping function may be a future enhancement
Options
The Builder uses functional options to control behavior:
| Option | Purpose | Default |
|---|---|---|
Tag(string) |
tag name for field mapping | "db" |
KeepEmpty() |
keep empty values in struct/map | On |
OmitEmpty() |
omit empty values in struct/map | Off |
NullEmpty() |
treat empty values as nulls in struct/map | Off |
WithEmptyFn(squint.EmptyFn) |
use a custom empty value handler | nil |
WithDefaultEmpty() |
use default empty value handler | On |
LogQuery(bool) |
log queries | false |
LogBinds(bool) |
log bind values | false |
Log(bool) |
shorthand to log both queries AND binds | false |
BindQuestion() |
use ? as bind placeholders (mysql, sqlite) |
On |
BindDollar() |
use $1, $2 style bind placeholders (postgres, sqlite) |
Off |
BindAt() |
use @p1, @p2 style placeholders (sqlserver) |
Off |
BindColon() |
use :b1, :b2 style placeholders (oracle) |
Off |
WithBindFn(squint.BindFn) |
use a custom bind placeholder function | Off |
These can all be set via NewBuilder():
b := squint.NewBuilder(
squint.NullEmpty(),
squint.Log(true),
)
They can also be set via Build() and will only be in effect for that query:
b.Build(
squint.LogBinds(false),
"update users set password =", &myPass,
"where id =", id,
)
Empty Values
When a struct or map is processed, empty (Go "zero") values need special consideration. You can control how they are treated on a builder level with the KeepEmpty(), OmitEmpty(), and NullEmpty() options. These are mutually exclusive, so only the last one used will win. Each of these options has a struct field equivalent for selective override:
type Person struct {
Name string
Age int `db:"omitempty"`
NumChildren int `db:"keepempty"`
ManagerID int `db:"nullempty"`
}
Since the empty value for pointers is nil, you can sometimes leverage this:
type Updates struct {
Department string
Balance *float64 `db:"omitempty"`
}
Now you can tell the difference between setting Balance to 0 or not setting it at all. With omitempty, an empty Balance would be skipped, but a pointer to a 0 would be kept.
NOTE: If OmitEmpty() is in effect for a multi-row inserts, KeepEmpty() will be used instead. This is because the column count must be consistent across rows. NullEmpty() and KeepEmpty() will be used as set.
Custom Empty Handler
Squint can also use your custom handler functions for empty values. When an empty value is encountered, your function will be called instead of the default logic. This means that any custom function will override the behavior of the KeepEmpty(), OmitEmpty(), and NullEmpty() options. Any field-level tags will still be respected, however.
A custom function looks like this:
func(in interface{}) (out interface{}, keep bool)
The parameter in is the empty value in question. Your function should return keep which determines if the empty value should be kept, and out which is the value to use if kept.
You register it via the squint.WithEmptyFn() option:
// use "N/A" for empty strings, skip all other types
func doEmpty(in interface{}) (out interface{}, keep bool) {
if s, ok := in.(string); ok {
return "N/A", true
}
return nil, false
}
// set globally in Builder
b := squint.NewBuilder(
squint.WithEmptyFn(doEmpty)
)
// override for single query
b.Build(
squint.WithEmptyFn(otherEmpty),
"insert into users", newUser,
)
To switch back to the default empty value handler, use the WithDefaultEmpty() option.
See Also
For a more seamless solution, see the squint driver package.
Documentation
¶
Index ¶
- type Bind
- type BindFn
- type Builder
- type Condition
- type EmptyFn
- type Option
- func BindAt() Option
- func BindColon() Option
- func BindDollar() Option
- func BindQuestion() Option
- func EmptyValues(b bool) Option
- func KeepEmpty() Option
- func Log(b bool) Option
- func LogBinds(b bool) Option
- func LogQuery(b bool) Option
- func NilValues(b bool) Option
- func NullEmpty() Option
- func OmitEmpty() Option
- func Tag(tag string) Option
- func WithBindFn(fn BindFn) Option
- func WithDefaultEmpty() Option
- func WithEmptyFn(fn EmptyFn) Option
- type Options
Constants ¶
This section is empty.
Variables ¶
This section is empty.
Functions ¶
This section is empty.
Types ¶
type Builder ¶
type Builder struct {
Options
}
Builder is the core of public squint interactions. It's responsible for processing inputs into SQL and binds
func NewBuilder ¶
NewBuilder returns a new Builder with the supplied options
func (*Builder) Build ¶
Build accepts a list of SQL fragments and Go variables and interpolates them into a query and a set of binds. These are appropriate to pass into a variety of execution methods of the sql (or sqlx) package.
sql, binds := b.Build("INSERT INTO users", &User)
func (*Builder) HasValues ¶
HasValues evaluates whether a struct or map has values that would be used according to the Builder's options.
If you have a situation where one might be considered empty, you can use this as a pre-check to avoid generating invalid SQL
type Condition ¶
type Condition struct {
// contains filtered or unexported fields
}
Condition will conditionally process a list of arguments
type EmptyFn ¶ added in v0.8.0
type EmptyFn func(in interface{}) (out interface{}, keep bool)
EmptyFn is an empty field handler
type Option ¶ added in v0.3.0
type Option func(*Options)
Option is a functional option
func BindAt ¶ added in v1.0.0
func BindAt() Option
BindAt uses @p1, @p2 style placeholders (sqlserver)
func BindColon ¶ added in v1.0.0
func BindColon() Option
BindColon uses :b1, :b2 style placeholders (oracle)
func BindDollar ¶ added in v1.0.0
func BindDollar() Option
BindDollar uses $1, $2 style placeholders (postgres, sqlite)
func BindQuestion ¶ added in v1.0.0
func BindQuestion() Option
BindQuestion uses ? as placeholder (MySQL, sqlite)
func EmptyValues ¶ added in v0.3.0
EmptyValues (DEPRECATED): keep empty string struct/map field values
func WithBindFn ¶ added in v1.0.0
WithBindFn uses a custom bind placeholder function of the form:
func(seq int) string
where seq is a 1-based bind sequence
func WithDefaultEmpty ¶ added in v0.8.0
func WithDefaultEmpty() Option
WithDefaultEmpty : use default empty field handler
func WithEmptyFn ¶ added in v0.8.0
WithEmptyFn : use custom empty field handler:
func(in interface{}) (out interface{}, keep bool)
in - incoming value out - outgoing value to use in SQL keep - keep the value or skip it?
Source Files
Directories