Geek Answers Handbook

How to connect to postgres database using Sequel?

I was building a deployment web application using Heroku.com when I realized that the only database type they support is PostgreSQL. So far, my application (running on Ruby gem Sinatra) has accessed the database using the .Sqlite method for Sequel gem.

Here is my Ruby script when I used Sequel to access the .db file through SQLite:

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


I installed PostgreSQL, learning that Heroku used just that. Here's the script via postgres (my username is literally "postgress", although I obviously will not 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 tried looking in Google, StackOverflow, Sequel docs and Heroku help docs for any help, but I did not find any solution to this problem.

Does anyone know what I'm doing wrong?

+4
source share
2 answers

The database mydatabase.db does not exist, according to the error message from Pg. Likely reasons:

  • You probably meant mydatabase without the SQLite .db filename suffix
  • You may have created db with a different case, for example, "Mydatabase.db" ;
  • You might be connecting to a different Pg server than you think.
  • You have never created a database . Unlke SQLite, by default, Pg does not create a database when trying to connect to a database that does not yet exist.

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

PostgreSQL documentation and tutorial are recommended. They are well written and will tell you about SQL in general, as well as about Pg in particular.

BTW, the postgres user is the superuser. You should not use it for your application; this is like starting your server as root, that is a very bad idea. Create a new PostgreSQL user without superuser rights, createb or createuser and use it for your application. You can either CREATE DATABASE somedb WITH OWNER myappuser - or it is preferable to create a database belonging to another user, the user of your web client, and then expelyly GRANT webapp user with the minimum necessary permissions. See user management and GRANT .

+3
source

For the hero, all you need to do is tell Sequel connect to the contents of the DATABASE_URL environment variable (which is a well-formed url that Sequel understands):

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

More articles:


All Articles