goqu

package module
v5.0.0+incompatible Latest Latest
Warning

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

 Go to latest Published: Sep 10, 2018 License: MIT Imports: 13 Imported by: 0

README 

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

GitHub tag Build Status GoDoc codecov

goqu is an expressive SQL builder

This library was built with the following goals:

  • Make the generation of SQL easy and enjoyable
  • Provide a DSL that accounts for the common SQL expressions, NOT every nuance for each database.
  • Allow users to use SQL when desired
  • Provide a simple query API for scanning rows
  • Allow the user to use the native sql.Db methods when desired

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 adapters 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:

Installation

go get -u gopkg.in/doug-martin/goqu.v5

Basics

In order to start using goqu with your database you need to load an adapter. We have included some adapters by default.

  1. Postgres - import "gopkg.in/doug-martin/goqu.v5/adapters/postgres"
  2. MySQL - import "gopkg.in/doug-martin/goqu.v5/adapters/mysql"
  3. SQLite3 - import "gopkg.in/doug-martin/goqu.v5/adapters/sqlite3"

Adapters in goqu work the same way as a driver with the database in that they register themselves with goqu once loaded.

import (
  "database/sql"
  "gopkg.in/doug-martin/goqu.v5"
  _ "gopkg.in/doug-martin/goqu.v5/adapters/postgres"
  _ "github.com/lib/pq"
)

Notice that we imported the adapter and driver for side effect only.

Once you have your adapter and driver loaded you can create a goqu.Database instance

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

Now that you have your goqu.Database you can build your SQL and it will be formatted appropriately for the provided dialect.

//interpolated sql
sql, _ := db.From("user").Where(goqu.Ex{
    "id": 10,
}).ToSql()
fmt.Println(sql)

//prepared sql
sql, args, _ := db.From("user").
    Prepared(true).
    Where(goqu.Ex{
        "id": 10,
    }).
    ToSql()
fmt.Println(sql)

Output

SELECT * FROM "user" WHERE "id" = 10
SELECT * FROM "user" WHERE "id" = $1

Expressions

goqu provides an idiomatic DSL for generating SQL however the Dataset only provides the different clause methods (e.g. Where, From, Select), most of these clause methods accept Expressions(with a few exceptions) 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

  • I() - An Identifier represents a schema, table, or column or any combination. You can use this when your expression cannot be expressed via the Ex map (e.g. Cast).
goqu.I("my_schema.table.col")
goqu.I("table.col")
goqu.I("col")

If you look at the IdentiferExpression docs it implements many of your common sql operations that you would perform.

goqu.I("col").Eq(10)
goqu.I("col").In([]int64{1,2,3,4})
goqu.I("col").Like(regexp.MustCompile("^(a|b)")
goqu.I("col").IsNull()

Please see the exmaples for I() to see more in depth examples

  • 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
goqu.L(`"col"::TEXT = ""other_col"::text`)

You can also use placeholders in your literal. When using the LiteralExpressions placeholders are normalized to the ? character and will be transformed to the correct placeholder for your adapter (e.g. ? mysql, $1 postgres, ? sqlite3)

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

Putting it together

sql, _, _ := db.From("test").Where(
   goqu.I("col").Eq(10),
   goqu.L(`"json"::TEXT = "other_json"::TEXT`),
).ToSql()
fmt.Println(sql)

SELECT * FROM "test" WHERE (("col" = 10) AND "json"::TEXT = "other_json"::TEXT)

Both the Identifier and Literal expressions will be ANDed together by default. You may however want to have your expressions ORed together you can use the Or() function to create an ExpressionList

sql, _, _ := db.From("test").Where(
   goqu.Or(
      goqu.I("col").Eq(10),
      goqu.L(`"col"::TEXT = "other_col"::TEXT`),
   ),
).ToSql()
fmt.Println(sql)

SELECT * FROM "test" WHERE (("col" = 10) OR "col"::TEXT = "other_col"::TEXT)

sql, _, _ := db.From("test").Where(
   Or(
      goqu.I("col").Eq(10),
      goqu.L(`"col"::TEXT = "other_col"::TEXT`),
   ),
).ToSql()
fmt.Println(sql)

SELECT * FROM "test" WHERE (("col" = 10) OR "col"::TEXT = "other_col"::TEXT)

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

sql, _, _ := db.From("test").Where(
   goqu.Or(
      goqu.I("a").Gt(10),
      goqu.And(
         goqu.I("b").Eq(100),
         goqu.I("c").Neq("test"),
      ),
   ),
).ToSql()
fmt.Println(sql)

Output:

SELECT * FROM "test" WHERE (("a" > 10) OR (("b" = 100) AND ("c" != 'test')))

You can also use Or with the map syntax

sql, _, _ := db.From("test").Where(
	goqu.Or(
        //Ex will be anded together
		goqu.Ex{
			"col1": nil,
			"col2": true,
		},
		goqu.Ex{
			"col3": nil,
			"col4": false,
		},
		goqu.L(`"col"::TEXT = "other_col"::TEXT`),
	),
).ToSql()
fmt.Println(sql)

Output:

SELECT * FROM "test" WHERE ((("col1" IS NULL) AND ("col2" IS TRUE)) OR (("col3" IS NULL) AND ("col4" IS FALSE)) OR "col"::TEXT = "other_col"::TEXT)

Complex Example

Using the Ex map syntax

sql, _, _ := 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.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)).
	ToSql()
fmt.Println(sql)

Using the Expression syntax

sql, _, _ := 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)).
	ToSql()
fmt.Println(sql)

Both examples generate the following SQL

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

Querying

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

Dataset

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{
    fmt.Println(err.Error())
    return
}
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{
    fmt.Println(err.Error())
    return
}
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)
}
  • 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 CrudExec 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.I("id")).Insert(users).ScanVals(&ids); err != nil{
    fmt.Println(err.Error())
    return
}
  • Update - Creates an UPDATE statement and returns anCrudExec to execute the statement
update := db.From("user").
    Where(goqu.I("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 CrudExec 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.I("status").Eq("paid")).
    Returning(goqu.I("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 interolated you can use the Prepared method to prevent this.

Note For the examples all placeholders are ? this will be adapter 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"})
  if _, err = update.Exec(); err != nil{
      return err
  }
  return nil
})
//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

Adapters

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

Between most dialects there is a large portion of shared syntax, for this reason we have a DefaultAdapter that can be used as a base for any new Dialect specific adapter. In fact for most use cases you will not have to override any methods but instead just override the default values as documented for DefaultAdapter.

Literal

The DefaultAdapter has a Literal function which should be used to serialize all sub expressions or values. This method prevents you from having to re-implement each adapter method while having your adapter methods called correctly.

How does it work?

The Literal method delegates back to the Dataset.Literal method which then calls the appropriate method on the adapter acting as a trampoline, between the DefaultAdapter and your Adapter.

For example if your adapter overrode the DefaultAdapter.QuoteIdentifier, method which is used by most methods in the DefaultAdapter, we need to ensure that your Adapters QuoteIdentifier method is called instead of the default implementation.

Because the Dataset has a pointer to your Adapter it will call the correct method, so instead of calling DefaultAdapter.QuoteIdentifier internally we delegate back to the Dataset by calling the Dataset.Literal which will the call your Adapters method.

Dataset.Literal -> Adapter.ExpressionListSql -> Adapter.Literal -> Dataset.Literal -> YourAdapter.QuoteIdentifier

It is important to maintain this pattern when writing your own Adapter.

Registering

When creating your adapters you must register your adapter with RegisterAdapter. This method requires 2 arguments.

  1. dialect - The dialect for your adapter.
  2. datasetAdapterFactory - This is a factory function that will return a new goqu.Adapter used to create the dialect specific SQL.

For example the code for the postgres adapter is fairly short.

package postgres

import (
    "gopkg.in/doug-martin/goqu.v5"
)

//postgres requires a $ placeholder for prepared statements
const placeholder_rune = '$'

func newDatasetAdapter(ds *goqu.Dataset) goqu.Adapter {
    ret := goqu.NewDefaultAdapter(ds).(*goqu.DefaultAdapter)

    //override the settings required
    ret.PlaceHolderRune = placeholder_rune
    //postgres requires a paceholder number (e.g. $1)
    ret.IncludePlaceholderNum = true
    return ret
}

func init() {
    //register our adapter with goqu
    goqu.RegisterAdapter("postgres", newDatasetAdapter)
}

If you are looking to write your own adapter take a look at the postgresm, mysql or sqlite3 adapter located at https://github.com/doug-martin/goqu/tree/master/adapters.

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 (
	INNER_JOIN JoinType = iota
	FULL_OUTER_JOIN
	RIGHT_OUTER_JOIN
	LEFT_OUTER_JOIN
	FULL_JOIN
	RIGHT_JOIN
	LEFT_JOIN
	NATURAL_JOIN
	NATURAL_LEFT_JOIN
	NATURAL_RIGHT_JOIN
	NATURAL_FULL_JOIN
	CROSS_JOIN

	USING_COND JoinCondition = iota
	ON_COND
)
View Source 
const (
	//Default null sort type with no null sort order
	NO_NULLS null_sort_type = iota
	//NULLS FIRST
	NULLS_FIRST
	//NULLS LAST
	NULLS_LAST

	//ASC
	SORT_ASC sort_direction = iota
	//DESC
	SORT_DESC
)
View Source 
const (
	UNION compoundType = iota
	UNION_ALL
	INTERSECT
	INTERSECT_ALL
)

Variables

This section is empty.

Functions

func And 

func And(expressions ...Expression) 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 
db := goqu.New("default", driver)
//by default Where assumes an And
sql, _, _ := db.From("test").Where(goqu.Ex{
	"a": goqu.Op{"gt": 10},
	"b": goqu.Op{"lt": 5},
}).ToSql()
fmt.Println(sql)

sql, _, _ = db.From("test").Where(
	goqu.I("a").Gt(10),
	goqu.I("b").Lt(5),
).ToSql()
fmt.Println(sql)

Output:

SELECT * FROM "test" WHERE (("a" > 10) AND ("b" < 5))
SELECT * FROM "test" WHERE (("a" > 10) AND ("b" < 5))
Example (WithOr) 
db := goqu.New("default", driver)
sql, _, _ := db.From("test").Where(
	goqu.I("a").Gt(10),
	goqu.Or(
		goqu.I("b").Lt(5),
		goqu.I("c").In([]string{"hello", "world"}),
	),
).ToSql()
fmt.Println(sql)

Output:

SELECT * FROM "test" WHERE (("a" > 10) AND (("b" < 5) OR ("c" IN ('hello', 'world'))))

func HasAdapter 

func HasAdapter(dialect string) bool

Returns true if the dialect has an adapter registered 

dialect: The dialect to test

func NewGoquError 

func NewGoquError(message string, args ...interface{}) error

func On 

func On(expressions ...Expression) joinExpression

Creates a new ON clause to be used within a join 

ds.Join(I("my_table"), On(I("my_table.fkey").Eq(I("other_table.id")))
Example 
db := goqu.New("default", driver)
sql, _, _ := db.From("test").Join(
	goqu.I("my_table"),
	goqu.On(goqu.Ex{"my_table.fkey": goqu.I("test.id")}),
).ToSql()
fmt.Println(sql)

sql, _, _ = db.From("test").Join(
	goqu.I("my_table"),
	goqu.On(goqu.I("my_table.fkey").Eq(goqu.I("test.id"))),
).ToSql()
fmt.Println(sql)

Output:

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

func Or 

func Or(expressions ...Expression) 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 
db := goqu.New("default", driver)
sql, _, _ := db.From("test").Where(goqu.Ex{
	"a": goqu.Op{"gt": 10, "lt": 5},
}).ToSql()
fmt.Println(sql)

sql, _, _ = db.From("test").Where(
	goqu.Or(
		goqu.I("a").Gt(10),
		goqu.I("a").Lt(5),
	),
).ToSql()
fmt.Println(sql)

Output:

SELECT * FROM "test" WHERE (("a" > 10) OR ("a" < 5))
SELECT * FROM "test" WHERE (("a" > 10) OR ("a" < 5))
Example (WithAnd) 
db := goqu.New("default", driver)
sql, _, _ := db.From("items").Where(
	goqu.Or(
		goqu.I("a").Gt(10),
		goqu.Ex{
			"b": 100,
			"c": goqu.Op{"neq": "test"},
		},
	),
).ToSql()
fmt.Println(sql)
sql, _, _ = db.From("items").Where(
	goqu.Or(
		goqu.I("a").Gt(10),
		goqu.And(
			goqu.I("b").Eq(100),
			goqu.I("c").Neq("test"),
		),
	),
).ToSql()
fmt.Println(sql)

Output:

SELECT * FROM "items" WHERE (("a" > 10) OR (("b" = 100) AND ("c" != 'test')))
SELECT * FROM "items" WHERE (("a" > 10) OR (("b" = 100) AND ("c" != 'test')))

func RegisterAdapter 

func RegisterAdapter(dialect string, factory func(ds *Dataset) Adapter)

Registers an adapter. 

dialect: The dialect this adapter is for
factory: a function that can be called to create a new Adapter for the dialect.

func Using 

func Using(expressions ...interface{}) joinExpression

Creates a new USING clause to be used within a join

Example 
db := goqu.New("default", driver)
sql, _, _ := db.From("test").Join(goqu.I("my_table"), goqu.Using(goqu.I("common_column"))).ToSql()
fmt.Println(sql)

Output:

SELECT * FROM "test" INNER JOIN "my_table" USING ("common_column")

Types

type Adapter 

type Adapter interface {
	//Returns true if the dialect supports ORDER BY expressions in DELETE statements
	SupportsOrderByOnDelete() bool
	//Returns true if the dialect supports ORDER BY expressions in UPDATE statements
	SupportsOrderByOnUpdate() bool
	//Returns true if the dialect supports LIMIT expressions in DELETE statements
	SupportsLimitOnDelete() bool
	//Returns true if the dialect supports LIMIT expressions in UPDATE statements
	SupportsLimitOnUpdate() bool
	//Returns true if the dialect supports RETURN expressions
	SupportsReturn() bool
	//Generates the sql for placeholders. Only invoked when not interpolating values.
	//
	//buf: The current SqlBuilder to write the sql to
	//i: the value that should be added the the sqlbuilders args.
	PlaceHolderSql(buf *SqlBuilder, i interface{}) error
	//Generates the correct beginning sql for an UPDATE statement
	//
	//buf: The current SqlBuilder to write the sql to
	UpdateBeginSql(buf *SqlBuilder) error
	//Generates the correct beginning sql for an INSERT statement
	//
	//buf: The current SqlBuilder to write the sql to
	InsertBeginSql(buf *SqlBuilder, o ConflictExpression) error
	//Generates the correct beginning sql for a DELETE statement
	//
	//buf: The current SqlBuilder to write the sql to
	DeleteBeginSql(buf *SqlBuilder) error
	//Generates the correct beginning sql for a TRUNCATE statement
	//
	//buf: The current SqlBuilder to write the sql to
	TruncateSql(buf *SqlBuilder, cols ColumnList, opts TruncateOptions) error
	//Generates the correct sql for inserting default values in SQL
	//
	//buf: The current SqlBuilder to write the sql to
	DefaultValuesSql(buf *SqlBuilder) error
	//Generates the sql for update expressions
	//
	//buf: The current SqlBuilder to write the sql to
	UpdateExpressionsSql(buf *SqlBuilder, updates ...UpdateExpression) error
	//Generates the sql for the SELECT and ColumnList for a select statement
	//
	//buf: The current SqlBuilder to write the sql to
	SelectSql(buf *SqlBuilder, cols ColumnList) error
	//Generates the sql for the SELECT DISTINCT and ColumnList for a select statement
	//
	//buf: The current SqlBuilder to write the sql to
	SelectDistinctSql(buf *SqlBuilder, cols ColumnList) error
	//Generates the sql for a RETURNING clause
	//
	//buf: The current SqlBuilder to write the sql to
	ReturningSql(buf *SqlBuilder, cols ColumnList) error
	//Generates the sql for a FROM clause
	//
	//buf: The current SqlBuilder to write the sql to
	FromSql(buf *SqlBuilder, from ColumnList) error
	//Generates the sql for a list of columns.
	//
	//buf: The current SqlBuilder to write the sql to
	SourcesSql(buf *SqlBuilder, from ColumnList) error
	//Generates the sql for JoiningClauses clauses
	//
	//buf: The current SqlBuilder to write the sql to
	JoinSql(buf *SqlBuilder, joins JoiningClauses) error
	//Generates the sql for WHERE clause
	//
	//buf: The current SqlBuilder to write the sql to
	WhereSql(buf *SqlBuilder, where ExpressionList) error
	//Generates the sql for GROUP BY clause
	//
	//buf: The current SqlBuilder to write the sql to
	GroupBySql(buf *SqlBuilder, groupBy ColumnList) error
	//Generates the sql for HAVING clause
	//
	//buf: The current SqlBuilder to write the sql to
	HavingSql(buf *SqlBuilder, having ExpressionList) error
	//Generates the sql for COMPOUND expressions, such as UNION, and INTERSECT
	//
	//buf: The current SqlBuilder to write the sql to
	CompoundsSql(buf *SqlBuilder, compounds []CompoundExpression) error
	//Generates the sql for the WITH clauses for common table expressions (CTE)
	//
	//buf: The current SqlBuilder to write the sql to
	CommonTablesSql(buf *SqlBuilder, ctes []CommonTableExpression) error
	//Generates the sql for ORDER BY clause
	//
	//buf: The current SqlBuilder to write the sql to
	OrderSql(buf *SqlBuilder, order ColumnList) error
	//Generates the sql for LIMIT clause
	//
	//buf: The current SqlBuilder to write the sql to
	LimitSql(buf *SqlBuilder, limit interface{}) error
	//Generates the sql for OFFSET clause
	//
	//buf: The current SqlBuilder to write the sql to
	OffsetSql(buf *SqlBuilder, offset uint) error
	//Generates the sql for another Dataset being used as a sub select.
	//
	//buf: The current SqlBuilder to write the sql to
	DatasetSql(buf *SqlBuilder, builder Dataset) error
	//Correctly quotes an Identifier for use in SQL.
	//
	//buf: The current SqlBuilder to write the sql to
	QuoteIdentifier(buf *SqlBuilder, ident IdentifierExpression) error
	//Generates SQL value for nil
	//
	//buf: The current SqlBuilder to write the sql to
	LiteralNil(buf *SqlBuilder) error
	//Generates SQL value for a bool (e.g. TRUE, FALSE, 1, 0)
	//
	//buf: The current SqlBuilder to write the sql to
	LiteralBool(buf *SqlBuilder, b bool) error
	//Generates SQL value for a time.Time
	//
	//buf: The current SqlBuilder to write the sql to
	LiteralTime(buf *SqlBuilder, t time.Time) error
	//Generates SQL value for float64
	//
	//buf: The current SqlBuilder to write the sql to
	LiteralFloat(buf *SqlBuilder, f float64) error
	//Generates SQL value for an int64
	//
	//buf: The current SqlBuilder to write the sql to
	LiteralInt(buf *SqlBuilder, i int64) error
	//Generates SQL value for a string
	//
	//buf: The current SqlBuilder to write the sql to
	LiteralString(buf *SqlBuilder, s string) error
	//Generates SQL value for a Slice of Bytes
	//
	//buf: The current SqlBuilder to write the sql to
	LiteralBytes(buf *SqlBuilder, bs []byte) error
	//Generates SQL value for a Slice
	//
	//buf: The current SqlBuilder to write the sql to
	SliceValueSql(buf *SqlBuilder, slice reflect.Value) error
	//Generates SQL value for an AliasedExpression
	//
	//buf: The current SqlBuilder to write the sql to
	AliasedExpressionSql(buf *SqlBuilder, aliased AliasedExpression) error
	//Generates SQL value for a BooleanExpression
	//
	//buf: The current SqlBuilder to write the sql to
	BooleanExpressionSql(buf *SqlBuilder, operator BooleanExpression) error
	//Generates SQL value for a RangeExpression
	//
	//buf: The current SqlBuilder to write the sql to
	RangeExpressionSql(buf *SqlBuilder, operator RangeExpression) error
	//Generates SQL value for an OrderedExpression
	//
	//buf: The current SqlBuilder to write the sql to
	OrderedExpressionSql(buf *SqlBuilder, order OrderedExpression) error
	//Generates SQL value for an ExpressionList
	//
	//buf: The current SqlBuilder to write the sql to
	ExpressionListSql(buf *SqlBuilder, expressionList ExpressionList) error
	//Generates SQL value for a SqlFunction
	//
	//buf: The current SqlBuilder to write the sql to
	SqlFunctionExpressionSql(buf *SqlBuilder, sqlFunc SqlFunctionExpression) error
	//Generates SQL value for a CastExpression
	//
	//buf: The current SqlBuilder to write the sql to
	CastExpressionSql(buf *SqlBuilder, casted CastExpression) error
	//Generates SQL value for a CompoundExpression
	//
	//buf: The current SqlBuilder to write the sql to
	CompoundExpressionSql(buf *SqlBuilder, compound CompoundExpression) error
	//Generates SQL value for a CommonTableExpression
	//
	//buf: The current SqlBuilder to write the sql to
	CommonTableExpressionSql(buf *SqlBuilder, commonTable CommonTableExpression) error
	//Generates SQL value for a ColumnList
	//
	//buf: The current SqlBuilder to write the sql to
	ColumnListSql(buf *SqlBuilder, columnList ColumnList) error
	//Generates SQL value for an UpdateExpression
	//
	//buf: The current SqlBuilder to write the sql to
	UpdateExpressionSql(buf *SqlBuilder, update UpdateExpression) error
	Literal(buf *SqlBuilder, i interface{}) error
	//Generates SQL value for a LiteralExpression
	//
	//buf: The current SqlBuilder to write the sql to
	LiteralExpressionSql(buf *SqlBuilder, literal LiteralExpression) error
	//Generates SQL value for an Ex Expression map
	//
	//buf: The current SqlBuilder to write the sql to
	ExpressionMapSql(buf *SqlBuilder, ex Ex) error
	//Generates SQL value for an ExOr Expression map
	//
	//buf: The current SqlBuilder to write the sql to
	ExpressionOrMapSql(buf *SqlBuilder, ex ExOr) error
	//Generates SQL value for the columns in an INSERT statement
	//
	//buf: The current SqlBuilder to write the sql to
	InsertColumnsSql(buf *SqlBuilder, cols ColumnList) error
	//Generates SQL value for the values in an INSERT statement
	//
	//buf: The current SqlBuilder to write the sql to
	InsertValuesSql(buf *SqlBuilder, values [][]interface{}) error
	//Returns true if the dialect supports INSERT IGNORE INTO syntax
	SupportsInsertIgnoreSyntax() bool
	//Returns true if the dialect supports ON CONFLICT (key) expressions
	SupportsConflictTarget() bool
	//Generates SQL value for the ON CONFLICT clause of an INSERT statement
	//
	//buf: The current SqlBuilder to write the sql to
	OnConflictSql(buf *SqlBuilder, o ConflictExpression) error
	//Returns true if the dialect supports a WHERE clause on upsert
	SupportConflictUpdateWhere() bool
	//Returns true if the dialect supports WITH common table expressions
	SupportsWithCTE() bool
	//Returns true if the dialect supports WITH RECURSIVE common table expressions
	SupportsWithRecursiveCTE() bool
}

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 NewAdapter 

func NewAdapter(dialect string, dataset *Dataset) Adapter

Creates the appropriate adapter for the given dialect. 

dialect: the dialect to create an adapter for
dataset: The dataset to be used by the adapter

func NewDefaultAdapter 

func NewDefaultAdapter(ds *Dataset) Adapter

type AliasMethods 

type AliasMethods interface {
	//Returns an AliasedExpression
	//    I("col").As("other_col") //"col" AS "other_col"
	//    I("col").As(I("other_col")) //"col" AS "other_col"
	As(interface{}) AliasedExpression
}

Interface that an expression should implement if it can be aliased.

Example 
db := goqu.New("default", driver)
sql, _, _ := db.From("test").Select(goqu.I("a").As("as_a")).ToSql()
fmt.Println(sql)

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

sql, _, _ = db.From("test").Select(goqu.L("sum(amount)").As("total_amount")).ToSql()
fmt.Println(sql)

sql, _, _ = db.From("test").Select(goqu.I("a").As(goqu.I("as_a"))).ToSql()
fmt.Println(sql)

Output:

SELECT "a" AS "as_a" FROM "test"
SELECT COUNT(*) AS "count" FROM "test"
SELECT sum(amount) AS "total_amount" FROM "test"
SELECT "a" AS "as_a" FROM "test"

type AliasedExpression 

type AliasedExpression interface {
	Expression
	//Returns the Epxression being aliased
	Aliased() Expression
	//Returns the alias value as an identiier expression
	GetAs() IdentifierExpression
}

Expression for Aliased expressions 

I("a").As("b") -> "a" AS "b"
SUM("a").As(I("a_sum")) -> SUM("a") AS "a_sum"

type BooleanExpression 

type BooleanExpression interface {
	Expression
	//Returns the operator for the expression
	Op() BooleanOperation
	//The left hand side of the expression (e.g. I("a")
	Lhs() Expression
	//The right hand side of the expression could be a primitive value, dataset, or expression
	Rhs() interface{}
}

type BooleanMethods 

type BooleanMethods interface {
	//Creates an Boolean expression IS clauses
	//   ds.Where(I("a").Is(nil)) //("a" IS NULL)
	//   ds.Where(I("a").Is(true)) //("a" IS TRUE)
	//   ds.Where(I("a").Is(false)) //("a" IS FALSE)
	Is(interface{}) BooleanExpression
	//Creates an Boolean expression IS NOT clauses
	//   ds.Where(I("a").IsNot(nil)) //("a" IS NOT NULL)
	//   ds.Where(I("a").IsNot(true)) //("a" IS NOT TRUE)
	//   ds.Where(I("a").IsNot(false)) //("a" IS NOT FALSE)
	IsNot(interface{}) BooleanExpression
	//Shortcut for Is(nil)
	IsNull() BooleanExpression
	//Shortcut for IsNot(nil)
	IsNotNull() BooleanExpression
	//Shortcut for Is(true)
	IsTrue() BooleanExpression
	//Shortcut for IsNot(true)
	IsNotTrue() BooleanExpression
	//Shortcut for Is(false)
	IsFalse() BooleanExpression
	//Shortcut for IsNot(false)
	IsNotFalse() BooleanExpression
}

Interface that an expression should implement if it can be used in simple boolean operations (e.g IS, IS NOT).

Example 
db := goqu.New("default", driver)
sql, _, _ := db.From("test").Where(goqu.I("a").Is(nil)).ToSql()
fmt.Println(sql)

sql, _, _ = db.From("test").Where(goqu.I("a").Is(true)).ToSql()
fmt.Println(sql)

sql, _, _ = db.From("test").Where(goqu.I("a").Is(false)).ToSql()
fmt.Println(sql)

sql, _, _ = db.From("test").Where(goqu.I("a").IsNot(nil)).ToSql()
fmt.Println(sql)

sql, _, _ = db.From("test").Where(goqu.I("a").IsNot(true)).ToSql()
fmt.Println(sql)

sql, _, _ = db.From("test").Where(goqu.I("a").IsNull(), goqu.I("b").IsNull()).ToSql()
fmt.Println(sql)

sql, _, _ = db.From("test").Where(goqu.I("a").IsTrue(), goqu.I("b").IsNotTrue()).ToSql()
fmt.Println(sql)

sql, _, _ = db.From("test").Where(goqu.I("a").IsFalse(), goqu.I("b").IsNotFalse()).ToSql()
fmt.Println(sql)

//with an ex expression map
sql, _, _ = db.From("test").Where(goqu.Ex{
	"a": true,
	"b": false,
	"c": nil,
	"d": goqu.Op{"isNot": true},
	"e": goqu.Op{"isNot": false},
	"f": goqu.Op{"isNot": nil},
}).ToSql()
fmt.Println(sql)

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 NOT NULL)
SELECT * FROM "test" WHERE ("a" IS NOT TRUE)
SELECT * FROM "test" WHERE (("a" IS NULL) AND ("b" IS NULL))
SELECT * FROM "test" WHERE (("a" IS TRUE) AND ("b" IS NOT TRUE))
SELECT * FROM "test" WHERE (("a" IS FALSE) AND ("b" IS NOT FALSE))
SELECT * FROM "test" WHERE (("a" IS TRUE) AND ("b" IS FALSE) AND ("c" IS NULL) AND ("d" IS NOT TRUE) AND ("e" IS NOT FALSE) AND ("f" IS NOT NULL))

type BooleanOperation 

type BooleanOperation int
const (
	//=
	EQ_OP BooleanOperation = iota
	//!= or <>
	NEQ_OP
	//IS
	IS_OP
	//IS NOT
	IS_NOT_OP
	//>
	GT_OP
	//>=
	GTE_OP
	//<
	LT_OP
	//<=
	LTE_OP
	//IN
	IN_OP
	//NOT IN
	NOT_IN_OP
	//LIKE, LIKE BINARY...
	LIKE_OP
	//NOT LIKE, NOT LIKE BINARY...
	NOT_LIKE_OP
	//ILIKE, LIKE
	I_LIKE_OP
	//NOT ILIKE, NOT LIKE
	NOT_I_LIKE_OP
	//~, REGEXP BINARY
	REGEXP_LIKE_OP
	//!~, NOT REGEXP BINARY
	REGEXP_NOT_LIKE_OP
	//~*, REGEXP
	REGEXP_I_LIKE_OP
	//!~*, NOT REGEXP
	REGEXP_NOT_I_LIKE_OP
)

type CastExpression 

type CastExpression interface {
	Expression
	AliasMethods
	ComparisonMethods
	InMethods
	StringMethods
	BooleanMethods
	OrderedMethods
	DistinctMethods
	RangeMethods
	//The exression being casted
	Casted() Expression
	//The the SQL type to cast the expression to
	Type() LiteralExpression
}

An Expression that represents another Expression casted to a SQL type

func Cast 

func Cast(e Expression, t string) CastExpression

Creates a new Casted expression 

Cast(I("a"), "NUMERIC") -> CAST("a" AS NUMERIC)
Example 
db := goqu.New("default", driver)
sql, _, _ := db.From("test").Where(goqu.I("json1").Cast("TEXT").Neq(goqu.I("json2").Cast("TEXT"))).ToSql()
fmt.Println(sql)

Output:

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

type CastMethods 

type CastMethods interface {
	//Casts an expression to the specified type
	//   I("a").Cast("numeric")//CAST("a" AS numeric)
	Cast(val string) CastExpression
}

Interface that an expression should implement if it can be casted to another SQL type .

Example 
db := goqu.New("default", driver)
sql, _, _ := db.From("test").Where(goqu.I("json1").Cast("TEXT").Neq(goqu.I("json2").Cast("TEXT"))).ToSql()
fmt.Println(sql)

Output:

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

type ColumnList 

type ColumnList interface {
	Expression
	//Returns the list of columns
	Columns() []Expression
	//Returns a new ColumnList with the columns appended.
	Append(...Expression) ColumnList
}

A list of columns. Typically used internally by Select, Order, From

type CommonTableExpression 

type CommonTableExpression interface {
	Expression
	IsRecursive() bool
	//Returns the alias name for the extracted expression
	Name() LiteralExpression
	//Returns the Expression being extracted
	SubQuery() SqlExpression
}

func With 

func With(recursive bool, name string, subQuery SqlExpression) CommonTableExpression

Creates a new WITH common table expression for a SqlExpression, typically Datasets'. This function is used internally by Dataset when a CTE is added to another Dataset

type ComparisonMethods 

type ComparisonMethods interface {
	//Creates a Boolean expression comparing equality
	//    I("col").Eq(1) //("col" = 1)
	Eq(interface{}) BooleanExpression
	//Creates a Boolean expression comparing in-equality
	//    I("col").Neq(1) //("col" != 1)
	Neq(interface{}) BooleanExpression
	//Creates a Boolean expression for greater than comparisons
	//    I("col").Gt(1) //("col" > 1)
	Gt(interface{}) BooleanExpression
	//Creates a Boolean expression for greater than or equal to than comparisons
	//    I("col").Gte(1) //("col" >= 1)
	Gte(interface{}) BooleanExpression
	//Creates a Boolean expression for less than comparisons
	//    I("col").Lt(1) //("col" < 1)
	Lt(interface{}) BooleanExpression
	//Creates a Boolean expression for less than or equal to comparisons
	//    I("col").Lte(1) //("col" <= 1)
	Lte(interface{}) BooleanExpression
}

Interface that an expression should implement if it can be compared with other values.

Example 
db := goqu.New("default", driver)
//used from an identifier
sql, _, _ := db.From("test").Where(goqu.I("a").Eq(10)).ToSql()
fmt.Println(sql)

sql, _, _ = db.From("test").Where(goqu.I("a").Neq(10)).ToSql()
fmt.Println(sql)

sql, _, _ = db.From("test").Where(goqu.I("a").Gt(10)).ToSql()
fmt.Println(sql)

sql, _, _ = db.From("test").Where(goqu.I("a").Gte(10)).ToSql()
fmt.Println(sql)

sql, _, _ = db.From("test").Where(goqu.I("a").Lt(10)).ToSql()
fmt.Println(sql)

sql, _, _ = db.From("test").Where(goqu.I("a").Lte(10)).ToSql()
fmt.Println(sql)
//used from a literal expression
sql, _, _ = db.From("test").Where(goqu.L("(a + b)").Eq(10)).ToSql()
fmt.Println(sql)

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

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

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

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

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

//used with Ex expression map
sql, _, _ = db.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},
}).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)
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)
SELECT * FROM "test" WHERE (("a" = 10) AND ("b" != 10) AND ("c" >= 10) AND ("d" < 10) AND ("e" <= 10))

type CompoundExpression 

type CompoundExpression interface {
	Expression
	Type() compoundType
	Rhs() SqlExpression
}

func Intersect 

func Intersect(rhs SqlExpression) CompoundExpression

Creates a new INTERSECT compound expression between SqlExpression, typically Datasets'. This function is used internally by Dataset when compounded with another Dataset

func IntersectAll 

func IntersectAll(rhs SqlExpression) CompoundExpression

Creates a new INTERSECT ALL compound expression between SqlExpression, typically Datasets'. This function is used internally by Dataset when compounded with another Dataset

func Union 

func Union(rhs SqlExpression) CompoundExpression

Creates a new UNION compound expression between SqlExpression, typically Datasets'. This function is used internally by Dataset when compounded with another Dataset

func UnionAll 

func UnionAll(rhs SqlExpression) CompoundExpression

Creates a new UNION ALL compound expression between SqlExpression, typically Datasets'. This function is used internally by Dataset when compounded with another Dataset

type Conflict 

type Conflict struct{}

func DoNothing 

func DoNothing() *Conflict

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

InsertConflict(DoNothing(),...) -> INSERT INTO ... ON CONFLICT DO NOTHING

func (Conflict) Updates 

func (c Conflict) Updates() *ConflictUpdate

Updates returns the struct that represents the UPDATE fragment of an INSERT ... ON CONFLICT/ON DUPLICATE KEY DO UPDATE statement If nil, no update is preformed.

type ConflictExpression 

type ConflictExpression interface {
	Updates() *ConflictUpdate
}

An Expression that the ON CONFLICT/ON DUPLICATE KEY portion of an INSERT statement

type ConflictUpdate 

type ConflictUpdate struct {
	Target      string
	Update      interface{}
	WhereClause ExpressionList
}

ConflictUpdate is the struct that represents the UPDATE fragment of an INSERT ... ON CONFLICT/ON DUPLICATE KEY DO UPDATE statement

func DoUpdate 

func DoUpdate(target string, update interface{}) *ConflictUpdate

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

func (ConflictUpdate) TargetColumn 

func (c ConflictUpdate) TargetColumn() string

Returns the target conflict column. Only necessary for Postgres. Will return an error for mysql/sqlite. Will also return an error if missing from a postgres ConflictUpdate.

func (ConflictUpdate) Updates 

func (c ConflictUpdate) Updates() *ConflictUpdate

Returns the Updates which represent the ON CONFLICT DO UPDATE portion of an insert statement. If nil, there are no updates.

func (*ConflictUpdate) Where 

func (c *ConflictUpdate) Where(expressions ...Expression) *ConflictUpdate

Append to the existing Where clause for an ON CONFLICT DO UPDATE ... WHERE ... 

InsertConflict(DoNothing(),...) -> INSERT INTO ... ON CONFLICT DO NOTHING

type CrudExec 

type CrudExec struct {
	Sql  string
	Args []interface{}
	// contains filtered or unexported fields
}

func (CrudExec) Exec 

func (me CrudExec) Exec() (sql.Result, error)

func (CrudExec) ExecContext 

func (me CrudExec) ExecContext(ctx context.Context) (sql.Result, error)

func (CrudExec) ScanStruct 

func (me CrudExec) ScanStruct(i interface{}) (bool, error)

This will execute the SQL and fill out the struct with the fields returned. This method returns a boolean value that is false if no record was found 

var myStruct MyStruct
found, err := From("test").Limit(1).ScanStruct(&myStruct)
if err != nil{
    panic(err.Error()
}
if !found{
      fmt.Println("NOT FOUND")
}

i: A pointer to a struct

func (CrudExec) ScanStructContext 

func (me CrudExec) ScanStructContext(ctx context.Context, i interface{}) (bool, error)

This will execute the SQL and fill out the struct with the fields returned. This method returns a boolean value that is false if no record was found 

var myStruct MyStruct
found, err := From("test").Limit(1).ScanStructContext(ctx, &myStruct)
if err != nil{
    panic(err.Error()
}
if !found{
      fmt.Println("NOT FOUND")
}

i: A pointer to a struct

func (CrudExec) ScanStructs 

func (me CrudExec) ScanStructs(i interface{}) error

This will execute the SQL and append results to the slice 

var myStructs []MyStruct
if err := From("test").ScanStructs(&myStructs); err != nil{
    panic(err.Error()
}
//use your structs

i: A pointer to a slice of structs.

func (CrudExec) ScanStructsContext 

func (me CrudExec) ScanStructsContext(ctx context.Context, i interface{}) error

This will execute the SQL and append results to the slice 

var myStructs []MyStruct
if err := From("test").ScanStructsContext(ctx, &myStructs); err != nil{
    panic(err.Error()
}
//use your structs

i: A pointer to a slice of structs.

func (CrudExec) ScanVal 

func (me CrudExec) ScanVal(i interface{}) (bool, error)

This will execute the SQL and set the value of the primitive. This method will return false if no record is found. 

 var id uint32
 found, err := From("test").Select("id").Limit(1).ScanVal(&id)
 if err != nil{
     panic(err.Error()
 }
 if !found{
     fmt.Println("NOT FOUND")
 }

i: Takes a pointer to a primitive value.

func (CrudExec) ScanValContext 

func (me CrudExec) ScanValContext(ctx context.Context, i interface{}) (bool, error)

This will execute the SQL and set the value of the primitive. This method will return false if no record is found. 

 var id uint32
 found, err := From("test").Select("id").Limit(1).ScanValContext(ctx, &id)
 if err != nil{
     panic(err.Error()
 }
 if !found{
     fmt.Println("NOT FOUND")
 }

i: Takes a pointer to a primitive value.

func (CrudExec) ScanVals 

func (me CrudExec) ScanVals(i interface{}) error

This will execute the SQL and append results to the slice. 

var ids []uint32
if err := From("test").Select("id").ScanVals(&ids); err != nil{
    panic(err.Error()
}

i: Takes a pointer to a slice of primitive values.

func (CrudExec) ScanValsContext 

func (me CrudExec) ScanValsContext(ctx context.Context, i interface{}) error

This will execute the SQL and append results to the slice. 

var ids []uint32
if err := From("test").Select("id").ScanValsContext(ctx, &ids); err != nil{
    panic(err.Error()
}

i: Takes a pointer to a slice of primitive values.

type Database 

type Database struct {
	Dialect string
	Db      *sql.DB
	// 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 *sql.DB) *Database

This is the common entry point into goqu.

dialect: This is the adapter dialect, you should see your database adapter for the string to use. Built in adpaters can be found at https://github.com/doug-martin/goqu/tree/master/adapters

db: A sql.Db to use for querying the database 

import (
    "database/sql"
    "fmt"
    "gopkg.in/doug-martin/goqu.v5"
    _ "gopkg.in/doug-martin/goqu.v5/adapters/postgres"
    _ "github.com/lib/pq"
)

func main() {
    sqlDb, err := sql.Open("postgres", "user=postgres dbname=goqupostgres sslmode=disable ")
    if err != nil {
        panic(err.Error())
    }
    db := goqu.New("postgres", sqlDb)
}

The most commonly used Database method is From, which 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)

func (*Database) Begin 

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

Starts a new Transaction.

func (*Database) Exec 

func (me *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

func (*Database) ExecContext 

func (me *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

func (*Database) From 

func (me *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

func (*Database) Logger 

func (me *Database) Logger(logger Logger)

Sets the logger for to use when logging queries

func (*Database) Prepare 

func (me *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 (me *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 (me *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)).Sql()
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 (me *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)).Sql()
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 (me *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).Sql()
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 (me *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).Sql()
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 (me *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 (me *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 (me *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 (me *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 (me *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 (me *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 (me *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 (me *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 (me *Database) Trace(op, sql string, args ...interface{})

Logs a given operation with the specified sql and arguments

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

  • Sql() - Returns a SELECT statement
  • UpdateSql() - Returns an UPDATE statement
  • InsertSql() - Returns an INSERT statement
  • DeleteSql() - Returns a DELETE statement
  • TruncateSql() - 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 statment with arguments

Instead with interpolation the database just executes the statement 

sql, err := From("test").Where(I("a").Eq(10).Sql() //SELECT * FROM "test" WHERE "a" = 10

Sometimes you might want to generated a prepared statement in which case you would use one of the "To" SQL generation methods, with the isPrepared argument set to true.

  • ToSql(true) - generates a SELECT statement without the arguments interpolated

  • ToUpdateSql(true, update) - generates an UPDATE statement without the arguments interpolated

  • ToInsertSql(true, rows....) - generates an INSERT statement without the arguments interpolated

  • ToDeleteSql(true) - generates a DELETE statement without arguments interpolated

  • ToTruncateSql(true, opts) - generates a TRUNCATE statement without arguments interpolated

    sql, args, err := From("test").Where(I("a").Eq(10).ToSql(true) //sql := SELECT * FROM "test" WHERE "a" = ? args:=[]interface{}{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 statment. 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.

func From 

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

Returns a dataset with the DefaultAdapter. Typically you would use Database#From.

func (*Dataset) Adapter 

func (me *Dataset) Adapter() Adapter

Returns the current adapter on the dataset

func (*Dataset) As 

func (me *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 
db := goqu.New("default", driver)
ds := db.From("test").As("t")
sql, _, _ := db.From(ds).ToSql()
fmt.Println(sql)

Output:

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

func (*Dataset) ClearLimit 

func (me *Dataset) ClearLimit() *Dataset

Removes the LIMIT clause.

Example 
db := goqu.New("default", driver)
ds := db.From("test").Limit(10)
sql, _, _ := ds.ClearLimit().ToSql()
fmt.Println(sql)

Output:

SELECT * FROM "test"

func (*Dataset) ClearOffset 

func (me *Dataset) ClearOffset() *Dataset

Removes the OFFSET clause from the Dataset

Example 
db := goqu.New("default", driver)
ds := db.From("test").
	Offset(2)
sql, _, _ := ds.
	ClearOffset().
	ToSql()
fmt.Println(sql)

Output:

SELECT * FROM "test"

func (*Dataset) ClearOrder 

func (me *Dataset) ClearOrder() *Dataset

Removes the ORDER BY clause. See examples.

Example 
db := goqu.New("default", driver)
ds := db.From("test").Order(goqu.I("a").Asc())
sql, _, _ := ds.ClearOrder().ToSql()
fmt.Println(sql)

Output:

SELECT * FROM "test"

func (*Dataset) ClearSelect 

func (me *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 
db := goqu.New("default", driver)
ds := db.From("test").Select("a", "b")
sql, _, _ := ds.ClearSelect().ToSql()
fmt.Println(sql)
ds = db.From("test").SelectDistinct("a", "b")
sql, _, _ = ds.ClearSelect().ToSql()
fmt.Println(sql)

Output:

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

func (*Dataset) ClearWhere 

func (me *Dataset) ClearWhere() *Dataset

Removes the WHERE clause. See examples.

Example 
db := goqu.New("default", driver)
ds := db.From("test").Where(
	goqu.Or(
		goqu.I("a").Gt(10),
		goqu.And(
			goqu.I("b").Lt(10),
			goqu.I("c").IsNull(),
		),
	),
)
sql, _, _ := ds.ClearWhere().ToSql()
fmt.Println(sql)

Output:

SELECT * FROM "test"

func (*Dataset) Clone 

func (me *Dataset) Clone() Expression

Clones the dataset

func (*Dataset) Count 

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

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

func (*Dataset) CountContext 

func (me *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 (me *Dataset) CrossJoin(table Expression) *Dataset

Adds a CROSS JOIN clause. See examples.

Example 
db := goqu.New("default", driver)
sql, _, _ := db.From("test").CrossJoin(goqu.I("test2")).ToSql()
fmt.Println(sql)
sql, _, _ = db.From("test").CrossJoin(db.From("test2").Where(goqu.I("amount").Gt(0))).ToSql()
fmt.Println(sql)
sql, _, _ = db.From("test").CrossJoin(db.From("test2").Where(goqu.I("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 (me *Dataset) Delete() *CrudExec

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

func (*Dataset) Expression 

func (me *Dataset) Expression() Expression

func (*Dataset) From 

func (me *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 
db := goqu.New("default", driver)
ds := db.From("test")
sql, _, _ := ds.From("test2").ToSql()
fmt.Println(sql)

Output:

SELECT * FROM "test2"
Example (WithAliasedDataset) 
db := goqu.New("default", driver)
ds := db.From("test")
fromDs := ds.Where(goqu.I("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) 
db := goqu.New("default", driver)
ds := db.From("test")
fromDs := ds.Where(goqu.I("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 (me *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 
db := goqu.New("default", driver)
sql, _, _ := db.From("test").FromSelf().ToSql()
fmt.Println(sql)
sql, _, _ = db.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 (me *Dataset) FullJoin(table Expression, condition joinExpression) *Dataset

Adds a FULL JOIN clause. See examples.

Example 
db := goqu.New("default", driver)
sql, _, _ := db.From("test").FullJoin(goqu.I("test2"), goqu.On(goqu.Ex{"test.fkey": goqu.I("test2.Id")})).ToSql()
fmt.Println(sql)
sql, _, _ = db.From("test").FullJoin(goqu.I("test2"), goqu.Using("common_column")).ToSql()
fmt.Println(sql)
sql, _, _ = db.From("test").FullJoin(db.From("test2").Where(goqu.I("amount").Gt(0)), goqu.On(goqu.I("test.fkey").Eq(goqu.I("test2.Id")))).ToSql()
fmt.Println(sql)
sql, _, _ = db.From("test").FullJoin(db.From("test2").Where(goqu.I("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 (me *Dataset) FullOuterJoin(table Expression, condition joinExpression) *Dataset

Adds a FULL OUTER JOIN clause. See examples.

Example 
db := goqu.New("default", driver)
sql, _, _ := db.From("test").FullOuterJoin(goqu.I("test2"), goqu.On(goqu.Ex{"test.fkey": goqu.I("test2.Id")})).ToSql()
fmt.Println(sql)
sql, _, _ = db.From("test").FullOuterJoin(goqu.I("test2"), goqu.Using("common_column")).ToSql()
fmt.Println(sql)
sql, _, _ = db.From("test").FullOuterJoin(db.From("test2").Where(goqu.I("amount").Gt(0)), goqu.On(goqu.I("test.fkey").Eq(goqu.I("test2.Id")))).ToSql()
fmt.Println(sql)
sql, _, _ = db.From("test").FullOuterJoin(db.From("test2").Where(goqu.I("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 (me *Dataset) GetClauses() clauses

Returns the current clauses on the dataset.

func (*Dataset) GroupBy 

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

Adds a GROUP BY clause. See examples.

func (*Dataset) Having 

func (me *Dataset) Having(expressions ...Expression) *Dataset

Adds a HAVING clause. See examples.

Example 
db := goqu.New("default", driver)
sql, _, _ := db.From("test").Having(goqu.SUM("income").Gt(1000)).ToSql()
fmt.Println(sql)
sql, _, _ = db.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 (me *Dataset) InnerJoin(table Expression, condition joinExpression) *Dataset

Adds an INNER JOIN clause. See examples.

Example 
db := goqu.New("default", driver)
sql, _, _ := db.From("test").InnerJoin(goqu.I("test2"), goqu.On(goqu.Ex{"test.fkey": goqu.I("test2.Id")})).ToSql()
fmt.Println(sql)
sql, _, _ = db.From("test").InnerJoin(goqu.I("test2"), goqu.Using("common_column")).ToSql()
fmt.Println(sql)
sql, _, _ = db.From("test").InnerJoin(db.From("test2").Where(goqu.I("amount").Gt(0)), goqu.On(goqu.I("test.fkey").Eq(goqu.I("test2.Id")))).ToSql()
fmt.Println(sql)
sql, _, _ = db.From("test").InnerJoin(db.From("test2").Where(goqu.I("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 (me *Dataset) Insert(i ...interface{}) *CrudExec

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#InsertSql for arguments

func (*Dataset) InsertConflict 

func (me *Dataset) InsertConflict(c ConflictExpression, i ...interface{}) *CrudExec

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 (me *Dataset) InsertIgnore(i ...interface{}) *CrudExec

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#InsertIgnore for arguments

func (*Dataset) Intersect 

func (me *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 
db := goqu.New("default", driver)
sql, _, _ := db.From("test").
	Intersect(db.From("test2")).
	ToSql()
fmt.Println(sql)
sql, _, _ = db.From("test").
	Limit(1).
	Intersect(db.From("test2")).
	ToSql()
fmt.Println(sql)
sql, _, _ = db.From("test").
	Limit(1).
	Intersect(db.From("test2").
		Order(goqu.I("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 (me *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 
db := goqu.New("default", driver)
sql, _, _ := db.From("test").
	IntersectAll(db.From("test2")).
	ToSql()
fmt.Println(sql)
sql, _, _ = db.From("test").
	Limit(1).
	IntersectAll(db.From("test2")).
	ToSql()
fmt.Println(sql)
sql, _, _ = goqu.
	From("test").
	Limit(1).
	IntersectAll(db.From("test2").
		Order(goqu.I("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) Join 

func (me *Dataset) Join(table Expression, condition joinExpression) *Dataset

Alias to InnerJoin. See examples.

Example 
db := goqu.New("default", driver)
sql, _, _ := db.From("test").Join(goqu.I("test2"), goqu.On(goqu.Ex{"test.fkey": goqu.I("test2.Id")})).ToSql()
fmt.Println(sql)
sql, _, _ = db.From("test").Join(goqu.I("test2"), goqu.Using("common_column")).ToSql()
fmt.Println(sql)
sql, _, _ = db.From("test").Join(db.From("test2").Where(goqu.I("amount").Gt(0)), goqu.On(goqu.I("test.fkey").Eq(goqu.I("test2.Id")))).ToSql()
fmt.Println(sql)
sql, _, _ = db.From("test").Join(db.From("test2").Where(goqu.I("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) LeftJoin 

func (me *Dataset) LeftJoin(table Expression, condition joinExpression) *Dataset

Adds a LEFT JOIN clause. See examples.

Example 
db := goqu.New("default", driver)
sql, _, _ := db.From("test").LeftJoin(goqu.I("test2"), goqu.On(goqu.Ex{"test.fkey": goqu.I("test2.Id")})).ToSql()
fmt.Println(sql)
sql, _, _ = db.From("test").LeftJoin(goqu.I("test2"), goqu.Using("common_column")).ToSql()
fmt.Println(sql)
sql, _, _ = db.From("test").LeftJoin(db.From("test2").Where(goqu.I("amount").Gt(0)), goqu.On(goqu.I("test.fkey").Eq(goqu.I("test2.Id")))).ToSql()
fmt.Println(sql)
sql, _, _ = db.From("test").LeftJoin(db.From("test2").Where(goqu.I("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 (me *Dataset) LeftOuterJoin(table Expression, condition joinExpression) *Dataset

Adds a LEFT OUTER JOIN clause. See examples.

Example 
db := goqu.New("default", driver)
sql, _, _ := db.From("test").LeftOuterJoin(goqu.I("test2"), goqu.On(goqu.Ex{"test.fkey": goqu.I("test2.Id")})).ToSql()
fmt.Println(sql)
sql, _, _ = db.From("test").LeftOuterJoin(goqu.I("test2"), goqu.Using("common_column")).ToSql()
fmt.Println(sql)
sql, _, _ = db.From("test").LeftOuterJoin(db.From("test2").Where(goqu.I("amount").Gt(0)), goqu.On(goqu.I("test.fkey").Eq(goqu.I("test2.Id")))).ToSql()
fmt.Println(sql)
sql, _, _ = db.From("test").LeftOuterJoin(db.From("test2").Where(goqu.I("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 (me *Dataset) Limit(limit uint) *Dataset

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

Example 
db := goqu.New("default", driver)
ds := db.From("test").Limit(10)
sql, _, _ := ds.ToSql()
fmt.Println(sql)

Output:

SELECT * FROM "test" LIMIT 10

func (*Dataset) LimitAll 

func (me *Dataset) LimitAll() *Dataset

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

Example 
db := goqu.New("default", driver)
ds := db.From("test").LimitAll()
sql, _, _ := ds.ToSql()
fmt.Println(sql)

Output:

SELECT * FROM "test" LIMIT ALL

func (*Dataset) Literal 

func (me *Dataset) Literal(buf *SqlBuilder, val interface{}) error

This method is used to serialize:

  • Primitive Values (e.g. float64, int64, string, bool, time.Time, or nil)
  • Expressions

buf: The SqlBuilder to write the generated SQL to

val: The value to serialize

Errors:

  • If there is an error generating the SQL

func (*Dataset) NaturalFullJoin 

func (me *Dataset) NaturalFullJoin(table Expression) *Dataset

Adds a NATURAL FULL JOIN clause. See examples.

Example 
db := goqu.New("default", driver)
sql, _, _ := db.From("test").NaturalFullJoin(goqu.I("test2")).ToSql()
fmt.Println(sql)
sql, _, _ = db.From("test").NaturalFullJoin(db.From("test2").Where(goqu.I("amount").Gt(0))).ToSql()
fmt.Println(sql)
sql, _, _ = db.From("test").NaturalFullJoin(db.From("test2").Where(goqu.I("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 (me *Dataset) NaturalJoin(table Expression) *Dataset

Adds a NATURAL JOIN clause. See examples.

Example 
db := goqu.New("default", driver)
sql, _, _ := db.From("test").NaturalJoin(goqu.I("test2")).ToSql()
fmt.Println(sql)
sql, _, _ = db.From("test").NaturalJoin(db.From("test2").Where(goqu.I("amount").Gt(0))).ToSql()
fmt.Println(sql)
sql, _, _ = db.From("test").NaturalJoin(db.From("test2").Where(goqu.I("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 (me *Dataset) NaturalLeftJoin(table Expression) *Dataset

Adds a NATURAL LEFT JOIN clause. See examples.

Example 
db := goqu.New("default", driver)
sql, _, _ := db.From("test").NaturalLeftJoin(goqu.I("test2")).ToSql()
fmt.Println(sql)
sql, _, _ = db.From("test").NaturalLeftJoin(db.From("test2").Where(goqu.I("amount").Gt(0))).ToSql()
fmt.Println(sql)
sql, _, _ = db.From("test").NaturalLeftJoin(db.From("test2").Where(goqu.I("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 (me *Dataset) NaturalRightJoin(table Expression) *Dataset

Adds a NATURAL RIGHT JOIN clause. See examples.

Example 
db := goqu.New("default", driver)
sql, _, _ := db.From("test").NaturalRightJoin(goqu.I("test2")).ToSql()
fmt.Println(sql)
sql, _, _ = db.From("test").NaturalRightJoin(db.From("test2").Where(goqu.I("amount").Gt(0))).ToSql()
fmt.Println(sql)
sql, _, _ = db.From("test").NaturalRightJoin(db.From("test2").Where(goqu.I("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 (me *Dataset) Offset(offset uint) *Dataset

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

Example 
db := goqu.New("default", driver)
ds := db.From("test").
	Offset(2)
sql, _, _ := ds.ToSql()
fmt.Println(sql)

Output:

SELECT * FROM "test" OFFSET 2

func (*Dataset) Order 

func (me *Dataset) Order(order ...OrderedExpression) *Dataset

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

Example 
db := goqu.New("default", driver)
ds := db.From("test").
	Order(goqu.I("a").Asc())
sql, _, _ := ds.ToSql()
fmt.Println(sql)

Output:

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

func (*Dataset) OrderAppend 

func (me *Dataset) OrderAppend(order ...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 
db := goqu.New("default", driver)
ds := db.From("test").Order(goqu.I("a").Asc())
sql, _, _ := ds.OrderAppend(goqu.I("b").Desc().NullsLast()).ToSql()
fmt.Println(sql)

Output:

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

func (*Dataset) Pluck 

func (me *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

func (*Dataset) PluckContext 

func (me *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 (me *Dataset) Prepared(prepared bool) *Dataset

Set the parameter interpolation behavior. See examples

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

Example 
db := goqu.New("default", driver)
sql, args, _ := db.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, _ = db.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, _ = db.From("items").Prepared(true).ToUpdateSql(
	goqu.Record{"name": "Test", "address": "111 Test Addr"},
)
fmt.Println(sql, args)

sql, args, _ = db.From("items").
	Prepared(true).
	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]

func (*Dataset) Returning 

func (me *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 
db := goqu.New("default", driver)
sql, _, _ := db.From("test").
	Returning("id").
	ToInsertSql(goqu.Record{"a": "a", "b": "b"})
fmt.Println(sql)
sql, _, _ = db.From("test").
	Returning(goqu.I("test.*")).
	ToInsertSql(goqu.Record{"a": "a", "b": "b"})
fmt.Println(sql)
sql, _, _ = db.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 (me *Dataset) RightJoin(table Expression, condition joinExpression) *Dataset

Adds a RIGHT JOIN clause. See examples.

Example 
db := goqu.New("default", driver)
sql, _, _ := db.From("test").RightJoin(goqu.I("test2"), goqu.On(goqu.Ex{"test.fkey": goqu.I("test2.Id")})).ToSql()
fmt.Println(sql)
sql, _, _ = db.From("test").RightJoin(goqu.I("test2"), goqu.Using("common_column")).ToSql()
fmt.Println(sql)
sql, _, _ = db.From("test").RightJoin(db.From("test2").Where(goqu.I("amount").Gt(0)), goqu.On(goqu.I("test.fkey").Eq(goqu.I("test2.Id")))).ToSql()
fmt.Println(sql)
sql, _, _ = db.From("test").RightJoin(db.From("test2").Where(goqu.I("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 (me *Dataset) RightOuterJoin(table Expression, condition joinExpression) *Dataset

Adds a RIGHT OUTER JOIN clause. See examples.

Example 
db := goqu.New("default", driver)
sql, _, _ := db.From("test").RightOuterJoin(goqu.I("test2"), goqu.On(goqu.Ex{"test.fkey": goqu.I("test2.Id")})).ToSql()
fmt.Println(sql)
sql, _, _ = db.From("test").RightOuterJoin(goqu.I("test2"), goqu.Using("common_column")).ToSql()
fmt.Println(sql)
sql, _, _ = db.From("test").RightOuterJoin(
	db.From("test2").Where(goqu.I("amount").Gt(0)),
	goqu.On(goqu.I("test.fkey").Eq(goqu.I("test2.Id"))),
).ToSql()
fmt.Println(sql)
sql, _, _ = db.From("test").RightOuterJoin(
	db.From("test2").Where(goqu.I("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 (me *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

func (*Dataset) ScanStructContext 

func (me *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 (me *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

func (*Dataset) ScanStructsContext 

func (me *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 (me *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

func (*Dataset) ScanValContext 

func (me *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 (me *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

func (*Dataset) ScanValsContext 

func (me *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 (me *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 
db := goqu.New("default", driver)
sql, _, _ := db.From("test").Select("a", "b", "c").ToSql()
fmt.Println(sql)

Output:

SELECT "a", "b", "c" FROM "test"
Example (WithAliasedDataset) 
db := goqu.New("default", driver)
ds := db.From("test")
fromDs := ds.Select("age").Where(goqu.I("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) 
db := goqu.New("default", driver)
ds := db.From("test")
fromDs := ds.Select("age").Where(goqu.I("age").Gt(10))
sql, _, _ := ds.From().Select(fromDs).ToSql()
fmt.Println(sql)

Output:

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

Output:

SELECT a + b AS "sum" FROM "test"
Example (WithSqlFunctionExpression) 
db := goqu.New("default", driver)
sql, _, _ := db.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) 
db := goqu.New("default", driver)
ds := db.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 (me *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 
db := goqu.New("default", driver)
ds := db.From("test").Select("a", "b")
sql, _, _ := ds.SelectAppend("c").ToSql()
fmt.Println(sql)
ds = db.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 (me *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 
db := goqu.New("default", driver)
sql, _, _ := db.From("test").SelectDistinct("a", "b").ToSql()
fmt.Println(sql)

Output:

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

func (*Dataset) SetAdapter 

func (me *Dataset) SetAdapter(adapter Adapter) *Dataset

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

func (*Dataset) ToDeleteSql 

func (me *Dataset) ToDeleteSql() (string, []interface{}, 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 
db := goqu.New("default", driver)
sql, args, _ := db.From("items").ToDeleteSql()
fmt.Println(sql, args)

sql, args, _ = db.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) 
db := goqu.New("default", driver)
sql, args, _ := db.From("items").Prepared(true).ToDeleteSql()
fmt.Println(sql, args)

sql, args, _ = db.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]

func (*Dataset) ToInsertConflictSql 

func (me *Dataset) ToInsertConflictSql(o ConflictExpression, rows ...interface{}) (string, []interface{}, 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 
db := goqu.New("mysql", driver)
type item struct {
	Id      uint32 `db:"id" goqu:"skipinsert"`
	Address string `db:"address"`
	Name    string `db:"name"`
}
sql, args, _ := db.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, _ = db.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, _ = db.From("items").ToInsertConflictSql(
	goqu.DoUpdate("key", goqu.Record{"updated": goqu.L("NOW()")}).Where(goqu.I("allow_update").IsTrue()),
	[]item{
		{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 (key) DO UPDATE SET "updated"=NOW() []
INSERT INTO "items" ("address", "name") VALUES ('111 Test Addr', 'Test1'), ('112 Test Addr', 'Test2') ON CONFLICT (key) DO UPDATE SET "updated"=NOW() WHERE ("allow_update" IS TRUE) []

func (*Dataset) ToInsertIgnoreSql 

func (me *Dataset) ToInsertIgnoreSql(rows ...interface{}) (string, []interface{}, 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

func (*Dataset) ToInsertSql 

func (me *Dataset) ToInsertSql(rows ...interface{}) (string, []interface{}, 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 
db := goqu.New("default", driver)
type item struct {
	Id      uint32 `db:"id" goqu:"skipinsert"`
	Address string `db:"address"`
	Name    string `db:"name"`
}
sql, args, _ := db.From("items").ToInsertSql(
	item{Name: "Test1", Address: "111 Test Addr"},
	item{Name: "Test2", Address: "112 Test Addr"},
)
fmt.Println(sql, args)

sql, args, _ = db.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, _ = db.From("items").ToInsertSql(
	[]item{
		{Name: "Test1", Address: "111 Test Addr"},
		{Name: "Test2", Address: "112 Test Addr"},
	})
fmt.Println(sql, args)

sql, args, _ = db.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) 
db := goqu.New("default", driver)
type item struct {
	Id      uint32 `db:"id" goqu:"skipinsert"`
	Address string `db:"address"`
	Name    string `db:"name"`
}

sql, args, _ := db.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, _ = db.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, _ = db.From("items").Prepared(true).ToInsertSql(
	[]item{
		{Name: "Test1", Address: "111 Test Addr"},
		{Name: "Test2", Address: "112 Test Addr"},
	})
fmt.Println(sql, args)

sql, args, _ = db.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]

func (*Dataset) ToSql 

func (me *Dataset) ToSql() (string, []interface{}, 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 
db := goqu.New("default", driver)
sql, args, _ := db.From("items").Where(goqu.Ex{"a": 1}).ToSql()
fmt.Println(sql, args)

Output:

SELECT * FROM "items" WHERE ("a" = 1) []
Example (Prepared) 
db := goqu.New("default", driver)
sql, args, _ := db.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 (me *Dataset) ToTruncateSql() (string, []interface{}, error)

Generates the default TRUNCATE statement. See examples.

Errors:

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

Output:

TRUNCATE "items" []

func (*Dataset) ToTruncateWithOptsSql 

func (me *Dataset) ToTruncateWithOptsSql(opts TruncateOptions) (string, []interface{}, 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 
db := goqu.New("default", driver)
sql, _, _ := db.From("items").
	ToTruncateWithOptsSql(goqu.TruncateOptions{})
fmt.Println(sql)
sql, _, _ = db.From("items").
	ToTruncateWithOptsSql(goqu.TruncateOptions{Cascade: true})
fmt.Println(sql)
sql, _, _ = db.From("items").
	ToTruncateWithOptsSql(goqu.TruncateOptions{Restrict: true})
fmt.Println(sql)
sql, _, _ = db.From("items").
	ToTruncateWithOptsSql(goqu.TruncateOptions{Identity: "RESTART"})
fmt.Println(sql)
sql, _, _ = db.From("items").
	ToTruncateWithOptsSql(goqu.TruncateOptions{Identity: "RESTART", Cascade: true})
fmt.Println(sql)
sql, _, _ = db.From("items").
	ToTruncateWithOptsSql(goqu.TruncateOptions{Identity: "RESTART", Restrict: true})
fmt.Println(sql)
sql, _, _ = db.From("items").
	ToTruncateWithOptsSql(goqu.TruncateOptions{Identity: "CONTINUE"})
fmt.Println(sql)
sql, _, _ = db.From("items").
	ToTruncateWithOptsSql(goqu.TruncateOptions{Identity: "CONTINUE", Cascade: true})
fmt.Println(sql)
sql, _, _ = db.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 (me *Dataset) ToUpdateSql(update interface{}) (string, []interface{}, 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 
db := goqu.New("default", driver)
type item struct {
	Address string `db:"address"`
	Name    string `db:"name"`
}
sql, args, _ := db.From("items").ToUpdateSql(
	item{Name: "Test", Address: "111 Test Addr"},
)
fmt.Println(sql, args)

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

sql, args, _ = db.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) 
db := goqu.New("default", driver)
type item struct {
	Address string `db:"address"`
	Name    string `db:"name"`
}

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

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

sql, args, _ = db.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]

func (*Dataset) Union 

func (me *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 
db := goqu.New("default", driver)
sql, _, _ := db.From("test").
	Union(db.From("test2")).
	ToSql()
fmt.Println(sql)
sql, _, _ = db.From("test").
	Limit(1).
	Union(db.From("test2")).
	ToSql()
fmt.Println(sql)
sql, _, _ = db.From("test").
	Limit(1).
	Union(db.From("test2").
		Order(goqu.I("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 (me *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 
db := goqu.New("default", driver)
sql, _, _ := db.From("test").
	UnionAll(db.From("test2")).
	ToSql()
fmt.Println(sql)
sql, _, _ = db.From("test").
	Limit(1).
	UnionAll(db.From("test2")).
	ToSql()
fmt.Println(sql)
sql, _, _ = db.From("test").
	Limit(1).
	UnionAll(db.From("test2").
		Order(goqu.I("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 (me *Dataset) Update(i interface{}) *CrudExec

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#UpdateSql for arguments

func (*Dataset) Where 

func (me *Dataset) Where(expressions ...Expression) *Dataset

Adds a WHERE clause. See examples.

Example 
db := goqu.New("default", driver)

//By default everything is anded together
sql, _, _ := db.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, _, _ = db.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, _, _ = db.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, _, _ = db.From("test").Where(
	goqu.I("a").Gt(10),
	goqu.I("b").Lt(10),
	goqu.I("c").IsNull(),
	goqu.I("d").In("a", "b", "c"),
).ToSql()
fmt.Println(sql)

//You can use a combination of Ors and Ands
sql, _, _ = db.From("test").Where(
	goqu.Or(
		goqu.I("a").Gt(10),
		goqu.And(
			goqu.I("b").Lt(10),
			goqu.I("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) 
db := goqu.New("default", driver)

//By default everything is anded together
sql, args, _ := db.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, _ = db.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, _ = db.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, _ = db.From("test").Prepared(true).Where(
	goqu.I("a").Gt(10),
	goqu.I("b").Lt(10),
	goqu.I("c").IsNull(),
	goqu.I("d").In("a", "b", "c"),
).ToSql()
fmt.Println(sql, args)

//You can use a combination of Ors and Ands
sql, args, _ = db.From("test").Prepared(true).Where(
	goqu.Or(
		goqu.I("a").Gt(10),
		goqu.And(
			goqu.I("b").Lt(10),
			goqu.I("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 (me *Dataset) With(name string, subquery *Dataset) *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.

func (*Dataset) WithRecursive 

func (me *Dataset) WithRecursive(name string, subquery *Dataset) *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.

type DefaultAdapter 

type DefaultAdapter struct {
	Adapter

	//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 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 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 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 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
	//Set to true to include positional argument numbers when creating a prepared statement
	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
	BooleanOperatorLookup map[BooleanOperation][]byte
	//A map used to look up RangeOperations and their SQL equivalents
	RangeOperatorLookup map[RangeOperation][]byte
	//A map used to look up JoinTypes and their SQL equivalents
	JoinTypeLookup map[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.
	EscapedRunes map[rune][]byte

	ConflictFragment             []byte
	ConflictDoNothingFragment    []byte
	ConflictDoUpdateFragment     []byte
	ConflictTargetSupported      bool
	ConflictUpdateWhereSupported bool
	InsertIgnoreSyntaxSupported  bool
	WithCTESupported             bool
	WithCTERecursiveSupported    bool
	// contains filtered or unexported fields
}

The default adapter. This class should be used when building a new adapter. When creating a new adapter you can either override methods, or more typically update default values. See (github.com/doug-martin/goqu/adapters/postgres)

func (*DefaultAdapter) AliasedExpressionSql 

func (me *DefaultAdapter) AliasedExpressionSql(buf *SqlBuilder, aliased AliasedExpression) error

Generates SQL for an AliasedExpression (e.g. I("a").As("b") -> "a" AS "b")

func (*DefaultAdapter) BooleanExpressionSql 

func (me *DefaultAdapter) BooleanExpressionSql(buf *SqlBuilder, operator BooleanExpression) error

Generates SQL for a BooleanExpresion (e.g. I("a").Eq(2) -> "a" = 2)

func (*DefaultAdapter) CastExpressionSql 

func (me *DefaultAdapter) CastExpressionSql(buf *SqlBuilder, cast CastExpression) error

Generates SQL for a CastExpression 

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

func (*DefaultAdapter) ColumnListSql 

func (me *DefaultAdapter) ColumnListSql(buf *SqlBuilder, columnList ColumnList) error

Generates SQL for a ColumnList

func (*DefaultAdapter) CommonTableExpressionSql 

func (me *DefaultAdapter) CommonTableExpressionSql(buf *SqlBuilder, cte CommonTableExpression) error

Generates SQL for a CommonTableExpression

func (*DefaultAdapter) CommonTablesSql 

func (me *DefaultAdapter) CommonTablesSql(buf *SqlBuilder, ctes []CommonTableExpression) error

Generates the sql for the WITH clauses for common table expressions (CTE)

func (*DefaultAdapter) CompoundExpressionSql 

func (me *DefaultAdapter) CompoundExpressionSql(buf *SqlBuilder, compound CompoundExpression) error

Generates SQL for a CompoundExpression

func (*DefaultAdapter) CompoundsSql 

func (me *DefaultAdapter) CompoundsSql(buf *SqlBuilder, compounds []CompoundExpression) error

Generates the compound sql clause for an SQL statement (e.g. UNION, INTERSECT)

func (*DefaultAdapter) DatasetSql 

func (me *DefaultAdapter) DatasetSql(buf *SqlBuilder, dataset Dataset) error

Generates creates the sql for a sub select on a Dataset

func (*DefaultAdapter) DefaultValuesSql 

func (me *DefaultAdapter) DefaultValuesSql(buf *SqlBuilder) error

Adds the DefaultValuesFragment to an SQL statement

func (*DefaultAdapter) DeleteBeginSql 

func (me *DefaultAdapter) DeleteBeginSql(buf *SqlBuilder) error

Adds the correct fragment to being an DELETE statement

func (*DefaultAdapter) ExpressionListSql 

func (me *DefaultAdapter) ExpressionListSql(buf *SqlBuilder, expressionList ExpressionList) error

Generates SQL for an ExpressionList (e.g. And(I("a").Eq("a"), I("b").Eq("b")) -> (("a" = 'a') AND ("b" = 'b')))

func (*DefaultAdapter) ExpressionMapSql 

func (me *DefaultAdapter) ExpressionMapSql(buf *SqlBuilder, ex Ex) error

func (*DefaultAdapter) ExpressionOrMapSql 

func (me *DefaultAdapter) ExpressionOrMapSql(buf *SqlBuilder, ex ExOr) error

func (*DefaultAdapter) FromSql 

func (me *DefaultAdapter) FromSql(buf *SqlBuilder, from ColumnList) error

Adds the FROM clause and tables to an sql statement

func (*DefaultAdapter) GroupBySql 

func (me *DefaultAdapter) GroupBySql(buf *SqlBuilder, groupBy ColumnList) error

Generates the GROUP BY clause for an SQL statement

func (*DefaultAdapter) HavingSql 

func (me *DefaultAdapter) HavingSql(buf *SqlBuilder, having ExpressionList) error

Generates the HAVING clause for an SQL statement

func (*DefaultAdapter) InsertBeginSql 

func (me *DefaultAdapter) InsertBeginSql(buf *SqlBuilder, o ConflictExpression) error

Adds the correct fragment to being an INSERT statement

func (*DefaultAdapter) InsertColumnsSql 

func (me *DefaultAdapter) InsertColumnsSql(buf *SqlBuilder, cols ColumnList) error

Adds the columns list to an insert statement

func (*DefaultAdapter) InsertValuesSql 

func (me *DefaultAdapter) InsertValuesSql(buf *SqlBuilder, values [][]interface{}) error

Adds the values clause to an SQL statement

func (*DefaultAdapter) JoinSql 

func (me *DefaultAdapter) JoinSql(buf *SqlBuilder, joins JoiningClauses) error

Generates the JOIN clauses for an SQL statement

func (*DefaultAdapter) LimitSql 

func (me *DefaultAdapter) LimitSql(buf *SqlBuilder, limit interface{}) error

Generates the LIMIT clause for an SQL statement

func (*DefaultAdapter) Literal 

func (me *DefaultAdapter) Literal(buf *SqlBuilder, val interface{}) error

This is a proxy to Dataset.Literal. Used internally to ensure the correct method is called on any subclasses and to prevent duplication of code

func (*DefaultAdapter) LiteralBool 

func (me *DefaultAdapter) LiteralBool(buf *SqlBuilder, b bool) error

Generates SQL bool literal, (e.g. TRUE, FALSE, mysql 1, 0, sqlite3 1, 0)

func (*DefaultAdapter) LiteralBytes 

func (me *DefaultAdapter) LiteralBytes(buf *SqlBuilder, bs []byte) error

Generates SQL for a slice of bytes

func (*DefaultAdapter) LiteralExpressionSql 

func (me *DefaultAdapter) LiteralExpressionSql(buf *SqlBuilder, literal LiteralExpression) error

Generates SQL for a LiteralExpression 

L("a + b") -> a + b
L("a = ?", 1) -> a = 1

func (*DefaultAdapter) LiteralFloat 

func (me *DefaultAdapter) LiteralFloat(buf *SqlBuilder, f float64) error

Generates SQL for a Float Value

func (*DefaultAdapter) LiteralInt 

func (me *DefaultAdapter) LiteralInt(buf *SqlBuilder, i int64) error

Generates SQL for an int value

func (*DefaultAdapter) LiteralNil 

func (me *DefaultAdapter) LiteralNil(buf *SqlBuilder) error

Generates SQL NULL value

func (*DefaultAdapter) LiteralString 

func (me *DefaultAdapter) LiteralString(buf *SqlBuilder, s string) error

Generates SQL for a string

func (*DefaultAdapter) LiteralTime 

func (me *DefaultAdapter) LiteralTime(buf *SqlBuilder, t time.Time) error

Generates SQL for a time.Time value

func (*DefaultAdapter) OffsetSql 

func (me *DefaultAdapter) OffsetSql(buf *SqlBuilder, offset uint) error

Generates the OFFSET clause for an SQL statement

func (*DefaultAdapter) OnConflictSql 

func (me *DefaultAdapter) OnConflictSql(buf *SqlBuilder, o ConflictExpression) error

Adds the DefaultValuesFragment to an SQL statement

func (*DefaultAdapter) OrderSql 

func (me *DefaultAdapter) OrderSql(buf *SqlBuilder, order ColumnList) error

Generates the ORDER BY clause for an SQL statement

func (*DefaultAdapter) OrderedExpressionSql 

func (me *DefaultAdapter) OrderedExpressionSql(buf *SqlBuilder, order OrderedExpression) error

Generates SQL for an OrderedExpression (e.g. I("a").Asc() -> "a" ASC)

func (*DefaultAdapter) PlaceHolderSql 

func (me *DefaultAdapter) PlaceHolderSql(buf *SqlBuilder, i interface{}) error

Generates a placeholder (e.g. ?, $1)

func (*DefaultAdapter) QuoteIdentifier 

func (me *DefaultAdapter) QuoteIdentifier(buf *SqlBuilder, ident IdentifierExpression) error

Quotes an identifier (e.g. "col", "table"."col"

func (*DefaultAdapter) RangeExpressionSql 

func (me *DefaultAdapter) RangeExpressionSql(buf *SqlBuilder, operator RangeExpression) error

Generates SQL for a RangeExpresion (e.g. I("a").Between(RangeVal{Start:2,End:5}) -> "a" BETWEEN 2 AND 5)

func (*DefaultAdapter) ReturningSql 

func (me *DefaultAdapter) ReturningSql(buf *SqlBuilder, returns ColumnList) error

func (*DefaultAdapter) SelectDistinctSql 

func (me *DefaultAdapter) SelectDistinctSql(buf *SqlBuilder, cols ColumnList) error

Adds the SELECT DISTINCT clause and columns to a sql statement

func (*DefaultAdapter) SelectSql 

func (me *DefaultAdapter) SelectSql(buf *SqlBuilder, cols ColumnList) error

Adds the SELECT clause and columns to a sql statement

func (*DefaultAdapter) SliceValueSql 

func (me *DefaultAdapter) SliceValueSql(buf *SqlBuilder, slice reflect.Value) error

Generates SQL for a slice of values (e.g. []int64{1,2,3,4} -> (1,2,3,4)

func (*DefaultAdapter) SourcesSql 

func (me *DefaultAdapter) SourcesSql(buf *SqlBuilder, from ColumnList) error

Adds the generates the SQL for a column list

func (*DefaultAdapter) SqlFunctionExpressionSql 

func (me *DefaultAdapter) SqlFunctionExpressionSql(buf *SqlBuilder, sqlFunc SqlFunctionExpression) error

Generates SQL for a SqlFunctionExpression 

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

func (*DefaultAdapter) SupportConflictUpdateWhere 

func (me *DefaultAdapter) SupportConflictUpdateWhere() bool

func (*DefaultAdapter) SupportsConflictTarget 

func (me *DefaultAdapter) SupportsConflictTarget() bool

Override to allow ORDER BY on UPDATE statements

func (*DefaultAdapter) SupportsConflictUpdateWhere 

func (me *DefaultAdapter) SupportsConflictUpdateWhere() bool

Override to allow ORDER BY on UPDATE statements

func (*DefaultAdapter) SupportsInsertIgnoreSyntax 

func (me *DefaultAdapter) SupportsInsertIgnoreSyntax() bool

func (*DefaultAdapter) SupportsLimitOnDelete 

func (me *DefaultAdapter) SupportsLimitOnDelete() bool

Override to allow LIMIT on DELETE statements

func (*DefaultAdapter) SupportsLimitOnUpdate 

func (me *DefaultAdapter) SupportsLimitOnUpdate() bool

Override to allow LIMIT on UPDATE statements

func (*DefaultAdapter) SupportsOrderByOnDelete 

func (me *DefaultAdapter) SupportsOrderByOnDelete() bool

Override to allow ORDER BY on DELETE statements

func (*DefaultAdapter) SupportsOrderByOnUpdate 

func (me *DefaultAdapter) SupportsOrderByOnUpdate() bool

Override to allow ORDER BY on UPDATE statements

func (*DefaultAdapter) SupportsReturn 

func (me *DefaultAdapter) SupportsReturn() bool

Override to prevent return statements from being generated when creating SQL

func (*DefaultAdapter) SupportsWithCTE 

func (me *DefaultAdapter) SupportsWithCTE() bool

func (*DefaultAdapter) SupportsWithRecursiveCTE 

func (me *DefaultAdapter) SupportsWithRecursiveCTE() bool

func (*DefaultAdapter) TruncateSql 

func (me *DefaultAdapter) TruncateSql(buf *SqlBuilder, from ColumnList, opts TruncateOptions) error

Generates a TRUNCATE statement

func (*DefaultAdapter) UpdateBeginSql 

func (me *DefaultAdapter) UpdateBeginSql(buf *SqlBuilder) error

Adds the correct fragment to being an UPDATE statement

func (*DefaultAdapter) UpdateExpressionSql 

func (me *DefaultAdapter) UpdateExpressionSql(buf *SqlBuilder, update UpdateExpression) error

Generates SQL for an UpdateEpxresion

func (*DefaultAdapter) UpdateExpressionsSql 

func (me *DefaultAdapter) UpdateExpressionsSql(buf *SqlBuilder, updates ...UpdateExpression) error

Adds column setters in an update SET clause

func (*DefaultAdapter) WhereSql 

func (me *DefaultAdapter) WhereSql(buf *SqlBuilder, where ExpressionList) error

Generates the WHERE clause for an SQL statement

type DistinctMethods 

type DistinctMethods interface {
	//Creates a DISTINCT clause
	//   I("a").Distinct() //DISTINCT("a")
	Distinct() SqlFunctionExpression
}

Interface that an expression should implement if it can be used in a DISTINCT epxression.

Example 
db := goqu.New("default", driver)
sql, _, _ := db.From("test").Select(goqu.COUNT(goqu.I("a").Distinct())).ToSql()
fmt.Println(sql)

Output:

SELECT COUNT(DISTINCT("a")) FROM "test"

type EncodeError 

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

func (EncodeError) Error 

func (me EncodeError) Error() string

type Ex 

type Ex map[string]interface{}

A map of expressions to be ANDed together where the keys are string that will be used as Identifiers and values will be used in a boolean operation. The Ex map can be used in tandem with Op map to create more complex expression such as LIKE, GT, LT... See examples.

Example 
db := goqu.New("default", driver)
sql, args, _ := 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, args)

Output:

SELECT * FROM "items" WHERE (("col1" = 'a') AND ("col2" = 1) AND ("col3" IS TRUE) AND ("col4" IS FALSE) AND ("col5" IS NULL) AND ("col6" IN ('a', 'b', 'c'))) []
Example (Prepared) 
db := goqu.New("default", driver)
sql, args, _ := db.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)

Output:

SELECT * FROM "items" WHERE (("col1" = ?) AND ("col2" = ?) AND ("col3" IS TRUE) AND ("col4" IS FALSE) AND ("col5" IN (?, ?, ?))) [a 1 a b c]
Example (WithOp) 
db := goqu.New("default", driver)
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)

sql, _, _ = db.From("items").Where(goqu.Ex{
	"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, _, _ = db.From("items").Where(goqu.Ex{
	"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, _, _ = db.From("items").Where(goqu.Ex{
	"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') AND ("col3" IS NOT TRUE) AND ("col6" NOT IN ('a', 'b', 'c')))
SELECT * FROM "items" WHERE (("col1" > 1) AND ("col2" >= 1) AND ("col3" < 1) AND ("col4" <= 1))
SELECT * FROM "items" WHERE (("col1" LIKE 'a%') AND ("col2" NOT LIKE 'a%') AND ("col3" ILIKE 'a%') AND ("col4" NOT ILIKE 'a%'))
SELECT * FROM "items" WHERE (("col1" ~ '^(a|b)') AND ("col2" !~ '^(a|b)') AND ("col3" ~* '^(a|b)') AND ("col4" !~* '^(a|b)'))
Example (WithOpPrepared) 
db := goqu.New("default", driver)
sql, args, _ := db.From("items").Prepared(true).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)

sql, args, _ = db.From("items").Prepared(true).Where(goqu.Ex{
	"col1": goqu.Op{"gt": 1},
	"col2": goqu.Op{"gte": 1},
	"col3": goqu.Op{"lt": 1},
	"col4": goqu.Op{"lte": 1},
}).ToSql()
fmt.Println(sql, args)

sql, args, _ = db.From("items").Prepared(true).Where(goqu.Ex{
	"col1": goqu.Op{"like": "a%"},
	"col2": goqu.Op{"notLike": "a%"},
	"col3": goqu.Op{"iLike": "a%"},
	"col4": goqu.Op{"notILike": "a%"},
}).ToSql()
fmt.Println(sql, args)

sql, args, _ = db.From("items").Prepared(true).Where(goqu.Ex{
	"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, args)

sql, args, _ = db.From("items").Prepared(true).Where(goqu.Ex{
	"col1": goqu.Op{"between": goqu.RangeVal{Start: 1, End: 10}},
	"col2": goqu.Op{"notbetween": goqu.RangeVal{Start: 1, End: 10}},
}).ToSql()
fmt.Println(sql, args)

Output:

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

func (Ex) Clone 

func (me Ex) Clone() Expression

func (Ex) Expression 

func (me Ex) Expression() Expression

func (Ex) ToExpressions 

func (me Ex) ToExpressions() (ExpressionList, error)

type ExOr 

type ExOr map[string]interface{}

A map of expressions to be ORed together where the keys are string that will be used as Identifiers and values will be used in a boolean operation. The Ex map can be used in tandem with Op map to create more complex expression such as LIKE, GT, LT... See examples.

Example 
db := goqu.New("default", driver)
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')))
Example (WithOp) 
db := goqu.New("default", driver)
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)

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

func (ExOr) Clone 

func (me ExOr) Clone() Expression

func (ExOr) Expression 

func (me ExOr) Expression() Expression

func (ExOr) ToExpressions 

func (me ExOr) ToExpressions() (ExpressionList, error)

type Expression 

type Expression interface {
	Clone() Expression
	Expression() Expression
}

Parent of all expression types

type ExpressionList 

type ExpressionList interface {
	Expression
	//Returns type (e.g. OR, AND)
	Type() ExpressionListType
	//Slice of expressions that should be joined togehter
	Expressions() []Expression
	//Returns a new expression list with the given expressions appended to the current Expressions list
	Append(...Expression) ExpressionList
}

A list of expressions that should be joined together 

And(I("a").Eq(10), I("b").Eq(11)) //(("a" = 10) AND ("b" = 11))
Or(I("a").Eq(10), I("b").Eq(11)) //(("a" = 10) OR ("b" = 11))

type ExpressionListType 

type ExpressionListType int
const (
	AND_TYPE ExpressionListType = iota
	OR_TYPE
)

type GoquError 

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

func (GoquError) Error 

func (me GoquError) Error() string

type IdentifierExpression 

type IdentifierExpression interface {
	Expression
	AliasMethods
	ComparisonMethods
	RangeMethods
	InMethods
	StringMethods
	BooleanMethods
	OrderedMethods

	DistinctMethods
	CastMethods
	//Returns a new IdentifierExpression with the specified schema
	Schema(string) IdentifierExpression
	//Returns the current schema
	GetSchema() string
	//Returns a new IdentifierExpression with the specified table
	Table(string) IdentifierExpression
	//Returns the current table
	GetTable() string
	//Returns a new IdentifierExpression with the specified column
	Col(interface{}) IdentifierExpression
	//Returns the current column
	GetCol() interface{}
	//Returns a new IdentifierExpression with the column set to *
	//   I("my_table").All() //"my_table".*
	All() IdentifierExpression
	// contains filtered or unexported methods
}

An Identifier that can contain schema, table and column identifiers

func I 

func I(ident string) 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"). An Identifier can represent a one or a combination of schema, table, and/or column. 

I("column") -> "column" //A Column
I("table.column") -> "table"."column" //A Column and table
I("schema.table.column") //Schema table and column
I("table.*") //Also handles the * operator
Example 
db := goqu.New("default", driver)
sql, _, _ := db.From("test").Where(
	goqu.I("a").Eq(10),
	goqu.I("b").Lt(10),
	goqu.I("d").IsTrue(),
).ToSql()
fmt.Println(sql)

//qualify with schema
sql, _, _ = db.From(goqu.I("test").Schema("my_schema")).ToSql()
fmt.Println(sql)

sql, _, _ = db.From(goqu.I("mychema.test")).Where(
	//qualify with schema, table, and col
	goqu.I("my_schema.test.a").Eq(10),
).ToSql()
fmt.Println(sql)

//* will be taken literally and no quoted
sql, _, _ = db.From(goqu.I("test")).Select(goqu.I("test.*")).ToSql()
fmt.Println(sql)

Output:

SELECT * FROM "test" WHERE (("a" = 10) AND ("b" < 10) AND ("d" IS TRUE))
SELECT * FROM "my_schema"."test"
SELECT * FROM "mychema"."test" WHERE ("my_schema"."test"."a" = 10)
SELECT "test".* FROM "test"

type InMethods 

type InMethods interface {
	//Creates a Boolean expression for IN clauses
	//    I("col").In([]string{"a", "b", "c"}) //("col" IN ('a', 'b', 'c'))
	In(...interface{}) BooleanExpression
	//Creates a Boolean expression for NOT IN clauses
	//    I("col").NotIn([]string{"a", "b", "c"}) //("col" NOT IN ('a', 'b', 'c'))
	NotIn(...interface{}) BooleanExpression
}

Interface that an expression should implement if it can be used in an IN expression

Example 
db := goqu.New("default", driver)
//using identifiers
sql, _, _ := db.From("test").Where(goqu.I("a").In("a", "b", "c")).ToSql()
fmt.Println(sql)

//with a slice
sql, _, _ = db.From("test").Where(goqu.I("a").In([]string{"a", "b", "c"})).ToSql()
fmt.Println(sql)

sql, _, _ = db.From("test").Where(goqu.I("a").NotIn("a", "b", "c")).ToSql()
fmt.Println(sql)

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

//using an Ex expression map
sql, _, _ = db.From("test").Where(goqu.Ex{
	"a": []string{"a", "b", "c"},
}).ToSql()
fmt.Println(sql)
sql, _, _ = db.From("test").Where(goqu.Ex{
	"a": goqu.Op{"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'))
SELECT * FROM "test" WHERE ("a" IN ('a', 'b', 'c'))
SELECT * FROM "test" WHERE ("a" NOT IN ('a', 'b', 'c'))

type JoinCondition 

type JoinCondition int

type JoinOnExpression 

type JoinOnExpression interface {
	On() ExpressionList
	// contains filtered or unexported methods
}

A join expression that uses an ON clause

type JoinType 

type JoinType int

type JoinUsingExpression 

type JoinUsingExpression interface {
	Using() ColumnList
	// contains filtered or unexported methods
}

type JoiningClause 

type JoiningClause struct {
	//The JoinType
	JoinType JoinType
	//If this is a conditioned join (e.g. NATURAL, or INNER)
	IsConditioned bool
	//The table expressions (e.g. LEFT JOIN "my_table", ON (....))
	Table Expression
	//The condition to join (e.g. USING("a", "b"), ON("my_table"."fkey" = "other_table"."id")
	Condition joinExpression
}

Container for all joins within a dataset

func (JoiningClause) Clone 

func (me JoiningClause) Clone() JoiningClause

type JoiningClauses 

type JoiningClauses []JoiningClause

func (JoiningClauses) Clone 

func (me JoiningClauses) Clone() JoiningClauses

type LiteralExpression 

type LiteralExpression interface {
	Expression
	AliasMethods
	ComparisonMethods
	RangeMethods
	OrderedMethods
	//Returns the literal sql
	Literal() string
	//Arguments to be replaced within the sql
	Args() []interface{}
}

Expression for representing "literal" sql. 

L("col = 1") -> col = 1)
L("? = ?", I("col"), 1) -> "col" = 1

func Default 

func Default() LiteralExpression

Returns a literal for DEFAULT sql keyword

func L 

func L(val string, args ...interface{}) LiteralExpression
Example 
db := goqu.New("default", driver)
sql, _, _ := db.From("test").Where(goqu.L("a = 1")).ToSql()
fmt.Println(sql)

sql, _, _ = db.From("test").Where(goqu.L("a = 1 AND (b = ? OR ? = ?)", "a", goqu.I("c"), 0.01)).ToSql()
fmt.Println(sql)

sql, _, _ = db.From("test").Where(
	goqu.L(
		"(? AND ?) OR ?",
		goqu.I("a").Eq(1),
		goqu.I("b").Eq("b"),
		goqu.I("c").In([]string{"a", "b", "c"}),
	),
).ToSql()
fmt.Println(sql)

Output:

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

func Literal 

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

Alias for L

func Star 

func Star() LiteralExpression

Returns a literal for the '*' operator

type Logger 

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

type Op 

type Op map[string]interface{}

Used in tandem with the Ex map to create complex comparisons such as LIKE, GT, LT... See examples

Example 
db := goqu.New("default", driver)
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)

sql, _, _ = db.From("items").Where(goqu.Ex{
	"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, _, _ = db.From("items").Where(goqu.Ex{
	"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, _, _ = db.From("items").Where(goqu.Ex{
	"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)

sql, _, _ = db.From("items").Where(goqu.Ex{
	"col1": goqu.Op{"between": goqu.RangeVal{Start: 1, End: 10}},
	"col2": goqu.Op{"notbetween": goqu.RangeVal{Start: 1, End: 10}},
}).ToSql()
fmt.Println(sql)

Output:

SELECT * FROM "items" WHERE (("col1" != 'a') AND ("col3" IS NOT TRUE) AND ("col6" NOT IN ('a', 'b', 'c')))
SELECT * FROM "items" WHERE (("col1" > 1) AND ("col2" >= 1) AND ("col3" < 1) AND ("col4" <= 1))
SELECT * FROM "items" WHERE (("col1" LIKE 'a%') AND ("col2" NOT LIKE 'a%') AND ("col3" ILIKE 'a%') AND ("col4" NOT ILIKE 'a%'))
SELECT * FROM "items" WHERE (("col1" ~ '^(a|b)') AND ("col2" !~ '^(a|b)') AND ("col3" ~* '^(a|b)') AND ("col4" !~* '^(a|b)'))
SELECT * FROM "items" WHERE (("col1" BETWEEN 1 AND 10) AND ("col2" NOT BETWEEN 1 AND 10))
Example (WithMultipleKeys) 
db := goqu.New("default", driver)
sql, _, _ := db.From("items").Where(goqu.Ex{
	"col1": goqu.Op{"is": nil, "eq": 10},
}).ToSql()
fmt.Println(sql)

Output:

SELECT * FROM "items" WHERE (("col1" = 10) OR ("col1" IS NULL))

type OrderedExpression 

type OrderedExpression interface {
	Expression
	//The expression being sorted
	SortExpression() Expression
	//Sort direction (e.g. ASC, DESC)
	Direction() sort_direction
	//If the adapter supports it null sort type (e.g. NULLS FIRST, NULLS LAST)
	NullSortType() null_sort_type
	//Returns a new OrderedExpression with NullSortType set to NULLS_FIRST
	NullsFirst() OrderedExpression
	//Returns a new OrderedExpression with NullSortType set to NULLS_LAST
	NullsLast() OrderedExpression
}

An expression for specifying sort order and options

type OrderedMethods 

type OrderedMethods interface {
	//Creates an Ordered Expression for sql ASC order
	//   ds.Order(I("a").Asc()) //ORDER BY "a" ASC
	Asc() OrderedExpression
	//Creates an Ordered Expression for sql DESC order
	//   ds.Order(I("a").Desc()) //ORDER BY "a" DESC
	Desc() OrderedExpression
}

Interface that an expression should implement if it can be ORDERED.

Example 
db := goqu.New("default", driver)
sql, _, _ := db.From("test").Order(goqu.I("a").Asc()).ToSql()
fmt.Println(sql)

sql, _, _ = db.From("test").Order(goqu.I("a").Desc()).ToSql()
fmt.Println(sql)

sql, _, _ = db.From("test").Order(goqu.I("a").Desc().NullsFirst()).ToSql()
fmt.Println(sql)

sql, _, _ = db.From("test").Order(goqu.I("a").Desc().NullsLast()).ToSql()
fmt.Println(sql)

Output:

SELECT * FROM "test" ORDER BY "a" ASC
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

type RangeExpression 

type RangeExpression interface {
	Expression
	//Returns the operator for the expression
	Op() RangeOperation
	//The left hand side of the expression (e.g. I("a")
	Lhs() Expression
	//The right hand side of the expression could be a primitive value, dataset, or expression
	Rhs() RangeVal
}

type RangeMethods 

type RangeMethods interface {
	//Creates a Range expression for between comparisons
	//    I("col").Between(RangeVal{Start:1, End:10}) //("col" BETWEEN 1 AND 10)
	Between(RangeVal) RangeExpression
	//Creates a Range expression for between comparisons
	//    I("col").NotBetween(RangeVal{Start:1, End:10}) //("col" NOT BETWEEN 1 AND 10)
	NotBetween(RangeVal) RangeExpression
}
Example 
db := goqu.New("default", driver)
sql, _, _ := db.From("test").Where(goqu.I("name").Between(goqu.RangeVal{Start: "a", End: "b"})).ToSql()
fmt.Println(sql)

sql, _, _ = db.From("test").Where(goqu.I("name").NotBetween(goqu.RangeVal{Start: "a", End: "b"})).ToSql()
fmt.Println(sql)

sql, _, _ = db.From("test").Where(goqu.I("x").Between(goqu.RangeVal{Start: goqu.I("y"), End: goqu.I("z")})).ToSql()
fmt.Println(sql)

sql, _, _ = db.From("test").Where(goqu.I("x").NotBetween(goqu.RangeVal{Start: goqu.I("y"), End: goqu.I("z")})).ToSql()
fmt.Println(sql)

sql, _, _ = db.From("test").Where(goqu.L("(a + b)").Between(goqu.RangeVal{Start: 10, End: 100})).ToSql()
fmt.Println(sql)

sql, _, _ = db.From("test").Where(goqu.L("(a + b)").NotBetween(goqu.RangeVal{Start: 10, End: 100})).ToSql()
fmt.Println(sql)

Output:

SELECT * FROM "test" WHERE ("name" BETWEEN 'a' AND 'b')
SELECT * FROM "test" WHERE ("name" NOT BETWEEN 'a' AND 'b')
SELECT * FROM "test" WHERE ("x" BETWEEN "y" AND "z")
SELECT * FROM "test" WHERE ("x" NOT BETWEEN "y" AND "z")
SELECT * FROM "test" WHERE ((a + b) BETWEEN 10 AND 100)
SELECT * FROM "test" WHERE ((a + b) NOT BETWEEN 10 AND 100)

type RangeOperation 

type RangeOperation int
const (
	//BETWEEN
	BETWEEN_OP RangeOperation = iota
	//NOT BETWEEN
	NBETWEEN_OP
)

type RangeVal 

type RangeVal struct {
	Start interface{}
	End   interface{}
}

type Record 

type Record map[string]interface{}

Alternative to writing map[string]interface{}. Can be used for Inserts, Updates or Deletes

type SqlBuilder 

type SqlBuilder struct {
	bytes.Buffer
	//True if the sql should not be interpolated
	IsPrepared bool
	//Current Number of arguments, used by adapters that need positional placeholders
	CurrentArgPosition int
	// contains filtered or unexported fields
}

Builder that is composed of a bytes.Buffer. It is used internally and by adapters to build SQL statements

func NewSqlBuilder 

func NewSqlBuilder(isPrepared bool) *SqlBuilder

func (*SqlBuilder) ToSql 

func (me *SqlBuilder) ToSql() (string, []interface{})

Returns the sql string, and arguments.

func (*SqlBuilder) WriteArg 

func (me *SqlBuilder) WriteArg(i interface{})

Adds an argument to the builder, used when IsPrepared is false

type SqlExpression 

type SqlExpression interface {
	Expression
	ToSql() (string, []interface{}, error)
}

An Expression that generates its own sql (e.g Dataset)

type SqlFunctionExpression 

type SqlFunctionExpression interface {
	Expression
	AliasMethods
	RangeMethods
	ComparisonMethods
	//The function name
	Name() string
	//Arguments to be passed to the function
	Args() []interface{}
}

Expression for representing a SqlFunction(e.g. COUNT, SUM, MIN, MAX...)

func AVG 

func AVG(col interface{}) SqlFunctionExpression

Creates a new AVG sql function 

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

func COALESCE 

func COALESCE(vals ...interface{}) SqlFunctionExpression

Creates a new COALESCE sql function 

COALESCE(I("a"), "a") -> COALESCE("a", 'a')
COALESCE(I("a"), I("b"), nil) -> COALESCE("a", "b", NULL)

func COUNT 

func COUNT(col interface{}) SqlFunctionExpression

Creates a new COUNT sql function 

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

func DISTINCT 

func DISTINCT(col interface{}) SqlFunctionExpression

Creates a new DISTINCT sql function 

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

func FIRST 

func FIRST(col interface{}) SqlFunctionExpression

Creates a new FIRST sql function 

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

func Func 

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

Creates a new SqlFunctionExpression with the given name and arguments

func LAST 

func LAST(col interface{}) SqlFunctionExpression

Creates a new LAST sql function 

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

func MAX 

func MAX(col interface{}) SqlFunctionExpression

Creates a new MAX sql function 

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

func MIN 

func MIN(col interface{}) SqlFunctionExpression

Creates a new MIN sql function 

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

func SUM 

func SUM(col interface{}) SqlFunctionExpression

Creates a new SUM sql function 

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

type StringMethods 

type StringMethods interface {
	//Creates an Boolean expression for LIKE clauses
	//   ds.Where(I("a").Like("a%")) //("a" LIKE 'a%')
	Like(interface{}) BooleanExpression
	//Creates an Boolean expression for NOT LIKE clauses
	//   ds.Where(I("a").NotLike("a%")) //("a" NOT LIKE 'a%')
	NotLike(interface{}) BooleanExpression
	//Creates an Boolean expression for case insensitive LIKE clauses
	//   ds.Where(I("a").ILike("a%")) //("a" ILIKE 'a%')
	ILike(interface{}) BooleanExpression
	//Creates an Boolean expression for case insensitive NOT LIKE clauses
	//   ds.Where(I("a").NotILike("a%")) //("a" NOT ILIKE 'a%')
	NotILike(interface{}) BooleanExpression
}

Interface that an expression should implement if it can be used in string operations (e.g. LIKE, NOT LIKE...).

Example 
db := goqu.New("default", driver)
//using identifiers
sql, _, _ := db.From("test").Where(goqu.I("a").Like("%a%")).ToSql()
fmt.Println(sql)

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

sql, _, _ = db.From("test").Where(goqu.I("a").NotLike("%a%")).ToSql()
fmt.Println(sql)

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

sql, _, _ = db.From("test").Where(goqu.I("a").ILike("%a%")).ToSql()
fmt.Println(sql)

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

sql, _, _ = db.From("test").Where(goqu.I("a").NotILike("%a%")).ToSql()
fmt.Println(sql)

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

//using an Ex expression map
sql, _, _ = db.From("test").Where(goqu.Ex{
	"a": goqu.Op{"like": "%a%"},
	"b": goqu.Op{"like": regexp.MustCompile("(a|b)")},
	"c": goqu.Op{"iLike": "%a%"},
	"d": goqu.Op{"iLike": regexp.MustCompile("(a|b)")},
	"e": goqu.Op{"notlike": "%a%"},
	"f": goqu.Op{"notLike": regexp.MustCompile("(a|b)")},
	"g": goqu.Op{"notILike": "%a%"},
	"h": goqu.Op{"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" NOT 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 ILIKE '%a%')
SELECT * FROM "test" WHERE ("a" !~* '(a|b)')
SELECT * FROM "test" WHERE (("a" LIKE '%a%') AND ("b" ~ '(a|b)') AND ("c" ILIKE '%a%') AND ("d" ~* '(a|b)') AND ("e" NOT LIKE '%a%') AND ("f" !~ '(a|b)') AND ("g" NOT ILIKE '%a%') AND ("h" !~* '(a|b)'))

type TruncateOptions 

type TruncateOptions struct {
	//Set to true to add CASCADE to the TRUNCATE statement
	Cascade bool
	//Set to true to add RESTRICT to the TRUNCATE statement
	Restrict bool
	//Set to true to specify IDENTITY options, (e.g. RESTART, CONTINUE) to the TRUNCATE statement
	Identity string
}

Options to use when generating a TRUNCATE statement

type TxDatabase 

type TxDatabase struct {
	Dialect string
	Tx      *sql.Tx
	// contains filtered or unexported fields
}

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

func (*TxDatabase) Commit 

func (me *TxDatabase) Commit() error

COMMIT the transaction

func (*TxDatabase) Exec 

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

See Database#Exec

func (*TxDatabase) ExecContext 

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

See Database#ExecContext

func (*TxDatabase) From 

func (me *TxDatabase) From(cols ...interface{}) *Dataset

Creates a new Dataset for querying a Database.

func (*TxDatabase) Logger 

func (me *TxDatabase) Logger(logger Logger)

Sets the logger

func (*TxDatabase) Prepare 

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

See Database#Prepare

func (*TxDatabase) PrepareContext 

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

See Database#PrepareContext

func (*TxDatabase) Query 

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

See Database#Query

func (*TxDatabase) QueryContext 

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

See Database#QueryContext

func (*TxDatabase) QueryRow 

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

See Database#QueryRow

func (*TxDatabase) QueryRowContext 

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

See Database#QueryRowContext

func (*TxDatabase) Rollback 

func (me *TxDatabase) Rollback() error

ROLLBACK the transaction

func (*TxDatabase) ScanStruct 

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

See Database#ScanStruct

func (*TxDatabase) ScanStructContext 

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

See Database#ScanStructContext

func (*TxDatabase) ScanStructs 

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

See Database#ScanStructs

func (*TxDatabase) ScanStructsContext 

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

See Database#ScanStructsContext

func (*TxDatabase) ScanVal 

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

See Database#ScanVal

func (*TxDatabase) ScanValContext 

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

See Database#ScanValContext

func (*TxDatabase) ScanVals 

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

See Database#ScanVals

func (*TxDatabase) ScanValsContext 

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

See Database#ScanValsContext

func (*TxDatabase) Trace 

func (me *TxDatabase) Trace(op, sql string, args ...interface{})

func (*TxDatabase) Wrap 

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

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

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

type UpdateExpression 

type UpdateExpression interface {
	Col() IdentifierExpression
	Val() interface{}
}

Source Files

View all Source files

Directories

Path Synopsis
adapters
mysql
postgres
sqlite3

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.