how to execute a .sql script on heroku?

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?

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

Why not just use psql?

If you look at the output of heroku config you will see the database URLs (DATABASE_URL key) your application is using - if you take this and break them apart in to the correct bits for using with the 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

I like updates that are testable and repeatable. When I need to update the database I write a rake task to perform the update; that way you can run it against test first to guarantee the output is correct before running in production.

You don't mention if this is an initial database load or one run later, but the convention for loading fresh data into a Rails database is to create a db:seed rake file that you can execute after your db:migrate task is done.

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