go-sqlbuilder: github.com/huandu/go-sqlbuilder Index | Examples | Files

package sqlbuilder

import "github.com/huandu/go-sqlbuilder"

Package sqlbuilder is a flexible and powerful tool to build SQL string and associated args.

Index

Examples

Package Files

args.go builder.go cond.go createtable.go delete.go doc.go flavor.go insert.go interpolate.go modifiers.go select.go struct.go update.go

Variables

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")
)
var (
    // DBTag is the struct tag to describe the name for a field in struct.
    DBTag = "db"

    // FieldTag is the struct tag to describe the tag name for a field in struct.
    // Use "," to separate different tags.
    FieldTag = "fieldtag"

    // FieldOpt is the options for a struct field.
    // As db column can contain "," in theory, field options should be provided in a separated tag.
    FieldOpt = "fieldopt"
)
var (
    // DefaultFlavor is the default flavor for all builders.
    DefaultFlavor = MySQL
)

func Escape Uses

func Escape(ident string) string

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

func EscapeAll Uses

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

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

func Flatten Uses

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 Uses

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 Uses

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 Uses

func Raw(expr string) interface{}

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

type Args Uses

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 Uses

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

Add adds an arg to Args and returns a placeholder.

func (*Args) Compile Uses

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 Uses

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.

type Builder Uses

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 Uses

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.

Code:

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

func BuildNamed Uses

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.

Code:

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

fmt.Println(sql)
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 Uses

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.

Code:

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

explain := 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]

func WithFlavor Uses

func WithFlavor(builder Builder, flavor Flavor) Builder

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

Code:

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)

Output:

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

type Cond Uses

type Cond struct {
    Args *Args
}

Cond provides several helper methods to build conditions.

func (*Cond) And Uses

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

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

func (*Cond) Between Uses

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

Between represents "field BETWEEN lower AND upper".

func (*Cond) E Uses

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

E is an alias of Equal.

func (*Cond) Equal Uses

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

Equal represents "field = value".

func (*Cond) G Uses

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

G is an alias of GreaterThan.

func (*Cond) GE Uses

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

GE is an alias of GreaterEqualThan.

func (*Cond) GreaterEqualThan Uses

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

GreaterEqualThan represents "field >= value".

func (*Cond) GreaterThan Uses

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

GreaterThan represents "field > value".

func (*Cond) In Uses

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

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

func (*Cond) IsNotNull Uses

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

IsNotNull represents "field IS NOT NULL".

func (*Cond) IsNull Uses

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

IsNull represents "field IS NULL".

func (*Cond) L Uses

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

L is an alias of LessThan.

func (*Cond) LE Uses

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

LE is an alias of LessEqualThan.

func (*Cond) LessEqualThan Uses

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

LessEqualThan represents "field <= value".

func (*Cond) LessThan Uses

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

LessThan represents "field < value".

func (*Cond) Like Uses

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

Like represents "field LIKE value".

func (*Cond) NE Uses

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

NE is an alias of NotEqual.

func (*Cond) NotBetween Uses

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

NotBetween represents "field NOT BETWEEN lower AND upper".

func (*Cond) NotEqual Uses

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

NotEqual represents "field != value".

func (*Cond) NotIn Uses

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

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

func (*Cond) NotLike Uses

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

NotLike represents "field NOT LIKE value".

func (*Cond) Or Uses

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

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

func (*Cond) Var Uses

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

Var returns a placeholder for value.

type CreateTableBuilder Uses

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

CreateTableBuilder is a builder to build CREATE TABLE.

Code:

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

Code:

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 NewCreateTableBuilder Uses

func NewCreateTableBuilder() *CreateTableBuilder

NewCreateTableBuilder creates a new CREATE TABLE builder.

func (*CreateTableBuilder) Build Uses

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 Uses

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 Uses

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

CreateTable sets the table name in CREATE TABLE.

func (*CreateTableBuilder) CreateTempTable Uses

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 Uses

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

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

func (*CreateTableBuilder) IfNotExists Uses

func (ctb *CreateTableBuilder) IfNotExists() *CreateTableBuilder

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

func (*CreateTableBuilder) Option Uses

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

Option adds a table option in CREATE TABLE.

func (*CreateTableBuilder) SetFlavor Uses

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

SetFlavor sets the flavor of compiled sql.

func (*CreateTableBuilder) String Uses

func (ctb *CreateTableBuilder) String() string

String returns the compiled INSERT string.

type DeleteBuilder Uses

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

DeleteBuilder is a builder to build DELETE.

Code:

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 Uses

func NewDeleteBuilder() *DeleteBuilder

NewDeleteBuilder creates a new DELETE builder.

func (*DeleteBuilder) Build Uses

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 Uses

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 Uses

func (db *DeleteBuilder) DeleteFrom(table string) *DeleteBuilder

DeleteFrom sets table name in DELETE.

func (*DeleteBuilder) SetFlavor Uses

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

SetFlavor sets the flavor of compiled sql.

func (*DeleteBuilder) String Uses

func (db *DeleteBuilder) String() string

String returns the compiled DELETE string.

func (*DeleteBuilder) Where Uses

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

Where sets expressions of WHERE in DELETE.

type Flavor Uses

type Flavor int

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

const (
    MySQL Flavor
    PostgreSQL
)

Supported flavors.

Code:

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

func (Flavor) Interpolate Uses

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.

Code:

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>

Code:

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

func (Flavor) NewCreateTableBuilder Uses

func (f Flavor) NewCreateTableBuilder() *CreateTableBuilder

NewCreateTableBuilder creates a new CREATE TABLE builder with flavor.

func (Flavor) NewDeleteBuilder Uses

func (f Flavor) NewDeleteBuilder() *DeleteBuilder

NewDeleteBuilder creates a new DELETE builder with flavor.

func (Flavor) NewInsertBuilder Uses

func (f Flavor) NewInsertBuilder() *InsertBuilder

NewInsertBuilder creates a new INSERT builder with flavor.

func (Flavor) NewSelectBuilder Uses

func (f Flavor) NewSelectBuilder() *SelectBuilder

NewSelectBuilder creates a new SELECT builder with flavor.

func (Flavor) NewUpdateBuilder Uses

func (f Flavor) NewUpdateBuilder() *UpdateBuilder

NewUpdateBuilder creates a new UPDATE builder with flavor.

func (Flavor) Quote Uses

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 Uses

func (f Flavor) String() string

String returns the name of f.

type InsertBuilder Uses

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

InsertBuilder is a builder to build INSERT.

Code:

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]

Code:

ib := 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 IGNORE INTO demo.user (id, name, status, created_at) VALUES (?, ?, ?, UNIX_TIMESTAMP(NOW())), (?, ?, ?, ?)
[1 Huan Du 1 2 Charmy Liu 1 1234567890]

Code:

ib := NewInsertBuilder()
ib.ReplaceInto("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:

REPLACE INTO demo.user (id, name, status, created_at) VALUES (?, ?, ?, UNIX_TIMESTAMP(NOW())), (?, ?, ?, ?)
[1 Huan Du 1 2 Charmy Liu 1 1234567890]

func NewInsertBuilder Uses

func NewInsertBuilder() *InsertBuilder

NewInsertBuilder creates a new INSERT builder.

func (*InsertBuilder) Build Uses

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 Uses

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 Uses

func (ib *InsertBuilder) Cols(col ...string) *InsertBuilder

Cols sets columns in INSERT.

func (*InsertBuilder) InsertIgnoreInto Uses

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

InsertIgnoreInto sets table name in INSERT IGNORE.

func (*InsertBuilder) InsertInto Uses

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

InsertInto sets table name in INSERT.

func (*InsertBuilder) ReplaceInto Uses

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

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

SetFlavor sets the flavor of compiled sql.

func (*InsertBuilder) String Uses

func (ib *InsertBuilder) String() string

String returns the compiled INSERT string.

func (*InsertBuilder) Values Uses

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

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

type JoinOption Uses

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 Uses

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

SelectBuilder is a builder to build SELECT.

Code:

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)

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

Code:

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"})...),
)

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

Code:

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]

Code:

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

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

Output:

SELECT colHasA$Sign, ? FROM table
[foo]

func NewSelectBuilder Uses

func NewSelectBuilder() *SelectBuilder

NewSelectBuilder creates a new SELECT builder.

func (*SelectBuilder) As Uses

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

As returns an AS expression.

func (*SelectBuilder) Asc Uses

func (sb *SelectBuilder) Asc() *SelectBuilder

Asc sets order of ORDER BY to ASC.

func (*SelectBuilder) Build Uses

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 Uses

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 Uses

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 Uses

func (sb *SelectBuilder) Desc() *SelectBuilder

Desc sets order of ORDER BY to DESC.

func (*SelectBuilder) Distinct Uses

func (sb *SelectBuilder) Distinct() *SelectBuilder

Distinct marks this SELECT as DISTINCT.

func (*SelectBuilder) From Uses

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

From sets table names in SELECT.

func (*SelectBuilder) GroupBy Uses

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

GroupBy sets columns of GROUP BY in SELECT.

func (*SelectBuilder) Having Uses

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

Having sets expressions of HAVING in SELECT.

func (*SelectBuilder) Join Uses

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 Uses

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 Uses

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

Limit sets the LIMIT in SELECT.

func (*SelectBuilder) Offset Uses

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

Offset sets the LIMIT offset in SELECT.

func (*SelectBuilder) OrderBy Uses

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

OrderBy sets columns of ORDER BY in SELECT.

func (*SelectBuilder) Select Uses

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

Select sets columns in SELECT.

func (*SelectBuilder) SetFlavor Uses

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

SetFlavor sets the flavor of compiled sql.

func (*SelectBuilder) String Uses

func (sb *SelectBuilder) String() string

String returns the compiled SELECT string.

func (*SelectBuilder) Where Uses

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

Where sets expressions of WHERE in SELECT.

type Struct Uses

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.

Code:

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

Code:

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

Code:

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

Code:

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 user.id, user.name, user.status FROM user WHERE id = $1
[1234]

Code:

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

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

Code:

// 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"`
//         Desc       string `db:"desc" fieldtag:"update" fieldopt:"withquote"` // `desc` is a keyword.
//         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,
        Desc:       "Best goods",
        CreatedAt:  now,
        ModifiedAt: now,
    }
    b := orderStruct.InsertInto(table, &order)
    sql, args := b.Build()
    db.Exec(sql, args)
    fmt.Println(sql)
}
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...)
    fmt.Println(sql)
}
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 rows.Close()
    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...)
    fmt.Println(sql)
}

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

fmt.Println("done")

Output:

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

func NewStruct Uses

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 Uses

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 Uses

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 Uses

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 Uses

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 Uses

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

For sets the default flavor of s.

func (*Struct) InsertIgnoreInto Uses

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 Uses

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.

func (*Struct) InsertInto Uses

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 Uses

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.

func (*Struct) ReplaceInto Uses

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 Uses

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.

func (*Struct) SelectFrom Uses

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 Uses

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 Uses

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 Uses

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 Uses

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

UpdateBuilder is a builder to build UPDATE.

Code:

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 Uses

func NewUpdateBuilder() *UpdateBuilder

NewUpdateBuilder creates a new UPDATE builder.

func (*UpdateBuilder) Add Uses

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

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

func (*UpdateBuilder) Assign Uses

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

Assign represents SET "field = value" in UPDATE.

func (*UpdateBuilder) Build Uses

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 Uses

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 Uses

func (ub *UpdateBuilder) Decr(field string) string

Decr represents SET "field = field - 1" in UPDATE.

func (*UpdateBuilder) Div Uses

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

Div represents SET "field = field / value" in UPDATE.

func (*UpdateBuilder) Incr Uses

func (ub *UpdateBuilder) Incr(field string) string

Incr represents SET "field = field + 1" in UPDATE.

func (*UpdateBuilder) Mul Uses

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

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

func (*UpdateBuilder) Set Uses

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

Set sets the assignements in SET.

func (*UpdateBuilder) SetFlavor Uses

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

SetFlavor sets the flavor of compiled sql.

func (*UpdateBuilder) SetMore Uses

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

SetMore appends the assignements in SET.

func (*UpdateBuilder) String Uses

func (ub *UpdateBuilder) String() string

String returns the compiled UPDATE string.

func (*UpdateBuilder) Sub Uses

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

Sub represents SET "field = field - value" in UPDATE.

func (*UpdateBuilder) Update Uses

func (ub *UpdateBuilder) Update(table string) *UpdateBuilder

Update sets table name in UPDATE.

func (*UpdateBuilder) Where Uses

func (ub *UpdateBuilder) Where(andExpr ...string) *UpdateBuilder

Where sets expressions of WHERE in UPDATE.

Package sqlbuilder imports 12 packages (graph) and is imported by 1 packages. Updated 2019-11-21. Refresh now. Tools for package owners.