sqlbuilder

package
v1.0.12 Latest Latest
Warning

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

 Go to latest Published: Nov 4, 2023 License: Apache-2.0, MIT Imports: 7 Imported by: 0

README

SQL builder for Go

Build Status GoDoc Go Report Coverage Status

Package sqlbuilder provides a set of flexible and powerful SQL string builders. The only goal of this package is to build SQL string with arguments which can be used in DB#Query or DB#Exec defined in package database/sql.

Install

Use go get to install this package.

go get -u github.com/huandu/go-sqlbuilder

Usage

Basic usage

Here is a sample to demonstrate how to build a SELECT query.

sb := sqlbuilder.NewSelectBuilder()

sb.Select("id", "name", sb.As("COUNT(*)", "c"))
sb.From("user")
sb.Where(sb.In("status", 1, 2, 5))

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

// Output:
// SELECT id, name, COUNT(*) AS c FROM user WHERE status IN (?, ?, ?)
// [1 2 5]

Following builders are implemented right now. API document and examples are provided in the godoc document.

Build SQL for MySQL or PostgreSQL

Parameter markers are different in MySQL and PostgreSQL. This package provides some methods to set the type of markers (we call it "flavor") in all builders.

By default, all builders uses DefaultFlavor to build SQL. The default value is MySQL.

There is a BuildWithFlavor method in Builder interface. We can use it to build a SQL with provided flavor.

We can wrap any Builder with a default flavor through WithFlavor.

To be more verbose, we can use PostgreSQL.NewSelectBuilder() to create a SelectBuilder with the PostgreSQL flavor. All builders can be created in this way.

Right now, there are only two flavors, MySQL and PostgreSQL. Open new issue to me to ask for a new flavor if you find it necessary.

Using Struct as a light weight ORM

Struct stores type information and struct fields of a struct. It's a factory of builders. We can use Struct methods to create initialized SELECT/INSERT/UPDATE/DELETE builders to work with the struct. It can help us to save time and avoid human-error on writing column names.

We can define a struct type and use field tags to let Struct know how to create right builders for us.

type ATable struct {
    Field1     string                                    // If a field doesn't has a tag, use "Field1" as column name in SQL.
    Field2     int    `db:"field2"`                      // Use "db" in field tag to set column name used in SQL.
    Field3     int64  `db:"field3" fieldtag:"foo,bar"`   // Set fieldtag to a field. We can use methods like `Struct#SelectForTag` to use it.
    Field4     int64  `db:"field4" fieldtag:"foo"`       // If we use `s.SelectForTag(table, "foo")`, columnes of SELECT are field3 and field4.
    Ignored    int32  `db:"-"`                           // If we set field name as "-", Struct will ignore it.
    unexported int                                       // Unexported field is not visible to Struct.
    Quoted     string `db:"quoted" fieldopt:"withquote"` // Add quote to the field using back quote or double quote. See `Flavor#Quote`.
}

Read examples for Struct to learn details of how to use it.

What's more, we can use Struct as a kind of zero-config ORM. While most ORM implementations requires several prerequisite configs to work with database connections, Struct doesn't require any config and work well with any SQL driver which works with database/sql. Struct doesn't call any database/sql API; It just creates right SQL with arguments for DB#Query/DB#Exec or a slice of address of struct fields for Rows#Scan/Row#Scan.

Here is a sample to use Struct as ORM. It should be quite straight forward for developers who are familiar with database/sql APIs.

type User struct {
    ID     int64  `db:"id"`
    Name   string `db:"name"`
    Status int    `db:"status"`
}

// A global variable to create SQL builders.
// All methods of userStruct are thread-safe.
var userStruct = NewStruct(new(User))

func ExampleStruct() {
    // Prepare SELECT query.
    //     SELECT id, name, status FROM user WHERE id = 1234
    sb := userStruct.SelectFrom("user")
    sb.Where(sb.Equal("id", 1234))

    // Execute the query.
    sql, args := sb.Build()
    rows, _ := db.Query(sql, args...)
    defer rows.Close()

    // Scan row data and set value to user.
    // Suppose we get following data.
    //
    //     |  id  |  name  | status |
    //     |------|--------|--------|
    //     | 1234 | huandu | 1      |
    var user User
    rows.Scan(userStruct.Addr(&user)...)

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

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

It's quite straight forward to create a nested SQL: use a builder as an argument to nest it.

Here is a sample.

sb := sqlbuilder.NewSelectBuilder()
fromSb := sqlbuilder.NewSelectBuilder()
statusSb := sqlbuilder.NewSelectBuilder()

sb.Select("id")
sb.From(sb.BuilderAs(fromSb, "user")))
sb.Where(sb.In("status", statusSb))

fromSb.Select("id").From("user").Where(fromSb.GreaterThan("level", 4))
statusSb.Select("status").From("config").Where(statusSb.Equal("state", 1))

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

// Output:
// SELECT id FROM (SELECT id FROM user WHERE level > ?) AS user WHERE status IN (SELECT status FROM config WHERE state = ?)
// [4 1]
Use sql.Named in a builder

The function sql.Named defined in database/sql can create a named argument in SQL. It's necessary if we want to reuse an argument several times in one SQL. It's still quite simple to use named arguments in a builder: use it as an argument.

Here is a sample.

now := time.Now().Unix()
start := sql.Named("start", now-86400)
end := sql.Named("end", now+86400)
sb := sqlbuilder.NewSelectBuilder()

sb.Select("name")
sb.From("user")
sb.Where(
    sb.Between("created_at", start, end),
    sb.GE("modified_at", start),
)

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

// Output:
// SELECT name FROM user WHERE created_at BETWEEN @start AND @end AND modified_at >= @start
// [{{} start 1514458225} {{} end 1514544625}]
Argument modifiers

There are several modifiers for arguments.

  • List(arg) represents a list of arguments. 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.
  • 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 $$.

FAQ

What's the difference between this package and squirrel

Package squirrel is another SQL builder package with outstanding design and high code quality. Comparing with squirrel, go-sqlbuilder is much more extensible with more built-in features.

Here are details.

  • API design: The core of go-sqlbuilder is Builder and Args. Nearly all featuers are built on top of them. If we want to extend this package, e.g. support EXPLAIN, we can use Build("EXPLAIN $?", builder) to add EXPLAIN in front of any SQL.
  • ORM: Package squirrel doesn't provide ORM directly. There is another package structable, which is based on squirrel, designed for ORM.
  • No design pitfalls: There is no design pitfalls like squirrel.Eq{"mynumber": []uint8{1,2,3}}. I'm proud of it. :)

License

This package is licensed under MIT license. See LICENSE for details.

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

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, intialValue ...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, intialValue ...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. nolint:gocyclo,lll

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})))
stmt, args := b.Build()

fmt.Println(stmt)
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"),
	})
stmt, args := b.Build()

fmt.Println(stmt)
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)
stmt, args := explain.Build()
fmt.Println(stmt)
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 
stmt, args := WithFlavor(Buildf("SELECT * FROM foo WHERE id = %v", 1234), PostgreSQL).Build()

fmt.Println(stmt)
fmt.Println(args)

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

fmt.Println(stmt)
fmt.Println(args)

Output:

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

type Cond 

type Cond struct {
	Args *Args
}

Cond provides several helper methods to build conditions.

func (*Cond) And 

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

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

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

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

Equal represents "field = value".

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

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

LikeBinary represents "field LIKE BINARY value".

func (*Cond) NE 

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

NE 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) 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) NotLikeBinary 

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

NotLikeBinary represents "field NOT LIKE BINARY value".

func (*Cond) Or 

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

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

func (*Cond) Var 

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

Var returns a placeholder for value.

type DeleteBuilder 

type DeleteBuilder struct {
	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 NewDeleteBuilder 

func NewDeleteBuilder() *DeleteBuilder

NewDeleteBuilder creates a new DELETE builder.

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

func (db *DeleteBuilder) SetFlavor(flavor Flavor) (old Flavor)

SetFlavor sets the flavor of compiled sql.

func (*DeleteBuilder) String 

func (db *DeleteBuilder) String() string

String returns the compiled DELETE string.

func (*DeleteBuilder) Where 

func (db *DeleteBuilder) Where(andExpr ...string) *DeleteBuilder

Where sets expressions of WHERE in DELETE.

type Flavor 

type Flavor int

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

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

Supported flavors.

func (Flavor) NewDeleteBuilder 

func (f Flavor) NewDeleteBuilder() *DeleteBuilder

NewDeleteBuilder creates a new DELETE builder with flavor.

func (Flavor) NewInsertBuilder 

func (f Flavor) NewInsertBuilder() *InsertBuilder

NewInsertBuilder creates a new INSERT builder with flavor.

func (Flavor) NewSelectBuilder 

func (f Flavor) NewSelectBuilder() *SelectBuilder

NewSelectBuilder creates a new SELECT builder with flavor.

func (Flavor) NewUpdateBuilder 

func (f Flavor) NewUpdateBuilder() *UpdateBuilder

NewUpdateBuilder creates a new UPDATE builder with flavor.

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, use double quote (") to quote name.

func (Flavor) String 

func (f Flavor) String() string

String returns the name of f.

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")
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]

func NewInsertBuilder 

func NewInsertBuilder() *InsertBuilder

NewInsertBuilder creates a new INSERT builder.

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

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

InsertInto sets table name in INSERT.

func (*InsertBuilder) SetFlavor 

func (ib *InsertBuilder) SetFlavor(flavor Flavor) (old Flavor)

SetFlavor sets the flavor of compiled sql.

func (*InsertBuilder) String 

func (ib *InsertBuilder) String() string

String returns the compiled DELETE string.

func (*InsertBuilder) Values 

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

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

type JoinOption 

type JoinOption string

JoinOption is the option in JOIN. 

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

Join options.

type SelectBuilder 

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

SelectBuilder is a builder to build SELECT.

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

stmt, args := sb.Build()
fmt.Println(stmt)
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()

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

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

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

Output:

SELECT id, name FROM (SELECT * FROM banned WHERE name NOT IN (?, ?)) AS user WHERE status IN (?, ?, ?) AND created_at BETWEEN @start AND @end ORDER BY modified_at DESC
[Huan Du Charmy Liu 1 2 3 {{} start 1234567890} {{} end 1234599999}]
Example (Join) 
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.
)

stmt, args := sb.Build()
fmt.Println(stmt)
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]

func NewSelectBuilder 

func NewSelectBuilder() *SelectBuilder

NewSelectBuilder creates a new SELECT builder.

func (*SelectBuilder) As 

func (sb *SelectBuilder) As(name, alias string) string

As returns an AS expression.

func (*SelectBuilder) Asc 

func (sb *SelectBuilder) Asc() *SelectBuilder

Asc sets order of ORDER BY to ASC.

func (*SelectBuilder) Build 

func (sb *SelectBuilder) Build() (sql string, args []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. nolint:gocyclo

func (*SelectBuilder) BuilderAs 

func (sb *SelectBuilder) BuilderAs(builder Builder, alias string) string

BuilderAs returns an AS expression wrapping a complex SQL. According to SQL syntax, SQL built by builder is surrounded by parens.

func (*SelectBuilder) Desc 

func (sb *SelectBuilder) Desc() *SelectBuilder

Desc sets order of ORDER BY to DESC.

func (*SelectBuilder) Distinct 

func (sb *SelectBuilder) Distinct() *SelectBuilder

Distinct marks this SELECT as DISTINCT.

func (*SelectBuilder) ForUpdate 

func (sb *SelectBuilder) ForUpdate() *SelectBuilder

ForUpdate add "FOR UPDATE" to SELECT

func (*SelectBuilder) From 

func (sb *SelectBuilder) From(table ...string) *SelectBuilder

From sets table names in SELECT.

func (*SelectBuilder) GroupBy 

func (sb *SelectBuilder) GroupBy(col ...string) *SelectBuilder

GroupBy sets columns of GROUP BY in SELECT.

func (*SelectBuilder) Having 

func (sb *SelectBuilder) Having(andExpr ...string) *SelectBuilder

Having sets expressions of HAVING in SELECT.

func (*SelectBuilder) Join 

func (sb *SelectBuilder) Join(table string, onExpr ...string) *SelectBuilder

Join sets expressions of JOIN in SELECT.

It builds a JOIN expression like 

JOIN table ON onExpr[0] AND onExpr[1] ...

func (*SelectBuilder) JoinWithOption 

func (sb *SelectBuilder) JoinWithOption(option JoinOption, table string, onExpr ...string) *SelectBuilder

JoinWithOption sets expressions of JOIN with an option.

It builds a JOIN expression like 

option JOIN table ON onExpr[0] AND onExpr[1] ...

Here is a list of supported options.

  • LeftJoin: LEFT JOIN
  • LeftOuterJoin: LEFT OUTER JOIN
  • RightJoin: RIGHT JOIN
  • RightOuterJoin: RIGHT OUTER JOIN

func (*SelectBuilder) Limit 

func (sb *SelectBuilder) Limit(limit int) *SelectBuilder

Limit sets the LIMIT in SELECT.

func (*SelectBuilder) Offset 

func (sb *SelectBuilder) Offset(offset int) *SelectBuilder

Offset sets the LIMIT offset in SELECT.

func (*SelectBuilder) OrderBy 

func (sb *SelectBuilder) OrderBy(col ...string) *SelectBuilder

OrderBy sets columns of ORDER BY in SELECT.

func (*SelectBuilder) Select 

func (sb *SelectBuilder) Select(col ...string) *SelectBuilder

Select sets columns in SELECT.

func (*SelectBuilder) SetFlavor 

func (sb *SelectBuilder) SetFlavor(flavor Flavor) (old Flavor)

SetFlavor sets the flavor of compiled sql.

func (*SelectBuilder) String 

func (sb *SelectBuilder) String() string

String returns the compiled SELECT string.

func (*SelectBuilder) Where 

func (sb *SelectBuilder) Where(andExpr ...string) *SelectBuilder

Where sets expressions of WHERE in SELECT.

type Struct 

type Struct struct {
	Flavor Flavor
	// contains filtered or unexported fields
}

Struct represents a struct type.

All methods in Struct are thread-safe. We can define a global variable to hold a Struct and use it in any goroutine.

Example (BuildDELETE) 
// Suppose we defined following type and global variable.
//
//     type User struct {
//         ID     int64  `db:"id"`
//         Name   string `db:"name"`
//         Status int    `db:"status"`
//     }
//
//     var userStruct = NewStruct(new(User))

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

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

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

Output:

DELETE FROM user WHERE id = ?
[1234]
Example (BuildINSERT) 
// Suppose we defined following type and global variable.
//
//     type User struct {
//         ID     int64  `db:"id"`
//         Name   string `db:"name"`
//         Status int    `db:"status"`
//     }
//
//     var userStruct = NewStruct(new(User))

// Prepare INSERT query.
user := &User{
	ID:     1234,
	Name:   "Huan Du",
	Status: 1,
}
ib := userStruct.InsertInto("user", user)

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

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

Output:

INSERT INTO user (id, name, status) VALUES (?, ?, ?)
[1234 Huan Du 1]
Example (BuildUPDATE) 
// Suppose we defined following type and global variable.
//
//     type User struct {
//         ID     int64  `db:"id"`
//         Name   string `db:"name"`
//         Status int    `db:"status"`
//     }
//
//     var userStruct = NewStruct(new(User))

// Prepare UPDATE query.
user := &User{
	ID:     1234,
	Name:   "Huan Du",
	Status: 1,
}
ub := userStruct.Update("user", user)
ub.Where(ub.E("id", user.ID))

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

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

Output:

UPDATE user SET id = ?, name = ?, status = ? WHERE id = ?
[1234 Huan Du 1 1234]
Example (ForPostgreSQL) 
userStruct := NewStruct(new(User)).For(PostgreSQL)

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

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

Output:

SELECT id, name, status FROM user WHERE id = $1
[1234]
Example (UseStructAsORM) 
// Suppose we defined following type and global variable.
//
//     type User struct {
//         ID     int64  `db:"id"`
//         Name   string `db:"name"`
//         Status int    `db:"status"`
//     }
//
//     var userStruct = NewStruct(new(User))

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

// Execute the query.
sql, args := sb.Build()
rows, _ := db.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 id, name, status FROM user WHERE id = ?
[1234]
sqlbuilder.User{ID:1234, Name:"huandu", Status:1}
Example (UseTag) 
// Suppose we defined following type and global variable.
//
//     type Order struct {
//         ID          int64  `db:"id" fieldtag:"update,paid"`
//         State       int    `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"`
//         Description string `db:"description" fieldtag:"update"`
//         CreatedAt   int64  `db:"created_at"`
//         ModifiedAt  int64  `db:"modified_at" fieldtag:"update,paid"`
//     }
//
//     var orderStruct = NewStruct(new(Order))

createOrder := func(table string) {
	now := time.Now().Unix()
	order := &Order{
		ID:          1234,
		State:       OrderStateCreated,
		SkuID:       5678,
		UserID:      7527,
		Price:       1000,
		Discount:    0,
		Description: "Best goods",
		CreatedAt:   now,
		ModifiedAt:  now,
	}
	b := orderStruct.InsertInto(table, &order)
	sql, args := b.Build()
	db.Exec(sql, args)
}
updatePrice := func(table string) {
	tag := "update"

	// Read order from database.
	var order Order
	sql, args := orderStruct.SelectFromForTag(table, tag).Where("id = 1234").Build()
	rows, _ := db.Query(sql, args...)
	defer rows.Close()
	_ = rows.Scan(orderStruct.AddrForTag(tag, &order)...)

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

	// Save the order.
	b := orderStruct.UpdateForTag(table, tag, &order)
	b.Where(b.E("id", order.ID))
	sql, args = b.Build()
	db.Exec(sql, args...)
}
updateState := func(table string) {
	tag := "paid"

	// Read order from database.
	var order Order
	sql, args := orderStruct.SelectFromForTag(table, tag).Where("id = 1234").Build()
	rows, _ := db.Query(sql, args...)
	defer func(rows testRows) {
		_ = rows.Close()
	}(rows)
	_ = rows.Scan(orderStruct.AddrForTag(tag, &order)...)

	// 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.
		return
	}

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

	// Save the order.
	b := orderStruct.UpdateForTag(table, tag, &order)
	b.Where(b.E("id", order.ID))
	sql, args = b.Build()
	db.Exec(sql, args...)
}

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

fmt.Println("done")

Output:

done

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

func (*Struct) Addr 

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

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

func (*Struct) AddrForTag 

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

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

If tag is not defined in s in advance,

func (*Struct) AddrWithCols 

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

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

func (*Struct) DeleteFrom 

func (s *Struct) DeleteFrom(table string) *DeleteBuilder

DeleteFrom creates a new `DeleteBuilder` with table name.

Caller is responsible to set WHERE condition to match right record.

func (*Struct) For 

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

For sets the default flavor of s.

func (*Struct) InsertInto 

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

InsertInto creates a new `InsertBuilder` with table name. By default, all exported fields of the s is inserted in INSERT with the field values from value. Bulk insert is supported. Item in value that is not the same as that of s will be skipped. If no item in value is valid, InsertInto returns a dummy `InsertBuilder` with table name.

func (*Struct) InsertIntoForTag 

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

InsertIntoForTag creates a new `InsertBuilder` with table name. By default, all fields of the s tagged with tag is inserted in INSERT with the field values from value. Bulk insert is supported. Item in value that is not the same as that of s will be skipped. If no item in value is valid, InsertIntoForTag returns a dummy `InsertBuilder` with table name.

func (*Struct) SelectFrom 

func (s *Struct) SelectFrom(table string) *SelectBuilder

SelectFrom creates a new `SelectBuilder` with table name. By default, all exported fields of the s are listed as columns in SELECT.

Caller is responsible to set WHERE condition to find right record.

func (*Struct) SelectFromForTag 

func (s *Struct) SelectFromForTag(table string, tag string) *SelectBuilder

SelectFromForTag creates a new `SelectBuilder` with table name for a specified tag. By default, all fields of the s tagged with tag are listed as columns in SELECT.

Caller is responsible to set WHERE condition to find right record.

func (*Struct) Update 

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

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.

type UpdateBuilder 

type UpdateBuilder struct {
	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.
)

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

func NewUpdateBuilder 

func NewUpdateBuilder() *UpdateBuilder

NewUpdateBuilder creates a new UPDATE builder.

func (*UpdateBuilder) Add 

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

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

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

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

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

func (*UpdateBuilder) Set 

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

Set sets the assignements in SET.

func (*UpdateBuilder) SetFlavor 

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

SetFlavor sets the flavor of compiled sql.

func (*UpdateBuilder) String 

func (ub *UpdateBuilder) String() string

String returns the compiled UPDATE string.

func (*UpdateBuilder) Sub 

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

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.