A sql query builder for golang
genarate SQL and bind params quikly
import
import (
"github.com/wazsmwazsm/QueryBuilder/builder"
)new sql builder
sb := builder.NewSQLBuilder()Tips: sql builder is not reusable, one sql builder only for one SQL
package main
import (
"github.com/wazsmwazsm/QueryBuilder/builder"
"log"
)
func main() {
// new sql builder
sb := builder.NewSQLBuilder()
// build sql
sql, err := sb.Table("`test`").
Select("`name`", "`age`", "`school`").
Where("`name`", "=", "jack").
Where("`age`", ">=", 18).
OrWhere("`name`", "like", "%admin%").
GetQuerySQL()
if err != nil {
log.Fatal(err)
}
// get bind params
params := sb.GetQueryParams()
log.Println(sql) // SELECT `name`,`age`,`school` FROM `test` WHERE `name` = ? AND `age` >= ? OR `name` like ?
log.Println(params) // [jack 18 %admin%]
// now you can use the sql and params to database/sql Query()\Exec() function
// do someting...
}tips: in postgresql or sqlite, you should change escape quota ` to "
package main
import (
"github.com/wazsmwazsm/QueryBuilder/builder"
"log"
)
func main() {
sb := builder.NewSQLBuilder()
sql, err := sb.Table("`test`").
Select("`name`", "`age`", "`school`").
WhereIn("`id`", 1, 2, 3).
OrWhereNotIn("`uid`", 2, 4).
GetQuerySQL()
if err != nil {
log.Fatal(err)
}
params := sb.GetQueryParams()
log.Println(sql) // SELECT `name`,`age`,`school` FROM `test` WHERE `id` IN (?,?,?) OR `uid` NOT IN (?,?)
log.Println(params) // [1 2 3 2 4]
}sometimes you you have more needs for where conditions, you can use raw sql with WhereRaw()\OrWhereRaw() method
package main
import (
"github.com/wazsmwazsm/QueryBuilder/builder"
"log"
)
func main() {
sb := builder.NewSQLBuilder()
sql, err := sb.Table("`test`").
Select("`name`", "`age`", "`school`").
WhereRaw("`title` = ?", "hello").
Where("`name`", "=", "jack").
OrWhereRaw("(`age` = ? OR `age` = ?) AND `class` = ?", 22, 25, "2-3").
GetQuerySQL()
if err != nil {
log.Fatal(err)
}
params := sb.GetQueryParams()
log.Println(sql) // SELECT `name`,`age`,`school` FROM `test` WHERE `title` = ? AND `name` = ? OR (`age` = ? OR `age` = ?) AND `class` = ?
log.Println(params) // [hello jack 22 25]
}also, you can use aggregate func with raw sql
package main
import (
"github.com/wazsmwazsm/QueryBuilder/builder"
"log"
)
func main() {
sb := builder.NewSQLBuilder()
sql, err := sb.Table("`test`").
Select("count(`age`)", "`username`").
GetQuerySQL()
if err != nil {
log.Fatal(err)
}
params := sb.GetQueryParams()
log.Println(sql) // SELECT count(`age`), `username` FROM `test`
log.Println(params) // []
}package main
import (
"github.com/wazsmwazsm/QueryBuilder/builder"
"log"
)
func main() {
sb := builder.NewSQLBuilder()
sql, err := sb.Table("`test`").
Select("`school`", "`class`", "COUNT(*) as `ct`").
GroupBy("`school`", "`class`").
Having("`ct`", ">", "2").
// Having("COUNT(*)", ">", "2"). // same as above
GetQuerySQL()
if err != nil {
log.Fatal(err)
}
params := sb.GetQueryParams()
log.Println(sql) // SELECT `school`,`class`,COUNT(*) as `ct` FROM `test` GROUP BY `school`,`class` HAVING `ct` > ?
log.Println(params) // [2]
}such as where, having can also use raw sql with HavingRaw() method
package main
import (
"github.com/wazsmwazsm/QueryBuilder/builder"
"log"
)
func main() {
sb := builder.NewSQLBuilder()
sql, err := sb.Table("test").
SelectRaw("`school`, `class`, COUNT(*)").
GroupBy("school", "class").
HavingRaw("COUNT(*) > 2").
GetQuerySQL()
if err != nil {
log.Fatal(err)
}
params := sb.GetQueryParams()
log.Println(sql) // SELECT `school`,`class`,COUNT(*) FROM `test` GROUP BY `school`,`class` HAVING COUNT(*) > 2
log.Println(params) // []
}package main
import (
"github.com/wazsmwazsm/QueryBuilder/builder"
"log"
)
func main() {
sb := builder.NewSQLBuilder()
sql, err := sb.Table("`test`").
Select("`name`", "`age`", "`school`").
Where("`name`", "=", "jack").
Where("`age`", ">=", 18).
OrderBy("DESC", "`age`", "`class`").
Limit(1, 10).
GetQuerySQL()
if err != nil {
log.Fatal(err)
}
params := sb.GetQueryParams()
log.Println(sql) // SELECT `name`,`age`,`school` FROM `test` WHERE `name` = ? AND `age` >= ? ORDER BY `age`,`class` DESC LIMIT ? OFFSET ?
log.Println(params) // [jack 18 10 1]
}join only provide raw sql mode now
simple join
package main
import (
"github.com/wazsmwazsm/QueryBuilder/builder"
"log"
)
func main() {
sb := builder.NewSQLBuilder()
sql, err := sb.Table("`test`").
Select("`test`.`name`", "`test`.`age`", "`test2`.`teacher`").
JoinRaw("LEFT JOIN `test2` ON `test`.`class` = `test2`.`class`").
Where("`test`.`age`", ">=", 18).
GetQuerySQL()
if err != nil {
log.Fatal(err)
}
params := sb.GetQueryParams()
log.Println(sql) // SELECT `test`.`name`,`test`.`age`,`test2`.`teacher` FROM `test` LEFT JOIN `test2` ON `test`.`class` = `test2`.`class` WHERE `test`.`age` >= ?
log.Println(params) // [18]
}join with params
package main
import (
"github.com/wazsmwazsm/QueryBuilder/builder"
"log"
)
func main() {
sb := builder.NewSQLBuilder()
sql, err := sb.Table("`test`").
Select("`test`.`name`", "`test`.`age`", "`test2`.`teacher`").
JoinRaw("LEFT JOIN `test2` ON `test`.`class` = `test2`.`class` AND `test`.`num` = ?", 2333).
Where("`test`.`age`", ">=", 18).
GetQuerySQL()
if err != nil {
log.Fatal(err)
}
params := sb.GetQueryParams()
log.Println(sql) // SELECT `test`.`name`,`test`.`age`,`test2`.`teacher` FROM `test` LEFT JOIN `test2` ON `test`.`class` = `test2`.`class` AND `test`.`num` = ? WHERE `test`.`age` >= ?
log.Println(params) // [2333 18]
}more
package main
import (
"github.com/wazsmwazsm/QueryBuilder/builder"
"log"
)
func main() {
sb := builder.NewSQLBuilder()
sql, err := sb.Table("`test` as t1").
Select("`t1`.`name`", "`t1`.`age`", "`t2`.`teacher`", "`t3`.`address`").
JoinRaw("LEFT JOIN `test2` as `t2` ON `t1`.`class` = `t2`.`class`").
JoinRaw("INNER JOIN `test3` as t3 ON `t1`.`school` = `t3`.`school`").
Where("`t1`.`age`", ">=", 18).
GroupBy("`t1`.`age`").
Having("COUNT(`t1`.`age`)", ">", 2).
OrderBy("DESC", "`t1`.`age`").
Limit(1, 10).
GetQuerySQL()
if err != nil {
log.Fatal(err)
}
params := sb.GetQueryParams()
log.Println(sql) // SELECT `t1`.`name`,`t1`.`age`,`t2`.`teacher`,`t3`.`address` FROM `test` as t1 LEFT JOIN `test2` as `t2` ON `t1`.`class` = `t2`.`class` INNER JOIN `test3` as t3 ON `t1`.`school` = `t3`.`school` WHERE `t1`.`age` >= ? GROUP BY `t1`.`age` HAVING COUNT(`t1`.`age`) > ? ORDER BY `t1`.`age` DESC LIMIT ? OFFSET ?
log.Println(params) // [18 2 10 1]
}package main
import (
"github.com/wazsmwazsm/QueryBuilder/builder"
"log"
)
func main() {
sb := builder.NewSQLBuilder()
sql, err := sb.Table("`test`").
Insert([]string{"`name`", "`age`"}, "jack", 18).
GetInsertSQL()
if err != nil {
log.Fatal(err)
}
params := sb.GetInsertParams()
log.Println(sql) // INSERT INTO `test` (`name`,`age`) VALUES (?,?)
log.Println(params) // [jack 18]
}package main
import (
"github.com/wazsmwazsm/QueryBuilder/builder"
"log"
)
func main() {
sb := builder.NewSQLBuilder()
sql, err := sb.Table("`test`").
Update([]string{"`name`", "`age`"}, "jack", 18).
Where("`id`", "=", 11).
GetUpdateSQL()
if err != nil {
log.Fatal(err)
}
params := sb.GetUpdateParams()
log.Println(sql) // UPDATE `test` SET `name` = ?,`age` = ? WHERE `id` = ?
log.Println(params) // [jack 18 11]
}package main
import (
"github.com/wazsmwazsm/QueryBuilder/builder"
"log"
)
func main() {
sb := builder.NewSQLBuilder()
sql, err := sb.Table("`test`").
Where("`id`", "=", 11).
GetDeleteSQL()
if err != nil {
log.Fatal(err)
}
params := sb.GetDeleteParams()
log.Println(sql) // DELETE FROM `test` WHERE `id` = ?
log.Println(params) // [11]
}The QueryBuilder is open-sourced software licensed under the MIT license.