Rails & Postgres: Better dropping of the database

UPDATE

Please see my new tip for a better solution!

If this sounds familiar...

• You're running your Rails apps locally via Pow
• You're running Postgres
• You want to drop a database entirely every now and then

...then you've probably been running bundle exec rake db:drop and getting error messages from Postgres about other sessions using the database. Annoying.

To solve this issue for myself I've created a small bash function that will close all the active connections for a Postgres database and then drop it.

pgdrop() {
  RAILS_ENV=$1
  : ${RAILS_ENV:="development"}
  RUBY_SCRIPT=$(cat <<SCRIPT
db_config_path = File.expand_path("config/database.yml", Dir.getwd)
abort 'NO_CONFIG' unless File.exists?(db_config_path)
db_config = YAML.load(File.open(db_config_path))["$RAILS_ENV"]
abort 'NO_POSTGRES' unless db_config["adapter"] == "postgresql"
abort db_config["database"]
SCRIPT)
  DB_NAME=$(ruby -ryaml -e $RUBY_SCRIPT 2>&1)
  if [[ $DB_NAME = 'NO_CONFIG' ]]
  then
    echo "No database config file could be found (./config/database.yml)\nMake sure you're running this command from the root of your Rails app"
    return 0
  elif [[ $DB_NAME = 'NO_POSTGRES' ]]
  then
    echo "Oops! It doesn't look like your using Postgres for your database"
    return 0
  fi

  # Assuming the latest verison of Postgres (9.2+)
  psql -c "SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = \"$DB_NAME\" AND pid <> pg_backend_pid();" > /dev/null 2>&1
  RESULT=$(psql -c "DROP DATABASE IF EXISTS \"$DB_NAME\";" 2>&1)
  if [[ $RESULT == *'does not exist'* ]]; then
    echo "Database doesn't exist! Nothing to do."
  else
    echo 'Database dropped!'
  fi
}

With this I can now run pgdrop from within my Rails app directory and drop the database easily. The script defaults to dropping the development environment database so if you want to specify a different environment pass the environment name as an argument e.g. pgdrop staging.

oh-my-zsh users: add it to ~/.zshrc and then source ~/.zshrc or open a new terminal window.

bash users: add it to ~/.bashrc and then source ~/.bashrc or open a new terminal window.

NOTE: I've only tested this with Postgres 9.3+. I believe it should work with 9.2+ but I'm offering no guarantees!

If you find any bugs or have any improvements, please update the Gist.