goqu: github.com/doug-martin/goqu Index | Examples | Files | Directories

package goqu

import "github.com/doug-martin/goqu"

goqu an idiomatch SQL builder, and query package.

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

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

Index

Examples

Package Files

database.go delete_dataset.go expressions.go goqu.go insert_dataset.go select_dataset.go sql_dialect.go truncate_dataset.go update_dataset.go

Constants

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

Variables

var (
    DefaultDialectOptions = sqlgen.DefaultDialectOptions
)

func AVG Uses

func AVG(col interface{}) exp.SQLFunctionExpression

Creates a new AVG sql function

AVG("a") -> AVG("a")
AVG(I("a")) -> AVG("a")

Code:

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" []

Code:

sql, _, _ := goqu.From("test").Select(goqu.AVG("a").As("a")).ToSQL()
fmt.Println(sql)

Output:

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

Code:

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 Uses

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

Code:

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]

You can use ExOr inside of And expression lists.

Code:

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

You can use And with Or to create more complex queries

Code:

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 Uses

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

Code:

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

Code:

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"

Code:

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]

Code:

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

Code:

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

Code:

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

Code:

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

Code:

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

Code:

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 Uses

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)

Code:

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

Code:

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 Uses

func COUNT(col interface{}) exp.SQLFunctionExpression

Creates a new COUNT sql function

COUNT("a") -> COUNT("a")
COUNT("*") -> COUNT("*")
COUNT(I("a")) -> COUNT("a")

Code:

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" []

Code:

sql, _, _ := goqu.From("test").Select(goqu.COUNT("*").As("count")).ToSQL()
fmt.Println(sql)

Output:

SELECT COUNT(*) AS "count" FROM "test"

Code:

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 Uses

func CUME_DIST() exp.SQLFunctionExpression

nolint: golint

func Cast Uses

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

Creates a new Casted expression

Cast(I("a"), "NUMERIC") -> CAST("a" AS NUMERIC)

Code:

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 Uses

func DENSE_RANK() exp.SQLFunctionExpression

nolint: golint

func DISTINCT Uses

func DISTINCT(col interface{}) exp.SQLFunctionExpression

Creates a new DISTINCT sql function

DISTINCT("a") -> DISTINCT("a")
DISTINCT(I("a")) -> DISTINCT("a")

Code:

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" []

Code:

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 Uses

func Default() exp.LiteralExpression

Returns a literal for DEFAULT sql keyword

Code:

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 Uses

func DeregisterDialect(name string)

func DoNothing Uses

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

Code:

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 Uses

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

Code:

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]

Code:

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 Uses

func FIRST(col interface{}) exp.SQLFunctionExpression

Creates a new FIRST sql function

FIRST("a") -> FIRST("a")
FIRST(I("a")) -> FIRST("a")

Code:

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" []

Code:

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 Uses

func FIRST_VALUE(val interface{}) exp.SQLFunctionExpression

nolint: golint

func Func Uses

func Func(name string, args ...interface{}) exp.SQLFunctionExpression

Creates a new SQLFunctionExpression with the given name and arguments

This example shows how to create custom SQL Functions

Code:

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 Uses

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".*

Code:

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 Uses

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

Code:

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]

Code:

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"

Code:

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]

Code:

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

Code:

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

Code:

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

Code:

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

Code:

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 Uses

func LAST(col interface{}) exp.SQLFunctionExpression

Creates a new LAST sql function

LAST("a") -> LAST("a")
LAST(I("a")) -> LAST("a")

Code:

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" []

Code:

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 Uses

func LAST_VALUE(val interface{}) exp.SQLFunctionExpression

nolint: golint

func Literal Uses

func Literal(sql string, args ...interface{}) exp.LiteralExpression

Alias for goqu.L

func MAX Uses

func MAX(col interface{}) exp.SQLFunctionExpression

Creates a new MAX sql function

MAX("a") -> MAX("a")
MAX(I("a")) -> MAX("a")

Code:

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" []

Code:

sql, _, _ := goqu.From("test").Select(goqu.MAX("a").As("a")).ToSQL()
fmt.Println(sql)

Output:

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

Code:

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 Uses

func MIN(col interface{}) exp.SQLFunctionExpression

Creates a new MIN sql function

MIN("a") -> MIN("a")
MIN(I("a")) -> MIN("a")

Code:

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" []

Code:

sql, _, _ := goqu.From("test").Select(goqu.MIN("a").As("a")).ToSQL()
fmt.Println(sql)

Output:

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

Code:

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 Uses

func NTH_VALUE(val interface{}, nth int) exp.SQLFunctionExpression

nolint: golint

func NTILE Uses

func NTILE(n int) exp.SQLFunctionExpression

func On Uses

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

Code:

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") []

Code:

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 Uses

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

Code:

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]

Code:

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]

Code:

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 Uses

func PERCENT_RANK() exp.SQLFunctionExpression

nolint: golint

func RANK Uses

func RANK() exp.SQLFunctionExpression

func ROW_NUMBER Uses

func ROW_NUMBER() exp.SQLFunctionExpression

nolint: golint

func Range Uses

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

Code:

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") []

Code:

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]

Code:

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 Uses

func RegisterDialect(name string, do *SQLDialectOptions)

Code:

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 Uses

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

Code:

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 Uses

func SUM(col interface{}) exp.SQLFunctionExpression

Creates a new SUM sql function

SUM("a") -> SUM("a")
SUM(I("a")) -> SUM("a")

Code:

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" []

Code:

sql, _, _ := goqu.From("test").Select(goqu.SUM("a").As("a")).ToSQL()
fmt.Println(sql)

Output:

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

Code:

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 Uses

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 Uses

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.

Code:

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 Uses

func Star() exp.LiteralExpression

Creates a literal *

Code:

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 Uses

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

Code:

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 Uses

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

Code:

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") []

Code:

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 Uses

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.

Code:

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

Code:

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 Uses

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

Code:

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") []

type Database Uses

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 Uses

func New(dialect string, db SQLDatabase) *Database

func (*Database) Begin Uses

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

Starts a new Transaction.

Code:

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 Uses

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

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

Code:

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 Uses

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

func (*Database) Dialect Uses

func (d *Database) Dialect() string

returns this databases dialect

Code:

db := getDb()

fmt.Println(db.Dialect())

Output:

postgres

func (*Database) Exec Uses

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

Code:

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 Uses

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

Code:

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 Uses

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

Code:

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 Uses

func (d *Database) Insert(table interface{}) *InsertDataset

func (*Database) Logger Uses

func (d *Database) Logger(logger Logger)

Sets the logger for to use when logging queries

func (*Database) Prepare Uses

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 Uses

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 Uses

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 Uses

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 Uses

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 Uses

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 Uses

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 Uses

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 Uses

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 Uses

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 Uses

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 Uses

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 Uses

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 Uses

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 Uses

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

func (*Database) Trace Uses

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

Logs a given operation with the specified sql and arguments

func (*Database) Truncate Uses

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

func (*Database) Update Uses

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

func (*Database) WithTx Uses

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

WithTx starts a new transaction and executes it in Wrap method

Code:

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 Uses

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

func Delete Uses

func Delete(table interface{}) *DeleteDataset

Code:

ds := goqu.Delete("items")

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

Output:

DELETE FROM "items" []

func (*DeleteDataset) AppendSQL Uses

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 Uses

func (dd *DeleteDataset) ClearLimit() *DeleteDataset

Removes the LIMIT clause.

Code:

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

func (dd *DeleteDataset) ClearOrder() *DeleteDataset

Removes the ORDER BY clause. See examples.

Code:

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

Output:

DELETE FROM "test"

func (*DeleteDataset) ClearWhere Uses

func (dd *DeleteDataset) ClearWhere() *DeleteDataset

Removes the WHERE clause. See examples.

Code:

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 Uses

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

Clones the dataset

func (*DeleteDataset) Dialect Uses

func (dd *DeleteDataset) Dialect() SQLDialect

Returns the current SQLDialect on the dataset

func (*DeleteDataset) Error Uses

func (dd *DeleteDataset) Error() error

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

func (*DeleteDataset) Executor Uses

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

Creates an QueryExecutor to execute the query.

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

See Dataset#ToUpdateSQL for arguments

Code:

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

Code:

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 Uses

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

func (*DeleteDataset) From Uses

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 Uses

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

func (*DeleteDataset) GetClauses Uses

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

Returns the current clauses on the dataset.

func (*DeleteDataset) IsPrepared Uses

func (dd *DeleteDataset) IsPrepared() bool

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

func (*DeleteDataset) Limit Uses

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

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

Code:

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

Output:

DELETE FROM `test` LIMIT 10

func (*DeleteDataset) LimitAll Uses

func (dd *DeleteDataset) LimitAll() *DeleteDataset

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

Code:

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

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

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

Code:

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

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.

Code:

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

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.

Code:

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

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

Set the parameter interpolation behavior. See examples

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

Code:

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 Uses

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

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

Code:

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 Uses

func (dd *DeleteDataset) ReturnsColumns() bool

func (*DeleteDataset) SetDialect Uses

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

Set the dialect for this dataset.

func (*DeleteDataset) SetError Uses

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 Uses

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

Code:

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 Uses

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

Adds a WHERE clause. See examples.

Code:

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

Code:

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

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.

Code:

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 Uses

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

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

func (*DeleteDataset) WithRecursive Uses

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.

Code:

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 Uses

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

func Dialect Uses

func Dialect(dialect string) DialectWrapper

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

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

Code:

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

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

Code:

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

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

Code:

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

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

Code:

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

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

Code:

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

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

Code:

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

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

func (DialectWrapper) Delete Uses

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

Create a new dataset for creating DELETE sql statements

func (DialectWrapper) From Uses

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

Create a new dataset for creating SELECT sql statements

func (DialectWrapper) Insert Uses

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

Create a new dataset for creating INSERT sql statements

func (DialectWrapper) Select Uses

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

Create a new dataset for creating SELECT sql statements

func (DialectWrapper) Truncate Uses

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

Create a new dataset for creating TRUNCATE sql statements

func (DialectWrapper) Update Uses

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

Create a new dataset for creating UPDATE sql statements

type Ex Uses

type Ex = exp.Ex

Code:

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]

Code:

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

Code:

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 Uses

type ExOr = exp.ExOr

Code:

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

Code:

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 Uses

type Expression = exp.Expression

type InsertDataset Uses

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

func Insert Uses

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

Code:

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') []

Code:

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" []

Code:

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" []

Code:

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') []

Code:

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') []

Code:

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]

Code:

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 Uses

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 Uses

func (id *InsertDataset) ClearCols() *InsertDataset

Clears the Columns to insert into

Code:

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 Uses

func (id *InsertDataset) ClearOnConflict() *InsertDataset

Clears the on conflict clause. See example

Code:

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 Uses

func (id *InsertDataset) ClearRows() *InsertDataset

Clears the rows for this insert dataset. See examples.

Code:

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 Uses

func (id *InsertDataset) ClearVals() *InsertDataset

Clears the values. See examples.

Code:

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 Uses

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

Clones the dataset

func (*InsertDataset) Cols Uses

func (id *InsertDataset) Cols(cols ...interface{}) *InsertDataset

Sets the Columns to insert into

Code:

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

Code:

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 Uses

func (id *InsertDataset) ColsAppend(cols ...interface{}) *InsertDataset

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

Code:

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 Uses

func (id *InsertDataset) Dialect() SQLDialect

Returns the current adapter on the dataset

func (*InsertDataset) Error Uses

func (id *InsertDataset) Error() error

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

func (*InsertDataset) Executor Uses

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

Code:

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]

Code:

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]

Code:

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 Uses

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

func (*InsertDataset) FromQuery Uses

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

Adds a subquery to the insert. See examples.

Code:

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 Uses

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

func (*InsertDataset) GetClauses Uses

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

Returns the current clauses on the dataset.

func (*InsertDataset) Into Uses

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

Code:

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

Code:

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 Uses

func (id *InsertDataset) IsPrepared() bool

func (*InsertDataset) OnConflict Uses

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.

Code:

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

Code:

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() []

Code:

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 Uses

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

Set the parameter interpolation behavior. See examples

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

Code:

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 Uses

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

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

Code:

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 Uses

func (id *InsertDataset) ReturnsColumns() bool

func (*InsertDataset) Rows Uses

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

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

Code:

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') []

Code:

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') []

Code:

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') []

Code:

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') []

Code:

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') []

Code:

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 Uses

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

Returns the current adapter on the dataset

func (*InsertDataset) SetError Uses

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 Uses

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

Code:

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 Uses

func (id *InsertDataset) Vals(vals ...[]interface{}) *InsertDataset

Manually set values to insert See examples.

Code:

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 Uses

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.

Code:

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 Uses

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

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

func (*InsertDataset) WithRecursive Uses

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.

Code:

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 Uses

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

type Op Uses

type Op = exp.Op

Code:

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]

Code:

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]

Code:

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

Code:

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

Code:

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

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

Code:

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 Uses

type Record = exp.Record

Code:

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]

Code:

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 Uses

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 Uses

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 Uses

func GetDialect(name string) SQLDialect

type SQLDialectOptions Uses

type SQLDialectOptions = sqlgen.SQLDialectOptions

type SQLTx Uses

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 Uses

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

Dataset for creating and/or executing SELECT SQL statements.

Code:

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

func From Uses

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

Code:

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

Output:

SELECT * FROM "test" []

func Select Uses

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

Code:

sql, _, _ := goqu.Select(goqu.L("NOW()")).ToSQL()
fmt.Println(sql)

Output:

SELECT NOW()

func (*SelectDataset) AppendSQL Uses

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 Uses

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.

Code:

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 Uses

func (sd *SelectDataset) ClearLimit() *SelectDataset

Removes the LIMIT clause.

Code:

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

Output:

SELECT * FROM "test"

func (*SelectDataset) ClearOffset Uses

func (sd *SelectDataset) ClearOffset() *SelectDataset

Removes the OFFSET clause from the Dataset

Code:

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

Output:

SELECT * FROM "test"

func (*SelectDataset) ClearOrder Uses

func (sd *SelectDataset) ClearOrder() *SelectDataset

Removes the ORDER BY clause. See examples.

Code:

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

Output:

SELECT * FROM "test"

func (*SelectDataset) ClearSelect Uses

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.

Code:

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 Uses

func (sd *SelectDataset) ClearWhere() *SelectDataset

Removes the WHERE clause. See examples.

Code:

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 Uses

func (sd *SelectDataset) ClearWindow() *SelectDataset

Sets the WINDOW clauses

func (*SelectDataset) Clone Uses

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

Clones the dataset

func (*SelectDataset) CompoundFromSelf Uses

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 Uses

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.

Code:

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 Uses

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 Uses

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

Adds a CROSS JOIN clause. See examples.

Code:

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 Uses

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`

Code:

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 Uses

func (sd *SelectDataset) Dialect() SQLDialect

Returns the current adapter on the dataset

func (*SelectDataset) Distinct Uses

func (sd *SelectDataset) Distinct(on ...interface{}) *SelectDataset

Code:

sql, _, _ := goqu.From("test").Select("a", "b").Distinct().ToSQL()
fmt.Println(sql)

Output:

SELECT DISTINCT "a", "b" FROM "test"

Code:

sql, _, _ := goqu.From("test").Distinct("a").ToSQL()
fmt.Println(sql)

Output:

SELECT DISTINCT ON ("a") * FROM "test"

Code:

sql, _, _ := goqu.From("test").Distinct(goqu.COALESCE(goqu.C("a"), "empty")).ToSQL()
fmt.Println(sql)

Output:

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

Code:

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 Uses

func (sd *SelectDataset) Error() error

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

func (*SelectDataset) Executor Uses

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

Code:

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}

Code:

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 Uses

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

func (*SelectDataset) ForKeyShare Uses

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

Adds a FOR KEY SHARE clause. See examples.

func (*SelectDataset) ForNoKeyUpdate Uses

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

Adds a FOR NO KEY UPDATE clause. See examples.

func (*SelectDataset) ForShare Uses

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

Adds a FOR SHARE clause. See examples.

func (*SelectDataset) ForUpdate Uses

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

Adds a FOR UPDATE clause. See examples.

func (*SelectDataset) From Uses

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

Code:

ds := goqu.From("test")
sql, _, _ := ds.From("test2").ToSQL()
fmt.Println(sql)

Output:

SELECT * FROM "test2"

Code:

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"

Code:

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 Uses

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.

Code:

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 Uses

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

Adds a FULL JOIN clause. See examples.

Code:

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 Uses

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

Adds a FULL OUTER JOIN clause. See examples.

Code:

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 Uses

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

Returns the alias value as an identiier expression

func (*SelectDataset) GetClauses Uses

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

Returns the current clauses on the dataset.

func (*SelectDataset) GroupBy Uses

func (sd *SelectDataset) GroupBy(groupBy ...interface{}) *SelectDataset

Adds a GROUP BY clause. See examples.

Code:

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 Uses

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

Adds a HAVING clause. See examples.

Code:

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 Uses

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

Adds an INNER JOIN clause. See examples.

Code:

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 Uses

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.

Code:

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 Uses

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.

Code:

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 Uses

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.

Code:

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 Uses

func (sd *SelectDataset) IsPrepared() bool

func (*SelectDataset) Join Uses

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

Alias to InnerJoin. See examples.

Code:

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 Uses

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

Adds a LEFT JOIN clause. See examples.

Code:

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 Uses

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

Adds a LEFT OUTER JOIN clause. See examples.

Code:

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 Uses

func (sd *SelectDataset) Limit(limit uint) *SelectDataset

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

Code:

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

Output:

SELECT * FROM "test" LIMIT 10

func (*SelectDataset) LimitAll Uses

func (sd *SelectDataset) LimitAll() *SelectDataset

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

Code:

ds := goqu.From("test").LimitAll()
sql, _, _ := ds.ToSQL()
fmt.Println(sql)

Output:

SELECT * FROM "test" LIMIT ALL

func (*SelectDataset) NaturalFullJoin Uses

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

Adds a NATURAL FULL JOIN clause. See examples.

Code:

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 Uses

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

Adds a NATURAL JOIN clause. See examples.

Code:

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 Uses

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

Adds a NATURAL LEFT JOIN clause. See examples.

Code:

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 Uses

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

Adds a NATURAL RIGHT JOIN clause. See examples.

Code:

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 Uses

func (sd *SelectDataset) Offset(offset uint) *SelectDataset

Adds an OFFSET clause. If the OFFSET is currently set it replaces it. See examples.

Code:

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

Output:

SELECT * FROM "test" OFFSET 2

func (*SelectDataset) Order Uses

func (sd *SelectDataset) Order(order ...exp.OrderedExpression) *SelectDataset

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

Code:

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 Uses

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.

Code:

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 Uses

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.

Code:

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 Uses

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

Code:

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 Uses

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 Uses

func (sd *SelectDataset) Prepared(prepared bool) *SelectDataset

Set the parameter interpolation behavior. See examples

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

Code:

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

func (*SelectDataset) ReturnsColumns Uses

func (sd *SelectDataset) ReturnsColumns() bool

func (*SelectDataset) RightJoin Uses

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

Adds a RIGHT JOIN clause. See examples.

Code:

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 Uses

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

Adds a RIGHT OUTER JOIN clause. See examples.

Code:

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 Uses

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

Code:

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

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

Code:

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

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

Code:

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 Uses

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 Uses

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

Code:

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

Code:

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

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

Code:

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

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

Code:

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 Uses

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 Uses

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

Code:

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 Uses

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 Uses

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

Code:

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 Uses

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 Uses

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

Code:

sql, _, _ := goqu.From("test").Select("a", "b", "c").ToSQL()
fmt.Println(sql)

Output:

SELECT "a", "b", "c" FROM "test"

Code:

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"

Code:

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

Code:

sql, _, _ := goqu.From("test").Select(goqu.L("a + b").As("sum")).ToSQL()
fmt.Println(sql)

Output:

SELECT a + b AS "sum" FROM "test"

Code:

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"

Code:

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 Uses

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

Code:

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 Uses

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 Uses

func (sd *SelectDataset) SetDialect(dialect SQLDialect) *SelectDataset

Returns the current adapter on the dataset

func (*SelectDataset) SetError Uses

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 Uses

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

Code:

sql, args, _ := goqu.From("items").Where(goqu.Ex{"a": 1}).ToSQL()
fmt.Println(sql, args)

Output:

SELECT * FROM "items" WHERE ("a" = 1) []

Code:

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 Uses

func (sd *SelectDataset) Truncate() *TruncateDataset

Creates a new TruncateDataset using the FROM of this dataset.

Code:

sql, args, _ := goqu.From("items").Truncate().ToSQL()
fmt.Println(sql, args)

Output:

TRUNCATE "items" []

func (*SelectDataset) Union Uses

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.

Code:

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 Uses

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.

Code:

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 Uses

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`

Code:

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 Uses

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

Adds a WHERE clause. See examples.

Code:

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

Code:

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

func (sd *SelectDataset) Window(ws ...exp.WindowExpression) *SelectDataset

Sets the WINDOW clauses

Code:

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") []

func (*SelectDataset) WindowAppend Uses

func (sd *SelectDataset) WindowAppend(ws ...exp.WindowExpression) *SelectDataset

Sets the WINDOW clauses

func (*SelectDataset) With Uses

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.

Code:

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"

Code:

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]

Code:

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]

Code:

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 Uses

func (sd *SelectDataset) WithDialect(dl string) *SelectDataset

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

func (*SelectDataset) WithRecursive Uses

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.

Code:

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 Uses

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

func Truncate Uses

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

func (*TruncateDataset) Cascade Uses

func (td *TruncateDataset) Cascade() *TruncateDataset

Adds a CASCADE clause

func (*TruncateDataset) Clone Uses

func (td *TruncateDataset) Clone() exp.Expression

Clones the dataset

func (*TruncateDataset) Dialect Uses

func (td *TruncateDataset) Dialect() SQLDialect

Returns the current adapter on the dataset

func (*TruncateDataset) Error Uses

func (td *TruncateDataset) Error() error

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

func (*TruncateDataset) Executor Uses

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 Uses

func (td *TruncateDataset) Expression() exp.Expression

func (*TruncateDataset) GetClauses Uses

func (td *TruncateDataset) GetClauses() exp.TruncateClauses

Returns the current clauses on the dataset.

func (*TruncateDataset) Identity Uses

func (td *TruncateDataset) Identity(identity string) *TruncateDataset

Add a IDENTITY clause (e.g. RESTART)

func (*TruncateDataset) IsPrepared Uses

func (td *TruncateDataset) IsPrepared() bool

func (*TruncateDataset) NoCascade Uses

func (td *TruncateDataset) NoCascade() *TruncateDataset

Clears the CASCADE clause

func (*TruncateDataset) NoRestrict Uses

func (td *TruncateDataset) NoRestrict() *TruncateDataset

Clears the RESTRICT clause

func (*TruncateDataset) Prepared Uses

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 Uses

func (td *TruncateDataset) Restrict() *TruncateDataset

Adds a RESTRICT clause

func (*TruncateDataset) SetDialect Uses

func (td *TruncateDataset) SetDialect(dialect SQLDialect) *TruncateDataset

Returns the current adapter on the dataset

func (*TruncateDataset) SetError Uses

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 Uses

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 Uses

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 Uses

func (td *TruncateDataset) WithDialect(dl string) *TruncateDataset

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

type TruncateOptions Uses

type TruncateOptions = exp.TruncateOptions

Options to use when generating a TRUNCATE statement

type TxDatabase Uses

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 Uses

func NewTx(dialect string, tx SQLTx) *TxDatabase

Creates a new TxDatabase

func (*TxDatabase) Commit Uses

func (td *TxDatabase) Commit() error

COMMIT the transaction

func (*TxDatabase) Delete Uses

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

func (*TxDatabase) Dialect Uses

func (td *TxDatabase) Dialect() string

returns this databases dialect

func (*TxDatabase) Exec Uses

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

See Database#Exec

func (*TxDatabase) ExecContext Uses

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

See Database#ExecContext

func (*TxDatabase) From Uses

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

Creates a new Dataset for querying a Database.

func (*TxDatabase) Insert Uses

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

func (*TxDatabase) Logger Uses

func (td *TxDatabase) Logger(logger Logger)

Sets the logger

func (*TxDatabase) Prepare Uses

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

See Database#Prepare

func (*TxDatabase) PrepareContext Uses

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

See Database#PrepareContext

func (*TxDatabase) Query Uses

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

See Database#Query

func (*TxDatabase) QueryContext Uses

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

See Database#QueryContext

func (*TxDatabase) QueryRow Uses

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

See Database#QueryRow

func (*TxDatabase) QueryRowContext Uses

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

See Database#QueryRowContext

func (*TxDatabase) Rollback Uses

func (td *TxDatabase) Rollback() error

ROLLBACK the transaction

func (*TxDatabase) ScanStruct Uses

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

See Database#ScanStruct

func (*TxDatabase) ScanStructContext Uses

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

See Database#ScanStructContext

func (*TxDatabase) ScanStructs Uses

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

See Database#ScanStructs

func (*TxDatabase) ScanStructsContext Uses

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

See Database#ScanStructsContext

func (*TxDatabase) ScanVal Uses

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

See Database#ScanVal

func (*TxDatabase) ScanValContext Uses

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

See Database#ScanValContext

func (*TxDatabase) ScanVals Uses

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

See Database#ScanVals

func (*TxDatabase) ScanValsContext Uses

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

See Database#ScanValsContext

func (*TxDatabase) Select Uses

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

func (*TxDatabase) Trace Uses

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

func (*TxDatabase) Truncate Uses

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

func (*TxDatabase) Update Uses

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

func (*TxDatabase) Wrap Uses

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 Uses

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

func Update Uses

func Update(table interface{}) *UpdateDataset

Code:

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' []

Code:

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' []

Code:

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' []

Code:

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 Uses

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 Uses

func (ud *UpdateDataset) ClearLimit() *UpdateDataset

Removes the LIMIT clause.

Code:

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 Uses

func (ud *UpdateDataset) ClearOrder() *UpdateDataset

Removes the ORDER BY clause. See examples.

Code:

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 Uses

func (ud *UpdateDataset) ClearWhere() *UpdateDataset

Removes the WHERE clause. See examples.

Code:

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 Uses

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

Clones the dataset

func (*UpdateDataset) Dialect Uses

func (ud *UpdateDataset) Dialect() SQLDialect

Returns the current adapter on the dataset

func (*UpdateDataset) Error Uses

func (ud *UpdateDataset) Error() error

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

func (*UpdateDataset) Executor Uses

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

Code:

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

Code:

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 Uses

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

func (*UpdateDataset) From Uses

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

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

Code:

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

Code:

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

Code:

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 Uses

func (ud *UpdateDataset) GetAs() exp.IdentifierExpression

func (*UpdateDataset) GetClauses Uses

func (ud *UpdateDataset) GetClauses() exp.UpdateClauses

Returns the current clauses on the dataset.

func (*UpdateDataset) IsPrepared Uses

func (ud *UpdateDataset) IsPrepared() bool

func (*UpdateDataset) Limit Uses

func (ud *UpdateDataset) Limit(limit uint) *UpdateDataset

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

Code:

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 Uses

func (ud *UpdateDataset) LimitAll() *UpdateDataset

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

Code:

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 Uses

func (ud *UpdateDataset) Order(order ...exp.OrderedExpression) *UpdateDataset

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

Code:

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 Uses

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.

Code:

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 Uses

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.

Code:

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 Uses

func (ud *UpdateDataset) Prepared(prepared bool) *UpdateDataset

Set the parameter interpolation behavior. See examples

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

Code:

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 Uses

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

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

Code:

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 Uses

func (ud *UpdateDataset) ReturnsColumns() bool

func (*UpdateDataset) Set Uses

func (ud *UpdateDataset) Set(values interface{}) *UpdateDataset

Sets the values to use in the SET clause. See examples.

Code:

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' []

Code:

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' []

Code:

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' []

Code:

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' []

Code:

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' []

Code:

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' []

Code:

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' []

Code:

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' []

Code:

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' []

Code:

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 Uses

func (ud *UpdateDataset) SetDialect(dialect SQLDialect) *UpdateDataset

Returns the current adapter on the dataset

func (*UpdateDataset) SetError Uses

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 Uses

func (ud *UpdateDataset) Table(table interface{}) *UpdateDataset

Sets the table to update.

Code:

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

Output:

UPDATE "test2" SET "foo"='bar'

Code:

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 Uses

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

Code:

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 Uses

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

Adds a WHERE clause. See examples.

Code:

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

Code:

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

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.

Code:

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 Uses

func (ud *UpdateDataset) WithDialect(dl string) *UpdateDataset

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

func (*UpdateDataset) WithRecursive Uses

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.

Code:

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 Uses

type Vals = exp.Vals

Code:

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

Directories

PathSynopsis
dialect/mysql
dialect/postgres
dialect/sqlite3
exec
exp
mocks
sqlgen
sqlgen/mocks

Package goqu imports 12 packages (graph) and is imported by 10 packages. Updated 2019-12-07. Refresh now. Tools for package owners.