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

I'm going to suggest another approach, since I was already benefited from this question and I looked for better options to do that (in terms of speediness).

I conditioned the database seeding to an environment variable. With Sequelize is going to be something like this (the code bellow is only to demonstrate the conditioning):

// Node.js app
if (process.env.RESTARTDB) {
  sequelize.sync({ force: true }).then(() => {
    // "init" is a raw SQL query
    sequelize.query(init);
  });
}

This method is not recommended if you don't want to restart your app every time you want to query that SQL file, but it is so much faster than doing by using heroku pg:psql.

Here you can find more info on how to config your vars.