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.

Comments
  • Improved SQL alias support on columns

    Improved SQL alias support on columns

    I have the use-case where I need to alias column names. I'm currently doing this:

    type Report struct {
        First string `db:"FIRST_NAME as \"First\""'`
        Last string `db:"LAST_NAME as \"Last\""'`
    }
    

    This works, but if feels like a hack?

    Assuming this is something you'd want to add to the library, would you recommend the above, or should we look at something like a new fieldopt

    enhancement 
    opened by danielnitsche 7
  • 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
  • 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
  • fieldas does not support tags

    fieldas does not support tags

    Didn't think about this when requesting fieldas -- at the moment if the same alias is used between tags, the last specified fieldas is used. For example:

    type structWithAsAndTags struct {
    	T5 string `db:"t5" fieldas:"f4" fieldtag:"a"`
    	T6 string `db:"t5" fieldas:"f5" fieldtag:"b"`
    }
    
    func TestStructFieldAsAndTagsA(t *testing.T) {
    	a := assert.New(t)
    	s := NewStruct(new(structWithAsAndTags))
    	sb := s.SelectFromForTag("t", "a")
    	b := Build(`COPY ($?) TO '/path/to/file.csv' (FORMAT CSV, HEADER)`, sb)
    	sql, _ := b.Build()
    	a.Equal(string(sql), `COPY (SELECT t.t5 AS f4 FROM t) TO '/path/to/file.csv' (FORMAT CSV, HEADER)`)
    }
    
    func TestStructFieldAsAndTagsB(t *testing.T) {
    	a := assert.New(t)
    	s := NewStruct(new(structWithAsAndTags))
    	sb := s.SelectFromForTag("t", "b")
    	b := Build(`COPY ($?) TO '/path/to/file.csv' (FORMAT CSV, HEADER)`, sb)
    	sql, _ := b.Build()
    	a.Equal(string(sql), `COPY (SELECT t.t5 AS f5 FROM t) TO '/path/to/file.csv' (FORMAT CSV, HEADER)`)
    }
    

    At the moment the first test passes, but the second fails. The use-case being: to support a different fieldas per tag.

    Do you think it would be worth refactoring to allow fieldas to support tags in this way?

    enhancement 
    opened by danielnitsche 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
  • 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
  • Struct SelectFrom should include full table name

    Struct SelectFrom should include full table name

    When making joins, it would be useful is the Struct added the table name to the fields or if it implemented a SelectFromAs method.

    Right now, for example, there is no way of doing this:

    var c Company
    sb := companyStruct.SelectFrom("companies", &c)
    sb.Join("company_balances", "company_balances.company_id = companies.company_id").
    	Where(sb.E("balance_id", balanceID)).
    	Limit(1)
    

    This will fail as both the companies and company_balances table have a company_id field.

    enhancement 
    opened by albertorestifo 4
  • 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
  • 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 3
  • How to model WITH queries

    How to model WITH queries

    Hey @huandu,

    First, thanks so much for this repo, helped me tremendously to streamline my code to something more readable and manageable.

    One thing I'm wondering how to migrate to sqlbuilder is queries using WITH pseudo tables, e.g.

    WITH food AS (
      SELECT * FROM fruits
      UNION
      SELECT * FROM vegetables
    )
    SELECT avg(price)
    FROM food;
    

    Thanks for your help :)

    question 
    opened by altitude 3
  • Add INNER as a JoinOption

    Add INNER as a JoinOption

    I've run into a use case that could be made simpler with another JoinOption available.

    I'm dynamically generating SQL queries and conditionally change from JOIN to LEFT JOIN. Since an unmodified JOIN is equivalent to INNER JOIN then I realized that I could use JoinWithOption, and conditionally change the JoinOption.

    There are a few obvious options Ito do this:

    • Conditionally call either Join or JoinWIthOption, with a JoinOption to the second
    • Create my own const JoinOption that's just the word "INNER". Then I'll conditionally just use a package constant Inner or the imported constant sqlbuilder.LeftJoin. It's a little inconsistent, but not bad
    • Add a new const Inner JoinOption = "INNER" to the library

    Any thoughts on the last option? I figured it's an easy and reasonable addition. I can create a PR if you think it's a good path forward for the library.

    enhancement 
    opened by rw-access 3
  • Why update method using flavor not replace with $ ?

    Why update method using flavor not replace with $ ?

    Hi @huandu, why i'm using flavor for update method is not work for query builder example 1, because this flavor not replace all ? in query update to $.

    UPDATE BUILDER 1

    psql := sqlb.PostgreSQL
    query := psql.NewUpdateBuilder().Update("todos").Set("name = ?", "category = ?", "description = ?").Where("id = ?").String()
    
    fmt.Println(query) // UPDATE todos SET name = ?, category = ?, description = ? WHERE id = ?
    

    UPDATE BUILDER 2

    psql := sqlb.PostgreSQL
    query := psql.NewUpdateBuilder().Update("todos")
    query.Set(query.Equal("name", "?"), query.Equal("category", "?"), query.Equal("description", "?")).Where(query.Equal("id", "?")).String()
    
    fmt.Println(query) //UPDATE todos SET name = $1, category = $2, description = $3 WHERE id = $4
    
    question 
    opened by restuwahyu13 1
  • Add CQL support

    Add CQL support

    • Add CQL flavor.
    • Add support for CQL argument compilation.
    • Adopt substests for interpolation tests.
    • Add support for CQL interpolation.
    • Add support for CQL blobs: https://docs.datastax.com/en/cql-oss/3.x/cql/cql_reference/blob_r.html
    • Add CQL timestamp interpolation support: https://docs.datastax.com/en/cql-oss/3.x/cql/cql_reference/timestamp_type_r.html
    • Add builder test for CQL.
    • Add support for the NOW function: https://docs.datastax.com/en/cql-oss/3.3/cql/cql_reference/timeuuid_functions_r.html?hl=now
    • Add support for CQL update IF statement: https://docs.datastax.com/en/cql-oss/3.3/cql/cql_reference/cqlUpdate.html#Conditionallyupdatingcolumns
    • Drop table prefixes for CQL SELECTs.
    • Switch to single quoting for CQL queries: https://docs.datastax.com/en/cql-oss/3.3/cql/cql_reference/escape_char_r.html
    • Update documentation.
    • Use valid CQL in tests.
    • Support CQL LIMIT.
    enhancement 
    opened by SpencerC 3
Releases(v1.18.0)
  • v1.18.0(Dec 15, 2022)

    We add a new flavor ClickHouse in this package. See #92 for details. Thanks for your contribution, @hanyuancheung.

    Full Changelog: https://github.com/huandu/go-sqlbuilder/compare/v1.17.0...v1.18.0

    Source code(tar.gz)
    Source code(zip)
  • v1.17.0(Nov 24, 2022)

    Struct and interpolate methods are aware of driver.Valuer type now. Thank @Sora233 to raise this issue.

    Full Changelog: https://github.com/huandu/go-sqlbuilder/compare/v1.16.0...v1.17.0

    Source code(tar.gz)
    Source code(zip)
  • v1.16.0(Sep 22, 2022)

  • v1.15.0(Jul 24, 2022)

    Per #78, add a new struct field tag fieldas to set set column alias (AS) used in SELECT. It can be useful to build SELECT statement, in which columns has alias names, with Struct.

    Full Changelog: https://github.com/huandu/go-sqlbuilder/compare/v1.14.1...v1.15.0

    Source code(tar.gz)
    Source code(zip)
  • v1.14.0(Jun 25, 2022)

    • [NEW] SelectBuilder#GroupBy and SelectBuilder#OrderBy works slightly different now. If we call them more than once, they will append columns rather than replace columns. It may be a breaking change. Please let me know if this change breaks any reasonable use case. https://github.com/huandu/go-sqlbuilder/issues/74
    • [NEW] Add new methods Struct#Columns, Struct#ColumnsForTag, Struct#Values and Struct#ValuesForTag. They can be useful when working with sqlmock. https://github.com/huandu/go-sqlbuilder/issues/75
    • [FIX] Fix small comment typo by @dgellow in https://github.com/huandu/go-sqlbuilder/pull/68
    • [FIX] Fix warnings and typos by @Serpentiel in https://github.com/huandu/go-sqlbuilder/pull/71
    • [FIX] Ignore unexported fields that are not embedded structs by @MDobak in https://github.com/huandu/go-sqlbuilder/pull/73

    Full Changelog: https://github.com/huandu/go-sqlbuilder/compare/v1.13.0...v1.14.0

    Source code(tar.gz)
    Source code(zip)
  • 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.6.0(Nov 11, 2019)

  • 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.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
Inflection is a string transformation library. It transforms strings from CamelCase to underscored string.

Inflection Inflection is a string transformation library. It transforms strings from CamelCase to underscored string. This is an implement of Inflecti

null 2 Jul 25, 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 Dec 26, 2022
Web-based, zero-config, dependency-free database schema change and version control tool for teams

Live Demo • Install • Help • Development • Design Doc Bytebase is a web-based, zero-config, dependency-free database schema change and version control

Bytebase 4.6k Jan 1, 2023
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 718 Dec 6, 2022
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. 176 Dec 16, 2022
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
BQB is a lightweight and easy to use query builder that works with sqlite, mysql, mariadb, postgres, and others.

Basic Query Builder Why Simple, lightweight, and fast Supports any and all syntax by the nature of how it works Doesn't require learning special synta

Aaron M 61 Dec 7, 2022
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 2k Jan 7, 2023
Go-sql-reader - Go utility to read the externalised sql with predefined tags

go-sql-reader go utility to read the externalised sql with predefined tags Usage

null 0 Jan 25, 2022
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 13.1k Jan 7, 2023
a powerful mysql toolset with Go

go-mysql A pure go library to handle MySQL network protocol and replication. Call for Committer/Maintainer Sorry that I have no enough time to maintai

siddontang 3.9k Dec 28, 2022
Thin clones of PostgreSQL to build powerful development, test, QA, staging environments

Database Lab Engine (DLE) ⚡ Blazing-fast cloning of PostgreSQL databases ?? Thin clones of PostgreSQL to build powerful development, test, QA, staging

Postgres.ai 1.5k Jan 3, 2023
A go Library for scan database/sql rows to struct、slice、other types. And it support multiple databases connection management

ploto A go Library for scan database/sql rows to struct、slice、other types. And it support multiple databases connection management It's not an ORM. wo

solar 3 Nov 3, 2022
Querycrate - A simple library that loads and keeps SQL queries from files

QueryCrate A simple library for loading & getting string queries from files. How

A Really Fake Floordiv Since 4th January, 2022 1 Feb 15, 2022
Zero boilerplate database operations for Go

(Now compatible with MySQL and PostgreSQL!) Everyone knows that performing simple DATABASE queries in Go takes numerous lines of code that is often re

null 357 Jan 2, 2023
Go library that stores data in Redis with SQL-like schema

Go library that stores data in Redis with SQL-like schema. The goal of this library is we can store data in Redis with table form.

kaharman 2 Mar 14, 2022
A tool to run queries in defined frequency and expose the count as prometheus metrics. Supports MongoDB and SQL

query2metric A tool to run db queries in defined frequency and expose the count as prometheus metrics. Why ? Product metrics play an important role in

S Santhosh Nagaraj 19 Jul 1, 2022
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 4.2k Dec 29, 2022