Invoking a large set of SQL from a Rails 4 application

Question

I have a Rails 4 application that I use in conjunction with sidekiq to run asynchronous jobs. One of the jobs I normally run outside of my Rails application is a large set of complex SQL queries that cannot really be modeled by ActiveRecord. The connection this set of SQL queries has with my Rails app is that it should be executed anytime one of my controller actions is invoked.

Ideally, I'd queue a job from my Rails application within the controller for Sidekiq to go ahead and run the queries. Right now they're stored in an external file, and I'm not entirely sure what the best way is to have Rails run the said SQL.

Any solutions are appreciated.

Answer 1

I agree with Sharagoz, if you just need to run a specific query, the best way is to send the query string directly into the connection, like:

ActiveRecord::Base.connection.execute(File.read("myquery.sql"))

If the query is not static and you have to compose it, I would use Arel, it's already present in Rails 4.x:

https://github.com/rails/arel

Answer 2

You didn't say what database you are using, so I'm going to assume MySQL.

You could shell out to the mysql binary to do the work:

result = `mysql -u #{user} --password #{password} #{database} < #{huge_sql_filename}`

Or use ActiveRecord::Base.connection.execute(File.read("huge.sql")), but it won't work out of the box if you have multiple SQL statements in your SQL file.

In order to run multiple statements you will need to create an initializer that monkey patches the ActiveRecord::Base.mysql2_connection to allow setting MySQL's CLIENT_MULTI_STATEMENTS and CLIENT_MULTI_RESULTS flags.

Create a new initializer config/initializers/mysql2.rb

module ActiveRecord
  class Base
    # Overriding ActiveRecord::Base.mysql2_connection
    # method to allow passing options from database.yml
    #
    # Example of database.yml
    #
    #   login: &login
    #     socket: /tmp/mysql.sock
    #     adapter: mysql2
    #     host: localhost
    #     encoding: utf8
    #     flags: 131072
    #
    # @param [Hash] config hash that you define in your
    #   database.yml
    # @return [Mysql2Adapter] new MySQL adapter object
    #
    def self.mysql2_connection(config)
      config[:username] = 'root' if config[:username].nil?

      if Mysql2::Client.const_defined? :FOUND_ROWS
        config[:flags] = config[:flags] ? config[:flags] | Mysql2::Client::FOUND_ROWS : Mysql2::Client::FOUND_ROWS
      end

      client = Mysql2::Client.new(config.symbolize_keys)
      options = [config[:host], config[:username], config[:password], config[:database], config[:port], config[:socket], 0]
      ConnectionAdapters::Mysql2Adapter.new(client, logger, options, config)
    end
  end
end

Then update config/database.yml to add flags:

development:
  adapter: mysql2
  database: app_development
  username: user
  password: password
  flags: <%= 65536 | 131072 %>

I just tested this on Rails 4.1 and it works great.

Source: http://www.spectator.in/2011/03/12/rails2-mysql2-and-stored-procedures/