Create Postgresql database in Heroku with Ruby (without Rails)

Question

I'm currently hosting a simple Ruby script that stores URLs and Scores and saving them to YAML. However, I'd like to save to a Postgresql database instead since the yaml file is deleted every time I restart the app. Here's the error I'm getting in Heroku:

could not connect to server: No such file or directory (PG::ConnectionBad)

Here's an example script that works locally, but throws me the above error in Heroku:

require 'pg'
conn = PG.connect( dbname: 'template1' )
res1 = conn.exec('SELECT * from pg_database where datname = $1', ['words'])
if res1.ntuples == 1 # db exists
  # do nothing
else
  conn.exec('CREATE DATABASE words')
  words_conn = PGconn.connect( :dbname => 'words')
  words_conn.exec("create table top (url varchar, score integer);")
  words_conn.exec("INSERT INTO top (url, score) VALUES ('http://apple.com', 1);")
end

Thanks in advance for any help or suggestions!

elithrar · Accepted Answer · 2015-07-15 09:08:45Z

1

Assuming you have created a Postgres database using the Heroku toolchain via heroku addons:add heroku-postgresql:dev (or the plan of your choice) you should have a DATABASE_URL environmental variable that contains your connection string. You can check that locally through heroku pg:config.

Using the pg gem (docs: http://deveiate.org/code/pg/PG/Connection.html) - and modifying the example from there to suit -

require 'pg'
# source the connection string from the DATABASE_URL environmental variable
conn = PG::Connection.new(ENV['DATABASE_URL'])
res = conn.exec_params('create table top (url varchar, score integer;")

Update: A slightly more complete example for the purposes of error handling:

conn = PG::Connection.new(ENV['TEST_DATABASE_URL'])
begin
    # Ensures the table is created if it doesn't exist
    res = conn.exec("CREATE TABLE IF NOT EXISTS top (url varchar, score integer);")
    res.result_status
rescue PG::Error => pg_error
    puts "Table creation failed: #{pg_error.message}"
end

edited Jul 15, 2015 at 9:08

answered Jul 15, 2015 at 5:37

elithrar

24.4k11 gold badges91 silver badges107 bronze badges

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

4 Comments

LeeCarl Over a year ago

Thank you! This worked perfectly! In my previous code, I was using my res1 PG::Result variable to check if the db existed (using ntuples == 1). How would one refactor that to work wit your example? THANKS AGAIN!

elithrar Over a year ago

The database is already created for you: if you inspect the result of heroku pg:credentials you'll find the DB name is defined as part of the DATABASE_URL. Otherwise, a try/rescue block can catch any other more terminal errors during initialisation. See my update for a quick example.

D-side Over a year ago

@LeeCarl database URL contains everything necessary to start working with it: protocol name, server hostname and port, user credentials and database name. Since DBaaS generates all that for you, it makes sense for to provide all that as one large chunk of information. And so it does.

LeeCarl Over a year ago

A rescue block was exactly what I needed. Thanks guys for all the help! :3

Collectives™ on Stack Overflow

Create Postgresql database in Heroku with Ruby (without Rails)

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related