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!

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.

Would it be possible for you to make use of gopkg.in to provide stable API branches?

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.

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

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

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?

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.

• Parameter Interpolation - Would require adapter specific settings
  • Reduces Roundtrips to server, currently depending on the driver it makes one request to prepare the statement then executes
  • Would be nice to also explicitly create a prepared statement
• Identifier support - Would Require adapter specific settings
  • Prevents unexpected side effects like case sensitive tables or schemas from being ignored, also if the schema or table contains non standard characters like - not quoting would not work for some dbs
• More robust expression support to support parameter interpolation.
  • Right now If I want to do something like COALESCE and didnt want the statement to be prepared, I would have to encode the value manually
  • Would require more expressions with things like SqlFunctions, Identifer expressions, literals etc..
  • Or interpolation of placeholder values if available
• Scanning into structs/slices
  • Right now it appears that I would have to scan results manually
  • This might require db specific adapters depending on values returned (i.e. postgres doesnt return last insert id), if I wanted to get that I would have create a suffix with a returning clause, and execute a query, while on mysql I could run exec.
• Support for multi insert with structs or maps
  • The db 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 updates
• Support for subqueries, and possible auto aliasing for the subselect depending on the number of sources that are in the FROM clause
• Other Possiblities
  • A Count method
  • A Pluck method for plucking a single field and scanning into a slice
  • Support for different date types
    • YEAR, TIME, TIMESTAMP, DATETIME etc
    • Would require adapter specific settings or user specified date formats
  • Cast support for identifier types
  • So I could do something like `I("created").Cast("DATE").Eq(Date(time.Now()))
• Trace logging of sql statements
• More robust/explicit transaction support

I 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

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

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?

I make pretty heavy use of Postgres's RETURNING clause on INSERTs and UPDATEs, 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.

For example, placefolder can be ? or $1 ? postgresql driver doesn't support ?.

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?

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.

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?

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.

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?

Reference

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;

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?

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:

• Ignore a condition if a zero-length slice is passed
• Treat a zero-length array as NULL
• Return an error when attempting to generate a query where one of the arguments is a zero-length slice

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.

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.

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)

SELECT IF( field IN (...), 0, 1) AS exists FROM table

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

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!

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

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)

Is there any roadmap for golang 1.8 sql named parameters?

q := squirrel.Select("*").From("users")

should generate SELECT * FROM users instead of SELECT * FROM users WHERE

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?

Is it possible to do SELECT SUM?

I think squirrel would be perfect if JOIN clauses could be added. Any plans to add support ?

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 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!

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?

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.

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")

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

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?

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?

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 ...")

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.

Do you have a Patreon? This library has saved me so much time debugging SQL queries, I'd love to buy you a beer.

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.

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.

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)

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.