Execute sql within rails environment without using your models

When you have a query that you need to run as raw sql against the database, like batch data processing or maybe some data cleanup, and you don`t want to create ActiveRecord models to handle the data. Because creating models is expensive and slow. So we want to execute directly against the database. But how do we execute directly on the database from within our rails application environment? The solution is to use ActiveRecord::Base.connection to do the work:

sql = "my complex sql statement"
	ActiveRecord::Base.connection.execute(sql)

This is really nice way to execute queries within rake tasks. In the following example I show how you would establish a database connection as well:

namespace :db do
	
	  desc "Cleanup the database by setting rows to deleted when older than xxx. Defaults to development database.  Set RAILS_ENV=[production, test, etc.] to override."
	  task :cleanup => :environment do
	    sql = <<-SQL
	      -- do some cleanup code
	    SQL
	    # used to connect active record to the database
	    ActiveRecord::Base.establish_connection
	    ActiveRecord::Base.connection.execute(sql)
	  end

	end
	

Here is a very funny tutorial on rake. That`s all for now folks…

Technorati Tags: Rails, Rake, SQL

Post created with TextMate.