Ruby on Rails Switch From Sqlite3 to Postgres

Here is a very short tutorial on this. I needed to reference this from another post so its a bit quick and dirty. We are going to switch from using the sqlite3 in our local environment to using postgres.

Here is what we need to do:

  1. Install postgres on our local machine. Get Postgres
  2. Add a helpful tool called pgAdmin. Get pgAdmin
  3. Change our Gemfile in our rails app.
  4. Run bundle install to get postgres gem.
  5. Change the database.yml file to use postgres.
  6. Make sure Postgres is up on our machine. (just start the app and it should automagically start up)
  7. Run rake db:setup followed by rake db:migrate
  8. Run rails server
  9. Go to localhost:3000 and see no exceptions!

 

For One and Two above I have provided a couple of links to get postgres and a phpmyadmin type database tool so you can physically see data in the db. I’ll pick up here with number three.

3. Here we make a change to our Gemfile. We need to out sqlite3 and in postgres.

Before:

source 'https://rubygems.org'

# Bundle edge Rails instead: gem 'rails', github: 'rails/rails'
gem 'rails', '4.0.8'

# Use sqlite3 as the database for Active Record
gem 'sqlite3'

# Use SCSS for stylesheets
gem 'sass-rails', '~> 4.0.2'

# Use Uglifier as compressor for JavaScript assets
gem 'uglifier', '>= 1.3.0'

# Use CoffeeScript for .js.coffee assets and views
gem 'coffee-rails', '~> 4.0.0'

# See https://github.com/sstephenson/execjs#readme for more supported runtimes
# gem 'therubyracer', platforms: :ruby

# Use jquery as the JavaScript library
gem 'jquery-rails'

# Turbolinks makes following links in your web application faster. Read more: https://github.com/rails/turbolinks
gem 'turbolinks'

# Build JSON APIs with ease. Read more: https://github.com/rails/jbuilder
gem 'jbuilder', '~> 1.2'

group :doc do
  # bundle exec rake doc:rails generates the API under doc/api.
  gem 'sdoc', require: false
end

# Use ActiveModel has_secure_password
# gem 'bcrypt', '~> 3.1.7'

# Use unicorn as the app server
# gem 'unicorn'

# Use Capistrano for deployment
# gem 'capistrano', group: :development

# Use debugger
# gem 'debugger', group: [:development, :test]

After:

source 'https://rubygems.org'

# Bundle edge Rails instead: gem 'rails', github: 'rails/rails'
gem 'rails', '4.0.8'

# Use postgres as the database for Active Record
gem 'pg'

# Use SCSS for stylesheets
gem 'sass-rails', '~> 4.0.2'

# Use Uglifier as compressor for JavaScript assets
gem 'uglifier', '>= 1.3.0'

# Use CoffeeScript for .js.coffee assets and views
gem 'coffee-rails', '~> 4.0.0'

# See https://github.com/sstephenson/execjs#readme for more supported runtimes
# gem 'therubyracer', platforms: :ruby

# Use jquery as the JavaScript library
gem 'jquery-rails'

# Turbolinks makes following links in your web application faster. Read more: https://github.com/rails/turbolinks
gem 'turbolinks'

# Build JSON APIs with ease. Read more: https://github.com/rails/jbuilder
gem 'jbuilder', '~>; 1.2'

group :doc do
  # bundle exec rake doc:rails generates the API under doc/api.
  gem 'sdoc', require: false
end

# Use ActiveModel has_secure_password
# gem 'bcrypt', '~>; 3.1.7'

# Use unicorn as the app server
# gem 'unicorn'

# Use Capistrano for deployment
# gem 'capistrano', group: :development

# Use debugger
# gem 'debugger', group: [:development, :test]

4. Now we need to run <b>bundle install</b> to get the newly added gem.

$ bundle install

5. Now we need to change our database.yml file located at <b>/config/database.yml</b>

Before:

# SQLite version 3.x
#   gem install sqlite3
#
#   Ensure the SQLite 3 gem is defined in your Gemfile
#   gem 'sqlite3'
development:
  adapter: sqlite3
  database: db/development.sqlite3
  pool: 5
  timeout: 5000

# 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.
test:
  adapter: sqlite3
  database: db/test.sqlite3
  pool: 5
  timeout: 5000

production:
  adapter: sqlite3
  database: db/production.sqlite3
  pool: 5
  timeout: 5000

After:

# PostgreSQL. Versions 8.2 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'
#
development:
  adapter: postgresql
  encoding: unicode
  database: demo_test_development
  pool: 5
  username: dave
  password:
  timeout: 5000

  # 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.
test:
  adapter: postgresql
  encoding: unicode
  database: demo_test_test
  pool: 5
  username: dave
  password:
  timeout: 5000

Here I’ve changed to the postgres adapter and added my username. In this case dave is my Mac username. The database: is my app name in this case demo_test followed by underscore development or test. There is no need to have a production setup here if you use Heroku like I do. Heroku will have its own db for your app and you wont have to config it like this.

6. Make sure postgres is up and running.If your on a Mac it should just start up by itself when you click the Application: postgres.app

7. Now we need to run <b>rake db:setup</b> and then <b>rake db:migrate</b> to get the database schema for our new db type.

$ rake db:setup
...
....some messages....
...
$ rake db:migrate

8. Start up the rails server by running <b>rails server</b>

$ rails server

9.Check our local URL. Most likely: http://localhost:3000 and we should be good to go! You should see the rails startup page that reads “welcome aboard” if you have an empty app.

*NOTE: If you ever want to create an app right from the get go that uses postgres try this command:

rails new my_app_name --database=postgresql

My Basic Ruby on Rails Set Up

If you read my last post you ended up with an empty rails skeleton and the standard Gemfile. I’ve made some changes to it. Here is a post on the changes.

Here is my new basic rails set up. I’ve been toying around with a lot of different set ups trying to dial in a set up for me that could help with rapid deployment for new apps. Really just a quick way to get up and running when I have an idea.

For me its always a lot easier if before running anything at the terminal, I get a short list together of what I want /need. Not too comprehensive, just a short list so I have a general idea of the goal and what I will need. Here is my short list so far. This is an empty Ruby on Rails setup. I’ll be adding few more things and posting a few more posts about it as the list gets more complete.

So far my new Gemfile looks like this:

source 'https://rubygems.org'

gem 'rails', '4.0.8'
gem 'pg' #### i'm a heroku guy so now its all I use for Ruby.####
gem 'sass-rails', '~&gt; 4.0.2'
gem 'uglifier', '&gt;= 1.3.0'
gem 'coffee-rails', '~&gt; 4.0.0'
gem 'jquery-rails'
gem 'turbolinks'
gem 'jbuilder', '~&gt; 1.2'

group :doc do
 # bundle exec rake doc:rails generates the API under doc/api.
 gem 'sdoc', require: false
end

#group :test, :development do
 #gem "rspec"
 #gem "rspec-rails", "~&gt; 2.0"
 #gem "factory_girl_rails", "~&gt; 4.0"
 #gem "capybara"
 #gem "database_cleaner"
 #gem "selenium-webdriver"
#end

I’ve commented out the gems I’d use of I was going to use rspec over mintest.

That’s about it for now. Next I’ll be getting the gems for Twitter Bootstrap and Devise so I can create a User and Admin user first thing. I like to think of the user as the person at the top of the tree that “has_many” of everything else.