doug-martin/goqu: SQL builder and query library for golang

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

goqu 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/doug-martin/goqu/v9

If you are not using go modules...

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

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

Migrating Between Versions

Features

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

• Query Builder

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

• gorm
• hood

Why?

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

• Make the generation of SQL easy and enjoyable
• Create an expressive DSL that would find common errors with SQL at compile time.
• Provide a DSL that accounts for the common SQL expressions, NOT every nuance for each database.
• Provide developers the ability to:

Docs

• Dialect - Introduction to different dialects (mysql, postgres, sqlite3, sqlserver etc)
• Expressions - Introduction to goqu 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 goqu.
• Database - Docs and examples of using a Database to execute queries in goqu
• 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, _, _ := goqu.From("test").ToSQL()
fmt.Println(sql)

Output:

SELECT * FROM "test"

sql, _, _ := goqu.From("test").Where(goqu.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 := goqu.Insert("user").
	Cols("first_name", "last_name").
	Vals(
		goqu.Vals{"Greg", "Farley"},
		goqu.Vals{"Jimmy", "Stewart"},
		goqu.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 := goqu.Insert("user").Rows(
	goqu.Record{"first_name": "Greg", "last_name": "Farley"},
	goqu.Record{"first_name": "Jimmy", "last_name": "Stewart"},
	goqu.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 := goqu.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 := goqu.Insert("user").Prepared(true).
	FromQuery(goqu.From("other_table"))
insertSQL, args, _ := ds.ToSQL()
fmt.Println(insertSQL, args)

Output:

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

ds := goqu.Insert("user").Prepared(true).
	Cols("first_name", "last_name").
	FromQuery(goqu.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, _ := goqu.Update("items").Set(
	goqu.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" goqu:"skipupdate"
}
sql, args, _ := goqu.Update("items").Set(
	item{Name: "Test", Address: "111 Test Addr"},
).ToSQL()
fmt.Println(sql, args)

Output:

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

sql, _, _ := goqu.Update("test").
	Set(goqu.Record{"foo": "bar"}).
	Where(goqu.Ex{
		"a": goqu.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 := goqu.Delete("items")
sql, args, _ := ds.ToSQL()
fmt.Println(sql, args)

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

Output:

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

Contributions

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

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

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

New Features

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

1. The use case
2. A short example

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

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

Running tests

go test -v -race ./...

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

MYSQL_VERSION=8 POSTGRES_VERSION=13.4 SQLSERVER_VERSION=2017-CU8-ubuntu GO_VERSION=latest docker-compose run goqu

License

goqu is released under the MIT License.