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

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

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

Directories

Path Synopsis
adapters

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL