Get the connection

ActiveRecord::Base.connection is a singleton allowing you direct access to the database connection. It is an object of type ActiveRecord::ConnectionAdapters::AbstractAdapter

Useful Methods

The most useful methods are select_all which returns an array of hashes, corresponding to the columns, and select_one which selects a single row.

If you simply want to retrieve information from a single column (i.e. a group of id’s) you may use select_values. If you want to retrieve a single value from the database, use select_value.

It also includes methods for insertion and deletion.

Remember to use the quote method around user supplied strings to prevent SQL Injection attacks.

Example

ActiveRecord::Base.connection.select_one('select sum(quantity) from table')['sum(quantity)'].to_i

Caveats

It’s important to note that select_one returns a hash with the column names as keys pointing to the record’s values, so just using

ActiveRecord::Base.connection.select_one('select sum(quantity) from table').to_i

doesn’t work. If you simply want the value, use select_value instead.

Execute()

You can use:

ActiveRecord::Base.connection.execute(sql)

to execute arbitrary insert statements. (the insert method tries to read the last inserted id, which will fail if you’re not inserting into an indexed table)

category: Howto
keywords: execute, SQL, raw, query