74

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?

Byron Singh
  • 1,408
  • 1
  • 13
  • 15

4 Answers4

130

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
catsby
  • 11,276
  • 3
  • 37
  • 37
  • 1
    Little note, the `--app app_name` is not required. I run `heroku pg:psql` from the directory my heroku app is located and it detects the app name. Besides that, this works great! – levibostian Aug 28 '15 at 02:32
  • 1
    @levibostian A git repo can be connected to multiple Heroku apps thourgh – leo Jun 10 '20 at 13:37
  • @leo you may be right and you may be wrong, I am not sure. I have not used heroku is a handful of years. I can no longer help this question or my comment. – levibostian Jun 10 '20 at 17:18
26

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
John Beynon
  • 37,398
  • 8
  • 88
  • 97
3

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

Richard Brown
  • 11,346
  • 4
  • 32
  • 43
  • The link says: _"A custom Rake task to reset and seed your database" you're looking for has been removed_ – chomp Oct 04 '17 at 18:39
  • That happens to old links. Archive.org to the rescue: http://web.archive.org/web/20130312172900/http://justinfrench.com/notebook/a-custom-rake-task-to-reset-and-seed-your-database – Richard Brown Oct 05 '17 at 20:28
0

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.

mdmundo
  • 1,988
  • 2
  • 23
  • 37