Repository files navigation

• Install
• Usage
  • Basic usage
  • Pre-defined SQL builders
  • BuildWHERE clause
  • ShareWHERE clause among builders
  • BuildORDER BY clause
  • Build SQL for different systems
  • UsingStruct as a light weight ORM
  • Nested SQL
  • NestedJOIN
  • Usesql.Named in a builder
  • Argument modifiers
  • Freestyle builder
  • Clone builders
  • Using special syntax to build SQL
  • Interpolateargs in thesql
• License

Thesqlbuilder 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 librarysql.DB andsql.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 this package by executing the following command:

go get github.com/huandu/go-sqlbuilder

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]

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

• Struct: Factory for creating builders based on struct definitions.
• CreateTableBuilder: Builder forCREATE TABLE.
• SelectBuilder: Builder forSELECT.
• InsertBuilder: Builder forINSERT.
• UpdateBuilder: Builder forUPDATE.
• DeleteBuilder: Builder forDELETE.
• UnionBuilder: Builder forUNION andUNION ALL.
• CTEBuilder: Builder for Common Table Expression (CTE), e.g.WITH name (col1, col2) AS (SELECT ...).
• Buildf: Freestyle builder employingfmt.Sprintf-like syntax.
• Build: Advanced freestyle builder utilizing special syntax as defined inArgs#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[]interface{}. For example, invokingFlatten([]interface{"foo", []int{2, 3}}) yields[]interface{}{"foo", 2, 3}. This method is compatible with builder methods such asIn,NotIn,Values, etc., facilitating the conversion of a typed array into[]interface{} or the merging of inputs.
• List operates similarly toFlatten, 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 SQLmy_func(?, ?, ?) with arguments[]interface{}{1, 2, 3}.
• Raw designates a string as a "raw string" within arguments. For instance,Buildf("SELECT %v", Raw("NOW()")).Build() results in SQLSELECT NOW().

For detailed instructions on utilizing these builders, consult theexamples provided on GoDoc.

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

To simplify the construction ofWHERE clauses, a utility type namedCond is provided for condition building. All builders that supportWHERE clauses possess an anonymousCond field, enabling the invocation ofCond 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.IsDistinctFromfield IS DISTINCT FROM value.
• Cond.IsNotDistinctFromfield IS NOT DISTINCT FROM value.
• Cond.Var: A placeholder for any value.

There are also some methods to combine conditions.

• Cond.And: Combine conditions withAND operator.
• Cond.Or: Combine conditions withOR operator.

Due to the importance of theWHERE statement in SQL, we often need to continuously append conditions and even share some commonWHERE conditions among different builders. Therefore, we abstract theWHERE statement into aWhereClause struct, which can be used to create reusableWHERE conditions.

The following example illustrates how to transfer aWHERE clause from aSelectBuilder to anUpdateBuilder.

// 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.WhereClausefmt.Println(ub)// Output:// SELECT name, level FROM users WHERE id = ?// UPDATE users SET level = level + ? WHERE id = ?

Refer to theWhereClause examples to learn its usage.

TheORDER BY clause is commonly used to sort query results. This package provides convenient methods to buildORDER BY clauses with proper ordering directions.

For scenarios where you need to order by multiple columns with different directions (ASC/DESC), useOrderByAsc andOrderByDesc 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 olderOrderBy method combined withAsc/Desc is still available but deprecated, as it only supports a single ordering direction for all columns. The newOrderByAsc andOrderByDesc methods provide more flexibility and clarity when working with multiple columns.

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

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

By default, all builders utilizeDefaultFlavor for SQL construction, withMySQL as the default setting.

For greater readibility,PostgreSQL.NewSelectBuilder() can be used to instantiate aSelectBuilder with thePostgreSQL flavor. All builders can be created in this way.

Struct encapsulates type information and struct fields, serving as a builder factory. UtilizingStruct methods, one can generateSELECT/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 guideStruct in generating the appropriate builders.

typeATablestruct {Field1string// If a field doesn't has a tag, use "Field1" as column name in SQL.Field2int`db:"field2"`// Use "db" in field tag to set column name used in SQL.Field3int64`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.Field4int64`db:"field4" fieldtag:"foo"`// If we use `s.WithTag("foo").Select("t")`, columnes of SELECT are "t.field3" and "t.field4".Field5string`db:"field5" fieldas:"f5_alias"`// Use "fieldas" in field tag to set a column alias (AS) used in SELECT.Ignoredint32`db:"-"`// If we set field name as "-", Struct will ignore it.unexportedint// Unexported field is not visible to Struct.Quotedstring`db:"quoted" fieldopt:"withquote"`// Add quote to the field using back quote or double quote. See `Flavor#Quote`.Emptyuint`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`.Taggedstring`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.FieldWithTableAliasstring`db:"m.field"`}

For detailed instructions on utilizingStruct, refer to theexamples.

Furthermore,Struct can be employed as a zero-configuration ORM. Unlike most ORM implementations that necessitate preliminary configurations for database connectivity,Struct operates without any configuration, functioning seamlessly with any SQL driver compatible withdatabase/sql.Struct does not invoke anydatabase/sql APIs; it solely generates the appropriate SQL statements with arguments forDB#Query/DB#Exec or an array of struct field addresses forRows#Scan/Row#Scan.

The following example demonstrates the use ofStruct as an ORM. It should be relatively straightforward for developers well-versed indatabase/sql APIs.

typeUserstruct {IDint64`db:"id" fieldtag:"pk"`Namestring`db:"name"`Statusint`db:"status"`}// A global variable for creating SQL builders.// All methods of userStruct are thread-safe.varuserStruct=NewStruct(new(User))funcExampleStruct() {// Prepare SELECT query.//     SELECT user.id, user.name, user.status FROM user WHERE id = 1234sb:=userStruct.SelectFrom("user")sb.Where(sb.Equal("id",1234))// Execute the query and scan the results into the user struct.sql,args:=sb.Build()rows,_:=db.Query(sql,args...)deferrows.Close()// Scan row data and set value to user.// Assuming the following data is retrieved:////     |  id  |  name  | status |//     |------|--------|--------|//     | 1234 | huandu | 1      |varuserUserrows.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 numerous production environments, table column names adhere to the snake_case convention, e.g.,user_id. Conversely, struct fields in Go are typically in CamelCase to maintain public accessibility and satisfygolint. Employing thedb tag for each struct field can be redundant. To streamline this, a field mapper function can be utilized to establish a consistent rule for mapping struct field names to database column names.

TheDefaultFieldMapper serves as a global field mapper function, tasked with the conversion of field names to a desired style. By default, it is set tonil, effectively performing no action. Recognizing that the majority of table column names follow the snake_case convention, one can assignDefaultFieldMapper tosqlbuilder.SnakeCaseMapper. For instances that deviate from this norm, a custom mapper can be assigned to aStruct via theWithFieldMapper method.

Here are important considerations regarding the field mapper:

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

Refer to thefield mapper function sample for an illustrative example.

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]

In addition to nested subqueries, you can also useBuilderAs 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 subquerynestedSb.Select("b.id","b.user_id")nestedSb.From("users2 AS b")nestedSb.Where(nestedSb.GreaterThan("b.age",20))// Build the main query with nested joinsb.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]

Thesql.Named function, as defined in thedatabase/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}]

Several argument modifiers are available:

• List(arg) encapsulates a series of arguments. Givenarg 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 withinIN expressions or within theVALUES clause of anINSERT INTO statement.
• TupleNames(names) andTuple(values) facilitate the representation of tuple syntax in SQL. For usage examples, refer toTuple.
• Named(name, arg) designates a named argument. Functionality is limited toBuild orBuildNamed, where it defines a named placeholder using the syntax${name}.
• Raw(expr) designatesexpr 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.

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

TheClone methods make any builder reusable as a template. You can create a partially initialized builder once (even as a global), then callClone() to get an independent copy to customize per request. This avoids repeated setup while keeping shared templates immutable and safe for concurrent use.

Supported builders withClone:

• CreateTableBuilder
• CTEBuilder
• CTEQueryBuilder
• DeleteBuilder
• InsertBuilder
• SelectBuilder
• UnionBuilder
• UpdateBuilder

Example: define a global SELECT template and clone it per call

package yourpkgimport"github.com/huandu/go-sqlbuilder"// Global template — safe to reuse by cloning.varbaseUserSelect=sqlbuilder.NewSelectBuilder().Select("id","name","email").From("users").Where("deleted_at IS NULL")funcListActiveUsers(limit,offsetint) (string, []interface{}) {sb:=baseUserSelect.Clone()// independent copysb.OrderByAsc("id")sb.Limit(limit).Offset(offset)returnsb.Build()}funcGetActiveUserByID(idint64) (string, []interface{}) {sb:=baseUserSelect.Clone()// start from the same templatesb.Where(sb.Equal("id",id))sb.Limit(1)returnsb.Build()}

The same template pattern applies to other builders. For example, keep a baseUpdateBuilder with the table and commonSET clauses, or a baseCTEBuilder defining reusable CTEs, thenClone() and add query-specificWHERE/ORDER BY/LIMIT/RETURNING as needed.

Thesqlbuilder package incorporates special syntax for representing uncompiled SQL internally. To leverage this syntax for developing customized tools, theBuild 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 infmt.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 byNamed using the specifiedname.
• $$ 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, utilizeBuildNamed. This function disables all special syntax except for${name} and$$.

Certain SQL-like drivers, such as those for Redis or Elasticsearch, do not implement theStmtExecContext#ExecContext method. These drivers encounter issues whenlen(args) > 0. The sole workaround is to interpolateargs directly into thesql string and then execute the resulting query with the driver.

The interpolation feature in this package is designed to provide a "basically sufficient" level of functionality, rather than a capability that rivals the comprehensive features of various SQL drivers and DBMS systems.

Security warning: While efforts are made to escape special characters in interpolation methods, this approach remains less secure than usingStmt as implemented by SQL drivers.

This feature draws inspiration from the interpolation capabilities found in thegithub.com/go-sql-driver/mysql package.

Here is an example specifically 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 an example for PostgreSQL, noting that dollar quoting 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>

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