Stored procedures in Ruby on Rails

You can call stored procedures from Rails, but you are going to lose most of the benefits of ActiveRecord, as the standard generated SQL will not work. You can use the native database connection and call it, but it's going to be a leaky abstraction. You may want to consider DataMapper.

taken from >> Using Stored Procedures in Rails

To sum up, its not the "RAILS WAY" to use stored procedures.

It might not be the "rails way" to use stored procedures but its also once not "The rails way" to use foreign key costraints, and we all know what a monumentally bad design decision that turned out to be.

So I would take "the rails way" with a grain of salt. If stored procedures work for you, use them.

Heres what I do. Understanding that ORMs often dont really 'understand' stored procedures without slightly more in depth magic, I avoid using it directly, but instead create a materialized view that encapsulates the stored procedure and then presents it as a regular table. Correctly set up, this gives the ORM something it can better understand whilst still leveraging the advantages of keeping database logic inside the layer its supposed to live in, the database, an engine that will always outperform the web framework at data crunching.

Is it common practice that stored procedures/sql functions are not preferred to use?

True. Building queries with Active Record allow you to manage them all in your application code.

What are pros and cons of using stored procedures?

Pros: you can hide complex query logic from your application code.

Cons: you have to create and execute a migration if you want to rewrite a procedure.

See this example on hiding logic in a database view, also applicable to procedures.

Pros Example:

You need to select all hotels with rooms available between start_time and end_time. Every hotel has total_rooms (integer attribute), hotel_times (entity that define operating hours for a hotel) and some bookings (entity that define a user that booked a room in a hotel). Some hotels are big and offer daily bookings. Other hotels are small and offer hourly bookings. You ask the user when he wants to book, which can be either a date or a date-with-time.

This involves some joins and sub-queries and would create a big ugly piece of Active Record code. Instead, you can write a procedure and call it like this:

Hotel.find_by_sql ['SELECT * FROM hotels_available_between(?, ?)', start_time, end_time]

Wrap it in a scope and get more ruby-ish:

class Hotel < ActiveRecord::Base
  scope :available_between, -> start_time, end_time do
    find_by_sql ['SELECT * FROM hotels_available_between(?, ?)', start_time, end_time]
  end
end

Hotel.available_between start_time, end_time

Is it common practice that stored procedures/sql functions are not preferred to use?

It is very common, most Rails apps will never need to use anything more than ActiveRecord.

One of the chief philosophies behind Rails is that it's more important to get a working product to market today than it is to get a "fast" product to market 6 months from now. Your product will almost certainly never be popular enough for performance to be a concern. If that does become a problem, you can shore up the performance side of things later, but the immediate concern is to be able to build an app quickly, and to be able to rapidly refactor some or all of it in response to your market.

What are pros and cons of using stored procedures?

They're slower to write and more difficult to change, and therefore front-load your development costs. However, they can be faster to execute.