The default database framework installed with Rails is
sqlite3 . As the name implies, it’s a lightweight SQL-based database system that works straight out of the box. Unfortunately, the framework isn’t supported by Heroku, which requires your code to utilize Postgres to function. I figured it’d be a good idea to set up a local Postgres server to familiarize myself with the framework.
Postgres Installation and Setup
There are multiple options when it comes to installing and managing Postgres. Postgres.app is a GUI that will also download and initialize a Postgres server for you. PgAdmin comes automatically with the installer provided by the Postgres website. Postico connects to an existing server, providing another GUI.
I opted for a command-line install as I wanted to get my hands dirty and understand how to interact with a bare-bones Postgres server without the help of a graphic user interface.
1. Install Postgres using Homebrew
brew install postgresql
With Rails and
sqlite3 , databases are individual files, generated and managed directly by ActiveRecord. Postgres hosts the databases on a local server that can be turned on and off. You will need to manage the server on its own, setting up the user(s) and database(s) before connecting them to Rails.
Homebrew starts the server and creates a default database named
postgres automatically upon installation. You can check your server status by typing
brew services list which should return the following output:
Name Status User Plist
postgresql started YOUR_USERNAME /Users/YOUR_USERNAME/Library/Launch
Homebrew will initialize a user and give it the username of whichever account is currently logged in on macOS (replacing
YOUR_USERNAME in the above example).
To start and stop the server:
brew services start postgresqlbrew services stop postgresql# You can check the status of your server at any time:brew services list
The homebrew installation of Postgres comes prepackaged with
psql , a command-line interface for your local Postgres server. To enter the
Side note: You can use the name of any valid database when calling
postgres is the default database initialized by Homebrew, so that’s what we’ll use to start.
A few handy commands to help you see what’s happening at any moment:
postgres=# /* Inside of psql environment */-- list available SQL commands
\help-- list backslash commands
\?-- Display current user
SELECT user;-- Display current database
SELECT current_database();-- Lists existing databases
\l-- Lists users and permissions
3. Create a database
CREATE DATABASE your_database_name;
The created database’s owner will be whoever is the session’s current user. You can specify a different owner if you’d like, but only if the current user has the requisite permissions.
-- Create a database and assign its owner
CREATE DATABASE your_database WITH OWNER = different_user;
Make sure to create two databases for your project. One for the test environment and one for development.
CREATE DATABASE your_database_name_test;
4. Create a user
-- WITH PASSWORD clause is optional
CREATE USER new_user WITH PASSWORD 'password_goes_here';
If you like, you can make your new user the owner of the new database you created.
ALTER DATABASE your_database OWNER = new_user;
Success! Your Postgres server should now be ready to use with your Rails project.
Configuring Rails for Postgres
If creating a new rails app from scratch, simply declare Postgres using the
--database flag. This should get you most of the way there.
rails new myapp --database=postgres
1. Ruby Gems
Double check to make sure your Gemfile has the
pg gem. Add
dotenv-rails to help handle database credentials.
# /Gemfilegem 'pg'
Be sure to run
bundle install before continuing.
2. Environment Variables
touch .env to create an environment file and be sure to list it in
.gitignore to prevent it from being pushed to GitHub. Then list the following info:
# ./envPOSTGRES_USER='new_user'# If you declared a password when creating the database:
3. Update database.yml
Now go to
config/database.yml and plug in the environment variables you declared.
# config/database.ymldefault: &default
username: <%= ENV['POSTGRES_USER'] %>
password: <%= ENV['POSTGRES_PASSWORD'] %>
host: <%= ENV['POSTGRES_HOST'] %>development:
database: <%= ENV['POSTGRES_DB'] %>test:
database: <%= ENV['POSTGRES_TEST_DB'] %>production:
database: <%= ENV['POSTGRES_DB'] %>
With that final step completed, you should be ready to generate your migrations as you would normally. Thanks for reading, and happy coding!