go-sqlbuilder

The sqlbuilder package provides a suite of utilities for constructing SQL strings in Go. It is designed to be compatible with the standard library sql.DB and sql.Stmt interfaces.

Key characteristics:

• Driver Agnostic: It does not depend on specific database drivers and does not manage database connections.
• Performance Focused: Designed to optimize SQL statement creation and minimize memory usage.
• Versatile: It does not execute the generated SQL itself, making it suitable for building custom ORMs, business-specific database interaction layers, or handling non-standard SQL in complex enterprise environments.

You can nest queries by passing one builder as an argument to another using BuilderAs. This works for both WHERE clauses (subqueries) and JOIN clauses.

// Nested Subquery in WHERE
sb.Where(sb.In("status", statusSb))

// Nested JOIN
sb.Join(
    sb.BuilderAs(nestedSb, "b"),
    "a.user_id = b.user_id",
)

You can reuse a WHERE clause by accessing the WhereClause field of a builder. This is useful for applying the same filters to different types of statements (e.g., applying a SELECT filter to an UPDATE statement).

sb := Select("name", "level").From("users")
sb.Where(sb.Equal("id", 1234))

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

// Transfer the WHERE clause from SELECT to UPDATE
ub.WhereClause = sb.WhereClause

sql, _ := ub.Build()
// UPDATE users SET level = level + ? WHERE id = ?

You can construct SQL statements using fluent method chaining. For simple queries, use the package-level functions. For queries requiring user input escaping, initialize a specific builder (e.g., NewSelectBuilder) to ensure arguments are properly parameterized.

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

// Parameterized construction (recommended for user input)
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()
// sql: SELECT id, name, COUNT(*) AS c FROM user WHERE status IN (?, ?, ?)
// args: [1 2 5]

Install the go-sqlbuilder package using the standard Go toolchain:

go get github.com/huandu/go-sqlbuilder

The WhereClause type is a builder used to construct WHERE conditions for SQL queries. It can be shared among multiple builders (e.g., a SELECT builder and an UPDATE builder), but it is not thread-safe.

Key capabilities:

• Add Expressions: Use AddWhereExpr to add AND conditions. It accepts an *Args object and one or more string expressions.
• Merge Clauses: Use AddWhereClause to append all conditions from one WhereClause into another.
• Flavor Management: You can set the SQL dialect (e.g., MySQL, PostgreSQL) using SetFlavor. If the WhereClause is part of a larger builder, the builder's flavor will typically take precedence during the build process.
• Cloning: Use CopyWhereClause to create a shallow copy of the clauses to avoid mutating the original instance.

When using Args.Compile or Args.CompileWithFlavor, you can use a special syntax within the format string to represent arguments:

• $?: Refers to successive arguments passed in the initialValue slice. It behaves similarly to %v in fmt.Sprintf.
• $0, $1, ..., $n: Refers to the $n$-th argument added via Args.Add. The next $? will use the argument at index $n+1$.
• ${name}: Refers to a named argument created using sql.NamedArg or internal named argument mechanisms.
• $$: Represents a literal $ character in the SQL string.

Example of successive arguments:

args := &sqlbuilder.Args{}
// If initialValue is []interface{}{1, "name"}
// "SELECT * FROM t WHERE id = $? AND name = $?" becomes "SELECT * FROM t WHERE id = 1 AND name = 'name'" (with placeholders)
query, values := args.Compile("SELECT * FROM t WHERE id = $? AND name = $?", 1, "name")

Builders that support WHERE clauses have an anonymous Cond field. You can use this field to build complex conditions using methods like And, Or, and various comparison operators. This allows for clean, programmatic construction of logic.

sb := sqlbuilder.NewSelectBuilder()
sb.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()
// SELECT id FROM user WHERE status IN (?, ?, ?) AND (name = ? OR email LIKE ?)

For PostgreSQL, SQLite, and SQLServer, UpdateBuilder.From can be used to emit a FROM clause. This is ignored by other database flavors.

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()
// UPDATE users SET name = $1 FROM people WHERE users.person_id = people.id

For drivers that do not support prepared statements (e.g., some Redis or Elasticsearch drivers), you can interpolate arguments directly into the SQL string using Flavor.Interpolate(sql, args).

Security Warning: Interpolation is less secure than using prepared statements provided by the database driver.