How to make generic SQL heredocs?

I’m making a mock ORM in Ruby, storing ‘forum’ data.

I have the following tables:

users (id, fname, lname), and questions (id, title, body, author_id).

a_user.create and a_question.create are more or less the same:

    # aUser#create

    def create
        # adds a user to the database
        raise "user already in db" if self.id

        begin
            QuestionsDB.instance.execute(<<-SQL, self.fname, self.lname)
                insert into users (fname, lname)
                values (?, ?);
            SQL
            self.id = QuestionsDB.instance.last_insert_row_id

            return self.id
        rescue => exception
            raise "failed to create user: #{exception}"
        end
    end

    # aQuestion#create

    def create
        # add question to db
        raise "already asked" if self.id
        raise "title already exists" unless Question.find_by_title(self.title).empty?
        
        begin
            QuestionsDB.instance.execute(<<-SQL, self.title, self.body, self.author_id)
                insert into questions (title, body, author_id)
                values (?, ?, ?);
            SQL
            self.id = QuestionsDB.instance.last_insert_row_id

            return self.id
        rescue => exception
            raise "failed to create question: #{exception}"
        end
    end

I’m writing another class, ModelBase, and I’d like to be able to say something like

ModelBase.create(aUser)

or

aMB = ModelBase.new(~characteristics_from_user_or_question_object~)
aMB.create

. Users and Questions do not have an id until after they are added to the database.

The SQL for both of the creates basically takes all the pre-creating attributes and plops them into all columns in the corresponding table, excluding the first ‘id’ column. Is there a way to generalize the instruction “insert all the attributes of this object into the specified table for values (‘all but the first’)”?

Answer

We can implement a very basic ORM with some Ruby meta programming but please keep in mind that this is nothing you should use in production for security reasons. Implementing a fully working ORM is A LOT of work.

We can get the instance variables of a class with instance_variables.

class User
  attr_reader :id, :first_name, :last_name

  def initialize(first_name:, last_name:)
    @first_name = first_name
    @last_name = last_name
  end
end

puts User.new(first_name: "Elvis", last_name: "Presley").instance_variables
# @first_name @last_name

Please note the @ in the beginning which we can remove with a simple gsub. We also need to reject the @id attribute. Finally with instance_variable_get we can get the actual values.

With this information we can now implement a columns and values method.

class OrmBase
  def columns
    instance_variables_without_id.map do |var_name|
      var_name.to_s.gsub /^@/, ''
    end
  end

  def values
    instance_variables_without_id.map do |var_name|
      instance_variable_get var_name
    end
  end

  def instance_variables_without_id
    instance_variables.reject { |var_name| var_name == "@id" }
  end
end

Now we need to compute the table name which we can derive from the class name of the object with self.class.name. If we put everything together it would look like this:

class OrmBase
  def create
    return if @id

    QuestionsDB.instance.execute(to_sql)
    @id = QuestionsDB.instance.last_insert_row_id
    self
  end

  def to_sql
    "INSERT INTO #{table_name} (#{columns.join(', ')}) values (#{values.join(', ')});" 
  end

  private

  def table_name
    "#{self.class.name}s".downcase  
  end

  def columns
    instance_variables_without_id.map do |var_name|
      var_name.to_s.gsub(/^@/, '')
    end
  end

  def values
    instance_variables_without_id.map do |var_name|
      instance_variable_get(var_name)
    end
  end

  def instance_variables_without_id
    instance_variables.reject { |var_name| var_name == "@id" }
  end
end

class User < OrmBase
  attr_reader :id, :first_name, :last_name

  def initialize(first_name:, last_name:)
    @first_name = first_name
    @last_name = last_name
  end
end

class Question < OrmBase
  attr_reader :id, :title

  def initialize(title:)
    @title = title
  end
end

puts User.new(first_name: "Elvis", last_name: "Presley").to_sql
# INSERT INTO users (first_name, last_name) values (Elvis, Presley);
puts Question.new(title: "What is your favorite song?").to_sql
# INSERT INTO questions (title) values (What is your favorite song?);

As mentioned in the beginning, please only use this as part of an exercise as it is not safe (SQL injections), has bugs (e.g. pluralisation of the table name and escaping of values) etc.

Another approach how this could get implemented is to query the database first which column names the table has and then derive the class attributes from it. ActiveRecord for instance does it this way.