go-sqlbuilder

About go-sqlbuilder

This large README is shown as a plain-text preview. Read the full README at the source

# SQL builder for Go

[![Go](https://github.com/huandu/go-sqlbuilder/workflows/Go/badge.svg)](https://github.com/huandu/go-sqlbuilder/actions)
[![GoDoc](https://godoc.org/github.com/huandu/go-sqlbuilder?status.svg)](https://pkg.go.dev/github.com/huandu/go-sqlbuilder)
[![Go Report](https://goreportcard.com/badge/github.com/huandu/go-sqlbuilder)](https://goreportcard.com/report/github.com/huandu/go-sqlbuilder)
[![Coverage Status](https://coveralls.io/repos/github/huandu/go-sqlbuilder/badge.svg?branch=master)](https://coveralls.io/github/huandu/go-sqlbuilder?branch=master)

- [Install](#install)
- [Usage](#usage)
  - [Basic usage](#basic-usage)
  - [Pre-defined SQL builders](#pre-defined-sql-builders)
  - [Build `WHERE` clause](#build-where-clause)
  - [Share `WHERE` clause among builders](#share-where-clause-among-builders)
  - [Build `ORDER BY` clause](#build-order-by-clause)
  - [Build SQL for different systems](#build-sql-for-different-systems)
  - [Using `Struct` as a light weight ORM](#using-struct-as-a-light-weight-orm)
  - [Nested SQL](#nested-sql)
  - [Nested `JOIN`](#nested-join)
  - [Use `sql.Named` in a builder](#use-sqlnamed-in-a-builder)
  - [Argument modifiers](#argument-modifiers)
  - [Freestyle builder](#freestyle-builder)
  - [Clone builders](#clone-builders)
  - [Using special syntax to build SQL](#using-special-syntax-to-build-sql)
  - [Interpolate `args` in the `sql`](#interpolate-args-in-the-sql)
- [License](#license)

The `sqlbuilder` package offers a comprehensive suite of SQL string concatenation utilities. It is designed to facilitate the construction of SQL statements compatible with Go's standard library `sql.DB` and `sql.Stmt` interfaces, focusing on optimizing the performance of SQL statement creation and minimizing memory usage.

The primary objective of this package's design was to craft a SQL construction library that operates independently of specific database drivers and business logic. It is tailored to accommodate the diverse needs of enterprise environments, including the use of custom database drivers, adherence to specialized operational standards, integration into heterogeneous systems, and handling of non-standard SQL in intricate scenarios. Following its open-source release, the package has undergone extensive testing within a large-scale enterprise context, successfully managing the workload of hundreds of millions of orders daily and nearly ten million transactions daily, thus highlighting its robust performance and scalability.

This package is not restricted to any particular database driver and does not automatically establish connections with any database systems. It does not presuppose the execution of the generated SQL, making it versatile for a broad spectrum of application scenarios that involve the construction of SQL-like statements. It is equally well-suited for further development aimed at creating more business-specific database interaction packages, ORMs, and similar tools.

## Install

Install this package by executing the following command:

```shell
go get github.com/huandu/go-sqlbuilder
```

## Usage

### Basic usage

We can rapidly construct SQL statements using this package.

```go
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 common scenarios, it is necessary to escape all user inputs. To achieve this, initialize a builder at the outset.

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

This package includes the following pre-defined builders. API documentation and usage examples are available in the `godoc` online documentation.

- [Struct](https://pkg.go.dev/github.com/huandu/go-sqlbuilder#Struct): Factory for creating builders based on struct definitions.
- [CreateTableBuilder](https://pkg.go.dev/github.com/huandu/go-sqlbuilder#CreateTableBuilder): Builder for `CREATE TABLE`.
- [SelectBuilder](https://pkg.go.dev/github.com/huandu/go-sqlbuilder#SelectBuilder): Builder for `SELECT`.
- [InsertBuilder](https://pkg.go.dev/github.com/huandu/go-sqlbuilder#InsertBuilder): Builder for `INSERT`.
- [UpdateBuilder](https://pkg.go.dev/github.com/huandu/go-sqlbuilder#UpdateBuilder): Builder for `UPDATE`.
- [DeleteBuilder](https://pkg.go.dev/github.com/huandu/go-sqlbuilder#DeleteBuilder): Builder for `DELETE`.
- [UnionBuilder](https://pkg.go.dev/github.com/huandu/go-sqlbuilder#UnionBuilder): Builder for `UNION` and `UNION ALL`.
- [CTEBuilder](https://pkg.go.dev/github.com/huandu/go-sqlbuilder#CTEBuilder): Builder for Common Table Expression (CTE), e.g. `WITH name (col1, col2) AS (SELECT ...)`.
- [Buildf](https://pkg.go.dev/github.com/huandu/go-sqlbuilder#Buildf): Freestyle builder employing `fmt.Sprintf`-like syntax.
- [Build](https://pkg.go.dev/github.com/huandu/go-sqlbuilder#Build): Advanced freestyle builder utilizing special syntax as defined in [Args#Compile](https://pkg.go.dev/github.com/huandu/go-sqlbuilder#Args.Compile).
- [BuildNamed](https://pkg.go.dev/github.com/huandu/go-sqlbuilder#BuildNamed): Advanced freestyle builder that uses `${key}` to reference values by key in a map.

A unique method, `SQL(sql string)`, is implemented across all statement builders, enabling the insertion of any arbitrary SQL segment into a builder during SQL construction. This feature is particularly beneficial for crafting SQL statements that incorporate non-standard syntax required by OLTP or OLAP systems.

```go
// 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`").
            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`
```

Below are several utility methods designed to address special cases.

- [Flatten](https://pkg.go.dev/github.com/huandu/go-sqlbuilder#Flatten) enables the recursive conversion of an array-like variable into a flat slice of `[]interface{}`. For example, invoking `Flatten([]interface{"foo", []int{2, 3}})` yields `[]interface{}{"foo", 2, 3}`. This method is compatible with builder methods such as `In`, `NotIn`, `Values`, etc., facilitating the conversion of a typed array into `[]interface{}` or the merging of inputs.
- [List](https://pkg.go.dev/github.com/huandu/go-sqlbuilder#List) operates similarly to `Flatten`, with the exception that its return value is specifically intended for use as builder arguments. For example, `Buildf("my_func(%v)", List([]int{1, 2, 3})).Build()` generates SQL `my_func(?, ?, ?)` with arguments `[]interface{}{1, 2, 3}`.
- [Raw](https://pkg.go.dev/github.com/huandu/go-sqlbuilder#Raw) designates a string as a "raw string" within arguments. For instance, `Buildf("SELECT %v", Raw("NOW()")).Build()` results in SQL `SELECT NOW()`.

For detailed instructions on utilizing these builders, consult the [examples provided on GoDoc](https://pkg.go.dev/github.com/huandu/go-sqlbuilder#pkg-examples).

### Build `WHERE` clause

`WHERE` clause is the most important part of a SQL. We can use `Where` method to add one or more conditions to a builder.

To simplify the construction of `WHERE` clauses, a utility type named `Cond` is provided for condition building. All builders that support `WHERE` clauses possess an anonymous `Cond` field, enabling the invocation of `Cond` methods on these builders.

```go
sb := sqlbuilder.Select("id").From("user")
sb.Where(
    sb.In("status", 1, 2, 5),
    sb.Or(
        sb.Equal("name", "foo"),
        sb.Like("email", "foo@%"),
    ),
)

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

// Output:
// SELECT id FROM user WHERE status IN (?, ?, ?) AND (name = ? OR email LIKE ?)
// [1 2 5 foo foo@%]
```

There are many methods for building conditions.

- [Cond.Equal](https://pkg.go.dev/github.com/huandu/go-sqlbuilder#Cond.Equal)/[Cond.E](https://pkg.go.dev/github.com/huandu/go-sqlbuilder#Cond.E)/[Cond.EQ](https://pkg.go.dev/github.com/huandu/go-sqlbuilder#Cond.EQ): `field = value`.
- [Cond.NotEqual](https://pkg.go.dev/github.com/huandu/go-sqlbuilder#Cond.NotEqual)/[Cond.NE](https://pkg.go.dev/github.com/huandu/go-sqlbuilder#Cond.NE)/[Cond.NEQ](https://pkg.go.dev/github.com/huandu/go-sqlbuilder#Cond.NEQ): `field <> value`.
- [Cond.GreaterThan](https://pkg.go.dev/github.com/huandu/go-sqlbuilder#Cond.GreaterThan)/[Cond.G](https://pkg.go.dev/github.com/huandu/go-sqlbuilder#Cond.G)/[Cond.GT](https://pkg.go.dev/github.com/huandu/go-sqlbuilder#Cond.GT): `field > value`.
- [Cond.GreaterEqualThan](https://pkg.go.dev/github.com/huandu/go-sqlbuilder#Cond.GreaterEqualThan)/[Cond.GE](https://pkg.go.dev/github.com/huandu/go-sqlbuilder#Cond.GE)/[Cond.GTE](https://pkg.go.dev/github.com/huandu/go-sqlbuilder#Cond.GTE): `field >= value`.
- [Cond.LessThan](https://pkg.go.dev/github.com/huandu/go-sqlbuilder#Cond.LessThan)/[Cond.L](https://pkg.go.dev/github.com/huandu/go-sqlbuilder#Cond.L)/[Cond.LT](https://pkg.go.dev/github.com/huandu/go-sqlbuilder#Cond.LT): `field < value`.
- [Cond.LessEqualThan](https://pkg.go.dev/github.com/huandu/go-sqlbuilder#Cond.LessEqualThan)/[Cond.LE](https://pkg.go.dev/github.com/huandu/go-sqlbuilder#Cond.LE)/[Cond.LTE](https://pkg.go.dev/github.com/huandu/go-sqlbuilder#Cond.LTE): `field <= value`.
- [Cond.In](https://pkg.go.dev/github.com/huandu/go-sqlbuilder#Cond.In): `field IN (value1, value2, ...)`.
- [Cond.NotIn](https://pkg.go.dev/github.com/huandu/go-sqlbuilder#Cond.NotIn): `field NOT IN (value1, value2, ...)`.
- [Cond.Like](https://pkg.go.dev/github.com/huandu/go-sqlbuilder#Cond.Like): `field LIKE value`.
- [Cond.ILike](https://pkg.go.dev/github.com/huandu/go-sqlbuilder#Cond.ILike): `field ILIKE value`.
- [Cond.NotLike](https://pkg.go.dev/github.com/huandu/go-sqlbuilder#Cond.NotLike): `field NOT LIKE value`.
- [Cond.NotILike](https://pkg.go.dev/github.com/huandu/go-sqlbuilder#Cond.NotILike): `field NOT ILIKE value`.
- [Cond.Between](https://pkg.go.dev/github.com/huandu/go-sqlbuilder#Cond.Between): `field BETWEEN lower AND upper`.
- [Cond.NotBetween](https://pkg.go.dev/github.com/huandu/go-sqlbuilder#Cond.NotBetween): `field NOT BETWEEN lower AND upper`.
- [Cond.IsNull](https://pkg.go.dev/github.com/huandu/go-sqlbuilder#Cond.IsNull): `field IS NULL`.
- [Cond.IsNotNull](https://pkg.go.dev/github.com/huandu/go-sqlbuilder#Cond.IsNotNull): `field IS NOT NULL`.
- [Cond.Exists](https://pkg.go.dev/github.com/huandu/go-sqlbuilder#Cond.Exists): `EXISTS (subquery)`.
- [Cond.NotExists](https://pkg.go.dev/github.com/huandu/go-sqlbuilder#Cond.NotExists): `NOT EXISTS (subquery)`.
- [Cond.Not](https://pkg.go.dev/github.com/huandu/go-sqlbuilder#Cond.Not): `NOT expr`.
- [Cond.Any](https://pkg.go.dev/github.com/huandu/go-sqlbuilder#Cond.Any): `field op ANY (value1, value2, ...)`.
- [Cond.All](https://pkg.go.dev/github.com/huandu/go-sqlbuilder#Cond.All): `field op ALL (value1, value2, ...)`.
- [Cond.Some](https://pkg.go.dev/github.com/huandu/go-sqlbuilder#Cond.Some): `field op SOME (value1, value2, ...)`.
- [Cond.IsDistinctFrom](https://pkg.go.dev/github.com/huandu/go-sqlbuilder#Cond.IsDistinctFrom) `field IS DISTINCT FROM value`.
- [Cond.IsNotDistinctFrom](https://pkg.go.dev/github.com/huandu/go-sqlbuilder#Cond.IsNotDistinctFrom) `field IS NOT DISTINCT FROM value`.
- [Cond.Var](https://pkg.go.dev/github.com/huandu/go-sqlbuilder#Cond.Var): A placeholder for any value.

There are also some methods to combine conditions.

- [Cond.And](https://pkg.go.dev/github.com/huandu/go-sqlbuilder#Cond.And): Combine conditions with `AND` operator.
- [Cond.Or](https://pkg.go.dev/github.com/huandu/go-sqlbuilder#Cond.Or): Combine conditions with `OR` operator.

### Share `WHERE` clause among builders

Due to the importance of the `WHERE` statement in SQL, we often need to continuously append conditions and even share some common `WHERE` conditions among different builders. Therefore, we abstract the `WHERE` statement into a `WhereClause` struct, which can be used to create reusable `WHERE` conditions.

The following example illustrates how to transfer a `WHERE` clause from a `SelectBuilder` to an `UpdateBuilder`.

```go
// Build a SQL to select a user from database.
sb := Select("name", "level").From("users")
sb.Where(
    sb.Equal("id", 1234),
)
fmt.Println(sb)

ub := Update("users")
ub.Set(
    ub.Add("level", 10),
)

// Set the WHERE clause of UPDATE to the WHERE clause of SELECT.
ub.WhereClause = sb.WhereClause
fmt.Println(ub)

// Output:
// SELECT name, level FROM users WHERE id = ?
// UPDATE users SET level = level + ? WHERE id = ?
```

### Build `UPDATE ... FROM`

`UpdateBuilder.From` emits a `FROM` clause for PostgreSQL, SQLite, and SQLServer flavors (it is ignored by other flavors). When a CTE includes tables created with `CTETable`, those table names are emitted before any explicit `From(...)` tables.

```go
ub := PostgreSQL.NewUpdateBuilder()
ub.Update("users")
ub.Set(ub.Assign("name", "Huan Du"))
ub.From("people")
ub.Where("users.person_id = people.id")

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

// Output:
// UPDATE users SET name = $1 FROM people WHERE users.person_id = people.id
// [Huan Du]
```

Refer to the [WhereClause](https://pkg.go.dev/github.com/huandu/go-sqlbuilder#WhereClause) examples to learn its usage.

### Build `ORDER BY` clause

The `ORDER BY` clause is commonly used to sort query results. This package provides convenient methods to build `ORDER BY` clauses with proper ordering directions.

For scenarios where you need to order by multiple columns with different directions (ASC/DESC), use `OrderByAsc` and `OrderByDesc` methods. These methods can be chained to add multiple columns with their specific ordering.

```go
sb := sqlbuilder.NewSelectBuilder()
sb.Select("id", "name", "score").From("users")
sb.OrderByDesc("score").OrderByAsc("name")

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

// Output:
// SELECT id, name, score FROM users ORDER BY score DESC, name ASC
```

The older `OrderBy` method combined with `Asc`/`Desc` is still available but deprecated, as it only supports a single ordering direction for all columns. The new `OrderByAsc` and `OrderByDesc` methods provide more flexibility and clarity when working with multiple columns.

### Build SQL for different systems

SQL syntax and parameter placeholders can differ across systems. To address these variations, this package introduces a concept termed "flavor".

Currently, flavors such as `MySQL`, `PostgreSQL`, `SQLite`, `SQLServer`, `CQL`, `ClickHouse`, `Presto`, `Oracle` and `Informix` are supported. Should there be a demand for additional flavors, please submit an issue or a pull request.

By default, all builders utilize `DefaultFlavor` for SQL construction, with `MySQL` as the default setting.

For greater readibility, `PostgreSQL.NewSelectBuilder()` can be used to instantiate a `SelectBuilder` with the `PostgreSQL` flavor. All builders can be created in this way.

### Using `Struct` as a light weight ORM

`Struct` encapsulates type information and struct fields, serving as a builder factory. Utilizing `Struct` methods, one can generate `SELECT`/`INSERT`/`UPDATE`/`DELETE` builders that are pre-configured for use with the struct, thereby conserving time and mitigating the risk of typographical errors in column name entries.

One can define a struct type and employ field tags to guide `Struct` in generating the appropriate builders.

```go
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 call `WithTag` to include fields with tag or `WithoutTag` to exclude fields with tag.
    Field4     int64  `db:"field4" fieldtag:"foo"`       // If we use `s.WithTag("foo").Select("t")`, columnes of SELECT are "t.field3" and "t.field4".
    Field5     string `db:"field5" fieldas:"f5_alias"`   // Use "fieldas" in field tag to set a column alias (AS) used in SELECT.
    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.
    Expanded   Meta   `db:"expanded" fieldopt:"expand"`  // Force a nested struct to expand even when sqlbuilder.NoExpand is true.
    Payload    Meta   `db:"payload" fieldopt:"noexpand"` // Treat a nested struct as one column instead of expanding it.

    // 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"`

    // By default, the `SelectFrom("t")` will add the "t." to all names of fields matched tag.
    // We can add dot to field name to disable this behavior.
    FieldWithTableAlias string `db:"m.field"`
}
```

If a field is itself a struct and has a `db` tag, `Struct` treats that tag as a database alias and expands the nested fields by default. This is useful for `JOIN` projections built from reusable structs.

```go
type Post struct {
    ID   string `db:"id"`
    Text string `db:"text"`
}

type Comment struct {
    Body string `db:"body"`
}

type PostCommentJoined struct {
    Post    Post    `db:"post"`
    Comment Comment `db:"comment"`
}

joined := sqlbuilder.NewStruct(new(PostCommentJoined))
sql, _ := joined.SelectFrom("posts post").
    Join("comments comment", "post.id = comment.post_id").
    Build()

fmt.Println(sql)

// Output:
// SELECT post.id, post.text, comment.body FROM posts post JOIN comments comment ON post.id = comment.post_id
```

Set `sqlbuilder.NoExpand = true` before first use of a `Struct` to keep tagged nested structs as a single column by default. When the flag is enabled, add `fieldopt:"expand"` to opt a specific field back into expansion.

```go
type Payload struct {
    Key string `json:"key"`
}

type Row struct {
    Payload Payload `db:"payload"`
    Post    Post    `db:"post" fieldopt:"expand"`
}

sqlbuilder.NoExpand = true
st := sqlbuilder.NewStruct(new(Row))

fmt.Println(st.Columns())

// Output:
// [payload post.id post.text]
```

If a nested struct should stay as a single column, add `fieldopt:"noexpand"` to opt out of the automatic expansion. This is useful for JSON columns scanned into Go structs by the database driver.

```go
type Payload struct {
    Key string `json:"key"`
}

type Row struct {
    ID      string  `db:"id"`
    Payload Payload `db:"payload" fieldopt:"noexpand"`
}

st := sqlbuilder.NewStruct(new(Row)).For(sqlbuilder.PostgreSQL)
sql, _ := st.SelectFrom("events e").Build()

fmt.Println(sql)

// Output:
// SELECT e.id, e.payload FROM events e
```

For detailed instructions on utilizing `Struct`, refer to the [examples](https://pkg.go.dev/github.com/huandu/go-sqlbuilder#Struct).

Frequently Asked Questions