Fluent SQL generation for golang

Stability: Maintenance

Squirrel is "complete".

Bug fixes will still be merged (slowly). Bug reports are welcome, but I will not necessarily respond to them. If another fork (or substantially similar project) actively improves on what Squirrel does, let me know and I may link to it here.

Squirrel - fluent SQL generator for Go

import "github.com/Masterminds/squirrel"

GoDoc Build Status

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

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.

Comments
  • Add UNION operator

    Add UNION operator

    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!

  • Make OR where queries

    Make OR where queries

    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.

  • Support of CASE operator in SELECT query

    Support of CASE operator in SELECT query

    Hi there! :) Here's a proposal for support of SELECT CASE statements (http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html#operator_case). The commit contains test which shows an idea which is better to discuss before writing any real code. The interface is a matter of discussion. Rationale of this proposal: In current version of squirrel we have to pregenerate a subquery with SELECT CASE, like this, for example:

        var values map[string]string = loadValues()
        args = make([]interface{}, len(values)*2)
    
        buf.WriteString("(SELECT CASE")
        for k, v := range values {
            buf.WriteString(" WHEN value = ? THEN ?")
            args[i*2], args[i*2+1] = value, i
        }
        buf.WriteString(" END AS col1)")
    
        qb := squirrel.Select(buf.String()).From("sometable")
    

    But in this case nothing stops developer from making mistakes like forgotten escaped value. Also we can use squirrel to pregenerate a separate subquery, but the code is still messy. Support of SELECT CASE can be used to protect the developer :)

        var values map[string]string = loadValues()
        qb := squirrel.Select("").
            From("sometable").
            Case(nil)
    
        for k, v := range values {
            qb = qb.WhenThen(squirrel.Eq{"value": value}, i)
        }
    
        qb = qb.EndCase("col1")
    

    What do you think about this?

    Regards, Ivan.

  • ToSql() alternative?

    ToSql() alternative?

    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?

  • add support for >,>=,<,<=

    add support for >,>=,<,<=

    This commit adds support for doing less than, less than or equal to, greater than, and greater than or equal to. I added tests as well.

    Please give feedback if not merged.

  • DISTINCT ON (postgres only)

    DISTINCT ON (postgres only)

    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?

  • Support subquery in FROM operator

    Support subquery in FROM operator

    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!

  • Please update license so GitHub will recognize its type in the API

    Please update license so GitHub will recognize its type in the API

    I am working on a license checker using the GitHub API and it comes back with NOASSERTION for this repo rather than MIT.

    Would it be possible for you to assert your license type in the Github Admin U so the API response would say MIT?

    https://docs.github.com/en/communities/setting-up-your-project-for-healthy-contributions/adding-a-license-to-a-repository

    Thank you in advance or considering.

  • Add `SuffixExpr` function to `SelectBuilder`

    Add `SuffixExpr` function to `SelectBuilder`

    I would like to do something like this:

    sq.
    	Select("col").
    	From("table").
    	LimitSelect(sq.
    		Select().
    		Column(sq.Case().
    			When("x = y", 10).
    			Else(0).From("table"),
    		),
    	)
    

    The SQL that would be generated by this would look something like:

    SELECT col FROM table 
    LIMIT (
      SELECT CASE WHEN x = y THEN 10 ELSE 0 END FROM table
    )
    

    This is valid for at least Postgres, as I'm able to run a query like this, however I am not sure about other flavors.

    I can sort of work around this with .Suffix but it makes me rather sad to have to do it that way 😢

  • Support *Context methods from database/sql in Go 1.8

    Support *Context methods from database/sql in Go 1.8

    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.

  • Support FROM clause in UpdateBuilder

    Support FROM clause in UpdateBuilder

    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

  • Misplaced params when using windows or subqueries

    Misplaced params when using windows or subqueries

    hi Team,

    We've been using Squirrel for a while now and (frankly) love it. The thing we're coming up against is there's not a lot of great support for subqueries. We're using sq.Alias to give a Window on a Column, but when params are passed in Squirrel seems to place them using the initial index despite passing Expr's around in what looks like a supported fashion.

    We've had to revert a code change that we found in testing here: https://github.com/autobrr/autobrr/pull/599

    This also seems to go wrong when using the WHERE IN clause, but admittedly that seems to be less supported (although greatly desired).

    Thoughts, directions, patches for us to test are greatly appreciated.

  • Sql doesn't match for SignleStore

    Sql doesn't match for SignleStore

    I'm trying to use this library for SingleStore MySQL DB. By Insertbuilder, I've got this query string.

    pgQb().Insert("hello").Columns("user_id", "name").Values(
    	 	3, "test"
    )
    

    INSERT INTO hello(user_id, name) VALUES ($1,$2) [3 "test"]

    Here is a valid query in SingleStore. INSERT INTO hello(user_id,name) VALUES (2, 'test323'); If I run this query in the SingleStore query field, it shows an error.

    Looking forward to hearing the solution soon. Best.

  • query.OrderBy working fine for DESC but not for ASC

    query.OrderBy working fine for DESC but not for ASC

    I have a Quotes table, there is a created_at time, in the format of DATETIME, I am using the line

    query = query.OrderBy(Quotes.created_at DESC")

    Which totally works fine, but when I use it as

    query = query.OrderBy(Quotes.created_at ASC")

    It is throwing error, I need it in both order as it is necessary, can anybody recommend a fix or something that will help? Thanks!

  • No way to add options between INTO and VALUES on INSERT (likely MS SQL specific)

    No way to add options between INTO and VALUES on INSERT (likely MS SQL specific)

    MS SQL has a specific way for definition of returning values. It is similar to Postgres' RETURNING ID but should be placed in the middle of the query, so suffix is not applicable.

    Example (from stackoverflow):

    INSERT INTO MyTable(Name, Address, PhoneNo)
    OUTPUT INSERTED.ID
    VALUES ('Yatrix', '1234 Address Stuff', '1112223333')
    
  • how to use InsertBatch and UpdateBatch to exec muliti sql

    how to use InsertBatch and UpdateBatch to exec muliti sql

    liek this

    func (m *customLabelModel) InsertBatch(rows []*Label) error {
    
    	var labelInsertRows = strings.Join(stringx.Remove(labelFieldNames, "`id`"), ",")
    
    	insert := squirrel.Insert("").Into(m.table).Columns(labelInsertRows)
    	for _, row := range rows {
    		insert = insert.Values(row.Id, row.Title, row.PicUrl, row.Sort, row.CreateTime, row.UpdateTime, row.IsDel)
    	}
    	query, values, err := insert.ToSql()
    	if err != nil {
    		return err
    	}
    	_, err = m.conn.Exec(query, values...)
    	if err != nil {
    		return err
    	}
    	return nil
    }
    
    
Type safe SQL builder with code generation and automatic query result data mapping
Type safe SQL builder with code generation and automatic query result data mapping

Jet Jet is a complete solution for efficient and high performance database access, consisting of type-safe SQL builder with code generation and automa

Jan 6, 2023
Squat is an application that provides simple SQL data generation functionality.

Squat Squat is an application that provides simple SQL data generation functionality. It generates synthetic SQL data based on the table definition, t

Sep 22, 2022
Command line tool to generate idiomatic Go code for SQL databases supporting PostgreSQL, MySQL, SQLite, Oracle, and Microsoft SQL Server

About xo xo is a command-line tool to generate Go code based on a database schema or a custom query. xo works by using database metadata and SQL intro

Jan 8, 2023
Go fearless SQL. Sqlvet performs static analysis on raw SQL queries in your Go code base.

Sqlvet Sqlvet performs static analysis on raw SQL queries in your Go code base to surface potential runtime errors at build time. Feature highlights:

Dec 19, 2022
A Golang library for using SQL.

dotsql A Golang library for using SQL. It is not an ORM, it is not a query builder. Dotsql is a library that helps you keep sql files in one place and

Dec 27, 2022
a golang library for sql builder

Gendry gendry is a Go library that helps you operate database. Based on go-sql-driver/mysql, it provides a series of simple but useful tools to prepar

Dec 26, 2022
SQL builder and query library for golang

__ _ ___ __ _ _ _ / _` |/ _ \ / _` | | | | | (_| | (_) | (_| | |_| | \__, |\___/ \__, |\__,_| |___/ |_| goqu is an expressive SQL bu

Dec 30, 2022
A Go (golang) package that enhances the standard database/sql package by providing powerful data retrieval methods as well as DB-agnostic query building capabilities.

ozzo-dbx Summary Description Requirements Installation Supported Databases Getting Started Connecting to Database Executing Queries Binding Parameters

Dec 31, 2022
golang orm and sql builder

gosql gosql is a easy ORM library for Golang. Style: var userList []UserModel err := db.FetchAll(&userList, gosql.Columns("id","name"), gosql.

Dec 22, 2022
This is a SQL Finder, Admin panel finder, and http analyzer that goes basses off requests. Made from 100% golang

This is a SQL Finder, Admin panel finder that goes basses off requests. Made from 100% golang

May 19, 2022
Database Abstraction Layer (dbal) for Go. Support SQL builder and get result easily (now only support mysql)

godbal Database Abstraction Layer (dbal) for go (now only support mysql) Motivation I wanted a DBAL that No ORM、No Reflect、Concurrency Save, support S

Nov 17, 2022
SQL query builder for Go

GoSQL Query builder with some handy utility functions. Documentation For full documentation see the pkg.go.dev or GitBook. Examples // Open database a

Dec 12, 2022
Write your SQL queries in raw files with all benefits of modern IDEs, use them in an easy way inside your application with all the profit of compile time constants

About qry is a general purpose library for storing your raw database queries in .sql files with all benefits of modern IDEs, instead of strings and co

Dec 25, 2022
Type safe SQL query builder and struct mapper for Go

sq (Structured Query) ?? ?? sq is a code-generated, type safe query builder and struct mapper for Go. ?? ?? Documentation • Reference • Examples This

Dec 19, 2022
Fast SQL query builder for Go

sqlf A fast SQL query builder for Go. sqlf statement builder provides a way to: Combine SQL statements from fragments of raw SQL and arguments that ma

Dec 23, 2022
💥 A lightweight DSL & ORM which helps you to write SQL in Go.
💥 A lightweight DSL & ORM which helps you to write SQL in Go.

sqlingo is a SQL DSL (a.k.a. SQL Builder or ORM) library in Go. It generates code from the database and lets you write SQL queries in an elegant way.

Jan 2, 2023
GraphJin - Build APIs in 5 minutes with GraphQL. An instant GraphQL to SQL compiler.
GraphJin - Build APIs in 5 minutes with GraphQL. An instant GraphQL to SQL compiler.

GraphJin - Build APIs in 5 minutes GraphJin gives you a high performance GraphQL API without you having to write any code. GraphQL is automagically co

Jan 4, 2023
Analyzer: helps uncover bugs by reporting a diagnostic for mistakes of *sql.Rows usage.

sqlrows sqlrows is a static code analyzer which helps uncover bugs by reporting a diagnostic for mistakes of sql.Rows usage. Install You can get sqlro

Mar 24, 2022
LBADD: An experimental, distributed SQL database
LBADD: An experimental, distributed SQL database

LBADD Let's build a distributed database. LBADD is an experimental distributed SQL database, written in Go. The goal of this project is to build a dat

Nov 29, 2022