Do I need Stored Procedures? Maybe or Maybe not.

I have written a couple of posts about 7-8 months back (here and here) on how to call stored procedures from a rails application. After using stored procedures in my app for a while I started to think about whether we really need them. So over a couple of months I discussed it (mainly short discussions about stored procedures not whether to use them or not) with my colleagues, friends, other people  and gave some thought to it. If you are new to stored procedures then here is a sort of textbookish definition.

Stored procedures are available to applications accessing an RDBMS, they are similar to constructs seen in other programming languages and stored in database data dictionary. Stored procedures remove the compilation overhead required for inline queries as they can be pre-compiled, hence they run faster in some cases. However, now most RDBMS, as they evolved, implement statement caches to avoid repetitive compilation of SQL statements.

Why people think we need them?

People, especially with database background or DBAs, are of the view that we should use stored procedures for most of the tasks involving database because they offer following advantages:

  1. Stored procedures separate server side function from the client side, i.e, put all your business logic in stored procedures.
  2. Stored procedures are modular and offer advantages compared to embedded queries buried within the GUI code.
  3. They are faster, secure and maintainable
  4. Reduced network usage between clients and servers

Why people or I think we don’t need them?

  1. If the application in question even has a remote chance of running on more than one RDBMS then I would just kick stored procedures out of the door because the exact implementation of stored procedure varies from one database system to another and I certainly won’t like to tie my app to a particular database.
  2. Having business logic in stored procedures is not only breaking every rule of good programming but also not following the tried and trusted way of architecting n-Tier applications, i.e. separating presentation, business logic and storage/data access. Do not mix storage/data access with logic because stored procedures are just an extension of data access tier. As they say, use database as a very efficient file cabinet. The only concern of a database should be to store data or persistent objects and the business logic should reside in the application code. Database in itself is not an application you are developing, it’s part of the layer/tier that stores data. We use SQL in RDBMS and SQL is simply a data manipulation language hence very limited in its scope. A programmer can do everything in the application code that can be done in a stored procedure but the opposite of this is definitely not possible. Hence, databases are not programmable enough to include all the business logic. If you somehow manage doing that using temp tables, inserting redundant rows into the tables, by spending ‘n’ number of extra hours or other hacks then you are gonna face performance/scalability issues and surely maintenance problems later in the development life cycle, i.e. a nightmarish, incomprehensible & complex POS (piece of shit). These issues will come back persistently to haunt you and your team. Hence, the advantage 1 and part of advantage 3 mentioned above is not valid anymore.
  3. Storing business logic in the stored procedures also leads to more work for testing compared to having them in (Object Relational Mapping) OR / domain model class and stored procedures/triggers make test driven development less productive. Also, if I am using ORM then I do not need to use any SQL in my application code because the ORM will generate optimised queries for me on the fly and will also bring simplification and consistency in the coding of whole team. Hence, advantage 2 and part of advantage 3 mentioned above is not valid any more.
  4. Stored procedures can very easily get out of synch with the application code. For instance, if I have different versions of my app in the source code repository and in case something goes wrong with my latest version then I can very easily switch back to an older version by simply checking out / exporting stuff from the repository. But these facilities do not exist for stored procedures / triggers. It’s troublesome to have business logic residing in stored procedures because they are in the database and once you alter an existing procedure the old one is gone, hence no going back. Now some of you may argue to keep each stored procedure/trigger in some text file and commit it to the repository with the app but that’s again creating additional work for your team which means you are also adding chances of error/mistakes. However, if you do this then you still have to run your stored procedures/triggers with every update that’s again prone to mistake/errors.
  5. When something goes wrong then stored procedures don’t provide meaningful feedback unless you code all the exception handling within the procedure itself.
  6. You can’t pass objects to stored procedures. If you are using an ORM, and you have a class that is mapping to a table then you can simply pass the object of that class and you are good but with stored procedures you have to pass each field or object attribute as parameters and you may end up passing lots of them, may be 20, 30,40 or even more. Hence, you end up writing more code which essentially is more error prone and if you get a ‘bad call’ error then go figure what just happened. That’s just creating one object what if you need to add a record to 4 tables simultaneously using transaction and let’s say each of them have 10 fields then you need to pass 40 parameters. Change is the only constant in life, especially in the business environment.  Lets say, due to some business requirement your database schema changes and you have to add/remove columns to/from those tables. If you are using stored procedures then you atleast need to take these steps to make sure that your application is still working fine:
    • Add new data vaildation rules for CRUD operation.
    • Change the way data presented to users and your form fields where you allow users to create/update records.
    • Add or remove new parameters to the stored procedure calls from your front end
    • Add or remove new parameters to your stored procedure definition
    • Add or remove new parameters to all other stored procedures or triggers from where you are calling your affected stored procedure(s).

    If you are using ORM instead of stored procedures then you need to take these steps to make sure that your application is OK:

    • Add new data vaildation rules for CRUD operation.
    • Change the way data presented to users and your form fields where you allow users to create/update records.

    That’s it and you will also have your whole app properly version controlled so that you can switch back and forth seamlessly. So why do you want to do something that involves more work leading to delays, increased complexity, less productivity and has more chances of error? Hence, stored procedures/triggers are actually a constraint in your system and according to Theory of Constraints; generally, in any system or process there is one constraint at a time. To improve overall system or process performance first find the constraint then improve it or eliminate the constraint entirely. Do I need to say more?

If you think you can use stored procedures to hide business logic,  then you are definitely using stored procedure what it is not good or designed for. I neither like putting SQL queries in my code nor have any problem using stored procedures only if they can offer me/my team/employer some measurable benefits but in my opinion an OR model is much better in terms of developer time, code, productivity and maintenance. Using stored procedures indicates adding another layer, another language, complexity and losing database portability. If I have an application running on ‘n’ number of different clients then it will be much easier for me to keep my application code synchronized and maintainable rather than the stored procedures and triggers. Triggers pose another problem area because application does not know whether they exist hence do not even have a clue whether they have run or not. What if you have some logic in one of your trigger and that itself may be calling another stored procedure(s) (again some logic on important data) and due to one or the other problem trigger didn’t fire, you are screwed because neither you nor your application will know whether the trigger was fired or not unless your application users see some discrepancy in their data or reports, report them back to you and then you run multiple sql queries and check everything manually. How kewl is that? Not for me atleast.

Having said all that, I don’t mean the stored procedures were never useful or the advantages of stored procedures cited by DBAs were never true, yes they were true until 6/7 years ago. But with time, new technologies come into existence, some lose their advantages and some become obsolete. There is no point in sticking to something for which you have other better options available.

Database views and Rails

When it comes to using database views then most of us think that it is difficult to use them with Rails or we may not be able to use ActiveRecord with it. Since I experimented with it in the past so I thought why not share it with you folks.

There are several situations when we would like to use database views with our Rails application like:

Situation 1:

If we have a complex SQL query that we don’t want to code in your Rails app due to performance related or any other issues.

Situation 2:

When our database guys have already spent a great amount of time writing useful views that can be used in the current app

Situation 3:

When we have some repeated query to run in every select statement to fetch data from every table. For instance
Select * from table_name where created_on < today;

In that case we would prefer to have a view with this query and call that view.

In all the above situations, it is pretty easy to use our database views with Rails.


Simply create your model as you would for a table and it will work fine just like your model for a database table.

Interesting thing is, apart from viewing data using views I can even update, delete and insert data in my database tables using my views with all those ActiveRecord/ORM niceties (in Sybase) as long as each database view consists of only one table. If you are using more than one table in your database views then you can only view data but cannot do delete, update and insert operation.

I think this is really useful if we have a database that do not conform to Rails conventions but we would like to follow those conventions without changing the current database if other applications depend on it as well. Simply create views for each table in the database and use those views for our models instead of actual tables.

Those single table views (in other words updatable views) will allow us to follow Rails database conventions without changing our existing database tables while also keeping our database guys happy ;o)

(Though we also have other options if we are not following rails conventions)

NOTE: I have tried updatable views only with Sybase and would love to know about your experience with other databases.

Stored Procedures and Rails – Part 2

Continued from Stored Procedures and Rails – Part 1

Now we know how to call our good and reliable stored procedures but there is a difference when we get records from database using Rails ORM/ActiveRecord compared to a stored procedure. This requires a different approach to access records returned from a stored procedure.

Lets say we have a table orders and a corresponding model Order. We all know, how to access data using ActiveRecord, eg.

@orders = Order.find(:all, :conditions => ["some simple condition"])

then what is returned to us is an array of order objects which we can access by either using a block or a for loop. For instance,

for order in @orders
  puts order.order_name

but this is not possible in case if we use a stored procedure to get data on orders because we don’t get back an array of order objects instead we simply get back an array of hashes, ie. an array of records with their attributes and values. Hence, the above code will not work.
Lets say you call your stored procedure, like this

@orders = DbStoredProcedure.fetch_records("exec procedure_name 'args1', 'args2'")

as mentioned in the previous post.
Then you need to access records with a little modification within your for loop

for order in @orders
  puts order['order_name']

I thought this might save sometime who are new to using stored procedures.

NOTE: The only downside of using stored procedures in Rails currently is; you cannot use the Object Relational Mapping which you might have been used to by know, ie. picking all children of an order by simply typing


The best thing to do is to use stored procedures judiciously and mainly for complex logic/calculation in your database but when you want to do something simple with your data like simply showing something or a quick update then use ORM over stored procedures.

Stored Procedures and Rails – Part 1

There is no documentation in Rails about how to use stored procedures or atleast it is difficult to find where to look at. And according to DHH stored procedures is not the Rails Way to do things. DHH mentioned in his post

I want a single layer of cleverness: My domain model. Object-orientation is all about encapsulating clever. Letting it sieve half ways through to the database is a terrible violation of those fine intentions. And I want no part of it.

I somewhat agree to what DHH has to say. And that’s all fine but what if we don’t have any other option apart from using stored procedure and our stored procedure has some complex logic which we don’t want to put in our Ruby code then here is a little hack to it

ActiveRecord::Base.connection.execute("execute procedure_name")

But the above code will not return anything it will only execute your procedure, considering everything went OK.

Moreover, it’s not DRY if everytime you have to write that line of code just to call a procedure.

Here is a better way to do it:

1) Create a class and let’s call it

Class DbStoredProcedure
  your methods here .......

2) Create a class method within it

def self.fetch_db_records(proc_name_with_parameters)

Then simply call it as

DbStoredProcedure.fetch_db_records("exec procedure_name 'args1', 'args2', 'and so on' ")

As mentioned above, this will only execute a procedure without returning any value. To return records or values from stored procedures can use other methods provided by the AR,

class DbStoredProcedure < ActiveRecord::Base

  def self.fetch_db_records(proc_name_with_parameters)

  def self.insert_update_delete_calculate(proc_name_with_parameters)

  def self.fetch_val_from_sp(proc_name_with_parameters)


And then simply call it like

DbStoredProcedure.method_name("exec procedure_name 'args1', 'args2', 'and so on' ")


If you know a better way to do it then I would love to know your views.

Stored Procedures and Rails – Part 2

RSS Feeds + Ruby on Rails + Web App

The Feedtools library is a very comprehensive and powerful Ruby library for handling rss, atom, etc as well as caching. It makes creating, consuming and manipulating feeds a piece of cake.

It’s ideal for parsing RSS feeds in Ruby on Rails applications.

Assuming that you already have installed Ruby and Rubygem, simply run

gem install feedtools

to install the FeedTools gem

or download it from the FeedTools project page and put the untar/unzipped folder in the vendor/plugins directory of your app.

To do a very quick test, create a file called testfeed.rb and add the below code in it:

    require "RubyGems"
    require "feed_tools"

    feedurls = ''
    # If you want to fetch more than one feed then comment the above feedurls variable and   uncomment the below one.
    #feedurls = %w(
    my_feeds = FeedTools::build_merged_feed feedurls

    my_feeds.title = ' Feed'
    my_feeds.copyright = 'SPhred' = 'Nasir ' = ""'./my_feeds.xml', 'w') do |file|
      file.puts my_feeds.build_xml()

Running this file from IRB will create a my_feeds.xml file in your current directory with all the feed contents.

To show feeds on a website, you need to do a few things:
1) Put

require "feed_tools"

at the top of your controller
2) Create an action within this controller to show your feeds, lets call that action user_data

    def user_data
      @feed =[:feed_url])
      # You can first test it with a static feed url like this
      #@feed ='')

3) In the corresponding view add this code

  <div class="feeds">
      <a href="<%= h %>">
      <%= @feed.title %></a>
    <p><%= @feed.description %></p>
    <% for feed in @feed.items %>
      <div class="feed_item">
          <a href="<%= h %>">
          <%= feed.title %></a>
        <p><%= feed.description %></p>
   <% end %>

This is all good for development environment or for small apps but once you go to production environment then you have to think of caching to avoid hitting the feed server every time before displaying feed contents.

Not binding your selection list to a particular model in Rails

In Rails, we use collection_select helper to display values from a particular model using a drop down but it is generally tied to a particular model, i.e. if we look at the generated html then we see something like

select name="customer[customer_search]" id="customer_customer_search"

What if we want something like:

select name="customer_search" id="customer_search"

Then we can simply use select_tag helper and get the values from our model like this

customers = Customer.find(:all, :order => 'first_name').map{|x| [x.full_name] + []}
select_tag(:customer_search, options_for_select(customers))

If we don’t want to display the values from model and would like to feed in something simple and manually, then

customers = [['text_to_display1', 'value1'], ['text_to_display2', 'value2']]
select_tag(:customer_search, options_for_select(customers))

If text_to_display and its value both are same then we can use something as simple as:

customers = ['wee', 'asdf']
select_tag(:customer_search, options_for_select(customers))

Pagination in Ruby On Rails using will_paginate plugin

We do pagination in Rails by using its built in paginate method in our controller

def display_data
@display_data_pages, @display_data = paginate(:location_data,
:conditions => ["loc.updated_at < ?", params[:date]],
:joins => "as loc inner join location_address as locadd on ="
:per_page => 10,
:order => "location_name")

Often we would like to put this code inside our model mainly for two reasons:
1) Code Reuse, and
2) To keep the business logic where it belongs
But we couldn’t do that (since it is difficult to do) due to the paginate method and had to write the same code more than once in the controller and sometimes in more than one controller.
On top of that we have to put this chunk of code in our views only to show the pagination links
<div class="pagination">
<%= if @survdefault_pages.current.previous
link_to("< Prev", { :page => @survdefault_pages.current.previous })
end %>

<%= pagination_links(@survdefault_pages) %>

<%= if
link_to("Next >", { :page =>})
end %>

We can put this code inside a partial but it is still a pain.

Moreover, Rails’ built in pagination will be out of Rails before it will reach version 2 which is not that far.

Update: Rails built in pagination is now removed but is still available as a plugin, ie, classic_pagination.

We have another option, use will_paginate plugin. The advantages of will_paginate plugin are:
1) Easy to use,
2) Less code in views to show pagination links, and
3) It can be used inside models

Follow these steps to use will_paginate plugin

1) Grab it, by going to the command prompt and change directory to the app root. Then run
ruby script/plugin install svn://
NOTE If the above code doesn’t installs the plugin in your vendor/plugins directory then you probably don’t have svn installed on your machine.

2) Now in our controller we can simply write

def display_data
@display_data = LocationData.paginate(:page => params[:page],
:conditions => ["loc.updated_at < ?", params[:date]],
:joins => "as loc inner join location_address as locadd on =", :per_page => 10,
:order => "location_name")

:per_page and :page are required.
The best thing is, as mentioned above, we can move this code to the LocationData model that will help in keeping our controller clean and will also help in code reuse.
To do that, we can create a class method in our model like:

def self.display_location_data(update_date, page)
paginate(:page => page, :conditions => ["loc.updated_at < ?", update_date], :joins => "as loc inner join location_address as locadd on =",
:per_page => 10,
:order => "location_name")

In our controller, the display_data method will now be replaced with just one line

def display_data
@display_data = LocationData.display_location_data(params[:date], params[:page])

In the view, the big chunk of code will be replaced by a single line to show our pagination links, like

<%= will_paginate @display_data %>

And we are done…

UPDATE: Formatting / Styling pagination links

Full text search using Ferret (acts_as_ferret) with rails

I recently started working with acts_as_ferret so I thought to put together a little how to:

Assuming you already have ruby on rails environment set up

1) To install it run gem install ferret
this will prompt you to select a gem for your platform

2) go to your rails project root directory and run
script/plugin install svn://

3) Add require 'acts_as_ferret' to your config/environment.rb

4) Resart your app

To add full text search capability to a model simply add this within your model


To add full text search capability to a model but on some specific attributes/field_names add this

acts_as_ferret :fields => [ :author, :content ]

How to perform search?

If your model name is location then you can use


Note: If you have very large number of records then it might take a while to run your search for the first time as it indexes all the records

If you want to use pagination or multiple models then use
Location.find_with_ferret query, :page => params[:page], :per_page => 10,
:multi => [ Model_2, Model_3 ]

I will be adding some advanced stuff later on …

How to get Gruff working with Rails on windows

NOTE: You can’t do gem install rmagick on windows since (as of now) rmagick gem is only for *nix systems

1) Download rmagick-win32 from

This also has imagemagick for 8 pixels so you don’t need to do a separate download for imagemagick. If you want imagemagick for 16 pixels then download it from here

Imagemagick site says ’16 bitversion permits you to read or write 16-bit images without losing precision but requires twice as much resources as the Q8 version.’

3) Install Imagemagick

To confirm if imagemagick is installed properly go to your command prompt and type

convert logo: logo.gif
identify logo.gif
imdisplay logo.gif

this should display the imagemagick logo if everything was ok

4) Go to your command prompt and run gem update --system

5) Go the directory where you unzipped the downloaded rmagick-win32 file

6) Run gem install rmagick --local

after it is installed successfully then you should see

Successfully installed rmagick, version 1.15.9

7) Either run gem install gruff


to use rails plugin. Go to your app root directory and run

ruby script/plugin install

And you are done…

Not following Rails table and field conventions

Whenever we generate a model then Rails expects that the underlying table has following:

1) table name starts with small letter and the word is in plural (ie, customers)

2) a table name that has more than one word in its name then it should be separated by an underscore (ie, line_items)

3) the field names with in the table should also start with small letters.

4) the primary key of the table should be an auto-incremented, integer type and with field name id

5) if you have a foreign key with in that table then it should be in the form table_name_id

If a table follows all the above conventions then the generated model will work straightaway. Otherwise follow these steps

  • If you are not following the conventions 1 & 2 and your table name is something like CustomerRecord instead of customer_records then you have to set the table name within your model just after the class name, like this:

class Customer < ActiveRecord::Base

set_table_name “CustomerRecord”



  • If you are not following the convention 4 and your auto-incremented primary key is called Customer_ID instead of id, then do this

class Customer < ActiveRecord::Base

set_primary_key “Customer_ID”



  • If you are not following convention 5 and your foreign key is named as Customer_Category, then you have to specify that in your model relationship,

class Customer < ActiveRecord::Base

set_table_name “CustomerRecord”

set_primary_key “Customer_ID”

belongs_to :category, :foreign_key => ‘Customer_Category’