Prerequisites

  • A Heroku account. You can create an account by clicking here or entering https://signup.heroku.com in your browser.

Heroku has a variety of plans to choose from, including a Free option. Check out Heroku’s Choosing the Right Heroku Postgres Plan article if you need some help selecting a plan.

Contact Heroku if you have questions about their pricing, product features, or support.


Step 1: Create a Heroku database

In this first step, you’ll create and provision a Heroku database.

  1. Sign into your Heroku account.
  2. Navigate to the Databases page by clicking the Options menu (the grid icon next to your avatar in the upper right corner) then Databases.
  3. Click the Create Database button in this screen.
  4. In the Plan prompt, select the plan you want to use.
  5. Select the Region for the database by clicking the Default Region drop-down menu.
  6. Click Add Database.

Heroku will begin the database provisioning process, which can take a few minutes. The status of your database will change to Available in the Database Dashboard page when things are complete:

Heroku Database Dashboard


Step 2: Locate the Heroku database connection settings

Next, you’ll locate the database settings in Heroku. This info will be used in the last section to connect Stitch to your Heroku-Postgres destination.

  1. On the Heroku Database Dashboard page, click the database you just created.
  2. The database settings and credentials will display. Everything you need is in the Connection Settings section of this page:

    Heroku Connection Settings

  3. Remember to click the Show link next to the Password field to retrieve the user’s password.

Leave this page open for now - you’ll need it to wrap things up.


Step 3: Grant the Heroku user CREATE permissions

Stitch requires CREATE permissions to create integration schemas and tables in your destination and load data. By default, Heroku credentials don’t include CREATE permissions, so you’ll need to grant them to the database user before continuing.

  1. Connect to your Heroku instance using a SQL client and the credentials you retrieved in Step 2.

  2. After connecting, you’ll assign the CREATE permissions to the user. This allows Stitch to create integration schemas and tables in the specified database.

    Run the command below, replacing <heroku_database_user> with the database username you retrieved in Step 2.

    GRANT CREATE ON DATABASE <database_name> TO <heroku_database_user>

In the table below are the database user privileges Stitch requires to connect to and load data into Heroku.

Privilege name Reason for requirement
CREATE ON DATABASE

Required to create the necessary database objects to load and store your data.

CREATE permissions on the database are required to successfully load data. When Stitch loads data, it will run a CREATE SCHEMA IF NOT EXISTS command, which will create a schema if it doesn’t already exist. To run this command, the Stitch user must have the CREATE ON DATABASE permission.

Note: The CREATE ON SCHEMA permission is not a sufficient alternative for CREATE ON DATABASE. As outlined in Heroku’s documentation, this permission only allows a user to create objects within a schema, but not the schema itself.

SELECT ON ALL TABLES IN information_schema

Required to select rows from tables in the information_schema schema. Prior to loading data, Stitch will use the data in this schema to verify the existence and structure of integration schemas and tables.

For Heroku destinations, access to the information_schema is granted by default to the main database user.

Note: Stitch will only ever read data from systems tables.

SELECT ON ALL TABLES IN pg_catalog

Required to select rows from tables in the pg_catalog schema. Prior to loading data, Stitch will use the data in this schema to verify the existence and structure of integration schemas and tables.

For Heroku destinations, access to the pg_catalog is granted by default to the main database user.

Note: Stitch will only ever read data from systems tables.


Step 4: Connect Stitch

Lastly, you’ll enter Heroku’s connection details into Stitch. When you do this, you’ll use the PostgreSQL destination option, as noted below.

  1. If you aren’t signed into your Stitch account, sign in now.
  2. Click the Destination tab.
  3. Click the PostgreSQL icon.
  4. Fill in the fields as follows:

    • Host (Endpoint): Paste the host of the Heroku database.

    • Port: Paste the port used by the Heroku database.

    • Username: Paste the username of the Heroku database user.

    • Password: Paste the password of the Heroku database user.

    • Database: Paste the name of the Heroku database.

    • Connect using SSL: Check this box. Heroku requires SSL to connect - if left unchecked, Stitch will be unable to connect to your Heroku database.

When finished, click Update PostgreSQL Settings.

Stitch will perform a connection test to the PostgreSQL database; if successful, a Success! mesage will display at the top of the screen. Note: This test may take a few minutes to complete.



Questions? Feedback?

Did this article help? If you have questions or feedback, feel free to submit a pull request with your suggestions, open an issue on GitHub, or reach out to us.