CODEWITHSUNDEEP
ruby-on-railsrubyactiverecord
Henley Wing Chiu
Henley Wing Chiu

Reputation: 22535

Converting Rails model to SQL insert Query?

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.

Upvotes: 11

Views: 7020

Answers (4)

Nobu
Nobu

Reputation: 10445

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

Upvotes: 10

Paladini
Paladini

Reputation: 4572

After search a lot over the Internet and forums, I think I found a better solution for your problem: just requires two line of code.

I found a good gem that do exactly what you want, but this gem only works for Rails 3.2 and older. I talked with author and he doesn't want support this gem anymore. So I discovered by myself how to support Rails 4.0 and now I'm maintaining this gem.

Download the "models-to-sql-rails" gem here, supporting Rails 4.0 and older.

With this gem, you can easily do the following. (the examples inside values are just a joke, you will get the correct values when using it in your object).

For objects:

object.to_sql_insert
# INSERT INTO modelName (field1, field2) VALUES ('Wow, amaze gem', 'much doge')

For array of objets:

array_of_objects.to_sql_insert
# INSERT INTO modelName (field1, field2) VALUES ('Awesome doge', "im fucking cop")
# INSERT INTO modelName (field1, field2) VALUES ('much profit', 'much doge')
# (...)

Just see the Github of this project and you'll find how to install and use this wonderful gem.

Upvotes: 0

Shawn Balestracci
Shawn Balestracci

Reputation: 7550

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.

Upvotes: 2

Leon
Leon

Reputation: 1302

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

Upvotes: 1

Related Questions