Go Database query builder
library
- Installation
- Selects, Ordering, Limit & Offset
- GroupBy / Having
- Where, AndWhere, OrWhere clauses
- WhereIn / WhereNotIn
- WhereNull / WhereNotNull
- Left / Right / Cross / Inner / Left Outer Joins
- Inserts
- Updates
- Delete
- Drop, Truncate, Rename
- Increment & Decrement
- Union / Union All
- Transaction mode
- Dump, Dd
- Check if table exists
- Check if columns exist in a table within schema
- Retrieving A Single Row / Column From A Table
- WhereExists / WhereNotExists
- Determining If Records Exist
- Aggregates
- Create table
- Add / Modify / Drop columns
- Chunking Results
- Pluck / PluckMap
go get -u github.com/arthurkushman/buildsqlxYou may not always want to select all columns from a database table. Using the select method, you can specify a custom select clause for the query:
package yourpackage
import (
"database/sql"
"github.com/arthurkushman/buildsqlx"
_ "github.com/lib/pq"
)
var db = buildsqlx.NewDb(buildsqlx.NewConnection("postgres", "user=postgres dbname=postgres password=postgres sslmode=disable"))
func main() {
qDb := db.Table("posts").Select("title", "body")
type DataStruct struct {
Foo string
Bar string
Baz *int64
}
dataStruct := DataStruct{}
var testStructs []DataStruct
// If you already have a query builder instance and you wish to add a column to its existing select clause, you may use the addSelect method:
err := qDb.AddSelect("points").GroupBy("topic").OrderBy("points", "DESC").Limit(15).Offset(5).EachToStruct(func(rows *sql.Rows) error {
err = db.Next(rows, &dataStruct)
if err != nil {
return err
}
testStructs = append(testStructs, dataStruct)
return nil
})
}err = db.Table("users").Select("name", "post", "user_id").InRandomOrder().ScanStruct(dataStruct)The GroupBy and Having methods may be used to group the query results.
The having method's signature is similar to that of the Where method:
err = db.table("users").GroupBy("account_id").Having("account_id", ">", 100).ScanStruct(dataStruct)You may use the Where method on a query builder instance to add where clauses to the query.
The most basic call to where requires three arguments.
The first argument is the name of the column.
The second argument is an operator, which can be any of the database's supported operators.
Finally, the third argument is the value to evaluate against the column.
err = db.Table("table1").Select("foo", "bar", "baz").Where("foo", "=", cmp).AndWhere("bar", "!=", "foo").OrWhere("baz", "=", 123)..ScanStruct(dataStruct)You may chain where constraints together as well as add or clauses to the query.
The OrWhere method accepts the same arguments as the Where method.
The WhereIn method verifies that a given column's value is contained within the given slice:
err = db.Table("table1").WhereIn("id", []int64{1, 2, 3}).OrWhereIn("name", []string{"John", "Paul"}).ScanStruct(dataStruct)The WhereNull method verifies that the value of the given column is NULL:
err = db.Table("posts").WhereNull("points").OrWhereNotNull("title")..ScanStruct(dataStruct)The query builder may also be used to write join statements.
To perform a basic "inner join", you may use the InnerJoin method on a query builder instance.
The first argument passed to the join method is the name of the table you need to join to,
while the remaining arguments specify the column constraints for the join.
You can even join to multiple tables in a single query:
err = db.Table("users").Select("name", "post", "user_id").LeftJoin("posts", "users.id", "=", "posts.user_id").EachToStruct(func(rows *sql.Rows) error {
err = db.Next(rows, &dataStruct)
if err != nil {
return err
}
testStructs = append(testStructs, dataStruct)
return nil
})The query builder also provides an Insert method for inserting records into the database table.
The Insert/InsertBatch methods accept a structure (or slice of structs) of column names and values:
// insert without getting id
err = db.Table("table1").Insert(DataStruct{
Foo: "foo foo foo",
Bar: "bar bar bar",
Baz: &baz,
})
// insert returning id
id, err := db.Table("table1").InsertGetId(DataStruct{
Foo: "foo foo foo",
Bar: "bar bar bar",
Baz: &baz,
})
// batch insert
err = db.Table("table1").InsertBatch([]DataStruct{
{Foo: "foo foo foo", Bar: "bar bar bar", Baz: &baz},
{Foo: "foo foo foo foo", Bar: "bar bar bar bar", Baz: &baz},
{Foo: "foo foo foo foo foo", Bar: "bar bar bar bar bar", Baz: &baz},
})In addition to inserting records into the database, the query builder can also update existing records using the update method. The update method, like the insert method, accepts a slice of column and value pairs containing the columns to be updated. You may constrain the update query using where clauses:
rows, err := db.Table("posts").Where("points", ">", 3).Update(DataStruct{
Title: "awesome",
})The query builder may also be used to delete records from the table via the delete method. You may constrain delete statements by adding where clauses before calling the delete method:
rows, err := db.Table("posts").Where("points", "=", 123).Delete()db.Drop("table_name")
db.DropIfExists("table_name")
db.Truncate("table_name")
db.Rename("table_name1", "table_name2")The query builder also provides convenient methods for incrementing or decrementing the value of a given column. This is a shortcut, providing a more expressive and terse interface compared to manually writing the update statement.
Both of these methods accept 2 arguments: the column to modify, a second argument to control the amount by which the column should be incremented or decremented:
db.Table("users").Increment("votes", 3)
db.Table("users").Decrement("votes", 1)The query builder also provides a quick way to "union" two queries together. For example, you may create an initial query and use the union method to union it with a second query:
union := db.Table("posts").Select("title", "likes").Union()
res, err := union.Table("users").Select("name", "points").ScanStruct(dataStruct)
// or if UNION ALL is of need
// union := db.Table("posts").Select("title", "likes").UnionAll()You can run arbitrary queries mixed with any code in transaction mode getting an error and as a result rollback if something went wrong or committed if everything is ok:
err := db.InTransaction(func () (interface{}, error) {
return db.Table("users").Select("name", "post", "user_id").ScanStruct(dataStruct)
})You may use the Dd or Dump methods while building a query to dump the query bindings and SQL. The dd method will display the debug information and then stop executing the request. The dump method will display the debug information but allow the request to keep executing:
// to print raw sql query to stdout
db.Table("table_name").Select("foo", "bar", "baz").Where("foo", "=", cmp).AndWhere("bar", "!=", "foo").Dump()
// or to print to stdout and exit a.k.a dump and die
db.Table("table_name").Select("foo", "bar", "baz").Where("foo", "=", cmp).AndWhere("bar", "!=", "foo").Dd()tblExists, err := db.HasTable("public", "posts")colsExists, err := db.HasColumns("public", "posts", "title", "user_id")If you just need to retrieve a single row from the database table, you may use the First func.
This method will return a single map[string]interface{}:
err = db.Table("posts").Select("title").OrderBy("created_at", "desc").First(dataStruct)
// usage ex: dataStruct.TitleIf you don't even need an entire row, you may extract a single value from a record using the Value method.
This method will return the value of the column directly:
err = db.Table("users").OrderBy("points", "desc").Value(dataStruct, "name")
// dataStruct.Name -> "Alex Shmidt"To retrieve a single row by its id column value, use the find method:
user, err := db.Table("users").Find(dataStruct, id)
// dataStruct.ID, dataStruct.Name, dataStruct.Email etcThe whereExists method allows you to write where exists SQL clauses. The whereExists method accepts a *DB argument, which will receive a query builder instance allowing you to define the query that should be placed inside the "exists" clause:
err = db.Table("users").Select("name").WhereExists(
db.Table("users").Select("name").Where("points", ">=", int64(12345)),
).First(dataStruct)Any query that is of need to build one can place inside WhereExists clause/func.
The whereBetween func verifies that a column's value is between two values:
err = db.Table(UsersTable).Select("name").WhereBetween("points", 1233, 12345).ScanStruct(&testStruct)The whereNotBetween func verifies that a column's value lies outside of two values:
err = db.Table(UsersTable).Select("name").WhereNotBetween("points", 123, 123456).ScanStruct(&testStruct)Instead of using the Count method to determine if any records exist that match your query's constraints,
you may use the exists and doesntExist methods:
exists, err := db.Table(UsersTable).Select("name").Where("points", ">=", int64(12345)).Exists()
// use an inverse DoesntExists() if neededThe query builder also provides a variety of aggregate methods such as Count, Max, Min, Avg, and Sum. You may call any of these methods after constructing your query:
cnt, err := db.Table(UsersTable).WHere("points", ">=", 1234).Count()
avg, err := db.Table(UsersTable).Avg("points")
mx, err := db.Table(UsersTable).Max("points")
mn, err := db.Table(UsersTable).Min("points")
sum, err := db.Table(UsersTable).Sum("points")To create a new database table, use the CreateTable method. The Schema method accepts two arguments. The first is the name of the table, while the second is an anonymous function/closure which receives a Table struct that may be used to define the new table:
res, err := db.Schema("big_tbl", func(table *Table) error {
table.Increments("id")
table.String("title", 128).Default("The quick brown fox jumped over the lazy dog").Unique("idx_ttl")
table.SmallInt("cnt").Default(1)
table.Integer("points").NotNull()
table.BigInt("likes").Index("idx_likes")
table.Text("comment").Comment("user comment").Collation("de_DE")
table.DblPrecision("likes_to_points").Default(0.0)
table.Char("tag", 10)
table.DateTime("created_at", true)
table.DateTimeTz("updated_at", true)
table.Decimal("tax", 2, 2)
table.TsVector("body")
table.TsQuery("body_query")
table.Jsonb("settings")
table.Point("pt")
table.Polygon("poly")
table.TableComment("big table for big data")
return nil
})
// to make a foreign key constraint from another table
_, err = db.Schema("tbl_to_ref", func (table *Table) error {
table.Increments("id")
table.Integer("big_tbl_id").ForeignKey("fk_idx_big_tbl_id", "big_tbl", "id").Concurrently().IfNotExists()
// to add index on existing column just repeat stmt + index e.g.:
table.Char("tag", 10).Index("idx_tag").Include("likes", "created_at")
table.Rename("settings", "options")
return nil
}) The Table structure in the Schema's 2nd argument may be used to update existing tables. Just the way you've been created it. The Change method allows you to modify some existing column types to a new type or modify the column's attributes.
res, err := db.Schema("tbl_name", func(table *Table) error {
table.String("title", 128).Change()
return nil
})Use DropColumn method to remove any column:
res, err := db.Schema("tbl_name", func(table *Table) error {
table.DropColumn("deleted_at").IfExists()
// To drop an index on the column
table.DropIndex("idx_title")
return nil
})If you need to work with thousands of database records, consider using the chunk method. This method retrieves a small chunk of the results at a time and feeds each chunk into a closure for processing.
var sumOfPoints int64
dataStruct := &DataStructUser{}
err = db.Table(UsersTable).Select("name", "points").Chunk(dataStruct, 100, func(users []any) bool {
for _, v := range users {
user := v.(DataStructUser)
// your code goes here e.g.:
sumOfPoints += user.Points
}
// or you can return false here to stop running chunks
return true
})If you would like to get values of a particular column(s) of a struct and place them into slice - use Pluck method:
dataStruct := &DataStructUser{}
res, err := db.Table(UsersTable).Pluck(dataStruct)
for k, v := range res {
val := v.(DataStructUser)
fmt.Println(val.Name) // f.e.: Alex Shmidt
}
// or use a PluckMap method to aggregate key/value pairs to a map
res, err := db.Table(UsersTable).PluckMap(dataStruct, "name", "points")
for k, m := range res {
for key, value := range m {
keyVal := key.(string)
valueVal := value.(DataStructUser)
// rest of the code ...
}
}PS Why use buildsqlx? Because it is simple and fast, yet versatile. The builder code-style has been inherited from greatest web-frameworks, so u can easily query anything from db.
Supporters gratitude:
