How to Execute a .SQL Script on Heroku

how to execute a .sql script on heroku?

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

How can I import a .sql file into my Heroku postgres database?

This is how you do it:

heroku pg:psql --app YOUR_APP_NAME_HERE < updates.sql

And if you want to restore your production into staging (assuming both are heroku postgres DBs):

heroku pgbackups:restore YOUR_STAGING_DATABASE_NAME `heroku pgbackups:url --app YOUR_PRODUCTION_APP_NAME` --app YOUR_STAGING_APP_NAME --confirm YOUR_STAGING_APP_NAME

Make sure to preserve the special single quotes around heroku pgbackups:url --app YOUR_PRODUCTION_APP_NAME.

---

HEROKU TOOLBELT UPDATE

Heroku has recently updated their toolbelt so the old commands are no longer valid (see this link for more info). Below is the new version of the restore command.

heroku pg:backups restore \
  `heroku pg:backups public-url -a YOUR_PRODUCTION_APP_NAME` \
  YOUR_STAGING_DATABASE_NAME \
  --app YOUR_STAGING_APP_NAME \
  --confirm YOUR_STAGING_APP_NAME

Heroku PSQL pass dynamic parameter to SQL file

You can get the URL to the database with the following heroku command:

$ heroku pg:credentials:url --app application_name

This will print something like:

Connection information for default credential.
Connection info string:
   "dbname=xyz host=something.compute.amazonaws.com port=1234 user=foobar password=p422w0rd sslmode=require"
Connection URL:
   postgres://foobar:p422w0rd@something.compute.amazonaws.com:1234/xyz

The URL (last line) can be used directly with psql. With grep we can get that line and pass it to psql:

$ psql $(heroku pg:credentials:url --app application_name | grep 'postgres://') \
    < ./somepath/file_to_execute.sql \
    -v param1="$File_name" \
    -v param2="$Tag_id" \
    -v param3="$job_name" \
    -v param4="$id"

Note that rather than putting single quotes in the parameter on the command line, you should access the parameter in the SQL as :'param1'.

SQL syntax error when executing MySQL script using Go during Heroku deployment

Most interfaces to run queries against MySQL do not support multi-query. In other words, they don't allow multiple SQL statements separated by semicolons, they only support one SQL statement per call.

In your case it returned a syntax error on USE ... because given that the input is parsed as a single statement, there is no USE in the CREATE DATABASE statement.

CREATE DATABASE assessment; USE assessment;

This means if you want to process a file of many SQL statements, you can't just do what you're doing and treat the entire file as one string to pass to a single call of Exec():

c, ioErr := ioutil.ReadFile("./schema.sql")
sqlScript := string(c)
...
_, err = db.Exec(sqlScript)

You must split the content of that file into individual SQL statements, and then loop over those statements, running each one at a time.

This is more complicated than it sounds, because:

• SQL scripts may contain a DELIMITER statement that changes the character between statements from semicolon to something else.

• There might be semicolons inside string literals or inside comments.

• Some statements such as CREATE PROCEDURE, CREATE TRIGGER, etc. may contain semicolons between statements in the body of the routine. You don't want these semicolons to be the end of the statement, you want to include all the content to the end of the routine definition.

• The DELIMITER statement itself can't be executed by the MySQL Server. It only control the client. So you must treat it as an exception that isn't sent to the server in your loop. In fact, there are a bunch of other mysql client builtin commands which must be treated similarly if you find them in an SQL script.

If you eventually code all this logic, you've basically reimplemented the MySQL command-line client in Go.

It would be far quicker and simpler if you skip past all that coding work, and just run the MySQL command-line client using os.exec.Command. Think about it — it'll save you weeks of coding work, duplicating all the nuanced features of running SQL scripts that is already implemented in the MySQL client.

SQL visual client to query a databse deployed at heroku

Take a look at http://www.pgadmin.org/ and install pgAdmin III. I use it for the same purpose, watch and query over the database of one of my Heroku apps.

How to use pg:psql in heroku?

You need a semicolon at the end of the query:

select * from users;

---

Related Topics