Builder

Package builder is a lightweight and fast SQL builder for Go and XORM.

Make sure you have installed Go 1.8+ and then:

  1. go get xorm.io/builder

Insert

  1. sql, args, err := builder.Insert(Eq{"c": 1, "d": 2}).Into("table1").ToSQL()
  3. // INSERT INTO table1 SELECT * FROM table2
  4. sql, err := builder.Insert().Into("table1").Select().From("table2").ToBoundSQL()
  6. // INSERT INTO table1 (a, b) SELECT b, c FROM table2
  7. sql, err = builder.Insert("a, b").Into("table1").Select("b, c").From("table2").ToBoundSQL()

Select

  1. // Simple Query
  2. sql, args, err := Select("c, d").From("table1").Where(Eq{"a": 1}).ToSQL()
  3. // With join
  4. sql, args, err = Select("c, d").From("table1").LeftJoin("table2", Eq{"table1.id": 1}.And(Lt{"table2.id": 3})).
  5.         RightJoin("table3", "table2.id = table3.tid").Where(Eq{"a": 1}).ToSQL()
  6. // From sub query
  7. sql, args, err := Select("sub.id").From(Select("c").From("table1").Where(Eq{"a": 1}), "sub").Where(Eq{"b": 1}).ToSQL()
  8. // From union query
  9. sql, args, err = Select("sub.id").From(
  10.     Select("id").From("table1").Where(Eq{"a": 1}).Union("all", Select("id").From("table1").Where(Eq{"a": 2})),"sub").
  11.     Where(Eq{"b": 1}).ToSQL()
  12. // With order by
  13. sql, args, err = Select("a", "b", "c").From("table1").Where(Eq{"f1": "v1", "f2": "v2"}).
  14.         OrderBy("a ASC").ToSQL()
  15. // With limit.
  16. // Be careful! You should set up specific dialect for builder before performing a query with LIMIT
  17. sql, args, err = Dialect(MYSQL).Select("a", "b", "c").From("table1").OrderBy("a ASC").
  18.         Limit(5, 10).ToSQL()

Update

  1. sql, args, err := Update(Eq{"a": 2}).From("table1").Where(Eq{"a": 1}).ToSQL()

Delete

  1. sql, args, err := Delete(Eq{"a": 1}).From("table1").ToSQL()

Union

  1. sql, args, err := Select("*").From("a").Where(Eq{"status": "1"}).
  2.         Union("all", Select("*").From("a").Where(Eq{"status": "2"})).
  3.         Union("distinct", Select("*").From("a").Where(Eq{"status": "3"})).
  4.         Union("", Select("*").From("a").Where(Eq{"status": "4"})).
  5.         ToSQL()

Conditions

  • Eq is a redefine of a map, you can give one or more conditions to Eq
  1. import . "xorm.io/builder"
  3. sql, args, _ := ToSQL(Eq{"a":1})
  4. // a=? [1]
  5. sql, args, _ := ToSQL(Eq{"b":"c"}.And(Eq{"c": 0}))
  6. // b=? AND c=? ["c", 0]
  7. sql, args, _ := ToSQL(Eq{"b":"c", "c":0})
  8. // b=? AND c=? ["c", 0]
  9. sql, args, _ := ToSQL(Eq{"b":"c"}.Or(Eq{"b":"d"}))
  10. // b=? OR b=? ["c", "d"]
  11. sql, args, _ := ToSQL(Eq{"b": []string{"c", "d"}})
  12. // b IN (?,?) ["c", "d"]
  13. sql, args, _ := ToSQL(Eq{"b": 1, "c":[]int{2, 3}})
  14. // b=? AND c IN (?,?) [1, 2, 3]
  • Neq is the same to Eq
  1. import . "xorm.io/builder"
  3. sql, args, _ := ToSQL(Neq{"a":1})
  4. // a<>? [1]
  5. sql, args, _ := ToSQL(Neq{"b":"c"}.And(Neq{"c": 0}))
  6. // b<>? AND c<>? ["c", 0]
  7. sql, args, _ := ToSQL(Neq{"b":"c", "c":0})
  8. // b<>? AND c<>? ["c", 0]
  9. sql, args, _ := ToSQL(Neq{"b":"c"}.Or(Neq{"b":"d"}))
  10. // b<>? OR b<>? ["c", "d"]
  11. sql, args, _ := ToSQL(Neq{"b": []string{"c", "d"}})
  12. // b NOT IN (?,?) ["c", "d"]
  13. sql, args, _ := ToSQL(Neq{"b": 1, "c":[]int{2, 3}})
  14. // b<>? AND c NOT IN (?,?) [1, 2, 3]
  • Gt, Gte, Lt, Lte
  1. import . "xorm.io/builder"
  3. sql, args, _ := ToSQL(Gt{"a", 1}.And(Gte{"b", 2}))
  4. // a>? AND b>=? [1, 2]
  5. sql, args, _ := ToSQL(Lt{"a", 1}.Or(Lte{"b", 2}))
  6. // a<? OR b<=? [1, 2]
  • Like
  1. import . "xorm.io/builder"
  3. sql, args, _ := ToSQL(Like{"a", "c"})
  4. // a LIKE ? [%c%]
  • Expr you can customerize your sql with Expr
  1. import . "xorm.io/builder"
  3. sql, args, _ := ToSQL(Expr("a = ? ", 1))
  4. // a = ? [1]
  5. sql, args, _ := ToSQL(Eq{"a": Expr("select id from table where c = ?", 1)})
  6. // a=(select id from table where c = ?) [1]
  • In and NotIn
  1. import . "xorm.io/builder"
  3. sql, args, _ := ToSQL(In("a", 1, 2, 3))
  4. // a IN (?,?,?) [1,2,3]
  5. sql, args, _ := ToSQL(In("a", []int{1, 2, 3}))
  6. // a IN (?,?,?) [1,2,3]
  7. sql, args, _ := ToSQL(NotIn("a", Expr("select id from b where c = ?", 1))))
  8. // a NOT IN (select id from b where c = ?) [1]
  • Exists and NotExists
  1. import . "xorm.io/builder"
  3. sql, args, _ := ToSQL(Exists(Select("a").From("table")))
  4. // EXISTS (SELECT a FROM table)
  5. sql, args, _ := ToSQL(NotExists(Select("a").From("table")))
  6. // NOT EXISTS (SELECT a FROM table)
  • IsNull and NotNull
  1. import . "xorm.io/builder"
  3. sql, args, _ := ToSQL(IsNull{"a"})
  4. // a IS NULL []
  5. sql, args, _ := ToSQL(NotNull{"b"})
  6.     // b IS NOT NULL []
  • And(conds ...Cond), And can connect one or more conditions via And
  1. import . "xorm.io/builder"
  3. sql, args, _ := ToSQL(And(Eq{"a":1}, Like{"b", "c"}, Neq{"d", 2}))
  4. // a=? AND b LIKE ? AND d<>? [1, %c%, 2]
  • Or(conds ...Cond), Or can connect one or more conditions via Or
  1. import . "xorm.io/builder"
  3. sql, args, _ := ToSQL(Or(Eq{"a":1}, Like{"b", "c"}, Neq{"d", 2}))
  4. // a=? OR b LIKE ? OR d<>? [1, %c%, 2]
  5. sql, args, _ := ToSQL(Or(Eq{"a":1}, And(Like{"b", "c"}, Neq{"d", 2})))
  6. // a=? OR (b LIKE ? AND d<>?) [1, %c%, 2]
  • Between
  1. import . "xorm.io/builder"
  3. sql, args, _ := ToSQL(Between{"a", 1, 2})
  4. // a BETWEEN 1 AND 2
  • Define yourself conditions

Since Cond is an interface.

  1. type Cond interface {
  2.     WriteTo(Writer) error
  3.     And(...Cond) Cond
  4.     Or(...Cond) Cond
  5.     IsValid() bool
  6. }

You can define yourself conditions and compose with other Cond.