Simple Database Interaction in Go

At clypd, almost every feature we build is data-driven. It is therefore important for the data access layer of our Go platform to be powerful, yet easy to build upon, maintain, and debug. Today we dive into the design of the clypd data access layer, the frameworks we chose, and how we augmented them.

A huge benefit provided by Go is simplicity throughout the clypd stack. Dependencies, particularly third-party libraries, are often opaque in other programming languages due to varying code style, source code that is difficult to find, or abstraction layers that are challenging to see through. The source code for any library is just a click away in Go. Design idioms found in dependency libraries are usually similar to the ones we use, which ensures that the entire system is easy for our team to comprehend.

As we continue to build out the clypd platform, our interactions with data become more complex, but our data interaction layer should remain simple. To this end, we have combined a set of frameworks and tools for simple database interaction, particularly for Postgres. These tools allow us to build advanced queries and map their outputs to Go structs while keeping the entire system comprehensible and easy to debug.

Prior to switching to Go, we used Rails ActiveRecord for basic CRUD operations and function-driven string manipulation for constructing advanced queries. Having had this experience, we find actual SQL be more legible, maintainable, and performant. With the switch from Ruby to Go, this steered us toward a lightweight framework that allows us to write SQL queries, as opposed to a more comprehensive framework such as gorm.

gorp & templates

The core of our Postgres integration layer consists of go-gorp/gorp on top of lib/pq. gorp (Go Relational Persistence) is a lightweight library for performing database operations and mapping data to and from structs. Data access becomes less magical when developers write SQL queries as opposed to code in a higher-level abstracted DSL. Gorp supports this by making query-writing an integral part of selecting data, but still does the grunt work of mapping resulting rows to struct fields. Once queries go beyond basic CRUD operations and start to make use of joins or advanced Postgres-specific features, having access to raw SQL beats wrestling with an ORM that only excels at the most basic operations.

Postgres prepared statement parameters are an important tool for query reuse and combating SQL injection. Parameters alone do not provide enough flexibility for advanced queries, however. For example, consider the following basic reporting query and supporting code:

This example is simple because the query is static. The only variable between executions is the input customerID, so the same statement can be prepared once and executed with different arguments. Now, consider this more complex reporting query:

In the above example, translateParams must translate the given input struct (params) into a SQL query and input parameters. The query may or may not have WHERE or GROUP BY clauses, containing date, customer_id or both. Static reportQuery instances are no longer feasible as the number of potential query configurations grows exponentially with each additional filter or grouping option. This is further complicated by the report’s ability to be filtered by multiple customerIDs resulting in an input parameter count that cannot be determined ahead of time.

This problem is solved by building the SQL dynamically. This is often done with string manipulation, but Go provides a much better alternative via the text/template standard package. This package provides a simple interface to build dynamic documents from input parameters. Typical use cases are building HTML or e-mail content, but this approach works equally well for SQL.

The text/template package provides a solid foundation for building dynamic SQL in a maintainable way. Standard template functions such as variable interpolation, conditionals and basic iteration are all provided. Template snippets can also be defined and reused in other templates. Furthermore, the package supports custom template functions, which enable the construction of more advanced features supporting building SQL.

clypd has developed a set of template functions that make constructing dynamic SQL a breeze. In addition to translating a template to a query, these functions translate tagged struct field values to a slice of Postgres arguments in-order. Postgres then substitutes these for the $1, $2, …, $n variables present in the translated query. Let’s see how this works using the example from before:

ReportParam is tagged to identify Postgres parameters:

Note that Postgres parameters are only those that get substituted for variables in the prepared statement by Postgres ($1, $2, … , $n). These are not the same as parameters that are only used by the template engine to evaluate the dynamic SQL template. The tags are used by the template functions to pick out the values that need to be passed into the prepared statement.

A template for the example above looks like this:

This template covers all 24 combinations of customer/date filtering and grouping, as well as the potential for multiple customer IDs. Grouping is straightforward: if the GroupByDate or GroupByCustomer Boolean is true, the corresponding columns are included in the SELECT and GROUP BY clauses. The {{GroupBy}} helper evaluates to “GROUP BY” on the first invocation, then subsequently evaluates to a comma. This allows zero or more columns in the GROUP BY clause with no assumptions about column order ahead of time.

Building the WHERE clause is a little bit trickier. If FilterDate is not nil, the contents of the if block is evaluated. GetOrdinal assigns a Postgres parameter ordinal to the associated parameter, so the result of the if block becomes something like: “WHERE date = $1”. FilterCustomerID evaluates along the same lines. Let’s say FilterCustomerID has three elements. The result of the if block becomes “AND customer_id IN ($2, $3, $4)”. The template functions then provide the four values that Postgres interpolates into the resulting dynamic query.

Managing template files

Deployment of a Go program is typically no more complex than compiling it and copying it to a machine running on the same architecture. Using templates potentially complicates this, because the program loads template resources at run-time. At clypd, we leverage go.rice to precompile templates and similar resources at build time. These resources get baked into the compiled binary, so deployment stays simple.

A handful of template building blocks for WHERE, GROUP BY & ORDER BY clauses enable surprisingly advanced queries to be constructed. By investing in these simple tools, we ensure that our stack remains easy to understand and debug. These tools have allowed the data access layer to keep up with the increasingly advanced data-driven features that we are building into the clypd platform.

Further reading

Want to learn more about Go? Check out our other blog posts:

Join the discussion 3 Comments

  • Paul says:

    Using templates is a great idea.
    Do you have any of the supporting code available?



    • Andrei says:

      Hello Paul,

      Thanks for the comment. We haven’t open sourced the code for translating parameter structs to Postgres ordinal arguments to evaluate these sorts of templates yet, but I would like to. It’s mostly a matter of separating the logic from the rest of our code and performing cleanup. I will respond here if/when we do.

      clypd still uses SQL templates the same way described in this post, but we have since cleaned up the interface a little bit to make more advanced conditional logic possible. For example, a parameter struct can have two filter criteria populated, but only one actually makes it into the query based on Boolean logic involving a third field on the parameter struct.

      – Andrei @ clypd

  • Dan O'Hara says:

    It has been a while, any update on posting some working examples?

Leave a Reply