Custom SQL Query Builder in Go

Photo by Franki Chamaki on Unsplash

I was recently trying to build a custom query builder using go-lang.
what i wanted to make achieve is a generic query builder to which we can pass some attributes and it should build a query using that attributes.

so lets start by declaring a struct , i have something like this in mind.

type  QueryParameters struct {
   ColNames     []string
   TableName    string
   InsertValues  []interface{}
   Where   SomeMagicalDatatype
   GroupBy []string
   Having  SomeMagicalDatatype
   OrderBy []string
   Limit   int
}

if we make a struct like this we can easily create SQL queries using the fields.
While most of the fields can be easily evaluated using an Array or String ,Where and having clause are a little tricky to handle. Imaging the possible conditions you can have in where clause. hard-coding them is not a solution, we need a recursive solution here.If we magically have them evaluated we are good to go.

So lets try to tackle this problem…
A possible solution is to make a expression tree. Do you know what an expression tree is ? if not have a quick look at https://www.geeksforgeeks.org/expression-tree/

Lets build an expression tree for our Query Builder.

type Node interface {
   eval(*int, *[]interface{}) string
}
type And struct {
   left, right Node
}
type OR struct {
   left, right Node
}
type Equals struct {
   key   string
   value interface{}
}
type Gt struct {
   key   string
   value interface{}
}
type Lt struct {
   key   string
   value interface{}
}
type In struct {
   key   string
   value []interface{}
}

we have now declared the struct and interfaces we need now lets implement the eval method for each.

func (eq *Equals) eval(count *int, queryValues *[]interface{})string {
 if eq == nil {
   return ""
 }
  *(count)++
  *queryValues = append((*queryValues), eq.value)
  return fmt.Sprintf("%s = $%d", eq.key, *count)
}
func (gt *Gt) eval(count *int, queryValues *[]interface{}) string {
 if gt == nil {
  return ""
 }
  *(count)++
  *queryValues = append((*queryValues), gt.value)
  return fmt.Sprintf("%s > $%d", gt.key, *count)
}
func (lt *Lt) eval(count *int, queryValues *[]interface{}) string {
 if lt == nil {
   return ""
 }
  *(count)++
  *queryValues = append((*queryValues), lt.value)
  return fmt.Sprintf("%s < $%d", lt.key, *count)
}
func (in *In) eval(count *int, queryValues *[]interface{}) string {
 if in == nil {
  return ""
 }
 inQuery := in.key + " IN " + "( "
 for i := 0; i < len(in.value); i++ {
   *(count)++
   inQuery += "$" + strconv.Itoa(*count)
   if i != len(in.value)-1 {
    inQuery += ","
   }
 }
  inQuery += " )"
  *queryValues = append(*queryValues, in.value...)
  return inQuery
}

I have left less-than or equal-to and greater-than or equal-to from the code i want you to try and add it on your own.

notice how golang interfaces make it much easier to write.
now we can store complex queries in a data type and evaluate when needed,This could be our magical data type.

type CustomQuery struct {
   ColNames     []string
   TableName    string
   InsertValues []interface{}
   Where   Node
   GroupBy []string
   Having  Node
   OrderBy []string
   Limit   int
}

Now lets write a function to build our select query.

func (query *CustomQuery) BuildSelectQuery() (string, []interface{}) {
  customQuery := ""
  var count = 0
  var queryValues []interface{}
  customQuery += "SELECT " + strings.Join(query.ColNames, ",") + "     FROM " + query.TableName
 if query.Where != nil {
   customQuery += " WHERE " + query.Where.eval(&count, &queryValues)
 }
 if query.GroupBy != nil {
   customQuery += " GROUP BY " + strings.Join(query.GroupBy, ",")
 }
 if query.Having != nil {
   customQuery += " HAVING " + query.Having.eval(&count, &queryValues)
 }
 if query.OrderBy != nil {
   customQuery += " ORDER BY " + strings.Join(query.OrderBy, ",")
 }
 if query.Limit != 0 {
  customQuery += fmt.Sprintf("LIMIT %d", query.Limit)
 }
  return customQuery, queryValues
}

Similarly you can write query for insert,delete and update.

Now lets test if our query builder actually works. For a basic sql query like “select * from employee where id = 10 and dept = “DS-ALGO”

Our where would look something like this.

whereTree := &And{
   &Equals{"id",1},
   &Equals{"dept","DS-ALGO"},
}

And our struct would look like

cust_query := &CustomQuery{
   TableName: "employee",
   ColNames:  []string{"*"},
   Where: whereTree,
}

now build the query to check if its correct

query, queryValues := cust_query.BuildSelectQuery()fmt.Println(query, queryValues)

Conclusion

We have created a query builder which can which can build queries using given attributes. Notice how the join Clause is missing im planning to add it very soon. Let me know if u have any suggestions.