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…
Technorati Tags: Rails, Rake, SQL
Post created with TextMate.
Kristofer said
This does not work for all queries for example
INSERT INTO portals(operator_id) VALUES(25)
Works fine in sql yog but in rails you get the exception:
Mysql::Error: Field ‘id’ doesn’t have a default value: INSERT INTO portals(operator_id) VALUES(25)
Maruis Marais said
I’ve done updates, inserts, deletes and selects from our production db with the above technique. To me it sounds like your “id” column isn’t set to be auto-increment. This depends on the database your using (mysql, postgres, sqllite). But it is hard to diagnose with the limited info.