Application config in a single-row table

Originally posted here

Here’s the scenario: I am building a fairly straightforward web application that grabs some data from an external API and displays it various ways on a dashboard. Essential to the process of making this particular data retrieval possible is the existence of an api key. This single key unlocks the API and all of its captivating curiosities for my app.

Other than actually accessing this data, one of my additional requirements was the ability to conveniently change the API key whenever I wanted, without having to dive into the application’s codebase. How can I do this?

I’m using Ruby on Rails, but this should prove to be fairly inconsequential. Sure, there are ways to handily and safely store application-wide configuration (like API keys) in Rails specifically, such as slotting them directly into the codebase like this:

# config/environments/production.rb
config.mailchimp_api_key = “ABCDEF”

Or I could use encrypted Rails secrets, and store something like an api key in the config/secrets.yml file, which could then be accessed like this:

Rails.application.credentials.dig(:facebook, :api_key)

The problem with both of these solutions is that they are not dynamic - if I want to update the key I’ve got to manually go into the codebase to change it, test it, then re-deploy the app. It’s just a bit of a faff, and I wanted to eliminate the dependence on somebody with access to, and knowledge of, the app’s codebase.

So, I decided to store the key in a database table - truly groundbreaking stuff. Let’s dig into why this simple solution works really well, and how you can go about implementing it.

I’ve gone with the Single Row Table (SRT). It’s relatively self-explanatory: it’s a table with a single row in it. In my case, this table is called app_settings, and its columns store an assortment of application-wide configuration, including my all-important API key. I can now perform ordinary read/write operations as I would with any other table. However, the difference here being that there’s only ever one row (or record) in the table itself. It might look like this:

Carrying this out in Rails is very straightforward - I can’t see how it could be much more complicated in any other web framework:

1. Using the attr_encrypted gem I followed the documentation’s short instructions to create a model called AppSetting (with a corresponding migration to create the app_settings table), along with an encrypted attribute/column for my API key.

2. To ensure I only ever create and use a single record on this table, I needed an interface that only performs queries on this  particular row. I added this class method to the AppSetting model, which calls Active Record's handy 'first_or_create' method:
   

def self.current
   first_or_create
end

1. From this point onwards, any time I use the AppSetting model, I do so in conjunction with the current class method, e.g Services::Api::Request.call(api_key: AppSetting.current.api_key). Importantly, I now have the means to easily update the api key through the app’s user interface using any old form.

There’s every chance you’re reading this trying to determine whether or not a single-row table just sounds a bit strange. I also sat there similarly deliberating and searching for what I hoped might be a “cleaner” way of doing it. I came across one other popular and viable solution: the Entity-Attribute-Value (EAV) table (also sometimes described as the Name-Value-Pair table).

EAV works very simply by defining a row per configuration value: you would create a table with a column for a setting name and a column for a setting value. In the context of my requirements, the table might look like this:

EAV’s appeal is obvious: you access a table in which each of your config options has its own row in the format of the familiar-looking key value pair. My decision to roll a Single Row Table however was very simply based on the fact that the general sentiment on developer forums seemed to favour SRT over EAV. I seized the opportunity to dip into the analog tool-belt and jot down a pro’s/con’s list. I’ve shared this below to help you pick or plan your response to the rare-sighted “editable application-wide config” conundrum.

Single Row Table Pro’s

• Put plainly, it’s just easy. It’s quick to implement, it feels familiar and it just works.
• Each config value is stored in its correct data type - i.e the encrypted_api_key column is a string, and refresh_seconds is an integer.
• If your requirements change such that your app-wide config need now apply to individual entities instead (for instance a User or a Company), it’s very simple to add a foreign key to your app_settings table, and you’re sorted. Moreover, it’s simply nowhere near as straightforward to do this if you’re using an Entity-Attribute-Value table.

Single Row Table Con’s

• Additional config options require database schema changes. I’ll need a new column if I want to add a Twitter api key for example.
• The table can become very wide if there are lots of config options.
• Not very easy to keep track of exactly when config values are added/updated on this row.

Entity-Attribute-Value Pro’s

• It’s also quite straightforward to implement and use.
• Adding a new config does not require a database schema change, they’re just rows.
• The table schema is very narrow.
• Easy to track when you add/update a config option if you add created_at & updated_at columns to your table.

Entity-Attribute-Value Con’s

• Everything is “stringly typed”: your value column might therefore potentially contain config with a multitude of inherent data types (JSON, string, integer, decimal) but all have to be stored as a string (the column’s field type).
• Even if you decide to solve the above by adding multiple type columns to store your various config options under their correct data type, you’re widening the table and will be left with lots of empty or NULL values on your records, like this:

• This is bad because it forces you to deal with your data pessimistically in your codebase - i.e as though a given value for a record potentially doesn’t exist.

In reality, this problem likely won’t surface very often for you. That said, it’s certainly a talking point for anyone looking to add in some editable application-wide configuration for their app(s). The take away and TL;DR here is that SRT and EAV are seemingly the most popular solutions.