postgresql - how to execute a .sql script on heroku?

Question

I have a .sql file with a bunch of insert commands that I want to execute on my postgres database on heroku. But I don't know how to do it:-

If I had access to postgres console I'd type the following:

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

but it seems that heroku doesn't support this command. I've tried with

heroku pg:psql

but that doesn't let me input a file.

Are there any other options?

Answer 1

For things like seeding a database, I recommend Richard Brown's answer: you're arguably better off using something like Rails seeds mechanism, or something scripted like a rake task.

That said, being able to pipe sql (raw, or a file) is a useful feature, especially for idempotent things like simple look ups or routine queries. In which case you can execute your local sql with any of the following:

$ 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