Rake Task to Load SQL

Posted by Pat Sat, 11 Feb 2006 23:58:00 GMT

Sometimes I have SQL files that I need to run. These can be from old projects, or something that another programmer working on the same project gives to me. It’s easy enough to run psql to load it up, but I’m lazy and would prefer not to have to type in things like the databsae name. More importantly, sometimes I’d like to do some processing on the DB immediately after loading the SQL. So I wrote a simple rake task to do it.

def load_sql_file(file)
  config = ActiveRecord::Base.configurations[RAILS_ENV]
  database = config['database']
  user = config['username']
  puts `psql -U #{user} -f #{file} #{database}`
end

desc 'Load an SQL file'
task :load_sql => [ENV['FILE'], :environment] do |t|
  file = ENV['FILE']
  file = RAILS_ROOT + "/" + file unless file[0].chr == '/'
  load_sql_file file
end

As you can see, the load_sql_file method simply invokes the CLI psql tool using your app’s settings.

The load_sql task takes an environment variable named FILE and runs load_sql_file using the filename specified. If a a relative pathname is given, it uses RAILS_ROOT as the base bath.

This lets you easily load up an SQL file: 
$ rake load_sql FILE=db/sql_file.sql
$ rake load_sql FILE=/usr/local/etc/sql_file.sql

I realize that’s not a huge deal, but I prefer running rake tasks to typing out psql with its arguments all the time, and this lets me set RAILS_ENV while running the task.

The final benefit is that you can create named tasks which load the SQL file and do some processing. Just as an example, let’s say I have an SQL file that inserts a bunch of user records, and for whatever reason I want to make sure that all the users have the admin field set to false. I could do something like this: 
desc 'Load up the users and set admin fields to false'
task :load_users => ["#{RAILS_ROOT}/db/users.sql", :environment] do |t|
  load_sql_file t.prerequisites.first
  User.update_all "admin=false"
end

Now running ‘rake load_users’ from the command line will load up the users.sql file and update every User object’s admin field to false.

So there you go, a task to easily load SQL files, as well as the ability to create named tasks for particular files. You can use that either just to have a named task, or to do some postprocessing.

Tags ,  | no comments