SQL builder for Go
Package sqlbuilder
provides a set of flexible and powerful SQL string builders. The only goal of this package is to build SQL string with arguments which can be used in DB#Query
or DB#Exec
defined in package database/sql
.
Install
Use go get
to install this package.
go get github.com/huandu/go-sqlbuilder
Usage
Basic usage
We can build a SQL really quick with this package.
sql := sqlbuilder.Select("id", "name").From("demo.user").
Where("status = 1").Limit(10).
String()
fmt.Println(sql)
In the most cases, we need to escape all input from user. In this case, create a builder before starting.
sb := sqlbuilder.NewSelectBuilder()
sb.Select("id", "name", sb.As("COUNT(*)", "c"))
sb.From("user")
sb.Where(sb.In("status", 1, 2, 5))
sql, args := sb.Build()
fmt.Println(sql)
fmt.Println(args)
Pre-defined SQL builders
This package includes following pre-defined builders so far. API document and examples can be found in the godoc
online document.
There is a special method SQL(sql string)
implemented by all statement builders. We can use this method to insert any arbitrary SQL fragment into a builder when building a SQL. It's quite useful to build SQL containing non-standard syntax supported by a OLTP or OLAP system.
sql := sqlbuilder.CreateTable("users").
SQL("PARTITION BY (year)").
SQL("AS").
SQL(
sqlbuilder.Select("columns[0] id", "columns[1] name", "columns[2] year").
From("`all-users.csv`").
Limit(100).
String(),
).
String()
fmt.Println(sql)
Following are some utility methods to deal with special cases.
- Flatten can convert an array-like variable to a flat slice of
[]interface{}
recursively. For instance, calling Flatten([]interface{"foo", []int{2, 3}})
returns []interface{}{"foo", 2, 3}
. This method can work with builder methods like In
/NotIn
/Values
/etc to convert a typed array to []interface{}
or merge inputs. - List works similar to
Flatten
except that its return value is dedecated for builder args. For instance, calling Buildf("my_func(%v)", List([]int{1, 2, 3})).Build()
returns SQL my_func(?, ?, ?)
and args []interface{}{1, 2, 3}
. - Raw marks a string as "raw string" in args. For instance, calling
Buildf("SELECT %v", Raw("NOW()")).Build()
returns SQL SELECT NOW()
.
To learn how to use builders, check out examples on GoDoc.
Build WHERE
clause
WHERE
clause is the most important part of a SQL. We can use Where
method to add one or more conditions to a builder.
To make building WHERE
clause easier, there is an utility type called Cond
to build condition. All builders which support WHERE
clause have an anonymous Cond
field so that we can call methods implemented by Cond
on these builders.
sb := sqlbuilder.Select("id").From("user")
sb.Where(
sb.In("status", 1, 2, 5),
sb.Or(
sb.Equal("name", "foo"),
sb.Like("email", "foo@%"),
),
)
sql, args := sb.Build()
fmt.Println(sql)
fmt.Println(args)
There are many methods for building conditions.
- Cond.Equal/Cond.E:
field = value
. - Cond.NotEqual/Cond.NE:
field <> value
. - Cond.GreaterThan/Cond.G:
field > value
. - Cond.GreaterEqualThan/Cond.GE:
field >= value
. - Cond.LessThan/Cond.L:
field < value
. - Cond.LessEqualThan/Cond.LE:
field <= value
. - Cond.In:
field IN (value1, value2, ...)
. - Cond.NotIn:
field NOT IN (value1, value2, ...)
. - Cond.Like:
field LIKE value
. - Cond.NotLike:
field NOT LIKE value
. - Cond.Between:
field BETWEEN lower AND upper
. - Cond.NotBetween:
field NOT BETWEEN lower AND upper
. - Cond.IsNull:
field IS NULL
. - Cond.IsNotNull:
field IS NOT NULL
. - Cond.Exists:
EXISTS (subquery)
. - Cond.NotExists:
NOT EXISTS (subquery)
. - Cond.Any:
field op ANY (value1, value2, ...)
. - Cond.All:
field op ALL (value1, value2, ...)
. - Cond.Some:
field op SOME (value1, value2, ...)
. - Cond.Var: A placeholder for any value.
There are also some methods to combine conditions.
- Cond.And: Combine conditions with
AND
operator. - Cond.Or: Combine conditions with
OR
operator.
Build SQL for different systems
SQL syntax and parameter marks vary in different systems. In this package, we introduce a concept called "flavor" to smooth out these difference.
Right now, MySQL
, PostgreSQL
, SQLServer
, SQLite
, CQL
, ClickHouse
, Presto
and Oracle
are defined in flavor list. Feel free to open issue or send pull request if anyone asks for a new flavor.
By default, all builders uses DefaultFlavor
to build SQL. The default value is MySQL
.
There is a BuildWithFlavor
method in Builder
interface. We can use it to build a SQL with provided flavor.
We can wrap any Builder
with a default flavor through WithFlavor
.
To be more verbose, we can use PostgreSQL.NewSelectBuilder()
to create a SelectBuilder
with the PostgreSQL
flavor. All builders can be created in this way.
Using Struct
as a light weight ORM
Struct
stores type information and struct fields of a struct. It's a factory of builders. We can use Struct
methods to create initialized SELECT/INSERT/UPDATE/DELETE builders to work with the struct. It can help us to save time and avoid human-error on writing column names.
We can define a struct type and use field tags to let Struct
know how to create right builders for us.
type ATable struct {
Field1 string
Field2 int `db:"field2"`
Field3 int64 `db:"field3" fieldtag:"foo,bar"`
Field4 int64 `db:"field4" fieldtag:"foo"`
Field5 string `db:"field5" fieldas:"f5_alias"`
Ignored int32 `db:"-"`
unexported int
Quoted string `db:"quoted" fieldopt:"withquote"`
Empty uint `db:"empty" fieldopt:"omitempty"`
Tagged string `db:"tagged" fieldopt:"omitempty(tag1,tag3)" fieldtag:"tag1,tag2,tag3"`
}
Read examples for Struct
to learn details of how to use it.
What's more, we can use Struct
as a kind of zero-config ORM. While most ORM implementations requires several prerequisite configs to work with database connections, Struct
doesn't require any config and work well with any SQL driver which works with database/sql
. Struct
doesn't call any database/sql
API; It just creates right SQL with arguments for DB#Query
/DB#Exec
or a slice of address of struct fields for Rows#Scan
/Row#Scan
.
Here is a sample to use Struct
as ORM. It should be quite straight forward for developers who are familiar with database/sql
APIs.
type User struct {
ID int64 `db:"id" fieldtag:"pk"`
Name string `db:"name"`
Status int `db:"status"`
}
var userStruct = NewStruct(new(User))
func ExampleStruct() {
sb := userStruct.SelectFrom("user")
sb.Where(sb.Equal("id", 1234))
sql, args := sb.Build()
rows, _ := db.Query(sql, args...)
defer rows.Close()
var user User
rows.Scan(userStruct.Addr(&user)...)
fmt.Println(sql)
fmt.Println(args)
fmt.Printf("%#v", user)
}
In many production environments, table column names are usually snake_case words, e.g. user_id
, while we have to use CamelCase in struct types to make struct fields public and golint
happy. It's a bit redundant to use the db
tag in every struct field. If there is a certain rule to map field names to table column names, We can use field mapper function to make code simpler.
The DefaultFieldMapper
is a global field mapper function to convert field name to new style. By default, it sets to nil
and does nothing. If we know that most table column names are snake_case words, we can set DefaultFieldMapper
to sqlbuilder.SnakeCaseMapper
. If we have some special cases, we can set custom mapper to a Struct
by calling WithFieldMapper
.
Following are special notes regarding to field mapper.
- Field tag has precedence over field mapper function - thus, mapper is ignored if the
db
tag is set; - Field mapper is called only once on a Struct when the Struct is used to create builder for the first time.
See field mapper function sample as a demo.
Nested SQL
It's quite straight forward to create a nested SQL: use a builder as an argument to nest it.
Here is a sample.
sb := sqlbuilder.NewSelectBuilder()
fromSb := sqlbuilder.NewSelectBuilder()
statusSb := sqlbuilder.NewSelectBuilder()
sb.Select("id")
sb.From(sb.BuilderAs(fromSb, "user")))
sb.Where(sb.In("status", statusSb))
fromSb.Select("id").From("user").Where(fromSb.GreaterThan("level", 4))
statusSb.Select("status").From("config").Where(statusSb.Equal("state", 1))
sql, args := sb.Build()
fmt.Println(sql)
fmt.Println(args)
Use sql.Named
in a builder
The function sql.Named
defined in database/sql
can create a named argument in SQL. It's necessary if we want to reuse an argument several times in one SQL. It's still quite simple to use named arguments in a builder: use it as an argument.
Here is a sample.
now := time.Now().Unix()
start := sql.Named("start", now-86400)
end := sql.Named("end", now+86400)
sb := sqlbuilder.NewSelectBuilder()
sb.Select("name")
sb.From("user")
sb.Where(
sb.Between("created_at", start, end),
sb.GE("modified_at", start),
)
sql, args := sb.Build()
fmt.Println(sql)
fmt.Println(args)
Argument modifiers
There are several modifiers for arguments.
List(arg)
represents a list of arguments. If arg
is a slice or array, e.g. a slice with 3 ints, it will be compiled to ?, ?, ?
and flattened in the final arguments as 3 ints. It's a tool for convenience. We can use it in the IN
expression or VALUES
of INSERT INTO
.TupleNames(names)
and Tuple(values)
represent the tuple syntax in SQL. See Tuple for usage.Named(name, arg)
represents a named argument. It only works with Build
or BuildNamed
to define a named placeholder using syntax ${name}
.Raw(expr)
marks an expr
as a plain string in SQL rather than an argument. When we build a builder, the value of raw expressions are copied in SQL string directly without leaving any ?
in SQL.
Freestyle builder
A builder is only a way to record arguments. If we want to build a long SQL with lots of special syntax (e.g. special comments for a database proxy), simply use Buildf
to format a SQL string using a fmt.Sprintf
-like syntax.
sb := sqlbuilder.NewSelectBuilder()
sb.Select("id").From("user")
explain := sqlbuilder.Buildf("EXPLAIN %v LEFT JOIN SELECT * FROM banned WHERE state IN (%v, %v)", sb, 1, 2)
sql, args := explain.Build()
fmt.Println(sql)
fmt.Println(args)
Using special syntax to build SQL
Package sqlbuilder
defines special syntax to represent an uncompiled SQL internally. If we want to take advantage of the syntax to build customized tools, we can use Build
to compile it with arguments.
The format string uses special syntax to represent arguments.
$?
refers successive arguments passed in the call. It works similar as %v
in fmt.Sprintf
.$0
$1
... $n
refers nth-argument passed in the call. Next $?
will use arguments n+1.${name}
refers a named argument created by Named
with name
.$$
is a "$"
string.
sb := sqlbuilder.NewSelectBuilder()
sb.Select("id").From("user").Where(sb.In("status", 1, 2))
b := sqlbuilder.Build("EXPLAIN $? LEFT JOIN SELECT * FROM $? WHERE created_at > $? AND state IN (${states}) AND modified_at BETWEEN $2 AND $?",
sb, sqlbuilder.Raw("banned"), 1514458225, 1514544625, sqlbuilder.Named("states", sqlbuilder.List([]int{3, 4, 5})))
sql, args := b.Build()
fmt.Println(sql)
fmt.Println(args)
If we just want to use ${name}
syntax to refer named arguments, use BuildNamed
instead. It disables all special syntax but ${name}
and $$
.
Interpolate args
in the sql
Some SQL drivers doesn't actually implement StmtExecContext#ExecContext
. They will fail when len(args) > 0
. The only solution is to interpolate args
in the sql
, and execute the interpolated query with the driver.
Security warning: I try my best to escape special characters in interpolate methods, but it's still less secure than Stmt
implemented by SQL servers.
This feature is inspired by interpolation feature in package github.com/go-sql-driver/mysql
.
Here is a sample for MySQL.
sb := MySQL.NewSelectBuilder()
sb.Select("name").From("user").Where(
sb.NE("id", 1234),
sb.E("name", "Charmy Liu"),
sb.Like("desc", "%mother's day%"),
)
sql, args := sb.Build()
query, err := MySQL.Interpolate(sql, args)
fmt.Println(query)
fmt.Println(err)
Here is a sample for PostgreSQL. Note that the dollar quote is supported.
query, err := PostgreSQL.Interpolate(`
CREATE FUNCTION dup(in int, out f1 int, out f2 text) AS $$
SELECT $1, CAST($1 AS text) || ' is text'
$$
LANGUAGE SQL;
SELECT * FROM dup($1);`, []interface{}{42})
fmt.Println(query)
fmt.Println(err)
FAQ
What's the difference between this package and squirrel
Package squirrel is another SQL builder package with outstanding design and high code quality.
Comparing with squirrel
, go-sqlbuilder
is much more extensible with more built-in features.
Here are details.
- API design: The core of
go-sqlbuilder
is Builder
and Args
. Nearly all features are built on top of them. If we want to extend this package, e.g. support EXPLAIN
, we can use Build("EXPLAIN $?", builder)
to add EXPLAIN
in front of any SQL. - ORM: Package
squirrel
doesn't provide ORM directly. There is another package structable, which is based on squirrel
, designed for ORM. - No design pitfalls: There is no design pitfalls like
squirrel.Eq{"mynumber": []uint8{1,2,3}}
. I'm proud of it. :)
License
This package is licensed under MIT license. See LICENSE for details.