depiq

package module
v0.8.1 Latest Latest
Warning

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

 Go to latest Published: Oct 28, 2022 License: MIT Imports: 12 Imported by: 0

README

DEPIQ

depiq is an expressive SQL builder and executor

If you are upgrading from an older version please read the Migrating Between Versions docs.

Installation

If using go modules.

go get -u github.com/orn-id/depiq

If you are not using go modules...

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

go get -u github.com/orn-id/depiq
Migrating Between Versions

Features

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

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

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

Why?

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

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

Docs

  • Dialect - Introduction to different dialects (mysql, postgres, sqlite3, sqlserver etc)
  • Expressions - Introduction to depiq expressions and common examples.
  • Select Dataset - Docs and examples about creating and executing SELECT sql statements.
  • Insert Dataset - Docs and examples about creating and executing INSERT sql statements.
  • Update Dataset - Docs and examples about creating and executing UPDATE sql statements.
  • Delete Dataset - Docs and examples about creating and executing DELETE sql statements.
  • Prepared Statements - Docs about interpolation and prepared statements in depiq.
  • Database - Docs and examples of using a Database to execute queries in depiq
  • Working with time.Time - Docs on how to use alternate time locations.

Quick Examples

Select

See the select dataset docs for more in depth examples

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

Output:

SELECT * FROM "test"

sql, _, _ := depiq.From("test").Where(depiq.Ex{
	"d": []string{"a", "b", "c"},
}).ToSQL()
fmt.Println(sql)

Output:

SELECT * FROM "test" WHERE ("d" IN ('a', 'b', 'c'))
Insert

See the insert dataset docs for more in depth examples

ds := depiq.Insert("user").
	Cols("first_name", "last_name").
	Vals(
		depiq.Vals{"Greg", "Farley"},
		depiq.Vals{"Jimmy", "Stewart"},
		depiq.Vals{"Jeff", "Jeffers"},
	)
insertSQL, args, _ := ds.ToSQL()
fmt.Println(insertSQL, args)

Output:

INSERT INTO "user" ("first_name", "last_name") VALUES ('Greg', 'Farley'), ('Jimmy', 'Stewart'), ('Jeff', 'Jeffers') []

ds := depiq.Insert("user").Rows(
	depiq.Record{"first_name": "Greg", "last_name": "Farley"},
	depiq.Record{"first_name": "Jimmy", "last_name": "Stewart"},
	depiq.Record{"first_name": "Jeff", "last_name": "Jeffers"},
)
insertSQL, args, _ := ds.ToSQL()
fmt.Println(insertSQL, args)

Output:

INSERT INTO "user" ("first_name", "last_name") VALUES ('Greg', 'Farley'), ('Jimmy', 'Stewart'), ('Jeff', 'Jeffers') []

type User struct {
	FirstName string `db:"first_name"`
	LastName  string `db:"last_name"`
}
ds := depiq.Insert("user").Rows(
	User{FirstName: "Greg", LastName: "Farley"},
	User{FirstName: "Jimmy", LastName: "Stewart"},
	User{FirstName: "Jeff", LastName: "Jeffers"},
)
insertSQL, args, _ := ds.ToSQL()
fmt.Println(insertSQL, args)

Output:

INSERT INTO "user" ("first_name", "last_name") VALUES ('Greg', 'Farley'), ('Jimmy', 'Stewart'), ('Jeff', 'Jeffers') []

ds := depiq.Insert("user").Prepared(true).
	FromQuery(depiq.From("other_table"))
insertSQL, args, _ := ds.ToSQL()
fmt.Println(insertSQL, args)

Output:

INSERT INTO "user" SELECT * FROM "other_table" []

ds := depiq.Insert("user").Prepared(true).
	Cols("first_name", "last_name").
	FromQuery(depiq.From("other_table").Select("fn", "ln"))
insertSQL, args, _ := ds.ToSQL()
fmt.Println(insertSQL, args)

Output:

INSERT INTO "user" ("first_name", "last_name") SELECT "fn", "ln" FROM "other_table" []
Update

See the update dataset docs for more in depth examples

sql, args, _ := depiq.Update("items").Set(
	depiq.Record{"name": "Test", "address": "111 Test Addr"},
).ToSQL()
fmt.Println(sql, args)

Output:

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

type item struct {
	Address string `db:"address"`
	Name    string `db:"name" depiq:"skipupdate"`
}
sql, args, _ := depiq.Update("items").Set(
	item{Name: "Test", Address: "111 Test Addr"},
).ToSQL()
fmt.Println(sql, args)

Output:

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

sql, _, _ := depiq.Update("test").
	Set(depiq.Record{"foo": "bar"}).
	Where(depiq.Ex{
		"a": depiq.Op{"gt": 10}
	}).ToSQL()
fmt.Println(sql)

Output:

UPDATE "test" SET "foo"='bar' WHERE ("a" > 10)
Delete

See the delete dataset docs for more in depth examples

ds := depiq.Delete("items")

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

sql, _, _ := depiq.Delete("test").Where(depiq.Ex{
		"c": nil
	}).ToSQL()
fmt.Println(sql)

Output:

DELETE FROM "test" WHERE ("c" IS NULL)

Documentation

Index

Examples

Constants

View Source 
const (
	Wait       = exp.Wait
	NoWait     = exp.NoWait
	SkipLocked = exp.SkipLocked
)

Variables

View Source 
var (
	DefaultDialectOptions = sqlgen.DefaultDialectOptions
)
View Source 
var ErrBadFromArgument = errors.New("unsupported DeleteDataset#From argument, a string or identifier expression is required")
View Source 
var ErrQueryFactoryNotFoundError = errors.New(
	"unable to execute query did you use depiq.Database#From to create the dataset",
)
View Source 
var ErrUnsupportedIntoType = errors.New("unsupported table type, a string or identifier expression is required")
View Source 
var ErrUnsupportedUpdateTableType = errors.New("unsupported table type, a string or identifier expression is required")

Functions

func AVG 

func AVG(col interface{}) exp.SQLFunctionExpression

Creates a new AVG sql function 

AVG("a") -> AVG("a")
AVG(I("a")) -> AVG("a")
Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	ds := depiq.From("test").Select(depiq.AVG("col"))
	sql, args, _ := ds.ToSQL()
	fmt.Println(sql, args)

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

Output:

SELECT AVG("col") FROM "test" []
SELECT AVG("col") FROM "test" []
Example (As) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	sql, _, _ := depiq.From("test").Select(depiq.AVG("a").As("a")).ToSQL()
	fmt.Println(sql)

}

Output:

SELECT AVG("a") AS "a" FROM "test"
Example (HavingClause) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	ds := depiq.
		From("test").
		Select(depiq.AVG("a").As("avg")).
		GroupBy("a").
		Having(depiq.AVG("a").Gt(10))

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

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

}

Output:

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

func All 

func All(val interface{}) exp.SQLFunctionExpression

Create a new ALL comparison

Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

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

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

Output:

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

func And 

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

A list of expressions that should be ANDed together 

And(I("a").Eq(10), I("b").Eq(11)) //(("a" = 10) AND ("b" = 11))
Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	ds := depiq.From("test").Where(
		depiq.And(
			depiq.C("col").Gt(10),
			depiq.C("col").Lt(20),
		),
	)
	sql, args, _ := ds.ToSQL()
	fmt.Println(sql, args)

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

}

Output:

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

You can use ExOr inside of And expression lists. 

package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

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

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

}

Output:

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

You can use And with Or to create more complex queries 

package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

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

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

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

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

}

Output:

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

func Any 

func Any(val interface{}) exp.SQLFunctionExpression

Create a new ANY comparison

Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

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

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

Output:

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

func C 

func C(col string) exp.IdentifierExpression

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

C("column") -> "column" //A Column
C("column").Table("table") -> "table"."column" //A Column and table
C("column").Table("table").Schema("schema") //Schema table and column
C("*") //Also handles the * operator
Example 
package main

import (
	"fmt"
	"regexp"

	"github.com/orn-id/depiq"
)

func main() {
	sql, args, _ := depiq.From("test").
		Select(depiq.C("*")).
		ToSQL()
	fmt.Println(sql, args)

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

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

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

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

}

Output:

SELECT * FROM "test" []
SELECT "col1" FROM "test" []
SELECT * FROM "test" WHERE (("col1" = 10) AND ("col2" IN (1, 2, 3, 4)) AND ("col3" ~ '^[ab]') AND ("col4" IS NULL)) []
SELECT * FROM "test" WHERE (("col1" = ?) AND ("col2" IN (?, ?, ?, ?)) AND ("col3" ~ ?) AND ("col4" IS NULL)) [10 1 2 3 4 ^[ab]]
Example (As) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	sql, _, _ := depiq.From("test").Select(depiq.C("a").As("as_a")).ToSQL()
	fmt.Println(sql)

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

}

Output:

SELECT "a" AS "as_a" FROM "test"
SELECT "a" AS "as_a" FROM "test"
Example (BetweenComparisons) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	ds := depiq.From("test").Where(
		depiq.C("a").Between(depiq.Range(1, 10)),
	)
	sql, args, _ := ds.ToSQL()
	fmt.Println(sql, args)

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

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

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

}

Output:

SELECT * FROM "test" WHERE ("a" BETWEEN 1 AND 10) []
SELECT * FROM "test" WHERE ("a" BETWEEN ? AND ?) [1 10]
SELECT * FROM "test" WHERE ("a" NOT BETWEEN 1 AND 10) []
SELECT * FROM "test" WHERE ("a" NOT BETWEEN ? AND ?) [1 10]
Example (Cast) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	sql, _, _ := depiq.From("test").
		Select(depiq.C("json1").Cast("TEXT").As("json_text")).
		ToSQL()
	fmt.Println(sql)

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

Output:

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

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	// used from an identifier
	sql, _, _ := depiq.From("test").Where(depiq.C("a").Eq(10)).ToSQL()
	fmt.Println(sql)

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

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

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

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

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

}

Output:

SELECT * FROM "test" WHERE ("a" = 10)
SELECT * FROM "test" WHERE ("a" != 10)
SELECT * FROM "test" WHERE ("a" > 10)
SELECT * FROM "test" WHERE ("a" >= 10)
SELECT * FROM "test" WHERE ("a" < 10)
SELECT * FROM "test" WHERE ("a" <= 10)
Example (InOperators) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	// using identifiers
	sql, _, _ := depiq.From("test").Where(depiq.C("a").In("a", "b", "c")).ToSQL()
	fmt.Println(sql)
	// with a slice
	sql, _, _ = depiq.From("test").Where(depiq.C("a").In([]string{"a", "b", "c"})).ToSQL()
	fmt.Println(sql)

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

}

Output:

SELECT * FROM "test" WHERE ("a" IN ('a', 'b', 'c'))
SELECT * FROM "test" WHERE ("a" IN ('a', 'b', 'c'))
SELECT * FROM "test" WHERE ("a" NOT IN ('a', 'b', 'c'))
SELECT * FROM "test" WHERE ("a" NOT IN ('a', 'b', 'c'))
Example (IsComparisons) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	sql, args, _ := depiq.From("test").Where(depiq.C("a").Is(nil)).ToSQL()
	fmt.Println(sql, args)

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

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

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

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

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

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

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

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

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

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

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

}

Output:

SELECT * FROM "test" WHERE ("a" IS NULL) []
SELECT * FROM "test" WHERE ("a" IS TRUE) []
SELECT * FROM "test" WHERE ("a" IS FALSE) []
SELECT * FROM "test" WHERE ("a" IS NULL) []
SELECT * FROM "test" WHERE ("a" IS TRUE) []
SELECT * FROM "test" WHERE ("a" IS FALSE) []
SELECT * FROM "test" WHERE ("a" IS NOT NULL) []
SELECT * FROM "test" WHERE ("a" IS NOT TRUE) []
SELECT * FROM "test" WHERE ("a" IS NOT FALSE) []
SELECT * FROM "test" WHERE ("a" IS NOT NULL) []
SELECT * FROM "test" WHERE ("a" IS NOT TRUE) []
SELECT * FROM "test" WHERE ("a" IS NOT FALSE) []
Example (LikeComparisons) 
package main

import (
	"fmt"
	"regexp"

	"github.com/orn-id/depiq"
)

func main() {
	// using identifiers
	sql, _, _ := depiq.From("test").Where(depiq.C("a").Like("%a%")).ToSQL()
	fmt.Println(sql)

	sql, _, _ = depiq.From("test").Where(depiq.C("a").Like(regexp.MustCompile(`[ab]`))).ToSQL()
	fmt.Println(sql)

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

	sql, _, _ = depiq.From("test").Where(depiq.C("a").ILike(regexp.MustCompile("[ab]"))).ToSQL()
	fmt.Println(sql)

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

	sql, _, _ = depiq.From("test").Where(depiq.C("a").NotLike(regexp.MustCompile("[ab]"))).ToSQL()
	fmt.Println(sql)

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

	sql, _, _ = depiq.From("test").Where(depiq.C("a").NotILike(regexp.MustCompile(`[ab]`))).ToSQL()
	fmt.Println(sql)

}

Output:

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

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	sql, args, _ := depiq.From("test").Order(depiq.C("a").Asc()).ToSQL()
	fmt.Println(sql, args)

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

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

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

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

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

}

Output:

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

func COALESCE 

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

Creates a new COALESCE sql function 

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

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	ds := depiq.From("test").Select(
		depiq.COALESCE(depiq.C("a"), "a"),
		depiq.COALESCE(depiq.C("a"), depiq.C("b"), nil),
	)
	sql, args, _ := ds.ToSQL()
	fmt.Println(sql, args)

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

Output:

SELECT COALESCE("a", 'a'), COALESCE("a", "b", NULL) FROM "test" []
SELECT COALESCE("a", ?), COALESCE("a", "b", ?) FROM "test" [a <nil>]
Example (As) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	sql, _, _ := depiq.From("test").Select(depiq.COALESCE(depiq.C("a"), "a").As("a")).ToSQL()
	fmt.Println(sql)

}

Output:

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

func COUNT 

func COUNT(col interface{}) exp.SQLFunctionExpression

Creates a new COUNT sql function 

COUNT("a") -> COUNT("a")
COUNT("*") -> COUNT("*")
COUNT(I("a")) -> COUNT("a")
Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	ds := depiq.From("test").Select(depiq.COUNT("*"))
	sql, args, _ := ds.ToSQL()
	fmt.Println(sql, args)

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

Output:

SELECT COUNT(*) FROM "test" []
SELECT COUNT(*) FROM "test" []
Example (As) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	sql, _, _ := depiq.From("test").Select(depiq.COUNT("*").As("count")).ToSQL()
	fmt.Println(sql)

}

Output:

SELECT COUNT(*) AS "count" FROM "test"
Example (HavingClause) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	ds := depiq.
		From("test").
		Select(depiq.COUNT("a").As("COUNT")).
		GroupBy("a").
		Having(depiq.COUNT("a").Gt(10))

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

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

}

Output:

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

func CUME_DIST 

func CUME_DIST() exp.SQLFunctionExpression

func Case 

func Case() exp.CaseExpression
Example (SearchElse) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

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

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

Output:

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

import (
	"fmt"

	"github.com/orn-id/depiq"
)

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

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

Output:

SELECT "col", CASE "str" WHEN 'foo' THEN 'FOO' WHEN 'bar' THEN 'BAR' END AS "foo_bar_upper" FROM "test" []
SELECT "col", CASE "str" WHEN ? THEN ? WHEN ? THEN ? END AS "foo_bar_upper" FROM "test" [foo FOO bar BAR]
Example (ValueElse) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

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

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

Output:

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

func Cast 

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

Creates a new Casted expression 

Cast(I("a"), "NUMERIC") -> CAST("a" AS NUMERIC)
Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	sql, _, _ := depiq.From("test").
		Select(depiq.Cast(depiq.C("json1"), "TEXT").As("json_text")).
		ToSQL()
	fmt.Println(sql)

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

Output:

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

func DENSE_RANK 

func DENSE_RANK() exp.SQLFunctionExpression

func DISTINCT 

func DISTINCT(col interface{}) exp.SQLFunctionExpression

Creates a new DISTINCT sql function 

DISTINCT("a") -> DISTINCT("a")
DISTINCT(I("a")) -> DISTINCT("a")
Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	ds := depiq.From("test").Select(depiq.DISTINCT("col"))
	sql, args, _ := ds.ToSQL()
	fmt.Println(sql, args)

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

Output:

SELECT DISTINCT("col") FROM "test" []
SELECT DISTINCT("col") FROM "test" []
Example (As) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	sql, _, _ := depiq.From("test").Select(depiq.DISTINCT("a").As("distinct_a")).ToSQL()
	fmt.Println(sql)

}

Output:

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

func Default 

func Default() exp.LiteralExpression

Returns a literal for DEFAULT sql keyword

Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	ds := depiq.Insert("items")

	sql, args, _ := ds.Rows(depiq.Record{
		"name":    depiq.Default(),
		"address": depiq.Default(),
	}).ToSQL()
	fmt.Println(sql, args)

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

}

Output:

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

func DeregisterDialect 

func DeregisterDialect(name string)

func DoNothing 

func DoNothing() exp.ConflictExpression

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

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

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	ds := depiq.Insert("items")

	sql, args, _ := ds.Rows(depiq.Record{
		"address": "111 Address",
		"name":    "bob",
	}).OnConflict(depiq.DoNothing()).ToSQL()
	fmt.Println(sql, args)

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

}

Output:

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

func DoUpdate 

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

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

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

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	ds := depiq.Insert("items")

	sql, args, _ := ds.
		Rows(depiq.Record{"address": "111 Address"}).
		OnConflict(depiq.DoUpdate("address", depiq.C("address").Set(depiq.I("excluded.address")))).
		ToSQL()
	fmt.Println(sql, args)

	sql, args, _ = ds.Prepared(true).
		Rows(depiq.Record{"address": "111 Address"}).
		OnConflict(depiq.DoUpdate("address", depiq.C("address").Set(depiq.I("excluded.address")))).
		ToSQL()
	fmt.Println(sql, args)

}

Output:

INSERT INTO "items" ("address") VALUES ('111 Address') ON CONFLICT (address) DO UPDATE SET "address"="excluded"."address" []
INSERT INTO "items" ("address") VALUES (?) ON CONFLICT (address) DO UPDATE SET "address"="excluded"."address" [111 Address]
Example (Where) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	ds := depiq.Insert("items")

	sql, args, _ := ds.
		Rows(depiq.Record{"address": "111 Address"}).
		OnConflict(depiq.DoUpdate(
			"address",
			depiq.C("address").Set(depiq.I("excluded.address"))).Where(depiq.I("items.updated").IsNull()),
		).
		ToSQL()
	fmt.Println(sql, args)

	sql, args, _ = ds.Prepared(true).
		Rows(depiq.Record{"address": "111 Address"}).
		OnConflict(depiq.DoUpdate(
			"address",
			depiq.C("address").Set(depiq.I("excluded.address"))).Where(depiq.I("items.updated").IsNull()),
		).
		ToSQL()
	fmt.Println(sql, args)

}

Output:

INSERT INTO "items" ("address") VALUES ('111 Address') ON CONFLICT (address) DO UPDATE SET "address"="excluded"."address" WHERE ("items"."updated" IS NULL) []
INSERT INTO "items" ("address") VALUES (?) ON CONFLICT (address) DO UPDATE SET "address"="excluded"."address" WHERE ("items"."updated" IS NULL) [111 Address]

func FIRST 

func FIRST(col interface{}) exp.SQLFunctionExpression

Creates a new FIRST sql function 

FIRST("a") -> FIRST("a")
FIRST(I("a")) -> FIRST("a")
Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	ds := depiq.From("test").Select(depiq.FIRST("col"))
	sql, args, _ := ds.ToSQL()
	fmt.Println(sql, args)

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

Output:

SELECT FIRST("col") FROM "test" []
SELECT FIRST("col") FROM "test" []
Example (As) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	sql, _, _ := depiq.From("test").Select(depiq.FIRST("a").As("a")).ToSQL()
	fmt.Println(sql)

}

Output:

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

func FIRST_VALUE 

func FIRST_VALUE(val interface{}) exp.SQLFunctionExpression

func Func 

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

Creates a new SQLFunctionExpression with the given name and arguments

Example

This example shows how to create custom SQL Functions 

package main

import (
	"fmt"

	"github.com/orn-id/depiq"
	"github.com/orn-id/depiq/exp"
)

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

}

Output:

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

func I 

func I(ident string) exp.IdentifierExpression

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

The identifier will be split by '.'

Table and Column example 

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

Schema table and column 

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

Table with star 

I("table.*") -> "table".*
Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	ds := depiq.From("test").
		Select(
			depiq.I("my_schema.table.col1"),
			depiq.I("table.col2"),
			depiq.I("col3"),
		)

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

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

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

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

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

}

Output:

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

func L 

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

Creates a new SQL literal with the provided arguments. 

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

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

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

Literals can also contain placeholders for other expressions 

L("(? AND ?) OR (?)", I("a").Eq(1), I("b").Eq("b"), I("c").In([]string{"a", "b", "c"}))
Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	ds := depiq.From("test").Where(
		// literal with no args
		depiq.L(`"col"::TEXT = ""other_col"::text`),
		// literal with args they will be interpolated into the sql by default
		depiq.L("col IN (?, ?, ?)", "a", "b", "c"),
	)

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

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

Output:

SELECT * FROM "test" WHERE ("col"::TEXT = ""other_col"::text AND col IN ('a', 'b', 'c')) []
SELECT * FROM "test" WHERE ("col"::TEXT = ""other_col"::text AND col IN (?, ?, ?)) [a b c]
Example (As) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	sql, _, _ := depiq.From("test").Select(depiq.L("json_col->>'totalAmount'").As("total_amount")).ToSQL()
	fmt.Println(sql)

}

Output:

SELECT json_col->>'totalAmount' AS "total_amount" FROM "test"
Example (BetweenComparisons) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	ds := depiq.From("test").Where(
		depiq.L("(a + b)").Between(depiq.Range(1, 10)),
	)
	sql, args, _ := ds.ToSQL()
	fmt.Println(sql, args)

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

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

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

}

Output:

SELECT * FROM "test" WHERE ((a + b) BETWEEN 1 AND 10) []
SELECT * FROM "test" WHERE ((a + b) BETWEEN ? AND ?) [1 10]
SELECT * FROM "test" WHERE ((a + b) NOT BETWEEN 1 AND 10) []
SELECT * FROM "test" WHERE ((a + b) NOT BETWEEN ? AND ?) [1 10]
Example (Comparisons) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	// used from a literal expression
	sql, _, _ := depiq.From("test").Where(depiq.L("(a + b)").Eq(10)).ToSQL()
	fmt.Println(sql)

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

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

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

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

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

}

Output:

SELECT * FROM "test" WHERE ((a + b) = 10)
SELECT * FROM "test" WHERE ((a + b) != 10)
SELECT * FROM "test" WHERE ((a + b) > 10)
SELECT * FROM "test" WHERE ((a + b) >= 10)
SELECT * FROM "test" WHERE ((a + b) < 10)
SELECT * FROM "test" WHERE ((a + b) <= 10)
Example (InOperators) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	// using identifiers
	sql, _, _ := depiq.From("test").Where(depiq.L("json_col->>'val'").In("a", "b", "c")).ToSQL()
	fmt.Println(sql)
	// with a slice
	sql, _, _ = depiq.From("test").Where(depiq.L("json_col->>'val'").In([]string{"a", "b", "c"})).ToSQL()
	fmt.Println(sql)

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

}

Output:

SELECT * FROM "test" WHERE (json_col->>'val' IN ('a', 'b', 'c'))
SELECT * FROM "test" WHERE (json_col->>'val' IN ('a', 'b', 'c'))
SELECT * FROM "test" WHERE (json_col->>'val' NOT IN ('a', 'b', 'c'))
SELECT * FROM "test" WHERE (json_col->>'val' NOT IN ('a', 'b', 'c'))
Example (IsComparisons) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	sql, args, _ := depiq.From("test").Where(depiq.L("a").Is(nil)).ToSQL()
	fmt.Println(sql, args)

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

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

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

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

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

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

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

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

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

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

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

}

Output:

SELECT * FROM "test" WHERE (a IS NULL) []
SELECT * FROM "test" WHERE (a IS TRUE) []
SELECT * FROM "test" WHERE (a IS FALSE) []
SELECT * FROM "test" WHERE (a IS NULL) []
SELECT * FROM "test" WHERE (a IS TRUE) []
SELECT * FROM "test" WHERE (a IS FALSE) []
SELECT * FROM "test" WHERE (a IS NOT NULL) []
SELECT * FROM "test" WHERE (a IS NOT TRUE) []
SELECT * FROM "test" WHERE (a IS NOT FALSE) []
SELECT * FROM "test" WHERE (a IS NOT NULL) []
SELECT * FROM "test" WHERE (a IS NOT TRUE) []
SELECT * FROM "test" WHERE (a IS NOT FALSE) []
Example (LikeComparisons) 
package main

import (
	"fmt"
	"regexp"

	"github.com/orn-id/depiq"
)

func main() {
	// using identifiers
	sql, _, _ := depiq.From("test").Where(depiq.L("(a::text || 'bar')").Like("%a%")).ToSQL()
	fmt.Println(sql)

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

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

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

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

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

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

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

}

Output:

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

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	ds := depiq.From("test").Where(
		depiq.L(
			"(? AND ?) OR ?",
			depiq.C("a").Eq(1),
			depiq.C("b").Eq("b"),
			depiq.C("c").In([]string{"a", "b", "c"}),
		),
	)

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

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

Output:

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

func LAST 

func LAST(col interface{}) exp.SQLFunctionExpression

Creates a new LAST sql function 

LAST("a") -> LAST("a")
LAST(I("a")) -> LAST("a")
Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	ds := depiq.From("test").Select(depiq.LAST("col"))
	sql, args, _ := ds.ToSQL()
	fmt.Println(sql, args)

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

Output:

SELECT LAST("col") FROM "test" []
SELECT LAST("col") FROM "test" []
Example (As) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	sql, _, _ := depiq.From("test").Select(depiq.LAST("a").As("a")).ToSQL()
	fmt.Println(sql)

}

Output:

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

func LAST_VALUE 

func LAST_VALUE(val interface{}) exp.SQLFunctionExpression

func Lateral 

func Lateral(table exp.AppendableExpression) exp.LateralExpression
Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

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

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

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

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

}

Output:

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

import (
	"fmt"

	"github.com/orn-id/depiq"
)

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

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

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

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

}

Output:

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

func Literal 

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

Alias for depiq.L

func MAX 

func MAX(col interface{}) exp.SQLFunctionExpression

Creates a new MAX sql function 

MAX("a") -> MAX("a")
MAX(I("a")) -> MAX("a")
Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	ds := depiq.From("test").Select(depiq.MAX("col"))
	sql, args, _ := ds.ToSQL()
	fmt.Println(sql, args)

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

Output:

SELECT MAX("col") FROM "test" []
SELECT MAX("col") FROM "test" []
Example (As) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	sql, _, _ := depiq.From("test").Select(depiq.MAX("a").As("a")).ToSQL()
	fmt.Println(sql)

}

Output:

SELECT MAX("a") AS "a" FROM "test"
Example (HavingClause) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	ds := depiq.
		From("test").
		Select(depiq.MAX("a").As("MAX")).
		GroupBy("a").
		Having(depiq.MAX("a").Gt(10))

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

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

}

Output:

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

func MIN 

func MIN(col interface{}) exp.SQLFunctionExpression

Creates a new MIN sql function 

MIN("a") -> MIN("a")
MIN(I("a")) -> MIN("a")
Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	ds := depiq.From("test").Select(depiq.MIN("col"))
	sql, args, _ := ds.ToSQL()
	fmt.Println(sql, args)

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

Output:

SELECT MIN("col") FROM "test" []
SELECT MIN("col") FROM "test" []
Example (As) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	sql, _, _ := depiq.From("test").Select(depiq.MIN("a").As("a")).ToSQL()
	fmt.Println(sql)

}

Output:

SELECT MIN("a") AS "a" FROM "test"
Example (HavingClause) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	ds := depiq.
		From("test").
		Select(depiq.MIN("a").As("MIN")).
		GroupBy("a").
		Having(depiq.MIN("a").Gt(10))

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

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

}

Output:

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

func NTH_VALUE 

func NTH_VALUE(val interface{}, nth int) exp.SQLFunctionExpression

func NTILE 

func NTILE(n int) exp.SQLFunctionExpression

func On 

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

Creates a new ON clause to be used within a join 

ds.Join(depiq.T("my_table"), depiq.On(
   depiq.I("my_table.fkey").Eq(depiq.I("other_table.id")),
))
Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	ds := depiq.From("test").Join(
		depiq.T("my_table"),
		depiq.On(depiq.I("my_table.fkey").Eq(depiq.I("other_table.id"))),
	)

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

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

Output:

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

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	ds := depiq.From("test").Join(
		depiq.T("my_table"),
		depiq.On(depiq.Ex{"my_table.fkey": depiq.I("other_table.id")}),
	)

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

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

Output:

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

func Or 

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

A list of expressions that should be ORed together 

Or(I("a").Eq(10), I("b").Eq(11)) //(("a" = 10) OR ("b" = 11))
Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	ds := depiq.From("test").Where(
		depiq.Or(
			depiq.C("col").Eq(10),
			depiq.C("col").Eq(20),
		),
	)
	sql, args, _ := ds.ToSQL()
	fmt.Println(sql, args)

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

}

Output:

SELECT * FROM "test" WHERE (("col" = 10) OR ("col" = 20)) []
SELECT * FROM "test" WHERE (("col" = ?) OR ("col" = ?)) [10 20]
Example (WithAnd) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	ds := depiq.From("items").Where(
		depiq.Or(
			depiq.C("a").Gt(10),
			depiq.And(
				depiq.C("b").Eq(100),
				depiq.C("c").Neq("test"),
			),
		),
	)
	sql, args, _ := ds.ToSQL()
	fmt.Println(sql, args)

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

Output:

SELECT * FROM "items" WHERE (("a" > 10) OR (("b" = 100) AND ("c" != 'test'))) []
SELECT * FROM "items" WHERE (("a" > ?) OR (("b" = ?) AND ("c" != ?))) [10 100 test]
Example (WithExMap) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	ds := depiq.From("test").Where(
		depiq.Or(
			// Ex will be anded together
			depiq.Ex{
				"col1": 1,
				"col2": true,
			},
			depiq.Ex{
				"col3": nil,
				"col4": "foo",
			},
		),
	)
	sql, args, _ := ds.ToSQL()
	fmt.Println(sql, args)

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

}

Output:

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

func PERCENT_RANK 

func PERCENT_RANK() exp.SQLFunctionExpression

func RANK 

func RANK() exp.SQLFunctionExpression

func ROW_NUMBER 

func ROW_NUMBER() exp.SQLFunctionExpression

func Range 

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

Creates a new Range to be used with a Between expression 

exp.C("col").Between(exp.Range(1, 10))
Example (Identifiers) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	ds := depiq.From("test").Where(
		depiq.C("col1").Between(depiq.Range(depiq.C("col2"), depiq.C("col3"))),
	)
	sql, args, _ := ds.ToSQL()
	fmt.Println(sql, args)

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

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

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

}

Output:

SELECT * FROM "test" WHERE ("col1" BETWEEN "col2" AND "col3") []
SELECT * FROM "test" WHERE ("col1" BETWEEN "col2" AND "col3") []
SELECT * FROM "test" WHERE ("col1" NOT BETWEEN "col2" AND "col3") []
SELECT * FROM "test" WHERE ("col1" NOT BETWEEN "col2" AND "col3") []
Example (Numbers) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	ds := depiq.From("test").Where(
		depiq.C("col").Between(depiq.Range(1, 10)),
	)
	sql, args, _ := ds.ToSQL()
	fmt.Println(sql, args)

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

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

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

}

Output:

SELECT * FROM "test" WHERE ("col" BETWEEN 1 AND 10) []
SELECT * FROM "test" WHERE ("col" BETWEEN ? AND ?) [1 10]
SELECT * FROM "test" WHERE ("col" NOT BETWEEN 1 AND 10) []
SELECT * FROM "test" WHERE ("col" NOT BETWEEN ? AND ?) [1 10]
Example (Strings) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	ds := depiq.From("test").Where(
		depiq.C("col").Between(depiq.Range("a", "z")),
	)
	sql, args, _ := ds.ToSQL()
	fmt.Println(sql, args)

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

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

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

}

Output:

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

func RegisterDialect 

func RegisterDialect(name string, do *SQLDialectOptions)
Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	opts := depiq.DefaultDialectOptions()
	opts.QuoteRune = '`'
	depiq.RegisterDialect("custom-dialect", opts)

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

	ds := dialect.From("test")

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

}

Output:

SELECT * FROM `test` []

func S 

func S(schema string) exp.IdentifierExpression

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

S("schema") -> "schema" //A Schema
S("schema").Table("table") -> "schema"."table" //A Schema and table
S("schema").Table("table").Col("col") //Schema table and column
S("schema").Table("table").Col("*") //Schema table and all columns
Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	s := depiq.S("test_schema")
	t := s.Table("test")
	sql, args, _ := depiq.
		From(t).
		Select(
			t.Col("col1"),
			t.Col("col2"),
			t.Col("col3"),
		).
		ToSQL()
	fmt.Println(sql, args)

}

Output:

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

func SUM 

func SUM(col interface{}) exp.SQLFunctionExpression

Creates a new SUM sql function 

SUM("a") -> SUM("a")
SUM(I("a")) -> SUM("a")
Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	ds := depiq.From("test").Select(depiq.SUM("col"))
	sql, args, _ := ds.ToSQL()
	fmt.Println(sql, args)

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

Output:

SELECT SUM("col") FROM "test" []
SELECT SUM("col") FROM "test" []
Example (As) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	sql, _, _ := depiq.From("test").Select(depiq.SUM("a").As("a")).ToSQL()
	fmt.Println(sql)

}

Output:

SELECT SUM("a") AS "a" FROM "test"
Example (HavingClause) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	ds := depiq.
		From("test").
		Select(depiq.SUM("a").As("SUM")).
		GroupBy("a").
		Having(depiq.SUM("a").Gt(10))

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

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

}

Output:

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

func SetColumnRenameFunction 

func SetColumnRenameFunction(renameFunc func(string) string)

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

func SetDefaultPrepared 

func SetDefaultPrepared(prepared bool)

SetDefaultPrepared controls the default Prepared state of all datasets. If set to true, any new dataset will use prepared queries by default.

func SetIgnoreUntaggedFields 

func SetIgnoreUntaggedFields(ignore bool)

Set the behavior when encountering struct fields that do not have a db tag. By default this is false; if set to true any field without a db tag will not be targeted by Select or Scan operations.

func SetTimeLocation 

func SetTimeLocation(loc *time.Location)

Set the location to use when interpolating time.Time instances. See https://golang.org/pkg/time/#LoadLocation NOTE: This has no effect when using prepared statements.

Example 
package main

import (
	"fmt"
	"time"

	"github.com/orn-id/depiq"

	_ "github.com/orn-id/depiq/dialect/mysql"
	_ "github.com/orn-id/depiq/dialect/postgres"
	_ "github.com/orn-id/depiq/dialect/sqlite3"
)

func main() {
	loc, err := time.LoadLocation("Asia/Shanghai")
	if err != nil {
		panic(err)
	}

	created, err := time.Parse(time.RFC3339, "2019-10-01T15:01:00Z")
	if err != nil {
		panic(err)
	}

	// use original time with tz info
	depiq.SetTimeLocation(loc)
	ds := depiq.Insert("test").Rows(depiq.Record{
		"address": "111 Address",
		"name":    "Bob Yukon",
		"created": created,
	})
	sql, _, _ := ds.ToSQL()
	fmt.Println(sql)

	// convert time to UTC
	depiq.SetTimeLocation(time.UTC)
	sql, _, _ = ds.ToSQL()
	fmt.Println(sql)

}

Output:

INSERT INTO "test" ("address", "created", "name") VALUES ('111 Address', '2019-10-01T23:01:00+08:00', 'Bob Yukon')
INSERT INTO "test" ("address", "created", "name") VALUES ('111 Address', '2019-10-01T15:01:00Z', 'Bob Yukon')

func Star 

func Star() exp.LiteralExpression

Creates a literal *

Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	ds := depiq.From("test").Select(depiq.Star())

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

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

}

Output:

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

func T 

func T(table string) exp.IdentifierExpression

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

T("table") -> "table" //A Column
T("table").Col("col") -> "table"."column" //A Column and table
T("table").Schema("schema").Col("col) -> "schema"."table"."column"  //Schema table and column
T("table").Schema("schema").Col("*") -> "schema"."table".*  //Also handles the * operator
Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	t := depiq.T("test")
	sql, args, _ := depiq.
		From(t).
		Select(
			t.Col("col1"),
			t.Col("col2"),
			t.Col("col3"),
		).
		ToSQL()
	fmt.Println(sql, args)

}

Output:

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

func Using 

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

Creates a new USING clause to be used within a join 

ds.Join(depiq.T("my_table"), depiq.Using("fkey"))
Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	ds := depiq.From("test").Join(
		depiq.T("my_table"),
		depiq.Using("fkey"),
	)

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

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

Output:

SELECT * FROM "test" INNER JOIN "my_table" USING ("fkey") []
SELECT * FROM "test" INNER JOIN "my_table" USING ("fkey") []
Example (WithIdentifier) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	ds := depiq.From("test").Join(
		depiq.T("my_table"),
		depiq.Using(depiq.C("fkey")),
	)

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

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

Output:

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

func V 

func V(val interface{}) exp.LiteralExpression

Create a new SQL value ( alias for depiq.L("?", val) ). The prrimary use case for this would be in selects. See examples.

Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	ds := depiq.From("user").Select(
		depiq.V(true).As("is_verified"),
		depiq.V(1.2).As("version"),
		"first_name",
		"last_name",
	)

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

	ds = depiq.From("user").Where(depiq.V(1).Neq(1))
	sql, args, _ = ds.ToSQL()
	fmt.Println(sql, args)

}

Output:

SELECT TRUE AS "is_verified", 1.2 AS "version", "first_name", "last_name" FROM "user" []
SELECT * FROM "user" WHERE (1 != 1) []
Example (Prepared) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	ds := depiq.From("user").Select(
		depiq.V(true).As("is_verified"),
		depiq.V(1.2).As("version"),
		"first_name",
		"last_name",
	)

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

	ds = depiq.From("user").Where(depiq.V(1).Neq(1))

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

}

Output:

SELECT ? AS "is_verified", ? AS "version", "first_name", "last_name" FROM "user" [true 1.2]
SELECT * FROM "user" WHERE (? != ?) [1 1]

func W 

func W(ws ...string) exp.WindowExpression

Create a new WINDOW clause 

W() -> ()
W().PartitionBy("a") -> (PARTITION BY "a")
W().PartitionBy("a").OrderBy("b") -> (PARTITION BY "a" ORDER BY "b")
W().PartitionBy("a").OrderBy("b").Inherit("w1") -> ("w1" PARTITION BY "a" ORDER BY "b")
W().PartitionBy("a").OrderBy(I("b").Desc()).Inherit("w1") -> ("w1" PARTITION BY "a" ORDER BY "b" DESC)
W("w") -> "w" AS ()
W("w", "w1") -> "w" AS ("w1")
W("w").Inherit("w1") -> "w" AS ("w1")
W("w").PartitionBy("a") -> "w" AS (PARTITION BY "a")
W("w", "w1").PartitionBy("a") -> "w" AS ("w1" PARTITION BY "a")
W("w", "w1").PartitionBy("a").OrderBy("b") -> "w" AS ("w1" PARTITION BY "a" ORDER BY "b")
Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	ds := depiq.From("test").
		Select(depiq.ROW_NUMBER().Over(depiq.W().PartitionBy("a").OrderBy(depiq.I("b").Asc())))
	query, args, _ := ds.ToSQL()
	fmt.Println(query, args)

	ds = depiq.From("test").
		Select(depiq.ROW_NUMBER().OverName(depiq.I("w"))).
		Window(depiq.W("w").PartitionBy("a").OrderBy(depiq.I("b").Asc()))
	query, args, _ = ds.ToSQL()
	fmt.Println(query, args)

	ds = depiq.From("test").
		Select(depiq.ROW_NUMBER().OverName(depiq.I("w1"))).
		Window(
			depiq.W("w1").PartitionBy("a"),
			depiq.W("w").Inherit("w1").OrderBy(depiq.I("b").Asc()),
		)
	query, args, _ = ds.ToSQL()
	fmt.Println(query, args)

	ds = depiq.From("test").
		Select(depiq.ROW_NUMBER().Over(depiq.W().Inherit("w").OrderBy("b"))).
		Window(depiq.W("w").PartitionBy("a"))
	query, args, _ = ds.ToSQL()
	fmt.Println(query, args)
}

Output:

SELECT ROW_NUMBER() OVER (PARTITION BY "a" ORDER BY "b" ASC) FROM "test" []
SELECT ROW_NUMBER() OVER "w" FROM "test" WINDOW "w" AS (PARTITION BY "a" ORDER BY "b" ASC) []
SELECT ROW_NUMBER() OVER "w1" FROM "test" WINDOW "w1" AS (PARTITION BY "a"), "w" AS ("w1" ORDER BY "b" ASC) []
SELECT ROW_NUMBER() OVER ("w" ORDER BY "b") FROM "test" WINDOW "w" AS (PARTITION BY "a") []

Types

type Database 

type Database struct {

	// nolint: stylecheck // keep for backwards compatibility
	Db SQLDatabase
	// contains filtered or unexported fields
}

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

func New 

func New(dialect string, db SQLDatabase) *Database

func (*Database) Begin 

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

Starts a new Transaction.

Example 
db := getDB()

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

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

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

Output:

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

func (*Database) BeginTx 

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

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

Example 
db := getDB()

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

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

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

Output:

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

func (*Database) Delete 

func (d *Database) Delete(table interface{}) *DeleteDataset

func (*Database) Dialect 

func (d *Database) Dialect() string

returns this databases dialect

Example 
db := getDB()

fmt.Println(db.Dialect())

Output:

postgres

func (*Database) Exec 

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

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

query: The SQL to execute

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

Example 
db := getDB()

_, err := db.Exec(`DROP TABLE "user_role"; DROP TABLE "depiq_user"`)
if err != nil {
	fmt.Println("Error occurred while dropping tables", err.Error())
}
fmt.Println("Dropped tables user_role and depiq_user")

Output:

Dropped tables user_role and depiq_user

func (*Database) ExecContext 

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

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

query: The SQL to execute

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

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

Output:

Dropped tables user_role and depiq_user

func (*Database) From 

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

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

var ids []uint32
if err := db.From("items").Where(depiq.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 depiq.Literal or another depiq.Dataset

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

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

Output:

Fetched Users names: [Bob Sally Vinita John]

func (*Database) Insert 

func (d *Database) Insert(table interface{}) *InsertDataset

func (*Database) Logger 

func (d *Database) Logger(logger Logger)

Sets the logger for to use when logging queries

func (*Database) Prepare 

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

Can be used to prepare a query.

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

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

query: The SQL statement to prepare.

func (*Database) PrepareContext 

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

Can be used to prepare a query.

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

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

query: The SQL statement to prepare.

func (*Database) Query 

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

Used to query for multiple rows.

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

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

query: The SQL to execute

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

func (*Database) QueryContext 

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

Used to query for multiple rows.

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

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

query: The SQL to execute

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

func (*Database) QueryRow 

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

Used to query for a single row.

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

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

query: The SQL to execute

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

func (*Database) QueryRowContext 

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

Used to query for a single row.

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

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

query: The SQL to execute

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

func (*Database) ScanStruct 

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

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

i: A pointer to a struct

query: The SQL to execute

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

func (*Database) ScanStructContext 

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

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

i: A pointer to a struct

query: The SQL to execute

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

func (*Database) ScanStructs 

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

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

i: A pointer to a slice of structs

query: The SQL to execute

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

func (*Database) ScanStructsContext 

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

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

i: A pointer to a slice of structs

query: The SQL to execute

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

func (*Database) ScanVal 

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

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

i: A pointer to a primitive value

query: The SQL to execute

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

func (*Database) ScanValContext 

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

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

i: A pointer to a primitive value

query: The SQL to execute

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

func (*Database) ScanVals 

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

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

i: A pointer to a slice of primitive values

query: The SQL to execute

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

func (*Database) ScanValsContext 

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

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

i: A pointer to a slice of primitive values

query: The SQL to execute

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

func (*Database) Select 

func (d *Database) Select(cols ...interface{}) *SelectDataset

func (*Database) Trace 

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

Logs a given operation with the specified sql and arguments

func (*Database) Truncate 

func (d *Database) Truncate(table ...interface{}) *TruncateDataset

func (*Database) Update 

func (d *Database) Update(table interface{}) *UpdateDataset

func (*Database) WithTx 

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

WithTx starts a new transaction and executes it in Wrap method

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

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

Output:

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

type DeleteDataset 

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

func Delete 

func Delete(table interface{}) *DeleteDataset
Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
	_ "github.com/orn-id/depiq/dialect/mysql"
)

func main() {
	ds := depiq.Delete("items")

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

}

Output:

DELETE FROM "items" []

func (*DeleteDataset) AppendSQL 

func (dd *DeleteDataset) AppendSQL(b sb.SQLBuilder)

Appends this Dataset's DELETE statement to the SQLBuilder This is used internally when using deletes in CTEs

func (*DeleteDataset) ClearLimit 

func (dd *DeleteDataset) ClearLimit() *DeleteDataset

Removes the LIMIT clause.

Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
	_ "github.com/orn-id/depiq/dialect/mysql"
)

func main() {
	// Using mysql dialect because it supports limit on delete
	ds := depiq.Dialect("mysql").Delete("test").Limit(10)
	sql, _, _ := ds.ClearLimit().ToSQL()
	fmt.Println(sql)
}

Output:

DELETE `test` FROM `test`

func (*DeleteDataset) ClearOrder 

func (dd *DeleteDataset) ClearOrder() *DeleteDataset

Removes the ORDER BY clause. See examples.

Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
	_ "github.com/orn-id/depiq/dialect/mysql"
)

func main() {
	ds := depiq.Delete("test").Order(depiq.C("a").Asc())
	sql, _, _ := ds.ClearOrder().ToSQL()
	fmt.Println(sql)
}

Output:

DELETE FROM "test"

func (*DeleteDataset) ClearWhere 

func (dd *DeleteDataset) ClearWhere() *DeleteDataset

Removes the WHERE clause. See examples.

Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
	_ "github.com/orn-id/depiq/dialect/mysql"
)

func main() {
	ds := depiq.Delete("test").Where(
		depiq.Or(
			depiq.C("a").Gt(10),
			depiq.And(
				depiq.C("b").Lt(10),
				depiq.C("c").IsNull(),
			),
		),
	)
	sql, _, _ := ds.ClearWhere().ToSQL()
	fmt.Println(sql)
}

Output:

DELETE FROM "test"

func (*DeleteDataset) Clone 

func (dd *DeleteDataset) Clone() exp.Expression

Clones the dataset

func (*DeleteDataset) Dialect 

func (dd *DeleteDataset) Dialect() SQLDialect

Returns the current SQLDialect on the dataset

func (*DeleteDataset) Error 

func (dd *DeleteDataset) Error() error

Get any error that has been set or nil if no error has been set.

func (*DeleteDataset) Executor 

func (dd *DeleteDataset) Executor() exec.QueryExecutor

Creates an QueryExecutor to execute the query. 

db.Delete("test").Exec()

See Dataset#ToUpdateSQL for arguments

Example 
db := getDB()

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

Output:

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

de := db.Delete("depiq_user").
	Where(depiq.C("last_name").Eq("Yukon")).
	Returning(depiq.C("id")).
	Executor()

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

Output:

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

func (*DeleteDataset) Expression 

func (dd *DeleteDataset) Expression() exp.Expression

func (*DeleteDataset) From 

func (dd *DeleteDataset) From(table interface{}) *DeleteDataset

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

func (*DeleteDataset) GetAs 

func (dd *DeleteDataset) GetAs() exp.IdentifierExpression

func (*DeleteDataset) GetClauses 

func (dd *DeleteDataset) GetClauses() exp.DeleteClauses

Returns the current clauses on the dataset.

func (*DeleteDataset) IsPrepared 

func (dd *DeleteDataset) IsPrepared() bool

Returns true if Prepared(true) has been called on this dataset

func (*DeleteDataset) Limit 

func (dd *DeleteDataset) Limit(limit uint) *DeleteDataset

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

Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
	_ "github.com/orn-id/depiq/dialect/mysql"
)

func main() {
	ds := depiq.Dialect("mysql").Delete("test").Limit(10)
	sql, _, _ := ds.ToSQL()
	fmt.Println(sql)
}

Output:

DELETE FROM `test` LIMIT 10

func (*DeleteDataset) LimitAll 

func (dd *DeleteDataset) LimitAll() *DeleteDataset

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

Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
	_ "github.com/orn-id/depiq/dialect/mysql"
)

func main() {
	// Using mysql dialect because it supports limit on delete
	ds := depiq.Dialect("mysql").Delete("test").LimitAll()
	sql, _, _ := ds.ToSQL()
	fmt.Println(sql)
}

Output:

DELETE FROM `test` LIMIT ALL

func (*DeleteDataset) Order 

func (dd *DeleteDataset) Order(order ...exp.OrderedExpression) *DeleteDataset

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

Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
	_ "github.com/orn-id/depiq/dialect/mysql"
)

func main() {
	// use mysql dialect because it supports order by on deletes
	ds := depiq.Dialect("mysql").Delete("test").Order(depiq.C("a").Asc())
	sql, _, _ := ds.ToSQL()
	fmt.Println(sql)
}

Output:

DELETE FROM `test` ORDER BY `a` ASC

func (*DeleteDataset) OrderAppend 

func (dd *DeleteDataset) OrderAppend(order ...exp.OrderedExpression) *DeleteDataset

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 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
	_ "github.com/orn-id/depiq/dialect/mysql"
)

func main() {
	// use mysql dialect because it supports order by on deletes
	ds := depiq.Dialect("mysql").Delete("test").Order(depiq.C("a").Asc())
	sql, _, _ := ds.OrderAppend(depiq.C("b").Desc().NullsLast()).ToSQL()
	fmt.Println(sql)
}

Output:

DELETE FROM `test` ORDER BY `a` ASC, `b` DESC NULLS LAST

func (*DeleteDataset) OrderPrepend 

func (dd *DeleteDataset) OrderPrepend(order ...exp.OrderedExpression) *DeleteDataset

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

Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
	_ "github.com/orn-id/depiq/dialect/mysql"
)

func main() {
	// use mysql dialect because it supports order by on deletes
	ds := depiq.Dialect("mysql").Delete("test").Order(depiq.C("a").Asc())
	sql, _, _ := ds.OrderPrepend(depiq.C("b").Desc().NullsLast()).ToSQL()
	fmt.Println(sql)
}

Output:

DELETE FROM `test` ORDER BY `b` DESC NULLS LAST, `a` ASC

func (*DeleteDataset) Prepared 

func (dd *DeleteDataset) Prepared(prepared bool) *DeleteDataset

Set the parameter interpolation behavior. See examples

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

Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
	_ "github.com/orn-id/depiq/dialect/mysql"
)

func main() {
	sql, args, _ := depiq.Delete("items").Prepared(true).ToSQL()
	fmt.Println(sql, args)

	sql, args, _ = depiq.Delete("items").
		Prepared(true).
		Where(depiq.Ex{"id": depiq.Op{"gt": 10}}).
		ToSQL()
	fmt.Println(sql, args)

}

Output:

DELETE FROM "items" []
DELETE FROM "items" WHERE ("id" > ?) [10]

func (*DeleteDataset) Returning 

func (dd *DeleteDataset) Returning(returning ...interface{}) *DeleteDataset

Adds a RETURNING clause to the dataset if the adapter supports it.

Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
	_ "github.com/orn-id/depiq/dialect/mysql"
)

func main() {
	ds := depiq.Delete("items")
	sql, args, _ := ds.Returning("id").ToSQL()
	fmt.Println(sql, args)

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

}

Output:

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

func (*DeleteDataset) ReturnsColumns 

func (dd *DeleteDataset) ReturnsColumns() bool

func (*DeleteDataset) SetDialect 

func (dd *DeleteDataset) SetDialect(dialect SQLDialect) *DeleteDataset

Set the dialect for this dataset.

func (*DeleteDataset) SetError 

func (dd *DeleteDataset) SetError(err error) *DeleteDataset

Set an error on the dataset if one has not already been set. This error will be returned by a future call to Error or as part of ToSQL. This can be used by end users to record errors while building up queries without having to track those separately.

func (*DeleteDataset) ToSQL 

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

Generates a DELETE 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 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
	_ "github.com/orn-id/depiq/dialect/mysql"
)

func main() {
	sql, args, _ := depiq.Delete("items").ToSQL()
	fmt.Println(sql, args)

	sql, args, _ = depiq.Delete("items").
		Where(depiq.Ex{"id": depiq.Op{"gt": 10}}).
		ToSQL()
	fmt.Println(sql, args)

}

Output:

DELETE FROM "items" []
DELETE FROM "items" WHERE ("id" > 10) []

func (*DeleteDataset) Where 

func (dd *DeleteDataset) Where(expressions ...exp.Expression) *DeleteDataset

Adds a WHERE clause. See examples.

Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
	_ "github.com/orn-id/depiq/dialect/mysql"
)

func main() {
	// By default everything is anded together
	sql, _, _ := depiq.Delete("test").Where(depiq.Ex{
		"a": depiq.Op{"gt": 10},
		"b": depiq.Op{"lt": 10},
		"c": nil,
		"d": []string{"a", "b", "c"},
	}).ToSQL()
	fmt.Println(sql)
	// You can use ExOr to get ORed expressions together
	sql, _, _ = depiq.Delete("test").Where(depiq.ExOr{
		"a": depiq.Op{"gt": 10},
		"b": depiq.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, _, _ = depiq.Delete("test").Where(
		depiq.Or(
			depiq.Ex{
				"a": depiq.Op{"gt": 10},
				"b": depiq.Op{"lt": 10},
			},
			depiq.Ex{
				"c": nil,
				"d": []string{"a", "b", "c"},
			},
		),
	).ToSQL()
	fmt.Println(sql)
	// By default everything is anded together
	sql, _, _ = depiq.Delete("test").Where(
		depiq.C("a").Gt(10),
		depiq.C("b").Lt(10),
		depiq.C("c").IsNull(),
		depiq.C("d").In("a", "b", "c"),
	).ToSQL()
	fmt.Println(sql)
	// You can use a combination of Ors and Ands
	sql, _, _ = depiq.Delete("test").Where(
		depiq.Or(
			depiq.C("a").Gt(10),
			depiq.And(
				depiq.C("b").Lt(10),
				depiq.C("c").IsNull(),
			),
		),
	).ToSQL()
	fmt.Println(sql)
}

Output:

DELETE FROM "test" WHERE (("a" > 10) AND ("b" < 10) AND ("c" IS NULL) AND ("d" IN ('a', 'b', 'c')))
DELETE FROM "test" WHERE (("a" > 10) OR ("b" < 10) OR ("c" IS NULL) OR ("d" IN ('a', 'b', 'c')))
DELETE FROM "test" WHERE ((("a" > 10) AND ("b" < 10)) OR (("c" IS NULL) AND ("d" IN ('a', 'b', 'c'))))
DELETE FROM "test" WHERE (("a" > 10) AND ("b" < 10) AND ("c" IS NULL) AND ("d" IN ('a', 'b', 'c')))
DELETE FROM "test" WHERE (("a" > 10) OR (("b" < 10) AND ("c" IS NULL)))
Example (Prepared) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
	_ "github.com/orn-id/depiq/dialect/mysql"
)

func main() {
	// By default everything is anded together
	sql, args, _ := depiq.Delete("test").Prepared(true).Where(depiq.Ex{
		"a": depiq.Op{"gt": 10},
		"b": depiq.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, _ = depiq.Delete("test").Prepared(true).Where(depiq.ExOr{
		"a": depiq.Op{"gt": 10},
		"b": depiq.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, _ = depiq.Delete("test").Prepared(true).Where(
		depiq.Or(
			depiq.Ex{
				"a": depiq.Op{"gt": 10},
				"b": depiq.Op{"lt": 10},
			},
			depiq.Ex{
				"c": nil,
				"d": []string{"a", "b", "c"},
			},
		),
	).ToSQL()
	fmt.Println(sql, args)
	// By default everything is anded together
	sql, args, _ = depiq.Delete("test").Prepared(true).Where(
		depiq.C("a").Gt(10),
		depiq.C("b").Lt(10),
		depiq.C("c").IsNull(),
		depiq.C("d").In("a", "b", "c"),
	).ToSQL()
	fmt.Println(sql, args)
	// You can use a combination of Ors and Ands
	sql, args, _ = depiq.Delete("test").Prepared(true).Where(
		depiq.Or(
			depiq.C("a").Gt(10),
			depiq.And(
				depiq.C("b").Lt(10),
				depiq.C("c").IsNull(),
			),
		),
	).ToSQL()
	fmt.Println(sql, args)
}

Output:

DELETE FROM "test" WHERE (("a" > ?) AND ("b" < ?) AND ("c" IS NULL) AND ("d" IN (?, ?, ?))) [10 10 a b c]
DELETE FROM "test" WHERE (("a" > ?) OR ("b" < ?) OR ("c" IS NULL) OR ("d" IN (?, ?, ?))) [10 10 a b c]
DELETE FROM "test" WHERE ((("a" > ?) AND ("b" < ?)) OR (("c" IS NULL) AND ("d" IN (?, ?, ?)))) [10 10 a b c]
DELETE FROM "test" WHERE (("a" > ?) AND ("b" < ?) AND ("c" IS NULL) AND ("d" IN (?, ?, ?))) [10 10 a b c]
DELETE FROM "test" WHERE (("a" > ?) OR (("b" < ?) AND ("c" IS NULL))) [10 10]

func (*DeleteDataset) With 

func (dd *DeleteDataset) With(name string, subquery exp.Expression) *DeleteDataset

Creates a WITH clause for a common table expression (CTE).

The name will be available to SELECT from in the associated query; and can optionally contain a list of column names "name(col1, col2, col3)".

The name will refer to the results of the specified subquery.

Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
	_ "github.com/orn-id/depiq/dialect/mysql"
)

func main() {
	sql, _, _ := depiq.Delete("test").
		With("check_vals(val)", depiq.From().Select(depiq.L("123"))).
		Where(depiq.C("val").Eq(depiq.From("check_vals").Select("val"))).
		ToSQL()
	fmt.Println(sql)

}

Output:

WITH check_vals(val) AS (SELECT 123) DELETE FROM "test" WHERE ("val" IN (SELECT "val" FROM "check_vals"))

func (*DeleteDataset) WithDialect 

func (dd *DeleteDataset) WithDialect(dl string) *DeleteDataset

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

func (*DeleteDataset) WithRecursive 

func (dd *DeleteDataset) WithRecursive(name string, subquery exp.Expression) *DeleteDataset

Creates a WITH RECURSIVE clause for a common table expression (CTE)

The name will be available to SELECT from in the associated query; and must contain a list of column names "name(col1, col2, col3)" for a recursive clause.

The name will refer to the results of the specified subquery. The subquery for a recursive query will always end with a UNION or UNION ALL with a clause that refers to the CTE by name.

Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
	_ "github.com/orn-id/depiq/dialect/mysql"
)

func main() {
	sql, _, _ := depiq.Delete("nums").
		WithRecursive("nums(x)",
			depiq.From().Select(depiq.L("1")).
				UnionAll(depiq.From("nums").
					Select(depiq.L("x+1")).Where(depiq.C("x").Lt(5)))).
		ToSQL()
	fmt.Println(sql)
}

Output:

WITH RECURSIVE nums(x) AS (SELECT 1 UNION ALL (SELECT x+1 FROM "nums" WHERE ("x" < 5))) DELETE FROM "nums"

type DialectWrapper 

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

func Dialect 

func Dialect(dialect string) DialectWrapper

Creates a new DialectWrapper to create depiq.Datasets or depiq.Databases with the specified dialect.

Example (DatasetMysql)

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

package main

import (
	"fmt"

	"github.com/orn-id/depiq"

	_ "github.com/orn-id/depiq/dialect/mysql"
	_ "github.com/orn-id/depiq/dialect/postgres"
	_ "github.com/orn-id/depiq/dialect/sqlite3"
)

func main() {
	// import _ "github.com/orn-id/depiq/dialect/mysql"

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

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

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

}

Output:

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

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

package main

import (
	"fmt"

	"github.com/orn-id/depiq"

	_ "github.com/orn-id/depiq/dialect/mysql"
	_ "github.com/orn-id/depiq/dialect/postgres"
	_ "github.com/orn-id/depiq/dialect/sqlite3"
)

func main() {
	// import _ "github.com/orn-id/depiq/dialect/postgres"

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

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

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

}

Output:

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

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

package main

import (
	"fmt"

	"github.com/orn-id/depiq"

	_ "github.com/orn-id/depiq/dialect/mysql"
	_ "github.com/orn-id/depiq/dialect/postgres"
	_ "github.com/orn-id/depiq/dialect/sqlite3"
)

func main() {
	// import _ "github.com/orn-id/depiq/dialect/sqlite3"

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

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

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

}

Output:

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

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

// import _ "github.com/orn-id/depiq/dialect/mysql"

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

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

d := depiq.Dialect("mysql")

db := d.DB(mDB)

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

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

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

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

Output:

{1 111 Test Addr Test1} true <nil>
{1 111 Test Addr Test1} true <nil>
Example (DbPostgres)

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

// import _ "github.com/orn-id/depiq/dialect/postgres"

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

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

d := depiq.Dialect("postgres")

db := d.DB(mDB)

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

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

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

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

Output:

{1 111 Test Addr Test1} true <nil>
{1 111 Test Addr Test1} true <nil>
Example (DbSqlite3)

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

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

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

d := depiq.Dialect("sqlite3")

db := d.DB(mDB)

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

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

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

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

Output:

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

func (DialectWrapper) DB 

func (dw DialectWrapper) DB(db SQLDatabase) *Database

func (DialectWrapper) Delete 

func (dw DialectWrapper) Delete(table interface{}) *DeleteDataset

Create a new dataset for creating DELETE sql statements

func (DialectWrapper) From 

func (dw DialectWrapper) From(table ...interface{}) *SelectDataset

Create a new dataset for creating SELECT sql statements

func (DialectWrapper) Insert 

func (dw DialectWrapper) Insert(table interface{}) *InsertDataset

Create a new dataset for creating INSERT sql statements

func (DialectWrapper) Select 

func (dw DialectWrapper) Select(cols ...interface{}) *SelectDataset

Create a new dataset for creating SELECT sql statements

func (DialectWrapper) Truncate 

func (dw DialectWrapper) Truncate(table ...interface{}) *TruncateDataset

Create a new dataset for creating TRUNCATE sql statements

func (DialectWrapper) Update 

func (dw DialectWrapper) Update(table interface{}) *UpdateDataset

Create a new dataset for creating UPDATE sql statements

type Ex 

type Ex = exp.Ex
Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	ds := depiq.From("items").Where(
		depiq.Ex{
			"col1": "a",
			"col2": 1,
			"col3": true,
			"col4": false,
			"col5": nil,
			"col6": []string{"a", "b", "c"},
		},
	)

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

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

}

Output:

SELECT * FROM "items" WHERE (("col1" = 'a') AND ("col2" = 1) AND ("col3" IS TRUE) AND ("col4" IS FALSE) AND ("col5" IS NULL) AND ("col6" IN ('a', 'b', 'c'))) []
SELECT * FROM "items" WHERE (("col1" = ?) AND ("col2" = ?) AND ("col3" IS TRUE) AND ("col4" IS FALSE) AND ("col5" IS NULL) AND ("col6" IN (?, ?, ?))) [a 1 a b c]
Example (In) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	// using an Ex expression map
	sql, _, _ := depiq.From("test").Where(depiq.Ex{
		"a": []string{"a", "b", "c"},
	}).ToSQL()
	fmt.Println(sql)

}

Output:

SELECT * FROM "test" WHERE ("a" IN ('a', 'b', 'c'))
Example (WithOp) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	sql, args, _ := depiq.From("items").Where(
		depiq.Ex{
			"col1": depiq.Op{"neq": "a"},
			"col3": depiq.Op{"isNot": true},
			"col6": depiq.Op{"notIn": []string{"a", "b", "c"}},
		},
	).ToSQL()
	fmt.Println(sql, args)
}

Output:

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

type ExOr 

type ExOr = exp.ExOr
Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

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

	// nolint:lll // sql statements are long
	

Output:
Example (WithOp) 
package main

import (
	"fmt"
	"regexp"

	"github.com/orn-id/depiq"
)

func main() {
	sql, _, _ := depiq.From("items").Where(depiq.ExOr{
		"col1": depiq.Op{"neq": "a"},
		"col3": depiq.Op{"isNot": true},
		"col6": depiq.Op{"notIn": []string{"a", "b", "c"}},
	}).ToSQL()
	fmt.Println(sql)

	sql, _, _ = depiq.From("items").Where(depiq.ExOr{
		"col1": depiq.Op{"gt": 1},
		"col2": depiq.Op{"gte": 1},
		"col3": depiq.Op{"lt": 1},
		"col4": depiq.Op{"lte": 1},
	}).ToSQL()
	fmt.Println(sql)

	sql, _, _ = depiq.From("items").Where(depiq.ExOr{
		"col1": depiq.Op{"like": "a%"},
		"col2": depiq.Op{"notLike": "a%"},
		"col3": depiq.Op{"iLike": "a%"},
		"col4": depiq.Op{"notILike": "a%"},
	}).ToSQL()
	fmt.Println(sql)

	sql, _, _ = depiq.From("items").Where(depiq.ExOr{
		"col1": depiq.Op{"like": regexp.MustCompile("^[ab]")},
		"col2": depiq.Op{"notLike": regexp.MustCompile("^[ab]")},
		"col3": depiq.Op{"iLike": regexp.MustCompile("^[ab]")},
		"col4": depiq.Op{"notILike": regexp.MustCompile("^[ab]")},
	}).ToSQL()
	fmt.Println(sql)

}

Output:

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

type Expression 

type Expression = exp.Expression

type InsertDataset 

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

func Insert 

func Insert(table interface{}) *InsertDataset

Creates a new InsertDataset for the provided table. Using this method will only allow you to create SQL user Database#From to create an InsertDataset with query capabilities

Example (ColsAndVals) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
	_ "github.com/orn-id/depiq/dialect/postgres"
)

func main() {
	ds := depiq.Insert("user").
		Cols("first_name", "last_name").
		Vals(
			depiq.Vals{"Greg", "Farley"},
			depiq.Vals{"Jimmy", "Stewart"},
			depiq.Vals{"Jeff", "Jeffers"},
		)
	insertSQL, args, _ := ds.ToSQL()
	fmt.Println(insertSQL, args)

}

Output:

INSERT INTO "user" ("first_name", "last_name") VALUES ('Greg', 'Farley'), ('Jimmy', 'Stewart'), ('Jeff', 'Jeffers') []
Example (DepiqRecord) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
	_ "github.com/orn-id/depiq/dialect/postgres"
)

func main() {
	ds := depiq.Insert("user").Rows(
		depiq.Record{"first_name": "Greg", "last_name": "Farley"},
		depiq.Record{"first_name": "Jimmy", "last_name": "Stewart"},
		depiq.Record{"first_name": "Jeff", "last_name": "Jeffers"},
	)
	insertSQL, args, _ := ds.ToSQL()
	fmt.Println(insertSQL, args)

}

Output:

INSERT INTO "user" ("first_name", "last_name") VALUES ('Greg', 'Farley'), ('Jimmy', 'Stewart'), ('Jeff', 'Jeffers') []
Example (FromQuery) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
	_ "github.com/orn-id/depiq/dialect/postgres"
)

func main() {
	ds := depiq.Insert("user").Prepared(true).
		FromQuery(depiq.From("other_table"))
	insertSQL, args, _ := ds.ToSQL()
	fmt.Println(insertSQL, args)

}

Output:

INSERT INTO "user" SELECT * FROM "other_table" []
Example (FromQueryWithCols) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
	_ "github.com/orn-id/depiq/dialect/postgres"
)

func main() {
	ds := depiq.Insert("user").Prepared(true).
		Cols("first_name", "last_name").
		FromQuery(depiq.From("other_table").Select("fn", "ln"))
	insertSQL, args, _ := ds.ToSQL()
	fmt.Println(insertSQL, args)

}

Output:

INSERT INTO "user" ("first_name", "last_name") SELECT "fn", "ln" FROM "other_table" []
Example (Map) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
	_ "github.com/orn-id/depiq/dialect/postgres"
)

func main() {
	ds := depiq.Insert("user").Rows(
		map[string]interface{}{"first_name": "Greg", "last_name": "Farley"},
		map[string]interface{}{"first_name": "Jimmy", "last_name": "Stewart"},
		map[string]interface{}{"first_name": "Jeff", "last_name": "Jeffers"},
	)
	insertSQL, args, _ := ds.ToSQL()
	fmt.Println(insertSQL, args)

}

Output:

INSERT INTO "user" ("first_name", "last_name") VALUES ('Greg', 'Farley'), ('Jimmy', 'Stewart'), ('Jeff', 'Jeffers') []
Example (Prepared) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
	_ "github.com/orn-id/depiq/dialect/postgres"
)

func main() {
	ds := depiq.Insert("user").Prepared(true).Rows(
		depiq.Record{"first_name": "Greg", "last_name": "Farley"},
		depiq.Record{"first_name": "Jimmy", "last_name": "Stewart"},
		depiq.Record{"first_name": "Jeff", "last_name": "Jeffers"},
	)
	insertSQL, args, _ := ds.ToSQL()
	fmt.Println(insertSQL, args)

}

Output:

INSERT INTO "user" ("first_name", "last_name") VALUES (?, ?), (?, ?), (?, ?) [Greg Farley Jimmy Stewart Jeff Jeffers]
Example (Struct) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
	_ "github.com/orn-id/depiq/dialect/postgres"
)

func main() {
	type User struct {
		FirstName string `db:"first_name"`
		LastName  string `db:"last_name"`
	}
	ds := depiq.Insert("user").Rows(
		User{FirstName: "Greg", LastName: "Farley"},
		User{FirstName: "Jimmy", LastName: "Stewart"},
		User{FirstName: "Jeff", LastName: "Jeffers"},
	)
	insertSQL, args, _ := ds.ToSQL()
	fmt.Println(insertSQL, args)

}

Output:

INSERT INTO "user" ("first_name", "last_name") VALUES ('Greg', 'Farley'), ('Jimmy', 'Stewart'), ('Jeff', 'Jeffers') []

func (*InsertDataset) AppendSQL 

func (id *InsertDataset) AppendSQL(b sb.SQLBuilder)

Appends this Dataset's INSERT statement to the SQLBuilder This is used internally when using inserts in CTEs

func (*InsertDataset) As 

func (id *InsertDataset) As(alias string) *InsertDataset

Sets the alias for this dataset. This is typically used when using a Dataset as MySQL upsert

func (*InsertDataset) ClearCols 

func (id *InsertDataset) ClearCols() *InsertDataset

Clears the Columns to insert into

Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
	_ "github.com/orn-id/depiq/dialect/postgres"
)

func main() {
	ds := depiq.Insert("test").Cols("a", "b", "c")
	insertSQL, _, _ := ds.ClearCols().Cols("other_a", "other_b", "other_c").
		FromQuery(depiq.From("foo").Select("d", "e", "f")).
		ToSQL()
	fmt.Println(insertSQL)
}

Output:

INSERT INTO "test" ("other_a", "other_b", "other_c") SELECT "d", "e", "f" FROM "foo"

func (*InsertDataset) ClearOnConflict 

func (id *InsertDataset) ClearOnConflict() *InsertDataset

Clears the on conflict clause. See example

Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
	_ "github.com/orn-id/depiq/dialect/postgres"
)

func main() {
	type item struct {
		ID      uint32 `db:"id" depiq:"skipinsert"`
		Address string `db:"address"`
		Name    string `db:"name"`
	}
	ds := depiq.Insert("items").OnConflict(depiq.DoNothing())
	insertSQL, args, _ := ds.ClearOnConflict().Rows(
		item{Name: "Test1", Address: "111 Test Addr"},
		item{Name: "Test2", Address: "112 Test Addr"},
	).ToSQL()
	fmt.Println(insertSQL, args)

}

Output:

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

func (*InsertDataset) ClearRows 

func (id *InsertDataset) ClearRows() *InsertDataset

Clears the rows for this insert dataset. See examples.

Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
	_ "github.com/orn-id/depiq/dialect/postgres"
)

func main() {
	type item struct {
		ID      uint32 `depiq:"skipinsert"`
		Address string
		Name    string
	}
	ds := depiq.Insert("items").Rows(
		item{Name: "Test1", Address: "111 Test Addr"},
		item{Name: "Test2", Address: "112 Test Addr"},
	)
	insertSQL, args, _ := ds.ClearRows().ToSQL()
	fmt.Println(insertSQL, args)

}

Output:

INSERT INTO "items" DEFAULT VALUES []

func (*InsertDataset) ClearVals 

func (id *InsertDataset) ClearVals() *InsertDataset

Clears the values. See examples.

Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
	_ "github.com/orn-id/depiq/dialect/postgres"
)

func main() {
	insertSQL, _, _ := depiq.Insert("test").
		Cols("a", "b", "c").
		Vals(
			[]interface{}{"a1", "b1", "c1"},
			[]interface{}{"a2", "b1", "c1"},
			[]interface{}{"a3", "b1", "c1"},
		).
		ClearVals().
		ToSQL()
	fmt.Println(insertSQL)

	insertSQL, _, _ = depiq.Insert("test").
		Cols("a", "b", "c").
		Vals([]interface{}{"a1", "b1", "c1"}).
		Vals([]interface{}{"a2", "b2", "c2"}).
		Vals([]interface{}{"a3", "b3", "c3"}).
		ClearVals().
		ToSQL()
	fmt.Println(insertSQL)
}

Output:

INSERT INTO "test" DEFAULT VALUES
INSERT INTO "test" DEFAULT VALUES

func (*InsertDataset) Clone 

func (id *InsertDataset) Clone() exp.Expression

Clones the dataset

func (*InsertDataset) Cols 

func (id *InsertDataset) Cols(cols ...interface{}) *InsertDataset

Sets the Columns to insert into

Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
	_ "github.com/orn-id/depiq/dialect/postgres"
)

func main() {
	insertSQL, _, _ := depiq.Insert("test").
		Cols("a", "b", "c").
		Vals(
			[]interface{}{"a1", "b1", "c1"},
			[]interface{}{"a2", "b1", "c1"},
			[]interface{}{"a3", "b1", "c1"},
		).
		ToSQL()
	fmt.Println(insertSQL)
}

Output:

INSERT INTO "test" ("a", "b", "c") VALUES ('a1', 'b1', 'c1'), ('a2', 'b1', 'c1'), ('a3', 'b1', 'c1')
Example (WithFromQuery) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
	_ "github.com/orn-id/depiq/dialect/postgres"
)

func main() {
	insertSQL, _, _ := depiq.Insert("test").
		Cols("a", "b", "c").
		FromQuery(depiq.From("foo").Select("d", "e", "f")).
		ToSQL()
	fmt.Println(insertSQL)
}

Output:

INSERT INTO "test" ("a", "b", "c") SELECT "d", "e", "f" FROM "foo"

func (*InsertDataset) ColsAppend 

func (id *InsertDataset) ColsAppend(cols ...interface{}) *InsertDataset

Adds columns to the current list of columns clause. See examples

Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
	_ "github.com/orn-id/depiq/dialect/postgres"
)

func main() {
	insertSQL, _, _ := depiq.Insert("test").
		Cols("a", "b").
		ColsAppend("c").
		Vals(
			[]interface{}{"a1", "b1", "c1"},
			[]interface{}{"a2", "b1", "c1"},
			[]interface{}{"a3", "b1", "c1"},
		).
		ToSQL()
	fmt.Println(insertSQL)
}

Output:

INSERT INTO "test" ("a", "b", "c") VALUES ('a1', 'b1', 'c1'), ('a2', 'b1', 'c1'), ('a3', 'b1', 'c1')

func (*InsertDataset) Dialect 

func (id *InsertDataset) Dialect() SQLDialect

Returns the current adapter on the dataset

func (*InsertDataset) Error 

func (id *InsertDataset) Error() error

Get any error that has been set or nil if no error has been set.

func (*InsertDataset) Executor 

func (id *InsertDataset) Executor() exec.QueryExecutor

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

db.Insert("test").Rows(Record{"name":"Bob"}).Executor().Exec()
Example (RecordReturning) 
db := getDB()

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

insert := db.Insert("depiq_user").Returning(depiq.C("id")).Rows(
	depiq.Record{"first_name": "Jed", "last_name": "Riley", "created": time.Now()},
).Executor()
var id int64
if _, err := insert.ScanVal(&id); err != nil {
	fmt.Println(err.Error())
} else {
	fmt.Printf("Inserted 1 user id:=%d\n", id)
}

insert = db.Insert("depiq_user").Returning(depiq.Star()).Rows([]depiq.Record{
	{"first_name": "Greg", "last_name": "Farley", "created": time.Now()},
	{"first_name": "Jimmy", "last_name": "Stewart", "created": time.Now()},
	{"first_name": "Jeff", "last_name": "Jeffers", "created": time.Now()},
}).Executor()
var insertedUsers []User
if err := insert.ScanStructs(&insertedUsers); err != nil {
	fmt.Println(err.Error())
} else {
	for _, u := range insertedUsers {
		fmt.Printf("Inserted user: [ID=%d], [FirstName=%+s] [LastName=%s]\n", u.ID.Int64, u.FirstName, u.LastName)
	}
}

Output:

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

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

insert := db.Insert("depiq_user").Returning("id").Rows(
	User{FirstName: "Jed", LastName: "Riley"},
).Executor()
var id int64
if _, err := insert.ScanVal(&id); err != nil {
	fmt.Println(err.Error())
} else {
	fmt.Printf("Inserted 1 user id:=%d\n", id)
}

insert = db.Insert("depiq_user").Returning(depiq.Star()).Rows([]User{
	{FirstName: "Greg", LastName: "Farley", Created: time.Now()},
	{FirstName: "Jimmy", LastName: "Stewart", Created: time.Now()},
	{FirstName: "Jeff", LastName: "Jeffers", Created: time.Now()},
}).Executor()
var insertedUsers []User
if err := insert.ScanStructs(&insertedUsers); err != nil {
	fmt.Println(err.Error())
} else {
	for _, u := range insertedUsers {
		fmt.Printf("Inserted user: [ID=%d], [FirstName=%+s] [LastName=%s]\n", u.ID.Int64, u.FirstName, u.LastName)
	}
}

Output:

Inserted 1 user id:=5
Inserted user: [ID=6], [FirstName=Greg] [LastName=Farley]
Inserted user: [ID=7], [FirstName=Jimmy] [LastName=Stewart]
Inserted user: [ID=8], [FirstName=Jeff] [LastName=Jeffers]
Example (WithRecord) 
db := getDB()
insert := db.Insert("depiq_user").Rows(
	depiq.Record{"first_name": "Jed", "last_name": "Riley", "created": time.Now()},
).Executor()
if _, err := insert.Exec(); err != nil {
	fmt.Println(err.Error())
} else {
	fmt.Println("Inserted 1 user")
}

users := []depiq.Record{
	{"first_name": "Greg", "last_name": "Farley", "created": time.Now()},
	{"first_name": "Jimmy", "last_name": "Stewart", "created": time.Now()},
	{"first_name": "Jeff", "last_name": "Jeffers", "created": time.Now()},
}
if _, err := db.Insert("depiq_user").Rows(users).Executor().Exec(); err != nil {
	fmt.Println(err.Error())
} else {
	fmt.Printf("Inserted %d users", len(users))
}

Output:

Inserted 1 user
Inserted 3 users

func (*InsertDataset) Expression 

func (id *InsertDataset) Expression() exp.Expression

func (*InsertDataset) FromQuery 

func (id *InsertDataset) FromQuery(from exp.AppendableExpression) *InsertDataset

Adds a subquery to the insert. See examples.

Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
	_ "github.com/orn-id/depiq/dialect/postgres"
)

func main() {
	insertSQL, _, _ := depiq.Insert("test").
		FromQuery(depiq.From("test2").Where(depiq.C("age").Gt(10))).
		ToSQL()
	fmt.Println(insertSQL)
}

Output:

INSERT INTO "test" SELECT * FROM "test2" WHERE ("age" > 10)

func (*InsertDataset) GetAs 

func (id *InsertDataset) GetAs() exp.IdentifierExpression

func (*InsertDataset) GetClauses 

func (id *InsertDataset) GetClauses() exp.InsertClauses

Returns the current clauses on the dataset.

func (*InsertDataset) Into 

func (id *InsertDataset) Into(into interface{}) *InsertDataset

Sets the table to insert INTO. This return a new dataset with the original table replaced. See examples. You can pass in the following. 

string: Will automatically be turned into an identifier
Expression: Any valid expression (IdentifierExpression, AliasedExpression, Literal, etc.)
Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
	_ "github.com/orn-id/depiq/dialect/postgres"
)

func main() {
	ds := depiq.Insert("test")
	insertSQL, _, _ := ds.Into("test2").Rows(depiq.Record{"first_name": "bob", "last_name": "yukon"}).ToSQL()
	fmt.Println(insertSQL)
}

Output:

INSERT INTO "test2" ("first_name", "last_name") VALUES ('bob', 'yukon')
Example (Aliased) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
	_ "github.com/orn-id/depiq/dialect/postgres"
)

func main() {
	ds := depiq.Insert("test")
	insertSQL, _, _ := ds.
		Into(depiq.T("test").As("t")).
		Rows(depiq.Record{"first_name": "bob", "last_name": "yukon"}).
		ToSQL()
	fmt.Println(insertSQL)
}

Output:

INSERT INTO "test" AS "t" ("first_name", "last_name") VALUES ('bob', 'yukon')

func (*InsertDataset) IsPrepared 

func (id *InsertDataset) IsPrepared() bool

func (*InsertDataset) OnConflict 

func (id *InsertDataset) OnConflict(conflict exp.ConflictExpression) *InsertDataset

Adds an (ON CONFLICT/ON DUPLICATE KEY) clause to the dataset if the dialect supports it. See examples.

Example (DoNothing) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
	_ "github.com/orn-id/depiq/dialect/postgres"
)

func main() {
	type item struct {
		ID      uint32 `db:"id" depiq:"skipinsert"`
		Address string `db:"address"`
		Name    string `db:"name"`
	}
	insertSQL, args, _ := depiq.Insert("items").Rows(
		item{Name: "Test1", Address: "111 Test Addr"},
		item{Name: "Test2", Address: "112 Test Addr"},
	).OnConflict(depiq.DoNothing()).ToSQL()
	fmt.Println(insertSQL, args)

}

Output:

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

import (
	"fmt"

	"github.com/orn-id/depiq"
	_ "github.com/orn-id/depiq/dialect/postgres"
)

func main() {
	insertSQL, args, _ := depiq.Insert("items").
		Rows(
			depiq.Record{"name": "Test1", "address": "111 Test Addr"},
			depiq.Record{"name": "Test2", "address": "112 Test Addr"},
		).
		OnConflict(depiq.DoUpdate("key", depiq.Record{"updated": depiq.L("NOW()")})).
		ToSQL()
	fmt.Println(insertSQL, args)

}

Output:

INSERT INTO "items" ("address", "name") VALUES ('111 Test Addr', 'Test1'), ('112 Test Addr', 'Test2') ON CONFLICT (key) DO UPDATE SET "updated"=NOW() []
Example (DoUpdateWithWhere) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
	_ "github.com/orn-id/depiq/dialect/postgres"
)

func main() {
	type item struct {
		ID      uint32 `db:"id" depiq:"skipinsert"`
		Address string `db:"address"`
		Name    string `db:"name"`
	}
	insertSQL, args, _ := depiq.Insert("items").
		Rows([]item{
			{Name: "Test1", Address: "111 Test Addr"},
			{Name: "Test2", Address: "112 Test Addr"},
		}).
		OnConflict(depiq.DoUpdate(
			"key",
			depiq.Record{"updated": depiq.L("NOW()")}).Where(depiq.C("allow_update").IsTrue()),
		).
		ToSQL()
	fmt.Println(insertSQL, args)

}

Output:

INSERT INTO "items" ("address", "name") VALUES ('111 Test Addr', 'Test1'), ('112 Test Addr', 'Test2') ON CONFLICT (key) DO UPDATE SET "updated"=NOW() WHERE ("allow_update" IS TRUE) []

func (*InsertDataset) Prepared 

func (id *InsertDataset) Prepared(prepared bool) *InsertDataset

Set the parameter interpolation behavior. See examples

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

Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
	_ "github.com/orn-id/depiq/dialect/postgres"
)

func main() {
	type item struct {
		ID      uint32 `db:"id" depiq:"skipinsert"`
		Address string `db:"address"`
		Name    string `db:"name"`
	}

	insertSQL, args, _ := depiq.Insert("items").Prepared(true).Rows(
		item{Name: "Test1", Address: "111 Test Addr"},
		item{Name: "Test2", Address: "112 Test Addr"},
	).ToSQL()
	fmt.Println(insertSQL, args)

	insertSQL, args, _ = depiq.Insert("items").Prepared(true).Rows(
		depiq.Record{"name": "Test1", "address": "111 Test Addr"},
		depiq.Record{"name": "Test2", "address": "112 Test Addr"},
	).ToSQL()
	fmt.Println(insertSQL, args)

	insertSQL, args, _ = depiq.Insert("items").Prepared(true).Rows(
		[]item{
			{Name: "Test1", Address: "111 Test Addr"},
			{Name: "Test2", Address: "112 Test Addr"},
		}).ToSQL()
	fmt.Println(insertSQL, args)

	insertSQL, args, _ = depiq.Insert("items").Prepared(true).Rows(
		[]depiq.Record{
			{"name": "Test1", "address": "111 Test Addr"},
			{"name": "Test2", "address": "112 Test Addr"},
		}).ToSQL()
	fmt.Println(insertSQL, args)
}

Output:

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

func (*InsertDataset) Returning 

func (id *InsertDataset) Returning(returning ...interface{}) *InsertDataset

Adds a RETURNING clause to the dataset if the adapter supports it See examples.

Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
	_ "github.com/orn-id/depiq/dialect/postgres"
)

func main() {
	insertSQL, _, _ := depiq.Insert("test").
		Returning("id").
		Rows(depiq.Record{"a": "a", "b": "b"}).
		ToSQL()
	fmt.Println(insertSQL)
	insertSQL, _, _ = depiq.Insert("test").
		Returning(depiq.T("test").All()).
		Rows(depiq.Record{"a": "a", "b": "b"}).
		ToSQL()
	fmt.Println(insertSQL)
	insertSQL, _, _ = depiq.Insert("test").
		Returning("a", "b").
		Rows(depiq.Record{"a": "a", "b": "b"}).
		ToSQL()
	fmt.Println(insertSQL)
}

Output:

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

func (*InsertDataset) ReturnsColumns 

func (id *InsertDataset) ReturnsColumns() bool

func (*InsertDataset) Rows 

func (id *InsertDataset) Rows(rows ...interface{}) *InsertDataset

Insert rows. Rows can be a map, depiq.Record or struct. See examples.

Example (WithEmbeddedStruct) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
	_ "github.com/orn-id/depiq/dialect/postgres"
)

func main() {
	type Address struct {
		Street string `db:"address_street"`
		State  string `db:"address_state"`
	}
	type User struct {
		Address
		FirstName string
		LastName  string
	}
	ds := depiq.Insert("user").Rows(
		User{Address: Address{Street: "111 Street", State: "NY"}, FirstName: "Greg", LastName: "Farley"},
		User{Address: Address{Street: "211 Street", State: "NY"}, FirstName: "Jimmy", LastName: "Stewart"},
		User{Address: Address{Street: "311 Street", State: "NY"}, FirstName: "Jeff", LastName: "Jeffers"},
	)
	insertSQL, args, _ := ds.ToSQL()
	fmt.Println(insertSQL, args)

}

Output:

INSERT INTO "user" ("address_state", "address_street", "firstname", "lastname") VALUES ('NY', '111 Street', 'Greg', 'Farley'), ('NY', '211 Street', 'Jimmy', 'Stewart'), ('NY', '311 Street', 'Jeff', 'Jeffers') []
Example (WithGoquDefaultIfEmptyTag) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
	_ "github.com/orn-id/depiq/dialect/postgres"
)

func main() {
	type item struct {
		ID      uint32 `depiq:"skipinsert"`
		Address string
		Name    string `depiq:"defaultifempty"`
	}
	insertSQL, args, _ := depiq.Insert("items").
		Rows(
			item{Name: "Test1", Address: "111 Test Addr"},
			item{Address: "112 Test Addr"},
		).
		ToSQL()
	fmt.Println(insertSQL, args)

	insertSQL, args, _ = depiq.Insert("items").
		Rows([]item{
			{Address: "111 Test Addr"},
			{Name: "Test2", Address: "112 Test Addr"},
		}).
		ToSQL()
	fmt.Println(insertSQL, args)

}

Output:

INSERT INTO "items" ("address", "name") VALUES ('111 Test Addr', 'Test1'), ('112 Test Addr', DEFAULT) []
INSERT INTO "items" ("address", "name") VALUES ('111 Test Addr', DEFAULT), ('112 Test Addr', 'Test2') []
Example (WithGoquSkipInsertTag) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
	_ "github.com/orn-id/depiq/dialect/postgres"
)

func main() {
	type item struct {
		ID      uint32 `depiq:"skipinsert"`
		Address string
		Name    string `depiq:"skipinsert"`
	}
	insertSQL, args, _ := depiq.Insert("items").
		Rows(
			item{Name: "Test1", Address: "111 Test Addr"},
			item{Name: "Test2", Address: "112 Test Addr"},
		).
		ToSQL()
	fmt.Println(insertSQL, args)

	insertSQL, args, _ = depiq.Insert("items").
		Rows([]item{
			{Name: "Test1", Address: "111 Test Addr"},
			{Name: "Test2", Address: "112 Test Addr"},
		}).
		ToSQL()
	fmt.Println(insertSQL, args)

}

Output:

INSERT INTO "items" ("address") VALUES ('111 Test Addr'), ('112 Test Addr') []
INSERT INTO "items" ("address") VALUES ('111 Test Addr'), ('112 Test Addr') []
Example (WithIgnoredEmbedded) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
	_ "github.com/orn-id/depiq/dialect/postgres"
)

func main() {
	type Address struct {
		Street string
		State  string
	}
	type User struct {
		Address   `db:"-"`
		FirstName string
		LastName  string
	}
	ds := depiq.Insert("user").Rows(
		User{Address: Address{Street: "111 Street", State: "NY"}, FirstName: "Greg", LastName: "Farley"},
		User{Address: Address{Street: "211 Street", State: "NY"}, FirstName: "Jimmy", LastName: "Stewart"},
		User{Address: Address{Street: "311 Street", State: "NY"}, FirstName: "Jeff", LastName: "Jeffers"},
	)
	insertSQL, args, _ := ds.ToSQL()
	fmt.Println(insertSQL, args)

}

Output:

INSERT INTO "user" ("firstname", "lastname") VALUES ('Greg', 'Farley'), ('Jimmy', 'Stewart'), ('Jeff', 'Jeffers') []
Example (WithNilEmbeddedPointer) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
	_ "github.com/orn-id/depiq/dialect/postgres"
)

func main() {
	type Address struct {
		Street string
		State  string
	}
	type User struct {
		*Address
		FirstName string
		LastName  string
	}
	ds := depiq.Insert("user").Rows(
		User{FirstName: "Greg", LastName: "Farley"},
		User{FirstName: "Jimmy", LastName: "Stewart"},
		User{FirstName: "Jeff", LastName: "Jeffers"},
	)
	insertSQL, args, _ := ds.ToSQL()
	fmt.Println(insertSQL, args)

}

Output:

INSERT INTO "user" ("firstname", "lastname") VALUES ('Greg', 'Farley'), ('Jimmy', 'Stewart'), ('Jeff', 'Jeffers') []
Example (WithNoDbTag) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
	_ "github.com/orn-id/depiq/dialect/postgres"
)

func main() {
	type item struct {
		ID      uint32 `depiq:"skipinsert"`
		Address string
		Name    string
	}
	insertSQL, args, _ := depiq.Insert("items").
		Rows(
			item{Name: "Test1", Address: "111 Test Addr"},
			item{Name: "Test2", Address: "112 Test Addr"},
		).
		ToSQL()
	fmt.Println(insertSQL, args)

	insertSQL, args, _ = depiq.Insert("items").
		Rows(
			item{Name: "Test1", Address: "111 Test Addr"},
			item{Name: "Test2", Address: "112 Test Addr"},
		).
		ToSQL()
	fmt.Println(insertSQL, args)

	insertSQL, args, _ = depiq.Insert("items").
		Rows([]item{
			{Name: "Test1", Address: "111 Test Addr"},
			{Name: "Test2", Address: "112 Test Addr"},
		}).
		ToSQL()
	fmt.Println(insertSQL, args)

}

Output:

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

func (*InsertDataset) SetDialect 

func (id *InsertDataset) SetDialect(dialect SQLDialect) *InsertDataset

Returns the current adapter on the dataset

func (*InsertDataset) SetError 

func (id *InsertDataset) SetError(err error) *InsertDataset

Set an error on the dataset if one has not already been set. This error will be returned by a future call to Error or as part of ToSQL. This can be used by end users to record errors while building up queries without having to track those separately.

func (*InsertDataset) ToSQL 

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

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

type Item struct{
   Id   uint32 `db:"id" depiq:"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 INTO 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 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
	_ "github.com/orn-id/depiq/dialect/postgres"
)

func main() {
	type item struct {
		ID      uint32 `db:"id" depiq:"skipinsert"`
		Address string `db:"address"`
		Name    string `db:"name"`
	}
	insertSQL, args, _ := depiq.Insert("items").Rows(
		item{Name: "Test1", Address: "111 Test Addr"},
		item{Name: "Test2", Address: "112 Test Addr"},
	).ToSQL()
	fmt.Println(insertSQL, args)

	insertSQL, args, _ = depiq.Insert("items").Rows(
		depiq.Record{"name": "Test1", "address": "111 Test Addr"},
		depiq.Record{"name": "Test2", "address": "112 Test Addr"},
	).ToSQL()
	fmt.Println(insertSQL, args)

	insertSQL, args, _ = depiq.Insert("items").Rows(
		[]item{
			{Name: "Test1", Address: "111 Test Addr"},
			{Name: "Test2", Address: "112 Test Addr"},
		}).ToSQL()
	fmt.Println(insertSQL, args)

	insertSQL, args, _ = depiq.From("items").Insert().Rows(
		[]depiq.Record{
			{"name": "Test1", "address": "111 Test Addr"},
			{"name": "Test2", "address": "112 Test Addr"},
		}).ToSQL()
	fmt.Println(insertSQL, args)
}

Output:

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

func (*InsertDataset) Vals 

func (id *InsertDataset) Vals(vals ...[]interface{}) *InsertDataset

Manually set values to insert See examples.

Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
	_ "github.com/orn-id/depiq/dialect/postgres"
)

func main() {
	insertSQL, _, _ := depiq.Insert("test").
		Cols("a", "b", "c").
		Vals(
			[]interface{}{"a1", "b1", "c1"},
			[]interface{}{"a2", "b2", "c2"},
			[]interface{}{"a3", "b3", "c3"},
		).
		ToSQL()
	fmt.Println(insertSQL)

	insertSQL, _, _ = depiq.Insert("test").
		Cols("a", "b", "c").
		Vals([]interface{}{"a1", "b1", "c1"}).
		Vals([]interface{}{"a2", "b2", "c2"}).
		Vals([]interface{}{"a3", "b3", "c3"}).
		ToSQL()
	fmt.Println(insertSQL)

}

Output:

INSERT INTO "test" ("a", "b", "c") VALUES ('a1', 'b1', 'c1'), ('a2', 'b2', 'c2'), ('a3', 'b3', 'c3')
INSERT INTO "test" ("a", "b", "c") VALUES ('a1', 'b1', 'c1'), ('a2', 'b2', 'c2'), ('a3', 'b3', 'c3')

func (*InsertDataset) With 

func (id *InsertDataset) With(name string, subquery exp.Expression) *InsertDataset

Creates a WITH clause for a common table expression (CTE).

The name will be available to SELECT from in the associated query; and can optionally contain a list of column names "name(col1, col2, col3)".

The name will refer to the results of the specified subquery.

Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
	_ "github.com/orn-id/depiq/dialect/postgres"
)

func main() {
	insertSQL, _, _ := depiq.Insert("foo").
		With("other", depiq.From("bar").Where(depiq.C("id").Gt(10))).
		FromQuery(depiq.From("other")).
		ToSQL()
	fmt.Println(insertSQL)

}

Output:

WITH other AS (SELECT * FROM "bar" WHERE ("id" > 10)) INSERT INTO "foo" SELECT * FROM "other"

func (*InsertDataset) WithDialect 

func (id *InsertDataset) WithDialect(dl string) *InsertDataset

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

func (*InsertDataset) WithRecursive 

func (id *InsertDataset) WithRecursive(name string, subquery exp.Expression) *InsertDataset

Creates a WITH RECURSIVE clause for a common table expression (CTE)

The name will be available to SELECT from in the associated query; and must contain a list of column names "name(col1, col2, col3)" for a recursive clause.

The name will refer to the results of the specified subquery. The subquery for a recursive query will always end with a UNION or UNION ALL with a clause that refers to the CTE by name.

Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
	_ "github.com/orn-id/depiq/dialect/postgres"
)

func main() {
	insertSQL, _, _ := depiq.Insert("num_count").
		WithRecursive("nums(x)",
			depiq.From().Select(depiq.L("1")).
				UnionAll(depiq.From("nums").
					Select(depiq.L("x+1")).Where(depiq.C("x").Lt(5))),
		).
		FromQuery(depiq.From("nums")).
		ToSQL()
	fmt.Println(insertSQL)
}

Output:

WITH RECURSIVE nums(x) AS (SELECT 1 UNION ALL (SELECT x+1 FROM "nums" WHERE ("x" < 5))) INSERT INTO "num_count" SELECT * FROM "nums"

type Logger 

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

type Op 

type Op = exp.Op
Example (BetweenComparisons) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	ds := depiq.From("test").Where(depiq.Ex{
		"a": depiq.Op{"between": depiq.Range(1, 10)},
	})
	sql, args, _ := ds.ToSQL()
	fmt.Println(sql, args)

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

	ds = depiq.From("test").Where(depiq.Ex{
		"a": depiq.Op{"notBetween": depiq.Range(1, 10)},
	})
	sql, args, _ = ds.ToSQL()
	fmt.Println(sql, args)

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

}

Output:

SELECT * FROM "test" WHERE ("a" BETWEEN 1 AND 10) []
SELECT * FROM "test" WHERE ("a" BETWEEN ? AND ?) [1 10]
SELECT * FROM "test" WHERE ("a" NOT BETWEEN 1 AND 10) []
SELECT * FROM "test" WHERE ("a" NOT BETWEEN ? AND ?) [1 10]
Example (Comparisons) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	ds := depiq.From("test").Where(depiq.Ex{
		"a": 10,
		"b": depiq.Op{"neq": 10},
		"c": depiq.Op{"gte": 10},
		"d": depiq.Op{"lt": 10},
		"e": depiq.Op{"lte": 10},
	})

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

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

}

Output:

SELECT * FROM "test" WHERE (("a" = 10) AND ("b" != 10) AND ("c" >= 10) AND ("d" < 10) AND ("e" <= 10)) []
SELECT * FROM "test" WHERE (("a" = ?) AND ("b" != ?) AND ("c" >= ?) AND ("d" < ?) AND ("e" <= ?)) [10 10 10 10 10]
Example (InComparisons) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	// using an Ex expression map
	ds := depiq.From("test").Where(depiq.Ex{
		"a": depiq.Op{"in": []string{"a", "b", "c"}},
	})

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

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

	ds = depiq.From("test").Where(depiq.Ex{
		"a": depiq.Op{"notIn": []string{"a", "b", "c"}},
	})

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

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

}

Output:

SELECT * FROM "test" WHERE ("a" IN ('a', 'b', 'c')) []
SELECT * FROM "test" WHERE ("a" IN (?, ?, ?)) [a b c]
SELECT * FROM "test" WHERE ("a" NOT IN ('a', 'b', 'c')) []
SELECT * FROM "test" WHERE ("a" NOT IN (?, ?, ?)) [a b c]
Example (IsComparisons) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	// using an Ex expression map
	ds := depiq.From("test").Where(depiq.Ex{
		"a": true,
	})
	sql, args, _ := ds.ToSQL()
	fmt.Println(sql, args)
	sql, args, _ = ds.Prepared(true).ToSQL()
	fmt.Println(sql, args)

	ds = depiq.From("test").Where(depiq.Ex{
		"a": depiq.Op{"is": true},
	})
	sql, args, _ = ds.ToSQL()
	fmt.Println(sql, args)
	sql, args, _ = ds.Prepared(true).ToSQL()
	fmt.Println(sql, args)

	ds = depiq.From("test").Where(depiq.Ex{
		"a": false,
	})
	sql, args, _ = ds.ToSQL()
	fmt.Println(sql, args)
	sql, args, _ = ds.Prepared(true).ToSQL()
	fmt.Println(sql, args)

	ds = depiq.From("test").Where(depiq.Ex{
		"a": depiq.Op{"is": false},
	})
	sql, args, _ = ds.ToSQL()
	fmt.Println(sql, args)
	sql, args, _ = ds.Prepared(true).ToSQL()
	fmt.Println(sql, args)

	ds = depiq.From("test").Where(depiq.Ex{
		"a": nil,
	})
	sql, args, _ = ds.ToSQL()
	fmt.Println(sql, args)
	sql, args, _ = ds.Prepared(true).ToSQL()
	fmt.Println(sql, args)

	ds = depiq.From("test").Where(depiq.Ex{
		"a": depiq.Op{"is": nil},
	})
	sql, args, _ = ds.ToSQL()
	fmt.Println(sql, args)
	sql, args, _ = ds.Prepared(true).ToSQL()
	fmt.Println(sql, args)

	ds = depiq.From("test").Where(depiq.Ex{
		"a": depiq.Op{"isNot": true},
	})
	sql, args, _ = ds.ToSQL()
	fmt.Println(sql, args)
	sql, args, _ = ds.Prepared(true).ToSQL()
	fmt.Println(sql, args)

	ds = depiq.From("test").Where(depiq.Ex{
		"a": depiq.Op{"isNot": false},
	})
	sql, args, _ = ds.ToSQL()
	fmt.Println(sql, args)
	sql, args, _ = ds.Prepared(true).ToSQL()
	fmt.Println(sql, args)

	ds = depiq.From("test").Where(depiq.Ex{
		"a": depiq.Op{"isNot": nil},
	})
	sql, args, _ = ds.ToSQL()
	fmt.Println(sql, args)
	sql, args, _ = ds.Prepared(true).ToSQL()
	fmt.Println(sql, args)

}

Output:

SELECT * FROM "test" WHERE ("a" IS TRUE) []
SELECT * FROM "test" WHERE ("a" IS TRUE) []
SELECT * FROM "test" WHERE ("a" IS TRUE) []
SELECT * FROM "test" WHERE ("a" IS TRUE) []
SELECT * FROM "test" WHERE ("a" IS FALSE) []
SELECT * FROM "test" WHERE ("a" IS FALSE) []
SELECT * FROM "test" WHERE ("a" IS FALSE) []
SELECT * FROM "test" WHERE ("a" IS FALSE) []
SELECT * FROM "test" WHERE ("a" IS NULL) []
SELECT * FROM "test" WHERE ("a" IS NULL) []
SELECT * FROM "test" WHERE ("a" IS NULL) []
SELECT * FROM "test" WHERE ("a" IS NULL) []
SELECT * FROM "test" WHERE ("a" IS NOT TRUE) []
SELECT * FROM "test" WHERE ("a" IS NOT TRUE) []
SELECT * FROM "test" WHERE ("a" IS NOT FALSE) []
SELECT * FROM "test" WHERE ("a" IS NOT FALSE) []
SELECT * FROM "test" WHERE ("a" IS NOT NULL) []
SELECT * FROM "test" WHERE ("a" IS NOT NULL) []
Example (LikeComparisons) 
package main

import (
	"fmt"
	"regexp"

	"github.com/orn-id/depiq"
)

func main() {
	// using an Ex expression map
	ds := depiq.From("test").Where(depiq.Ex{
		"a": depiq.Op{"like": "%a%"},
	})
	sql, args, _ := ds.ToSQL()
	fmt.Println(sql, args)

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

	ds = depiq.From("test").Where(depiq.Ex{
		"a": depiq.Op{"like": regexp.MustCompile("[ab]")},
	})

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

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

	ds = depiq.From("test").Where(depiq.Ex{
		"a": depiq.Op{"iLike": "%a%"},
	})

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

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

	ds = depiq.From("test").Where(depiq.Ex{
		"a": depiq.Op{"iLike": regexp.MustCompile("[ab]")},
	})

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

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

	ds = depiq.From("test").Where(depiq.Ex{
		"a": depiq.Op{"notLike": "%a%"},
	})

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

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

	ds = depiq.From("test").Where(depiq.Ex{
		"a": depiq.Op{"notLike": regexp.MustCompile("[ab]")},
	})

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

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

	ds = depiq.From("test").Where(depiq.Ex{
		"a": depiq.Op{"notILike": "%a%"},
	})

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

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

	ds = depiq.From("test").Where(depiq.Ex{
		"a": depiq.Op{"notILike": regexp.MustCompile("[ab]")},
	})
	sql, args, _ = ds.ToSQL()
	fmt.Println(sql, args)

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

}

Output:

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

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

package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	ds := depiq.From("items").Where(depiq.Ex{
		"col1": depiq.Op{"is": nil, "eq": 10},
	})

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

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

}

Output:

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

type Record 

type Record = exp.Record
Example (Insert) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	ds := depiq.Insert("test")

	records := []depiq.Record{
		{"col1": 1, "col2": "foo"},
		{"col1": 2, "col2": "bar"},
	}

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

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

Output:

INSERT INTO "test" ("col1", "col2") VALUES (1, 'foo'), (2, 'bar') []
INSERT INTO "test" ("col1", "col2") VALUES (?, ?), (?, ?) [1 foo 2 bar]
Example (Update) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	ds := depiq.Update("test")
	update := depiq.Record{"col1": 1, "col2": "foo"}

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

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

Output:

UPDATE "test" SET "col1"=1,"col2"='foo' []
UPDATE "test" SET "col1"=?,"col2"=? [1 foo]

type SQLDatabase 

type SQLDatabase interface {
	Begin() (*sql.Tx, error)
	BeginTx(ctx context.Context, opts *sql.TxOptions) (*sql.Tx, error)
	ExecContext(ctx context.Context, query string, args ...interface{}) (sql.Result, error)
	PrepareContext(ctx context.Context, query string) (*sql.Stmt, error)
	QueryContext(ctx context.Context, query string, args ...interface{}) (*sql.Rows, error)
	QueryRowContext(ctx context.Context, query string, args ...interface{}) *sql.Row
}

Interface for sql.DB, an interface is used so you can use with other libraries such as sqlx instead of the native sql.DB

type SQLDialect 

type SQLDialect interface {
	Dialect() string
	ToSelectSQL(b sb.SQLBuilder, clauses exp.SelectClauses)
	ToUpdateSQL(b sb.SQLBuilder, clauses exp.UpdateClauses)
	ToInsertSQL(b sb.SQLBuilder, clauses exp.InsertClauses)
	ToDeleteSQL(b sb.SQLBuilder, clauses exp.DeleteClauses)
	ToTruncateSQL(b sb.SQLBuilder, clauses exp.TruncateClauses)
}

An adapter interface to be used by a Dataset to generate SQL for a specific dialect. See DefaultAdapter for a concrete implementation and examples.

func GetDialect 

func GetDialect(name string) SQLDialect

type SQLDialectOptions 

type SQLDialectOptions = sqlgen.SQLDialectOptions

type SQLTx 

type SQLTx interface {
	ExecContext(ctx context.Context, query string, args ...interface{}) (sql.Result, error)
	PrepareContext(ctx context.Context, query string) (*sql.Stmt, error)
	QueryContext(ctx context.Context, query string, args ...interface{}) (*sql.Rows, error)
	QueryRowContext(ctx context.Context, query string, args ...interface{}) *sql.Row
	Commit() error
	Rollback() error
}

Interface for sql.Tx, an interface is used so you can use with other libraries such as sqlx instead of the native sql.DB

type SelectDataset 

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

Dataset for creating and/or executing SELECT SQL statements.

Example 
package main

import (
	"fmt"
	"regexp"

	"github.com/orn-id/depiq"
)

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

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

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

Output:

func From 

func From(table ...interface{}) *SelectDataset
Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	sql, args, _ := depiq.From("test").ToSQL()
	fmt.Println(sql, args)

}

Output:

SELECT * FROM "test" []

func Select 

func Select(cols ...interface{}) *SelectDataset
Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	sql, _, _ := depiq.Select(depiq.L("NOW()")).ToSQL()
	fmt.Println(sql)

}

Output:

SELECT NOW()

func (*SelectDataset) AppendSQL 

func (sd *SelectDataset) AppendSQL(b sb.SQLBuilder)

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

func (*SelectDataset) As 

func (sd *SelectDataset) As(alias string) *SelectDataset

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

Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	ds := depiq.From("test").As("t")
	sql, _, _ := depiq.From(ds).ToSQL()
	fmt.Println(sql)
}

Output:

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

func (*SelectDataset) ClearLimit 

func (sd *SelectDataset) ClearLimit() *SelectDataset

Removes the LIMIT clause.

Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	ds := depiq.From("test").Limit(10)
	sql, _, _ := ds.ClearLimit().ToSQL()
	fmt.Println(sql)
}

Output:

SELECT * FROM "test"

func (*SelectDataset) ClearOffset 

func (sd *SelectDataset) ClearOffset() *SelectDataset

Removes the OFFSET clause from the Dataset

Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	ds := depiq.From("test").
		Offset(2)
	sql, _, _ := ds.
		ClearOffset().
		ToSQL()
	fmt.Println(sql)
}

Output:

SELECT * FROM "test"

func (*SelectDataset) ClearOrder 

func (sd *SelectDataset) ClearOrder() *SelectDataset

Removes the ORDER BY clause. See examples.

Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	ds := depiq.From("test").Order(depiq.C("a").Asc())
	sql, _, _ := ds.ClearOrder().ToSQL()
	fmt.Println(sql)
}

Output:

SELECT * FROM "test"

func (*SelectDataset) ClearSelect 

func (sd *SelectDataset) ClearSelect() *SelectDataset

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

Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	ds := depiq.From("test").Select("a", "b")
	sql, _, _ := ds.ClearSelect().ToSQL()
	fmt.Println(sql)
	ds = depiq.From("test").Select("a", "b").Distinct()
	sql, _, _ = ds.ClearSelect().ToSQL()
	fmt.Println(sql)
}

Output:

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

func (*SelectDataset) ClearWhere 

func (sd *SelectDataset) ClearWhere() *SelectDataset

Removes the WHERE clause. See examples.

Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	ds := depiq.From("test").Where(
		depiq.Or(
			depiq.C("a").Gt(10),
			depiq.And(
				depiq.C("b").Lt(10),
				depiq.C("c").IsNull(),
			),
		),
	)
	sql, _, _ := ds.ClearWhere().ToSQL()
	fmt.Println(sql)
}

Output:

SELECT * FROM "test"

func (*SelectDataset) ClearWindow 

func (sd *SelectDataset) ClearWindow() *SelectDataset

Sets the WINDOW clauses

func (*SelectDataset) Clone 

func (sd *SelectDataset) Clone() exp.Expression

Clones the dataset

func (*SelectDataset) CompoundFromSelf 

func (sd *SelectDataset) CompoundFromSelf() *SelectDataset

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

func (*SelectDataset) Count 

func (sd *SelectDataset) Count() (int64, error)

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

Example 
package main

import (
	goSQL "database/sql"
	"fmt"
	"os"
	"time"

	"github.com/lib/pq"
	"github.com/orn-id/depiq"
)

const schema = `
		DROP TABLE IF EXISTS "user_role";
		DROP TABLE IF EXISTS "depiq_user";	
		CREATE  TABLE "depiq_user" (
			"id" SERIAL PRIMARY KEY NOT NULL,
			"first_name" VARCHAR(45) NOT NULL,
			"last_name" VARCHAR(45) NOT NULL,
			"created" TIMESTAMP NOT NULL DEFAULT now()
		);
		CREATE  TABLE "user_role" (
			"id" SERIAL PRIMARY KEY NOT NULL,
			"user_id" BIGINT NOT NULL REFERENCES depiq_user(id) ON DELETE CASCADE,
			"name" VARCHAR(45) NOT NULL,
			"created" TIMESTAMP NOT NULL DEFAULT now()
		); 
    `

const defaultDBURI = "postgres://postgres:@localhost:5435/depiqpostgres?sslmode=disable"

var depiqDB *depiq.Database

func getDB() *depiq.Database {
	if depiqDB == nil {
		dbURI := os.Getenv("PG_URI")
		if dbURI == "" {
			dbURI = defaultDBURI
		}
		uri, err := pq.ParseURL(dbURI)
		if err != nil {
			panic(err)
		}
		pdb, err := goSQL.Open("postgres", uri)
		if err != nil {
			panic(err)
		}
		depiqDB = depiq.New("postgres", pdb)
	}

	if _, err := depiqDB.Exec(schema); err != nil {
		panic(err)
	}
	type depiqUser struct {
		ID        int64     `db:"id" depiq:"skipinsert"`
		FirstName string    `db:"first_name"`
		LastName  string    `db:"last_name"`
		Created   time.Time `db:"created" depiq:"skipupdate"`
	}

	users := []depiqUser{
		{FirstName: "Bob", LastName: "Yukon"},
		{FirstName: "Sally", LastName: "Yukon"},
		{FirstName: "Vinita", LastName: "Yukon"},
		{FirstName: "John", LastName: "Doe"},
	}
	var userIds []int64
	err := depiqDB.Insert("depiq_user").Rows(users).Returning("id").Executor().ScanVals(&userIds)
	if err != nil {
		panic(err)
	}
	type userRole struct {
		ID      int64     `db:"id" depiq:"skipinsert"`
		UserID  int64     `db:"user_id"`
		Name    string    `db:"name"`
		Created time.Time `db:"created" depiq:"skipupdate"`
	}

	roles := []userRole{
		{UserID: userIds[0], Name: "Admin"},
		{UserID: userIds[1], Name: "Manager"},
		{UserID: userIds[2], Name: "Manager"},
		{UserID: userIds[3], Name: "User"},
	}
	_, err = depiqDB.Insert("user_role").Rows(roles).Executor().Exec()
	if err != nil {
		panic(err)
	}
	return depiqDB
}

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

}

Output:

Count is 4

func (*SelectDataset) CountContext 

func (sd *SelectDataset) 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 (*SelectDataset) CrossJoin 

func (sd *SelectDataset) CrossJoin(table exp.Expression) *SelectDataset

Adds a CROSS JOIN clause. See examples.

Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	sql, _, _ := depiq.From("test").CrossJoin(depiq.T("test2")).ToSQL()
	fmt.Println(sql)

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

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

Output:

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

func (*SelectDataset) Delete 

func (sd *SelectDataset) Delete() *DeleteDataset

Creates a new DeleteDataset using the FROM of this dataset. This method will also copy over the `WITH`, `WHERE`, `ORDER , and `LIMIT`

Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	sql, args, _ := depiq.From("items").Delete().ToSQL()
	fmt.Println(sql, args)

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

}

Output:

DELETE FROM "items" []
DELETE FROM "items" WHERE ("id" > 10) []

func (*SelectDataset) Dialect 

func (sd *SelectDataset) Dialect() SQLDialect

Returns the current adapter on the dataset

func (*SelectDataset) Distinct 

func (sd *SelectDataset) Distinct(on ...interface{}) *SelectDataset
Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	sql, _, _ := depiq.From("test").Select("a", "b").Distinct().ToSQL()
	fmt.Println(sql)
}

Output:

SELECT DISTINCT "a", "b" FROM "test"
Example (On) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	sql, _, _ := depiq.From("test").Distinct("a").ToSQL()
	fmt.Println(sql)
}

Output:

SELECT DISTINCT ON ("a") * FROM "test"
Example (OnCoalesce) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	sql, _, _ := depiq.From("test").Distinct(depiq.COALESCE(depiq.C("a"), "empty")).ToSQL()
	fmt.Println(sql)
}

Output:

SELECT DISTINCT ON (COALESCE("a", 'empty')) * FROM "test"
Example (OnWithLiteral) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	sql, _, _ := depiq.From("test").Distinct(depiq.L("COALESCE(?, ?)", depiq.C("a"), "empty")).ToSQL()
	fmt.Println(sql)
}

Output:

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

func (*SelectDataset) Error 

func (sd *SelectDataset) Error() error

Get any error that has been set or nil if no error has been set.

func (*SelectDataset) Executor 

func (sd *SelectDataset) Executor() exec.QueryExecutor

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

db.From("test").Select("col").Executor()

See Dataset#ToUpdateSQL for arguments

Example (ScannerScanStruct) 
package main

import (
	goSQL "database/sql"
	"fmt"
	"os"
	"time"

	"github.com/lib/pq"
	"github.com/orn-id/depiq"
)

const schema = `
		DROP TABLE IF EXISTS "user_role";
		DROP TABLE IF EXISTS "depiq_user";	
		CREATE  TABLE "depiq_user" (
			"id" SERIAL PRIMARY KEY NOT NULL,
			"first_name" VARCHAR(45) NOT NULL,
			"last_name" VARCHAR(45) NOT NULL,
			"created" TIMESTAMP NOT NULL DEFAULT now()
		);
		CREATE  TABLE "user_role" (
			"id" SERIAL PRIMARY KEY NOT NULL,
			"user_id" BIGINT NOT NULL REFERENCES depiq_user(id) ON DELETE CASCADE,
			"name" VARCHAR(45) NOT NULL,
			"created" TIMESTAMP NOT NULL DEFAULT now()
		); 
    `

const defaultDBURI = "postgres://postgres:@localhost:5435/depiqpostgres?sslmode=disable"

var depiqDB *depiq.Database

func getDB() *depiq.Database {
	if depiqDB == nil {
		dbURI := os.Getenv("PG_URI")
		if dbURI == "" {
			dbURI = defaultDBURI
		}
		uri, err := pq.ParseURL(dbURI)
		if err != nil {
			panic(err)
		}
		pdb, err := goSQL.Open("postgres", uri)
		if err != nil {
			panic(err)
		}
		depiqDB = depiq.New("postgres", pdb)
	}

	if _, err := depiqDB.Exec(schema); err != nil {
		panic(err)
	}
	type depiqUser struct {
		ID        int64     `db:"id" depiq:"skipinsert"`
		FirstName string    `db:"first_name"`
		LastName  string    `db:"last_name"`
		Created   time.Time `db:"created" depiq:"skipupdate"`
	}

	users := []depiqUser{
		{FirstName: "Bob", LastName: "Yukon"},
		{FirstName: "Sally", LastName: "Yukon"},
		{FirstName: "Vinita", LastName: "Yukon"},
		{FirstName: "John", LastName: "Doe"},
	}
	var userIds []int64
	err := depiqDB.Insert("depiq_user").Rows(users).Returning("id").Executor().ScanVals(&userIds)
	if err != nil {
		panic(err)
	}
	type userRole struct {
		ID      int64     `db:"id" depiq:"skipinsert"`
		UserID  int64     `db:"user_id"`
		Name    string    `db:"name"`
		Created time.Time `db:"created" depiq:"skipupdate"`
	}

	roles := []userRole{
		{UserID: userIds[0], Name: "Admin"},
		{UserID: userIds[1], Name: "Manager"},
		{UserID: userIds[2], Name: "Manager"},
		{UserID: userIds[3], Name: "User"},
	}
	_, err = depiqDB.Insert("user_role").Rows(roles).Executor().Exec()
	if err != nil {
		panic(err)
	}
	return depiqDB
}

func main() {
	type User struct {
		FirstName string `db:"first_name"`
		LastName  string `db:"last_name"`
	}
	db := getDB()

	scanner, err := db.
		From("depiq_user").
		Select("first_name", "last_name").
		Where(depiq.Ex{
			"last_name": "Yukon",
		}).
		Executor().
		Scanner()
	if err != nil {
		fmt.Println(err.Error())
		return
	}

	defer scanner.Close()

	for scanner.Next() {
		u := User{}

		err = scanner.ScanStruct(&u)
		if err != nil {
			fmt.Println(err.Error())
			return
		}

		fmt.Printf("\n%+v", u)
	}

	if scanner.Err() != nil {
		fmt.Println(scanner.Err().Error())
	}

}

Output:

{FirstName:Bob LastName:Yukon}
{FirstName:Sally LastName:Yukon}
{FirstName:Vinita LastName:Yukon}
Example (ScannerScanVal) 
package main

import (
	goSQL "database/sql"
	"fmt"
	"os"
	"time"

	"github.com/lib/pq"
	"github.com/orn-id/depiq"
)

const schema = `
		DROP TABLE IF EXISTS "user_role";
		DROP TABLE IF EXISTS "depiq_user";	
		CREATE  TABLE "depiq_user" (
			"id" SERIAL PRIMARY KEY NOT NULL,
			"first_name" VARCHAR(45) NOT NULL,
			"last_name" VARCHAR(45) NOT NULL,
			"created" TIMESTAMP NOT NULL DEFAULT now()
		);
		CREATE  TABLE "user_role" (
			"id" SERIAL PRIMARY KEY NOT NULL,
			"user_id" BIGINT NOT NULL REFERENCES depiq_user(id) ON DELETE CASCADE,
			"name" VARCHAR(45) NOT NULL,
			"created" TIMESTAMP NOT NULL DEFAULT now()
		); 
    `

const defaultDBURI = "postgres://postgres:@localhost:5435/depiqpostgres?sslmode=disable"

var depiqDB *depiq.Database

func getDB() *depiq.Database {
	if depiqDB == nil {
		dbURI := os.Getenv("PG_URI")
		if dbURI == "" {
			dbURI = defaultDBURI
		}
		uri, err := pq.ParseURL(dbURI)
		if err != nil {
			panic(err)
		}
		pdb, err := goSQL.Open("postgres", uri)
		if err != nil {
			panic(err)
		}
		depiqDB = depiq.New("postgres", pdb)
	}

	if _, err := depiqDB.Exec(schema); err != nil {
		panic(err)
	}
	type depiqUser struct {
		ID        int64     `db:"id" depiq:"skipinsert"`
		FirstName string    `db:"first_name"`
		LastName  string    `db:"last_name"`
		Created   time.Time `db:"created" depiq:"skipupdate"`
	}

	users := []depiqUser{
		{FirstName: "Bob", LastName: "Yukon"},
		{FirstName: "Sally", LastName: "Yukon"},
		{FirstName: "Vinita", LastName: "Yukon"},
		{FirstName: "John", LastName: "Doe"},
	}
	var userIds []int64
	err := depiqDB.Insert("depiq_user").Rows(users).Returning("id").Executor().ScanVals(&userIds)
	if err != nil {
		panic(err)
	}
	type userRole struct {
		ID      int64     `db:"id" depiq:"skipinsert"`
		UserID  int64     `db:"user_id"`
		Name    string    `db:"name"`
		Created time.Time `db:"created" depiq:"skipupdate"`
	}

	roles := []userRole{
		{UserID: userIds[0], Name: "Admin"},
		{UserID: userIds[1], Name: "Manager"},
		{UserID: userIds[2], Name: "Manager"},
		{UserID: userIds[3], Name: "User"},
	}
	_, err = depiqDB.Insert("user_role").Rows(roles).Executor().Exec()
	if err != nil {
		panic(err)
	}
	return depiqDB
}

func main() {
	db := getDB()

	scanner, err := db.
		From("depiq_user").
		Select("first_name").
		Where(depiq.Ex{
			"last_name": "Yukon",
		}).
		Executor().
		Scanner()
	if err != nil {
		fmt.Println(err.Error())
		return
	}

	defer scanner.Close()

	for scanner.Next() {
		name := ""

		err = scanner.ScanVal(&name)
		if err != nil {
			fmt.Println(err.Error())
			return
		}

		fmt.Println(name)
	}

	if scanner.Err() != nil {
		fmt.Println(scanner.Err().Error())
	}

}

Output:

Bob
Sally
Vinita

func (*SelectDataset) Expression 

func (sd *SelectDataset) Expression() exp.Expression

func (*SelectDataset) FecthRow added in v0.8.1 

func (sd *SelectDataset) FecthRow(i interface{}) (bool, error)

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

FecthRow 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 (*SelectDataset) Fetch added in v0.8.1 

func (sd *SelectDataset) Fetch(i interface{}) error

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

Fetch 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 (*SelectDataset) FetchContext added in v0.8.1 

func (sd *SelectDataset) FetchContext(ctx context.Context, i interface{}) error

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

FetchContext 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 (*SelectDataset) FetchtRowContext added in v0.8.1 

func (sd *SelectDataset) FetchtRowContext(ctx context.Context, i interface{}) (bool, error)

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

FetchtRowContext 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 (*SelectDataset) ForKeyShare 

func (sd *SelectDataset) ForKeyShare(waitOption exp.WaitOption, of ...exp.IdentifierExpression) *SelectDataset

Adds a FOR KEY SHARE clause. See examples.

func (*SelectDataset) ForNoKeyUpdate 

func (sd *SelectDataset) ForNoKeyUpdate(waitOption exp.WaitOption, of ...exp.IdentifierExpression) *SelectDataset

Adds a FOR NO KEY UPDATE clause. See examples.

func (*SelectDataset) ForShare 

func (sd *SelectDataset) ForShare(waitOption exp.WaitOption, of ...exp.IdentifierExpression) *SelectDataset

Adds a FOR SHARE clause. See examples.

func (*SelectDataset) ForUpdate 

func (sd *SelectDataset) ForUpdate(waitOption exp.WaitOption, of ...exp.IdentifierExpression) *SelectDataset

Adds a FOR UPDATE clause. See examples.

func (*SelectDataset) From 

func (sd *SelectDataset) From(from ...interface{}) *SelectDataset

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 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	ds := depiq.From("test")
	sql, _, _ := ds.From("test2").ToSQL()
	fmt.Println(sql)
}

Output:

SELECT * FROM "test2"
Example (WithAliasedDataset) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	ds := depiq.From("test")
	fromDs := ds.Where(depiq.C("age").Gt(10))
	sql, _, _ := ds.From(fromDs.As("test2")).ToSQL()
	fmt.Println(sql)
}

Output:

SELECT * FROM (SELECT * FROM "test" WHERE ("age" > 10)) AS "test2"
Example (WithDataset) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	ds := depiq.From("test")
	fromDs := ds.Where(depiq.C("age").Gt(10))
	sql, _, _ := ds.From(fromDs).ToSQL()
	fmt.Println(sql)
}

Output:

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

func (*SelectDataset) FromSelf 

func (sd *SelectDataset) FromSelf() *SelectDataset

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 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	sql, _, _ := depiq.From("test").FromSelf().ToSQL()
	fmt.Println(sql)
	sql, _, _ = depiq.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 (*SelectDataset) FullJoin 

func (sd *SelectDataset) FullJoin(table exp.Expression, condition exp.JoinCondition) *SelectDataset

Adds a FULL JOIN clause. See examples.

Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

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

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

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

	sql, _, _ = depiq.From("test").FullJoin(
		depiq.From("test2").Where(depiq.C("amount").Gt(0)).As("t"),
		depiq.On(depiq.I("test.fkey").Eq(depiq.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 (*SelectDataset) FullOuterJoin 

func (sd *SelectDataset) FullOuterJoin(table exp.Expression, condition exp.JoinCondition) *SelectDataset

Adds a FULL OUTER JOIN clause. See examples.

Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

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

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

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

	sql, _, _ = depiq.From("test").FullOuterJoin(
		depiq.From("test2").Where(depiq.C("amount").Gt(0)).As("t"),
		depiq.On(depiq.I("test.fkey").Eq(depiq.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 (*SelectDataset) GetAs 

func (sd *SelectDataset) GetAs() exp.IdentifierExpression

Returns the alias value as an identiier expression

func (*SelectDataset) GetClauses 

func (sd *SelectDataset) GetClauses() exp.SelectClauses

Returns the current clauses on the dataset.

func (*SelectDataset) GroupBy 

func (sd *SelectDataset) GroupBy(groupBy ...interface{}) *SelectDataset

Adds a GROUP BY clause. See examples.

Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	sql, _, _ := depiq.From("test").
		Select(depiq.SUM("income").As("income_sum")).
		GroupBy("age").
		ToSQL()
	fmt.Println(sql)
}

Output:

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

func (*SelectDataset) GroupByAppend 

func (sd *SelectDataset) GroupByAppend(groupBy ...interface{}) *SelectDataset

Adds more columns to the current GROUP BY clause. See examples.

Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

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

Output:

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

func (*SelectDataset) Having 

func (sd *SelectDataset) Having(expressions ...exp.Expression) *SelectDataset

Adds a HAVING clause. See examples.

Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	sql, _, _ := depiq.From("test").Having(depiq.SUM("income").Gt(1000)).ToSQL()
	fmt.Println(sql)
	sql, _, _ = depiq.From("test").GroupBy("age").Having(depiq.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 (*SelectDataset) InnerJoin 

func (sd *SelectDataset) InnerJoin(table exp.Expression, condition exp.JoinCondition) *SelectDataset

Adds an INNER JOIN clause. See examples.

Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

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

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

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

	sql, _, _ = depiq.From("test").InnerJoin(
		depiq.From("test2").Where(depiq.C("amount").Gt(0)).As("t"),
		depiq.On(depiq.I("test.fkey").Eq(depiq.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 (*SelectDataset) Insert 

func (sd *SelectDataset) Insert() *InsertDataset

Creates a new InsertDataset using the FROM of this dataset. This method will also copy over the `WITH` clause to the insert.

Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	type item struct {
		ID      uint32 `db:"id" depiq:"skipinsert"`
		Address string `db:"address"`
		Name    string `db:"name"`
	}
	sql, args, _ := depiq.From("items").Insert().Rows(
		item{Name: "Test1", Address: "111 Test Addr"},
		item{Name: "Test2", Address: "112 Test Addr"},
	).ToSQL()
	fmt.Println(sql, args)

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

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

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

Output:

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

func (*SelectDataset) Intersect 

func (sd *SelectDataset) Intersect(other *SelectDataset) *SelectDataset

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 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	sql, _, _ := depiq.From("test").
		Intersect(depiq.From("test2")).
		ToSQL()
	fmt.Println(sql)
	sql, _, _ = depiq.From("test").
		Limit(1).
		Intersect(depiq.From("test2")).
		ToSQL()
	fmt.Println(sql)
	sql, _, _ = depiq.From("test").
		Limit(1).
		Intersect(depiq.From("test2").
			Order(depiq.C("id").Desc())).
		ToSQL()
	fmt.Println(sql)
}

Output:

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

func (*SelectDataset) IntersectAll 

func (sd *SelectDataset) IntersectAll(other *SelectDataset) *SelectDataset

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 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	sql, _, _ := depiq.From("test").
		IntersectAll(depiq.From("test2")).
		ToSQL()
	fmt.Println(sql)
	sql, _, _ = depiq.From("test").
		Limit(1).
		IntersectAll(depiq.From("test2")).
		ToSQL()
	fmt.Println(sql)
	sql, _, _ = depiq.From("test").
		Limit(1).
		IntersectAll(depiq.From("test2").
			Order(depiq.C("id").Desc())).
		ToSQL()
	fmt.Println(sql)
}

Output:

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

func (*SelectDataset) IsPrepared 

func (sd *SelectDataset) IsPrepared() bool

func (*SelectDataset) Join 

func (sd *SelectDataset) Join(table exp.Expression, condition exp.JoinCondition) *SelectDataset

Alias to InnerJoin. See examples.

Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

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

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

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

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

Output:

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

func (*SelectDataset) LeftJoin 

func (sd *SelectDataset) LeftJoin(table exp.Expression, condition exp.JoinCondition) *SelectDataset

Adds a LEFT JOIN clause. See examples.

Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

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

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

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

	sql, _, _ = depiq.From("test").LeftJoin(
		depiq.From("test2").Where(depiq.C("amount").Gt(0)).As("t"),
		depiq.On(depiq.I("test.fkey").Eq(depiq.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 (*SelectDataset) LeftOuterJoin 

func (sd *SelectDataset) LeftOuterJoin(table exp.Expression, condition exp.JoinCondition) *SelectDataset

Adds a LEFT OUTER JOIN clause. See examples.

Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

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

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

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

	sql, _, _ = depiq.From("test").LeftOuterJoin(
		depiq.From("test2").Where(depiq.C("amount").Gt(0)).As("t"),
		depiq.On(depiq.I("test.fkey").Eq(depiq.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 (*SelectDataset) Limit 

func (sd *SelectDataset) Limit(limit uint) *SelectDataset

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

Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	ds := depiq.From("test").Limit(10)
	sql, _, _ := ds.ToSQL()
	fmt.Println(sql)
}

Output:

SELECT * FROM "test" LIMIT 10

func (*SelectDataset) LimitAll 

func (sd *SelectDataset) LimitAll() *SelectDataset

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

Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	ds := depiq.From("test").LimitAll()
	sql, _, _ := ds.ToSQL()
	fmt.Println(sql)
}

Output:

SELECT * FROM "test" LIMIT ALL

func (*SelectDataset) NaturalFullJoin 

func (sd *SelectDataset) NaturalFullJoin(table exp.Expression) *SelectDataset

Adds a NATURAL FULL JOIN clause. See examples.

Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	sql, _, _ := depiq.From("test").NaturalFullJoin(depiq.T("test2")).ToSQL()
	fmt.Println(sql)

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

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

Output:

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

func (*SelectDataset) NaturalJoin 

func (sd *SelectDataset) NaturalJoin(table exp.Expression) *SelectDataset

Adds a NATURAL JOIN clause. See examples.

Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	sql, _, _ := depiq.From("test").NaturalJoin(depiq.T("test2")).ToSQL()
	fmt.Println(sql)

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

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

Output:

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

func (*SelectDataset) NaturalLeftJoin 

func (sd *SelectDataset) NaturalLeftJoin(table exp.Expression) *SelectDataset

Adds a NATURAL LEFT JOIN clause. See examples.

Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	sql, _, _ := depiq.From("test").NaturalLeftJoin(depiq.T("test2")).ToSQL()
	fmt.Println(sql)

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

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

Output:

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

func (*SelectDataset) NaturalRightJoin 

func (sd *SelectDataset) NaturalRightJoin(table exp.Expression) *SelectDataset

Adds a NATURAL RIGHT JOIN clause. See examples.

Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	sql, _, _ := depiq.From("test").NaturalRightJoin(depiq.T("test2")).ToSQL()
	fmt.Println(sql)

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

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

Output:

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

func (*SelectDataset) Offset 

func (sd *SelectDataset) Offset(offset uint) *SelectDataset

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

Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	ds := depiq.From("test").Offset(2)
	sql, _, _ := ds.ToSQL()
	fmt.Println(sql)
}

Output:

SELECT * FROM "test" OFFSET 2

func (*SelectDataset) Order 

func (sd *SelectDataset) Order(order ...exp.OrderedExpression) *SelectDataset

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

Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	ds := depiq.From("test").Order(depiq.C("a").Asc())
	sql, _, _ := ds.ToSQL()
	fmt.Println(sql)
}

Output:

SELECT * FROM "test" ORDER BY "a" ASC
Example (CaseExpression) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

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

Output:

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

func (*SelectDataset) OrderAppend 

func (sd *SelectDataset) OrderAppend(order ...exp.OrderedExpression) *SelectDataset

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 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	ds := depiq.From("test").Order(depiq.C("a").Asc())
	sql, _, _ := ds.OrderAppend(depiq.C("b").Desc().NullsLast()).ToSQL()
	fmt.Println(sql)
}

Output:

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

func (*SelectDataset) OrderPrepend 

func (sd *SelectDataset) OrderPrepend(order ...exp.OrderedExpression) *SelectDataset

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

Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	ds := depiq.From("test").Order(depiq.C("a").Asc())
	sql, _, _ := ds.OrderPrepend(depiq.C("b").Desc().NullsLast()).ToSQL()
	fmt.Println(sql)
}

Output:

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

func (*SelectDataset) Pluck 

func (sd *SelectDataset) Pluck(i interface{}, col string) error

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

i: A slice of primitive values

col: The column to select when generative the SQL

Example 
package main

import (
	goSQL "database/sql"
	"fmt"
	"os"
	"time"

	"github.com/lib/pq"
	"github.com/orn-id/depiq"
)

const schema = `
		DROP TABLE IF EXISTS "user_role";
		DROP TABLE IF EXISTS "depiq_user";	
		CREATE  TABLE "depiq_user" (
			"id" SERIAL PRIMARY KEY NOT NULL,
			"first_name" VARCHAR(45) NOT NULL,
			"last_name" VARCHAR(45) NOT NULL,
			"created" TIMESTAMP NOT NULL DEFAULT now()
		);
		CREATE  TABLE "user_role" (
			"id" SERIAL PRIMARY KEY NOT NULL,
			"user_id" BIGINT NOT NULL REFERENCES depiq_user(id) ON DELETE CASCADE,
			"name" VARCHAR(45) NOT NULL,
			"created" TIMESTAMP NOT NULL DEFAULT now()
		); 
    `

const defaultDBURI = "postgres://postgres:@localhost:5435/depiqpostgres?sslmode=disable"

var depiqDB *depiq.Database

func getDB() *depiq.Database {
	if depiqDB == nil {
		dbURI := os.Getenv("PG_URI")
		if dbURI == "" {
			dbURI = defaultDBURI
		}
		uri, err := pq.ParseURL(dbURI)
		if err != nil {
			panic(err)
		}
		pdb, err := goSQL.Open("postgres", uri)
		if err != nil {
			panic(err)
		}
		depiqDB = depiq.New("postgres", pdb)
	}

	if _, err := depiqDB.Exec(schema); err != nil {
		panic(err)
	}
	type depiqUser struct {
		ID        int64     `db:"id" depiq:"skipinsert"`
		FirstName string    `db:"first_name"`
		LastName  string    `db:"last_name"`
		Created   time.Time `db:"created" depiq:"skipupdate"`
	}

	users := []depiqUser{
		{FirstName: "Bob", LastName: "Yukon"},
		{FirstName: "Sally", LastName: "Yukon"},
		{FirstName: "Vinita", LastName: "Yukon"},
		{FirstName: "John", LastName: "Doe"},
	}
	var userIds []int64
	err := depiqDB.Insert("depiq_user").Rows(users).Returning("id").Executor().ScanVals(&userIds)
	if err != nil {
		panic(err)
	}
	type userRole struct {
		ID      int64     `db:"id" depiq:"skipinsert"`
		UserID  int64     `db:"user_id"`
		Name    string    `db:"name"`
		Created time.Time `db:"created" depiq:"skipupdate"`
	}

	roles := []userRole{
		{UserID: userIds[0], Name: "Admin"},
		{UserID: userIds[1], Name: "Manager"},
		{UserID: userIds[2], Name: "Manager"},
		{UserID: userIds[3], Name: "User"},
	}
	_, err = depiqDB.Insert("user_role").Rows(roles).Executor().Exec()
	if err != nil {
		panic(err)
	}
	return depiqDB
}

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

}

Output:

LastNames = [Yukon Yukon Yukon Doe]

func (*SelectDataset) PluckContext 

func (sd *SelectDataset) 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 (*SelectDataset) Prepared 

func (sd *SelectDataset) Prepared(prepared bool) *SelectDataset

Set the parameter interpolation behavior. See examples

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

Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	sql, args, _ := depiq.From("items").Prepared(true).Where(depiq.Ex{
		"col1": "a",
		"col2": 1,
		"col3": true,
		"col4": false,
		"col5": []string{"a", "b", "c"},
	}).ToSQL()
	fmt.Println(sql, args)
	// nolint:lll // sql statements are long
	

Output:

func (*SelectDataset) ReturnsColumns 

func (sd *SelectDataset) ReturnsColumns() bool

func (*SelectDataset) RightJoin 

func (sd *SelectDataset) RightJoin(table exp.Expression, condition exp.JoinCondition) *SelectDataset

Adds a RIGHT JOIN clause. See examples.

Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

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

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

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

	sql, _, _ = depiq.From("test").RightJoin(
		depiq.From("test2").Where(depiq.C("amount").Gt(0)).As("t"),
		depiq.On(depiq.I("test.fkey").Eq(depiq.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 (*SelectDataset) RightOuterJoin 

func (sd *SelectDataset) RightOuterJoin(table exp.Expression, condition exp.JoinCondition) *SelectDataset

Adds a RIGHT OUTER JOIN clause. See examples.

Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

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

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

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

	sql, _, _ = depiq.From("test").RightOuterJoin(
		depiq.From("test2").Where(depiq.C("amount").Gt(0)).As("t"),
		depiq.On(depiq.I("test.fkey").Eq(depiq.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 (*SelectDataset) ScanVal 

func (sd *SelectDataset) ScanVal(i interface{}) (bool, error)

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

i: A pointer to a primitive value

Example 
package main

import (
	goSQL "database/sql"
	"fmt"
	"os"
	"time"

	"github.com/lib/pq"
	"github.com/orn-id/depiq"
)

const schema = `
		DROP TABLE IF EXISTS "user_role";
		DROP TABLE IF EXISTS "depiq_user";	
		CREATE  TABLE "depiq_user" (
			"id" SERIAL PRIMARY KEY NOT NULL,
			"first_name" VARCHAR(45) NOT NULL,
			"last_name" VARCHAR(45) NOT NULL,
			"created" TIMESTAMP NOT NULL DEFAULT now()
		);
		CREATE  TABLE "user_role" (
			"id" SERIAL PRIMARY KEY NOT NULL,
			"user_id" BIGINT NOT NULL REFERENCES depiq_user(id) ON DELETE CASCADE,
			"name" VARCHAR(45) NOT NULL,
			"created" TIMESTAMP NOT NULL DEFAULT now()
		); 
    `

const defaultDBURI = "postgres://postgres:@localhost:5435/depiqpostgres?sslmode=disable"

var depiqDB *depiq.Database

func getDB() *depiq.Database {
	if depiqDB == nil {
		dbURI := os.Getenv("PG_URI")
		if dbURI == "" {
			dbURI = defaultDBURI
		}
		uri, err := pq.ParseURL(dbURI)
		if err != nil {
			panic(err)
		}
		pdb, err := goSQL.Open("postgres", uri)
		if err != nil {
			panic(err)
		}
		depiqDB = depiq.New("postgres", pdb)
	}

	if _, err := depiqDB.Exec(schema); err != nil {
		panic(err)
	}
	type depiqUser struct {
		ID        int64     `db:"id" depiq:"skipinsert"`
		FirstName string    `db:"first_name"`
		LastName  string    `db:"last_name"`
		Created   time.Time `db:"created" depiq:"skipupdate"`
	}

	users := []depiqUser{
		{FirstName: "Bob", LastName: "Yukon"},
		{FirstName: "Sally", LastName: "Yukon"},
		{FirstName: "Vinita", LastName: "Yukon"},
		{FirstName: "John", LastName: "Doe"},
	}
	var userIds []int64
	err := depiqDB.Insert("depiq_user").Rows(users).Returning("id").Executor().ScanVals(&userIds)
	if err != nil {
		panic(err)
	}
	type userRole struct {
		ID      int64     `db:"id" depiq:"skipinsert"`
		UserID  int64     `db:"user_id"`
		Name    string    `db:"name"`
		Created time.Time `db:"created" depiq:"skipupdate"`
	}

	roles := []userRole{
		{UserID: userIds[0], Name: "Admin"},
		{UserID: userIds[1], Name: "Manager"},
		{UserID: userIds[2], Name: "Manager"},
		{UserID: userIds[3], Name: "User"},
	}
	_, err = depiqDB.Insert("user_role").Rows(roles).Executor().Exec()
	if err != nil {
		panic(err)
	}
	return depiqDB
}

func main() {
	db := getDB()
	findUserIDByName := func(name string) {
		var id int64
		ds := db.From("depiq_user").
			Select("id").
			Where(depiq.C("first_name").Eq(name))

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

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

Output:

Found userId: 1
No id found for user Zeb

func (*SelectDataset) ScanValContext 

func (sd *SelectDataset) 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 (*SelectDataset) ScanVals 

func (sd *SelectDataset) ScanVals(i interface{}) error

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

i: A pointer to a slice of primitive values

Example 
package main

import (
	goSQL "database/sql"
	"fmt"
	"os"
	"time"

	"github.com/lib/pq"
	"github.com/orn-id/depiq"
)

const schema = `
		DROP TABLE IF EXISTS "user_role";
		DROP TABLE IF EXISTS "depiq_user";	
		CREATE  TABLE "depiq_user" (
			"id" SERIAL PRIMARY KEY NOT NULL,
			"first_name" VARCHAR(45) NOT NULL,
			"last_name" VARCHAR(45) NOT NULL,
			"created" TIMESTAMP NOT NULL DEFAULT now()
		);
		CREATE  TABLE "user_role" (
			"id" SERIAL PRIMARY KEY NOT NULL,
			"user_id" BIGINT NOT NULL REFERENCES depiq_user(id) ON DELETE CASCADE,
			"name" VARCHAR(45) NOT NULL,
			"created" TIMESTAMP NOT NULL DEFAULT now()
		); 
    `

const defaultDBURI = "postgres://postgres:@localhost:5435/depiqpostgres?sslmode=disable"

var depiqDB *depiq.Database

func getDB() *depiq.Database {
	if depiqDB == nil {
		dbURI := os.Getenv("PG_URI")
		if dbURI == "" {
			dbURI = defaultDBURI
		}
		uri, err := pq.ParseURL(dbURI)
		if err != nil {
			panic(err)
		}
		pdb, err := goSQL.Open("postgres", uri)
		if err != nil {
			panic(err)
		}
		depiqDB = depiq.New("postgres", pdb)
	}

	if _, err := depiqDB.Exec(schema); err != nil {
		panic(err)
	}
	type depiqUser struct {
		ID        int64     `db:"id" depiq:"skipinsert"`
		FirstName string    `db:"first_name"`
		LastName  string    `db:"last_name"`
		Created   time.Time `db:"created" depiq:"skipupdate"`
	}

	users := []depiqUser{
		{FirstName: "Bob", LastName: "Yukon"},
		{FirstName: "Sally", LastName: "Yukon"},
		{FirstName: "Vinita", LastName: "Yukon"},
		{FirstName: "John", LastName: "Doe"},
	}
	var userIds []int64
	err := depiqDB.Insert("depiq_user").Rows(users).Returning("id").Executor().ScanVals(&userIds)
	if err != nil {
		panic(err)
	}
	type userRole struct {
		ID      int64     `db:"id" depiq:"skipinsert"`
		UserID  int64     `db:"user_id"`
		Name    string    `db:"name"`
		Created time.Time `db:"created" depiq:"skipupdate"`
	}

	roles := []userRole{
		{UserID: userIds[0], Name: "Admin"},
		{UserID: userIds[1], Name: "Manager"},
		{UserID: userIds[2], Name: "Manager"},
		{UserID: userIds[3], Name: "User"},
	}
	_, err = depiqDB.Insert("user_role").Rows(roles).Executor().Exec()
	if err != nil {
		panic(err)
	}
	return depiqDB
}

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

}

Output:

UserIds = [1 2 3 4]

func (*SelectDataset) ScanValsContext 

func (sd *SelectDataset) 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 (*SelectDataset) Select 

func (sd *SelectDataset) Select(selects ...interface{}) *SelectDataset

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 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	sql, _, _ := depiq.From("test").Select("a", "b", "c").ToSQL()
	fmt.Println(sql)
}

Output:

SELECT "a", "b", "c" FROM "test"
Example (WithAliasedDataset) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	ds := depiq.From("test")
	fromDs := ds.Select("age").Where(depiq.C("age").Gt(10))
	sql, _, _ := ds.From().Select(fromDs.As("ages")).ToSQL()
	fmt.Println(sql)
}

Output:

SELECT (SELECT "age" FROM "test" WHERE ("age" > 10)) AS "ages"
Example (WithDataset) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	ds := depiq.From("test")
	fromDs := ds.Select("age").Where(depiq.C("age").Gt(10))
	sql, _, _ := ds.From().Select(fromDs).ToSQL()
	fmt.Println(sql)
}

Output:

SELECT (SELECT "age" FROM "test" WHERE ("age" > 10))
Example (WithLiteral) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	sql, _, _ := depiq.From("test").Select(depiq.L("a + b").As("sum")).ToSQL()
	fmt.Println(sql)
}

Output:

SELECT a + b AS "sum" FROM "test"
Example (WithSQLFunctionExpression) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	sql, _, _ := depiq.From("test").Select(
		depiq.COUNT("*").As("age_count"),
		depiq.MAX("age").As("max_age"),
		depiq.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) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	ds := depiq.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 (*SelectDataset) SelectAppend 

func (sd *SelectDataset) SelectAppend(selects ...interface{}) *SelectDataset

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 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	ds := depiq.From("test").Select("a", "b")
	sql, _, _ := ds.SelectAppend("c").ToSQL()
	fmt.Println(sql)
	ds = depiq.From("test").Select("a", "b").Distinct()
	sql, _, _ = ds.SelectAppend("c").ToSQL()
	fmt.Println(sql)
}

Output:

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

func (*SelectDataset) SelectDistinct 

func (sd *SelectDataset) SelectDistinct(selects ...interface{}) *SelectDataset

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

Deprecated: Use Distinct() instead.

func (*SelectDataset) SetDialect 

func (sd *SelectDataset) SetDialect(dialect SQLDialect) *SelectDataset

Returns the current adapter on the dataset

func (*SelectDataset) SetError 

func (sd *SelectDataset) SetError(err error) *SelectDataset

Set an error on the dataset if one has not already been set. This error will be returned by a future call to Error or as part of ToSQL. This can be used by end users to record errors while building up queries without having to track those separately.

func (*SelectDataset) ToSQL 

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

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

Errors:

  • There is an error generating the SQL
Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

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

Output:

SELECT * FROM "items" WHERE ("a" = 1) []
Example (Prepared) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	sql, args, _ := depiq.From("items").Where(depiq.Ex{"a": 1}).Prepared(true).ToSQL()
	fmt.Println(sql, args)
}

Output:

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

func (*SelectDataset) Truncate 

func (sd *SelectDataset) Truncate() *TruncateDataset

Creates a new TruncateDataset using the FROM of this dataset.

Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	sql, args, _ := depiq.From("items").Truncate().ToSQL()
	fmt.Println(sql, args)
}

Output:

TRUNCATE "items" []

func (*SelectDataset) Union 

func (sd *SelectDataset) Union(other *SelectDataset) *SelectDataset

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 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	sql, _, _ := depiq.From("test").
		Union(depiq.From("test2")).
		ToSQL()
	fmt.Println(sql)

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

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

Output:

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

func (*SelectDataset) UnionAll 

func (sd *SelectDataset) UnionAll(other *SelectDataset) *SelectDataset

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 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	sql, _, _ := depiq.From("test").
		UnionAll(depiq.From("test2")).
		ToSQL()
	fmt.Println(sql)
	sql, _, _ = depiq.From("test").
		Limit(1).
		UnionAll(depiq.From("test2")).
		ToSQL()
	fmt.Println(sql)
	sql, _, _ = depiq.From("test").
		Limit(1).
		UnionAll(depiq.From("test2").
			Order(depiq.C("id").Desc())).
		ToSQL()
	fmt.Println(sql)
}

Output:

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

func (*SelectDataset) Update 

func (sd *SelectDataset) Update() *UpdateDataset

Creates a new UpdateDataset using the FROM of this dataset. This method will also copy over the `WITH`, `WHERE`, `ORDER , and `LIMIT`

Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	type item struct {
		Address string `db:"address"`
		Name    string `db:"name"`
	}
	sql, args, _ := depiq.From("items").Update().Set(
		item{Name: "Test", Address: "111 Test Addr"},
	).ToSQL()
	fmt.Println(sql, args)

	sql, args, _ = depiq.From("items").Update().Set(
		depiq.Record{"name": "Test", "address": "111 Test Addr"},
	).ToSQL()
	fmt.Println(sql, args)

	sql, args, _ = depiq.From("items").Update().Set(
		map[string]interface{}{"name": "Test", "address": "111 Test Addr"},
	).ToSQL()
	fmt.Println(sql, args)

}

Output:

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

func (*SelectDataset) Where 

func (sd *SelectDataset) Where(expressions ...exp.Expression) *SelectDataset

Adds a WHERE clause. See examples.

Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	// By default everything is anded together
	sql, _, _ := depiq.From("test").Where(depiq.Ex{
		"a": depiq.Op{"gt": 10},
		"b": depiq.Op{"lt": 10},
		"c": nil,
		"d": []string{"a", "b", "c"},
	}).ToSQL()
	fmt.Println(sql)
	// You can use ExOr to get ORed expressions together
	sql, _, _ = depiq.From("test").Where(depiq.ExOr{
		"a": depiq.Op{"gt": 10},
		"b": depiq.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, _, _ = depiq.From("test").Where(
		depiq.Or(
			depiq.Ex{
				"a": depiq.Op{"gt": 10},
				"b": depiq.Op{"lt": 10},
			},
			depiq.Ex{
				"c": nil,
				"d": []string{"a", "b", "c"},
			},
		),
	).ToSQL()
	fmt.Println(sql)
	// By default everything is anded together
	sql, _, _ = depiq.From("test").Where(
		depiq.C("a").Gt(10),
		depiq.C("b").Lt(10),
		depiq.C("c").IsNull(),
		depiq.C("d").In("a", "b", "c"),
	).ToSQL()
	fmt.Println(sql)
	// You can use a combination of Ors and Ands
	sql, _, _ = depiq.From("test").Where(
		depiq.Or(
			depiq.C("a").Gt(10),
			depiq.And(
				depiq.C("b").Lt(10),
				depiq.C("c").IsNull(),
			),
		),
	).ToSQL()
	fmt.Println(sql)
}

Output:

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

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	// By default everything is anded together
	sql, args, _ := depiq.From("test").Prepared(true).Where(depiq.Ex{
		"a": depiq.Op{"gt": 10},
		"b": depiq.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, _ = depiq.From("test").Prepared(true).Where(depiq.ExOr{
		"a": depiq.Op{"gt": 10},
		"b": depiq.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, _ = depiq.From("test").Prepared(true).Where(
		depiq.Or(
			depiq.Ex{
				"a": depiq.Op{"gt": 10},
				"b": depiq.Op{"lt": 10},
			},
			depiq.Ex{
				"c": nil,
				"d": []string{"a", "b", "c"},
			},
		),
	).ToSQL()
	fmt.Println(sql, args)
	// By default everything is anded together
	sql, args, _ = depiq.From("test").Prepared(true).Where(
		depiq.C("a").Gt(10),
		depiq.C("b").Lt(10),
		depiq.C("c").IsNull(),
		depiq.C("d").In("a", "b", "c"),
	).ToSQL()
	fmt.Println(sql, args)
	// You can use a combination of Ors and Ands
	sql, args, _ = depiq.From("test").Prepared(true).Where(
		depiq.Or(
			depiq.C("a").Gt(10),
			depiq.And(
				depiq.C("b").Lt(10),
				depiq.C("c").IsNull(),
			),
		),
	).ToSQL()
	fmt.Println(sql, args)
}

Output:

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

func (*SelectDataset) Window 

func (sd *SelectDataset) Window(ws ...exp.WindowExpression) *SelectDataset

Sets the WINDOW clauses

Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	ds := depiq.From("test").
		Select(depiq.ROW_NUMBER().Over(depiq.W().PartitionBy("a").OrderBy(depiq.I("b").Asc())))
	query, args, _ := ds.ToSQL()
	fmt.Println(query, args)

	ds = depiq.From("test").
		Select(depiq.ROW_NUMBER().OverName(depiq.I("w"))).
		Window(depiq.W("w").PartitionBy("a").OrderBy(depiq.I("b").Asc()))
	query, args, _ = ds.ToSQL()
	fmt.Println(query, args)

	ds = depiq.From("test").
		Select(depiq.ROW_NUMBER().OverName(depiq.I("w1"))).
		Window(
			depiq.W("w1").PartitionBy("a"),
			depiq.W("w").Inherit("w1").OrderBy(depiq.I("b").Asc()),
		)
	query, args, _ = ds.ToSQL()
	fmt.Println(query, args)

	ds = depiq.From("test").
		Select(depiq.ROW_NUMBER().Over(depiq.W().Inherit("w").OrderBy("b"))).
		Window(depiq.W("w").PartitionBy("a"))
	query, args, _ = ds.ToSQL()
	fmt.Println(query, args)
	// Output
	// SELECT ROW_NUMBER() OVER (PARTITION BY "a" ORDER BY "b" ASC) FROM "test" []
	// SELECT ROW_NUMBER() OVER "w" FROM "test" WINDOW "w" AS (PARTITION BY "a" ORDER BY "b" ASC) []
	// SELECT ROW_NUMBER() OVER "w" FROM "test" WINDOW "w1" AS (PARTITION BY "a"), "w" AS ("w1" ORDER BY "b" ASC) []
	// SELECT ROW_NUMBER() OVER ("w" ORDER BY "b") FROM "test" WINDOW "w" AS (PARTITION BY "a") []
}

Output:

func (*SelectDataset) WindowAppend 

func (sd *SelectDataset) WindowAppend(ws ...exp.WindowExpression) *SelectDataset

Sets the WINDOW clauses

func (*SelectDataset) With 

func (sd *SelectDataset) With(name string, subquery exp.Expression) *SelectDataset

Creates a WITH clause for a common table expression (CTE).

The name will be available to SELECT from in the associated query; and can optionally contain a list of column names "name(col1, col2, col3)".

The name will refer to the results of the specified subquery.

Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	sql, _, _ := depiq.From("one").
		With("one", depiq.From().Select(depiq.L("1"))).
		Select(depiq.Star()).
		ToSQL()
	fmt.Println(sql)
	sql, _, _ = depiq.From("derived").
		With("intermed", depiq.From("test").Select(depiq.Star()).Where(depiq.C("x").Gte(5))).
		With("derived", depiq.From("intermed").Select(depiq.Star()).Where(depiq.C("x").Lt(10))).
		Select(depiq.Star()).
		ToSQL()
	fmt.Println(sql)
	sql, _, _ = depiq.From("multi").
		With("multi(x,y)", depiq.From().Select(depiq.L("1"), depiq.L("2"))).
		Select(depiq.C("x"), depiq.C("y")).
		ToSQL()
	fmt.Println(sql)

}

Output:

WITH one AS (SELECT 1) SELECT * FROM "one"
WITH intermed AS (SELECT * FROM "test" WHERE ("x" >= 5)), derived AS (SELECT * FROM "intermed" WHERE ("x" < 10)) SELECT * FROM "derived"
WITH multi(x,y) AS (SELECT 1, 2) SELECT "x", "y" FROM "multi"
Example (DeleteDataset) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

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

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

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

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

Output:

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

import (
	"fmt"

	"github.com/orn-id/depiq"
)

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

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

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

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

}

Output:

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

import (
	"fmt"

	"github.com/orn-id/depiq"
)

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

	ds := depiq.From("bar").
		With("upd", updateDs).
		Select("bar_name").
		Where(depiq.Ex{"bar.user_id": depiq.I("upd.user_id")})

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

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

}

Output:

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

func (*SelectDataset) WithDialect 

func (sd *SelectDataset) WithDialect(dl string) *SelectDataset

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

func (*SelectDataset) WithRecursive 

func (sd *SelectDataset) WithRecursive(name string, subquery exp.Expression) *SelectDataset

Creates a WITH RECURSIVE clause for a common table expression (CTE)

The name will be available to SELECT from in the associated query; and must contain a list of column names "name(col1, col2, col3)" for a recursive clause.

The name will refer to the results of the specified subquery. The subquery for a recursive query will always end with a UNION or UNION ALL with a clause that refers to the CTE by name.

Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	sql, _, _ := depiq.From("nums").
		WithRecursive("nums(x)",
			depiq.From().Select(depiq.L("1")).
				UnionAll(depiq.From("nums").
					Select(depiq.L("x+1")).Where(depiq.C("x").Lt(5)))).
		ToSQL()
	fmt.Println(sql)
}

Output:

WITH RECURSIVE nums(x) AS (SELECT 1 UNION ALL (SELECT x+1 FROM "nums" WHERE ("x" < 5))) SELECT * FROM "nums"

type TruncateDataset 

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

func Truncate 

func Truncate(table ...interface{}) *TruncateDataset

func (*TruncateDataset) Cascade 

func (td *TruncateDataset) Cascade() *TruncateDataset

Adds a CASCADE clause

func (*TruncateDataset) Clone 

func (td *TruncateDataset) Clone() exp.Expression

Clones the dataset

func (*TruncateDataset) Dialect 

func (td *TruncateDataset) Dialect() SQLDialect

Returns the current adapter on the dataset

func (*TruncateDataset) Error 

func (td *TruncateDataset) Error() error

Get any error that has been set or nil if no error has been set.

func (*TruncateDataset) Executor 

func (td *TruncateDataset) Executor() exec.QueryExecutor

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

db.From("test").Truncate().Executor().Exec()

func (*TruncateDataset) Expression 

func (td *TruncateDataset) Expression() exp.Expression

func (*TruncateDataset) GetClauses 

func (td *TruncateDataset) GetClauses() exp.TruncateClauses

Returns the current clauses on the dataset.

func (*TruncateDataset) Identity 

func (td *TruncateDataset) Identity(identity string) *TruncateDataset

Add a IDENTITY clause (e.g. RESTART)

func (*TruncateDataset) IsPrepared 

func (td *TruncateDataset) IsPrepared() bool

func (*TruncateDataset) NoCascade 

func (td *TruncateDataset) NoCascade() *TruncateDataset

Clears the CASCADE clause

func (*TruncateDataset) NoRestrict 

func (td *TruncateDataset) NoRestrict() *TruncateDataset

Clears the RESTRICT clause

func (*TruncateDataset) Prepared 

func (td *TruncateDataset) Prepared(prepared bool) *TruncateDataset

Set the parameter interpolation behavior. See examples

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

func (*TruncateDataset) Restrict 

func (td *TruncateDataset) Restrict() *TruncateDataset

Adds a RESTRICT clause

func (*TruncateDataset) SetDialect 

func (td *TruncateDataset) SetDialect(dialect SQLDialect) *TruncateDataset

Returns the current adapter on the dataset

func (*TruncateDataset) SetError 

func (td *TruncateDataset) SetError(err error) *TruncateDataset

Set an error on the dataset if one has not already been set. This error will be returned by a future call to Error or as part of ToSQL. This can be used by end users to record errors while building up queries without having to track those separately.

func (*TruncateDataset) Table 

func (td *TruncateDataset) Table(table ...interface{}) *TruncateDataset

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
IdentifierExpression
LiteralExpression: (See Literal) Will use the literal SQL

func (*TruncateDataset) ToSQL 

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

Generates a TRUNCATE 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

func (*TruncateDataset) WithDialect 

func (td *TruncateDataset) WithDialect(dl string) *TruncateDataset

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

type TruncateOptions 

type TruncateOptions = exp.TruncateOptions

Options to use when generating a TRUNCATE statement

type TxDatabase 

type TxDatabase struct {
	Tx SQLTx
	// contains filtered or unexported fields
}

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

func NewTx 

func NewTx(dialect string, tx SQLTx) *TxDatabase

Creates a new TxDatabase

func (*TxDatabase) Commit 

func (td *TxDatabase) Commit() error

COMMIT the transaction

func (*TxDatabase) Delete 

func (td *TxDatabase) Delete(table interface{}) *DeleteDataset

func (*TxDatabase) Dialect 

func (td *TxDatabase) Dialect() string

returns this databases dialect

func (*TxDatabase) Exec 

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

See Database#Exec

func (*TxDatabase) ExecContext 

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

See Database#ExecContext

func (*TxDatabase) From 

func (td *TxDatabase) From(cols ...interface{}) *SelectDataset

Creates a new Dataset for querying a Database.

func (*TxDatabase) Insert 

func (td *TxDatabase) Insert(table interface{}) *InsertDataset

func (*TxDatabase) Logger 

func (td *TxDatabase) Logger(logger Logger)

Sets the logger

func (*TxDatabase) Prepare 

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

See Database#Prepare

func (*TxDatabase) PrepareContext 

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

See Database#PrepareContext

func (*TxDatabase) Query 

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

See Database#Query

func (*TxDatabase) QueryContext 

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

See Database#QueryContext

func (*TxDatabase) QueryRow 

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

See Database#QueryRow

func (*TxDatabase) QueryRowContext 

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

See Database#QueryRowContext

func (*TxDatabase) Rollback 

func (td *TxDatabase) Rollback() error

ROLLBACK the transaction

func (*TxDatabase) ScanStruct 

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

See Database#ScanStruct

func (*TxDatabase) ScanStructContext 

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

See Database#ScanStructContext

func (*TxDatabase) ScanStructs 

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

See Database#ScanStructs

func (*TxDatabase) ScanStructsContext 

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

See Database#ScanStructsContext

func (*TxDatabase) ScanVal 

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

See Database#ScanVal

func (*TxDatabase) ScanValContext 

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

See Database#ScanValContext

func (*TxDatabase) ScanVals 

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

See Database#ScanVals

func (*TxDatabase) ScanValsContext 

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

See Database#ScanValsContext

func (*TxDatabase) Select 

func (td *TxDatabase) Select(cols ...interface{}) *SelectDataset

func (*TxDatabase) Trace 

func (td *TxDatabase) Trace(op, sqlString string, args ...interface{})

func (*TxDatabase) Truncate 

func (td *TxDatabase) Truncate(table ...interface{}) *TruncateDataset

func (*TxDatabase) Update 

func (td *TxDatabase) Update(table interface{}) *UpdateDataset

func (*TxDatabase) Wrap 

func (td *TxDatabase) Wrap(fn func() error) (err 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 UpdateDataset 

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

func Update 

func Update(table interface{}) *UpdateDataset
Example (WithGoquRecord) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
	_ "github.com/orn-id/depiq/dialect/mysql"
)

func main() {
	sql, args, _ := depiq.Update("items").Set(
		depiq.Record{"name": "Test", "address": "111 Test Addr"},
	).ToSQL()
	fmt.Println(sql, args)

}

Output:

UPDATE "items" SET "address"='111 Test Addr',"name"='Test' []
Example (WithMap) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
	_ "github.com/orn-id/depiq/dialect/mysql"
)

func main() {
	sql, args, _ := depiq.Update("items").Set(
		map[string]interface{}{"name": "Test", "address": "111 Test Addr"},
	).ToSQL()
	fmt.Println(sql, args)

}

Output:

UPDATE "items" SET "address"='111 Test Addr',"name"='Test' []
Example (WithSkipUpdateTag) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
	_ "github.com/orn-id/depiq/dialect/mysql"
)

func main() {
	type item struct {
		Address string `db:"address"`
		Name    string `db:"name" depiq:"skipupdate"`
	}
	sql, args, _ := depiq.Update("items").Set(
		item{Name: "Test", Address: "111 Test Addr"},
	).ToSQL()
	fmt.Println(sql, args)

}

Output:

UPDATE "items" SET "address"='111 Test Addr' []
Example (WithStruct) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
	_ "github.com/orn-id/depiq/dialect/mysql"
)

func main() {
	type item struct {
		Address string `db:"address"`
		Name    string `db:"name"`
	}
	sql, args, _ := depiq.Update("items").Set(
		item{Name: "Test", Address: "111 Test Addr"},
	).ToSQL()
	fmt.Println(sql, args)

}

Output:

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

func (*UpdateDataset) AppendSQL 

func (ud *UpdateDataset) AppendSQL(b sb.SQLBuilder)

Appends this Dataset's UPDATE statement to the SQLBuilder This is used internally when using updates in CTEs

func (*UpdateDataset) ClearLimit 

func (ud *UpdateDataset) ClearLimit() *UpdateDataset

Removes the LIMIT clause.

Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
	_ "github.com/orn-id/depiq/dialect/mysql"
)

func main() {
	ds := depiq.Dialect("mysql").
		Update("test").
		Set(depiq.Record{"foo": "bar"}).
		Limit(10)
	sql, _, _ := ds.ClearLimit().ToSQL()
	fmt.Println(sql)
}

Output:

UPDATE `test` SET `foo`='bar'

func (*UpdateDataset) ClearOrder 

func (ud *UpdateDataset) ClearOrder() *UpdateDataset

Removes the ORDER BY clause. See examples.

Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
	_ "github.com/orn-id/depiq/dialect/mysql"
)

func main() {
	ds := depiq.Dialect("mysql").
		Update("test").
		Set(depiq.Record{"foo": "bar"}).
		Order(depiq.C("a").Asc())
	sql, _, _ := ds.ClearOrder().ToSQL()
	fmt.Println(sql)
}

Output:

UPDATE `test` SET `foo`='bar'

func (*UpdateDataset) ClearWhere 

func (ud *UpdateDataset) ClearWhere() *UpdateDataset

Removes the WHERE clause. See examples.

Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
	_ "github.com/orn-id/depiq/dialect/mysql"
)

func main() {
	ds := depiq.
		Update("test").
		Set(depiq.Record{"foo": "bar"}).
		Where(
			depiq.Or(
				depiq.C("a").Gt(10),
				depiq.And(
					depiq.C("b").Lt(10),
					depiq.C("c").IsNull(),
				),
			),
		)
	sql, _, _ := ds.ClearWhere().ToSQL()
	fmt.Println(sql)
}

Output:

UPDATE "test" SET "foo"='bar'

func (*UpdateDataset) Clone 

func (ud *UpdateDataset) Clone() exp.Expression

Clones the dataset

func (*UpdateDataset) Dialect 

func (ud *UpdateDataset) Dialect() SQLDialect

Returns the current adapter on the dataset

func (*UpdateDataset) Error 

func (ud *UpdateDataset) Error() error

Get any error that has been set or nil if no error has been set.

func (*UpdateDataset) Executor 

func (ud *UpdateDataset) Executor() exec.QueryExecutor

Generates the UPDATE sql, and returns an exec.QueryExecutor with the sql set to the UPDATE statement 

db.Update("test").Set(Record{"name":"Bob", update: time.Now()}).Executor()
Example 
db := getDB()
update := db.Update("depiq_user").
	Where(depiq.C("first_name").Eq("Bob")).
	Set(depiq.Record{"first_name": "Bobby"}).
	Executor()

if r, err := update.Exec(); err != nil {
	fmt.Println(err.Error())
} else {
	c, _ := r.RowsAffected()
	fmt.Printf("Updated %d users", c)
}

Output:

Updated 1 users
Example (Returning) 
db := getDB()
var ids []int64
update := db.Update("depiq_user").
	Set(depiq.Record{"last_name": "ucon"}).
	Where(depiq.Ex{"last_name": "Yukon"}).
	Returning("id").
	Executor()
if err := update.ScanVals(&ids); err != nil {
	fmt.Println(err.Error())
} else {
	fmt.Printf("Updated users with ids %+v", ids)
}

Output:

Updated users with ids [1 2 3]

func (*UpdateDataset) Expression 

func (ud *UpdateDataset) Expression() exp.Expression

func (*UpdateDataset) From 

func (ud *UpdateDataset) From(tables ...interface{}) *UpdateDataset

Allows specifying other tables to reference in your update (If your dialect supports it). See examples.

Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
	_ "github.com/orn-id/depiq/dialect/mysql"
)

func main() {
	ds := depiq.Update("table_one").
		Set(depiq.Record{"foo": depiq.I("table_two.bar")}).
		From("table_two").
		Where(depiq.Ex{"table_one.id": depiq.I("table_two.id")})

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

Output:

UPDATE "table_one" SET "foo"="table_two"."bar" FROM "table_two" WHERE ("table_one"."id" = "table_two"."id")
Example (Mysql) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
	_ "github.com/orn-id/depiq/dialect/mysql"
)

func main() {
	dialect := depiq.Dialect("mysql")

	ds := dialect.Update("table_one").
		Set(depiq.Record{"foo": depiq.I("table_two.bar")}).
		From("table_two").
		Where(depiq.Ex{"table_one.id": depiq.I("table_two.id")})

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

Output:

UPDATE `table_one`,`table_two` SET `foo`=`table_two`.`bar` WHERE (`table_one`.`id` = `table_two`.`id`)
Example (Postgres) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
	_ "github.com/orn-id/depiq/dialect/mysql"
)

func main() {
	dialect := depiq.Dialect("postgres")

	ds := dialect.Update("table_one").
		Set(depiq.Record{"foo": depiq.I("table_two.bar")}).
		From("table_two").
		Where(depiq.Ex{"table_one.id": depiq.I("table_two.id")})

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

Output:

UPDATE "table_one" SET "foo"="table_two"."bar" FROM "table_two" WHERE ("table_one"."id" = "table_two"."id")

func (*UpdateDataset) GetAs 

func (ud *UpdateDataset) GetAs() exp.IdentifierExpression

func (*UpdateDataset) GetClauses 

func (ud *UpdateDataset) GetClauses() exp.UpdateClauses

Returns the current clauses on the dataset.

func (*UpdateDataset) IsPrepared 

func (ud *UpdateDataset) IsPrepared() bool

func (*UpdateDataset) Limit 

func (ud *UpdateDataset) Limit(limit uint) *UpdateDataset

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

Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
	_ "github.com/orn-id/depiq/dialect/mysql"
)

func main() {
	ds := depiq.Dialect("mysql").
		Update("test").
		Set(depiq.Record{"foo": "bar"}).
		Limit(10)
	sql, _, _ := ds.ToSQL()
	fmt.Println(sql)
}

Output:

UPDATE `test` SET `foo`='bar' LIMIT 10

func (*UpdateDataset) LimitAll 

func (ud *UpdateDataset) LimitAll() *UpdateDataset

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

Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
	_ "github.com/orn-id/depiq/dialect/mysql"
)

func main() {
	ds := depiq.Dialect("mysql").
		Update("test").
		Set(depiq.Record{"foo": "bar"}).
		LimitAll()
	sql, _, _ := ds.ToSQL()
	fmt.Println(sql)
}

Output:

UPDATE `test` SET `foo`='bar' LIMIT ALL

func (*UpdateDataset) Order 

func (ud *UpdateDataset) Order(order ...exp.OrderedExpression) *UpdateDataset

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

Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
	_ "github.com/orn-id/depiq/dialect/mysql"
)

func main() {
	ds := depiq.Dialect("mysql").
		Update("test").
		Set(depiq.Record{"foo": "bar"}).
		Order(depiq.C("a").Asc())
	sql, _, _ := ds.ToSQL()
	fmt.Println(sql)
}

Output:

UPDATE `test` SET `foo`='bar' ORDER BY `a` ASC

func (*UpdateDataset) OrderAppend 

func (ud *UpdateDataset) OrderAppend(order ...exp.OrderedExpression) *UpdateDataset

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 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
	_ "github.com/orn-id/depiq/dialect/mysql"
)

func main() {
	ds := depiq.Dialect("mysql").
		Update("test").
		Set(depiq.Record{"foo": "bar"}).
		Order(depiq.C("a").Asc())
	sql, _, _ := ds.OrderAppend(depiq.C("b").Desc().NullsLast()).ToSQL()
	fmt.Println(sql)
}

Output:

UPDATE `test` SET `foo`='bar' ORDER BY `a` ASC, `b` DESC NULLS LAST

func (*UpdateDataset) OrderPrepend 

func (ud *UpdateDataset) OrderPrepend(order ...exp.OrderedExpression) *UpdateDataset

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

Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
	_ "github.com/orn-id/depiq/dialect/mysql"
)

func main() {
	ds := depiq.Dialect("mysql").
		Update("test").
		Set(depiq.Record{"foo": "bar"}).
		Order(depiq.C("a").Asc())

	sql, _, _ := ds.OrderPrepend(depiq.C("b").Desc().NullsLast()).ToSQL()
	fmt.Println(sql)
}

Output:

UPDATE `test` SET `foo`='bar' ORDER BY `b` DESC NULLS LAST, `a` ASC

func (*UpdateDataset) Prepared 

func (ud *UpdateDataset) Prepared(prepared bool) *UpdateDataset

Set the parameter interpolation behavior. See examples

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

Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
	_ "github.com/orn-id/depiq/dialect/mysql"
)

func main() {
	sql, args, _ := depiq.Update("items").Prepared(true).Set(
		depiq.Record{"name": "Test", "address": "111 Test Addr"},
	).ToSQL()
	fmt.Println(sql, args)

}

Output:

UPDATE "items" SET "address"=?,"name"=? [111 Test Addr Test]

func (*UpdateDataset) Returning 

func (ud *UpdateDataset) Returning(returning ...interface{}) *UpdateDataset

Adds a RETURNING clause to the dataset if the adapter supports it. See examples.

Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
	_ "github.com/orn-id/depiq/dialect/mysql"
)

func main() {
	sql, _, _ := depiq.Update("test").
		Set(depiq.Record{"foo": "bar"}).
		Returning("id").
		ToSQL()
	fmt.Println(sql)
	sql, _, _ = depiq.Update("test").
		Set(depiq.Record{"foo": "bar"}).
		Returning(depiq.T("test").All()).
		ToSQL()
	fmt.Println(sql)
	sql, _, _ = depiq.Update("test").
		Set(depiq.Record{"foo": "bar"}).
		Returning("a", "b").
		ToSQL()
	fmt.Println(sql)
}

Output:

UPDATE "test" SET "foo"='bar' RETURNING "id"
UPDATE "test" SET "foo"='bar' RETURNING "test".*
UPDATE "test" SET "foo"='bar' RETURNING "a", "b"

func (*UpdateDataset) ReturnsColumns 

func (ud *UpdateDataset) ReturnsColumns() bool

func (*UpdateDataset) Set 

func (ud *UpdateDataset) Set(values interface{}) *UpdateDataset

Sets the values to use in the SET clause. See examples.

Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
	_ "github.com/orn-id/depiq/dialect/mysql"
)

func main() {
	type item struct {
		Address string `db:"address"`
		Name    string `db:"name"`
	}
	sql, args, _ := depiq.Update("items").Set(
		item{Name: "Test", Address: "111 Test Addr"},
	).ToSQL()
	fmt.Println(sql, args)

	sql, args, _ = depiq.Update("items").Set(
		depiq.Record{"name": "Test", "address": "111 Test Addr"},
	).ToSQL()
	fmt.Println(sql, args)

	sql, args, _ = depiq.Update("items").Set(
		map[string]interface{}{"name": "Test", "address": "111 Test Addr"},
	).ToSQL()
	fmt.Println(sql, args)

}

Output:

UPDATE "items" SET "address"='111 Test Addr',"name"='Test' []
UPDATE "items" SET "address"='111 Test Addr',"name"='Test' []
UPDATE "items" SET "address"='111 Test Addr',"name"='Test' []
Example (DepiqRecord) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
	_ "github.com/orn-id/depiq/dialect/mysql"
)

func main() {
	sql, args, _ := depiq.Update("items").Set(
		depiq.Record{"name": "Test", "address": "111 Test Addr"},
	).ToSQL()
	fmt.Println(sql, args)

}

Output:

UPDATE "items" SET "address"='111 Test Addr',"name"='Test' []
Example (Map) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
	_ "github.com/orn-id/depiq/dialect/mysql"
)

func main() {
	sql, args, _ := depiq.Update("items").Set(
		map[string]interface{}{"name": "Test", "address": "111 Test Addr"},
	).ToSQL()
	fmt.Println(sql, args)

}

Output:

UPDATE "items" SET "address"='111 Test Addr',"name"='Test' []
Example (Struct) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
	_ "github.com/orn-id/depiq/dialect/mysql"
)

func main() {
	type item struct {
		Address string `db:"address"`
		Name    string `db:"name"`
	}
	sql, args, _ := depiq.Update("items").Set(
		item{Name: "Test", Address: "111 Test Addr"},
	).ToSQL()
	fmt.Println(sql, args)

}

Output:

UPDATE "items" SET "address"='111 Test Addr',"name"='Test' []
Example (WithDefaultIfEmptyTag) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
	_ "github.com/orn-id/depiq/dialect/mysql"
)

func main() {
	type item struct {
		Address string `db:"address"`
		Name    string `db:"name" depiq:"defaultifempty"`
	}
	sql, args, _ := depiq.Update("items").Set(
		item{Address: "111 Test Addr"},
	).ToSQL()
	fmt.Println(sql, args)

	sql, args, _ = depiq.Update("items").Set(
		item{Name: "Bob Yukon", Address: "111 Test Addr"},
	).ToSQL()
	fmt.Println(sql, args)

}

Output:

UPDATE "items" SET "address"='111 Test Addr',"name"=DEFAULT []
UPDATE "items" SET "address"='111 Test Addr',"name"='Bob Yukon' []
Example (WithEmbeddedStruct) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
	_ "github.com/orn-id/depiq/dialect/mysql"
)

func main() {
	type Address struct {
		Street string `db:"address_street"`
		State  string `db:"address_state"`
	}
	type User struct {
		Address
		FirstName string
		LastName  string
	}
	ds := depiq.Update("user").Set(
		User{Address: Address{Street: "111 Street", State: "NY"}, FirstName: "Greg", LastName: "Farley"},
	)
	updateSQL, args, _ := ds.ToSQL()
	fmt.Println(updateSQL, args)

}

Output:

UPDATE "user" SET "address_state"='NY',"address_street"='111 Street',"firstname"='Greg',"lastname"='Farley' []
Example (WithIgnoredEmbedded) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
	_ "github.com/orn-id/depiq/dialect/mysql"
)

func main() {
	type Address struct {
		Street string
		State  string
	}
	type User struct {
		Address   `db:"-"`
		FirstName string
		LastName  string
	}
	ds := depiq.Update("user").Set(
		User{Address: Address{Street: "111 Street", State: "NY"}, FirstName: "Greg", LastName: "Farley"},
	)
	updateSQL, args, _ := ds.ToSQL()
	fmt.Println(updateSQL, args)

}

Output:

UPDATE "user" SET "firstname"='Greg',"lastname"='Farley' []
Example (WithNilEmbeddedPointer) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
	_ "github.com/orn-id/depiq/dialect/mysql"
)

func main() {
	type Address struct {
		Street string
		State  string
	}
	type User struct {
		*Address
		FirstName string
		LastName  string
	}
	ds := depiq.Update("user").Set(
		User{FirstName: "Greg", LastName: "Farley"},
	)
	updateSQL, args, _ := ds.ToSQL()
	fmt.Println(updateSQL, args)

}

Output:

UPDATE "user" SET "firstname"='Greg',"lastname"='Farley' []
Example (WithNoTags) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
	_ "github.com/orn-id/depiq/dialect/mysql"
)

func main() {
	type item struct {
		Address string
		Name    string
	}
	sql, args, _ := depiq.Update("items").Set(
		item{Name: "Test", Address: "111 Test Addr"},
	).ToSQL()
	fmt.Println(sql, args)

}

Output:

UPDATE "items" SET "address"='111 Test Addr',"name"='Test' []
Example (WithSkipUpdateTag) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
	_ "github.com/orn-id/depiq/dialect/mysql"
)

func main() {
	type item struct {
		Address string `db:"address"`
		Name    string `db:"name" depiq:"skipupdate"`
	}
	sql, args, _ := depiq.Update("items").Set(
		item{Name: "Test", Address: "111 Test Addr"},
	).ToSQL()
	fmt.Println(sql, args)

}

Output:

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

func (*UpdateDataset) SetDialect 

func (ud *UpdateDataset) SetDialect(dialect SQLDialect) *UpdateDataset

Returns the current adapter on the dataset

func (*UpdateDataset) SetError 

func (ud *UpdateDataset) SetError(err error) *UpdateDataset

Set an error on the dataset if one has not already been set. This error will be returned by a future call to Error or as part of ToSQL. This can be used by end users to record errors while building up queries without having to track those separately.

func (*UpdateDataset) Table 

func (ud *UpdateDataset) Table(table interface{}) *UpdateDataset

Sets the table to update.

Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
	_ "github.com/orn-id/depiq/dialect/mysql"
)

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

Output:

UPDATE "test2" SET "foo"='bar'
Example (Aliased) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
	_ "github.com/orn-id/depiq/dialect/mysql"
)

func main() {
	ds := depiq.Update("test")
	sql, _, _ := ds.Table(depiq.T("test").As("t")).Set(depiq.Record{"foo": "bar"}).ToSQL()
	fmt.Println(sql)
}

Output:

UPDATE "test" AS "t" SET "foo"='bar'

func (*UpdateDataset) ToSQL 

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

Generates an UPDATE 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 (Prepared) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
	_ "github.com/orn-id/depiq/dialect/mysql"
)

func main() {
	type item struct {
		Address string `db:"address"`
		Name    string `db:"name"`
	}

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

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

	sql, args, _ = depiq.From("items").Prepared(true).Update().Set(
		map[string]interface{}{"name": "Test", "address": "111 Test Addr"},
	).ToSQL()
	fmt.Println(sql, args)
}

Output:

UPDATE "items" SET "address"=?,"name"=? [111 Test Addr Test]
UPDATE "items" SET "address"=?,"name"=? [111 Test Addr Test]
UPDATE "items" SET "address"=?,"name"=? [111 Test Addr Test]

func (*UpdateDataset) Where 

func (ud *UpdateDataset) Where(expressions ...exp.Expression) *UpdateDataset

Adds a WHERE clause. See examples.

Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
	_ "github.com/orn-id/depiq/dialect/mysql"
)

func main() {
	// By default everything is anded together
	sql, _, _ := depiq.Update("test").
		Set(depiq.Record{"foo": "bar"}).
		Where(depiq.Ex{
			"a": depiq.Op{"gt": 10},
			"b": depiq.Op{"lt": 10},
			"c": nil,
			"d": []string{"a", "b", "c"},
		}).ToSQL()
	fmt.Println(sql)
	// You can use ExOr to get ORed expressions together
	sql, _, _ = depiq.Update("test").
		Set(depiq.Record{"foo": "bar"}).
		Where(depiq.ExOr{
			"a": depiq.Op{"gt": 10},
			"b": depiq.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, _, _ = depiq.Update("test").
		Set(depiq.Record{"foo": "bar"}).
		Where(
			depiq.Or(
				depiq.Ex{
					"a": depiq.Op{"gt": 10},
					"b": depiq.Op{"lt": 10},
				},
				depiq.Ex{
					"c": nil,
					"d": []string{"a", "b", "c"},
				},
			),
		).ToSQL()
	fmt.Println(sql)
	// By default everything is anded together
	sql, _, _ = depiq.Update("test").
		Set(depiq.Record{"foo": "bar"}).
		Where(
			depiq.C("a").Gt(10),
			depiq.C("b").Lt(10),
			depiq.C("c").IsNull(),
			depiq.C("d").In("a", "b", "c"),
		).ToSQL()
	fmt.Println(sql)
	// You can use a combination of Ors and Ands
	sql, _, _ = depiq.Update("test").
		Set(depiq.Record{"foo": "bar"}).
		Where(
			depiq.Or(
				depiq.C("a").Gt(10),
				depiq.And(
					depiq.C("b").Lt(10),
					depiq.C("c").IsNull(),
				),
			),
		).ToSQL()
	fmt.Println(sql)
}

Output:

UPDATE "test" SET "foo"='bar' WHERE (("a" > 10) AND ("b" < 10) AND ("c" IS NULL) AND ("d" IN ('a', 'b', 'c')))
UPDATE "test" SET "foo"='bar' WHERE (("a" > 10) OR ("b" < 10) OR ("c" IS NULL) OR ("d" IN ('a', 'b', 'c')))
UPDATE "test" SET "foo"='bar' WHERE ((("a" > 10) AND ("b" < 10)) OR (("c" IS NULL) AND ("d" IN ('a', 'b', 'c'))))
UPDATE "test" SET "foo"='bar' WHERE (("a" > 10) AND ("b" < 10) AND ("c" IS NULL) AND ("d" IN ('a', 'b', 'c')))
UPDATE "test" SET "foo"='bar' WHERE (("a" > 10) OR (("b" < 10) AND ("c" IS NULL)))
Example (Prepared) 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
	_ "github.com/orn-id/depiq/dialect/mysql"
)

func main() {
	// By default everything is anded together
	sql, args, _ := depiq.Update("test").
		Prepared(true).
		Set(depiq.Record{"foo": "bar"}).
		Where(depiq.Ex{
			"a": depiq.Op{"gt": 10},
			"b": depiq.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, _ = depiq.Update("test").Prepared(true).
		Set(depiq.Record{"foo": "bar"}).
		Where(depiq.ExOr{
			"a": depiq.Op{"gt": 10},
			"b": depiq.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, _ = depiq.Update("test").Prepared(true).
		Set(depiq.Record{"foo": "bar"}).
		Where(
			depiq.Or(
				depiq.Ex{
					"a": depiq.Op{"gt": 10},
					"b": depiq.Op{"lt": 10},
				},
				depiq.Ex{
					"c": nil,
					"d": []string{"a", "b", "c"},
				},
			),
		).ToSQL()
	fmt.Println(sql, args)
	// By default everything is anded together
	sql, args, _ = depiq.Update("test").Prepared(true).
		Set(depiq.Record{"foo": "bar"}).
		Where(
			depiq.C("a").Gt(10),
			depiq.C("b").Lt(10),
			depiq.C("c").IsNull(),
			depiq.C("d").In("a", "b", "c"),
		).ToSQL()
	fmt.Println(sql, args)
	// You can use a combination of Ors and Ands
	sql, args, _ = depiq.Update("test").Prepared(true).
		Set(depiq.Record{"foo": "bar"}).
		Where(
			depiq.Or(
				depiq.C("a").Gt(10),
				depiq.And(
					depiq.C("b").Lt(10),
					depiq.C("c").IsNull(),
				),
			),
		).ToSQL()
	fmt.Println(sql, args)
}

Output:

UPDATE "test" SET "foo"=? WHERE (("a" > ?) AND ("b" < ?) AND ("c" IS NULL) AND ("d" IN (?, ?, ?))) [bar 10 10 a b c]
UPDATE "test" SET "foo"=? WHERE (("a" > ?) OR ("b" < ?) OR ("c" IS NULL) OR ("d" IN (?, ?, ?))) [bar 10 10 a b c]
UPDATE "test" SET "foo"=? WHERE ((("a" > ?) AND ("b" < ?)) OR (("c" IS NULL) AND ("d" IN (?, ?, ?)))) [bar 10 10 a b c]
UPDATE "test" SET "foo"=? WHERE (("a" > ?) AND ("b" < ?) AND ("c" IS NULL) AND ("d" IN (?, ?, ?))) [bar 10 10 a b c]
UPDATE "test" SET "foo"=? WHERE (("a" > ?) OR (("b" < ?) AND ("c" IS NULL))) [bar 10 10]

func (*UpdateDataset) With 

func (ud *UpdateDataset) With(name string, subquery exp.Expression) *UpdateDataset

Creates a WITH clause for a common table expression (CTE).

The name will be available to use in the UPDATE from in the associated query; and can optionally contain a list of column names "name(col1, col2, col3)".

The name will refer to the results of the specified subquery.

Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
	_ "github.com/orn-id/depiq/dialect/mysql"
)

func main() {
	sql, _, _ := depiq.Update("test").
		With("some_vals(val)", depiq.From().Select(depiq.L("123"))).
		Where(depiq.C("val").Eq(depiq.From("some_vals").Select("val"))).
		Set(depiq.Record{"name": "Test"}).ToSQL()
	fmt.Println(sql)

}

Output:

WITH some_vals(val) AS (SELECT 123) UPDATE "test" SET "name"='Test' WHERE ("val" IN (SELECT "val" FROM "some_vals"))

func (*UpdateDataset) WithDialect 

func (ud *UpdateDataset) WithDialect(dl string) *UpdateDataset

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

func (*UpdateDataset) WithRecursive 

func (ud *UpdateDataset) WithRecursive(name string, subquery exp.Expression) *UpdateDataset

Creates a WITH RECURSIVE clause for a common table expression (CTE)

The name will be available to use in the UPDATE from in the associated query; and must contain a list of column names "name(col1, col2, col3)" for a recursive clause.

The name will refer to the results of the specified subquery. The subquery for a recursive query will always end with a UNION or UNION ALL with a clause that refers to the CTE by name.

Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
	_ "github.com/orn-id/depiq/dialect/mysql"
)

func main() {
	sql, _, _ := depiq.Update("nums").
		WithRecursive("nums(x)", depiq.From().Select(depiq.L("1").As("num")).
			UnionAll(depiq.From("nums").
				Select(depiq.L("x+1").As("num")).Where(depiq.C("x").Lt(5)))).
		Set(depiq.Record{"foo": depiq.T("nums").Col("num")}).
		ToSQL()
	fmt.Println(sql)
}

Output:

WITH RECURSIVE nums(x) AS (SELECT 1 AS "num" UNION ALL (SELECT x+1 AS "num" FROM "nums" WHERE ("x" < 5))) UPDATE "nums" SET "foo"="nums"."num"

type Vals 

type Vals = exp.Vals
Example 
package main

import (
	"fmt"

	"github.com/orn-id/depiq"
)

func main() {
	ds := depiq.Insert("user").
		Cols("first_name", "last_name", "is_verified").
		Vals(
			depiq.Vals{"Greg", "Farley", true},
			depiq.Vals{"Jimmy", "Stewart", true},
			depiq.Vals{"Jeff", "Jeffers", false},
		)
	insertSQL, args, _ := ds.ToSQL()
	fmt.Println(insertSQL, args)

}

Output:

INSERT INTO "user" ("first_name", "last_name", "is_verified") VALUES ('Greg', 'Farley', TRUE), ('Jimmy', 'Stewart', TRUE), ('Jeff', 'Jeffers', FALSE) []

Source Files

View all Source files

Directories

Path Synopsis
dialect
mysql
postgres
sqlite3
sqlserver
internal
errors
sb
tag
util
mocks

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.