quick-psql

_quick_psql

#!/usr/bin/env ruby

require "yaml"

CONFIG = "~/database.yml"
CONFIG_PARAMS = %w(protocol host port database username password).map(&:to_sym)

def say(channel=:info, msg)
  puts "[#{channel.to_s.upcase}] #{msg}"
end

def fail!(msg)
  say(:fatal, msg)
  exit
end

def psql_cmd(protocol:, username:, password:, host:, port:, database:)
  pw = password.empty? ? "" : ":#{password}"
  "psql #{protocol}://#{username}#{pw}@#{host}:#{port}/#{database}"
end

def execute(cmd)
  say("Executing: #{cmd}")
  exec cmd
end

def config(dbalias=nil)
  @config ||= begin
    unless config = YAML::load(File.open(File.expand_path(CONFIG), "r"))
      fail!("Could not load configuration file #{CONFIG}")
    end
    config
  end

  return @config unless dbalias

  unless config = @config[dbalias]
    fail!("Could not find database configuration for '#{dbalias}'")
  end

  config = config.inject({}){|memo,(k,v)| memo[k.to_sym] = v; memo}
  config[:protocol] ||= "postgres"
  config[:password] ||= ""

  unless (missing_keys = CONFIG_PARAMS.select { |k| !config.include?(k) }).empty?
    fail!("Invalid configuration for '#{dbalias}': Missing #{missing_keys.join(", ")}")
  end

  say("Loaded config for '#{dbalias}':")
  CONFIG_PARAMS.each do |k|
    say("\t#{k}: #{config[k]}")
  end

  config
end

if ARGV[0].eql?("-l")
  say("Available alias:")
  config.keys.each do |dbalias|
    say("\t#{dbalias}")
  end
  exit
end

unless dbalias = ARGV[0]
  fail!("Expected database alias as first argument")
end

db = config(dbalias)

say("")
execute(psql_cmd(db))

database.yml

db-dev:
  protocol: postgres
  host: 192.168.99.101
  port: 5432
  database: db_development
  username: postgres
  password: development

> quick_psql -l
[INFO] Available alias:
[INFO]  db-dev
[INFO]  db-prd-bi

> quick_psql db-dev
[INFO] Loaded config for 'pop-bi':
[INFO]  protocol: postgres
[INFO]  host: 192.168.99.101
[INFO]  port: 5432
[INFO]  database: db_development
[INFO]  username: postgres
[INFO]  password: development
[INFO] 
[INFO] Executing: psql postgres://postgres:development@192.168.99.101:5432/db_development

Can't read the output? Dealing with long strings?
https://stackoverflow.com/questions/9604723/alternate-output-format-for-psql
\x on

CSV export?
https://gist.github.com/tarla/36b117fa91c029e46a91ddedef76cebe