4

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?

Username
  • 3,463
  • 11
  • 68
  • 111

2 Answers2

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.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • 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. – Username Aug 26 '12 at 23:27
  • Re `psql`, see http://www.postgresql.org/docs/9.1/static/tutorial-accessdb.html, http://www.postgresql.org/docs/9.1/static/app-psql.html, https://devcenter.heroku.com/articles/heroku-postgres-documentation and https://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 Aug 26 '12 at 23:52
  • Re case issues, see http://www.postgresql.org/docs/8.0/static/sql-syntax.html#SQL-SYNTAX-IDENTIFIERS for a discussion of case sensitivity and quoting. – Craig Ringer Aug 26 '12 at 23:53
  • 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. – Username Aug 27 '12 at 01:00
  • Er, have you actually created the database you're trying to connect to? http://www.postgresql.org/docs/current/static/sql-createdatabase.html . For user management, see http://www.postgresql.org/docs/9.1/static/user-manag.html. It's Monday morning for me btw. Answer updated. – Craig Ringer Aug 27 '12 at 01:07
  • Hm, I guess I haven't. What I'd like to do is make the database with Sequel. I thought the code I had in my question would be the appropriate syntax, but it appears to not be the case – Username Aug 27 '12 at 01:12
  • @username The `postgres` user, as a superuser, has full control over the whole database system. It can drop and create databases, data types, languages, etc. It can write files to the data directory. Since your app should obviously not run under such a powerful user account in production, you should really test with a lower-priveleged account too, so you don't run into issues where your app works in testing but not in production. – Craig Ringer Aug 27 '12 at 01:13
  • @username Unless this "Sequel" tool has special features specific to it (I don't know or use it), you'll need to do it yourself. Your code can connect to the `postgres` database and run a `CREATE DATABASE mydatabase WITH OWNER myappuser;` command, then disconnect and reconnect to the database `mydatabase`. You'd need to manually `CREATE USER myappuser WITH CREATEDB PASSWORD 'xxxxx';` first. Alternately you can connect the 1st time as the `postgres` user and have your app create its own user account before creating the database, in which case you can remove the `CREATEDB`. – Craig Ringer Aug 27 '12 at 01:16
  • Thanks for the explanation on superuser capabilities. As for Sequel, I could swear there was a way to make Postgres databases with it. If not, I'll research what you said some more. – Username Aug 27 '12 at 01:22
  • @username Not as far as I can see with a quick search of the docs, but maybe there's something hidden away in the db-driver-specific bits. – Craig Ringer Aug 27 '12 at 01:44
  • So, I made a database using pgAdmin. Now, all I need to do is learn some of its peculiarities like the proper DateTime format. Thanks for your help, Craig Ringer. – Username Aug 27 '12 at 01:54
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'])
hgmnz
  • 13,208
  • 4
  • 37
  • 41
  • 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. – Username Aug 30 '12 at 18:06
  • Where is 'DATABASE_URL' stored? – nutella_eater Sep 16 '16 at 09:06
  • @alexeypolusov heroku automatically takes care of that when you provision a database. Here is an example of provisioning postgresql on heroku: https://devcenter.heroku.com/articles/heroku-postgresql#provisioning-heroku-postgres – BenKoshy Jan 02 '20 at 04:49