Selecting

• Creating a SelectDataset
• Building SQL
  • Select
  • Distinct
  • From
  • Join
  • Where
  • Limit
  • Offset
  • GroupBy
  • Having
  • Window
  • With
  • SetError
  • ForUpdate
• Executing Queries
  • ScanStructs - Scans rows into a slice of structs
  • ScanStruct - Scans a row into a slice a struct, returns false if a row wasnt found
  • ScanVals- Scans a rows of 1 column into a slice of primitive values
  • ScanVal - Scans a row of 1 column into a primitive value, returns false if a row wasnt found.
  • Scanner - Allows you to interatively scan rows into structs or values.
  • Count - Returns the count for the current query
  • Exists - Reports whether the current query returns at least one row
  • Pluck - Selects a single column and stores the results into a slice of primitive values

To create a SelectDataset you can use

goqu.From and goqu.Select

When you just want to create some quick SQL, this mostly follows the Postgres with the exception of placeholders for prepared statements.

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

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

Output:

SELECT * FROM "table"
SELECT NOW()

DialectWrapper.From and DialectWrapper.Select

Use this when you want to create SQL for a specific dialect

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

dialect := goqu.Dialect("mysql")

sql, _, _ := dialect.From("table").ToSQL()
fmt.Println(sql)

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

Output:

SELECT * FROM `table`
SELECT NOW()

Database.From and Database.Select

Use this when you want to execute the SQL or create SQL for the drivers dialect.

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

mysqlDB := //initialize your db
db := goqu.New("mysql", mysqlDB)

sql, _, _ := db.From("table").ToSQL()
fmt.Println(sql)

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

Output:

SELECT * FROM `table`
SELECT NOW()

Examples

For more examples visit the Docs

Select

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

Output:

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

You can also ues another dataset in your select

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

Selecting a literal

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

Output:

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

Select aggregate functions

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"

Selecting columns from a struct

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)

Output:

SELECT "address", "email_address", "name" FROM "test"

Distinct

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

Output:

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

If you dialect supports DISTINCT ON you provide arguments to the Distinct method.

NOTE currently only the postgres and the default dialects support DISTINCT ON clauses

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

Output:

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

You can also provide other expression arguments

With goqu.L

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"

With goqu.Coalesce

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

Output:

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

From

Overriding the original from

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

Output:

SELECT * FROM "test2"

From another dataset

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"

From an aliased dataset

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"

Lateral Query

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

Join

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

Output:

SELECT * FROM "test" INNER JOIN "test2" ON ("test"."fkey" = "test2"."Id")

InnerJoin

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

Output:

SELECT * FROM "test" INNER JOIN "test2" ON ("test"."fkey" = "test2"."Id")

FullOuterJoin

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

Output:

SELECT * FROM "test" FULL OUTER JOIN "test2" ON ("test"."fkey" = "test2"."Id")

RightOuterJoin

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

Output:

SELECT * FROM "test" RIGHT OUTER JOIN "test2" ON ("test"."fkey" = "test2"."Id")

LeftOuterJoin

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

Output:

SELECT * FROM "test" LEFT OUTER JOIN "test2" ON ("test"."fkey" = "test2"."Id")

FullJoin

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

Output:

SELECT * FROM "test" FULL JOIN "test2" ON ("test"."fkey" = "test2"."Id")

RightJoin

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

Output:

SELECT * FROM "test" RIGHT JOIN "test2" ON ("test"."fkey" = "test2"."Id")

LeftJoin

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

Output:

SELECT * FROM "test" LEFT JOIN "test2" ON ("test"."fkey" = "test2"."Id")

NaturalJoin

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

Output:

SELECT * FROM "test" NATURAL JOIN "test2"

NaturalLeftJoin

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

Output:

SELECT * FROM "test" NATURAL LEFT JOIN "test2"

NaturalRightJoin

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

Output:

SELECT * FROM "test" NATURAL RIGHT LEFT JOIN "test2"

NaturalFullJoin

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

Output:

SELECT * FROM "test" NATURAL FULL LEFT JOIN "test2"

CrossJoin

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

Output:

SELECT * FROM "test" CROSS JOIN "test2"

StraightJoin

NOTE STRAIGHT_JOIN is only supported by the mysql and mysql8 dialects; other dialects return an error. It requires an On condition; Using is rejected because MySQL 5.7 does not accept it for STRAIGHT_JOIN.

sql, _, _ := goqu.Dialect("mysql").From("test").StraightJoin(
	goqu.T("test2"),
	goqu.On(goqu.Ex{"test.fkey": goqu.I("test2.Id")}),
).ToSQL()
fmt.Println(sql)

Output:

SELECT * FROM `test` STRAIGHT_JOIN `test2` ON (`test`.`fkey` = `test2`.`Id`)

CustomJoin

Adds a join clause for syntax goqu does not model, such as ClickHouse’s ARRAY JOIN. The expression is written exactly as given, so you are responsible for generating valid SQL for your database.

NOTE The expression must contain the whole join text including the join keyword (goqu.T("foo") would be written as an alias, not a join). It is not validated or escaped, so never build it from untrusted input; use placeholders for values, e.g. goqu.L("ARRAY JOIN ?", goqu.I("tags")). A dialect that builds its own JoinTypeLookup from scratch (instead of starting from DefaultDialectOptions()) must add exp.CustomJoinType to it, otherwise CustomJoin returns an error.

sql, _, _ := goqu.From("test").
	CustomJoin(goqu.L("ARRAY JOIN tags").As("tag")).
	ToSQL()
fmt.Println(sql)

Output:

SELECT * FROM "test" ARRAY JOIN tags AS "tag"

Join with a Lateral

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]

Where

You can use goqu.Ex to create an ANDed condition

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)

Output:

SELECT * FROM "test" WHERE (("a" > 10) AND ("b" < 10) AND ("c" IS NULL) AND ("d" IN ('a', 'b', 'c')))

You can use goqu.ExOr to create an ORed condition

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)

Output:

SELECT * FROM "test" WHERE (("a" > 10) OR ("b" < 10) OR ("c" IS NULL) OR ("d" IN ('a', 'b', 'c')))

You can use goqu.Ex with goqu.ExOr for complex expressions

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

Output:

SELECT * FROM "test" WHERE ((("a" > 10) AND ("b" < 10)) OR (("c" IS NULL) AND ("d" IN ('a', 'b', 'c'))))

You can also use identifiers to create your where condition

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)

Output:

SELECT * FROM "test" WHERE (("a" > 10) AND ("b" < 10) AND ("c" IS NULL) AND ("d" IN ('a', 'b', 'c')))

Using goqu.Or to create ORed expression

// 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) OR (("b" < 10) AND ("c" IS NULL)))

Limit

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

Output:

SELECT * FROM "test" LIMIT 10

Limit(0) removes the limit. Use LimitZero to force a LIMIT 0 (for example to get the columns of a query without any rows):

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

Output:

SELECT * FROM "test" LIMIT 0

LimitZero is only available on select datasets (not update or delete, where many dialects do not support a LIMIT). ScanVal and ScanStruct keep it, so they report no row. Dialects that paginate with OFFSET/FETCH (sqlserver) cannot fetch zero rows after an offset and return an error for that combination.

Offset

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

Output:

SELECT * FROM "test" OFFSET 2

NOTE: dialects that paginate with OFFSET ... FETCH instead of LIMIT (for example sqlserver) require an ORDER BY when an offset is used, otherwise ToSQL returns an error. See Dialect for databases that only support OFFSET ... FETCH.

GroupBy

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"

Having

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

Output:

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

Qualify

QUALIFY filters rows on the result of window functions (Teradata, Snowflake, BigQuery and others).

NOTE None of the built in dialects support QUALIFY; generating SQL with a Qualify returns an error. Register a dialect with SupportsQualify set to true to use it.

opts := goqu.DefaultDialectOptions()
opts.SupportsQualify = true
goqu.RegisterDialect("qualify", opts)

dialect := goqu.Dialect("qualify")
sql, _, _ := dialect.From("test").
	Select("a", goqu.ROW_NUMBER().Over(goqu.W().PartitionBy("a").OrderBy(goqu.C("b").Asc())).As("rn")).
	Qualify(goqu.C("rn").Eq(1)).
	ToSQL()
fmt.Println(sql)

Output:

SELECT "a", ROW_NUMBER() OVER (PARTITION BY "a" ORDER BY "b" ASC) AS "rn" FROM "test" QUALIFY ("rn" = 1)

With

To use CTEs in SELECT statements you can use the With method.

NOTE Common table expressions are not supported by the mysql dialect (MySQL 5.x); use the mysql8 dialect for MySQL 8.0+.

Simple Example

sql, _, _ := goqu.From("one").
	With("one", goqu.From().Select(goqu.L("1"))).
	Select(goqu.Star()).
	ToSQL()
fmt.Println(sql)

Output:

WITH one AS (SELECT 1) SELECT * FROM "one"

Dependent WITH clauses:

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)

Output:

WITH intermed AS (SELECT * FROM "test" WHERE ("x" >= 5)), derived AS (SELECT * FROM "intermed" WHERE ("x" < 10)) SELECT * FROM "derived"

WITH clause with arguments

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 multi(x,y) AS (SELECT 1, 2) SELECT "x", "y" FROM "multi"

Using a InsertDataset.

insertDs := goqu.Insert("foo").Rows(goqu.Record{"user_id": 10}).Returning("id")

ds := goqu.From("bar").
	With("ins", insertDs).
	Select("bar_name").
	Where(goqu.Ex{"bar.user_id": goqu.I("ins.user_id")})

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

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

Output:

WITH ins AS (INSERT INTO "foo" ("user_id") VALUES (10) RETURNING "id") SELECT "bar_name" FROM "bar" WHERE ("bar"."user_id" = "ins"."user_id")
WITH ins AS (INSERT INTO "foo" ("user_id") VALUES (?) RETURNING "id") SELECT "bar_name" FROM "bar" WHERE ("bar"."user_id" = "ins"."user_id") [10]

Using an UpdateDataset

updateDs := goqu.Update("foo").Set(goqu.Record{"bar": "baz"}).Returning("id")

ds := goqu.From("bar").
	With("upd", updateDs).
	Select("bar_name").
	Where(goqu.Ex{"bar.user_id": goqu.I("ins.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]

Using a DeleteDataset

deleteDs := goqu.Delete("foo").Where(goqu.Ex{"bar": "baz"}).Returning("id")

ds := goqu.From("bar").
	With("del", deleteDs).
	Select("bar_name").
	Where(goqu.Ex{"bar.user_id": goqu.I("del.user_id")})

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

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

Output:

WITH del AS (DELETE FROM "foo" WHERE ("bar" = 'baz') RETURNING "id") SELECT "bar_name" FROM "bar" WHERE ("bar"."user_id" = "del"."user_id")
WITH del AS (DELETE FROM "foo" WHERE ("bar" = ?) RETURNING "id") SELECT "bar_name" FROM "bar" WHERE ("bar"."user_id" = "del"."user_id") [baz]

Window Function

NOTE currently only the postgres, mysql8 (NOT mysql) and the default dialect support Window Function

To use windowing in SELECT statements you can use the Over method on an SQLFunction

sql, _, _ := goqu.From("test").Select(
	goqu.ROW_NUMBER().Over(goqu.W().PartitionBy("a").OrderBy(goqu.I("b").Asc())),
)
fmt.Println(sql)

Output:

SELECT ROW_NUMBER() OVER (PARTITION BY "a" ORDER BY "b") FROM "test"

goqu also supports the WINDOW clause.

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

Output:

SELECT ROW_NUMBER() OVER "w" FROM "test" WINDOW "w" AS (PARTITION BY "a" ORDER BY "b")

SetError

Sometimes while building up a query with goqu you will encounter situations where certain preconditions are not met or some end-user contraint has been violated. While you could track this error case separately, goqu provides a convenient built-in mechanism to set an error on a dataset if one has not already been set to simplify query building.

Set an Error on a dataset:

func GetSelect(name string) *goqu.SelectDataset {

    var ds = goqu.From("test")

    if len(name) == 0 {
        return ds.SetError(fmt.Errorf("name is empty"))
    }

    return ds.Select(name)
}

This error is returned on any subsequent call to Error or ToSQL:

var name string = ""
ds = GetSelect(name)
fmt.Println(ds.Error())

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

Output:

name is empty
name is empty

ForUpdate

sql, _, _ := goqu.From("test").ForUpdate(exp.Wait).ToSQL()
fmt.Println(sql)

Output:

SELECT * FROM "test" FOR UPDATE

If your dialect supports FOR UPDATE OF you provide tables to be locked as variable arguments to the ForUpdate method.

sql, _, _ := goqu.From("test").ForUpdate(exp.Wait, goqu.T("test")).ToSQL()
fmt.Println(sql)

Output:

SELECT * FROM "test" FOR UPDATE OF "test"

Executing Queries

To execute your query use goqu.Database#From to create your dataset

ScanStructs

Scans rows into a slice of structs

NOTE ScanStructs will only select the columns that can be scanned in to the structs unless you have explicitly selected certain columns.

type User struct{
  FirstName string `db:"first_name"`
  LastName  string `db:"last_name"`
  Age       int    `db:"-"` // a field that shouldn't be selected
}

var users []User
//SELECT "first_name", "last_name" FROM "user";
if err := db.From("user").ScanStructs(&users); err != nil{
  panic(err.Error())
}
fmt.Printf("\n%+v", users)

var users []User
//SELECT "first_name" FROM "user";
if err := db.From("user").Select("first_name").ScanStructs(&users); err != nil{
  panic(err.Error())
}
fmt.Printf("\n%+v", users)

Fields whose type is a non-empty interface that sql.Scanner values can be stored in (such as sql.Scanner itself) can be scanned into by setting the field to a non-nil pointer to a value that implements sql.Scanner before calling ScanStruct; the column is scanned straight into that value. If the field is nil, holds a non-pointer, or you use ScanStructs (which creates new structs), ScanStruct/ScanStructs return an error. The value is scanned in place, so when iterating with a Scanner set a new value for every row, otherwise all of the items you collect share the same value.

NOTE Fields of type interface{} are not scanned in place: they keep receiving the value returned by the driver (even when they already hold a pointer), and a nil interface{} field is fine.

type Item struct {
	ID    int64       `db:"id"`
	Value sql.Scanner `db:"value"`
}

item := Item{Value: &sql.NullString{}}
found, err := db.From("items").ScanStruct(&item)

goqu also supports scanning into multiple structs. In the example below we define a Role and User struct that could both be used individually to scan into. However, you can also create a new struct that adds both structs as fields that can be populated in a single query.

NOTE When calling ScanStructs without a select already defined it will automatically only SELECT the columns found in the struct, omitting any that are tagged with db:"-"

type Role struct {
  Id     uint64 `db:"id"`
	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)
}

You can alternatively manually select the columns with the appropriate aliases using the goqu.C method to create the alias.

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

ScanStruct

Scans a row into a slice a struct, returns false if a row wasnt found

NOTE ScanStruct will only select the columns that can be scanned in to the struct unless you have explicitly selected certain columns.

type User struct{
  FirstName string `db:"first_name"`
  LastName  string `db:"last_name"`
  Age       int    `db:"-"` // a field that shouldn't be selected
}

var user User
// SELECT "first_name", "last_name" FROM "user" LIMIT 1;
found, err := db.From("user").ScanStruct(&user)
if err != nil{
  fmt.Println(err.Error())
  return
}
if !found {
  fmt.Println("No user found")
} else {
  fmt.Printf("\nFound user: %+v", user)
}

goqu also supports scanning into multiple structs. In the example below we define a Role and User struct that could both be used individually to scan into. However, you can also create a new struct that adds both structs as fields that can be populated in a single query.

NOTE When calling ScanStruct without a select already defined it will automatically only SELECT the columns found in the struct, omitting any that are tagged with db:"-"

NOTE The columns and the struct have to line up in both directions:

• If a struct field has no matching column in the database, the query fails (for example Unknown column 'otp_enabled'). There is no tag to make a field optional. Use a struct without the field, or select the columns explicitly with Select(...).
• If your query returns extra columns that are not in the struct (for example SELECT *, (SELECT COUNT(*) FROM t) AS total_count), scanning into that struct fails because the column has nowhere to go. Either leave the extra column out of the query, or scan it separately with ScanVal, or scan into a struct that contains it.

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

found, err := ds.ScanStruct(&userAndRole)
if err != nil{
  fmt.Println(err.Error())
  return
}
if !found {
  fmt.Println("No user found")
} else {
  fmt.Printf("\nFound user: %+v", user)
}

You can alternatively manually select the columns with the appropriate aliases using the goqu.C method to create the alias.

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()
var userAndRole UserAndRole
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("Bob"))

found, err := ds.ScanStruct(&userAndRole)
if err != nil{
  fmt.Println(err.Error())
  return
}
if !found {
  fmt.Println("No user found")
} else {
  fmt.Printf("\nFound user: %+v", user)
}

NOTE Using the goqu.SetColumnRenameFunction function, you can change the function that’s used to rename struct fields when struct tags aren’t defined

import "strings"

goqu.SetColumnRenameFunction(strings.ToUpper)

type User struct{
  FirstName string
  LastName string
}

var user User
//SELECT "FIRSTNAME", "LASTNAME" FROM "user" LIMIT 1;
found, err := db.From("user").ScanStruct(&user)
// ...

NOTE Using the goqu.SetIgnoreUntaggedFields(true) function, you can cause goqu to ignore any fields that aren’t explicitly tagged.

goqu.SetIgnoreUntaggedFields(true)

type User struct{
  FirstName string `db:"first_name"`
  LastName string
}

var user User
//SELECT "first_name" FROM "user" LIMIT 1;
found, err := db.From("user").ScanStruct(&user)
// ...

ScanVals

Scans a rows of 1 column into a slice of primitive values

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

ScanVal

Scans a row of 1 column into a primitive value, returns false if a row wasnt found.

Note when using the dataset a LIMIT of 1 is automatically applied.

var id int64
found, err := db.From("user").Select("id").ScanVal(&id)
if err != nil{
  fmt.Println(err.Error())
  return
}
if !found{
  fmt.Println("No id found")
}else{
  fmt.Printf("\nFound id: %d", id)
}

Scanner

Scanner knows how to scan rows into structs. This is useful when dealing with large result sets where you can have only one item scanned in memory at one time.

In the following example we scan each row into struct.

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

In this example we scan each row into a val.

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

Count

Returns the count for the current query

count, err := db.From("user").Count()
if err != nil{
  fmt.Println(err.Error())
  return
}
fmt.Printf("\nCount:= %d", count)

Exists

Reports whether the current query returns at least one row

exists, err := db.From("user").Where(goqu.C("last_name").Eq("Yukon")).Exists()
if err != nil{
  fmt.Println(err.Error())
  return
}
fmt.Printf("\nExists:= %t", exists)

The dataset is wrapped in an EXISTS subquery, so it is evaluated exactly as written. The result is selected with CASE WHEN, which keeps the SQL valid on every dialect (including those that cannot select a boolean expression directly, such as SQL Server):

SELECT CASE WHEN EXISTS (SELECT * FROM "user" WHERE ("last_name" = 'Yukon')) THEN 1 ELSE 0 END LIMIT 1

Pluck

Selects a single column and stores the results into a slice of primitive values

var ids []int64
if err := db.From("user").Pluck(&ids, "id"); err != nil{
  fmt.Println(err.Error())
  return
}
fmt.Printf("\nIds := %+v", ids)