Avoid writing raw SQL when using Rails. Usually you are doing something wrong if you find yourself doing this. It's usually done when performance is lacking or you need to use some database functionality not implemented in Rails.

# returns value of the first column of the first row
ActiveRecord::Base.connection.select_value("SELECT id FROM users")
# => 1

# returns an array of values of the first column of all result
ActiveRecord::Base.connection.select_values("SELECT id FROM users")
# => [1, 2, 3]

# returns the first result row as an hash
ActiveRecord::Base.connection.select_one("SELECT id, email FROM users")
# => {"id"=>"1", "email"=>"me@ruk.si"}

# returns an array of arrays containing the values
ActiveRecord::Base.connection.select_rows("SELECT id, email FROM users")
# => [["1", "me@ruk.si"], ...]

# returns an ActiveRecord result.
users = ActiveRecord::Base.connection.select_all("SELECT id, email FROM users")
users.columns # => ["id", "email"]
users.rows    # => [["1", "me@ruk.si"], ...]
users.each do |u|
  puts u['id']
end
# => 1 2 3

# returns a result object of your DB adapter, e.g. PG result.
q = ActiveRecord::Base.connection.method(:quote)
sql = <<-SQL
  SELECT id, email FROM users WHERE id = #{q[1]}
SQL
records = ActiveRecord::Base.connection.execute(sql)
records.each do |r|     # each row as a hash
  puts r[:id]
end
records.each_row do |r| # each row as an array
  puts r[0]
end
records.fields          # returned columns as array of string
records.ntuples         # number of returned rows
records.result_status   # status code, 2 means OK
records.values          # return result as an array of arrays

To use the full power of your database, you must enable SQL schema definition.

# config/application.rb set
config.active_record.schema_format = :sql

# you must redo your migrations if you switch to SQL style schema definition
bundle exec rake db:drop
bundle exec rake db:create
bundle exec rake db:migrate

# initializing new servers is now done with `db/structure.sql`
bundle exec rake db:structure:load
# you cannot execute this command multiple times per installation, it will fail

Use reversible for more complex database definitions. Like constraints or database functions.

Creating database constraints.

rails g model Distributor zipcode:text

class CreateDistributors < ActiveRecord::Migration
  def change
    create_table :distributors do |t|
      t.text :zipcode
    end

    reversible do |direction|
      direction.up do
        execute <<-SQL
          ALTER TABLE distributors
            ADD CONSTRAINT zipcode_check
              CHECK (char_length(zipcode) = 5) NO INHERIT;
        SQL
      end
      direction.down do
        execute <<-SQL
          ALTER TABLE distributors
            DROP CONSTRAINT zipcode_check
        SQL
      end
    end
  end

Creating database functions. More about PostgreSQL functions in PostgreSQL notes..

rails g migration AddAwesomeDatabaseFunction

class AddAwesomeDatabaseFunction < ActiveRecord::Migration
  def change
    reversible do |direction|
      direction.up do
        execute <<-SQL
          CREATE FUNCTION sales_tax(subtotal real) RETURNS real AS $$
          BEGIN
            RETURN subtotal * 0.06;
          END;
          $$ LANGUAGE plpgsql;
        SQL
      end
      direction.down do
        execute <<-SQL
          DROP FUNCTION IF EXISTS sales_tax(real);
        SQL
      end
    end
  end
end

ActiveRecord::Base.connection.select_value("SELECT sales_tax(0.99);")
# => 0.0594

Sources

• Call Database function in server with ruby on rails
• Can ActiveRecord call native db functions?