Squirrel - fluent SQL generator for Go

import"gopkg.in/Masterminds/squirrel.v1"

or if you prefer using master (which may be arbitrarily ahead of or behind v1):

NOTE: as of Go 1.6, go get correctly clones the Github default branch (which is v1 in this repo).

import"github.com/Masterminds/squirrel"

_Note: This project has moved from github.com/lann/squirrel to github.com/Masterminds/squirrel. Lann remains the architect of the project, but we're helping him curate.

Squirrel is not an ORM. For an application of Squirrel, check out structable, a table-struct mapper

Squirrel helps you build SQL queries from composable parts:

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"

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:

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:

iflen(q) >0 {
users=users.Where("name LIKE ?", fmt.Sprint("%", q, "%"))
}

Squirrel wants to make your life easier:

// StmtCache caches Prepared Stmts for youdbCache:=sq.NewStmtCacher(db)
// StatementBuilder keeps your syntax neatmydb:=sq.StatementBuilder.RunWith(dbCache)
select_users:=mydb.Select("*").From("users")

Squirrel loves PostgreSQL:

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 mask by inserting two question marks:

SELECT*FROM nodes WHERE meta->'format' ??| array[?,?]

will generate with the Dollar Placeholder:

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)

  Squirrel does not explicitly support tuples, but you can get the same effect with e.g.:

  sq.Or{
  sq.Eq{"col1": 1, "col2": 2},
  sq.Eq{"col1": 3, "col2": 4}}

  WHERE (col1 =1AND col2 =2) OR (col1 =3AND 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)

  Values of type []byte are handled specially by database/sql. In Go, byte is just an alias of uint8, 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.