How to use databases with Golang

There are a lot of different and approaches to use databases within Golang. I don’t like many of these approaches, especially ORM’s. Many times they are generating inefficient queries, wrong locks, retrieving too much data and you don’t have a clue what is going on. After several implementations, I settled with this pattern. The pattern uses the sql abstraction library sqlx of jmoiron and allows complete customization and control.

The project structure will look as follows:

• db/db.go => contains the interface definitions and embeds default sqlx structs
• model/ => contains all the database models
• utils/ => contains generic functions

The db.go file contains all magic and interfaces and is essentially a wrapper around default sqlx functions. The struct tries to find a custom implementation within the model itself, and if it can’t find any it or returns an error or returns a default implementation. The struct defines default behaviour like (limited) select, count, select, insert, update and delete.

db.go

package db

import ("errors""fmt""reflect"

"github.com/jmoiron/sqlx""github.com/op/go-logging")

var log = logging.MustGetLogger("db")

type Query string

type Queryx struct {Query QueryParams []interface{}}

type DB struct {*sqlx.DB}

type Tx struct {*sqlx.Tx}

var (ErrNoGetterFound = errors.New("No getter found")ErrNoDeleterFound = errors.New("No deleter found")ErrNoSelecterFound = errors.New("No getter found")ErrNoUpdaterFound = errors.New("No updater found")ErrNoInserterFound = errors.New("No inserter found"))

func Limit(offset, count int) selectOption {return &limitOption{offset, count}}

type limitOption struct {offset intcount int}

func (o *limitOption) Wrap(query string, params []interface{}) (string, []interface{}) {query = fmt.Sprintf("SELECT a.* FROM (%s) a LIMIT ?, ?", query)params = append(params, o.offset)params = append(params, o.count)return query, params}

type selectOption interface {Wrap(string, []interface{}) (string, []interface{})}

func (tx *Tx) Selectx(o interface{}, qx Queryx, options ...selectOption) error {q := string(qx.Query)params := qx.Params

log.Debug(q)

for _, option := range options {q, params = option.Wrap(q, params)}

if u, ok := o.(Selecter); ok {return u.Select(tx.Tx, Query(q), params...)}

stmt, err := tx.Preparex(q)if err != nil {return err}

return stmt.Select(o, params...)}

func (tx *Tx) Countx(qx Queryx) (int, error) {stmt, err := tx.Preparex(fmt.Sprintf("SELECT COUNT(*) FROM (%s) q", string(qx.Query)))if err != nil {return 0, err}

count := 0err = stmt.Get(&count, qx.Params...)return count, err}

func (tx *Tx) Getx(o interface{}, qx Queryx) error {if u, ok := o.(Getter); ok {return u.Get(tx.Tx, qx.Query, qx.Params...)}

stmt, err := tx.Preparex(string(qx.Query))if err != nil {return err}

return stmt.Get(o, qx.Params...)}

func (tx *Tx) Get(o interface{}, query Query, params ...interface{}) error {if u, ok := o.(Getter); ok {return u.Get(tx.Tx, query, params...)}

stmt, err := tx.Preparex(string(query))if err != nil {return err}

return stmt.Get(o, params...)}

func (tx *Tx) Update(o interface{}) error {if u, ok := o.(Updater); ok {return u.Update(tx.Tx)}

log.Debug("No updater found for object: %s", reflect.TypeOf(o))return ErrNoUpdaterFound}

func (tx *Tx) Delete(o interface{}) error {if u, ok := o.(Deleter); ok {return u.Delete(tx.Tx)}

log.Debug("No deleter found for object: %s", reflect.TypeOf(o))return ErrNoDeleterFound}

func (tx *Tx) Insert(o interface{}) error {if u, ok := o.(Inserter); ok {err := u.Insert(tx.Tx)if err != nil {log.Error(err.Error())}return err}

log.Debug("No inserter found for object: %s", reflect.TypeOf(o))return ErrNoInserterFound}

func (db *DB) Begin() *Tx {tx := db.MustBegin()return &Tx{tx}}

type Updater interface {Update(*sqlx.Tx) error}

type Inserter interface {Insert(*sqlx.Tx) error}

type Selecter interface {Select(*sqlx.Tx, Query, ...interface{}) error}

type Getter interface {Get(*sqlx.Tx, Query, ...interface{}) error}

type Deleter interface {Delete(*sqlx.Tx) error}

This is an example implementation of the person model.

package model

import ("fmt""time"

"github.com/jmoiron/sqlx"

db "./db")

type Gender string

var (GenderMale Gender = "male"GenderFemale Gender = "female")

func (u *Gender) Scan(value interface{}) error {if value == nil {return nil}

b := value.([]byte)*u = Gender(b)return nil}

func (u Gender) Value() (driver.Value, error) {return string(u), nil}

type Person struct {PersonID utils.UUID `db:"person_id"`

FirstName string `db:"first_name"`LastName string `db:"last_name"`

Active Bool `db:"active"`Gender Gender `db:"gender"`

ModifiedDate time.Time `db:"modified_date"`}

var (queryPersons db.Query = "SELECT person_id, first_name, last_name, gender, active, modified_date FROM persons"queryPersonByID db.Query = "SELECT person_id, first_name, last_name, gender, active, modified_date FROM persons WHERE person_id=:person_id"queryPersonInsert db.Query = "INSERT INTO persons (person_id, first_name, last_name, gender, active, modified_date) VALUES (:person_id, :first_name, :last_name, :gender, :active, :modified_date)"queryPersonUpdate db.Query = "UPDATE persons SET first_name=:first_name, last_name=:last_name, gender=:gender, modified_date=:modified_date, active=:active WHERE person_id=:person_id")

func QueryPersons(offset, count int) db.Queryx {return db.Queryx{Query: queryPersons,Params: []interface{}{},}}

func QueryPersonByID(personID utils.UUID) db.Queryx {return db.Queryx{Query: queryPersonByID,Params: []interface{}{personID,},}}

func NewPerson() *Person {return &Person{PersonID: utils.NewUUID(), ModifiedDate: time.Now() }}

func (s *Person) Insert(tx *sqlx.Tx) error {_, err := tx.NamedExec(string(queryPersonInsert), s)return err}

func (s *Person) Update(tx *sqlx.Tx) error {s.ModifiedDate = time.Now()

_, err := tx.NamedExec(string(queryPersonUpdate), s)return err}

func (s *Person) Delete(tx *sqlx.Tx) error {s.Active = falsereturn s.Update(tx)}

Now with both the db and model defined, you can use the pattern as follows:

tx := db.Begin()

var err errordefer func() {if err != nil {tx.Rollback()return}

tx.Commit()}()

// retrieve single personperson := model.Person{}if err := tx.Getx(&person, model.QueryPersonByID(personID)); err != nil {return err}

person.Lastname = "Doe"

// update the personif err := tx.Update(&person); err != nil {return err}

index := 0count := 50

// retrieve multiple paged personspersons := []model.Person{}if err := ctx.tx.Selectx(&persons, model.QueryPersons(user), db.Limit(index, count)); err == sql.ErrNoRows {} else if err == nil {} else {return err}

// count number of resultstotal, err := ctx.tx.Countx(model.QueryPersons())if err != nil {return err}

Defer will check if an error has occured and if it will rollback the transaction. Otherwise it will just commit the transaction. We don’t have to take care of updating the last modification date and such within the implementation, this is being taken care of in the model. We can define different behavoir for delete as well, by setting the active flag. Additional selectOptions can be implemented, like the limitOption.

This pattern has the following advantages:

• completely in charge of query definition, it is easy to define joins, subqueries or specific optimizations
• using the New function you can initialize with sane defaults
• each operation (insert, update or delete) can have a custom implementation with additional checks or behaviour
• support for default operations linke count and limit
• all operations are strongly typed, saving a lot of errors
• all operations are organized in one package
• all queries are near each other, allowing easy verification of the queries (in the case of adding fields or filters)
• each query is wrapped within a transaction
• enums (like gender) can be used
• tests can be implemented easily in the db class
• with some modification queries can be implemented differently depending on the database being used
• the queries itself could be generated partly using go generate

One of the disadvantages is that you need to write quite some code, but in return everything is structured, testable and it will give much less errors.

Hacker Noon is how hackers start their afternoons. We’re a part of the @AMIfamily. We are now accepting submissions and happy to discuss advertising &sponsorship opportunities.

To learn more, read our about page, like/message us on Facebook, or simply, tweet/DM @HackerNoon.

If you enjoyed this story, we recommend reading our latest tech stories and trending tech stories. Until next time, don’t take the realities of the world for granted!