This is a Fork with some improvements from the original Squirrel package

The main difference with the original squirrel, is this package has the ability to tag structs with database fields, so now you can get the names from the database field a struct represents and build a Where clause for a Select from a DTO struct automatically, without the risk of a SQL injection.

Please check the documentation bellow

Squirrel - fluent SQL generator for Go

import "github.com/Masterminds/squirrel"

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.NewStmtCache(db)

// StatementBuilder keeps your syntax neat
mydb := sq.StatementBuilder.RunWith(dbCache)
select_users := mydb.Select("*").From("users")

Tag your structs to name your database fields:

// Tag a Struct with the db tag
type Example struct {
    Name string `db:"example.name"`
    ID int64 `db:"example.id"`
}

And build the WHERE clause with it:

filters := Example{Name:"someone", ID: 1}

// Create a Select builder
queryBuilder := sq.Select("name, id").From("example")

// Add Where clauses 
queryBuilder, err := queryBuilder.Filters(filters)

query := queryBuilder.ToSql()
// query == `SELECT name, id FROM example WHERE example.name = "someone" AND example.id = 1`

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 marks 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.