Execute sql within rails environment without using your models
Posted by Marcus Wyatt on 19 August 2008
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 = # 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…
Post created with TextMate.