Revel, GORP, and MySQL

Building a classic 3-tier web application controller in Golang.

Richard Clayton

Stumbling my way through the great wastelands of enterprise software development.

This is a compilation of things I've had to piece together from blog posts, example projects, and experimentation. I hope it saves you a bunch of time.

Goal:

Build a simple Revel controller that performs CRUD operations against a MySQL database.

Caveats:

Use RESTful Paths.
Use JSON request bodies and responses.
Use Revel Validation.

This setup should reflect a backend that you would use with a Single Page Application framework like AngularJS.

Synopsis:

I'm building a simple auction management system for the wife. One of the model items I have to deal with is a BidItem, which represents something you can purchase during the bidding process. I'll demonstrate how to build the full controller and database bindings for this one model item.

We will implement the controller with the following steps:

Define REST Routes.
Define Model.
Implement Validation Logic.
Set up the GorpController.
Register Configuration.
Set up the Database.
Extend the GorpController.
Handle JSON Request Body (for Creation and Updates).
Implement Controller CRUD functionality.

What I will not go into is security and proper response handling. Also, I'm going to be as succinct as possible so I don't waste your time.

1. Define REST Routes.

In <app-root>/conf/routes, add the following lines:

GET     /item/:id   BidItemCtrl.Get
POST    /item       BidItemCtrl.Add
PUT     /item/:id   BidItemCtrl.Update
DELETE  /item/:id   BidItemCtrl.Delete
GET     /items      BidItemCtrl.List

The first two columns are obvious (HTTP Verb and Route). The :id is an example of placeholder syntax. Revel will provides three ways to get this value, including using the value as a function argument. The BidItemCtrl.<Func> refers to the controller and method that will handle the route.

2. Define Model.

I've created a folder called models in <app-root>/app. This is where I define my models. This is my model in file <app-root>/app/models/bid-item.go:

package models

type BidItem struct {
    Id              int64   `db:"id" json:"id"`
    Name            string  `db:"name" json:"name"`
    Category        string  `db:"category" json:"category"`
    EstimatedValue  float32 `db:"est_value" json:"est_value"`
    StartBid        float32 `db:"start_bid" json:"start_bid"`
    BidIncrement    float32 `db:"bid_incr" json:"bid_incr"`
    InstantBuy      float32 `db:"inst_buy" json:"inst_buy"`
}

Note the meta information after each property on the structure. The db:"<name>" is the desired column-name in the database and the json:"<name>" is the JSON property name used in serialization/deserialization. These values are not mandatory, but if you do not specify them, both GORP and the encoding/json package will use the property name. So you will be stuck with columns and JSON properties in Pascal casing.

3. Implement Validation Logic.

For model validation, we could implement our own custom logic, but Revel already has a pretty good validation facility (so we'll just use that). Of course, this means we are going to trade off having a clean model (unaware of other frameworks) for development expedience.

import (
    "github.com/revel/revel"
    "regexp"
)

// ...

func (b *BidItem) Validate(v *revel.Validation) {

    v.Check(b.Name,
        revel.ValidRequired(),
        revel.ValidMaxSize(25))

    v.Check(b.Category,
        revel.ValidRequired(),
        revel.ValidMatch(
            regexp.MustCompile(
                "^(travel|leasure|sports|entertainment)$")))

    v.Check(b.EstimatedValue,
        revel.ValidRequired())

    v.Check(b.StartBid,
        revel.ValidRequired())

    v.Check(b.BidIncrement,
        revel.ValidRequired())
}

This implements some of the validation logic by constructing the ruleset for model. It's certainly not complete and leaves much to be desired.

4. Set up the `GorpController`.

If you would like transactions for your database, but don't want to have to manually set them up in your controller, I would recommend following this step. Keep in mind, all the code I show you after this will utilize this functionality.

If you go spelunking in the Revel sample projects, you will find this little gem in the Bookings project. This is the GorpController, which is a simple extension to the revel.Controller that defines some boiler plate around wrapping controller methods in database transactions. I've moved out the database creation code into a separate file (which we will come back to in step 6), so the file should now look like this:

package controllers

import (
    "github.com/coopernurse/gorp"
    "database/sql"
    "github.com/revel/revel"
)

var (
    Dbm *gorp.DbMap
)

type GorpController struct {
    *revel.Controller
    Txn *gorp.Transaction
}

func (c *GorpController) Begin() revel.Result {
    txn, err := Dbm.Begin()
    if err != nil {
        panic(err)
    }
    c.Txn = txn
    return nil
}

func (c *GorpController) Commit() revel.Result {
    if c.Txn == nil {
        return nil
    }
    if err := c.Txn.Commit(); err != nil && err != sql.ErrTxDone {
        panic(err)
    }
    c.Txn = nil
    return nil
}

func (c *GorpController) Rollback() revel.Result {
    if c.Txn == nil {
        return nil
    }
    if err := c.Txn.Rollback(); err != nil && err != sql.ErrTxDone {
        panic(err)
    }
    c.Txn = nil
    return nil
}

Copy this code into a new file under <app-root>/app/controllers. I call the file gorp.go.

Two important variables to note are the var ( Dbm *gorp.DbMap ) and the GorpController's property Txn *gorp.Transaction. We will use the Dbm variable to perform database creation and the Txn variable to execute queries and commands against MySQL in the controller.

Now in order for us to get the magically wrapped transactional functionality with our controller actions, we need to register these functions with Revel's AOP mechanism.

Create a file called init.go in <app-root>/app/controllers. In this file, we will define an init() function which will register the handlers:

package controllers

import "github.com/revel/revel"

func init(){
    revel.InterceptMethod((*GorpController).Begin, revel.BEFORE)
    revel.InterceptMethod((*GorpController).Commit, revel.AFTER)
    revel.InterceptMethod((*GorpController).Rollback, revel.FINALLY)
}

Now we have transaction support for controller actions.

5. Register Configuration.

We are about ready to start setting up the database. Instead of throwing in some hard coded values for a connection string, we would prefer to pull this information from configuration. This is how you do that.

Open <app-root>/conf/app.conf and add the following lines. Keep in mind that there are sections for multiple deployment environments. For now, we're going to throw our configuration values in the [dev] section:

[dev]
db.user = auctioneer
db.password = password
db.host = 192.168.24.42
db.port = 3306
db.name = auction

Now these values will be made available to use at runtime via the Revel API: revel.Config.String(paramName).

6. Setup the Database.

Next, we need to set up the database. This involves instantiating a connection to the database and creating the tables for our model if the table does not already exist.

We are going to go back to our <app-root>/app/controllers/init.go file and add the logic to create a database connection, as well as, the table for our BidItem model if it does not exist.

First, I'm going to add two helper functions. The first is a more generic way for us to pull out configuration values from Revel (including providing a default value). The second is a helper function for building the MySQL connection string.

func getParamString(param string, defaultValue string) string {
    p, found := revel.Config.String(param)
    if !found {
        if defaultValue == "" {
            revel.ERROR.Fatal("Cound not find parameter: " + param)
        } else {
            return defaultValue
        }
    }
    return p
}

func getConnectionString() string {
    host := getParamString("db.host", "")
    port := getParamString("db.port", "3306")
    user := getParamString("db.user", "")
    pass := getParamString("db.password", "")
    dbname := getParamString("db.name", "auction")
    protocol := getParamString("db.protocol", "tcp")
    dbargs := getParamString("dbargs", " ")

    if strings.Trim(dbargs, " ") != "" {
        dbargs = "?" + dbargs
    } else {
        dbargs = ""
    }
    return fmt.Sprintf("%s:%s@%s([%s]:%s)/%s%s", 
        user, pass, protocol, host, port, dbname, dbargs)
}

With these two functions, we can construct a connection to MySQL. Let's create a function that will encapsulate initializing the database connection and creating the initial databases:

var InitDb func() = func(){
    connectionString := getConnectionString()
    if db, err := sql.Open("mysql", connectionString); err != nil {
        revel.ERROR.Fatal(err)
    } else {
        Dbm = &gorp.DbMap{
            Db: db, 
            Dialect: gorp.MySQLDialect{"InnoDB", "UTF8"}}
    }
    // Defines the table for use by GORP
    // This is a function we will create soon.
    defineBidItemTable(Dbm)
    if err := Dbm.CreateTablesIfNotExists(); err != nil {
        revel.ERROR.Fatal(err)
    }
}

One thing to note in the code above is that we are setting the Dbm variable we defined in the GorpController file gorp.go. Using the connection, Dbm, we will define our BidItem schema and create the table if it does not exist. We have not yet written the defineBidItemTable(Dbm) function; I will show you that soon.

Before we move on, we need to talk about imports. All of the helper functions and code in the init.go file will require the following libraries:

import (
    "github.com/revel/revel"
    "github.com/coopernurse/gorp"
    "database/sql"
    _ "github.com/go-sql-driver/mysql"
    "fmt"
    "strings"
)

Of special note is the import and non-use of the mysql library: _ "github.com/go-sql-driver/mysql". If you do not include this import statement, your project will break. The reason is that GORP relies on the database/sql package, which is only a set of interfaces. The mysql package implements those interfaces, but you will not see any direct reference to the library in the code.

Now it's time to implement the defineBidItemTable(Dbm) function.

func defineBidItemTable(dbm *gorp.DbMap){
    // set "id" as primary key and autoincrement
    t := dbm.AddTable(models.BidItem{}).SetKeys(true, "id") 
    // e.g. VARCHAR(25)
    t.ColMap("name").SetMaxSize(25)
}

Notice how I use the term define and not create. This is because the database is not actually created until the call to Dbm.CreateTablesIfNotExists().

Finally, the InitDb() function needs to be executed when the application starts. We register the function similar to the way we did the AOP functions for the GorpController in the init() function:

func init(){
    revel.OnAppStart(InitDb)
    revel.InterceptMethod((*GorpController).Begin, revel.BEFORE)
    revel.InterceptMethod((*GorpController).Commit, revel.AFTER)
    revel.InterceptMethod((*GorpController).Rollback, revel.FINALLY)
}

When we are done with these steps, you will be able to start the application and verify the table creation in MySQL. If you are curious as to how GORP defines the table, here's a screen capture of mysql > describe BidItem;:

BidItem table description

7. Extend the `GorpController`.

We are ready to start working on the controller. First we need to create a new file for the controller: <app-root>/app/controllers/bid-item.go. Using the GorpController as the prototype, let's define our new BidItemCtrl:

package controllers

import (
    "auction/app/models"
    "github.com/revel/revel"
    "encoding/json"
)

type BidItemCtrl struct {
    GorpController
}

We will use those imports soon.

8. Handle JSON Request Body.

This is one of those things that is not well documented in Revel, in part because, Revel doesn't consider it a part of the framework. If you want to be able to transform JSON requests into your own model representations you will need to define that functionality.

We are going to add a simple method to the controller that will parse the request body and return a BidItem and Error tuple:

func (c BidItemCtrl) parseBidItem() (models.BidItem, error) {
    biditem := models.BidItem{}
    err := json.NewDecoder(c.Request.Body).Decode(&biditem)
    return biditem, err
}

We now have a way of getting a BidItem from the Request.Body. You will obviously have to know the context in which to use this method, but that should be obvious since you do that anyway in most web frameworks (albeit it's a little it's a little less elegant in Revel).

9. Implement Controller CRUD functionality.

Let's finish up the rest of the controller!

Add BidItem.

Inserting the record is pretty easy with GORP: c.Txn.Insert(<pointer to struct>).

func (c BidItemCtrl) Add() revel.Result {
    if biditem, err := c.parseBidItem(); err != nil {
        return c.RenderText("Unable to parse the BidItem from JSON.")
    } else {
        // Validate the model
        biditem.Validate(c.Validation)
        if c.Validation.HasErrors() {
            // Do something better here!
            return c.RenderText("You have error in your BidItem.")
        } else {
            if err := c.Txn.Insert(&biditem); err != nil {
                return c.RenderText(
                    "Error inserting record into database!")
            } else {
                return c.RenderJson(biditem)
            }
        }
    }
}

Get BidItem.

func (c BidItemCtrl) Get(id int64) revel.Result {
    biditem := new(models.BidItem)
    err := c.Txn.SelectOne(biditem, 
        `SELECT * FROM BidItem WHERE id = ?`, id)
    if err != nil {
        return c.RenderText("Error.  Item probably doesn't exist.")
    }
    return c.RenderJson(biditem)
}

List BidItems (with paging).

For listing BidItems, were going to need to implement some simple paging. Since we have an auto incremented, indexed bigint(20) for an identifier, we'll keep it simple and use the last id as the start field and a limit to indicate the amount of records we want. For this, we will accept two query parameters: lid (last id) and limit (number of rows to return).

Since we don't want a SQL injection attack, we're going to parse these query options as integers. Here are a couple of functions to help the parsing (keeping it in <app-root>/app/controllers/common.go):

package controllers

import (
    "strconv"
)

func parseUintOrDefault(intStr string, _default uint64) uint64 {
    if value, err := strconv.ParseUint(intStr, 0, 64); err != nil {
        return _default
    } else {
        return value
    }
}

func parseIntOrDefault(intStr string, _default int64) int64 {
    if value, err := strconv.ParseInt(intStr, 0, 64); err != nil {
        return _default
    } else {
        return value
    }
}

The controller function is pretty simple:

func (c BidItemCtrl) List() revel.Result {
    lastId := parseIntOrDefault(c.Params.Get("lid"), -1)
    limit := parseUintOrDefault(c.Params.Get("limit"), uint64(25))
    biditems, err := c.Txn.Select(models.BidItem{}, 
        `SELECT * FROM BidItem WHERE Id > ? LIMIT ?`, lastId, limit)
    if err != nil {
        return c.RenderText(
            "Error trying to get records from DB.")
    }
    return c.RenderJson(biditems)
}

Update BidItem.

func (c BidItemCtrl) Update(id int64) revel.Result {
    biditem, err := c.parseBidItem()
    if err != nil {
        return c.RenderText("Unable to parse the BidItem from JSON.")
    }
    // Ensure the Id is set.
    biditem.Id = id
    success, err := c.Txn.Update(&biditem)
    if err != nil || success == 0 {
        return c.RenderText("Unable to update bid item.")
    }
    return c.RenderText("Updated %v", id)
}

GORP takes the convention of returning a tuple of int64 and Error on Updates and Deletes. The int64 is either a 0 or 1 value signifying whether the action was successful or not.

Delete BidItem.

And the last action for the controller.

func (c BidItemCtrl) Delete(id int64) revel.Result {
    success, err := c.Txn.Delete(&models.BidItem{Id: id})
    if err != nil || success == 0 {
        return c.RenderText("Failed to remove BidItem")
    }
    return c.RenderText("Deleted %v", id)
}

Conclusion.

While a little long, I think that was probably the most complete walkthrough/example you'll find on implementing a Controller in Revel with GORP and MySQL. I hope this helps you in your effort of learning Go, GORP or Revel.

References.

Plus many other StackOverflow articles and blog posts...