Skip to content
Fluent SQL generation for golang
Branch: v1
Clone or download
lann Merge pull request #179 from GeorgeMac/gm/stmt-cache-close
Add ability to Clear() statement cache
Latest commit d67d6a2 Feb 27, 2019
Permalink
Type Name Latest commit message Commit time
Failed to load latest commit information.
.gitignore
.travis.yml Add go 1.11.x version Dec 11, 2018
LICENSE.txt Convert empty array/slice in expr to "IN (NULL)" rather than erroring Feb 26, 2016
README.md Fix structable link Dec 29, 2018
case.go
case_test.go
delete.go
delete_ctx.go
delete_ctx_test.go
delete_test.go
expr.go
expr_test.go
go.mod
go.sum Add go-module support Dec 19, 2018
insert.go
insert_ctx.go
insert_ctx_test.go
insert_test.go
integration_test.go
part.go
placeholder.go
placeholder_test.go Adding Placeholder Colon format. Sep 14, 2018
row.go Prepare for release Jan 28, 2014
row_test.go Use assert in tests Feb 9, 2014
select.go
select_ctx.go Update Context support Aug 11, 2017
select_ctx_test.go Go fmt Aug 23, 2017
select_test.go Add OrderByClause to SelectBuilder Feb 26, 2019
squirrel.go Fix Context methods with stdsqlRunner (#165) Nov 26, 2018
squirrel_ctx.go improve setRunWith to be more accepting. Nov 3, 2018
squirrel_ctx_test.go
squirrel_test.go
statement.go
statement_test.go Select RunWith(*sql.Tx) supports QueryRow Aug 29, 2014
stmtcacher.go
stmtcacher_ctx.go
stmtcacher_ctx_test.go Rename StmtCacher to StmtCache and deprecate NewStmtCacher Feb 26, 2019
stmtcacher_noctx.go Rename StmtCacher to StmtCache and deprecate NewStmtCacher Feb 26, 2019
stmtcacher_test.go Rename StmtCacher to StmtCache and deprecate NewStmtCacher Feb 26, 2019
update.go Add query api to update. Mar 24, 2016
update_ctx.go
update_ctx_test.go
update_test.go Add Prefix and Suffix methods Jun 18, 2014
where.go fix: nested select placeholders numbering Jun 12, 2018
where_test.go Convert empty array/slice in expr to "IN (NULL)" rather than erroring Feb 26, 2016

README.md

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"

GoDoc Build Status

_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:

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

Squirrel wants to make your life easier:

// StmtCache caches Prepared Stmts for you
dbCache := sq.NewStmtCacher(db)

// StatementBuilder keeps your syntax neat
mydb := 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 = 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)

    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.

You can’t perform that action at this time.
You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session.