Creating Query Builder in Golang

Implementing laravel style query builder in golang using sql package#

In this post we will try to create a laravel style query builder. If you are from php background and have used laravel then you must know that Laravel’s query builder gives some predefined function for creating sql queries which is very easy to use if you are not good in sql.

There may be already few packages in golang for query builder but i wanted to show you how actually you can make your own or just to get idea how this works, the underline structure.

In laravel we do like below.

$user = User::select("column1","column2")->where("col", "val")->orderBy()
->get();

This will fetch data from users table based on where condition.

Why it is better than raw query ?#

Suppose you have created a user table and written a lots of raw query in many places of your project and then your boss told you to change the table name, what will you do , you have to change all query at all places.

But if you are using some sort of Model class for your table then you just have to change that table name in just one file , in User model class.

Now come to the actual topic lets start with our own package.

first do the the following steps.

  • create a new directory in GOPATH directory.
  • run go mod init github.com/example.com/pkg
  • run go mod tidy
  • create a directory goqrm, you can choose name you like
  • create a file model.go in that goqrm directory

this model.go file will not contain main() function because we are creating a package.

first declare a package

package goqrm

Now create a interface modeler which will contains helper method for query builder

type Modeler interface {
	Select(...string) *Model
	Get() []map[string]interface{}
	Where(column, operator, value string) *Model
	OrderBy(column, order string) *Model
	Limit(num int) *Model
}

Now create a Connect() function which will connect to your database and return a conn and also set to global connection variable.

By default Connect() function will connect to mysql database but you can pass a database type parameter for connecting to another database.

Connect() method will look like this



func Connect(connString string) (*sql.DB, error) {

	db, err := sql.Open("mysql", connString)

	Conn = db
	return db, err
}

Now create a contructor method which will initialize our Model struct we are going to use reflect package to find the Table property value because we are defining the table name in seperate model class.

func NewModel(modeler Modeler) *Model {
	val := reflect.Indirect(reflect.ValueOf(modeler))
	tableName := val.FieldByName("Table")
	return &Model{
		db:             Conn,
		table:          tableName.String(),
		whereCondition: []string{"Where 1 = 1 "},
	}
}

Model struct will contain all necessary variable to hold the parts of sql query like where condition, select columns, orderby and so on. later will use all those parts and construct a sql query.

Model struct will look like this

type Model struct {
	db             *sql.DB
	table          string
	columns        []string
	whereCondition []string
	orderBy        string
	limit          string
}

Now you can implements interface functions Like Select(), Where(), Get() Complete model.go file will look like this

model.go

package goqrm
import (
	"database/sql"
	"fmt"
	"reflect"
	"strings"

	_ "github.com/go-sql-driver/mysql"
)

var Conn *sql.DB

type Modeler interface {
	Select(...string) *Model
	Get() []map[string]interface{}
	Where(column, operator, value string) *Model
	OrderBy(column, order string) *Model
	Limit(num int) *Model
}

func Connect(connString string) (*sql.DB, error) {

	db, err := sql.Open("mysql", connString)

	Conn = db
	return db, err
}

func NewModel(modeler Modeler) *Model {
	val := reflect.Indirect(reflect.ValueOf(modeler))
	tableName := val.FieldByName("Table")
	return &Model{
		db:             Conn,
		table:          tableName.String(),
		whereCondition: []string{"Where 1 = 1 "},
	}
}

type Model struct {
	db             *sql.DB
	table          string
	columns        []string
	whereCondition []string
	orderBy        string
	limit          string
}

func (m *Model) Select(str ...string) *Model {
	m.columns = str
	return m
}

func (m *Model) Where(column, operator, value string) *Model {
	m.whereCondition = append(m.whereCondition, fmt.Sprint("and ", column, operator, "'", value, "'"))
	return m
}
func (m *Model) OrderBy(column, order string) *Model {
	m.orderBy = fmt.Sprint(" Order by ", column, order)
	return m
}
func (m *Model) Limit(num int) *Model {
	m.limit = fmt.Sprint(" limit ", num)
	return m
}
func (m *Model) ToSql() string {
	columnsString := " * "
	if len(m.columns) > 0 {
		columnsString = strings.Join(m.columns, " , ")
	}

	sql := fmt.Sprint("Select ", columnsString, " from ", m.table, " ", strings.Join(m.whereCondition, " "), " ", m.orderBy, " ", m.limit)

	return sql
}
func (m *Model) Get() []map[string]interface{} {

	rows, err := m.db.Query(m.ToSql())

	if err != nil {
		fmt.Println("Query Error")
		panic(err)
	}

	defer rows.Close()

	columns, err := rows.Columns()
	if err != nil {
		panic(err)
	}

	// below code is taken from stackoverflow post
	//https://stackoverflow.com/questions/53435455/handling-dynamic-queries-cant-scan-into-struct
	var allMaps []map[string]interface{}
	for rows.Next() {
		values := make([]string, len(columns))
		pointers := make([]interface{}, len(columns))
		for i, _ := range values {
			pointers[i] = &values[i]
		}
		rows.Scan(pointers...)
		resultMap := make(map[string]interface{})
		for i, val := range values {
			resultMap[columns[i]] = val
		}
		allMaps = append(allMaps, resultMap)
	}
	return allMaps
}

I have already created a package which is available on github you can use like this first get the package

go get github.com/djomnitrix/goqrm

now create a user.go model file

package main

import "github.com/djomnitrix/goqrm"

type Users struct {
	*goqrm.Model
	Table string
}

func UsersModel() *Users {
	return &Users{Table: "users"}
}

and in main.go file you can query like below.

package main

import (
	"fmt"

	"github.com/djomnitrix/goqrm"
)

func main() {
	connString := fmt.Sprintf("%s:%s@tcp(%s:%s)/%s",
		"db_username", "db_password",
		"localhost", "3306", "Demo")

	_, err := goqrm.Connect(connString)
	if err != nil {
		panic(err)
	}

	userModel := goqrm.NewModel(UsersModel()).Where("id", "=", "2").Get()

	fmt.Println(userModel)

}

I will add remaining methods for advanced query building, if you want to contribute you can fork the project.

Thanks for reading.

comments powered by Disqus