exceptionz

Thoughts on Technology, Methodology and Programming.

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 = <<-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: , ,

Post created with TextMate.

Advertisements

5 Responses to “Execute sql within rails environment without using your models”

  1. 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)

  2. 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.

  3. Jason FB said

    what do I do about
    uninitialized constant ActiveRecord

    I must not be requiring it… seems a simple problem but what/who makes ActiveRecord in a rake task?

    • Just add a require ‘active_record’ to the top of your rake file. If you are trying to run the rake tasks outside of a Rails project, then you probably also need to require ‘rubygems’ above the require ‘active_record’. This specific tip use ActiveRecord ORM.

      I almost exclusively work within Rails, so sometimes I would forget that some people use Ruby without Rails.

  4. Zack said

    This is helpful. Thank you. Do you have an example of running a select statement and looping through the result set? Thanks

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: