README
¶
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 -u github.com/huandu/go-sqlbuilder
Usage
Basic usage
Here is a sample to demonstrate how to build a SELECT query.
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)
// Output:
// SELECT id, name, COUNT(*) AS c FROM user WHERE status IN (?, ?, ?)
// [1 2 5]
Following builders are implemented right now. API document and examples are provided in the godoc
document.
- Struct: Builder factory for a struct.
- SelectBuilder: Builder for SELECT.
- InsertBuilder: Builder for INSERT.
- UpdateBuilder: Builder for UPDATE.
- DeleteBuilder: Builder for DELETE.
- Buildf: Freestyle builder using
fmt.Sprintf
-like syntax. - Build: Advanced freestyle builder using special syntax defined in Args#Compile.
- BuildNamed: Advanced freestyle builder using
${key}
to refer the value of a map by key.
Build SQL for MySQL or PostgreSQL
Parameter markers are different in MySQL and PostgreSQL. This package provides some methods to set the type of markers (we call it "flavor") in all builders.
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.
Right now, there are only two flavors, MySQL
and PostgreSQL
. Open new issue to me to ask for a new flavor if you find it necessary.
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 // If a field doesn't has a tag, use "Field1" as column name in SQL.
Field2 int `db:"field2"` // Use "db" in field tag to set column name used in SQL.
Field3 int64 `db:"field3" fieldtag:"foo,bar"` // Set fieldtag to a field. We can use methods like `Struct#SelectForTag` to use it.
Field4 int64 `db:"field4" fieldtag:"foo"` // If we use `s.SelectForTag(table, "foo")`, columnes of SELECT are field3 and field4.
Ignored int32 `db:"-"` // If we set field name as "-", Struct will ignore it.
unexported int // Unexported field is not visible to Struct.
Quoted string `db:"quoted" fieldopt:"withquote"` // Add quote to the field using back quote or double quote. See `Flavor#Quote`.
}
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"`
Name string `db:"name"`
Status int `db:"status"`
}
// A global variable to create SQL builders.
// All methods of userStruct are thread-safe.
var userStruct = NewStruct(new(User))
func ExampleStruct() {
// Prepare SELECT query.
// SELECT id, name, status FROM user WHERE id = 1234
sb := userStruct.SelectFrom("user")
sb.Where(sb.Equal("id", 1234))
// Execute the query.
sql, args := sb.Build()
rows, _ := db.Query(sql, args...)
defer rows.Close()
// Scan row data and set value to user.
// Suppose we get following data.
//
// | id | name | status |
// |------|--------|--------|
// | 1234 | huandu | 1 |
var user User
rows.Scan(userStruct.Addr(&user)...)
fmt.Println(sql)
fmt.Println(args)
fmt.Printf("%#v", user)
// Output:
// SELECT id, name, status FROM user WHERE id = ?
// [1234]
// sqlbuilder.User{ID:1234, Name:"huandu", Status:1}
}
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)
// Output:
// SELECT id FROM (SELECT id FROM user WHERE level > ?) AS user WHERE status IN (SELECT status FROM config WHERE state = ?)
// [4 1]
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)
// Output:
// SELECT name FROM user WHERE created_at BETWEEN @start AND @end AND modified_at >= @start
// [{{} start 1514458225} {{} end 1514544625}]
Argument modifiers
There are several modifiers for arguments.
List(arg)
represents a list of arguments. Ifarg
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 theIN
expression orVALUES
ofINSERT INTO
.Named(name, arg)
represents a named argument. It only works withBuild
orBuildNamed
to define a named placeholder using syntax${name}
.Raw(expr)
marks anexpr
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)
// Output:
// EXPLAIN SELECT id FROM user LEFT JOIN SELECT * FROM banned WHERE state IN (?, ?)
// [1 2]
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
infmt.Sprintf
.$0
$1
...$n
refers nth-argument passed in the call. Next$?
will use arguments n+1.${name}
refers a named argument created byNamed
withname
.$$
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)
// Output:
// EXPLAIN SELECT id FROM user WHERE status IN (?, ?) LEFT JOIN SELECT * FROM banned WHERE created_at > ? AND state IN (?, ?, ?) AND modified_at BETWEEN ? AND ?
// [1 2 1514458225 3 4 5 1514458225 1514544625]
If we just want to use ${name}
syntax to refer named arguments, use BuildNamed
instead. It disables all special syntax but ${name}
and $$
.
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
isBuilder
andArgs
. Nearly all featuers are built on top of them. If we want to extend this package, e.g. supportEXPLAIN
, we can useBuild("EXPLAIN $?", builder)
to addEXPLAIN
in front of any SQL. - ORM: Package
squirrel
doesn't provide ORM directly. There is another package structable, which is based onsquirrel
, 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.
Documentation
¶
Overview ¶
Package sqlbuilder is a flexible and powerful tool to build SQL string and associated args.
Index ¶
- Variables
- func Escape(ident string) string
- func EscapeAll(ident ...string) []string
- func Flatten(slices any) (flattened []any)
- func List(arg any) any
- func Named(name string, arg any) any
- func Raw(expr string) any
- type Args
- type Builder
- type Cond
- func (c *Cond) And(andExpr ...string) string
- func (c *Cond) Between(field string, lower, upper any) string
- func (c *Cond) E(field string, value any) string
- func (c *Cond) Equal(field string, value any) string
- func (c *Cond) G(field string, value any) string
- func (c *Cond) GE(field string, value any) string
- func (c *Cond) GreaterEqualThan(field string, value any) string
- func (c *Cond) GreaterThan(field string, value any) string
- func (c *Cond) In(field string, value ...any) string
- func (c *Cond) IsNotNull(field string) string
- func (c *Cond) IsNull(field string) string
- func (c *Cond) L(field string, value any) string
- func (c *Cond) LE(field string, value any) string
- func (c *Cond) LessEqualThan(field string, value any) string
- func (c *Cond) LessThan(field string, value any) string
- func (c *Cond) Like(field string, value any) string
- func (c *Cond) LikeBinary(field string, value any) string
- func (c *Cond) NE(field string, value any) string
- func (c *Cond) NotBetween(field string, lower, upper any) string
- func (c *Cond) NotEqual(field string, value any) string
- func (c *Cond) NotIn(field string, value ...any) string
- func (c *Cond) NotLike(field string, value any) string
- func (c *Cond) NotLikeBinary(field string, value any) string
- func (c *Cond) Or(orExpr ...string) string
- func (c *Cond) Var(value any) string
- type DeleteBuilder
- func (db *DeleteBuilder) Build() (sql string, args []any)
- func (db *DeleteBuilder) BuildWithFlavor(flavor Flavor, initialArg ...any) (sql string, args []any)
- func (db *DeleteBuilder) DeleteFrom(table string) *DeleteBuilder
- func (db *DeleteBuilder) SetFlavor(flavor Flavor) (old Flavor)
- func (db *DeleteBuilder) String() string
- func (db *DeleteBuilder) Where(andExpr ...string) *DeleteBuilder
- type Flavor
- type InsertBuilder
- func (ib *InsertBuilder) Build() (sql string, args []any)
- func (ib *InsertBuilder) BuildWithFlavor(flavor Flavor, initialArg ...any) (sql string, args []any)
- func (ib *InsertBuilder) Cols(col ...string) *InsertBuilder
- func (ib *InsertBuilder) InsertInto(table string) *InsertBuilder
- func (ib *InsertBuilder) SetFlavor(flavor Flavor) (old Flavor)
- func (ib *InsertBuilder) String() string
- func (ib *InsertBuilder) Values(value ...any) *InsertBuilder
- type JoinOption
- type SelectBuilder
- func (sb *SelectBuilder) As(name, alias string) string
- func (sb *SelectBuilder) Asc() *SelectBuilder
- func (sb *SelectBuilder) Build() (sql string, args []any)
- func (sb *SelectBuilder) BuildWithFlavor(flavor Flavor, initialArg ...any) (sql string, args []any)
- func (sb *SelectBuilder) BuilderAs(builder Builder, alias string) string
- func (sb *SelectBuilder) Desc() *SelectBuilder
- func (sb *SelectBuilder) Distinct() *SelectBuilder
- func (sb *SelectBuilder) ForUpdate() *SelectBuilder
- func (sb *SelectBuilder) From(table ...string) *SelectBuilder
- func (sb *SelectBuilder) GroupBy(col ...string) *SelectBuilder
- func (sb *SelectBuilder) Having(andExpr ...string) *SelectBuilder
- func (sb *SelectBuilder) Join(table string, onExpr ...string) *SelectBuilder
- func (sb *SelectBuilder) JoinWithOption(option JoinOption, table string, onExpr ...string) *SelectBuilder
- func (sb *SelectBuilder) Limit(limit int) *SelectBuilder
- func (sb *SelectBuilder) Offset(offset int) *SelectBuilder
- func (sb *SelectBuilder) OrderBy(col ...string) *SelectBuilder
- func (sb *SelectBuilder) Select(col ...string) *SelectBuilder
- func (sb *SelectBuilder) SetFlavor(flavor Flavor) (old Flavor)
- func (sb *SelectBuilder) String() string
- func (sb *SelectBuilder) Where(andExpr ...string) *SelectBuilder
- type Struct
- func (s *Struct) Addr(value any) []any
- func (s *Struct) AddrForTag(tag string, value any) []any
- func (s *Struct) AddrWithCols(cols []string, value any) []any
- func (s *Struct) DeleteFrom(table string) *DeleteBuilder
- func (s *Struct) For(flavor Flavor) *Struct
- func (s *Struct) InsertInto(table string, value ...any) *InsertBuilder
- func (s *Struct) InsertIntoForTag(table string, tag string, value ...any) *InsertBuilder
- func (s *Struct) SelectFrom(table string) *SelectBuilder
- func (s *Struct) SelectFromForTag(table string, tag string) *SelectBuilder
- func (s *Struct) Update(table string, value any) *UpdateBuilder
- func (s *Struct) UpdateForTag(table string, tag string, value any) *UpdateBuilder
- type UpdateBuilder
- func (ub *UpdateBuilder) Add(field string, value any) string
- func (ub *UpdateBuilder) Assign(field string, value any) string
- func (ub *UpdateBuilder) Build() (sql string, args []any)
- func (ub *UpdateBuilder) BuildWithFlavor(flavor Flavor, initialArg ...any) (sql string, args []any)
- func (ub *UpdateBuilder) Decr(field string) string
- func (ub *UpdateBuilder) Div(field string, value any) string
- func (ub *UpdateBuilder) Incr(field string) string
- func (ub *UpdateBuilder) Mul(field string, value any) string
- func (ub *UpdateBuilder) Set(assignment ...string) *UpdateBuilder
- func (ub *UpdateBuilder) SetFlavor(flavor Flavor) (old Flavor)
- func (ub *UpdateBuilder) String() string
- func (ub *UpdateBuilder) Sub(field string, value any) string
- func (ub *UpdateBuilder) Update(table string) *UpdateBuilder
- func (ub *UpdateBuilder) Where(andExpr ...string) *UpdateBuilder
Examples ¶
Constants ¶
This section is empty.
Variables ¶
var ( // DBTag is the struct tag to describe the name for a field in struct. DBTag = "db" // FieldTag is the struct tag to describe the tag name for a field in struct. // Use "," to separate different tags. FieldTag = "fieldtag" // FieldOpt is the options for a struct field. // As db column can contain "," in theory, field options should be provided in a separated tag. FieldOpt = "fieldopt" )
var ( // DefaultFlavor is the default flavor for all builders. DefaultFlavor = MySQL )
Functions ¶
func Flatten ¶
Flatten recursively extracts values in slices and returns a flattened []interface{} with all values. If slices is not a slice, return `[]interface{}{slices}`.
func List ¶
List marks arg as a list of data. If arg is `[]int{1, 2, 3}`, it will be compiled to `?, ?, ?` with args `[1 2 3]`.
Types ¶
type Args ¶
type Args struct { // The default flavor used by `Args#Compile` Flavor Flavor // contains filtered or unexported fields }
Args stores arguments associated with a SQL.
func (*Args) Compile ¶
Compile compiles builder's format to standard sql and returns associated args.
The format string uses a 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.
func (*Args) CompileWithFlavor ¶
func (args *Args) CompileWithFlavor(format string, flavor Flavor, intialValue ...any) (query string, values []any)
CompileWithFlavor compiles builder's format to standard sql with flavor and returns associated args.
See doc for `Compile` to learn details. nolint:gocyclo,lll
type Builder ¶
type Builder interface { Build() (sql string, args []any) BuildWithFlavor(flavor Flavor, initialArg ...any) (sql string, args []any) }
Builder is a general SQL builder. It's used by Args to create nested SQL like the `IN` expression in `SELECT * FROM t1 WHERE id IN (SELECT id FROM t2)`.
func Build ¶
Build creates a Builder from a format string. The format string uses special syntax to represent arguments. See doc in `Args#Compile` for syntax details.
Example ¶
Output: EXPLAIN SELECT id FROM user WHERE status IN (?, ?) LEFT JOIN SELECT * FROM banned WHERE created_at > ? AND state IN (?, ?, ?) AND modified_at BETWEEN ? AND ? [1 2 1514458225 3 4 5 1514458225 1514544625]
func BuildNamed ¶
BuildNamed creates a Builder from a format string. The format string uses `${key}` to refer the value of named by key.
Example ¶
Output: SELECT * FROM user WHERE status IN (?, ?, ?) AND name LIKE ? AND created_at > @start AND modified_at < @start + 86400 [1 2 5 Huan% {{} start 1234567890}]
type Cond ¶
type Cond struct {
Args *Args
}
Cond provides several helper methods to build conditions.
func (*Cond) GreaterEqualThan ¶
GreaterEqualThan represents "field >= value".
func (*Cond) GreaterThan ¶
GreaterThan represents "field > value".
func (*Cond) LessEqualThan ¶
LessEqualThan represents "field <= value".
func (*Cond) LikeBinary ¶
LikeBinary represents "field LIKE BINARY value".
func (*Cond) NotBetween ¶
NotBetween represents "field NOT BETWEEN lower AND upper".
func (*Cond) NotLikeBinary ¶
NotLikeBinary represents "field NOT LIKE BINARY value".
type DeleteBuilder ¶
type DeleteBuilder struct { Cond // contains filtered or unexported fields }
DeleteBuilder is a builder to build DELETE.
Example ¶
Output: DELETE FROM demo.user WHERE id > ? AND name LIKE ? AND (id_card IS NULL OR status IN (?, ?, ?)) AND modified_at > created_at + ? [1234 %Du 1 2 5 86400]
func NewDeleteBuilder ¶
func NewDeleteBuilder() *DeleteBuilder
NewDeleteBuilder creates a new DELETE builder.
func (*DeleteBuilder) Build ¶
func (db *DeleteBuilder) Build() (sql string, args []any)
Build returns compiled DELETE string and args. They can be used in `DB#Query` of package `database/sql` directly.
func (*DeleteBuilder) BuildWithFlavor ¶
func (db *DeleteBuilder) BuildWithFlavor(flavor Flavor, initialArg ...any) (sql string, args []any)
BuildWithFlavor returns compiled DELETE string and args with flavor and initial args. They can be used in `DB#Query` of package `database/sql` directly.
func (*DeleteBuilder) DeleteFrom ¶
func (db *DeleteBuilder) DeleteFrom(table string) *DeleteBuilder
DeleteFrom sets table name in DELETE.
func (*DeleteBuilder) SetFlavor ¶
func (db *DeleteBuilder) SetFlavor(flavor Flavor) (old Flavor)
SetFlavor sets the flavor of compiled sql.
func (*DeleteBuilder) String ¶
func (db *DeleteBuilder) String() string
String returns the compiled DELETE string.
func (*DeleteBuilder) Where ¶
func (db *DeleteBuilder) Where(andExpr ...string) *DeleteBuilder
Where sets expressions of WHERE in DELETE.
type Flavor ¶
type Flavor int
Flavor is the flag to control the format of compiled sql.
Example ¶
Output: SELECT name FROM user WHERE id = $1 AND rank > $2 [1234 3]
const ( MySQL Flavor PostgreSQL )
Supported flavors.
func (Flavor) NewDeleteBuilder ¶
func (f Flavor) NewDeleteBuilder() *DeleteBuilder
NewDeleteBuilder creates a new DELETE builder with flavor.
func (Flavor) NewInsertBuilder ¶
func (f Flavor) NewInsertBuilder() *InsertBuilder
NewInsertBuilder creates a new INSERT builder with flavor.
func (Flavor) NewSelectBuilder ¶
func (f Flavor) NewSelectBuilder() *SelectBuilder
NewSelectBuilder creates a new SELECT builder with flavor.
func (Flavor) NewUpdateBuilder ¶
func (f Flavor) NewUpdateBuilder() *UpdateBuilder
NewUpdateBuilder creates a new UPDATE builder with flavor.
type InsertBuilder ¶
type InsertBuilder struct {
// contains filtered or unexported fields
}
InsertBuilder is a builder to build INSERT.
Example ¶
Output: INSERT INTO demo.user (id, name, status, created_at) VALUES (?, ?, ?, UNIX_TIMESTAMP(NOW())), (?, ?, ?, ?) [1 Huan Du 1 2 Charmy Liu 1 1234567890]
func NewInsertBuilder ¶
func NewInsertBuilder() *InsertBuilder
NewInsertBuilder creates a new INSERT builder.
func (*InsertBuilder) Build ¶
func (ib *InsertBuilder) Build() (sql string, args []any)
Build returns compiled INSERT string and args. They can be used in `DB#Query` of package `database/sql` directly.
func (*InsertBuilder) BuildWithFlavor ¶
func (ib *InsertBuilder) BuildWithFlavor(flavor Flavor, initialArg ...any) (sql string, args []any)
BuildWithFlavor returns compiled INSERT string and args with flavor and initial args. They can be used in `DB#Query` of package `database/sql` directly.
func (*InsertBuilder) Cols ¶
func (ib *InsertBuilder) Cols(col ...string) *InsertBuilder
Cols sets columns in INSERT.
func (*InsertBuilder) InsertInto ¶
func (ib *InsertBuilder) InsertInto(table string) *InsertBuilder
InsertInto sets table name in INSERT.
func (*InsertBuilder) SetFlavor ¶
func (ib *InsertBuilder) SetFlavor(flavor Flavor) (old Flavor)
SetFlavor sets the flavor of compiled sql.
func (*InsertBuilder) String ¶
func (ib *InsertBuilder) String() string
String returns the compiled DELETE string.
func (*InsertBuilder) Values ¶
func (ib *InsertBuilder) Values(value ...any) *InsertBuilder
Values adds a list of values for a row in INSERT.
type JoinOption ¶
type JoinOption string
JoinOption is the option in JOIN.
const ( LeftJoin JoinOption = "LEFT" LeftOuterJoin JoinOption = "LEFT OUTER" RightJoin JoinOption = "RIGHT" RightOuterJoin JoinOption = "RIGHT OUTER" FullJoin JoinOption = "FULL" FullOuterJoin JoinOption = "FULL OUTER" )
Join options.
type SelectBuilder ¶
type SelectBuilder struct { Cond // contains filtered or unexported fields }
SelectBuilder is a builder to build SELECT.
Example ¶
Output: SELECT DISTINCT id, name, COUNT(*) AS t FROM demo.user WHERE id > ? AND name LIKE ? AND (id_card IS NULL OR status IN (?, ?, ?)) AND id NOT IN (SELECT id FROM banned) AND modified_at > created_at + ? GROUP BY status HAVING status NOT IN (?, ?) ORDER BY modified_at ASC LIMIT 10 OFFSET 5 [1234 %Du 1 2 5 86400 4 5]
Example (AdvancedUsage) ¶
Output: SELECT id, name FROM (SELECT * FROM banned WHERE name NOT IN (?, ?)) AS user WHERE status IN (?, ?, ?) AND created_at BETWEEN @start AND @end ORDER BY modified_at DESC [Huan Du Charmy Liu 1 2 3 {{} start 1234567890} {{} end 1234599999}]
Example (Join) ¶
Output: SELECT u.id, u.name, c.type, p.nickname FROM user u JOIN contract c ON u.id = c.user_id AND c.status IN (?, ?, ?) RIGHT OUTER JOIN person p ON u.id = p.user_id AND p.surname LIKE ? WHERE u.modified_at > u.created_at + ? [1 2 5 %Du 86400]
func NewSelectBuilder ¶
func NewSelectBuilder() *SelectBuilder
NewSelectBuilder creates a new SELECT builder.
func (*SelectBuilder) As ¶
func (sb *SelectBuilder) As(name, alias string) string
As returns an AS expression.
func (*SelectBuilder) Asc ¶
func (sb *SelectBuilder) Asc() *SelectBuilder
Asc sets order of ORDER BY to ASC.
func (*SelectBuilder) Build ¶
func (sb *SelectBuilder) Build() (sql string, args []any)
Build returns compiled SELECT string and args. They can be used in `DB#Query` of package `database/sql` directly.
func (*SelectBuilder) BuildWithFlavor ¶
func (sb *SelectBuilder) BuildWithFlavor(flavor Flavor, initialArg ...any) (sql string, args []any)
BuildWithFlavor returns compiled SELECT string and args with flavor and initial args. They can be used in `DB#Query` of package `database/sql` directly. nolint:gocyclo
func (*SelectBuilder) BuilderAs ¶
func (sb *SelectBuilder) BuilderAs(builder Builder, alias string) string
BuilderAs returns an AS expression wrapping a complex SQL. According to SQL syntax, SQL built by builder is surrounded by parens.
func (*SelectBuilder) Desc ¶
func (sb *SelectBuilder) Desc() *SelectBuilder
Desc sets order of ORDER BY to DESC.
func (*SelectBuilder) Distinct ¶
func (sb *SelectBuilder) Distinct() *SelectBuilder
Distinct marks this SELECT as DISTINCT.
func (*SelectBuilder) ForUpdate ¶
func (sb *SelectBuilder) ForUpdate() *SelectBuilder
ForUpdate add "FOR UPDATE" to SELECT
func (*SelectBuilder) From ¶
func (sb *SelectBuilder) From(table ...string) *SelectBuilder
From sets table names in SELECT.
func (*SelectBuilder) GroupBy ¶
func (sb *SelectBuilder) GroupBy(col ...string) *SelectBuilder
GroupBy sets columns of GROUP BY in SELECT.
func (*SelectBuilder) Having ¶
func (sb *SelectBuilder) Having(andExpr ...string) *SelectBuilder
Having sets expressions of HAVING in SELECT.
func (*SelectBuilder) Join ¶
func (sb *SelectBuilder) Join(table string, onExpr ...string) *SelectBuilder
Join sets expressions of JOIN in SELECT.
It builds a JOIN expression like
JOIN table ON onExpr[0] AND onExpr[1] ...
func (*SelectBuilder) JoinWithOption ¶
func (sb *SelectBuilder) JoinWithOption(option JoinOption, table string, onExpr ...string) *SelectBuilder
JoinWithOption sets expressions of JOIN with an option.
It builds a JOIN expression like
option JOIN table ON onExpr[0] AND onExpr[1] ...
Here is a list of supported options.
- LeftJoin: LEFT JOIN
- LeftOuterJoin: LEFT OUTER JOIN
- RightJoin: RIGHT JOIN
- RightOuterJoin: RIGHT OUTER JOIN
func (*SelectBuilder) Limit ¶
func (sb *SelectBuilder) Limit(limit int) *SelectBuilder
Limit sets the LIMIT in SELECT.
func (*SelectBuilder) Offset ¶
func (sb *SelectBuilder) Offset(offset int) *SelectBuilder
Offset sets the LIMIT offset in SELECT.
func (*SelectBuilder) OrderBy ¶
func (sb *SelectBuilder) OrderBy(col ...string) *SelectBuilder
OrderBy sets columns of ORDER BY in SELECT.
func (*SelectBuilder) Select ¶
func (sb *SelectBuilder) Select(col ...string) *SelectBuilder
Select sets columns in SELECT.
func (*SelectBuilder) SetFlavor ¶
func (sb *SelectBuilder) SetFlavor(flavor Flavor) (old Flavor)
SetFlavor sets the flavor of compiled sql.
func (*SelectBuilder) String ¶
func (sb *SelectBuilder) String() string
String returns the compiled SELECT string.
func (*SelectBuilder) Where ¶
func (sb *SelectBuilder) Where(andExpr ...string) *SelectBuilder
Where sets expressions of WHERE in SELECT.
type Struct ¶
type Struct struct { Flavor Flavor // contains filtered or unexported fields }
Struct represents a struct type.
All methods in Struct are thread-safe. We can define a global variable to hold a Struct and use it in any goroutine.
Example (BuildDELETE) ¶
Output: DELETE FROM user WHERE id = ? [1234]
Example (BuildINSERT) ¶
Output: INSERT INTO user (id, name, status) VALUES (?, ?, ?) [1234 Huan Du 1]
Example (BuildUPDATE) ¶
Output: UPDATE user SET id = ?, name = ?, status = ? WHERE id = ? [1234 Huan Du 1 1234]
Example (ForPostgreSQL) ¶
Output: SELECT id, name, status FROM user WHERE id = $1 [1234]
Example (UseStructAsORM) ¶
Output: SELECT id, name, status FROM user WHERE id = ? [1234] sqlbuilder.User{Id:1234, Name:"huandu", Status:1}
Example (UseTag) ¶
Output: done
func NewStruct ¶
NewStruct analyzes type information in structValue and creates a new Struct with all structValue fields. If structValue is not a struct, NewStruct returns a dummy Sturct.
func (*Struct) Addr ¶
Addr takes address of all exported fields of the s from the value. The returned result can be used in `Row#Scan` directly.
func (*Struct) AddrForTag ¶
AddrForTag takes address of all fields of the s tagged with tag from the value. The returned result can be used in `Row#Scan` directly.
If tag is not defined in s in advance,
func (*Struct) AddrWithCols ¶
AddrWithCols takes address of all columns defined in cols from the value. The returned result can be used in `Row#Scan` directly.
func (*Struct) DeleteFrom ¶
func (s *Struct) DeleteFrom(table string) *DeleteBuilder
DeleteFrom creates a new `DeleteBuilder` with table name.
Caller is responsible to set WHERE condition to match right record.
func (*Struct) InsertInto ¶
func (s *Struct) InsertInto(table string, value ...any) *InsertBuilder
InsertInto creates a new `InsertBuilder` with table name. By default, all exported fields of the s is inserted in INSERT with the field values from value. Bulk insert is supported. Item in value that is not the same as that of s will be skipped. If no item in value is valid, InsertInto returns a dummy `InsertBuilder` with table name.
func (*Struct) InsertIntoForTag ¶
func (s *Struct) InsertIntoForTag(table string, tag string, value ...any) *InsertBuilder
InsertIntoForTag creates a new `InsertBuilder` with table name. By default, all fields of the s tagged with tag is inserted in INSERT with the field values from value. Bulk insert is supported. Item in value that is not the same as that of s will be skipped. If no item in value is valid, InsertIntoForTag returns a dummy `InsertBuilder` with table name.
func (*Struct) SelectFrom ¶
func (s *Struct) SelectFrom(table string) *SelectBuilder
SelectFrom creates a new `SelectBuilder` with table name. By default, all exported fields of the s are listed as columns in SELECT.
Caller is responsible to set WHERE condition to find right record.
func (*Struct) SelectFromForTag ¶
func (s *Struct) SelectFromForTag(table string, tag string) *SelectBuilder
SelectFromForTag creates a new `SelectBuilder` with table name for a specified tag. By default, all fields of the s tagged with tag are listed as columns in SELECT.
Caller is responsible to set WHERE condition to find right record.
func (*Struct) Update ¶
func (s *Struct) Update(table string, value any) *UpdateBuilder
Update creates a new `UpdateBuilder` with table name. By default, all exported fields of the s is assigned in UPDATE with the field values from value. If value's type is not the same as that of s, Update returns a dummy `UpdateBuilder` with table name.
Caller is responsible to set WHERE condition to match right record.
func (*Struct) UpdateForTag ¶
func (s *Struct) UpdateForTag(table string, tag string, value any) *UpdateBuilder
UpdateForTag creates a new `UpdateBuilder` with table name. By default, all fields of the s tagged with tag is assigned in UPDATE with the field values from value. If value's type is not the same as that of s, UpdateForTag returns a dummy `UpdateBuilder` with table name.
Caller is responsible to set WHERE condition to match right record.
type UpdateBuilder ¶
type UpdateBuilder struct { Cond // contains filtered or unexported fields }
UpdateBuilder is a builder to build UPDATE.
Example ¶
Output: UPDATE demo.user SET type = ?, credit = credit + 1, modified_at = UNIX_TIMESTAMP(NOW()) WHERE id > ? AND name LIKE ? AND (id_card IS NULL OR status IN (?, ?, ?)) AND modified_at > created_at + ? [sys 1234 %Du 1 2 5 86400]
func NewUpdateBuilder ¶
func NewUpdateBuilder() *UpdateBuilder
NewUpdateBuilder creates a new UPDATE builder.
func (*UpdateBuilder) Add ¶
func (ub *UpdateBuilder) Add(field string, value any) string
Add represents SET "field = field + value" in UPDATE.
func (*UpdateBuilder) Assign ¶
func (ub *UpdateBuilder) Assign(field string, value any) string
Assign represents SET "field = value" in UPDATE.
func (*UpdateBuilder) Build ¶
func (ub *UpdateBuilder) Build() (sql string, args []any)
Build returns compiled UPDATE string and args. They can be used in `DB#Query` of package `database/sql` directly.
func (*UpdateBuilder) BuildWithFlavor ¶
func (ub *UpdateBuilder) BuildWithFlavor(flavor Flavor, initialArg ...any) (sql string, args []any)
BuildWithFlavor returns compiled UPDATE string and args with flavor and initial args. They can be used in `DB#Query` of package `database/sql` directly.
func (*UpdateBuilder) Decr ¶
func (ub *UpdateBuilder) Decr(field string) string
Decr represents SET "field = field - 1" in UPDATE.
func (*UpdateBuilder) Div ¶
func (ub *UpdateBuilder) Div(field string, value any) string
Div represents SET "field = field / value" in UPDATE.
func (*UpdateBuilder) Incr ¶
func (ub *UpdateBuilder) Incr(field string) string
Incr represents SET "field = field + 1" in UPDATE.
func (*UpdateBuilder) Mul ¶
func (ub *UpdateBuilder) Mul(field string, value any) string
Mul represents SET "field = field * value" in UPDATE.
func (*UpdateBuilder) Set ¶
func (ub *UpdateBuilder) Set(assignment ...string) *UpdateBuilder
Set sets the assignements in SET.
func (*UpdateBuilder) SetFlavor ¶
func (ub *UpdateBuilder) SetFlavor(flavor Flavor) (old Flavor)
SetFlavor sets the flavor of compiled sql.
func (*UpdateBuilder) String ¶
func (ub *UpdateBuilder) String() string
String returns the compiled UPDATE string.
func (*UpdateBuilder) Sub ¶
func (ub *UpdateBuilder) Sub(field string, value any) string
Sub represents SET "field = field - value" in UPDATE.
func (*UpdateBuilder) Update ¶
func (ub *UpdateBuilder) Update(table string) *UpdateBuilder
Update sets table name in UPDATE.
func (*UpdateBuilder) Where ¶
func (ub *UpdateBuilder) Where(andExpr ...string) *UpdateBuilder
Where sets expressions of WHERE in UPDATE.