github.com/qjebbs/go-sqls

Package sqlf focuses only on building SQL queries by combining fragments. Low reusability and scalability are the main challenges we face when writing SQL, the package is designed to solve these problems.

Fragment

Unlike any other sql builder or ORMs, Fragment is the only concept you need to learn.

Fragment is usually a part of a SQL query, for example, combining main fragment and any number of condition fragments, we can get a complete query.

query, args, _ := (&sqlf.Fragment{
	Raw: `SELECT * FROM foo WHERE #join('#fragment', ' AND ')`,
	Fragments: []*sqlf.Fragment{
		sqlf.Fa(`bar IN (#join('#argDollar', ', '))`, 1, 2, 3),
		sqlf.Fa(`baz = $1`, true),
	},
}).Build()
fmt.Println(query)
fmt.Println(args)
// Output:
// SELECT * FROM foo WHERE bar IN ($1, $2, $3) AND baz = $4
// [1 2 3 true]

Explanation:

• We pay attention only to the references inside a fragment, e.g., use $1 to refer Fragment.Args[0], or ? to refer Fragment.Args in order.
• #join, #fragment, etc., are preprocessing functions, which will be explained later.

See example_test.go for more examples.

Preprocessing Functions

name	description	example
c, column	Fragment.Columns at index	#c1
t, table	Fragment.Tables at index	#t1
fragment	Fragment.Fragments at index	#fragment1
builder	Fragment.Builders at index	#builder1
argDollar	Fragment.Args at index with style $	#join('#argDollar', ', ')
argQuestion	Fragment.Args at index with style ?	#join('#argQuestion', ', ')
join	Join the template with separator	#join('#fragment', ' AND ')
	Join from index 3 to end	#join('#argDollar', ',', 3)
	Join from index 3 to 6	#join('#argDollar', ',', 3, 6)

Note:

• #c1 is equivalent to #c(1), which is a special syntax to call preprocessing functions when an integer (usually an index) is the only argument.
• Expressions in the #join template are functions, not function calls.

You can register custom preprocessing functions to the build context.

ctx := sqlf.NewContext()
ids := sqlf.NewArgsProperty(1, 2, 3)
_ = ctx.Funcs(sqlf.FuncMap{
	"_id": func(i int) (string, error) {
		return ids.Build(ctx, i, syntax.Dollar)
	},
})
b := &sqlf.Fragment{
	Raw: "#join('#fragment', '\nUNION\n')",
	Fragments: []*sqlf.Fragment{
		{Raw: "SELECT id, 'foo' typ, count FROM foo WHERE id IN (#join('#_id', ', '))"},
		{Raw: "SELECT id, 'bar' typ, count FROM bar WHERE id IN (#join('#_id', ', '))"},
	},
}
query, _ := b.BuildContext(ctx)
fmt.Println(query)
fmt.Println(ctx.Args())
// SELECT id, 'foo' typ, count FROM foo WHERE id IN ($1, $2, $3)
// UNION
// SELECT id, 'bar' typ, count FROM bar WHERE id IN ($1, $2, $3)
// [1 2 3]

QueryBuilder

*sqlb.QueryBuilder is a high-level abstraction of SQL queries for building complex queries, with *sqlf.Fragment as its underlying foundation.

See sqlb/example_test.go for examples.