goqu

package module
v9.7.1 Latest Latest
Warning

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

 Go to latest Published: Mar 17, 2020 License: MIT Imports: 12 Imported by: 425

README 

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

GitHub tag Test GoDoc codecov Go Report Card

goqu 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/doug-martin/goqu/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/doug-martin/goqu/v9 -> import "github.com/doug-martin/goqu"

go get -u github.com/doug-martin/goqu

Migrating Between Versions

Features

goqu 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 goqu 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. goqu 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 goqu 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 goqu.
  • Database - Docs and examples of using a Database to execute queries in goqu
  • 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, _, _ := goqu.From("test").ToSQL()
fmt.Println(sql)

Output:

SELECT * FROM "test"

sql, _, _ := goqu.From("test").Where(goqu.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 := goqu.Insert("user").
	Cols("first_name", "last_name").
	Vals(
		goqu.Vals{"Greg", "Farley"},
		goqu.Vals{"Jimmy", "Stewart"},
		goqu.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 := goqu.Insert("user").Rows(
	goqu.Record{"first_name": "Greg", "last_name": "Farley"},
	goqu.Record{"first_name": "Jimmy", "last_name": "Stewart"},
	goqu.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 := goqu.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 := goqu.Insert("user").Prepared(true).
	FromQuery(goqu.From("other_table"))
insertSQL, args, _ := ds.ToSQL()
fmt.Println(insertSQL, args)

Output:

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

ds := goqu.Insert("user").Prepared(true).
	Cols("first_name", "last_name").
	FromQuery(goqu.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, _ := goqu.Update("items").Set(
	goqu.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" goqu:"skipupdate"`
}
sql, args, _ := goqu.Update("items").Set(
	item{Name: "Test", Address: "111 Test Addr"},
).ToSQL()
fmt.Println(sql, args)

Output:

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

sql, _, _ := goqu.Update("test").
	Set(goqu.Record{"foo": "bar"}).
	Where(goqu.Ex{
		"a": goqu.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 := goqu.Delete("items")

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

sql, _, _ := goqu.Delete("test").Where(goqu.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 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.

GO_VERSION=latest docker-compose run goqu

License

goqu is released under the MIT License.

Documentation

Overview

goqu an idiomatch SQL builder, and query package. 

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

Please see https://github.com/doug-martin/goqu for an introduction to goqu.

Index

Examples

Constants

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

Variables

View Source 
var (
	DefaultDialectOptions = sqlgen.DefaultDialectOptions
)

Functions

func AVG 

func AVG(col interface{}) exp.SQLFunctionExpression

Creates a new AVG sql function 

AVG("a") -> AVG("a")
AVG(I("a")) -> AVG("a")
Example 
ds := goqu.From("test").Select(goqu.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, _, _ := goqu.From("test").Select(goqu.AVG("a").As("a")).ToSQL()
fmt.Println(sql)

Output:

SELECT AVG("a") AS "a" FROM "test"
Example (HavingClause) 
ds := goqu.
	From("test").
	Select(goqu.AVG("a").As("avg")).
	GroupBy("a").
	Having(goqu.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 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 := goqu.From("test").Where(
	goqu.And(
		goqu.C("col").Gt(10),
		goqu.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 := goqu.From("test").Where(
	goqu.C("col1").IsTrue(),
	goqu.ExOr{
		"col2": goqu.Op{"gt": 10},
		"col3": goqu.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 := goqu.From("test").Where(
	goqu.And(
		goqu.C("col1").IsTrue(),
		goqu.Or(
			goqu.C("col2").Gt(10),
			goqu.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 = goqu.From("test").Where(
	goqu.C("col1").IsTrue(),
	goqu.Or(
		goqu.C("col2").Gt(10),
		goqu.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 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, _ := goqu.From("test").
	Select(goqu.C("*")).
	ToSQL()
fmt.Println(sql, args)

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

ds := goqu.From("test").Where(
	goqu.C("col1").Eq(10),
	goqu.C("col2").In([]int64{1, 2, 3, 4}),
	goqu.C("col3").Like(regexp.MustCompile("^(a|b)")),
	goqu.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" ~ '^(a|b)') AND ("col4" IS NULL)) []
SELECT * FROM "test" WHERE (("col1" = ?) AND ("col2" IN (?, ?, ?, ?)) AND ("col3" ~ ?) AND ("col4" IS NULL)) [10 1 2 3 4 ^(a|b)]
Example (As) 
sql, _, _ := goqu.From("test").Select(goqu.C("a").As("as_a")).ToSQL()
fmt.Println(sql)

sql, _, _ = goqu.From("test").Select(goqu.C("a").As(goqu.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 := goqu.From("test").Where(
	goqu.C("a").Between(goqu.Range(1, 10)),
)
sql, args, _ := ds.ToSQL()
fmt.Println(sql, args)

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

ds = goqu.From("test").Where(
	goqu.C("a").NotBetween(goqu.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, _, _ := goqu.From("test").
	Select(goqu.C("json1").Cast("TEXT").As("json_text")).
	ToSQL()
fmt.Println(sql)

sql, _, _ = goqu.From("test").Where(
	goqu.C("json1").Cast("TEXT").Neq(
		goqu.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, _, _ := goqu.From("test").Where(goqu.C("a").Eq(10)).ToSQL()
fmt.Println(sql)

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

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

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

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

sql, _, _ = goqu.From("test").Where(goqu.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, _, _ := goqu.From("test").Where(goqu.C("a").In("a", "b", "c")).ToSQL()
fmt.Println(sql)
// with a slice
sql, _, _ = goqu.From("test").Where(goqu.C("a").In([]string{"a", "b", "c"})).ToSQL()
fmt.Println(sql)

sql, _, _ = goqu.From("test").Where(goqu.C("a").NotIn("a", "b", "c")).ToSQL()
fmt.Println(sql)
// with a slice
sql, _, _ = goqu.From("test").Where(goqu.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, _ := goqu.From("test").Where(goqu.C("a").Is(nil)).ToSQL()
fmt.Println(sql, args)

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

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

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

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

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

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

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

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

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

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

sql, args, _ = goqu.From("test").Where(goqu.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, _, _ := goqu.From("test").Where(goqu.C("a").Like("%a%")).ToSQL()
fmt.Println(sql)

sql, _, _ = goqu.From("test").Where(goqu.C("a").Like(regexp.MustCompile("(a|b)"))).ToSQL()
fmt.Println(sql)

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

sql, _, _ = goqu.From("test").Where(goqu.C("a").ILike(regexp.MustCompile("(a|b)"))).ToSQL()
fmt.Println(sql)

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

sql, _, _ = goqu.From("test").Where(goqu.C("a").NotLike(regexp.MustCompile("(a|b)"))).ToSQL()
fmt.Println(sql)

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

sql, _, _ = goqu.From("test").Where(goqu.C("a").NotILike(regexp.MustCompile("(a|b)"))).ToSQL()
fmt.Println(sql)

Output:

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

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

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

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

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

sql, args, _ = goqu.From("test").Order(goqu.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 ...interface{}) 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 := goqu.From("test").Select(
	goqu.COALESCE(goqu.C("a"), "a"),
	goqu.COALESCE(goqu.C("a"), goqu.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, _, _ := goqu.From("test").Select(goqu.COALESCE(goqu.C("a"), "a").As("a")).ToSQL()
fmt.Println(sql)

Output:

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

func COUNT 

func COUNT(col interface{}) exp.SQLFunctionExpression

Creates a new COUNT sql function 

COUNT("a") -> COUNT("a")
COUNT("*") -> COUNT("*")
COUNT(I("a")) -> COUNT("a")
Example 
ds := goqu.From("test").Select(goqu.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, _, _ := goqu.From("test").Select(goqu.COUNT("*").As("count")).ToSQL()
fmt.Println(sql)

Output:

SELECT COUNT(*) AS "count" FROM "test"
Example (HavingClause) 
ds := goqu.
	From("test").
	Select(goqu.COUNT("a").As("COUNT")).
	GroupBy("a").
	Having(goqu.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 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, _, _ := goqu.From("test").
	Select(goqu.Cast(goqu.C("json1"), "TEXT").As("json_text")).
	ToSQL()
fmt.Println(sql)

sql, _, _ = goqu.From("test").Where(
	goqu.Cast(goqu.C("json1"), "TEXT").Neq(
		goqu.Cast(goqu.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 interface{}) exp.SQLFunctionExpression

Creates a new DISTINCT sql function 

DISTINCT("a") -> DISTINCT("a")
DISTINCT(I("a")) -> DISTINCT("a")
Example 
ds := goqu.From("test").Select(goqu.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, _, _ := goqu.From("test").Select(goqu.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 := goqu.Insert("items")

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

sql, args, _ = ds.Prepared(true).Rows(goqu.Record{
	"name":    goqu.Default(),
	"address": goqu.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 := goqu.Insert("items")

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

sql, args, _ = ds.Prepared(true).Rows(goqu.Record{
	"address": "111 Address",
	"name":    "bob",
}).OnConflict(goqu.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 interface{}) 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 := goqu.Insert("items")

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

sql, args, _ = ds.Prepared(true).
	Rows(goqu.Record{"address": "111 Address"}).
	OnConflict(goqu.DoUpdate("address", goqu.C("address").Set(goqu.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 := goqu.Insert("items")

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

sql, args, _ = ds.Prepared(true).
	Rows(goqu.Record{"address": "111 Address"}).
	OnConflict(goqu.DoUpdate(
		"address",
		goqu.C("address").Set(goqu.I("excluded.address"))).Where(goqu.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 interface{}) exp.SQLFunctionExpression

Creates a new FIRST sql function 

FIRST("a") -> FIRST("a")
FIRST(I("a")) -> FIRST("a")
Example 
ds := goqu.From("test").Select(goqu.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, _, _ := goqu.From("test").Select(goqu.FIRST("a").As("a")).ToSQL()
fmt.Println(sql)

Output:

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

func FIRST_VALUE 

func FIRST_VALUE(val interface{}) exp.SQLFunctionExpression

func Func 

func Func(name string, args ...interface{}) 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 goqu.Func("str_agg", expression, goqu.L(delimiter))
}
sql, _, _ := goqu.From("test").Select(stragg(goqu.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 := goqu.From("test").
	Select(
		goqu.I("my_schema.table.col1"),
		goqu.I("table.col2"),
		goqu.I("col3"),
	)

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

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

ds = goqu.From("test").Select(goqu.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 ...interface{}) 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 := goqu.From("test").Where(
	// literal with no args
	goqu.L(`"col"::TEXT = ""other_col"::text`),
	// literal with args they will be interpolated into the sql by default
	goqu.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, _, _ := goqu.From("test").Select(goqu.L("json_col->>'totalAmount'").As("total_amount")).ToSQL()
fmt.Println(sql)

Output:

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

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

ds = goqu.From("test").Where(
	goqu.L("(a + b)").NotBetween(goqu.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, _, _ := goqu.From("test").Where(goqu.L("(a + b)").Eq(10)).ToSQL()
fmt.Println(sql)

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

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

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

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

sql, _, _ = goqu.From("test").Where(goqu.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, _, _ := goqu.From("test").Where(goqu.L("json_col->>'val'").In("a", "b", "c")).ToSQL()
fmt.Println(sql)
// with a slice
sql, _, _ = goqu.From("test").Where(goqu.L("json_col->>'val'").In([]string{"a", "b", "c"})).ToSQL()
fmt.Println(sql)

sql, _, _ = goqu.From("test").Where(goqu.L("json_col->>'val'").NotIn("a", "b", "c")).ToSQL()
fmt.Println(sql)
// with a slice
sql, _, _ = goqu.From("test").Where(goqu.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, _ := goqu.From("test").Where(goqu.L("a").Is(nil)).ToSQL()
fmt.Println(sql, args)

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

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

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

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

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

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

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

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

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

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

sql, args, _ = goqu.From("test").Where(goqu.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, _, _ := goqu.From("test").Where(goqu.L("(a::text || 'bar')").Like("%a%")).ToSQL()
fmt.Println(sql)

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

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

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

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

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

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

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

Output:

SELECT * FROM "test" WHERE ((a::text || 'bar') LIKE '%a%')
SELECT * FROM "test" WHERE ((a::text || 'bar') ~ '(a|b)')
SELECT * FROM "test" WHERE ((a::text || 'bar') ILIKE '%a%')
SELECT * FROM "test" WHERE ((a::text || 'bar') ~* '(a|b)')
SELECT * FROM "test" WHERE ((a::text || 'bar') NOT LIKE '%a%')
SELECT * FROM "test" WHERE ((a::text || 'bar') !~ '(a|b)')
SELECT * FROM "test" WHERE ((a::text || 'bar') NOT ILIKE '%a%')
SELECT * FROM "test" WHERE ((a::text || 'bar') !~* '(a|b)')
Example (WithArgs) 
ds := goqu.From("test").Where(
	goqu.L(
		"(? AND ?) OR ?",
		goqu.C("a").Eq(1),
		goqu.C("b").Eq("b"),
		goqu.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 interface{}) exp.SQLFunctionExpression

Creates a new LAST sql function 

LAST("a") -> LAST("a")
LAST(I("a")) -> LAST("a")
Example 
ds := goqu.From("test").Select(goqu.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, _, _ := goqu.From("test").Select(goqu.LAST("a").As("a")).ToSQL()
fmt.Println(sql)

Output:

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

func LAST_VALUE 

func LAST_VALUE(val interface{}) exp.SQLFunctionExpression

func Lateral added in v9.6.0 

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

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

ds := goqu.
	Select("e.id", "max_entry.max_int", "max_id.id").
	From(
		goqu.T("entry").As("e"),
		goqu.Lateral(maxEntry),
		goqu.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 := goqu.From("entry").
	Select(goqu.MAX("int").As("max_int")).
	Where(goqu.Ex{"time": goqu.Op{"lt": goqu.I("e.time")}}).
	As("max_entry")

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

ds := goqu.
	Select("e.id", "max_entry.max_int", "max_id.id").
	From(goqu.T("entry").As("e")).
	Join(goqu.Lateral(maxEntry), goqu.On(goqu.V(true))).
	Join(goqu.Lateral(maxID), goqu.On(goqu.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 ...interface{}) exp.LiteralExpression

Alias for goqu.L

func MAX 

func MAX(col interface{}) exp.SQLFunctionExpression

Creates a new MAX sql function 

MAX("a") -> MAX("a")
MAX(I("a")) -> MAX("a")
Example 
ds := goqu.From("test").Select(goqu.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, _, _ := goqu.From("test").Select(goqu.MAX("a").As("a")).ToSQL()
fmt.Println(sql)

Output:

SELECT MAX("a") AS "a" FROM "test"
Example (HavingClause) 
ds := goqu.
	From("test").
	Select(goqu.MAX("a").As("MAX")).
	GroupBy("a").
	Having(goqu.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 interface{}) exp.SQLFunctionExpression

Creates a new MIN sql function 

MIN("a") -> MIN("a")
MIN(I("a")) -> MIN("a")
Example 
ds := goqu.From("test").Select(goqu.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, _, _ := goqu.From("test").Select(goqu.MIN("a").As("a")).ToSQL()
fmt.Println(sql)

Output:

SELECT MIN("a") AS "a" FROM "test"
Example (HavingClause) 
ds := goqu.
	From("test").
	Select(goqu.MIN("a").As("MIN")).
	GroupBy("a").
	Having(goqu.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 interface{}, 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(goqu.T("my_table"), goqu.On(
   goqu.I("my_table.fkey").Eq(goqu.I("other_table.id")),
))
Example 
ds := goqu.From("test").Join(
	goqu.T("my_table"),
	goqu.On(goqu.I("my_table.fkey").Eq(goqu.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 := goqu.From("test").Join(
	goqu.T("my_table"),
	goqu.On(goqu.Ex{"my_table.fkey": goqu.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 := goqu.From("test").Where(
	goqu.Or(
		goqu.C("col").Eq(10),
		goqu.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 := goqu.From("items").Where(
	goqu.Or(
		goqu.C("a").Gt(10),
		goqu.And(
			goqu.C("b").Eq(100),
			goqu.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 := goqu.From("test").Where(
	goqu.Or(
		// Ex will be anded together
		goqu.Ex{
			"col1": 1,
			"col2": true,
		},
		goqu.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 interface{}) exp.RangeVal

Creates a new Range to be used with a Between expression 

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

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

ds = goqu.From("test").Where(
	goqu.C("col1").NotBetween(goqu.Range(goqu.C("col2"), goqu.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 := goqu.From("test").Where(
	goqu.C("col").Between(goqu.Range(1, 10)),
)
sql, args, _ := ds.ToSQL()
fmt.Println(sql, args)

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

ds = goqu.From("test").Where(
	goqu.C("col").NotBetween(goqu.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 := goqu.From("test").Where(
	goqu.C("col").Between(goqu.Range("a", "z")),
)
sql, args, _ := ds.ToSQL()
fmt.Println(sql, args)

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

ds = goqu.From("test").Where(
	goqu.C("col").NotBetween(goqu.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 := goqu.DefaultDialectOptions()
opts.QuoteRune = '`'
goqu.RegisterDialect("custom-dialect", opts)

dialect := goqu.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 := goqu.S("test_schema")
t := s.Table("test")
sql, args, _ := goqu.
	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 interface{}) exp.SQLFunctionExpression

Creates a new SUM sql function 

SUM("a") -> SUM("a")
SUM(I("a")) -> SUM("a")
Example 
ds := goqu.From("test").Select(goqu.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, _, _ := goqu.From("test").Select(goqu.SUM("a").As("a")).ToSQL()
fmt.Println(sql)

Output:

SELECT SUM("a") AS "a" FROM "test"
Example (HavingClause) 
ds := goqu.
	From("test").
	Select(goqu.SUM("a").As("SUM")).
	GroupBy("a").
	Having(goqu.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 SetTimeLocation added in v9.1.0 

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
goqu.SetTimeLocation(loc)
ds := goqu.Insert("test").Rows(goqu.Record{
	"address": "111 Address",
	"name":    "Bob Yukon",
	"created": created,
})
sql, _, _ := ds.ToSQL()
fmt.Println(sql)

// convert time to UTC
goqu.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 := goqu.From("test").Select(goqu.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 := goqu.T("test")
sql, args, _ := goqu.
	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 ...interface{}) exp.JoinCondition

Creates a new USING clause to be used within a join 

ds.Join(goqu.T("my_table"), goqu.Using("fkey"))
Example 
ds := goqu.From("test").Join(
	goqu.T("my_table"),
	goqu.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 := goqu.From("test").Join(
	goqu.T("my_table"),
	goqu.Using(goqu.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 interface{}) exp.LiteralExpression

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

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

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

ds = goqu.From("user").Where(goqu.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 := goqu.From("user").Select(
	goqu.V(true).As("is_verified"),
	goqu.V(1.2).As("version"),
	"first_name",
	"last_name",
)

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

ds = goqu.From("user").Where(goqu.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 := goqu.From("test").
	Select(goqu.ROW_NUMBER().Over(goqu.W().PartitionBy("a").OrderBy(goqu.I("b").Asc())))
query, args, _ := ds.ToSQL()
fmt.Println(query, args)

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

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

ds = goqu.From("test").
	Select(goqu.ROW_NUMBER().Over(goqu.W().Inherit("w").OrderBy("b"))).
	Window(goqu.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 {
	Db SQLDatabase
	// 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 SQLDatabase) *Database

func (*Database) Begin 

func (d *Database) Begin() (*TxDatabase, error)

Starts a new Transaction.

Example 
db := getDb()

tx, err := db.Begin()
if err != nil {
	fmt.Println("Error starting transaction", err.Error())
}

// use tx.From to get a dataset that will execute within this transaction
update := tx.Update("goqu_user").
	Set(goqu.Record{"last_name": "Ucon"}).
	Where(goqu.Ex{"last_name": "Yukon"}).
	Returning("id").
	Executor()

var ids []int64
if err := update.ScanVals(&ids); err != nil {
	if rErr := tx.Rollback(); rErr != nil {
		fmt.Println("An error occurred while issuing ROLLBACK\n\t", rErr.Error())
	} else {
		fmt.Println("An error occurred while updating users ROLLBACK transaction\n\t", err.Error())
	}
	return
}
if err := tx.Commit(); err != nil {
	fmt.Println("An error occurred while issuing COMMIT\n\t", err.Error())
} else {
	fmt.Printf("Updated users in transaction [ids:=%+v]", ids)
}

Output:

Updated users in transaction [ids:=[1 2 3]]

func (*Database) BeginTx 

func (d *Database) BeginTx(ctx context.Context, opts *sql.TxOptions) (*TxDatabase, error)

Starts a new Transaction. See sql.DB#BeginTx for option description

Example 
db := getDb()

ctx := context.Background()
tx, err := db.BeginTx(ctx, &sql.TxOptions{Isolation: sql.LevelReadCommitted})
if err != nil {
	fmt.Println("Error starting transaction", err.Error())
}

// use tx.From to get a dataset that will execute within this transaction
update := tx.Update("goqu_user").
	Set(goqu.Record{"last_name": "Ucon"}).
	Where(goqu.Ex{"last_name": "Yukon"}).
	Returning("id").
	Executor()

var ids []int64
if err := update.ScanVals(&ids); err != nil {
	if rErr := tx.Rollback(); rErr != nil {
		fmt.Println("An error occurred while issuing ROLLBACK\n\t", rErr.Error())
	} else {
		fmt.Println("An error occurred while updating users ROLLBACK transaction\n\t", err.Error())
	}
	return
}
if err := tx.Commit(); err != nil {
	fmt.Println("An error occurred while issuing COMMIT\n\t", err.Error())
} else {
	fmt.Printf("Updated users in transaction [ids:=%+v]", ids)
}

Output:

Updated users in transaction [ids:=[1 2 3]]

func (*Database) Delete 

func (d *Database) Delete(table interface{}) *DeleteDataset

func (*Database) Dialect 

func (d *Database) Dialect() string

returns this databases dialect

Example 
db := getDb()

fmt.Println(db.Dialect())

Output:

postgres

func (*Database) Exec 

func (d *Database) Exec(query string, args ...interface{}) (sql.Result, error)

Uses the db to Execute the query with arguments and return the sql.Result

query: The SQL to execute

args...: for any placeholder parameters in the query

Example 
db := getDb()

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

Output:

Dropped tables user_role and goqu_user

func (*Database) ExecContext 

func (d *Database) ExecContext(ctx context.Context, query string, args ...interface{}) (sql.Result, error)

Uses the db to Execute the query with arguments and return the sql.Result

query: The SQL to execute

args...: for any placeholder parameters in the query

Example 
db := getDb()
d := time.Now().Add(50 * time.Millisecond)
ctx, cancel := context.WithDeadline(context.Background(), d)
defer cancel()
_, err := db.ExecContext(ctx, `DROP TABLE "user_role"; DROP TABLE "goqu_user"`)
if err != nil {
	fmt.Println("Error occurred while dropping tables", err.Error())
}
fmt.Println("Dropped tables user_role and goqu_user")

Output:

Dropped tables user_role and goqu_user

func (*Database) From 

func (d *Database) From(from ...interface{}) *SelectDataset

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

var ids []uint32
if err := db.From("items").Where(goqu.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 goqu.Literal or another goqu.Dataset

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

if err := db.From("goqu_user").Select("first_name").ScanVals(&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 interface{}) *InsertDataset

func (*Database) Logger 

func (d *Database) Logger(logger Logger)

Sets the logger for to use when logging queries

func (*Database) Prepare 

func (d *Database) Prepare(query string) (*sql.Stmt, error)

Can be used to prepare a query.

You can use this in tandem with a dataset by doing the following. 

sql, args, err := db.From("items").Where(goqu.I("id").Gt(10)).ToSQL(true)
if err != nil{
    panic(err.Error()) //you could gracefully handle the error also
}
stmt, err := db.Prepare(sql)
if err != nil{
    panic(err.Error()) //you could gracefully handle the error also
}
defer stmt.Close()
rows, err := stmt.Query(args)
if err != nil{
    panic(err.Error()) //you could gracefully handle the error also
}
defer rows.Close()
for rows.Next(){
          //scan your rows
}
if rows.Err() != nil{
    panic(err.Error()) //you could gracefully handle the error also
}

query: The SQL statement to prepare.

func (*Database) PrepareContext 

func (d *Database) PrepareContext(ctx context.Context, query string) (*sql.Stmt, error)

Can be used to prepare a query.

You can use this in tandem with a dataset by doing the following. 

sql, args, err := db.From("items").Where(goqu.I("id").Gt(10)).ToSQL(true)
if err != nil{
    panic(err.Error()) //you could gracefully handle the error also
}
stmt, err := db.Prepare(sql)
if err != nil{
    panic(err.Error()) //you could gracefully handle the error also
}
defer stmt.Close()
rows, err := stmt.QueryContext(ctx, args)
if err != nil{
    panic(err.Error()) //you could gracefully handle the error also
}
defer rows.Close()
for rows.Next(){
          //scan your rows
}
if rows.Err() != nil{
    panic(err.Error()) //you could gracefully handle the error also
}

query: The SQL statement to prepare.

func (*Database) Query 

func (d *Database) Query(query string, args ...interface{}) (*sql.Rows, error)

Used to query for multiple rows.

You can use this in tandem with a dataset by doing the following. 

sql, err := db.From("items").Where(goqu.I("id").Gt(10)).ToSQL()
if err != nil{
    panic(err.Error()) //you could gracefully handle the error also
}
rows, err := stmt.Query(args)
if err != nil{
    panic(err.Error()) //you could gracefully handle the error also
}
defer rows.Close()
for rows.Next(){
          //scan your rows
}
if rows.Err() != nil{
    panic(err.Error()) //you could gracefully handle the error also
}

query: The SQL to execute

args...: for any placeholder parameters in the query

func (*Database) QueryContext 

func (d *Database) QueryContext(ctx context.Context, query string, args ...interface{}) (*sql.Rows, error)

Used to query for multiple rows.

You can use this in tandem with a dataset by doing the following. 

sql, err := db.From("items").Where(goqu.I("id").Gt(10)).ToSQL()
if err != nil{
    panic(err.Error()) //you could gracefully handle the error also
}
rows, err := stmt.QueryContext(ctx, args)
if err != nil{
    panic(err.Error()) //you could gracefully handle the error also
}
defer rows.Close()
for rows.Next(){
          //scan your rows
}
if rows.Err() != nil{
    panic(err.Error()) //you could gracefully handle the error also
}

query: The SQL to execute

args...: for any placeholder parameters in the query

func (*Database) QueryRow 

func (d *Database) QueryRow(query string, args ...interface{}) *sql.Row

Used to query for a single row.

You can use this in tandem with a dataset by doing the following. 

sql, err := db.From("items").Where(goqu.I("id").Gt(10)).Limit(1).ToSQL()
if err != nil{
    panic(err.Error()) //you could gracefully handle the error also
}
rows, err := stmt.QueryRow(args)
if err != nil{
    panic(err.Error()) //you could gracefully handle the error also
}
//scan your row

query: The SQL to execute

args...: for any placeholder parameters in the query

func (*Database) QueryRowContext 

func (d *Database) QueryRowContext(ctx context.Context, query string, args ...interface{}) *sql.Row

Used to query for a single row.

You can use this in tandem with a dataset by doing the following. 

sql, err := db.From("items").Where(goqu.I("id").Gt(10)).Limit(1).ToSQL()
if err != nil{
    panic(err.Error()) //you could gracefully handle the error also
}
rows, err := stmt.QueryRowContext(ctx, args)
if err != nil{
    panic(err.Error()) //you could gracefully handle the error also
}
//scan your row

query: The SQL to execute

args...: for any placeholder parameters in the query

func (*Database) ScanStruct 

func (d *Database) ScanStruct(i interface{}, query string, args ...interface{}) (bool, error)

Queries the database using the supplied query, and args and uses CrudExec.ScanStruct to scan the results into a struct

i: A pointer to a struct

query: The SQL to execute

args...: for any placeholder parameters in the query

func (*Database) ScanStructContext 

func (d *Database) ScanStructContext(ctx context.Context, i interface{}, query string, args ...interface{}) (bool, error)

Queries the database using the supplied context, query, and args and uses CrudExec.ScanStructContext to scan the results into a struct

i: A pointer to a struct

query: The SQL to execute

args...: for any placeholder parameters in the query

func (*Database) ScanStructs 

func (d *Database) ScanStructs(i interface{}, query string, args ...interface{}) error

Queries the database using the supplied query, and args and uses CrudExec.ScanStructs to scan the results into a slice of structs

i: A pointer to a slice of structs

query: The SQL to execute

args...: for any placeholder parameters in the query

func (*Database) ScanStructsContext 

func (d *Database) ScanStructsContext(ctx context.Context, i interface{}, query string, args ...interface{}) error

Queries the database using the supplied context, query, and args and uses CrudExec.ScanStructsContext to scan the results into a slice of structs

i: A pointer to a slice of structs

query: The SQL to execute

args...: for any placeholder parameters in the query

func (*Database) ScanVal 

func (d *Database) ScanVal(i interface{}, query string, args ...interface{}) (bool, error)

Queries the database using the supplied query, and args and uses CrudExec.ScanVal to scan the results into a primitive value

i: A pointer to a primitive value

query: The SQL to execute

args...: for any placeholder parameters in the query

func (*Database) ScanValContext 

func (d *Database) ScanValContext(ctx context.Context, i interface{}, query string, args ...interface{}) (bool, error)

Queries the database using the supplied context, query, and args and uses CrudExec.ScanValContext to scan the results into a primitive value

i: A pointer to a primitive value

query: The SQL to execute

args...: for any placeholder parameters in the query

func (*Database) ScanVals 

func (d *Database) ScanVals(i interface{}, query string, args ...interface{}) error

Queries the database using the supplied query, and args and uses CrudExec.ScanVals to scan the results into a slice of primitive values

i: A pointer to a slice of primitive values

query: The SQL to execute

args...: for any placeholder parameters in the query

func (*Database) ScanValsContext 

func (d *Database) ScanValsContext(ctx context.Context, i interface{}, query string, args ...interface{}) error

Queries the database using the supplied context, query, and args and uses CrudExec.ScanValsContext to scan the results into a slice of primitive values

i: A pointer to a slice of primitive values

query: The SQL to execute

args...: for any placeholder parameters in the query

func (*Database) Select 

func (d *Database) Select(cols ...interface{}) *SelectDataset

func (*Database) Trace 

func (d *Database) Trace(op, sqlString string, args ...interface{})

Logs a given operation with the specified sql and arguments

func (*Database) Truncate 

func (d *Database) Truncate(table ...interface{}) *TruncateDataset

func (*Database) Update 

func (d *Database) Update(table interface{}) *UpdateDataset

func (*Database) WithTx 

func (d *Database) WithTx(fn func(*TxDatabase) error) error

WithTx starts a new transaction and executes it in Wrap method

Example 
db := getDb()
var ids []int64
if err := db.WithTx(func(tx *goqu.TxDatabase) error {
	// use tx.From to get a dataset that will execute within this transaction
	update := tx.Update("goqu_user").
		Where(goqu.Ex{"last_name": "Yukon"}).
		Returning("id").
		Set(goqu.Record{"last_name": "Ucon"}).
		Executor()

	return update.ScanVals(&ids)
}); err != nil {
	fmt.Println("An error occurred in transaction\n\t", err.Error())
} else {
	fmt.Printf("Updated users in transaction [ids:=%+v]", ids)
}

Output:

Updated users in transaction [ids:=[1 2 3]]

type DeleteDataset 

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

func Delete 

func Delete(table interface{}) *DeleteDataset
Example 
ds := goqu.Delete("items")

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

Output:

DELETE FROM "items" []

func (*DeleteDataset) AppendSQL added in v9.3.0 

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 := goqu.Dialect("mysql").Delete("test").Limit(10)
sql, _, _ := ds.ClearLimit().ToSQL()
fmt.Println(sql)

Output:

DELETE FROM `test`

func (*DeleteDataset) ClearOrder 

func (dd *DeleteDataset) ClearOrder() *DeleteDataset

Removes the ORDER BY clause. See examples.

Example 
ds := goqu.Delete("test").Order(goqu.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 := goqu.Delete("test").Where(
	goqu.Or(
		goqu.C("a").Gt(10),
		goqu.And(
			goqu.C("b").Lt(10),
			goqu.C("c").IsNull(),
		),
	),
)
sql, _, _ := ds.ClearWhere().ToSQL()
fmt.Println(sql)

Output:

DELETE FROM "test"

func (*DeleteDataset) Clone added in v9.3.0 

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

func (dd *DeleteDataset) Executor() exec.QueryExecutor

Creates an QueryExecutor to execute the query. 

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

See Dataset#ToUpdateSQL for arguments

Example 
db := getDb()

de := db.Delete("goqu_user").
	Where(goqu.Ex{"first_name": "Bob"}).
	Executor()
if r, err := de.Exec(); err != nil {
	fmt.Println(err.Error())
} else {
	c, _ := r.RowsAffected()
	fmt.Printf("Deleted %d users", c)
}

Output:

Deleted 1 users
Example (Returning) 
db := getDb()

de := db.Delete("goqu_user").
	Where(goqu.C("last_name").Eq("Yukon")).
	Returning(goqu.C("id")).
	Executor()

var ids []int64
if err := de.ScanVals(&ids); err != nil {
	fmt.Println(err.Error())
} else {
	fmt.Printf("Deleted users [ids:=%+v]", ids)
}

Output:

Deleted users [ids:=[1 2 3]]

func (*DeleteDataset) Expression added in v9.3.0 

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

func (*DeleteDataset) From 

func (dd *DeleteDataset) From(table interface{}) *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 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

func (*DeleteDataset) GetAs added in v9.3.0 

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 := goqu.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 := goqu.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 := goqu.Dialect("mysql").Delete("test").Order(goqu.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 := goqu.Dialect("mysql").Delete("test").Order(goqu.C("a").Asc())
sql, _, _ := ds.OrderAppend(goqu.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 := goqu.Dialect("mysql").Delete("test").Order(goqu.C("a").Asc())
sql, _, _ := ds.OrderPrepend(goqu.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, _ := goqu.Delete("items").Prepared(true).ToSQL()
fmt.Println(sql, args)

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

Output:

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

func (*DeleteDataset) Returning 

func (dd *DeleteDataset) Returning(returning ...interface{}) *DeleteDataset

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

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

sql, args, _ = ds.Returning("id").Where(goqu.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 added in v9.3.0 

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 []interface{}, 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, _ := goqu.Delete("items").ToSQL()
fmt.Println(sql, args)

sql, args, _ = goqu.Delete("items").
	Where(goqu.Ex{"id": goqu.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, _, _ := goqu.Delete("test").Where(goqu.Ex{
	"a": goqu.Op{"gt": 10},
	"b": goqu.Op{"lt": 10},
	"c": nil,
	"d": []string{"a", "b", "c"},
}).ToSQL()
fmt.Println(sql)
// You can use ExOr to get ORed expressions together
sql, _, _ = goqu.Delete("test").Where(goqu.ExOr{
	"a": goqu.Op{"gt": 10},
	"b": goqu.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, _, _ = goqu.Delete("test").Where(
	goqu.Or(
		goqu.Ex{
			"a": goqu.Op{"gt": 10},
			"b": goqu.Op{"lt": 10},
		},
		goqu.Ex{
			"c": nil,
			"d": []string{"a", "b", "c"},
		},
	),
).ToSQL()
fmt.Println(sql)
// By default everything is anded together
sql, _, _ = goqu.Delete("test").Where(
	goqu.C("a").Gt(10),
	goqu.C("b").Lt(10),
	goqu.C("c").IsNull(),
	goqu.C("d").In("a", "b", "c"),
).ToSQL()
fmt.Println(sql)
// You can use a combination of Ors and Ands
sql, _, _ = goqu.Delete("test").Where(
	goqu.Or(
		goqu.C("a").Gt(10),
		goqu.And(
			goqu.C("b").Lt(10),
			goqu.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, _ := goqu.Delete("test").Prepared(true).Where(goqu.Ex{
	"a": goqu.Op{"gt": 10},
	"b": goqu.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, _ = goqu.Delete("test").Prepared(true).Where(goqu.ExOr{
	"a": goqu.Op{"gt": 10},
	"b": goqu.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, _ = goqu.Delete("test").Prepared(true).Where(
	goqu.Or(
		goqu.Ex{
			"a": goqu.Op{"gt": 10},
			"b": goqu.Op{"lt": 10},
		},
		goqu.Ex{
			"c": nil,
			"d": []string{"a", "b", "c"},
		},
	),
).ToSQL()
fmt.Println(sql, args)
// By default everything is anded together
sql, args, _ = goqu.Delete("test").Prepared(true).Where(
	goqu.C("a").Gt(10),
	goqu.C("b").Lt(10),
	goqu.C("c").IsNull(),
	goqu.C("d").In("a", "b", "c"),
).ToSQL()
fmt.Println(sql, args)
// You can use a combination of Ors and Ands
sql, args, _ = goqu.Delete("test").Prepared(true).Where(
	goqu.Or(
		goqu.C("a").Gt(10),
		goqu.And(
			goqu.C("b").Lt(10),
			goqu.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, _, _ := goqu.Delete("test").
	With("check_vals(val)", goqu.From().Select(goqu.L("123"))).
	Where(goqu.C("val").Eq(goqu.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, _, _ := goqu.Delete("nums").
	WithRecursive("nums(x)",
		goqu.From().Select(goqu.L("1")).
			UnionAll(goqu.From("nums").
				Select(goqu.L("x+1")).Where(goqu.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 goqu.Datasets or goqu.Databases with the specified dialect.

Example (DatasetMysql)

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

// import _ "github.com/doug-martin/goqu/v9/adapters/mysql"

d := goqu.Dialect("mysql")
ds := d.From("test").Where(goqu.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/doug-martin/goqu/v9/adapters/postgres"

d := goqu.Dialect("postgres")
ds := d.From("test").Where(goqu.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/doug-martin/goqu/v9/adapters/sqlite3"

d := goqu.Dialect("sqlite3")
ds := d.From("test").Where(goqu.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/doug-martin/goqu/v9/adapters/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 := goqu.Dialect("mysql")

db := d.DB(mDb)

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

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

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

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

Output:

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

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

// import _ "github.com/doug-martin/goqu/v9/adapters/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 := goqu.Dialect("postgres")

db := d.DB(mDb)

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

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

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

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

Output:

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

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

// import _ "github.com/doug-martin/goqu/v9/adapters/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 := goqu.Dialect("sqlite3")

db := d.DB(mDb)

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

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

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

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

Output:

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

func (DialectWrapper) DB 

func (dw DialectWrapper) DB(db SQLDatabase) *Database

func (DialectWrapper) Delete 

func (dw DialectWrapper) Delete(table interface{}) *DeleteDataset

Create a new dataset for creating DELETE sql statements

func (DialectWrapper) From 

func (dw DialectWrapper) From(table ...interface{}) *SelectDataset

Create a new dataset for creating SELECT sql statements

func (DialectWrapper) Insert 

func (dw DialectWrapper) Insert(table interface{}) *InsertDataset

Create a new dataset for creating INSERT sql statements

func (DialectWrapper) Select 

func (dw DialectWrapper) Select(cols ...interface{}) *SelectDataset

Create a new dataset for creating SELECT sql statements

func (DialectWrapper) Truncate 

func (dw DialectWrapper) Truncate(table ...interface{}) *TruncateDataset

Create a new dataset for creating TRUNCATE sql statements

func (DialectWrapper) Update 

func (dw DialectWrapper) Update(table interface{}) *UpdateDataset

Create a new dataset for creating UPDATE sql statements

type Ex 

type Ex = exp.Ex
Example 
ds := goqu.From("items").Where(
	goqu.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, _, _ := goqu.From("test").Where(goqu.Ex{
	"a": []string{"a", "b", "c"},
}).ToSQL()
fmt.Println(sql)

Output:

SELECT * FROM "test" WHERE ("a" IN ('a', 'b', 'c'))
Example (WithOp) 
sql, args, _ := goqu.From("items").Where(
	goqu.Ex{
		"col1": goqu.Op{"neq": "a"},
		"col3": goqu.Op{"isNot": true},
		"col6": goqu.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, _ := goqu.From("items").Where(
	goqu.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, _, _ := goqu.From("items").Where(goqu.ExOr{
	"col1": goqu.Op{"neq": "a"},
	"col3": goqu.Op{"isNot": true},
	"col6": goqu.Op{"notIn": []string{"a", "b", "c"}},
}).ToSQL()
fmt.Println(sql)

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

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

sql, _, _ = goqu.From("items").Where(goqu.ExOr{
	"col1": goqu.Op{"like": regexp.MustCompile("^(a|b)")},
	"col2": goqu.Op{"notLike": regexp.MustCompile("^(a|b)")},
	"col3": goqu.Op{"iLike": regexp.MustCompile("^(a|b)")},
	"col4": goqu.Op{"notILike": regexp.MustCompile("^(a|b)")},
}).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" ~ '^(a|b)') OR ("col2" !~ '^(a|b)') OR ("col3" ~* '^(a|b)') OR ("col4" !~* '^(a|b)'))

type Expression 

type Expression = exp.Expression

type InsertDataset 

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

func Insert 

func Insert(table interface{}) *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 (ColsAndVals) 
ds := goqu.Insert("user").
	Cols("first_name", "last_name").
	Vals(
		goqu.Vals{"Greg", "Farley"},
		goqu.Vals{"Jimmy", "Stewart"},
		goqu.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 := goqu.Insert("user").Prepared(true).
	FromQuery(goqu.From("other_table"))
insertSQL, args, _ := ds.ToSQL()
fmt.Println(insertSQL, args)

Output:

INSERT INTO "user" SELECT * FROM "other_table" []
Example (FromQueryWithCols) 
ds := goqu.Insert("user").Prepared(true).
	Cols("first_name", "last_name").
	FromQuery(goqu.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 (GoquRecord) 
ds := goqu.Insert("user").Rows(
	goqu.Record{"first_name": "Greg", "last_name": "Farley"},
	goqu.Record{"first_name": "Jimmy", "last_name": "Stewart"},
	goqu.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 (Map) 
ds := goqu.Insert("user").Rows(
	map[string]interface{}{"first_name": "Greg", "last_name": "Farley"},
	map[string]interface{}{"first_name": "Jimmy", "last_name": "Stewart"},
	map[string]interface{}{"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 := goqu.Insert("user").Prepared(true).Rows(
	goqu.Record{"first_name": "Greg", "last_name": "Farley"},
	goqu.Record{"first_name": "Jimmy", "last_name": "Stewart"},
	goqu.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 := goqu.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 added in v9.3.0 

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

func (id *InsertDataset) ClearCols() *InsertDataset

Clears the Columns to insert into

Example 
ds := goqu.Insert("test").Cols("a", "b", "c")
insertSQL, _, _ := ds.ClearCols().Cols("other_a", "other_b", "other_c").
	FromQuery(goqu.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" goqu:"skipinsert"`
	Address string `db:"address"`
	Name    string `db:"name"`
}
ds := goqu.Insert("items").OnConflict(goqu.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 `goqu:"skipinsert"`
	Address string
	Name    string
}
ds := goqu.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, _, _ := goqu.Insert("test").
	Cols("a", "b", "c").
	Vals(
		[]interface{}{"a1", "b1", "c1"},
		[]interface{}{"a2", "b1", "c1"},
		[]interface{}{"a3", "b1", "c1"},
	).
	ClearVals().
	ToSQL()
fmt.Println(insertSQL)

insertSQL, _, _ = goqu.Insert("test").
	Cols("a", "b", "c").
	Vals([]interface{}{"a1", "b1", "c1"}).
	Vals([]interface{}{"a2", "b2", "c2"}).
	Vals([]interface{}{"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 ...interface{}) *InsertDataset

Sets the Columns to insert into

Example 
insertSQL, _, _ := goqu.Insert("test").
	Cols("a", "b", "c").
	Vals(
		[]interface{}{"a1", "b1", "c1"},
		[]interface{}{"a2", "b1", "c1"},
		[]interface{}{"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, _, _ := goqu.Insert("test").
	Cols("a", "b", "c").
	FromQuery(goqu.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 ...interface{}) *InsertDataset

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

Example 
insertSQL, _, _ := goqu.Insert("test").
	Cols("a", "b").
	ColsAppend("c").
	Vals(
		[]interface{}{"a1", "b1", "c1"},
		[]interface{}{"a2", "b1", "c1"},
		[]interface{}{"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) Executor 

func (id *InsertDataset) Executor() exec.QueryExecutor

Generates the INSERT sql, and returns an QueryExecutor struct with the sql set to the INSERT statement 

db.Insert("test").Rows(Record{"name":"Bob"}).Executor().Exec()
Example (RecordReturning) 
db := getDb()

type User struct {
	ID        sql.NullInt64 `db:"id"`
	FirstName string        `db:"first_name"`
	LastName  string        `db:"last_name"`
	Created   time.Time     `db:"created"`
}

insert := db.Insert("goqu_user").Returning(goqu.C("id")).Rows(
	goqu.Record{"first_name": "Jed", "last_name": "Riley", "created": time.Now()},
).Executor()
var id int64
if _, err := insert.ScanVal(&id); err != nil {
	fmt.Println(err.Error())
} else {
	fmt.Printf("Inserted 1 user id:=%d\n", id)
}

insert = db.Insert("goqu_user").Returning(goqu.Star()).Rows([]goqu.Record{
	{"first_name": "Greg", "last_name": "Farley", "created": time.Now()},
	{"first_name": "Jimmy", "last_name": "Stewart", "created": time.Now()},
	{"first_name": "Jeff", "last_name": "Jeffers", "created": time.Now()},
}).Executor()
var insertedUsers []User
if err := insert.ScanStructs(&insertedUsers); err != nil {
	fmt.Println(err.Error())
} else {
	for _, u := range insertedUsers {
		fmt.Printf("Inserted user: [ID=%d], [FirstName=%+s] [LastName=%s]\n", u.ID.Int64, u.FirstName, u.LastName)
	}
}

Output:

Inserted 1 user id:=5
Inserted user: [ID=6], [FirstName=Greg] [LastName=Farley]
Inserted user: [ID=7], [FirstName=Jimmy] [LastName=Stewart]
Inserted user: [ID=8], [FirstName=Jeff] [LastName=Jeffers]
Example (ScanStructs) 
db := getDb()

type User struct {
	ID        sql.NullInt64 `db:"id" goqu:"skipinsert"`
	FirstName string        `db:"first_name"`
	LastName  string        `db:"last_name"`
	Created   time.Time     `db:"created"`
}

insert := db.Insert("goqu_user").Returning("id").Rows(
	User{FirstName: "Jed", LastName: "Riley"},
).Executor()
var id int64
if _, err := insert.ScanVal(&id); err != nil {
	fmt.Println(err.Error())
} else {
	fmt.Printf("Inserted 1 user id:=%d\n", id)
}

insert = db.Insert("goqu_user").Returning(goqu.Star()).Rows([]User{
	{FirstName: "Greg", LastName: "Farley", Created: time.Now()},
	{FirstName: "Jimmy", LastName: "Stewart", Created: time.Now()},
	{FirstName: "Jeff", LastName: "Jeffers", Created: time.Now()},
}).Executor()
var insertedUsers []User
if err := insert.ScanStructs(&insertedUsers); err != nil {
	fmt.Println(err.Error())
} else {
	for _, u := range insertedUsers {
		fmt.Printf("Inserted user: [ID=%d], [FirstName=%+s] [LastName=%s]\n", u.ID.Int64, u.FirstName, u.LastName)
	}
}

Output:

Inserted 1 user id:=5
Inserted user: [ID=6], [FirstName=Greg] [LastName=Farley]
Inserted user: [ID=7], [FirstName=Jimmy] [LastName=Stewart]
Inserted user: [ID=8], [FirstName=Jeff] [LastName=Jeffers]
Example (WithRecord) 
db := getDb()
insert := db.Insert("goqu_user").Rows(
	goqu.Record{"first_name": "Jed", "last_name": "Riley", "created": time.Now()},
).Executor()
if _, err := insert.Exec(); err != nil {
	fmt.Println(err.Error())
} else {
	fmt.Println("Inserted 1 user")
}

users := []goqu.Record{
	{"first_name": "Greg", "last_name": "Farley", "created": time.Now()},
	{"first_name": "Jimmy", "last_name": "Stewart", "created": time.Now()},
	{"first_name": "Jeff", "last_name": "Jeffers", "created": time.Now()},
}
if _, err := db.Insert("goqu_user").Rows(users).Executor().Exec(); err != nil {
	fmt.Println(err.Error())
} else {
	fmt.Printf("Inserted %d users", len(users))
}

Output:

Inserted 1 user
Inserted 3 users

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, _, _ := goqu.Insert("test").
	FromQuery(goqu.From("test2").Where(goqu.C("age").Gt(10))).
	ToSQL()
fmt.Println(insertSQL)

Output:

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

func (*InsertDataset) GetAs added in v9.3.0 

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 interface{}) *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 := goqu.Insert("test")
insertSQL, _, _ := ds.Into("test2").Rows(goqu.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 := goqu.Insert("test")
insertSQL, _, _ := ds.
	Into(goqu.T("test").As("t")).
	Rows(goqu.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" goqu:"skipinsert"`
	Address string `db:"address"`
	Name    string `db:"name"`
}
insertSQL, args, _ := goqu.Insert("items").Rows(
	item{Name: "Test1", Address: "111 Test Addr"},
	item{Name: "Test2", Address: "112 Test Addr"},
).OnConflict(goqu.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, _ := goqu.Insert("items").
	Rows(
		goqu.Record{"name": "Test1", "address": "111 Test Addr"},
		goqu.Record{"name": "Test2", "address": "112 Test Addr"},
	).
	OnConflict(goqu.DoUpdate("key", goqu.Record{"updated": goqu.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" goqu:"skipinsert"`
	Address string `db:"address"`
	Name    string `db:"name"`
}
insertSQL, args, _ := goqu.Insert("items").
	Rows([]item{
		{Name: "Test1", Address: "111 Test Addr"},
		{Name: "Test2", Address: "112 Test Addr"},
	}).
	OnConflict(goqu.DoUpdate(
		"key",
		goqu.Record{"updated": goqu.L("NOW()")}).Where(goqu.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" goqu:"skipinsert"`
	Address string `db:"address"`
	Name    string `db:"name"`
}

insertSQL, args, _ := goqu.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, _ = goqu.Insert("items").Prepared(true).Rows(
	goqu.Record{"name": "Test1", "address": "111 Test Addr"},
	goqu.Record{"name": "Test2", "address": "112 Test Addr"},
).ToSQL()
fmt.Println(insertSQL, args)

insertSQL, args, _ = goqu.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, _ = goqu.Insert("items").Prepared(true).Rows(
	[]goqu.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) Returning 

func (id *InsertDataset) Returning(returning ...interface{}) *InsertDataset

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

Example 
insertSQL, _, _ := goqu.Insert("test").
	Returning("id").
	Rows(goqu.Record{"a": "a", "b": "b"}).
	ToSQL()
fmt.Println(insertSQL)
insertSQL, _, _ = goqu.Insert("test").
	Returning(goqu.T("test").All()).
	Rows(goqu.Record{"a": "a", "b": "b"}).
	ToSQL()
fmt.Println(insertSQL)
insertSQL, _, _ = goqu.Insert("test").
	Returning("a", "b").
	Rows(goqu.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 added in v9.3.0 

func (id *InsertDataset) ReturnsColumns() bool

func (*InsertDataset) Rows 

func (id *InsertDataset) Rows(rows ...interface{}) *InsertDataset

Insert rows. Rows can be a map, goqu.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 := goqu.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 (WithGoquDefaultIfEmptyTag) 
type item struct {
	ID      uint32 `goqu:"skipinsert"`
	Address string
	Name    string `goqu:"defaultifempty"`
}
insertSQL, args, _ := goqu.Insert("items").
	Rows(
		item{Name: "Test1", Address: "111 Test Addr"},
		item{Address: "112 Test Addr"},
	).
	ToSQL()
fmt.Println(insertSQL, args)

insertSQL, args, _ = goqu.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 (WithGoquSkipInsertTag) 
type item struct {
	ID      uint32 `goqu:"skipinsert"`
	Address string
	Name    string `goqu:"skipinsert"`
}
insertSQL, args, _ := goqu.Insert("items").
	Rows(
		item{Name: "Test1", Address: "111 Test Addr"},
		item{Name: "Test2", Address: "112 Test Addr"},
	).
	ToSQL()
fmt.Println(insertSQL, args)

insertSQL, args, _ = goqu.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') []
Example (WithIgnoredEmbedded) 
type Address struct {
	Street string
	State  string
}
type User struct {
	Address   `db:"-"`
	FirstName string
	LastName  string
}
ds := goqu.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 := goqu.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 `goqu:"skipinsert"`
	Address string
	Name    string
}
insertSQL, args, _ := goqu.Insert("items").
	Rows(
		item{Name: "Test1", Address: "111 Test Addr"},
		item{Name: "Test2", Address: "112 Test Addr"},
	).
	ToSQL()
fmt.Println(insertSQL, args)

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

insertSQL, args, _ = goqu.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') []

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 []interface{}, 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 goqu tag with `skipinsert` 

type Item struct{
   Id   uint32 `db:"id" goqu:"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" goqu:"skipinsert"`
	Address string `db:"address"`
	Name    string `db:"name"`
}
insertSQL, args, _ := goqu.Insert("items").Rows(
	item{Name: "Test1", Address: "111 Test Addr"},
	item{Name: "Test2", Address: "112 Test Addr"},
).ToSQL()
fmt.Println(insertSQL, args)

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

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

insertSQL, args, _ = goqu.From("items").Insert().Rows(
	[]goqu.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 ...[]interface{}) *InsertDataset

Manually set values to insert See examples.

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

insertSQL, _, _ = goqu.Insert("test").
	Cols("a", "b", "c").
	Vals([]interface{}{"a1", "b1", "c1"}).
	Vals([]interface{}{"a2", "b2", "c2"}).
	Vals([]interface{}{"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, _, _ := goqu.Insert("foo").
	With("other", goqu.From("bar").Where(goqu.C("id").Gt(10))).
	FromQuery(goqu.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, _, _ := goqu.Insert("num_count").
	WithRecursive("nums(x)",
		goqu.From().Select(goqu.L("1")).
			UnionAll(goqu.From("nums").
				Select(goqu.L("x+1")).Where(goqu.C("x").Lt(5))),
	).
	FromQuery(goqu.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 Logger 

type Logger interface {
	Printf(format string, v ...interface{})
}

type Op 

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

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

ds = goqu.From("test").Where(goqu.Ex{
	"a": goqu.Op{"notBetween": goqu.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 := goqu.From("test").Where(goqu.Ex{
	"a": 10,
	"b": goqu.Op{"neq": 10},
	"c": goqu.Op{"gte": 10},
	"d": goqu.Op{"lt": 10},
	"e": goqu.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 := goqu.From("test").Where(goqu.Ex{
	"a": goqu.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 = goqu.From("test").Where(goqu.Ex{
	"a": goqu.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 := goqu.From("test").Where(goqu.Ex{
	"a": true,
})
sql, args, _ := ds.ToSQL()
fmt.Println(sql, args)
sql, args, _ = ds.Prepared(true).ToSQL()
fmt.Println(sql, args)

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

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

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

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

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

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

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

ds = goqu.From("test").Where(goqu.Ex{
	"a": goqu.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 := goqu.From("test").Where(goqu.Ex{
	"a": goqu.Op{"like": "%a%"},
})
sql, args, _ := ds.ToSQL()
fmt.Println(sql, args)

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

ds = goqu.From("test").Where(goqu.Ex{
	"a": goqu.Op{"like": regexp.MustCompile("(a|b)")},
})

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

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

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

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

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

ds = goqu.From("test").Where(goqu.Ex{
	"a": goqu.Op{"iLike": regexp.MustCompile("(a|b)")},
})

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

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

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

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

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

ds = goqu.From("test").Where(goqu.Ex{
	"a": goqu.Op{"notLike": regexp.MustCompile("(a|b)")},
})

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

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

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

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

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

ds = goqu.From("test").Where(goqu.Ex{
	"a": goqu.Op{"notILike": regexp.MustCompile("(a|b)")},
})
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" ~ '(a|b)') []
SELECT * FROM "test" WHERE ("a" ~ ?) [(a|b)]
SELECT * FROM "test" WHERE ("a" ILIKE '%a%') []
SELECT * FROM "test" WHERE ("a" ILIKE ?) [%a%]
SELECT * FROM "test" WHERE ("a" ~* '(a|b)') []
SELECT * FROM "test" WHERE ("a" ~* ?) [(a|b)]
SELECT * FROM "test" WHERE ("a" NOT LIKE '%a%') []
SELECT * FROM "test" WHERE ("a" NOT LIKE ?) [%a%]
SELECT * FROM "test" WHERE ("a" !~ '(a|b)') []
SELECT * FROM "test" WHERE ("a" !~ ?) [(a|b)]
SELECT * FROM "test" WHERE ("a" NOT ILIKE '%a%') []
SELECT * FROM "test" WHERE ("a" NOT ILIKE ?) [%a%]
SELECT * FROM "test" WHERE ("a" !~* '(a|b)') []
SELECT * FROM "test" WHERE ("a" !~* ?) [(a|b)]
Example (WithMultipleKeys)

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

ds := goqu.From("items").Where(goqu.Ex{
	"col1": goqu.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 := goqu.Insert("test")

records := []goqu.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 := goqu.Update("test")
update := goqu.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 SQLDatabase 

type SQLDatabase interface {
	Begin() (*sql.Tx, error)
	BeginTx(ctx context.Context, opts *sql.TxOptions) (*sql.Tx, error)
	ExecContext(ctx context.Context, query string, args ...interface{}) (sql.Result, error)
	PrepareContext(ctx context.Context, query string) (*sql.Stmt, error)
	QueryContext(ctx context.Context, query string, args ...interface{}) (*sql.Rows, error)
	QueryRowContext(ctx context.Context, query string, args ...interface{}) *sql.Row
}

Interface for sql.DB, an interface is used so you can use with other libraries such as sqlx instead of the native sql.DB

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 SQLTx 

type SQLTx interface {
	ExecContext(ctx context.Context, query string, args ...interface{}) (sql.Result, error)
	PrepareContext(ctx context.Context, query string) (*sql.Stmt, error)
	QueryContext(ctx context.Context, query string, args ...interface{}) (*sql.Rows, error)
	QueryRowContext(ctx context.Context, query string, args ...interface{}) *sql.Row
	Commit() error
	Rollback() error
}

Interface for sql.Tx, an interface is used so you can use with other libraries such as sqlx instead of the native sql.DB

type SelectDataset 

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

Dataset for creating and/or executing SELECT SQL statements.

Example 
ds := goqu.From("test").
	Select(goqu.COUNT("*")).
	InnerJoin(goqu.T("test2"), goqu.On(goqu.I("test.fkey").Eq(goqu.I("test2.id")))).
	LeftJoin(goqu.T("test3"), goqu.On(goqu.I("test2.fkey").Eq(goqu.I("test3.id")))).
	Where(
		goqu.Ex{
			"test.name": goqu.Op{
				"like": regexp.MustCompile("^(a|b)"),
			},
			"test2.amount": goqu.Op{
				"isNot": nil,
			},
		},
		goqu.ExOr{
			"test3.id":     nil,
			"test3.status": []string{"passed", "active", "registered"},
		}).
	Order(goqu.I("test.created").Desc().NullsLast()).
	GroupBy(goqu.I("test.user_id")).
	Having(goqu.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 ...interface{}) *SelectDataset
Example 
sql, args, _ := goqu.From("test").ToSQL()
fmt.Println(sql, args)

Output:

SELECT * FROM "test" []

func Select 

func Select(cols ...interface{}) *SelectDataset
Example 
sql, _, _ := goqu.Select(goqu.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 := goqu.From("test").As("t")
sql, _, _ := goqu.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 := goqu.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 := goqu.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 := goqu.From("test").Order(goqu.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 := goqu.From("test").Select("a", "b")
sql, _, _ := ds.ClearSelect().ToSQL()
fmt.Println(sql)
ds = goqu.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 := goqu.From("test").Where(
	goqu.Or(
		goqu.C("a").Gt(10),
		goqu.And(
			goqu.C("b").Lt(10),
			goqu.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#ScanVal to scan the result into an int64.

Example 
if count, err := getDb().From("goqu_user").Count(); err != nil {
	fmt.Println(err.Error())
} else {
	fmt.Printf("\nCount:= %d", count)
}

Output:

Count:= 4

func (*SelectDataset) CountContext 

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

Generates the SELECT COUNT(*) sql for this dataset and uses Exec#ScanValContext 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, _, _ := goqu.From("test").CrossJoin(goqu.T("test2")).ToSQL()
fmt.Println(sql)

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

sql, _, _ = goqu.From("test").CrossJoin(
	goqu.From("test2").Where(goqu.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, _ := goqu.From("items").Delete().ToSQL()
fmt.Println(sql, args)

sql, args, _ = goqu.From("items").
	Where(goqu.Ex{"id": goqu.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 ...interface{}) *SelectDataset
Example 
sql, _, _ := goqu.From("test").Select("a", "b").Distinct().ToSQL()
fmt.Println(sql)

Output:

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

Output:

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

Output:

SELECT DISTINCT ON (COALESCE("a", 'empty')) * FROM "test"
Example (OnWithLiteral) 
sql, _, _ := goqu.From("test").Distinct(goqu.L("COALESCE(?, ?)", goqu.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) Executor 

func (sd *SelectDataset) Executor() exec.QueryExecutor

Generates the SELECT sql, and returns an Exec struct with the sql set to the SELECT statement 

db.From("test").Select("col").Executor()

See Dataset#ToUpdateSQL for arguments

Example (ScannerScanStruct) 
type User struct {
	FirstName string `db:"first_name"`
	LastName  string `db:"last_name"`
}
db := getDb()

scanner, err := db.
	From("goqu_user").
	Select("first_name", "last_name").
	Where(goqu.Ex{
		"last_name": "Yukon",
	}).
	Executor().
	Scanner()

if err != nil {
	fmt.Println(err.Error())
	return
}

defer scanner.Close()

for scanner.Next() {
	u := User{}

	err = scanner.ScanStruct(&u)
	if err != nil {
		fmt.Println(err.Error())
		return
	}

	fmt.Printf("\n%+v", u)
}

if scanner.Err() != nil {
	fmt.Println(scanner.Err().Error())
}

Output:

{FirstName:Bob LastName:Yukon}
{FirstName:Sally LastName:Yukon}
{FirstName:Vinita LastName:Yukon}
Example (ScannerScanVal) 
db := getDb()

scanner, err := db.
	From("goqu_user").
	Select("first_name").
	Where(goqu.Ex{
		"last_name": "Yukon",
	}).
	Executor().
	Scanner()

if err != nil {
	fmt.Println(err.Error())
	return
}

defer scanner.Close()

for scanner.Next() {
	name := ""

	err = scanner.ScanVal(&name)
	if err != nil {
		fmt.Println(err.Error())
		return
	}

	fmt.Println(name)
}

if scanner.Err() != nil {
	fmt.Println(scanner.Err().Error())
}

Output:

Bob
Sally
Vinita

func (*SelectDataset) Expression 

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

func (*SelectDataset) ForKeyShare 

func (sd *SelectDataset) ForKeyShare(waitOption exp.WaitOption) *SelectDataset

Adds a FOR KEY SHARE clause. See examples.

func (*SelectDataset) ForNoKeyUpdate 

func (sd *SelectDataset) ForNoKeyUpdate(waitOption exp.WaitOption) *SelectDataset

Adds a FOR NO KEY UPDATE clause. See examples.

func (*SelectDataset) ForShare 

func (sd *SelectDataset) ForShare(waitOption exp.WaitOption) *SelectDataset

Adds a FOR SHARE clause. See examples.

func (*SelectDataset) ForUpdate 

func (sd *SelectDataset) ForUpdate(waitOption exp.WaitOption) *SelectDataset

Adds a FOR UPDATE clause. See examples.

func (*SelectDataset) From 

func (sd *SelectDataset) From(from ...interface{}) *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 := goqu.From("test")
sql, _, _ := ds.From("test2").ToSQL()
fmt.Println(sql)

Output:

SELECT * FROM "test2"
Example (WithAliasedDataset) 
ds := goqu.From("test")
fromDs := ds.Where(goqu.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 := goqu.From("test")
fromDs := ds.Where(goqu.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, _, _ := goqu.From("test").FromSelf().ToSQL()
fmt.Println(sql)
sql, _, _ = goqu.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, _, _ := goqu.From("test").FullJoin(
	goqu.T("test2"),
	goqu.On(goqu.Ex{
		"test.fkey": goqu.I("test2.Id"),
	}),
).ToSQL()
fmt.Println(sql)

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

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

sql, _, _ = goqu.From("test").FullJoin(
	goqu.From("test2").Where(goqu.C("amount").Gt(0)).As("t"),
	goqu.On(goqu.I("test.fkey").Eq(goqu.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, _, _ := goqu.From("test").FullOuterJoin(
	goqu.T("test2"),
	goqu.On(goqu.Ex{
		"test.fkey": goqu.I("test2.Id"),
	}),
).ToSQL()
fmt.Println(sql)

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

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

sql, _, _ = goqu.From("test").FullOuterJoin(
	goqu.From("test2").Where(goqu.C("amount").Gt(0)).As("t"),
	goqu.On(goqu.I("test.fkey").Eq(goqu.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 added in v9.3.0 

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 ...interface{}) *SelectDataset

Adds a GROUP BY clause. See examples.

Example 
sql, _, _ := goqu.From("test").
	Select(goqu.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) Having 

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

Adds a HAVING clause. See examples.

Example 
sql, _, _ := goqu.From("test").Having(goqu.SUM("income").Gt(1000)).ToSQL()
fmt.Println(sql)
sql, _, _ = goqu.From("test").GroupBy("age").Having(goqu.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, _, _ := goqu.From("test").InnerJoin(
	goqu.T("test2"),
	goqu.On(goqu.Ex{
		"test.fkey": goqu.I("test2.Id"),
	}),
).ToSQL()
fmt.Println(sql)

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

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

sql, _, _ = goqu.From("test").InnerJoin(
	goqu.From("test2").Where(goqu.C("amount").Gt(0)).As("t"),
	goqu.On(goqu.I("test.fkey").Eq(goqu.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" goqu:"skipinsert"`
	Address string `db:"address"`
	Name    string `db:"name"`
}
sql, args, _ := goqu.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, _ = goqu.From("items").Insert().Rows(
	goqu.Record{"name": "Test1", "address": "111 Test Addr"},
	goqu.Record{"name": "Test2", "address": "112 Test Addr"},
).ToSQL()
fmt.Println(sql, args)

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

sql, args, _ = goqu.From("items").Insert().Rows(
	[]goqu.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, _, _ := goqu.From("test").
	Intersect(goqu.From("test2")).
	ToSQL()
fmt.Println(sql)
sql, _, _ = goqu.From("test").
	Limit(1).
	Intersect(goqu.From("test2")).
	ToSQL()
fmt.Println(sql)
sql, _, _ = goqu.From("test").
	Limit(1).
	Intersect(goqu.From("test2").
		Order(goqu.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, _, _ := goqu.From("test").
	IntersectAll(goqu.From("test2")).
	ToSQL()
fmt.Println(sql)
sql, _, _ = goqu.From("test").
	Limit(1).
	IntersectAll(goqu.From("test2")).
	ToSQL()
fmt.Println(sql)
sql, _, _ = goqu.From("test").
	Limit(1).
	IntersectAll(goqu.From("test2").
		Order(goqu.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, _, _ := goqu.From("test").Join(
	goqu.T("test2"),
	goqu.On(goqu.Ex{"test.fkey": goqu.I("test2.Id")}),
).ToSQL()
fmt.Println(sql)

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

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

sql, _, _ = goqu.From("test").Join(
	goqu.From("test2").Where(goqu.C("amount").Gt(0)).As("t"),
	goqu.On(goqu.T("test").Col("fkey").Eq(goqu.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, _, _ := goqu.From("test").LeftJoin(
	goqu.T("test2"),
	goqu.On(goqu.Ex{
		"test.fkey": goqu.I("test2.Id"),
	}),
).ToSQL()
fmt.Println(sql)

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

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

sql, _, _ = goqu.From("test").LeftJoin(
	goqu.From("test2").Where(goqu.C("amount").Gt(0)).As("t"),
	goqu.On(goqu.I("test.fkey").Eq(goqu.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, _, _ := goqu.From("test").LeftOuterJoin(
	goqu.T("test2"),
	goqu.On(goqu.Ex{
		"test.fkey": goqu.I("test2.Id"),
	}),
).ToSQL()
fmt.Println(sql)

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

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

sql, _, _ = goqu.From("test").LeftOuterJoin(
	goqu.From("test2").Where(goqu.C("amount").Gt(0)).As("t"),
	goqu.On(goqu.I("test.fkey").Eq(goqu.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 := goqu.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 := goqu.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, _, _ := goqu.From("test").NaturalFullJoin(goqu.T("test2")).ToSQL()
fmt.Println(sql)

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

sql, _, _ = goqu.From("test").NaturalFullJoin(
	goqu.From("test2").Where(goqu.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, _, _ := goqu.From("test").NaturalJoin(goqu.T("test2")).ToSQL()
fmt.Println(sql)

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

sql, _, _ = goqu.From("test").NaturalJoin(
	goqu.From("test2").Where(goqu.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, _, _ := goqu.From("test").NaturalLeftJoin(goqu.T("test2")).ToSQL()
fmt.Println(sql)

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

sql, _, _ = goqu.From("test").NaturalLeftJoin(
	goqu.From("test2").Where(goqu.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, _, _ := goqu.From("test").NaturalRightJoin(goqu.T("test2")).ToSQL()
fmt.Println(sql)

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

sql, _, _ = goqu.From("test").NaturalRightJoin(
	goqu.From("test2").Where(goqu.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 := goqu.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 := goqu.From("test").Order(goqu.C("a").Asc())
sql, _, _ := ds.ToSQL()
fmt.Println(sql)

Output:

SELECT * FROM "test" ORDER BY "a" 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 := goqu.From("test").Order(goqu.C("a").Asc())
sql, _, _ := ds.OrderAppend(goqu.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 := goqu.From("test").Order(goqu.C("a").Asc())
sql, _, _ := ds.OrderPrepend(goqu.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(i interface{}, col string) error

Generates the SELECT sql only selecting the passed in column and uses Exec#ScanVals 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("goqu_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, i interface{}, col string) error

Generates the SELECT sql only selecting the passed in column and uses Exec#ScanValsContext 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, _ := goqu.From("items").Prepared(true).Where(goqu.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) ReturnsColumns added in v9.3.0 

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, _, _ := goqu.From("test").RightJoin(
	goqu.T("test2"),
	goqu.On(goqu.Ex{
		"test.fkey": goqu.I("test2.Id"),
	}),
).ToSQL()
fmt.Println(sql)

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

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

sql, _, _ = goqu.From("test").RightJoin(
	goqu.From("test2").Where(goqu.C("amount").Gt(0)).As("t"),
	goqu.On(goqu.I("test.fkey").Eq(goqu.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, _, _ := goqu.From("test").RightOuterJoin(
	goqu.T("test2"),
	goqu.On(goqu.Ex{
		"test.fkey": goqu.I("test2.Id"),
	}),
).ToSQL()
fmt.Println(sql)

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

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

sql, _, _ = goqu.From("test").RightOuterJoin(
	goqu.From("test2").Where(goqu.C("amount").Gt(0)).As("t"),
	goqu.On(goqu.I("test.fkey").Eq(goqu.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) ScanStruct 

func (sd *SelectDataset) ScanStruct(i interface{}) (bool, error)

Generates the SELECT sql for this dataset and uses Exec#ScanStruct to scan the result into a slice of structs

ScanStruct 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("goqu_user").Where(goqu.C("first_name").Eq(name))
	found, err := ds.ScanStruct(&user)
	switch {
	case err != nil:
		fmt.Println(err.Error())
	case !found:
		fmt.Printf("No user found for first_name %s\n", name)
	default:
		fmt.Printf("Found user: %+v\n", user)
	}
}

findUserByName("Bob")
findUserByName("Zeb")

Output:

Found user: {FirstName:Bob LastName:Yukon}
No user found for first_name Zeb
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 User `db:"goqu_user"` // tag as the "goqu_user" table
	Role Role `db:"user_role"` // tag as "user_role" table
}
db := getDb()
findUserAndRoleByName := func(name string) {
	var userAndRole UserAndRole
	ds := db.
		From("goqu_user").
		Join(
			goqu.T("user_role"),
			goqu.On(goqu.I("goqu_user.id").Eq(goqu.I("user_role.user_id"))),
		).
		Where(goqu.C("first_name").Eq(name))
	found, err := ds.ScanStruct(&userAndRole)
	switch {
	case err != nil:
		fmt.Println(err.Error())
	case !found:
		fmt.Printf("No user found for first_name %s\n", name)
	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}}
No user found for first_name Zeb
Example (WithJoinManualSelect)

In this example we create a new struct that has the user properties as well as a nested Role struct from the join table 

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"`
	Role      Role   `db:"user_role"` // tag as "user_role" table
}
db := getDb()
findUserByName := func(name string) {
	var userAndRole User
	ds := db.
		Select(
			"goqu_user.id",
			"goqu_user.first_name",
			"goqu_user.last_name",
			// alias the fully qualified identifier `C` is important here so it doesnt parse it
			goqu.I("user_role.user_id").As(goqu.C("user_role.user_id")),
			goqu.I("user_role.name").As(goqu.C("user_role.name")),
		).
		From("goqu_user").
		Join(
			goqu.T("user_role"),
			goqu.On(goqu.I("goqu_user.id").Eq(goqu.I("user_role.user_id"))),
		).
		Where(goqu.C("first_name").Eq(name))
	found, err := ds.ScanStruct(&userAndRole)
	switch {
	case err != nil:
		fmt.Println(err.Error())
	case !found:
		fmt.Printf("No user found for first_name %s\n", name)
	default:
		fmt.Printf("Found user and role: %+v\n", userAndRole)
	}
}

findUserByName("Bob")
findUserByName("Zeb")

Output:

Found user and role: {ID:1 FirstName:Bob LastName:Yukon Role:{UserID:1 Name:Admin}}
No user found for first_name Zeb

func (*SelectDataset) ScanStructContext 

func (sd *SelectDataset) ScanStructContext(ctx context.Context, i interface{}) (bool, error)

Generates the SELECT sql for this dataset and uses Exec#ScanStructContext to scan the result into a slice of structs

ScanStructContext 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) ScanStructs 

func (sd *SelectDataset) ScanStructs(i interface{}) error

Generates the SELECT sql for this dataset and uses Exec#ScanStructs to scan the results into a slice of structs.

ScanStructs 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("goqu_user").ScanStructs(&users); err != nil {
	fmt.Println(err.Error())
	return
}
fmt.Printf("\n%+v", users)

users = users[0:0]
if err := db.From("goqu_user").Select("first_name").ScanStructs(&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("goqu_user").
	Prepared(true).
	Where(goqu.Ex{
		"last_name": "Yukon",
	})

var users []User
if err := ds.ScanStructs(&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 User `db:"goqu_user"` // tag as the "goqu_user" table
	Role Role `db:"user_role"` // tag as "user_role" table
}
db := getDb()

ds := db.
	From("goqu_user").
	Join(goqu.T("user_role"), goqu.On(goqu.I("goqu_user.id").Eq(goqu.I("user_role.user_id"))))
var users []UserAndRole
// Scan structs will auto build the
if err := ds.ScanStructs(&users); 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}}
Example (WithJoinManualSelect)

In this example we create a new struct that has the user properties as well as a nested Role struct from the join table 

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"`
	Role      Role   `db:"user_role"` // tag as "user_role" table
}
db := getDb()

ds := db.
	Select(
		"goqu_user.id",
		"goqu_user.first_name",
		"goqu_user.last_name",
		// alias the fully qualified identifier `C` is important here so it doesnt parse it
		goqu.I("user_role.user_id").As(goqu.C("user_role.user_id")),
		goqu.I("user_role.name").As(goqu.C("user_role.name")),
	).
	From("goqu_user").
	Join(goqu.T("user_role"), goqu.On(goqu.I("goqu_user.id").Eq(goqu.I("user_role.user_id"))))
var users []User
if err := ds.ScanStructs(&users); err != nil {
	fmt.Println(err.Error())
	return
}
for _, u := range users {
	fmt.Printf("\n%+v", u)
}

Output:

{ID:1 FirstName:Bob LastName:Yukon Role:{UserID:1 Name:Admin}}
{ID:2 FirstName:Sally LastName:Yukon Role:{UserID:2 Name:Manager}}
{ID:3 FirstName:Vinita LastName:Yukon Role:{UserID:3 Name:Manager}}
{ID:4 FirstName:John LastName:Doe Role:{UserID:4 Name:User}}

func (*SelectDataset) ScanStructsContext 

func (sd *SelectDataset) ScanStructsContext(ctx context.Context, i interface{}) error

Generates the SELECT sql for this dataset and uses Exec#ScanStructsContext to scan the results into a slice of structs.

ScanStructsContext 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) ScanVal 

func (sd *SelectDataset) ScanVal(i interface{}) (bool, error)

Generates the SELECT sql for this dataset and uses Exec#ScanVal to scan the result into a primitive value

i: A pointer to a primitive value

Example 
db := getDb()
findUserIDByName := func(name string) {
	var id int64
	ds := db.From("goqu_user").
		Select("id").
		Where(goqu.C("first_name").Eq(name))

	found, err := ds.ScanVal(&id)
	switch {
	case err != nil:
		fmt.Println(err.Error())
	case !found:
		fmt.Printf("No id found for user %s", name)
	default:
		fmt.Printf("\nFound userId: %+v\n", id)
	}
}

findUserIDByName("Bob")
findUserIDByName("Zeb")

Output:

Found userId: 1
No id found for user Zeb

func (*SelectDataset) ScanValContext 

func (sd *SelectDataset) ScanValContext(ctx context.Context, i interface{}) (bool, error)

Generates the SELECT sql for this dataset and uses Exec#ScanValContext to scan the result into a primitive value

i: A pointer to a primitive value

func (*SelectDataset) ScanVals 

func (sd *SelectDataset) ScanVals(i interface{}) error

Generates the SELECT sql for this dataset and uses Exec#ScanVals to scan the results into a slice of primitive values

i: A pointer to a slice of primitive values

Example 
var ids []int64
if err := getDb().From("goqu_user").Select("id").ScanVals(&ids); err != nil {
	fmt.Println(err.Error())
	return
}
fmt.Printf("UserIds = %+v", ids)

Output:

UserIds = [1 2 3 4]

func (*SelectDataset) ScanValsContext 

func (sd *SelectDataset) ScanValsContext(ctx context.Context, i interface{}) error

Generates the SELECT sql for this dataset and uses Exec#ScanValsContext to scan the results into a slice of primitive values

i: A pointer to a slice of primitive values

func (*SelectDataset) Select 

func (sd *SelectDataset) Select(selects ...interface{}) *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, _, _ := goqu.From("test").Select("a", "b", "c").ToSQL()
fmt.Println(sql)

Output:

SELECT "a", "b", "c" FROM "test"
Example (WithAliasedDataset) 
ds := goqu.From("test")
fromDs := ds.Select("age").Where(goqu.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 := goqu.From("test")
fromDs := ds.Select("age").Where(goqu.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, _, _ := goqu.From("test").Select(goqu.L("a + b").As("sum")).ToSQL()
fmt.Println(sql)

Output:

SELECT a + b AS "sum" FROM "test"
Example (WithSQLFunctionExpression) 
sql, _, _ := goqu.From("test").Select(
	goqu.COUNT("*").As("age_count"),
	goqu.MAX("age").As("max_age"),
	goqu.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 := goqu.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 ...interface{}) *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 := goqu.From("test").Select("a", "b")
sql, _, _ := ds.SelectAppend("c").ToSQL()
fmt.Println(sql)
ds = goqu.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 

func (sd *SelectDataset) SelectDistinct(selects ...interface{}) *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 []interface{}, 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, _ := goqu.From("items").Where(goqu.Ex{"a": 1}).ToSQL()
fmt.Println(sql, args)

Output:

SELECT * FROM "items" WHERE ("a" = 1) []
Example (Prepared) 
sql, args, _ := goqu.From("items").Where(goqu.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, _ := goqu.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, _, _ := goqu.From("test").
	Union(goqu.From("test2")).
	ToSQL()
fmt.Println(sql)

sql, _, _ = goqu.From("test").
	Limit(1).
	Union(goqu.From("test2")).
	ToSQL()
fmt.Println(sql)

sql, _, _ = goqu.From("test").
	Limit(1).
	Union(goqu.From("test2").
		Order(goqu.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, _, _ := goqu.From("test").
	UnionAll(goqu.From("test2")).
	ToSQL()
fmt.Println(sql)
sql, _, _ = goqu.From("test").
	Limit(1).
	UnionAll(goqu.From("test2")).
	ToSQL()
fmt.Println(sql)
sql, _, _ = goqu.From("test").
	Limit(1).
	UnionAll(goqu.From("test2").
		Order(goqu.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, _ := goqu.From("items").Update().Set(
	item{Name: "Test", Address: "111 Test Addr"},
).ToSQL()
fmt.Println(sql, args)

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

sql, args, _ = goqu.From("items").Update().Set(
	map[string]interface{}{"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, _, _ := goqu.From("test").Where(goqu.Ex{
	"a": goqu.Op{"gt": 10},
	"b": goqu.Op{"lt": 10},
	"c": nil,
	"d": []string{"a", "b", "c"},
}).ToSQL()
fmt.Println(sql)
// You can use ExOr to get ORed expressions together
sql, _, _ = goqu.From("test").Where(goqu.ExOr{
	"a": goqu.Op{"gt": 10},
	"b": goqu.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, _, _ = goqu.From("test").Where(
	goqu.Or(
		goqu.Ex{
			"a": goqu.Op{"gt": 10},
			"b": goqu.Op{"lt": 10},
		},
		goqu.Ex{
			"c": nil,
			"d": []string{"a", "b", "c"},
		},
	),
).ToSQL()
fmt.Println(sql)
// By default everything is anded together
sql, _, _ = goqu.From("test").Where(
	goqu.C("a").Gt(10),
	goqu.C("b").Lt(10),
	goqu.C("c").IsNull(),
	goqu.C("d").In("a", "b", "c"),
).ToSQL()
fmt.Println(sql)
// You can use a combination of Ors and Ands
sql, _, _ = goqu.From("test").Where(
	goqu.Or(
		goqu.C("a").Gt(10),
		goqu.And(
			goqu.C("b").Lt(10),
			goqu.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, _ := goqu.From("test").Prepared(true).Where(goqu.Ex{
	"a": goqu.Op{"gt": 10},
	"b": goqu.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, _ = goqu.From("test").Prepared(true).Where(goqu.ExOr{
	"a": goqu.Op{"gt": 10},
	"b": goqu.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, _ = goqu.From("test").Prepared(true).Where(
	goqu.Or(
		goqu.Ex{
			"a": goqu.Op{"gt": 10},
			"b": goqu.Op{"lt": 10},
		},
		goqu.Ex{
			"c": nil,
			"d": []string{"a", "b", "c"},
		},
	),
).ToSQL()
fmt.Println(sql, args)
// By default everything is anded together
sql, args, _ = goqu.From("test").Prepared(true).Where(
	goqu.C("a").Gt(10),
	goqu.C("b").Lt(10),
	goqu.C("c").IsNull(),
	goqu.C("d").In("a", "b", "c"),
).ToSQL()
fmt.Println(sql, args)
// You can use a combination of Ors and Ands
sql, args, _ = goqu.From("test").Prepared(true).Where(
	goqu.Or(
		goqu.C("a").Gt(10),
		goqu.And(
			goqu.C("b").Lt(10),
			goqu.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 := goqu.From("test").
	Select(goqu.ROW_NUMBER().Over(goqu.W().PartitionBy("a").OrderBy(goqu.I("b").Asc())))
query, args, _ := ds.ToSQL()
fmt.Println(query, args)

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

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

ds = goqu.From("test").
	Select(goqu.ROW_NUMBER().Over(goqu.W().Inherit("w").OrderBy("b"))).
	Window(goqu.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, _, _ := goqu.From("one").
	With("one", goqu.From().Select(goqu.L("1"))).
	Select(goqu.Star()).
	ToSQL()
fmt.Println(sql)
sql, _, _ = goqu.From("derived").
	With("intermed", goqu.From("test").Select(goqu.Star()).Where(goqu.C("x").Gte(5))).
	With("derived", goqu.From("intermed").Select(goqu.Star()).Where(goqu.C("x").Lt(10))).
	Select(goqu.Star()).
	ToSQL()
fmt.Println(sql)
sql, _, _ = goqu.From("multi").
	With("multi(x,y)", goqu.From().Select(goqu.L("1"), goqu.L("2"))).
	Select(goqu.C("x"), goqu.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 := goqu.Delete("foo").Where(goqu.Ex{"bar": "baz"}).Returning("id")

ds := goqu.From("bar").
	With("del", deleteDs).
	Select("bar_name").
	Where(goqu.Ex{"bar.user_id": goqu.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 := goqu.Insert("foo").Rows(goqu.Record{"user_id": 10}).Returning("id")

ds := goqu.From("bar").
	With("ins", insertDs).
	Select("bar_name").
	Where(goqu.Ex{"bar.user_id": goqu.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 := goqu.Update("foo").Set(goqu.Record{"bar": "baz"}).Returning("id")

ds := goqu.From("bar").
	With("upd", updateDs).
	Select("bar_name").
	Where(goqu.Ex{"bar.user_id": goqu.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, _, _ := goqu.From("nums").
	WithRecursive("nums(x)",
		goqu.From().Select(goqu.L("1")).
			UnionAll(goqu.From("nums").
				Select(goqu.L("x+1")).Where(goqu.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 ...interface{}) *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) Executor 

func (td *TruncateDataset) Executor() exec.QueryExecutor

Generates the TRUNCATE sql, and returns an Exec struct with the sql set to the TRUNCATE statement 

db.From("test").Truncate().Executor().Exec()

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 ...interface{}) *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 []interface{}, 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) 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 {
	Tx SQLTx
	// contains filtered or unexported fields
}

A wrapper around a sql.Tx and works the same way as Database

func NewTx 

func NewTx(dialect string, tx SQLTx) *TxDatabase

Creates a new TxDatabase

func (*TxDatabase) Commit 

func (td *TxDatabase) Commit() error

COMMIT the transaction

func (*TxDatabase) Delete 

func (td *TxDatabase) Delete(table interface{}) *DeleteDataset

func (*TxDatabase) Dialect 

func (td *TxDatabase) Dialect() string

returns this databases dialect

func (*TxDatabase) Exec 

func (td *TxDatabase) Exec(query string, args ...interface{}) (sql.Result, error)

See Database#Exec

func (*TxDatabase) ExecContext 

func (td *TxDatabase) ExecContext(ctx context.Context, query string, args ...interface{}) (sql.Result, error)

See Database#ExecContext

func (*TxDatabase) From 

func (td *TxDatabase) From(cols ...interface{}) *SelectDataset

Creates a new Dataset for querying a Database.

func (*TxDatabase) Insert 

func (td *TxDatabase) Insert(table interface{}) *InsertDataset

func (*TxDatabase) Logger 

func (td *TxDatabase) Logger(logger Logger)

Sets the logger

func (*TxDatabase) Prepare 

func (td *TxDatabase) Prepare(query string) (*sql.Stmt, error)

See Database#Prepare

func (*TxDatabase) PrepareContext 

func (td *TxDatabase) PrepareContext(ctx context.Context, query string) (*sql.Stmt, error)

See Database#PrepareContext

func (*TxDatabase) Query 

func (td *TxDatabase) Query(query string, args ...interface{}) (*sql.Rows, error)

See Database#Query

func (*TxDatabase) QueryContext 

func (td *TxDatabase) QueryContext(ctx context.Context, query string, args ...interface{}) (*sql.Rows, error)

See Database#QueryContext

func (*TxDatabase) QueryRow 

func (td *TxDatabase) QueryRow(query string, args ...interface{}) *sql.Row

See Database#QueryRow

func (*TxDatabase) QueryRowContext 

func (td *TxDatabase) QueryRowContext(ctx context.Context, query string, args ...interface{}) *sql.Row

See Database#QueryRowContext

func (*TxDatabase) Rollback 

func (td *TxDatabase) Rollback() error

ROLLBACK the transaction

func (*TxDatabase) ScanStruct 

func (td *TxDatabase) ScanStruct(i interface{}, query string, args ...interface{}) (bool, error)

See Database#ScanStruct

func (*TxDatabase) ScanStructContext 

func (td *TxDatabase) ScanStructContext(ctx context.Context, i interface{}, query string, args ...interface{}) (bool, error)

See Database#ScanStructContext

func (*TxDatabase) ScanStructs 

func (td *TxDatabase) ScanStructs(i interface{}, query string, args ...interface{}) error

See Database#ScanStructs

func (*TxDatabase) ScanStructsContext 

func (td *TxDatabase) ScanStructsContext(ctx context.Context, i interface{}, query string, args ...interface{}) error

See Database#ScanStructsContext

func (*TxDatabase) ScanVal 

func (td *TxDatabase) ScanVal(i interface{}, query string, args ...interface{}) (bool, error)

See Database#ScanVal

func (*TxDatabase) ScanValContext 

func (td *TxDatabase) ScanValContext(ctx context.Context, i interface{}, query string, args ...interface{}) (bool, error)

See Database#ScanValContext

func (*TxDatabase) ScanVals 

func (td *TxDatabase) ScanVals(i interface{}, query string, args ...interface{}) error

See Database#ScanVals

func (*TxDatabase) ScanValsContext 

func (td *TxDatabase) ScanValsContext(ctx context.Context, i interface{}, query string, args ...interface{}) error

See Database#ScanValsContext

func (*TxDatabase) Select 

func (td *TxDatabase) Select(cols ...interface{}) *SelectDataset

func (*TxDatabase) Trace 

func (td *TxDatabase) Trace(op, sqlString string, args ...interface{})

func (*TxDatabase) Truncate 

func (td *TxDatabase) Truncate(table ...interface{}) *TruncateDataset

func (*TxDatabase) Update 

func (td *TxDatabase) Update(table interface{}) *UpdateDataset

func (*TxDatabase) Wrap 

func (td *TxDatabase) Wrap(fn func() error) error

A helper method that will automatically COMMIT or ROLLBACK once the supplied function is done executing 

tx, err := db.Begin()
if err != nil{
     panic(err.Error()) // you could gracefully handle the error also
}
if err := tx.Wrap(func() error{
    if _, err := tx.From("test").Insert(Record{"a":1, "b": "b"}).Exec(){
        // this error will be the return error from the Wrap call
        return err
    }
    return nil
}); err != nil{
     panic(err.Error()) // you could gracefully handle the error also
}

type UpdateDataset 

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

func Update 

func Update(table interface{}) *UpdateDataset
Example (WithGoquRecord) 
sql, args, _ := goqu.Update("items").Set(
	goqu.Record{"name": "Test", "address": "111 Test Addr"},
).ToSQL()
fmt.Println(sql, args)

Output:

UPDATE "items" SET "address"='111 Test Addr',"name"='Test' []
Example (WithMap) 
sql, args, _ := goqu.Update("items").Set(
	map[string]interface{}{"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" goqu:"skipupdate"`
}
sql, args, _ := goqu.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, _ := goqu.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' []

func (*UpdateDataset) AppendSQL added in v9.3.0 

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 := goqu.Dialect("mysql").
	Update("test").
	Set(goqu.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 := goqu.Dialect("mysql").
	Update("test").
	Set(goqu.Record{"foo": "bar"}).
	Order(goqu.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 := goqu.
	Update("test").
	Set(goqu.Record{"foo": "bar"}).
	Where(
		goqu.Or(
			goqu.C("a").Gt(10),
			goqu.And(
				goqu.C("b").Lt(10),
				goqu.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) Executor 

func (ud *UpdateDataset) Executor() exec.QueryExecutor

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()
update := db.Update("goqu_user").
	Where(goqu.C("first_name").Eq("Bob")).
	Set(goqu.Record{"first_name": "Bobby"}).
	Executor()

if r, err := update.Exec(); err != nil {
	fmt.Println(err.Error())
} else {
	c, _ := r.RowsAffected()
	fmt.Printf("Updated %d users", c)
}

Output:

Updated 1 users
Example (Returning) 
db := getDb()
var ids []int64
update := db.Update("goqu_user").
	Set(goqu.Record{"last_name": "ucon"}).
	Where(goqu.Ex{"last_name": "Yukon"}).
	Returning("id").
	Executor()
if err := update.ScanVals(&ids); err != nil {
	fmt.Println(err.Error())
} else {
	fmt.Printf("Updated users with ids %+v", ids)
}

Output:

Updated users with ids [1 2 3]

func (*UpdateDataset) Expression 

func (ud *UpdateDataset) Expression() exp.Expression

func (*UpdateDataset) From 

func (ud *UpdateDataset) From(tables ...interface{}) *UpdateDataset

Allows specifying other tables to reference in your update (If your dialect supports it). See examples.

Example 
ds := goqu.Update("table_one").
	Set(goqu.Record{"foo": goqu.I("table_two.bar")}).
	From("table_two").
	Where(goqu.Ex{"table_one.id": goqu.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 := goqu.Dialect("mysql")

ds := dialect.Update("table_one").
	Set(goqu.Record{"foo": goqu.I("table_two.bar")}).
	From("table_two").
	Where(goqu.Ex{"table_one.id": goqu.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 := goqu.Dialect("postgres")

ds := dialect.Update("table_one").
	Set(goqu.Record{"foo": goqu.I("table_two.bar")}).
	From("table_two").
	Where(goqu.Ex{"table_one.id": goqu.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 added in v9.3.0 

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 := goqu.Dialect("mysql").
	Update("test").
	Set(goqu.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 := goqu.Dialect("mysql").
	Update("test").
	Set(goqu.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 := goqu.Dialect("mysql").
	Update("test").
	Set(goqu.Record{"foo": "bar"}).
	Order(goqu.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 := goqu.Dialect("mysql").
	Update("test").
	Set(goqu.Record{"foo": "bar"}).
	Order(goqu.C("a").Asc())
sql, _, _ := ds.OrderAppend(goqu.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 := goqu.Dialect("mysql").
	Update("test").
	Set(goqu.Record{"foo": "bar"}).
	Order(goqu.C("a").Asc())

sql, _, _ := ds.OrderPrepend(goqu.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, _ := goqu.Update("items").Prepared(true).Set(
	goqu.Record{"name": "Test", "address": "111 Test Addr"},
).ToSQL()
fmt.Println(sql, args)

Output:

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

func (*UpdateDataset) Returning 

func (ud *UpdateDataset) Returning(returning ...interface{}) *UpdateDataset

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

Example 
sql, _, _ := goqu.Update("test").
	Set(goqu.Record{"foo": "bar"}).
	Returning("id").
	ToSQL()
fmt.Println(sql)
sql, _, _ = goqu.Update("test").
	Set(goqu.Record{"foo": "bar"}).
	Returning(goqu.T("test").All()).
	ToSQL()
fmt.Println(sql)
sql, _, _ = goqu.Update("test").
	Set(goqu.Record{"foo": "bar"}).
	Returning("a", "b").
	ToSQL()
fmt.Println(sql)

Output:

UPDATE "test" SET "foo"='bar' RETURNING "id"
UPDATE "test" SET "foo"='bar' RETURNING "test".*
UPDATE "test" SET "foo"='bar' RETURNING "a", "b"

func (*UpdateDataset) ReturnsColumns added in v9.3.0 

func (ud *UpdateDataset) ReturnsColumns() bool

func (*UpdateDataset) Set 

func (ud *UpdateDataset) Set(values interface{}) *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, _ := goqu.Update("items").Set(
	item{Name: "Test", Address: "111 Test Addr"},
).ToSQL()
fmt.Println(sql, args)

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

sql, args, _ = goqu.Update("items").Set(
	map[string]interface{}{"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 (GoquRecord) 
sql, args, _ := goqu.Update("items").Set(
	goqu.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, _ := goqu.Update("items").Set(
	map[string]interface{}{"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, _ := goqu.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" goqu:"defaultifempty"`
}
sql, args, _ := goqu.Update("items").Set(
	item{Address: "111 Test Addr"},
).ToSQL()
fmt.Println(sql, args)

sql, args, _ = goqu.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 := goqu.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 := goqu.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 := goqu.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, _ := goqu.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" goqu:"skipupdate"`
}
sql, args, _ := goqu.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 interface{}) *UpdateDataset

Sets the table to update.

Example 
ds := goqu.Update("test")
sql, _, _ := ds.Table("test2").Set(goqu.Record{"foo": "bar"}).ToSQL()
fmt.Println(sql)

Output:

UPDATE "test2" SET "foo"='bar'
Example (Aliased) 
ds := goqu.Update("test")
sql, _, _ := ds.Table(goqu.T("test").As("t")).Set(goqu.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 []interface{}, 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 (Prepared) 
type item struct {
	Address string `db:"address"`
	Name    string `db:"name"`
}

sql, args, _ := goqu.From("items").Prepared(true).Update().Set(
	item{Name: "Test", Address: "111 Test Addr"},
).ToSQL()
fmt.Println(sql, args)

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

sql, args, _ = goqu.From("items").Prepared(true).Update().Set(
	map[string]interface{}{"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, _, _ := goqu.Update("test").
	Set(goqu.Record{"foo": "bar"}).
	Where(goqu.Ex{
		"a": goqu.Op{"gt": 10},
		"b": goqu.Op{"lt": 10},
		"c": nil,
		"d": []string{"a", "b", "c"},
	}).ToSQL()
fmt.Println(sql)
// You can use ExOr to get ORed expressions together
sql, _, _ = goqu.Update("test").
	Set(goqu.Record{"foo": "bar"}).
	Where(goqu.ExOr{
		"a": goqu.Op{"gt": 10},
		"b": goqu.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, _, _ = goqu.Update("test").
	Set(goqu.Record{"foo": "bar"}).
	Where(
		goqu.Or(
			goqu.Ex{
				"a": goqu.Op{"gt": 10},
				"b": goqu.Op{"lt": 10},
			},
			goqu.Ex{
				"c": nil,
				"d": []string{"a", "b", "c"},
			},
		),
	).ToSQL()
fmt.Println(sql)
// By default everything is anded together
sql, _, _ = goqu.Update("test").
	Set(goqu.Record{"foo": "bar"}).
	Where(
		goqu.C("a").Gt(10),
		goqu.C("b").Lt(10),
		goqu.C("c").IsNull(),
		goqu.C("d").In("a", "b", "c"),
	).ToSQL()
fmt.Println(sql)
// You can use a combination of Ors and Ands
sql, _, _ = goqu.Update("test").
	Set(goqu.Record{"foo": "bar"}).
	Where(
		goqu.Or(
			goqu.C("a").Gt(10),
			goqu.And(
				goqu.C("b").Lt(10),
				goqu.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, _ := goqu.Update("test").
	Prepared(true).
	Set(goqu.Record{"foo": "bar"}).
	Where(goqu.Ex{
		"a": goqu.Op{"gt": 10},
		"b": goqu.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, _ = goqu.Update("test").Prepared(true).
	Set(goqu.Record{"foo": "bar"}).
	Where(goqu.ExOr{
		"a": goqu.Op{"gt": 10},
		"b": goqu.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, _ = goqu.Update("test").Prepared(true).
	Set(goqu.Record{"foo": "bar"}).
	Where(
		goqu.Or(
			goqu.Ex{
				"a": goqu.Op{"gt": 10},
				"b": goqu.Op{"lt": 10},
			},
			goqu.Ex{
				"c": nil,
				"d": []string{"a", "b", "c"},
			},
		),
	).ToSQL()
fmt.Println(sql, args)
// By default everything is anded together
sql, args, _ = goqu.Update("test").Prepared(true).
	Set(goqu.Record{"foo": "bar"}).
	Where(
		goqu.C("a").Gt(10),
		goqu.C("b").Lt(10),
		goqu.C("c").IsNull(),
		goqu.C("d").In("a", "b", "c"),
	).ToSQL()
fmt.Println(sql, args)
// You can use a combination of Ors and Ands
sql, args, _ = goqu.Update("test").Prepared(true).
	Set(goqu.Record{"foo": "bar"}).
	Where(
		goqu.Or(
			goqu.C("a").Gt(10),
			goqu.And(
				goqu.C("b").Lt(10),
				goqu.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, _, _ := goqu.Update("test").
	With("some_vals(val)", goqu.From().Select(goqu.L("123"))).
	Where(goqu.C("val").Eq(goqu.From("some_vals").Select("val"))).
	Set(goqu.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, _, _ := goqu.Update("nums").
	WithRecursive("nums(x)", goqu.From().Select(goqu.L("1").As("num")).
		UnionAll(goqu.From("nums").
			Select(goqu.L("x+1").As("num")).Where(goqu.C("x").Lt(5)))).
	Set(goqu.Record{"foo": goqu.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 := goqu.Insert("user").
	Cols("first_name", "last_name", "is_verified").
	Vals(
		goqu.Vals{"Greg", "Farley", true},
		goqu.Vals{"Jimmy", "Stewart", true},
		goqu.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
sqlite3
sqlserver
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.