sqlx

package module
v0.48.1 Latest Latest
Warning

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

 Go to latest Published: Apr 28, 2025 License: Apache-2.0 Imports: 23 Imported by: 1

README

SQL Builder

Build Status GoDoc License Minimum Go Version Latest SemVer

Package sqlx provides a set of flexible and powerful SQL builders, not ORM, which is inspired by go-sqlbuilder. The built result can be used by DB.Query() and DB.Exec()

Install

$ go get -u github.com/xgfone/go-sqlx

Usage

package main

import (
	"fmt"

	"github.com/xgfone/go-op"
	"github.com/xgfone/go-sqlx"
)

func main() {
	builder := sqlx.Select("*").From("table")
	builder.Where(op.Equal("id", 123), op.Between("age", 20, 30))

	// You can set the dialect by hand, which is DefaultDialect by default.
	// DefaultDialect is the MySQL dialect, but you can modify it.
	// builder.SetDialect(Sqlite3)

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

	// Output:
	// SELECT * FROM `table` WHERE (`id`=? AND `age` BETWEEN ? AND ?)
	// [123 20 30]
}

You can use sqlx.DB, which is the proxy of builder and sql.DB, it will automatically set the dialect by the sql driver name. For example,

// Set the dialect to MySQL.
db, _ := sqlx.Open("mysql", "user:password@tcp(127.0.0.1:3306)/db")
builder := db.Select("*").From("table").Where(op.Equal("id", 123))

sql, args := builder.Build()
rows := db.QueryRows(sql, args.Args()...)

// Or
// rows := builder.QueryRows()

if rows.Err != nil {
	// TODO: ...
	return
}

defer rows.Close()
// TODO: ...
Intercept SQL
package main

import (
	"fmt"

	"github.com/xgfone/go-op"
	"github.com/xgfone/go-sqlx"
)

func main() {
	// Open DB connecting the mysql server and set the dialect to MySQL.
	db, err := sqlx.Open("mysql", "user:password@tcp(127.0.0.1:3306)/db")
	if err != nil {
		fmt.Println(err)
		return
	}
	defer db.Close()

	// Set the interceptor to print the sql statement.
	db.Interceptor = sqlx.InterceptorFunc(func(sql string, args []any) (string, []any, error) {
		fmt.Println(sql)
		return sql, args, nil
	})

	// Build the SELECT SQL statement
	builder := db.Select("*").From("table")
	builder.Where(op.Equal("id", 123))
	rows := builder.QueryRows()
	if rows.Err != nil {
		fmt.Println(err)
		return
	}
	// TODO: ...

	// Interceptor will output:
	// SELECT * FROM `table` WHERE `id`=?
}

Documentation

Overview

Package sqlx is a set of the simple, flexible and powerful SQL builders with zero-config.

Index

Examples

Constants

View Source 
const (
	// DateZero is the ZERO of the sql date.
	DateZero = "0000-00-00"

	// TimeZero is the ZERO of the sql time.
	TimeZero = "00:00:00"

	// DateTimeZero is the ZERO of the sql datetime.
	DateTimeZero = "0000-00-00 00:00:00"
)
View Source 
const SliceSep = ","

SliceSep is used to combine a string slice to a string.

Variables

View Source 
var (
	// DefaultRowsCap is the default capacity to allocate a map or slice for scanned rows.
	DefaultRowsCap = 16

	// DefaultSliceCap is the default mixed rows binder to bind the rows to a map or slice.
	//
	// It has registered some rows binders for specific-types, such as
	//   []struct
	//   []int, []int64, []string
	//   map[string]int, map[string]string
	//   map[string]bool, map[string]struct{}
	DefaultMixRowsBinder = NewMixRowsBinder()

	// CommonSliceRowsBinder is the common rows binder to bind the rows to a slice.
	//
	// Notice: it uses the reflect package for the default implementation.
	CommonSliceRowsBinder RowsBinder = RowsBinderFunc(commonSliceRowsBinder)
)
View Source 
var DefaultArgsCap = 32

DefaultArgsCap is the default capacity to be allocated for ArgsBuilder.

View Source 
var DefaultBufferCap = 512

DefaultBufferCap is the default capacity to be allocated for buffer from pool.

View Source 
var DefaultConfigs = []Config{MaxOpenConns(0), ConnMaxIdleTime(time.Minute * 5)}

DefaultConfigs is the default configs.

View Source 
var DefaultDB = new(DB)

DefaultDB is the default global DB.

View Source 
var DefaultDialect = MySQL

DefaultDialect is the default dialect.

View Source 
var DefaultSliceCap = 16
View Source 
var DefaultSqlCollector = NewSqlCollector()

DefaultSqlCollector is the default sql collector.

View Source 
var Sep = "_"

Sep is the separator by the select struct.

Functions

func BuildOp added in v0.27.0 

func BuildOp(ab *ArgsBuilder, op op.Op) string

BuildOp builds the operation.

func BuildOper added in v0.27.0 

func BuildOper(ab *ArgsBuilder, op op.Oper) string

BuildOper is equal to BuildOp(ab, op.Operation()).

func CheckErrNoRows 

func CheckErrNoRows(err error) (exist bool, e error)

CheckErrNoRows extracts the error sql.ErrNoRows as the bool, which returns

  • (true, nil) if err is equal to nil
  • (false, nil) if err is equal to sql.ErrNoRows
  • (false, err) if err is equal to others

func Count added in v0.30.0 

func Count(field string) string

Count returns a COUNT(field).

func CountDistinct added in v0.30.0 

func CountDistinct(field string) string

CountDistinct returns a COUNT(DISTINCT field).

func DecodeInt64s added in v0.47.0 

func DecodeInt64s[S ~[]int64](s *S, src any) error

DecodeInt64s decodes a int64 slice from string or []byte.

func DecodeMap added in v0.39.0 

func DecodeMap[M ~map[string]T, T any](m *M, src any) error

DecodeMap decodes a map from string or []byte.

func DecodeStrings added in v0.39.0 

func DecodeStrings[S ~[]string](s *S, src any, sep string) error

DecodeStrings decodes a string slice from string or []byte.

func EncodeInt64s added in v0.47.0 

func EncodeInt64s[S ~[]int64](s S) string

EncodeInt64s encodes a int64 slice to string.

func EncodeMap added in v0.39.0 

func EncodeMap[M ~map[string]T, T any](m M) (string, error)

EncodeMap encodes a map to string.

func EncodeStrings added in v0.47.0 

func EncodeStrings[S ~[]string](s S, sep string) string

EncodeStrings encodes a string slice to string separated by a given separator.

func IsPointerToStruct added in v0.43.0 

func IsPointerToStruct(v any) (ok bool)

IsPointerToStruct returns true if v is a pointer to struct, else false.

Notice: struct{} is considered as a struct, but time.Time is not.

func IsUnsupportedTypeError added in v0.47.0 

func IsUnsupportedTypeError(err error) bool

IsUnsupportedTypeError checks if the error is a UnsupportedTypeError.

func RegisterDialect 

func RegisterDialect(dialect Dialect, force bool)

RegisterDialect registers the Dialect with the name.

If the name has been registered, it will panic. But you can set force to true to ignore it.

func RegisterMapRowsBinder added in v0.48.0 

func RegisterMapRowsBinder[K comparable, V any](b *MixRowsBinder, keyf func(V) K)

RegisterMapRowsBinder is a convenient function to register a rows binder, which binds the row to the value and maps the value to the key, for a specific map type, that's, map[K]V and *map[K]V.

If b is nil, use DefaultMixRowsBinder instead.

func RegisterOpBuilder added in v0.27.0 

func RegisterOpBuilder(op string, builder OpBuilder)

RegisterOpBuilder registers the operation builder.

func ScanColumnsToStruct 

func ScanColumnsToStruct(scan func(...any) error, columns []string, s any) (err error)

ScanColumnsToStruct scans the columns into the fields of the struct s, which supports the tag named "sql" to modify the field name.

If the value of the tag is "-", however, the field will be ignored.

func ScanRow 

func ScanRow(scan func(dests ...any) error, dests ...any) error

ScanRow uses the function scan to scan the sql row into dests, which may be used as a proxy of the function sql.Row.Scan or sql.Rows.Scan.

For the pointers to the built-in types, it will use GeneralScanner to wrap them.

func SetConnURLLocation 

func SetConnURLLocation(connURL string, loc *time.Location) string

SetConnURLLocation sets the argument "loc" in the connection url if missing.

If loc is nil, use Location instead.

func Sum added in v0.31.0 

func Sum(field string) string

Sum returns a SUM(field).

Types

type ArgsBuilder 

type ArgsBuilder struct {
	Dialect
	// contains filtered or unexported fields
}

ArgsBuilder is used to build the arguments.

func GetArgsBuilderFromPool added in v0.34.0 

func GetArgsBuilderFromPool(dialect Dialect) *ArgsBuilder

GetArgsBuilderFromPool acquires an ArgsBuilder with the dialect from pool.

func (*ArgsBuilder) Add 

func (a *ArgsBuilder) Add(arg any) (placeholder string)

Add appends the argument and returns the its placeholder.

If arg is the type of sql.NamedArg, it will use @arg.Name as the placeholder and arg.Value as the value.

func (*ArgsBuilder) Args 

func (a *ArgsBuilder) Args() (args []any)

Args returns the added arguments.

func (*ArgsBuilder) Release added in v0.34.0 

func (a *ArgsBuilder) Release()

Release puts itself into the pool if it is acquired from the pool.

func (*ArgsBuilder) Reset added in v0.34.0 

func (a *ArgsBuilder) Reset()

Reset resets the args to empty.

func (*ArgsBuilder) WithDialect added in v0.34.0 

func (a *ArgsBuilder) WithDialect(dialect Dialect) *ArgsBuilder

WithDialect sets the dialect and returns itself.

type Base 

type Base = Base1

Base is the alias of Base1.

type Base1 added in v0.45.0 

type Base1 struct {
	Id int64 `sql:"id,omitempty" json:",omitempty,omitzero"`

	CreatedAt time.Time `sql:"created_at,omitempty" json:",omitempty,omitzero"`
}

Base1 is the simplified model columns of the sql table.

type Base2 added in v0.45.0 

type Base2 struct {
	Id int64 `sql:"id,omitempty" json:",omitempty,omitzero"`

	CreatedAt time.Time `sql:"created_at,omitempty" json:",omitempty,omitzero"`
	UpdatedAt time.Time `sql:"updated_at,omitempty" json:",omitempty,omitzero"`
	DeletedAt time.Time `sql:"deleted_at,omitempty" json:",omitempty,omitzero"`
}

Base2 is the richer model columns of the sql table.

type Config 

type Config func(db *sql.DB)

Config is used to configure the DB.

func ConnMaxIdleTime 

func ConnMaxIdleTime(d time.Duration) Config

ConnMaxIdleTime returns a Config to set the maximum idle time of the connection.

func ConnMaxLifetime 

func ConnMaxLifetime(d time.Duration) Config

ConnMaxLifetime returns a Config to set the maximum lifetime of the connection.

func MaxIdleConns 

func MaxIdleConns(n int) Config

MaxIdleConns returns a Config to set the maximum number of the idle connection.

func MaxOpenConns 

func MaxOpenConns(maxnum int) Config

MaxOpenConns returns a Config to set the maximum number of the open connection.

If maxnum is equal to 0, it is runtime.NumCPU()*2 by default.

type DB 

type DB struct {
	Dialect
	Executor
	Interceptor
}

DB is the wrapper of the sql.DB.

func Open 

func Open(driverName, dataSourceName string, configs ...Config) (*DB, error)

Open opens a database specified by its database driver name and a driver-specific data source name,

func (*DB) Delete 

func (db *DB) Delete() *DeleteBuilder

Delete returns a DELETE SQL builder, which is short for DeleteBuilder.

func (*DB) DeleteBuilder added in v0.45.0 

func (db *DB) DeleteBuilder() *DeleteBuilder

DeleteBuilder returns a new empty DeleteBuilder.

func (*DB) Exec added in v0.26.0 

func (db *DB) Exec(query string, args ...any) (r sql.Result, err error)

Exec is equal to db.ExecContext(context.Background(), query, args...).

func (*DB) ExecContext 

func (db *DB) ExecContext(ctx context.Context, query string, args ...any) (r sql.Result, err error)

ExecContext executes the sql statement.

func (*DB) GetDialect added in v0.26.0 

func (db *DB) GetDialect() Dialect

GetDialect returns the dialect of the db.

If not set, return DefaultDialect instead.

func (*DB) Insert 

func (db *DB) Insert() *InsertBuilder

Insert returns a INSERT SQL builder, which is short for InsertBuilder.

func (*DB) InsertBuilder added in v0.45.0 

func (db *DB) InsertBuilder() *InsertBuilder

InsertBuilder returns a new empty InsertBuilder.

func (*DB) Intercept added in v0.26.0 

func (db *DB) Intercept(sql string, args []any) (string, []any, error)

func (*DB) NewTable 

func (db *DB) NewTable(name string) Table

NewTable returns a new Table with the db.

func (*DB) Query added in v0.26.0 

func (db *DB) Query(query string, args ...any) (rows *sql.Rows, err error)

Query is equal to db.QueryContext(context.Background(), query, args...).

func (*DB) QueryContext 

func (db *DB) QueryContext(ctx context.Context, query string, args ...any) (rows *sql.Rows, err error)

QueryContext executes the query sql statement.

func (*DB) QueryRow added in v0.26.0 

func (db *DB) QueryRow(query string, args ...any) *sql.Row

QueryRow is equal to db.QueryRowContext(context.Background(), query, args...)

func (*DB) QueryRowContext 

func (db *DB) QueryRowContext(ctx context.Context, query string, args ...any) *sql.Row

QueryRowContext executes the row query sql statement.

func (*DB) QueryRowOne added in v0.43.0 

func (db *DB) QueryRowOne(query string, args ...any) Row

QueryRowOne executes the row query sql statement and returns Row instead of *sql.Row.

func (*DB) QueryRowOneContext added in v0.43.0 

func (db *DB) QueryRowOneContext(ctx context.Context, query string, args ...any) Row

QueryRowOneContext executes the row query sql statement and returns Row instead of *sql.Row.

func (*DB) QueryRows added in v0.32.0 

func (db *DB) QueryRows(query string, args ...any) Rows

QueryRows executes the query sql statement and returns Rows instead of *sql.Rows.

func (*DB) QueryRowsContext added in v0.32.0 

func (db *DB) QueryRowsContext(ctx context.Context, query string, args ...any) Rows

QueryRowsContext executes the query sql statement and returns Rows instead of *sql.Rows.

func (*DB) Reset added in v0.37.0 

func (db *DB) Reset(other *DB)

Set resets the current db to other.

func (*DB) Select 

func (db *DB) Select(column string) *SelectBuilder

Select is equal todb.SelectAlias(column, "").

func (*DB) SelectAlias added in v0.27.0 

func (db *DB) SelectAlias(column, alias string) *SelectBuilder

SelectAlias is equal to SelectAlias(column, alias).

func (*DB) SelectBuilder added in v0.45.0 

func (db *DB) SelectBuilder() *SelectBuilder

SelectBuilder returns a new empty SelectBuilder.

func (*DB) SelectStruct 

func (db *DB) SelectStruct(s any) *SelectBuilder

SelectStruct is equal to db.SelectStructWithTable(s, "").

func (*DB) SelectStructWithTable added in v0.27.0 

func (db *DB) SelectStructWithTable(s any, table string) *SelectBuilder

SelectStructWithTable is equal to SelectStructWithTable(s, table...).

func (*DB) Selects 

func (db *DB) Selects(columns ...string) *SelectBuilder

Selects is equal to db.Select(columns[0]).Select(columns[1])...

func (*DB) Update 

func (db *DB) Update() *UpdateBuilder

Update returns a UPDATE SQL builder, which is short for NewUpdateBuilder.

func (*DB) UpdateBuilder added in v0.45.0 

func (db *DB) UpdateBuilder() *UpdateBuilder

UpdateBuilder returns a new empty UpdateBuilder.

type DeleteBuilder 

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

DeleteBuilder is used to build the DELETE statement.

Example 
// No Where
delete1 := Delete().From("table")

// With Where
delete2 := Delete().From("table").
	Where(
		op.Greater("c1", 123),
		op.IsNotNull("c2"),
	).
	Where(
		op.Or(
			op.Less("c3", 456),
			op.In("c4", []string{"a", "b"}),
		),
	)

sql1, args1 := delete1.Build() // Use the default dialect.
sql2, args2 := delete2.Build() // Use the PostgreSQL dialect.

fmt.Println(sql1)
fmt.Println(args1.Args())
fmt.Println(sql2)
fmt.Println(args2.Args())

Output:

DELETE FROM `table`
[]
DELETE FROM `table` WHERE (`c1`>? AND `c2` IS NOT NULL AND (`c3`<? OR `c4` IN (?, ?)))
[123 456 a b]

func Delete 

func Delete() *DeleteBuilder

Delete is short for NewDeleteBuilder.

func NewDeleteBuilder 

func NewDeleteBuilder() *DeleteBuilder

NewDeleteBuilder returns a new DELETE builder.

func (*DeleteBuilder) Build 

func (b *DeleteBuilder) Build() (sql string, args *ArgsBuilder)

Build builds the DELETE FROM TABLE sql statement.

func (*DeleteBuilder) Comment added in v0.37.0 

func (b *DeleteBuilder) Comment(comment string) *DeleteBuilder

Comment set the comment, which will be appended to the end of the built SQL statement.

func (*DeleteBuilder) Exec 

func (b *DeleteBuilder) Exec() (sql.Result, error)

Exec builds the sql and executes it by *sql.DB.

func (*DeleteBuilder) ExecContext 

func (b *DeleteBuilder) ExecContext(ctx context.Context) (sql.Result, error)

ExecContext builds the sql and executes it by *sql.DB.

func (*DeleteBuilder) From 

func (b *DeleteBuilder) From(table string) *DeleteBuilder

From is equal to b.FromAlias(table, "").

func (*DeleteBuilder) FromAlias added in v0.27.0 

func (b *DeleteBuilder) FromAlias(table string, alias string) *DeleteBuilder

From appends the "FROM table AS alias" statement.

If alias is empty, use "FROM table" instead.

func (*DeleteBuilder) Join added in v0.47.0 

func (b *DeleteBuilder) Join(table, alias string, ons ...JoinOn) *DeleteBuilder

Join appends the "JOIN table ON on..." statement.

func (*DeleteBuilder) JoinFull 

func (b *DeleteBuilder) JoinFull(table, alias string, ons ...JoinOn) *DeleteBuilder

JoinFull appends the "FULL JOIN table ON on..." statement.

func (*DeleteBuilder) JoinFullOuter 

func (b *DeleteBuilder) JoinFullOuter(table, alias string, ons ...JoinOn) *DeleteBuilder

JoinFullOuter appends the "FULL OUTER JOIN table ON on..." statement.

func (*DeleteBuilder) JoinInner added in v0.47.0 

func (b *DeleteBuilder) JoinInner(table, alias string, ons ...JoinOn) *DeleteBuilder

JoinInner appends the "INNER JOIN table ON on..." statement.

func (*DeleteBuilder) JoinLeft 

func (b *DeleteBuilder) JoinLeft(table, alias string, ons ...JoinOn) *DeleteBuilder

JoinLeft appends the "LEFT JOIN table ON on..." statement.

func (*DeleteBuilder) JoinLeftOuter 

func (b *DeleteBuilder) JoinLeftOuter(table, alias string, ons ...JoinOn) *DeleteBuilder

JoinLeftOuter appends the "LEFT OUTER JOIN table ON on..." statement.

func (*DeleteBuilder) JoinRight 

func (b *DeleteBuilder) JoinRight(table, alias string, ons ...JoinOn) *DeleteBuilder

JoinRight appends the "RIGHT JOIN table ON on..." statement.

func (*DeleteBuilder) JoinRightOuter 

func (b *DeleteBuilder) JoinRightOuter(table, alias string, ons ...JoinOn) *DeleteBuilder

JoinRightOuter appends the "RIGHT OUTER JOIN table ON on..." statement.

func (*DeleteBuilder) SetDB 

func (b *DeleteBuilder) SetDB(db *DB) *DeleteBuilder

SetDB sets the db.

func (*DeleteBuilder) String 

func (b *DeleteBuilder) String() string

String is the same as b.Build(), except args.

func (*DeleteBuilder) Where 

func (b *DeleteBuilder) Where(andConditions ...op.Condition) *DeleteBuilder

Where sets the "WHERE" conditions.

func (*DeleteBuilder) WhereNamedArgs 

func (b *DeleteBuilder) WhereNamedArgs(andArgs ...sql.NamedArg) *DeleteBuilder

WhereNamedArgs is the same as Where, but uses the NamedArg as the condition.

type Dialect 

type Dialect interface {
	// Name returns the name of the dialect.
	Name() string

	// Placeholder returns the format of the ith argument,
	// such as "?" for MySQL and "$i" for PostgreSQL.
	//
	// Notice: i starts with 1.
	Placeholder(i int) string

	// Quote returns the quotation format of sql string,
	// such as `s` for MySQL and "s" for PostgreSQL.
	Quote(s string) string

	// LimitOffset returns the LIMIT OFFSET statement,
	// such as "LIMIT n" or "LIMIT n OFFSET m" for MySQL and PostgreSQL.
	LimitOffset(limit, offset int64) string
}

Dialect represents a dialect of the SQL. 

var (
	MySQL    Dialect = dialect{mysqlDialect}
	Sqlite3  Dialect = dialect{sqlite3Dialect}
	Postgres Dialect = dialect{pqDialect}
)

Predefine some dialects.

func GetDialect 

func GetDialect(name string) Dialect

GetDialect returns the dialect named name. Return nil instead if not exist.

type Executor 

type Executor interface {
	io.Closer

	ExecContext(ctx context.Context, query string, args ...any) (sql.Result, error)
	QueryContext(ctx context.Context, query string, args ...any) (*sql.Rows, error)
	QueryRowContext(ctx context.Context, query string, args ...any) *sql.Row
}

Executor is used to execute the sql statement.

type GeneralScanner added in v0.43.0 

type GeneralScanner struct {
	Value any
}

GeneralScanner is a general sql.Scanner.

func (GeneralScanner) Scan added in v0.43.0 

func (s GeneralScanner) Scan(src any) (err error)

Scan implements the interface sql.Scanner to scan the sql column src into the wrapped Value, which supports the sql NULL as the ZERO.

For time, if src is empty or equal to "0000-00-00 00:00:00", the time value will be ZERO.

The wrapped value supports the following types: 

nil: ignore the column value src
*any: put src as it is into the wrapped value
*time.Duration:
    string:    time.ParseDuration(src)
    []byte:    time.ParseDuration(string(src))
    int64:     time.Duration(src) * time.Millisecond
    float64:   time.Duration(src  * float64(time.Second))
*time.Time:
    int64:     time.Unix(src, 0).In(Location)
    float64:   time.Unix(Integer, Fraction).In(Location)
    string:    time.ParseInLocation(DatetimeLayout, src, Location))
    []byte:    time.ParseInLocation(DatetimeLayout, string(src), Location))
    time.Time: src
*bool:
     bool:     src
     int64:    src!=0
     float64:  src!=0
     string:   strconv.ParseBool(src)
     []byte:
               len(src)==1: src[0] != '\x00'
               len(src)!=1: strconv.ParseBool(string(src))
*string:
    string:    src
    []byte:    string(src)
    bool:      "true" or "false"
    int64:     strconv.FormatInt(src, 10)
    float64:   strconv.FormatFloat(src, 'f', -1, 64)
    time.Time: src.In(Location).Format(DatetimeLayout)
*float32, *float64:
    bool:      true=>1, false=>0
    int64:     floatXX(src)
    float64:   floatXX(src)
    string:    strconv.ParseFloat(src, 64)
    []byte:    strconv.ParseFloat(string(src), 64)
*int, *int8, *int16, *int32, *int64:
	bool:      true=>1, false=>0
    int64:     intXX(src)
    float64:   intXX(src)
    string:    strconv.ParseInt(src, 10, 64)
    []byte:    strconv.ParseInt(string(src), 10, 64)
    time.Time: src.Unix() only for int/int64
*uint, *uint8, *uint16, *uint32, *uint64:
	bool:      true=>1, false=>0
    int64:     uintXX(src)
    float64:   uintXX(src)
    string:    strconv.ParseUint(src, 10, 64)
    []byte:    strconv.ParseUint(string(src), 10, 64)
    time.Time: src.Unix() only for uint/uint64

type InsertBuilder 

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

InsertBuilder is used to build the INSERT statement.

Example 
// Single Value
insert1 := Insert().Into("table").Columns("c1", "c2", "c3").
	Values("v1", "v2", "v3")

// Many Values
insert2 := Insert().Into("table").Columns("c1", "c2", "c3").
	Values("v1", "v2", "v3").
	Values("v4", "v5", "v6").
	Values("v7", "v8", "v9")

// No Value, which will build a single value placeholder.
insert3 := Insert().Into("table").Columns("c1", "c2", "c3")

// No Column
insert4 := Insert().Into("table").Values("v1", "v2", "v3")
insert5 := Insert().Into("table").Values("v11", "v12").Values("v21", "v22")

sql1, args1 := insert1.SetDB(&DB{Dialect: Postgres}).Build() // Use the PostgreSQL dialect.
sql2, args2 := insert2.SetDB(&DB{Dialect: Postgres}).Build() // Use the PostgreSQL dialect.
sql3, args3 := insert3.Build()                               // Use the default dialect.
sql4, args4 := insert4.Build()                               // Use the default dialect.
sql5, args5 := insert5.Build()                               // Use the default dialect.

fmt.Println(sql1)
fmt.Println(args1.Args())

fmt.Println(sql2)
fmt.Println(args2.Args())

fmt.Println(sql3)
fmt.Println(args3.Args())

fmt.Println(sql4)
fmt.Println(args4.Args())

fmt.Println(sql5)
fmt.Println(args5.Args())

Output:

INSERT INTO "table" ("c1", "c2", "c3") VALUES ($1, $2, $3)
[v1 v2 v3]
INSERT INTO "table" ("c1", "c2", "c3") VALUES ($1, $2, $3), ($4, $5, $6), ($7, $8, $9)
[v1 v2 v3 v4 v5 v6 v7 v8 v9]
INSERT INTO `table` (`c1`, `c2`, `c3`) VALUES (?, ?, ?)
[]
INSERT INTO `table` VALUES (?, ?, ?)
[v1 v2 v3]
INSERT INTO `table` VALUES (?, ?), (?, ?)
[v11 v12 v21 v22]

func Insert 

func Insert() *InsertBuilder

Insert is short for NewInsertBuilder.

func NewInsertBuilder 

func NewInsertBuilder() *InsertBuilder

NewInsertBuilder returns a new INSERT builder.

func (*InsertBuilder) Build 

func (b *InsertBuilder) Build() (sql string, args *ArgsBuilder)

Build builds the INSERT INTO TABLE sql statement.

func (*InsertBuilder) Columns 

func (b *InsertBuilder) Columns(columns ...string) *InsertBuilder

Columns sets the inserted columns.

func (*InsertBuilder) Comment added in v0.37.0 

func (b *InsertBuilder) Comment(comment string) *InsertBuilder

Comment set the comment, which will be appended to the end of the built SQL statement.

func (*InsertBuilder) Exec 

func (b *InsertBuilder) Exec() (sql.Result, error)

Exec builds the sql and executes it by *sql.DB.

func (*InsertBuilder) ExecContext 

func (b *InsertBuilder) ExecContext(ctx context.Context) (sql.Result, error)

ExecContext builds the sql and executes it by *sql.DB.

func (*InsertBuilder) GrowValues added in v0.46.0 

func (b *InsertBuilder) GrowValues(newcap int) *InsertBuilder

GrowValues grows the capacity of the values and returns itself.

func (*InsertBuilder) IgnoreInto 

func (b *InsertBuilder) IgnoreInto(table string) *InsertBuilder

IgnoreInto sets the table name with "INSERT IGNORE INTO".

func (*InsertBuilder) Into 

func (b *InsertBuilder) Into(table string) *InsertBuilder

Into sets the table name with "INSERT INTO".

func (*InsertBuilder) NamedValues 

func (b *InsertBuilder) NamedValues(nvs ...sql.NamedArg) *InsertBuilder

NamedValues is the same as Values. But it will set it if the columns are not set.

Example 
v1 := sql.Named("column1", "value1")
v2 := sql.Named("column2", "value2")
v3 := sql.Named("column3", "value3")

insert := Insert().Into("table").NamedValues(v1, v2, v3)
sql, args := insert.Build()

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

Output:

INSERT INTO `table` (`column1`, `column2`, `column3`) VALUES (?, ?, ?)
[value1 value2 value3]

func (*InsertBuilder) Ops added in v0.27.0 

func (b *InsertBuilder) Ops(ops ...op.Op) *InsertBuilder

Ops is the same as Values. But it will set it if the columns are not set.

func (*InsertBuilder) ReplaceInto 

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

ReplaceInto sets the table name with "REPLACE INTO".

REPLACE INTO is a MySQL extension to the SQL standard.

func (*InsertBuilder) SetDB 

func (b *InsertBuilder) SetDB(db *DB) *InsertBuilder

SetDB sets the db.

func (*InsertBuilder) String 

func (b *InsertBuilder) String() string

String is the same as b.Build(), except args.

func (*InsertBuilder) Struct 

func (b *InsertBuilder) Struct(s any) *InsertBuilder

Struct is the same as NamedValues, but extracts the fields of the struct as the named values to be inserted, which supports the tag named "sql" to modify the column name.

  1. If the value of the tag is "-", however, the field will be ignored.
  2. If the tag value contains "omitempty" or "omitzero", the ZERO field will be ignored.
Example 
_time := time.Date(2025, 1, 2, 3, 4, 5, 0, time.Local)
s1 := InsertStruct{DefaultField: "v1", IgnoredField: "v2", Valuer: NewMyTime(_time)}
insert1 := Insert().Into("table").Struct(s1)
sql1, args1 := insert1.Build()

s2 := InsertStruct{Base2: Base2{Id: 123}, DefaultField: "v1", ModifiedField: "v2", ZeroField: "v3", IgnoredField: "v4"}
insert2 := Insert().Into("table").Struct(s2)
sql2, args2 := insert2.Build()

fmt.Println(sql1)
fmt.Println(args1.Args())

fmt.Println(sql2)
fmt.Println(args2.Args())

Output:

INSERT INTO `table` (`DefaultField`, `field`, `time`) VALUES (?, ?, ?)
[v1  2025-01-02/03:04:05]
INSERT INTO `table` (`id`, `DefaultField`, `field`, `ZeroField`) VALUES (?, ?, ?, ?)
[123 v1 v2 v3]

func (*InsertBuilder) Values 

func (b *InsertBuilder) Values(values ...any) *InsertBuilder

Values appends the inserted values.

func (*InsertBuilder) ValuesFromStructs added in v0.46.0 

func (b *InsertBuilder) ValuesFromStructs(slice any) *InsertBuilder

ValuesFromStructs is the same as Values, but extracts the fields of the structs.

Example 
_time := time.Date(2025, 1, 2, 3, 4, 5, 0, time.Local)
sql1, args1 := Insert().
	Into("table").
	Columns("field", "time", "ZeroField").
	ValuesFromStructs([]InsertStruct{
		{DefaultField: "v1", IgnoredField: "v2", Valuer: NewMyTime(_time)},
		{ModifiedField: "v3", ZeroField: "v4"},
	}).
	Build()

fmt.Println(sql1)
fmt.Println(args1.Args())

Output:

INSERT INTO `table` (`field`, `time`, `ZeroField`) VALUES (?, ?, ?), (?, ?, ?)
[ 2025-01-02/03:04:05  v3 0001-01-01/00:00:00 v4]

type Int64s added in v0.47.0 

type Int64s []int64

Int64s is an int64 slice value type, which is encoded to a string or decoded from a []byte or string.

func (Int64s) IsZero added in v0.47.0 

func (vs Int64s) IsZero() bool

IsZero returns true if the slice is empty.

func (*Int64s) Scan added in v0.47.0 

func (vs *Int64s) Scan(src any) error

Scan implements the interface sql.Scanner to scan a sql value([]byte or string) to the slice.

func (Int64s) Value added in v0.47.0 

func (vs Int64s) Value() (driver.Value, error)

Value implements the interface driver.Valuer to encode the slice to a sql value(string).

type Interceptor 

type Interceptor interface {
	Intercept(sql string, args []any) (string, []any, error)
}

Interceptor is used to intercept the executed sql statement and arguments and return a new one.

type InterceptorFunc added in v0.26.0 

type InterceptorFunc func(sql string, args []any) (string, []any, error)

InterceptorFunc is an interceptor function.

func (InterceptorFunc) Intercept added in v0.26.0 

func (f InterceptorFunc) Intercept(sql string, args []any) (string, []any, error)

Intercept implements the interface Interceptor.

type Interceptors added in v0.36.0 

type Interceptors []Interceptor

Interceptors is a set of Interceptors.

func (Interceptors) Intercept added in v0.36.0 

func (is Interceptors) Intercept(sql string, args []any) (string, []any, error)

Intercept implements the interface Interceptor.

type JoinOn 

type JoinOn struct {
	Left  string
	Right string
	IsArg bool // Right is the argument or not.
}

JoinOn is the join on statement.

func On 

func On(left, right string) JoinOn

On returns a JoinOn instance with IsArg=false.

func OnArg added in v0.48.1 

func OnArg(left, right string) JoinOn

OnArg returns a JoinOn instance with IsArg=true.

type Map added in v0.39.0 

type Map[T any] map[string]T

Map is a map value type, which is encoded to a string or decoded from a []byte or string.

func (Map[T]) IsZero added in v0.47.0 

func (m Map[T]) IsZero() bool

IsZero reports whether the map is ZERO.

func (*Map[T]) Scan added in v0.39.0 

func (m *Map[T]) Scan(src any) error

Scan implements the interface sql.Scanner to scan a sql value to the map.

func (Map[T]) Value added in v0.39.0 

func (m Map[T]) Value() (driver.Value, error)

Value implements the interface driver.Valuer to encode the map to a sql value(string).

type MixRowsBinder added in v0.43.0 

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

MixRowsBinder is a mixed rows binder based on the reflected type.

func NewMixRowsBinder added in v0.43.0 

func NewMixRowsBinder() *MixRowsBinder

NewMixRowsBinder returns a new MixRowsBinder.

func (*MixRowsBinder) BindRows added in v0.43.0 

func (b *MixRowsBinder) BindRows(scanner RowScanner, dst any) (err error)

BindRows implements the interface RowsBinder.

func (*MixRowsBinder) Get added in v0.48.0 

func (b *MixRowsBinder) Get(vtype reflect.Type) RowsBinder

Get returns the rows binder for a specific type.

Return nil if the type has been not registered.

func (*MixRowsBinder) Register added in v0.43.0 

func (b *MixRowsBinder) Register(vtype reflect.Type, binder RowsBinder) (old RowsBinder)

Register registers a rows binder for a specific type.

type Namer added in v0.45.0 

type Namer struct {
	Name  string
	Alias string
}

Namer represents the name and alias of a column.

type OpBuilder added in v0.27.0 

type OpBuilder interface {
	Build(*ArgsBuilder, op.Op) string
}

OpBuilder is an operation builder to build a sql statement based on op.

func GetOpBuilder added in v0.27.0 

func GetOpBuilder(op string) OpBuilder

GetOpBuilder returns the op builder by the op.

Return nil instead if no the op builder.

type OpBuilderFunc added in v0.27.0 

type OpBuilderFunc func(ab *ArgsBuilder, op op.Op) string

OpBuilderFunc is a operation build function.

func (OpBuilderFunc) Build added in v0.27.0 

func (f OpBuilderFunc) Build(ab *ArgsBuilder, op op.Op) string

Build implements the interface OpBuilder.

type Opener 

type Opener func(driverName, dataSourceName string) (*sql.DB, error)

Opener is used to open a *sql.DB. 

var DefaultOpener Opener = sql.Open

DefaultOpener is used to open a *sql.DB.

type Oper added in v0.34.0 

type Oper[T any] struct {
	Table Table

	// Sorter is used to sort the records when querying the records.
	//
	// Default: op.KeyId.OrderDesc()
	Sorter op.Sorter

	// SoftCondition is used by the method SoftXxxx as the WHERE condition.
	//
	// Default: op.IsNotDeletedCond
	SoftCondition op.Condition

	// SoftDeleteUpdater is used by SoftDelete to delete the records.
	//
	// Default: op.KeyDeletedAt.Set(time.Now())
	SoftDeleteUpdater func(context.Context) op.Updater
	// contains filtered or unexported fields
}

Oper is used to collect a set of SQL DML & DQL operations based on a table.

func NewOper added in v0.34.0 

func NewOper[T any](table string) Oper[T]

NewOper returns a new Oper with the table name.

func NewOperWithTable added in v0.34.0 

func NewOperWithTable[T any](table Table) Oper[T]

NewOperWithTable returns a new Oper with the table.

func (Oper[T]) Add added in v0.34.0 

func (o Oper[T]) Add(obj T) (err error)

Add is equal to o.AddContext(context.Background(), obj).

func (Oper[T]) AddContext added in v0.34.0 

func (o Oper[T]) AddContext(ctx context.Context, obj T) (err error)

AddContext inserts the struct as the record into the sql table.

func (Oper[T]) AddContextWithId added in v0.34.0 

func (o Oper[T]) AddContextWithId(ctx context.Context, obj T) (id int64, err error)

AddContextWithId is the same as AddContext, but also returns the inserted id.

func (Oper[T]) AddWithId added in v0.34.0 

func (o Oper[T]) AddWithId(obj T) (id int64, err error)

AddWithId is equal to o.AddContextWithId(context.Background(), obj).

func (Oper[T]) AppendRowsBinders added in v0.47.0 

func (o Oper[T]) AppendRowsBinders(binders ...RowsBinder) Oper[T]

AppendRowsBinders returns a new Oper, which appends the new rows binders by ComposeRowsBinders.

func (Oper[T]) Count added in v0.34.0 

func (o Oper[T]) Count(conds ...op.Condition) (total int, err error)

Count is equal to o.CountContext(context.Background(), conds...).

func (Oper[T]) CountContext added in v0.35.0 

func (o Oper[T]) CountContext(ctx context.Context, conds ...op.Condition) (total int, err error)

CountContext is used to count the number of records by the condition.

func (Oper[T]) CountDistinct added in v0.34.0 

func (o Oper[T]) CountDistinct(field string, conds ...op.Condition) (total int, err error)

CountDistinct is equal to o.CountDistinctContext(context.Background(), field, conds...).

func (Oper[T]) CountDistinctContext added in v0.35.0 

func (o Oper[T]) CountDistinctContext(ctx context.Context, field string, conds ...op.Condition) (total int, err error)

CountDistinctContext is the same as Count, but excluding the same field records.

func (Oper[T]) CountQuery added in v0.41.0 

func (o Oper[T]) CountQuery(page, pagesize int64, conds ...op.Condition) (total int, objs []T, err error)

CountQuery is equal to o.CountQueryContext(context.Background(), page, pagesize, conds...).

func (Oper[T]) CountQueryContext added in v0.41.0 

func (o Oper[T]) CountQueryContext(ctx context.Context, page, pagesize int64, conds ...op.Condition) (total int, objs []T, err error)

CountQueryContext is the combination of CountContext and QueryContext.

func (Oper[T]) Delete added in v0.34.0 

func (o Oper[T]) Delete(conds ...op.Condition) (err error)

Delete is equal to o.DeleteContext(context.Background(), conds...).

func (Oper[T]) DeleteById added in v0.36.0 

func (o Oper[T]) DeleteById(id int64) error

DeleteById is equal to o.DeleteWithId(id).

func (Oper[T]) DeleteContext added in v0.34.0 

func (o Oper[T]) DeleteContext(ctx context.Context, conds ...op.Condition) error

DeleteContext executes a DELETE statement to delete the records from table.

func (Oper[T]) DeleteWithId added in v0.46.0 

func (o Oper[T]) DeleteWithId(id int64, conds ...op.Condition) error

DeleteWithId is equal to o.Delete(op.KeyId.Eq(id), op.And(conds...)).

func (Oper[T]) Exist added in v0.34.0 

func (o Oper[T]) Exist(conds ...op.Condition) (exist bool, err error)

Exist is equal to o.ExistContext(context.Background(), conds...).

func (Oper[T]) ExistById added in v0.46.0 

func (o Oper[T]) ExistById(id int64) (bool, error)

ExistById is equal to o.ExistWithId(id).

func (Oper[T]) ExistContext added in v0.35.0 

func (o Oper[T]) ExistContext(ctx context.Context, conds ...op.Condition) (exist bool, err error)

ExistContext is used to check whether the records qualified by the conditions exist.

func (Oper[T]) ExistWithId added in v0.46.0 

func (o Oper[T]) ExistWithId(id int64, conds ...op.Condition) (bool, error)

ExistWithId is equal to o.Exist(op.KeyId.Eq(id), op.And(conds...)).

func (Oper[T]) Get added in v0.34.0 

func (o Oper[T]) Get(conds ...op.Condition) (obj T, ok bool, err error)

Get is equal to o.GetContext(context.Background(), conds...).

func (Oper[T]) GetAll added in v0.40.0 

func (o Oper[T]) GetAll(conds ...op.Condition) ([]T, error)

GetAll is equal to o.Gets(nil, conds...).

func (Oper[T]) GetById added in v0.36.0 

func (o Oper[T]) GetById(id int64) (v T, ok bool, err error)

GetById is equal to o.GetWithId(id).

func (Oper[T]) GetContext added in v0.34.0 

func (o Oper[T]) GetContext(ctx context.Context, conds ...op.Condition) (obj T, ok bool, err error)

GetContext just queries a first record from table.

func (Oper[T]) GetRow added in v0.36.0 

func (o Oper[T]) GetRow(columns any, conds ...op.Condition) Row

GetRow is equal to o.GetRowContext(context.Background(), columns, conds...).

func (Oper[T]) GetRowContext added in v0.36.0 

func (o Oper[T]) GetRowContext(ctx context.Context, columns any, conds ...op.Condition) Row

GetRowContext builds a SELECT statement and returns a Row.

func (Oper[T]) GetRows added in v0.35.0 

func (o Oper[T]) GetRows(columns any, page op.Pagination, conds ...op.Condition) Rows

GetRows is equal to o.GetRowsContext(context.Background(), columns, page, conds...).

func (Oper[T]) GetRowsContext added in v0.35.0 

func (o Oper[T]) GetRowsContext(ctx context.Context, columns any, page op.Pagination, conds ...op.Condition) Rows

GetRowsContext builds a SELECT statement and returns a Rows.

func (Oper[T]) GetWithId added in v0.46.0 

func (o Oper[T]) GetWithId(id int64, conds ...op.Condition) (v T, ok bool, err error)

GetWithId is equal to o.Get(nil, op.KeyId.Eq(id), op.And(conds...)).

func (Oper[T]) Gets added in v0.34.0 

func (o Oper[T]) Gets(page op.Pagination, conds ...op.Condition) (objs []T, err error)

Gets is equal to o.GetsContext(context.Background(), page, conds...).

func (Oper[T]) GetsContext added in v0.34.0 

func (o Oper[T]) GetsContext(ctx context.Context, page op.Pagination, conds ...op.Condition) (objs []T, err error)

GetsContext queries a set of results from table.

func (Oper[T]) IgnoredColumns added in v0.45.0 

func (o Oper[T]) IgnoredColumns() []string

IgnoredColumns returned the ignored selected columns.

func (Oper[T]) MakeSlice added in v0.34.0 

func (o Oper[T]) MakeSlice(cap int) []T

MakeSlice makes a slice with the cap.

If cap is equal to 0, use RowsCap or DefaultRowsCap instead.

func (Oper[T]) Query added in v0.36.0 

func (o Oper[T]) Query(page, pageSize int64, conds ...op.Condition) ([]T, error)

Query is equal to o.QueryContext(context.Background(), page, pageSize, conds...).

func (Oper[T]) QueryContext added in v0.36.0 

func (o Oper[T]) QueryContext(ctx context.Context, page, pageSize int64, conds ...op.Condition) ([]T, error)

QueryContext is a simplified GetsContext, which is equal to 

o.GetsContext(ctx, op.PageSize(page, pageSize), conds...)

page starts with 1. And if page or pageSize is less than 1, ignore the pagination.

func (Oper[T]) RowsBinder added in v0.47.0 

func (o Oper[T]) RowsBinder() RowsBinder

RowsBinder returns the inner rows binder.

func (Oper[T]) Select added in v0.36.0 

func (o Oper[T]) Select(columns any, conds ...op.Condition) *SelectBuilder

Select returns a SELECT builder, which sets the selected columns and the where condtions.

columns supports one of types as follow: 

string
[]string
struct

func (Oper[T]) SoftCount added in v0.34.0 

func (o Oper[T]) SoftCount(conds ...op.Condition) (total int, err error)

SoftCount is equal to o.SoftCountContext(context.Background(), conds...).

func (Oper[T]) SoftCountContext added in v0.36.0 

func (o Oper[T]) SoftCountContext(ctx context.Context, conds ...op.Condition) (total int, err error)

SoftCountContext is the same as CountContext, but appending SoftCondition into the conditions.

func (Oper[T]) SoftCountDistinct added in v0.34.0 

func (o Oper[T]) SoftCountDistinct(field string, conds ...op.Condition) (total int, err error)

SoftCountDistinct is equal to o.SoftCountDistinctContext(context.Background(), field, conds...).

func (Oper[T]) SoftCountDistinctContext added in v0.36.0 

func (o Oper[T]) SoftCountDistinctContext(ctx context.Context, field string, conds ...op.Condition) (total int, err error)

SoftCountDistinctContext is the same as CountDistinctContext, but appending SoftCondition into the conditions.

func (Oper[T]) SoftCountQuery added in v0.41.0 

func (o Oper[T]) SoftCountQuery(page, pagesize int64, conds ...op.Condition) (total int, objs []T, err error)

SoftCountQuery is equal to o.SoftCountQueryContext(context.Background(), page, pagesize, conds...).

func (Oper[T]) SoftCountQueryContext added in v0.41.0 

func (o Oper[T]) SoftCountQueryContext(ctx context.Context, page, pagesize int64, conds ...op.Condition) (total int, objs []T, err error)

SoftCountQueryContext is the same as CountQueryContext, but appending SoftCondition into the conditions.

func (Oper[T]) SoftDelete added in v0.34.0 

func (o Oper[T]) SoftDelete(conds ...op.Condition) error

SoftDelete is equal to o.SoftDeleteContext(context.Background(), conds...).

func (Oper[T]) SoftDeleteById added in v0.34.0 

func (o Oper[T]) SoftDeleteById(id int64) error

SoftDeleteById is equal to o.SoftDeleteWithId(id).

func (Oper[T]) SoftDeleteContext added in v0.34.0 

func (o Oper[T]) SoftDeleteContext(ctx context.Context, conds ...op.Condition) error

SoftDeleteContext soft deletes the records from the table, which only marks the records deleted.

func (Oper[T]) SoftDeleteWithId added in v0.46.0 

func (o Oper[T]) SoftDeleteWithId(id int64, conds ...op.Condition) error

SoftDeleteWithId is equal to o.SoftDelete(op.KeyId.Eq(id), op.And(conds...)).

func (Oper[T]) SoftExist added in v0.34.0 

func (o Oper[T]) SoftExist(conds ...op.Condition) (exist bool, err error)

SoftExist is equal to o.SoftExistContext(context.Background(), conds... ).

func (Oper[T]) SoftExistById added in v0.46.0 

func (o Oper[T]) SoftExistById(id int64) (bool, error)

SoftExistById is equal to o.SoftExistWithId(id).

func (Oper[T]) SoftExistContext added in v0.36.0 

func (o Oper[T]) SoftExistContext(ctx context.Context, conds ...op.Condition) (exist bool, err error)

SoftExistContext is the same as ExistContext, but appending SoftCondition into the conditions.

func (Oper[T]) SoftExistWithId added in v0.46.0 

func (o Oper[T]) SoftExistWithId(id int64, conds ...op.Condition) (bool, error)

SoftExistWithId is equal to o.SoftExist(op.KeyId.Eq(id), op.And(conds...)).

func (Oper[T]) SoftGet added in v0.34.0 

func (o Oper[T]) SoftGet(conds ...op.Condition) (obj T, ok bool, err error)

SoftGet is equal to o.SoftGetContext(context.Background(), conds...).

func (Oper[T]) SoftGetAll added in v0.40.0 

func (o Oper[T]) SoftGetAll(conds ...op.Condition) ([]T, error)

SoftGetAll is equal to o.SoftGets(nil, conds...).

func (Oper[T]) SoftGetById added in v0.34.0 

func (o Oper[T]) SoftGetById(id int64) (v T, ok bool, err error)

SoftGetById is equal to o.SoftGetWithId(id).

func (Oper[T]) SoftGetContext added in v0.34.0 

func (o Oper[T]) SoftGetContext(ctx context.Context, conds ...op.Condition) (obj T, ok bool, err error)

SoftGetContext is the same as GetContext, but appending SoftCondition into the conditions.

func (Oper[T]) SoftGetRow added in v0.43.0 

func (o Oper[T]) SoftGetRow(columns any, conds ...op.Condition) Row

SoftGetRow is equal to o.SoftGetRowContext(context.Background(), columns, conds...).

func (Oper[T]) SoftGetRowContext added in v0.43.0 

func (o Oper[T]) SoftGetRowContext(ctx context.Context, columns any, conds ...op.Condition) Row

SoftGetRowContext is the same as GetRowContext, but appending SoftCondition into the conditions.

func (Oper[T]) SoftGetRows added in v0.35.0 

func (o Oper[T]) SoftGetRows(columns any, page op.Pagination, conds ...op.Condition) Rows

SoftGetRows is equal to o.SoftGetRowsContext(context.Background(), columns, page, conds...).

func (Oper[T]) SoftGetRowsContext added in v0.35.0 

func (o Oper[T]) SoftGetRowsContext(ctx context.Context, columns any, page op.Pagination, conds ...op.Condition) Rows

SoftGetRowsContext is the same as GetRowsContext, but appending SoftCondition into the conditions.

func (Oper[T]) SoftGetWithId added in v0.46.0 

func (o Oper[T]) SoftGetWithId(id int64, conds ...op.Condition) (v T, ok bool, err error)

SoftGetWithId is equal to o.SoftGet(nil, op.KeyId.Eq(id), op.And(conds...)).

func (Oper[T]) SoftGets added in v0.34.0 

func (o Oper[T]) SoftGets(page op.Pagination, conds ...op.Condition) ([]T, error)

SoftGets is equal to o.SoftGetsContext(context.Background(), page, conds...).

func (Oper[T]) SoftGetsContext added in v0.34.0 

func (o Oper[T]) SoftGetsContext(ctx context.Context, page op.Pagination, conds ...op.Condition) ([]T, error)

SoftGetsContext is the same as GetsContext, but appending SoftCondition into the conditions.

func (Oper[T]) SoftQuery added in v0.34.0 

func (o Oper[T]) SoftQuery(page, pageSize int64, conds ...op.Condition) ([]T, error)

SoftQuery is equal to o.SoftQueryContext(context.Background(), page, pageSize, conds...).

func (Oper[T]) SoftQueryContext added in v0.36.0 

func (o Oper[T]) SoftQueryContext(ctx context.Context, page, pageSize int64, conds ...op.Condition) ([]T, error)

SoftQueryContext is the same as QueryContext, but appending SoftCondition into the conditions.

func (Oper[T]) SoftSelect added in v0.36.0 

func (o Oper[T]) SoftSelect(columns any, conds ...op.Condition) *SelectBuilder

SoftSelect is the same as Select, but appends SoftCondition into the conditions.

func (Oper[T]) SoftSum added in v0.35.0 

func (o Oper[T]) SoftSum(field string, conds ...op.Condition) (total int, err error)

SoftSum is equal to o.SoftSumContext(context.Background(), field, conds...).

func (Oper[T]) SoftSumContext added in v0.36.0 

func (o Oper[T]) SoftSumContext(ctx context.Context, field string, conds ...op.Condition) (total int, err error)

SoftSumContext is the same as SumContext, but appending SoftCondition into the conditions.

func (Oper[T]) SoftUpdate added in v0.34.0 

func (o Oper[T]) SoftUpdate(updater op.Updater, conds ...op.Condition) (err error)

SoftUpdate is equal to o.SoftUpdateContext(context.Background(), updater, conds...).

func (Oper[T]) SoftUpdateById added in v0.34.0 

func (o Oper[T]) SoftUpdateById(id int64, updaters ...op.Updater) error

SoftUpdateById is equal to o.SoftUpdate(op.Batch(updaters...), op.KeyId.Eq(id)).

func (Oper[T]) SoftUpdateContext added in v0.34.0 

func (o Oper[T]) SoftUpdateContext(ctx context.Context, updater op.Updater, conds ...op.Condition) error

SoftUpdateContext is the same as UpdateContext, but appending SoftCondition into the conditions.

func (Oper[T]) Sum added in v0.35.0 

func (o Oper[T]) Sum(field string, conds ...op.Condition) (int, error)

Sum is equal to o.SumContext(context.Background(), field, conds...).

func (Oper[T]) SumContext added in v0.35.0 

func (o Oper[T]) SumContext(ctx context.Context, field string, conds ...op.Condition) (total int, err error)

SumContext is used to sum the field values of the records by the condition.

func (Oper[T]) Update added in v0.34.0 

func (o Oper[T]) Update(updater op.Updater, conds ...op.Condition) error

Update is equal to o.UpdateContext(context.Background(), updater, conds...).

func (Oper[T]) UpdateById added in v0.36.0 

func (o Oper[T]) UpdateById(id int64, updaters ...op.Updater) error

UpdateById is equal to o.Update(op.Batch(updaters...), op.KeyId.Eq(id)).

func (Oper[T]) UpdateContext added in v0.34.0 

func (o Oper[T]) UpdateContext(ctx context.Context, updater op.Updater, conds ...op.Condition) error

UpdateContext updates the sql table records.

If updater is nil, do nothing.

func (Oper[T]) WithDB added in v0.34.0 

func (o Oper[T]) WithDB(db *DB) Oper[T]

WithDB returns a new Oper with the new db.

func (Oper[T]) WithIgnoredColumns added in v0.45.0 

func (o Oper[T]) WithIgnoredColumns(columns []string) Oper[T]

WithIgnoredColumns returns a new Oper with the ignored selected columns.

Default: nil

func (Oper[T]) WithRowScannerWrapper added in v0.43.0 

func (o Oper[T]) WithRowScannerWrapper(wrapper RowScannerWrapper) Oper[T]

WithRowScannerWrapper returns a new Oper with the row scanner wrapper to wrap the row scanner to customize to scan the row.

Default: DefaultRowScanWrapper

func (Oper[T]) WithRowsBinder added in v0.44.0 

func (o Oper[T]) WithRowsBinder(binder RowsBinder) Oper[T]

WithRowsBinder returns a new Oper with the rows binder to bind the rows to a slice, map or other.

Default: NewDegradedSliceRowsBinder[[]T](DefaultMixRowsBinder)

func (Oper[T]) WithRowsCap added in v0.43.0 

func (o Oper[T]) WithRowsCap(cap int) Oper[T]

WithRowsCap returns a new Oper with the default cap of the container, such as slice or map, bound from rows.

Default: DefaultRowsCap

func (Oper[T]) WithSoftCondition added in v0.34.0 

func (o Oper[T]) WithSoftCondition(softcond op.Condition) Oper[T]

WithSoftCondition returns a new Oper with the soft condition.

func (Oper[T]) WithSoftDeleteUpdater added in v0.34.0 

func (o Oper[T]) WithSoftDeleteUpdater(softDeleteUpdater func(context.Context) op.Updater) Oper[T]

WithSoftDeleteUpdater returns a new Oper with the soft delete udpater.

func (Oper[T]) WithSorter added in v0.41.0 

func (o Oper[T]) WithSorter(sorter op.Sorter) Oper[T]

WithSorter returns a new Oper with the new sorter.

func (Oper[T]) WithTable added in v0.34.0 

func (o Oper[T]) WithTable(table Table) Oper[T]

WithTable returns a new Oper with the new table.

type Order 

type Order string

Order represents the order used by ORDER BY. 

const (
	Asc  Order = "ASC"
	Desc Order = "DESC"
)

Predefine some orders used by ORDER BY.

type Row 

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

Row is the same as sql.Row to scan the row to the values.

func NewRow 

func NewRow(rows *sql.Rows, columns []string, err error) Row

NewRow returns a new Row.

func (Row) Bind added in v0.35.0 

func (r Row) Bind(dsts ...any) (ok bool, err error)

Bind binds the row to the dsts, which never return sql.ErrNoRows as err and uses ok instead of it.

func (Row) BindStruct added in v0.35.0 

func (r Row) BindStruct(s any) (ok bool, err error)

Bind is the same as BindStruct, but returns (false, nil) if Scan returns sql.ErrNoRows.

DEPRECATED!!! Please use r.Bind(s) instead.

func (Row) Columns 

func (r Row) Columns() ([]string, error)

Columns returns the names of the selected columns.

func (Row) Next added in v0.43.0 

func (r Row) Next() bool

Next is the same as sql.Row.Next, but only used to implement RowScanner and must not be called.

func (Row) Scan 

func (r Row) Scan(dsts ...any) (err error)

Scan implements the interface sql.Scanner, which is the same as sql.Row.Scan but supports that the sql value is NULL.

func (Row) ScanStruct 

func (r Row) ScanStruct(s any) (err error)

ScanStruct is the same as Scan, but the columns are scanned into the struct s, which uses ScanColumnsToStruct.

DEPRECATED!!! Please use r.Bind(s) instead.

func (Row) ScanStructWithColumns 

func (r Row) ScanStructWithColumns(s any, columns ...string) (err error)

ScanStructWithColumns is the same as Scan, but the columns are scanned into the struct s by using ScanColumnsToStruct.

DEPRECATED!!! Please use r.WithColumns(columns...).Bind(s) instead.

func (Row) WithColumns added in v0.43.0 

func (r Row) WithColumns(columns ...string) Row

WithColumns resets the names of the selected columns and returns a new Row.

func (Row) WithScanner added in v0.43.0 

func (r Row) WithScanner(wrapper RowScannerWrapper) Row

WithScanner resets the row scanner wrapper and returns a new Row.

type RowScanner added in v0.43.0 

type RowScanner interface {
	Columns() ([]string, error)
	Scan(dst ...any) error
	Next() bool
}

RowScanner is an interface to scan the row.

All of *sql.Rows, Rows and Row have implement the interface.

type RowScannerWrapper added in v0.43.0 

type RowScannerWrapper func(scanner RowScanner, dsts ...any) (err error)

RowScannerWrapper is used to wrap the row scanner to customize to scan the row. 

var DefaultRowScanWrapper RowScannerWrapper = defaultRowScanWrapper

DefaultRowScanWrapper is the default wrapper for RowScanner.

type Rows 

type Rows struct {
	*sql.Rows
	Err error
	// contains filtered or unexported fields
}

Rows is the same as sql.Rows to scan the rows to a map or slice.

func NewRows 

func NewRows(rows *sql.Rows, columns []string, err error) Rows

NewRows returns a new Rows.

func (Rows) Bind added in v0.43.0 

func (r Rows) Bind(dst any) error

Bind binds the rows to dst that may be a map or slice

func (Rows) BindSlice added in v0.35.0 

func (r Rows) BindSlice(slice any) (err error)

BindSlice is the same as ScanSlice, but closes sql.Rows.

DEPRECATED!!! Please use r.Bind(slice) instead.

func (Rows) Columns 

func (r Rows) Columns() ([]string, error)

Columns returns the names of the selected columns.

func (Rows) RowsCap added in v0.43.0 

func (r Rows) RowsCap() int

RowsCap returns the capacity of the rows.

func (Rows) Scan 

func (r Rows) Scan(dsts ...any) (err error)

Scan implements the interface sql.Scanner, which is the same as sql.Rows.Scan but supports that the sql value is NULL.

func (Rows) ScanSlice 

func (r Rows) ScanSlice(slice any) (err error)

ScanSlice is used to scan the row set into the slice.

DEPRECATED!!! Please use r.Bind(slice) instead.

func (Rows) ScanStruct 

func (r Rows) ScanStruct(s any) (err error)

ScanStruct is the same as Scan, but the columns are scanned into the struct s, which uses ScanColumnsToStruct.

DEPRECATED!!! Please use r.Bind(slice) instead.

func (Rows) ScanStructWithColumns 

func (r Rows) ScanStructWithColumns(s any, columns ...string) (err error)

ScanStructWithColumns is the same as Scan, but the columns are scanned into the struct s by using ScanColumnsToStruct.

DEPRECATED!!! Please use r.Bind(slice) instead.

func (Rows) TryBindSlice added in v0.35.0 

func (r Rows) TryBindSlice(slice any, err error) error

TryBindSlice is the same as BindSlice, which binds rows to slice only if err is equal to nil.

DEPRECATED!!! Please use r.Bind(slice) instead.

func (Rows) WithBinder added in v0.43.0 

func (r Rows) WithBinder(binder RowsBinder) Rows

WithBinder resets the rows binder and returns a new Rows.

func (Rows) WithColumns added in v0.43.0 

func (r Rows) WithColumns(columns ...string) Rows

WithColumns resets the names of the selected columns and returns a new Rows.

func (Rows) WithRowsCap added in v0.43.0 

func (r Rows) WithRowsCap(cap int) Rows

WithRowsCap resets the capacity of the rows and returns a new Rows.

func (Rows) WithScanner added in v0.43.0 

func (r Rows) WithScanner(wrapper RowScannerWrapper) Rows

WithScanner resets the row scanner wrapper and returns a new Rows.

type RowsBinder added in v0.43.0 

type RowsBinder interface {
	BindRows(scanner RowScanner, dst any) error
}

RowsBinder is an interface to bind the rows to dst that may be a map or slice.

func ComposeRowsBinders added in v0.47.0 

func ComposeRowsBinders(binders ...RowsBinder) RowsBinder

ComposeRowsBinders returns a composed rows binder which tries each binder in order.

If the binder returns an UnsupportedTypeError, the next binder is tried.

func NewDegradedSliceRowsBinder added in v0.43.0 

func NewDegradedSliceRowsBinder[S ~[]T, T any](degraded RowsBinder) RowsBinder

NewDegradedSliceRowsBinder returns a rows binder which prefers to try to bind *S to the rows, or use the degraded rows binder to bind the rows.

func NewMapRowsBinderForKey added in v0.43.0 

func NewMapRowsBinderForKey[M ~map[K]V, K comparable, V any](valuef func(K) V) RowsBinder

NewMapRowsBinderForKey returns a rows binder which binds the rows as the map keys and extracts the map values from the keys.

func NewMapRowsBinderForKeyAndFixedValue added in v0.43.0 

func NewMapRowsBinderForKeyAndFixedValue[M ~map[K]V, K comparable, V any](value V) RowsBinder

NewMapRowsBinderForKeyAndFixedValue is short for NewMapRowsBinderForKey, which is equal to 

NewMapRowsBinderForKey[M](func(K) V { return value })

func NewMapRowsBinderForKeyValue added in v0.43.0 

func NewMapRowsBinderForKeyValue[M ~map[K]V, K comparable, V any]() RowsBinder

NewMapRowsBinderForKeyValue returns a rows binder which binds the rows as the map keys and values.

Notice: each row must have two columns as key and value from front to back.

func NewMapRowsBinderForValue added in v0.43.0 

func NewMapRowsBinderForValue[M ~map[K]V, K comparable, V any](keyf func(V) K) RowsBinder

NewMapRowsBinderForValue returns a rows binder which binds the rows as the map values and extracts the map keys from the values.

func NewSliceRowsBinder added in v0.43.0 

func NewSliceRowsBinder[S ~[]T, T any]() RowsBinder

NewSliceRowsBinder returns a rows binder which binds the rows as the slice.

It does not use the reflect package.

type RowsBinderFunc added in v0.43.0 

type RowsBinderFunc func(scanner RowScanner, dst any) (err error)

RowsBinderFunc is a function to bind the rows to dst that may be a map or slice.

func (RowsBinderFunc) BindRows added in v0.43.0 

func (f RowsBinderFunc) BindRows(scanner RowScanner, dst any) error

BindRows implements the interface RowsBinder.

type SelectBuilder 

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

SelectBuilder is used to build the SELECT statement.

Example 
sel1 := Select("*").From("table").Where(op.Equal("id", 123)).Comment("abc")
sel2 := Select("*").FromAlias("table", "alias").Where(op.Equal("id", 123))
sel3 := SelectAlias("id", "c1").SelectAlias("name", "c2").FromAlias("table", "alias").Where(op.Equal("id", 123))
sel4 := Select("A.id").Select("B.name").FromAlias("table1", "A").FromAlias("table2", "B").Where(op.EqualKey("A.id", "B.id"))

sql1, args1 := sel1.Build()
sql2, args2 := sel2.Build()
sql3, args3 := sel3.Build()
sql4, args4 := sel4.Build()

fmt.Println(sql1)
fmt.Println(args1.Args())

fmt.Println(sql2)
fmt.Println(args2.Args())

fmt.Println(sql3)
fmt.Println(args3.Args())

fmt.Println(sql4)
fmt.Println(args4.Args())

Output:

SELECT * FROM `table` WHERE `id`=? /* abc */
[123]
SELECT * FROM `table` AS `alias` WHERE `id`=?
[123]
SELECT `id` AS `c1`, `name` AS `c2` FROM `table` AS `alias` WHERE `id`=?
[123]
SELECT `A`.`id`, `B`.`name` FROM `table1` AS `A`, `table2` AS `B` WHERE `A`.`id`=`B`.`id`
[]

func NewSelectBuilder 

func NewSelectBuilder() *SelectBuilder

NewSelectBuilder returns a new SELECT builder.

func Select 

func Select(column string) *SelectBuilder

Select is equal to SelectAlias(column, "").

func SelectAlias added in v0.27.0 

func SelectAlias(column, alias string) *SelectBuilder

SelectAlias is equal to NewSelectBuilder().SelectAlias(column, alias).

func SelectStruct 

func SelectStruct(s any) *SelectBuilder

SelectStruct is equal to SelectStructWithTable(s, "").

func SelectStructWithTable added in v0.27.0 

func SelectStructWithTable(s any, table string) *SelectBuilder

SelectStruct is equal to NewSelectBuilder().SelectStructWithTable(s, table).

func Selects 

func Selects(columns ...string) *SelectBuilder

Selects is equal to Select(columns[0]).Select(columns[1])...

func (*SelectBuilder) BindRow 

func (b *SelectBuilder) BindRow(dest ...any) (bool, error)

BindRow is equal to b.BindRowContext(context.Background(), dest...).

DEPRECATED!!! Please use b.QueryRow().Bind(dest...) instead.

func (*SelectBuilder) BindRowContext 

func (b *SelectBuilder) BindRowContext(c context.Context, dest ...any) (bool, error)

BindRowContext is convenient function, which is equal to b.QueryRowContext(c).Bind(dest...).

DEPRECATED!!! Please use b.QueryRowContext(c).Bind(dest...) instead.

func (*SelectBuilder) BindRowStruct 

func (b *SelectBuilder) BindRowStruct(dest any) (bool, error)

BindRowStruct is equal to b.BindRowStructContext(context.Background(), dest).

DEPRECATED!!! Please use b.QueryRow().Bind(dest) instead.

func (*SelectBuilder) BindRowStructContext 

func (b *SelectBuilder) BindRowStructContext(c context.Context, dest any) (bool, error)

BindRowStructContext is convenient function, which is equal to b.QueryRowContext(c).BindStruct(dest).

DEPRECATED!!! Please use b.QueryRowContext(c).Bind(dest) instead.

func (*SelectBuilder) BindRows 

func (b *SelectBuilder) BindRows(slice any) error

BindRows is equal to b.BindRowsContext(context.Background(), slice).

DEPRECATED!!! Please use b.QueryRows().Bind(slice) instead.

func (*SelectBuilder) BindRowsContext 

func (b *SelectBuilder) BindRowsContext(ctx context.Context, slice any) error

BindRowsContext is the same QueryContext, but scans the result set into the slice.

Notice: slice must be a pointer to a slice. And the element of the slice may be a struct or type implemented the interface sql.Scanner.

DEPRECATED!!! Please use b.QueryRowsContext(ctx).Bind(slice) instead.

func (*SelectBuilder) Build 

func (b *SelectBuilder) Build() (sql string, args *ArgsBuilder)

Build builds the SELECT sql statement.

func (*SelectBuilder) Comment added in v0.37.0 

func (b *SelectBuilder) Comment(comment string) *SelectBuilder

Comment set the comment, which will be appended to the end of the built SQL statement.

func (*SelectBuilder) Distinct 

func (b *SelectBuilder) Distinct() *SelectBuilder

Distinct marks SELECT as DISTINCT.

func (*SelectBuilder) ForceOrderBy added in v0.47.0 

func (b *SelectBuilder) ForceOrderBy(force bool) *SelectBuilder

ForceOrderBy builds the "ORDER BY" clause for the columns unconditionally.

If false, the "ORDER BY" clause will be built only if the column is selected.

Default: false

func (*SelectBuilder) From 

func (b *SelectBuilder) From(table string) *SelectBuilder

From is equal to b.FromAlias(table, "").

func (*SelectBuilder) FromAlias added in v0.27.0 

func (b *SelectBuilder) FromAlias(table, alias string) *SelectBuilder

FromAlias appends the FROM table name in SELECT with the alias.

If alias is empty, ignore it.

func (*SelectBuilder) Froms 

func (b *SelectBuilder) Froms(tables ...string) *SelectBuilder

Froms is the same as b.From(table0).From(table1)...

func (*SelectBuilder) GroupBy 

func (b *SelectBuilder) GroupBy(columns ...string) *SelectBuilder

GroupBy resets the GROUP BY columns.

Example 
s := Select("*").From("table").Where(op.Equal("id", 123)).GroupBy("area")
sql, args := s.Build()

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

Output:

SELECT * FROM `table` WHERE `id`=? GROUP BY `area`
[123]

func (*SelectBuilder) Having 

func (b *SelectBuilder) Having(exprs ...string) *SelectBuilder

Having appends the HAVING expression.

func (*SelectBuilder) IgnoreColumns added in v0.43.0 

func (b *SelectBuilder) IgnoreColumns(columns []string) *SelectBuilder

IgnoredColumns sets the ignored columns and returns itself.

Example 
b := Selects("id", "name", "age", "updated_at").From("table").
	Where(op.Equal("id", 123)).IgnoreColumns([]string{"updated_at"})

sql, args := b.Build()

fmt.Println(b.SelectedColumns())
fmt.Println(sql)
fmt.Println(args.Args())

Output:

[id name age]
SELECT `id`, `name`, `age` FROM `table` WHERE `id`=?
[123]

func (*SelectBuilder) Join 

func (b *SelectBuilder) Join(table, alias string, ons ...JoinOn) *SelectBuilder

Join appends the "JOIN table ON on..." statement.

Example 
s := Select("*").From("table1").Join("table2", "", On("table1.id", "table2.id")).
	Where(op.Equal("table1.id", 123)).OrderByAsc("table1.time").Limit(10).Offset(100)
sql, args := s.Build()

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

Output:

SELECT * FROM `table1` JOIN `table2` ON `table1`.`id`=`table2`.`id` WHERE `table1`.`id`=? ORDER BY `table1`.`time` ASC LIMIT 10 OFFSET 100
[123]

func (*SelectBuilder) JoinFull 

func (b *SelectBuilder) JoinFull(table, alias string, ons ...JoinOn) *SelectBuilder

JoinFull appends the "FULL JOIN table ON on..." statement.

func (*SelectBuilder) JoinFullOuter 

func (b *SelectBuilder) JoinFullOuter(table, alias string, ons ...JoinOn) *SelectBuilder

JoinFullOuter appends the "FULL OUTER JOIN table ON on..." statement.

func (*SelectBuilder) JoinInner added in v0.47.0 

func (b *SelectBuilder) JoinInner(table, alias string, ons ...JoinOn) *SelectBuilder

JoinInner appends the "INNER JOIN table ON on..." statement.

func (*SelectBuilder) JoinLeft 

func (b *SelectBuilder) JoinLeft(table, alias string, ons ...JoinOn) *SelectBuilder

JoinLeft appends the "LEFT JOIN table ON on..." statement.

func (*SelectBuilder) JoinLeftOuter 

func (b *SelectBuilder) JoinLeftOuter(table, alias string, ons ...JoinOn) *SelectBuilder

JoinLeftOuter appends the "LEFT OUTER JOIN table ON on..." statement.

func (*SelectBuilder) JoinRight 

func (b *SelectBuilder) JoinRight(table, alias string, ons ...JoinOn) *SelectBuilder

JoinRight appends the "RIGHT JOIN table ON on..." statement.

func (*SelectBuilder) JoinRightOuter 

func (b *SelectBuilder) JoinRightOuter(table, alias string, ons ...JoinOn) *SelectBuilder

JoinRightOuter appends the "RIGHT OUTER JOIN table ON on..." statement.

func (*SelectBuilder) Limit 

func (b *SelectBuilder) Limit(limit int64) *SelectBuilder

Limit sets the LIMIT to limit.

Example 
s := Select("*").From("table").Where(op.Equal("id", 123)).
	OrderByAsc("time").Limit(10).Offset(100)
sql, args := s.Build()

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

Output:

SELECT * FROM `table` WHERE `id`=? ORDER BY `time` ASC LIMIT 10 OFFSET 100
[123]

func (*SelectBuilder) Offset 

func (b *SelectBuilder) Offset(offset int64) *SelectBuilder

Offset sets the OFFSET to offset.

func (*SelectBuilder) OrderBy 

func (b *SelectBuilder) OrderBy(column string, order Order) *SelectBuilder

OrderBy appends the column used by ORDER BY.

Example 
s1 := Select("*").From("table").Where(op.Equal("id", 123)).OrderBy("time", Asc)
s2 := Select("*").From("table").Where(op.Equal("id", 123)).OrderBy("time", Desc)

sql1, args1 := s1.Build()
sql2, args2 := s2.Build()

fmt.Println(sql1)
fmt.Println(args1.Args())

fmt.Println(sql2)
fmt.Println(args2.Args())

Output:

SELECT * FROM `table` WHERE `id`=? ORDER BY `time` ASC
[123]
SELECT * FROM `table` WHERE `id`=? ORDER BY `time` DESC
[123]

func (*SelectBuilder) OrderByAsc 

func (b *SelectBuilder) OrderByAsc(column string) *SelectBuilder

OrderByAsc appends the column used by ORDER BY ASC.

func (*SelectBuilder) OrderByDesc 

func (b *SelectBuilder) OrderByDesc(column string) *SelectBuilder

OrderByDesc appends the column used by ORDER BY DESC.

func (*SelectBuilder) Paginate 

func (b *SelectBuilder) Paginate(pageNum, pageSize int64) *SelectBuilder

Paginate is equal to b.Limit(pageSize).Offset((pageNum-1) * pageSize).

pageNum starts with 1. If pageNum or pageSize is less than 1, do nothing.

func (*SelectBuilder) Pagination added in v0.43.0 

func (b *SelectBuilder) Pagination(page op.Pagination) *SelectBuilder

Pagination sets the Pagination, which is the same as b.Paginate.

func (*SelectBuilder) Paginator added in v0.27.0 

func (b *SelectBuilder) Paginator(page op.Pagination) *SelectBuilder

Paginator is deprecated and reserved as the alias of Pagination for backward compatibility.

func (*SelectBuilder) Query 

func (b *SelectBuilder) Query() (Rows, error)

Query builds the sql and executes it.

DEPRECATED!!! Please use b.QueryRows() instead.

func (*SelectBuilder) QueryContext 

func (b *SelectBuilder) QueryContext(ctx context.Context) (Rows, error)

QueryContext builds the sql and executes it.

DEPRECATED!!! Please use b.QueryRowsContext(ctx) instead.

func (*SelectBuilder) QueryRow 

func (b *SelectBuilder) QueryRow() Row

QueryRow builds the sql and executes it.

func (*SelectBuilder) QueryRowContext 

func (b *SelectBuilder) QueryRowContext(ctx context.Context) Row

QueryRowContext builds the sql and executes it.

func (*SelectBuilder) QueryRows added in v0.43.0 

func (b *SelectBuilder) QueryRows() Rows

QueryRows builds the sql and executes it.

func (*SelectBuilder) QueryRowsContext added in v0.43.0 

func (b *SelectBuilder) QueryRowsContext(ctx context.Context) Rows

QueryRowsContext builds the sql and executes it.

func (*SelectBuilder) Select 

func (b *SelectBuilder) Select(column string) *SelectBuilder

Select appends the selected column in SELECT.

func (*SelectBuilder) SelectAlias added in v0.27.0 

func (b *SelectBuilder) SelectAlias(column, alias string) *SelectBuilder

Select appends the selected column in SELECT with the alias.

If alias is empty, it will be ignored.

func (*SelectBuilder) SelectCount added in v0.30.0 

func (b *SelectBuilder) SelectCount(field string) *SelectBuilder

SelectCount appends the selected COUNT(field) column in SELECT.

func (*SelectBuilder) SelectCountDistinct added in v0.30.0 

func (b *SelectBuilder) SelectCountDistinct(field string) *SelectBuilder

SelectCountDistinct appends the selected COUNT(DISTINCT field) column in SELECT.

func (*SelectBuilder) SelectNamers added in v0.45.0 

func (b *SelectBuilder) SelectNamers(columns ...Namer) *SelectBuilder

SelectNamers appends the selected column in SELECT from the column names and aliases.

func (*SelectBuilder) SelectStruct 

func (b *SelectBuilder) SelectStruct(s any) *SelectBuilder

SelectStruct is equal to b.SelectStructWithTable(s, "").

Example 
type S struct {
	DefaultField  string
	ModifiedField string `sql:"field"`
	IgnoredField  string `sql:"-"`

	Valuer MyTime `sql:"time"`
}

s := S{}
sb := SelectStructWithTable(s, "A")
columns := sb.SelectedColumns()
fmt.Println(columns)

err := ScanColumnsToStruct(func(values ...any) error {
	_time := time.Date(2025, 1, 2, 3, 4, 5, 0, time.Local)
	for i, v := range values {
		switch vp := v.(type) {
		case *MyTime:
			vp.Time = _time

		case *string:
			switch i {
			case 0:
				*vp = "a"
			case 1:
				*vp = "b"
			default:
				fmt.Printf("unknown %dth column value\n", i)
			}
		}
	}
	return nil
}, columns, &s)

if err != nil {
	fmt.Println(err)
} else {
	fmt.Println(s.DefaultField)
	fmt.Println(s.ModifiedField)
	fmt.Println(s.IgnoredField)
	fmt.Println(s.Valuer.String())
}

Output:

[DefaultField field time]
a
b

2025-01-02/03:04:05

func (*SelectBuilder) SelectStructWithTable added in v0.27.0 

func (b *SelectBuilder) SelectStructWithTable(s any, table string) *SelectBuilder

SelectStructWithTable reflects and extracts the fields of the struct as the selected columns, which supports the tag named "sql" to modify the column name.

If the value of the tag is "-", however, the field will be ignored.

func (*SelectBuilder) SelectedColumns 

func (b *SelectBuilder) SelectedColumns() []string

SelectedColumns is the same as SelectedFullColumns, but returns the short names instead.

Example 
b := Select("A.C1").SelectAlias("B.C2", "F2").FromAlias("table1", "A").FromAlias("table2", "B")
columns := b.SelectedColumns()
fmt.Println(columns)

Output:

[C1 F2]

func (*SelectBuilder) SelectedFullColumns 

func (b *SelectBuilder) SelectedFullColumns() []string

SelectedFullColumns returns the full names of the selected columns.

Notice: if the column has the alias, the alias will be returned instead.

Example 
b := Select("A.C1").SelectAlias("B.C2", "F2").FromAlias("table1", "A").FromAlias("table2", "B")
columns := b.SelectedFullColumns()
fmt.Println(columns)

Output:

[A.C1 B.C2]

func (*SelectBuilder) Selects 

func (b *SelectBuilder) Selects(columns ...string) *SelectBuilder

Selects appends a group of columns in SELECT from the column names.

func (*SelectBuilder) SetDB 

func (b *SelectBuilder) SetDB(db *DB) *SelectBuilder

SetDB sets the db.

func (*SelectBuilder) Sort added in v0.27.0 

func (b *SelectBuilder) Sort(sorter op.Sorter) *SelectBuilder

Sort appends a sort.

func (*SelectBuilder) Sorts added in v0.41.0 

func (b *SelectBuilder) Sorts(sorters ...op.Sorter) *SelectBuilder

Sorts appends a set of sorts.

func (*SelectBuilder) String 

func (b *SelectBuilder) String() string

String is the same as b.Build(), except args.

func (*SelectBuilder) Sum added in v0.31.0 

func (b *SelectBuilder) Sum(field string) *SelectBuilder

SelectSum appends the selected SUM(field) column in SELECT.

func (*SelectBuilder) Where 

func (b *SelectBuilder) Where(andConditions ...op.Condition) *SelectBuilder

Where sets the WHERE conditions.

func (*SelectBuilder) WhereNamedArgs 

func (b *SelectBuilder) WhereNamedArgs(andArgs ...sql.NamedArg) *SelectBuilder

WhereNamedArgs is the same as Where, but uses the NamedArg as the condition.

type SqlCollector added in v0.36.0 

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

SqlCollector is used to collect the executed sqls.

func NewSqlCollector added in v0.36.0 

func NewSqlCollector() *SqlCollector

NewSqlCollector returns a new SqlCollector with enabled==true.

func (*SqlCollector) Intercept added in v0.36.0 

func (c *SqlCollector) Intercept(sql string, args []any) (string, []any, error)

Intercept implements the interface Interceptor.

func (*SqlCollector) SetEnableFunc added in v0.36.0 

func (c *SqlCollector) SetEnableFunc(enablef func() bool) *SqlCollector

SetEnableFunc sets whether to collect the executed sql.

It's not thread-safe and should be called after using.

func (*SqlCollector) SetEnabled added in v0.36.0 

func (c *SqlCollector) SetEnabled(enabled bool) *SqlCollector

SetEnabled sets whether to collect the executed sql.

It's thread-safe. But It will have no effect if enablef is set.

func (*SqlCollector) SetFilterFunc added in v0.36.0 

func (c *SqlCollector) SetFilterFunc(filterf func(sql string) bool) *SqlCollector

SetFilterFunc sets a filter function to decide to collect the sql only if filter returns true.

It's not thread-safe and should be called after using.

func (*SqlCollector) Sqls added in v0.36.0 

func (c *SqlCollector) Sqls() []string

Sqls returns the executed sqls.

type Strings added in v0.39.0 

type Strings []string

String is a string slice value type, which is encoded to a string or decoded from a []byte or string.

func (Strings) IsZero added in v0.47.0 

func (vs Strings) IsZero() bool

IsZero reports whether the string slice is ZERO.

func (*Strings) Scan added in v0.39.0 

func (vs *Strings) Scan(src any) error

Scan implements the interface sql.Scanner to scan a sql value to the map.

func (Strings) Value added in v0.39.0 

func (vs Strings) Value() (driver.Value, error)

Value implements the interface driver.Valuer to encode the map to a sql value(string).

type Table 

type Table struct {
	Name string
	*DB
}

Table represents a SQL table.

func NewTable 

func NewTable(name string) Table

NewTable returns a new Table with the name.

func (Table) DeleteFrom 

func (t Table) DeleteFrom(conds ...op.Condition) *DeleteBuilder

DeleteFrom returns a DELETE FROM builder.

func (Table) GetDB 

func (t Table) GetDB() *DB

GetDB returns the set DB. Or returns DefaultDB instead if not set.

func (Table) InsertInto 

func (t Table) InsertInto() *InsertBuilder

InsertInto returns a INSERT INTO builder.

func (Table) Select 

func (t Table) Select(column string) *SelectBuilder

Select is equal to t.GetDB().Select(column).

func (Table) SelectAlias added in v0.27.0 

func (t Table) SelectAlias(column, alias string) *SelectBuilder

SelectAlias is equal to t.GetDB().SelectAlias(column, alias).

func (Table) SelectStruct 

func (t Table) SelectStruct(s any) *SelectBuilder

SelectStruct is equal to t.GetDB().SelectStructWithTable(s, "").

func (Table) SelectStructWithTable added in v0.27.0 

func (t Table) SelectStructWithTable(s any, table string) *SelectBuilder

SelectStructWithTable is equal to t.GetDB().SelectStructWithTable(s, table).

func (Table) Selects 

func (t Table) Selects(columns ...string) *SelectBuilder

Selects is equal to t.GetDB().Selects(columns...).

func (*Table) SetDB 

func (t *Table) SetDB(db *DB)

SetDB reset the db.

func (Table) String added in v0.27.0 

func (t Table) String() string

String returns the table name.

func (Table) Update 

func (t Table) Update(updaters ...op.Updater) *UpdateBuilder

Update returns a UPDATE builder.

func (Table) WithDB 

func (t Table) WithDB(db *DB) Table

WithDB returns a new Table with the given db.

type UnsupportedTypeError added in v0.47.0 

type UnsupportedTypeError struct {
	Name string
	Type string
}

UnsupportedTypeError represents the error that the type is not supported.

func (UnsupportedTypeError) Error added in v0.47.0 

func (e UnsupportedTypeError) Error() string

type UpdateBuilder 

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

UpdateBuilder is used to build the UPDATE statement.

Example 
// No Where
update1 := Update().Table("table").
	Set(op.Add("c1", 11), op.Sub("c2", 22)).
	Set(op.Mul("c3", 33), op.Div("c4", 44))

// With Where
update2 := Update().Table("table").
	Set(op.Set("c1", "v1"), op.Inc("c2"), op.Dec("c3")).
	Where(op.Equal("c4", "v4"), op.NotEqual("c5", "v5")).
	Where(op.Like("c6", "v6"), op.NotLike("c7", "v7%")).
	Where(op.Between("c8", 11, 22))

sql1, args1 := update1.Build()
sql2, args2 := update2.SetDB(&DB{Dialect: Postgres}).Build()

fmt.Println(sql1)
fmt.Println(args1.Args())
fmt.Println(sql2)
fmt.Println(args2.Args())

Output:

UPDATE `table` SET `c1`=`c1`+?, `c2`=`c2`-?, `c3`=`c3`*?, `c4`=`c4`/?
[11 22 33 44]
UPDATE "table" SET "c1"=$1, "c2"="c2"+1, "c3"="c3"-1 WHERE ("c4"=$2 AND "c5"<>$3 AND "c6" LIKE $4 AND "c7" NOT LIKE $5 AND "c8" BETWEEN $6 AND $7)
[v1 v4 v5 %v6% v7% 11 22]

func NewUpdateBuilder 

func NewUpdateBuilder() *UpdateBuilder

NewUpdateBuilder returns a new UPDATE builder.

func Update 

func Update() *UpdateBuilder

Update is short for NewUpdateBuilder.

func (*UpdateBuilder) Build 

func (b *UpdateBuilder) Build() (sql string, args *ArgsBuilder)

Build builds the "UPDATE" sql statement.

func (*UpdateBuilder) Comment added in v0.37.0 

func (b *UpdateBuilder) Comment(comment string) *UpdateBuilder

Comment set the comment, which will be appended to the end of the built SQL statement.

func (*UpdateBuilder) Exec 

func (b *UpdateBuilder) Exec() (sql.Result, error)

Exec builds the sql and executes it by *sql.DB.

func (*UpdateBuilder) ExecContext 

func (b *UpdateBuilder) ExecContext(ctx context.Context) (sql.Result, error)

ExecContext builds the sql and executes it by *sql.DB.

func (*UpdateBuilder) From 

func (b *UpdateBuilder) From(table string, alias ...string) *UpdateBuilder

From is equal to b.FromAlias(table, "").

func (*UpdateBuilder) FromAlias added in v0.27.0 

func (b *UpdateBuilder) FromAlias(table string, alias string) *UpdateBuilder

From appends the "FROM table AS alias" statement.

If alias is empty, use "FROM table" instead.

func (*UpdateBuilder) Join added in v0.47.0 

func (b *UpdateBuilder) Join(table, alias string, ons ...JoinOn) *UpdateBuilder

Join appends the "JOIN table ON on..." statement.

func (*UpdateBuilder) JoinFull 

func (b *UpdateBuilder) JoinFull(table, alias string, ons ...JoinOn) *UpdateBuilder

JoinFull appends the "FULL JOIN table ON on..." statement.

func (*UpdateBuilder) JoinFullOuter 

func (b *UpdateBuilder) JoinFullOuter(table, alias string, ons ...JoinOn) *UpdateBuilder

JoinFullOuter appends the "FULL OUTER JOIN table ON on..." statement.

func (*UpdateBuilder) JoinInner added in v0.47.0 

func (b *UpdateBuilder) JoinInner(table, alias string, ons ...JoinOn) *UpdateBuilder

JoinInner appends the "INNER JOIN table ON on..." statement.

func (*UpdateBuilder) JoinLeft 

func (b *UpdateBuilder) JoinLeft(table, alias string, ons ...JoinOn) *UpdateBuilder

JoinLeft appends the "LEFT JOIN table ON on..." statement.

func (*UpdateBuilder) JoinLeftOuter 

func (b *UpdateBuilder) JoinLeftOuter(table, alias string, ons ...JoinOn) *UpdateBuilder

JoinLeftOuter appends the "LEFT OUTER JOIN table ON on..." statement.

func (*UpdateBuilder) JoinRight 

func (b *UpdateBuilder) JoinRight(table, alias string, ons ...JoinOn) *UpdateBuilder

JoinRight appends the "RIGHT JOIN table ON on..." statement.

func (*UpdateBuilder) JoinRightOuter 

func (b *UpdateBuilder) JoinRightOuter(table, alias string, ons ...JoinOn) *UpdateBuilder

JoinRightOuter appends the "RIGHT OUTER JOIN table ON on..." statement.

func (*UpdateBuilder) Set 

func (b *UpdateBuilder) Set(updaters ...op.Updater) *UpdateBuilder

Set appends the "SET" statement to setters.

func (*UpdateBuilder) SetDB 

func (b *UpdateBuilder) SetDB(db *DB) *UpdateBuilder

SetDB sets the DB to db.

func (*UpdateBuilder) SetNamedArg 

func (b *UpdateBuilder) SetNamedArg(args ...sql.NamedArg) *UpdateBuilder

SetNamedArg is the same as Set, but uses the NamedArg as the Setter.

func (*UpdateBuilder) String 

func (b *UpdateBuilder) String() string

String is the same as b.Build(), except args.

func (*UpdateBuilder) Table 

func (b *UpdateBuilder) Table(table string) *UpdateBuilder

Table is equal to b.TableAlias(table, "")

func (*UpdateBuilder) TableAlias added in v0.27.0 

func (b *UpdateBuilder) TableAlias(table string, alias string) *UpdateBuilder

Table appends the "UPDATE table AS alias" statement.

If alias is empty, use "UPDATE table" instead.

func (*UpdateBuilder) Where 

func (b *UpdateBuilder) Where(andConditions ...op.Condition) *UpdateBuilder

Where appends the "WHERE" conditions.

func (*UpdateBuilder) WhereNamedArgs 

func (b *UpdateBuilder) WhereNamedArgs(andArgs ...sql.NamedArg) *UpdateBuilder

WhereNamedArgs is the same as Where, but uses the NamedArg as the EQUAL condition.

Source Files

View all Source files

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.