A flexible and powerful SQL string builder library plus a zero-config ORM.

Overview

SQL builder for Go

Go GoDoc Go Report Coverage Status

Package sqlbuilder provides a set of flexible and powerful SQL string builders. The only goal of this package is to build SQL string with arguments which can be used in DB#Query or DB#Exec defined in package database/sql.

Install

Use go get to install this package.

go get github.com/huandu/go-sqlbuilder

Usage

Basic usage

We can build a SQL really quick with this package.

sql := sqlbuilder.Select("id", "name").From("demo.user").
    Where("status = 1").Limit(10).
    String()

fmt.Println(sql)

// Output:
// SELECT id, name FROM demo.user WHERE status = 1 LIMIT 10

In the most cases, we need to escape all input from user. In this case, create a builder before starting.

sb := sqlbuilder.NewSelectBuilder()

sb.Select("id", "name", sb.As("COUNT(*)", "c"))
sb.From("user")
sb.Where(sb.In("status", 1, 2, 5))

sql, args := sb.Build()
fmt.Println(sql)
fmt.Println(args)

// Output:
// SELECT id, name, COUNT(*) AS c FROM user WHERE status IN (?, ?, ?)
// [1 2 5]

Pre-defined SQL builders

Following builders are implemented right now. API document and examples are provided in the godoc document.

There is a method SQL(sql string) implemented by all statement builders like SelectBuilder. We can use this method to insert any arbitrary SQL fragment when building a SQL. It's quite useful to build SQL containing non-standard syntax supported by a OLTP or OLAP system.

// Build a SQL to create a HIVE table.
sql := sqlbuilder.CreateTable("users").
    SQL("PARTITION BY (year)").
    SQL("AS").
    SQL(
        sqlbuilder.Select("columns[0] id", "columns[1] name", "columns[2] year").
            From("`all-users.csv`").
            Limit(100).
            String(),
    ).
    String()

fmt.Println(sql)

// Output:
// CREATE TABLE users PARTITION BY (year) AS SELECT columns[0] id, columns[1] name, columns[2] year FROM `all-users.csv` LIMIT 100

To learn how to use builders, check out examples on GoDoc.

Build SQL for MySQL, PostgreSQL or SQLite

Parameter markers are different in MySQL, PostgreSQL and SQLite. This package provides some methods to set the type of markers (we call it "flavor") in all builders.

By default, all builders uses DefaultFlavor to build SQL. The default value is MySQL.

There is a BuildWithFlavor method in Builder interface. We can use it to build a SQL with provided flavor.

We can wrap any Builder with a default flavor through WithFlavor.

To be more verbose, we can use PostgreSQL.NewSelectBuilder() to create a SelectBuilder with the PostgreSQL flavor. All builders can be created in this way.

Right now, there are only three flavors, MySQL, PostgreSQL and SQLite. Open new issue to me to ask for a new flavor if you find it necessary.

Using Struct as a light weight ORM

Struct stores type information and struct fields of a struct. It's a factory of builders. We can use Struct methods to create initialized SELECT/INSERT/UPDATE/DELETE builders to work with the struct. It can help us to save time and avoid human-error on writing column names.

We can define a struct type and use field tags to let Struct know how to create right builders for us.

type ATable struct {
    Field1     string                                    // If a field doesn't has a tag, use "Field1" as column name in SQL.
    Field2     int    `db:"field2"`                      // Use "db" in field tag to set column name used in SQL.
    Field3     int64  `db:"field3" fieldtag:"foo,bar"`   // Set fieldtag to a field. We can use methods like `Struct#SelectForTag` to use it.
    Field4     int64  `db:"field4" fieldtag:"foo"`       // If we use `s.SelectForTag(table, "foo")`, columnes of SELECT are field3 and field4.
    Ignored    int32  `db:"-"`                           // If we set field name as "-", Struct will ignore it.
    unexported int                                       // Unexported field is not visible to Struct.
    Quoted     string `db:"quoted" fieldopt:"withquote"` // Add quote to the field using back quote or double quote. See `Flavor#Quote`.
    Empty      uint   `db:"empty" fieldopt:"omitempty"`  // Omit the field in UPDATE if it is a nil or zero value.

    // The `omitempty` can be written as a function.
    // In this case, omit empty field `Tagged` when UPDATE for tag `tag1` and `tag3` but not `tag2`.
    Tagged     string `db:"tagged" fieldopt:"omitempty(tag1,tag3)" fieldtag:"tag1,tag2,tag3"`
}

Read examples for Struct to learn details of how to use it.

What's more, we can use Struct as a kind of zero-config ORM. While most ORM implementations requires several prerequisite configs to work with database connections, Struct doesn't require any config and work well with any SQL driver which works with database/sql. Struct doesn't call any database/sql API; It just creates right SQL with arguments for DB#Query/DB#Exec or a slice of address of struct fields for Rows#Scan/Row#Scan.

Here is a sample to use Struct as ORM. It should be quite straight forward for developers who are familiar with database/sql APIs.

type User struct {
    ID     int64  `db:"id"`
    Name   string `db:"name"`
    Status int    `db:"status"`
}

// A global variable to create SQL builders.
// All methods of userStruct are thread-safe.
var userStruct = NewStruct(new(User))

func ExampleStruct() {
    // Prepare SELECT query.
    //     SELECT id, name, status FROM user WHERE id = 1234
    sb := userStruct.SelectFrom("user")
    sb.Where(sb.Equal("id", 1234))

    // Execute the query.
    sql, args := sb.Build()
    rows, _ := db.Query(sql, args...)
    defer rows.Close()

    // Scan row data and set value to user.
    // Suppose we get following data.
    //
    //     |  id  |  name  | status |
    //     |------|--------|--------|
    //     | 1234 | huandu | 1      |
    var user User
    rows.Scan(userStruct.Addr(&user)...)

    fmt.Println(sql)
    fmt.Println(args)
    fmt.Printf("%#v", user)

    // Output:
    // SELECT user.id, user.name, user.status FROM user WHERE id = ?
    // [1234]
    // sqlbuilder.User{ID:1234, Name:"huandu", Status:1}
}

In many production environments, table column names are usually snake_case words, e.g. user_id, while we have to use CamelCase in struct types to make struct fields public and golint happy. It's a bit redundant to use the db tag in every struct field. If there is a certain rule to map field names to table column names, We can use field mapper function to make code simpler.

The DefaultFieldMapper is a global field mapper function to convert field name to new style. By default, it sets to nil and does nothing. If we know that most table column names are snake_case words, we can set DefaultFieldMapper to sqlbuilder.SnakeCaseMapper. If we have some special cases, we can set custom mapper to a Struct by calling WithFieldMapper.

Following are special notes regarding to field mapper.

  • Field tag has precedence over field mapper function - thus, mapper is ignored if the db tag is set;
  • Field mapper is called only once on a Struct when the Struct is used to create builder for the first time.

See field mapper function sample as a demo.

Nested SQL

It's quite straight forward to create a nested SQL: use a builder as an argument to nest it.

Here is a sample.

sb := sqlbuilder.NewSelectBuilder()
fromSb := sqlbuilder.NewSelectBuilder()
statusSb := sqlbuilder.NewSelectBuilder()

sb.Select("id")
sb.From(sb.BuilderAs(fromSb, "user")))
sb.Where(sb.In("status", statusSb))

fromSb.Select("id").From("user").Where(fromSb.GreaterThan("level", 4))
statusSb.Select("status").From("config").Where(statusSb.Equal("state", 1))

sql, args := sb.Build()
fmt.Println(sql)
fmt.Println(args)

// Output:
// SELECT id FROM (SELECT id FROM user WHERE level > ?) AS user WHERE status IN (SELECT status FROM config WHERE state = ?)
// [4 1]

Use sql.Named in a builder

The function sql.Named defined in database/sql can create a named argument in SQL. It's necessary if we want to reuse an argument several times in one SQL. It's still quite simple to use named arguments in a builder: use it as an argument.

Here is a sample.

now := time.Now().Unix()
start := sql.Named("start", now-86400)
end := sql.Named("end", now+86400)
sb := sqlbuilder.NewSelectBuilder()

sb.Select("name")
sb.From("user")
sb.Where(
    sb.Between("created_at", start, end),
    sb.GE("modified_at", start),
)

sql, args := sb.Build()
fmt.Println(sql)
fmt.Println(args)

// Output:
// SELECT name FROM user WHERE created_at BETWEEN @start AND @end AND modified_at >= @start
// [{{} start 1514458225} {{} end 1514544625}]

Argument modifiers

There are several modifiers for arguments.

  • List(arg) represents a list of arguments. If arg is a slice or array, e.g. a slice with 3 ints, it will be compiled to ?, ?, ? and flattened in the final arguments as 3 ints. It's a tool for convenience. We can use it in the IN expression or VALUES of INSERT INTO.
  • Named(name, arg) represents a named argument. It only works with Build or BuildNamed to define a named placeholder using syntax ${name}.
  • Raw(expr) marks an expr as a plain string in SQL rather than an argument. When we build a builder, the value of raw expressions are copied in SQL string directly without leaving any ? in SQL.

Freestyle builder

A builder is only a way to record arguments. If we want to build a long SQL with lots of special syntax (e.g. special comments for a database proxy), simply use Buildf to format a SQL string using a fmt.Sprintf-like syntax.

sb := sqlbuilder.NewSelectBuilder()
sb.Select("id").From("user")

explain := sqlbuilder.Buildf("EXPLAIN %v LEFT JOIN SELECT * FROM banned WHERE state IN (%v, %v)", sb, 1, 2)
sql, args := explain.Build()
fmt.Println(sql)
fmt.Println(args)

// Output:
// EXPLAIN SELECT id FROM user LEFT JOIN SELECT * FROM banned WHERE state IN (?, ?)
// [1 2]

Using special syntax to build SQL

Package sqlbuilder defines special syntax to represent an uncompiled SQL internally. If we want to take advantage of the syntax to build customized tools, we can use Build to compile it with arguments.

The format string uses special syntax to represent arguments.

  • $? refers successive arguments passed in the call. It works similar as %v in fmt.Sprintf.
  • $0 $1 ... $n refers nth-argument passed in the call. Next $? will use arguments n+1.
  • ${name} refers a named argument created by Named with name.
  • $$ is a "$" string.
sb := sqlbuilder.NewSelectBuilder()
sb.Select("id").From("user").Where(sb.In("status", 1, 2))

b := sqlbuilder.Build("EXPLAIN $? LEFT JOIN SELECT * FROM $? WHERE created_at > $? AND state IN (${states}) AND modified_at BETWEEN $2 AND $?",
    sb, sqlbuilder.Raw("banned"), 1514458225, 1514544625, sqlbuilder.Named("states", sqlbuilder.List([]int{3, 4, 5})))
sql, args := b.Build()

fmt.Println(sql)
fmt.Println(args)

// Output:
// EXPLAIN SELECT id FROM user WHERE status IN (?, ?) LEFT JOIN SELECT * FROM banned WHERE created_at > ? AND state IN (?, ?, ?) AND modified_at BETWEEN ? AND ?
// [1 2 1514458225 3 4 5 1514458225 1514544625]

If we just want to use ${name} syntax to refer named arguments, use BuildNamed instead. It disables all special syntax but ${name} and $$.

Interpolate args in the sql

Some SQL drivers doesn't actually implement StmtExecContext#ExecContext. They will fail when len(args) > 0. The only solution is to interpolate args in the sql, and execute the interpolated query with the driver.

Security warning: I try my best to escape special characters in interpolate methods, but it's still less secure than Stmt implemented by SQL servers.

This feature is inspired by interpolation feature in package github.com/go-sql-driver/mysql.

Here is a sample for MySQL.

sb := MySQL.NewSelectBuilder()
sb.Select("name").From("user").Where(
    sb.NE("id", 1234),
    sb.E("name", "Charmy Liu"),
    sb.Like("desc", "%mother's day%"),
)
sql, args := sb.Build()
query, err := MySQL.Interpolate(sql, args)

fmt.Println(query)
fmt.Println(err)

// Output:
// SELECT name FROM user WHERE id <> 1234 AND name = 'Charmy Liu' AND desc LIKE '%mother\'s day%'
// <nil>

Here is a sample for PostgreSQL. Note that the dollar quote is supported.

// Only the last `$1` is interpolated.
// Others are not interpolated as they are inside dollar quote (the `$$`).
query, err := PostgreSQL.Interpolate(`
CREATE FUNCTION dup(in int, out f1 int, out f2 text) AS $$
    SELECT $1, CAST($1 AS text) || ' is text'
$$
LANGUAGE SQL;

SELECT * FROM dup($1);`, []interface{}{42})

fmt.Println(query)
fmt.Println(err)

// Output:
//
// CREATE FUNCTION dup(in int, out f1 int, out f2 text) AS $$
//     SELECT $1, CAST($1 AS text) || ' is text'
// $$
// LANGUAGE SQL;
//
// SELECT * FROM dup(42);
// <nil>

FAQ

What's the difference between this package and squirrel

Package squirrel is another SQL builder package with outstanding design and high code quality. Comparing with squirrel, go-sqlbuilder is much more extensible with more built-in features.

Here are details.

  • API design: The core of go-sqlbuilder is Builder and Args. Nearly all features are built on top of them. If we want to extend this package, e.g. support EXPLAIN, we can use Build("EXPLAIN $?", builder) to add EXPLAIN in front of any SQL.
  • ORM: Package squirrel doesn't provide ORM directly. There is another package structable, which is based on squirrel, designed for ORM.
  • No design pitfalls: There is no design pitfalls like squirrel.Eq{"mynumber": []uint8{1,2,3}}. I'm proud of it. :)

License

This package is licensed under MIT license. See LICENSE for details.

Issues
  • Needs JOIN syntax for SQL query

    Needs JOIN syntax for SQL query

    Do you plan to do JOIN?

    enhancement 
    opened by vvasystem 8
  • why are select, order by, and group by $ escaped

    why are select, order by, and group by $ escaped

    Is there any particular reason for this? Is there a workaround to use sub-expressions with positional arguments ($1, $2, etc. obtained using .Var()) in these positions?

    bug 
    opened by dvic 6
  • 生成插入SQL如何忽略主键

    生成插入SQL如何忽略主键

    question 
    opened by xiusin 5
  • Plan about support SQLite3

    Plan about support SQLite3

    Could you please clarify plans about SQLite3 support?

    enhancement 
    opened by vit1251 5
  • Auto create table builder by fields in struct

    Auto create table builder by fields in struct

    type User struct{
      ID int `db:"id" sqldb:"int(11)"`
      Login string `db:"login" sqldb:"varchar(255)"`
    }
    
    ctb := sqlbuilder.NewCreateTableBuilder()
    // compile struct to ctb obj
    ctb = ctb.IfNotExists()
    println(ctb.String())
    // CREATE TABLE IF NOT EXISTS ...
    
    enhancement wontfix 
    opened by writerim 5
  • JSON_TABLE issue

    JSON_TABLE issue

    Input: sb.From("warehouse", "JSON_TABLE(data, '$[*]' COLUMNS (id JSON PATH '$.id')) AS warehouseJson")`

    Output: FROM warehouse, JSON_TABLE(data, '[*]' COLUMNS (id JSON PATH '.id')) AS warehouseJson

    "$" is missing :-/

    question 
    opened by srzkptrsk 4
  • SQL Server flavor

    SQL Server flavor

    Hello,

    This is a very interesting project, thank you for publishing @huandu !

    Right now, there are only three flavors, MySQL, PostgreSQL and SQLite. Open new issue to me to ask for a new flavor if you find it necessary.

    Any chance you could add SQL Server support ? If go-sqlbuilder supports the 2nd, 4th and 9th RDBMS by rank, why not the 3rd as well? I believe this will increase project adoption by enterprise developers.

    enhancement 
    opened by ghost 4
  • Support for `FOR UPDATE` or `IN SHARE MODE` in `SELECT` clause

    Support for `FOR UPDATE` or `IN SHARE MODE` in `SELECT` clause

    RT.

    Maybe we can support these two clause in SELECT statement?

    enhancement 
    opened by wangggong 4
  • Add SQLServer flavor

    Add SQLServer flavor

    Please help me to review the code, @MagnifiGo. Thanks.

    I don't have a SQLServer environment to execute real queries. I just read official docs and source code of the most famous SQLServer Go driver as reference.

    enhancement 
    opened by huandu 4
  • struct bulk insert

    struct bulk insert

    InsertInto and InsertIntoForTag support bulk insert

    opened by petersunbag 4
  • No easy way to use .In(...) with a slice

    No easy way to use .In(...) with a slice

    Hi,

    I have the following problem: Something like this is not possible:

    func getSomethingByIds(ctx context.Contex, ids []int32) error {
    	b := p.getANewSelect(ctx)
    	b.Where(b.In("something.somethingId", ids...))
    //...
    }
    

    It cannot convert []int32 to []interface{} on the fly. But as this seems to be a very usefull pattern to me (to pass any slice in an In statement), it would be great to have support for this. Also b.In("parameter.parameterid", ids) doesn't work, which would also be a nice syntax and possible with reflection.

    The only workaround I see for now is to first convert all values to an interface{} in a for loop before passing it, which is very cumbersome and not nice.

    Possible solutions:

    1. Just provide an .InSlice(interface{}) which only accepts slices directly and panics if it's no slice (as it's a programmer error)
    2. Use reflection in .In() to and check if the first passed value is a slice.

    What do you think?

    question 
    opened by aligator 2
  • ON DUPLICATE KEY UPDATE

    ON DUPLICATE KEY UPDATE

    enhancement question 
    opened by Torwang1 8
Releases(v1.13.0)
  • v1.13.0(Sep 7, 2021)

  • v1.12.0(Feb 21, 2021)

    Per #59, new APIs around field mapper functions are added.

    In many production environments, table column names are usually snake_case words, e.g. user_id, while we have to use CamelCase in struct types to make struct fields public and golint happy. It's a bit redundant to use the db tag in every struct field. If there is a certain rule to map field names to table column names, We can use field mapper function to make code simpler.

    See field mapper function sample as a demo.

    Source code(tar.gz)
    Source code(zip)
  • v1.11.0(Feb 3, 2021)

    There is a method SQL(sql string) implemented by all statement builders like SelectBuilder. We can use this method to insert any arbitrary SQL fragment when building a SQL. It's quite useful to build SQL containing non-standard syntax supported by a OLTP or OLAP system.

    See README.md for more details.

    Source code(tar.gz)
    Source code(zip)
  • v1.10.0(Dec 26, 2020)

  • v1.9.0(Nov 2, 2020)

  • v1.8.0(Jul 28, 2020)

  • v1.7.0(Nov 21, 2019)

    Per #40, table name should be added as prefix (like table.col) to column names to avoid column name conflict. It's useful when building a SELECT with JOIN.

    Source code(tar.gz)
    Source code(zip)
  • v1.5.0(Nov 11, 2019)

    Per #32, add new method SetMore in UpdateBuilder to add more assignments to UPDATE.

    Per #33, add new method InsertIgnoreInto in InsertBuilder to support INSERT IGNORE INTO.

    Source code(tar.gz)
    Source code(zip)
  • v1.6.0(Nov 11, 2019)

  • v1.4.0(Apr 24, 2019)

    #23 This feature is inspired by the interpolation feature implemented by github.com/go-sql-driver/mysql. It's designed to work with some less functional drivers to bind params to a SQL on client side rather than server side.

    Source code(tar.gz)
    Source code(zip)
  • v1.3.0(Apr 21, 2019)

Owner
Huan Du
I'm a software developer from China. I feel very satisfied when other developers use my code to solve their own problems.
Huan Du
A fully-featured AWS Athena database driver (+ athenareader https://github.com/uber/athenadriver/tree/master/athenareader)

?? athenadriver - A fully-featured AWS Athena database driver for Go ?? athenareader - A moneywise command line utililty to query athena in command li

Uber Open Source 87 Oct 7, 2021
A MySQL-compatible relational database with a storage agnostic query engine. Implemented in pure Go.

go-mysql-server go-mysql-server is a SQL engine which parses standard SQL (based on MySQL syntax) and executes queries on data sources of your choice.

DoltHub 634 Oct 23, 2021
pggen - generate type safe Go methods from Postgres SQL queries

pggen - generate type safe Go methods from Postgres SQL queries pggen is a tool that generates Go code to provide a typesafe wrapper around Postgres q

Joe Schafer 111 Oct 20, 2021
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.3k Oct 25, 2021
go mysql driver, support distributed transaction

Go-MySQL-Driver A MySQL-Driver for Go's database/sql package Features Requirements Installation Usage DSN (Data Source Name) Password Protocol Address

Open Transaction 26 Sep 22, 2021
A Go SQL query builder and struct mapper.

godb - a Go query builder and struct mapper godb is a simple Go query builder and struct mapper, not a full-featured ORM. godb does not manage relatio

Samuel GAY 673 Oct 14, 2021
Universal command-line interface for SQL databases

usql A universal command-line interface for PostgreSQL, MySQL, Oracle Database, SQLite3, Microsoft SQL Server, and many other databases including NoSQ

XO 6.8k Oct 16, 2021
Goose database migration tool - fork of https://bitbucket.org/liamstask/goose

goose Goose is a database migration tool. Manage your database schema by creating incremental SQL changes or Go functions. Goals of this fork github.c

Pressly Inc. 1.9k Oct 14, 2021
MySQL Storage engine conversion,Support mutual conversion between MyISAM and InnoDB engines.

econvert MySQL Storage engine conversion 简介 此工具用于MySQL存储引擎转换,支持CTAS和ALTER两种模式,目前只支持MyISAM和InnoDB存储引擎相互转换,其它引擎尚不支持。 注意:当对表进行引擎转换时,建议业务停止访问或者极少量访问时进行。 原

null 5 Sep 5, 2021
sqlbench measures and compares the execution time of one or more SQL queries.

sqlbench sqlbench measures and compares the execution time of one or more SQL queries. The main use case is benchmarking simple CPU-bound query varian

Felix Geisendörfer 343 Sep 15, 2021
OctoSQL is a query tool that allows you to join, analyse and transform data from multiple databases and file formats using SQL.

OctoSQL OctoSQL is a query tool that allows you to join, analyse and transform data from multiple databases, streaming sources and file formats using

Jacob Martin 2.5k Oct 18, 2021
Go package for sharding databases ( Supports every ORM or raw SQL )

Octillery Octillery is a Go package for sharding databases. It can use with every OR Mapping library ( xorm , gorp , gorm , dbr ...) implementing data

BlasTrain Co., Ltd. 145 Oct 11, 2021
write APIs using direct SQL queries with no hassle, let's rethink about SQL

SQLer SQL-er is a tiny portable server enables you to write APIs using SQL query to be executed when anyone hits it, also it enables you to define val

Mohammed Al Ashaal 1.9k Oct 18, 2021
CLI tool that can execute SQL queries on CSV, LTSV, JSON and TBLN. Can output to various formats.

trdsql CLI tool that can execute SQL queries on CSV, LTSV, JSON and TBLN. It is a tool like q, textql and others. The difference from these tools is t

Noboru Saito 649 Oct 21, 2021
Workshop: Building data-intensive apps with SingleStore, Redpanda, and Golang

This repo provides a starting point for building applications using SingleStore, Redpanda (by Vectorized), and the Go language. SingleStore is a scale-out relational database built for data-intensive workloads. Redpanda is a Kafka API compatible streaming platform for mission-critical workloads created by the team at Vectorized.

SingleStore Labs 9 Oct 1, 2021
Go package providing simple database and server interfaces for the CSV files produced by the sfomuseum/go-libraryofcongress package

go-libraryofcongress-database Go package providing simple database and server interfaces for the CSV files produced by the sfomuseum/go-libraryofcongr

San Francisco International Airport Museum 1 Oct 19, 2021
sqlx is a library which provides a set of extensions on go's standard database/sql library

sqlx is a library which provides a set of extensions on go's standard database/sql library. The sqlx versions of sql.DB, sql.TX, sql.Stmt, et al. all leave the underlying interfaces untouched, so that their interfaces are a superset on the standard ones. This makes it relatively painless to integrate existing codebases using database/sql with sqlx.

Jason Moiron 10.9k Oct 23, 2021
Parses a file and associate SQL queries to a map. Useful for separating SQL from code logic

goyesql This package is based on nleof/goyesql but is not compatible with it any more. This package introduces support for arbitrary tag types and cha

null 0 Oct 20, 2021