Fluent SQL generation for golang

Overview

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!

    opened by umisama 14
  • 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.

    opened by elwinar 12
  • 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.

    opened by elgris 11
  • 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?

    opened by benjamin-thomas 10
  • 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.

    opened by collinglass 10
  • 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?

    opened by jsimnz 9
  • 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!

    opened by umisama 9
  • 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 😢

    opened by slnt 7
  • 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.

    opened by cv 7
  • 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

    opened by scags9876 7
  • Feature Request: QueryRow() on UpdateBuilder

    Feature Request: QueryRow() on UpdateBuilder

    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!

    opened by ngauthier 7
  • feat: add support for row (and optional column) aliases

    feat: add support for row (and optional column) aliases

    Add support for MySQL 8's row alias

    From the MySQL 8 docs:

    The use of VALUES() to refer to the new row and columns is deprecated beginning with MySQL 8.0.20, and is subject to removal in a future version of MySQL.

    opened by Vesquen 1
  • feat: Add index hints (use, force, ignore)

    feat: Add index hints (use, force, ignore)

    Added following index hints:

    • USE INDEX
    • USE INDEX FOR JOIN
    • USE INDEX FOR ORDER BY
    • USE INDEX FOR GROUP BY
    • FORCE INDEX
    • FORCE INDEX FOR JOIN
    • FORCE INDEX FOR ORDER BY
    • FORCE INDEX FOR GROUP BY
    • IGNORE INDEX
    • IGNORE INDEX FOR JOIN
    • IGNORE INDEX FOR ORDER BY
    • IGNORE INDEX FOR GROUP BY

    More info: https://dev.mysql.com/doc/refman/8.0/en/index-hints.html

    opened by Vesquen 0
  • Question: Build INSERT statement on SELECT from VALUES

    Question: Build INSERT statement on SELECT from VALUES

    I have been trying to build a query which results into a statement like this:

    INSERT INTO geo_table (name, geog)
    SELECT col1, st_makepoint(col2, col3)
    FROM (VALUES ($1, $2, $3)) temp (col1, col2, col3);
    

    but I haven't been able to bind the values in any way. The closest I'm getting is:

    psql := sq.StatementBuilder.PlaceholderFormat(sq.Dollar)
    sql, args, _ := psql.Insert("geo_table").Columns("name", "geog").
    Select(
        psql.Select("name", "st_makepoint(longitude, latitude)").
            FromSelect(sq.Select("name", "longitude", "latitude"), "temp")
    ).
    Values(name, longitude, latitude).ToSql()
    

    which produces:

    INSERT INTO geo_table (name, geog)
    SELECT name, ST_MakePoint(longitude, latitude)
    FROM (SELECT name, longitude, latitude) AS temp
    

    and then fails with ERROR: column \"name\" does not exist (SQLSTATE 42703) (justly).

    Another option would be to put the VALUES into a WITH query, but I haven't yet been able to find a way to do it either.

    opened by akolybelnikov 0
  • Question: OrderBy column name placeholder

    Question: OrderBy column name placeholder

    Hi! It is known that SQL ORDER BY clauses cannot use a placeholder for a column name. However, my question is - is there a way in squirrel to safely build an ORDER BY clause with column name coming from user input? (i.e. building a string and then using it in builder.OrderBy seems unsafe). Thanks!

    opened by dchukmasov 1
  • Add 'QueryRow' method to DeleteBuilder

    Add 'QueryRow' method to DeleteBuilder

    This PR adds the QueryRow method to the DeleteBuilder type. It is really handy, especially in use with the RETURNING suffix, e.g. when deleting a single object from the database.

    opened by mpieczaba 0
Releases(v1.5.3)
Owner
null
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

null 795 Sep 26, 2022
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

Mateusz Urbanek 9 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

XO 3.2k Sep 24, 2022
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:

QP Hou 455 Sep 22, 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

Gustavo Chaín 650 Sep 26, 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

DiDi 1.4k Sep 26, 2022
SQL builder and query library for golang

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

Doug Martin 1.7k Sep 22, 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

Ozzo Framework 574 Sep 22, 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.

RushTeam 166 Sep 6, 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

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

徐佳军 53 Jan 23, 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

Travis Harmon 26 Sep 26, 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

Sergey Treinis 22 Jul 4, 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

null 171 Sep 1, 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

Vlad Glushchuk 84 Sep 15, 2022
💥 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.

Qishuai Liu 261 Sep 16, 2022
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

Vikram Rangnekar 2.3k Sep 20, 2022
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

GoStaticAnalysis 86 Mar 24, 2022
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

Tom Arrell 378 Aug 10, 2022