How do I get my rails app to use my postgresql db?

Question

I installed prostgres to use with my rails4 app, and I am able to connect to the database server and create databases. Then I made the necessary changes to my Gemfile and config/database.yml to use postgres. Then I created a new app, and in the app I created a User model:

$ rails generate model User name:string email:string
      invoke  active_record
      create    db/migrate/20130806145935_create_users.rb
      create    app/models/user.rb
      invoke    rspec
      create      spec/models/user_spec.rb

And then I did:

$ bundle exec rake db:migrate
==  CreateUsers: migrating ====================================================
-- create_table(:users)
   -> 0.1498s
==  CreateUsers: migrated (0.1500s) ===========================================

But in the db directory, I see these files:

development.sqlite3
test.sqlite3

Furthermore, when I try to look at those files with the SQLite Database Browser, I get an error that says they are not sqlite 3 databases. In fact, they are empty:

~/rails_projects/sample_app4_0/db$ ls -al
total 40
drwxr-xr-x   8 7stud  staff    272 Aug  6 22:50 .
drwxr-xr-x  24 7stud  staff    816 Aug  6 22:23 ..
-rw-r--r--   1 7stud  staff      0 Jul 30 00:21 development.sqlite3
drwxr-xr-x   3 7stud  staff    102 Aug  6 22:22 migrate
-rw-r--r--   1 7stud  staff   1063 Aug  6 09:06 schema.rb
-rw-r--r--   1 7stud  staff    343 Jul 29 20:01 seeds.rb
-rw-r--r--   1 7stud  staff      0 Jul 30 03:02 test.sqlite3

I read that you can create your rails app with a flag that tells it to use postgres:

rails new myapp --database postgresql

but I already created my app. Do I have to start over? I'm using git if that makes a difference.

My Gemfile:

source 'https://rubygems.org'
ruby '2.0.0'
#ruby-gemset=railstutorial_rails_4_0

gem 'rails', '4.0.0'
gem 'bootstrap-sass', '2.3.2.0'
#Added for postgres:
gem 'pg', '0.15.1'


group :development, :test do
  #gem 'sqlite3', '1.3.7'
  gem 'rspec-rails', '2.13.1'
  gem 'guard-rspec', '2.5.0'

  gem 'spork-rails', github: 'sporkrb/spork-rails'
  gem 'guard-spork', '1.5.0'
  gem 'childprocess', '0.3.6'
end

group :test do
  gem 'selenium-webdriver', '2.0.0'
  gem 'capybara', '2.1.0'
  gem 'growl', '1.0.3'
end

gem 'sass-rails', '4.0.0'
gem 'uglifier', '2.1.1'
gem 'coffee-rails', '4.0.0'
gem 'jquery-rails', '2.2.1'
gem 'turbolinks', '1.1.1'
gem 'jbuilder', '1.0.2'

group :doc do
  gem 'sdoc', '0.3.20', require: false
end

group :production do
  #gem 'pg', '0.15.1'
  gem 'rails_12factor', '0.0.2'
end

config/database.yml:

development:
  adapter: postgresql
  encoding: utf8
  database: sampleapp_dev  #can be anything unique

  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: postgresql
  encoding: utf8
  database: sampleapp_test  #can be anything unique

  pool: 5
  timeout: 5000

production:
  adapter: postgresql
  database: sampleapp_prod 

  pool: 5
  timeout: 5000

Answer 1

Okay, to figure out what's going on I created a new test app using the --database flag:

rails_projects$ rails new test_postgres --database postgresql

It turns out, all that does is set up your config/database.yml file like this:

development:
  adapter: postgresql
  encoding: unicode
  database: test_postgres_development
  pool: 5
  username: test_postgres
  password:

test:
  adapter: postgresql
  encoding: unicode
  database: test_postgres_test
  pool: 5
  username: test_postgres
  password:

production:
  adapter: postgresql
  encoding: unicode
  database: test_postgres_production
  pool: 5
  username: test_postgres
  password:

And the empty sqlite files:

development.sqlite3
test.sqlite3 

were still present in the db directory.

Then I started up postgres, and I used pgAdmin3 to connect to the postgres database server. (If that is confusing, see: How do I start enterpiseDB PostgreSQL on Mac OSX 10.6.8?)

Then I created a model:

~/rails_projects$ cd test_postgres/
~/rails_projects/test_postgres$ rails g scaffold Post title:string author:string body:text

      invoke  active_record
      create    db/migrate/20130807061320_create_posts.rb
      create    app/models/post.rb
      invoke    test_unit
      create      test/models/post_test.rb
      create      test/fixtures/posts.yml
      invoke  resource_route
       route    resources :posts
      invoke  scaffold_controller
      create    app/controllers/posts_controller.rb
      invoke    erb
      create      app/views/posts
      create      app/views/posts/index.html.erb
      create      app/views/posts/edit.html.erb
      create      app/views/posts/show.html.erb
      create      app/views/posts/new.html.erb
      create      app/views/posts/_form.html.erb
      invoke    test_unit
      create      test/controllers/posts_controller_test.rb
      invoke    helper
      create      app/helpers/posts_helper.rb
      invoke      test_unit
      create        test/helpers/posts_helper_test.rb
      invoke    jbuilder
      create      app/views/posts/index.json.jbuilder
      create      app/views/posts/show.json.jbuilder
      invoke  assets
      invoke    coffee
      create      app/assets/javascripts/posts.js.coffee
      invoke    scss
      create      app/assets/stylesheets/posts.css.scss
      invoke  scss
      create    app/assets/stylesheets/scaffolds.css.scss

Then I tried to execute the migration:

~/rails_projects/test_postgres$ bundle exec rake db:migrate
rake aborted!
FATAL:  role "test_postgres" does not exist
...
...

That error occured because I don't have a database user(or role in postgres speak) named test_postgres--and it's likely no one else will either. When I set up postgres, I created the user 7stud, which is my Mac user name. To fix that rails error, I just changed the username to 7stud on the three lines in config/database.yml. Edit: in fact, I didn't even need to do that. Because I am the user 7stud on my Mac, that means I am executing my rails commands as 7stud, and coupled with the fact that my postgres db was setup with a user named 7stud, that allows me to completely eliminate the username line in config/database.yml:

development:
  adapter: postgresql
  encoding: unicode
  database: test_postgres_development
  pool: 5

test:
  adapter: postgresql
  encoding: unicode
  database: test_postgres_test
  pool: 5

production:
  adapter: postgresql
  encoding: unicode
  database: test_postgres_production
  pool: 5

Then I tried executing the migration again:

~/rails_projects/test_postgres$ bundle exec rake db:migrate
rake aborted!
FATAL:  database "test_postgres_development" does not exist
...
...

Notice that the database.yml file specifies three database names. So I created the development and test databases:

~$ cd /Library/PostgreSQL/9.2/
/Library/PostgreSQL/9.2$ sudo su postgres
Password: <normal sudo password>
bash-3.2$ createdb -O7stud -Eutf8 test_postgres_development
bash-3.2$ createdb -O7stud -Eutf8 test_postgres_test

-O owner
-E encoding

Then I tried executing the migration again:

~/rails_projects/test_postgres$ bundle exec rake db:migrate
==  CreatePosts: migrating ====================================================
-- create_table(:posts)
   -> 0.0441s
==  CreatePosts: migrated (0.0443s) ===========================================

Success (but elsewhere on the net examples show different output with 'Notice' lines).

Then I started a server:

~/rails_projects/test_postgres$ rails s

...and in my browser I entered the url:

http://localhost:3000/posts

...and I created a couple of Posts.

Then using pgAdmin3, I tried to find the posts in the test_postgres_development database. First, I refreshed the 'Databases(3)' line in pgAdmin3 by right clicking on the line and choosing 'Refresh'. Then I searched through the test_postgres_development directory for a long time, with no luck. Under Schemas, I could see a Tables directory that contained a posts directory, so the table got created, but I couldn't figure out how to see if there were any entries in the table. It turns out, you have to right click on the posts directory, then select 'View Data', and voila there were the posts I created.

Or you can view the entries using the command line:

~$ cd /Library/PostgreSQL/9.2/
/Library/PostgreSQL/9.2$ sudo su postgres
Password: <normal sudo password>
bash-3.2$ psql -U 7stud test_postgres_development
psql (9.2.4)
Type "help" for help.

test_postgres_development=> \dt

             List of relations
 Schema |       Name        | Type  | Owner 
--------+-------------------+-------+-------
 public | posts             | table | 7stud
 public | schema_migrations | table | 7stud
(2 rows)

test_postgres_development=> \d posts

                                     Table "public.posts"
   Column   |            Type             |                     Modifiers                      
------------+-----------------------------+----------------------------------------------------
 id         | integer                     | not null default nextval('posts_id_seq'::regclass)
 title      | character varying(255)      | 
 author     | character varying(255)      | 
 body       | text                        | 
 created_at | timestamp without time zone | 
 updated_at | timestamp without time zone | 
Indexes:
    "posts_pkey" PRIMARY KEY, btree (id)


test_postgres_development=> select id, title, author, created_at from posts;


 id |         title          |    author    |         created_at         
----+------------------------+--------------+----------------------------
  1 | Hi there.              | Tom          | 2013-08-07 06:24:57.806237
  2 | Ola!                   | Nancy        | 2013-08-07 06:25:23.989643
(2 rows)

test_postgres_development=>