Open-sourcing SQX, a way to build flexible database models in Go

Mapping between database rows and in-memory structs can be difficult. Dozens of solutions are available across a plethora of design patterns. Today, we’d like to talk about how we handle this common problem at Stytch using a lightweight approach and our SQX library.

We’re excited to show off SQX, which we’ve just open sourced (available here). We’ll show you the approach we use to keep our code reusable, testable, and easy to maintain and how the SQX library eliminates most of the boilerplate code from the process.

Queryables and Widgets

The first thing we want to talk about is the sqx.Queryable interface (defined here). This is an interface for dealing with databases that allows us to use a sql.DB, a sql.TX, or even our internal db.DBConnector, which is a special interface with additional functionality built on top of a sql.DB object. The important point is that most models don’t care what kind of reference to a database they have – as long as it’s queryable.

Next, let’s take a look at the model we’ll use for our examples. We’re going to be showing examples from our test Widget class (code):

Although it’s simple, it will allow us to show off why SQX is so powerful and how you can build a fully-fledged model <-> db interaction interface with just a few lines of code.

Create, Delete, and DBWidget

The first two methods we’ll talk about are Create and Delete. With SQX, these methods are one-liners. Note that sqx.ToSetMap() will handle converting our struct into a map automatically. We add just a bit of logic for our own toSetMap() just to make sure that the ID and Status strings have actually been initialized. This prevents us from accidentally writing widgets into the db with empty IDs.

We also include dbWidgetin this example. It’s common practice for us to define a specific interface for dealing with database models. In the case of many Stytch models, we commonly take as input a project_id here, which enforces that all interactions are scoped to a specific project. This is a handy way of ensuring that all filters are scoped to the correct project when doing SELECTs or UPDATEs.

Generic filters and clauses

The next concept we want to introduce is the “filter” (or more generally, “clauses”) that uses nil to represent a clause that should not be applied and any non-nil value as something that should be applied. This is incredibly powerful and allows us to define simple Get and Update methods that can be reused across a variety of applications for a single database model.

Here we can see that we’ve once again added some lightweight checks on top of toSetMap for our use case: in this case, we return an error if someone tries to set a widget status to “Greasy” and otherwise defer to the SQX implementation of ToSetMap.

SELECT One(), SELECT All()

It’s possible to reuse the wdigetUpdateFilter for SELECT operations, too, but for some applications, it may be desirable to intentionally limit the scope of what can be filtered or updated – in those cases, you may want to define separate getFilters and updateFilters. That’s what we’ve done here for Widgets. We add a Get method that allows filtering on Widget IDs and Statuses and a GetAll method that returns all widgets in the table.

Quick note: Since WidgetID in the widgetGetFilter is a slice, it gets translated to a clause like “WHERE widget_id IN (‘111’, ‘222’, ‘333’)” in the generated SQL. This is another feature that makes it easy to define complex queries in very little code.

As a side note, you may be wondering what OneStrict() does. That method will raise an error if more than one result is returned by the underlying query. This is useful in cases where you want to hint to the reader that the query should return exactly one result and otherwise something has gone wrong.

If you instead wanted to create a query that may return multiple results but you only care about one of them, you can use sqx.First()instead of sqx.OneStrict(). Lastly, sqx.One() exists which will log a warning if multiple rows are returned, but won’t completely raise an error.

Testing the code

We’ve just defined a flexible interface to handle any necessary Widget query in under 100 lines of code, which is incredible… but you shouldn’t trust code until you’ve tested it! Luckily, our design allows us to make testing easy. Furthermore, our usage of the Queryable interface will let us use sql.Tx to run a test and automatically clean up data at the end without polluting our local database.

Below you can see an example of how we test our Widget reading ability (the full test code is available here). First we write our simple setupTestWidgetsTable function, which starts a transaction and creates a clean table for all of our testing.

We can use that setup function to then test each Get method along with typical error cases that may arise.

Summary

The SQX library is a powerful tool that simplifies the process of creating flexible database interfaces. This combination of a robust library and well-crafted design patterns enables Stytch developers to efficiently handle database interactions while maintaining code reusability, testability, and ease of maintenance. We’re excited to open source this critical piece of code and see how others can benefit.