buildsqlx

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

Installation

go get -u github.com/arthurkushman/buildsqlx

Selects, Ordering, Limit & Offset

You 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
	})
}

InRandomOrder

err = db.Table("users").Select("name", "post", "user_id").InRandomOrder().ScanStruct(dataStruct)

GroupBy / Having

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)

Where, AndWhere, OrWhere clauses

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.

WhereIn / WhereNotIn

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)

WhereNull / WhereNotNull

The WhereNull method verifies that the value of the given column is NULL:

err = db.Table("posts").WhereNull("points").OrWhereNotNull("title")..ScanStruct(dataStruct)

Left / Right / Cross / Inner / Left Outer Joins

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
})

Inserts

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},
})

Updates

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",
})

Delete

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()

Drop, Truncate, Rename

db.Drop("table_name")

db.DropIfExists("table_name")

db.Truncate("table_name")

db.Rename("table_name1", "table_name2")

Increment & Decrement

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)

Union / Union All

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()

Transaction mode

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)
})

Dump, Dd

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()

Check if table exists

tblExists, err := db.HasTable("public", "posts")

Check if columns exist in a table within schema

colsExists, err := db.HasColumns("public", "posts", "title", "user_id")

Retrieving A Single Row /