How to use ActiveRecord to Execute SQL Query

Establish the database connection for the current environment with connection values specified in the database.yml.

$ rails c
Loading development environment (Rails 4.2.6)
 p = ActiveRecord::Base.establish_connection
 => #<ActiveRecord::ConnectionAdapters::ConnectionPool:0x00720 @mon_owner=nil, @mon_count=0, @mon_mutex=#<Thread::Mutex:0x007a8>, @spec=#<ActiveRecord::ConnectionAdapters::ConnectionSpecification:0x007fc8 @config={:adapter=>"mysql2", :encoding=>"utf8", :reconnect=>false, :database=>"lafon_development", :pool=>5...> 

The establish_connection method returns a connection pool object.

 => ActiveRecord::ConnectionAdapters::ConnectionPool 

We can get a connection from the pool by calling connection on the pool object.

 c = p.connection
 => #<ActiveRecord::ConnectionAdapters::Mysql2Adapter:0x00c88 @transaction_manager=#<ActiveRecord::ConnectionAdapters::TransactionManager:0x00220 @stack=[], @connection=#<ActiveRecord::ConnectionAdapters::Mysql2Adapter:0x00e8 ...>>, @query_cache={}, @query_cache_enabled=false, ... > 

Now, we can execute SQL query using that connection.

results = c.execute('select count(*) from articles;')
   (0.8ms)  select count(*) from articles;
 => #<Mysql2::Result:0x00780 @query_options={:as=>:array, :async=>false, :cast_booleans=>false, :symbolize_keys=>false, :database_timezone=>:utc, :application_timezone=>nil, :cache_rows=>true, :connect_flags=>2147525125, :cast=>true, :default_file=>nil, :default_group=>nil, :adapter=>"mysql2", :encoding=>"utf8", :reconnect=>false, :database=>"lafon_development", :pool=>5, :username=>"root", :password=>secret, :host=>"localhost", :port=>3306, :flags=>2}> 

This returns a Mysql2::Result class.

 => Mysql2::Result 

We can iterate through the results list to print it.

  results.each do |result|
      p result
 => [[305]] 

What if we want to retrieve rows? It will be similar to what we did to get the number of articles in the database.

 rows = c.execute('select id, title from articles limit 4;')
    (0.4ms)  select id, title from articles limit 4;
  => #<Mysql2::Result:0x0032 @query_options={:as=>:array, :async=>false, :cast_booleans=>false, :symbolize_keys=>false, :database_timezone=>:utc, :application_timezone=>nil, :cache_rows=>true, :connect_flags=>2147525125, :cast=>true, :default_file=>nil, :default_group=>nil, :adapter=>"mysql2", :encoding=>"utf8", :reconnect=>false, :database=>"lafon_development", :pool=>5, :username=>"root", :password=>humpdaddy, :host=>"localhost", :port=>3306, :flags=>2}> 
  > rows.each do |row|
  >     p row
  >   end
 [41, "Articles by Category"]
 [51, "TDD Basics : Writing your First Test"]
 [81, "TDD Basics : Test First Programming"]
 [91, "TDD Basics : Canonical Test Structure"]
  => [[41, "Articles by Category"], [51, "TDD Basics : Writing your First Test"], [81, "TDD Basics : Test First Programming"], [91, "TDD Basics : Canonical Test Structure"]]


In this article, you learned how you can send query to the database using ActiveRecord methods. This comes in handy in situations where you need to fire one-off queries to check the data.

Related Articles

Create your own user feedback survey