Converting Rails model to SQL insert Query?

Question

Is there a way to convert a Rails model into an insert query?

For instance, if I have a model like:

m = Model.new
m.url = "url"
m.header = "header"

How can I get the corresponding SQL query ActiveRecord would generate if I did m.save?

I want to get: "INSERT INTO models(url, header) VALUES('url', 'header')" if possible.

Note: I don't want to actually save the model and get the query back (from log file, etc). I want to get the query IF I chose to save it.

Answer 1

On Rails 4.1, I found the below code snippet working:

record = Post.new(:title => 'Yay', :body => 'This is some insert SQL')

record.class.arel_table.create_insert
  .tap { |im| im.insert(record.send(
            :arel_attributes_with_values_for_create,
            record.attribute_names)) }
  .to_sql

Thanks to https://coderwall.com/p/obrxhq/how-to-generate-activerecord-insert-sql

Answer 2

If you dont want to save the model you call m.destroy when you are done with the object.

You can log the sql query by debugging it like this

Rails.logger.debug "INSERT INTO models(url, header) VALUES(#{m.url}, #{m.header}).inspect

Answer 3

Tested in Rails 3.2.13: I think I got it right this time, it definitely does not persist to the db this time. It also won't fire validations or callbacks so anything they change won't be in the results unless you've called them some other way.

Save this in lib as insert_sqlable.rb and you can then

#in your models or you can send it to ActiveRecord::Base
include InsertSqlable

Then it is model.insert_sql to see it.

#lib/insert_sqlable
module InsertSqlable
    def insert_sql
      values = arel_attributes_values
      primary_key_value = nil

      if self.class.primary_key && Hash === values
        primary_key_value = values[values.keys.find { |k|
          k.name == self.class.primary_key
        }]

        if !primary_key_value && connection.prefetch_primary_key?(self.class.table_name)
          primary_key_value = connection.next_sequence_value(self.class.sequence_name)
          values[self.class.arel_table[self.class.primary_key]] = primary_key_value
        end
      end

      im = self.class.arel_table.create_insert
      im.into self.class.arel_table

      conn = self.class.connection

      substitutes = values.sort_by { |arel_attr,_| arel_attr.name }
      binds       = substitutes.map do |arel_attr, value|
        [self.class.columns_hash[arel_attr.name], value]
      end

      substitutes.each_with_index do |tuple, i|
        tuple[1] = conn.substitute_at(binds[i][0], i)
      end

      if values.empty? # empty insert
        im.values = Arel.sql(self.class.connectionconnection.empty_insert_statement_value)
      else
        im.insert substitutes
      end

      conn.to_sql(im,binds)
    end
end

It turns out the code is in ActiveRecord::Relation and not ActiveRecord::Persistence. The only significant change is the last line which generates the sql instead of performing it.