SQL builder for Go

• Install
• Usage
  • Basic usage
  • Pre-defined SQL builders
  • Build WHERE clause
  • Share WHERE clause among builders
  • Build ORDER BY clause
  • Build SQL for different systems
  • Using Struct as a light weight ORM
  • Nested SQL
  • Nested JOIN
  • Use sql.Named in a builder
  • Argument modifiers
  • Freestyle builder
  • Using special syntax to build SQL
• 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:

go get github.com/huandu/go-sqlbuilder

Usage

Basic usage

We can rapidly construct SQL statements using 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 common scenarios, it is necessary to escape all user inputs. To achieve this, initialize a builder at the outset.

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: Factory for creating builders based on struct definitions.
• CreateTableBuilder: Builder for CREATE TABLE.
• SelectBuilder: Builder for SELECT.
• InsertBuilder: Builder for INSERT.
• UpdateBuilder: Builder for UPDATE.
• DeleteBuilder: Builder for DELETE.
• UnionBuilder: Builder for UNION and UNION ALL.
• CTEBuilder: Builder for Common Table Expression (CTE), e.g. WITH name (col1, col2) AS (SELECT ...).
• Buildf: Freestyle builder employing fmt.Sprintf-like syntax.
• Build: Advanced freestyle builder utilizing special syntax as defined in Args#Compile.
• 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.

// 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 enables the recursive conversion of an array-like variable into a flat slice of []any. For example, invoking Flatten([]interface{"foo", []int{2, 3}}) yields []any{"foo", 2, 3}. This method is compatible with builder methods such as In, NotIn, Values, etc., facilitating the conversion of a typed array into []any or the merging of inputs.
• 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 []any{1, 2, 3}.
• 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.

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.

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/Cond.E/Cond.EQ: field = value.
• Cond.NotEqual/Cond.NE/Cond.NEQ: field <> value.
• Cond.GreaterThan/Cond.G/Cond.GT: field > value.
• Cond.GreaterEqualThan/Cond.GE/Cond.GTE: field >= value.
• Cond.LessThan/Cond.L/Cond.LT: field < value.
• Cond.LessEqualThan/Cond.LE/Cond.LTE: field <= value.
• Cond.In: field IN (value1, value2, ...).
• Cond.NotIn: field NOT IN (value1, value2, ...).
• Cond.Like: field LIKE value.
• Cond.ILike: field ILIKE value.
• Cond.NotLike: field NOT LIKE value.
• Cond.NotILike: field NOT ILIKE value.
• Cond.Between: field BETWEEN lower AND upper.
• Cond.NotBetween: field NOT BETWEEN lower AND upper.
• Cond.IsNull: field IS NULL.
• Cond.IsNotNull: field IS NOT NULL.
• Cond.Exists: EXISTS (subquery).
• Cond.NotExists: NOT EXISTS (subquery).
• Cond.Not: NOT expr.
• Cond.Any: field op ANY (value1, value2, ...).
• Cond.All: field op ALL (value1, value2, ...).
• Cond.Some: field op SOME (value1, value2, ...).
• Cond.IsDistinctFrom field IS DISTINCT FROM value.
• Cond.IsNotDistinctFrom field IS NOT DISTINCT FROM value.
• Cond.Var: A placeholder for any value.

There are also some methods to combine conditions.

• Cond.And: Combine conditions with AND operator.
• 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.

// 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.

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 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.

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.

Nested SQL

Creating nested SQL is straightforward: simply use a builder as an argument for nesting.

Here is an illustrative example.

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]

Nested JOIN

In addition to nested subqueries, you can also use BuilderAs to create nested JOINs. This is particularly useful when you need to join with a filtered or transformed dataset.

Here is an example showing how to join a table with a nested subquery:

sb := sqlbuilder.NewSelectBuilder()
nestedSb := sqlbuilder.NewSelectBuilder()

// Build the nested subquery
nestedSb.Select("b.id", "b.user_id")
nestedSb.From("users2 AS b")
nestedSb.Where(nestedSb.GreaterThan("b.age", 20))

// Build the main query with nested join
sb.Select("a.id", "a.user_id")
sb.From("users AS a")
sb.Join(
    sb.BuilderAs(nestedSb, "b"),
    "a.user_id = b.user_id",
)

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

// Output:
// SELECT a.id, a.user_id FROM users AS a JOIN (SELECT b.id, b.user_id FROM users2 AS b WHERE b.age > ?) AS b ON a.user_id = b.user_id
// [20]

Use sql.Named in a builder

The sql.Named function, as defined in the database/sql package, facilitates the creation of named arguments within SQL statements. This feature is essential for scenarios where an argument needs to be reused multiple times within a single SQL statement. Incorporating named arguments into a builder is straightforward: treat them as regular arguments.

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

Several argument modifiers are available:

• List(arg) encapsulates a series of arguments. Given arg as a slice or array, for instance, a slice containing three integers, it compiles to ?, ?, ? and is presented in the final arguments as three individual integers. This serves as a convenience tool, utilizable within IN expressions or within the VALUES clause of an INSERT INTO statement.
• TupleNames(names) and Tuple(values) facilitate the representation of tuple syntax in SQL. For usage examples, refer to Tuple.
• Named(name, arg) designates a named argument. Functionality is limited to Build or BuildNamed, where it defines a named placeholder using the syntax ${name}.
• Raw(expr) designates expr as a plain string within SQL, as opposed to an argument. During the construction of a builder, raw expressions are directly embedded into the SQL string, omitting the need for ? placeholders.

Freestyle builder

A builder essentially serves as a means to log arguments. For constructing lengthy SQL statements that incorporate numerous special syntax elements (e.g., special comments intended for a database proxy), Buildf can be employed to format the SQL string using a syntax akin to fmt.Sprintf.

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

The sqlbuilder package incorporates special syntax for representing uncompiled SQL internally. To leverage this syntax for developing customized tools, the Build function can be utilized to compile it with the necessary arguments.

The format string employs special syntax for representing arguments:

• $? references successive arguments supplied in the function call, functioning similarly to %v in fmt.Sprintf.
• $0, $1, ..., $n reference the nth argument provided in the call; subsequent $? will then refer to arguments n+1 onwards.
• ${name} references a named argument defined by Named using the specified name.
• $$ represents a literal "$" character.

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]

For scenarios where only the ${name} syntax is required to reference named arguments, utilize BuildNamed. This function disables all special syntax except for ${name} and $$.

License

This package is licensed under the MIT license. For more information, refer to the LICENSE file.