How to execute .sql script on a hero?

Question

I have a .sql file with a bunch of paste commands that I want to execute in my postgres database on heroku. But I do not know how to do this: -

If I had access to the postgres console, I would type the following:

psql -h localhost -d database -U username -f datafile.sql 

but heroku doesn't seem to support this command. I tried using

 heroku pg:psql 

but this does not allow me to enter the file.

Are there any other options?

Answer 1

For things like sowing a database, I recommend Richard Brown answer: you might be better off using something like a Rails seed mechanism or something like a script, like a rake.

However, being able to process sql (raw or a file) is a useful feature, especially for idempotent things like simple search queries or regular queries. In this case, you can execute your local sql with any of the following actions:

 $ cat file.sql | heroku pg:psql --app app_name $ echo "select * from table;" | heroku pg:psql --app app_name $ heroku pg:psql --app app_name < file.sql 

Answer 2

Why not just use psql?

If you look at the result of heroku config , you will see the database URLs (key DATABASE_URL) that your application uses - if you take this and split them into the right bits for use with psql , all will be good.

eg,

 DATABASE_URL: postgres://username:password@host:port/dbname 

becomes

 psql -h host -p port -d dbname -U username -f datafile.sql 

Answer 3

I like updates that can be tested and played. When I need to update a database, I write a rake task to perform the update; thus, you can run it against the test first to ensure that the output is correct before starting work.

You did not mention whether this is the initial loading of the database or one start later, but the agreement to load fresh data into the Rails database is to create a rake db:seed file that can be executed after the db:migrate task is completed.

See: http://justinfrench.com/notebook/a-custom-rake-task-to-reset-and-seed-your-database AND: http://railscasts.com/episodes/179-seed-data