Quick change over to PostgreSQL in rails in less than 4 minutes

So you messed up. Heroku is mad at you and your build keeps failing. Moving (just a little too) quickly you jumped on the command line and hit `rails new` without specifying the database. Because you were just so excited, you got straight to work and generated a controller or two and probably started styling what will become your root_path. Maybe you plugged in a couple gems like bootstrap. Who knows? Bottom line, you were excited and running. Though it wouldn’t take all that long, you aren’t wanting to back track.

It’s okay. (Or at least it’s going to be…)

The path to a successful build is insight. Luckily you don’t have much (if any) critical data slammed into your relational database.

What went wrong

# what you wrote
rails new COOL_NAME
# what you meant to write
rails new SUPER_COOL_APP --database=postgresql

If you haven’t noticed yet, Rails ships with SQLite3 by default. This is what went wrong for you. This is just dandy for rapid prototyping and proof-of-concept CRUD builds still in development that aren’t relying on a slew (or any) of production data. When you’re looking to go to the big leagues, it’s time to move up to Postgres. Heroku believes that:

PostgreSQL offers the best mix of powerful features, data integrity, speed, standards compliance, and open-source code of any SQL database on the planet.

And they’re right. It’s really nice, rather powerful and dare I say even fun to work with. MySQL is undoubtedly strong and well supported, but Heroku likes Postgres and you like Heroku so it’s time to get your baby ported over to the right database.

To move forward make sure that you have postgres installed locally and have a friendly GUI like pgadmin3 hanging out for when you’re looking to dive into your dataset in an excel-like way.

How to fix it

Two things. One you’ll need to make tweaks to your gem file and re-run a bundle install, but first you need to make changes to your database.yml file.

Navigate to config/database.yml

In atom or sublime, look at your directory structure and navigate to config and then find the database.yml file. It won’t be in any subfolders. It chills with the big boys.

It will look like:

# SQLite version 3.x
# gem install sqlite3
# Ensure the SQLite 3 gem is defined in your Gemfile
# gem 'sqlite3'
default: &default
adapter: sqlite3
pool: 5
timeout: 5000
<<: *default
database: db/development.sqlite3
# Warning: The database defined as "test" will be erased and
# re-generated from your development database when you run "rake".
# Do not set this db to the same as development or production.
<<: *default
database: db/test.sqlite3
<<: *default
database: db/production.sqlite3

In Rails 5, It needs to look something like (copy and paste + replace APP_NAME)

# PostgreSQL. Versions 9.1 and up are supported.
# Install the pg driver:
# gem install pg
# On OS X with Homebrew:
# gem install pg -- --with-pg-config=/usr/local/bin/pg_config
# On OS X with MacPorts:
# gem install pg -- --with-pg-config=/opt/local/lib/postgresql84/bin/pg_config
# On Windows:
# gem install pg
# Choose the win32 build.
# Install PostgreSQL and put its /bin directory on your path.
# Configure Using Gemfile
# gem 'pg'
default: &default
adapter: postgresql
encoding: unicode
# For details on connection pooling, see rails configuration guide
# http://guides.rubyonrails.org/configuring.html#database-pooling
pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
username: postgres
password: password
host: localhost
<<: *default
database: APP_NAME_development
# The specified database role being used to connect to postgres.
# To create additional roles in postgres see `$ createuser --help`.
# When left blank, postgres will use the default role. This is
# the same name as the operating system user that initialized the database.
#username: APP_NAME
# The password associated with the postgres role (username).
# Connect on a TCP socket. Omitted by default since the client uses a
# domain socket that doesn't need configuration. Windows does not have
# domain sockets, so uncomment these lines.
#host: localhost
# The TCP port the server listens on. Defaults to 5432.
# If your server runs on a different port number, change accordingly.
#port: 5432
# Schema search path. The server defaults to $user,public
#schema_search_path: myapp,sharedapp,public
# Minimum log levels, in increasing order:
# debug5, debug4, debug3, debug2, debug1,
# log, notice, warning, error, fatal, and panic
# Defaults to warning.
#min_messages: notice
# Warning: The database defined as "test" will be erased and
# re-generated from your development database when you run "rake".
# Do not set this db to the same as development or production.
<<: *default
database: APP_NAME_test
# As with config/secrets.yml, you never want to store sensitive information,
# like your database password, in your source code. If your source code is
# ever seen by anyone, they now have access to your database.
# Instead, provide the password as a unix environment variable when you boot
# the app. Read http://guides.rubyonrails.org/configuring.html#configuring-a-database
# for a full rundown on how to provide these environment variables in a
# production deployment.
# On Heroku and other platform providers, you may have a full connection URL
# available as an environment variable. For example:
# DATABASE_URL="postgres://myuser:mypass@localhost/somedatabase"
# You can use this database configuration with:
# production:
# url: <%= ENV['DATABASE_URL'] %>
<<: *default
database: APP_NAME_production

And make the change to the gem file:

# what you have (starting at line 1)
source 'https://rubygems.org'
# Bundle edge Rails instead: gem 'rails', github: 'rails/rails'
gem 'rails', '~> 5.0.0'
# Use sqlite3 as the database for Active Record
gem 'sqlite3'
# what you should have (from the top)
source 'https://rubygems.org'
# Bundle edge Rails instead: gem 'rails', github: 'rails/rails'
gem 'rails', '~> 5.0.0'
# Use postgresql as the database for Active Record
gem 'pg', '~> 0.18'
# lots of others...
# don't forget to add rails12factor for heroku
gem 'rails_12factor', group: :production

Then run a bundle install and you’ll be in the clear!

# that bundle install
bundle install
# git dance
git add .
git commit -m "Fixed DB issues"
# push it up
git push
git push heroku master


When you’re running a rails server (rails s) moving forward locally make sure that you have postgres running or else it won’t work.

Keep in mind that you’ll also have to create any databases before you can run a migration.

# it's easy don't worry
rake db:create
# then eventually...
rake db:migrate

Hope this helped. Happy dev-ing!