Database Storage in Go with Structable
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"
userRecorder.Insert()
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 {
structable.Recorder
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"
user.Insert()
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.
