1```
2 __ _ ___ __ _ _ _
3 / _` |/ _ \ / _` | | | |
4| (_| | (_) | (_| | |_| |
5 \__, |\___/ \__, |\__,_|
6 |___/ |_|
7```
8[](https://github.com/doug-martin/goqu/releases)
9[](https://github.com/doug-martin/goqu/actions?query=workflow%3ATest+and+branch%3Amaster+)
10[](https://pkg.go.dev/github.com/doug-martin/goqu/v9)
11[](https://codecov.io/gh/doug-martin/goqu)
12[](https://goreportcard.com/report/github.com/doug-martin/goqu/v9)
13
14`goqu` is an expressive SQL builder and executor
15
16If you are upgrading from an older version please read the [Migrating Between Versions](./docs/version_migration.md) docs.
17
18
19## Installation
20
21If using go modules.
22
23```sh
24go get -u github.com/doug-martin/goqu/v9
25```
26
27If you are not using go modules...
28
29**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"`
30
31```sh
32go get -u github.com/doug-martin/goqu
33```
34
35### [Migrating Between Versions](./docs/version_migration.md)
36
37## Features
38
39`goqu` comes with many features but here are a few of the more notable ones
40
41* Query Builder
42* Parameter interpolation (e.g `SELECT * FROM "items" WHERE "id" = ?` -> `SELECT * FROM "items" WHERE "id" = 1`)
43* Built from the ground up with multiple dialects in mind
44* Insert, Multi Insert, Update, and Delete support
45* Scanning of rows to struct[s] or primitive value[s]
46
47While goqu may support the scanning of rows into structs it is not intended to be used as an ORM if you are looking for common ORM features like associations,
48or hooks I would recommend looking at some of the great ORM libraries such as:
49
50* [gorm](https://github.com/jinzhu/gorm)
51* [hood](https://github.com/eaigner/hood)
52
53## Why?
54
55We 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:
56
57* Make the generation of SQL easy and enjoyable
58* Create an expressive DSL that would find common errors with SQL at compile time.
59* Provide a DSL that accounts for the common SQL expressions, NOT every nuance for each database.
60* Provide developers the ability to:
61 * Use SQL when desired
62 * Easily scan results into primitive values and structs
63 * Use the native sql.Db methods when desired
64
65## Docs
66
67* [Dialect](./docs/dialect.md) - Introduction to different dialects (`mysql`, `postgres`, `sqlite3`, `sqlserver` etc)
68* [Expressions](./docs/expressions.md) - Introduction to `goqu` expressions and common examples.
69* [Select Dataset](./docs/selecting.md) - Docs and examples about creating and executing SELECT sql statements.
70* [Insert Dataset](./docs/inserting.md) - Docs and examples about creating and executing INSERT sql statements.
71* [Update Dataset](./docs/updating.md) - Docs and examples about creating and executing UPDATE sql statements.
72* [Delete Dataset](./docs/deleting.md) - Docs and examples about creating and executing DELETE sql statements.
73* [Prepared Statements](./docs/interpolation.md) - Docs about interpolation and prepared statements in `goqu`.
74* [Database](./docs/database.md) - Docs and examples of using a Database to execute queries in `goqu`
75* [Working with time.Time](./docs/time.md) - Docs on how to use alternate time locations.
76
77## Quick Examples
78
79### Select
80
81See the [select dataset](./docs/selecting.md) docs for more in depth examples
82
83```go
84sql, _, _ := goqu.From("test").ToSQL()
85fmt.Println(sql)
86```
87
88Output:
89
90```
91SELECT * FROM "test"
92```
93
94```go
95sql, _, _ := goqu.From("test").Where(goqu.Ex{
96 "d": []string{"a", "b", "c"},
97}).ToSQL()
98fmt.Println(sql)
99```
100
101Output:
102
103```
104SELECT * FROM "test" WHERE ("d" IN ('a', 'b', 'c'))
105```
106
107### Insert
108
109See the [insert dataset](./docs/inserting.md) docs for more in depth examples
110
111```go
112ds := goqu.Insert("user").
113 Cols("first_name", "last_name").
114 Vals(
115 goqu.Vals{"Greg", "Farley"},
116 goqu.Vals{"Jimmy", "Stewart"},
117 goqu.Vals{"Jeff", "Jeffers"},
118 )
119insertSQL, args, _ := ds.ToSQL()
120fmt.Println(insertSQL, args)
121```
122
123Output:
124```sql
125INSERT INTO "user" ("first_name", "last_name") VALUES ('Greg', 'Farley'), ('Jimmy', 'Stewart'), ('Jeff', 'Jeffers') []
126```
127
128```go
129ds := goqu.Insert("user").Rows(
130 goqu.Record{"first_name": "Greg", "last_name": "Farley"},
131 goqu.Record{"first_name": "Jimmy", "last_name": "Stewart"},
132 goqu.Record{"first_name": "Jeff", "last_name": "Jeffers"},
133)
134insertSQL, args, _ := ds.ToSQL()
135fmt.Println(insertSQL, args)
136```
137
138Output:
139```
140INSERT INTO "user" ("first_name", "last_name") VALUES ('Greg', 'Farley'), ('Jimmy', 'Stewart'), ('Jeff', 'Jeffers') []
141```
142
143
144```go
145type User struct {
146 FirstName string `db:"first_name"`
147 LastName string `db:"last_name"`
148}
149ds := goqu.Insert("user").Rows(
150 User{FirstName: "Greg", LastName: "Farley"},
151 User{FirstName: "Jimmy", LastName: "Stewart"},
152 User{FirstName: "Jeff", LastName: "Jeffers"},
153)
154insertSQL, args, _ := ds.ToSQL()
155fmt.Println(insertSQL, args)
156```
157
158Output:
159```
160INSERT INTO "user" ("first_name", "last_name") VALUES ('Greg', 'Farley'), ('Jimmy', 'Stewart'), ('Jeff', 'Jeffers') []
161```
162
163```go
164ds := goqu.Insert("user").Prepared(true).
165 FromQuery(goqu.From("other_table"))
166insertSQL, args, _ := ds.ToSQL()
167fmt.Println(insertSQL, args)
168```
169
170Output:
171```
172INSERT INTO "user" SELECT * FROM "other_table" []
173```
174
175```go
176ds := goqu.Insert("user").Prepared(true).
177 Cols("first_name", "last_name").
178 FromQuery(goqu.From("other_table").Select("fn", "ln"))
179insertSQL, args, _ := ds.ToSQL()
180fmt.Println(insertSQL, args)
181```
182
183Output:
184```
185INSERT INTO "user" ("first_name", "last_name") SELECT "fn", "ln" FROM "other_table" []
186```
187
188### Update
189
190See the [update dataset](./docs/updating.md) docs for more in depth examples
191
192```go
193sql, args, _ := goqu.Update("items").Set(
194 goqu.Record{"name": "Test", "address": "111 Test Addr"},
195).ToSQL()
196fmt.Println(sql, args)
197```
198
199Output:
200```
201UPDATE "items" SET "address"='111 Test Addr',"name"='Test' []
202```
203
204```go
205type item struct {
206 Address string `db:"address"`
207 Name string `db:"name" goqu:"skipupdate"`
208}
209sql, args, _ := goqu.Update("items").Set(
210 item{Name: "Test", Address: "111 Test Addr"},
211).ToSQL()
212fmt.Println(sql, args)
213```
214
215Output:
216```
217UPDATE "items" SET "address"='111 Test Addr' []
218```
219
220```go
221sql, _, _ := goqu.Update("test").
222 Set(goqu.Record{"foo": "bar"}).
223 Where(goqu.Ex{
224 "a": goqu.Op{"gt": 10}
225 }).ToSQL()
226fmt.Println(sql)
227```
228
229Output:
230```
231UPDATE "test" SET "foo"='bar' WHERE ("a" > 10)
232```
233
234### Delete
235
236See the [delete dataset](./docs/deleting.md) docs for more in depth examples
237
238```go
239ds := goqu.Delete("items")
240
241sql, args, _ := ds.ToSQL()
242fmt.Println(sql, args)
243```
244
245```go
246sql, _, _ := goqu.Delete("test").Where(goqu.Ex{
247 "c": nil
248 }).ToSQL()
249fmt.Println(sql)
250```
251
252Output:
253```
254DELETE FROM "test" WHERE ("c" IS NULL)
255```
256
257<a name="contributions"></a>
258## Contributions
259
260I 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.
261
262* An issue with Documentation
263* You found the documentation lacking in some way
264
265If you have an issue with the package please include the following
266
267* The dialect you are using
268* A description of the problem
269* A short example of how to reproduce (if applicable)
270
271Without those basics it can be difficult to reproduce your issue locally. You may be asked for more information but that is a good starting point.
272
273### New Features
274
275New 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.
276
2771. The use case
2782. A short example
279
280If you are issuing a PR also include the following
281
2821. Tests - otherwise the PR will not be merged
2832. Documentation - otherwise the PR will not be merged
2843. Examples - [If applicable] see example_test.go for examples
285
286If you find an issue you want to work on please comment on it letting other people know you are looking at it and I will assign the issue to you.
287
288If 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.
289
290### Running tests
291The test suite requires a postgres, mysql and sqlserver databases. You can override the connection strings with the [`MYSQL_URI`, `PG_URI`, `SQLSERVER_URI` environment variables](https://github.com/doug-martin/goqu/blob/2fe3349/docker-compose.yml#L26)*
292
293```sh
294go test -v -race ./...
295```
296
297You can also run the tests in a container using [docker-compose](https://docs.docker.com/compose/).
298
299```sh
300MYSQL_VERSION=8 POSTGRES_VERSION=13.4 SQLSERVER_VERSION=2017-CU8-ubuntu GO_VERSION=latest docker-compose run goqu
301```
302
303## License
304
305`goqu` is released under the [MIT License](http://www.opensource.org/licenses/MIT).
306
307
308
309
310
View as plain text