Package goqu

▾ Example

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

▾ Example (As)

Code:

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

Output:

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

▾ Example (HavingClause)

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]

▾ Example

Code:

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

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

Output:

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

▾ Example

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]

▾ Example (WithExOr)

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]

▾ Example (WithOr)

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]

▾ Example

Code:

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

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

Output:

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

▾ Example

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("^[ab]")),
    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" ~ '^[ab]') AND ("col4" IS NULL)) []
SELECT * FROM "test" WHERE (("col1" = ?) AND ("col2" IN (?, ?, ?, ?)) AND ("col3" ~ ?) AND ("col4" IS NULL)) [10 1 2 3 4 ^[ab]]

▾ Example (As)

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"

▾ Example (BetweenComparisons)

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]

▾ Example (Cast)

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

▾ Example (Comparisons)

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)

▾ Example (InOperators)

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

▾ Example (IsComparisons)

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

▾ Example (LikeComparisons)

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(`[ab]`))).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("[ab]"))).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("[ab]"))).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(`[ab]`))).ToSQL()
fmt.Println(sql)

Output:

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

▾ Example (Ordering)

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

▾ Example

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

▾ Example (As)

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"

▾ Example

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

▾ Example (As)

Code:

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

Output:

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

▾ Example (HavingClause)

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]

▾ Example (Search)

Code:

ds := goqu.From("test").
    Select(
        goqu.C("col"),
        goqu.Case().
            When(goqu.C("col").Gt(0), true).
            When(goqu.C("col").Lte(0), false).
            As("is_gt_zero"),
    )
sql, args, _ := ds.ToSQL()
fmt.Println(sql, args)

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

Output:

SELECT "col", CASE  WHEN ("col" > 0) THEN TRUE WHEN ("col" <= 0) THEN FALSE END AS "is_gt_zero" FROM "test" []
SELECT "col", CASE  WHEN ("col" > ?) THEN ? WHEN ("col" <= ?) THEN ? END AS "is_gt_zero" FROM "test" [0 true 0 false]

▾ Example (SearchElse)

Code:

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

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

Output:

SELECT "col", CASE  WHEN ("col" > 10) THEN 'Gt 10' WHEN ("col" > 20) THEN 'Gt 20' ELSE 'Bad Val' END AS "str_val" FROM "test" []
SELECT "col", CASE  WHEN ("col" > ?) THEN ? WHEN ("col" > ?) THEN ? ELSE ? END AS "str_val" FROM "test" [10 Gt 10 20 Gt 20 Bad Val]

▾ Example (Value)

Code:

ds := goqu.From("test").
    Select(
        goqu.C("col"),
        goqu.Case().
            Value(goqu.C("str")).
            When("foo", "FOO").
            When("bar", "BAR").
            As("foo_bar_upper"),
    )
sql, args, _ := ds.ToSQL()
fmt.Println(sql, args)

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

Output:

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

▾ Example (ValueElse)

Code:

ds := goqu.From("test").
    Select(
        goqu.C("col"),
        goqu.Case().
            Value(goqu.C("str")).
            When("foo", "FOO").
            When("bar", "BAR").
            Else("Baz").
            As("foo_bar_upper"),
    )
sql, args, _ := ds.ToSQL()
fmt.Println(sql, args)

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

Output:

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

▾ Example

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

▾ Example

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

▾ Example (As)

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"

▾ Example

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

▾ Example

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]

▾ Example

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]

▾ Example (Where)

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]

▾ Example

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

▾ Example (As)

Code:

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

Output:

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

▾ Example

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"

▾ Example

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

▾ Example

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]

▾ Example (As)

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"

▾ Example (BetweenComparisons)

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]

▾ Example (Comparisons)

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)

▾ Example (InOperators)

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

▾ Example (IsComparisons)

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

▾ Example (LikeComparisons)

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("[ab]")),
).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("[ab]")),
).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("[ab]")),
).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("[ab]")),
).ToSQL()
fmt.Println(sql)

Output:

SELECT * FROM "test" WHERE ((a::text || 'bar') LIKE '%a%')
SELECT * FROM "test" WHERE ((a::text || 'bar') ~ '[ab]')
SELECT * FROM "test" WHERE ((a::text || 'bar') ILIKE '%a%')
SELECT * FROM "test" WHERE ((a::text || 'bar') ~* '[ab]')
SELECT * FROM "test" WHERE ((a::text || 'bar') NOT LIKE '%a%')
SELECT * FROM "test" WHERE ((a::text || 'bar') !~ '[ab]')
SELECT * FROM "test" WHERE ((a::text || 'bar') NOT ILIKE '%a%')
SELECT * FROM "test" WHERE ((a::text || 'bar') !~* '[ab]')

▾ Example (WithArgs)

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]

▾ Example

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

▾ Example (As)

Code:

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

Output:

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

▾ Example

Code:

maxEntry := goqu.From("entry").
    Select(goqu.MAX("int").As("max_int")).
    Where(goqu.Ex{"time": goqu.Op{"lt": goqu.I("e.time")}}).
    As("max_entry")

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

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

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

Output:

SELECT "e"."id", "max_entry"."max_int", "max_id"."id" FROM "entry" AS "e", LATERAL (SELECT MAX("int") AS "max_int" FROM "entry" WHERE ("time" < "e"."time")) AS "max_entry", LATERAL (SELECT "id" FROM "entry" WHERE ("int" = "max_entry"."max_int")) AS "max_id" []
SELECT "e"."id", "max_entry"."max_int", "max_id"."id" FROM "entry" AS "e", LATERAL (SELECT MAX("int") AS "max_int" FROM "entry" WHERE ("time" < "e"."time")) AS "max_entry", LATERAL (SELECT "id" FROM "entry" WHERE ("int" = "max_entry"."max_int")) AS "max_id" []

▾ Example (Join)

Code:

maxEntry := goqu.From("entry").
    Select(goqu.MAX("int").As("max_int")).
    Where(goqu.Ex{"time": goqu.Op{"lt": goqu.I("e.time")}}).
    As("max_entry")

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

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

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

Output:

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

▾ Example

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

▾ Example (As)

Code:

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

Output:

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

▾ Example (HavingClause)

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]

▾ Example

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

▾ Example (As)

Code:

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

Output:

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

▾ Example (HavingClause)

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]

▾ Example

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

▾ Example (WithEx)

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

▾ Example

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]

▾ Example (WithAnd)

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]

▾ Example (WithExMap)

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]

▾ Example (Identifiers)

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

▾ Example (Numbers)

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]

▾ Example (Strings)

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]

▾ Example

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

▾ Example

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

▾ Example

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

▾ Example (As)

Code:

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

Output:

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

▾ Example (HavingClause)

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]

▾ Example

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

▾ Example

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

▾ Example

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

▾ Example

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

▾ Example (WithIdentifier)

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

▾ Example

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

▾ Example (Prepared)

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]

▾ Example

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 "w1" FROM "test" WINDOW "w1" AS (PARTITION BY "a"), "w" AS ("w1" ORDER BY "b" ASC) []
SELECT ROW_NUMBER() OVER ("w" ORDER BY "b") FROM "test" WINDOW "w" AS (PARTITION BY "a") []

▾ Example

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

▾ Example

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

▾ Example

Code:

db := getDB()

fmt.Println(db.Dialect())

Output:

postgres

▾ Example

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

▾ Example

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

▾ Example

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]

▾ Example

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

▾ Example

Code:

ds := goqu.Delete("items")

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

Output:

DELETE FROM "items" []

▾ Example

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 `test` FROM `test`

▾ Example

Code:

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

Output:

DELETE FROM "test"

▾ Example

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"

▾ Example

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

▾ Example (Returning)

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

▾ Example

Code:

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

Output:

DELETE FROM `test` LIMIT 10

▾ Example

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

▾ Example

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

▾ Example

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

▾ Example

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

▾ Example

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]

▾ Example

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

▾ Example

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

▾ Example

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

▾ Example (Prepared)

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]

▾ Example

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

▾ Example

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"

▾ Example (DatasetMysql)

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

Code:

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

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

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

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

Output:

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

▾ Example (DatasetPostgres)

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

Code:

// import _ "github.com/doug-martin/goqu/v9/dialect/postgres"

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

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

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

Output:

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

▾ Example (DatasetSqlite3)

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

Code:

// import _ "github.com/doug-martin/goqu/v9/dialect/sqlite3"

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

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

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

Output:

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

▾ Example (DbMysql)

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

Code:

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

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

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

d := goqu.Dialect("mysql")

db := d.DB(mDB)

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

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

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

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

Output:

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

▾ Example (DbPostgres)

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

Code:

// import _ "github.com/doug-martin/goqu/v9/dialect/postgres"

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

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

d := goqu.Dialect("postgres")

db := d.DB(mDB)

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

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

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

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

Output:

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

▾ Example (DbSqlite3)

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

Code:

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

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

d := 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>

▾ Example

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]

▾ Example (In)

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

▾ Example (WithOp)

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

▾ Example

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 // sql statements are long

▾ Example (WithOp)

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("^[ab]")},
    "col2": goqu.Op{"notLike": regexp.MustCompile("^[ab]")},
    "col3": goqu.Op{"iLike": regexp.MustCompile("^[ab]")},
    "col4": goqu.Op{"notILike": regexp.MustCompile("^[ab]")},
}).ToSQL()
fmt.Println(sql)

Output:

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

▾ Example (ColsAndVals)

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

▾ Example (FromQuery)

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

▾ Example (FromQueryWithCols)

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

▾ Example (GoquRecord)

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

▾ Example (Map)

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

▾ Example (Prepared)

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]

▾ Example (Struct)

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

▾ Example

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"

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

▾ Example

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

▾ Example

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

▾ Example

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

▾ Example (WithFromQuery)

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"

▾ Example

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

▾ Example (RecordReturning)

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]

▾ Example (ScanStructs)

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]

▾ Example (WithRecord)

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

▾ Example

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)

▾ Example

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

▾ Example (Aliased)

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

▾ Example (DoNothing)

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

▾ Example (DoUpdate)

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

▾ Example (DoUpdateWithWhere)

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

▾ Example

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]

▾ Example

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"

▾ Example (WithEmbeddedStruct)

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

▾ Example (WithGoquDefaultIfEmptyTag)

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

▾ Example (WithGoquSkipInsertTag)

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

▾ Example (WithIgnoredEmbedded)

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

▾ Example (WithNilEmbeddedPointer)

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

▾ Example (WithNoDbTag)

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

▾ Example

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

▾ Example

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

▾ Example

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"

▾ Example

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"

▾ Example (BetweenComparisons)

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]

▾ Example (Comparisons)

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]

▾ Example (InComparisons)

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]

▾ Example (IsComparisons)

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

▾ Example (LikeComparisons)

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("[ab]")},
})

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("[ab]")},
})

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("[ab]")},
})

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("[ab]")},
})
sql, args, _ = ds.ToSQL()
fmt.Println(sql, args)

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

Output:

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

▾ Example (WithMultipleKeys)

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

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]

▾ Example (Insert)

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]

▾ Example (Update)

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]

▾ Example

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("^[ab]"),
            },
            "test2.amount": goqu.Op{
                "isNot": nil,
            },
        },
        goqu.ExOr{
            "test3.id":     nil,
            "test3.status": []string{"passed", "active", "registered"},
        }).
    Order(goqu.I("test.created").Desc().NullsLast()).
    GroupBy(goqu.I("test.user_id")).
    Having(goqu.AVG("test3.age").Gt(10))

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

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

▾ Example

Code:

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

Output:

SELECT * FROM "test" []

▾ Example

Code:

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

Output:

SELECT NOW()

▾ Example

Code:

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

Output:

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

▾ Example

Code:

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

Output:

SELECT * FROM "test"

▾ Example

Code:

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

Output:

SELECT * FROM "test"

▾ Example

Code:

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

Output:

SELECT * FROM "test"

▾ Example

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"

▾ Example

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"

▾ Example

Code:

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

Output:

Count is 4

▾ Example

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"

▾ Example

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

▾ Example

Code:

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

Output:

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

▾ Example (On)

Code:

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

Output:

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

▾ Example (OnCoalesce)

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"

▾ Example (OnWithLiteral)

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"

▾ Example (ScannerScanStruct)

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}

▾ Example (ScannerScanVal)

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

▾ Example

Code:

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

Output:

SELECT * FROM "test2"

▾ Example (WithAliasedDataset)

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"

▾ Example (WithDataset)

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"

▾ Example

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"

▾ Example

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

▾ Example

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

▾ Example

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"

▾ Example

Code:

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

Output:

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

▾ Example

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)

▾ Example

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

▾ Example

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

▾ Example

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

▾ Example

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

▾ Example

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

▾ Example

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

▾ Example

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

▾ Example

Code:

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

Output:

SELECT * FROM "test" LIMIT 10

▾ Example

Code:

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

Output:

SELECT * FROM "test" LIMIT ALL

▾ Example

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"

▾ Example

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"

▾ Example

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"

▾ Example

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"

▾ Example

Code:

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

Output:

SELECT * FROM "test" OFFSET 2

▾ Example

Code:

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

Output:

SELECT * FROM "test" ORDER BY "a" ASC

▾ Example (CaseExpression)

Code:

ds := goqu.From("test").Order(goqu.Case().When(goqu.C("num").Gt(10), 0).Else(1).Asc())
sql, _, _ := ds.ToSQL()
fmt.Println(sql)

Output:

SELECT * FROM "test" ORDER BY CASE  WHEN ("num" > 10) THEN 0 ELSE 1 END ASC

▾ Example

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

▾ Example

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

▾ Example

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]

▾ Example

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 // sql statements are long

▾ Example

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

▾ Example

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

▾ Example

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

▾ Example (WithJoinAutoSelect)

In this example we create a new struct that has two structs that represent two table the User and Role fields are tagged with the table name

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

▾ Example (WithJoinManualSelect)

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

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

▾ Example

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

▾ Example (Prepared)

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

▾ Example (WithJoinAutoSelect)

In this example we create a new struct that has two structs that represent two table the User and Role fields are tagged with the table name

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

▾ Example (WithJoinManualSelect)

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

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

▾ Example

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

▾ Example

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]

▾ Example

Code:

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

Output:

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

▾ Example (WithAliasedDataset)

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"

▾ Example (WithDataset)

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

▾ Example (WithLiteral)

Code:

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

Output:

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

▾ Example (WithSQLFunctionExpression)

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"

▾ Example (WithStruct)

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"

▾ Example

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"

▾ Example

Code:

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

Output:

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

▾ Example (Prepared)

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]

▾ Example

Code:

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

Output:

TRUNCATE "items" []

▾ Example

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

▾ Example

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

▾ Example

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

▾ Example

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

▾ Example (Prepared)

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]

▾ Example

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

▾ Example

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"

▾ Example (DeleteDataset)

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]

▾ Example (InsertDataset)

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]

▾ Example (UpdateDataset)

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]

▾ Example

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"

▾ Example (WithGoquRecord)

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

▾ Example (WithMap)

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

▾ Example (WithSkipUpdateTag)

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

▾ Example (WithStruct)

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

▾ Example

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'

▾ Example

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'

▾ Example

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'

▾ Example

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

▾ Example (Returning)

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]

▾ Example

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

▾ Example (Mysql)

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

▾ Example (Postgres)

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

▾ Example

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

▾ Example

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

▾ Example

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

▾ Example

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

▾ Example

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

▾ Example

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]

▾ Example

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"

▾ Example

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

▾ Example (GoquRecord)

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

▾ Example (Map)

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

▾ Example (Struct)

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

▾ Example (WithDefaultIfEmptyTag)

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

▾ Example (WithEmbeddedStruct)

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

▾ Example (WithIgnoredEmbedded)

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

▾ Example (WithNilEmbeddedPointer)

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

▾ Example (WithNoTags)

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

▾ Example (WithSkipUpdateTag)

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

▾ Example

Code:

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

Output:

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

▾ Example (Aliased)

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'

▾ Example (Prepared)

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]

▾ Example

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

▾ Example (Prepared)

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]

▾ Example

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

▾ Example

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"

▾ Example

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