grizzle

package module
v1.37.2 Latest Latest
Warning

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

 Go to latest Published: Oct 19, 2025 License: MIT Imports: 25 Imported by: 0

README

SQL builder for Go

Go GoDoc Go Report Coverage Status

The sqlbuilder package offers a comprehensive suite of SQL string concatenation utilities. It is designed to facilitate the construction of SQL statements compatible with Go's standard library sql.DB and sql.Stmt interfaces, focusing on optimizing the performance of SQL statement creation and minimizing memory usage.

The primary objective of this package's design was to craft a SQL construction library that operates independently of specific database drivers and business logic. It is tailored to accommodate the diverse needs of enterprise environments, including the use of custom database drivers, adherence to specialized operational standards, integration into heterogeneous systems, and handling of non-standard SQL in intricate scenarios. Following its open-source release, the package has undergone extensive testing within a large-scale enterprise context, successfully managing the workload of hundreds of millions of orders daily and nearly ten million transactions daily, thus highlighting its robust performance and scalability.

This package is not restricted to any particular database driver and does not automatically establish connections with any database systems. It does not presuppose the execution of the generated SQL, making it versatile for a broad spectrum of application scenarios that involve the construction of SQL-like statements. It is equally well-suited for further development aimed at creating more business-specific database interaction packages, ORMs, and similar tools.

Install

Install this package by executing the following command:

go get github.com/huandu/go-sqlbuilder

Usage

Basic usage

We can rapidly construct SQL statements using 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 common scenarios, it is necessary to escape all user inputs. To achieve this, initialize a builder at the outset.

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 the following pre-defined builders. API documentation and usage examples are available in the godoc online documentation.

A unique method, SQL(sql string), is implemented across all statement builders, enabling the insertion of any arbitrary SQL segment into a builder during SQL construction. This feature is particularly beneficial for crafting SQL statements that incorporate non-standard syntax required by OLTP or OLAP systems.

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

Below are several utility methods designed to address special cases.

  • Flatten enables the recursive conversion of an array-like variable into a flat slice of []interface{}. For example, invoking Flatten([]interface{"foo", []int{2, 3}}) yields []interface{}{"foo", 2, 3}. This method is compatible with builder methods such as In, NotIn, Values, etc., facilitating the conversion of a typed array into []interface{} or the merging of inputs.
  • List operates similarly to Flatten, with the exception that its return value is specifically intended for use as builder arguments. For example, Buildf("my_func(%v)", List([]int{1, 2, 3})).Build() generates SQL my_func(?, ?, ?) with arguments []interface{}{1, 2, 3}.
  • Raw designates a string as a "raw string" within arguments. For instance, Buildf("SELECT %v", Raw("NOW()")).Build() results in SQL SELECT NOW().

For detailed instructions on utilizing these builders, consult the examples provided 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 simplify the construction of WHERE clauses, a utility type named Cond is provided for condition building. All builders that support WHERE clauses possess an anonymous Cond field, enabling the invocation of Cond methods 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.

The following example illustrates how to transfer a 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 = ?

Refer to the WhereClause examples to learn its usage.

Build SQL for different systems

SQL syntax and parameter placeholders can differ across systems. To address these variations, this package introduces a concept termed "flavor".

Currently, flavors such as MySQL, PostgreSQL, SQLite, SQLServer, CQL, ClickHouse, Presto, Oracle and Informix are supported. Should there be a demand for additional flavors, please submit an issue or a pull request.

By default, all builders utilize DefaultFlavor for SQL construction, with MySQL as the default setting.

For greater readibility, PostgreSQL.NewSelectBuilder() can be used to instantiate a SelectBuilder with the PostgreSQL flavor. All builders can be created in this way.

Using Struct as a light weight ORM

Struct encapsulates type information and struct fields, serving as a builder factory. Utilizing Struct methods, one can generate SELECT/INSERT/UPDATE/DELETE builders that are pre-configured for use with the struct, thereby conserving time and mitigating the risk of typographical errors in column name entries.

One can define a struct type and employ field tags to guide Struct in generating the appropriate builders.

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("t")`, columnes of SELECT are "t.field3" and "t.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"`

    // By default, the `SelectFrom("t")` will add the "t." to all names of fields matched tag.
    // We can add dot to field name to disable this behavior.
    FieldWithTableAlias string `db:"m.field"`
}

For detailed instructions on utilizing Struct, refer to the examples.

Furthermore, Struct can be employed as a zero-configuration ORM. Unlike most ORM implementations that necessitate preliminary configurations for database connectivity, Struct operates without any configuration, functioning seamlessly with any SQL driver compatible with database/sql. Struct does not invoke any database/sql APIs; it solely generates the appropriate SQL statements with arguments for DB#Query/DB#Exec or an array of struct field addresses for Rows#Scan/Row#Scan.

The following example demonstrates the use of Struct as an ORM. It should be relatively straightforward for developers well-versed in database/sql APIs.

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

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

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

    // Execute the query and scan the results into the user struct.
    sql, args := sb.Build()
    rows, _ := db.Query(sql, args...)
    defer rows.Close()

    // Scan row data and set value to user.
    // Assuming the following data is retrieved:
    //
    //     |  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 numerous production environments, table column names adhere to the snake_case convention, e.g., user_id. Conversely, struct fields in Go are typically in CamelCase to maintain public accessibility and satisfy golint. Employing the db tag for each struct field can be redundant. To streamline this, a field mapper function can be utilized to establish a consistent rule for mapping struct field names to database column names.

The DefaultFieldMapper serves as a global field mapper function, tasked with the conversion of field names to a desired style. By default, it is set to nil, effectively performing no action. Recognizing that the majority of table column names follow the snake_case convention, one can assign DefaultFieldMapper to sqlbuilder.SnakeCaseMapper. For instances that deviate from this norm, a custom mapper can be assigned to a Struct via the WithFieldMapper method.

Here are important considerations regarding the 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.

Refer to the field mapper function sample for an illustrative example.

Nested SQL

Creating nested SQL is straightforward: simply use a builder as an argument for nesting.

Here is an illustrative example.

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 sql.Named function, as defined in the database/sql package, facilitates the creation of named arguments within SQL statements. This feature is essential for scenarios where an argument needs to be reused multiple times within a single SQL statement. Incorporating named arguments into a builder is straightforward: treat them as regular arguments.

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

Several argument modifiers are available:

  • List(arg) encapsulates a series of arguments. Given arg as a slice or array, for instance, a slice containing three integers, it compiles to ?, ?, ? and is presented in the final arguments as three individual integers. This serves as a convenience tool, utilizable within IN expressions or within the VALUES clause of an INSERT INTO statement.
  • TupleNames(names) and Tuple(values) facilitate the representation of tuple syntax in SQL. For usage examples, refer to Tuple.
  • Named(name, arg) designates a named argument. Functionality is limited to Build or BuildNamed, where it defines a named placeholder using the syntax ${name}.
  • Raw(expr) designates expr as a plain string within SQL, as opposed to an argument. During the construction of a builder, raw expressions are directly embedded into the SQL string, omitting the need for ? placeholders.
Freestyle builder

A builder essentially serves as a means to log arguments. For constructing lengthy SQL statements that incorporate numerous special syntax elements (e.g., special comments intended for a database proxy), Buildf can be employed to format the SQL string using a syntax akin to fmt.Sprintf.

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]
Clone builders

The Clone methods make any builder reusable as a template. You can create a partially initialized builder once (even as a global), then call Clone() to get an independent copy to customize per request. This avoids repeated setup while keeping shared templates immutable and safe for concurrent use.

Supported builders with Clone:

Example: define a global SELECT template and clone it per call

package yourpkg

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

// Global template — safe to reuse by cloning.
var baseUserSelect = sqlbuilder.NewSelectBuilder().
    Select("id", "name", "email").
    From("users").
    Where("deleted_at IS NULL")

func ListActiveUsers(limit, offset int) (string, []interface{}) {
    sb := baseUserSelect.Clone() // independent copy
    sb.OrderBy("id").Asc()
    sb.Limit(limit).Offset(offset)
    return sb.Build()
}

func GetActiveUserByID(id int64) (string, []interface{}) {
    sb := baseUserSelect.Clone() // start from the same template
    sb.Where(sb.Equal("id", id))
    sb.Limit(1)
    return sb.Build()
}

The same template pattern applies to other builders. For example, keep a base UpdateBuilder with the table and common SET clauses, or a base CTEBuilder defining reusable CTEs, then Clone() and add query-specific WHERE/ORDER BY/LIMIT/RETURNING as needed.

Using special syntax to build SQL

The sqlbuilder package incorporates special syntax for representing uncompiled SQL internally. To leverage this syntax for developing customized tools, the Build function can be utilized to compile it with the necessary arguments.

The format string employs special syntax for representing arguments:

  • $? references successive arguments supplied in the function call, functioning similarly to %v in fmt.Sprintf.
  • $0, $1, ..., $n reference the nth argument provided in the call; subsequent $? will then refer to arguments n+1 onwards.
  • ${name} references a named argument defined by Named using the specified name.
  • $$ represents a literal "$" character.
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]

For scenarios where only the ${name} syntax is required to reference named arguments, utilize BuildNamed. This function disables all special syntax except for ${name} and $$.

Interpolate args in the sql

Certain SQL-like drivers, such as those for Redis or Elasticsearch, do not implement the StmtExecContext#ExecContext method. These drivers encounter issues when len(args) > 0. The sole workaround is to interpolate args directly into the sql string and then execute the resulting query with the driver.

The interpolation feature in this package is designed to provide a "basically sufficient" level of functionality, rather than a capability that rivals the comprehensive features of various SQL drivers and DBMS systems.

Security warning: While efforts are made to escape special characters in interpolation methods, this approach remains less secure than using Stmt as implemented by SQL drivers.

This feature draws inspiration from the interpolation capabilities found in the github.com/go-sql-driver/mysql package.

Here is an example specifically 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 an example for PostgreSQL, noting that dollar quoting 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 the MIT license. For more information, refer to the LICENSE file.

Documentation

Overview

package grizzle 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("grizzle: 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("grizzle: not enough args when interpolating")

	// ErrInterpolateUnsupportedArgs means that some types of the args are not supported.
	ErrInterpolateUnsupportedArgs = errors.New("grizzle: 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 Convert 

func Convert[S any, D any](src S, changes map[string]Transformer) D

Convert copies fields from src struct to dst struct, applying optional transformers.

func EnsureOutputDir 

func EnsureOutputDir(outputDir string) error

EnsureOutputDir ensures the output directory exists

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 GenerateEntities 

func GenerateEntities(tables map[string]*Table, outputDir string) error

GenerateEntities generates entity files from multiple table definitions

func GenerateEntity 

func GenerateEntity(table *Table, entityName, outputDir string) error

GenerateEntity generates an entity file from a table definition

func GenerateFromFile 

func GenerateFromFile(inputFile, outputDir string) error

GenerateFromFile is a convenience function that can be called from go:generate

func GenerateFromTable 

func GenerateFromTable(table *Table, entityName, outputDir string) error

GenerateFromTable generates entity files from a table definition

func GenerateFromTables 

func GenerateFromTables(tables map[string]*Table, outputDir string) error

GenerateFromTables generates entity files from multiple table definitions

func GetEntityPath 

func GetEntityPath(outputDir, entityName string) string

GetEntityPath returns the full path for an entity file

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 

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 

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 

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.

func (*Args) Replace 

func (args *Args) Replace(placeholder string, arg interface{})

Replace replaces the placeholder with arg.

The placeholder must be the value returned by `Add`, e.g. "$1". If the placeholder is not found, this method does nothing.

func (*Args) Value 

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

Value returns the value of the arg. The arg must be the value returned by `Add`.

type Builder 

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

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 CTEBuilder 

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

CTEBuilder is a CTE (Common Table Expression) builder.

Example 
usersBuilder := Select("id", "name", "level").From("users")
usersBuilder.Where(
	usersBuilder.GreaterEqualThan("level", 10),
)
cteb := With(
	CTETable("valid_users").As(usersBuilder),
)
fmt.Println(cteb)

sb := Select("valid_users.id", "valid_users.name", "orders.id").
	From("users").With(cteb).
	Join("orders", "users.id = orders.user_id")
sb.Where(
	sb.LessEqualThan("orders.price", 200),
	"valid_users.level < orders.min_level",
).OrderBy("orders.price").Desc()

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

Output:
WITH valid_users AS (SELECT id, name, level FROM users WHERE level >= ?)
WITH valid_users AS (SELECT id, name, level FROM users WHERE level >= ?) SELECT valid_users.id, valid_users.name, orders.id FROM users, valid_users JOIN orders ON users.id = orders.user_id WHERE orders.price <= ? AND valid_users.level < orders.min_level ORDER BY orders.price DESC
[10 200]
[users valid_users]
Example (Delete) 
sql := With(
	CTETable("users", "user_id").As(
		Select("user_id").From("cheaters"),
	),
).DeleteFrom("awards").Where(
	"users.user_id = awards.user_id",
).String()

fmt.Println(sql)

Output:
WITH users (user_id) AS (SELECT user_id FROM cheaters) DELETE FROM awards, users WHERE users.user_id = awards.user_id
Example (Update) 
builder := With(
	CTETable("users", "user_id").As(
		Select("user_id").From("vip_users"),
	),
).Update("orders").Set(
	"orders.transport_fee = 0",
).Where(
	"users.user_id = orders.user_id",
)

sqlForMySQL, _ := builder.BuildWithFlavor(MySQL)
sqlForPostgreSQL, _ := builder.BuildWithFlavor(PostgreSQL)

fmt.Println(sqlForMySQL)
fmt.Println(sqlForPostgreSQL)

Output:
WITH users (user_id) AS (SELECT user_id FROM vip_users) UPDATE orders, users SET orders.transport_fee = 0 WHERE users.user_id = orders.user_id
WITH users (user_id) AS (SELECT user_id FROM vip_users) UPDATE orders SET orders.transport_fee = 0 FROM users WHERE users.user_id = orders.user_id

func With 

func With(tables ...*CTEQueryBuilder) *CTEBuilder

With creates a new CTE builder with default flavor.

Example 
sb := With(
	CTETable("users", "id", "name").As(
		Select("id", "name").From("users").Where("name IS NOT NULL"),
	),
	CTETable("devices").As(
		Select("device_id").From("devices"),
	),
).Select("users.id", "orders.id", "devices.device_id").Join(
	"orders",
	"users.id = orders.user_id",
	"devices.device_id = orders.device_id",
)

fmt.Println(sb)

Output:
WITH users (id, name) AS (SELECT id, name FROM users WHERE name IS NOT NULL), devices AS (SELECT device_id FROM devices) SELECT users.id, orders.id, devices.device_id FROM users, devices JOIN orders ON users.id = orders.user_id AND devices.device_id = orders.device_id

func WithRecursive 

func WithRecursive(tables ...*CTEQueryBuilder) *CTEBuilder

WithRecursive creates a new recursive CTE builder with default flavor.

Example 
sb := WithRecursive(
	CTEQuery("source_accounts", "id", "parent_id").As(
		UnionAll(
			Select("p.id", "p.parent_id").
				From("accounts AS p").
				Where("p.id = 2"), // Show orders for account 2 and all its child accounts
			Select("c.id", "c.parent_id").
				From("accounts AS c").
				Join("source_accounts AS sa", "c.parent_id = sa.id"),
		),
	),
).Select("o.id", "o.date", "o.amount").
	From("orders AS o").
	Join("source_accounts", "o.account_id = source_accounts.id")

fmt.Println(sb)

Output:
WITH RECURSIVE source_accounts (id, parent_id) AS ((SELECT p.id, p.parent_id FROM accounts AS p WHERE p.id = 2) UNION ALL (SELECT c.id, c.parent_id FROM accounts AS c JOIN source_accounts AS sa ON c.parent_id = sa.id)) SELECT o.id, o.date, o.amount FROM orders AS o JOIN source_accounts ON o.account_id = source_accounts.id

func (*CTEBuilder) Build 

func (cteb *CTEBuilder) Build() (sql string, args []interface{})

Build returns compiled CTE string and args.

func (*CTEBuilder) BuildWithFlavor 

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

BuildWithFlavor builds a CTE with the specified flavor and initial arguments.

func (*CTEBuilder) Clone 

func (cteb *CTEBuilder) Clone() *CTEBuilder

Clone returns a deep copy of CTEBuilder. It's useful when you want to create a base builder and clone it to build similar queries.

func (*CTEBuilder) DeleteFrom 

func (cteb *CTEBuilder) DeleteFrom(table string) *DeleteBuilder

DeleteFrom creates a new DeleteBuilder to build a DELETE statement using this CTE.

func (*CTEBuilder) Flavor 

func (cteb *CTEBuilder) Flavor() Flavor

Flavor returns flavor of builder

func (*CTEBuilder) SQL 

func (cteb *CTEBuilder) SQL(sql string) *CTEBuilder

SQL adds an arbitrary sql to current position.

func (*CTEBuilder) Select 

func (cteb *CTEBuilder) Select(col ...string) *SelectBuilder

Select creates a new SelectBuilder to build a SELECT statement using this CTE.

func (*CTEBuilder) SetFlavor 

func (cteb *CTEBuilder) SetFlavor(flavor Flavor) (old Flavor)

SetFlavor sets the flavor of compiled sql.

func (*CTEBuilder) String 

func (cteb *CTEBuilder) String() string

String returns the compiled CTE string.

func (*CTEBuilder) TableNames 

func (cteb *CTEBuilder) TableNames() []string

TableNames returns all table names in a CTE.

func (*CTEBuilder) Update 

func (cteb *CTEBuilder) Update(table string) *UpdateBuilder

Update creates a new UpdateBuilder to build an UPDATE statement using this CTE.

func (*CTEBuilder) With 

func (cteb *CTEBuilder) With(queries ...*CTEQueryBuilder) *CTEBuilder

With sets the CTE name and columns.

func (*CTEBuilder) WithRecursive 

func (cteb *CTEBuilder) WithRecursive(queries ...*CTEQueryBuilder) *CTEBuilder

WithRecursive sets the CTE name and columns and turns on the RECURSIVE keyword.

type CTEQueryBuilder 

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

CTEQueryBuilder is a builder to build one table in CTE (Common Table Expression).

func CTEQuery 

func CTEQuery(name string, cols ...string) *CTEQueryBuilder

CTEQuery creates a new CTE query builder with default flavor.

func CTETable 

func CTETable(name string, cols ...string) *CTEQueryBuilder

CTETable creates a new CTE query builder with default flavor, marking it as a table.

The resulting CTE query can be used in a `SelectBuilder“, where its table name will be automatically included in the FROM clause.

func (*CTEQueryBuilder) AddToTableList 

func (ctetb *CTEQueryBuilder) AddToTableList() *CTEQueryBuilder

AddToTableList sets flag to add table name to table list in FROM clause of SELECT statement.

func (*CTEQueryBuilder) As 

func (ctetb *CTEQueryBuilder) As(builder Builder) *CTEQueryBuilder

As sets the builder to select data.

func (*CTEQueryBuilder) Build 

func (ctetb *CTEQueryBuilder) Build() (sql string, args []interface{})

Build returns compiled CTE string and args.

func (*CTEQueryBuilder) BuildWithFlavor 

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

BuildWithFlavor builds a CTE with the specified flavor and initial arguments.

func (*CTEQueryBuilder) Clone 

func (ctetb *CTEQueryBuilder) Clone() *CTEQueryBuilder

Clone returns a deep copy of CTEQueryBuilder. It's useful when you want to create a base builder and clone it to build similar queries.

func (*CTEQueryBuilder) Flavor 

func (ctetb *CTEQueryBuilder) Flavor() Flavor

Flavor returns flavor of builder

func (*CTEQueryBuilder) SQL 

func (ctetb *CTEQueryBuilder) SQL(sql string) *CTEQueryBuilder

SQL adds an arbitrary sql to current position.

func (*CTEQueryBuilder) SetFlavor 

func (ctetb *CTEQueryBuilder) SetFlavor(flavor Flavor) (old Flavor)

SetFlavor sets the flavor of compiled sql.

func (*CTEQueryBuilder) ShouldAddToTableList 

func (ctetb *CTEQueryBuilder) ShouldAddToTableList() bool

ShouldAddToTableList returns flag to add table name to table list in FROM clause of SELECT statement.

func (*CTEQueryBuilder) String 

func (ctetb *CTEQueryBuilder) String() string

String returns the compiled CTE string.

func (*CTEQueryBuilder) Table 

func (ctetb *CTEQueryBuilder) Table(name string, cols ...string) *CTEQueryBuilder

Table sets the table name and columns in a CTE table.

func (*CTEQueryBuilder) TableName 

func (ctetb *CTEQueryBuilder) TableName() string

TableName returns the CTE table name.

type CTETableBuilder deprecated 

type CTETableBuilder = CTEQueryBuilder

CTETableBuilder is an alias of CTEQueryBuilder for backward compatibility.

Deprecated: use CTEQueryBuilder instead.

type Column 

type Column[T any] struct {
	ParentAlias   string
	Name          string
	Type          string     // Manual SQL type override
	AbstractType  ColumnType // Abstract type for equivalent mapping
	Default       T
	HasDefault    bool
	AutoIncrement bool
	Length        *int // For string types like varchar, char
	Precision     *int // For decimal
	Scale         *int // For decimal
}

Column represents a table column with generic type T.

func BigInt 

func BigInt(name string, args ...ColumnOption[int64]) *Column[any]

BigInt creates a column for large integers. Go type: int64 Abstract type: ColumnTypeBigInt

func Binary 

func Binary(name string, args ...ColumnOption[[]byte]) *Column[any]

Binary creates a column for fixed-length binary strings. Go type: []byte Abstract type: ColumnTypeBinary Default length: 1, override with WithLength.

func Bit 

func Bit(name string, args ...ColumnOption[int64]) *Column[any]

Bit creates a column for bit values. Go type: int64 Abstract type: ColumnTypeBit Default length: 1 (bits), override with WithLength for multi-bit where supported.

func Blob 

func Blob(name string, args ...ColumnOption[[]byte]) *Column[any]

Blob creates a column for binary data. Go type: []byte Abstract type: ColumnTypeBlob

func Boolean 

func Boolean(name string, args ...ColumnOption[bool]) *Column[any]

Boolean creates a column for booleans. Go type: bool Abstract type: ColumnTypeBoolean

func Char 

func Char(name string, args ...ColumnOption[string]) *Column[any]

Char creates a column for fixed-length strings. Go type: string Abstract type: ColumnTypeChar Default length: 1, override with WithLength.

func Date 

func Date(name string, args ...ColumnOption[time.Time]) *Column[any]

Date creates a column for dates. Go type: time.Time Abstract type: ColumnTypeDate

func DateTime 

func DateTime(name string, args ...ColumnOption[time.Time]) *Column[any]

DateTime creates a column for datetimes. Go type: time.Time Abstract type: ColumnTypeDateTime

func Decimal 

func Decimal(name string, args ...ColumnOption[string]) *Column[any]

Decimal creates a column for decimals. Go type: string Abstract type: ColumnTypeDecimal Default precision/scale: 10,2, override with WithPrecision.

func Double 

func Double(name string, args ...ColumnOption[float64]) *Column[any]

Double creates a column for double-precision floats. Go type: float64 Abstract type: ColumnTypeDouble

func Int 

func Int(name string, args ...ColumnOption[int32]) *Column[any]

Int creates a column for integers. Go type: int32 Abstract type: ColumnTypeInt

func Json 

func Json(name string, args ...ColumnOption[string]) *Column[any]

Json creates a column for JSON data. Go type: string Abstract type: ColumnTypeJson

func Money 

func Money(name string, args ...ColumnOption[string]) *Column[any]

Money creates a column for currency amounts. Go type: string Abstract type: ColumnTypeMoney Default precision/scale: 19,4, override with WithPrecision.

func Real 

func Real(name string, args ...ColumnOption[float32]) *Column[any]

Real creates a column for single-precision floats. Go type: float32 Abstract type: ColumnTypeReal

func SmallInt 

func SmallInt(name string, args ...ColumnOption[int16]) *Column[any]

SmallInt creates a column for small integers. Go type: int16 Abstract type: ColumnTypeSmallInt

func Text 

func Text(name string, args ...ColumnOption[string]) *Column[any]

Text creates a column for long text. Go type: string Abstract type: ColumnTypeText

func Time 

func Time(name string, args ...ColumnOption[time.Time]) *Column[any]

Time creates a column for times. Go type: time.Time Abstract type: ColumnTypeTime

func Timestamp 

func Timestamp(name string, args ...ColumnOption[time.Time]) *Column[any]

Timestamp creates a column for timestamps. Go type: time.Time Abstract type: ColumnTypeTimestamp

func TinyInt 

func TinyInt(name string, args ...ColumnOption[int8]) *Column[any]

TinyInt creates a column for tiny integers. Go type: int8 Abstract type: ColumnTypeTinyInt

func Uuid 

func Uuid(name string, args ...ColumnOption[string]) *Column[any]

Uuid creates a column for UUIDs. Go type: string Abstract type: ColumnTypeUuid

func Varbinary 

func Varbinary(name string, args ...ColumnOption[[]byte]) *Column[any]

Varbinary creates a column for variable-length binary strings. Go type: []byte Abstract type: ColumnTypeVarbinary Default length: 255, override with WithLength.

func Varchar 

func Varchar(name string, args ...ColumnOption[string]) *Column[any]

Varchar creates a column for variable-length strings. Go type: string Abstract type: ColumnTypeVarchar Default length: 255, override with WithLength.

func Xml 

func Xml(name string, args ...ColumnOption[string]) *Column[any]

Xml creates a column for XML data. Go type: string Abstract type: ColumnTypeXml

func (*Column[T]) String 

func (c *Column[T]) String() string

func (*Column[T]) WithAlias 

func (c *Column[T]) WithAlias(alias string) *Column[T]

WithAlias creates a new column with the specified alias

type ColumnInfo 

type ColumnInfo struct {
	Name          string
	GoType        string
	SQLType       string
	AbstractType  string
	AutoIncrement bool
	HasDefault    bool
	DefaultValue  interface{}
	Length        *int
	Precision     *int
	Scale         *int
}

ColumnInfo represents information about a column

type ColumnOption 

type ColumnOption[T any] func(*Column[T])

ColumnOption is a function to configure a Column.

func WithAutoIncrement 

func WithAutoIncrement[T Numeric](active bool) ColumnOption[T]

WithAutoIncrement enables auto-increment for numeric columns.

func WithDefault 

func WithDefault[T any](value T) ColumnOption[T]

WithDefault sets the default value.

func WithLength 

func WithLength[T any](length int) ColumnOption[T]

WithLength sets the length for string types.

func WithPrecision 

func WithPrecision[T any](precision, scale int) ColumnOption[T]

WithPrecision sets the precision and scale for decimal types.

func WithType 

func WithType[T any, U ColumnTypeOrString](typ U) ColumnOption[T]

WithType sets a manual SQL type override.

type ColumnType 

type ColumnType int

ColumnType represents all column types across supported databases. Types are organized by database category with reserved number ranges: 0-27: Shared types (common across databases) 1000-1025: PostgreSQL specific types 2000-2019: MySQL/MariaDB specific types 3000-3015: SQL Server specific types 4000-4009: CQL (Cassandra) specific types 5000-5019: ClickHouse specific types 6000-6015: Presto specific types 7000-7019: Oracle specific types 8000-8013: Informix specific types 

const (
	// ===== SHARED TYPES (0-27) =====
	// ColumnTypeVarchar represents a variable-length string with a specified maximum length.
	// Go type: string
	// Database: Variable-length character string, e.g., VARCHAR(length) with default length 255.
	ColumnTypeVarchar ColumnType = iota

	// ColumnTypeChar represents a fixed-length string.
	// Go type: string
	// Database: Fixed-length character string, e.g., CHAR(length) with default length 1.
	ColumnTypeChar

	// ColumnTypeText represents an unlimited-length text string.
	// Go type: string
	// Database: Long text storage, e.g., TEXT or CLOB.
	ColumnTypeText

	// ColumnTypeTinyInt represents a very small integer.
	// Go type: int8
	// Database: 8-bit integer, e.g., TINYINT or SMALLINT.
	ColumnTypeTinyInt

	// ColumnTypeSmallInt represents a small integer.
	// Go type: int16
	// Database: 16-bit integer, e.g., SMALLINT.
	ColumnTypeSmallInt

	// ColumnTypeInt represents a standard integer.
	// Go type: int32
	// Database: 32-bit integer, e.g., INT or INTEGER.
	ColumnTypeInt

	// ColumnTypeBigInt represents a large integer.
	// Go type: int64
	// Database: 64-bit integer, e.g., BIGINT.
	ColumnTypeBigInt

	// ColumnTypeBoolean represents a boolean value.
	// Go type: bool
	// Database: Boolean or equivalent, e.g., BOOLEAN or TINYINT(1).
	ColumnTypeBoolean

	// ColumnTypeReal represents a single-precision floating-point number.
	// Go type: float32
	// Database: Single-precision float, e.g., REAL or FLOAT.
	ColumnTypeReal

	// ColumnTypeDouble represents a double-precision floating-point number.
	// Go type: float64
	// Database: Double-precision float, e.g., DOUBLE or FLOAT(53).
	ColumnTypeDouble

	// ColumnTypeDecimal represents a fixed-precision decimal number.
	// Go type: string (to avoid precision loss)
	// Database: Exact numeric type, e.g., DECIMAL(precision, scale) with defaults 10,2.
	ColumnTypeDecimal

	// ColumnTypeDate represents a date without time.
	// Go type: time.Time
	// Database: Date type, e.g., DATE.
	ColumnTypeDate

	// ColumnTypeTime represents a time without date.
	// Go type: time.Time
	// Database: Time type, e.g., TIME.
	ColumnTypeTime

	// ColumnTypeDateTime represents a date and time without timezone.
	// Go type: time.Time
	// Database: Datetime type, e.g., DATETIME.
	ColumnTypeDateTime

	// ColumnTypeTimestamp represents a timestamp, possibly with timezone.
	// Go type: time.Time
	// Database: Timestamp type, e.g., TIMESTAMP.
	ColumnTypeTimestamp

	// ColumnTypeBlob represents binary large object.
	// Go type: []byte
	// Database: Binary storage, e.g., BLOB or BYTEA.
	ColumnTypeBlob

	// ColumnTypeJson represents JSON data.
	// Go type: string
	// Database: JSON type where supported, else TEXT.
	ColumnTypeJson

	// ColumnTypeUuid represents a universally unique identifier.
	// Go type: string
	// Database: UUID type where supported, else VARCHAR(36).
	ColumnTypeUuid

	// ColumnTypeBit represents a bit or bit string.
	// Go type: int64
	// Database: Bit type, e.g., BIT or BOOLEAN for single bit.
	ColumnTypeBit

	// ColumnTypeBinary represents a fixed-length binary string.
	// Go type: []byte
	// Database: Fixed-length binary, e.g., BINARY(length) with default length 1.
	ColumnTypeBinary

	// ColumnTypeVarbinary represents a variable-length binary string.
	// Go type: []byte
	// Database: Variable-length binary, e.g., VARBINARY(length) with default length 255.
	ColumnTypeVarbinary

	// ColumnTypeMoney represents a currency amount.
	// Go type: string
	// Database: Money type where supported, else DECIMAL(19,4).
	ColumnTypeMoney

	// ColumnTypeXml represents XML data.
	// Go type: string
	// Database: XML type where supported, else TEXT.
	ColumnTypeXml

	// ===== POSTGRESQL TYPES (1000-1025) =====
	// ColumnTypePostgresJsonb represents binary JSON storage in PostgreSQL.
	// Go type: string
	// Database: JSONB
	ColumnTypePostgresJsonb ColumnType = 1000 + iota

	// ColumnTypePostgresHstore represents key-value pair storage in PostgreSQL.
	// Go type: map[string]string or string
	// Database: HSTORE
	ColumnTypePostgresHstore

	// ColumnTypePostgresTsVector represents text search vector in PostgreSQL.
	// Go type: string
	// Database: TSVECTOR
	ColumnTypePostgresTsVector

	// ColumnTypePostgresMoney represents currency amount in PostgreSQL.
	// Go type: string
	// Database: MONEY
	ColumnTypePostgresMoney

	// ColumnTypePostgresInterval represents time span in PostgreSQL.
	// Go type: string or custom
	// Database: INTERVAL
	ColumnTypePostgresInterval

	// ColumnTypePostgresInet represents IPv4 or IPv6 network address in PostgreSQL.
	// Go type: string
	// Database: INET
	ColumnTypePostgresInet

	// ColumnTypePostgresMacaddr represents MAC address in PostgreSQL.
	// Go type: string
	// Database: MACADDR
	ColumnTypePostgresMacaddr

	// ColumnTypePostgresMacaddr8 represents MAC address (EUI-64) in PostgreSQL.
	// Go type: string
	// Database: MACADDR8
	ColumnTypePostgresMacaddr8

	// ColumnTypePostgresBit represents fixed-length bit string in PostgreSQL.
	// Go type: string or int64
	// Database: BIT
	ColumnTypePostgresBit

	// ColumnTypePostgresVarbit represents variable-length bit string in PostgreSQL.
	// Go type: string or int64
	// Database: VARBIT
	ColumnTypePostgresVarbit

	// ColumnTypePostgresBox represents rectangular box on a plane in PostgreSQL.
	// Go type: custom
	// Database: BOX
	ColumnTypePostgresBox

	// ColumnTypePostgresCircle represents circle on a plane in PostgreSQL.
	// Go type: custom
	// Database: CIRCLE
	ColumnTypePostgresCircle

	// ColumnTypePostgresLine represents infinite line on a plane in PostgreSQL.
	// Go type: custom
	// Database: LINE
	ColumnTypePostgresLine

	// ColumnTypePostgresLseg represents line segment on a plane in PostgreSQL.
	// Go type: custom
	// Database: LSEG
	ColumnTypePostgresLseg

	// ColumnTypePostgresPath represents geometric path on a plane in PostgreSQL.
	// Go type: custom
	// Database: PATH
	ColumnTypePostgresPath

	// ColumnTypePostgresPolygon represents closed geometric path on a plane in PostgreSQL.
	// Go type: custom
	// Database: POLYGON
	ColumnTypePostgresPolygon

	// ColumnTypePostgresTsquery represents text search query in PostgreSQL.
	// Go type: string
	// Database: TSQUERY
	ColumnTypePostgresTsquery

	// ColumnTypePostgresJsonpath represents JSON path in PostgreSQL.
	// Go type: string
	// Database: JSONPATH
	ColumnTypePostgresJsonpath

	// ColumnTypePostgresXml represents XML data in PostgreSQL.
	// Go type: string
	// Database: XML
	ColumnTypePostgresXml

	// ColumnTypePostgresArray represents array in PostgreSQL.
	// Go type: []any
	// Database: ARRAY
	ColumnTypePostgresArray

	// ColumnTypePostgresRange represents range in PostgreSQL.
	// Go type: custom
	// Database: RANGE
	ColumnTypePostgresRange

	// ColumnTypePostgresMultirange represents multirange in PostgreSQL.
	// Go type: custom
	// Database: MULTIRANGE
	ColumnTypePostgresMultirange

	// ColumnTypePostgresPgLsn represents PostgreSQL Log Sequence Number.
	// Go type: string
	// Database: PG_LSN
	ColumnTypePostgresPgLsn

	// ColumnTypePostgresPgSnapshot represents user-level transaction ID snapshot in PostgreSQL.
	// Go type: string
	// Database: PG_SNAPSHOT
	ColumnTypePostgresPgSnapshot

	// ===== MYSQL/MARIADB TYPES (2000-2019) =====
	// ColumnTypeMySQLSet represents a set of values in MySQL.
	// Go type: string
	// Database: SET
	ColumnTypeMySQLSet ColumnType = 2000 + iota

	// ColumnTypeMySQLEnum represents an enumeration in MySQL.
	// Go type: string
	// Database: ENUM
	ColumnTypeMySQLEnum

	// ColumnTypeMySQLPoint represents a spatial point in MySQL.
	// Go type: string or custom
	// Database: POINT
	ColumnTypeMySQLPoint

	// ColumnTypeMySQLTinytext represents tiny text in MySQL.
	// Go type: string
	// Database: TINYTEXT
	ColumnTypeMySQLTinytext

	// ColumnTypeMySQLMediumtext represents medium text in MySQL.
	// Go type: string
	// Database: MEDIUMTEXT
	ColumnTypeMySQLMediumtext

	// ColumnTypeMySQLLongtext represents long text in MySQL.
	// Go type: string
	// Database: LONGTEXT
	ColumnTypeMySQLLongtext

	// ColumnTypeMySQLTinyblob represents tiny blob in MySQL.
	// Go type: []byte
	// Database: TINYBLOB
	ColumnTypeMySQLTinyblob

	// ColumnTypeMySQLMediumblob represents medium blob in MySQL.
	// Go type: []byte
	// Database: MEDIUMBLOB
	ColumnTypeMySQLMediumblob

	// ColumnTypeMySQLLongblob represents long blob in MySQL.
	// Go type: []byte
	// Database: LONGBLOB
	ColumnTypeMySQLLongblob

	// ColumnTypeMySQLYear represents year in MySQL.
	// Go type: int
	// Database: YEAR
	ColumnTypeMySQLYear

	// ColumnTypeMySQLGeometry represents geometry in MySQL.
	// Go type: custom
	// Database: GEOMETRY
	ColumnTypeMySQLGeometry

	// ColumnTypeMySQLLinestring represents line string in MySQL.
	// Go type: custom
	// Database: LINESTRING
	ColumnTypeMySQLLinestring

	// ColumnTypeMySQLPolygon represents polygon in MySQL.
	// Go type: custom
	// Database: POLYGON
	ColumnTypeMySQLPolygon

	// ColumnTypeMySQLMultipoint represents multi point in MySQL.
	// Go type: custom
	// Database: MULTIPOINT
	ColumnTypeMySQLMultipoint

	// ColumnTypeMySQLMultilinestring represents multi line string in MySQL.
	// Go type: custom
	// Database: MULTILINESTRING
	ColumnTypeMySQLMultilinestring

	// ColumnTypeMySQLMultipolygon represents multi polygon in MySQL.
	// Go type: custom
	// Database: MULTIPOLYGON
	ColumnTypeMySQLMultipolygon

	// ColumnTypeMySQLGeometrycollection represents geometry collection in MySQL.
	// Go type: custom
	// Database: GEOMETRYCOLLECTION
	ColumnTypeMySQLGeometrycollection

	// ===== SQL SERVER TYPES (3000-3015) =====
	// ColumnTypeSQLServerXml represents XML data in SQL Server.
	// Go type: string
	// Database: XML
	ColumnTypeSQLServerXml ColumnType = 3000 + iota

	// ColumnTypeSQLServerGeography represents spatial geography data in SQL Server.
	// Go type: custom or []byte
	// Database: GEOGRAPHY
	ColumnTypeSQLServerGeography

	// ColumnTypeSQLServerGeometry represents spatial geometry data in SQL Server.
	// Go type: custom or []byte
	// Database: GEOMETRY
	ColumnTypeSQLServerGeometry

	// ColumnTypeSQLServerHierarchyid represents hierarchy ID in SQL Server.
	// Go type: string
	// Database: HIERARCHYID
	ColumnTypeSQLServerHierarchyid

	// ColumnTypeSQLServerUniqueidentifier represents unique identifier in SQL Server.
	// Go type: string
	// Database: UNIQUEIDENTIFIER
	ColumnTypeSQLServerUniqueidentifier

	// ColumnTypeSQLServerImage represents image in SQL Server.
	// Go type: []byte
	// Database: IMAGE
	ColumnTypeSQLServerImage

	// ColumnTypeSQLServerNtext represents ntext in SQL Server.
	// Go type: string
	// Database: NTEXT
	ColumnTypeSQLServerNtext

	// ColumnTypeSQLServerSqlVariant represents SQL variant in SQL Server.
	// Go type: any
	// Database: SQL_VARIANT
	ColumnTypeSQLServerSqlVariant

	// ColumnTypeSQLServerTimestamp represents timestamp (rowversion) in SQL Server.
	// Go type: []byte
	// Database: TIMESTAMP
	ColumnTypeSQLServerTimestamp

	// ColumnTypeSQLServerMoney represents money in SQL Server.
	// Go type: string
	// Database: MONEY
	ColumnTypeSQLServerMoney

	// ColumnTypeSQLServerSmallmoney represents smallmoney in SQL Server.
	// Go type: string
	// Database: SMALLMONEY
	ColumnTypeSQLServerSmallmoney

	// ColumnTypeSQLServerDatetime2 represents datetime2 in SQL Server.
	// Go type: time.Time
	// Database: DATETIME2
	ColumnTypeSQLServerDatetime2

	// ColumnTypeSQLServerDatetimeoffset represents datetimeoffset in SQL Server.
	// Go type: time.Time
	// Database: DATETIMEOFFSET
	ColumnTypeSQLServerDatetimeoffset

	// ColumnTypeSQLServerSmalldatetime represents smalldatetime in SQL Server.
	// Go type: time.Time
	// Database: SMALLDATETIME
	ColumnTypeSQLServerSmalldatetime

	// ===== CQL (CASSANDRA) TYPES (4000-4009) =====
	// ColumnTypeCQLCounter represents a counter column in Cassandra.
	// Go type: int64
	// Database: COUNTER
	ColumnTypeCQLCounter ColumnType = 4000 + iota

	// ColumnTypeCQLDuration represents a duration in Cassandra.
	// Go type: string
	// Database: DURATION
	ColumnTypeCQLDuration

	// ColumnTypeCQLInet represents an IP address in Cassandra.
	// Go type: string
	// Database: INET
	ColumnTypeCQLInet

	// ColumnTypeCQLList represents a list in Cassandra.
	// Go type: []any
	// Database: LIST
	ColumnTypeCQLList

	// ColumnTypeCQLMap represents a map in Cassandra.
	// Go type: map[any]any
	// Database: MAP
	ColumnTypeCQLMap

	// ColumnTypeCQLSet represents a set in Cassandra.
	// Go type: []any
	// Database: SET
	ColumnTypeCQLSet

	// ColumnTypeCQLTuple represents a tuple in Cassandra.
	// Go type: any
	// Database: TUPLE
	ColumnTypeCQLTuple

	// ColumnTypeCQLVector represents a vector in Cassandra.
	// Go type: []float64
	// Database: VECTOR
	ColumnTypeCQLVector

	// ===== CLICKHOUSE TYPES (5000-5019) =====
	// ColumnTypeClickHouseLowCardinality represents low cardinality string in ClickHouse.
	// Go type: string
	// Database: LowCardinality
	ColumnTypeClickHouseLowCardinality ColumnType = 5000 + iota

	// ColumnTypeClickHouseNullable represents nullable type in ClickHouse.
	// Go type: any
	// Database: Nullable
	ColumnTypeClickHouseNullable

	// ColumnTypeClickHouseArray represents array in ClickHouse.
	// Go type: []any
	// Database: Array
	ColumnTypeClickHouseArray

	// ColumnTypeClickHouseMap represents map in ClickHouse.
	// Go type: map[any]any
	// Database: Map
	ColumnTypeClickHouseMap

	// ColumnTypeClickHouseTuple represents tuple in ClickHouse.
	// Go type: any
	// Database: Tuple
	ColumnTypeClickHouseTuple

	// ColumnTypeClickHouseNested represents nested in ClickHouse.
	// Go type: any
	// Database: Nested
	ColumnTypeClickHouseNested

	// ColumnTypeClickHouseEnum8 represents 8-bit enum in ClickHouse.
	// Go type: string
	// Database: Enum8
	ColumnTypeClickHouseEnum8

	// ColumnTypeClickHouseEnum16 represents 16-bit enum in ClickHouse.
	// Go type: string
	// Database: Enum16
	ColumnTypeClickHouseEnum16

	// ColumnTypeClickHouseDate32 represents 32-bit date in ClickHouse.
	// Go type: time.Time
	// Database: Date32
	ColumnTypeClickHouseDate32

	// ColumnTypeClickHouseDateTime64 represents datetime64 in ClickHouse.
	// Go type: time.Time
	// Database: DateTime64
	ColumnTypeClickHouseDateTime64

	// ColumnTypeClickHouseIPv4 represents IPv4 in ClickHouse.
	// Go type: string
	// Database: IPv4
	ColumnTypeClickHouseIPv4

	// ColumnTypeClickHouseIPv6 represents IPv6 in ClickHouse.
	// Go type: string
	// Database: IPv6
	ColumnTypeClickHouseIPv6

	// ColumnTypeClickHouseObjectJson represents JSON object in ClickHouse.
	// Go type: string
	// Database: Object('json')
	ColumnTypeClickHouseObjectJson

	// ColumnTypeClickHouseDecimal32 represents 32-bit decimal in ClickHouse.
	// Go type: string
	// Database: Decimal32
	ColumnTypeClickHouseDecimal32

	// ColumnTypeClickHouseDecimal64 represents 64-bit decimal in ClickHouse.
	// Go type: string
	// Database: Decimal64
	ColumnTypeClickHouseDecimal64

	// ColumnTypeClickHouseDecimal128 represents 128-bit decimal in ClickHouse.
	// Go type: string
	// Database: Decimal128
	ColumnTypeClickHouseDecimal128

	// ColumnTypeClickHouseDecimal256 represents 256-bit decimal in ClickHouse.
	// Go type: string
	// Database: Decimal256
	ColumnTypeClickHouseDecimal256

	// ColumnTypeClickHouseAggregateFunction represents aggregate function in ClickHouse.
	// Go type: any
	// Database: AggregateFunction
	ColumnTypeClickHouseAggregateFunction

	// ColumnTypeClickHouseSimpleAggregateFunction represents simple aggregate function in ClickHouse.
	// Go type: any
	// Database: SimpleAggregateFunction
	ColumnTypeClickHouseSimpleAggregateFunction

	// ===== PRESTO TYPES (6000-6015) =====
	// ColumnTypePrestoRow represents a row/struct in Presto.
	// Go type: any
	// Database: ROW
	ColumnTypePrestoRow ColumnType = 6000 + iota

	// ColumnTypePrestoArray represents array in Presto.
	// Go type: []any
	// Database: ARRAY
	ColumnTypePrestoArray

	// ColumnTypePrestoMap represents map in Presto.
	// Go type: map[any]any
	// Database: MAP
	ColumnTypePrestoMap

	// ColumnTypePrestoIntervalYearToMonth represents interval year to month in Presto.
	// Go type: string
	// Database: INTERVAL YEAR TO MONTH
	ColumnTypePrestoIntervalYearToMonth

	// ColumnTypePrestoIntervalDayToSecond represents interval day to second in Presto.
	// Go type: string
	// Database: INTERVAL DAY TO SECOND
	ColumnTypePrestoIntervalDayToSecond

	// ColumnTypePrestoIpaddress represents IP address in Presto.
	// Go type: string
	// Database: IPADDRESS
	ColumnTypePrestoIpaddress

	// ColumnTypePrestoGeometry represents geometry in Presto.
	// Go type: custom
	// Database: GEOMETRY
	ColumnTypePrestoGeometry

	// ColumnTypePrestoBingTile represents bing tile in Presto.
	// Go type: custom
	// Database: BING_TILE
	ColumnTypePrestoBingTile

	// ColumnTypePrestoHyperloglog represents hyperloglog in Presto.
	// Go type: custom
	// Database: HYPERLOGLOG
	ColumnTypePrestoHyperloglog

	// ColumnTypePrestoP4hyperloglog represents p4hyperloglog in Presto.
	// Go type: custom
	// Database: P4HYPERLOGLOG
	ColumnTypePrestoP4hyperloglog

	// ColumnTypePrestoQdigest represents qdigest in Presto.
	// Go type: custom
	// Database: QDIGEST
	ColumnTypePrestoQdigest

	// ColumnTypePrestoTdigest represents tdigest in Presto.
	// Go type: custom
	// Database: TDIGEST
	ColumnTypePrestoTdigest

	// ColumnTypePrestoBarcode represents barcode in Presto.
	// Go type: string
	// Database: BARCODE
	ColumnTypePrestoBarcode

	// ColumnTypePrestoTimeWithTimezone represents time with time zone in Presto.
	// Go type: time.Time
	// Database: TIME WITH TIME ZONE
	ColumnTypePrestoTimeWithTimezone

	// ColumnTypePrestoTimestampWithTimezone represents timestamp with time zone in Presto.
	// Go type: time.Time
	// Database: TIMESTAMP WITH TIME ZONE
	ColumnTypePrestoTimestampWithTimezone

	// ===== ORACLE TYPES (7000-7019) =====
	// ColumnTypeOracleNclob represents national character large object in Oracle.
	// Go type: string
	// Database: NCLOB
	ColumnTypeOracleNclob ColumnType = 7000 + iota

	// ColumnTypeOracleRaw represents raw binary data in Oracle.
	// Go type: []byte
	// Database: RAW
	ColumnTypeOracleRaw

	// ColumnTypeOracleBinaryFloat represents binary float in Oracle.
	// Go type: float32
	// Database: BINARY_FLOAT
	ColumnTypeOracleBinaryFloat

	// ColumnTypeOracleBinaryDouble represents binary double in Oracle.
	// Go type: float64
	// Database: BINARY_DOUBLE
	ColumnTypeOracleBinaryDouble

	// ColumnTypeOracleIntervalYearToMonth represents interval year to month in Oracle.
	// Go type: string
	// Database: INTERVAL YEAR TO MONTH
	ColumnTypeOracleIntervalYearToMonth

	// ColumnTypeOracleIntervalDayToSecond represents interval day to second in Oracle.
	// Go type: string
	// Database: INTERVAL DAY TO SECOND
	ColumnTypeOracleIntervalDayToSecond

	// ColumnTypeOracleUrowid represents universal rowid in Oracle.
	// Go type: string
	// Database: UROWID
	ColumnTypeOracleUrowid

	// ColumnTypeOracleAnydata represents anydata in Oracle.
	// Go type: any
	// Database: ANYDATA
	ColumnTypeOracleAnydata

	// ColumnTypeOracleAnytype represents anytype in Oracle.
	// Go type: any
	// Database: ANYTYPE
	ColumnTypeOracleAnytype

	// ColumnTypeOracleAnydataset represents anydataset in Oracle.
	// Go type: any
	// Database: ANYDATASET
	ColumnTypeOracleAnydataset

	// ColumnTypeOracleXmltype represents XML type in Oracle.
	// Go type: string
	// Database: XMLTYPE
	ColumnTypeOracleXmltype

	// ColumnTypeOracleUritype represents URI type in Oracle.
	// Go type: string
	// Database: URITYPE
	ColumnTypeOracleUritype

	// ColumnTypeOracleDburitype represents DB URI type in Oracle.
	// Go type: string
	// Database: DBURITYPE
	ColumnTypeOracleDburitype

	// ColumnTypeOracleXdburitype represents XDB URI type in Oracle.
	// Go type: string
	// Database: XDBURITYPE
	ColumnTypeOracleXdburitype

	// ColumnTypeOracleHttpuritype represents HTTP URI type in Oracle.
	// Go type: string
	// Database: HTTPURITYPE
	ColumnTypeOracleHttpuritype

	// ColumnTypeOracleSdoGeometry represents SDO geometry in Oracle.
	// Go type: custom
	// Database: SDO_GEOMETRY
	ColumnTypeOracleSdoGeometry

	// ColumnTypeOracleSdoTopoGeometry represents SDO topo geometry in Oracle.
	// Go type: custom
	// Database: SDO_TOPO_GEOMETRY
	ColumnTypeOracleSdoTopoGeometry

	// ColumnTypeOracleSdoGeoraster represents SDO georaster in Oracle.
	// Go type: custom
	// Database: SDO_GEORASTER
	ColumnTypeOracleSdoGeoraster

	// ===== INFORMIX TYPES (8000-8013) =====
	// ColumnTypeInformixLvarchar represents large varchar in Informix.
	// Go type: string
	// Database: LVARCHAR
	ColumnTypeInformixLvarchar ColumnType = 8000 + iota

	// ColumnTypeInformixByte represents byte data in Informix.
	// Go type: []byte
	// Database: BYTE
	ColumnTypeInformixByte

	// ColumnTypeInformixMoney represents money in Informix.
	// Go type: string
	// Database: MONEY
	ColumnTypeInformixMoney

	// ColumnTypeInformixSerial represents serial in Informix.
	// Go type: int
	// Database: SERIAL
	ColumnTypeInformixSerial

	// ColumnTypeInformixSerial8 represents serial8 in Informix.
	// Go type: int64
	// Database: SERIAL8
	ColumnTypeInformixSerial8

	// ColumnTypeInformixBigserial represents bigserial in Informix.
	// Go type: int64
	// Database: BIGSERIAL
	ColumnTypeInformixBigserial

	// ColumnTypeInformixClob represents clob in Informix.
	// Go type: string
	// Database: CLOB
	ColumnTypeInformixClob

	// ColumnTypeInformixInterval represents interval in Informix.
	// Go type: string
	// Database: INTERVAL
	ColumnTypeInformixInterval

	// ColumnTypeInformixList represents list in Informix.
	// Go type: []any
	// Database: LIST
	ColumnTypeInformixList

	// ColumnTypeInformixMultiset represents multiset in Informix.
	// Go type: []any
	// Database: MULTISET
	ColumnTypeInformixMultiset

	// ColumnTypeInformixSet represents set in Informix.
	// Go type: []any
	// Database: SET
	ColumnTypeInformixSet

	// ColumnTypeInformixRow represents row in Informix.
	// Go type: any
	// Database: ROW
	ColumnTypeInformixRow
)

func (ColumnType) String 

func (ct ColumnType) String() string

type ColumnTypeOrString 

type ColumnTypeOrString interface {
	ColumnType | ~string
}

type Cond 

type Cond struct {
	Args *Args
}

Cond provides several helper methods to build conditions.

func NewCond 

func NewCond() *Cond

NewCond returns a new Cond.

func (*Cond) All 

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

All is used to construct the expression "field op ALL (value...)".

func (*Cond) And 

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

And is used to construct the expression AND logic like "expr1 AND expr2 AND expr3".

func (*Cond) Any 

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

Any is used to construct the expression "field op ANY (value...)".

func (*Cond) Between 

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

Between is used to construct the expression "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 

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 is used to construct the expression "field = value".

func (*Cond) Exists 

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

Exists is used to construct the expression "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 

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

GT is an alias of GreaterThan.

func (*Cond) GTE 

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 is used to construct the expression "field >= value".

func (*Cond) GreaterThan 

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

GreaterThan is used to construct the expression "field > value".

func (*Cond) ILike 

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

ILike is used to construct the expression "field ILIKE value".

When the database system does not support the ILIKE operator, the ILike method will return "LOWER(field) LIKE LOWER(value)" to simulate the behavior of the ILIKE operator.

func (*Cond) In 

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

In is used to construct the expression "field IN (value...)".

func (*Cond) IsDistinctFrom 

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

IsDistinctFrom is used to construct the expression "field IS DISTINCT FROM value".

When the database system does not support the IS DISTINCT FROM operator, the NotILike method will return "NOT field <=> value" for MySQL or a "CASE ... WHEN ... ELSE ... END" expression to simulate the behavior of the IS DISTINCT FROM operator.

func (*Cond) IsNotDistinctFrom 

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

IsNotDistinctFrom is used to construct the expression "field IS NOT DISTINCT FROM value".

When the database system does not support the IS NOT DISTINCT FROM operator, the NotILike method will return "field <=> value" for MySQL or a "CASE ... WHEN ... ELSE ... END" expression to simulate the behavior of the IS NOT DISTINCT FROM operator.

func (*Cond) IsNotNull 

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

IsNotNull is used to construct the expression "field IS NOT NULL".

func (*Cond) IsNull 

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

IsNull is used to construct the expression "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 

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

LT is an alias of LessThan.

func (*Cond) LTE 

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 is used to construct the expression "field <= value".

func (*Cond) LessThan 

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

LessThan is used to construct the expression "field < value".

func (*Cond) Like 

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

Like is used to construct the expression "field LIKE value".

func (*Cond) NE 

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

NE is an alias of NotEqual.

func (*Cond) NEQ 

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

NEQ is an alias of NotEqual.

func (*Cond) Not 

func (c *Cond) Not(notExpr string) string

Not is used to construct the expression "NOT expr".

func (*Cond) NotBetween 

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

NotBetween is used to construct the expression "field NOT BETWEEN lower AND upper".

func (*Cond) NotEqual 

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

NotEqual is used to construct the expression "field <> value".

func (*Cond) NotExists 

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

NotExists is used to construct the expression "NOT EXISTS (subquery)".

func (*Cond) NotILike 

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

NotILike is used to construct the expression "field NOT ILIKE value".

When the database system does not support the ILIKE operator, the NotILike method will return "LOWER(field) NOT LIKE LOWER(value)" to simulate the behavior of the ILIKE operator.

func (*Cond) NotIn 

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

NotIn is used to construct the expression "field NOT IN (value...)".

func (*Cond) NotLike 

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

NotLike is used to construct the expression "field NOT LIKE value".

func (*Cond) Or 

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

Or is used to construct the expression OR logic like "expr1 OR expr2 OR expr3".

func (*Cond) Some 

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

Some is used to construct the expression "field op SOME (value...)".

func (*Cond) Var 

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

Var returns a placeholder for value.

type CreateTableBuilder 

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 

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 

func NewCreateTableBuilder() *CreateTableBuilder

NewCreateTableBuilder creates a new CREATE TABLE builder.

func (*CreateTableBuilder) Build 

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 

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

func (ctb *CreateTableBuilder) Clone() *CreateTableBuilder

Clone returns a deep copy of CreateTableBuilder. It's useful when you want to create a base builder and clone it to build similar queries.

func (*CreateTableBuilder) CreateTable 

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

CreateTable sets the table name in CREATE TABLE.

func (*CreateTableBuilder) CreateTempTable 

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 

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

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

func (*CreateTableBuilder) Flavor 

func (ctb *CreateTableBuilder) Flavor() Flavor

Flavor returns flavor of builder

func (*CreateTableBuilder) IfNotExists 

func (ctb *CreateTableBuilder) IfNotExists() *CreateTableBuilder

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

func (*CreateTableBuilder) NumDefine 

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 

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

Option adds a table option in CREATE TABLE.

func (*CreateTableBuilder) SQL 

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 

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

SetFlavor sets the flavor of compiled sql.

func (*CreateTableBuilder) String 

func (ctb *CreateTableBuilder) String() string

String returns the compiled INSERT string.

func (*CreateTableBuilder) Var 

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 

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 ?

func NewDeleteBuilder 

func NewDeleteBuilder() *DeleteBuilder

NewDeleteBuilder creates a new DELETE builder.

func (*DeleteBuilder) AddWhereClause 

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

AddWhereClause adds all clauses in the whereClause to SELECT.

func (*DeleteBuilder) Asc 

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

func (db *DeleteBuilder) Clone() *DeleteBuilder

Clone returns a deep copy of DeleteBuilder. It's useful when you want to create a base builder and clone it to build similar queries.

func (*DeleteBuilder) DeleteFrom 

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

DeleteFrom sets table name in DELETE.

func (*DeleteBuilder) Desc 

func (db *DeleteBuilder) Desc() *DeleteBuilder

Desc sets order of ORDER BY to DESC.

func (*DeleteBuilder) Flavor 

func (db *DeleteBuilder) Flavor() Flavor

Flavor returns flavor of builder

func (*DeleteBuilder) Limit 

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

Limit sets the LIMIT in DELETE.

func (*DeleteBuilder) OrderBy 

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

OrderBy sets columns of ORDER BY in DELETE.

func (*DeleteBuilder) Returning 

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

Returning sets returning columns. For DBMS that doesn't support RETURNING, e.g. MySQL, it will be ignored.

Example 
db := NewDeleteBuilder()
db.DeleteFrom("user")
db.Where(db.Equal("id", 123))
db.Returning("id", "deleted_at")

sql, args := db.BuildWithFlavor(PostgreSQL)
fmt.Println(sql)
fmt.Println(args)

Output:
DELETE FROM user WHERE id = $1 RETURNING id, deleted_at
[123]

func (*DeleteBuilder) SQL 

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 ? /* after limit */
[1234 10]

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

func (db *DeleteBuilder) TableNames() []string

TableNames returns all table names in this DELETE statement.

func (*DeleteBuilder) Where 

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

Where sets expressions of WHERE in DELETE.

func (*DeleteBuilder) With 

func (db *DeleteBuilder) With(builder *CTEBuilder) *DeleteBuilder

With sets WITH clause (the Common Table Expression) before DELETE.

Example 
sql := With(
	CTEQuery("users").As(
		Select("id", "name").From("users").Where("name IS NULL"),
	),
).DeleteFrom("orders").Where(
	"users.id = orders.user_id",
).String()

fmt.Println(sql)

Output:
WITH users AS (SELECT id, name FROM users WHERE name IS NULL) DELETE FROM orders WHERE users.id = orders.user_id

type EntityInfo 

type EntityInfo struct {
	Name    string
	Table   *Table
	Columns []ColumnInfo
}

EntityInfo represents information about an entity to be generated

type FieldMapperFunc 

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 ?
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
	Doris
)

Supported flavors.

func (Flavor) Interpolate 

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

func (f Flavor) NewCTEBuilder() *CTEBuilder

NewCTEBuilder creates a new CTE builder with flavor.

func (Flavor) NewCTEQueryBuilder 

func (f Flavor) NewCTEQueryBuilder() *CTEQueryBuilder

NewCTETableBuilder creates a new CTE table builder with flavor.

func (Flavor) NewCreateTableBuilder 

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 

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 

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 Generator 

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

Generator handles code generation for Grizzle entities

func NewGenerator 

func NewGenerator(outputDir string) *Generator

NewGenerator creates a new code generator

func (*Generator) GenerateFromFile 

func (g *Generator) GenerateFromFile(filePath string) error

GenerateFromFile parses a Go file and generates entity files

type GetAliasFunc 

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 

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 

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 

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

func (ib *InsertBuilder) Clone() *InsertBuilder

Clone returns a deep copy of InsertBuilder. It's useful when you want to create a base builder and clone it to build similar queries.

func (*InsertBuilder) Cols 

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

Cols sets columns in INSERT.

func (*InsertBuilder) Flavor 

func (ib *InsertBuilder) Flavor() Flavor

Flavor returns flavor of builder

func (*InsertBuilder) InsertIgnoreInto 

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 

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 

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

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

Returning sets returning columns. For DBMS that doesn't support RETURNING, e.g. MySQL, it will be ignored.

Example 
sql, args := InsertInto("user").
	Cols("name").Values("Huan Du").
	Returning("id").
	BuildWithFlavor(PostgreSQL)

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

Output:
INSERT INTO user (name) VALUES ($1) RETURNING id
[Huan Du]

func (*InsertBuilder) SQL 

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 

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 

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

Var returns a placeholder for value.

type JoinOption 

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 Numeric 

type Numeric interface {
	~int | ~int8 | ~int16 | ~int32 | ~int64 |
		~uint | ~uint8 | ~uint16 | ~uint32 | ~uint64 | ~uintptr |
		~float32 | ~float64 | ~complex64 | ~complex128
}

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 ? OFFSET ?
[1234 %Du 1 2 5 86400 4 5 10 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, Doris}
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 ? OFFSET ?
#4: SELECT * FROM user LIMIT ?
#5: SELECT * FROM user ORDER BY id LIMIT ? OFFSET ?

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

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

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

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

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

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

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

Informix
#1: SELECT * FROM user
#2: SELECT * FROM user
#3: SELECT * FROM user SKIP ? FIRST ?
#4: SELECT * FROM user FIRST ?
#5: SELECT * FROM user ORDER BY id SKIP ? FIRST ?

Doris
#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
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 

func Select(col ...string) *SelectBuilder

Select sets columns in SELECT.

Example 
// Build a SQL to create a HIVE table using MySQL-like SQL syntax.
sql, args := Select("columns[0] id", "columns[1] name", "columns[2] year").
	From(MySQL.Quote("all-users.csv")).
	Limit(100).
	Build()

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

Output:
SELECT columns[0] id, columns[1] name, columns[2] year FROM `all-users.csv` LIMIT ?
[100]

func (*SelectBuilder) AddWhereClause 

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

func (sb *SelectBuilder) Clone() *SelectBuilder

Clone returns a deep copy of SelectBuilder. It's useful when you want to create a base builder and clone it to build similar queries.

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

func (sb *SelectBuilder) Flavor() Flavor

Flavor returns flavor of builder

func (*SelectBuilder) ForShare 

func (sb *SelectBuilder) ForShare() *SelectBuilder

ForShare adds FOR SHARE at the end of SELECT statement.

func (*SelectBuilder) ForUpdate 

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 

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.

  • 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) LateralAs 

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

LateralAs returns a LATERAL derived table expression wrapping a complex SQL.

Example 
// Demo SQL comes from a sample on https://dev.mysql.com/doc/refman/8.4/en/lateral-derived-tables.html.
sb := Select(
	"salesperson.name",
	"max_sale.amount",
	"max_sale.customer_name",
)
sb.From(
	"salesperson",
	sb.LateralAs(
		Select("amount", "customer_name").
			From("all_sales").
			Where(
				"all_sales.salesperson_id = salesperson.id",
			).
			OrderBy("amount").Desc().Limit(1),
		"max_sale",
	),
)

fmt.Println(sb)

Output:
SELECT salesperson.name, max_sale.amount, max_sale.customer_name FROM salesperson, LATERAL (SELECT amount, customer_name FROM all_sales WHERE all_sales.salesperson_id = salesperson.id ORDER BY amount DESC LIMIT ?) AS max_sale

func (*SelectBuilder) Limit 

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

Limit sets the LIMIT in SELECT.

func (*SelectBuilder) NumCol 

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 

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 ? /* after limit */ FOR SHARE /* after for */

func (*SelectBuilder) Select 

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

Select sets columns in SELECT.

func (*SelectBuilder) SelectMore 

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

SelectMore adds more 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) TableNames 

func (sb *SelectBuilder) TableNames() []string

TableNames returns all table names in this SELECT statement.

func (*SelectBuilder) Where 

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

Where sets expressions of WHERE in SELECT.

func (*SelectBuilder) With 

func (sb *SelectBuilder) With(builder *CTEBuilder) *SelectBuilder

With sets WITH clause (the Common Table Expression) before SELECT.

Example 
sql := With(
	CTEQuery("users").As(
		Select("id", "name").From("users").Where("prime IS NOT NULL"),
	),

	// The CTE table orders will be added to table list of FROM clause automatically.
	CTETable("orders").As(
		Select("id", "user_id").From("orders"),
	),
).Select("orders.id").Join("users", "orders.user_id = users.id").Limit(10).String()

fmt.Println(sql)

Output:
WITH users AS (SELECT id, name FROM users WHERE prime IS NOT NULL), orders AS (SELECT id, user_id FROM orders) SELECT orders.id FROM orders JOIN users ON orders.user_id = users.id LIMIT ?

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,
}
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 (BuildJOIN) 
// Suppose we're going to query a "member" table joined with "user" table.
type Member struct {
	ID         string    `db:"id"`
	UserID     string    `db:"user_id"`
	MemberName int       `db:"name"`
	CreatedAt  time.Time `db:"created_at"`

	// Add "u." prefix to the field name to specify the field in "user" table.
	Name  string `db:"u.name"`
	Email string `db:"u.email"`
}

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

// Prepare JOIN query.
sb := memberStruct.SelectFrom("member m").Join("user u", "m.user_id = u.user_id")
sb.Where(sb.Like("m.name", "Huan%"))

sql, args := sb.Build()

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

Output:
SELECT m.id, m.user_id, m.name, m.created_at, u.name, u.email FROM member m JOIN user u ON m.user_id = u.user_id WHERE m.name LIKE ?
[Huan%]
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]
grizzle.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 

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

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

func (*Struct) ColumnsForTag deprecated 

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 

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

ForeachRead foreach tags.

func (*Struct) ForeachWrite 

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

ForeachWrite foreach tags.

func (*Struct) InsertIgnoreInto 

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 

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 

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 

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 

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

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

func (*Struct) ValuesForTag deprecated 

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 

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 

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 

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 Table 

type Table struct {
	Name    string
	Columns []*Column[any]
}

Table represents a database table.

func (*Table) BuildCreate 

func (t *Table) BuildCreate(flavor Flavor) string

BuildCreate builds the CREATE TABLE SQL for the given flavor.

type Transformer 

type Transformer = func(any) any

type UnionBuilder 

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

UnionBuilder is a builder to build UNION.

Example (Limit_offset) 
flavors := []Flavor{MySQL, PostgreSQL, SQLite, SQLServer, CQL, ClickHouse, Presto, Oracle, Informix, Doris}
results := make([][]string, len(flavors))

ub := NewUnionBuilder()
saveResults := func() {
	sb1 := NewSelectBuilder()
	sb1.Select("*").From("user1")
	sb2 := NewSelectBuilder()
	sb2.Select("*").From("user2")
	ub.Union(sb1, sb2)
	for i, f := range flavors {
		s, _ := ub.BuildWithFlavor(f)
		results[i] = append(results[i], s)
	}
}

// Case #1: limit < 0 and offset < 0
//
// All: No limit or offset in query.
ub.Limit(-1)
ub.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.
ub.Limit(-1)
ub.Offset(0)
saveResults()

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

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

// Case #5: limit >= 0 and offset >= 0 order by id
//
// CQL: Ignore offset.
// All others: Set both limit and offset.
ub.Limit(1)
ub.Offset(1)
ub.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 user1) UNION (SELECT * FROM user2)
#2: (SELECT * FROM user1) UNION (SELECT * FROM user2)
#3: (SELECT * FROM user1) UNION (SELECT * FROM user2) LIMIT ? OFFSET ?
#4: (SELECT * FROM user1) UNION (SELECT * FROM user2) LIMIT ?
#5: (SELECT * FROM user1) UNION (SELECT * FROM user2) ORDER BY id LIMIT ? OFFSET ?

PostgreSQL
#1: (SELECT * FROM user1) UNION (SELECT * FROM user2)
#2: (SELECT * FROM user1) UNION (SELECT * FROM user2) OFFSET $1
#3: (SELECT * FROM user1) UNION (SELECT * FROM user2) LIMIT $1 OFFSET $2
#4: (SELECT * FROM user1) UNION (SELECT * FROM user2) LIMIT $1
#5: (SELECT * FROM user1) UNION (SELECT * FROM user2) ORDER BY id LIMIT $1 OFFSET $2

SQLite
#1: SELECT * FROM user1 UNION SELECT * FROM user2
#2: SELECT * FROM user1 UNION SELECT * FROM user2
#3: SELECT * FROM user1 UNION SELECT * FROM user2 LIMIT ? OFFSET ?
#4: SELECT * FROM user1 UNION SELECT * FROM user2 LIMIT ?
#5: SELECT * FROM user1 UNION SELECT * FROM user2 ORDER BY id LIMIT ? OFFSET ?

SQLServer
#1: (SELECT * FROM user1) UNION (SELECT * FROM user2)
#2: (SELECT * FROM user1) UNION (SELECT * FROM user2) ORDER BY 1 OFFSET @p1 ROWS
#3: (SELECT * FROM user1) UNION (SELECT * FROM user2) ORDER BY 1 OFFSET @p1 ROWS FETCH NEXT @p2 ROWS ONLY
#4: (SELECT * FROM user1) UNION (SELECT * FROM user2) ORDER BY 1 OFFSET 0 ROWS FETCH NEXT @p1 ROWS ONLY
#5: (SELECT * FROM user1) UNION (SELECT * FROM user2) ORDER BY id OFFSET @p1 ROWS FETCH NEXT @p2 ROWS ONLY

CQL
#1: (SELECT * FROM user1) UNION (SELECT * FROM user2)
#2: (SELECT * FROM user1) UNION (SELECT * FROM user2)
#3: (SELECT * FROM user1) UNION (SELECT * FROM user2) LIMIT ?
#4: (SELECT * FROM user1) UNION (SELECT * FROM user2) LIMIT ?
#5: (SELECT * FROM user1) UNION (SELECT * FROM user2) ORDER BY id LIMIT ?

ClickHouse
#1: (SELECT * FROM user1) UNION (SELECT * FROM user2)
#2: (SELECT * FROM user1) UNION (SELECT * FROM user2)
#3: (SELECT * FROM user1) UNION (SELECT * FROM user2) LIMIT ? OFFSET ?
#4: (SELECT * FROM user1) UNION (SELECT * FROM user2) LIMIT ?
#5: (SELECT * FROM user1) UNION (SELECT * FROM user2) ORDER BY id LIMIT ? OFFSET ?

Presto
#1: (SELECT * FROM user1) UNION (SELECT * FROM user2)
#2: (SELECT * FROM user1) UNION (SELECT * FROM user2) OFFSET ?
#3: (SELECT * FROM user1) UNION (SELECT * FROM user2) OFFSET ? LIMIT ?
#4: (SELECT * FROM user1) UNION (SELECT * FROM user2) LIMIT ?
#5: (SELECT * FROM user1) UNION (SELECT * FROM user2) ORDER BY id OFFSET ? LIMIT ?

Oracle
#1: (SELECT * FROM user1) UNION (SELECT * FROM user2)
#2: SELECT * FROM ( (SELECT * FROM user1) UNION (SELECT * FROM user2) ) OFFSET :1 ROWS
#3: SELECT * FROM ( (SELECT * FROM user1) UNION (SELECT * FROM user2) ) OFFSET :1 ROWS FETCH NEXT :2 ROWS ONLY
#4: SELECT * FROM ( (SELECT * FROM user1) UNION (SELECT * FROM user2) ) OFFSET 0 ROWS FETCH NEXT :1 ROWS ONLY
#5: SELECT * FROM ( (SELECT * FROM user1) UNION (SELECT * FROM user2) ) ORDER BY id OFFSET :1 ROWS FETCH NEXT :2 ROWS ONLY

Informix
#1: (SELECT * FROM user1) UNION (SELECT * FROM user2)
#2: (SELECT * FROM user1) UNION (SELECT * FROM user2)
#3: SELECT * FROM ( (SELECT * FROM user1) UNION (SELECT * FROM user2) ) SKIP ? FIRST ?
#4: SELECT * FROM ( (SELECT * FROM user1) UNION (SELECT * FROM user2) ) FIRST ?
#5: SELECT * FROM ( (SELECT * FROM user1) UNION (SELECT * FROM user2) ) ORDER BY id SKIP ? FIRST ?

Doris
#1: (SELECT * FROM user1) UNION (SELECT * FROM user2)
#2: (SELECT * FROM user1) UNION (SELECT * FROM user2)
#3: (SELECT * FROM user1) UNION (SELECT * FROM user2) LIMIT 1 OFFSET 0
#4: (SELECT * FROM user1) UNION (SELECT * FROM user2) LIMIT 1
#5: (SELECT * FROM user1) UNION (SELECT * FROM user2) ORDER BY id LIMIT 1 OFFSET 1

func NewUnionBuilder 

func NewUnionBuilder() *UnionBuilder

NewUnionBuilder creates a new UNION builder.

func Union 

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 

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 ? OFFSET ?
[1234 100 5]

func (*UnionBuilder) Asc 

func (ub *UnionBuilder) Asc() *UnionBuilder

Asc sets order of ORDER BY to ASC.

func (*UnionBuilder) Build 

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 

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

func (ub *UnionBuilder) Clone() *UnionBuilder

Clone returns a deep copy of UnionBuilder. It's useful when you want to create a base builder and clone it to build similar queries.

func (*UnionBuilder) Desc 

func (ub *UnionBuilder) Desc() *UnionBuilder

Desc sets order of ORDER BY to DESC.

func (*UnionBuilder) Flavor 

func (ub *UnionBuilder) Flavor() Flavor

Flavor returns flavor of builder

func (*UnionBuilder) Limit 

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

Limit sets the LIMIT in SELECT.

func (*UnionBuilder) Offset 

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

Offset sets the LIMIT offset in SELECT.

func (*UnionBuilder) OrderBy 

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

OrderBy sets columns of ORDER BY in SELECT.

func (*UnionBuilder) SQL 

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 ? OFFSET ? /* after limit */

func (*UnionBuilder) SetFlavor 

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

SetFlavor sets the flavor of compiled sql.

func (*UnionBuilder) String 

func (ub *UnionBuilder) String() string

String returns the compiled SELECT string.

func (*UnionBuilder) Union 

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

Union unions all builders together using UNION operator.

func (*UnionBuilder) UnionAll 

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

UnionAll unions all builders together using UNION ALL operator.

func (*UnionBuilder) Var 

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 

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 

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

AddWhereClause adds all clauses in the whereClause to SELECT.

func (*UpdateBuilder) Asc 

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

func (ub *UpdateBuilder) Clone() *UpdateBuilder

Clone returns a deep copy of UpdateBuilder. It's useful when you want to create a base builder and clone it to build similar queries.

func (*UpdateBuilder) Decr 

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

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

func (*UpdateBuilder) Desc 

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

func (ub *UpdateBuilder) Flavor() Flavor

Flavor returns flavor of builder

func (*UpdateBuilder) Incr 

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

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

func (*UpdateBuilder) Limit 

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 

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 

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

OrderBy sets columns of ORDER BY in UPDATE.

func (*UpdateBuilder) Returning 

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

Returning sets returning columns. For DBMS that doesn't support RETURNING, e.g. MySQL, it will be ignored.

Example 
ub := NewUpdateBuilder()
ub.Update("user")
ub.Set(ub.Assign("name", "Huan Du"))
ub.Where(ub.Equal("id", 123))
ub.Returning("id", "updated_at")

sql, args := ub.BuildWithFlavor(PostgreSQL)
fmt.Println(sql)
fmt.Println(args)

Output:
UPDATE user SET name = $1 WHERE id = $2 RETURNING id, updated_at
[Huan Du 123]

func (*UpdateBuilder) SQL 

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

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

func (ub *UpdateBuilder) TableNames() (tableNames []string)

TableNames returns all table names in this UPDATE statement.

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.

func (*UpdateBuilder) With 

func (ub *UpdateBuilder) With(builder *CTEBuilder) *UpdateBuilder

With sets WITH clause (the Common Table Expression) before UPDATE.

Example 
sql := With(
	CTETable("users").As(
		Select("id", "name").From("users").Where("prime IS NOT NULL"),
	),
).Update("orders").Set(
	"orders.transport_fee = 0",
).Where(
	"users.id = orders.user_id",
).String()

fmt.Println(sql)

Output:
WITH users AS (SELECT id, name FROM users WHERE prime IS NOT NULL) UPDATE orders, users SET orders.transport_fee = 0 WHERE users.id = orders.user_id

type WhereClause 

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 

func CopyWhereClause(whereClause *WhereClause) *WhereClause

CopyWhereClause creates a copy of the whereClause.

func NewWhereClause 

func NewWhereClause() *WhereClause

NewWhereClause creates a new WhereClause.

func (*WhereClause) AddWhereClause 

func (wc *WhereClause) AddWhereClause(whereClause *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 

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

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 

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

Build returns compiled WHERE clause string and args.

func (*WhereClause) BuildWithFlavor 

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

func (wc *WhereClause) Flavor() Flavor

Flavor returns flavor of clause

func (*WhereClause) SetFlavor 

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

Directories

Path Synopsis
cmd
grizzle command
grizzle/commands

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.