How do I connect to a postgres database with Sequel?

Question

I was making a web app to deploy using Heroku.com when I realized that the only database type they support is PostgreSQL. Up until now, my app (powered by the Ruby gem Sinatra) accessed the database via the .Sqlite method for the Sequel gem.

Here's my Ruby script from when I used Sequel to access the .db file via SQLite:

DB = Sequel.sqlite('mydatabase.db')
DB.create_table :mytable do
  primary_key :id
  String :column_name
end

I installed PostgreSQL after learning Heroku used only that. Here's the script via postgres (my username is literally 'postgress', though I obviously won't reveal my password in this question):

DB = Sequel.postgres('mydatabase.db',:user=>'postgres',:password=>'my_password_here',:host=>'localhost',:port=>5432,:max_connections=>10)
DB.create_table :mytable do
  primary_key :id
  String :column_name 
end

However, when I run this code, I get the following error:

C:/Ruby193/lib/ruby/gems/1.9.1/gems/sequel-3.38.0/lib/sequel/adapters/postgres.rb:208:in 'initialize': PG::Error: FATAL: database "mydatabase.db" does not exist (Sequel::DatabaseConnectionError)

I've tried searching Google, StackOverflow, Sequel documents, and the Heroku help documents for any help, but I've found no fix to this problem.

Does anyone know what I am doing wrong?

Craig Ringer · Accepted Answer · 2012-08-27 01:43:43Z

3

The database mydatabase.db doesn't exist, as per the error message from Pg. Likely reasons:

You probably meant mydatabase without the SQLite-specific .db filename suffix
It's possible you created the db with different case, eg "Mydatabase.db";
You might be connecting to a different Pg server than you think you are
You never created the database. Unlke SQLite's default behaviour, Pg doesn't create databases when you try to connect to a database that doesn't exist yet.

If in doubt, connect to Pg with psql and run \l to list databases, or connect via PgAdmin-III.

The PostgreSQL documentation and tutorial are highly recommended, too. They're well written and will teach you a lot about SQL in general as well as Pg in particular.

BTW, the postgres user is a superuser. You should not be using it for your application; it's like running your server as root, ie a really bad idea. Create a new PostgreSQL user without superuser, createdb or createuser rights and use that for your application. You can either CREATE DATABASE somedb WITH OWNER myappuser - or preferably, create the database owned by a different user to your webapp user and then expicitly GRANT the webapp user the minimum required permissions. See user management and GRANT.

edited Aug 27, 2012 at 1:43

answered Aug 26, 2012 at 23:19

Craig Ringer

329k84 gold badges742 silver badges820 bronze badges

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

11 Comments

Username Over a year ago

It doesn't seem that dropping the ".db" works. I'd like to try your second solution, but I'm not sure I understand. Sorry, I'm new to Postgres. When you say connect to Pg with psql, how do I do that? Thanks for your response.

Craig Ringer Over a year ago

Re psql, see postgresql.org/docs/9.1/static/tutorial-accessdb.html, postgresql.org/docs/9.1/static/app-psql.html, devcenter.heroku.com/articles/heroku-postgres-documentation and devcenter.heroku.com/articles/heroku-postgres-addon . The PgAdmin-III gui is also fine; once connected you'll get a database list with it. I don't use Heroku, but the docs I liked to should explain how to connect. IIRC you have to use SSL when connecting to Heroku from the outside.

Craig Ringer Over a year ago

Re case issues, see postgresql.org/docs/8.0/static/… for a discussion of case sensitivity and quoting.

Username Over a year ago

I used PgAdmin-III to connect to localhost:5432, but I don't see my database in there. I only see a database called 'postgres'. Any idea how I get it on there? As for what you said in that paragraph about Postgres superuser, I kind of get it, but not totally. I'm guessing I can make new users in the PgAdmin-III gui by making a new Login Role, though I'm no sure what you meant about the 'postgres' user being bad for app testing -- though that's mostly because I'm just today gettting into this. Again, I appreciate your help -- especially on a Sunday night.

Craig Ringer Over a year ago

Er, have you actually created the database you're trying to connect to? postgresql.org/docs/current/static/sql-createdatabase.html . For user management, see postgresql.org/docs/9.1/static/user-manag.html. It's Monday morning for me btw. Answer updated.

|

hgmnz · Accepted Answer · 2012-08-29 15:54:05Z

1

On heroku all you need to do is tell Sequel to connect to the content of the DATABASE_URL environment variable (which is a properly formed url that Sequel understands):

DB = Sequel.connect(ENV['DATABASE_URL'])

answered Aug 29, 2012 at 15:54

hgmnz

13.3k4 gold badges39 silver badges41 bronze badges

3 Comments

Username Over a year ago

Is the syntax "Sequel.postgres(PARAMETERS_GO_HERE)" acceptable? I've managed to solve the problem by adding on a PostgreSQL database to my Heroku app, getting its info, and changing my Ruby script's PostgreSQL-connection syntax to reflect the database's info.

nutella_eater Over a year ago

Where is 'DATABASE_URL' stored?

BenKoshy Over a year ago

@alexeypolusov heroku automatically takes care of that when you provision a database. Here is an example of provisioning postgresql on heroku: devcenter.heroku.com/articles/…

Collectives™ on Stack Overflow

How do I connect to a postgres database with Sequel?

2 Answers 2

11 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

11 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related