sqlbuilder

package module
v1.27.0 Latest Latest
Warning

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

 Go to latest Published: Apr 15, 2024 License: MIT Imports: 17 Imported by: 107

README

SQL builder for Go

Go GoDoc Go Report Coverage Status

The sqlbuilder package implements a series of flexible and powerful SQL string concatenation builders. This package focuses on constructing SQL strings for direct use with the Go standard library's sql.DB and sql.Stmt related interfaces, and strives to optimize the performance of building SQL and reduce memory consumption.

The initial goal in designing this package was to create a pure SQL construction library that is independent of specific database drivers and business logic. It is designed to meet the needs of enterprise-level scenarios that require various customized database drivers, special operation and maintenance standards, heterogeneous systems, and non-standard SQL in complex situations. Since its open-source inception, this package has been tested in a large enterprise-level application scenario, enduring the pressure of hundreds of millions of orders daily and nearly ten million transactions per day, demonstrating good performance and scalability.

This package does not bind to any specific database driver, nor does it automatically connect to any database. It does not even assume the use of the generated SQL, making it suitable for any application scenario that constructs SQL-like statements. It is also very suitable for secondary development on this basis, to implement more business-related database access packages, ORMs, and so on.

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)

// Output:
// SELECT id, name FROM demo.user WHERE status = 1 LIMIT 10

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)

// Output:
// SELECT id, name, COUNT(*) AS c FROM user WHERE status IN (?, ?, ?)
// [1 2 5]
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.

// Build a SQL to create a HIVE table.
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)

// Output:
// CREATE TABLE users PARTITION BY (year) AS SELECT columns[0] id, columns[1] name, columns[2] year FROM `all-users.csv` LIMIT 100

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)

// Output:
// SELECT id FROM user WHERE status IN (?, ?, ?) AND (name = ? OR email LIKE ?)
// [1 2 5 foo foo@%]

There are many methods for building conditions.

There are also some methods to combine conditions.

  • Cond.And: Combine conditions with AND operator.
  • Cond.Or: Combine conditions with OR operator.
Share WHERE clause among builders

Due to the importance of the WHERE statement in SQL, we often need to continuously append conditions and even share some common WHERE conditions among different builders. Therefore, we abstract the WHERE statement into a WhereClause struct, which can be used to create reusable WHERE conditions.

Here is a sample to show how to copy WHERE clause from a SelectBuilder to an UpdateBuilder.

// Build a SQL to select a user from database.
sb := Select("name", "level").From("users")
sb.Where(
    sb.Equal("id", 1234),
)
fmt.Println(sb)

ub := Update("users")
ub.Set(
    ub.Add("level", 10),
)

// Set the WHERE clause of UPDATE to the WHERE clause of SELECT.
ub.WhereClause = sb.WhereClause
fmt.Println(ub)

// Output:
// SELECT name, level FROM users WHERE id = ?
// UPDATE users SET level = level + ? WHERE id = ?

Read samples for WhereClause to learn how to use it.

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                                    // 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 call `WithTag` to include fields with tag or `WithoutTag` to exclude fields with tag.
    Field4     int64  `db:"field4" fieldtag:"foo"`       // If we use `s.WithTag("foo").Select(table)`, columnes of SELECT are field3 and field4.
    Field5     string `db:"field5" fieldas:"f5_alias"`   // Use "fieldas" in field tag to set a column alias (AS) used in SELECT.
    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`.
    Empty      uint   `db:"empty" fieldopt:"omitempty"`  // Omit the field in UPDATE if it is a nil or zero value.

    // The `omitempty` can be written as a function.
    // In this case, omit empty field `Tagged` when UPDATE for tag `tag1` and `tag3` but not `tag2`.
    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"`
}

// 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 user.id, user.name, user.status FROM user WHERE id = ?
    // [1234]
    // sqlbuilder.User{ID:1234, Name:"huandu", Status:1}
}

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)

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

// 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 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)

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

Interpolate args in the sql

Some SQL-like drivers, e.g. SQL for Redis, SQL for ES, etc., 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.

The design goal of the interpolation feature in this package is to implement a "basically sufficient" capability, rather than a feature that is on par with various SQL drivers and DBMS systems.

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)

// Output:
// SELECT name FROM user WHERE id <> 1234 AND name = 'Charmy Liu' AND desc LIKE '%mother\'s day%'
// <nil>

Here is a sample for PostgreSQL. Note that the dollar quote is supported.

// Only the last `$1` is interpolated.
// Others are not interpolated as they are inside dollar quote (the `$$`).
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)

// Output:
//
// 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(42);
// <nil>

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

Examples

Constants

This section is empty.

Variables

View Source 
var (
	// DefaultFieldMapper is the default field name to table column name mapper func.
	// It's nil by default which means field name will be kept as it is.
	//
	// If a Struct has its own mapper func, the DefaultFieldMapper is ignored in this Struct.
	// Field tag has precedence over all kinds of field mapper functions.
	//
	// Field mapper is called only once on a Struct when the Struct is used to create builder for the first time.
	DefaultFieldMapper FieldMapperFunc

	// DefaultGetAlias is the default alias and dbtag get func
	DefaultGetAlias GetAliasFunc
)
View Source 
var (
	// ErrInterpolateNotImplemented means the method or feature is not implemented right now.
	ErrInterpolateNotImplemented = errors.New("go-sqlbuilder: interpolation for this flavor is not implemented")

	// ErrInterpolateMissingArgs means there are some args missing in query, so it's not possible to
	// prepare a query with such args.
	ErrInterpolateMissingArgs = errors.New("go-sqlbuilder: not enough args when interpolating")

	// ErrInterpolateUnsupportedArgs means that some types of the args are not supported.
	ErrInterpolateUnsupportedArgs = errors.New("go-sqlbuilder: unsupported args when interpolating")
)
View Source 
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"

	// FieldAs is the column alias (AS) for a struct field.
	FieldAs = "fieldas"
)
View Source 
var (
	// DefaultFlavor is the default flavor for all builders.
	DefaultFlavor = MySQL
)

Functions

func Escape 

func Escape(ident string) string

Escape replaces `$` with `$$` in ident.

func EscapeAll 

func EscapeAll(ident ...string) []string

EscapeAll replaces `$` with `$$` in all strings of ident.

func Flatten 

func Flatten(slices interface{}) (flattened []interface{})

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 

func List(arg interface{}) interface{}

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

func Named 

func Named(name string, arg interface{}) interface{}

Named creates a named argument. Unlike `sql.Named`, this named argument works only with `Build` or `BuildNamed` for convenience and will be replaced to a `?` after `Compile`.

func Raw 

func Raw(expr string) interface{}

Raw marks the expr as a raw value which will not be added to args.

func SnakeCaseMapper added in v1.12.0 

func SnakeCaseMapper(field string) string

SnakeCaseMapper is a field mapper which can convert field name from CamelCase to snake_case.

For instance, it will convert "MyField" to "my_field".

SnakeCaseMapper uses package "xstrings" to do the conversion. See https://pkg.go.dev/github.com/huandu/xstrings#ToSnakeCase for conversion rules.

func Tuple added in v1.22.0 

func Tuple(values ...interface{}) interface{}

Tuple wraps values into a tuple and can be used as a single value.

Example 
sb := Select("id", "name").From("user")
sb.Where(
	sb.In(
		TupleNames("type", "status"),
		Tuple("web", 1),
		Tuple("app", 1),
		Tuple("app", 2),
	),
)
sql, args := sb.Build()

fmt.Println(sql)
fmt.Println(args)

Output:

SELECT id, name FROM user WHERE (type, status) IN ((?, ?), (?, ?), (?, ?))
[web 1 app 1 app 2]

func TupleNames added in v1.22.0 

func TupleNames(names ...string) string

TupleNames joins names with tuple format. The names is not escaped. Use `EscapeAll` to escape them if necessary.

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) Add 

func (args *Args) Add(arg interface{}) string

Add adds an arg to Args and returns a placeholder.

func (*Args) Compile 

func (args *Args) Compile(format string, initialValue ...interface{}) (query string, values []interface{})

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, initialValue ...interface{}) (query string, values []interface{})

CompileWithFlavor compiles builder's format to standard sql with flavor and returns associated args.

See doc for `Compile` to learn details.

type Builder 

type Builder interface {
	Build() (sql string, args []interface{})
	BuildWithFlavor(flavor Flavor, initialArg ...interface{}) (sql string, args []interface{})
}

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 

func Build(format string, arg ...interface{}) Builder

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 
sb := NewSelectBuilder()
sb.Select("id").From("user").Where(sb.In("status", 1, 2))

b := Build("EXPLAIN $? LEFT JOIN SELECT * FROM $? WHERE created_at > $? AND state IN (${states}) AND modified_at BETWEEN $2 AND $?",
	sb, Raw("banned"), 1514458225, 1514544625, Named("states", List([]int{3, 4, 5})))
s, args := b.Build()

fmt.Println(s)
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]

func BuildNamed 

func BuildNamed(format string, named map[string]interface{}) Builder

BuildNamed creates a Builder from a format string. The format string uses `${key}` to refer the value of named by key.

Example 
b := BuildNamed("SELECT * FROM ${table} WHERE status IN (${status}) AND name LIKE ${name} AND created_at > ${time} AND modified_at < ${time} + 86400",
	map[string]interface{}{
		"time":   sql.Named("start", 1234567890),
		"status": List([]int{1, 2, 5}),
		"name":   "Huan%",
		"table":  Raw("user"),
	})
s, args := b.Build()

fmt.Println(s)
fmt.Println(args)

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}]

func Buildf 

func Buildf(format string, arg ...interface{}) Builder

Buildf creates a Builder from a format string using `fmt.Sprintf`-like syntax. As all arguments will be converted to a string internally, e.g. "$0", only `%v` and `%s` are valid.

Example 
sb := NewSelectBuilder()
sb.Select("id").From("user")

explain := Buildf("EXPLAIN %v LEFT JOIN SELECT * FROM banned WHERE state IN (%v, %v)", sb, 1, 2)
s, args := explain.Build()
fmt.Println(s)
fmt.Println(args)

Output:

EXPLAIN SELECT id FROM user LEFT JOIN SELECT * FROM banned WHERE state IN (?, ?)
[1 2]

func WithFlavor 

func WithFlavor(builder Builder, flavor Flavor) Builder

WithFlavor creates a new Builder based on builder with a default flavor.

Example 
sql, args := WithFlavor(Buildf("SELECT * FROM foo WHERE id = %v", 1234), PostgreSQL).Build()

fmt.Println(sql)
fmt.Println(args)

// Explicitly use MySQL as the flavor.
sql, args = WithFlavor(Buildf("SELECT * FROM foo WHERE id = %v", 1234), PostgreSQL).BuildWithFlavor(MySQL)

fmt.Println(sql)
fmt.Println(args)

// Explicitly use MySQL as the informix.
sql, args = WithFlavor(Buildf("SELECT * FROM foo WHERE id = %v", 1234), Informix).Build()

fmt.Println(sql)
fmt.Println(args)

Output:

SELECT * FROM foo WHERE id = $1
[1234]
SELECT * FROM foo WHERE id = ?
[1234]
SELECT * FROM foo WHERE id = ?
[1234]

type Cond 

type Cond struct {
	Args *Args
}

Cond provides several helper methods to build conditions.

func NewCond added in v1.27.0 

func NewCond() *Cond

NewCond returns a new Cond.

func (*Cond) All added in v1.21.0 

func (c *Cond) All(field, op string, value ...interface{}) string

All represents "field op ALL (value...)".

func (*Cond) And added in v1.1.0 

func (c *Cond) And(andExpr ...string) string

And represents AND logic like "expr1 AND expr2 AND expr3".

func (*Cond) Any added in v1.21.0 

func (c *Cond) Any(field, op string, value ...interface{}) string

Any represents "field op ANY (value...)".

func (*Cond) Between 

func (c *Cond) Between(field string, lower, upper interface{}) string

Between represents "field BETWEEN lower AND upper".

func (*Cond) E 

func (c *Cond) E(field string, value interface{}) string

E is an alias of Equal.

func (*Cond) EQ added in v1.23.0 

func (c *Cond) EQ(field string, value interface{}) string

EQ is an alias of Equal.

func (*Cond) Equal 

func (c *Cond) Equal(field string, value interface{}) string

Equal represents "field = value".

func (*Cond) Exists added in v1.21.0 

func (c *Cond) Exists(subquery interface{}) string

Exists represents "EXISTS (subquery)".

func (*Cond) G 

func (c *Cond) G(field string, value interface{}) string

G is an alias of GreaterThan.

func (*Cond) GE 

func (c *Cond) GE(field string, value interface{}) string

GE is an alias of GreaterEqualThan.

func (*Cond) GT added in v1.23.0 

func (c *Cond) GT(field string, value interface{}) string

GT is an alias of GreaterThan.

func (*Cond) GTE added in v1.23.0 

func (c *Cond) GTE(field string, value interface{}) string

GTE is an alias of GreaterEqualThan.

func (*Cond) GreaterEqualThan 

func (c *Cond) GreaterEqualThan(field string, value interface{}) string

GreaterEqualThan represents "field >= value".

func (*Cond) GreaterThan 

func (c *Cond) GreaterThan(field string, value interface{}) string

GreaterThan represents "field > value".

func (*Cond) In 

func (c *Cond) In(field string, value ...interface{}) string

In represents "field IN (value...)".

func (*Cond) IsNotNull 

func (c *Cond) IsNotNull(field string) string

IsNotNull represents "field IS NOT NULL".

func (*Cond) IsNull 

func (c *Cond) IsNull(field string) string

IsNull represents "field IS NULL".

func (*Cond) L 

func (c *Cond) L(field string, value interface{}) string

L is an alias of LessThan.

func (*Cond) LE 

func (c *Cond) LE(field string, value interface{}) string

LE is an alias of LessEqualThan.

func (*Cond) LT added in v1.23.0 

func (c *Cond) LT(field string, value interface{}) string

LT is an alias of LessThan.

func (*Cond) LTE added in v1.23.0 

func (c *Cond) LTE(field string, value interface{}) string

LTE is an alias of LessEqualThan.

func (*Cond) LessEqualThan 

func (c *Cond) LessEqualThan(field string, value interface{}) string

LessEqualThan represents "field <= value".

func (*Cond) LessThan 

func (c *Cond) LessThan(field string, value interface{}) string

LessThan represents "field < value".

func (*Cond) Like 

func (c *Cond) Like(field string, value interface{}) string

Like represents "field LIKE value".

func (*Cond) NE 

func (c *Cond) NE(field string, value interface{}) string

NE is an alias of NotEqual.

func (*Cond) NEQ added in v1.23.0 

func (c *Cond) NEQ(field string, value interface{}) string

NEQ is an alias of NotEqual.

func (*Cond) NotBetween 

func (c *Cond) NotBetween(field string, lower, upper interface{}) string

NotBetween represents "field NOT BETWEEN lower AND upper".

func (*Cond) NotEqual 

func (c *Cond) NotEqual(field string, value interface{}) string

NotEqual represents "field <> value".

func (*Cond) NotExists added in v1.21.0 

func (c *Cond) NotExists(subquery interface{}) string

NotExists represents "NOT EXISTS (subquery)".

func (*Cond) NotIn 

func (c *Cond) NotIn(field string, value ...interface{}) string

NotIn represents "field NOT IN (value...)".

func (*Cond) NotLike 

func (c *Cond) NotLike(field string, value interface{}) string

NotLike represents "field NOT LIKE value".

func (*Cond) Or 

func (c *Cond) Or(orExpr ...string) string

Or represents OR logic like "expr1 OR expr2 OR expr3".

func (*Cond) Some added in v1.21.0 

func (c *Cond) Some(field, op string, value ...interface{}) string

Some represents "field op SOME (value...)".

func (*Cond) Var 

func (c *Cond) Var(value interface{}) string

Var returns a placeholder for value.

type CreateTableBuilder added in v1.3.0 

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

CreateTableBuilder is a builder to build CREATE TABLE.

Example 
ctb := NewCreateTableBuilder()
ctb.CreateTable("demo.user").IfNotExists()
ctb.Define("id", "BIGINT(20)", "NOT NULL", "AUTO_INCREMENT", "PRIMARY KEY", `COMMENT "user id"`)
ctb.Define("name", "VARCHAR(255)", "NOT NULL", `COMMENT "user name"`)
ctb.Define("created_at", "DATETIME", "NOT NULL", `COMMENT "user create time"`)
ctb.Define("modified_at", "DATETIME", "NOT NULL", `COMMENT "user modify time"`)
ctb.Define("KEY", "idx_name_modified_at", "name, modified_at")
ctb.Option("DEFAULT CHARACTER SET", "utf8mb4")

fmt.Println(ctb)

Output:

CREATE TABLE IF NOT EXISTS demo.user (id BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT "user id", name VARCHAR(255) NOT NULL COMMENT "user name", created_at DATETIME NOT NULL COMMENT "user create time", modified_at DATETIME NOT NULL COMMENT "user modify time", KEY idx_name_modified_at name, modified_at) DEFAULT CHARACTER SET utf8mb4
Example (TempTable) 
ctb := NewCreateTableBuilder()
ctb.CreateTempTable("demo.user").IfNotExists()
ctb.Define("id", "BIGINT(20)", "NOT NULL", "AUTO_INCREMENT", "PRIMARY KEY", `COMMENT "user id"`)
ctb.Define("name", "VARCHAR(255)", "NOT NULL", `COMMENT "user name"`)
ctb.Define("created_at", "DATETIME", "NOT NULL", `COMMENT "user create time"`)
ctb.Define("modified_at", "DATETIME", "NOT NULL", `COMMENT "user modify time"`)
ctb.Define("KEY", "idx_name_modified_at", "name, modified_at")
ctb.Option("DEFAULT CHARACTER SET", "utf8mb4")

fmt.Println(ctb)

Output:

CREATE TEMPORARY TABLE IF NOT EXISTS demo.user (id BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT "user id", name VARCHAR(255) NOT NULL COMMENT "user name", created_at DATETIME NOT NULL COMMENT "user create time", modified_at DATETIME NOT NULL COMMENT "user modify time", KEY idx_name_modified_at name, modified_at) DEFAULT CHARACTER SET utf8mb4

func CreateTable added in v1.11.0 

func CreateTable(table string) *CreateTableBuilder

CreateTable sets the table name in CREATE TABLE.

Example 
sql := CreateTable("demo.user").IfNotExists().
	Define("id", "BIGINT(20)", "NOT NULL", "AUTO_INCREMENT", "PRIMARY KEY", `COMMENT "user id"`).
	String()

fmt.Println(sql)

Output:

CREATE TABLE IF NOT EXISTS demo.user (id BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT "user id")

func NewCreateTableBuilder added in v1.3.0 

func NewCreateTableBuilder() *CreateTableBuilder

NewCreateTableBuilder creates a new CREATE TABLE builder.

func (*CreateTableBuilder) Build added in v1.3.0 

func (ctb *CreateTableBuilder) Build() (sql string, args []interface{})

Build returns compiled CREATE TABLE string and args. They can be used in `DB#Query` of package `database/sql` directly.

func (*CreateTableBuilder) BuildWithFlavor added in v1.3.0 

func (ctb *CreateTableBuilder) BuildWithFlavor(flavor Flavor, initialArg ...interface{}) (sql string, args []interface{})

BuildWithFlavor returns compiled CREATE TABLE string and args with flavor and initial args. They can be used in `DB#Query` of package `database/sql` directly.

func (*CreateTableBuilder) CreateTable added in v1.3.0 

func (ctb *CreateTableBuilder) CreateTable(table string) *CreateTableBuilder

CreateTable sets the table name in CREATE TABLE.

func (*CreateTableBuilder) CreateTempTable added in v1.3.0 

func (ctb *CreateTableBuilder) CreateTempTable(table string) *CreateTableBuilder

CreateTempTable sets the table name and changes the verb of ctb to CREATE TEMPORARY TABLE.

func (*CreateTableBuilder) Define added in v1.3.0 

func (ctb *CreateTableBuilder) Define(def ...string) *CreateTableBuilder

Define adds definition of a column or index in CREATE TABLE.

func (*CreateTableBuilder) IfNotExists added in v1.3.0 

func (ctb *CreateTableBuilder) IfNotExists() *CreateTableBuilder

IfNotExists adds IF NOT EXISTS before table name in CREATE TABLE.

func (*CreateTableBuilder) NumDefine added in v1.25.0 

func (ctb *CreateTableBuilder) NumDefine() int

NumDefine returns the number of definitions in CREATE TABLE.

Example 
ctb := NewCreateTableBuilder()
ctb.CreateTable("demo.user").IfNotExists()
ctb.Define("id", "BIGINT(20)", "NOT NULL", "AUTO_INCREMENT", "PRIMARY KEY", `COMMENT "user id"`)
ctb.Define("name", "VARCHAR(255)", "NOT NULL", `COMMENT "user name"`)
ctb.Define("created_at", "DATETIME", "NOT NULL", `COMMENT "user create time"`)
ctb.Define("modified_at", "DATETIME", "NOT NULL", `COMMENT "user modify time"`)
ctb.Define("KEY", "idx_name_modified_at", "name, modified_at")
ctb.Option("DEFAULT CHARACTER SET", "utf8mb4")

// Count the number of definitions.
fmt.Println(ctb.NumDefine())

Output:

5

func (*CreateTableBuilder) Option added in v1.3.0 

func (ctb *CreateTableBuilder) Option(opt ...string) *CreateTableBuilder

Option adds a table option in CREATE TABLE.

func (*CreateTableBuilder) SQL added in v1.11.0 

func (ctb *CreateTableBuilder) SQL(sql string) *CreateTableBuilder

SQL adds an arbitrary sql to current position.

Example 
ctb := NewCreateTableBuilder()
ctb.SQL(`/* before */`)
ctb.CreateTempTable("demo.user").IfNotExists()
ctb.SQL("/* after create */")
ctb.Define("id", "BIGINT(20)", "NOT NULL", "AUTO_INCREMENT", "PRIMARY KEY", `COMMENT "user id"`)
ctb.Define("name", "VARCHAR(255)", "NOT NULL", `COMMENT "user name"`)
ctb.SQL("/* after define */")
ctb.Option("DEFAULT CHARACTER SET", "utf8mb4")
ctb.SQL(ctb.Var(Build("AS SELECT * FROM old.user WHERE name LIKE $?", "%Huan%")))

sql, args := ctb.Build()
fmt.Println(sql)
fmt.Println(args)

Output:

/* before */ CREATE TEMPORARY TABLE IF NOT EXISTS demo.user /* after create */ (id BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT "user id", name VARCHAR(255) NOT NULL COMMENT "user name") /* after define */ DEFAULT CHARACTER SET utf8mb4 AS SELECT * FROM old.user WHERE name LIKE ?
[%Huan%]

func (*CreateTableBuilder) SetFlavor added in v1.3.0 

func (ctb *CreateTableBuilder) SetFlavor(flavor Flavor) (old Flavor)

SetFlavor sets the flavor of compiled sql.

func (*CreateTableBuilder) String added in v1.3.0 

func (ctb *CreateTableBuilder) String() string

String returns the compiled INSERT string.

func (*CreateTableBuilder) Var added in v1.11.0 

func (ctb *CreateTableBuilder) Var(arg interface{}) string

Var returns a placeholder for value.

type DeleteBuilder 

type DeleteBuilder struct {
	*WhereClause
	Cond
	// contains filtered or unexported fields
}

DeleteBuilder is a builder to build DELETE.

Example 
db := NewDeleteBuilder()
db.DeleteFrom("demo.user")
db.Where(
	db.GreaterThan("id", 1234),
	db.Like("name", "%Du"),
	db.Or(
		db.IsNull("id_card"),
		db.In("status", 1, 2, 5),
	),
	"modified_at > created_at + "+db.Var(86400), // It's allowed to write arbitrary SQL.
)

sql, args := db.Build()
fmt.Println(sql)
fmt.Println(args)

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 DeleteFrom added in v1.11.0 

func DeleteFrom(table string) *DeleteBuilder

DeleteFrom sets table name in DELETE.

Example 
sql := DeleteFrom("demo.user").
	Where(
		"status = 1",
	).
	Limit(10).
	String()

fmt.Println(sql)

Output:

DELETE FROM demo.user WHERE status = 1 LIMIT 10

func NewDeleteBuilder 

func NewDeleteBuilder() *DeleteBuilder

NewDeleteBuilder creates a new DELETE builder.

func (*DeleteBuilder) AddWhereClause added in v1.27.0 

func (db *DeleteBuilder) AddWhereClause(whereClause *WhereClause) *DeleteBuilder

AddWhereClause adds all clauses in the whereClause to SELECT.

func (*DeleteBuilder) Asc added in v1.11.0 

func (db *DeleteBuilder) Asc() *DeleteBuilder

Asc sets order of ORDER BY to ASC.

func (*DeleteBuilder) Build 

func (db *DeleteBuilder) Build() (sql string, args []interface{})

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 ...interface{}) (sql string, args []interface{})

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) Desc added in v1.11.0 

func (db *DeleteBuilder) Desc() *DeleteBuilder

Desc sets order of ORDER BY to DESC.

func (*DeleteBuilder) Limit added in v1.11.0 

func (db *DeleteBuilder) Limit(limit int) *DeleteBuilder

Limit sets the LIMIT in DELETE.

func (*DeleteBuilder) OrderBy added in v1.11.0 

func (db *DeleteBuilder) OrderBy(col ...string) *DeleteBuilder

OrderBy sets columns of ORDER BY in DELETE.

func (*DeleteBuilder) SQL added in v1.11.0 

func (db *DeleteBuilder) SQL(sql string) *DeleteBuilder

SQL adds an arbitrary sql to current position.

Example 
db := NewDeleteBuilder()
db.SQL(`/* before */`)
db.DeleteFrom("demo.user")
db.SQL("PARTITION (p0)")
db.Where(
	db.GreaterThan("id", 1234),
)
db.SQL("/* after where */")
db.OrderBy("id")
db.SQL("/* after order by */")
db.Limit(10)
db.SQL("/* after limit */")

sql, args := db.Build()
fmt.Println(sql)
fmt.Println(args)

Output:

/* before */ DELETE FROM demo.user PARTITION (p0) WHERE id > ? /* after where */ ORDER BY id /* after order by */ LIMIT 10 /* after limit */
[1234]

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 FieldMapperFunc added in v1.12.0 

type FieldMapperFunc func(name string) string

FieldMapperFunc is a func to map struct field names to column names, which will be used in query as columns.

Example 
type Orders struct {
	ID            int64
	UserID        int64
	ProductName   string
	Status        int
	UserAddrLine1 string
	UserAddrLine2 string
	CreatedAt     time.Time
}

// Create a Struct for Orders.
orders := NewStruct(new(Orders))

// Set the default field mapper to snake_case mapper globally.
DefaultFieldMapper = SnakeCaseMapper

// Field names are converted to snake_case words.
sql1, _ := orders.SelectFrom("orders").Limit(10).Build()

fmt.Println(sql1)

// Changing the default field mapper will *NOT* affect field names in orders.
// Once field name conversion is done, they will not be changed again.
DefaultFieldMapper = SomeOtherMapper
sql2, _ := orders.SelectFrom("orders").Limit(10).Build()

fmt.Println(sql1 == sql2)

Output:

SELECT orders.id, orders.user_id, orders.product_name, orders.status, orders.user_addr_line1, orders.user_addr_line2, orders.created_at FROM orders LIMIT 10
true

type Flavor 

type Flavor int

Flavor is the flag to control the format of compiled sql.

Example 
// Create a flavored builder.
sb := PostgreSQL.NewSelectBuilder()
sb.Select("name").From("user").Where(
	sb.E("id", 1234),
	sb.G("rank", 3),
)
sql, args := sb.Build()

fmt.Println(sql)
fmt.Println(args)

Output:

SELECT name FROM user WHERE id = $1 AND rank > $2
[1234 3]

const (
	MySQL Flavor
	PostgreSQL
	SQLite
	SQLServer
	CQL
	ClickHouse
	Presto
	Oracle
	Informix
)

Supported flavors.

func (Flavor) Interpolate added in v1.4.0 

func (f Flavor) Interpolate(sql string, args []interface{}) (string, error)

Interpolate parses sql returned by `Args#Compile` or `Builder`, and interpolate args to replace placeholders in the sql.

If there are some args missing in sql, e.g. the number of placeholders are larger than len(args), returns ErrMissingArgs error.

Example (Cql) 
sb := CQL.NewSelectBuilder()
sb.Select("name").From("user").Where(
	sb.E("id", 1234),
	sb.E("name", "Charmy Liu"),
)
sql, args := sb.Build()
query, err := CQL.Interpolate(sql, args)

fmt.Println(query)
fmt.Println(err)

Output:

SELECT name FROM user WHERE id = 1234 AND name = 'Charmy Liu'
<nil>
Example (Infomix) 
sb := Informix.NewSelectBuilder()
sb.Select("name").From("user").Where(
	sb.NE("id", 1234),
	sb.E("name", "Charmy Liu"),
	sb.E("enabled", true),
)
sql, args := sb.Build()
query, err := Informix.Interpolate(sql, args)

fmt.Println(query)
fmt.Println(err)

Output:

SELECT name FROM user WHERE id <> 1234 AND name = 'Charmy Liu' AND enabled = TRUE
<nil>
Example (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)

Output:

SELECT name FROM user WHERE id <> 1234 AND name = 'Charmy Liu' AND desc LIKE '%mother\'s day%'
<nil>
Example (Oracle) 
sb := Oracle.NewSelectBuilder()
sb.Select("name").From("user").Where(
	sb.E("id", 1234),
	sb.E("name", "Charmy Liu"),
	sb.E("enabled", true),
)
sql, args := sb.Build()
query, err := Oracle.Interpolate(sql, args)

fmt.Println(query)
fmt.Println(err)

Output:

SELECT name FROM user WHERE id = 1234 AND name = 'Charmy Liu' AND enabled = 1
<nil>
Example (PostgreSQL) 
// Only the last `$1` is interpolated.
// Others are not interpolated as they are inside dollar quote (the `$$`).
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)

Output:


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(42);
<nil>
Example (SqlServer) 
sb := SQLServer.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 := SQLServer.Interpolate(sql, args)

fmt.Println(query)
fmt.Println(err)

Output:

SELECT name FROM user WHERE id <> 1234 AND name = N'Charmy Liu' AND desc LIKE N'%mother\'s day%'
<nil>
Example (Sqlite) 
sb := SQLite.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 := SQLite.Interpolate(sql, args)

fmt.Println(query)
fmt.Println(err)

Output:

SELECT name FROM user WHERE id <> 1234 AND name = 'Charmy Liu' AND desc LIKE '%mother\'s day%'
<nil>

func (Flavor) NewCreateTableBuilder added in v1.3.0 

func (f Flavor) NewCreateTableBuilder() *CreateTableBuilder

NewCreateTableBuilder creates a new CREATE TABLE builder with flavor.

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) NewUnionBuilder added in v1.11.0 

func (f Flavor) NewUnionBuilder() *UnionBuilder

NewUnionBuilder creates a new UNION builder with flavor.

func (Flavor) NewUpdateBuilder 

func (f Flavor) NewUpdateBuilder() *UpdateBuilder

NewUpdateBuilder creates a new UPDATE builder with flavor.

func (Flavor) PrepareInsertIgnore added in v1.16.0 

func (f Flavor) PrepareInsertIgnore(table string, ib *InsertBuilder)

PrepareInsertIgnore prepares the insert builder to build insert ignore SQL statement based on the sql flavor

func (Flavor) Quote 

func (f Flavor) Quote(name string) string

Quote adds quote for name to make sure the name can be used safely as table name or field name.

  • For MySQL, use back quote (`) to quote name;
  • For PostgreSQL, SQL Server and SQLite, use double quote (") to quote name.

func (Flavor) String 

func (f Flavor) String() string

String returns the name of f.

type GetAliasFunc added in v1.23.0 

type GetAliasFunc func(field *reflect.StructField) (alias string, dbtag string)

GetAliasFunc is a func to get alias and dbtag

type InsertBuilder 

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

InsertBuilder is a builder to build INSERT.

Example 
ib := NewInsertBuilder()
ib.InsertInto("demo.user")
ib.Cols("id", "name", "status", "created_at", "updated_at")
ib.Values(1, "Huan Du", 1, Raw("UNIX_TIMESTAMP(NOW())"))
ib.Values(2, "Charmy Liu", 1, 1234567890)

sql, args := ib.Build()
fmt.Println(sql)
fmt.Println(args)

Output:

INSERT INTO demo.user (id, name, status, created_at, updated_at) VALUES (?, ?, ?, UNIX_TIMESTAMP(NOW())), (?, ?, ?, ?)
[1 Huan Du 1 2 Charmy Liu 1 1234567890]
Example (FlavorOracle) 
ib := Oracle.NewInsertBuilder()
ib.InsertInto("demo.user")
ib.Cols("id", "name", "status")
ib.Values(1, "Huan Du", 1)
ib.Values(2, "Charmy Liu", 1)

sql, args := ib.Build()
fmt.Println(sql)
fmt.Println(args)

Output:

INSERT ALL INTO demo.user (id, name, status) VALUES (:1, :2, :3) INTO demo.user (id, name, status) VALUES (:4, :5, :6) SELECT 1 from DUAL
[1 Huan Du 1 2 Charmy Liu 1]
Example (InsertIgnore) 
ib := NewInsertBuilder()
ib.InsertIgnoreInto("demo.user")
ib.Cols("id", "name", "status", "created_at", "updated_at")
ib.Values(1, "Huan Du", 1, Raw("UNIX_TIMESTAMP(NOW())"))
ib.Values(2, "Charmy Liu", 1, 1234567890)

sql, args := ib.Build()
fmt.Println(sql)
fmt.Println(args)

Output:

INSERT IGNORE INTO demo.user (id, name, status, created_at, updated_at) VALUES (?, ?, ?, UNIX_TIMESTAMP(NOW())), (?, ?, ?, ?)
[1 Huan Du 1 2 Charmy Liu 1 1234567890]
Example (InsertIgnore_clickhouse) 
ib := ClickHouse.NewInsertBuilder()
ib.InsertIgnoreInto("demo.user")
ib.Cols("id", "name", "status", "created_at")
ib.Values(1, "Huan Du", 1, Raw("UNIX_TIMESTAMP(NOW())"))
ib.Values(2, "Charmy Liu", 1, 1234567890)

sql, args := ib.Build()
fmt.Println(sql)
fmt.Println(args)

Output:

INSERT INTO demo.user (id, name, status, created_at) VALUES (?, ?, ?, UNIX_TIMESTAMP(NOW())), (?, ?, ?, ?)
[1 Huan Du 1 2 Charmy Liu 1 1234567890]
Example (InsertIgnore_postgres) 
ib := PostgreSQL.NewInsertBuilder()
ib.InsertIgnoreInto("demo.user")
ib.Cols("id", "name", "status", "created_at")
ib.Values(1, "Huan Du", 1, Raw("UNIX_TIMESTAMP(NOW())"))
ib.Values(2, "Charmy Liu", 1, 1234567890)

sql, args := ib.Build()
fmt.Println(sql)
fmt.Println(args)

Output:

INSERT INTO demo.user (id, name, status, created_at) VALUES ($1, $2, $3, UNIX_TIMESTAMP(NOW())), ($4, $5, $6, $7) ON CONFLICT DO NOTHING
[1 Huan Du 1 2 Charmy Liu 1 1234567890]
Example (InsertIgnore_sqlite) 
ib := SQLite.NewInsertBuilder()
ib.InsertIgnoreInto("demo.user")
ib.Cols("id", "name", "status", "created_at")
ib.Values(1, "Huan Du", 1, Raw("UNIX_TIMESTAMP(NOW())"))
ib.Values(2, "Charmy Liu", 1, 1234567890)

sql, args := ib.Build()
fmt.Println(sql)
fmt.Println(args)

Output:

INSERT OR IGNORE INTO demo.user (id, name, status, created_at) VALUES (?, ?, ?, UNIX_TIMESTAMP(NOW())), (?, ?, ?, ?)
[1 Huan Du 1 2 Charmy Liu 1 1234567890]
Example (ReplaceInto) 
ib := NewInsertBuilder()
ib.ReplaceInto("demo.user")
ib.Cols("id", "name", "status", "created_at", "updated_at")
ib.Values(1, "Huan Du", 1, Raw("UNIX_TIMESTAMP(NOW())"))
ib.Values(2, "Charmy Liu", 1, 1234567890)

sql, args := ib.Build()
fmt.Println(sql)
fmt.Println(args)

Output:

REPLACE INTO demo.user (id, name, status, created_at, updated_at) VALUES (?, ?, ?, UNIX_TIMESTAMP(NOW())), (?, ?, ?, ?)
[1 Huan Du 1 2 Charmy Liu 1 1234567890]
Example (SubSelect) 
ib := NewInsertBuilder()
ib.InsertInto("demo.user")
ib.Cols("id", "name")
sb := ib.Select("id", "name").From("demo.test")
sb.Where(sb.EQ("id", 1))

sql, args := ib.Build()
fmt.Println(sql)
fmt.Println(args)

Output:

INSERT INTO demo.user (id, name) SELECT id, name FROM demo.test WHERE id = ?
[1]
Example (SubSelect_informix) 
ib := Informix.NewInsertBuilder()
ib.InsertInto("demo.user")
ib.Cols("id", "name")
sb := ib.Select("id", "name").From("demo.test")
sb.Where(sb.EQ("id", 1))

sql, args := ib.Build()
fmt.Println(sql)
fmt.Println(args)

Output:

INSERT INTO demo.user (id, name) SELECT id, name FROM demo.test WHERE id = ?
[1]
Example (SubSelect_oracle) 
ib := Oracle.NewInsertBuilder()
ib.InsertInto("demo.user")
ib.Cols("id", "name")
sb := ib.Select("id", "name").From("demo.test")
sb.Where(sb.EQ("id", 1))

sql, args := ib.Build()
fmt.Println(sql)
fmt.Println(args)

Output:

INSERT INTO demo.user (id, name) SELECT id, name FROM demo.test WHERE id = :1
[1]

func InsertIgnoreInto added in v1.11.0 

func InsertIgnoreInto(table string) *InsertBuilder

InsertIgnoreInto sets table name in INSERT IGNORE.

Example 
sql, args := InsertIgnoreInto("demo.user").
	Cols("id", "name", "status").
	Values(4, "Sample", 2).
	Build()

fmt.Println(sql)
fmt.Println(args)

Output:

INSERT IGNORE INTO demo.user (id, name, status) VALUES (?, ?, ?)
[4 Sample 2]

func InsertInto added in v1.11.0 

func InsertInto(table string) *InsertBuilder

InsertInto sets table name in INSERT.

Example 
sql, args := InsertInto("demo.user").
	Cols("id", "name", "status").
	Values(4, "Sample", 2).
	Build()

fmt.Println(sql)
fmt.Println(args)

Output:

INSERT INTO demo.user (id, name, status) VALUES (?, ?, ?)
[4 Sample 2]

func NewInsertBuilder 

func NewInsertBuilder() *InsertBuilder

NewInsertBuilder creates a new INSERT builder.

func ReplaceInto added in v1.11.0 

func ReplaceInto(table string) *InsertBuilder

ReplaceInto sets table name and changes the verb of ib to REPLACE. REPLACE INTO is a MySQL extension to the SQL standard.

Example 
sql, args := ReplaceInto("demo.user").
	Cols("id", "name", "status").
	Values(4, "Sample", 2).
	Build()

fmt.Println(sql)
fmt.Println(args)

Output:

REPLACE INTO demo.user (id, name, status) VALUES (?, ?, ?)
[4 Sample 2]

func (*InsertBuilder) Build 

func (ib *InsertBuilder) Build() (sql string, args []interface{})

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 ...interface{}) (sql string, args []interface{})

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) InsertIgnoreInto added in v1.5.0 

func (ib *InsertBuilder) InsertIgnoreInto(table string) *InsertBuilder

InsertIgnoreInto sets table name in INSERT IGNORE.

func (*InsertBuilder) InsertInto 

func (ib *InsertBuilder) InsertInto(table string) *InsertBuilder

InsertInto sets table name in INSERT.

func (*InsertBuilder) NumValue added in v1.25.0 

func (ib *InsertBuilder) NumValue() int

NumValue returns the number of values to insert.

Example 
ib := NewInsertBuilder()
ib.InsertInto("demo.user")
ib.Cols("id", "name")
ib.Values(1, "Huan Du")
ib.Values(2, "Charmy Liu")

// Count the number of values.
fmt.Println(ib.NumValue())

Output:

2

func (*InsertBuilder) ReplaceInto added in v1.3.0 

func (ib *InsertBuilder) ReplaceInto(table string) *InsertBuilder

ReplaceInto sets table name and changes the verb of ib to REPLACE. REPLACE INTO is a MySQL extension to the SQL standard.

func (*InsertBuilder) SQL added in v1.11.0 

func (ib *InsertBuilder) SQL(sql string) *InsertBuilder

SQL adds an arbitrary sql to current position.

Example 
ib := NewInsertBuilder()
ib.SQL("/* before */")
ib.InsertInto("demo.user")
ib.SQL("PARTITION (p0)")
ib.Cols("id", "name", "status", "created_at")
ib.SQL("/* after cols */")
ib.Values(3, "Shawn Du", 1, 1234567890)
ib.SQL(ib.Var(Build("ON DUPLICATE KEY UPDATE status = $?", 1)))

sql, args := ib.Build()
fmt.Println(sql)
fmt.Println(args)

Output:

/* before */ INSERT INTO demo.user PARTITION (p0) (id, name, status, created_at) /* after cols */ VALUES (?, ?, ?, ?) ON DUPLICATE KEY UPDATE status = ?
[3 Shawn Du 1 1234567890 1]

func (*InsertBuilder) Select added in v1.24.0 

func (isb *InsertBuilder) Select(col ...string) *SelectBuilder

Select returns a new SelectBuilder to build a SELECT statement inside the INSERT INTO.

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

func (*InsertBuilder) Values 

func (ib *InsertBuilder) Values(value ...interface{}) *InsertBuilder

Values adds a list of values for a row in INSERT.

func (*InsertBuilder) Var added in v1.11.0 

func (ib *InsertBuilder) Var(arg interface{}) string

Var returns a placeholder for value.

type JoinOption added in v1.1.0 

type JoinOption string

JoinOption is the option in JOIN. 

const (
	FullJoin       JoinOption = "FULL"
	FullOuterJoin  JoinOption = "FULL OUTER"
	InnerJoin      JoinOption = "INNER"
	LeftJoin       JoinOption = "LEFT"
	LeftOuterJoin  JoinOption = "LEFT OUTER"
	RightJoin      JoinOption = "RIGHT"
	RightOuterJoin JoinOption = "RIGHT OUTER"
)

Join options.

type SelectBuilder 

type SelectBuilder struct {
	*WhereClause
	Cond
	// contains filtered or unexported fields
}

SelectBuilder is a builder to build SELECT.

Example 
sb := NewSelectBuilder()
sb.Distinct().Select("id", "name", sb.As("COUNT(*)", "t"))
sb.From("demo.user")
sb.Where(
	sb.GreaterThan("id", 1234),
	sb.Like("name", "%Du"),
	sb.Or(
		sb.IsNull("id_card"),
		sb.In("status", 1, 2, 5),
	),
	sb.NotIn(
		"id",
		NewSelectBuilder().Select("id").From("banned"),
	), // Nested SELECT.
	"modified_at > created_at + "+sb.Var(86400), // It's allowed to write arbitrary SQL.
)
sb.GroupBy("status").Having(sb.NotIn("status", 4, 5))
sb.OrderBy("modified_at").Asc()
sb.Limit(10).Offset(5)

s, args := sb.Build()
fmt.Println(s)
fmt.Println(args)

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) 
sb := NewSelectBuilder()
innerSb := NewSelectBuilder()

// Named arguments are supported.
start := sql.Named("start", 1234567890)
end := sql.Named("end", 1234599999)
level := sql.Named("level", 20)

sb.Select("id", "name")
sb.From(
	sb.BuilderAs(innerSb, "user"),
)
sb.Where(
	sb.In("status", Flatten([]int{1, 2, 3})...),
	sb.Between("created_at", start, end),
)
sb.OrderBy("modified_at").Desc()

innerSb.Select("*")
innerSb.From("banned")
innerSb.Where(
	innerSb.GreaterThan("level", level),
	innerSb.LessEqualThan("updated_at", end),
	innerSb.NotIn("name", Flatten([]string{"Huan Du", "Charmy Liu"})...),
)

s, args := sb.Build()
fmt.Println(s)
fmt.Println(args)

Output:

SELECT id, name FROM (SELECT * FROM banned WHERE level > @level AND updated_at <= @end AND 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 {{} level 20} {{} end 1234599999} {{} start 1234567890}]
Example (CustomSELECT)

Example for issue #115. 

sb := NewSelectBuilder()

// Set a custom SELECT clause.
sb.SQL("SELECT id, name FROM user").Where(
	sb.In("id", 1, 2, 3),
)

s, args := sb.Build()
fmt.Println(s)
fmt.Println(args)

Output:

SELECT id, name FROM user WHERE id IN (?, ?, ?)
[1 2 3]
Example (Join) 
sb := NewSelectBuilder()
sb.Select("u.id", "u.name", "c.type", "p.nickname")
sb.From("user u")
sb.Join("contract c",
	"u.id = c.user_id",
	sb.In("c.status", 1, 2, 5),
)
sb.JoinWithOption(RightOuterJoin, "person p",
	"u.id = p.user_id",
	sb.Like("p.surname", "%Du"),
)
sb.Where(
	"u.modified_at > u.created_at + " + sb.Var(86400), // It's allowed to write arbitrary SQL.
)

sql, args := sb.Build()
fmt.Println(sql)
fmt.Println(args)

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]
Example (Limit_offset) 
flavors := []Flavor{MySQL, PostgreSQL, SQLite, SQLServer, CQL, ClickHouse, Presto, Oracle, Informix}
results := make([][]string, len(flavors))
sb := NewSelectBuilder()
saveResults := func() {
	for i, f := range flavors {
		s, _ := sb.BuildWithFlavor(f)
		results[i] = append(results[i], s)
	}
}

sb.Select("*")
sb.From("user")

// Case #1: limit < 0 and offset < 0
//
// All: No limit or offset in query.
sb.Limit(-1)
sb.Offset(-1)
saveResults()

// Case #2: limit < 0 and offset >= 0
//
// MySQL and SQLite: Ignore offset if the limit is not set.
// PostgreSQL: Offset can be set without limit.
// SQLServer: Offset can be set without limit.
// CQL: Ignore offset.
// Oracle: Offset can be set without limit.
sb.Limit(-1)
sb.Offset(0)
saveResults()

// Case #3: limit >= 0 and offset >= 0
//
// CQL: Ignore offset.
// All others: Set both limit and offset.
sb.Limit(1)
sb.Offset(0)
saveResults()

// Case #4: limit >= 0 and offset < 0
//
// All: Set limit in query.
sb.Limit(1)
sb.Offset(-1)
saveResults()

// Case #5: limit >= 0 and offset >= 0 order by id
//
// CQL: Ignore offset.
// All others: Set both limit and offset.
sb.Limit(1)
sb.Offset(1)
sb.OrderBy("id")
saveResults()

for i, result := range results {
	fmt.Println()
	fmt.Println(flavors[i])

	for n, s := range result {
		fmt.Printf("#%d: %s\n", n+1, s)
	}
}

Output:


MySQL
#1: SELECT * FROM user
#2: SELECT * FROM user
#3: SELECT * FROM user LIMIT 1 OFFSET 0
#4: SELECT * FROM user LIMIT 1
#5: SELECT * FROM user ORDER BY id LIMIT 1 OFFSET 1

PostgreSQL
#1: SELECT * FROM user
#2: SELECT * FROM user OFFSET 0
#3: SELECT * FROM user LIMIT 1 OFFSET 0
#4: SELECT * FROM user LIMIT 1
#5: SELECT * FROM user ORDER BY id LIMIT 1 OFFSET 1

SQLite
#1: SELECT * FROM user
#2: SELECT * FROM user
#3: SELECT * FROM user LIMIT 1 OFFSET 0
#4: SELECT * FROM user LIMIT 1
#5: SELECT * FROM user ORDER BY id LIMIT 1 OFFSET 1

SQLServer
#1: SELECT * FROM user
#2: SELECT * FROM user ORDER BY 1 OFFSET 0 ROWS
#3: SELECT * FROM user ORDER BY 1 OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY
#4: SELECT * FROM user ORDER BY 1 OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY
#5: SELECT * FROM user ORDER BY id OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY

CQL
#1: SELECT * FROM user
#2: SELECT * FROM user
#3: SELECT * FROM user LIMIT 1
#4: SELECT * FROM user LIMIT 1
#5: SELECT * FROM user ORDER BY id LIMIT 1

ClickHouse
#1: SELECT * FROM user
#2: SELECT * FROM user
#3: SELECT * FROM user LIMIT 1 OFFSET 0
#4: SELECT * FROM user LIMIT 1
#5: SELECT * FROM user ORDER BY id LIMIT 1 OFFSET 1

Presto
#1: SELECT * FROM user
#2: SELECT * FROM user OFFSET 0
#3: SELECT * FROM user LIMIT 1 OFFSET 0
#4: SELECT * FROM user LIMIT 1
#5: SELECT * FROM user ORDER BY id LIMIT 1 OFFSET 1

Oracle
#1: SELECT * FROM user
#2: SELECT * FROM ( SELECT ROWNUM r, * FROM ( SELECT * FROM user ) user ) WHERE r >= 1
#3: SELECT * FROM ( SELECT ROWNUM r, * FROM ( SELECT * FROM user ) user ) WHERE r BETWEEN 1 AND 1
#4: SELECT * FROM ( SELECT ROWNUM r, * FROM ( SELECT * FROM user ) user ) WHERE r BETWEEN 1 AND 1
#5: SELECT * FROM ( SELECT ROWNUM r, * FROM ( SELECT * FROM user ORDER BY id ) user ) WHERE r BETWEEN 2 AND 2

Informix
#1: SELECT * FROM user
#2: SELECT * FROM user
#3: SELECT * FROM user SKIP 0 FIRST 1
#4: SELECT * FROM user FIRST 1
#5: SELECT * FROM user ORDER BY id SKIP 1 FIRST 1
Example (VarInCols) 
// Column name may contain some characters, e.g. the $ sign, which have special meanings in builders.
// It's recommended to call Escape() or EscapeAll() to escape the name.

sb := NewSelectBuilder()
v := sb.Var("foo")
sb.Select(Escape("colHasA$Sign"), v)
sb.From("table")

s, args := sb.Build()
fmt.Println(s)
fmt.Println(args)

Output:

SELECT colHasA$Sign, ? FROM table
[foo]

func NewSelectBuilder 

func NewSelectBuilder() *SelectBuilder

NewSelectBuilder creates a new SELECT builder.

func Select added in v1.11.0 

func Select(col ...string) *SelectBuilder

Select sets columns in SELECT.

Example 
// Build a SQL to create a HIVE table.
s := CreateTable("users").
	SQL("PARTITION BY (year)").
	SQL("AS").
	SQL(
		Select("columns[0] id", "columns[1] name", "columns[2] year").
			From("`all-users.csv`").
			Limit(100).
			String(),
	).
	String()

fmt.Println(s)

Output:

CREATE TABLE users PARTITION BY (year) AS SELECT columns[0] id, columns[1] name, columns[2] year FROM `all-users.csv` LIMIT 100

func (*SelectBuilder) AddWhereClause added in v1.27.0 

func (sb *SelectBuilder) AddWhereClause(whereClause *WhereClause) *SelectBuilder

AddWhereClause adds all clauses in the whereClause to SELECT.

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 []interface{})

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 ...interface{}) (sql string, args []interface{})

BuildWithFlavor returns compiled SELECT string and args with flavor and initial args. They can be used in `DB#Query` of package `database/sql` directly.

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) ForShare added in v1.11.0 

func (sb *SelectBuilder) ForShare() *SelectBuilder

ForShare adds FOR SHARE at the end of SELECT statement.

func (*SelectBuilder) ForUpdate added in v1.11.0 

func (sb *SelectBuilder) ForUpdate() *SelectBuilder

ForUpdate adds FOR UPDATE at the end of SELECT statement.

Example 
sb := newSelectBuilder()
sb.Select("*").From("user").Where(
	sb.Equal("id", 1234),
).ForUpdate()

sql, args := sb.Build()
fmt.Println(sql)
fmt.Println(args)

Output:

SELECT * FROM user WHERE id = ? FOR UPDATE
[1234]

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 added in v1.1.0 

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 added in v1.1.0 

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.

  • FullJoin: FULL JOIN
  • FullOuterJoin: FULL OUTER JOIN
  • InnerJoin: INNER JOIN
  • 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) NumCol added in v1.25.0 

func (sb *SelectBuilder) NumCol() int

NumCol returns the number of columns to select.

Example 
sb := NewSelectBuilder()
sb.Select("id", "name", "created_at")
sb.From("demo.user")
sb.Where(
	sb.GreaterThan("id", 1234),
)

// Count the number of columns.
fmt.Println(sb.NumCol())

Output:

3

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) SQL added in v1.11.0 

func (sb *SelectBuilder) SQL(sql string) *SelectBuilder

SQL adds an arbitrary sql to current position.

Example 
sb := NewSelectBuilder()
sb.SQL("/* before */")
sb.Select("u.id", "u.name", "c.type", "p.nickname")
sb.SQL("/* after select */")
sb.From("user u")
sb.SQL("/* after from */")
sb.Join("contract c",
	"u.id = c.user_id",
)
sb.JoinWithOption(RightOuterJoin, "person p",
	"u.id = p.user_id",
)
sb.SQL("/* after join */")
sb.Where(
	"u.modified_at > u.created_at",
)
sb.SQL("/* after where */")
sb.OrderBy("id")
sb.SQL("/* after order by */")
sb.Limit(10)
sb.SQL("/* after limit */")
sb.ForShare()
sb.SQL("/* after for */")

s := sb.String()
fmt.Println(s)

Output:

/* before */ SELECT u.id, u.name, c.type, p.nickname /* after select */ FROM user u /* after from */ JOIN contract c ON u.id = c.user_id RIGHT OUTER JOIN person p ON u.id = p.user_id /* after join */ WHERE u.modified_at > u.created_at /* after where */ ORDER BY id /* after order by */ LIMIT 10 /* after limit */ FOR SHARE /* after for */

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) 
// Suppose we defined following type for user db.
type User struct {
	ID     int64  `db:"id" fieldtag:"pk"`
	Name   string `db:"name"`
	Status int    `db:"status"`
}

// Parse user struct. The userStruct can be a global variable.
// It's guraanteed to be thread-safe.
var userStruct = NewStruct(new(User))

// Prepare DELETE query.
user := &User{
	ID:     1234,
	Name:   "Huan Du",
	Status: 1,
}
b := userStruct.DeleteFrom("user")
b.Where(b.Equal("id", user.ID))

// Execute the query.
sql, args := b.Build()
orderDB.Exec(sql, args...)

fmt.Println(sql)
fmt.Println(args)

Output:

DELETE FROM user WHERE id = ?
[1234]
Example (BuildINSERT) 
// Suppose we defined following type for user db.
type User struct {
	ID     int64  `db:"id" fieldtag:"pk"`
	Name   string `db:"name"`
	Status int    `db:"status"`
}

// Parse user struct. The userStruct can be a global variable.
// It's guraanteed to be thread-safe.
var userStruct = NewStruct(new(User))

// Prepare INSERT query.
// Suppose that user id is generated by database.
user := &User{
	Name:   "Huan Du",
	Status: 1,
}
ib := userStruct.WithoutTag("pk").InsertInto("user", user)

// Execute the query.
sql, args := ib.Build()
orderDB.Exec(sql, args...)

fmt.Println(sql)
fmt.Println(args)

Output:

INSERT INTO user (name, status) VALUES (?, ?)
[Huan Du 1]
Example (BuildUPDATE) 
// Suppose we defined following type for user db.
type User struct {
	ID     int64  `db:"id" fieldtag:"pk"`
	Name   string `db:"name"`
	Status int    `db:"status"`
}

// Parse user struct. The userStruct can be a global variable.
// It's guraanteed to be thread-safe.
var userStruct = NewStruct(new(User))

// Prepare UPDATE query.
// We should not update the primary key field.
user := &User{
	ID:     1234,
	Name:   "Huan Du",
	Status: 1,
}
ub := userStruct.WithoutTag("pk").Update("user", user)
ub.Where(ub.Equal("id", user.ID))

// Execute the query.
sql, args := ub.Build()
orderDB.Exec(sql, args...)

fmt.Println(sql)
fmt.Println(args)

Output:

UPDATE user SET name = ?, status = ? WHERE id = ?
[Huan Du 1 1234]
Example (ForCQL) 
// Suppose we defined following type for user db.
type User struct {
	ID     int64  `db:"id" fieldtag:"pk"`
	Name   string `db:"name"`
	Status int    `db:"status"`
}

// Parse user struct. The userStruct can be a global variable.
// It's guraanteed to be thread-safe.
userStruct := NewStruct(new(User)).For(CQL)

sb := userStruct.SelectFrom("user")
sb.Where(sb.Equal("id", 1234))
sql, args := sb.Build()

fmt.Println(sql)
fmt.Println(args)

Output:

SELECT id, name, status FROM user WHERE id = ?
[1234]
Example (ForPostgreSQL) 
// Suppose we defined following type for user db.
type User struct {
	ID     int64  `db:"id" fieldtag:"pk"`
	Name   string `db:"name"`
	Status int    `db:"status"`
}

// Parse user struct. The userStruct can be a global variable.
// It's guraanteed to be thread-safe.
var userStruct = NewStruct(new(User)).For(PostgreSQL)

sb := userStruct.SelectFrom("user")
sb.Where(sb.Equal("id", 1234))
sql, args := sb.Build()

fmt.Println(sql)
fmt.Println(args)

Output:

SELECT user.id, user.name, user.status FROM user WHERE id = $1
[1234]
Example (UseStructAsORM) 
// Suppose we defined following type for user db.
type User struct {
	ID     int64  `db:"id" fieldtag:"pk"`
	Name   string `db:"name"`
	Status int    `db:"status"`
}

// Parse user struct. The userStruct can be a global variable.
// It's guraanteed to be thread-safe.
var userStruct = NewStruct(new(User))

// Prepare SELECT query.
sb := userStruct.SelectFrom("user")
sb.Where(sb.Equal("id", 1234))

// Execute the query.
sql, args := sb.Build()
rows, _ := userDB.Query(sql, args...)
defer func(rows testRows) {
	_ = rows.Close()
}(rows)

// Scan row data to user.
var user User
_ = rows.Scan(userStruct.Addr(&user)...)

fmt.Println(sql)
fmt.Println(args)
fmt.Printf("%#v", user)

Output:

SELECT user.id, user.name, user.status FROM user WHERE id = ?
[1234]
sqlbuilder.User{ID:1234, Name:"huandu", Status:1}

func NewStruct 

func NewStruct(structValue interface{}) *Struct

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

func (*Struct) Addr 

func (s *Struct) Addr(st interface{}) []interface{}

Addr takes address of all exported fields of the s from the st. The returned result can be used in `Row#Scan` directly.

func (*Struct) AddrForTag deprecated 

func (s *Struct) AddrForTag(tag string, st interface{}) []interface{}

AddrForTag takes address of all fields of the s tagged with tag from the st. The returned value can be used in `Row#Scan` directly.

If tag is not defined in s in advance, returns nil.

Deprecated: It's recommended to use s.WithTag(tag).Addr(...) instead of calling this method. The former one is more readable and can be chained with other methods.

func (*Struct) AddrWithCols 

func (s *Struct) AddrWithCols(cols []string, st interface{}) []interface{}

AddrWithCols takes address of all columns defined in cols from the st. The returned value can be used in `Row#Scan` directly.

func (*Struct) Columns added in v1.14.0 

func (s *Struct) Columns() []string

Columns returns column names of s for all exported struct fields.

func (*Struct) ColumnsForTag deprecated added in v1.14.0 

func (s *Struct) ColumnsForTag(tag string) (cols []string)

ColumnsForTag returns column names of the s tagged with tag.

Deprecated: It's recommended to use s.WithTag(tag).Columns(...) instead of calling this method. The former one is more readable and can be chained with other methods.

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) For 

func (s *Struct) For(flavor Flavor) *Struct

For sets the default flavor of s and returns a shadow copy of s. The original s.Flavor is not changed.

func (*Struct) ForeachRead added in v1.23.0 

func (s *Struct) ForeachRead(trans func(dbtag string, isQuoted bool, field reflect.StructField))

ForeachRead foreach tags.

func (*Struct) ForeachWrite added in v1.23.0 

func (s *Struct) ForeachWrite(trans func(dbtag string, isQuoted bool, field reflect.StructField))

ForeachWrite foreach tags.

func (*Struct) InsertIgnoreInto added in v1.6.0 

func (s *Struct) InsertIgnoreInto(table string, value ...interface{}) *InsertBuilder

InsertIgnoreInto creates a new `InsertBuilder` with table name using verb INSERT IGNORE INTO. By default, all exported fields of s are set as columns by calling `InsertBuilder#Cols`, and value is added as a list of values by calling `InsertBuilder#Values`.

InsertIgnoreInto never returns any error. If the type of any item in value is not expected, it will be ignored. If value is an empty slice, `InsertBuilder#Values` will not be called.

func (*Struct) InsertIgnoreIntoForTag deprecated added in v1.6.0 

func (s *Struct) InsertIgnoreIntoForTag(table string, tag string, value ...interface{}) *InsertBuilder

InsertIgnoreIntoForTag creates a new `InsertBuilder` with table name using verb INSERT IGNORE INTO. By default, exported fields tagged with tag are set as columns by calling `InsertBuilder#Cols`, and value is added as a list of values by calling `InsertBuilder#Values`.

InsertIgnoreIntoForTag never returns any error. If the type of any item in value is not expected, it will be ignored. If value is an empty slice, `InsertBuilder#Values` will not be called.

Deprecated: It's recommended to use s.WithTag(tag).InsertIgnoreInto(...) instead of calling this method. The former one is more readable and can be chained with other methods.

func (*Struct) InsertInto 

func (s *Struct) InsertInto(table string, value ...interface{}) *InsertBuilder

InsertInto creates a new `InsertBuilder` with table name using verb INSERT INTO. By default, all exported fields of s are set as columns by calling `InsertBuilder#Cols`, and value is added as a list of values by calling `InsertBuilder#Values`.

InsertInto never returns any error. If the type of any item in value is not expected, it will be ignored. If value is an empty slice, `InsertBuilder#Values` will not be called.

func (*Struct) InsertIntoForTag deprecated 

func (s *Struct) InsertIntoForTag(table string, tag string, value ...interface{}) *InsertBuilder

InsertIntoForTag creates a new `InsertBuilder` with table name using verb INSERT INTO. By default, exported fields tagged with tag are set as columns by calling `InsertBuilder#Cols`, and value is added as a list of values by calling `InsertBuilder#Values`.

InsertIntoForTag never returns any error. If the type of any item in value is not expected, it will be ignored. If value is an empty slice, `InsertBuilder#Values` will not be called.

Deprecated: It's recommended to use s.WithTag(tag).InsertInto(...) instead of calling this method. The former one is more readable and can be chained with other methods.

func (*Struct) ReplaceInto added in v1.6.0 

func (s *Struct) ReplaceInto(table string, value ...interface{}) *InsertBuilder

ReplaceInto creates a new `InsertBuilder` with table name using verb REPLACE INTO. By default, all exported fields of s are set as columns by calling `InsertBuilder#Cols`, and value is added as a list of values by calling `InsertBuilder#Values`.

ReplaceInto never returns any error. If the type of any item in value is not expected, it will be ignored. If value is an empty slice, `InsertBuilder#Values` will not be called.

func (*Struct) ReplaceIntoForTag deprecated added in v1.6.0 

func (s *Struct) ReplaceIntoForTag(table string, tag string, value ...interface{}) *InsertBuilder

ReplaceIntoForTag creates a new `InsertBuilder` with table name using verb REPLACE INTO. By default, exported fields tagged with tag are set as columns by calling `InsertBuilder#Cols`, and value is added as a list of values by calling `InsertBuilder#Values`.

ReplaceIntoForTag never returns any error. If the type of any item in value is not expected, it will be ignored. If value is an empty slice, `InsertBuilder#Values` will not be called.

Deprecated: It's recommended to use s.WithTag(tag).ReplaceInto(...) instead of calling this method. The former one is more readable and can be chained with other methods.

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 deprecated 

func (s *Struct) SelectFromForTag(table string, tag string) (sb *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.

Deprecated: It's recommended to use s.WithTag(tag).SelectFrom(...) instead of calling this method. The former one is more readable and can be chained with other methods.

func (*Struct) Update 

func (s *Struct) Update(table string, value interface{}) *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 deprecated 

func (s *Struct) UpdateForTag(table string, tag string, value interface{}) *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.

Deprecated: It's recommended to use s.WithTag(tag).Update(...) instead of calling this method. The former one is more readable and can be chained with other methods.

func (*Struct) Values added in v1.14.0 

func (s *Struct) Values(st interface{}) []interface{}

Values returns a shadow copy of all exported fields in st.

func (*Struct) ValuesForTag deprecated added in v1.14.0 

func (s *Struct) ValuesForTag(tag string, value interface{}) (values []interface{})

ValuesForTag returns a shadow copy of all fields tagged with tag in st.

Deprecated: It's recommended to use s.WithTag(tag).Values(...) instead of calling this method. The former one is more readable and can be chained with other methods.

func (*Struct) WithFieldMapper added in v1.12.0 

func (s *Struct) WithFieldMapper(mapper FieldMapperFunc) *Struct

WithFieldMapper returns a new Struct based on s with custom field mapper. The original s is not changed.

func (*Struct) WithTag added in v1.15.1 

func (s *Struct) WithTag(tags ...string) *Struct

WithTag sets included tag(s) for all builder methods. For instance, calling s.WithTag("tag").SelectFrom("t") is to select all fields tagged with "tag" from table "t".

If multiple tags are provided, fields tagged with any of them are included. That is, s.WithTag("tag1", "tag2").SelectFrom("t") is to select all fields tagged with "tag1" or "tag2" from table "t".

Example 
// Suppose we defined following type for an order.
type Order struct {
	ID         int64  `db:"id"`
	State      State  `db:"state" fieldtag:"paid"`
	SkuID      int64  `db:"sku_id"`
	UserID     int64  `db:"user_id"`
	Price      int64  `db:"price" fieldtag:"update"`
	Discount   int64  `db:"discount" fieldtag:"update"`
	Desc       string `db:"desc" fieldtag:"new,update" fieldopt:"withquote"`
	CreatedAt  int64  `db:"created_at"`
	ModifiedAt int64  `db:"modified_at" fieldtag:"update,paid"`
}

// The orderStruct is a global variable for Order type.
var orderStruct = NewStruct(new(Order))

// Create an order with all fields set.
createOrder := func(table string) {
	now := time.Now().Unix()
	order := &Order{
		ID:         1234,
		State:      OrderStateCreated,
		SkuID:      5678,
		UserID:     7527,
		Price:      1000,
		Discount:   0,
		Desc:       "Best goods",
		CreatedAt:  now,
		ModifiedAt: now,
	}
	b := orderStruct.InsertInto(table, &order)
	sql, args := b.Build()
	orderDB.Exec(sql, args)
	fmt.Println(sql)
}

// Update order only with price related fields, which is tagged with "update".
updatePrice := func(table string) {
	// Use tag "update" in all struct methods.
	st := orderStruct.WithTag("update")

	// Read order from database.
	var order Order
	sql, args := st.SelectFrom(table).Where("id = 1234").Build()
	rows, _ := orderDB.Query(sql, args...)
	defer func(rows testRows) {
		_ = rows.Close()
	}(rows)
	_ = rows.Scan(st.Addr(&order)...)
	fmt.Println(sql)

	// Discount for this user.
	// Use tag "update" to update necessary columns only.
	order.Discount += 100
	order.ModifiedAt = time.Now().Unix()

	// Save the order.
	b := st.Update(table, &order)
	b.Where(b.E("id", order.ID))
	sql, args = b.Build()
	orderDB.Exec(sql, args...)
	fmt.Println(sql)
}

// Update order only with payment related fields, which is tagged with "paid".
updateState := func(table string) {
	st := orderStruct.WithTag("paid")

	// Read order from database.
	var order Order
	sql, args := st.SelectFrom(table).Where("id = 1234").Build()
	rows, _ := orderDB.Query(sql, args...)
	defer func(rows testRows) {
		_ = rows.Close()
	}(rows)
	_ = rows.Scan(st.Addr(&order)...)
	fmt.Println(sql)

	// Update state to paid when user has paid for the order.
	// Use tag "paid" to update necessary columns only.
	if order.State != OrderStateCreated {
		// Report state error here.
		panic(order.State)
		// return
	}

	// Update order state.
	order.State = OrderStatePaid
	order.ModifiedAt = time.Now().Unix()

	// Save the order.
	b := st.Update(table, &order)
	b.Where(b.E("id", order.ID))
	sql, args = b.Build()
	orderDB.Exec(sql, args...)
	fmt.Println(sql)
}

table := "order"
createOrder(table)
updatePrice(table)
updateState(table)

Output:

INSERT INTO order (id, state, sku_id, user_id, price, discount, `desc`, created_at, modified_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
SELECT order.price, order.discount, order.`desc`, order.modified_at FROM order WHERE id = 1234
UPDATE order SET price = ?, discount = ?, `desc` = ?, modified_at = ? WHERE id = ?
SELECT order.state, order.modified_at FROM order WHERE id = 1234
UPDATE order SET state = ?, modified_at = ? WHERE id = ?

func (*Struct) WithoutTag added in v1.20.0 

func (s *Struct) WithoutTag(tags ...string) *Struct

WithoutTag sets excluded tag(s) for all builder methods. For instance, calling s.WithoutTag("tag").SelectFrom("t") is to select all fields except those tagged with "tag" from table "t".

If multiple tags are provided, fields tagged with any of them are excluded. That is, s.WithoutTag("tag1", "tag2").SelectFrom("t") is to exclude any field tagged with "tag1" or "tag2" from table "t".

Example 
// We can use WithoutTag to exclude fields with specific tag.
// It's useful when we want to update all fields except some fields.

type User struct {
	ID             int64     `db:"id" fieldtag:"pk"`
	FirstName      string    `db:"first_name"`
	LastName       string    `db:"last_name"`
	ModifiedAtTime time.Time `db:"modified_at_time"`
}

// The userStruct is a global variable for User type.
var userStruct = NewStruct(new(User))

// Update user with all fields except the user_id field which is tagged with "pk".
user := &User{
	FirstName:      "Huan",
	LastName:       "Du",
	ModifiedAtTime: time.Now(),
}
sql, _ := userStruct.WithoutTag("pk").Update("user", user).Where("id = 1234").Build()
fmt.Println(sql)

Output:

UPDATE user SET first_name = ?, last_name = ?, modified_at_time = ? WHERE id = 1234

type UnionBuilder added in v1.8.0 

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

UnionBuilder is a builder to build UNION.

func NewUnionBuilder added in v1.11.0 

func NewUnionBuilder() *UnionBuilder

NewUnionBuilder creates a new UNION builder.

func Union added in v1.8.0 

func Union(builders ...Builder) *UnionBuilder

Union unions all builders together using UNION operator.

Example 
sb1 := NewSelectBuilder()
sb1.Select("id", "name", "created_at")
sb1.From("demo.user")
sb1.Where(
	sb1.GreaterThan("id", 1234),
)

sb2 := newSelectBuilder()
sb2.Select("id", "avatar")
sb2.From("demo.user_profile")
sb2.Where(
	sb2.In("status", 1, 2, 5),
)

ub := Union(sb1, sb2)
ub.OrderBy("created_at").Desc()

sql, args := ub.Build()
fmt.Println(sql)
fmt.Println(args)

Output:

(SELECT id, name, created_at FROM demo.user WHERE id > ?) UNION (SELECT id, avatar FROM demo.user_profile WHERE status IN (?, ?, ?)) ORDER BY created_at DESC
[1234 1 2 5]

func UnionAll added in v1.8.0 

func UnionAll(builders ...Builder) *UnionBuilder

UnionAll unions all builders together using UNION ALL operator.

Example 
sb := NewSelectBuilder()
sb.Select("id", "name", "created_at")
sb.From("demo.user")
sb.Where(
	sb.GreaterThan("id", 1234),
)

ub := UnionAll(sb, Build("TABLE demo.user_profile"))
ub.OrderBy("created_at").Asc()
ub.Limit(100).Offset(5)

sql, args := ub.Build()
fmt.Println(sql)
fmt.Println(args)

Output:

(SELECT id, name, created_at FROM demo.user WHERE id > ?) UNION ALL (TABLE demo.user_profile) ORDER BY created_at ASC LIMIT 100 OFFSET 5
[1234]

func (*UnionBuilder) Asc added in v1.8.0 

func (ub *UnionBuilder) Asc() *UnionBuilder

Asc sets order of ORDER BY to ASC.

func (*UnionBuilder) Build added in v1.8.0 

func (ub *UnionBuilder) Build() (sql string, args []interface{})

Build returns compiled SELECT string and args. They can be used in `DB#Query` of package `database/sql` directly.

func (*UnionBuilder) BuildWithFlavor added in v1.8.0 

func (ub *UnionBuilder) BuildWithFlavor(flavor Flavor, initialArg ...interface{}) (sql string, args []interface{})

BuildWithFlavor returns compiled SELECT string and args with flavor and initial args. They can be used in `DB#Query` of package `database/sql` directly.

func (*UnionBuilder) Desc added in v1.8.0 

func (ub *UnionBuilder) Desc() *UnionBuilder

Desc sets order of ORDER BY to DESC.

func (*UnionBuilder) Limit added in v1.8.0 

func (ub *UnionBuilder) Limit(limit int) *UnionBuilder

Limit sets the LIMIT in SELECT.

func (*UnionBuilder) Offset added in v1.8.0 

func (ub *UnionBuilder) Offset(offset int) *UnionBuilder

Offset sets the LIMIT offset in SELECT.

func (*UnionBuilder) OrderBy added in v1.8.0 

func (ub *UnionBuilder) OrderBy(col ...string) *UnionBuilder

OrderBy sets columns of ORDER BY in SELECT.

func (*UnionBuilder) SQL added in v1.11.0 

func (ub *UnionBuilder) SQL(sql string) *UnionBuilder

SQL adds an arbitrary sql to current position.

Example 
sb1 := NewSelectBuilder()
sb1.Select("id", "name", "created_at")
sb1.From("demo.user")

sb2 := newSelectBuilder()
sb2.Select("id", "avatar")
sb2.From("demo.user_profile")

ub := NewUnionBuilder()
ub.SQL("/* before */")
ub.Union(sb1, sb2)
ub.SQL("/* after union */")
ub.OrderBy("created_at").Desc()
ub.SQL("/* after order by */")
ub.Limit(100).Offset(5)
ub.SQL("/* after limit */")

sql := ub.String()
fmt.Println(sql)

Output:

/* before */ (SELECT id, name, created_at FROM demo.user) UNION (SELECT id, avatar FROM demo.user_profile) /* after union */ ORDER BY created_at DESC /* after order by */ LIMIT 100 OFFSET 5 /* after limit */

func (*UnionBuilder) SetFlavor added in v1.8.0 

func (ub *UnionBuilder) SetFlavor(flavor Flavor) (old Flavor)

SetFlavor sets the flavor of compiled sql.

func (*UnionBuilder) String added in v1.8.0 

func (ub *UnionBuilder) String() string

String returns the compiled SELECT string.

func (*UnionBuilder) Union added in v1.11.0 

func (ub *UnionBuilder) Union(builders ...Builder) *UnionBuilder

Union unions all builders together using UNION operator.

func (*UnionBuilder) UnionAll added in v1.11.0 

func (ub *UnionBuilder) UnionAll(builders ...Builder) *UnionBuilder

UnionAll unions all builders together using UNION ALL operator.

func (*UnionBuilder) Var added in v1.11.0 

func (ub *UnionBuilder) Var(arg interface{}) string

Var returns a placeholder for value.

type UpdateBuilder 

type UpdateBuilder struct {
	*WhereClause
	Cond
	// contains filtered or unexported fields
}

UpdateBuilder is a builder to build UPDATE.

Example 
ub := NewUpdateBuilder()
ub.Update("demo.user")
ub.Set(
	ub.Assign("type", "sys"),
	ub.Incr("credit"),
	"modified_at = UNIX_TIMESTAMP(NOW())", // It's allowed to write arbitrary SQL.
)
ub.Where(
	ub.GreaterThan("id", 1234),
	ub.Like("name", "%Du"),
	ub.Or(
		ub.IsNull("id_card"),
		ub.In("status", 1, 2, 5),
	),
	"modified_at > created_at + "+ub.Var(86400), // It's allowed to write arbitrary SQL.
)
ub.OrderBy("id").Asc()

sql, args := ub.Build()
fmt.Println(sql)
fmt.Println(args)

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 + ? ORDER BY id ASC
[sys 1234 %Du 1 2 5 86400]

func NewUpdateBuilder 

func NewUpdateBuilder() *UpdateBuilder

NewUpdateBuilder creates a new UPDATE builder.

func Update added in v1.11.0 

func Update(table string) *UpdateBuilder

Update sets table name in UPDATE.

Example 
sql := Update("demo.user").
	Set(
		"visited = visited + 1",
	).
	Where(
		"id = 1234",
	).
	String()

fmt.Println(sql)

Output:

UPDATE demo.user SET visited = visited + 1 WHERE id = 1234

func (*UpdateBuilder) Add 

func (ub *UpdateBuilder) Add(field string, value interface{}) string

Add represents SET "field = field + value" in UPDATE.

func (*UpdateBuilder) AddWhereClause added in v1.27.0 

func (ub *UpdateBuilder) AddWhereClause(whereClause *WhereClause) *UpdateBuilder

AddWhereClause adds all clauses in the whereClause to SELECT.

func (*UpdateBuilder) Asc added in v1.11.0 

func (ub *UpdateBuilder) Asc() *UpdateBuilder

Asc sets order of ORDER BY to ASC.

func (*UpdateBuilder) Assign 

func (ub *UpdateBuilder) Assign(field string, value interface{}) string

Assign represents SET "field = value" in UPDATE.

func (*UpdateBuilder) Build 

func (ub *UpdateBuilder) Build() (sql string, args []interface{})

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 ...interface{}) (sql string, args []interface{})

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) Desc added in v1.11.0 

func (ub *UpdateBuilder) Desc() *UpdateBuilder

Desc sets order of ORDER BY to DESC.

func (*UpdateBuilder) Div 

func (ub *UpdateBuilder) Div(field string, value interface{}) 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) Limit added in v1.11.0 

func (ub *UpdateBuilder) Limit(limit int) *UpdateBuilder

Limit sets the LIMIT in UPDATE.

func (*UpdateBuilder) Mul 

func (ub *UpdateBuilder) Mul(field string, value interface{}) string

Mul represents SET "field = field * value" in UPDATE.

func (*UpdateBuilder) NumAssignment added in v1.25.0 

func (ub *UpdateBuilder) NumAssignment() int

NumAssignment returns the number of assignments to update.

Example 
ub := NewUpdateBuilder()
ub.Update("demo.user")
ub.Set(
	ub.Assign("type", "sys"),
	ub.Incr("credit"),
	"modified_at = UNIX_TIMESTAMP(NOW())",
)

// Count the number of assignments.
fmt.Println(ub.NumAssignment())

Output:

3

func (*UpdateBuilder) OrderBy added in v1.11.0 

func (ub *UpdateBuilder) OrderBy(col ...string) *UpdateBuilder

OrderBy sets columns of ORDER BY in UPDATE.

func (*UpdateBuilder) SQL added in v1.11.0 

func (ub *UpdateBuilder) SQL(sql string) *UpdateBuilder

SQL adds an arbitrary sql to current position.

Example 
ub := NewUpdateBuilder()
ub.SQL("/* before */")
ub.Update("demo.user")
ub.SQL("/* after update */")
ub.Set(
	ub.Assign("type", "sys"),
)
ub.SQL("/* after set */")
ub.OrderBy("id").Desc()
ub.SQL("/* after order by */")
ub.Limit(10)
ub.SQL("/* after limit */")

sql := ub.String()
fmt.Println(sql)

Output:

/* before */ UPDATE demo.user /* after update */ SET type = ? /* after set */ ORDER BY id DESC /* after order by */ LIMIT 10 /* after limit */

func (*UpdateBuilder) Set 

func (ub *UpdateBuilder) Set(assignment ...string) *UpdateBuilder

Set sets the assignments in SET.

func (*UpdateBuilder) SetFlavor 

func (ub *UpdateBuilder) SetFlavor(flavor Flavor) (old Flavor)

SetFlavor sets the flavor of compiled sql.

func (*UpdateBuilder) SetMore added in v1.4.2 

func (ub *UpdateBuilder) SetMore(assignment ...string) *UpdateBuilder

SetMore appends the assignments in SET.

Example 
ub := NewUpdateBuilder()
ub.Update("demo.user")
ub.Set(
	ub.Assign("type", "sys"),
	ub.Incr("credit"),
)
ub.SetMore(
	"modified_at = UNIX_TIMESTAMP(NOW())", // It's allowed to write arbitrary SQL.
)

sql, args := ub.Build()
fmt.Println(sql)
fmt.Println(args)

Output:

UPDATE demo.user SET type = ?, credit = credit + 1, modified_at = UNIX_TIMESTAMP(NOW())
[sys]

func (*UpdateBuilder) String 

func (ub *UpdateBuilder) String() string

String returns the compiled UPDATE string.

func (*UpdateBuilder) Sub 

func (ub *UpdateBuilder) Sub(field string, value interface{}) 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.

type WhereClause added in v1.27.0 

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

WhereClause is a Builder for WHERE clause. All builders which support `WHERE` clause have an anonymous `WhereClause` field, in which the conditions are stored.

WhereClause can be shared among multiple builders. However, it is not thread-safe.

Example 
// Build a SQL to select a user from database.
sb := Select("name", "level").From("users")
sb.Where(
	sb.Equal("id", 1234),
)
sql, args := sb.Build()
fmt.Println(sql)
fmt.Println(args)

// Query database with the sql and update this user's level...

ub := Update("users")
ub.Set(
	ub.Add("level", 10),
)

// The WHERE clause of UPDATE should be the same as the WHERE clause of SELECT.
ub.WhereClause = sb.WhereClause

sql, args = ub.Build()
fmt.Println(sql)
fmt.Println(args)

Output:

SELECT name, level FROM users WHERE id = ?
[1234]
UPDATE users SET level = level + ? WHERE id = ?
[10 1234]
Example (ClearWhereClause) 
db := DeleteFrom("users")
db.Where(
	db.GreaterThan("level", 10),
)

sql, args := db.Build()
fmt.Println(sql)
fmt.Println(args)

// Clear WHERE clause.
db.WhereClause = nil
sql, args = db.Build()
fmt.Println(sql)
fmt.Println(args)

db.Where(
	db.Equal("id", 1234),
)
sql, args = db.Build()
fmt.Println(sql)
fmt.Println(args)

Output:

DELETE FROM users WHERE level > ?
[10]
DELETE FROM users
[]
DELETE FROM users WHERE id = ?
[1234]
Example (SharedAmongBuilders) 
// A WhereClause can be shared among builders.
// However, as it's not thread-safe, don't use it in a concurrent environment.
sb1 := Select("level").From("users")
sb2 := Select("status").From("users")

// Share the same WhereClause between sb1 and sb2.
whereClause := NewWhereClause()
sb1.WhereClause = whereClause
sb2.WhereClause = whereClause

// The Where method in sb1 and sb2 will update the same WhereClause.
// When we call sb1.Where(), the WHERE clause in sb2 will also be updated.
sb1.Where(
	sb1.Like("name", "Charmy%"),
)

// We can get a copy of the WhereClause.
// The copy is independent from the original.
sb3 := Select("name").From("users")
sb3.WhereClause = CopyWhereClause(whereClause)

// Adding more expressions to sb1 and sb2 will not affect sb3.
sb2.Where(
	sb2.In("status", 1, 2, 3),
)

// Adding more expressions to sb3 will not affect sb1 and sb2.
sb3.Where(
	sb3.GreaterEqualThan("level", 10),
)

sql1, args1 := sb1.Build()
sql2, args2 := sb2.Build()
sql3, args3 := sb3.Build()

fmt.Println(sql1)
fmt.Println(args1)
fmt.Println(sql2)
fmt.Println(args2)
fmt.Println(sql3)
fmt.Println(args3)

Output:

SELECT level FROM users WHERE name LIKE ? AND status IN (?, ?, ?)
[Charmy% 1 2 3]
SELECT status FROM users WHERE name LIKE ? AND status IN (?, ?, ?)
[Charmy% 1 2 3]
SELECT name FROM users WHERE name LIKE ? AND level >= ?
[Charmy% 10]

func CopyWhereClause added in v1.27.0 

func CopyWhereClause(whereClause *WhereClause) *WhereClause

CopyWhereClause creates a copy of the whereClause.

func NewWhereClause added in v1.27.0 

func NewWhereClause() *WhereClause

NewWhereClause creates a new WhereClause.

func (*WhereClause) AddWhereClause added in v1.27.0 

func (wc *WhereClause) AddWhereClause(whereClause *WhereClause)

AddWhereClause adds all clauses in the whereClause to the wc.

Example 
sb := Select("level").From("users")
sb.Where(
	sb.Equal("id", 1234),
)

sql, args := sb.Build()
fmt.Println(sql)
fmt.Println(args)

ub := Update("users")
ub.Set(
	ub.Add("level", 10),
)

// Copy the WHERE clause of sb into ub and add more expressions.
ub.AddWhereClause(sb.WhereClause).Where(
	ub.Equal("deleted", 0),
)

sql, args = ub.Build()
fmt.Println(sql)
fmt.Println(args)

Output:

SELECT level FROM users WHERE id = ?
[1234]
UPDATE users SET level = level + ? WHERE id = ? AND deleted = ?
[10 1234 0]

func (*WhereClause) AddWhereExpr added in v1.27.0 

func (wc *WhereClause) AddWhereExpr(args *Args, andExpr ...string)

AddWhereExpr adds an AND expression to WHERE clause with the specified arguments.

Example 
// WhereClause can be used as a standalone builder to build WHERE clause.
// It's recommended to use it with Cond.
whereClause := NewWhereClause()
cond := NewCond()

whereClause.AddWhereExpr(
	cond.Args,
	cond.In("name", "Charmy", "Huan"),
	cond.LessEqualThan("level", 10),
)

// Set the flavor of the WhereClause to PostgreSQL.
whereClause.SetFlavor(PostgreSQL)

sql, args := whereClause.Build()
fmt.Println(sql)
fmt.Println(args)

// Use this WhereClause in another builder.
sb := MySQL.NewSelectBuilder()
sb.Select("name", "level").From("users")
sb.WhereClause = whereClause

// The flavor of sb overrides the flavor of the WhereClause.
sql, args = sb.Build()
fmt.Println(sql)
fmt.Println(args)

Output:

WHERE name IN ($1, $2) AND level <= $3
[Charmy Huan 10]
SELECT name, level FROM users WHERE name IN (?, ?) AND level <= ?
[Charmy Huan 10]

func (*WhereClause) Build added in v1.27.0 

func (wc *WhereClause) Build() (sql string, args []interface{})

Build returns compiled WHERE clause string and args.

func (*WhereClause) BuildWithFlavor added in v1.27.0 

func (wc *WhereClause) BuildWithFlavor(flavor Flavor, initialArg ...interface{}) (sql string, args []interface{})

BuildWithFlavor builds a WHERE clause with the specified flavor and initial arguments.

func (*WhereClause) SetFlavor added in v1.27.0 

func (wc *WhereClause) SetFlavor(flavor Flavor) (old Flavor)

SetFlavor sets the flavor of compiled sql. When the WhereClause belongs to a builder, the flavor of the builder will be used when building SQL.

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.