Wednesday, December 26, 2007

Go to mysql command shell using rake

Tired of typing mysql -u foo -pbar -h db01.firewalled.com baz_development yet? Add to that when you have database environments galore like test, QA, selenium, staging, etc.

Here's a rake task for you. Just point it to a Rails environment and execute and you are in the corresponding mysql command prompt.

e.g.

$ rake mysql RAILS_ENV=qa

Loading mysql in RAILS_ENV=qa...
Executing: mysql -u foo -pbar -h localhost baz_qa_test

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.51 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>


Code:
desc "Loads mysql command prompt"
task :mysql do
puts "\n\nLoading mysql in RAILS_ENV=#{RAILS_ENV}...\n"

database_yml_path = "#{RAILS_ROOT}/config/database.yml"
database_yml = YAML.load(ERB.new(File.read(database_yml_path)).result)

raise "Could not find environment #{RAILS_ENV} in database.yml" unless database_yml[RAILS_ENV]

config = database_yml[RAILS_ENV]
username = config['username']
password = config['password']
database_name = config['database']
host = config['host'] || 'localhost'
port = config['port'] || 3306
socket = config['socket'] || '/tmp/mysql.sock'

raise "Failed. Setup requires a user and database for environment #{environment} in '#{database_yml_path}'.\n\n#{database_yml.to_yaml}" unless username and database_name

args = "-u #{username}"
args << " -p#{password}" unless password.to_s.strip.empty?
args << " -h #{host}" unless host.to_s.strip.empty?
args << " #{database_name}"

command = []
command << "mysql #{args}"

puts <<-EOS
Executing: #{command.join(' ')}\n

EOS

system command.join(' ')
end

3 comments:

KetanPadegaonkar said...

Great stuff. I was looking for this.

Can you make this into a generator ? And host it on svn somewhere on google code or something ?

KetanPadegaonkar said...

In case you get an error "uninitialized constant ERB"

just add:
require 'erb'
after the desc line.

Julian said...

Thanks that's very helpful!

Tiny tweak -- you missed out the socket parameter.

args << " -S #{socket}" unless socket.to_s.strip.empty?