builder

package module
v9.0.0-...-c490369 Latest Latest
Warning

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

 Go to latest Published: Sep 18, 2023 License: MIT Imports: 13 Imported by: 0

README

builder is an expressive SQL builder and executor

If you are upgrading from an older version please read the Migrating Between Versions docs.

Installation

If using go modules.

go get -u github.com/Tooooommy/builder/v9

If you are not using go modules...

NOTE You should still be able to use this package if you are using go version >v1.10 but, you will need to drop the version from the package. import "github.com/Tooooommy/builder/v9 -> import "github.com/Tooooommy/builder"

go get -u github.com/Tooooommy/builder

Migrating Between Versions

Features

builder comes with many features but here are a few of the more notable ones

  • Query Builder
  • Parameter interpolation (e.g SELECT * FROM "items" WHERE "id" = ? -> SELECT * FROM "items" WHERE "id" = 1)
  • Built from the ground up with multiple dialects in mind
  • Insert, Multi Insert, Update, and Delete support
  • Scanning of rows to struct[s] or primitive value[s]

While builder may support the scanning of rows into structs it is not intended to be used as an ORM if you are looking for common ORM features like associations, or hooks I would recommend looking at some of the great ORM libraries such as:

Why?

We tried a few other sql builders but each was a thin wrapper around sql fragments that we found error prone. builder was built with the following goals in mind:

  • Make the generation of SQL easy and enjoyable
  • Create an expressive DSL that would find common errors with SQL at compile time.
  • Provide a DSL that accounts for the common SQL expressions, NOT every nuance for each database.
  • Provide developers the ability to:
    • Use SQL when desired
    • Easily scan results into primitive values and structs
    • Use the native sql.Db methods when desired

Docs

  • Dialect - Introduction to different dialects (mysql, postgres, sqlite3, sqlserver etc)
  • Expressions - Introduction to builder expressions and common examples.
  • Select Dataset - Docs and examples about creating and executing SELECT sql statements.
  • Insert Dataset - Docs and examples about creating and executing INSERT sql statements.
  • Update Dataset - Docs and examples about creating and executing UPDATE sql statements.
  • Delete Dataset - Docs and examples about creating and executing DELETE sql statements.
  • Prepared Statements - Docs about interpolation and prepared statements in builder.
  • Database - Docs and examples of using a Database to execute queries in builder
  • Working with time.Time - Docs on how to use alternate time locations.

Quick Examples

Select

See the select dataset docs for more in depth examples

sql, _, _ := builder.From("test").ToSQL()
fmt.Println(sql)

Output:

SELECT * FROM "test"

sql, _, _ := builder.From("test").Where(builder.Ex{
	"d": []string{"a", "b", "c"},
}).ToSQL()
fmt.Println(sql)

Output:

SELECT * FROM "test" WHERE ("d" IN ('a', 'b', 'c'))

Insert

See the insert dataset docs for more in depth examples

ds := builder.Insert("user").
	Cols("first_name", "last_name").
	Vals(
		builder.Vals{"Greg", "Farley"},
		builder.Vals{"Jimmy", "Stewart"},
		builder.Vals{"Jeff", "Jeffers"},
	)
insertSQL, args, _ := ds.ToSQL()
fmt.Println(insertSQL, args)

Output:

INSERT INTO "user" ("first_name", "last_name") VALUES ('Greg', 'Farley'), ('Jimmy', 'Stewart'), ('Jeff', 'Jeffers') []

ds := builder.Insert("user").Rows(
	builder.Record{"first_name": "Greg", "last_name": "Farley"},
	builder.Record{"first_name": "Jimmy", "last_name": "Stewart"},
	builder.Record{"first_name": "Jeff", "last_name": "Jeffers"},
)
insertSQL, args, _ := ds.ToSQL()
fmt.Println(insertSQL, args)

Output:

INSERT INTO "user" ("first_name", "last_name") VALUES ('Greg', 'Farley'), ('Jimmy', 'Stewart'), ('Jeff', 'Jeffers') []

type User struct {
	FirstName string `db:"first_name"`
	LastName  string `db:"last_name"`
}
ds := builder.Insert("user").Rows(
	User{FirstName: "Greg", LastName: "Farley"},
	User{FirstName: "Jimmy", LastName: "Stewart"},
	User{FirstName: "Jeff", LastName: "Jeffers"},
)
insertSQL, args, _ := ds.ToSQL()
fmt.Println(insertSQL, args)

Output:

INSERT INTO "user" ("first_name", "last_name") VALUES ('Greg', 'Farley'), ('Jimmy', 'Stewart'), ('Jeff', 'Jeffers') []

ds := builder.Insert("user").Prepared(true).
	FromQuery(builder.From("other_table"))
insertSQL, args, _ := ds.ToSQL()
fmt.Println(insertSQL, args)

Output:

INSERT INTO "user" SELECT * FROM "other_table" []

ds := builder.Insert("user").Prepared(true).
	Cols("first_name", "last_name").
	FromQuery(builder.From("other_table").Select("fn", "ln"))
insertSQL, args, _ := ds.ToSQL()
fmt.Println(insertSQL, args)

Output:

INSERT INTO "user" ("first_name", "last_name") SELECT "fn", "ln" FROM "other_table" []

Update

See the update dataset docs for more in depth examples

sql, args, _ := builder.Update("items").Set(
	builder.Record{"name": "Test", "address": "111 Test Addr"},
).ToSQL()
fmt.Println(sql, args)

Output:

UPDATE "items" SET "address"='111 Test Addr',"name"='Test' []

type item struct {
	Address string `db:"address"`
	Name    string `db:"name" builder:"skipupdate"`
}
sql, args, _ := builder.Update("items").Set(
	item{Name: "Test", Address: "111 Test Addr"},
).ToSQL()
fmt.Println(sql, args)

Output:

UPDATE "items" SET "address"='111 Test Addr' []

sql, _, _ := builder.Update("test").
	Set(builder.Record{"foo": "bar"}).
	Where(builder.Ex{
		"a": builder.Op{"gt": 10}
	}).ToSQL()
fmt.Println(sql)

Output:

UPDATE "test" SET "foo"='bar' WHERE ("a" > 10)

Delete

See the delete dataset docs for more in depth examples

ds := builder.Delete("items")

sql, args, _ := ds.ToSQL()
fmt.Println(sql, args)

sql, _, _ := builder.Delete("test").Where(builder.Ex{
		"c": nil
	}).ToSQL()
fmt.Println(sql)

Output:

DELETE FROM "test" WHERE ("c" IS NULL)

Contributions

I am always welcoming contributions of any type. Please open an issue or create a PR if you find an issue with any of the following.

  • An issue with Documentation
  • You found the documentation lacking in some way

If you have an issue with the package please include the following

  • The dialect you are using
  • A description of the problem
  • A short example of how to reproduce (if applicable)

Without those basics it can be difficult to reproduce your issue locally. You may be asked for more information but that is a good starting point.

New Features

New features and/or enhancements are great and I encourage you to either submit a PR or create an issue. In both cases include the following as the need/requirement may not be readily apparent.

  1. The use case
  2. A short example

If you are issuing a PR also include the following

  1. Tests - otherwise the PR will not be merged
  2. Documentation - otherwise the PR will not be merged
  3. Examples - [If applicable] see example_test.go for examples

If you find an issue you want to work on please comment on it letting other people know you are looking at it and I will assign the issue to you.

If want to work on an issue but dont know where to start just leave a comment and I'll be more than happy to point you in the right direction.

Running tests

The test suite requires a postgres, mysql and sqlserver databases. You can override the connection strings with the MYSQL_URI, PG_URI, SQLSERVER_URI environment variables*

go test -v -race ./...

You can also run the tests in a container using docker-compose.

MYSQL_VERSION=8 POSTGRES_VERSION=13.4 SQLSERVER_VERSION=2017-CU8-ubuntu GO_VERSION=latest docker-compose run builder

License

builder is released under the MIT License.

Documentation

Overview

builder an idiomatch SQL builder, and query package. 

  __ _  ___   __ _ _   _
 / _` |/ _ \ / _` | | | |
| (_| | (_) | (_| | |_| |
 \__, |\___/ \__, |\__,_|
 |___/          |_|

Please see https://github.com/Tooooommy/builder for an introduction to builder.

Index

Examples

Constants

View Source 
const (
	Wait       = exp.Wait
	NoWait     = exp.NoWait
	SkipLocked = exp.SkipLocked
)

Variables

View Source 
var (
	DefaultDialectOptions = sqlgen.DefaultDialectOptions
)
View Source 
var ErrBadFromArgument = errors.New(
	"unsupported DeleteDataset#From argument, a string or ddentifier expression is required",
)
View Source 
var ErrExecutorNotFoundError = errors.New(
	"unable to execute query did you use builder.Database#From to create the dataset",
)
View Source 
var ErrUnsupportedIntoType = errors.New("unsupported table type, a string or identifier expression is required")
View Source 
var ErrUnsupportedUpdateTableType = errors.New("unsupported table type, a string or identifier expression is required")

Functions

func AVG 

func AVG(col any) exp.SQLFunctionExpression

Creates a new AVG sql function 

AVG("a") -> AVG("a")
AVG(I("a")) -> AVG("a")
Example 
ds := builder.From("test").Select(builder.AVG("col"))
sql, args, _ := ds.ToSQL()
fmt.Println(sql, args)

sql, args, _ = ds.Prepared(true).ToSQL()
fmt.Println(sql, args)

Output:

SELECT AVG("col") FROM "test" []
SELECT AVG("col") FROM "test" []
Example (As) 
sql, _, _ := builder.From("test").Select(builder.AVG("a").As("a")).ToSQL()
fmt.Println(sql)

Output:

SELECT AVG("a") AS "a" FROM "test"
Example (HavingClause) 
ds := builder.
	From("test").
	Select(builder.AVG("a").As("avg")).
	GroupBy("a").
	Having(builder.AVG("a").Gt(10))

sql, args, _ := ds.ToSQL()
fmt.Println(sql, args)

sql, args, _ = ds.Prepared(true).ToSQL()
fmt.Println(sql, args)

Output:

SELECT AVG("a") AS "avg" FROM "test" GROUP BY "a" HAVING (AVG("a") > 10) []
SELECT AVG("a") AS "avg" FROM "test" GROUP BY "a" HAVING (AVG("a") > ?) [10]

func All 

func All(val any) exp.SQLFunctionExpression

Create a new ALL comparison

Example 
ds := builder.From("test").Where(builder.Ex{
	"id": builder.All(builder.From("other").Select("test_id")),
})
sql, args, _ := ds.ToSQL()
fmt.Println(sql, args)

sql, args, _ = ds.Prepared(true).ToSQL()
fmt.Println(sql, args)

Output:

SELECT * FROM "test" WHERE ("id" = ALL ((SELECT "test_id" FROM "other"))) []
SELECT * FROM "test" WHERE ("id" = ALL ((SELECT "test_id" FROM "other"))) []

func And 

func And(expressions ...exp.Expression) exp.ExpressionList

A list of expressions that should be ANDed together 

And(I("a").Eq(10), I("b").Eq(11)) //(("a" = 10) AND ("b" = 11))
Example 
ds := builder.From("test").Where(
	builder.And(
		builder.C("col").Gt(10),
		builder.C("col").Lt(20),
	),
)
sql, args, _ := ds.ToSQL()
fmt.Println(sql, args)

sql, args, _ = ds.Prepared(true).ToSQL()
fmt.Println(sql, args)

Output:

SELECT * FROM "test" WHERE (("col" > 10) AND ("col" < 20)) []
SELECT * FROM "test" WHERE (("col" > ?) AND ("col" < ?)) [10 20]
Example (WithExOr)

You can use ExOr inside of And expression lists. 

// by default expressions are anded together
ds := builder.From("test").Where(
	builder.C("col1").IsTrue(),
	builder.ExOr{
		"col2": builder.Op{"gt": 10},
		"col3": builder.Op{"lt": 20},
	},
)
sql, args, _ := ds.ToSQL()
fmt.Println(sql, args)

sql, args, _ = ds.Prepared(true).ToSQL()
fmt.Println(sql, args)

Output:

SELECT * FROM "test" WHERE (("col1" IS TRUE) AND (("col2" > 10) OR ("col3" < 20))) []
SELECT * FROM "test" WHERE (("col1" IS TRUE) AND (("col2" > ?) OR ("col3" < ?))) [10 20]
Example (WithOr)

You can use And with Or to create more complex queries 

ds := builder.From("test").Where(
	builder.And(
		builder.C("col1").IsTrue(),
		builder.Or(
			builder.C("col2").Gt(10),
			builder.C("col2").Lt(20),
		),
	),
)
sql, args, _ := ds.ToSQL()
fmt.Println(sql, args)

sql, args, _ = ds.Prepared(true).ToSQL()
fmt.Println(sql, args)

// by default expressions are anded together
ds = builder.From("test").Where(
	builder.C("col1").IsTrue(),
	builder.Or(
		builder.C("col2").Gt(10),
		builder.C("col2").Lt(20),
	),
)
sql, args, _ = ds.ToSQL()
fmt.Println(sql, args)

sql, args, _ = ds.Prepared(true).ToSQL()
fmt.Println(sql, args)

Output:

SELECT * FROM "test" WHERE (("col1" IS TRUE) AND (("col2" > 10) OR ("col2" < 20))) []
SELECT * FROM "test" WHERE (("col1" IS TRUE) AND (("col2" > ?) OR ("col2" < ?))) [10 20]
SELECT * FROM "test" WHERE (("col1" IS TRUE) AND (("col2" > 10) OR ("col2" < 20))) []
SELECT * FROM "test" WHERE (("col1" IS TRUE) AND (("col2" > ?) OR ("col2" < ?))) [10 20]

func Any 

func Any(val any) exp.SQLFunctionExpression

Create a new ANY comparison

Example 
ds := builder.From("test").Where(builder.Ex{
	"id": builder.Any(builder.From("other").Select("test_id")),
})
sql, args, _ := ds.ToSQL()
fmt.Println(sql, args)

sql, args, _ = ds.Prepared(true).ToSQL()
fmt.Println(sql, args)

Output:

SELECT * FROM "test" WHERE ("id" = ANY ((SELECT "test_id" FROM "other"))) []
SELECT * FROM "test" WHERE ("id" = ANY ((SELECT "test_id" FROM "other"))) []

func C 

func C(col string) exp.IdentifierExpression

Creates a new Column Identifier, the generated sql will use adapter specific quoting or '"' by default, this ensures case sensitivity and in certain databases allows for special characters, (e.g. "curr-table", "my table"). An Identifier can represent a one or a combination of schema, table, and/or column. 

C("column") -> "column" //A Column
C("column").Table("table") -> "table"."column" //A Column and table
C("column").Table("table").Schema("schema") //Schema table and column
C("*") //Also handles the * operator
Example 
sql, args, _ := builder.From("test").
	Select(builder.C("*")).
	ToSQL()
fmt.Println(sql, args)

sql, args, _ = builder.From("test").
	Select(builder.C("col1")).
	ToSQL()
fmt.Println(sql, args)

ds := builder.From("test").Where(
	builder.C("col1").Eq(10),
	builder.C("col2").In([]int64{1, 2, 3, 4}),
	builder.C("col3").Like(regexp.MustCompile("^[ab]")),
	builder.C("col4").IsNull(),
)

sql, args, _ = ds.ToSQL()
fmt.Println(sql, args)

sql, args, _ = ds.Prepared(true).ToSQL()
fmt.Println(sql, args)

Output:

SELECT * FROM "test" []
SELECT "col1" FROM "test" []
SELECT * FROM "test" WHERE (("col1" = 10) AND ("col2" IN (1, 2, 3, 4)) AND ("col3" ~ '^[ab]') AND ("col4" IS NULL)) []
SELECT * FROM "test" WHERE (("col1" = ?) AND ("col2" IN (?, ?, ?, ?)) AND ("col3" ~ ?) AND ("col4" IS NULL)) [10 1 2 3 4 ^[ab]]
Example (As) 
sql, _, _ := builder.From("test").Select(builder.C("a").As("as_a")).ToSQL()
fmt.Println(sql)

sql, _, _ = builder.From("test").Select(builder.C("a").As(builder.C("as_a"))).ToSQL()
fmt.Println(sql)

Output:

SELECT "a" AS "as_a" FROM "test"
SELECT "a" AS "as_a" FROM "test"
Example (BetweenComparisons) 
ds := builder.From("test").Where(
	builder.C("a").Between(builder.Range(1, 10)),
)
sql, args, _ := ds.ToSQL()
fmt.Println(sql, args)

sql, args, _ = ds.Prepared(true).ToSQL()
fmt.Println(sql, args)

ds = builder.From("test").Where(
	builder.C("a").NotBetween(builder.Range(1, 10)),
)
sql, args, _ = ds.ToSQL()
fmt.Println(sql, args)

sql, args, _ = ds.Prepared(true).ToSQL()
fmt.Println(sql, args)

Output:

SELECT * FROM "test" WHERE ("a" BETWEEN 1 AND 10) []
SELECT * FROM "test" WHERE ("a" BETWEEN ? AND ?) [1 10]
SELECT * FROM "test" WHERE ("a" NOT BETWEEN 1 AND 10) []
SELECT * FROM "test" WHERE ("a" NOT BETWEEN ? AND ?) [1 10]
Example (Cast) 
sql, _, _ := builder.From("test").
	Select(builder.C("json1").Cast("TEXT").As("json_text")).
	ToSQL()
fmt.Println(sql)

sql, _, _ = builder.From("test").Where(
	builder.C("json1").Cast("TEXT").Neq(
		builder.C("json2").Cast("TEXT"),
	),
).ToSQL()
fmt.Println(sql)

Output:

SELECT CAST("json1" AS TEXT) AS "json_text" FROM "test"
SELECT * FROM "test" WHERE (CAST("json1" AS TEXT) != CAST("json2" AS TEXT))
Example (Comparisons) 
// used from an identifier
sql, _, _ := builder.From("test").Where(builder.C("a").Eq(10)).ToSQL()
fmt.Println(sql)

sql, _, _ = builder.From("test").Where(builder.C("a").Neq(10)).ToSQL()
fmt.Println(sql)

sql, _, _ = builder.From("test").Where(builder.C("a").Gt(10)).ToSQL()
fmt.Println(sql)

sql, _, _ = builder.From("test").Where(builder.C("a").Gte(10)).ToSQL()
fmt.Println(sql)

sql, _, _ = builder.From("test").Where(builder.C("a").Lt(10)).ToSQL()
fmt.Println(sql)

sql, _, _ = builder.From("test").Where(builder.C("a").Lte(10)).ToSQL()
fmt.Println(sql)

Output:

SELECT * FROM "test" WHERE ("a" = 10)
SELECT * FROM "test" WHERE ("a" != 10)
SELECT * FROM "test" WHERE ("a" > 10)
SELECT * FROM "test" WHERE ("a" >= 10)
SELECT * FROM "test" WHERE ("a" < 10)
SELECT * FROM "test" WHERE ("a" <= 10)
Example (InOperators) 
// using identifiers
sql, _, _ := builder.From("test").Where(builder.C("a").In("a", "b", "c")).ToSQL()
fmt.Println(sql)
// with a slice
sql, _, _ = builder.From("test").Where(builder.C("a").In([]string{"a", "b", "c"})).ToSQL()
fmt.Println(sql)

sql, _, _ = builder.From("test").Where(builder.C("a").NotIn("a", "b", "c")).ToSQL()
fmt.Println(sql)
// with a slice
sql, _, _ = builder.From("test").Where(builder.C("a").NotIn([]string{"a", "b", "c"})).ToSQL()
fmt.Println(sql)

Output:

SELECT * FROM "test" WHERE ("a" IN ('a', 'b', 'c'))
SELECT * FROM "test" WHERE ("a" IN ('a', 'b', 'c'))
SELECT * FROM "test" WHERE ("a" NOT IN ('a', 'b', 'c'))
SELECT * FROM "test" WHERE ("a" NOT IN ('a', 'b', 'c'))
Example (IsComparisons) 
sql, args, _ := builder.From("test").Where(builder.C("a").Is(nil)).ToSQL()
fmt.Println(sql, args)

sql, args, _ = builder.From("test").Where(builder.C("a").Is(true)).ToSQL()
fmt.Println(sql, args)

sql, args, _ = builder.From("test").Where(builder.C("a").Is(false)).ToSQL()
fmt.Println(sql, args)

sql, args, _ = builder.From("test").Where(builder.C("a").IsNull()).ToSQL()
fmt.Println(sql, args)

sql, args, _ = builder.From("test").Where(builder.C("a").IsTrue()).ToSQL()
fmt.Println(sql, args)

sql, args, _ = builder.From("test").Where(builder.C("a").IsFalse()).ToSQL()
fmt.Println(sql, args)

sql, args, _ = builder.From("test").Where(builder.C("a").IsNot(nil)).ToSQL()
fmt.Println(sql, args)

sql, args, _ = builder.From("test").Where(builder.C("a").IsNot(true)).ToSQL()
fmt.Println(sql, args)

sql, args, _ = builder.From("test").Where(builder.C("a").IsNot(false)).ToSQL()
fmt.Println(sql, args)

sql, args, _ = builder.From("test").Where(builder.C("a").IsNotNull()).ToSQL()
fmt.Println(sql, args)

sql, args, _ = builder.From("test").Where(builder.C("a").IsNotTrue()).ToSQL()
fmt.Println(sql, args)

sql, args, _ = builder.From("test").Where(builder.C("a").IsNotFalse()).ToSQL()
fmt.Println(sql, args)

Output:

SELECT * FROM "test" WHERE ("a" IS NULL) []
SELECT * FROM "test" WHERE ("a" IS TRUE) []
SELECT * FROM "test" WHERE ("a" IS FALSE) []
SELECT * FROM "test" WHERE ("a" IS NULL) []
SELECT * FROM "test" WHERE ("a" IS TRUE) []
SELECT * FROM "test" WHERE ("a" IS FALSE) []
SELECT * FROM "test" WHERE ("a" IS NOT NULL) []
SELECT * FROM "test" WHERE ("a" IS NOT TRUE) []
SELECT * FROM "test" WHERE ("a" IS NOT FALSE) []
SELECT * FROM "test" WHERE ("a" IS NOT NULL) []
SELECT * FROM "test" WHERE ("a" IS NOT TRUE) []
SELECT * FROM "test" WHERE ("a" IS NOT FALSE) []
Example (LikeComparisons) 
// using identifiers
sql, _, _ := builder.From("test").Where(builder.C("a").Like("%a%")).ToSQL()
fmt.Println(sql)

sql, _, _ = builder.From("test").Where(builder.C("a").Like(regexp.MustCompile(`[ab]`))).ToSQL()
fmt.Println(sql)

sql, _, _ = builder.From("test").Where(builder.C("a").ILike("%a%")).ToSQL()
fmt.Println(sql)

sql, _, _ = builder.From("test").Where(builder.C("a").ILike(regexp.MustCompile("[ab]"))).ToSQL()
fmt.Println(sql)

sql, _, _ = builder.From("test").Where(builder.C("a").NotLike("%a%")).ToSQL()
fmt.Println(sql)

sql, _, _ = builder.From("test").Where(builder.C("a").NotLike(regexp.MustCompile("[ab]"))).ToSQL()
fmt.Println(sql)

sql, _, _ = builder.From("test").Where(builder.C("a").NotILike("%a%")).ToSQL()
fmt.Println(sql)

sql, _, _ = builder.From("test").Where(builder.C("a").NotILike(regexp.MustCompile(`[ab]`))).ToSQL()
fmt.Println(sql)

Output:

SELECT * FROM "test" WHERE ("a" LIKE '%a%')
SELECT * FROM "test" WHERE ("a" ~ '[ab]')
SELECT * FROM "test" WHERE ("a" ILIKE '%a%')
SELECT * FROM "test" WHERE ("a" ~* '[ab]')
SELECT * FROM "test" WHERE ("a" NOT LIKE '%a%')
SELECT * FROM "test" WHERE ("a" !~ '[ab]')
SELECT * FROM "test" WHERE ("a" NOT ILIKE '%a%')
SELECT * FROM "test" WHERE ("a" !~* '[ab]')
Example (Ordering) 
sql, args, _ := builder.From("test").Order(builder.C("a").Asc()).ToSQL()
fmt.Println(sql, args)

sql, args, _ = builder.From("test").Order(builder.C("a").Asc().NullsFirst()).ToSQL()
fmt.Println(sql, args)

sql, args, _ = builder.From("test").Order(builder.C("a").Asc().NullsLast()).ToSQL()
fmt.Println(sql, args)

sql, args, _ = builder.From("test").Order(builder.C("a").Desc()).ToSQL()
fmt.Println(sql, args)

sql, args, _ = builder.From("test").Order(builder.C("a").Desc().NullsFirst()).ToSQL()
fmt.Println(sql, args)

sql, args, _ = builder.From("test").Order(builder.C("a").Desc().NullsLast()).ToSQL()
fmt.Println(sql, args)

Output:

SELECT * FROM "test" ORDER BY "a" ASC []
SELECT * FROM "test" ORDER BY "a" ASC NULLS FIRST []
SELECT * FROM "test" ORDER BY "a" ASC NULLS LAST []
SELECT * FROM "test" ORDER BY "a" DESC []
SELECT * FROM "test" ORDER BY "a" DESC NULLS FIRST []
SELECT * FROM "test" ORDER BY "a" DESC NULLS LAST []

func COALESCE 

func COALESCE(vals ...any) exp.SQLFunctionExpression

Creates a new COALESCE sql function 

COALESCE(I("a"), "a") -> COALESCE("a", 'a')
COALESCE(I("a"), I("b"), nil) -> COALESCE("a", "b", NULL)
Example 
ds := builder.From("test").Select(
	builder.COALESCE(builder.C("a"), "a"),
	builder.COALESCE(builder.C("a"), builder.C("b"), nil),
)
sql, args, _ := ds.ToSQL()
fmt.Println(sql, args)

sql, args, _ = ds.Prepared(true).ToSQL()
fmt.Println(sql, args)

Output:

SELECT COALESCE("a", 'a'), COALESCE("a", "b", NULL) FROM "test" []
SELECT COALESCE("a", ?), COALESCE("a", "b", ?) FROM "test" [a <nil>]
Example (As) 
sql, _, _ := builder.From("test").Select(builder.COALESCE(builder.C("a"), "a").As("a")).ToSQL()
fmt.Println(sql)

Output:

SELECT COALESCE("a", 'a') AS "a" FROM "test"

func COUNT 

func COUNT(col any) exp.SQLFunctionExpression

Creates a new COUNT sql function 

COUNT("a") -> COUNT("a")
COUNT("*") -> COUNT("*")
COUNT(I("a")) -> COUNT("a")
Example 
ds := builder.From("test").Select(builder.COUNT("*"))
sql, args, _ := ds.ToSQL()
fmt.Println(sql, args)

sql, args, _ = ds.Prepared(true).ToSQL()
fmt.Println(sql, args)

Output:

SELECT COUNT(*) FROM "test" []
SELECT COUNT(*) FROM "test" []
Example (As) 
sql, _, _ := builder.From("test").Select(builder.COUNT("*").As("count")).ToSQL()
fmt.Println(sql)

Output:

SELECT COUNT(*) AS "count" FROM "test"
Example (HavingClause) 
ds := builder.
	From("test").
	Select(builder.COUNT("a").As("COUNT")).
	GroupBy("a").
	Having(builder.COUNT("a").Gt(10))

sql, args, _ := ds.ToSQL()
fmt.Println(sql, args)

sql, args, _ = ds.Prepared(true).ToSQL()
fmt.Println(sql, args)

Output:

SELECT COUNT("a") AS "COUNT" FROM "test" GROUP BY "a" HAVING (COUNT("a") > 10) []
SELECT COUNT("a") AS "COUNT" FROM "test" GROUP BY "a" HAVING (COUNT("a") > ?) [10]

func CUME_DIST 

func CUME_DIST() exp.SQLFunctionExpression

func Case 

func Case() exp.CaseExpression
Example (SearchElse) 
ds := builder.From("test").
	Select(
		builder.C("col"),
		builder.Case().
			When(builder.C("col").Gt(10), "Gt 10").
			When(builder.C("col").Gt(20), "Gt 20").
			Else("Bad Val").
			As("str_val"),
	)
sql, args, _ := ds.ToSQL()
fmt.Println(sql, args)

sql, args, _ = ds.Prepared(true).ToSQL()
fmt.Println(sql, args)

Output:

SELECT "col", CASE  WHEN ("col" > 10) THEN 'Gt 10' WHEN ("col" > 20) THEN 'Gt 20' ELSE 'Bad Val' END AS "str_val" FROM "test" []
SELECT "col", CASE  WHEN ("col" > ?) THEN ? WHEN ("col" > ?) THEN ? ELSE ? END AS "str_val" FROM "test" [10 Gt 10 20 Gt 20 Bad Val]
Example (Value) 
ds := builder.From("test").
	Select(
		builder.C("col"),
		builder.Case().
			Value(builder.C("str")).
			When("foo", "FOO").
			When("bar", "BAR").
			As("foo_bar_upper"),
	)
sql, args, _ := ds.ToSQL()
fmt.Println(sql, args)

sql, args, _ = ds.Prepared(true).ToSQL()
fmt.Println(sql, args)

Output:

SELECT "col", CASE "str" WHEN 'foo' THEN 'FOO' WHEN 'bar' THEN 'BAR' END AS "foo_bar_upper" FROM "test" []
SELECT "col", CASE "str" WHEN ? THEN ? WHEN ? THEN ? END AS "foo_bar_upper" FROM "test" [foo FOO bar BAR]
Example (ValueElse) 
ds := builder.From("test").
	Select(
		builder.C("col"),
		builder.Case().
			Value(builder.C("str")).
			When("foo", "FOO").
			When("bar", "BAR").
			Else("Baz").
			As("foo_bar_upper"),
	)
sql, args, _ := ds.ToSQL()
fmt.Println(sql, args)

sql, args, _ = ds.Prepared(true).ToSQL()
fmt.Println(sql, args)

Output:

SELECT "col", CASE "str" WHEN 'foo' THEN 'FOO' WHEN 'bar' THEN 'BAR' ELSE 'Baz' END AS "foo_bar_upper" FROM "test" []
SELECT "col", CASE "str" WHEN ? THEN ? WHEN ? THEN ? ELSE ? END AS "foo_bar_upper" FROM "test" [foo FOO bar BAR Baz]

func Cast 

func Cast(e exp.Expression, t string) exp.CastExpression

Creates a new Casted expression 

Cast(I("a"), "NUMERIC") -> CAST("a" AS NUMERIC)
Example 
sql, _, _ := builder.From("test").
	Select(builder.Cast(builder.C("json1"), "TEXT").As("json_text")).
	ToSQL()
fmt.Println(sql)

sql, _, _ = builder.From("test").Where(
	builder.Cast(builder.C("json1"), "TEXT").Neq(
		builder.Cast(builder.C("json2"), "TEXT"),
	),
).ToSQL()
fmt.Println(sql)

Output:

SELECT CAST("json1" AS TEXT) AS "json_text" FROM "test"
SELECT * FROM "test" WHERE (CAST("json1" AS TEXT) != CAST("json2" AS TEXT))

func DENSE_RANK 

func DENSE_RANK() exp.SQLFunctionExpression

func DISTINCT 

func DISTINCT(col any) exp.SQLFunctionExpression

Creates a new DISTINCT sql function 

DISTINCT("a") -> DISTINCT("a")
DISTINCT(I("a")) -> DISTINCT("a")
Example 
ds := builder.From("test").Select(builder.DISTINCT("col"))
sql, args, _ := ds.ToSQL()
fmt.Println(sql, args)

sql, args, _ = ds.Prepared(true).ToSQL()
fmt.Println(sql, args)

Output:

SELECT DISTINCT("col") FROM "test" []
SELECT DISTINCT("col") FROM "test" []
Example (As) 
sql, _, _ := builder.From("test").Select(builder.DISTINCT("a").As("distinct_a")).ToSQL()
fmt.Println(sql)

Output:

SELECT DISTINCT("a") AS "distinct_a" FROM "test"

func Default 

func Default() exp.LiteralExpression

Returns a literal for DEFAULT sql keyword

Example 
ds := builder.Insert("items")

sql, args, _ := ds.Rows(builder.Record{
	"name":    builder.Default(),
	"address": builder.Default(),
}).ToSQL()
fmt.Println(sql, args)

sql, args, _ = ds.Prepared(true).Rows(builder.Record{
	"name":    builder.Default(),
	"address": builder.Default(),
}).ToSQL()
fmt.Println(sql, args)

Output:

INSERT INTO "items" ("address", "name") VALUES (DEFAULT, DEFAULT) []
INSERT INTO "items" ("address", "name") VALUES (DEFAULT, DEFAULT) []

func DeregisterDialect 

func DeregisterDialect(name string)

func DoNothing 

func DoNothing() exp.ConflictExpression

Creates a conflict struct to be passed to InsertConflict to ignore constraint errors 

InsertConflict(DoNothing(),...) -> INSERT INTO ... ON CONFLICT DO NOTHING
Example 
ds := builder.Insert("items")

sql, args, _ := ds.Rows(builder.Record{
	"address": "111 Address",
	"name":    "bob",
}).OnConflict(builder.DoNothing()).ToSQL()
fmt.Println(sql, args)

sql, args, _ = ds.Prepared(true).Rows(builder.Record{
	"address": "111 Address",
	"name":    "bob",
}).OnConflict(builder.DoNothing()).ToSQL()
fmt.Println(sql, args)

Output:

INSERT INTO "items" ("address", "name") VALUES ('111 Address', 'bob') ON CONFLICT DO NOTHING []
INSERT INTO "items" ("address", "name") VALUES (?, ?) ON CONFLICT DO NOTHING [111 Address bob]

func DoUpdate 

func DoUpdate(target string, update any) exp.ConflictUpdateExpression

Creates a ConflictUpdate struct to be passed to InsertConflict Represents a ON CONFLICT DO UPDATE portion of an INSERT statement (ON DUPLICATE KEY UPDATE for mysql) 

InsertConflict(DoUpdate("target_column", update),...) ->
	INSERT INTO ... ON CONFLICT DO UPDATE SET a=b
InsertConflict(DoUpdate("target_column", update).Where(Ex{"a": 1},...) ->
	INSERT INTO ... ON CONFLICT DO UPDATE SET a=b WHERE a=1
Example 
ds := builder.Insert("items")

sql, args, _ := ds.
	Rows(builder.Record{"address": "111 Address"}).
	OnConflict(builder.DoUpdate("address", builder.C("address").Set(builder.I("excluded.address")))).
	ToSQL()
fmt.Println(sql, args)

sql, args, _ = ds.Prepared(true).
	Rows(builder.Record{"address": "111 Address"}).
	OnConflict(builder.DoUpdate("address", builder.C("address").Set(builder.I("excluded.address")))).
	ToSQL()
fmt.Println(sql, args)

Output:

INSERT INTO "items" ("address") VALUES ('111 Address') ON CONFLICT (address) DO UPDATE SET "address"="excluded"."address" []
INSERT INTO "items" ("address") VALUES (?) ON CONFLICT (address) DO UPDATE SET "address"="excluded"."address" [111 Address]
Example (Where) 
ds := builder.Insert("items")

sql, args, _ := ds.
	Rows(builder.Record{"address": "111 Address"}).
	OnConflict(builder.DoUpdate(
		"address",
		builder.C("address").Set(builder.I("excluded.address"))).Where(builder.I("items.updated").IsNull()),
	).
	ToSQL()
fmt.Println(sql, args)

sql, args, _ = ds.Prepared(true).
	Rows(builder.Record{"address": "111 Address"}).
	OnConflict(builder.DoUpdate(
		"address",
		builder.C("address").Set(builder.I("excluded.address"))).Where(builder.I("items.updated").IsNull()),
	).
	ToSQL()
fmt.Println(sql, args)

Output:

INSERT INTO "items" ("address") VALUES ('111 Address') ON CONFLICT (address) DO UPDATE SET "address"="excluded"."address" WHERE ("items"."updated" IS NULL) []
INSERT INTO "items" ("address") VALUES (?) ON CONFLICT (address) DO UPDATE SET "address"="excluded"."address" WHERE ("items"."updated" IS NULL) [111 Address]

func FIRST 

func FIRST(col any) exp.SQLFunctionExpression

Creates a new FIRST sql function 

FIRST("a") -> FIRST("a")
FIRST(I("a")) -> FIRST("a")
Example 
ds := builder.From("test").Select(builder.FIRST("col"))
sql, args, _ := ds.ToSQL()
fmt.Println(sql, args)

sql, args, _ = ds.Prepared(true).ToSQL()
fmt.Println(sql, args)

Output:

SELECT FIRST("col") FROM "test" []
SELECT FIRST("col") FROM "test" []
Example (As) 
sql, _, _ := builder.From("test").Select(builder.FIRST("a").As("a")).ToSQL()
fmt.Println(sql)

Output:

SELECT FIRST("a") AS "a" FROM "test"

func FIRST_VALUE 

func FIRST_VALUE(val any) exp.SQLFunctionExpression

func Func 

func Func(name string, args ...any) exp.SQLFunctionExpression

Creates a new SQLFunctionExpression with the given name and arguments

Example

This example shows how to create custom SQL Functions 

stragg := func(expression exp.Expression, delimiter string) exp.SQLFunctionExpression {
	return builder.Func("str_agg", expression, builder.L(delimiter))
}
sql, _, _ := builder.From("test").Select(stragg(builder.C("col"), "|")).ToSQL()
fmt.Println(sql)

Output:

SELECT str_agg("col", |) FROM "test"

func I 

func I(ident string) exp.IdentifierExpression

Creates a new Identifier, the generated sql will use adapter specific quoting or '"' by default, this ensures case sensitivity and in certain databases allows for special characters, (e.g. "curr-table", "my table").

The identifier will be split by '.'

Table and Column example 

I("table.column") -> "table"."column" //A Column and table

Schema table and column 

I("schema.table.column") -> "schema"."table"."column"

Table with star 

I("table.*") -> "table".*
Example 
ds := builder.From("test").
	Select(
		builder.I("my_schema.table.col1"),
		builder.I("table.col2"),
		builder.I("col3"),
	)

sql, args, _ := ds.ToSQL()
fmt.Println(sql, args)

sql, args, _ = ds.Prepared(true).ToSQL()
fmt.Println(sql, args)

ds = builder.From("test").Select(builder.I("test.*"))

sql, args, _ = ds.ToSQL()
fmt.Println(sql, args)

sql, args, _ = ds.Prepared(true).ToSQL()
fmt.Println(sql, args)

Output:

SELECT "my_schema"."table"."col1", "table"."col2", "col3" FROM "test" []
SELECT "my_schema"."table"."col1", "table"."col2", "col3" FROM "test" []
SELECT "test".* FROM "test" []
SELECT "test".* FROM "test" []

func L 

func L(sql string, args ...any) exp.LiteralExpression

Creates a new SQL literal with the provided arguments. 

L("a = 1") -> a = 1

You can also you placeholders. All placeholders within a Literal are represented by '?' 

L("a = ?", "b") -> a = 'b'

Literals can also contain placeholders for other expressions 

L("(? AND ?) OR (?)", I("a").Eq(1), I("b").Eq("b"), I("c").In([]string{"a", "b", "c"}))
Example 
ds := builder.From("test").Where(
	// literal with no args
	builder.L(`"col"::TEXT = ""other_col"::text`),
	// literal with args they will be interpolated into the sql by default
	builder.L("col IN (?, ?, ?)", "a", "b", "c"),
)

sql, args, _ := ds.ToSQL()
fmt.Println(sql, args)

sql, args, _ = ds.Prepared(true).ToSQL()
fmt.Println(sql, args)

Output:

SELECT * FROM "test" WHERE ("col"::TEXT = ""other_col"::text AND col IN ('a', 'b', 'c')) []
SELECT * FROM "test" WHERE ("col"::TEXT = ""other_col"::text AND col IN (?, ?, ?)) [a b c]
Example (As) 
sql, _, _ := builder.From("test").Select(builder.L("json_col->>'totalAmount'").As("total_amount")).ToSQL()
fmt.Println(sql)

Output:

SELECT json_col->>'totalAmount' AS "total_amount" FROM "test"
Example (BetweenComparisons) 
ds := builder.From("test").Where(
	builder.L("(a + b)").Between(builder.Range(1, 10)),
)
sql, args, _ := ds.ToSQL()
fmt.Println(sql, args)

sql, args, _ = ds.Prepared(true).ToSQL()
fmt.Println(sql, args)

ds = builder.From("test").Where(
	builder.L("(a + b)").NotBetween(builder.Range(1, 10)),
)
sql, args, _ = ds.ToSQL()
fmt.Println(sql, args)

sql, args, _ = ds.Prepared(true).ToSQL()
fmt.Println(sql, args)

Output:

SELECT * FROM "test" WHERE ((a + b) BETWEEN 1 AND 10) []
SELECT * FROM "test" WHERE ((a + b) BETWEEN ? AND ?) [1 10]
SELECT * FROM "test" WHERE ((a + b) NOT BETWEEN 1 AND 10) []
SELECT * FROM "test" WHERE ((a + b) NOT BETWEEN ? AND ?) [1 10]
Example (Comparisons) 
// used from a literal expression
sql, _, _ := builder.From("test").Where(builder.L("(a + b)").Eq(10)).ToSQL()
fmt.Println(sql)

sql, _, _ = builder.From("test").Where(builder.L("(a + b)").Neq(10)).ToSQL()
fmt.Println(sql)

sql, _, _ = builder.From("test").Where(builder.L("(a + b)").Gt(10)).ToSQL()
fmt.Println(sql)

sql, _, _ = builder.From("test").Where(builder.L("(a + b)").Gte(10)).ToSQL()
fmt.Println(sql)

sql, _, _ = builder.From("test").Where(builder.L("(a + b)").Lt(10)).ToSQL()
fmt.Println(sql)

sql, _, _ = builder.From("test").Where(builder.L("(a + b)").Lte(10)).ToSQL()
fmt.Println(sql)

Output:

SELECT * FROM "test" WHERE ((a + b) = 10)
SELECT * FROM "test" WHERE ((a + b) != 10)
SELECT * FROM "test" WHERE ((a + b) > 10)
SELECT * FROM "test" WHERE ((a + b) >= 10)
SELECT * FROM "test" WHERE ((a + b) < 10)
SELECT * FROM "test" WHERE ((a + b) <= 10)
Example (InOperators) 
// using identifiers
sql, _, _ := builder.From("test").Where(builder.L("json_col->>'val'").In("a", "b", "c")).ToSQL()
fmt.Println(sql)
// with a slice
sql, _, _ = builder.From("test").Where(builder.L("json_col->>'val'").In([]string{"a", "b", "c"})).ToSQL()
fmt.Println(sql)

sql, _, _ = builder.From("test").Where(builder.L("json_col->>'val'").NotIn("a", "b", "c")).ToSQL()
fmt.Println(sql)
// with a slice
sql, _, _ = builder.From("test").Where(builder.L("json_col->>'val'").NotIn([]string{"a", "b", "c"})).ToSQL()
fmt.Println(sql)

Output:

SELECT * FROM "test" WHERE (json_col->>'val' IN ('a', 'b', 'c'))
SELECT * FROM "test" WHERE (json_col->>'val' IN ('a', 'b', 'c'))
SELECT * FROM "test" WHERE (json_col->>'val' NOT IN ('a', 'b', 'c'))
SELECT * FROM "test" WHERE (json_col->>'val' NOT IN ('a', 'b', 'c'))
Example (IsComparisons) 
sql, args, _ := builder.From("test").Where(builder.L("a").Is(nil)).ToSQL()
fmt.Println(sql, args)

sql, args, _ = builder.From("test").Where(builder.L("a").Is(true)).ToSQL()
fmt.Println(sql, args)

sql, args, _ = builder.From("test").Where(builder.L("a").Is(false)).ToSQL()
fmt.Println(sql, args)

sql, args, _ = builder.From("test").Where(builder.L("a").IsNull()).ToSQL()
fmt.Println(sql, args)

sql, args, _ = builder.From("test").Where(builder.L("a").IsTrue()).ToSQL()
fmt.Println(sql, args)

sql, args, _ = builder.From("test").Where(builder.L("a").IsFalse()).ToSQL()
fmt.Println(sql, args)

sql, args, _ = builder.From("test").Where(builder.L("a").IsNot(nil)).ToSQL()
fmt.Println(sql, args)

sql, args, _ = builder.From("test").Where(builder.L("a").IsNot(true)).ToSQL()
fmt.Println(sql, args)

sql, args, _ = builder.From("test").Where(builder.L("a").IsNot(false)).ToSQL()
fmt.Println(sql, args)

sql, args, _ = builder.From("test").Where(builder.L("a").IsNotNull()).ToSQL()
fmt.Println(sql, args)

sql, args, _ = builder.From("test").Where(builder.L("a").IsNotTrue()).ToSQL()
fmt.Println(sql, args)

sql, args, _ = builder.From("test").Where(builder.L("a").IsNotFalse()).ToSQL()
fmt.Println(sql, args)

Output:

SELECT * FROM "test" WHERE (a IS NULL) []
SELECT * FROM "test" WHERE (a IS TRUE) []
SELECT * FROM "test" WHERE (a IS FALSE) []
SELECT * FROM "test" WHERE (a IS NULL) []
SELECT * FROM "test" WHERE (a IS TRUE) []
SELECT * FROM "test" WHERE (a IS FALSE) []
SELECT * FROM "test" WHERE (a IS NOT NULL) []
SELECT * FROM "test" WHERE (a IS NOT TRUE) []
SELECT * FROM "test" WHERE (a IS NOT FALSE) []
SELECT * FROM "test" WHERE (a IS NOT NULL) []
SELECT * FROM "test" WHERE (a IS NOT TRUE) []
SELECT * FROM "test" WHERE (a IS NOT FALSE) []
Example (LikeComparisons) 
// using identifiers
sql, _, _ := builder.From("test").Where(builder.L("(a::text || 'bar')").Like("%a%")).ToSQL()
fmt.Println(sql)

sql, _, _ = builder.From("test").Where(
	builder.L("(a::text || 'bar')").Like(regexp.MustCompile("[ab]")),
).ToSQL()
fmt.Println(sql)

sql, _, _ = builder.From("test").Where(builder.L("(a::text || 'bar')").ILike("%a%")).ToSQL()
fmt.Println(sql)

sql, _, _ = builder.From("test").Where(
	builder.L("(a::text || 'bar')").ILike(regexp.MustCompile("[ab]")),
).ToSQL()
fmt.Println(sql)

sql, _, _ = builder.From("test").Where(builder.L("(a::text || 'bar')").NotLike("%a%")).ToSQL()
fmt.Println(sql)

sql, _, _ = builder.From("test").Where(
	builder.L("(a::text || 'bar')").NotLike(regexp.MustCompile("[ab]")),
).ToSQL()
fmt.Println(sql)

sql, _, _ = builder.From("test").Where(builder.L("(a::text || 'bar')").NotILike("%a%")).ToSQL()
fmt.Println(sql)

sql, _, _ = builder.From("test").Where(
	builder.L("(a::text || 'bar')").NotILike(regexp.MustCompile("[ab]")),
).ToSQL()
fmt.Println(sql)

Output:

SELECT * FROM "test" WHERE ((a::text || 'bar') LIKE '%a%')
SELECT * FROM "test" WHERE ((a::text || 'bar') ~ '[ab]')
SELECT * FROM "test" WHERE ((a::text || 'bar') ILIKE '%a%')
SELECT * FROM "test" WHERE ((a::text || 'bar') ~* '[ab]')
SELECT * FROM "test" WHERE ((a::text || 'bar') NOT LIKE '%a%')
SELECT * FROM "test" WHERE ((a::text || 'bar') !~ '[ab]')
SELECT * FROM "test" WHERE ((a::text || 'bar') NOT ILIKE '%a%')
SELECT * FROM "test" WHERE ((a::text || 'bar') !~* '[ab]')
Example (WithArgs) 
ds := builder.From("test").Where(
	builder.L(
		"(? AND ?) OR ?",
		builder.C("a").Eq(1),
		builder.C("b").Eq("b"),
		builder.C("c").In([]string{"a", "b", "c"}),
	),
)

sql, args, _ := ds.ToSQL()
fmt.Println(sql, args)

sql, args, _ = ds.Prepared(true).ToSQL()
fmt.Println(sql, args)

Output:

SELECT * FROM "test" WHERE (("a" = 1) AND ("b" = 'b')) OR ("c" IN ('a', 'b', 'c')) []
SELECT * FROM "test" WHERE (("a" = ?) AND ("b" = ?)) OR ("c" IN (?, ?, ?)) [1 b a b c]

func LAST 

func LAST(col any) exp.SQLFunctionExpression

Creates a new LAST sql function 

LAST("a") -> LAST("a")
LAST(I("a")) -> LAST("a")
Example 
ds := builder.From("test").Select(builder.LAST("col"))
sql, args, _ := ds.ToSQL()
fmt.Println(sql, args)

sql, args, _ = ds.Prepared(true).ToSQL()
fmt.Println(sql, args)

Output:

SELECT LAST("col") FROM "test" []
SELECT LAST("col") FROM "test" []
Example (As) 
sql, _, _ := builder.From("test").Select(builder.LAST("a").As("a")).ToSQL()
fmt.Println(sql)

Output:

SELECT LAST("a") AS "a" FROM "test"

func LAST_VALUE 

func LAST_VALUE(val any) exp.SQLFunctionExpression

func Lateral 

func Lateral(table exp.AppendableExpression) exp.LateralExpression
Example 
maxEntry := builder.From("entry").
	Select(builder.MAX("int").As("max_int")).
	Where(builder.Ex{"time": builder.Op{"lt": builder.I("e.time")}}).
	As("max_entry")

maxID := builder.From("entry").
	Select("id").
	Where(builder.Ex{"int": builder.I("max_entry.max_int")}).
	As("max_id")

ds := builder.
	Select("e.id", "max_entry.max_int", "max_id.id").
	From(
		builder.T("entry").As("e"),
		builder.Lateral(maxEntry),
		builder.Lateral(maxID),
	)
query, args, _ := ds.ToSQL()
fmt.Println(query, args)

query, args, _ = ds.Prepared(true).ToSQL()
fmt.Println(query, args)

Output:

SELECT "e"."id", "max_entry"."max_int", "max_id"."id" FROM "entry" AS "e", LATERAL (SELECT MAX("int") AS "max_int" FROM "entry" WHERE ("time" < "e"."time")) AS "max_entry", LATERAL (SELECT "id" FROM "entry" WHERE ("int" = "max_entry"."max_int")) AS "max_id" []
SELECT "e"."id", "max_entry"."max_int", "max_id"."id" FROM "entry" AS "e", LATERAL (SELECT MAX("int") AS "max_int" FROM "entry" WHERE ("time" < "e"."time")) AS "max_entry", LATERAL (SELECT "id" FROM "entry" WHERE ("int" = "max_entry"."max_int")) AS "max_id" []
Example (Join) 
maxEntry := builder.From("entry").
	Select(builder.MAX("int").As("max_int")).
	Where(builder.Ex{"time": builder.Op{"lt": builder.I("e.time")}}).
	As("max_entry")

maxID := builder.From("entry").
	Select("id").
	Where(builder.Ex{"int": builder.I("max_entry.max_int")}).
	As("max_id")

ds := builder.
	Select("e.id", "max_entry.max_int", "max_id.id").
	From(builder.T("entry").As("e")).
	Join(builder.Lateral(maxEntry), builder.On(builder.V(true))).
	Join(builder.Lateral(maxID), builder.On(builder.V(true)))
query, args, _ := ds.ToSQL()
fmt.Println(query, args)

query, args, _ = ds.Prepared(true).ToSQL()
fmt.Println(query, args)

Output:

SELECT "e"."id", "max_entry"."max_int", "max_id"."id" FROM "entry" AS "e" INNER JOIN LATERAL (SELECT MAX("int") AS "max_int" FROM "entry" WHERE ("time" < "e"."time")) AS "max_entry" ON TRUE INNER JOIN LATERAL (SELECT "id" FROM "entry" WHERE ("int" = "max_entry"."max_int")) AS "max_id" ON TRUE []
SELECT "e"."id", "max_entry"."max_int", "max_id"."id" FROM "entry" AS "e" INNER JOIN LATERAL (SELECT MAX("int") AS "max_int" FROM "entry" WHERE ("time" < "e"."time")) AS "max_entry" ON ? INNER JOIN LATERAL (SELECT "id" FROM "entry" WHERE ("int" = "max_entry"."max_int")) AS "max_id" ON ? [true true]

func Literal 

func Literal(sql string, args ...any) exp.LiteralExpression

Alias for builder.L

func MAX 

func MAX(col any) exp.SQLFunctionExpression

Creates a new MAX sql function 

MAX("a") -> MAX("a")
MAX(I("a")) -> MAX("a")
Example 
ds := builder.From("test").Select(builder.MAX("col"))
sql, args, _ := ds.ToSQL()
fmt.Println(sql, args)

sql, args, _ = ds.Prepared(true).ToSQL()
fmt.Println(sql, args)

Output:

SELECT MAX("col") FROM "test" []
SELECT MAX("col") FROM "test" []
Example (As) 
sql, _, _ := builder.From("test").Select(builder.MAX("a").As("a")).ToSQL()
fmt.Println(sql)

Output:

SELECT MAX("a") AS "a" FROM "test"
Example (HavingClause) 
ds := builder.
	From("test").
	Select(builder.MAX("a").As("MAX")).
	GroupBy("a").
	Having(builder.MAX("a").Gt(10))

sql, args, _ := ds.ToSQL()
fmt.Println(sql, args)

sql, args, _ = ds.Prepared(true).ToSQL()
fmt.Println(sql, args)

Output:

SELECT MAX("a") AS "MAX" FROM "test" GROUP BY "a" HAVING (MAX("a") > 10) []
SELECT MAX("a") AS "MAX" FROM "test" GROUP BY "a" HAVING (MAX("a") > ?) [10]

func MIN 

func MIN(col any) exp.SQLFunctionExpression

Creates a new MIN sql function 

MIN("a") -> MIN("a")
MIN(I("a")) -> MIN("a")
Example 
ds := builder.From("test").Select(builder.MIN("col"))
sql, args, _ := ds.ToSQL()
fmt.Println(sql, args)

sql, args, _ = ds.Prepared(true).ToSQL()
fmt.Println(sql, args)

Output:

SELECT MIN("col") FROM "test" []
SELECT MIN("col") FROM "test" []
Example (As) 
sql, _, _ := builder.From("test").Select(builder.MIN("a").As("a")).ToSQL()
fmt.Println(sql)

Output:

SELECT MIN("a") AS "a" FROM "test"
Example (HavingClause) 
ds := builder.
	From("test").
	Select(builder.MIN("a").As("MIN")).
	GroupBy("a").
	Having(builder.MIN("a").Gt(10))

sql, args, _ := ds.ToSQL()
fmt.Println(sql, args)

sql, args, _ = ds.Prepared(true).ToSQL()
fmt.Println(sql, args)

Output:

SELECT MIN("a") AS "MIN" FROM "test" GROUP BY "a" HAVING (MIN("a") > 10) []
SELECT MIN("a") AS "MIN" FROM "test" GROUP BY "a" HAVING (MIN("a") > ?) [10]

func NTH_VALUE 

func NTH_VALUE(val any, nth int) exp.SQLFunctionExpression

func NTILE 

func NTILE(n int) exp.SQLFunctionExpression

func On 

func On(expressions ...exp.Expression) exp.JoinCondition

Creates a new ON clause to be used within a join 

ds.Join(builder.T("my_table"), builder.On(
   builder.I("my_table.fkey").Eq(builder.I("other_table.id")),
))
Example 
ds := builder.From("test").Join(
	builder.T("my_table"),
	builder.On(builder.I("my_table.fkey").Eq(builder.I("other_table.id"))),
)

sql, args, _ := ds.ToSQL()
fmt.Println(sql, args)

sql, args, _ = ds.Prepared(true).ToSQL()
fmt.Println(sql, args)

Output:

SELECT * FROM "test" INNER JOIN "my_table" ON ("my_table"."fkey" = "other_table"."id") []
SELECT * FROM "test" INNER JOIN "my_table" ON ("my_table"."fkey" = "other_table"."id") []
Example (WithEx) 
ds := builder.From("test").Join(
	builder.T("my_table"),
	builder.On(builder.Ex{"my_table.fkey": builder.I("other_table.id")}),
)

sql, args, _ := ds.ToSQL()
fmt.Println(sql, args)

sql, args, _ = ds.Prepared(true).ToSQL()
fmt.Println(sql, args)

Output:

SELECT * FROM "test" INNER JOIN "my_table" ON ("my_table"."fkey" = "other_table"."id") []
SELECT * FROM "test" INNER JOIN "my_table" ON ("my_table"."fkey" = "other_table"."id") []

func Or 

func Or(expressions ...exp.Expression) exp.ExpressionList

A list of expressions that should be ORed together 

Or(I("a").Eq(10), I("b").Eq(11)) //(("a" = 10) OR ("b" = 11))
Example 
ds := builder.From("test").Where(
	builder.Or(
		builder.C("col").Eq(10),
		builder.C("col").Eq(20),
	),
)
sql, args, _ := ds.ToSQL()
fmt.Println(sql, args)

sql, args, _ = ds.Prepared(true).ToSQL()
fmt.Println(sql, args)

Output:

SELECT * FROM "test" WHERE (("col" = 10) OR ("col" = 20)) []
SELECT * FROM "test" WHERE (("col" = ?) OR ("col" = ?)) [10 20]
Example (WithAnd) 
ds := builder.From("items").Where(
	builder.Or(
		builder.C("a").Gt(10),
		builder.And(
			builder.C("b").Eq(100),
			builder.C("c").Neq("test"),
		),
	),
)
sql, args, _ := ds.ToSQL()
fmt.Println(sql, args)

sql, args, _ = ds.Prepared(true).ToSQL()
fmt.Println(sql, args)

Output:

SELECT * FROM "items" WHERE (("a" > 10) OR (("b" = 100) AND ("c" != 'test'))) []
SELECT * FROM "items" WHERE (("a" > ?) OR (("b" = ?) AND ("c" != ?))) [10 100 test]
Example (WithExMap) 
ds := builder.From("test").Where(
	builder.Or(
		// Ex will be anded together
		builder.Ex{
			"col1": 1,
			"col2": true,
		},
		builder.Ex{
			"col3": nil,
			"col4": "foo",
		},
	),
)
sql, args, _ := ds.ToSQL()
fmt.Println(sql, args)

sql, args, _ = ds.Prepared(true).ToSQL()
fmt.Println(sql, args)

Output:

SELECT * FROM "test" WHERE ((("col1" = 1) AND ("col2" IS TRUE)) OR (("col3" IS NULL) AND ("col4" = 'foo'))) []
SELECT * FROM "test" WHERE ((("col1" = ?) AND ("col2" IS TRUE)) OR (("col3" IS NULL) AND ("col4" = ?))) [1 foo]

func PERCENT_RANK 

func PERCENT_RANK() exp.SQLFunctionExpression

func RANK 

func RANK() exp.SQLFunctionExpression

func ROW_NUMBER 

func ROW_NUMBER() exp.SQLFunctionExpression

func Range 

func Range(start, end any) exp.RangeVal

Creates a new Range to be used with a Between expression 

exp.C("col").Between(exp.Range(1, 10))
Example (Identifiers) 
ds := builder.From("test").Where(
	builder.C("col1").Between(builder.Range(builder.C("col2"), builder.C("col3"))),
)
sql, args, _ := ds.ToSQL()
fmt.Println(sql, args)

sql, args, _ = ds.Prepared(true).ToSQL()
fmt.Println(sql, args)

ds = builder.From("test").Where(
	builder.C("col1").NotBetween(builder.Range(builder.C("col2"), builder.C("col3"))),
)
sql, args, _ = ds.ToSQL()
fmt.Println(sql, args)

sql, args, _ = ds.Prepared(true).ToSQL()
fmt.Println(sql, args)

Output:

SELECT * FROM "test" WHERE ("col1" BETWEEN "col2" AND "col3") []
SELECT * FROM "test" WHERE ("col1" BETWEEN "col2" AND "col3") []
SELECT * FROM "test" WHERE ("col1" NOT BETWEEN "col2" AND "col3") []
SELECT * FROM "test" WHERE ("col1" NOT BETWEEN "col2" AND "col3") []
Example (Numbers) 
ds := builder.From("test").Where(
	builder.C("col").Between(builder.Range(1, 10)),
)
sql, args, _ := ds.ToSQL()
fmt.Println(sql, args)

sql, args, _ = ds.Prepared(true).ToSQL()
fmt.Println(sql, args)

ds = builder.From("test").Where(
	builder.C("col").NotBetween(builder.Range(1, 10)),
)
sql, args, _ = ds.ToSQL()
fmt.Println(sql, args)

sql, args, _ = ds.Prepared(true).ToSQL()
fmt.Println(sql, args)

Output:

SELECT * FROM "test" WHERE ("col" BETWEEN 1 AND 10) []
SELECT * FROM "test" WHERE ("col" BETWEEN ? AND ?) [1 10]
SELECT * FROM "test" WHERE ("col" NOT BETWEEN 1 AND 10) []
SELECT * FROM "test" WHERE ("col" NOT BETWEEN ? AND ?) [1 10]
Example (Strings) 
ds := builder.From("test").Where(
	builder.C("col").Between(builder.Range("a", "z")),
)
sql, args, _ := ds.ToSQL()
fmt.Println(sql, args)

sql, args, _ = ds.Prepared(true).ToSQL()
fmt.Println(sql, args)

ds = builder.From("test").Where(
	builder.C("col").NotBetween(builder.Range("a", "z")),
)
sql, args, _ = ds.ToSQL()
fmt.Println(sql, args)

sql, args, _ = ds.Prepared(true).ToSQL()
fmt.Println(sql, args)

Output:

SELECT * FROM "test" WHERE ("col" BETWEEN 'a' AND 'z') []
SELECT * FROM "test" WHERE ("col" BETWEEN ? AND ?) [a z]
SELECT * FROM "test" WHERE ("col" NOT BETWEEN 'a' AND 'z') []
SELECT * FROM "test" WHERE ("col" NOT BETWEEN ? AND ?) [a z]

func RegisterDialect 

func RegisterDialect(name string, do *SQLDialectOptions)
Example 
opts := builder.DefaultDialectOptions()
opts.QuoteRune = '`'
builder.RegisterDialect("custom-dialect", opts)

dialect := builder.Dialect("custom-dialect")

ds := dialect.From("test")

sql, args, _ := ds.ToSQL()
fmt.Println(sql, args)

Output:

SELECT * FROM `test` []

func S 

func S(schema string) exp.IdentifierExpression

Creates a new Schema Identifier, the generated sql will use adapter specific quoting or '"' by default, this ensures case sensitivity and in certain databases allows for special characters, (e.g. "curr-schema", "my schema"). 

S("schema") -> "schema" //A Schema
S("schema").Table("table") -> "schema"."table" //A Schema and table
S("schema").Table("table").Col("col") //Schema table and column
S("schema").Table("table").Col("*") //Schema table and all columns
Example 
s := builder.S("test_schema")
t := s.Table("test")
sql, args, _ := builder.
	From(t).
	Select(
		t.Col("col1"),
		t.Col("col2"),
		t.Col("col3"),
	).
	ToSQL()
fmt.Println(sql, args)

Output:

SELECT "test_schema"."test"."col1", "test_schema"."test"."col2", "test_schema"."test"."col3" FROM "test_schema"."test" []

func SUM 

func SUM(col any) exp.SQLFunctionExpression

Creates a new SUM sql function 

SUM("a") -> SUM("a")
SUM(I("a")) -> SUM("a")
Example 
ds := builder.From("test").Select(builder.SUM("col"))
sql, args, _ := ds.ToSQL()
fmt.Println(sql, args)

sql, args, _ = ds.Prepared(true).ToSQL()
fmt.Println(sql, args)

Output:

SELECT SUM("col") FROM "test" []
SELECT SUM("col") FROM "test" []
Example (As) 
sql, _, _ := builder.From("test").Select(builder.SUM("a").As("a")).ToSQL()
fmt.Println(sql)

Output:

SELECT SUM("a") AS "a" FROM "test"
Example (HavingClause) 
ds := builder.
	From("test").
	Select(builder.SUM("a").As("SUM")).
	GroupBy("a").
	Having(builder.SUM("a").Gt(10))

sql, args, _ := ds.ToSQL()
fmt.Println(sql, args)

sql, args, _ = ds.Prepared(true).ToSQL()
fmt.Println(sql, args)

Output:

SELECT SUM("a") AS "SUM" FROM "test" GROUP BY "a" HAVING (SUM("a") > 10) []
SELECT SUM("a") AS "SUM" FROM "test" GROUP BY "a" HAVING (SUM("a") > ?) [10]

func SetColumnRenameFunction 

func SetColumnRenameFunction(renameFunc func(string) string)

Set the column rename function. This is used for struct fields that do not have a db tag to specify the column name By default all struct fields that do not have a db tag will be converted lowercase

func SetDefaultPrepared 

func SetDefaultPrepared(prepared bool)

SetDefaultPrepared controls the default Prepared state of all datasets. If set to true, any new dataset will use prepared queries by default.

func SetIgnoreUntaggedFields 

func SetIgnoreUntaggedFields(ignore bool)

Set the behavior when encountering struct fields that do not have a db tag. By default this is false; if set to true any field without a db tag will not be targeted by Select or Scan operations.

func SetTimeLocation 

func SetTimeLocation(loc *time.Location)

Set the location to use when interpolating time.Time instances. See https://golang.org/pkg/time/#LoadLocation NOTE: This has no effect when using prepared statements.

Example 
loc, err := time.LoadLocation("Asia/Shanghai")
if err != nil {
	panic(err)
}

created, err := time.Parse(time.RFC3339, "2019-10-01T15:01:00Z")
if err != nil {
	panic(err)
}

// use original time with tz info
builder.SetTimeLocation(loc)
ds := builder.Insert("test").Rows(builder.Record{
	"address": "111 Address",
	"name":    "Bob Yukon",
	"created": created,
})
sql, _, _ := ds.ToSQL()
fmt.Println(sql)

// convert time to UTC
builder.SetTimeLocation(time.UTC)
sql, _, _ = ds.ToSQL()
fmt.Println(sql)

Output:

INSERT INTO "test" ("address", "created", "name") VALUES ('111 Address', '2019-10-01T23:01:00+08:00', 'Bob Yukon')
INSERT INTO "test" ("address", "created", "name") VALUES ('111 Address', '2019-10-01T15:01:00Z', 'Bob Yukon')

func Star 

func Star() exp.LiteralExpression

Creates a literal *

Example 
ds := builder.From("test").Select(builder.Star())

sql, args, _ := ds.ToSQL()
fmt.Println(sql, args)

sql, args, _ = ds.Prepared(true).ToSQL()
fmt.Println(sql, args)

Output:

SELECT * FROM "test" []
SELECT * FROM "test" []

func T 

func T(table string) exp.IdentifierExpression

Creates a new Table Identifier, the generated sql will use adapter specific quoting or '"' by default, this ensures case sensitivity and in certain databases allows for special characters, (e.g. "curr-table", "my table"). 

T("table") -> "table" //A Column
T("table").Col("col") -> "table"."column" //A Column and table
T("table").Schema("schema").Col("col) -> "schema"."table"."column"  //Schema table and column
T("table").Schema("schema").Col("*") -> "schema"."table".*  //Also handles the * operator
Example 
t := builder.T("test")
sql, args, _ := builder.
	From(t).
	Select(
		t.Col("col1"),
		t.Col("col2"),
		t.Col("col3"),
	).
	ToSQL()
fmt.Println(sql, args)

Output:

SELECT "test"."col1", "test"."col2", "test"."col3" FROM "test" []

func Using 

func Using(columns ...any) exp.JoinCondition

Creates a new USING clause to be used within a join 

ds.Join(builder.T("my_table"), builder.Using("fkey"))
Example 
ds := builder.From("test").Join(
	builder.T("my_table"),
	builder.Using("fkey"),
)

sql, args, _ := ds.ToSQL()
fmt.Println(sql, args)

sql, args, _ = ds.Prepared(true).ToSQL()
fmt.Println(sql, args)

Output:

SELECT * FROM "test" INNER JOIN "my_table" USING ("fkey") []
SELECT * FROM "test" INNER JOIN "my_table" USING ("fkey") []
Example (WithIdentifier) 
ds := builder.From("test").Join(
	builder.T("my_table"),
	builder.Using(builder.C("fkey")),
)

sql, args, _ := ds.ToSQL()
fmt.Println(sql, args)

sql, args, _ = ds.Prepared(true).ToSQL()
fmt.Println(sql, args)

Output:

SELECT * FROM "test" INNER JOIN "my_table" USING ("fkey") []
SELECT * FROM "test" INNER JOIN "my_table" USING ("fkey") []

func V 

func V(val any) exp.LiteralExpression

Create a new SQL value ( alias for builder.L("?", val) ). The prrimary use case for this would be in selects. See examples.

Example 
ds := builder.From("user").Select(
	builder.V(true).As("is_verified"),
	builder.V(1.2).As("version"),
	"first_name",
	"last_name",
)

sql, args, _ := ds.ToSQL()
fmt.Println(sql, args)

ds = builder.From("user").Where(builder.V(1).Neq(1))
sql, args, _ = ds.ToSQL()
fmt.Println(sql, args)

Output:

SELECT TRUE AS "is_verified", 1.2 AS "version", "first_name", "last_name" FROM "user" []
SELECT * FROM "user" WHERE (1 != 1) []
Example (Prepared) 
ds := builder.From("user").Select(
	builder.V(true).As("is_verified"),
	builder.V(1.2).As("version"),
	"first_name",
	"last_name",
)

sql, args, _ := ds.Prepared(true).ToSQL()
fmt.Println(sql, args)

ds = builder.From("user").Where(builder.V(1).Neq(1))

sql, args, _ = ds.Prepared(true).ToSQL()
fmt.Println(sql, args)

Output:

SELECT ? AS "is_verified", ? AS "version", "first_name", "last_name" FROM "user" [true 1.2]
SELECT * FROM "user" WHERE (? != ?) [1 1]

func W 

func W(ws ...string) exp.WindowExpression

Create a new WINDOW clause 

W() -> ()
W().PartitionBy("a") -> (PARTITION BY "a")
W().PartitionBy("a").OrderBy("b") -> (PARTITION BY "a" ORDER BY "b")
W().PartitionBy("a").OrderBy("b").Inherit("w1") -> ("w1" PARTITION BY "a" ORDER BY "b")
W().PartitionBy("a").OrderBy(I("b").Desc()).Inherit("w1") -> ("w1" PARTITION BY "a" ORDER BY "b" DESC)
W("w") -> "w" AS ()
W("w", "w1") -> "w" AS ("w1")
W("w").Inherit("w1") -> "w" AS ("w1")
W("w").PartitionBy("a") -> "w" AS (PARTITION BY "a")
W("w", "w1").PartitionBy("a") -> "w" AS ("w1" PARTITION BY "a")
W("w", "w1").PartitionBy("a").OrderBy("b") -> "w" AS ("w1" PARTITION BY "a" ORDER BY "b")
Example 
ds := builder.From("test").
	Select(builder.ROW_NUMBER().Over(builder.W().PartitionBy("a").OrderBy(builder.I("b").Asc())))
query, args, _ := ds.ToSQL()
fmt.Println(query, args)

ds = builder.From("test").
	Select(builder.ROW_NUMBER().OverName(builder.I("w"))).
	Window(builder.W("w").PartitionBy("a").OrderBy(builder.I("b").Asc()))
query, args, _ = ds.ToSQL()
fmt.Println(query, args)

ds = builder.From("test").
	Select(builder.ROW_NUMBER().OverName(builder.I("w1"))).
	Window(
		builder.W("w1").PartitionBy("a"),
		builder.W("w").Inherit("w1").OrderBy(builder.I("b").Asc()),
	)
query, args, _ = ds.ToSQL()
fmt.Println(query, args)

ds = builder.From("test").
	Select(builder.ROW_NUMBER().Over(builder.W().Inherit("w").OrderBy("b"))).
	Window(builder.W("w").PartitionBy("a"))
query, args, _ = ds.ToSQL()
fmt.Println(query, args)

Output:

SELECT ROW_NUMBER() OVER (PARTITION BY "a" ORDER BY "b" ASC) FROM "test" []
SELECT ROW_NUMBER() OVER "w" FROM "test" WINDOW "w" AS (PARTITION BY "a" ORDER BY "b" ASC) []
SELECT ROW_NUMBER() OVER "w1" FROM "test" WINDOW "w1" AS (PARTITION BY "a"), "w" AS ("w1" ORDER BY "b" ASC) []
SELECT ROW_NUMBER() OVER ("w" ORDER BY "b") FROM "test" WINDOW "w" AS (PARTITION BY "a") []

Types

type Database 

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

This struct is the wrapper for a Db. The struct delegates most calls to either an Exec instance or to the Db passed into the constructor.

func New 

func New(dialect string, db sqlx.SqlConn) *Database

func (*Database) Delete 

func (d *Database) Delete(table any) *DeleteDataset

func (*Database) Dialect 

func (d *Database) Dialect() string

returns this databases dialect

Example 
db := getDB()

fmt.Println(db.Dialect())

Output:

mysql

func (*Database) Exec 

func (d *Database) Exec(query string, args ...any) (sql.Result, error)
Example 
db := getDB()

_, err := db.Exec(`DROP TABLE user_role`)
if err != nil {
	fmt.Println("Error occurred while dropping tables", err.Error())
}
_, err = db.Exec(`DROP TABLE builder_user`)
if err != nil {
	fmt.Println("Error occurred while dropping tables", err.Error())
}
fmt.Println("Dropped tables user_role and builder_user")

Output:

Dropped tables user_role and builder_user

func (*Database) ExecCtx 

func (d *Database) ExecCtx(ctx context.Context, query string, args ...any) (sql.Result, error)

func (*Database) From 

func (d *Database) From(from ...any) *SelectDataset

Creates a new Dataset that uses the correct adapter and supports queries. 

var ids []uint32
if err := db.From("items").Where(builder.I("id").Gt(10)).Pluck("id", &ids); err != nil {
    panic(err.Error())
}
fmt.Printf("%+v", ids)

from...: Sources for you dataset, could be table names (strings), a builder.Literal or another builder.Dataset

Example 
db := getDB()
var names []string

if err := db.From("builder_user").Select("first_name").QueryRows(&names); err != nil {
	fmt.Println(err.Error())
} else {
	fmt.Println("Fetched Users names:", names)
}

Output:

Fetched Users names: [Bob Sally Vinita John]

func (*Database) Insert 

func (d *Database) Insert(table any) *InsertDataset

func (*Database) Logger 

func (d *Database) Logger(logger logx.Logger)

Sets the logger for to use when logging queries

func (*Database) Prepare 

func (d *Database) Prepare(query string) (sqlx.StmtSession, error)

func (*Database) PrepareCtx 

func (d *Database) PrepareCtx(ctx context.Context, query string) (sqlx.StmtSession, error)

func (*Database) QueryRow 

func (d *Database) QueryRow(v any, query string, args ...any) error

func (*Database) QueryRowCtx 

func (d *Database) QueryRowCtx(ctx context.Context, v any, query string, args ...any) error

func (*Database) QueryRowPartial 

func (d *Database) QueryRowPartial(v any, query string, args ...any) error

func (*Database) QueryRowPartialCtx 

func (d *Database) QueryRowPartialCtx(ctx context.Context, v any, query string, args ...any) error

func (*Database) QueryRows 

func (d *Database) QueryRows(v any, query string, args ...any) error

func (*Database) QueryRowsCtx 

func (d *Database) QueryRowsCtx(ctx context.Context, v any, query string, args ...any) error

func (*Database) QueryRowsPartial 

func (d *Database) QueryRowsPartial(v any, query string, args ...any) error

func (*Database) QueryRowsPartialCtx 

func (d *Database) QueryRowsPartialCtx(ctx context.Context, v any, query string, args ...any) error

func (*Database) Select 

func (d *Database) Select(cols ...any) *SelectDataset

func (*Database) Trace 

func (d *Database) Trace(ctx context.Context, op, sqlString string, args ...any)

Logs a given operation with the specified sql and arguments

func (*Database) Transact 

func (d *Database) Transact(fn func(td *TxDatabase) error) (err error)

Transact starts a new transaction and executes it in function method

func (*Database) TransactCtx 

func (d *Database) TransactCtx(ctx context.Context, fn func(ctx context.Context, td *TxDatabase) error) (err error)

func (*Database) Truncate 

func (d *Database) Truncate(table ...any) *TruncateDataset

func (*Database) Update 

func (d *Database) Update(table any) *UpdateDataset

type DeleteDataset 

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

func Delete 

func Delete(table any) *DeleteDataset
Example 
ds := builder.Delete("items")

sql, args, _ := ds.ToSQL()
fmt.Println(sql, args)

Output:

DELETE FROM "items" []

func (*DeleteDataset) AppendSQL 

func (dd *DeleteDataset) AppendSQL(b sb.SQLBuilder)

Appends this Dataset's DELETE statement to the SQLBuilder This is used internally when using deletes in CTEs

func (*DeleteDataset) ClearLimit 

func (dd *DeleteDataset) ClearLimit() *DeleteDataset

Removes the LIMIT clause.

Example 
// Using mysql dialect because it supports limit on delete
ds := builder.Dialect("mysql").Delete("test").Limit(10)
sql, _, _ := ds.ClearLimit().ToSQL()
fmt.Println(sql)

Output:

DELETE `test` FROM `test`

func (*DeleteDataset) ClearOrder 

func (dd *DeleteDataset) ClearOrder() *DeleteDataset

Removes the ORDER BY clause. See examples.

Example 
ds := builder.Delete("test").Order(builder.C("a").Asc())
sql, _, _ := ds.ClearOrder().ToSQL()
fmt.Println(sql)

Output:

DELETE FROM "test"

func (*DeleteDataset) ClearWhere 

func (dd *DeleteDataset) ClearWhere() *DeleteDataset

Removes the WHERE clause. See examples.

Example 
ds := builder.Delete("test").Where(
	builder.Or(
		builder.C("a").Gt(10),
		builder.And(
			builder.C("b").Lt(10),
			builder.C("c").IsNull(),
		),
	),
)
sql, _, _ := ds.ClearWhere().ToSQL()
fmt.Println(sql)

Output:

DELETE FROM "test"

func (*DeleteDataset) Clone 

func (dd *DeleteDataset) Clone() exp.Expression

Clones the dataset

func (*DeleteDataset) Dialect 

func (dd *DeleteDataset) Dialect() SQLDialect

Returns the current SQLDialect on the dataset

func (*DeleteDataset) Error 

func (dd *DeleteDataset) Error() error

Get any error that has been set or nil if no error has been set.

func (*DeleteDataset) Exec 

func (dd *DeleteDataset) Exec() (sql.Result, error)

Creates an QueryExecutor to execute the query. 

db.Delete("test").Exec()

See Dataset#ToUpdateSQL for arguments

func (*DeleteDataset) ExecCtx 

func (dd *DeleteDataset) ExecCtx(ctx context.Context) (sql.Result, error)

func (*DeleteDataset) Expression 

func (dd *DeleteDataset) Expression() exp.Expression

func (*DeleteDataset) From 

func (dd *DeleteDataset) From(table any) *DeleteDataset

Adds a FROM clause. This return a new dataset with the original sources replaced. See examples. You can pass in the following. 

string: Will automatically be turned into an ddentifier
Dataset: Will be added as a sub select. If the Dataset is not aliased it will automatically be aliased
LiteralExpression: (See Literal) Will use the literal SQL

func (*DeleteDataset) GetAs 

func (dd *DeleteDataset) GetAs() exp.IdentifierExpression

func (*DeleteDataset) GetClauses 

func (dd *DeleteDataset) GetClauses() exp.DeleteClauses

Returns the current clauses on the dataset.

func (*DeleteDataset) IsPrepared 

func (dd *DeleteDataset) IsPrepared() bool

Returns true if Prepared(true) has been called on this dataset

func (*DeleteDataset) Limit 

func (dd *DeleteDataset) Limit(limit uint) *DeleteDataset

Adds a LIMIT clause. If the LIMIT is currently set it replaces it. See examples.

Example 
ds := builder.Dialect("mysql").Delete("test").Limit(10)
sql, _, _ := ds.ToSQL()
fmt.Println(sql)

Output:

DELETE FROM `test` LIMIT 10

func (*DeleteDataset) LimitAll 

func (dd *DeleteDataset) LimitAll() *DeleteDataset

Adds a LIMIT ALL clause. If the LIMIT is currently set it replaces it. See examples.

Example 
// Using mysql dialect because it supports limit on delete
ds := builder.Dialect("mysql").Delete("test").LimitAll()
sql, _, _ := ds.ToSQL()
fmt.Println(sql)

Output:

DELETE FROM `test` LIMIT ALL

func (*DeleteDataset) Order 

func (dd *DeleteDataset) Order(order ...exp.OrderedExpression) *DeleteDataset

Adds a ORDER clause. If the ORDER is currently set it replaces it. See examples.

Example 
// use mysql dialect because it supports order by on deletes
ds := builder.Dialect("mysql").Delete("test").Order(builder.C("a").Asc())
sql, _, _ := ds.ToSQL()
fmt.Println(sql)

Output:

DELETE FROM `test` ORDER BY `a` ASC

func (*DeleteDataset) OrderAppend 

func (dd *DeleteDataset) OrderAppend(order ...exp.OrderedExpression) *DeleteDataset

Adds a more columns to the current ORDER BY clause. If no order has be previously specified it is the same as calling Order. See examples.

Example 
// use mysql dialect because it supports order by on deletes
ds := builder.Dialect("mysql").Delete("test").Order(builder.C("a").Asc())
sql, _, _ := ds.OrderAppend(builder.C("b").Desc().NullsLast()).ToSQL()
fmt.Println(sql)

Output:

DELETE FROM `test` ORDER BY `a` ASC, `b` DESC NULLS LAST

func (*DeleteDataset) OrderPrepend 

func (dd *DeleteDataset) OrderPrepend(order ...exp.OrderedExpression) *DeleteDataset

Adds a more columns to the beginning of the current ORDER BY clause. If no order has be previously specified it is the same as calling Order. See examples.

Example 
// use mysql dialect because it supports order by on deletes
ds := builder.Dialect("mysql").Delete("test").Order(builder.C("a").Asc())
sql, _, _ := ds.OrderPrepend(builder.C("b").Desc().NullsLast()).ToSQL()
fmt.Println(sql)

Output:

DELETE FROM `test` ORDER BY `b` DESC NULLS LAST, `a` ASC

func (*DeleteDataset) Prepared 

func (dd *DeleteDataset) Prepared(prepared bool) *DeleteDataset

Set the parameter interpolation behavior. See examples

prepared: If true the dataset WILL NOT interpolate the parameters.

Example 
sql, args, _ := builder.Delete("items").Prepared(true).ToSQL()
fmt.Println(sql, args)

sql, args, _ = builder.Delete("items").
	Prepared(true).
	Where(builder.Ex{"id": builder.Op{"gt": 10}}).
	ToSQL()
fmt.Println(sql, args)

Output:

DELETE FROM "items" []
DELETE FROM "items" WHERE ("id" > ?) [10]

func (*DeleteDataset) QueryRow 

func (dd *DeleteDataset) QueryRow(v any) error

func (*DeleteDataset) QueryRowCtx 

func (dd *DeleteDataset) QueryRowCtx(ctx context.Context, v any) error

func (*DeleteDataset) QueryRowPartial 

func (dd *DeleteDataset) QueryRowPartial(v any) error

func (*DeleteDataset) QueryRowPartialCtx 

func (dd *DeleteDataset) QueryRowPartialCtx(ctx context.Context, v any) error

func (*DeleteDataset) QueryRows 

func (dd *DeleteDataset) QueryRows(v any) error

func (*DeleteDataset) QueryRowsCtx 

func (dd *DeleteDataset) QueryRowsCtx(ctx context.Context, v any) error

func (*DeleteDataset) QueryRowsPartial 

func (dd *DeleteDataset) QueryRowsPartial(v any) error

func (*DeleteDataset) QueryRowsPartialCtx 

func (dd *DeleteDataset) QueryRowsPartialCtx(ctx context.Context, v any) error

func (*DeleteDataset) Returning 

func (dd *DeleteDataset) Returning(returning ...any) *DeleteDataset

Adds a RETURNING clause to the dataset if the adapter supports it.

Example 
ds := builder.Delete("items")
sql, args, _ := ds.Returning("id").ToSQL()
fmt.Println(sql, args)

sql, args, _ = ds.Returning("id").Where(builder.C("id").IsNotNull()).ToSQL()
fmt.Println(sql, args)

Output:

DELETE FROM "items" RETURNING "id" []
DELETE FROM "items" WHERE ("id" IS NOT NULL) RETURNING "id" []

func (*DeleteDataset) ReturnsColumns 

func (dd *DeleteDataset) ReturnsColumns() bool

func (*DeleteDataset) SetDialect 

func (dd *DeleteDataset) SetDialect(dialect SQLDialect) *DeleteDataset

Set the dialect for this dataset.

func (*DeleteDataset) SetError 

func (dd *DeleteDataset) SetError(err error) *DeleteDataset

Set an error on the dataset if one has not already been set. This error will be returned by a future call to Error or as part of ToSQL. This can be used by end users to record errors while building up queries without having to track those separately.

func (*DeleteDataset) ToSQL 

func (dd *DeleteDataset) ToSQL() (sql string, params []any, err error)

Generates a DELETE sql statement, if Prepared has been called with true then the parameters will not be interpolated. See examples.

Errors:

  • There is an error generating the SQL
Example 
sql, args, _ := builder.Delete("items").ToSQL()
fmt.Println(sql, args)

sql, args, _ = builder.Delete("items").
	Where(builder.Ex{"id": builder.Op{"gt": 10}}).
	ToSQL()
fmt.Println(sql, args)

Output:

DELETE FROM "items" []
DELETE FROM "items" WHERE ("id" > 10) []

func (*DeleteDataset) Where 

func (dd *DeleteDataset) Where(expressions ...exp.Expression) *DeleteDataset

Adds a WHERE clause. See examples.

Example 
// By default everything is anded together
sql, _, _ := builder.Delete("test").Where(builder.Ex{
	"a": builder.Op{"gt": 10},
	"b": builder.Op{"lt": 10},
	"c": nil,
	"d": []string{"a", "b", "c"},
}).ToSQL()
fmt.Println(sql)
// You can use ExOr to get ORed expressions together
sql, _, _ = builder.Delete("test").Where(builder.ExOr{
	"a": builder.Op{"gt": 10},
	"b": builder.Op{"lt": 10},
	"c": nil,
	"d": []string{"a", "b", "c"},
}).ToSQL()
fmt.Println(sql)
// You can use Or with Ex to Or multiple Ex maps together
sql, _, _ = builder.Delete("test").Where(
	builder.Or(
		builder.Ex{
			"a": builder.Op{"gt": 10},
			"b": builder.Op{"lt": 10},
		},
		builder.Ex{
			"c": nil,
			"d": []string{"a", "b", "c"},
		},
	),
).ToSQL()
fmt.Println(sql)
// By default everything is anded together
sql, _, _ = builder.Delete("test").Where(
	builder.C("a").Gt(10),
	builder.C("b").Lt(10),
	builder.C("c").IsNull(),
	builder.C("d").In("a", "b", "c"),
).ToSQL()
fmt.Println(sql)
// You can use a combination of Ors and Ands
sql, _, _ = builder.Delete("test").Where(
	builder.Or(
		builder.C("a").Gt(10),
		builder.And(
			builder.C("b").Lt(10),
			builder.C("c").IsNull(),
		),
	),
).ToSQL()
fmt.Println(sql)

Output:

DELETE FROM "test" WHERE (("a" > 10) AND ("b" < 10) AND ("c" IS NULL) AND ("d" IN ('a', 'b', 'c')))
DELETE FROM "test" WHERE (("a" > 10) OR ("b" < 10) OR ("c" IS NULL) OR ("d" IN ('a', 'b', 'c')))
DELETE FROM "test" WHERE ((("a" > 10) AND ("b" < 10)) OR (("c" IS NULL) AND ("d" IN ('a', 'b', 'c'))))
DELETE FROM "test" WHERE (("a" > 10) AND ("b" < 10) AND ("c" IS NULL) AND ("d" IN ('a', 'b', 'c')))
DELETE FROM "test" WHERE (("a" > 10) OR (("b" < 10) AND ("c" IS NULL)))
Example (Prepared) 
// By default everything is anded together
sql, args, _ := builder.Delete("test").Prepared(true).Where(builder.Ex{
	"a": builder.Op{"gt": 10},
	"b": builder.Op{"lt": 10},
	"c": nil,
	"d": []string{"a", "b", "c"},
}).ToSQL()
fmt.Println(sql, args)
// You can use ExOr to get ORed expressions together
sql, args, _ = builder.Delete("test").Prepared(true).Where(builder.ExOr{
	"a": builder.Op{"gt": 10},
	"b": builder.Op{"lt": 10},
	"c": nil,
	"d": []string{"a", "b", "c"},
}).ToSQL()
fmt.Println(sql, args)
// You can use Or with Ex to Or multiple Ex maps together
sql, args, _ = builder.Delete("test").Prepared(true).Where(
	builder.Or(
		builder.Ex{
			"a": builder.Op{"gt": 10},
			"b": builder.Op{"lt": 10},
		},
		builder.Ex{
			"c": nil,
			"d": []string{"a", "b", "c"},
		},
	),
).ToSQL()
fmt.Println(sql, args)
// By default everything is anded together
sql, args, _ = builder.Delete("test").Prepared(true).Where(
	builder.C("a").Gt(10),
	builder.C("b").Lt(10),
	builder.C("c").IsNull(),
	builder.C("d").In("a", "b", "c"),
).ToSQL()
fmt.Println(sql, args)
// You can use a combination of Ors and Ands
sql, args, _ = builder.Delete("test").Prepared(true).Where(
	builder.Or(
		builder.C("a").Gt(10),
		builder.And(
			builder.C("b").Lt(10),
			builder.C("c").IsNull(),
		),
	),
).ToSQL()
fmt.Println(sql, args)

Output:

DELETE FROM "test" WHERE (("a" > ?) AND ("b" < ?) AND ("c" IS NULL) AND ("d" IN (?, ?, ?))) [10 10 a b c]
DELETE FROM "test" WHERE (("a" > ?) OR ("b" < ?) OR ("c" IS NULL) OR ("d" IN (?, ?, ?))) [10 10 a b c]
DELETE FROM "test" WHERE ((("a" > ?) AND ("b" < ?)) OR (("c" IS NULL) AND ("d" IN (?, ?, ?)))) [10 10 a b c]
DELETE FROM "test" WHERE (("a" > ?) AND ("b" < ?) AND ("c" IS NULL) AND ("d" IN (?, ?, ?))) [10 10 a b c]
DELETE FROM "test" WHERE (("a" > ?) OR (("b" < ?) AND ("c" IS NULL))) [10 10]

func (*DeleteDataset) With 

func (dd *DeleteDataset) With(name string, subquery exp.Expression) *DeleteDataset

Creates a WITH clause for a common table expression (CTE).

The name will be available to SELECT from in the associated query; and can optionally contain a list of column names "name(col1, col2, col3)".

The name will refer to the results of the specified subquery.

Example 
sql, _, _ := builder.Delete("test").
	With("check_vals(val)", builder.From().Select(builder.L("123"))).
	Where(builder.C("val").Eq(builder.From("check_vals").Select("val"))).
	ToSQL()
fmt.Println(sql)

Output:

WITH check_vals(val) AS (SELECT 123) DELETE FROM "test" WHERE ("val" IN (SELECT "val" FROM "check_vals"))

func (*DeleteDataset) WithDialect 

func (dd *DeleteDataset) WithDialect(dl string) *DeleteDataset

Sets the adapter used to serialize values and create the SQL statement

func (*DeleteDataset) WithRecursive 

func (dd *DeleteDataset) WithRecursive(name string, subquery exp.Expression) *DeleteDataset

Creates a WITH RECURSIVE clause for a common table expression (CTE)

The name will be available to SELECT from in the associated query; and must contain a list of column names "name(col1, col2, col3)" for a recursive clause.

The name will refer to the results of the specified subquery. The subquery for a recursive query will always end with a UNION or UNION ALL with a clause that refers to the CTE by name.

Example 
sql, _, _ := builder.Delete("nums").
	WithRecursive("nums(x)",
		builder.From().Select(builder.L("1")).
			UnionAll(builder.From("nums").
				Select(builder.L("x+1")).Where(builder.C("x").Lt(5)))).
	ToSQL()
fmt.Println(sql)

Output:

WITH RECURSIVE nums(x) AS (SELECT 1 UNION ALL (SELECT x+1 FROM "nums" WHERE ("x" < 5))) DELETE FROM "nums"

type DialectWrapper 

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

func Dialect 

func Dialect(dialect string) DialectWrapper

Creates a new DialectWrapper to create builder.Datasets or builder.Databases with the specified dialect.

Example (DatasetMysql)

Creating a mysql dataset. Be sure to import the mysql adapter. 

// import _ "github.com/Tooooommy/builder/v9/dialect/mysql"

d := builder.Dialect("mysql")
ds := d.From("test").Where(builder.Ex{
	"foo": "bar",
	"baz": []int64{1, 2, 3},
}).Limit(10)

sql, args, _ := ds.ToSQL()
fmt.Println(sql, args)

sql, args, _ = ds.Prepared(true).ToSQL()
fmt.Println(sql, args)

Output:

SELECT * FROM `test` WHERE ((`baz` IN (1, 2, 3)) AND (`foo` = 'bar')) LIMIT 10 []
SELECT * FROM `test` WHERE ((`baz` IN (?, ?, ?)) AND (`foo` = ?)) LIMIT ? [1 2 3 bar 10]
Example (DatasetPostgres)

Creating a mysql dataset. Be sure to import the postgres adapter 

// import _ "github.com/Tooooommy/builder/v9/dialect/postgres"

d := builder.Dialect("postgres")
ds := d.From("test").Where(builder.Ex{
	"foo": "bar",
	"baz": []int64{1, 2, 3},
}).Limit(10)

sql, args, _ := ds.ToSQL()
fmt.Println(sql, args)

sql, args, _ = ds.Prepared(true).ToSQL()
fmt.Println(sql, args)

Output:

SELECT * FROM "test" WHERE (("baz" IN (1, 2, 3)) AND ("foo" = 'bar')) LIMIT 10 []
SELECT * FROM "test" WHERE (("baz" IN ($1, $2, $3)) AND ("foo" = $4)) LIMIT $5 [1 2 3 bar 10]
Example (DatasetSqlite3)

Creating a mysql dataset. Be sure to import the sqlite3 adapter 

// import _ "github.com/Tooooommy/builder/v9/dialect/sqlite3"

d := builder.Dialect("sqlite3")
ds := d.From("test").Where(builder.Ex{
	"foo": "bar",
	"baz": []int64{1, 2, 3},
}).Limit(10)

sql, args, _ := ds.ToSQL()
fmt.Println(sql, args)

sql, args, _ = ds.Prepared(true).ToSQL()
fmt.Println(sql, args)

Output:

SELECT * FROM "test" WHERE (("baz" IN (1, 2, 3)) AND ("foo" = 'bar')) LIMIT 10 []
SELECT * FROM "test" WHERE (("baz" IN (?, ?, ?)) AND ("foo" = ?)) LIMIT ? [1 2 3 bar 10]
Example (DbMysql)

Creating a mysql database. Be sure to import the mysql adapter. 

// import _ "github.com/Tooooommy/builder/v9/dialect/mysql"

type item struct {
	ID      int64  `db:"id"`
	Address string `db:"address"`
	Name    string `db:"name"`
}

// set up a mock db this would normally be
// db, err := sql.Open("mysql", dbURI)
// 	if err != nil {
// 		panic(err.Error())
// 	}
mDB, mock, _ := sqlmock.New()

d := builder.Dialect("mysql")

conn := sqlx.NewSqlConnFromDB(mDB)
db := d.DB(conn)

// use the db.From to get a dataset to execute queries
ds := db.From("items").Where(builder.C("id").Eq(1))

// set up mock for example purposes
mock.ExpectQuery("SELECT \\* FROM `items` WHERE \\(`id` = 1\\) LIMIT 1").
	WillReturnRows(
		sqlmock.NewRows([]string{"id", "address", "name"}).
			FromCSVString("1, 111 Test Addr,Test1"),
	)
var it item
err := ds.QueryRow(&it)
fmt.Println(it, err)

// set up mock for example purposes
mock.ExpectQuery("SELECT \\* FROM `items` WHERE \\(`id` = \\?\\) LIMIT \\?").
	WithArgs(1, 1).
	WillReturnRows(
		sqlmock.NewRows([]string{"id", "address", "name"}).
			FromCSVString("1, 111 Test Addr,Test1"),
	)

err = ds.Prepared(true).QueryRow(&it)
fmt.Println(it, err)

Output:

{1 111 Test Addr Test1} <nil>
{1 111 Test Addr Test1} <nil>
Example (DbPostgres)

Creating a postgres dataset. Be sure to import the postgres adapter 

// import _ "github.com/Tooooommy/builder/v9/dialect/postgres"

type item struct {
	ID      int64  `db:"id"`
	Address string `db:"address"`
	Name    string `db:"name"`
}

// set up a mock db this would normally be
// db, err := sql.Open("postgres", dbURI)
// 	if err != nil {
// 		panic(err.Error())
// 	}
mDB, mock, _ := sqlmock.New()

d := builder.Dialect("postgres")

conn := sqlx.NewSqlConnFromDB(mDB)
db := d.DB(conn)

// use the db.From to get a dataset to execute queries
ds := db.From("items").Where(builder.C("id").Eq(1))

// set up mock for example purposes
mock.ExpectQuery(`SELECT \* FROM "items" WHERE \("id" = 1\) LIMIT 1`).
	WillReturnRows(
		sqlmock.NewRows([]string{"id", "address", "name"}).
			FromCSVString("1, 111 Test Addr,Test1"),
	)
var it item
err := ds.QueryRow(&it)
fmt.Println(it, err)

// set up mock for example purposes
mock.ExpectQuery(`SELECT \* FROM "items" WHERE \("id" = \$1\) LIMIT \$2`).
	WithArgs(1, 1).
	WillReturnRows(
		sqlmock.NewRows([]string{"id", "address", "name"}).
			FromCSVString("1, 111 Test Addr,Test1"),
	)

err = ds.Prepared(true).QueryRow(&it)
fmt.Println(it, err)

Output:

{1 111 Test Addr Test1} <nil>
{1 111 Test Addr Test1} <nil>
Example (DbSqlite3)

Creating a sqlite3 database. Be sure to import the sqlite3 adapter 

// import _ "github.com/Tooooommy/builder/v9/dialect/sqlite3"
type item struct {
	ID      int64  `db:"id"`
	Address string `db:"address"`
	Name    string `db:"name"`
}

// set up a mock db this would normally be
// db, err := sql.Open("sqlite3", dbURI)
// 	if err != nil {
// 		panic(err.Error())
// 	}
mDB, mock, _ := sqlmock.New()

d := builder.Dialect("sqlite3")

conn := sqlx.NewSqlConnFromDB(mDB)
db := d.DB(conn)

// use the db.From to get a dataset to execute queries
ds := db.From("items").Where(builder.C("id").Eq(1))

// set up mock for example purposes
mock.ExpectQuery(`SELECT \* FROM "items" WHERE \("id" = 1\) LIMIT 1`).
	WillReturnRows(
		sqlmock.NewRows([]string{"id", "address", "name"}).
			FromCSVString("1, 111 Test Addr,Test1"),
	)
var it item
err := ds.QueryRow(&it)
fmt.Println(it, err)

// set up mock for example purposes
mock.ExpectQuery(`SELECT \* FROM "items" WHERE \("id" = \?\) LIMIT \?`).
	WithArgs(1, 1).
	WillReturnRows(
		sqlmock.NewRows([]string{"id", "address", "name"}).
			FromCSVString("1, 111 Test Addr,Test1"),
	)

err = ds.Prepared(true).QueryRow(&it)
fmt.Println(it, err)

Output:

{1 111 Test Addr Test1} <nil>
{1 111 Test Addr Test1} <nil>

func (DialectWrapper) DB 

func (dw DialectWrapper) DB(db sqlx.SqlConn) *Database

func (DialectWrapper) Delete 

func (dw DialectWrapper) Delete(table any) *DeleteDataset

Create a new dataset for creating DELETE sql statements

func (DialectWrapper) From 

func (dw DialectWrapper) From(table ...any) *SelectDataset

Create a new dataset for creating SELECT sql statements

func (DialectWrapper) Insert 

func (dw DialectWrapper) Insert(table any) *InsertDataset

Create a new dataset for creating INSERT sql statements

func (DialectWrapper) Select 

func (dw DialectWrapper) Select(cols ...any) *SelectDataset

Create a new dataset for creating SELECT sql statements

func (DialectWrapper) Truncate 

func (dw DialectWrapper) Truncate(table ...any) *TruncateDataset

Create a new dataset for creating TRUNCATE sql statements

func (DialectWrapper) Update 

func (dw DialectWrapper) Update(table any) *UpdateDataset

Create a new dataset for creating UPDATE sql statements

type Ex 

type Ex = exp.Ex
Example 
ds := builder.From("items").Where(
	builder.Ex{
		"col1": "a",
		"col2": 1,
		"col3": true,
		"col4": false,
		"col5": nil,
		"col6": []string{"a", "b", "c"},
	},
)

sql, args, _ := ds.ToSQL()
fmt.Println(sql, args)

sql, args, _ = ds.Prepared(true).ToSQL()
fmt.Println(sql, args)

Output:

SELECT * FROM "items" WHERE (("col1" = 'a') AND ("col2" = 1) AND ("col3" IS TRUE) AND ("col4" IS FALSE) AND ("col5" IS NULL) AND ("col6" IN ('a', 'b', 'c'))) []
SELECT * FROM "items" WHERE (("col1" = ?) AND ("col2" = ?) AND ("col3" IS TRUE) AND ("col4" IS FALSE) AND ("col5" IS NULL) AND ("col6" IN (?, ?, ?))) [a 1 a b c]
Example (In) 
// using an Ex expression map
sql, _, _ := builder.From("test").Where(builder.Ex{
	"a": []string{"a", "b", "c"},
}).ToSQL()
fmt.Println(sql)

Output:

SELECT * FROM "test" WHERE ("a" IN ('a', 'b', 'c'))
Example (WithOp) 
sql, args, _ := builder.From("items").Where(
	builder.Ex{
		"col1": builder.Op{"neq": "a"},
		"col3": builder.Op{"isNot": true},
		"col6": builder.Op{"notIn": []string{"a", "b", "c"}},
	},
).ToSQL()
fmt.Println(sql, args)

Output:

SELECT * FROM "items" WHERE (("col1" != 'a') AND ("col3" IS NOT TRUE) AND ("col6" NOT IN ('a', 'b', 'c'))) []

type ExOr 

type ExOr = exp.ExOr
Example 
sql, args, _ := builder.From("items").Where(
	builder.ExOr{
		"col1": "a",
		"col2": 1,
		"col3": true,
		"col4": false,
		"col5": nil,
		"col6": []string{"a", "b", "c"},
	},
).ToSQL()
fmt.Println(sql, args)

// nolint:lll // sql statements are long

Output:
Example (WithOp) 
sql, _, _ := builder.From("items").Where(builder.ExOr{
	"col1": builder.Op{"neq": "a"},
	"col3": builder.Op{"isNot": true},
	"col6": builder.Op{"notIn": []string{"a", "b", "c"}},
}).ToSQL()
fmt.Println(sql)

sql, _, _ = builder.From("items").Where(builder.ExOr{
	"col1": builder.Op{"gt": 1},
	"col2": builder.Op{"gte": 1},
	"col3": builder.Op{"lt": 1},
	"col4": builder.Op{"lte": 1},
}).ToSQL()
fmt.Println(sql)

sql, _, _ = builder.From("items").Where(builder.ExOr{
	"col1": builder.Op{"like": "a%"},
	"col2": builder.Op{"notLike": "a%"},
	"col3": builder.Op{"iLike": "a%"},
	"col4": builder.Op{"notILike": "a%"},
}).ToSQL()
fmt.Println(sql)

sql, _, _ = builder.From("items").Where(builder.ExOr{
	"col1": builder.Op{"like": regexp.MustCompile("^[ab]")},
	"col2": builder.Op{"notLike": regexp.MustCompile("^[ab]")},
	"col3": builder.Op{"iLike": regexp.MustCompile("^[ab]")},
	"col4": builder.Op{"notILike": regexp.MustCompile("^[ab]")},
}).ToSQL()
fmt.Println(sql)

Output:

SELECT * FROM "items" WHERE (("col1" != 'a') OR ("col3" IS NOT TRUE) OR ("col6" NOT IN ('a', 'b', 'c')))
SELECT * FROM "items" WHERE (("col1" > 1) OR ("col2" >= 1) OR ("col3" < 1) OR ("col4" <= 1))
SELECT * FROM "items" WHERE (("col1" LIKE 'a%') OR ("col2" NOT LIKE 'a%') OR ("col3" ILIKE 'a%') OR ("col4" NOT ILIKE 'a%'))
SELECT * FROM "items" WHERE (("col1" ~ '^[ab]') OR ("col2" !~ '^[ab]') OR ("col3" ~* '^[ab]') OR ("col4" !~* '^[ab]'))

type Expression 

type Expression = exp.Expression

type InsertDataset 

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

func Insert 

func Insert(table any) *InsertDataset

Creates a new InsertDataset for the provided table. Using this method will only allow you to create SQL user Database#From to create an InsertDataset with query capabilities

Example (BuilderRecord) 
ds := builder.Insert("user").Rows(
	builder.Record{"first_name": "Greg", "last_name": "Farley"},
	builder.Record{"first_name": "Jimmy", "last_name": "Stewart"},
	builder.Record{"first_name": "Jeff", "last_name": "Jeffers"},
)
insertSQL, args, _ := ds.ToSQL()
fmt.Println(insertSQL, args)

Output:

INSERT INTO "user" ("first_name", "last_name") VALUES ('Greg', 'Farley'), ('Jimmy', 'Stewart'), ('Jeff', 'Jeffers') []
Example (ColsAndVals) 
ds := builder.Insert("user").
	Cols("first_name", "last_name").
	Vals(
		builder.Vals{"Greg", "Farley"},
		builder.Vals{"Jimmy", "Stewart"},
		builder.Vals{"Jeff", "Jeffers"},
	)
insertSQL, args, _ := ds.ToSQL()
fmt.Println(insertSQL, args)

Output:

INSERT INTO "user" ("first_name", "last_name") VALUES ('Greg', 'Farley'), ('Jimmy', 'Stewart'), ('Jeff', 'Jeffers') []
Example (FromQuery) 
ds := builder.Insert("user").Prepared(true).
	FromQuery(builder.From("other_table"))
insertSQL, args, _ := ds.ToSQL()
fmt.Println(insertSQL, args)

Output:

INSERT INTO "user" SELECT * FROM "other_table" []
Example (FromQueryWithCols) 
ds := builder.Insert("user").Prepared(true).
	Cols("first_name", "last_name").
	FromQuery(builder.From("other_table").Select("fn", "ln"))
insertSQL, args, _ := ds.ToSQL()
fmt.Println(insertSQL, args)

Output:

INSERT INTO "user" ("first_name", "last_name") SELECT "fn", "ln" FROM "other_table" []
Example (Map) 
ds := builder.Insert("user").Rows(
	map[string]any{"first_name": "Greg", "last_name": "Farley"},
	map[string]any{"first_name": "Jimmy", "last_name": "Stewart"},
	map[string]any{"first_name": "Jeff", "last_name": "Jeffers"},
)
insertSQL, args, _ := ds.ToSQL()
fmt.Println(insertSQL, args)

Output:

INSERT INTO "user" ("first_name", "last_name") VALUES ('Greg', 'Farley'), ('Jimmy', 'Stewart'), ('Jeff', 'Jeffers') []
Example (Prepared) 
ds := builder.Insert("user").Prepared(true).Rows(
	builder.Record{"first_name": "Greg", "last_name": "Farley"},
	builder.Record{"first_name": "Jimmy", "last_name": "Stewart"},
	builder.Record{"first_name": "Jeff", "last_name": "Jeffers"},
)
insertSQL, args, _ := ds.ToSQL()
fmt.Println(insertSQL, args)

Output:

INSERT INTO "user" ("first_name", "last_name") VALUES (?, ?), (?, ?), (?, ?) [Greg Farley Jimmy Stewart Jeff Jeffers]
Example (Struct) 
type User struct {
	FirstName string `db:"first_name"`
	LastName  string `db:"last_name"`
}
ds := builder.Insert("user").Rows(
	User{FirstName: "Greg", LastName: "Farley"},
	User{FirstName: "Jimmy", LastName: "Stewart"},
	User{FirstName: "Jeff", LastName: "Jeffers"},
)
insertSQL, args, _ := ds.ToSQL()
fmt.Println(insertSQL, args)

Output:

INSERT INTO "user" ("first_name", "last_name") VALUES ('Greg', 'Farley'), ('Jimmy', 'Stewart'), ('Jeff', 'Jeffers') []

func (*InsertDataset) AppendSQL 

func (id *InsertDataset) AppendSQL(b sb.SQLBuilder)

Appends this Dataset's INSERT statement to the SQLBuilder This is used internally when using inserts in CTEs

func (*InsertDataset) As 

func (id *InsertDataset) As(alias string) *InsertDataset

Sets the alias for this dataset. This is typically used when using a Dataset as MySQL upsert

func (*InsertDataset) ClearCols 

func (id *InsertDataset) ClearCols() *InsertDataset

Clears the Columns to insert into

Example 
ds := builder.Insert("test").Cols("a", "b", "c")
insertSQL, _, _ := ds.ClearCols().Cols("other_a", "other_b", "other_c").
	FromQuery(builder.From("foo").Select("d", "e", "f")).
	ToSQL()
fmt.Println(insertSQL)

Output:

INSERT INTO "test" ("other_a", "other_b", "other_c") SELECT "d", "e", "f" FROM "foo"

func (*InsertDataset) ClearOnConflict 

func (id *InsertDataset) ClearOnConflict() *InsertDataset

Clears the on conflict clause. See example

Example 
type item struct {
	ID      uint32 `db:"id" builder:"skipinsert"`
	Address string `db:"address"`
	Name    string `db:"name"`
}
ds := builder.Insert("items").OnConflict(builder.DoNothing())
insertSQL, args, _ := ds.ClearOnConflict().Rows(
	item{Name: "Test1", Address: "111 Test Addr"},
	item{Name: "Test2", Address: "112 Test Addr"},
).ToSQL()
fmt.Println(insertSQL, args)

Output:

INSERT INTO "items" ("address", "name") VALUES ('111 Test Addr', 'Test1'), ('112 Test Addr', 'Test2') []

func (*InsertDataset) ClearRows 

func (id *InsertDataset) ClearRows() *InsertDataset

Clears the rows for this insert dataset. See examples.

Example 
type item struct {
	ID      uint32 `builder:"skipinsert"`
	Address string
	Name    string
}
ds := builder.Insert("items").Rows(
	item{Name: "Test1", Address: "111 Test Addr"},
	item{Name: "Test2", Address: "112 Test Addr"},
)
insertSQL, args, _ := ds.ClearRows().ToSQL()
fmt.Println(insertSQL, args)

Output:

INSERT INTO "items" DEFAULT VALUES []

func (*InsertDataset) ClearVals 

func (id *InsertDataset) ClearVals() *InsertDataset

Clears the values. See examples.

Example 
insertSQL, _, _ := builder.Insert("test").
	Cols("a", "b", "c").
	Vals(
		[]any{"a1", "b1", "c1"},
		[]any{"a2", "b1", "c1"},
		[]any{"a3", "b1", "c1"},
	).
	ClearVals().
	ToSQL()
fmt.Println(insertSQL)

insertSQL, _, _ = builder.Insert("test").
	Cols("a", "b", "c").
	Vals([]any{"a1", "b1", "c1"}).
	Vals([]any{"a2", "b2", "c2"}).
	Vals([]any{"a3", "b3", "c3"}).
	ClearVals().
	ToSQL()
fmt.Println(insertSQL)

Output:

INSERT INTO "test" DEFAULT VALUES
INSERT INTO "test" DEFAULT VALUES

func (*InsertDataset) Clone 

func (id *InsertDataset) Clone() exp.Expression

Clones the dataset

func (*InsertDataset) Cols 

func (id *InsertDataset) Cols(cols ...any) *InsertDataset

Sets the Columns to insert into

Example 
insertSQL, _, _ := builder.Insert("test").
	Cols("a", "b", "c").
	Vals(
		[]any{"a1", "b1", "c1"},
		[]any{"a2", "b1", "c1"},
		[]any{"a3", "b1", "c1"},
	).
	ToSQL()
fmt.Println(insertSQL)

Output:

INSERT INTO "test" ("a", "b", "c") VALUES ('a1', 'b1', 'c1'), ('a2', 'b1', 'c1'), ('a3', 'b1', 'c1')
Example (WithFromQuery) 
insertSQL, _, _ := builder.Insert("test").
	Cols("a", "b", "c").
	FromQuery(builder.From("foo").Select("d", "e", "f")).
	ToSQL()
fmt.Println(insertSQL)

Output:

INSERT INTO "test" ("a", "b", "c") SELECT "d", "e", "f" FROM "foo"

func (*InsertDataset) ColsAppend 

func (id *InsertDataset) ColsAppend(cols ...any) *InsertDataset

Adds columns to the current list of columns clause. See examples

Example 
insertSQL, _, _ := builder.Insert("test").
	Cols("a", "b").
	ColsAppend("c").
	Vals(
		[]any{"a1", "b1", "c1"},
		[]any{"a2", "b1", "c1"},
		[]any{"a3", "b1", "c1"},
	).
	ToSQL()
fmt.Println(insertSQL)

Output:

INSERT INTO "test" ("a", "b", "c") VALUES ('a1', 'b1', 'c1'), ('a2', 'b1', 'c1'), ('a3', 'b1', 'c1')

func (*InsertDataset) Dialect 

func (id *InsertDataset) Dialect() SQLDialect

Returns the current adapter on the dataset

func (*InsertDataset) Error 

func (id *InsertDataset) Error() error

Get any error that has been set or nil if no error has been set.

func (*InsertDataset) Exec 

func (id *InsertDataset) Exec() (sql.Result, error)

func (*InsertDataset) ExecCtx 

func (id *InsertDataset) ExecCtx(ctx context.Context) (sql.Result, error)

func (*InsertDataset) Expression 

func (id *InsertDataset) Expression() exp.Expression

func (*InsertDataset) FromQuery 

func (id *InsertDataset) FromQuery(from exp.AppendableExpression) *InsertDataset

Adds a subquery to the insert. See examples.

Example 
insertSQL, _, _ := builder.Insert("test").
	FromQuery(builder.From("test2").Where(builder.C("age").Gt(10))).
	ToSQL()
fmt.Println(insertSQL)

Output:

INSERT INTO "test" SELECT * FROM "test2" WHERE ("age" > 10)

func (*InsertDataset) GetAs 

func (id *InsertDataset) GetAs() exp.IdentifierExpression

func (*InsertDataset) GetClauses 

func (id *InsertDataset) GetClauses() exp.InsertClauses

Returns the current clauses on the dataset.

func (*InsertDataset) Into 

func (id *InsertDataset) Into(into any) *InsertDataset

Sets the table to insert INTO. This return a new dataset with the original table replaced. See examples. You can pass in the following. 

string: Will automatically be turned into an identifier
Expression: Any valid expression (IdentifierExpression, AliasedExpression, Literal, etc.)
Example 
ds := builder.Insert("test")
insertSQL, _, _ := ds.Into("test2").Rows(builder.Record{"first_name": "bob", "last_name": "yukon"}).ToSQL()
fmt.Println(insertSQL)

Output:

INSERT INTO "test2" ("first_name", "last_name") VALUES ('bob', 'yukon')
Example (Aliased) 
ds := builder.Insert("test")
insertSQL, _, _ := ds.
	Into(builder.T("test").As("t")).
	Rows(builder.Record{"first_name": "bob", "last_name": "yukon"}).
	ToSQL()
fmt.Println(insertSQL)

Output:

INSERT INTO "test" AS "t" ("first_name", "last_name") VALUES ('bob', 'yukon')

func (*InsertDataset) IsPrepared 

func (id *InsertDataset) IsPrepared() bool

func (*InsertDataset) OnConflict 

func (id *InsertDataset) OnConflict(conflict exp.ConflictExpression) *InsertDataset

Adds an (ON CONFLICT/ON DUPLICATE KEY) clause to the dataset if the dialect supports it. See examples.

Example (DoNothing) 
type item struct {
	ID      uint32 `db:"id" builder:"skipinsert"`
	Address string `db:"address"`
	Name    string `db:"name"`
}
insertSQL, args, _ := builder.Insert("items").Rows(
	item{Name: "Test1", Address: "111 Test Addr"},
	item{Name: "Test2", Address: "112 Test Addr"},
).OnConflict(builder.DoNothing()).ToSQL()
fmt.Println(insertSQL, args)

Output:

INSERT INTO "items" ("address", "name") VALUES ('111 Test Addr', 'Test1'), ('112 Test Addr', 'Test2') ON CONFLICT DO NOTHING []
Example (DoUpdate) 
insertSQL, args, _ := builder.Insert("items").
	Rows(
		builder.Record{"name": "Test1", "address": "111 Test Addr"},
		builder.Record{"name": "Test2", "address": "112 Test Addr"},
	).
	OnConflict(builder.DoUpdate("key", builder.Record{"updated": builder.L("NOW()")})).
	ToSQL()
fmt.Println(insertSQL, args)

Output:

INSERT INTO "items" ("address", "name") VALUES ('111 Test Addr', 'Test1'), ('112 Test Addr', 'Test2') ON CONFLICT (key) DO UPDATE SET "updated"=NOW() []
Example (DoUpdateWithWhere) 
type item struct {
	ID      uint32 `db:"id" builder:"skipinsert"`
	Address string `db:"address"`
	Name    string `db:"name"`
}
insertSQL, args, _ := builder.Insert("items").
	Rows([]item{
		{Name: "Test1", Address: "111 Test Addr"},
		{Name: "Test2", Address: "112 Test Addr"},
	}).
	OnConflict(builder.DoUpdate(
		"key",
		builder.Record{"updated": builder.L("NOW()")}).Where(builder.C("allow_update").IsTrue()),
	).
	ToSQL()
fmt.Println(insertSQL, args)

Output:

INSERT INTO "items" ("address", "name") VALUES ('111 Test Addr', 'Test1'), ('112 Test Addr', 'Test2') ON CONFLICT (key) DO UPDATE SET "updated"=NOW() WHERE ("allow_update" IS TRUE) []

func (*InsertDataset) Prepared 

func (id *InsertDataset) Prepared(prepared bool) *InsertDataset

Set the parameter interpolation behavior. See examples

prepared: If true the dataset WILL NOT interpolate the parameters.

Example 
type item struct {
	ID      uint32 `db:"id" builder:"skipinsert"`
	Address string `db:"address"`
	Name    string `db:"name"`
}

insertSQL, args, _ := builder.Insert("items").Prepared(true).Rows(
	item{Name: "Test1", Address: "111 Test Addr"},
	item{Name: "Test2", Address: "112 Test Addr"},
).ToSQL()
fmt.Println(insertSQL, args)

insertSQL, args, _ = builder.Insert("items").Prepared(true).Rows(
	builder.Record{"name": "Test1", "address": "111 Test Addr"},
	builder.Record{"name": "Test2", "address": "112 Test Addr"},
).ToSQL()
fmt.Println(insertSQL, args)

insertSQL, args, _ = builder.Insert("items").Prepared(true).Rows(
	[]item{
		{Name: "Test1", Address: "111 Test Addr"},
		{Name: "Test2", Address: "112 Test Addr"},
	}).ToSQL()
fmt.Println(insertSQL, args)

insertSQL, args, _ = builder.Insert("items").Prepared(true).Rows(
	[]builder.Record{
		{"name": "Test1", "address": "111 Test Addr"},
		{"name": "Test2", "address": "112 Test Addr"},
	}).ToSQL()
fmt.Println(insertSQL, args)

Output:

INSERT INTO "items" ("address", "name") VALUES (?, ?), (?, ?) [111 Test Addr Test1 112 Test Addr Test2]
INSERT INTO "items" ("address", "name") VALUES (?, ?), (?, ?) [111 Test Addr Test1 112 Test Addr Test2]
INSERT INTO "items" ("address", "name") VALUES (?, ?), (?, ?) [111 Test Addr Test1 112 Test Addr Test2]
INSERT INTO "items" ("address", "name") VALUES (?, ?), (?, ?) [111 Test Addr Test1 112 Test Addr Test2]

func (*InsertDataset) QueryRow 

func (id *InsertDataset) QueryRow(v any) error

func (*InsertDataset) QueryRowCtx 

func (id *InsertDataset) QueryRowCtx(ctx context.Context, v any) error

func (*InsertDataset) QueryRowPartial 

func (id *InsertDataset) QueryRowPartial(v any) error

func (*InsertDataset) QueryRowPartialCtx 

func (id *InsertDataset) QueryRowPartialCtx(ctx context.Context, v any) error

func (*InsertDataset) QueryRows 

func (id *InsertDataset) QueryRows(v any) error

func (*InsertDataset) QueryRowsCtx 

func (id *InsertDataset) QueryRowsCtx(ctx context.Context, v any) error

func (*InsertDataset) QueryRowsPartial 

func (id *InsertDataset) QueryRowsPartial(v any) error

func (*InsertDataset) QueryRowsPartialCtx 

func (id *InsertDataset) QueryRowsPartialCtx(ctx context.Context, v any) error

func (*InsertDataset) Returning 

func (id *InsertDataset) Returning(returning ...any) *InsertDataset

Adds a RETURNING clause to the dataset if the adapter supports it See examples.

Example 
insertSQL, _, _ := builder.Insert("test").
	Returning("id").
	Rows(builder.Record{"a": "a", "b": "b"}).
	ToSQL()
fmt.Println(insertSQL)
insertSQL, _, _ = builder.Insert("test").
	Returning(builder.T("test").All()).
	Rows(builder.Record{"a": "a", "b": "b"}).
	ToSQL()
fmt.Println(insertSQL)
insertSQL, _, _ = builder.Insert("test").
	Returning("a", "b").
	Rows(builder.Record{"a": "a", "b": "b"}).
	ToSQL()
fmt.Println(insertSQL)

Output:

INSERT INTO "test" ("a", "b") VALUES ('a', 'b') RETURNING "id"
INSERT INTO "test" ("a", "b") VALUES ('a', 'b') RETURNING "test".*
INSERT INTO "test" ("a", "b") VALUES ('a', 'b') RETURNING "a", "b"

func (*InsertDataset) ReturnsColumns 

func (id *InsertDataset) ReturnsColumns() bool

func (*InsertDataset) Rows 

func (id *InsertDataset) Rows(rows ...any) *InsertDataset

Insert rows. Rows can be a map, builder.Record or struct. See examples.

Example (WithEmbeddedStruct) 
type Address struct {
	Street string `db:"address_street"`
	State  string `db:"address_state"`
}
type User struct {
	Address
	FirstName string
	LastName  string
}
ds := builder.Insert("user").Rows(
	User{Address: Address{Street: "111 Street", State: "NY"}, FirstName: "Greg", LastName: "Farley"},
	User{Address: Address{Street: "211 Street", State: "NY"}, FirstName: "Jimmy", LastName: "Stewart"},
	User{Address: Address{Street: "311 Street", State: "NY"}, FirstName: "Jeff", LastName: "Jeffers"},
)
insertSQL, args, _ := ds.ToSQL()
fmt.Println(insertSQL, args)

Output:

INSERT INTO "user" ("address_state", "address_street", "firstname", "lastname") VALUES ('NY', '111 Street', 'Greg', 'Farley'), ('NY', '211 Street', 'Jimmy', 'Stewart'), ('NY', '311 Street', 'Jeff', 'Jeffers') []
Example (WithIgnoredEmbedded) 
type Address struct {
	Street string
	State  string
}
type User struct {
	Address   `db:"-"`
	FirstName string
	LastName  string
}
ds := builder.Insert("user").Rows(
	User{Address: Address{Street: "111 Street", State: "NY"}, FirstName: "Greg", LastName: "Farley"},
	User{Address: Address{Street: "211 Street", State: "NY"}, FirstName: "Jimmy", LastName: "Stewart"},
	User{Address: Address{Street: "311 Street", State: "NY"}, FirstName: "Jeff", LastName: "Jeffers"},
)
insertSQL, args, _ := ds.ToSQL()
fmt.Println(insertSQL, args)

Output:

INSERT INTO "user" ("firstname", "lastname") VALUES ('Greg', 'Farley'), ('Jimmy', 'Stewart'), ('Jeff', 'Jeffers') []
Example (WithNilEmbeddedPointer) 
type Address struct {
	Street string
	State  string
}
type User struct {
	*Address
	FirstName string
	LastName  string
}
ds := builder.Insert("user").Rows(
	User{FirstName: "Greg", LastName: "Farley"},
	User{FirstName: "Jimmy", LastName: "Stewart"},
	User{FirstName: "Jeff", LastName: "Jeffers"},
)
insertSQL, args, _ := ds.ToSQL()
fmt.Println(insertSQL, args)

Output:

INSERT INTO "user" ("firstname", "lastname") VALUES ('Greg', 'Farley'), ('Jimmy', 'Stewart'), ('Jeff', 'Jeffers') []
Example (WithNoDbTag) 
type item struct {
	ID      uint32 `builder:"skipinsert"`
	Address string
	Name    string
}
insertSQL, args, _ := builder.Insert("items").
	Rows(
		item{Name: "Test1", Address: "111 Test Addr"},
		item{Name: "Test2", Address: "112 Test Addr"},
	).
	ToSQL()
fmt.Println(insertSQL, args)

insertSQL, args, _ = builder.Insert("items").
	Rows(
		item{Name: "Test1", Address: "111 Test Addr"},
		item{Name: "Test2", Address: "112 Test Addr"},
	).
	ToSQL()
fmt.Println(insertSQL, args)

insertSQL, args, _ = builder.Insert("items").
	Rows([]item{
		{Name: "Test1", Address: "111 Test Addr"},
		{Name: "Test2", Address: "112 Test Addr"},
	}).
	ToSQL()
fmt.Println(insertSQL, args)

Output:

INSERT INTO "items" ("address", "name") VALUES ('111 Test Addr', 'Test1'), ('112 Test Addr', 'Test2') []
INSERT INTO "items" ("address", "name") VALUES ('111 Test Addr', 'Test1'), ('112 Test Addr', 'Test2') []
INSERT INTO "items" ("address", "name") VALUES ('111 Test Addr', 'Test1'), ('112 Test Addr', 'Test2') []
Example (WithbuilderDefaultIfEmptyTag) 
type item struct {
	ID      uint32 `builder:"skipinsert"`
	Address string
	Name    string `builder:"defaultifempty"`
}
insertSQL, args, _ := builder.Insert("items").
	Rows(
		item{Name: "Test1", Address: "111 Test Addr"},
		item{Address: "112 Test Addr"},
	).
	ToSQL()
fmt.Println(insertSQL, args)

insertSQL, args, _ = builder.Insert("items").
	Rows([]item{
		{Address: "111 Test Addr"},
		{Name: "Test2", Address: "112 Test Addr"},
	}).
	ToSQL()
fmt.Println(insertSQL, args)

Output:

INSERT INTO "items" ("address", "name") VALUES ('111 Test Addr', 'Test1'), ('112 Test Addr', DEFAULT) []
INSERT INTO "items" ("address", "name") VALUES ('111 Test Addr', DEFAULT), ('112 Test Addr', 'Test2') []
Example (WithbuilderSkipInsertTag) 
type item struct {
	ID      uint32 `builder:"skipinsert"`
	Address string
	Name    string `builder:"skipinsert"`
}
insertSQL, args, _ := builder.Insert("items").
	Rows(
		item{Name: "Test1", Address: "111 Test Addr"},
		item{Name: "Test2", Address: "112 Test Addr"},
	).
	ToSQL()
fmt.Println(insertSQL, args)

insertSQL, args, _ = builder.Insert("items").
	Rows([]item{
		{Name: "Test1", Address: "111 Test Addr"},
		{Name: "Test2", Address: "112 Test Addr"},
	}).
	ToSQL()
fmt.Println(insertSQL, args)

Output:

INSERT INTO "items" ("address") VALUES ('111 Test Addr'), ('112 Test Addr') []
INSERT INTO "items" ("address") VALUES ('111 Test Addr'), ('112 Test Addr') []

func (*InsertDataset) SetDialect 

func (id *InsertDataset) SetDialect(dialect SQLDialect) *InsertDataset

Returns the current adapter on the dataset

func (*InsertDataset) SetError 

func (id *InsertDataset) SetError(err error) *InsertDataset

Set an error on the dataset if one has not already been set. This error will be returned by a future call to Error or as part of ToSQL. This can be used by end users to record errors while building up queries without having to track those separately.

func (*InsertDataset) ToSQL 

func (id *InsertDataset) ToSQL() (sql string, params []any, err error)

Generates the default INSERT statement. If Prepared has been called with true then the statement will not be interpolated. See examples. When using structs you may specify a column to be skipped in the insert, (e.g. id) by specifying a builder tag with `skipinsert` 

type Item struct{
   Id   uint32 `db:"id" builder:"skipinsert"`
   Name string `db:"name"`
}

rows: variable number arguments of either map[string]interface, Record, struct, or a single slice argument of the accepted types.

Errors:

  • There is no INTO clause
  • Different row types passed in, all rows must be of the same type
  • Maps with different numbers of K/V pairs
  • Rows of different lengths, (i.e. (Record{"name": "a"}, Record{"name": "a", "age": 10})
  • Error generating SQL
Example 
type item struct {
	ID      uint32 `db:"id" builder:"skipinsert"`
	Address string `db:"address"`
	Name    string `db:"name"`
}
insertSQL, args, _ := builder.Insert("items").Rows(
	item{Name: "Test1", Address: "111 Test Addr"},
	item{Name: "Test2", Address: "112 Test Addr"},
).ToSQL()
fmt.Println(insertSQL, args)

insertSQL, args, _ = builder.Insert("items").Rows(
	builder.Record{"name": "Test1", "address": "111 Test Addr"},
	builder.Record{"name": "Test2", "address": "112 Test Addr"},
).ToSQL()
fmt.Println(insertSQL, args)

insertSQL, args, _ = builder.Insert("items").Rows(
	[]item{
		{Name: "Test1", Address: "111 Test Addr"},
		{Name: "Test2", Address: "112 Test Addr"},
	}).ToSQL()
fmt.Println(insertSQL, args)

insertSQL, args, _ = builder.From("items").Insert().Rows(
	[]builder.Record{
		{"name": "Test1", "address": "111 Test Addr"},
		{"name": "Test2", "address": "112 Test Addr"},
	}).ToSQL()
fmt.Println(insertSQL, args)

Output:

INSERT INTO "items" ("address", "name") VALUES ('111 Test Addr', 'Test1'), ('112 Test Addr', 'Test2') []
INSERT INTO "items" ("address", "name") VALUES ('111 Test Addr', 'Test1'), ('112 Test Addr', 'Test2') []
INSERT INTO "items" ("address", "name") VALUES ('111 Test Addr', 'Test1'), ('112 Test Addr', 'Test2') []
INSERT INTO "items" ("address", "name") VALUES ('111 Test Addr', 'Test1'), ('112 Test Addr', 'Test2') []

func (*InsertDataset) Vals 

func (id *InsertDataset) Vals(vals ...[]any) *InsertDataset

Manually set values to insert See examples.

Example 
insertSQL, _, _ := builder.Insert("test").
	Cols("a", "b", "c").
	Vals(
		[]any{"a1", "b1", "c1"},
		[]any{"a2", "b2", "c2"},
		[]any{"a3", "b3", "c3"},
	).
	ToSQL()
fmt.Println(insertSQL)

insertSQL, _, _ = builder.Insert("test").
	Cols("a", "b", "c").
	Vals([]any{"a1", "b1", "c1"}).
	Vals([]any{"a2", "b2", "c2"}).
	Vals([]any{"a3", "b3", "c3"}).
	ToSQL()
fmt.Println(insertSQL)

Output:

INSERT INTO "test" ("a", "b", "c") VALUES ('a1', 'b1', 'c1'), ('a2', 'b2', 'c2'), ('a3', 'b3', 'c3')
INSERT INTO "test" ("a", "b", "c") VALUES ('a1', 'b1', 'c1'), ('a2', 'b2', 'c2'), ('a3', 'b3', 'c3')

func (*InsertDataset) With 

func (id *InsertDataset) With(name string, subquery exp.Expression) *InsertDataset

Creates a WITH clause for a common table expression (CTE).

The name will be available to SELECT from in the associated query; and can optionally contain a list of column names "name(col1, col2, col3)".

The name will refer to the results of the specified subquery.

Example 
insertSQL, _, _ := builder.Insert("foo").
	With("other", builder.From("bar").Where(builder.C("id").Gt(10))).
	FromQuery(builder.From("other")).
	ToSQL()
fmt.Println(insertSQL)

Output:

WITH other AS (SELECT * FROM "bar" WHERE ("id" > 10)) INSERT INTO "foo" SELECT * FROM "other"

func (*InsertDataset) WithDialect 

func (id *InsertDataset) WithDialect(dl string) *InsertDataset

Sets the adapter used to serialize values and create the SQL statement

func (*InsertDataset) WithRecursive 

func (id *InsertDataset) WithRecursive(name string, subquery exp.Expression) *InsertDataset

Creates a WITH RECURSIVE clause for a common table expression (CTE)

The name will be available to SELECT from in the associated query; and must contain a list of column names "name(col1, col2, col3)" for a recursive clause.

The name will refer to the results of the specified subquery. The subquery for a recursive query will always end with a UNION or UNION ALL with a clause that refers to the CTE by name.

Example 
insertSQL, _, _ := builder.Insert("num_count").
	WithRecursive("nums(x)",
		builder.From().Select(builder.L("1")).
			UnionAll(builder.From("nums").
				Select(builder.L("x+1")).Where(builder.C("x").Lt(5))),
	).
	FromQuery(builder.From("nums")).
	ToSQL()
fmt.Println(insertSQL)

Output:

WITH RECURSIVE nums(x) AS (SELECT 1 UNION ALL (SELECT x+1 FROM "nums" WHERE ("x" < 5))) INSERT INTO "num_count" SELECT * FROM "nums"

type Op 

type Op = exp.Op
Example (BetweenComparisons) 
ds := builder.From("test").Where(builder.Ex{
	"a": builder.Op{"between": builder.Range(1, 10)},
})
sql, args, _ := ds.ToSQL()
fmt.Println(sql, args)

sql, args, _ = ds.Prepared(true).ToSQL()
fmt.Println(sql, args)

ds = builder.From("test").Where(builder.Ex{
	"a": builder.Op{"notBetween": builder.Range(1, 10)},
})
sql, args, _ = ds.ToSQL()
fmt.Println(sql, args)

sql, args, _ = ds.Prepared(true).ToSQL()
fmt.Println(sql, args)

Output:

SELECT * FROM "test" WHERE ("a" BETWEEN 1 AND 10) []
SELECT * FROM "test" WHERE ("a" BETWEEN ? AND ?) [1 10]
SELECT * FROM "test" WHERE ("a" NOT BETWEEN 1 AND 10) []
SELECT * FROM "test" WHERE ("a" NOT BETWEEN ? AND ?) [1 10]
Example (Comparisons) 
ds := builder.From("test").Where(builder.Ex{
	"a": 10,
	"b": builder.Op{"neq": 10},
	"c": builder.Op{"gte": 10},
	"d": builder.Op{"lt": 10},
	"e": builder.Op{"lte": 10},
})

sql, args, _ := ds.ToSQL()
fmt.Println(sql, args)

sql, args, _ = ds.Prepared(true).ToSQL()
fmt.Println(sql, args)

Output:

SELECT * FROM "test" WHERE (("a" = 10) AND ("b" != 10) AND ("c" >= 10) AND ("d" < 10) AND ("e" <= 10)) []
SELECT * FROM "test" WHERE (("a" = ?) AND ("b" != ?) AND ("c" >= ?) AND ("d" < ?) AND ("e" <= ?)) [10 10 10 10 10]
Example (InComparisons) 
// using an Ex expression map
ds := builder.From("test").Where(builder.Ex{
	"a": builder.Op{"in": []string{"a", "b", "c"}},
})

sql, args, _ := ds.ToSQL()
fmt.Println(sql, args)

sql, args, _ = ds.Prepared(true).ToSQL()
fmt.Println(sql, args)

ds = builder.From("test").Where(builder.Ex{
	"a": builder.Op{"notIn": []string{"a", "b", "c"}},
})

sql, args, _ = ds.ToSQL()
fmt.Println(sql, args)

sql, args, _ = ds.Prepared(true).ToSQL()
fmt.Println(sql, args)

Output:

SELECT * FROM "test" WHERE ("a" IN ('a', 'b', 'c')) []
SELECT * FROM "test" WHERE ("a" IN (?, ?, ?)) [a b c]
SELECT * FROM "test" WHERE ("a" NOT IN ('a', 'b', 'c')) []
SELECT * FROM "test" WHERE ("a" NOT IN (?, ?, ?)) [a b c]
Example (IsComparisons) 
// using an Ex expression map
ds := builder.From("test").Where(builder.Ex{
	"a": true,
})
sql, args, _ := ds.ToSQL()
fmt.Println(sql, args)
sql, args, _ = ds.Prepared(true).ToSQL()
fmt.Println(sql, args)

ds = builder.From("test").Where(builder.Ex{
	"a": builder.Op{"is": true},
})
sql, args, _ = ds.ToSQL()
fmt.Println(sql, args)
sql, args, _ = ds.Prepared(true).ToSQL()
fmt.Println(sql, args)

ds = builder.From("test").Where(builder.Ex{
	"a": false,
})
sql, args, _ = ds.ToSQL()
fmt.Println(sql, args)
sql, args, _ = ds.Prepared(true).ToSQL()
fmt.Println(sql, args)

ds = builder.From("test").Where(builder.Ex{
	"a": builder.Op{"is": false},
})
sql, args, _ = ds.ToSQL()
fmt.Println(sql, args)
sql, args, _ = ds.Prepared(true).ToSQL()
fmt.Println(sql, args)

ds = builder.From("test").Where(builder.Ex{
	"a": nil,
})
sql, args, _ = ds.ToSQL()
fmt.Println(sql, args)
sql, args, _ = ds.Prepared(true).ToSQL()
fmt.Println(sql, args)

ds = builder.From("test").Where(builder.Ex{
	"a": builder.Op{"is": nil},
})
sql, args, _ = ds.ToSQL()
fmt.Println(sql, args)
sql, args, _ = ds.Prepared(true).ToSQL()
fmt.Println(sql, args)

ds = builder.From("test").Where(builder.Ex{
	"a": builder.Op{"isNot": true},
})
sql, args, _ = ds.ToSQL()
fmt.Println(sql, args)
sql, args, _ = ds.Prepared(true).ToSQL()
fmt.Println(sql, args)

ds = builder.From("test").Where(builder.Ex{
	"a": builder.Op{"isNot": false},
})
sql, args, _ = ds.ToSQL()
fmt.Println(sql, args)
sql, args, _ = ds.Prepared(true).ToSQL()
fmt.Println(sql, args)

ds = builder.From("test").Where(builder.Ex{
	"a": builder.Op{"isNot": nil},
})
sql, args, _ = ds.ToSQL()
fmt.Println(sql, args)
sql, args, _ = ds.Prepared(true).ToSQL()
fmt.Println(sql, args)

Output:

SELECT * FROM "test" WHERE ("a" IS TRUE) []
SELECT * FROM "test" WHERE ("a" IS TRUE) []
SELECT * FROM "test" WHERE ("a" IS TRUE) []
SELECT * FROM "test" WHERE ("a" IS TRUE) []
SELECT * FROM "test" WHERE ("a" IS FALSE) []
SELECT * FROM "test" WHERE ("a" IS FALSE) []
SELECT * FROM "test" WHERE ("a" IS FALSE) []
SELECT * FROM "test" WHERE ("a" IS FALSE) []
SELECT * FROM "test" WHERE ("a" IS NULL) []
SELECT * FROM "test" WHERE ("a" IS NULL) []
SELECT * FROM "test" WHERE ("a" IS NULL) []
SELECT * FROM "test" WHERE ("a" IS NULL) []
SELECT * FROM "test" WHERE ("a" IS NOT TRUE) []
SELECT * FROM "test" WHERE ("a" IS NOT TRUE) []
SELECT * FROM "test" WHERE ("a" IS NOT FALSE) []
SELECT * FROM "test" WHERE ("a" IS NOT FALSE) []
SELECT * FROM "test" WHERE ("a" IS NOT NULL) []
SELECT * FROM "test" WHERE ("a" IS NOT NULL) []
Example (LikeComparisons) 
// using an Ex expression map
ds := builder.From("test").Where(builder.Ex{
	"a": builder.Op{"like": "%a%"},
})
sql, args, _ := ds.ToSQL()
fmt.Println(sql, args)

sql, args, _ = ds.Prepared(true).ToSQL()
fmt.Println(sql, args)

ds = builder.From("test").Where(builder.Ex{
	"a": builder.Op{"like": regexp.MustCompile("[ab]")},
})

sql, args, _ = ds.ToSQL()
fmt.Println(sql, args)

sql, args, _ = ds.Prepared(true).ToSQL()
fmt.Println(sql, args)

ds = builder.From("test").Where(builder.Ex{
	"a": builder.Op{"iLike": "%a%"},
})

sql, args, _ = ds.ToSQL()
fmt.Println(sql, args)

sql, args, _ = ds.Prepared(true).ToSQL()
fmt.Println(sql, args)

ds = builder.From("test").Where(builder.Ex{
	"a": builder.Op{"iLike": regexp.MustCompile("[ab]")},
})

sql, args, _ = ds.ToSQL()
fmt.Println(sql, args)

sql, args, _ = ds.Prepared(true).ToSQL()
fmt.Println(sql, args)

ds = builder.From("test").Where(builder.Ex{
	"a": builder.Op{"notLike": "%a%"},
})

sql, args, _ = ds.ToSQL()
fmt.Println(sql, args)

sql, args, _ = ds.Prepared(true).ToSQL()
fmt.Println(sql, args)

ds = builder.From("test").Where(builder.Ex{
	"a": builder.Op{"notLike": regexp.MustCompile("[ab]")},
})

sql, args, _ = ds.ToSQL()
fmt.Println(sql, args)

sql, args, _ = ds.Prepared(true).ToSQL()
fmt.Println(sql, args)

ds = builder.From("test").Where(builder.Ex{
	"a": builder.Op{"notILike": "%a%"},
})

sql, args, _ = ds.ToSQL()
fmt.Println(sql, args)

sql, args, _ = ds.Prepared(true).ToSQL()
fmt.Println(sql, args)

ds = builder.From("test").Where(builder.Ex{
	"a": builder.Op{"notILike": regexp.MustCompile("[ab]")},
})
sql, args, _ = ds.ToSQL()
fmt.Println(sql, args)

sql, args, _ = ds.Prepared(true).ToSQL()
fmt.Println(sql, args)

Output:

SELECT * FROM "test" WHERE ("a" LIKE '%a%') []
SELECT * FROM "test" WHERE ("a" LIKE ?) [%a%]
SELECT * FROM "test" WHERE ("a" ~ '[ab]') []
SELECT * FROM "test" WHERE ("a" ~ ?) [[ab]]
SELECT * FROM "test" WHERE ("a" ILIKE '%a%') []
SELECT * FROM "test" WHERE ("a" ILIKE ?) [%a%]
SELECT * FROM "test" WHERE ("a" ~* '[ab]') []
SELECT * FROM "test" WHERE ("a" ~* ?) [[ab]]
SELECT * FROM "test" WHERE ("a" NOT LIKE '%a%') []
SELECT * FROM "test" WHERE ("a" NOT LIKE ?) [%a%]
SELECT * FROM "test" WHERE ("a" !~ '[ab]') []
SELECT * FROM "test" WHERE ("a" !~ ?) [[ab]]
SELECT * FROM "test" WHERE ("a" NOT ILIKE '%a%') []
SELECT * FROM "test" WHERE ("a" NOT ILIKE ?) [%a%]
SELECT * FROM "test" WHERE ("a" !~* '[ab]') []
SELECT * FROM "test" WHERE ("a" !~* ?) [[ab]]
Example (WithMultipleKeys)

When using a single op with multiple keys they are ORed together 

ds := builder.From("items").Where(builder.Ex{
	"col1": builder.Op{"is": nil, "eq": 10},
})

sql, args, _ := ds.ToSQL()
fmt.Println(sql, args)

sql, args, _ = ds.Prepared(true).ToSQL()
fmt.Println(sql, args)

Output:

SELECT * FROM "items" WHERE (("col1" = 10) OR ("col1" IS NULL)) []
SELECT * FROM "items" WHERE (("col1" = ?) OR ("col1" IS NULL)) [10]

type Record 

type Record = exp.Record
Example (Insert) 
ds := builder.Insert("test")

records := []builder.Record{
	{"col1": 1, "col2": "foo"},
	{"col1": 2, "col2": "bar"},
}

sql, args, _ := ds.Rows(records).ToSQL()
fmt.Println(sql, args)

sql, args, _ = ds.Prepared(true).Rows(records).ToSQL()
fmt.Println(sql, args)

Output:

INSERT INTO "test" ("col1", "col2") VALUES (1, 'foo'), (2, 'bar') []
INSERT INTO "test" ("col1", "col2") VALUES (?, ?), (?, ?) [1 foo 2 bar]
Example (Update) 
ds := builder.Update("test")
update := builder.Record{"col1": 1, "col2": "foo"}

sql, args, _ := ds.Set(update).ToSQL()
fmt.Println(sql, args)

sql, args, _ = ds.Prepared(true).Set(update).ToSQL()
fmt.Println(sql, args)

Output:

UPDATE "test" SET "col1"=1,"col2"='foo' []
UPDATE "test" SET "col1"=?,"col2"=? [1 foo]

type SQLDialect 

type SQLDialect interface {
	Dialect() string
	ToSelectSQL(b sb.SQLBuilder, clauses exp.SelectClauses)
	ToUpdateSQL(b sb.SQLBuilder, clauses exp.UpdateClauses)
	ToInsertSQL(b sb.SQLBuilder, clauses exp.InsertClauses)
	ToDeleteSQL(b sb.SQLBuilder, clauses exp.DeleteClauses)
	ToTruncateSQL(b sb.SQLBuilder, clauses exp.TruncateClauses)
}

An adapter interface to be used by a Dataset to generate SQL for a specific dialect. See DefaultAdapter for a concrete implementation and examples.

func GetDialect 

func GetDialect(name string) SQLDialect

type SQLDialectOptions 

type SQLDialectOptions = sqlgen.SQLDialectOptions

type SelectDataset 

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

Dataset for creating and/or executing SELECT SQL statements.

Example 
ds := builder.From("test").
	Select(builder.COUNT("*")).
	InnerJoin(builder.T("test2"), builder.On(builder.I("test.fkey").Eq(builder.I("test2.id")))).
	LeftJoin(builder.T("test3"), builder.On(builder.I("test2.fkey").Eq(builder.I("test3.id")))).
	Where(
		builder.Ex{
			"test.name": builder.Op{
				"like": regexp.MustCompile("^[ab]"),
			},
			"test2.amount": builder.Op{
				"isNot": nil,
			},
		},
		builder.ExOr{
			"test3.id":     nil,
			"test3.status": []string{"passed", "active", "registered"},
		}).
	Order(builder.I("test.created").Desc().NullsLast()).
	GroupBy(builder.I("test.user_id")).
	Having(builder.AVG("test3.age").Gt(10))

sql, args, _ := ds.ToSQL()
fmt.Println(sql, args)

sql, args, _ = ds.Prepared(true).ToSQL()
fmt.Println(sql, args)
// nolint:lll // SQL statements are long

Output:

func From 

func From(table ...any) *SelectDataset
Example 
sql, args, _ := builder.From("test").ToSQL()
fmt.Println(sql, args)

Output:

SELECT * FROM "test" []

func Select 

func Select(cols ...any) *SelectDataset
Example 
sql, _, _ := builder.Select(builder.L("NOW()")).ToSQL()
fmt.Println(sql)

Output:

SELECT NOW()

func (*SelectDataset) AppendSQL 

func (sd *SelectDataset) AppendSQL(b sb.SQLBuilder)

Appends this Dataset's SELECT statement to the SQLBuilder This is used internally for sub-selects by the dialect

func (*SelectDataset) As 

func (sd *SelectDataset) As(alias string) *SelectDataset

Sets the alias for this dataset. This is typically used when using a Dataset as a subselect. See examples.

Example 
ds := builder.From("test").As("t")
sql, _, _ := builder.From(ds).ToSQL()
fmt.Println(sql)

Output:

SELECT * FROM (SELECT * FROM "test") AS "t"

func (*SelectDataset) ClearLimit 

func (sd *SelectDataset) ClearLimit() *SelectDataset

Removes the LIMIT clause.

Example 
ds := builder.From("test").Limit(10)
sql, _, _ := ds.ClearLimit().ToSQL()
fmt.Println(sql)

Output:

SELECT * FROM "test"

func (*SelectDataset) ClearOffset 

func (sd *SelectDataset) ClearOffset() *SelectDataset

Removes the OFFSET clause from the Dataset

Example 
ds := builder.From("test").
	Offset(2)
sql, _, _ := ds.
	ClearOffset().
	ToSQL()
fmt.Println(sql)

Output:

SELECT * FROM "test"

func (*SelectDataset) ClearOrder 

func (sd *SelectDataset) ClearOrder() *SelectDataset

Removes the ORDER BY clause. See examples.

Example 
ds := builder.From("test").Order(builder.C("a").Asc())
sql, _, _ := ds.ClearOrder().ToSQL()
fmt.Println(sql)

Output:

SELECT * FROM "test"

func (*SelectDataset) ClearSelect 

func (sd *SelectDataset) ClearSelect() *SelectDataset

Resets to SELECT *. If the SelectDistinct or Distinct was used the returned Dataset will have the the dataset set to SELECT *. See examples.

Example 
ds := builder.From("test").Select("a", "b")
sql, _, _ := ds.ClearSelect().ToSQL()
fmt.Println(sql)
ds = builder.From("test").Select("a", "b").Distinct()
sql, _, _ = ds.ClearSelect().ToSQL()
fmt.Println(sql)

Output:

SELECT * FROM "test"
SELECT * FROM "test"

func (*SelectDataset) ClearWhere 

func (sd *SelectDataset) ClearWhere() *SelectDataset

Removes the WHERE clause. See examples.

Example 
ds := builder.From("test").Where(
	builder.Or(
		builder.C("a").Gt(10),
		builder.And(
			builder.C("b").Lt(10),
			builder.C("c").IsNull(),
		),
	),
)
sql, _, _ := ds.ClearWhere().ToSQL()
fmt.Println(sql)

Output:

SELECT * FROM "test"

func (*SelectDataset) ClearWindow 

func (sd *SelectDataset) ClearWindow() *SelectDataset

Sets the WINDOW clauses

func (*SelectDataset) Clone 

func (sd *SelectDataset) Clone() exp.Expression

Clones the dataset

func (*SelectDataset) CompoundFromSelf 

func (sd *SelectDataset) CompoundFromSelf() *SelectDataset

Used internally to determine if the dataset needs to use iteself as a source. If the dataset has an order or limit it will select from itself

func (*SelectDataset) Count 

func (sd *SelectDataset) Count() (int64, error)

Generates the SELECT COUNT(*) sql for this dataset and uses Exec#QueryRow to scan the result into an int64.

Example 
count, err := getDB().From("builder_user").Count()
if err != nil {
	fmt.Println(err.Error())
	return
}
fmt.Printf("Count is %d", count)

Output:

Count is 4

func (*SelectDataset) CountContext 

func (sd *SelectDataset) CountContext(ctx context.Context) (int64, error)

Generates the SELECT COUNT(*) sql for this dataset and uses Exec#QueryRowContext to scan the result into an int64.

func (*SelectDataset) CrossJoin 

func (sd *SelectDataset) CrossJoin(table exp.Expression) *SelectDataset

Adds a CROSS JOIN clause. See examples.

Example 
sql, _, _ := builder.From("test").CrossJoin(builder.T("test2")).ToSQL()
fmt.Println(sql)

sql, _, _ = builder.From("test").CrossJoin(
	builder.From("test2").Where(builder.C("amount").Gt(0)),
).ToSQL()
fmt.Println(sql)

sql, _, _ = builder.From("test").CrossJoin(
	builder.From("test2").Where(builder.C("amount").Gt(0)).As("t"),
).ToSQL()
fmt.Println(sql)

Output:

SELECT * FROM "test" CROSS JOIN "test2"
SELECT * FROM "test" CROSS JOIN (SELECT * FROM "test2" WHERE ("amount" > 0))
SELECT * FROM "test" CROSS JOIN (SELECT * FROM "test2" WHERE ("amount" > 0)) AS "t"

func (*SelectDataset) Delete 

func (sd *SelectDataset) Delete() *DeleteDataset

Creates a new DeleteDataset using the FROM of this dataset. This method will also copy over the `WITH`, `WHERE`, `ORDER , and `LIMIT`

Example 
sql, args, _ := builder.From("items").Delete().ToSQL()
fmt.Println(sql, args)

sql, args, _ = builder.From("items").
	Where(builder.Ex{"id": builder.Op{"gt": 10}}).
	Delete().
	ToSQL()
fmt.Println(sql, args)

Output:

DELETE FROM "items" []
DELETE FROM "items" WHERE ("id" > 10) []

func (*SelectDataset) Dialect 

func (sd *SelectDataset) Dialect() SQLDialect

Returns the current adapter on the dataset

func (*SelectDataset) Distinct 

func (sd *SelectDataset) Distinct(on ...any) *SelectDataset
Example 
sql, _, _ := builder.From("test").Select("a", "b").Distinct().ToSQL()
fmt.Println(sql)

Output:

SELECT DISTINCT "a", "b" FROM "test"
Example (On) 
sql, _, _ := builder.From("test").Distinct("a").ToSQL()
fmt.Println(sql)

Output:

SELECT DISTINCT ON ("a") * FROM "test"
Example (OnCoalesce) 
sql, _, _ := builder.From("test").Distinct(builder.COALESCE(builder.C("a"), "empty")).ToSQL()
fmt.Println(sql)

Output:

SELECT DISTINCT ON (COALESCE("a", 'empty')) * FROM "test"
Example (OnWithLiteral) 
sql, _, _ := builder.From("test").Distinct(builder.L("COALESCE(?, ?)", builder.C("a"), "empty")).ToSQL()
fmt.Println(sql)

Output:

SELECT DISTINCT ON (COALESCE("a", 'empty')) * FROM "test"

func (*SelectDataset) Error 

func (sd *SelectDataset) Error() error

Get any error that has been set or nil if no error has been set.

func (*SelectDataset) Expression 

func (sd *SelectDataset) Expression() exp.Expression

func (*SelectDataset) ForKeyShare 

func (sd *SelectDataset) ForKeyShare(waitOption exp.WaitOption, of ...exp.IdentifierExpression) *SelectDataset

Adds a FOR KEY SHARE clause. See examples.

func (*SelectDataset) ForNoKeyUpdate 

func (sd *SelectDataset) ForNoKeyUpdate(waitOption exp.WaitOption, of ...exp.IdentifierExpression) *SelectDataset

Adds a FOR NO KEY UPDATE clause. See examples.

func (*SelectDataset) ForShare 

func (sd *SelectDataset) ForShare(waitOption exp.WaitOption, of ...exp.IdentifierExpression) *SelectDataset

Adds a FOR SHARE clause. See examples.

func (*SelectDataset) ForUpdate 

func (sd *SelectDataset) ForUpdate(waitOption exp.WaitOption, of ...exp.IdentifierExpression) *SelectDataset

Adds a FOR UPDATE clause. See examples.

func (*SelectDataset) From 

func (sd *SelectDataset) From(from ...any) *SelectDataset

Adds a FROM clause. This return a new dataset with the original sources replaced. See examples. You can pass in the following. 

string: Will automatically be turned into an identifier
Dataset: Will be added as a sub select. If the Dataset is not aliased it will automatically be aliased
LiteralExpression: (See Literal) Will use the literal SQL
Example 
ds := builder.From("test")
sql, _, _ := ds.From("test2").ToSQL()
fmt.Println(sql)

Output:

SELECT * FROM "test2"
Example (WithAliasedDataset) 
ds := builder.From("test")
fromDs := ds.Where(builder.C("age").Gt(10))
sql, _, _ := ds.From(fromDs.As("test2")).ToSQL()
fmt.Println(sql)

Output:

SELECT * FROM (SELECT * FROM "test" WHERE ("age" > 10)) AS "test2"
Example (WithDataset) 
ds := builder.From("test")
fromDs := ds.Where(builder.C("age").Gt(10))
sql, _, _ := ds.From(fromDs).ToSQL()
fmt.Println(sql)

Output:

SELECT * FROM (SELECT * FROM "test" WHERE ("age" > 10)) AS "t1"

func (*SelectDataset) FromSelf 

func (sd *SelectDataset) FromSelf() *SelectDataset

Returns a new Dataset with the current one as an source. If the current Dataset is not aliased (See Dataset#As) then it will automatically be aliased. See examples.

Example 
sql, _, _ := builder.From("test").FromSelf().ToSQL()
fmt.Println(sql)
sql, _, _ = builder.From("test").As("my_test_table").FromSelf().ToSQL()
fmt.Println(sql)

Output:

SELECT * FROM (SELECT * FROM "test") AS "t1"
SELECT * FROM (SELECT * FROM "test") AS "my_test_table"

func (*SelectDataset) FullJoin 

func (sd *SelectDataset) FullJoin(table exp.Expression, condition exp.JoinCondition) *SelectDataset

Adds a FULL JOIN clause. See examples.

Example 
sql, _, _ := builder.From("test").FullJoin(
	builder.T("test2"),
	builder.On(builder.Ex{
		"test.fkey": builder.I("test2.Id"),
	}),
).ToSQL()
fmt.Println(sql)

sql, _, _ = builder.From("test").FullJoin(
	builder.T("test2"),
	builder.Using("common_column"),
).ToSQL()
fmt.Println(sql)

sql, _, _ = builder.From("test").FullJoin(
	builder.From("test2").Where(builder.C("amount").Gt(0)),
	builder.On(builder.I("test.fkey").Eq(builder.I("test2.Id"))),
).ToSQL()
fmt.Println(sql)

sql, _, _ = builder.From("test").FullJoin(
	builder.From("test2").Where(builder.C("amount").Gt(0)).As("t"),
	builder.On(builder.I("test.fkey").Eq(builder.I("t.Id"))),
).ToSQL()
fmt.Println(sql)

Output:

SELECT * FROM "test" FULL JOIN "test2" ON ("test"."fkey" = "test2"."Id")
SELECT * FROM "test" FULL JOIN "test2" USING ("common_column")
SELECT * FROM "test" FULL JOIN (SELECT * FROM "test2" WHERE ("amount" > 0)) ON ("test"."fkey" = "test2"."Id")
SELECT * FROM "test" FULL JOIN (SELECT * FROM "test2" WHERE ("amount" > 0)) AS "t" ON ("test"."fkey" = "t"."Id")

func (*SelectDataset) FullOuterJoin 

func (sd *SelectDataset) FullOuterJoin(table exp.Expression, condition exp.JoinCondition) *SelectDataset

Adds a FULL OUTER JOIN clause. See examples.

Example 
sql, _, _ := builder.From("test").FullOuterJoin(
	builder.T("test2"),
	builder.On(builder.Ex{
		"test.fkey": builder.I("test2.Id"),
	}),
).ToSQL()
fmt.Println(sql)

sql, _, _ = builder.From("test").FullOuterJoin(
	builder.T("test2"),
	builder.Using("common_column"),
).ToSQL()
fmt.Println(sql)

sql, _, _ = builder.From("test").FullOuterJoin(
	builder.From("test2").Where(builder.C("amount").Gt(0)),
	builder.On(builder.I("test.fkey").Eq(builder.I("test2.Id"))),
).ToSQL()
fmt.Println(sql)

sql, _, _ = builder.From("test").FullOuterJoin(
	builder.From("test2").Where(builder.C("amount").Gt(0)).As("t"),
	builder.On(builder.I("test.fkey").Eq(builder.I("t.Id"))),
).ToSQL()
fmt.Println(sql)

Output:

SELECT * FROM "test" FULL OUTER JOIN "test2" ON ("test"."fkey" = "test2"."Id")
SELECT * FROM "test" FULL OUTER JOIN "test2" USING ("common_column")
SELECT * FROM "test" FULL OUTER JOIN (SELECT * FROM "test2" WHERE ("amount" > 0)) ON ("test"."fkey" = "test2"."Id")
SELECT * FROM "test" FULL OUTER JOIN (SELECT * FROM "test2" WHERE ("amount" > 0)) AS "t" ON ("test"."fkey" = "t"."Id")

func (*SelectDataset) GetAs 

func (sd *SelectDataset) GetAs() exp.IdentifierExpression

Returns the alias value as an identiier expression

func (*SelectDataset) GetClauses 

func (sd *SelectDataset) GetClauses() exp.SelectClauses

Returns the current clauses on the dataset.

func (*SelectDataset) GroupBy 

func (sd *SelectDataset) GroupBy(groupBy ...any) *SelectDataset

Adds a GROUP BY clause. See examples.

Example 
sql, _, _ := builder.From("test").
	Select(builder.SUM("income").As("income_sum")).
	GroupBy("age").
	ToSQL()
fmt.Println(sql)

Output:

SELECT SUM("income") AS "income_sum" FROM "test" GROUP BY "age"

func (*SelectDataset) GroupByAppend 

func (sd *SelectDataset) GroupByAppend(groupBy ...any) *SelectDataset

Adds more columns to the current GROUP BY clause. See examples.

Example 
ds := builder.From("test").
	Select(builder.SUM("income").As("income_sum")).
	GroupBy("age")
sql, _, _ := ds.
	GroupByAppend("job").
	ToSQL()
fmt.Println(sql)
// the original dataset group by does not change
sql, _, _ = ds.ToSQL()
fmt.Println(sql)

Output:

SELECT SUM("income") AS "income_sum" FROM "test" GROUP BY "age", "job"
SELECT SUM("income") AS "income_sum" FROM "test" GROUP BY "age"

func (*SelectDataset) Having 

func (sd *SelectDataset) Having(expressions ...exp.Expression) *SelectDataset

Adds a HAVING clause. See examples.

Example 
sql, _, _ := builder.From("test").Having(builder.SUM("income").Gt(1000)).ToSQL()
fmt.Println(sql)
sql, _, _ = builder.From("test").GroupBy("age").Having(builder.SUM("income").Gt(1000)).ToSQL()
fmt.Println(sql)

Output:

SELECT * FROM "test" HAVING (SUM("income") > 1000)
SELECT * FROM "test" GROUP BY "age" HAVING (SUM("income") > 1000)

func (*SelectDataset) InnerJoin 

func (sd *SelectDataset) InnerJoin(table exp.Expression, condition exp.JoinCondition) *SelectDataset

Adds an INNER JOIN clause. See examples.

Example 
sql, _, _ := builder.From("test").InnerJoin(
	builder.T("test2"),
	builder.On(builder.Ex{
		"test.fkey": builder.I("test2.Id"),
	}),
).ToSQL()
fmt.Println(sql)

sql, _, _ = builder.From("test").InnerJoin(
	builder.T("test2"),
	builder.Using("common_column"),
).ToSQL()
fmt.Println(sql)

sql, _, _ = builder.From("test").InnerJoin(
	builder.From("test2").Where(builder.C("amount").Gt(0)),
	builder.On(builder.I("test.fkey").Eq(builder.I("test2.Id"))),
).ToSQL()
fmt.Println(sql)

sql, _, _ = builder.From("test").InnerJoin(
	builder.From("test2").Where(builder.C("amount").Gt(0)).As("t"),
	builder.On(builder.I("test.fkey").Eq(builder.I("t.Id"))),
).ToSQL()
fmt.Println(sql)

Output:

SELECT * FROM "test" INNER JOIN "test2" ON ("test"."fkey" = "test2"."Id")
SELECT * FROM "test" INNER JOIN "test2" USING ("common_column")
SELECT * FROM "test" INNER JOIN (SELECT * FROM "test2" WHERE ("amount" > 0)) ON ("test"."fkey" = "test2"."Id")
SELECT * FROM "test" INNER JOIN (SELECT * FROM "test2" WHERE ("amount" > 0)) AS "t" ON ("test"."fkey" = "t"."Id")

func (*SelectDataset) Insert 

func (sd *SelectDataset) Insert() *InsertDataset

Creates a new InsertDataset using the FROM of this dataset. This method will also copy over the `WITH` clause to the insert.

Example 
type item struct {
	ID      uint32 `db:"id" builder:"skipinsert"`
	Address string `db:"address"`
	Name    string `db:"name"`
}
sql, args, _ := builder.From("items").Insert().Rows(
	item{Name: "Test1", Address: "111 Test Addr"},
	item{Name: "Test2", Address: "112 Test Addr"},
).ToSQL()
fmt.Println(sql, args)

sql, args, _ = builder.From("items").Insert().Rows(
	builder.Record{"name": "Test1", "address": "111 Test Addr"},
	builder.Record{"name": "Test2", "address": "112 Test Addr"},
).ToSQL()
fmt.Println(sql, args)

sql, args, _ = builder.From("items").Insert().Rows(
	[]item{
		{Name: "Test1", Address: "111 Test Addr"},
		{Name: "Test2", Address: "112 Test Addr"},
	}).ToSQL()
fmt.Println(sql, args)

sql, args, _ = builder.From("items").Insert().Rows(
	[]builder.Record{
		{"name": "Test1", "address": "111 Test Addr"},
		{"name": "Test2", "address": "112 Test Addr"},
	}).ToSQL()
fmt.Println(sql, args)

Output:

INSERT INTO "items" ("address", "name") VALUES ('111 Test Addr', 'Test1'), ('112 Test Addr', 'Test2') []
INSERT INTO "items" ("address", "name") VALUES ('111 Test Addr', 'Test1'), ('112 Test Addr', 'Test2') []
INSERT INTO "items" ("address", "name") VALUES ('111 Test Addr', 'Test1'), ('112 Test Addr', 'Test2') []
INSERT INTO "items" ("address", "name") VALUES ('111 Test Addr', 'Test1'), ('112 Test Addr', 'Test2') []

func (*SelectDataset) Intersect 

func (sd *SelectDataset) Intersect(other *SelectDataset) *SelectDataset

Creates an INTERSECT statement with another dataset. If this or the other dataset has a limit or offset it will use that dataset as a subselect in the FROM clause. See examples.

Example 
sql, _, _ := builder.From("test").
	Intersect(builder.From("test2")).
	ToSQL()
fmt.Println(sql)
sql, _, _ = builder.From("test").
	Limit(1).
	Intersect(builder.From("test2")).
	ToSQL()
fmt.Println(sql)
sql, _, _ = builder.From("test").
	Limit(1).
	Intersect(builder.From("test2").
		Order(builder.C("id").Desc())).
	ToSQL()
fmt.Println(sql)

Output:

SELECT * FROM "test" INTERSECT (SELECT * FROM "test2")
SELECT * FROM (SELECT * FROM "test" LIMIT 1) AS "t1" INTERSECT (SELECT * FROM "test2")
SELECT * FROM (SELECT * FROM "test" LIMIT 1) AS "t1" INTERSECT (SELECT * FROM (SELECT * FROM "test2" ORDER BY "id" DESC) AS "t1")

func (*SelectDataset) IntersectAll 

func (sd *SelectDataset) IntersectAll(other *SelectDataset) *SelectDataset

Creates an INTERSECT ALL statement with another dataset. If this or the other dataset has a limit or offset it will use that dataset as a subselect in the FROM clause. See examples.

Example 
sql, _, _ := builder.From("test").
	IntersectAll(builder.From("test2")).
	ToSQL()
fmt.Println(sql)
sql, _, _ = builder.From("test").
	Limit(1).
	IntersectAll(builder.From("test2")).
	ToSQL()
fmt.Println(sql)
sql, _, _ = builder.From("test").
	Limit(1).
	IntersectAll(builder.From("test2").
		Order(builder.C("id").Desc())).
	ToSQL()
fmt.Println(sql)

Output:

SELECT * FROM "test" INTERSECT ALL (SELECT * FROM "test2")
SELECT * FROM (SELECT * FROM "test" LIMIT 1) AS "t1" INTERSECT ALL (SELECT * FROM "test2")
SELECT * FROM (SELECT * FROM "test" LIMIT 1) AS "t1" INTERSECT ALL (SELECT * FROM (SELECT * FROM "test2" ORDER BY "id" DESC) AS "t1")

func (*SelectDataset) IsPrepared 

func (sd *SelectDataset) IsPrepared() bool

func (*SelectDataset) Join 

func (sd *SelectDataset) Join(table exp.Expression, condition exp.JoinCondition) *SelectDataset

Alias to InnerJoin. See examples.

Example 
sql, _, _ := builder.From("test").Join(
	builder.T("test2"),
	builder.On(builder.Ex{"test.fkey": builder.I("test2.Id")}),
).ToSQL()
fmt.Println(sql)

sql, _, _ = builder.From("test").Join(builder.T("test2"), builder.Using("common_column")).ToSQL()
fmt.Println(sql)

sql, _, _ = builder.From("test").Join(
	builder.From("test2").Where(builder.C("amount").Gt(0)),
	builder.On(builder.I("test.fkey").Eq(builder.T("test2").Col("Id"))),
).ToSQL()
fmt.Println(sql)

sql, _, _ = builder.From("test").Join(
	builder.From("test2").Where(builder.C("amount").Gt(0)).As("t"),
	builder.On(builder.T("test").Col("fkey").Eq(builder.T("t").Col("Id"))),
).ToSQL()
fmt.Println(sql)

Output:

SELECT * FROM "test" INNER JOIN "test2" ON ("test"."fkey" = "test2"."Id")
SELECT * FROM "test" INNER JOIN "test2" USING ("common_column")
SELECT * FROM "test" INNER JOIN (SELECT * FROM "test2" WHERE ("amount" > 0)) ON ("test"."fkey" = "test2"."Id")
SELECT * FROM "test" INNER JOIN (SELECT * FROM "test2" WHERE ("amount" > 0)) AS "t" ON ("test"."fkey" = "t"."Id")

func (*SelectDataset) LeftJoin 

func (sd *SelectDataset) LeftJoin(table exp.Expression, condition exp.JoinCondition) *SelectDataset

Adds a LEFT JOIN clause. See examples.

Example 
sql, _, _ := builder.From("test").LeftJoin(
	builder.T("test2"),
	builder.On(builder.Ex{
		"test.fkey": builder.I("test2.Id"),
	}),
).ToSQL()
fmt.Println(sql)

sql, _, _ = builder.From("test").LeftJoin(
	builder.T("test2"),
	builder.Using("common_column"),
).ToSQL()
fmt.Println(sql)

sql, _, _ = builder.From("test").LeftJoin(
	builder.From("test2").Where(builder.C("amount").Gt(0)),
	builder.On(builder.I("test.fkey").Eq(builder.I("test2.Id"))),
).ToSQL()
fmt.Println(sql)

sql, _, _ = builder.From("test").LeftJoin(
	builder.From("test2").Where(builder.C("amount").Gt(0)).As("t"),
	builder.On(builder.I("test.fkey").Eq(builder.I("t.Id"))),
).ToSQL()
fmt.Println(sql)

Output:

SELECT * FROM "test" LEFT JOIN "test2" ON ("test"."fkey" = "test2"."Id")
SELECT * FROM "test" LEFT JOIN "test2" USING ("common_column")
SELECT * FROM "test" LEFT JOIN (SELECT * FROM "test2" WHERE ("amount" > 0)) ON ("test"."fkey" = "test2"."Id")
SELECT * FROM "test" LEFT JOIN (SELECT * FROM "test2" WHERE ("amount" > 0)) AS "t" ON ("test"."fkey" = "t"."Id")

func (*SelectDataset) LeftOuterJoin 

func (sd *SelectDataset) LeftOuterJoin(table exp.Expression, condition exp.JoinCondition) *SelectDataset

Adds a LEFT OUTER JOIN clause. See examples.

Example 
sql, _, _ := builder.From("test").LeftOuterJoin(
	builder.T("test2"),
	builder.On(builder.Ex{
		"test.fkey": builder.I("test2.Id"),
	}),
).ToSQL()
fmt.Println(sql)

sql, _, _ = builder.From("test").LeftOuterJoin(
	builder.T("test2"),
	builder.Using("common_column"),
).ToSQL()
fmt.Println(sql)

sql, _, _ = builder.From("test").LeftOuterJoin(
	builder.From("test2").Where(builder.C("amount").Gt(0)),
	builder.On(builder.I("test.fkey").Eq(builder.I("test2.Id"))),
).ToSQL()
fmt.Println(sql)

sql, _, _ = builder.From("test").LeftOuterJoin(
	builder.From("test2").Where(builder.C("amount").Gt(0)).As("t"),
	builder.On(builder.I("test.fkey").Eq(builder.I("t.Id"))),
).ToSQL()
fmt.Println(sql)

Output:

SELECT * FROM "test" LEFT OUTER JOIN "test2" ON ("test"."fkey" = "test2"."Id")
SELECT * FROM "test" LEFT OUTER JOIN "test2" USING ("common_column")
SELECT * FROM "test" LEFT OUTER JOIN (SELECT * FROM "test2" WHERE ("amount" > 0)) ON ("test"."fkey" = "test2"."Id")
SELECT * FROM "test" LEFT OUTER JOIN (SELECT * FROM "test2" WHERE ("amount" > 0)) AS "t" ON ("test"."fkey" = "t"."Id")

func (*SelectDataset) Limit 

func (sd *SelectDataset) Limit(limit uint) *SelectDataset

Adds a LIMIT clause. If the LIMIT is currently set it replaces it. See examples.

Example 
ds := builder.From("test").Limit(10)
sql, _, _ := ds.ToSQL()
fmt.Println(sql)

Output:

SELECT * FROM "test" LIMIT 10

func (*SelectDataset) LimitAll 

func (sd *SelectDataset) LimitAll() *SelectDataset

Adds a LIMIT ALL clause. If the LIMIT is currently set it replaces it. See examples.

Example 
ds := builder.From("test").LimitAll()
sql, _, _ := ds.ToSQL()
fmt.Println(sql)

Output:

SELECT * FROM "test" LIMIT ALL

func (*SelectDataset) NaturalFullJoin 

func (sd *SelectDataset) NaturalFullJoin(table exp.Expression) *SelectDataset

Adds a NATURAL FULL JOIN clause. See examples.

Example 
sql, _, _ := builder.From("test").NaturalFullJoin(builder.T("test2")).ToSQL()
fmt.Println(sql)

sql, _, _ = builder.From("test").NaturalFullJoin(
	builder.From("test2").Where(builder.C("amount").Gt(0)),
).ToSQL()
fmt.Println(sql)

sql, _, _ = builder.From("test").NaturalFullJoin(
	builder.From("test2").Where(builder.C("amount").Gt(0)).As("t"),
).ToSQL()
fmt.Println(sql)

Output:

SELECT * FROM "test" NATURAL FULL JOIN "test2"
SELECT * FROM "test" NATURAL FULL JOIN (SELECT * FROM "test2" WHERE ("amount" > 0))
SELECT * FROM "test" NATURAL FULL JOIN (SELECT * FROM "test2" WHERE ("amount" > 0)) AS "t"

func (*SelectDataset) NaturalJoin 

func (sd *SelectDataset) NaturalJoin(table exp.Expression) *SelectDataset

Adds a NATURAL JOIN clause. See examples.

Example 
sql, _, _ := builder.From("test").NaturalJoin(builder.T("test2")).ToSQL()
fmt.Println(sql)

sql, _, _ = builder.From("test").NaturalJoin(
	builder.From("test2").Where(builder.C("amount").Gt(0)),
).ToSQL()
fmt.Println(sql)

sql, _, _ = builder.From("test").NaturalJoin(
	builder.From("test2").Where(builder.C("amount").Gt(0)).As("t"),
).ToSQL()
fmt.Println(sql)

Output:

SELECT * FROM "test" NATURAL JOIN "test2"
SELECT * FROM "test" NATURAL JOIN (SELECT * FROM "test2" WHERE ("amount" > 0))
SELECT * FROM "test" NATURAL JOIN (SELECT * FROM "test2" WHERE ("amount" > 0)) AS "t"

func (*SelectDataset) NaturalLeftJoin 

func (sd *SelectDataset) NaturalLeftJoin(table exp.Expression) *SelectDataset

Adds a NATURAL LEFT JOIN clause. See examples.

Example 
sql, _, _ := builder.From("test").NaturalLeftJoin(builder.T("test2")).ToSQL()
fmt.Println(sql)

sql, _, _ = builder.From("test").NaturalLeftJoin(
	builder.From("test2").Where(builder.C("amount").Gt(0)),
).ToSQL()
fmt.Println(sql)

sql, _, _ = builder.From("test").NaturalLeftJoin(
	builder.From("test2").Where(builder.C("amount").Gt(0)).As("t"),
).ToSQL()
fmt.Println(sql)

Output:

SELECT * FROM "test" NATURAL LEFT JOIN "test2"
SELECT * FROM "test" NATURAL LEFT JOIN (SELECT * FROM "test2" WHERE ("amount" > 0))
SELECT * FROM "test" NATURAL LEFT JOIN (SELECT * FROM "test2" WHERE ("amount" > 0)) AS "t"

func (*SelectDataset) NaturalRightJoin 

func (sd *SelectDataset) NaturalRightJoin(table exp.Expression) *SelectDataset

Adds a NATURAL RIGHT JOIN clause. See examples.

Example 
sql, _, _ := builder.From("test").NaturalRightJoin(builder.T("test2")).ToSQL()
fmt.Println(sql)

sql, _, _ = builder.From("test").NaturalRightJoin(
	builder.From("test2").Where(builder.C("amount").Gt(0)),
).ToSQL()
fmt.Println(sql)

sql, _, _ = builder.From("test").NaturalRightJoin(
	builder.From("test2").Where(builder.C("amount").Gt(0)).As("t"),
).ToSQL()
fmt.Println(sql)

Output:

SELECT * FROM "test" NATURAL RIGHT JOIN "test2"
SELECT * FROM "test" NATURAL RIGHT JOIN (SELECT * FROM "test2" WHERE ("amount" > 0))
SELECT * FROM "test" NATURAL RIGHT JOIN (SELECT * FROM "test2" WHERE ("amount" > 0)) AS "t"

func (*SelectDataset) Offset 

func (sd *SelectDataset) Offset(offset uint) *SelectDataset

Adds an OFFSET clause. If the OFFSET is currently set it replaces it. See examples.

Example 
ds := builder.From("test").Offset(2)
sql, _, _ := ds.ToSQL()
fmt.Println(sql)

Output:

SELECT * FROM "test" OFFSET 2

func (*SelectDataset) Order 

func (sd *SelectDataset) Order(order ...exp.OrderedExpression) *SelectDataset

Adds a ORDER clause. If the ORDER is currently set it replaces it. See examples.

Example 
ds := builder.From("test").Order(builder.C("a").Asc())
sql, _, _ := ds.ToSQL()
fmt.Println(sql)

Output:

SELECT * FROM "test" ORDER BY "a" ASC
Example (CaseExpression) 
ds := builder.From("test").Order(builder.Case().When(builder.C("num").Gt(10), 0).Else(1).Asc())
sql, _, _ := ds.ToSQL()
fmt.Println(sql)

Output:

SELECT * FROM "test" ORDER BY CASE  WHEN ("num" > 10) THEN 0 ELSE 1 END ASC

func (*SelectDataset) OrderAppend 

func (sd *SelectDataset) OrderAppend(order ...exp.OrderedExpression) *SelectDataset

Adds a more columns to the current ORDER BY clause. If no order has be previously specified it is the same as calling Order. See examples.

Example 
ds := builder.From("test").Order(builder.C("a").Asc())
sql, _, _ := ds.OrderAppend(builder.C("b").Desc().NullsLast()).ToSQL()
fmt.Println(sql)

Output:

SELECT * FROM "test" ORDER BY "a" ASC, "b" DESC NULLS LAST

func (*SelectDataset) OrderPrepend 

func (sd *SelectDataset) OrderPrepend(order ...exp.OrderedExpression) *SelectDataset

Adds a more columns to the beginning of the current ORDER BY clause. If no order has be previously specified it is the same as calling Order. See examples.

Example 
ds := builder.From("test").Order(builder.C("a").Asc())
sql, _, _ := ds.OrderPrepend(builder.C("b").Desc().NullsLast()).ToSQL()
fmt.Println(sql)

Output:

SELECT * FROM "test" ORDER BY "b" DESC NULLS LAST, "a" ASC

func (*SelectDataset) Pluck 

func (sd *SelectDataset) Pluck(v any, col string) error

Generates the SELECT sql only selecting the passed in column and uses Exec#QueryRows to scan the result into a slice of primitive values.

i: A slice of primitive values

col: The column to select when generative the SQL

Example 
var lastNames []string
if err := getDB().From("builder_user").Pluck(&lastNames, "last_name"); err != nil {
	fmt.Println(err.Error())
	return
}
fmt.Printf("LastNames = %+v", lastNames)

Output:

LastNames = [Yukon Yukon Yukon Doe]

func (*SelectDataset) PluckContext 

func (sd *SelectDataset) PluckContext(ctx context.Context, v any, col string) error

Generates the SELECT sql only selecting the passed in column and uses Exec#QueryRowsContext to scan the result into a slice of primitive values.

i: A slice of primitive values

col: The column to select when generative the SQL

func (*SelectDataset) Prepared 

func (sd *SelectDataset) Prepared(prepared bool) *SelectDataset

Set the parameter interpolation behavior. See examples

prepared: If true the dataset WILL NOT interpolate the parameters.

Example 
sql, args, _ := builder.From("items").Prepared(true).Where(builder.Ex{
	"col1": "a",
	"col2": 1,
	"col3": true,
	"col4": false,
	"col5": []string{"a", "b", "c"},
}).ToSQL()
fmt.Println(sql, args)
// nolint:lll // sql statements are long

Output:

func (*SelectDataset) QueryRow 

func (sd *SelectDataset) QueryRow(v any) error

Generates the SELECT sql for this dataset and uses Exec#QueryRow to scan the result into a slice of structs

QueryRow will only select the columns that can be scanned in to the struct unless you have explicitly selected certain columns. See examples.

i: A pointer to a structs

Example 
type User struct {
	FirstName string `db:"first_name"`
	LastName  string `db:"last_name"`
}
db := getDB()
findUserByName := func(name string) {
	var user User
	ds := db.From("builder_user").Where(builder.C("first_name").Eq(name))
	err := ds.QueryRowPartial(&user)
	switch {
	case err != nil:
		fmt.Println(err.Error())
	default:
		fmt.Printf("Found user: %+v\n", user)
	}
}

findUserByName("Bob")
findUserByName("Zeb")

Output:

Found user: {FirstName:Bob LastName:Yukon}
sql: no rows in result set
Example (WithJoinAutoSelect)

In this example we create a new struct that has two structs that represent two table the User and Role fields are tagged with the table name 

type Role struct {
	UserID uint64 `db:"user_id"`
	Name   string `db:"name"`
}
type User struct {
	ID        uint64 `db:"id"`
	FirstName string `db:"first_name"`
	LastName  string `db:"last_name"`
}
type UserAndRole struct {
	User // tag as the "builder_user" table
	Role // tag as "user_role" table
}
db := getDB()
findUserAndRoleByName := func(name string) {
	var userAndRole UserAndRole
	ds := db.
		From("builder_user").
		Join(
			builder.T("user_role"),
			builder.On(builder.I("builder_user.id").Eq(builder.I("user_role.user_id"))),
		).
		Where(builder.C("first_name").Eq(name))
	err := ds.QueryRow(&userAndRole)
	switch {
	case err != nil:
		fmt.Println(err.Error())
	default:
		fmt.Printf("Found user and role: %+v\n", userAndRole)
	}
}

findUserAndRoleByName("Bob")
findUserAndRoleByName("Zeb")

Output:

Found user and role: {User:{ID:1 FirstName:Bob LastName:Yukon} Role:{UserID:1 Name:Admin}}
sql: no rows in result set

func (*SelectDataset) QueryRowCtx 

func (sd *SelectDataset) QueryRowCtx(ctx context.Context, v any) error

Generates the SELECT sql for this dataset and uses Exec#QueryRowContext to scan the result into a slice of structs

QueryRowContext will only select the columns that can be scanned in to the struct unless you have explicitly selected certain columns. See examples.

i: A pointer to a structs

func (*SelectDataset) QueryRowPartial 

func (sd *SelectDataset) QueryRowPartial(v any) error

Generates the SELECT sql for this dataset and uses Exec#QueryRow to scan the result into a slice of structs

QueryRow will only select the columns that can be scanned in to the struct unless you have explicitly selected certain columns. See examples.

i: A pointer to a structs

Example 
db := getDB()
findUserIDByName := func(name string) {
	var id int64
	ds := db.From("builder_user").
		Select("id").
		Where(builder.C("first_name").Eq(name))

	err := ds.QueryRow(&id)
	switch {
	case err != nil:
		fmt.Println(err.Error())
	default:
		fmt.Printf("\nFound userId: %+v\n", id)
	}
}

findUserIDByName("Bob")
findUserIDByName("Zeb")

Output:

Found userId: 1
sql: no rows in result set

func (*SelectDataset) QueryRowPartialCtx 

func (sd *SelectDataset) QueryRowPartialCtx(ctx context.Context, v any) error

Generates the SELECT sql for this dataset and uses Exec#QueryRowContext to scan the result into a slice of structs

QueryRowContext will only select the columns that can be scanned in to the struct unless you have explicitly selected certain columns. See examples.

i: A pointer to a structs

func (*SelectDataset) QueryRows 

func (sd *SelectDataset) QueryRows(v any) error

Generates the SELECT sql for this dataset and uses Exec#QueryRows to scan the results into a slice of structs.

QueryRows will only select the columns that can be scanned in to the struct unless you have explicitly selected certain columns. See examples.

i: A pointer to a slice of structs

Example 
type User struct {
	FirstName string `db:"first_name"`
	LastName  string `db:"last_name"`
}
db := getDB()
var users []User
if err := db.From("builder_user").QueryRows(&users); err != nil {
	fmt.Println(err.Error())
	return
}
fmt.Printf("\n%+v", users)

users = users[0:0]
if err := db.From("builder_user").Select("first_name").QueryRowsPartial(&users); err != nil {
	fmt.Println(err.Error())
	return
}
fmt.Printf("\n%+v", users)

Output:

[{FirstName:Bob LastName:Yukon} {FirstName:Sally LastName:Yukon} {FirstName:Vinita LastName:Yukon} {FirstName:John LastName:Doe}]
[{FirstName:Bob LastName:} {FirstName:Sally LastName:} {FirstName:Vinita LastName:} {FirstName:John LastName:}]
Example (Prepared) 
type User struct {
	FirstName string `db:"first_name"`
	LastName  string `db:"last_name"`
}
db := getDB()

ds := db.From("builder_user").
	Prepared(true).
	Where(builder.Ex{
		"last_name": "Yukon",
	})

var users []User
if err := ds.QueryRows(&users); err != nil {
	fmt.Println(err.Error())
	return
}
fmt.Printf("\n%+v", users)

Output:

[{FirstName:Bob LastName:Yukon} {FirstName:Sally LastName:Yukon} {FirstName:Vinita LastName:Yukon}]
Example (WithJoinAutoSelect)

In this example we create a new struct that has two structs that represent two table the User and Role fields are tagged with the table name 

type Role struct {
	UserID uint64 `db:"user_id"`
	Name   string `db:"name"`
}
type User struct {
	ID        uint64 `db:"id"`
	FirstName string `db:"first_name"`
	LastName  string `db:"last_name"`
}
type UserAndRole struct {
	User // tag as the "builder_user" table
	Role // tag as "user_role" table
}
db := getDB()

ds := db.
	From("builder_user").
	Join(builder.T("user_role"), builder.On(builder.I("builder_user.id").Eq(builder.I("user_role.user_id"))))
var users []UserAndRole
// query rows will auto build the
err := ds.QueryRows(&users)
if err != nil {
	fmt.Println(err.Error())
	return
}
for _, u := range users {
	fmt.Printf("\n%+v", u)
}

Output:

{User:{ID:1 FirstName:Bob LastName:Yukon} Role:{UserID:1 Name:Admin}}
{User:{ID:2 FirstName:Sally LastName:Yukon} Role:{UserID:2 Name:Manager}}
{User:{ID:3 FirstName:Vinita LastName:Yukon} Role:{UserID:3 Name:Manager}}
{User:{ID:4 FirstName:John LastName:Doe} Role:{UserID:4 Name:User}}

func (*SelectDataset) QueryRowsCtx 

func (sd *SelectDataset) QueryRowsCtx(ctx context.Context, v any) error

Generates the SELECT sql for this dataset and uses Exec#QueryRowsContext to scan the results into a slice of structs.

QueryRowsContext will only select the columns that can be scanned in to the struct unless you have explicitly selected certain columns. See examples.

i: A pointer to a slice of structs

func (*SelectDataset) QueryRowsPartial 

func (sd *SelectDataset) QueryRowsPartial(v any) error

Generates the SELECT sql for this dataset and uses Exec#QueryRows to scan the results into a slice of structs.

QueryRows will only select the columns that can be scanned in to the struct unless you have explicitly selected certain columns. See examples.

i: A pointer to a slice of structs

Example 
var ids []int64
if err := getDB().From("builder_user").Select("id").QueryRows(&ids); err != nil {
	fmt.Println(err.Error())
	return
}
fmt.Printf("UserIds = %+v", ids)

Output:

UserIds = [1 2 3 4]

func (*SelectDataset) QueryRowsPartialCtx 

func (sd *SelectDataset) QueryRowsPartialCtx(ctx context.Context, v any) error

Generates the SELECT sql for this dataset and uses Exec#QueryRowsContext to scan the results into a slice of structs.

QueryRowsContext will only select the columns that can be scanned in to the struct unless you have explicitly selected certain columns. See examples.

i: A pointer to a slice of structs

func (*SelectDataset) ReturnsColumns 

func (sd *SelectDataset) ReturnsColumns() bool

func (*SelectDataset) RightJoin 

func (sd *SelectDataset) RightJoin(table exp.Expression, condition exp.JoinCondition) *SelectDataset

Adds a RIGHT JOIN clause. See examples.

Example 
sql, _, _ := builder.From("test").RightJoin(
	builder.T("test2"),
	builder.On(builder.Ex{
		"test.fkey": builder.I("test2.Id"),
	}),
).ToSQL()
fmt.Println(sql)

sql, _, _ = builder.From("test").RightJoin(
	builder.T("test2"),
	builder.Using("common_column"),
).ToSQL()
fmt.Println(sql)

sql, _, _ = builder.From("test").RightJoin(
	builder.From("test2").Where(builder.C("amount").Gt(0)),
	builder.On(builder.I("test.fkey").Eq(builder.I("test2.Id"))),
).ToSQL()
fmt.Println(sql)

sql, _, _ = builder.From("test").RightJoin(
	builder.From("test2").Where(builder.C("amount").Gt(0)).As("t"),
	builder.On(builder.I("test.fkey").Eq(builder.I("t.Id"))),
).ToSQL()
fmt.Println(sql)

Output:

SELECT * FROM "test" RIGHT JOIN "test2" ON ("test"."fkey" = "test2"."Id")
SELECT * FROM "test" RIGHT JOIN "test2" USING ("common_column")
SELECT * FROM "test" RIGHT JOIN (SELECT * FROM "test2" WHERE ("amount" > 0)) ON ("test"."fkey" = "test2"."Id")
SELECT * FROM "test" RIGHT JOIN (SELECT * FROM "test2" WHERE ("amount" > 0)) AS "t" ON ("test"."fkey" = "t"."Id")

func (*SelectDataset) RightOuterJoin 

func (sd *SelectDataset) RightOuterJoin(table exp.Expression, condition exp.JoinCondition) *SelectDataset

Adds a RIGHT OUTER JOIN clause. See examples.

Example 
sql, _, _ := builder.From("test").RightOuterJoin(
	builder.T("test2"),
	builder.On(builder.Ex{
		"test.fkey": builder.I("test2.Id"),
	}),
).ToSQL()
fmt.Println(sql)

sql, _, _ = builder.From("test").RightOuterJoin(
	builder.T("test2"),
	builder.Using("common_column"),
).ToSQL()
fmt.Println(sql)

sql, _, _ = builder.From("test").RightOuterJoin(
	builder.From("test2").Where(builder.C("amount").Gt(0)),
	builder.On(builder.I("test.fkey").Eq(builder.I("test2.Id"))),
).ToSQL()
fmt.Println(sql)

sql, _, _ = builder.From("test").RightOuterJoin(
	builder.From("test2").Where(builder.C("amount").Gt(0)).As("t"),
	builder.On(builder.I("test.fkey").Eq(builder.I("t.Id"))),
).ToSQL()
fmt.Println(sql)

Output:

SELECT * FROM "test" RIGHT OUTER JOIN "test2" ON ("test"."fkey" = "test2"."Id")
SELECT * FROM "test" RIGHT OUTER JOIN "test2" USING ("common_column")
SELECT * FROM "test" RIGHT OUTER JOIN (SELECT * FROM "test2" WHERE ("amount" > 0)) ON ("test"."fkey" = "test2"."Id")
SELECT * FROM "test" RIGHT OUTER JOIN (SELECT * FROM "test2" WHERE ("amount" > 0)) AS "t" ON ("test"."fkey" = "t"."Id")

func (*SelectDataset) Select 

func (sd *SelectDataset) Select(selects ...any) *SelectDataset

Adds columns to the SELECT clause. See examples You can pass in the following. 

string: Will automatically be turned into an identifier
Dataset: Will use the SQL generated from that Dataset. If the dataset is aliased it will use that alias as the
column name.
LiteralExpression: (See Literal) Will use the literal SQL
SQLFunction: (See Func, MIN, MAX, COUNT....)
Struct: If passing in an instance of a struct, we will parse the struct for the column names to select.
See examples
Example 
sql, _, _ := builder.From("test").Select("a", "b", "c").ToSQL()
fmt.Println(sql)

Output:

SELECT "a", "b", "c" FROM "test"
Example (WithAliasedDataset) 
ds := builder.From("test")
fromDs := ds.Select("age").Where(builder.C("age").Gt(10))
sql, _, _ := ds.From().Select(fromDs.As("ages")).ToSQL()
fmt.Println(sql)

Output:

SELECT (SELECT "age" FROM "test" WHERE ("age" > 10)) AS "ages"
Example (WithDataset) 
ds := builder.From("test")
fromDs := ds.Select("age").Where(builder.C("age").Gt(10))
sql, _, _ := ds.From().Select(fromDs).ToSQL()
fmt.Println(sql)

Output:

SELECT (SELECT "age" FROM "test" WHERE ("age" > 10))
Example (WithLiteral) 
sql, _, _ := builder.From("test").Select(builder.L("a + b").As("sum")).ToSQL()
fmt.Println(sql)

Output:

SELECT a + b AS "sum" FROM "test"
Example (WithSQLFunctionExpression) 
sql, _, _ := builder.From("test").Select(
	builder.COUNT("*").As("age_count"),
	builder.MAX("age").As("max_age"),
	builder.AVG("age").As("avg_age"),
).ToSQL()
fmt.Println(sql)

Output:

SELECT COUNT(*) AS "age_count", MAX("age") AS "max_age", AVG("age") AS "avg_age" FROM "test"
Example (WithStruct) 
ds := builder.From("test")

type myStruct struct {
	Name         string
	Address      string `db:"address"`
	EmailAddress string `db:"email_address"`
}

// Pass with pointer
sql, _, _ := ds.Select(&myStruct{}).ToSQL()
fmt.Println(sql)

// Pass instance of
sql, _, _ = ds.Select(myStruct{}).ToSQL()
fmt.Println(sql)

type myStruct2 struct {
	myStruct
	Zipcode string `db:"zipcode"`
}

// Pass pointer to struct with embedded struct
sql, _, _ = ds.Select(&myStruct2{}).ToSQL()
fmt.Println(sql)

// Pass instance of struct with embedded struct
sql, _, _ = ds.Select(myStruct2{}).ToSQL()
fmt.Println(sql)

var myStructs []myStruct

// Pass slice of structs, will only select columns from underlying type
sql, _, _ = ds.Select(myStructs).ToSQL()
fmt.Println(sql)

Output:

SELECT "address", "email_address", "name" FROM "test"
SELECT "address", "email_address", "name" FROM "test"
SELECT "address", "email_address", "name", "zipcode" FROM "test"
SELECT "address", "email_address", "name", "zipcode" FROM "test"
SELECT "address", "email_address", "name" FROM "test"

func (*SelectDataset) SelectAppend 

func (sd *SelectDataset) SelectAppend(selects ...any) *SelectDataset

Adds columns to the SELECT clause. See examples You can pass in the following. 

string: Will automatically be turned into an identifier
Dataset: Will use the SQL generated from that Dataset. If the dataset is aliased it will use that alias as the
column name.
LiteralExpression: (See Literal) Will use the literal SQL
SQLFunction: (See Func, MIN, MAX, COUNT....)
Example 
ds := builder.From("test").Select("a", "b")
sql, _, _ := ds.SelectAppend("c").ToSQL()
fmt.Println(sql)
ds = builder.From("test").Select("a", "b").Distinct()
sql, _, _ = ds.SelectAppend("c").ToSQL()
fmt.Println(sql)

Output:

SELECT "a", "b", "c" FROM "test"
SELECT DISTINCT "a", "b", "c" FROM "test"

func (*SelectDataset) SelectDistinct deprecated 

func (sd *SelectDataset) SelectDistinct(selects ...any) *SelectDataset

Adds columns to the SELECT DISTINCT clause. See examples You can pass in the following. 

string: Will automatically be turned into an identifier
Dataset: Will use the SQL generated from that Dataset. If the dataset is aliased it will use that alias as the
column name.
LiteralExpression: (See Literal) Will use the literal SQL
SQLFunction: (See Func, MIN, MAX, COUNT....)
Struct: If passing in an instance of a struct, we will parse the struct for the column names to select.
See examples

Deprecated: Use Distinct() instead.

func (*SelectDataset) SetDialect 

func (sd *SelectDataset) SetDialect(dialect SQLDialect) *SelectDataset

Returns the current adapter on the dataset

func (*SelectDataset) SetError 

func (sd *SelectDataset) SetError(err error) *SelectDataset

Set an error on the dataset if one has not already been set. This error will be returned by a future call to Error or as part of ToSQL. This can be used by end users to record errors while building up queries without having to track those separately.

func (*SelectDataset) ToSQL 

func (sd *SelectDataset) ToSQL() (sql string, params []any, err error)

Generates a SELECT sql statement, if Prepared has been called with true then the parameters will not be interpolated. See examples.

Errors:

  • There is an error generating the SQL
Example 
sql, args, _ := builder.From("items").Where(builder.Ex{"a": 1}).ToSQL()
fmt.Println(sql, args)

Output:

SELECT * FROM "items" WHERE ("a" = 1) []
Example (Prepared) 
sql, args, _ := builder.From("items").Where(builder.Ex{"a": 1}).Prepared(true).ToSQL()
fmt.Println(sql, args)

Output:

SELECT * FROM "items" WHERE ("a" = ?) [1]

func (*SelectDataset) Truncate 

func (sd *SelectDataset) Truncate() *TruncateDataset

Creates a new TruncateDataset using the FROM of this dataset.

Example 
sql, args, _ := builder.From("items").Truncate().ToSQL()
fmt.Println(sql, args)

Output:

TRUNCATE "items" []

func (*SelectDataset) Union 

func (sd *SelectDataset) Union(other *SelectDataset) *SelectDataset

Creates an UNION statement with another dataset. If this or the other dataset has a limit or offset it will use that dataset as a subselect in the FROM clause. See examples.

Example 
sql, _, _ := builder.From("test").
	Union(builder.From("test2")).
	ToSQL()
fmt.Println(sql)

sql, _, _ = builder.From("test").
	Limit(1).
	Union(builder.From("test2")).
	ToSQL()
fmt.Println(sql)

sql, _, _ = builder.From("test").
	Limit(1).
	Union(builder.From("test2").
		Order(builder.C("id").Desc())).
	ToSQL()
fmt.Println(sql)

Output:

SELECT * FROM "test" UNION (SELECT * FROM "test2")
SELECT * FROM (SELECT * FROM "test" LIMIT 1) AS "t1" UNION (SELECT * FROM "test2")
SELECT * FROM (SELECT * FROM "test" LIMIT 1) AS "t1" UNION (SELECT * FROM (SELECT * FROM "test2" ORDER BY "id" DESC) AS "t1")

func (*SelectDataset) UnionAll 

func (sd *SelectDataset) UnionAll(other *SelectDataset) *SelectDataset

Creates an UNION ALL statement with another dataset. If this or the other dataset has a limit or offset it will use that dataset as a subselect in the FROM clause. See examples.

Example 
sql, _, _ := builder.From("test").
	UnionAll(builder.From("test2")).
	ToSQL()
fmt.Println(sql)
sql, _, _ = builder.From("test").
	Limit(1).
	UnionAll(builder.From("test2")).
	ToSQL()
fmt.Println(sql)
sql, _, _ = builder.From("test").
	Limit(1).
	UnionAll(builder.From("test2").
		Order(builder.C("id").Desc())).
	ToSQL()
fmt.Println(sql)

Output:

SELECT * FROM "test" UNION ALL (SELECT * FROM "test2")
SELECT * FROM (SELECT * FROM "test" LIMIT 1) AS "t1" UNION ALL (SELECT * FROM "test2")
SELECT * FROM (SELECT * FROM "test" LIMIT 1) AS "t1" UNION ALL (SELECT * FROM (SELECT * FROM "test2" ORDER BY "id" DESC) AS "t1")

func (*SelectDataset) Update 

func (sd *SelectDataset) Update() *UpdateDataset

Creates a new UpdateDataset using the FROM of this dataset. This method will also copy over the `WITH`, `WHERE`, `ORDER , and `LIMIT`

Example 
type item struct {
	Address string `db:"address"`
	Name    string `db:"name"`
}
sql, args, _ := builder.From("items").Update().Set(
	item{Name: "Test", Address: "111 Test Addr"},
).ToSQL()
fmt.Println(sql, args)

sql, args, _ = builder.From("items").Update().Set(
	builder.Record{"name": "Test", "address": "111 Test Addr"},
).ToSQL()
fmt.Println(sql, args)

sql, args, _ = builder.From("items").Update().Set(
	map[string]any{"name": "Test", "address": "111 Test Addr"},
).ToSQL()
fmt.Println(sql, args)

Output:

UPDATE "items" SET "address"='111 Test Addr',"name"='Test' []
UPDATE "items" SET "address"='111 Test Addr',"name"='Test' []
UPDATE "items" SET "address"='111 Test Addr',"name"='Test' []

func (*SelectDataset) Where 

func (sd *SelectDataset) Where(expressions ...exp.Expression) *SelectDataset

Adds a WHERE clause. See examples.

Example 
// By default everything is anded together
sql, _, _ := builder.From("test").Where(builder.Ex{
	"a": builder.Op{"gt": 10},
	"b": builder.Op{"lt": 10},
	"c": nil,
	"d": []string{"a", "b", "c"},
}).ToSQL()
fmt.Println(sql)
// You can use ExOr to get ORed expressions together
sql, _, _ = builder.From("test").Where(builder.ExOr{
	"a": builder.Op{"gt": 10},
	"b": builder.Op{"lt": 10},
	"c": nil,
	"d": []string{"a", "b", "c"},
}).ToSQL()
fmt.Println(sql)
// You can use Or with Ex to Or multiple Ex maps together
sql, _, _ = builder.From("test").Where(
	builder.Or(
		builder.Ex{
			"a": builder.Op{"gt": 10},
			"b": builder.Op{"lt": 10},
		},
		builder.Ex{
			"c": nil,
			"d": []string{"a", "b", "c"},
		},
	),
).ToSQL()
fmt.Println(sql)
// By default everything is anded together
sql, _, _ = builder.From("test").Where(
	builder.C("a").Gt(10),
	builder.C("b").Lt(10),
	builder.C("c").IsNull(),
	builder.C("d").In("a", "b", "c"),
).ToSQL()
fmt.Println(sql)
// You can use a combination of Ors and Ands
sql, _, _ = builder.From("test").Where(
	builder.Or(
		builder.C("a").Gt(10),
		builder.And(
			builder.C("b").Lt(10),
			builder.C("c").IsNull(),
		),
	),
).ToSQL()
fmt.Println(sql)

Output:

SELECT * FROM "test" WHERE (("a" > 10) AND ("b" < 10) AND ("c" IS NULL) AND ("d" IN ('a', 'b', 'c')))
SELECT * FROM "test" WHERE (("a" > 10) OR ("b" < 10) OR ("c" IS NULL) OR ("d" IN ('a', 'b', 'c')))
SELECT * FROM "test" WHERE ((("a" > 10) AND ("b" < 10)) OR (("c" IS NULL) AND ("d" IN ('a', 'b', 'c'))))
SELECT * FROM "test" WHERE (("a" > 10) AND ("b" < 10) AND ("c" IS NULL) AND ("d" IN ('a', 'b', 'c')))
SELECT * FROM "test" WHERE (("a" > 10) OR (("b" < 10) AND ("c" IS NULL)))
Example (Prepared) 
// By default everything is anded together
sql, args, _ := builder.From("test").Prepared(true).Where(builder.Ex{
	"a": builder.Op{"gt": 10},
	"b": builder.Op{"lt": 10},
	"c": nil,
	"d": []string{"a", "b", "c"},
}).ToSQL()
fmt.Println(sql, args)
// You can use ExOr to get ORed expressions together
sql, args, _ = builder.From("test").Prepared(true).Where(builder.ExOr{
	"a": builder.Op{"gt": 10},
	"b": builder.Op{"lt": 10},
	"c": nil,
	"d": []string{"a", "b", "c"},
}).ToSQL()
fmt.Println(sql, args)
// You can use Or with Ex to Or multiple Ex maps together
sql, args, _ = builder.From("test").Prepared(true).Where(
	builder.Or(
		builder.Ex{
			"a": builder.Op{"gt": 10},
			"b": builder.Op{"lt": 10},
		},
		builder.Ex{
			"c": nil,
			"d": []string{"a", "b", "c"},
		},
	),
).ToSQL()
fmt.Println(sql, args)
// By default everything is anded together
sql, args, _ = builder.From("test").Prepared(true).Where(
	builder.C("a").Gt(10),
	builder.C("b").Lt(10),
	builder.C("c").IsNull(),
	builder.C("d").In("a", "b", "c"),
).ToSQL()
fmt.Println(sql, args)
// You can use a combination of Ors and Ands
sql, args, _ = builder.From("test").Prepared(true).Where(
	builder.Or(
		builder.C("a").Gt(10),
		builder.And(
			builder.C("b").Lt(10),
			builder.C("c").IsNull(),
		),
	),
).ToSQL()
fmt.Println(sql, args)

Output:

SELECT * FROM "test" WHERE (("a" > ?) AND ("b" < ?) AND ("c" IS NULL) AND ("d" IN (?, ?, ?))) [10 10 a b c]
SELECT * FROM "test" WHERE (("a" > ?) OR ("b" < ?) OR ("c" IS NULL) OR ("d" IN (?, ?, ?))) [10 10 a b c]
SELECT * FROM "test" WHERE ((("a" > ?) AND ("b" < ?)) OR (("c" IS NULL) AND ("d" IN (?, ?, ?)))) [10 10 a b c]
SELECT * FROM "test" WHERE (("a" > ?) AND ("b" < ?) AND ("c" IS NULL) AND ("d" IN (?, ?, ?))) [10 10 a b c]
SELECT * FROM "test" WHERE (("a" > ?) OR (("b" < ?) AND ("c" IS NULL))) [10 10]

func (*SelectDataset) Window 

func (sd *SelectDataset) Window(ws ...exp.WindowExpression) *SelectDataset

Sets the WINDOW clauses

Example 
ds := builder.From("test").
	Select(builder.ROW_NUMBER().Over(builder.W().PartitionBy("a").OrderBy(builder.I("b").Asc())))
query, args, _ := ds.ToSQL()
fmt.Println(query, args)

ds = builder.From("test").
	Select(builder.ROW_NUMBER().OverName(builder.I("w"))).
	Window(builder.W("w").PartitionBy("a").OrderBy(builder.I("b").Asc()))
query, args, _ = ds.ToSQL()
fmt.Println(query, args)

ds = builder.From("test").
	Select(builder.ROW_NUMBER().OverName(builder.I("w1"))).
	Window(
		builder.W("w1").PartitionBy("a"),
		builder.W("w").Inherit("w1").OrderBy(builder.I("b").Asc()),
	)
query, args, _ = ds.ToSQL()
fmt.Println(query, args)

ds = builder.From("test").
	Select(builder.ROW_NUMBER().Over(builder.W().Inherit("w").OrderBy("b"))).
	Window(builder.W("w").PartitionBy("a"))
query, args, _ = ds.ToSQL()
fmt.Println(query, args)
// Output
// SELECT ROW_NUMBER() OVER (PARTITION BY "a" ORDER BY "b" ASC) FROM "test" []
// SELECT ROW_NUMBER() OVER "w" FROM "test" WINDOW "w" AS (PARTITION BY "a" ORDER BY "b" ASC) []
// SELECT ROW_NUMBER() OVER "w" FROM "test" WINDOW "w1" AS (PARTITION BY "a"), "w" AS ("w1" ORDER BY "b" ASC) []
// SELECT ROW_NUMBER() OVER ("w" ORDER BY "b") FROM "test" WINDOW "w" AS (PARTITION BY "a") []

Output:

func (*SelectDataset) WindowAppend 

func (sd *SelectDataset) WindowAppend(ws ...exp.WindowExpression) *SelectDataset

Sets the WINDOW clauses

func (*SelectDataset) With 

func (sd *SelectDataset) With(name string, subquery exp.Expression) *SelectDataset

Creates a WITH clause for a common table expression (CTE).

The name will be available to SELECT from in the associated query; and can optionally contain a list of column names "name(col1, col2, col3)".

The name will refer to the results of the specified subquery.

Example 
sql, _, _ := builder.From("one").
	With("one", builder.From().Select(builder.L("1"))).
	Select(builder.Star()).
	ToSQL()
fmt.Println(sql)
sql, _, _ = builder.From("derived").
	With("intermed", builder.From("test").Select(builder.Star()).Where(builder.C("x").Gte(5))).
	With("derived", builder.From("intermed").Select(builder.Star()).Where(builder.C("x").Lt(10))).
	Select(builder.Star()).
	ToSQL()
fmt.Println(sql)
sql, _, _ = builder.From("multi").
	With("multi(x,y)", builder.From().Select(builder.L("1"), builder.L("2"))).
	Select(builder.C("x"), builder.C("y")).
	ToSQL()
fmt.Println(sql)

Output:

WITH one AS (SELECT 1) SELECT * FROM "one"
WITH intermed AS (SELECT * FROM "test" WHERE ("x" >= 5)), derived AS (SELECT * FROM "intermed" WHERE ("x" < 10)) SELECT * FROM "derived"
WITH multi(x,y) AS (SELECT 1, 2) SELECT "x", "y" FROM "multi"
Example (DeleteDataset) 
deleteDs := builder.Delete("foo").Where(builder.Ex{"bar": "baz"}).Returning("id")

ds := builder.From("bar").
	With("del", deleteDs).
	Select("bar_name").
	Where(builder.Ex{"bar.user_id": builder.I("del.user_id")})

sql, _, _ := ds.ToSQL()
fmt.Println(sql)

sql, args, _ := ds.Prepared(true).ToSQL()
fmt.Println(sql, args)

Output:

WITH del AS (DELETE FROM "foo" WHERE ("bar" = 'baz') RETURNING "id") SELECT "bar_name" FROM "bar" WHERE ("bar"."user_id" = "del"."user_id")
WITH del AS (DELETE FROM "foo" WHERE ("bar" = ?) RETURNING "id") SELECT "bar_name" FROM "bar" WHERE ("bar"."user_id" = "del"."user_id") [baz]
Example (InsertDataset) 
insertDs := builder.Insert("foo").Rows(builder.Record{"user_id": 10}).Returning("id")

ds := builder.From("bar").
	With("ins", insertDs).
	Select("bar_name").
	Where(builder.Ex{"bar.user_id": builder.I("ins.user_id")})

sql, _, _ := ds.ToSQL()
fmt.Println(sql)

sql, args, _ := ds.Prepared(true).ToSQL()
fmt.Println(sql, args)

Output:

WITH ins AS (INSERT INTO "foo" ("user_id") VALUES (10) RETURNING "id") SELECT "bar_name" FROM "bar" WHERE ("bar"."user_id" = "ins"."user_id")
WITH ins AS (INSERT INTO "foo" ("user_id") VALUES (?) RETURNING "id") SELECT "bar_name" FROM "bar" WHERE ("bar"."user_id" = "ins"."user_id") [10]
Example (UpdateDataset) 
updateDs := builder.Update("foo").Set(builder.Record{"bar": "baz"}).Returning("id")

ds := builder.From("bar").
	With("upd", updateDs).
	Select("bar_name").
	Where(builder.Ex{"bar.user_id": builder.I("upd.user_id")})

sql, _, _ := ds.ToSQL()
fmt.Println(sql)

sql, args, _ := ds.Prepared(true).ToSQL()
fmt.Println(sql, args)

Output:

WITH upd AS (UPDATE "foo" SET "bar"='baz' RETURNING "id") SELECT "bar_name" FROM "bar" WHERE ("bar"."user_id" = "upd"."user_id")
WITH upd AS (UPDATE "foo" SET "bar"=? RETURNING "id") SELECT "bar_name" FROM "bar" WHERE ("bar"."user_id" = "upd"."user_id") [baz]

func (*SelectDataset) WithDialect 

func (sd *SelectDataset) WithDialect(dl string) *SelectDataset

Sets the adapter used to serialize values and create the SQL statement

func (*SelectDataset) WithRecursive 

func (sd *SelectDataset) WithRecursive(name string, subquery exp.Expression) *SelectDataset

Creates a WITH RECURSIVE clause for a common table expression (CTE)

The name will be available to SELECT from in the associated query; and must contain a list of column names "name(col1, col2, col3)" for a recursive clause.

The name will refer to the results of the specified subquery. The subquery for a recursive query will always end with a UNION or UNION ALL with a clause that refers to the CTE by name.

Example 
sql, _, _ := builder.From("nums").
	WithRecursive("nums(x)",
		builder.From().Select(builder.L("1")).
			UnionAll(builder.From("nums").
				Select(builder.L("x+1")).Where(builder.C("x").Lt(5)))).
	ToSQL()
fmt.Println(sql)

Output:

WITH RECURSIVE nums(x) AS (SELECT 1 UNION ALL (SELECT x+1 FROM "nums" WHERE ("x" < 5))) SELECT * FROM "nums"

type TruncateDataset 

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

func Truncate 

func Truncate(table ...any) *TruncateDataset

func (*TruncateDataset) Cascade 

func (td *TruncateDataset) Cascade() *TruncateDataset

Adds a CASCADE clause

func (*TruncateDataset) Clone 

func (td *TruncateDataset) Clone() exp.Expression

Clones the dataset

func (*TruncateDataset) Dialect 

func (td *TruncateDataset) Dialect() SQLDialect

Returns the current adapter on the dataset

func (*TruncateDataset) Error 

func (td *TruncateDataset) Error() error

Get any error that has been set or nil if no error has been set.

func (*TruncateDataset) Expression 

func (td *TruncateDataset) Expression() exp.Expression

func (*TruncateDataset) GetClauses 

func (td *TruncateDataset) GetClauses() exp.TruncateClauses

Returns the current clauses on the dataset.

func (*TruncateDataset) Identity 

func (td *TruncateDataset) Identity(identity string) *TruncateDataset

Add a IDENTITY clause (e.g. RESTART)

func (*TruncateDataset) IsPrepared 

func (td *TruncateDataset) IsPrepared() bool

func (*TruncateDataset) NoCascade 

func (td *TruncateDataset) NoCascade() *TruncateDataset

Clears the CASCADE clause

func (*TruncateDataset) NoRestrict 

func (td *TruncateDataset) NoRestrict() *TruncateDataset

Clears the RESTRICT clause

func (*TruncateDataset) Prepared 

func (td *TruncateDataset) Prepared(prepared bool) *TruncateDataset

Set the parameter interpolation behavior. See examples

prepared: If true the dataset WILL NOT interpolate the parameters.

func (*TruncateDataset) Restrict 

func (td *TruncateDataset) Restrict() *TruncateDataset

Adds a RESTRICT clause

func (*TruncateDataset) SetDialect 

func (td *TruncateDataset) SetDialect(dialect SQLDialect) *TruncateDataset

Returns the current adapter on the dataset

func (*TruncateDataset) SetError 

func (td *TruncateDataset) SetError(err error) *TruncateDataset

Set an error on the dataset if one has not already been set. This error will be returned by a future call to Error or as part of ToSQL. This can be used by end users to record errors while building up queries without having to track those separately.

func (*TruncateDataset) Table 

func (td *TruncateDataset) Table(table ...any) *TruncateDataset

Adds a FROM clause. This return a new dataset with the original sources replaced. See examples. You can pass in the following. 

string: Will automatically be turned into an identifier
IdentifierExpression
LiteralExpression: (See Literal) Will use the literal SQL

func (*TruncateDataset) ToSQL 

func (td *TruncateDataset) ToSQL() (sql string, params []any, err error)

Generates a TRUNCATE sql statement, if Prepared has been called with true then the parameters will not be interpolated. See examples.

Errors:

  • There is an error generating the SQL

func (*TruncateDataset) Truncate 

func (td *TruncateDataset) Truncate() (sql.Result, error)

func (*TruncateDataset) TruncateCtx 

func (td *TruncateDataset) TruncateCtx(ctx context.Context) (sql.Result, error)

func (*TruncateDataset) WithDialect 

func (td *TruncateDataset) WithDialect(dl string) *TruncateDataset

Sets the adapter used to serialize values and create the SQL statement

type TruncateOptions 

type TruncateOptions = exp.TruncateOptions

Options to use when generating a TRUNCATE statement

type TxDatabase 

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

A wrapper around a sql.Tx and works the same way as Database

func NewTx 

func NewTx(dialect string, session sqlx.Session) *TxDatabase

Creates a new TxDatabase

func (*TxDatabase) Delete 

func (td *TxDatabase) Delete(table any) *DeleteDataset

func (*TxDatabase) Dialect 

func (td *TxDatabase) Dialect() string

returns this databases dialect

func (*TxDatabase) Exec 

func (td *TxDatabase) Exec(query string, args ...any) (sql.Result, error)

See Database#Exec

func (*TxDatabase) ExecCtx 

func (td *TxDatabase) ExecCtx(ctx context.Context, query string, args ...any) (sql.Result, error)

See Database#ExecContext

func (*TxDatabase) From 

func (td *TxDatabase) From(cols ...any) *SelectDataset

Creates a new Dataset for querying a Database.

func (*TxDatabase) Insert 

func (td *TxDatabase) Insert(table any) *InsertDataset

func (*TxDatabase) Logger 

func (td *TxDatabase) Logger(logger logx.Logger)

Sets the logger

func (*TxDatabase) Prepare 

func (td *TxDatabase) Prepare(query string) (sqlx.StmtSession, error)

See Database#Prepare

func (*TxDatabase) PrepareCtx 

func (td *TxDatabase) PrepareCtx(ctx context.Context, query string) (sqlx.StmtSession, error)

See Database#PrepareContext

func (*TxDatabase) QueryRow 

func (td *TxDatabase) QueryRow(v any, query string, args ...any) error

See Database#Query

func (*TxDatabase) QueryRowCtx 

func (td *TxDatabase) QueryRowCtx(ctx context.Context, v any, query string, args ...any) error

See Database#QueryContext

func (*TxDatabase) QueryRowPartial 

func (td *TxDatabase) QueryRowPartial(v any, query string, args ...any) error

See Database#Query

func (*TxDatabase) QueryRowPartialCtx 

func (td *TxDatabase) QueryRowPartialCtx(ctx context.Context, v any, query string, args ...any) error

See Database#QueryContext

func (*TxDatabase) QueryRows 

func (td *TxDatabase) QueryRows(v any, query string, args ...any) error

See Database#Query

func (*TxDatabase) QueryRowsCtx 

func (td *TxDatabase) QueryRowsCtx(ctx context.Context, v any, query string, args ...any) error

See Database#QueryContext

func (*TxDatabase) QueryRowsPartial 

func (td *TxDatabase) QueryRowsPartial(v any, query string, args ...any) error

See Database#Query

func (*TxDatabase) QueryRowsPartialCtx 

func (td *TxDatabase) QueryRowsPartialCtx(ctx context.Context, v any, query string, args ...any) error

See Database#QueryContext

func (*TxDatabase) Select 

func (td *TxDatabase) Select(cols ...any) *SelectDataset

func (*TxDatabase) Trace 

func (td *TxDatabase) Trace(ctx context.Context, op, sqlString string, args ...any)

func (*TxDatabase) Truncate 

func (td *TxDatabase) Truncate(table ...any) *TruncateDataset

func (*TxDatabase) Update 

func (td *TxDatabase) Update(table any) *UpdateDataset

type UpdateDataset 

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

func Update 

func Update(table any) *UpdateDataset
Example (WithMap) 
sql, args, _ := builder.Update("items").Set(
	map[string]any{"name": "Test", "address": "111 Test Addr"},
).ToSQL()
fmt.Println(sql, args)

Output:

UPDATE "items" SET "address"='111 Test Addr',"name"='Test' []
Example (WithSkipUpdateTag) 
type item struct {
	Address string `db:"address"`
	Name    string `db:"name" builder:"skipupdate"`
}
sql, args, _ := builder.Update("items").Set(
	item{Name: "Test", Address: "111 Test Addr"},
).ToSQL()
fmt.Println(sql, args)

Output:

UPDATE "items" SET "address"='111 Test Addr' []
Example (WithStruct) 
type item struct {
	Address string `db:"address"`
	Name    string `db:"name"`
}
sql, args, _ := builder.Update("items").Set(
	item{Name: "Test", Address: "111 Test Addr"},
).ToSQL()
fmt.Println(sql, args)

Output:

UPDATE "items" SET "address"='111 Test Addr',"name"='Test' []
Example (WithbuilderRecord) 
sql, args, _ := builder.Update("items").Set(
	builder.Record{"name": "Test", "address": "111 Test Addr"},
).ToSQL()
fmt.Println(sql, args)

Output:

UPDATE "items" SET "address"='111 Test Addr',"name"='Test' []

func (*UpdateDataset) AppendSQL 

func (ud *UpdateDataset) AppendSQL(b sb.SQLBuilder)

Appends this Dataset's UPDATE statement to the SQLBuilder This is used internally when using updates in CTEs

func (*UpdateDataset) ClearLimit 

func (ud *UpdateDataset) ClearLimit() *UpdateDataset

Removes the LIMIT clause.

Example 
ds := builder.Dialect("mysql").
	Update("test").
	Set(builder.Record{"foo": "bar"}).
	Limit(10)
sql, _, _ := ds.ClearLimit().ToSQL()
fmt.Println(sql)

Output:

UPDATE `test` SET `foo`='bar'

func (*UpdateDataset) ClearOrder 

func (ud *UpdateDataset) ClearOrder() *UpdateDataset

Removes the ORDER BY clause. See examples.

Example 
ds := builder.Dialect("mysql").
	Update("test").
	Set(builder.Record{"foo": "bar"}).
	Order(builder.C("a").Asc())
sql, _, _ := ds.ClearOrder().ToSQL()
fmt.Println(sql)

Output:

UPDATE `test` SET `foo`='bar'

func (*UpdateDataset) ClearWhere 

func (ud *UpdateDataset) ClearWhere() *UpdateDataset

Removes the WHERE clause. See examples.

Example 
ds := builder.
	Update("test").
	Set(builder.Record{"foo": "bar"}).
	Where(
		builder.Or(
			builder.C("a").Gt(10),
			builder.And(
				builder.C("b").Lt(10),
				builder.C("c").IsNull(),
			),
		),
	)
sql, _, _ := ds.ClearWhere().ToSQL()
fmt.Println(sql)

Output:

UPDATE "test" SET "foo"='bar'

func (*UpdateDataset) Clone 

func (ud *UpdateDataset) Clone() exp.Expression

Clones the dataset

func (*UpdateDataset) Dialect 

func (ud *UpdateDataset) Dialect() SQLDialect

Returns the current adapter on the dataset

func (*UpdateDataset) Error 

func (ud *UpdateDataset) Error() error

Get any error that has been set or nil if no error has been set.

func (*UpdateDataset) Exec 

func (ud *UpdateDataset) Exec() (sql.Result, error)

Generates the UPDATE sql, and returns an exec.QueryExecutor with the sql set to the UPDATE statement 

db.Update("test").Set(Record{"name":"Bob", update: time.Now()}).Executor()
Example 
db := getDB()
_, err := db.Update("builder_user").
	Set(builder.Record{"last_name": "ucon"}).
	Where(builder.Ex{"last_name": "Yukon"}).Exec()

if err != nil {
	fmt.Println(err.Error())
} else {
	fmt.Printf("UpdateDatabaset Exec")
}

Output:

UpdateDatabaset Exec

func (*UpdateDataset) ExecCtx 

func (ud *UpdateDataset) ExecCtx(ctx context.Context) (sql.Result, error)

func (*UpdateDataset) Expression 

func (ud *UpdateDataset) Expression() exp.Expression

func (*UpdateDataset) From 

func (ud *UpdateDataset) From(tables ...any) *UpdateDataset

Allows specifying other tables to reference in your update (If your dialect supports it). See examples.

Example 
ds := builder.Update("table_one").
	Set(builder.Record{"foo": builder.I("table_two.bar")}).
	From("table_two").
	Where(builder.Ex{"table_one.id": builder.I("table_two.id")})

sql, _, _ := ds.ToSQL()
fmt.Println(sql)

Output:

UPDATE "table_one" SET "foo"="table_two"."bar" FROM "table_two" WHERE ("table_one"."id" = "table_two"."id")
Example (Mysql) 
dialect := builder.Dialect("mysql")

ds := dialect.Update("table_one").
	Set(builder.Record{"foo": builder.I("table_two.bar")}).
	From("table_two").
	Where(builder.Ex{"table_one.id": builder.I("table_two.id")})

sql, _, _ := ds.ToSQL()
fmt.Println(sql)

Output:

UPDATE `table_one`,`table_two` SET `foo`=`table_two`.`bar` WHERE (`table_one`.`id` = `table_two`.`id`)
Example (Postgres) 
dialect := builder.Dialect("postgres")

ds := dialect.Update("table_one").
	Set(builder.Record{"foo": builder.I("table_two.bar")}).
	From("table_two").
	Where(builder.Ex{"table_one.id": builder.I("table_two.id")})

sql, _, _ := ds.ToSQL()
fmt.Println(sql)

Output:

UPDATE "table_one" SET "foo"="table_two"."bar" FROM "table_two" WHERE ("table_one"."id" = "table_two"."id")

func (*UpdateDataset) GetAs 

func (ud *UpdateDataset) GetAs() exp.IdentifierExpression

func (*UpdateDataset) GetClauses 

func (ud *UpdateDataset) GetClauses() exp.UpdateClauses

Returns the current clauses on the dataset.

func (*UpdateDataset) IsPrepared 

func (ud *UpdateDataset) IsPrepared() bool

func (*UpdateDataset) Limit 

func (ud *UpdateDataset) Limit(limit uint) *UpdateDataset

Adds a LIMIT clause. If the LIMIT is currently set it replaces it. See examples.

Example 
ds := builder.Dialect("mysql").
	Update("test").
	Set(builder.Record{"foo": "bar"}).
	Limit(10)
sql, _, _ := ds.ToSQL()
fmt.Println(sql)

Output:

UPDATE `test` SET `foo`='bar' LIMIT 10

func (*UpdateDataset) LimitAll 

func (ud *UpdateDataset) LimitAll() *UpdateDataset

Adds a LIMIT ALL clause. If the LIMIT is currently set it replaces it. See examples.

Example 
ds := builder.Dialect("mysql").
	Update("test").
	Set(builder.Record{"foo": "bar"}).
	LimitAll()
sql, _, _ := ds.ToSQL()
fmt.Println(sql)

Output:

UPDATE `test` SET `foo`='bar' LIMIT ALL

func (*UpdateDataset) Order 

func (ud *UpdateDataset) Order(order ...exp.OrderedExpression) *UpdateDataset

Adds a ORDER clause. If the ORDER is currently set it replaces it. See examples.

Example 
ds := builder.Dialect("mysql").
	Update("test").
	Set(builder.Record{"foo": "bar"}).
	Order(builder.C("a").Asc())
sql, _, _ := ds.ToSQL()
fmt.Println(sql)

Output:

UPDATE `test` SET `foo`='bar' ORDER BY `a` ASC

func (*UpdateDataset) OrderAppend 

func (ud *UpdateDataset) OrderAppend(order ...exp.OrderedExpression) *UpdateDataset

Adds a more columns to the current ORDER BY clause. If no order has be previously specified it is the same as calling Order. See examples.

Example 
ds := builder.Dialect("mysql").
	Update("test").
	Set(builder.Record{"foo": "bar"}).
	Order(builder.C("a").Asc())
sql, _, _ := ds.OrderAppend(builder.C("b").Desc().NullsLast()).ToSQL()
fmt.Println(sql)

Output:

UPDATE `test` SET `foo`='bar' ORDER BY `a` ASC, `b` DESC NULLS LAST

func (*UpdateDataset) OrderPrepend 

func (ud *UpdateDataset) OrderPrepend(order ...exp.OrderedExpression) *UpdateDataset

Adds a more columns to the beginning of the current ORDER BY clause. If no order has be previously specified it is the same as calling Order. See examples.

Example 
ds := builder.Dialect("mysql").
	Update("test").
	Set(builder.Record{"foo": "bar"}).
	Order(builder.C("a").Asc())

sql, _, _ := ds.OrderPrepend(builder.C("b").Desc().NullsLast()).ToSQL()
fmt.Println(sql)

Output:

UPDATE `test` SET `foo`='bar' ORDER BY `b` DESC NULLS LAST, `a` ASC

func (*UpdateDataset) Prepared 

func (ud *UpdateDataset) Prepared(prepared bool) *UpdateDataset

Set the parameter interpolation behavior. See examples

prepared: If true the dataset WILL NOT interpolate the parameters.

Example 
sql, args, _ := builder.Update("items").Prepared(true).Set(
	builder.Record{"name": "Test", "address": "111 Test Addr"},
).ToSQL()
fmt.Println(sql, args)

Output:

UPDATE "items" SET "address"=?,"name"=? [111 Test Addr Test]

func (*UpdateDataset) QueryRow 

func (ud *UpdateDataset) QueryRow(v any) error

func (*UpdateDataset) QueryRowCtx 

func (ud *UpdateDataset) QueryRowCtx(ctx context.Context, v any) error

func (*UpdateDataset) QueryRowPartial 

func (ud *UpdateDataset) QueryRowPartial(v any) error

func (*UpdateDataset) QueryRowPartialCtx 

func (ud *UpdateDataset) QueryRowPartialCtx(ctx context.Context, v any) error

func (*UpdateDataset) QueryRows 

func (ud *UpdateDataset) QueryRows(v any) error

func (*UpdateDataset) QueryRowsCtx 

func (ud *UpdateDataset) QueryRowsCtx(ctx context.Context, v any) error

func (*UpdateDataset) QueryRowsPartial 

func (ud *UpdateDataset) QueryRowsPartial(v any) error

func (*UpdateDataset) QueryRowsPartialCtx 

func (ud *UpdateDataset) QueryRowsPartialCtx(ctx context.Context, v any) error

func (*UpdateDataset) Returning 

func (ud *UpdateDataset) Returning(returning ...any) *UpdateDataset

Adds a RETURNING clause to the dataset if the adapter supports it. See examples.

func (*UpdateDataset) ReturnsColumns 

func (ud *UpdateDataset) ReturnsColumns() bool

func (*UpdateDataset) Set 

func (ud *UpdateDataset) Set(values any) *UpdateDataset

Sets the values to use in the SET clause. See examples.

Example 
type item struct {
	Address string `db:"address"`
	Name    string `db:"name"`
}
sql, args, _ := builder.Update("items").Set(
	item{Name: "Test", Address: "111 Test Addr"},
).ToSQL()
fmt.Println(sql, args)

sql, args, _ = builder.Update("items").Set(
	builder.Record{"name": "Test", "address": "111 Test Addr"},
).ToSQL()
fmt.Println(sql, args)

sql, args, _ = builder.Update("items").Set(
	map[string]any{"name": "Test", "address": "111 Test Addr"},
).ToSQL()
fmt.Println(sql, args)

Output:

UPDATE "items" SET "address"='111 Test Addr',"name"='Test' []
UPDATE "items" SET "address"='111 Test Addr',"name"='Test' []
UPDATE "items" SET "address"='111 Test Addr',"name"='Test' []
Example (BuilderRecord) 
sql, args, _ := builder.Update("items").Set(
	builder.Record{"name": "Test", "address": "111 Test Addr"},
).ToSQL()
fmt.Println(sql, args)

Output:

UPDATE "items" SET "address"='111 Test Addr',"name"='Test' []
Example (Map) 
sql, args, _ := builder.Update("items").Set(
	map[string]any{"name": "Test", "address": "111 Test Addr"},
).ToSQL()
fmt.Println(sql, args)

Output:

UPDATE "items" SET "address"='111 Test Addr',"name"='Test' []
Example (Struct) 
type item struct {
	Address string `db:"address"`
	Name    string `db:"name"`
}
sql, args, _ := builder.Update("items").Set(
	item{Name: "Test", Address: "111 Test Addr"},
).ToSQL()
fmt.Println(sql, args)

Output:

UPDATE "items" SET "address"='111 Test Addr',"name"='Test' []
Example (WithDefaultIfEmptyTag) 
type item struct {
	Address string `db:"address"`
	Name    string `db:"name" builder:"defaultifempty"`
}
sql, args, _ := builder.Update("items").Set(
	item{Address: "111 Test Addr"},
).ToSQL()
fmt.Println(sql, args)

sql, args, _ = builder.Update("items").Set(
	item{Name: "Bob Yukon", Address: "111 Test Addr"},
).ToSQL()
fmt.Println(sql, args)

Output:

UPDATE "items" SET "address"='111 Test Addr',"name"=DEFAULT []
UPDATE "items" SET "address"='111 Test Addr',"name"='Bob Yukon' []
Example (WithEmbeddedStruct) 
type Address struct {
	Street string `db:"address_street"`
	State  string `db:"address_state"`
}
type User struct {
	Address
	FirstName string
	LastName  string
}
ds := builder.Update("user").Set(
	User{Address: Address{Street: "111 Street", State: "NY"}, FirstName: "Greg", LastName: "Farley"},
)
updateSQL, args, _ := ds.ToSQL()
fmt.Println(updateSQL, args)

Output:

UPDATE "user" SET "address_state"='NY',"address_street"='111 Street',"firstname"='Greg',"lastname"='Farley' []
Example (WithIgnoredEmbedded) 
type Address struct {
	Street string
	State  string
}
type User struct {
	Address   `db:"-"`
	FirstName string
	LastName  string
}
ds := builder.Update("user").Set(
	User{Address: Address{Street: "111 Street", State: "NY"}, FirstName: "Greg", LastName: "Farley"},
)
updateSQL, args, _ := ds.ToSQL()
fmt.Println(updateSQL, args)

Output:

UPDATE "user" SET "firstname"='Greg',"lastname"='Farley' []
Example (WithNilEmbeddedPointer) 
type Address struct {
	Street string
	State  string
}
type User struct {
	*Address
	FirstName string
	LastName  string
}
ds := builder.Update("user").Set(
	User{FirstName: "Greg", LastName: "Farley"},
)
updateSQL, args, _ := ds.ToSQL()
fmt.Println(updateSQL, args)

Output:

UPDATE "user" SET "firstname"='Greg',"lastname"='Farley' []
Example (WithNoTags) 
type item struct {
	Address string
	Name    string
}
sql, args, _ := builder.Update("items").Set(
	item{Name: "Test", Address: "111 Test Addr"},
).ToSQL()
fmt.Println(sql, args)

Output:

UPDATE "items" SET "address"='111 Test Addr',"name"='Test' []
Example (WithSkipUpdateTag) 
type item struct {
	Address string `db:"address"`
	Name    string `db:"name" builder:"skipupdate"`
}
sql, args, _ := builder.Update("items").Set(
	item{Name: "Test", Address: "111 Test Addr"},
).ToSQL()
fmt.Println(sql, args)

Output:

UPDATE "items" SET "address"='111 Test Addr' []

func (*UpdateDataset) SetDialect 

func (ud *UpdateDataset) SetDialect(dialect SQLDialect) *UpdateDataset

Returns the current adapter on the dataset

func (*UpdateDataset) SetError 

func (ud *UpdateDataset) SetError(err error) *UpdateDataset

Set an error on the dataset if one has not already been set. This error will be returned by a future call to Error or as part of ToSQL. This can be used by end users to record errors while building up queries without having to track those separately.

func (*UpdateDataset) Table 

func (ud *UpdateDataset) Table(table any) *UpdateDataset

Sets the table to update.

Example 
ds := builder.Update("test")
sql, _, _ := ds.Table("test2").Set(builder.Record{"foo": "bar"}).ToSQL()
fmt.Println(sql)

Output:

UPDATE "test2" SET "foo"='bar'
Example (Aliased) 
ds := builder.Update("test")
sql, _, _ := ds.Table(builder.T("test").As("t")).Set(builder.Record{"foo": "bar"}).ToSQL()
fmt.Println(sql)

Output:

UPDATE "test" AS "t" SET "foo"='bar'

func (*UpdateDataset) ToSQL 

func (ud *UpdateDataset) ToSQL() (sql string, params []any, err error)

Generates an UPDATE sql statement, if Prepared has been called with true then the parameters will not be interpolated. See examples.

Errors:

  • There is an error generating the SQL
Example 
sql, _, _ := builder.Update("test").
	Set(builder.Record{"foo": "bar"}).
	ToSQL()
fmt.Println(sql)
sql, _, _ = builder.Update("test").
	Set(builder.Record{"foo": "bar"}).
	ToSQL()
fmt.Println(sql)
sql, _, _ = builder.Update("test").
	Set(builder.Record{"foo": "bar"}).
	ToSQL()
fmt.Println(sql)

Output:

UPDATE "test" SET "foo"='bar'
UPDATE "test" SET "foo"='bar'
UPDATE "test" SET "foo"='bar'
Example (Prepared) 
type item struct {
	Address string `db:"address"`
	Name    string `db:"name"`
}

sql, args, _ := builder.From("items").Prepared(true).Update().Set(
	item{Name: "Test", Address: "111 Test Addr"},
).ToSQL()
fmt.Println(sql, args)

sql, args, _ = builder.From("items").Prepared(true).Update().Set(
	builder.Record{"name": "Test", "address": "111 Test Addr"},
).ToSQL()
fmt.Println(sql, args)

sql, args, _ = builder.From("items").Prepared(true).Update().Set(
	map[string]any{"name": "Test", "address": "111 Test Addr"},
).ToSQL()
fmt.Println(sql, args)

Output:

UPDATE "items" SET "address"=?,"name"=? [111 Test Addr Test]
UPDATE "items" SET "address"=?,"name"=? [111 Test Addr Test]
UPDATE "items" SET "address"=?,"name"=? [111 Test Addr Test]

func (*UpdateDataset) Where 

func (ud *UpdateDataset) Where(expressions ...exp.Expression) *UpdateDataset

Adds a WHERE clause. See examples.

Example 
// By default everything is anded together
sql, _, _ := builder.Update("test").
	Set(builder.Record{"foo": "bar"}).
	Where(builder.Ex{
		"a": builder.Op{"gt": 10},
		"b": builder.Op{"lt": 10},
		"c": nil,
		"d": []string{"a", "b", "c"},
	}).ToSQL()
fmt.Println(sql)
// You can use ExOr to get ORed expressions together
sql, _, _ = builder.Update("test").
	Set(builder.Record{"foo": "bar"}).
	Where(builder.ExOr{
		"a": builder.Op{"gt": 10},
		"b": builder.Op{"lt": 10},
		"c": nil,
		"d": []string{"a", "b", "c"},
	}).ToSQL()
fmt.Println(sql)
// You can use Or with Ex to Or multiple Ex maps together
sql, _, _ = builder.Update("test").
	Set(builder.Record{"foo": "bar"}).
	Where(
		builder.Or(
			builder.Ex{
				"a": builder.Op{"gt": 10},
				"b": builder.Op{"lt": 10},
			},
			builder.Ex{
				"c": nil,
				"d": []string{"a", "b", "c"},
			},
		),
	).ToSQL()
fmt.Println(sql)
// By default everything is anded together
sql, _, _ = builder.Update("test").
	Set(builder.Record{"foo": "bar"}).
	Where(
		builder.C("a").Gt(10),
		builder.C("b").Lt(10),
		builder.C("c").IsNull(),
		builder.C("d").In("a", "b", "c"),
	).ToSQL()
fmt.Println(sql)
// You can use a combination of Ors and Ands
sql, _, _ = builder.Update("test").
	Set(builder.Record{"foo": "bar"}).
	Where(
		builder.Or(
			builder.C("a").Gt(10),
			builder.And(
				builder.C("b").Lt(10),
				builder.C("c").IsNull(),
			),
		),
	).ToSQL()
fmt.Println(sql)

Output:

UPDATE "test" SET "foo"='bar' WHERE (("a" > 10) AND ("b" < 10) AND ("c" IS NULL) AND ("d" IN ('a', 'b', 'c')))
UPDATE "test" SET "foo"='bar' WHERE (("a" > 10) OR ("b" < 10) OR ("c" IS NULL) OR ("d" IN ('a', 'b', 'c')))
UPDATE "test" SET "foo"='bar' WHERE ((("a" > 10) AND ("b" < 10)) OR (("c" IS NULL) AND ("d" IN ('a', 'b', 'c'))))
UPDATE "test" SET "foo"='bar' WHERE (("a" > 10) AND ("b" < 10) AND ("c" IS NULL) AND ("d" IN ('a', 'b', 'c')))
UPDATE "test" SET "foo"='bar' WHERE (("a" > 10) OR (("b" < 10) AND ("c" IS NULL)))
Example (Prepared) 
// By default everything is anded together
sql, args, _ := builder.Update("test").
	Prepared(true).
	Set(builder.Record{"foo": "bar"}).
	Where(builder.Ex{
		"a": builder.Op{"gt": 10},
		"b": builder.Op{"lt": 10},
		"c": nil,
		"d": []string{"a", "b", "c"},
	}).ToSQL()
fmt.Println(sql, args)
// You can use ExOr to get ORed expressions together
sql, args, _ = builder.Update("test").Prepared(true).
	Set(builder.Record{"foo": "bar"}).
	Where(builder.ExOr{
		"a": builder.Op{"gt": 10},
		"b": builder.Op{"lt": 10},
		"c": nil,
		"d": []string{"a", "b", "c"},
	}).ToSQL()
fmt.Println(sql, args)
// You can use Or with Ex to Or multiple Ex maps together
sql, args, _ = builder.Update("test").Prepared(true).
	Set(builder.Record{"foo": "bar"}).
	Where(
		builder.Or(
			builder.Ex{
				"a": builder.Op{"gt": 10},
				"b": builder.Op{"lt": 10},
			},
			builder.Ex{
				"c": nil,
				"d": []string{"a", "b", "c"},
			},
		),
	).ToSQL()
fmt.Println(sql, args)
// By default everything is anded together
sql, args, _ = builder.Update("test").Prepared(true).
	Set(builder.Record{"foo": "bar"}).
	Where(
		builder.C("a").Gt(10),
		builder.C("b").Lt(10),
		builder.C("c").IsNull(),
		builder.C("d").In("a", "b", "c"),
	).ToSQL()
fmt.Println(sql, args)
// You can use a combination of Ors and Ands
sql, args, _ = builder.Update("test").Prepared(true).
	Set(builder.Record{"foo": "bar"}).
	Where(
		builder.Or(
			builder.C("a").Gt(10),
			builder.And(
				builder.C("b").Lt(10),
				builder.C("c").IsNull(),
			),
		),
	).ToSQL()
fmt.Println(sql, args)

Output:

UPDATE "test" SET "foo"=? WHERE (("a" > ?) AND ("b" < ?) AND ("c" IS NULL) AND ("d" IN (?, ?, ?))) [bar 10 10 a b c]
UPDATE "test" SET "foo"=? WHERE (("a" > ?) OR ("b" < ?) OR ("c" IS NULL) OR ("d" IN (?, ?, ?))) [bar 10 10 a b c]
UPDATE "test" SET "foo"=? WHERE ((("a" > ?) AND ("b" < ?)) OR (("c" IS NULL) AND ("d" IN (?, ?, ?)))) [bar 10 10 a b c]
UPDATE "test" SET "foo"=? WHERE (("a" > ?) AND ("b" < ?) AND ("c" IS NULL) AND ("d" IN (?, ?, ?))) [bar 10 10 a b c]
UPDATE "test" SET "foo"=? WHERE (("a" > ?) OR (("b" < ?) AND ("c" IS NULL))) [bar 10 10]

func (*UpdateDataset) With 

func (ud *UpdateDataset) With(name string, subquery exp.Expression) *UpdateDataset

Creates a WITH clause for a common table expression (CTE).

The name will be available to use in the UPDATE from in the associated query; and can optionally contain a list of column names "name(col1, col2, col3)".

The name will refer to the results of the specified subquery.

Example 
sql, _, _ := builder.Update("test").
	With("some_vals(val)", builder.From().Select(builder.L("123"))).
	Where(builder.C("val").Eq(builder.From("some_vals").Select("val"))).
	Set(builder.Record{"name": "Test"}).ToSQL()
fmt.Println(sql)

Output:

WITH some_vals(val) AS (SELECT 123) UPDATE "test" SET "name"='Test' WHERE ("val" IN (SELECT "val" FROM "some_vals"))

func (*UpdateDataset) WithDialect 

func (ud *UpdateDataset) WithDialect(dl string) *UpdateDataset

Sets the adapter used to serialize values and create the SQL statement

func (*UpdateDataset) WithRecursive 

func (ud *UpdateDataset) WithRecursive(name string, subquery exp.Expression) *UpdateDataset

Creates a WITH RECURSIVE clause for a common table expression (CTE)

The name will be available to use in the UPDATE from in the associated query; and must contain a list of column names "name(col1, col2, col3)" for a recursive clause.

The name will refer to the results of the specified subquery. The subquery for a recursive query will always end with a UNION or UNION ALL with a clause that refers to the CTE by name.

Example 
sql, _, _ := builder.Update("nums").
	WithRecursive("nums(x)", builder.From().Select(builder.L("1").As("num")).
		UnionAll(builder.From("nums").
			Select(builder.L("x+1").As("num")).Where(builder.C("x").Lt(5)))).
	Set(builder.Record{"foo": builder.T("nums").Col("num")}).
	ToSQL()
fmt.Println(sql)

Output:

WITH RECURSIVE nums(x) AS (SELECT 1 AS "num" UNION ALL (SELECT x+1 AS "num" FROM "nums" WHERE ("x" < 5))) UPDATE "nums" SET "foo"="nums"."num"

type Vals 

type Vals = exp.Vals
Example 
ds := builder.Insert("user").
	Cols("first_name", "last_name", "is_verified").
	Vals(
		builder.Vals{"Greg", "Farley", true},
		builder.Vals{"Jimmy", "Stewart", true},
		builder.Vals{"Jeff", "Jeffers", false},
	)
insertSQL, args, _ := ds.ToSQL()
fmt.Println(insertSQL, args)

Output:

INSERT INTO "user" ("first_name", "last_name", "is_verified") VALUES ('Greg', 'Farley', TRUE), ('Jimmy', 'Stewart', TRUE), ('Jeff', 'Jeffers', FALSE) []

Source Files

View all Source files

Directories

Path Synopsis
dialect
mysql
postgres
internal
errors
sb
tag
util
mocks

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.