masterminds / squirrel Goto Github PK
View Code? Open in Web Editor NEWFluent SQL generation for golang
License: Other
Fluent SQL generation for golang
License: Other
So, I ended up building github.com/mipearson/sqlc, which has a subset of this functionality, and a near identical API, because I didn't know this existed.
Can I please add this project to the golang wiki on your behalf?
Due to this line, it seems one can't generate the following SQL.
SELECT id, id2, status FROM posts WHERE (id > 1 AND status = 0) OR (id < 100 AND status = 1)
Am I missing something?
I'd like to commit a bunch of example tests, because I feel this could make the documentation better?
Any thoughts?
Lots of my code looks like this:
if old.ReportURL != bg.ReportURL {
q.Set("report_url", NullString(bg.ReportURL, true))
changed = true
}
if old.Status != bg.Status {
q.Set("status", NullString(bg.Status, true))
changed = true
}...
I would like to be able to determine if there are any set clauses. I can do a pull-request if you'd like. Are you ok exposing the SetClauses via the UpdateBuilder API?
I want to create query like SELECT * FROM (SELECT Max(someval), modified FROM sometable group by someval ) WHERE modified>?
, but I can't find any way because squirrel is not supporting subquery in FROM
operator.
I have a idea to support subquery, see following code.
q, _, _ := sq.Select("*").FromSubQuery(
sq.Select("Max(someval)", "modified").
From("sometable").
GroupBy("someval")).
Where("modifyed > ?", t)
define:
func (b SelectBuilder) FromSubQuery(SelectBuilder) SelectBuilder
What do you think so? If you agree on this idea, I'll create patch and send PR.
Thanks!
I am interested to know if you think it would possible to support queries which are Microsoft SQL Server friendly?
I am currently using Microsoft SQL Server on projects with the github.com/denisenkom/go-mssqldb driver and would like to have a tool like squirrel to use for SQL code generation.
I can help with implementation and testing.
Kind Regards
I make pretty heavy use of Postgres's RETURNING
clause on INSERT
s and UPDATE
s, and I'm not sure how best to express that when I am using Squirrel.
Typically my statement looks something like:
INSERT INTO whatever (col1, col2) VALUES ('foo', 'bar')
RETURNING id, created_at;
paired with a db.Query
to read the values back out.
I guess you probably don't want to have Postgres-specific code in the Squirrel API, but I am also trying to avoid needing to deal with a text API by using Squirrel. I'm curious if you have suggestions on how best to deal with database-specific implementation details.
When I run go get -u github.com/Masterminds/squirrel
I got these reply
../lann/builder/builder.go:149: cannot use func literal (type func(string, ps.Any)) as type func(string, interface {}) in argument to m.ForEach
../lann/builder/builder.go:203: cannot use func literal (type func(string, ps.Any)) as type func(string, interface {}) in argument to getBuilderMap(builder).ForEach
So, when you're building a query that uses the same prepared value in multiple locations, currently they are each sent as additional parameters.
It would be nice to be able to take advantage of this.
It isn't supported across multiple drivers like MySQL that use the ? style. Does this make it a no go? Let me know.
Thanks :)
In the Readme
psql := sq.StatementBuilder.PlaceholderFormat(Dollar)
should be
psql := sq.StatementBuilder.PlaceholderFormat(sq.Dollar)
There is currently no way to do queries using the CALL
statement. It is probably not the hugest use case in the set, but it must be trivial to implement… I will try it later (when I will be home), unless you do it before.
Hi, All!
As you know go get
doesn't work with tags or branches in a git repository. So, could you merge v1 into master branch?
Hello, I looked through this repo for some way to support Postgres's 'on conflict' mechanism. Is there a recommend approach for using that?
Thanks
Would it be possible for you to make use of gopkg.in to provide stable API branches?
Say that you have a TEXT field (t
) in your database and you want to filter with WHERE t = ?
[someValue]
.
In squirrel, this would be addressed as something like:
whereMap := map[string]interface{}{
"t": "value"
}
sq.Update(tableName).SetMap(setMap).Where(whereMap).ToSql()
This works great, with squirrel generating WHERE t = ?
[value]
as the sql and args.
However, let's change the code above to have a byte array:
whereMap := map[string]interface{}{
"t": []byte("value")
}
sq.Update(tableName).SetMap(setMap).Where(whereMap).ToSql()
This would result in the following query:
WHERE t IN (? ? ? ? ?)
[118 97 108 117 101]
I'm not sure if this is the correct behaviour or not in squirrel, but this is definitely not the right query to generate.
I'm proposing that squirrel.Eq
forces the generation of t = ?
, even if the value given is an array. Would that work?
When Inserting, I can do this:
return psql().
Insert(tableName).
Columns(columns...).
Values(values...).
Suffix(`RETURNING "id"`).
RunWith(db).
QueryRow().
Scan(&obj.ID)
But I cannot do a similar thing in update. This is not possible:
return psql().
Update(tableName).
Set("foo", "bar")
Suffix(`RETURNING "foo"`).
RunWith(db).
QueryRow().
Scan(&obj.Foo)
Which is a nice way to ensure that I keep my struct up to date with changes.
My workaround is this:
rows, err := sq.QueryWith(db, psql().
Update(tableName).
Set("foo", "bar").
Where(sq.Eq{"id": id}).
Suffix(`RETURNING "foo"`),
)
if err != nil {
return err
}
defer rows.Close()
rows.Next()
return rows.Scan(&obj.Foo)
Is QueryRow on Update (and also, Query) something we could add to squirrel?
Thanks!
Is it possible to do SELECT SUM?
For example, placefolder can be ?
or $1
? postgresql driver doesn't support ?
.
Hi,
We were looking at using Squirrel for our project but are unsure about the roadmap and features that will/will not be added and had a list items that would be required in order for us to use but are unsure if you feel they are out of the scope of your project or if you have purposefully not added some of them for philosophical reasons.
-
not quoting would not work for some dbsdb
tag seems to be fairly common would be nice If I would pass in a struct or a slice of structs to generate insert statements, and a single struct for updatesI completely understand if you feel these requests are outside of the scope of the project, as some of the requests could require a large refactor. As it appears right now squirrel seems less opinionated and gives more control to the developer about how they want a statement expressed and some of these requests could take away some of that.
-Doug
It is not possible currently to construct a query with arguments in OrderBy clause via squirrel, for ex.:
Select("*").From("geo").OrderBy("ST_Distance(ST_Point(lng, lat), ST_Point(?, ?))", lng, lat)
Hi,
I've been playing with your library (feels great!) to handle complex queries based on user input.
I've had the idea of implementing my own intermediary "builder" methods that would help finalize the SQL query and thought about using "test examples" to quickly test the feature.
// builders_test.go, run with `go test -v`
package main
import (
"fmt"
sq "gopkg.in/Masterminds/squirrel.v1"
)
func mustPrintSQL(builder sq.SelectBuilder) {
sql, params, err := builder.ToSql()
if err != nil {
panic(err)
}
fmt.Print(sql + " ")
fmt.Println(params)
}
func ExampleBuildSearchWithNoInput() {
builder := sq.Select("*").From("posts")
builder = buildSearch(builder, "", "")
mustPrintSQL(builder)
// Output: SELECT * FROM posts []
}
func ExampleBuildSearchWithWildcard() {
builder := sq.Select("*").From("posts")
builder = buildSearch(builder, "title", "*hello*")
mustPrintSQL(builder)
// Output: SELECT * FROM posts WHERE title ILIKE ? [%hello%]
}
func ExampleBuildSearchWithoutWildcard() {
builder := sq.Select("*").From("posts")
builder = buildSearch(builder, "title", "Hello!")
mustPrintSQL(builder)
// Output: SELECT * FROM posts WHERE title = ? [Hello!]
}
I understand the query parameters need to get passed separately to the db driver, but I was wondering if it was possible to inspect the final output, for debugging or testing, especially for very complex queries.
So instead of:
SELECT * FROM posts WHERE id = ? [1]
I would see
SELECT * FROM posts WHERE id = 1
Maybe a ToSQLUnsafe()
, DumpSQL()
or DebugSQL()
function?
Any thoughts?
Using the latest version of squirrel, when I do the following:
builder := psql.Select("j.title").
From("jobs j").
Join("companies c on j.company_id = c.id").
Where("c.id IN ($1)", "SELECT * FROM ...")
I get
pq: invalid input syntax for integer: "SELECT * FROM ..."
Therefore I have to use fmt.Sprintf
:
builder := psql.Select("j.title").
From("jobs j").
Join("companies c on j.company_id = c.id").
Where(fmt.Sprintf("c.id IN (%s)", "SELECT * FROM ...")
The API currently includes DISTINCT SQL command, however postgres supports a DISTINCT ON command which allows you to define a column to compute distinctiveness, but still select columns as you usually would.
Is there any plans for this, or, if I submit a PM, would this be an accepted feature?
Given
squirrel.Or{
squirrel.Eq{"id": IDs},
squirrel.Eq{"visible": true},
}
Non-empty slice works fine
IDs := []int64{1,2,3}
// generates WHERE id IN (1, 2, 3) OR visible=true
But empty slice shouldn't generate IN ( )
at all
IDs := []int64{}
// should generate WHERE visible=true
From what I can see, squirrel currently does not quote the SQL queries it generates, which could lead it to generate some invalid queries like:
INSERT INTO table (group) VALUES (?)
Note that since group
is not quoted, something liky MySQL will interpret it as the reserved keyword GROUP
, which causes a syntax error.
Are there any plans to address this in the SQL builder? Would a PR for this make sense? I have yet to look deeply into the codebase, but would this be difficult to change in squirrel?
Hi guys,
Great library. Was using it for the past day or so and was wondering if there is a support for the Postgresql's Upsert ON CONFLICT statements.
New API in Go 1.8 allows passing a context.Context
to Exec
, Query
and QueryRow
(via the ExecContext
, QueryContext
and QueryRowContext
methods, respectively.)
Drivers can take advantage of this in numerous ways - we would particularly enjoy distributed tracing in Lightstep.
Runner includes the method
QueryRow(query string, args ...interface{}) RowScanner
while sql.DB implements
QueryRow(query string, args ...interface{}) *sql.Row
Even though *sql.Row implements RowScanner, sql.DB still doesn't implement Runner, which is extremely annoying.
Workaround is to always wrap your sql.DB in a NewStmtCacher, which is annoying.
Would you be interested in letting me maintain for a while?
My proposal would be to move the project under the Masterminds org (pending @mattfarina's agreement), then add you and I as maintainers. If at any point you want to take it back over, you could just do it. And I'd be able to ask you questions for guidance on where this library should go.
I want to express query SELECT a FROM b WHERE col>? UNION SELECT a FROM c WHERE col<?
but squirrel is not supporting UNION
.
I think, The following example code is good design for support UNION
.
q, attrs, err := sq.Union(
sq.Select("*").From("a").Where("col > ?", hoge),
sq.Select("*").From("b").Where("col < ?", piyo)).ToSql()
q == "SELECT a FROM b WHERE col>? UNION SELECT a FROM c WHERE col<?"
attrs == []interface{hoge, piyo}
What do you think so? If you agree on this idea, I'll create patch and send PR.
Thanks!
Do you have a Patreon? This library has saved me so much time debugging SQL queries, I'd love to buy you a beer.
I was looking at the API and thinking that we should support fmt.Stringer
on anything that already has a ToSql
method. I can't remember if we discussed this back when @lann first wrote Squirrel. It's pretty trivial, so if there's no objection I might knock this out really quickly.
In some cases I'm getting a:
reflect: call of reflect.Value.Set on zero Value > stacktrace: /main.go:66 (0x402a53)
func.001: stack := string(debug.Stack())
/usr/lib/go/src/pkg/runtime/panic.c:248 (0x411d3d)
panic: runtime·newstackcall(d->fn, (byte*)d->args, d->siz);
/usr/lib/go/src/pkg/reflect/value.go:315 (0x4cd87a)
flag.mustBeExported: panic(&ValueError{methodName(), 0})
/usr/lib/go/src/pkg/reflect/value.go:1501 (0x4d3d00)
Value.Set: x.mustBeExported() // do not let unexported x leak
/home/gchain/.go/src/github.com/lann/builder/builder.go:212 (0x629601)
func.003: field.Set(reflect.ValueOf(val))
/home/gchain/.go/src/github.com/mndrix/ps/map.go:277 (0x631480)
(*tree).ForEach: f(m.key, m.value)
/home/gchain/.go/src/github.com/mndrix/ps/map.go:282 (0x6314f1)
(*tree).ForEach: t.ForEach(f)
/home/gchain/.go/src/github.com/lann/builder/builder.go:214 (0x628ba2)
scanStruct: })
/home/gchain/.go/src/github.com/lann/builder/builder.go:185 (0x628ad6)
GetStruct: return scanStruct(builder, structVal)
/home/gchain/.go/src/github.com/lann/squirrel/insert.go:117 (0x59c7a4)
InsertBuilder.Exec: data := builder.GetStruct(b).(insertData)
.....
Sometimes just building a simple Insert. Can't spot why. I'm I missing something?
With the postgresql support there is no way to generate such query:
SELECT * FROM json_test WHERE data ?& array['a', 'b'];
The ?
will be replaced by #1
however ?&
is a valid operator.
When passing a set of conditions to .Where(Eq{...
, if one of the conditions is a zero length slice, the resulting SQL will be invalid, and the query will be generated without an error. Here's a simple example:
package main
import (
"fmt"
"github.com/lann/squirrel"
)
func main() {
q := squirrel.Select("*").From("test").Where(squirrel.Eq{"condition": []string{}})
data, args, err := q.ToSql()
fmt.Printf("Query: %s, Data: %v, Error: %v\r\n", data, args, err)
}
Produces:
Query: SELECT * FROM test WHERE condition IN (), Data: [], Error: <nil>
Which is invalid SQL. If this is desired behavior, disregard this, but it seems as though squirrel
should do one of the following:
NULL
I'm happy to submit a PR with any of the above changes, but I didn't want to do anything until I heard other opinions.
LIMIT ALL
is valid SQL (and is equivalent to omitting the LIMIT
clause). Limit()
only accepts uint64
values, meaning you can't build a query with this syntax. I have a use case where I have a SelectBuilder
that has already had Limit()
called, and I would like to override the clause with ALL
(or remove it, since it would be equivalent), but neither seem possible at the moment with squirrel
.
If i use a sqlx db when calling stmt.RunWith(db) when the query is run it complains that RunWith has not been set.
example
import "fmt"
import "github.com/jmoiron/sqlx"
import sq "gopkg.in/Masterminds/squirrel.v1"
func TestSqlx (db_type, connect string) {
db, _ := sqlx.Open(db_type, connect)
rows, err := sq.Select("1").RunWith(db).Query()
if err != nil {
fmt.Println(err)
return
}
var One int
for rows.Next() {
rows.Scan(&One)
fmt.Println(One)
}
}
result
cannot run; no Runner set (RunWith)
Currently, due to the requirement to have at least one item specified in Values()
, the Insert Builder does not support this possibility (even through use of Suffix
()):
INSERT INTO table2
SELECT * FROM table1
WHERE condition;
I can't find a way to express queries of the form SELECT * FROM table WHERE a OR b
. According to the documentation (and the code I read so far), all the call of the Where
method are put together using AND
. It would be useful to be able to do OR
too.
Here is what I came up with while thinking about what the cleanest syntax would be:
squirrel.Select("*").From("table").Where(squirrel.Or{
conditionA,
conditionB,
})
Here, the Where
method is simply a variadic function taking a number of Or{}
and And{}
struct, which are merely slices of interfaces of the same kind than the current parameters of Where
. That way would make complex imbrication of conditions quite easy.
What do you think about it ? If you agree with that system, would you like some help to make it ?
Cheers.
SELECT IF( field IN (...), 0, 1) AS exists FROM table
There should be a constant or a function called Format Time which can convert from time.Time to a String that can be submitted to psql:
t.Format("2006-01-02 15:04:05-07")
q := squirrel.Select("*").From("users")
should generate SELECT * FROM users
instead of SELECT * FROM users WHERE
Currently if we use Eq{"field": v}
and v
implements driver.Valuer interface it gets expanded into v.Value()
even if it would be correct to use array/slice implementation (say pq.Int64Array
implements valuer, but it should expand into IN (?, ?, ?...)
)
Is this working as expected or it is a bug? There's a test case in my fork https://github.com/quetz/squirrel in v1
branch.
Currently, for handling PATCH requests from a REST API, we have functions that look like..
query := psql.Update("users").Suffix(returningQuery)
if user.Email != nil { query = query.Set("email", user.Email) }
if user.Name != nil { query = query.Set("name", user.Name) }
...
I feel like there should be an easier way to accomplish the goal of partial updates using SetMap, however I haven't found the method by which to skip setting a field if it is Nil.
Does it exist?
Is there any roadmap for golang 1.8 sql named parameters?
Hey there, I was looking for a way to write a WITH query for a Update, and then select the resulting rows along with some of the relational data, it will also be nice to able to chain these WITH statements together.
I'm not sure if this is supported in the current query builder, if it is, did I miss the documentation? The reason we like squirrel is it lets us conditionally build the insert / update statements, and composing the selects makes the code for filtering/sorting much cleaner.
Below is a simple example for what I'm looking for and hopefully I've overlooked it somewhere, thanks!
WITH inserted_job AS (
INSERT INTO jobs SET status = 1, progress = 54 WHERE ... RETURNING ...
)
SELECT status from inserted_job join ....
I think squirrel would be perfect if JOIN clauses could be added. Any plans to add support ?
Currently the UpdateBuilder doesn't have support for the Query/QueryRow API, so with PSQL you cannot run a update with a returning clause to get the current values to return a full object representation after a partial update. You have to use the ToSql()
method call. Any plans on adding this, or should we just continue to use the ToSql after building the query?
How can one specify a schema when creating a query for PostgreSQL. In NodeJS library called KnexJS, one can specify schema name like knex('tablename').schema('public').where(...)
I have a database that has a schema for each client and I need to switch the schema dynamically based on the schema linked to that user for each request. Thanks.
Trying to construct an update statement like the following in squirrel is proving impossible:
-- Update value in table a for all rows where the associated table b row has specified value in test_column
UPDATE table_a
SET table_a.value_column = $1
FROM table_b
WHERE table_b.a_id = table_a.id
and table_b.test_column = $2
I think UpdateBuilder should support a FROM clause to be able to leverage the abilities of postgres updates syntax: https://www.postgresql.org/docs/current/static/sql-update.html
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.