Data Access Code Separation with Go

golang-250.png

One concept common in medium-to-large software development projects is the separation of data-access code into layers. This has the benefits of separating concerns, easing testing, and simplifying dependencies.

While the Go programming language has several ORMs in various stages of development to assist with this concept, most pull you further away from the database driver implementation, making it harder to leverage features specific to the underlying database (e.g. array and JSON column types in Postgres).

With that in mind, in this post we’ll discuss a simple strategy for separating data access in a Go project. The desired characteristics are:

  1. Models are plain Go structs – know nothing of database
  2. Data Layer allows DB access to Models – knows nothing of app
  3. Application makes use of Data Layer and Models for data access – knows nothing of database

Models

 

Our example will be the tried-and-true Todo application. The models package in this case has a single struct definition: TodoItem. Note that we do import database/sql for the sql.NullInt64 type. This is required in order to handle nullable database fields. However, this import does not contain any database implementation. That is left to database drivers in Go.

models/todoitem.go

package models

import "database/sql"

type TodoItem struct {
    Id    int64
    Title string
    Due   sql.NullInt64
    Done  bool
}

Data Layer

The data package in this example contains two files. The first is a simple sql.go file with the SQL statements we’ll be using to access our Postgres DB.

data/sql.go

package data

const (
    // Entries table
    sqlCreateTodoItemsTable = ` CREATE TABLE IF NOT EXISTS todo_items ( id bigserial PRIMARY KEY, title text NOT NULL, 	due bigint, 	done boolean ) WITH ( 	OIDS=FALSE )`
    sqlSelectTodoItemCount = ` SELECT COUNT(id) FROM todo_items`
    sqlSelectAllTodoItems = ` SELECT id 	, title 	, due 	, done FROM todo_items`
    sqlInsertTodoItem = ` INSERT INTO todo_items (title, due, done) VALUES ($1, $2, $3)`
    sqlUpdateTodoItem = ` UPDATE todo_items SET title = $2 , due = $3 , done = $4 WHERE id = $1`
    sqlDeleteTodoItem = ` DELETE FROM todo_items WHERE id = $1` )

The access.go file is where things get a bit more interesting. We first declare two new structs that use the concept of embedding to provide access to our plain TodoItem model. This allows us to add the two scan functions below which provide support for reading data into the TodoItem model from the DB.

data/access.go
package data

import (
    "database/sql"
    _ "github.com/lib/pq"
    "log"
    "github.com/idmworks/todo-go/models"
)

// DBTodoItem embeds TodoItem and adds DB-specific methods
// https://golang.org/doc/effective_go.html#embedding
type DBTodoItem struct {
    *models.TodoItem
}

func (todoItem *DBTodoItem) scan(rows *sql.Rows) {
    err := rows.Scan(
        &todoItem.Id,
        &todoItem.Title,
        &todoItem.Due,
        &todoItem.Done) 
    if err != nil {
        log.Fatalln(err)
    }
}

// DBTodoItems embeds DBTodoItem and adds DB-specific methods
type DBTodoItems []*DBTodoItem

func (todoItems *DBTodoItems) scan(rows *sql.Rows) error {
    for rows.Next() {
        todoItem := &DBTodoItem{&models.TodoItem{}} 
        todoItem.scan(rows)
        *todoItems = append(*todoItems, todoItem)
    }
    return rows.Err()
}

We then declare a TodoDB struct that will provide the application access to the database data. Note that the db field is private (denoted by lower-case in Go) meaning there is no direct access to the DB-driver from the application. All access is via the functions declared on TodoDB below.

// TodoDB provides methods for accessing DB data
type TodoDB struct {
    db *sql.DB
}

// OpenDb opens a Postgres database with the specified dbname and dbuser
func (todoDb *TodoDB) OpenDb(dbname string, dbuser string) error {
    db, err := sql.Open("postgres", "user="+dbuser+" dbname="+dbname+" sslmode=disable")
    todoDb.db = db
    return err
}

// CreateTablesIfNotExists creates any Postgres tables that do not exist
func (todoDb *TodoDB) CreateTablesIfNotExists() error { 
    _, err := todoDb.db.Exec(sqlCreateTodoItemsTable)
    return err
}

// SelectAllTodoItems returns all rows from the DB as DBTodoItems
func (todoDb *TodoDB) SelectAllTodoItems() (items *DBTodoItems, err error) {
    todoItems := &DBTodoItems{}
    rows, err := todoDb.db.Query(sqlSelectAllTodoItems)
    if err != nil {
        return nil, err
    }
    todoItems.scan(rows)
    return todoItems, nil
}

// InsertTodoItem inserts a single DBTodoItem into the DB
func (todoDb *TodoDB) InsertTodoItem(item *DBTodoItem) error {
    _, err := todoDb.db.Exec(
        sqlInsertTodoItem,
        item.Title,
        item.Due,
        item.Done)
    return err
}

// UpdateTodoItem updates a single DBTodoItem within the DB
func (todoDb *TodoDB) UpdateTodoItem(item *DBTodoItem) error {
    _, err := todoDb.db.Exec(
        sqlUpdateTodoItem,
        item.Id,
        item.Title,
        item.Due,
        item.Done)
    return err
}

// UpdateTodoItem updates a single DBTodoItem within the DB
func (todoDb *TodoDB) DeleteTodoItem(item *DBTodoItem) error {
    _, err := todoDb.db.Exec(
        sqlDeleteTodoItem,
        item.Id) 
    return err
}
Application

The main application Go file ties the above packages together. There’s not much to highlight here. We declare our data-access TodoDB struct and use that to open the Postgres database connection. The functions on the TodoDB struct are then used to provide strongly-typed access to the underlying database data.

todoapp.go
package main

// no sql or db driver imports
import (
    "github.com/idmworks/todo-go/data"
    "github.com/idmworks/todo-go/models"
    "log"
)

const (
    dbname = "todoapp"
    dbuser = "todoapp"
)

// note error handling is omitted for brevity - handle err returns!
func main() {
    // declare data-access struct
    db := &data.TodoDB{}

    // open PG database
    db.OpenDb(dbname, dbuser)

    // create DB schema (if needed)
    db.CreateTablesIfNotExists()

    // fetch DB items as a typed slice of DBTodoItem
    items, _ := db.SelectAllTodoItems()
    log.Println(len(*items), "items in the database")

    // declare a new DBTodoItem (with embedded TodoItem)
    item := &data.DBTodoItem{&models.TodoItem{}}
    item.Title = "test"

    // insert into the DB
    db.InsertTodoItem(item)
    log.Printf("%s (done: %t) inserted into the database", item.Title, item.Done)

    // demonstrate successful insert
    items, _ = db.SelectAllTodoItems()
    log.Println(len(*items), "items in the database")
    item = (*items)[0]
    log.Printf("%s (done: %t) found in database", item.Title, item.Done)

    // flag item as Done
    item.Done = true

    // update in DB
    db.UpdateTodoItem(item)
    log.Printf("%s (done: %t) updated in the database", item.Title, item.Done)

    // demonstrate successful update
    items, _ = db.SelectAllTodoItems()
    log.Println(len(*items), "items in the database")
    item = (*items)[0]
    log.Printf("%s (done: %t) found in database", item.Title, item.Done)

    // delete item
    db.DeleteTodoItem(item)

    // demonstrate successful deletion 	items, _ = db.SelectAllTodoItems()
    log.Println(len(*items), "items in the database")
}
Conclusion

With these pieces in place we can now run the resulting Go application:

Output
2015/03/03 11:52:28 0 items in the database 
2015/03/03 11:52:28 test (done: false) inserted into the database 
2015/03/03 11:52:28 1 items in the database 
2015/03/03 11:52:28 test (done: false) found in database 
2015/03/03 11:52:28 test (done: true) updated in the database 
2015/03/03 11:52:28 1 items in the database 
2015/03/03 11:52:28 test (done: true) found in database 
2015/03/03 11:52:28 0 items in the database

As you can see the results look good and our goals were accomplished. Neither the main application or models have direct knowledge of the database driver or SQL interaction.