activerecord-factory: dynamic user-designed schemas

TL;DR

My team was tasked with developing a Ruby on Rails application that allowed admins to design questionnaires for users to complete. Our initial naive solution was to use 4 models: a Questionnaire with many Questions, matched to many Submissions, each with many Answers. While it did work, the solution was inefficient at scale and inconvenient to query with SQL. I proposed a simpler approach using metaprogramming to dynamically create one table and model for every QuestionnaireDefinition designed by the admin, which was much easier to query and yielded appropriate performance at scale.

Problem

I once had to develop a Ruby on Rails application that allowed admins to design questionnaires and send them for users to complete. Each questionnaire had a title and list of questions, and each question had a prompt and a data type, like yes/no or an integer from 1 to 10. The customer wanted a web interface where they could define their custom questionnaires without programmer intervention.

Developing a fixed questionnaire was an easy and straightforward MVC type of task covered by the Ruby on Rails guide. It required one model and table where each row represented a user submission. Something like this:

But how to make it dynamic? My immediate naive solution, which was also the same of my colleagues, was to use 4 models like this:

However, while it did work, I hope you appreciate how difficult and inefficient it would be to query this data structure, specially at scale.

This is not how a database is supposed to be used.

Solution

Taking a step back, would it be possible to have one table and model for each questionnaire, like in the first fixed example, but have those created dynamically? The answer is yes.

{
  "qstn_1": {
    "type": "boolean",
    "prompt": "yes or no?"
  },
  "qstn_2": {
    "type": "string",
    "prompt": "but why?"
  }
}

{
  "qstn_1": {
    "type": "integer",
    "prompt": "how many?"
  }
}

I started a new Rails app to try it out:

#!/bin/sh
rails new --minimal -B activerecord-factory
cd activerecord-factory
bundle config set path 'vendor/bundle'
bin/rails g scaffold questionnaire_definition title schema:json
bin/rake db:migrate
      

Then I opened the new QuestionnaireDefinition model and put this in:

class QuestionnaireDefinition < ApplicationRecord
  after_create :create_data_table

  def data_table_name
    "questionnaire_#{id}"
  end

  def create_data_table
    self.class.connection.create_table(data_table_name) do |table|
      schema.each do |question, specs|
        table.send(specs["type"], question)
      end
    end
  end

  def data_model
    Class.new(ApplicationRecord).tap do |klass|
      klass.table_name = data_table_name
    end
  end
end
      

Then I went to the Rails console and did this:

#!bin/rails r
QuestionnaireDefinition.create(
  title: "a questionnaire",
  schema: {
    "amount": {
      "type": "integer",
      "prompt": "how many?"
    }
  }
)

q = QuestionnaireDefinition.find(1)
q.data_model.create(amount: 3)
q.data_model.create(amount: 4)
q.data_model.create(amount: 5)
p q.data_model.all
      

Which gave me the following:

[#<#<Class:0x00007f6e16c5a338>:0x00007f6e1689eb18 id: 1, amount: 3>,
 #<#<Class:0x00007f6e16c5a338>:0x00007f6e1689e9d8 id: 2, amount: 4>,
 #<#<Class:0x00007f6e16c5a338>:0x00007f6e1689e898 id: 3, amount: 5>]
      

And it looked pretty on sqlite too:

sqlite> .mode table
sqlite> SELECT * FROM questionnaire_1;
+----+--------+
| id | amount |
+----+--------+
| 1  | 3      |
| 2  | 4      |
| 3  | 5      |
+----+--------+
      

Conclusion

While not covered by the Ruby on Rails guide, it is possible to modify database schema at runtime, create new tables, and use metaprogramming to give a convenient model API for developers. The naive approach described at the beginning would've worked for a small data set, but would've been a disaster at scale both in terms of performance and developer experience. Having one table per questionnaire is the correct way of utilising the database, allowing data to be queried as usual and yielding appropriate performance at scale.