goqu

package module
v7.4.0 Latest Latest
Warning

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

 Go to latest Published: Jul 24, 2019 License: MIT Imports: 14 Imported by: 0

README 

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

GitHub tag Build Status GoDoc codecov Go Report Card

goqu is an expressive SQL builder and executor

Installation

If using go modules.

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

If you are not using go modules...

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

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

Migrating Between Versions

Features

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

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

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

Why?

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

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

Usage

Dialect

Dialects allow goqu the build the correct SQL for each database. There are three dialects that come packaged with goqu

  • mysql - import _ "github.com/doug-martin/goqu/v7/dialect/mysql"
  • postgres - import _ "github.com/doug-martin/goqu/v7/dialect/postgres"
  • sqlite3 - import _ "github.com/doug-martin/goqu/v7/dialect/sqlite3"

NOTE Dialects work like drivers in go where they are not registered until you import the package.

Below are examples for each dialect. Notice how the dialect is imported and then looked up using goqu.Dialect

Postgres

import (
  "fmt"
  "github.com/doug-martin/goqu/v7"
  // import the dialect
  _ "github.com/doug-martin/goqu/v7/dialect/postgres"
)

// look up the dialect
dialect := goqu.Dialect("postgres")

// use dialect.From to get a dataset to build your SQL
ds := dialect.From("test").Where(goqu.Ex{"id": 10})
sql, args, err := ds.ToSQL()
if err != nil{
  fmt.Println("An error occurred while generating the SQL", err.Error())
}else{
  fmt.Println(sql, args)
}

Output:

SELECT * FROM "test" WHERE "id" = 10 []

MySQL

import (
  "fmt"
  "github.com/doug-martin/goqu/v7"
  // import the dialect
  _ "github.com/doug-martin/goqu/v7/dialect/mysql"
)

// look up the dialect
dialect := goqu.Dialect("mysql")

// use dialect.From to get a dataset to build your SQL
ds := dialect.From("test").Where(goqu.Ex{"id": 10})
sql, args, err := ds.ToSQL()
if err != nil{
  fmt.Println("An error occurred while generating the SQL", err.Error())
}else{
  fmt.Println(sql, args)
}

Output:

SELECT * FROM `test` WHERE `id` = 10 []

SQLite3

import (
  "fmt"
  "github.com/doug-martin/goqu/v7"
  // import the dialect
  _ "github.com/doug-martin/goqu/v7/dialect/sqlite3"
)

// look up the dialect
dialect := goqu.Dialect("sqlite3")

// use dialect.From to get a dataset to build your SQL
ds := dialect.From("test").Where(goqu.Ex{"id": 10})
sql, args, err := ds.ToSQL()
if err != nil{
  fmt.Println("An error occurred while generating the SQL", err.Error())
}else{
  fmt.Println(sql, args)
}

Output:

SELECT * FROM `test` WHERE `id` = 10 []

Dataset

A goqu.Dataset is the most commonly used data structure used in goqu. A Dataset can be used to:

  • build SQL - When used with a dialect and expressions a dataset is an expressive SQL builder
  • execute queries - When used with a goqu.Database a goqu.Dataset can be used to:
    • ScanStruct - scan into a struct
    • ScanStructs - scan into a slice of structs
    • ScanVal - scan into a primitive value or a driver.Valuer
    • ScanVals - scan into a slice of primitive values or driver.Valuers
    • Count - count the number of records in a table
    • Pluck - pluck a column from a table
    • Insert - insert records into a table
    • Update - update records in a table
    • Delete - delete records in a table

Building SQL

To build SQL with a dialect you can use goqu.Dialect

NOTE if you use do not create a goqu.Database you can only create SQL

import (
  "fmt"
  "github.com/doug-martin/goqu/v7"
  _ "github.com/doug-martin/goqu/v7/dialect/postgres"
)

dialect := goqu.Dialect("postgres")

//interpolated sql
ds := dialect.From("test").Where(goqu.Ex{"id": 10})
sql, args, err := ds.ToSQL()
if err != nil{
  fmt.Println("An error occurred while generating the SQL", err.Error())
}else{
  fmt.Println(sql, args)
}

//prepared sql
sql, args, err := ds.Prepared(true).ToSQL()
if err != nil{
  fmt.Println("An error occurred while generating the SQL", err.Error())
}else{
  fmt.Println(sql, args)
}

Output:

SELECT * FROM "test" WHERE "id" = 10 []
SELECT * FROM "test" WHERE "id" = $1 [10]

Expressions

goqu provides an idiomatic DSL for generating SQL. Datasets only act as a clause builder (i.e. Where, From, Select), most of these clause methods accept Expressions which are the building blocks for your SQL statement, you can think of them as fragments of SQL.

The entry points for expressions are:

  • Ex{} - A map where the key will become an Identifier and the Key is the value, this is most commonly used in the Where clause. By default Ex will use the equality operator except in cases where the equality operator will not work, see the example below.

    sql, _, _ := db.From("items").Where(goqu.Ex{
    "col1": "a",
    "col2": 1,
    "col3": true,
    "col4": false,
    "col5": nil,
    "col6": []string{"a", "b", "c"},
}).ToSQL()
fmt.Println(sql)

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

    You can also use the Op map which allows you to create more complex expressions using the map syntax. When using the Op map the key is the name of the comparison you want to make (e.g. "neq", "like", "is", "in"), the key is case insensitive.

    sql, _, _ := db.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)

    Output:

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

    For a more complete examples see the Op and Ex docs

  • ExOr{} - A map where the key will become an Identifier and the Key is the value, this is most commonly used in the Where clause. By default ExOr will use the equality operator except in cases where the equality operator will not work, see the example below.

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

    Output:

    SELECT * FROM "items" WHERE (("col1" = 'a') OR ("col2" = 1) OR ("col3" IS TRUE) OR ("col4" IS FALSE) OR ("col5" IS NULL) OR ("col6" IN ('a', 'b', 'c')))

    You can also use the Op map which allows you to create more complex expressions using the map syntax. When using the Op map the key is the name of the comparison you want to make (e.g. "neq", "like", "is", "in"), the key is case insensitive.

    sql, _, _ := db.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)

    Output:

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

    For a more complete examples see the Op and ExOr docs

  • S() - An Identifier that represents a schema. With a schema identifier you can fully qualify tables and columns. 
    s := goqu.S("my_schema")

// "my_schema"."my_table"
t := s.Table("my_table")

// "my_schema"."my_table"."my_column"

sql, _, _ := goqu.From(t).Select(t.Col("my_column").ToSQL()
// SELECT "my_schema"."my_table"."my_column" FROM "my_schema"."my_table"
fmt.Println(sql)

  • T() - An Identifier that represents a Table. With a Table identifier you can fully qualify columns. 
    t := s.Table("my_table")

sql, _, _ := goqu.From(t).Select(t.Col("my_column").ToSQL()
// SELECT "my_table"."my_column" FROM "my_table"
fmt.Println(sql)

// qualify the table with a schema
sql, _, _ := goqu.From(t.Schema("my_schema")).Select(t.Col("my_column").ToSQL()
// SELECT "my_table"."my_column" FROM "my_schema"."my_table"
fmt.Println(sql)

  • C() - An Identifier that represents a Column. See the docs for more examples 
    sql, _, _ := goqu.From("table").Where(goqu.C("col").Eq(10)).ToSQL()
// SELECT * FROM "table" WHERE "col" = 10
fmt.Println(sql)

  • I() - An Identifier represents a schema, table, or column or any combination. I parses identifiers seperated by a . character. 
    // with three parts it is assumed you have provided a schema, table and column
goqu.I("my_schema.table.col") == goqu.S("my_schema").Table("table").Col("col")

// with two parts it is assumed you have provided a table and column
goqu.I("table.col") == goqu.T("table").Col("col")

// with a single value it is the same as calling goqu.C
goqu.I("col") == goqu.C("col")

  • L() - An SQL literal. You may find yourself in a situation where an IdentifierExpression cannot expression an SQL fragment that your database supports. In that case you can use a LiteralExpression

    // manual casting
goqu.L(`"json"::TEXT = "other_json"::text`)

// custom function invocation
goqu.L(`custom_func("a")`)

// postgres JSON access
goqu.L(`"json_col"->>'someField'`).As("some_field")

    You can also use placeholders in your literal with a ? character. goqu will handle changing it to what the dialect needs (e.g. ? mysql, $1 postgres, ? sqlite3).

    NOTE If your query is not prepared the placeholders will be properly interpolated.

    goqu.L("col IN (?, ?, ?)", "a", "b", "c")

    Putting it together

    ds := db.From("test").Where(
  goqu.L(`("json"::TEXT = "other_json"::TEXT)`),
  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 ("json"::TEXT = "other_json"::TEXT) AND col IN ('a', 'b', 'c') []
-- assuming postgres dialect
SELECT * FROM "test" WHERE ("json"::TEXT = "other_json"::TEXT) AND col IN ($1, $2, $3) [a, b, c]

  • And() - You can use the And function to AND multiple expressions together.

    NOTE By default goqu will AND expressions together

    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]

  • Or() - You can use the Or function to OR multiple expressions together.

    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]

    You can also use Or and And functions in tandem which will give you control not only over how the Expressions are joined together, but also how they are grouped

    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]

    You can also use Or with the map syntax

    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]

Complex Example

Using the Ex map syntax

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

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

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

Using the Expression syntax

ds := db.From("test").
  Select(goqu.COUNT("*")).
  InnerJoin(goqu.I("test2"), goqu.On(goqu.I("test.fkey").Eq(goqu.I("test2.id")))).
  LeftJoin(goqu.I("test3"), goqu.On(goqu.I("test2.fkey").Eq(goqu.I("test3.id")))).
  Where(
    goqu.I("test.name").Like(regexp.MustCompile("^(a|b)")),
    goqu.I("test2.amount").IsNotNull(),
    goqu.Or(
      goqu.I("test3.id").IsNull(),
      goqu.I("test3.status").In("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)

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

Both examples generate the following SQL

-- interpolated
SELECT COUNT(*)
FROM "test"
         INNER JOIN "test2" ON ("test"."fkey" = "test2"."id")
         LEFT JOIN "test3" ON ("test2"."fkey" = "test3"."id")
WHERE ((("test"."name" ~ '^(a|b)') AND ("test2"."amount" IS NOT NULL)) AND
       (("test3"."id" IS NULL) OR ("test3"."status" IN ('passed', 'active', 'registered'))))
GROUP BY "test"."user_id"
HAVING (AVG("test3"."age") > 10)
ORDER BY "test"."created" DESC NULLS LAST []

-- prepared
SELECT COUNT(*)
FROM "test"
         INNER JOIN "test2" ON ("test"."fkey" = "test2"."id")
         LEFT JOIN "test3" ON ("test2"."fkey" = "test3"."id")
WHERE ((("test"."name" ~ ?) AND ("test2"."amount" IS NOT NULL)) AND
       (("test3"."id" IS NULL) OR ("test3"."status" IN (?, ?, ?))))
GROUP BY "test"."user_id"
HAVING (AVG("test3"."age") > ?)
ORDER BY "test"."created" DESC NULLS LAST [^(a|b) passed active registered 10]

Querying

goqu also has basic query support through the use of either the Database or the Dataset.

Executing Queries

You can also create a goqu.Database instance to query records.

In the example below notice that we imported the dialect and driver for side effect only.

import (
  "database/sql"
  "github.com/doug-martin/goqu/v7"
  _ "github.com/doug-martin/goqu/v7/dialect/postgres"
  _ "github.com/lib/pq"
)

dialect := goqu.Dialect("postgres")

pgDb, err := sql.Open("postgres", "user=postgres dbname=goqupostgres sslmode=disable ")
if err != nil {
  panic(err.Error())
}
db := dialect.DB(pgDb)

// "SELECT COUNT(*) FROM "user";
if count, err := db.From("user").Count(); err != nil {
  fmt.Println(err.Error())
}else{
  fmt.Printf("User count = %d", count)
}

  • 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"`
}

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)

  • 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"`
}

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

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

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

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

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

  • Insert - Creates an INSERT statement and returns a QueryExecutor to execute the statement

    insert := db.From("user").Insert(goqu.Record{
  "first_name": "Bob", 
  "last_name":  "Yukon", 
  "created":    time.Now(),
})
if _, err := insert.Exec(); err != nil{
  fmt.Println(err.Error())
  return
}

    Insert will also handle multi inserts if supported by the database

    users := []goqu.Record{
  {"first_name": "Bob",   "last_name": "Yukon", "created": time.Now()},
  {"first_name": "Sally", "last_name": "Yukon", "created": time.Now()},
  {"first_name": "Jimmy", "last_name": "Yukon", "created": time.Now()},
}
if _, err := db.From("user").Insert(users).Exec(); err != nil{
  fmt.Println(err.Error())
  return
}

    If your database supports the RETURN clause you can also use the different Scan methods to get results

    var ids []int64
users := []goqu.Record{
  {"first_name": "Bob",   "last_name": "Yukon", "created": time.Now()},
  {"first_name": "Sally", "last_name": "Yukon", "created": time.Now()},
  {"first_name": "Jimmy", "last_name": "Yukon", "created": time.Now()},
}
if err := db.From("user").Returning(goqu.C("id")).Insert(users).ScanVals(&ids); err != nil{
  fmt.Println(err.Error())
  return
}

  • Update - Creates an UPDATE statement and returns QueryExecutor to execute the statement

    update := db.From("user").
  Where(goqu.C("status").Eq("inactive")).
  Update(goqu.Record{"password": nil, "updated": time.Now()})
if _, err := update.Exec(); err != nil{
  fmt.Println(err.Error())
  return
}

    If your database supports the RETURN clause you can also use the different Scan methods to get results

    var ids []int64
update := db.From("user").
  Where(goqu.Ex{"status":"inactive"}).
  Returning("id").
  Update(goqu.Record{"password": nil, "updated": time.Now()})
if err := update.ScanVals(&ids); err != nil{
  fmt.Println(err.Error())
  return
}

  • Delete - Creates an DELETE statement and returns a QueryExecutor to execute the statement

    delete := db.From("invoice").
  Where(goqu.Ex{"status":"paid"}).
  Delete()
if _, err := delete.Exec(); err != nil{
  fmt.Println(err.Error())
  return
}

    If your database supports the RETURN clause you can also use the different Scan methods to get results

    var ids []int64
delete := db.From("invoice").
  Where(goqu.C("status").Eq("paid")).
  Returning(goqu.C("id")).
  Delete()
if err := delete.ScanVals(&ids); err != nil{
  fmt.Println(err.Error())
  return
}

Prepared Statements

By default the Dataset will interpolate all parameters, if you do not want to have values interpolated you can use the Prepared method to prevent this.

Note For the examples all placeholders are ? this will be dialect specific when using other examples (e.g. Postgres $1, $2...)


preparedDs := db.From("items").Prepared(true)

sql, args, _ := preparedDs.Where(goqu.Ex{
	"col1": "a",
	"col2": 1,
	"col3": true,
	"col4": false,
	"col5": []string{"a", "b", "c"},
}).ToSQL()
fmt.Println(sql, args)

sql, args, _ = preparedDs.ToInsertSQL(
	goqu.Record{"name": "Test1", "address": "111 Test Addr"},
	goqu.Record{"name": "Test2", "address": "112 Test Addr"},
)
fmt.Println(sql, args)

sql, args, _ = preparedDs.ToUpdateSQL(
	goqu.Record{"name": "Test", "address": "111 Test Addr"},
)
fmt.Println(sql, args)

sql, args, _ = preparedDs.
	Where(goqu.Ex{"id": goqu.Op{"gt": 10}}).
	ToDeleteSQL()
fmt.Println(sql, args)

// Output:
// SELECT * FROM "items" WHERE (("col1" = ?) AND ("col2" = ?) AND ("col3" IS TRUE) AND ("col4" IS FALSE) AND ("col5" IN (?, ?, ?))) [a 1 a b c]
// INSERT INTO "items" ("address", "name") VALUES (?, ?), (?, ?) [111 Test Addr Test1 112 Test Addr Test2]
// UPDATE "items" SET "address"=?,"name"=? [111 Test Addr Test]
// DELETE FROM "items" WHERE ("id" > ?) [10]

When setting prepared to true executing the SQL using the different querying methods will also use the non-interpolated SQL also.

var items []Item
sql, args, _ := db.From("items").Prepared(true).Where(goqu.Ex{
	"col1": "a",
	"col2": 1,
}).ScanStructs(&items)

//Is the same as
db.ScanStructs(&items, `SELECT * FROM "items" WHERE (("col1" = ?) AND ("col2" = ?))`,  "a", 1)

Database

The Database also allows you to execute queries but expects raw SQL to execute. The supported methods are

Transactions

goqu has builtin support for transactions to make the use of the Datasets and querying seamless

tx, err := db.Begin()
if err != nil{
   return err
}
//use tx.From to get a dataset that will execute within this transaction
update := tx.From("user").
    Where(goqu.Ex("password": nil}).
    Update(goqu.Record{"status": "inactive"})
if _, err = update.Exec(); err != nil{
    if rErr := tx.Rollback(); rErr != nil{
        return rErr
    }
    return err
}
if err = tx.Commit(); err != nil{
    return err
}
return

The TxDatabase also has all methods that the Database has along with

Wrap

The TxDatabase.Wrap is a convience method for automatically handling COMMIT and ROLLBACK

tx, err := db.Begin()
if err != nil{
   return err
}
err = tx.Wrap(func() error{
  update := tx.From("user").
      Where(goqu.Ex("password": nil}).
      Update(goqu.Record{"status": "inactive"})
  return update.Exec()
})
//err will be the original error from the update statement, unless there was an error executing ROLLBACK
if err != nil{
    return err
}

Logging

To enable trace logging of SQL statements use the Database.Logger method to set your logger.

NOTE The logger must implement the Logger interface

NOTE If you start a transaction using a database your set a logger on the transaction will inherit that logger automatically

Custom Dialects

Dialects in goqu are the foundation of building the correct SQL for each DB dialect.

Dialect Options

Most SQL dialects share a majority of their syntax, for this reason goqu has a default set of dialect options that can be used as a base for any new Dialect.

When creating a new SQLDialect you just need to override the default values that are documented in SQLDialectOptions.

Take a look at postgres, mysql and sqlite3 for examples.

Creating a custom dialect

When creating a new dialect you must register it using RegisterDialect. This method requires 2 arguments.

  1. dialect string - The name of your dialect
  2. opts SQLDialectOptions - The custom options for your dialect

For example you could create a custom dialect that replaced the default quote '"' with a backtick `

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

For more examples look at postgres, mysql and sqlite3 for examples.

Migrating Between Versions

<v7 to v7

  • Updated all sql generations methods to from Sql to SQL
    • ToSql -> ToSQL
    • ToInsertSql -> ToInsertSQL
    • ToUpdateSql -> ToUpdateSQL
    • ToDeleteSql -> ToDeleteSQL
    • ToTruncateSql -> ToTruncateSQL
  • Abstracted out dialect_options from the adapter to make the dialect self contained.
    • This also removed the dataset<->adapter co dependency making the dialect self contained.
    • Added new dialect options to specify the order than SQL statements are built.
  • Refactored the goqu.I method.
    • Added new goqu.S, goqu.T and goqu.C methods to clarify why type of identifier you are using.
    • goqu.I should only be used when you have a qualified identifier (e.g. `goqu.I("my_schema.my_table.my_col")
  • Added new goqu.Dialect method to make using goqu as an SQL builder easier.

Contributions

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

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

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

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

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

New Features

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

  1. The use case
  2. A short example

If you are issuing a PR also also include the following

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

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

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

Running tests

The test suite requires a postgres and mysql database. You can override the mysql/postgres connection strings with the MYSQL_URI and PG_URI environment variables*

go test -v -race ./...

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

GO_VERSION=latest docker-compose run goqu

License

goqu is released under the MIT License.

Documentation

Overview

goqu an idiomatch SQL builder, and query package. 

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

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

Index

Examples

Constants

View Source 
const (
	Wait       = exp.Wait
	NoWait     = exp.NoWait
	SkipLocked = exp.SkipLocked
)
View Source 
const (
	CommonTableSQLFragment = iota
	SelectSQLFragment
	FromSQLFragment
	JoinSQLFragment
	WhereSQLFragment
	GroupBySQLFragment
	HavingSQLFragment
	CompoundsSQLFragment
	OrderSQLFragment
	LimitSQLFragment
	OffsetSQLFragment
	ForSQLFragment
	UpdateBeginSQLFragment
	SourcesSQLFragment
	UpdateSQLFragment
	ReturningSQLFragment
	InsertBeingSQLFragment
	InsertSQLFragment
	DeleteBeginSQLFragment
	TruncateSQLFragment
)

Variables

View Source 
var (
	TrueLiteral  = exp.NewLiteralExpression("TRUE")
	FalseLiteral = exp.NewLiteralExpression("FALSE")
)

Functions

func AVG 

func AVG(col interface{}) exp.SQLFunctionExpression

Creates a new AVG sql function 

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

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

Output:

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

Output:

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

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

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

Output:

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

func And 

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

A list of expressions that should be ANDed together 

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

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

Output:

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

You can use ExOr inside of And expression lists. 

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

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

Output:

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

You can use And with Or to create more complex queries 

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

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

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

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

Output:

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

func C 

func C(col string) exp.IdentifierExpression

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

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

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

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

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

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

Output:

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

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

Output:

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

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

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

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

Output:

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

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

Output:

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

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

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

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

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

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

Output:

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

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

Output:

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

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

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

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

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

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

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

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

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

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

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

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

Output:

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

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

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

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

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

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

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

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

Output:

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

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

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

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

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

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

Output:

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

func COALESCE 

func COALESCE(vals ...interface{}) exp.SQLFunctionExpression

Creates a new COALESCE sql function 

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

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

Output:

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

Output:

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

func COUNT 

func COUNT(col interface{}) exp.SQLFunctionExpression

Creates a new COUNT sql function 

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

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

Output:

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

Output:

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

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

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

Output:

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

func Cast 

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

Creates a new Casted expression 

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

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

Output:

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

func DISTINCT 

func DISTINCT(col interface{}) exp.SQLFunctionExpression

Creates a new DISTINCT sql function 

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

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

Output:

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

Output:

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

func Default 

func Default() exp.LiteralExpression

Returns a literal for DEFAULT sql keyword

Example 
ds := goqu.From("items")

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

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

Output:

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

func DeregisterDialect 

func DeregisterDialect(name string)

func DoNothing 

func DoNothing() exp.ConflictExpression

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

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

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

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

Output:

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

func DoUpdate 

func DoUpdate(target string, update interface{}) exp.ConflictUpdateExpression

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

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

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

sql, args, _ = ds.Prepared(true).ToInsertConflictSQL(
	goqu.DoUpdate("address", goqu.C("address").Set(goqu.I("EXCLUDED.address"))),
	goqu.Record{"address": "111 Address"},
)
fmt.Println(sql, args)

Output:

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

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

sql, args, _ = ds.Prepared(true).ToInsertConflictSQL(
	goqu.DoUpdate("address", goqu.C("address").Set(goqu.I("EXCLUDED.address"))).Where(goqu.I("items.updated").IsNull()),
	goqu.Record{"address": "111 Address"},
)
fmt.Println(sql, args)

// nolint:lll

Output:

func FIRST 

func FIRST(col interface{}) exp.SQLFunctionExpression

Creates a new FIRST sql function 

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

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

Output:

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

Output:

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

func Func 

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

Creates a new SQLFunctionExpression with the given name and arguments

Example

This example shows how to create custom SQL Functions 

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

Output:

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

func I 

func I(ident string) exp.IdentifierExpression

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

The identifier will be split by '.'

Table and Column example 

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

Schema table and column 

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

Table with star 

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

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

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

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

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

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

Output:

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

func L 

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

Creates a new SQL literal with the provided arguments. 

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

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

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

Literals can also contain placeholders for other expressions 

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

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

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

Output:

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

Output:

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

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

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

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

Output:

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

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

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

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

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

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

Output:

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

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

Output:

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

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

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

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

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

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

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

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

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

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

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

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

Output:

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

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

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

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

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

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

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

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

Output:

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

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

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

Output:

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

func LAST 

func LAST(col interface{}) exp.SQLFunctionExpression

Creates a new LAST sql function 

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

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

Output:

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

Output:

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

func Literal 

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

Alias for goqu.L

func MAX 

func MAX(col interface{}) exp.SQLFunctionExpression

Creates a new MAX sql function 

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

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

Output:

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

Output:

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

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

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

Output:

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

func MIN 

func MIN(col interface{}) exp.SQLFunctionExpression

Creates a new MIN sql function 

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

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

Output:

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

Output:

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

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

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

Output:

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

func On 

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

Creates a new ON clause to be used within a join 

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

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

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

Output:

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

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

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

Output:

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

func Or 

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

A list of expressions that should be ORed together 

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

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

Output:

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

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

Output:

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

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

Output:

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

func Range 

func Range(start, end interface{}) exp.RangeVal

Creates a new Range to be used with a Between expression 

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

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

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

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

Output:

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

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

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

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

Output:

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

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

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

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

Output:

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

func RegisterDialect 

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

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

ds := dialect.From("test")

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

Output:

SELECT * FROM `test` []

func S 

func S(schema string) exp.IdentifierExpression

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

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

Output:

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

func SUM 

func SUM(col interface{}) exp.SQLFunctionExpression

Creates a new SUM sql function 

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

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

Output:

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

Output:

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

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

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

Output:

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

func SetColumnRenameFunction 

func SetColumnRenameFunction(renameFunc func(string) string)

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

func Star 

func Star() exp.LiteralExpression

Creates a literal *

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

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

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

Output:

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

func T 

func T(table string) exp.IdentifierExpression

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

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

Output:

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

func Using 

func Using(columns ...interface{}) exp.JoinCondition

Creates a new USING clause to be used within a join 

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

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

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

Output:

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

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

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

Output:

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

Types

type Database 

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

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

func New 

func New(dialect string, db SQLDatabase) *Database

func (*Database) Begin 

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

Starts a new Transaction.

Example 
db := getDb()

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

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

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

Output:

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

func (*Database) BeginTx added in v7.3.1 

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

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

Example 
db := getDb()

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

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

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

Output:

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

func (*Database) Dialect 

func (d *Database) Dialect() string

returns this databases dialect

Example 
db := getDb()

fmt.Println(db.Dialect())

Output:

postgres

func (*Database) Exec 

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

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

query: The SQL to execute

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

Example 
db := getDb()

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

Output:

Dropped table goqu_user

func (*Database) ExecContext 

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

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

query: The SQL to execute

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

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

Output:

Dropped table goqu_user

func (*Database) From 

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

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

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

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

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

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

Output:

Fetched Users names: [Bob Sally Vinita John]

func (*Database) Logger 

func (d *Database) Logger(logger Logger)

Sets the logger for to use when logging queries

func (*Database) Prepare 

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

Can be used to prepare a query.

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

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

query: The SQL statement to prepare.

func (*Database) PrepareContext 

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

Can be used to prepare a query.

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

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

query: The SQL statement to prepare.

func (*Database) Query 

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

Used to query for multiple rows.

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

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

query: The SQL to execute

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

func (*Database) QueryContext 

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

Used to query for multiple rows.

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

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

query: The SQL to execute

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

func (*Database) QueryRow 

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

Used to query for a single row.

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

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

query: The SQL to execute

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

func (*Database) QueryRowContext 

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

Used to query for a single row.

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

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

query: The SQL to execute

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

func (*Database) ScanStruct 

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

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

i: A pointer to a struct

query: The SQL to execute

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

func (*Database) ScanStructContext 

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

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

i: A pointer to a struct

query: The SQL to execute

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

func (*Database) ScanStructs 

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

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

i: A pointer to a slice of structs

query: The SQL to execute

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

func (*Database) ScanStructsContext 

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

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

i: A pointer to a slice of structs

query: The SQL to execute

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

func (*Database) ScanVal 

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

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

i: A pointer to a primitive value

query: The SQL to execute

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

func (*Database) ScanValContext 

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

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

i: A pointer to a primitive value

query: The SQL to execute

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

func (*Database) ScanVals 

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

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

i: A pointer to a slice of primitive values

query: The SQL to execute

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

func (*Database) ScanValsContext 

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

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

i: A pointer to a slice of primitive values

query: The SQL to execute

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

func (*Database) Trace 

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

Logs a given operation with the specified sql and arguments

func (*Database) WithTx added in v7.4.0 

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

WithTx starts a new transaction and executes it in Wrap method

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

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

Output:

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

type Dataset 

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

A Dataset is used to build up an SQL statement, each method returns a copy of the current Dataset with options added to it.

Once done building up your Dataset you can either call an action method on it to execute the statement or use one of the SQL generation methods.

Common SQL clauses are represented as methods on the Dataset (e.g. Where, From, Select, Limit...)

  • ToSQL() - Returns a SELECT statement
  • ToUpdateSQL() - Returns an UPDATE statement
  • ToInsertSQL(rows ...interface{}) - Returns an INSERT statement
  • ToDeleteSQL() - Returns a DELETE statement
  • ToTruncateSQL() - Returns a TRUNCATE statement.

Each SQL generation method returns an interpolated statement. Without interpolation each SQL statement could cause two calls to the database:

  1. Prepare the statement
  2. Execute the statement with arguments

Instead with interpolation the database just executes the statement 

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

// Output:
// SELECT * FROM "test" WHERE "a" = 10

Sometimes you might want to generated a prepared statement in which case you would use one of the "Prepared" method on the dataset 

sql, args, err := From("test").Prepared(true).Where(I("a").Eq(10)).ToSQL()
fmt.Println(sql, args)

// Output:
// SELECT * FROM "test" WHERE "a" = ? [10]

A Dataset can also execute statements directly. By calling:

  • ScanStructs(i interface{}) - Scans returned rows into a slice of structs
  • ScanStruct(i interface{}) - Scans a single rom into a struct, if no struct is found this method will return false
  • ScanVals(i interface{}) - Scans rows of one columns into a slice of primitive values
  • ScanVal(i interface{}) - Scans a single row of one column into a primitive value
  • Count() - Returns a count of rows
  • Pluck(i interface{}, col string) - Retrives a columns from rows and scans the resules into a slice of primitive values.

Update, Delete, and Insert return an CrudExec struct which can be used to scan values or just execute the statement. You might use the scan methods if the database supports return values. For example 

UPDATE "items" SET updated = NOW RETURNING "items".*

Could be executed with ScanStructs.

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

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

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

Output:

func From 

func From(table ...interface{}) *Dataset
Example 
sql, args, _ := goqu.From("test").ToSQL()
fmt.Println(sql, args)

Output:

SELECT * FROM "test" []

func (*Dataset) AppendSQL 

func (d *Dataset) AppendSQL(b sb.SQLBuilder)

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

func (*Dataset) As 

func (d *Dataset) As(alias string) *Dataset

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

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

Output:

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

func (*Dataset) ClearLimit 

func (d *Dataset) ClearLimit() *Dataset

Removes the LIMIT clause.

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

Output:

SELECT * FROM "test"

func (*Dataset) ClearOffset 

func (d *Dataset) ClearOffset() *Dataset

Removes the OFFSET clause from the Dataset

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

Output:

SELECT * FROM "test"

func (*Dataset) ClearOrder 

func (d *Dataset) ClearOrder() *Dataset

Removes the ORDER BY clause. See examples.

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

Output:

SELECT * FROM "test"

func (*Dataset) ClearSelect 

func (d *Dataset) ClearSelect() *Dataset

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

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

Output:

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

func (*Dataset) ClearWhere 

func (d *Dataset) ClearWhere() *Dataset

Removes the WHERE clause. See examples.

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

Output:

SELECT * FROM "test"

func (*Dataset) Clone 

func (d *Dataset) Clone() exp.Expression

Clones the dataset

func (*Dataset) CompoundFromSelf 

func (d *Dataset) CompoundFromSelf() *Dataset

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

func (*Dataset) Count 

func (d *Dataset) Count() (int64, error)

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

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

Output:

Count:= 4

func (*Dataset) CountContext 

func (d *Dataset) CountContext(ctx context.Context) (int64, error)

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

func (*Dataset) CrossJoin 

func (d *Dataset) CrossJoin(table exp.Expression) *Dataset

Adds a CROSS JOIN clause. See examples.

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

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

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

Output:

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

func (*Dataset) Delete 

func (d *Dataset) Delete() exec.QueryExecutor

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

db.From("test").Where(I("id").Gt(10)).Exec()
Example 
db := getDb()

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

Output:

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

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

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

Output:

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

func (*Dataset) Dialect 

func (d *Dataset) Dialect() SQLDialect

Returns the current adapter on the dataset

func (*Dataset) Expression 

func (d *Dataset) Expression() exp.Expression

func (*Dataset) ForKeyShare 

func (d *Dataset) ForKeyShare(waitOption exp.WaitOption) *Dataset

Adds a FOR KEY SHARE clause. See examples.

func (*Dataset) ForNoKeyUpdate 

func (d *Dataset) ForNoKeyUpdate(waitOption exp.WaitOption) *Dataset

Adds a FOR NO KEY UPDATE clause. See examples.

func (*Dataset) ForShare 

func (d *Dataset) ForShare(waitOption exp.WaitOption) *Dataset

Adds a FOR SHARE clause. See examples.

func (*Dataset) ForUpdate 

func (d *Dataset) ForUpdate(waitOption exp.WaitOption) *Dataset

Adds a FOR UPDATE clause. See examples.

func (*Dataset) From 

func (d *Dataset) From(from ...interface{}) *Dataset

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

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

Output:

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

Output:

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

Output:

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

func (*Dataset) FromSelf 

func (d *Dataset) FromSelf() *Dataset

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

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

Output:

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

func (*Dataset) FullJoin 

func (d *Dataset) FullJoin(table exp.Expression, condition exp.JoinCondition) *Dataset

Adds a FULL JOIN clause. See examples.

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

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

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

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

Output:

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

func (*Dataset) FullOuterJoin 

func (d *Dataset) FullOuterJoin(table exp.Expression, condition exp.JoinCondition) *Dataset

Adds a FULL OUTER JOIN clause. See examples.

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

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

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

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

Output:

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

func (*Dataset) GetClauses 

func (d *Dataset) GetClauses() exp.Clauses

Returns the current clauses on the dataset.

func (*Dataset) GroupBy 

func (d *Dataset) GroupBy(groupBy ...interface{}) *Dataset

Adds a GROUP BY clause. See examples.

func (*Dataset) Having 

func (d *Dataset) Having(expressions ...exp.Expression) *Dataset

Adds a HAVING clause. See examples.

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

Output:

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

func (*Dataset) InnerJoin 

func (d *Dataset) InnerJoin(table exp.Expression, condition exp.JoinCondition) *Dataset

Adds an INNER JOIN clause. See examples.

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

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

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

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

Output:

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

func (*Dataset) Insert 

func (d *Dataset) Insert(i ...interface{}) exec.QueryExecutor

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

db.From("test").Insert(Record{"name":"Bob"}).Exec()

See Dataset#ToInsertSQL for arguments

Example (RecordExec) 
db := getDb()
insert := db.From("goqu_user").Insert(
	goqu.Record{"first_name": "Jed", "last_name": "Riley", "created": time.Now()},
)
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.From("goqu_user").Insert(users).Exec(); err != nil {
	fmt.Println(err.Error())
} else {
	fmt.Printf("Inserted %d users", len(users))
}

Output:

Inserted 1 user
Inserted 3 users
Example (RecordReturning) 
db := getDb()

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

insert := db.From("goqu_user").Returning(goqu.C("id")).Insert(
	goqu.Record{"first_name": "Jed", "last_name": "Riley", "created": time.Now()},
)
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.From("goqu_user").Returning(goqu.Star()).Insert([]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()},
})
var insertedUsers []User
if err := insert.ScanStructs(&insertedUsers); err != nil {
	fmt.Println(err.Error())
} else {
	for _, u := range insertedUsers {
		fmt.Printf("Inserted user: [ID=%d], [FirstName=%+s] [LastName=%s]\n", u.ID.Int64, u.FirstName, u.LastName)
	}

}

Output:

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

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

insert := db.From("goqu_user").Returning("id").Insert(
	User{FirstName: "Jed", LastName: "Riley"},
)
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.From("goqu_user").Returning(goqu.Star()).Insert([]User{
	{FirstName: "Greg", LastName: "Farley", Created: time.Now()},
	{FirstName: "Jimmy", LastName: "Stewart", Created: time.Now()},
	{FirstName: "Jeff", LastName: "Jeffers", Created: time.Now()},
})
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]

func (*Dataset) InsertConflict 

func (d *Dataset) InsertConflict(c exp.ConflictExpression, i ...interface{}) exec.QueryExecutor

Generates the INSERT sql with (ON CONFLICT/ON DUPLICATE KEY) clause, and returns an Exec struct with the sql set to the INSERT statement 

db.From("test").InsertConflict(DoNothing(), Record{"name":"Bob"}).Exec()

See Dataset#Upsert for arguments

func (*Dataset) InsertIgnore 

func (d *Dataset) InsertIgnore(i ...interface{}) exec.QueryExecutor

Generates the INSERT IGNORE (mysql) or INSERT ... ON CONFLICT DO NOTHING (postgres) and returns an Exec struct. 

db.From("test").InsertIgnore(DoNothing(), Record{"name":"Bob"}).Exec()

See Dataset#ToInsertConflictSQL for arguments

func (*Dataset) Intersect 

func (d *Dataset) Intersect(other *Dataset) *Dataset

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

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

Output:

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

func (*Dataset) IntersectAll 

func (d *Dataset) IntersectAll(other *Dataset) *Dataset

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

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

Output:

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

func (*Dataset) IsPrepared 

func (d *Dataset) IsPrepared() bool

func (*Dataset) Join 

func (d *Dataset) Join(table exp.Expression, condition exp.JoinCondition) *Dataset

Alias to InnerJoin. See examples.

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

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

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

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

Output:

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

func (*Dataset) LeftJoin 

func (d *Dataset) LeftJoin(table exp.Expression, condition exp.JoinCondition) *Dataset

Adds a LEFT JOIN clause. See examples.

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

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

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

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

Output:

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

func (*Dataset) LeftOuterJoin 

func (d *Dataset) LeftOuterJoin(table exp.Expression, condition exp.JoinCondition) *Dataset

Adds a LEFT OUTER JOIN clause. See examples.

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

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

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

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

Output:

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

func (*Dataset) Limit 

func (d *Dataset) Limit(limit uint) *Dataset

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

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

Output:

SELECT * FROM "test" LIMIT 10

func (*Dataset) LimitAll 

func (d *Dataset) LimitAll() *Dataset

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

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

Output:

SELECT * FROM "test" LIMIT ALL

func (*Dataset) NaturalFullJoin 

func (d *Dataset) NaturalFullJoin(table exp.Expression) *Dataset

Adds a NATURAL FULL JOIN clause. See examples.

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

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

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

Output:

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

func (*Dataset) NaturalJoin 

func (d *Dataset) NaturalJoin(table exp.Expression) *Dataset

Adds a NATURAL JOIN clause. See examples.

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

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

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

Output:

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

func (*Dataset) NaturalLeftJoin 

func (d *Dataset) NaturalLeftJoin(table exp.Expression) *Dataset

Adds a NATURAL LEFT JOIN clause. See examples.

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

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

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

Output:

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

func (*Dataset) NaturalRightJoin 

func (d *Dataset) NaturalRightJoin(table exp.Expression) *Dataset

Adds a NATURAL RIGHT JOIN clause. See examples.

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

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

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

Output:

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

func (*Dataset) Offset 

func (d *Dataset) Offset(offset uint) *Dataset

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

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

Output:

SELECT * FROM "test" OFFSET 2

func (*Dataset) Order 

func (d *Dataset) Order(order ...exp.OrderedExpression) *Dataset

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

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

Output:

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

func (*Dataset) OrderAppend 

func (d *Dataset) OrderAppend(order ...exp.OrderedExpression) *Dataset

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

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

Output:

SELECT * FROM "test" ORDER BY "a" ASC, "b" DESC NULLS LAST

func (*Dataset) OrderPrepend added in v7.2.0 

func (d *Dataset) OrderPrepend(order ...exp.OrderedExpression) *Dataset

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

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

Output:

SELECT * FROM "test" ORDER BY "b" DESC NULLS LAST, "a" ASC

func (*Dataset) Pluck 

func (d *Dataset) Pluck(i interface{}, col string) error

Generates the SELECT sql only selecting the passed in column and uses Exec#ScanVals to scan the result into a slice of primitive values.

i: A slice of primitive values

col: The column to select when generative the SQL

Example 
var lastNames []string
if err := getDb().From("goqu_user").Pluck(&lastNames, "last_name"); err != nil {
	fmt.Println(err.Error())
	return
}
fmt.Printf("LastNames := %+v", lastNames)

Output:

LastNames := [Yukon Yukon Yukon Doe]

func (*Dataset) PluckContext 

func (d *Dataset) PluckContext(ctx context.Context, i interface{}, col string) error

Generates the SELECT sql only selecting the passed in column and uses Exec#ScanValsContext to scan the result into a slice of primitive values.

i: A slice of primitive values

col: The column to select when generative the SQL

func (*Dataset) Prepared 

func (d *Dataset) Prepared(prepared bool) *Dataset

Set the parameter interpolation behavior. See examples

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

Example 
sql, args, _ := goqu.From("items").Prepared(true).Where(goqu.Ex{
	"col1": "a",
	"col2": 1,
	"col3": true,
	"col4": false,
	"col5": []string{"a", "b", "c"},
}).ToSQL()
fmt.Println(sql, args)

sql, args, _ = goqu.From("items").Prepared(true).ToInsertSQL(
	goqu.Record{"name": "Test1", "address": "111 Test Addr"},
	goqu.Record{"name": "Test2", "address": "112 Test Addr"},
)
fmt.Println(sql, args)

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

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

// nolint:lll

Output:

func (*Dataset) Returning 

func (d *Dataset) Returning(returning ...interface{}) *Dataset

Adds a RETURNING clause to the dataset if the adapter supports it. Typically used for INSERT, UPDATE or DELETE. See examples.

Example 
sql, _, _ := goqu.From("test").
	Returning("id").
	ToInsertSQL(goqu.Record{"a": "a", "b": "b"})
fmt.Println(sql)
sql, _, _ = goqu.From("test").
	Returning(goqu.T("test").All()).
	ToInsertSQL(goqu.Record{"a": "a", "b": "b"})
fmt.Println(sql)
sql, _, _ = goqu.From("test").
	Returning("a", "b").
	ToInsertSQL(goqu.Record{"a": "a", "b": "b"})
fmt.Println(sql)

Output:

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

func (*Dataset) RightJoin 

func (d *Dataset) RightJoin(table exp.Expression, condition exp.JoinCondition) *Dataset

Adds a RIGHT JOIN clause. See examples.

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

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

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

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

Output:

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

func (*Dataset) RightOuterJoin 

func (d *Dataset) RightOuterJoin(table exp.Expression, condition exp.JoinCondition) *Dataset

Adds a RIGHT OUTER JOIN clause. See examples.

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

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

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

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

Output:

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

func (*Dataset) ScanStruct 

func (d *Dataset) ScanStruct(i interface{}) (bool, error)

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

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

i: A pointer to a structs

Example 
type User struct {
	FirstName string `db:"first_name"`
	LastName  string `db:"last_name"`
}
db := getDb()
findUserByName := func(name string) {
	var user User
	ds := db.From("goqu_user").Where(goqu.C("first_name").Eq(name))
	found, err := ds.ScanStruct(&user)
	switch {
	case err != nil:
		fmt.Println(err.Error())
	case !found:
		fmt.Printf("No user found for first_name %s\n", name)
	default:
		fmt.Printf("Found user: %+v\n", user)
	}
}

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

Output:

Found user: {FirstName:Bob LastName:Yukon}
No user found for first_name Zeb

func (*Dataset) ScanStructContext 

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

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

ScanStructContext will only select the columns that can be scanned in to the struct unless you have explicitly selected certain columns. See examples.

i: A pointer to a structs

func (*Dataset) ScanStructs 

func (d *Dataset) ScanStructs(i interface{}) error

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

ScanStructs will only select the columns that can be scanned in to the struct unless you have explicitly selected certain columns. See examples.

i: A pointer to a slice of structs

Example 
type User struct {
	FirstName string `db:"first_name"`
	LastName  string `db:"last_name"`
}
db := getDb()
var users []User
if err := db.From("goqu_user").ScanStructs(&users); err != nil {
	fmt.Println(err.Error())
	return
}
fmt.Printf("\n%+v", users)

users = users[0:0]
if err := db.From("goqu_user").Select("first_name").ScanStructs(&users); err != nil {
	fmt.Println(err.Error())
	return
}
fmt.Printf("\n%+v", users)

Output:

[{FirstName:Bob LastName:Yukon} {FirstName:Sally LastName:Yukon} {FirstName:Vinita LastName:Yukon} {FirstName:John LastName:Doe}]
[{FirstName:Bob LastName:} {FirstName:Sally LastName:} {FirstName:Vinita LastName:} {FirstName:John LastName:}]
Example (Prepared) 
type User struct {
	FirstName string `db:"first_name"`
	LastName  string `db:"last_name"`
}
db := getDb()

ds := db.From("goqu_user").
	Prepared(true).
	Where(goqu.Ex{
		"last_name": "Yukon",
	})

var users []User
if err := ds.ScanStructs(&users); err != nil {
	fmt.Println(err.Error())
	return
}
fmt.Printf("\n%+v", users)

Output:

[{FirstName:Bob LastName:Yukon} {FirstName:Sally LastName:Yukon} {FirstName:Vinita LastName:Yukon}]

func (*Dataset) ScanStructsContext 

func (d *Dataset) ScanStructsContext(ctx context.Context, i interface{}) error

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

ScanStructsContext will only select the columns that can be scanned in to the struct unless you have explicitly selected certain columns. See examples.

i: A pointer to a slice of structs

func (*Dataset) ScanVal 

func (d *Dataset) ScanVal(i interface{}) (bool, error)

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

i: A pointer to a primitive value

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

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

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

Output:

Found userId: 1
No id found for user Zeb

func (*Dataset) ScanValContext 

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

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

i: A pointer to a primitive value

func (*Dataset) ScanVals 

func (d *Dataset) ScanVals(i interface{}) error

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

i: A pointer to a slice of primitive values

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

Output:

UserIds = [1 2 3 4]

func (*Dataset) ScanValsContext 

func (d *Dataset) ScanValsContext(ctx context.Context, i interface{}) error

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

i: A pointer to a slice of primitive values

func (*Dataset) Select 

func (d *Dataset) Select(selects ...interface{}) *Dataset

Adds columns to the SELECT clause. See examples You can pass in the following. 

string: Will automatically be turned into an identifier
Dataset: Will use the SQL generated from that Dataset. If the dataset is aliased it will use that alias as the
column name.
LiteralExpression: (See Literal) Will use the literal SQL
SQLFunction: (See Func, MIN, MAX, COUNT....)
Struct: If passing in an instance of a struct, we will parse the struct for the column names to select.
See examples
Example 
sql, _, _ := goqu.From("test").Select("a", "b", "c").ToSQL()
fmt.Println(sql)

Output:

SELECT "a", "b", "c" FROM "test"
Example (WithAliasedDataset) 
ds := goqu.From("test")
fromDs := ds.Select("age").Where(goqu.C("age").Gt(10))
sql, _, _ := ds.From().Select(fromDs.As("ages")).ToSQL()
fmt.Println(sql)

Output:

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

Output:

SELECT (SELECT "age" FROM "test" WHERE ("age" > 10))
Example (WithLiteral) 
sql, _, _ := goqu.From("test").Select(goqu.L("a + b").As("sum")).ToSQL()
fmt.Println(sql)

Output:

SELECT a + b AS "sum" FROM "test"
Example (WithSQLFunctionExpression) 
sql, _, _ := goqu.From("test").Select(
	goqu.COUNT("*").As("age_count"),
	goqu.MAX("age").As("max_age"),
	goqu.AVG("age").As("avg_age"),
).ToSQL()
fmt.Println(sql)

Output:

SELECT COUNT(*) AS "age_count", MAX("age") AS "max_age", AVG("age") AS "avg_age" FROM "test"
Example (WithStruct) 
ds := goqu.From("test")

type myStruct struct {
	Name         string
	Address      string `db:"address"`
	EmailAddress string `db:"email_address"`
}

// Pass with pointer
sql, _, _ := ds.Select(&myStruct{}).ToSQL()
fmt.Println(sql)

// Pass instance of
sql, _, _ = ds.Select(myStruct{}).ToSQL()
fmt.Println(sql)

type myStruct2 struct {
	myStruct
	Zipcode string `db:"zipcode"`
}

// Pass pointer to struct with embedded struct
sql, _, _ = ds.Select(&myStruct2{}).ToSQL()
fmt.Println(sql)

// Pass instance of struct with embedded struct
sql, _, _ = ds.Select(myStruct2{}).ToSQL()
fmt.Println(sql)

var myStructs []myStruct

// Pass slice of structs, will only select columns from underlying type
sql, _, _ = ds.Select(myStructs).ToSQL()
fmt.Println(sql)

Output:

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

func (*Dataset) SelectAppend 

func (d *Dataset) SelectAppend(selects ...interface{}) *Dataset

Adds columns to the SELECT clause. See examples You can pass in the following. 

string: Will automatically be turned into an identifier
Dataset: Will use the SQL generated from that Dataset. If the dataset is aliased it will use that alias as the
column name.
LiteralExpression: (See Literal) Will use the literal SQL
SQLFunction: (See Func, MIN, MAX, COUNT....)
Example 
ds := goqu.From("test").Select("a", "b")
sql, _, _ := ds.SelectAppend("c").ToSQL()
fmt.Println(sql)
ds = goqu.From("test").SelectDistinct("a", "b")
sql, _, _ = ds.SelectAppend("c").ToSQL()
fmt.Println(sql)

Output:

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

func (*Dataset) SelectDistinct 

func (d *Dataset) SelectDistinct(selects ...interface{}) *Dataset

Adds columns to the SELECT DISTINCT clause. See examples You can pass in the following. 

string: Will automatically be turned into an identifier
Dataset: Will use the SQL generated from that Dataset. If the dataset is aliased it will use that alias as the
column name.
LiteralExpression: (See Literal) Will use the literal SQL
SQLFunction: (See Func, MIN, MAX, COUNT....)
Struct: If passing in an instance of a struct, we will parse the struct for the column names to select.
See examples
Example 
sql, _, _ := goqu.From("test").SelectDistinct("a", "b").ToSQL()
fmt.Println(sql)

Output:

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

func (*Dataset) SetDialect 

func (d *Dataset) SetDialect(dialect SQLDialect) *Dataset

Returns the current adapter on the dataset

func (*Dataset) ToDeleteSQL 

func (d *Dataset) ToDeleteSQL() (sql string, params []interface{}, err error)

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

isPrepared: Set to true to true to ensure values are NOT interpolated

Errors:

  • There is no FROM clause
  • Error generating SQL
Example 
sql, args, _ := goqu.From("items").ToDeleteSQL()
fmt.Println(sql, args)

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

Output:

DELETE FROM "items" []
DELETE FROM "items" WHERE ("id" > 10) []
Example (Prepared) 
sql, args, _ := goqu.From("items").Prepared(true).ToDeleteSQL()
fmt.Println(sql, args)

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

Output:

DELETE FROM "items" []
DELETE FROM "items" WHERE ("id" > ?) [10]
Example (WithReturning) 
ds := goqu.From("items")
sql, args, _ := ds.Returning("id").ToDeleteSQL()
fmt.Println(sql, args)

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

Output:

DELETE FROM "items" RETURNING "id" []
DELETE FROM "items" WHERE ("id" IS NOT NULL) RETURNING "id" []
Example (WithWhere) 
sql, args, _ := goqu.From("items").Where(goqu.C("id").IsNotNull()).ToDeleteSQL()
fmt.Println(sql, args)

Output:

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

func (*Dataset) ToInsertConflictSQL 

func (d *Dataset) ToInsertConflictSQL(o exp.ConflictExpression, rows ...interface{}) (sql string, params []interface{}, err error)

Generates the INSERT [IGNORE] ... ON CONFLICT/DUPLICATE KEY. If Prepared has been called with true then the statement will not be interpolated. See examples.

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

Errors:

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

sql, args, _ = goqu.From("items").ToInsertConflictSQL(
	goqu.DoUpdate("key", goqu.Record{"updated": goqu.L("NOW()")}),
	goqu.Record{"name": "Test1", "address": "111 Test Addr"},
	goqu.Record{"name": "Test2", "address": "112 Test Addr"},
)
fmt.Println(sql, args)

sql, args, _ = goqu.From("items").ToInsertConflictSQL(
	goqu.DoUpdate("key", goqu.Record{"updated": goqu.L("NOW()")}).Where(goqu.C("allow_update").IsTrue()),
	[]item{
		{Name: "Test1", Address: "111 Test Addr"},
		{Name: "Test2", Address: "112 Test Addr"},
	})
fmt.Println(sql, args)

// nolint:lll

Output:
Example (WithGoquSkipInsertTag) 
type item struct {
	ID      uint32 `goqu:"skipinsert"`
	Address string
	Name    string `goqu:"skipinsert"`
}
sql, args, _ := goqu.From("items").ToInsertConflictSQL(
	goqu.DoNothing(),
	item{Name: "Test1", Address: "111 Test Addr"},
	item{Name: "Test2", Address: "112 Test Addr"},
)
fmt.Println(sql, args)

sql, args, _ = goqu.From("items").ToInsertConflictSQL(
	goqu.DoUpdate("key", goqu.Record{"updated": goqu.L("NOW()")}),
	item{Name: "Test1", Address: "111 Test Addr"},
	item{Name: "Test2", Address: "112 Test Addr"},
)
fmt.Println(sql, args)

sql, args, _ = goqu.From("items").ToInsertConflictSQL(
	goqu.DoUpdate("key", goqu.Record{"updated": goqu.L("NOW()")}).Where(goqu.C("allow_update").IsTrue()),
	[]item{
		{Name: "Test1", Address: "111 Test Addr"},
		{Name: "Test2", Address: "112 Test Addr"},
	})
fmt.Println(sql, args)

// nolint:lll

Output:
Example (WithNoDbTag) 
type item struct {
	ID      uint32 `goqu:"skipinsert"`
	Address string
	Name    string
}
sql, args, _ := goqu.From("items").ToInsertConflictSQL(
	goqu.DoNothing(),
	item{Name: "Test1", Address: "111 Test Addr"},
	item{Name: "Test2", Address: "112 Test Addr"},
)
fmt.Println(sql, args)

sql, args, _ = goqu.From("items").ToInsertConflictSQL(
	goqu.DoUpdate("key", goqu.Record{"updated": goqu.L("NOW()")}),
	item{Name: "Test1", Address: "111 Test Addr"},
	item{Name: "Test2", Address: "112 Test Addr"},
)
fmt.Println(sql, args)

sql, args, _ = goqu.From("items").ToInsertConflictSQL(
	goqu.DoUpdate("key", goqu.Record{"updated": goqu.L("NOW()")}).Where(goqu.C("allow_update").IsTrue()),
	[]item{
		{Name: "Test1", Address: "111 Test Addr"},
		{Name: "Test2", Address: "112 Test Addr"},
	})
fmt.Println(sql, args)

// nolint:lll

Output:

func (*Dataset) ToInsertIgnoreSQL 

func (d *Dataset) ToInsertIgnoreSQL(rows ...interface{}) (sql string, params []interface{}, err error)

Generates the default INSERT IGNORE/ INSERT ... ON CONFLICT DO NOTHING statement. If Prepared has been called with true then the statement will not be interpolated. See examples.

c: ConflictExpression action. Can be DoNothing/Ignore or DoUpdate/DoUpdateWhere. rows: variable number arguments of either map[string]interface, Record, struct, or a single slice argument of the accepted types.

Errors:

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

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

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

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

Output:

INSERT INTO "items" ("address", "name") VALUES ('111 Test Addr', 'Test1'), ('112 Test Addr', 'Test2') ON CONFLICT DO NOTHING []
INSERT INTO "items" ("address", "name") VALUES ('111 Test Addr', 'Test1'), ('112 Test Addr', 'Test2') ON CONFLICT DO NOTHING []
INSERT INTO "items" ("address", "name") VALUES ('111 Test Addr', 'Test1'), ('112 Test Addr', 'Test2') ON CONFLICT DO NOTHING []
INSERT INTO "items" ("address", "name") VALUES ('111 Test Addr', 'Test1'), ('112 Test Addr', 'Test2') ON CONFLICT DO NOTHING []
Example (WithGoquSkipInsertTag) 
type item struct {
	ID      uint32 `goqu:"skipinsert"`
	Address string
	Name    string `goqu:"skipinsert"`
}
sql, args, _ := goqu.From("items").ToInsertIgnoreSQL(
	item{Name: "Test1", Address: "111 Test Addr"},
	item{Name: "Test2", Address: "112 Test Addr"},
)
fmt.Println(sql, args)

Output:

INSERT INTO "items" ("address") VALUES ('111 Test Addr'), ('112 Test Addr') ON CONFLICT DO NOTHING []
Example (WithNoDBTag) 
type item struct {
	ID      uint32 `goqu:"skipinsert"`
	Address string
	Name    string
}
sql, args, _ := goqu.From("items").ToInsertIgnoreSQL(
	item{Name: "Test1", Address: "111 Test Addr"},
	item{Name: "Test2", Address: "112 Test Addr"},
)
fmt.Println(sql, args)

Output:

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

func (*Dataset) ToInsertSQL 

func (d *Dataset) ToInsertSQL(rows ...interface{}) (sql string, params []interface{}, err error)

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

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

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

Errors:

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

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

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

sql, args, _ = goqu.From("items").ToInsertSQL(
	[]goqu.Record{
		{"name": "Test1", "address": "111 Test Addr"},
		{"name": "Test2", "address": "112 Test Addr"},
	})
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 (Prepared) 
type item struct {
	ID      uint32 `db:"id" goqu:"skipinsert"`
	Address string `db:"address"`
	Name    string `db:"name"`
}

sql, args, _ := goqu.From("items").Prepared(true).ToInsertSQL(
	item{Name: "Test1", Address: "111 Test Addr"},
	item{Name: "Test2", Address: "112 Test Addr"},
)
fmt.Println(sql, args)

sql, args, _ = goqu.From("items").Prepared(true).ToInsertSQL(
	goqu.Record{"name": "Test1", "address": "111 Test Addr"},
	goqu.Record{"name": "Test2", "address": "112 Test Addr"},
)
fmt.Println(sql, args)

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

sql, args, _ = goqu.From("items").Prepared(true).ToInsertSQL(
	[]goqu.Record{
		{"name": "Test1", "address": "111 Test Addr"},
		{"name": "Test2", "address": "112 Test Addr"},
	})
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 (WithGoquSkipInsertTag) 
type item struct {
	ID      uint32 `goqu:"skipinsert"`
	Address string `goqu:"skipinsert"`
	Name    string
}
sql, args, _ := goqu.From("items").ToInsertSQL(
	item{Name: "Test1", Address: "111 Test Addr"},
	item{Name: "Test2", Address: "112 Test Addr"},
)
fmt.Println(sql, args)

Output:

INSERT INTO "items" ("name") VALUES ('Test1'), ('Test2') []
Example (WithNoDbTag) 
type item struct {
	ID      uint32 `goqu:"skipinsert"`
	Address string
	Name    string
}
sql, args, _ := goqu.From("items").ToInsertSQL(
	item{Name: "Test1", Address: "111 Test Addr"},
	item{Name: "Test2", Address: "112 Test Addr"},
)
fmt.Println(sql, args)

Output:

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

func (*Dataset) ToSQL 

func (d *Dataset) ToSQL() (sql string, params []interface{}, err error)

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

Errors:

  • There is an error generating the SQL
Example 
sql, args, _ := goqu.From("items").Where(goqu.Ex{"a": 1}).ToSQL()
fmt.Println(sql, args)

Output:

SELECT * FROM "items" WHERE ("a" = 1) []
Example (Prepared) 
sql, args, _ := goqu.From("items").Where(goqu.Ex{"a": 1}).Prepared(true).ToSQL()
fmt.Println(sql, args)

Output:

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

func (*Dataset) ToTruncateSQL 

func (d *Dataset) ToTruncateSQL() (sql string, params []interface{}, err error)

Generates the default TRUNCATE statement. See examples.

Errors:

  • There is no FROM clause
  • Error generating SQL
Example 
sql, args, _ := goqu.From("items").ToTruncateSQL()
fmt.Println(sql, args)

Output:

TRUNCATE "items" []

func (*Dataset) ToTruncateWithOptsSQL 

func (d *Dataset) ToTruncateWithOptsSQL(opts exp.TruncateOptions) (sql string, params []interface{}, err error)

Generates the default TRUNCATE statement with the specified options. See examples.

opts: Options to use when generating the TRUNCATE statement

Errors:

  • There is no FROM clause
  • Error generating SQL
Example 
sql, _, _ := goqu.From("items").
	ToTruncateWithOptsSQL(goqu.TruncateOptions{})
fmt.Println(sql)
sql, _, _ = goqu.From("items").
	ToTruncateWithOptsSQL(goqu.TruncateOptions{Cascade: true})
fmt.Println(sql)
sql, _, _ = goqu.From("items").
	ToTruncateWithOptsSQL(goqu.TruncateOptions{Restrict: true})
fmt.Println(sql)
sql, _, _ = goqu.From("items").
	ToTruncateWithOptsSQL(goqu.TruncateOptions{Identity: "RESTART"})
fmt.Println(sql)
sql, _, _ = goqu.From("items").
	ToTruncateWithOptsSQL(goqu.TruncateOptions{Identity: "RESTART", Cascade: true})
fmt.Println(sql)
sql, _, _ = goqu.From("items").
	ToTruncateWithOptsSQL(goqu.TruncateOptions{Identity: "RESTART", Restrict: true})
fmt.Println(sql)
sql, _, _ = goqu.From("items").
	ToTruncateWithOptsSQL(goqu.TruncateOptions{Identity: "CONTINUE"})
fmt.Println(sql)
sql, _, _ = goqu.From("items").
	ToTruncateWithOptsSQL(goqu.TruncateOptions{Identity: "CONTINUE", Cascade: true})
fmt.Println(sql)
sql, _, _ = goqu.From("items").
	ToTruncateWithOptsSQL(goqu.TruncateOptions{Identity: "CONTINUE", Restrict: true})
fmt.Println(sql)

Output:

TRUNCATE "items"
TRUNCATE "items" CASCADE
TRUNCATE "items" RESTRICT
TRUNCATE "items" RESTART IDENTITY
TRUNCATE "items" RESTART IDENTITY CASCADE
TRUNCATE "items" RESTART IDENTITY RESTRICT
TRUNCATE "items" CONTINUE IDENTITY
TRUNCATE "items" CONTINUE IDENTITY CASCADE
TRUNCATE "items" CONTINUE IDENTITY RESTRICT

func (*Dataset) ToUpdateSQL 

func (d *Dataset) ToUpdateSQL(update interface{}) (sql string, params []interface{}, err error)

Generates an UPDATE statement. If `Prepared` has been called with true then the statement will not be interpolated. When using structs you may specify a column to be skipped in the update, (e.g. created) by specifying a goqu tag with `skipupdate` 

type Item struct{
   Id      uint32    `db:"id"
   Created time.Time `db:"created" goqu:"skipupdate"`
   Name    string    `db:"name"`
}

update: can either be a a map[string]interface{}, Record or a struct

Errors:

  • The update is not a of type struct, Record, or map[string]interface{}
  • The update statement has no FROM clause
  • There is an error generating the SQL
Example 
type item struct {
	Address string `db:"address"`
	Name    string `db:"name"`
}
sql, args, _ := goqu.From("items").ToUpdateSQL(
	item{Name: "Test", Address: "111 Test Addr"},
)
fmt.Println(sql, args)

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

sql, args, _ = goqu.From("items").ToUpdateSQL(
	map[string]interface{}{"name": "Test", "address": "111 Test Addr"},
)
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 (Prepared) 
type item struct {
	Address string `db:"address"`
	Name    string `db:"name"`
}

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

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

sql, args, _ = goqu.From("items").Prepared(true).ToUpdateSQL(
	map[string]interface{}{"name": "Test", "address": "111 Test Addr"},
)
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 (WithNoTags) 
type item struct {
	Address string
	Name    string
}
sql, args, _ := goqu.From("items").ToUpdateSQL(
	item{Name: "Test", Address: "111 Test Addr"},
)
fmt.Println(sql, args)

Output:

UPDATE "items" SET "address"='111 Test Addr',"name"='Test' []
Example (WithSkipUpdateTag) 
type item struct {
	Address string `db:"address"`
	Name    string `db:"name" goqu:"skipupdate"`
}
sql, args, _ := goqu.From("items").ToUpdateSQL(
	item{Name: "Test", Address: "111 Test Addr"},
)
fmt.Println(sql, args)

Output:

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

func (*Dataset) Union 

func (d *Dataset) Union(other *Dataset) *Dataset

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

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

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

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

Output:

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

func (*Dataset) UnionAll 

func (d *Dataset) UnionAll(other *Dataset) *Dataset

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

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

Output:

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

func (*Dataset) Update 

func (d *Dataset) Update(i interface{}) exec.QueryExecutor

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

db.From("test").Update(Record{"name":"Bob", update: time.Now()}).Exec()

See Dataset#ToUpdateSQL for arguments

Example 
db := getDb()
update := db.From("goqu_user").
	Where(goqu.C("first_name").Eq("Bob")).
	Update(goqu.Record{"first_name": "Bobby"})

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

Output:

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

Output:

Updated users with ids [1 2 3]

func (*Dataset) Where 

func (d *Dataset) Where(expressions ...exp.Expression) *Dataset

Adds a WHERE clause. See examples.

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

Output:

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

Output:

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

func (*Dataset) With 

func (d *Dataset) With(name string, subquery exp.Expression) *Dataset

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

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

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

Example 
sql, _, _ := goqu.From("one").
	With("one", goqu.From().Select(goqu.L("1"))).
	Select(goqu.Star()).
	ToSQL()
fmt.Println(sql)
sql, _, _ = goqu.From("derived").
	With("intermed", goqu.From("test").Select(goqu.Star()).Where(goqu.C("x").Gte(5))).
	With("derived", goqu.From("intermed").Select(goqu.Star()).Where(goqu.C("x").Lt(10))).
	Select(goqu.Star()).
	ToSQL()
fmt.Println(sql)
sql, _, _ = goqu.From("multi").
	With("multi(x,y)", goqu.From().Select(goqu.L("1"), goqu.L("2"))).
	Select(goqu.C("x"), goqu.C("y")).
	ToSQL()
fmt.Println(sql)

sql, _, _ = goqu.From("test").
	With("moved_rows", goqu.From("other").Where(goqu.C("date").Lt(123))).
	ToInsertSQL(goqu.From("moved_rows"))
fmt.Println(sql)
sql, _, _ = goqu.From("test").
	With("check_vals(val)", goqu.From().Select(goqu.L("123"))).
	Where(goqu.C("val").Eq(goqu.From("check_vals").Select("val"))).
	ToDeleteSQL()
fmt.Println(sql)
sql, _, _ = goqu.From("test").
	With("some_vals(val)", goqu.From().Select(goqu.L("123"))).
	Where(goqu.C("val").Eq(goqu.From("some_vals").Select("val"))).
	ToUpdateSQL(goqu.Record{"name": "Test"})
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"
WITH moved_rows AS (SELECT * FROM "other" WHERE ("date" < 123)) INSERT INTO "test" SELECT * FROM "moved_rows"
WITH check_vals(val) AS (SELECT 123) DELETE FROM "test" WHERE ("val" IN (SELECT "val" FROM "check_vals"))
WITH some_vals(val) AS (SELECT 123) UPDATE "test" SET "name"='Test' WHERE ("val" IN (SELECT "val" FROM "some_vals"))

func (*Dataset) WithDialect 

func (d *Dataset) WithDialect(dl string) *Dataset

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

func (*Dataset) WithRecursive 

func (d *Dataset) WithRecursive(name string, subquery exp.Expression) *Dataset

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

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

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

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

Output:

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

type DialectWrapper 

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

func Dialect 

func Dialect(dialect string) DialectWrapper

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

Example (DatasetMysql)

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

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

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

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

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

Output:

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

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

// import _ "github.com/doug-martin/goqu/v7/adapters/postgres"

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

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

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

Output:

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

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

// import _ "github.com/doug-martin/goqu/v7/adapters/sqlite3"

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

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

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

Output:

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

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

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

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

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

d := goqu.Dialect("mysql")

db := d.DB(mDb)

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

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

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

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

Output:

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

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

// import _ "github.com/doug-martin/goqu/v7/adapters/postgres"

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

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

d := goqu.Dialect("postgres")

db := d.DB(mDb)

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

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

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

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

Output:

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

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

// import _ "github.com/doug-martin/goqu/v7/adapters/sqlite3"

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

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

d := goqu.Dialect("sqlite3")

db := d.DB(mDb)

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

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

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

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

Output:

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

func (DialectWrapper) DB 

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

func (DialectWrapper) From 

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

type Ex 

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

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

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

// nolint:lll

Output:
Example (In) 
// using an Ex expression map
sql, _, _ := goqu.From("test").Where(goqu.Ex{
	"a": []string{"a", "b", "c"},
}).ToSQL()
fmt.Println(sql)

Output:

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

Output:

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

type ExOr 

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

// nolint:lll

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

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

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

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

Output:

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

type Expression 

type Expression = exp.Expression

type Logger 

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

type Op 

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

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

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

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

Output:

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

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

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

Output:

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

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

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

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

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

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

Output:

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

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

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

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

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

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

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

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

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

Output:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

ds = goqu.From("test").Where(goqu.Ex{
	"a": goqu.Op{"notILike": regexp.MustCompile("(a|b)")},
})
sql, args, _ = ds.ToSQL()
fmt.Println(sql, args)

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

Output:

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

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

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

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

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

Output:

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

type Record 

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

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

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

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

Output:

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

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

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

Output:

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

type SQLDatabase added in v7.3.0 

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

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

type SQLDialect 

type SQLDialect interface {
	ToSelectSQL(b sb.SQLBuilder, clauses exp.Clauses)
	ToUpdateSQL(b sb.SQLBuilder, clauses exp.Clauses, update interface{})
	ToInsertSQL(b sb.SQLBuilder, clauses exp.Clauses, ie exp.InsertExpression)
	ToDeleteSQL(b sb.SQLBuilder, clauses exp.Clauses)
	ToTruncateSQL(b sb.SQLBuilder, clauses exp.Clauses, options exp.TruncateOptions)
}

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

func GetDialect 

func GetDialect(name string) SQLDialect

type SQLDialectOptions 

type SQLDialectOptions struct {
	// Set to true if the dialect supports ORDER BY expressions in DELETE statements (DEFAULT=false)
	SupportsOrderByOnDelete bool
	// Set to true if the dialect supports ORDER BY expressions in UPDATE statements (DEFAULT=false)
	SupportsOrderByOnUpdate bool
	// Set to true if the dialect supports LIMIT expressions in DELETE statements (DEFAULT=false)
	SupportsLimitOnDelete bool
	// Set to true if the dialect supports LIMIT expressions in UPDATE statements (DEFAULT=false)
	SupportsLimitOnUpdate bool
	// Set to true if the dialect supports RETURN expressions (DEFAULT=true)
	SupportsReturn bool
	// Set to true if the dialect supports Conflict Target (DEFAULT=true)
	SupportsConflictTarget bool
	// Set to true if the dialect supports Conflict Target (DEFAULT=true)
	SupportsConflictUpdateWhere bool
	// Set to true if the dialect supports Insert Ignore syntax (DEFAULT=false)
	SupportsInsertIgnoreSyntax bool
	// Set to true if the dialect supports Common Table Expressions (DEFAULT=true)
	SupportsWithCTE bool
	// Set to true if the dialect supports recursive Common Table Expressions (DEFAULT=true)
	SupportsWithCTERecursive bool
	// Set to false if the dialect does not require expressions to be wrapped in parens (DEFAULT=true)
	WrapCompoundsInParens bool

	// The UPDATE fragment to use when generating sql. (DEFAULT=[]byte("UPDATE"))
	UpdateClause []byte
	// The INSERT fragment to use when generating sql. (DEFAULT=[]byte("INSERT INTO"))
	InsertClause []byte
	// The INSERT IGNORE INTO fragment to use when generating sql. (DEFAULT=[]byte("INSERT IGNORE INTO"))
	InsertIgnoreClause []byte
	// The SELECT fragment to use when generating sql. (DEFAULT=[]byte("SELECT"))
	SelectClause []byte
	// The DELETE fragment to use when generating sql. (DEFAULT=[]byte("DELETE"))
	DeleteClause []byte
	// The TRUNCATE fragment to use when generating sql. (DEFAULT=[]byte("TRUNCATE"))
	TruncateClause []byte
	// The WITH fragment to use when generating sql. (DEFAULT=[]byte("WITH "))
	WithFragment []byte
	// The RECURSIVE fragment to use when generating sql (after WITH). (DEFAULT=[]byte("RECURSIVE "))
	RecursiveFragment []byte
	// The CASCADE fragment to use when generating sql. (DEFAULT=[]byte(" CASCADE"))
	CascadeFragment []byte
	// The RESTRICT fragment to use when generating sql. (DEFAULT=[]byte(" RESTRICT"))
	RestrictFragment []byte
	// The SQL fragment to use when generating insert sql and using
	// DEFAULT VALUES (e.g. postgres="DEFAULT VALUES", mysql="", sqlite3=""). (DEFAULT=[]byte(" DEFAULT VALUES"))
	DefaultValuesFragment []byte
	// The SQL fragment to use when generating insert sql and listing columns using a VALUES clause
	// (DEFAULT=[]byte(" VALUES "))
	ValuesFragment []byte
	// The SQL fragment to use when generating truncate sql and using the IDENTITY clause
	// (DEFAULT=[]byte(" IDENTITY"))
	IdentityFragment []byte
	// The SQL fragment to use when generating update sql and using the SET clause (DEFAULT=[]byte(" SET "))
	SetFragment []byte
	// The SQL DISTINCT keyword (DEFAULT=[]byte(" DISTINCT "))
	DistinctFragment []byte
	// The SQL RETURNING clause (DEFAULT=[]byte(" RETURNING "))
	ReturningFragment []byte
	// The SQL FROM clause fragment (DEFAULT=[]byte(" FROM"))
	FromFragment []byte
	// The SQL USING join clause fragment (DEFAULT=[]byte(" USING "))
	UsingFragment []byte
	// The SQL ON join clause fragment (DEFAULT=[]byte(" ON "))
	OnFragment []byte
	// The SQL WHERE clause fragment (DEFAULT=[]byte(" WHERE "))
	WhereFragment []byte
	// The SQL GROUP BY clause fragment(DEFAULT=[]byte(" GROUP BY "))
	GroupByFragment []byte
	// The SQL HAVING clause fragment(DELiFAULT=[]byte(" HAVING "))
	HavingFragment []byte
	// The SQL ORDER BY clause fragment(DEFAULT=[]byte(" ORDER BY "))
	OrderByFragment []byte
	// The SQL LIMIT BY clause fragment(DEFAULT=[]byte(" LIMIT "))
	LimitFragment []byte
	// The SQL OFFSET BY clause fragment(DEFAULT=[]byte(" OFFSET "))
	OffsetFragment []byte
	// The SQL FOR UPDATE fragment(DEFAULT=[]byte(" FOR UPDATE "))
	ForUpdateFragment []byte
	// The SQL FOR NO KEY UPDATE fragment(DEFAULT=[]byte(" FOR NO KEY UPDATE "))
	ForNoKeyUpdateFragment []byte
	// The SQL FOR SHARE fragment(DEFAULT=[]byte(" FOR SHARE "))
	ForShareFragment []byte
	// The SQL FOR KEY SHARE fragment(DEFAULT=[]byte(" FOR KEY SHARE "))
	ForKeyShareFragment []byte
	// The SQL NOWAIT fragment(DEFAULT=[]byte("NOWAIT"))
	NowaitFragment []byte
	// The SQL SKIP LOCKED fragment(DEFAULT=[]byte("SKIP LOCKED"))
	SkipLockedFragment []byte
	// The SQL AS fragment when aliasing an Expression(DEFAULT=[]byte(" AS "))
	AsFragment []byte
	// The quote rune to use when quoting identifiers(DEFAULT='"')
	QuoteRune rune
	// The NULL literal to use when interpolating nulls values (DEFAULT=[]byte("NULL"))
	Null []byte
	// The TRUE literal to use when interpolating bool true values (DEFAULT=[]byte("TRUE"))
	True []byte
	// The FALSE literal to use when interpolating bool false values (DEFAULT=[]byte("FALSE"))
	False []byte
	// The ASC fragment when specifying column order (DEFAULT=[]byte(" ASC"))
	AscFragment []byte
	// The DESC fragment when specifying column order (DEFAULT=[]byte(" DESC"))
	DescFragment []byte
	// The NULLS FIRST fragment when specifying column order (DEFAULT=[]byte(" NULLS FIRST"))
	NullsFirstFragment []byte
	// The NULLS LAST fragment when specifying column order (DEFAULT=[]byte(" NULLS LAST"))
	NullsLastFragment []byte
	// The AND keyword used when joining ExpressionLists (DEFAULT=[]byte(" AND "))
	AndFragment []byte
	// The OR keyword used when joining ExpressionLists (DEFAULT=[]byte(" OR "))
	OrFragment []byte
	// The UNION keyword used when creating compound statements (DEFAULT=[]byte(" UNION "))
	UnionFragment []byte
	// The UNION ALL keyword used when creating compound statements (DEFAULT=[]byte(" UNION ALL "))
	UnionAllFragment []byte
	// The INTERSECT keyword used when creating compound statements (DEFAULT=[]byte(" INTERSECT "))
	IntersectFragment []byte
	// The INTERSECT ALL keyword used when creating compound statements (DEFAULT=[]byte(" INTERSECT ALL "))
	IntersectAllFragment []byte
	// The CAST keyword to use when casting a value (DEFAULT=[]byte("CAST"))
	CastFragment []byte
	// The quote rune to use when quoting string literals (DEFAULT='\”)
	StringQuote rune
	// The operator to use when setting values in an update statement (DEFAULT='=')
	SetOperatorRune rune
	// The placeholder rune to use when generating a non interpolated statement (DEFAULT='?')
	PlaceHolderRune rune
	// Empty string (DEFAULT="")
	EmptyString string
	// Comma rune (DEFAULT=',')
	CommaRune rune
	// Space rune (DEFAULT=' ')
	SpaceRune rune
	// Left paren rune (DEFAULT='(')
	LeftParenRune rune
	// Right paren rune (DEFAULT=')')
	RightParenRune rune
	// Star rune (DEFAULT='*')
	StarRune rune
	// Period rune (DEFAULT='.')
	PeriodRune rune
	// Set to true to include positional argument numbers when creating a prepared statement (Default=false)
	IncludePlaceholderNum bool
	// The time format to use when serializing time.Time (DEFAULT=time.RFC3339Nano)
	TimeFormat string
	// A map used to look up BooleanOperations and their SQL equivalents
	// (Default= map[exp.BooleanOperation][]byte{
	// 		exp.EqOp:             []byte("="),
	// 		exp.NeqOp:            []byte("!="),
	// 		exp.GtOp:             []byte(">"),
	// 		exp.GteOp:            []byte(">="),
	// 		exp.LtOp:             []byte("<"),
	// 		exp.LteOp:            []byte("<="),
	// 		exp.InOp:             []byte("IN"),
	// 		exp.NotInOp:          []byte("NOT IN"),
	// 		exp.IsOp:             []byte("IS"),
	// 		exp.IsNotOp:          []byte("IS NOT"),
	// 		exp.LikeOp:           []byte("LIKE"),
	// 		exp.NotLikeOp:        []byte("NOT LIKE"),
	// 		exp.ILikeOp:          []byte("ILIKE"),
	// 		exp.NotILikeOp:       []byte("NOT ILIKE"),
	// 		exp.RegexpLikeOp:     []byte("~"),
	// 		exp.RegexpNotLikeOp:  []byte("!~"),
	// 		exp.RegexpILikeOp:    []byte("~*"),
	// 		exp.RegexpNotILikeOp: []byte("!~*"),
	// })
	BooleanOperatorLookup map[exp.BooleanOperation][]byte
	// A map used to look up RangeOperations and their SQL equivalents
	// (Default=map[exp.RangeOperation][]byte{
	// 		exp.BetweenOp:    []byte("BETWEEN"),
	// 		exp.NotBetweenOp: []byte("NOT BETWEEN"),
	// 	})
	RangeOperatorLookup map[exp.RangeOperation][]byte
	// A map used to look up JoinTypes and their SQL equivalents
	// (Default= map[exp.JoinType][]byte{
	// 		exp.InnerJoinType:        []byte(" INNER JOIN "),
	// 		exp.FullOuterJoinType:    []byte(" FULL OUTER JOIN "),
	// 		exp.RightOuterJoinType:   []byte(" RIGHT OUTER JOIN "),
	// 		exp.LeftOuterJoinType:    []byte(" LEFT OUTER JOIN "),
	// 		exp.FullJoinType:         []byte(" FULL JOIN "),
	// 		exp.RightJoinType:        []byte(" RIGHT JOIN "),
	// 		exp.LeftJoinType:         []byte(" LEFT JOIN "),
	// 		exp.NaturalJoinType:      []byte(" NATURAL JOIN "),
	// 		exp.NaturalLeftJoinType:  []byte(" NATURAL LEFT JOIN "),
	// 		exp.NaturalRightJoinType: []byte(" NATURAL RIGHT JOIN "),
	// 		exp.NaturalFullJoinType:  []byte(" NATURAL FULL JOIN "),
	// 		exp.CrossJoinType:        []byte(" CROSS JOIN "),
	// 	})
	JoinTypeLookup map[exp.JoinType][]byte
	// Whether or not to use literal TRUE or FALSE for IS statements (e.g. IS TRUE or IS 0)
	UseLiteralIsBools bool
	// EscapedRunes is a map of a rune and the corresponding escape sequence in bytes. Used when escaping text
	// types.
	// (Default= map[rune][]byte{
	// 		'\”: []byte("”"),
	// 	})
	EscapedRunes map[rune][]byte

	// The SQL fragment to use for CONFLICT (Default=[]byte(" ON CONFLICT"))
	ConflictFragment []byte
	// The SQL fragment to use for CONFLICT DO NOTHING (Default=[]byte(" DO NOTHING"))
	ConflictDoNothingFragment []byte
	// The SQL fragment to use for CONFLICT DO UPDATE (Default=[]byte(" DO UPDATE SET"))
	ConflictDoUpdateFragment []byte

	// The order of SQL fragments when creating a SELECT statement
	// (Default=[]SQLFragmentType{
	// 		CommonTableSQLFragment,
	// 		SelectSQLFragment,
	// 		FromSQLFragment,
	// 		JoinSQLFragment,
	// 		WhereSQLFragment,
	// 		GroupBySQLFragment,
	// 		HavingSQLFragment,
	// 		CompoundsSQLFragment,
	// 		OrderSQLFragment,
	// 		LimitSQLFragment,
	// 		OffsetSQLFragment,
	// 		ForSQLFragment,
	// 	})
	SelectSQLOrder []SQLFragmentType

	// The order of SQL fragments when creating an UPDATE statement
	// (Default=[]SQLFragmentType{
	// 		CommonTableSQLFragment,
	// 		UpdateBeginSQLFragment,
	// 		SourcesSQLFragment,
	// 		UpdateSQLFragment,
	// 		WhereSQLFragment,
	// 		OrderSQLFragment,
	// 		LimitSQLFragment,
	// 		ReturningSQLFragment,
	// 	})
	UpdateSQLOrder []SQLFragmentType

	// The order of SQL fragments when creating an INSERT statement
	// (Default=[]SQLFragmentType{
	// 		CommonTableSQLFragment,
	// 		InsertBeingSQLFragment,
	// 		SourcesSQLFragment,
	// 		InsertSQLFragment,
	// 		ReturningSQLFragment,
	// 	})
	InsertSQLOrder []SQLFragmentType

	// The order of SQL fragments when creating a DELETE statement
	// (Default=[]SQLFragmentType{
	// 		CommonTableSQLFragment,
	// 		DeleteBeginSQLFragment,
	// 		FromSQLFragment,
	// 		WhereSQLFragment,
	// 		OrderSQLFragment,
	// 		LimitSQLFragment,
	// 		ReturningSQLFragment,
	// 	})
	DeleteSQLOrder []SQLFragmentType

	// The order of SQL fragments when creating a TRUNCATE statement
	// (Default=[]SQLFragmentType{
	// 		TruncateSQLFragment,
	// 	})
	TruncateSQLOrder []SQLFragmentType
}

func DefaultDialectOptions 

func DefaultDialectOptions() *SQLDialectOptions

type SQLFragmentType 

type SQLFragmentType int

func (SQLFragmentType) String 

func (sf SQLFragmentType) String() string

type SQLTx added in v7.3.1 

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

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

type TruncateOptions 

type TruncateOptions = exp.TruncateOptions

Options to use when generating a TRUNCATE statement

type TxDatabase 

type TxDatabase struct {
	Tx SQLTx
	// contains filtered or unexported fields
}

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

func NewTx added in v7.3.1 

func NewTx(dialect string, tx SQLTx) *TxDatabase

Creates a new TxDatabase

func (*TxDatabase) Commit 

func (td *TxDatabase) Commit() error

COMMIT the transaction

func (*TxDatabase) Dialect 

func (td *TxDatabase) Dialect() string

returns this databases dialect

func (*TxDatabase) Exec 

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

See Database#Exec

func (*TxDatabase) ExecContext 

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

See Database#ExecContext

func (*TxDatabase) From 

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

Creates a new Dataset for querying a Database.

func (*TxDatabase) Logger 

func (td *TxDatabase) Logger(logger Logger)

Sets the logger

func (*TxDatabase) Prepare 

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

See Database#Prepare

func (*TxDatabase) PrepareContext 

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

See Database#PrepareContext

func (*TxDatabase) Query 

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

See Database#Query

func (*TxDatabase) QueryContext 

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

See Database#QueryContext

func (*TxDatabase) QueryRow 

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

See Database#QueryRow

func (*TxDatabase) QueryRowContext 

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

See Database#QueryRowContext

func (*TxDatabase) Rollback 

func (td *TxDatabase) Rollback() error

ROLLBACK the transaction

func (*TxDatabase) ScanStruct 

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

See Database#ScanStruct

func (*TxDatabase) ScanStructContext 

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

See Database#ScanStructContext

func (*TxDatabase) ScanStructs 

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

See Database#ScanStructs

func (*TxDatabase) ScanStructsContext 

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

See Database#ScanStructsContext

func (*TxDatabase) ScanVal 

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

See Database#ScanVal

func (*TxDatabase) ScanValContext 

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

See Database#ScanValContext

func (*TxDatabase) ScanVals 

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

See Database#ScanVals

func (*TxDatabase) ScanValsContext 

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

See Database#ScanValsContext

func (*TxDatabase) Trace 

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

func (*TxDatabase) Wrap 

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

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

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

Source Files

View all Source files

Directories

Path Synopsis
dialect
mysql
postgres
sqlite3
internal
errors
sb
tag
util

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.