Over a million developers have joined DZone.

Database Storage in Go with Structable

· Java Zone

Discover how AppDynamics steps in to upgrade your performance game and prevent your enterprise from these top 10 Java performance problems, brought to you in partnership with AppDynamics.

I'm not a huge fan of ORMs. Don't get me wrong, I'm not about to start a campaign against them. I know that many people find them to be a great abstraction layer on their database. But I usually feel more productive with my query writing when I can just write SQL.

Okay, not always. Writing plain old CRUD (Create, Read, Update, Delete) queries is a mindless chore equivalent to scrubbing the toilet.

So I wrote a little library called structable that takes care of the boilerplate CRUD for me, but makes it nice and easy to build my own queries when it's time to move beyond simple operations.

Note: Structable 3.0 is new, and breaks backward compatibility with Structable 1.0. This article covers 3.0.

Struct-Table Mapping

At the core of Structable is a simple mapper that reads an annotated struct and maps it to a table in a relational database. I call it "struct-table mapping" (thus the name StrucTable).

With a few annotations, I can make a simple map:

type User struct {
    Id int `stbl:"id,PRIMARY_KEY,SERIAL"`
    Name string `stbl:"name"`
    Email string `stbl:"email"`
  NotMapped string // This will not be mapped to a DB record at all.

The stbl annotation tells Structable about a field on the struct. For example, the Name string is mapped to the column name via the stbl annotation. Structable assumes that you designed your database in such a way that you will be able to store a string in that column.

The Id field has some additional stuff in the stbl tag:

Id int `stbl:"id,PRIMARY_KEY,SERIAL"`

The PRIMARY_KEY keyword tells structable that the Id field is the primary key for the table. The SERIAL (or AUTOINCREMENT if you prefer) tells Structable that this field's value can be assumed to be managed by the database.

Record vs. Recorder

The struct above is a record. It contains data, but it doesn't know how to do anything with that data. Structable provides a recorder to do something with the data.

In it's simplest form, using a recorder is a matter of attaching a record struct to a recorder:

db = //... we'll get to this in a minute.
dbFlavor = "postgres"
user := new(User)
userRecorder = structable.New(db, dbFlavor).Bind("users_table", user)

A recorder needs a few things. First, it needs a handle to the database. I'll come back to that, but in a nutshell, Structable uses my personal favorite database tool: squirrel.

With a database connection in place, we can Bind() (attach) a specific table and struct to the recorder. Now we've essentially said that a User struct is stored in the users_table, and is managed by our new userRecorder.

From here we can use the recorder to insert, update, load, test, or delete a record:

user.Name = "Matt"

In theory, this is similar to the design pattern known as DAO. But Structable's most elegant expression is found using the Active Record pattern.

Using Structable for Active Records

An Active Record is a record object that is its own recorder. We can do that easily with Structable with just two small additions to our User struct:

type User struct {
    builder squirrel.StatementBuilderType

    Id int `stbl:"id,PRIMARY_KEY,SERIAL"`
    Name string `stbl:"name"`
    Email string `stbl:"email"`

// NewUser creates a new Structable wrapper for a user.
// Of particular importance, watch how we intialize the Recorder.
func NewUser(db squirrel.DBProxyBeginner, dbFlavor string) *User {
    u := new(User)
    u.Recorder = structable.New(db, dbFlavor).Bind("user_table", u)
    return u

The User struct now has an anonymous structable.Recorder. In Go, this allows us to "inherit" the Recorder's methods. For the sake of extensibility, I also always store a squirrel.StatementBuilderType on my records so that I can later add new database methods.

Now let's look at the NewUser method. Instead of creating an external recorder, it initializes its internal anonymous Recorder. Now I can use instances of my User struct like this:

user := NewUser(db, dbFlavor)
user.Name = "Matt"

anotherUser := NewUser(db, dbFlavor)
anotherUser.Id = 123
anotherUser.Load() // This loads because Id is a PRIMARY_KEY field.

A Quick Word about the Database Connection

You can take a look at Squirrel to get a better feeling for how to manage connections. It's intuitive and very powerful. But here's how I set that up for the examples above:

func main() {

    // Boilerplate DB setup.
    // First, we need to know the database driver.
    driver := "postgres"
    // Second, we need a database connection.
    con, _ := sql.Open(driver, "dbname=structable_test sslmode=disable")
    // Third, we wrap in a prepared statement cache for better performance.
    cache := squirrel.NewStmtCacheProxy(con)

    // Create an empty new user and give it some properties.
    user := NewUser(cache, driver)
    user.Name = "Matt"
    user.Email = "matt@example.com"

Essentially, I create a new Postgres database connection (though it works fine with MySQL and probably other DB flavors, too). Then I wrap it in Squirrel's statement cache so that I can transparently re-use prepared statements. From there, I'm off to the races.

The Java Zone is brought to you in partnership with AppDynamics. AppDynamics helps you gain the fundamentals behind application performance, and implement best practices so you can proactively analyze and act on performance problems as they arise, and more specifically with your Java applications. Start a Free Trial.


Published at DZone with permission of Matt Butcher, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}