Ruby Sinatra/Postgres - can't connect to heroku database with PG.connect?

Question

I am trying to get a site set up on Heroku using Sinatra and PostgreSQL. It worked locally (connecting to local database), but after pushing it to Heroku and changing my PG.connect to reflect that, I get an Internal Server Error the moment a page tries to access the database.

require 'uri'
require 'pg'

uri = URI.parse(ENV['DATABASE_URL'])

def db(uri)
  begin
    connection = PG.connect(uri.hostname, uri.port, nil, nil, uri.path[1..-1], uri.user, uri.password)
    yield(connection)
  ensure
    connection.close
  end
end

I am pretty sure these are parsing correctly, because ENV['DATABASE_URL'] displays the full postgres://user:password@host:port/database information that I'm expecting, and if I do the same in IRB uri.hostname, ui.port, etc all return what's expected .

This is my first time trying to get a site working on Heroku, so I am not even sure how to troubleshoot this. (And I googled for about all of yesterday.)

Results for heroku pg:

=== DATABASE_URL
Plan:        Hobby-dev
Status:      Available
Connections: 0/20
PG Version:  9.4.2
Created:     2015-05-30 19:24 UTC
Data Size:   17.7 MB
Tables:      5
Rows:        9320/10000 (In compliance, close to row limit)
Fork/Follow: Unsupported
Rollback:    Unsupported

And all the tables show up when when I do heroku pg:psql <database> from the cli.

Some answers I've seen said to add database.yml to my root app directory, so:

production:
  adapter: 'postgresql'
  database: '<database>'
  host: ENV['DATABASE_URL']
  username: '<username>'

There's probably something simple I'm missing, but I haven't seen a complete guide for Sinatra/PSQL on Heroku - nothing that goes specifically into setting up and connecting to your database. (Everything seems Rails-related.)

Not sure why I didn't get email alerts for replies... Logs just say "No method 'close' for nil class", so PG.connect is not finding the database and returning nil instead — kevlarr
– kevlarr, Commented May 31, 2015 at 18:05
Shouldn’t PG.connect raise an error if it can’t connect? — matt
– matt, Commented May 31, 2015 at 18:58
I thought so.. Testing it locally, if I supply a nonexistent database, it kicks up a PG::ConnectionBad error. If I supply zero parameters or nil, it returns a PG::Connection object which can be .closed. So I'm really not sure how it's getting to close at all without either giving an error beforehand or returning a connection object — kevlarr
– kevlarr, Commented May 31, 2015 at 20:07

frostmatthew · Accepted Answer · 2015-05-31 15:13:02Z

2

In your database.yml file you need to specify the correct host for the host entry. You are passing what is stored in DATABASE_URL (something like postgres://user:password@host:port/database) but it should just be the host.

You will also need to specify a port if it isn't the default for PostgreSQL.

Edit: should also point out if you plan to store the host (or anything else - you definitely should for username and password) in an environment variable you'll need to wrap it, e.g. <%= ENV['HOST'] %>, not just ENV['HOST'] (i.e. how you have in the database.yml excerpt above)

edited May 31, 2015 at 15:13

answered May 31, 2015 at 15:05

frostmatthew

3,2964 gold badges44 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

kevlarr Over a year ago

Hmm okay, that first part makes sense - I overlooked that I was passing the full URL instead of just the host portion. If I'm populating the database.yml with <%= ENV['HOST'] %>, what would the others be? 'PORT', 'USERNAME', 'PASSWORD', etc? I'm also just really confused about database.yml in general - I thought the PG.connect in my db would be the only thing responsible for connecting to the database. I've seen so many different tutorials and posts, some just use PG gem, some use database.yml, some are passing stuff in through a config.ru...

frostmatthew Over a year ago

There is no environment variable for HOST - you would need to set it (you don't even need to call it HOST, you can call it ZOMBIES if you want) as well as any others you don't want to hardcode into the file. If you're unfamiliar with environment variables in general or with use on Heroku I'd suggest reading their guide - but basically you'll do something like heroku config:set HOST=myhost.com and then use <%= ENV['HOST'] %> in your database.yml file.

kevlarr Over a year ago

Somehow nothing I read mentioned setting up those environment variables, thanks.. I've set those up, and I know that my app can access them (I had them render on the root view briefly), and I fixed my database.yml and PG.connect calls to use those instead of parsing them from the URI... Still the same result - call to .connect returns nil

frostmatthew Over a year ago

You might find this tutorial useful. Also, if you haven't gotten too far with your current project and aren't terribly beholden to Sinatra you may want to consider using Rails instead, which comes with an ORM (ActiveRecord) out of the box which will greatly simplify accessing the database (i.e. you won't need to manage connections).

kevlarr Over a year ago

Nice link, thanks! Sinatra's been incredibly useful to learn a lot of the basics that Rails keeps 'hidden' (to us newbies), but.. No, I'm not terribly beholden to it - I had really just been hoping to get a demo app online, but I think I'll wait until we've gotten into ActiveRecord for Sinatra and Rails. I appreciate the help!

Collectives™ on Stack Overflow

Ruby Sinatra/Postgres - can't connect to heroku database with PG.connect?

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related