1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
|
[](https://masterminds.github.io/stability/maintenance.html)
### Squirrel is "complete".
Bug fixes will still be merged (slowly). Bug reports are welcome, but I will not necessarily respond to them. If another fork (or substantially similar project) actively improves on what Squirrel does, let me know and I may link to it here.
# Squirrel - fluent SQL generator for Go
```go
import "github.com/Masterminds/squirrel"
```
[](https://godoc.org/github.com/Masterminds/squirrel)
[](https://travis-ci.org/Masterminds/squirrel)
**Squirrel is not an ORM.** For an application of Squirrel, check out
[structable, a table-struct mapper](https://github.com/Masterminds/structable)
Squirrel helps you build SQL queries from composable parts:
```go
import sq "github.com/Masterminds/squirrel"
users := sq.Select("*").From("users").Join("emails USING (email_id)")
active := users.Where(sq.Eq{"deleted_at": nil})
sql, args, err := active.ToSql()
sql == "SELECT * FROM users JOIN emails USING (email_id) WHERE deleted_at IS NULL"
```
```go
sql, args, err := sq.
Insert("users").Columns("name", "age").
Values("moe", 13).Values("larry", sq.Expr("? + 5", 12)).
ToSql()
sql == "INSERT INTO users (name,age) VALUES (?,?),(?,? + 5)"
```
Squirrel can also execute queries directly:
```go
stooges := users.Where(sq.Eq{"username": []string{"moe", "larry", "curly", "shemp"}})
three_stooges := stooges.Limit(3)
rows, err := three_stooges.RunWith(db).Query()
// Behaves like:
rows, err := db.Query("SELECT * FROM users WHERE username IN (?,?,?,?) LIMIT 3",
"moe", "larry", "curly", "shemp")
```
Squirrel makes conditional query building a breeze:
```go
if len(q) > 0 {
users = users.Where("name LIKE ?", fmt.Sprint("%", q, "%"))
}
```
Squirrel wants to make your life easier:
```go
// StmtCache caches Prepared Stmts for you
dbCache := sq.NewStmtCache(db)
// StatementBuilder keeps your syntax neat
mydb := sq.StatementBuilder.RunWith(dbCache)
select_users := mydb.Select("*").From("users")
```
Squirrel loves PostgreSQL:
```go
psql := sq.StatementBuilder.PlaceholderFormat(sq.Dollar)
// You use question marks for placeholders...
sql, _, _ := psql.Select("*").From("elephants").Where("name IN (?,?)", "Dumbo", "Verna").ToSql()
/// ...squirrel replaces them using PlaceholderFormat.
sql == "SELECT * FROM elephants WHERE name IN ($1,$2)"
/// You can retrieve id ...
query := sq.Insert("nodes").
Columns("uuid", "type", "data").
Values(node.Uuid, node.Type, node.Data).
Suffix("RETURNING \"id\"").
RunWith(m.db).
PlaceholderFormat(sq.Dollar)
query.QueryRow().Scan(&node.id)
```
You can escape question marks by inserting two question marks:
```sql
SELECT * FROM nodes WHERE meta->'format' ??| array[?,?]
```
will generate with the Dollar Placeholder:
```sql
SELECT * FROM nodes WHERE meta->'format' ?| array[$1,$2]
```
## FAQ
* **How can I build an IN query on composite keys / tuples, e.g. `WHERE (col1, col2) IN ((1,2),(3,4))`? ([#104](https://github.com/Masterminds/squirrel/issues/104))**
Squirrel does not explicitly support tuples, but you can get the same effect with e.g.:
```go
sq.Or{
sq.Eq{"col1": 1, "col2": 2},
sq.Eq{"col1": 3, "col2": 4}}
```
```sql
WHERE (col1 = 1 AND col2 = 2) OR (col1 = 3 AND col2 = 4)
```
(which should produce the same query plan as the tuple version)
* **Why doesn't `Eq{"mynumber": []uint8{1,2,3}}` turn into an `IN` query? ([#114](https://github.com/Masterminds/squirrel/issues/114))**
Values of type `[]byte` are handled specially by `database/sql`. In Go, [`byte` is just an alias of `uint8`](https://golang.org/pkg/builtin/#byte), so there is no way to distinguish `[]uint8` from `[]byte`.
* **Some features are poorly documented!**
This isn't a frequent complaints section!
* **Some features are poorly documented?**
Yes. The tests should be considered a part of the documentation; take a look at those for ideas on how to express more complex queries.
## License
Squirrel is released under the
[MIT License](http://www.opensource.org/licenses/MIT).
|
