GitHub - Masterminds/squirrel: Fluent SQL generation for golang
source link: https://github.com/Masterminds/squirrel
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
README.md
Squirrel - fluent SQL generator for Go
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:
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 anIN
query? (#114)Values of type
[]byte
are handled specially bydatabase/sql
. In Go,byte
is just an alias ofuint8
, 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.
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK