Heroku Connect

Last updated 28 September 2016

Heroku Connect is an add-on that synchronizes data between your Salesforce organization and a Heroku Postgres database. Using Heroku Connect with Heroku Postgres, you can build applications that interact with your Salesforce data using your favorite tools, including all languages and frameworks supported by Heroku.

How Heroku Connect works

Heroku Connect operates by synchronizing data between all records of a standard or custom Salesforce object and a Heroku Postgres database table using the identical schema. The schema is defined by the Salesforce system.

Heroku Connect creates each database table on demand as an exact replica of the Salesforce object. Table and column names are the same between Salesforce and the database, except the database uses only lowercase letters. Whenever data changes in Salesforce, those changes are applied to the database table. Similarly, any changes made to data in the database table can be synchronized to Salesforce.

Heroku Connect uses SystemModStamp to detect changes to Salesforce objects selected for synchronization from Salesforce to the database. Objects without that attribute can be read from Salesforce, but not sync’d. Heroku Connect uses database triggers to record changes to be sent from the Postgres database to Salesforce.

Heroku Connect implements a “last writer wins” scheme where the latest update on either side is propagated to the other. All Salesforce changes will be written to the database. All updates to the database will be written back to Salesforce subject to user permissions and validation rules in effect within Salesforce.

Heroku Connect does not support in­-flight transformations. Instead, data transformations can be performed inside the relational database using standard SQL or via any application that can connect to the Heroku Postgres database.

Provisioning the add-on

To quickly create a new application, database and Heroku Connect instance use the Heroku button. To add Heroku Connect to an existing application you can either use the Heroku Dashboard or the Heroku CLI.

Available plans

There are two plans for Heroku Connect:

• demo

This is the default plan when provisioning the add-on and provides access to all features of Heroku Connect with the following limitations:

• A maximum of 10,000 synchronized rows across all mappings.
• A minimum polling interval of 10 minutes.

• The trigger log is archived for 7 days instead of 31 days.

  • danketsu

This is the paid plan that you will use after purchasing Heroku Connect. If you have existing add-ons using the demo plan they will be automatically upgraded to danketsu if you are entitled to use it. Even if you provision a ‘demo’ plan, it will automatically upgrade to danketsu if you are entitled to use the higher plan level.

Please contact sales for more information on the Heroku Connect danketsu plan.

You can use the heroku addons command to see if your application already has Heroku Connect provisioned and the current plan:

$ heroku addons | grep herokuconnect
herokuconnect (rolling-gladly-8321)  demo   free

Create a new instance

Heroku Connect can be attached to your application using:

$ heroku addons:create herokuconnect
Creating rolling-gladly-8321... done, (free)
Adding rolling-gladly-8321 to sleepy-ocean-5276... done
Use `heroku addons:open herokuconnect` to finish setup
Use `heroku addons:docs herokuconnect` to view documentation.

When you provision the add-on a globally unique identifier will automatically be generated: in the above example the unique identifier is rolling-gladly-8321. You can specify your own unique identifier by passing the --name flag to the addons:create command.

If you have purchased Heroku Connect you can specify the danketsu plan when adding the add-on to your application using:

$ heroku addons:create herokuconnect:danketsu

When Heroku Connect is provisioned to an app in a Heroku Private Space, the default region is US for all supported Private Space regions except Frankfurt. Instances in Frankfurt will default to EU.

Completing configuration

When the Heroku Connect add-on has been attached to your application you can complete configuration by navigating to the add-on from the Heroku Dashboard or from the CLI:

$ heroku addons:open herokuconnect

In order to complete the configuration you will need a Postgres database attached to your application that Heroku Connect can use to store data for your mapped Salesforce objects.

Heroku Connect supports all Heroku Postgres plan types: standard, premium and private. Heroku Connect can be used to sync data between a Salesforce org and a Heroku Postgres database inside or outside a Heroku Private Space.

Please carefully consider the overall architecture of any solution which employs a Heroku Connect, Heroku Postgres, and Salesforce org distributed across divergent geographic locations. Network latency can become a factor in service performance.

To complete configuration of Heroku Connect click the Setup Connection button in the dashboard.

• Choose a Heroku Postgres database that is attached to your application.

If you have multiple databases attached to your application you can choose the one you want Heroku Connect to use. The database specified by the DATABASE_URL config var will be selected by default.

You can also choose the Postgres schema to be used for the tables created by Heroku Connect. The default is salesforce however you can choose to put your tables in an existing schema, including public, as long as no tables exist in the schema.

Click Next to setup the database.

• You then need to authenticate to your Salesforce organization: you can choose to authenticate to a production or sandbox organization or you can choose to use a custom login domain. Click the Authorize button and you will be taken to Salesforce to enter your login credentials and authorize Heroku Connect to have access to your data.

You have the option to select the Salesforce API version you want Heroku Connect to use. Once selected, the API version cannot be changed. It is straightforward to create a second instance using a different API version with the same configuration should you wish to change API version at a later date.

It is recommended that you use a dedicated integration user for Heroku Connect with the necessary permissions to allow for successful synchronization (for example the most permissive option is to make the integration user a System Administrator).

Heroku Connect typically requires View All Data permission and it may also be necessary to grant Modify All Data permission when dealing with relationships between objects. Both the user and the Salesforce organization will also need to have API Enabled permission granted.

Without the View All Data permission, Salesforce has to check permissions for each row individually. This can make many of Connect’s operations much slower. For larger connections, it can also result in OPERATION_TOO_LARGE errors that cause sync to fail altogether.

After you have authenticated to your Salesforce organization you can start mapping objects: to begin the process click the Create Mapping button on the Overview tab of the Heroku Connect dashboard.

Multiple add-ons per application

It is possible to add multiple Heroku Connect add-ons to a single application: for example you may wish to synchronize data from multiple Salesforce organizations and one way to achieve this is to have multiple add-ons each authorized and configured to work with different organizations.

It may also be desirable to use multiple add-ons per application, each with their own dedicated and distinct integration user to the same Salesforce org, if your intended sync pattern is to sync hundreds of objects from a single org into a single Postgres DB. There are concurrency limits on long running SOAP queries for a single user which can slow sync if a single integration user is employed for hundreds of objects, or conflict with other operations the same integration user identity is likewise performing.

When multiple Heroku Connect add-ons exist it is necessary to use the globally unique identifier assigned to the add-on when running commands from the CLI. For example to open the Heroku Connect dashboard:

$ heroku addons:open rolling-gladly-8321

Config vars

After configuration is complete the HEROKUCONNECT_URL config var will be added to your application. It contains the name of the config var and schema name for the database connection used by Heroku Connect.

$ heroku config:get HEROKUCONNECT_URL
DATABASE_URL:salesforce

If you have multiple Heroku Connect add-ons attached to your application the first add-on will use the HEROKUCONNECT_URL config var and subsequent add-ons will use HEROKUCONNECT_COLOR_URL config vars, where each add-on has its own color, to store database connection information. For example:

$ heroku config | grep HEROKUCONNECT
HEROKUCONNECT_ORANGE_URL:     DATABASE_URL:salesforce_secondary
HEROKUCONNECT_URL:            DATABASE_URL:salesforce_primary

Upgrading the add-on plan

You can upgrade from the free demo to the paid danketsu plan using the Edit plan option in the Heroku Dashboard or using the CLI, assuming you are operating inside a Heroku organization with a paid plan entitlement:

$ heroku addons:upgrade herokuconnect:danketsu
WARNING: No add-on name specified (see `heroku help addons:upgrade`)
Finding add-on from service herokuconnect on app sleepy-ocean-5276... done
Found rolling-gladly-8321 (herokuconnect:demo) on sleepy-ocean-5276.
Changing rolling-gladly-8321 plan to herokuconnect:danketsu... done, (free)
Plan change successful

If you have multiple Heroku Connect add-ons attached to your application you will also need to specify the globally unique identifier assigned to the add-on when running the upgrade command:

$ heroku addons:upgrade rolling-gladly-8321 herokuconnect:danketsu
Changing rolling-gladly-8321 plan to herokuconnect:danketsu... done, (free)
Plan change successful

The Heroku Connect dashboard

The Heroku Connect dashboard allows you to configure, monitor and troubleshoot your mappings.

The Heroku Connect dashboard is available to any member or collaborator on your application. See Collaborating with Other Developers on Your App for more information on how to manage the users who have access to your application.

Overview tab

The Overview tab allows you to get a real-time overview of your mappings as well as providing links to create new and edit existing mappings.

There is also a chart showing the number of rows written to Salesforce in blue at the top and the number of rows read from Salesforce in purple at the bottom. The number of errors for reads and writes are also shown in orange on each chart. You can highlight a data point to see the specific number of rows and errors and you can change the time period shown using the drop-down list to the top-right of the chart.

Logs tab

The Logs tab allows you to view detailed information about the synchronization operations being performed by Heroku Connect. You can filter by mapped object, log type and detail and by date.

Explorer tab

The Explorer tab provides access to Sync Explorer: a tool that allows you to get detailed information on the status of individual rows for each mapping.

You can filter by mapping and synchronization state (see Status information for details) and can search for text contained in the id, sfid, _hc_err and name fields.

Sync Explorer also allows you to view a side-by-side comparison of the data held in Salesforce and in the mapped database table.

Sync Explorer, used in conjunction with the Logs tab, is an invaluable tool to help you understand the operations being performed by Heroku Connect and enables you to diagnose and fix synchronization issues that can occur.

The most common synchronization error that occurs when reading is insufficient permissions to read the mapped object. The most common synchronization error that occurs when writing is insufficient write permissions for the target Salesforce org.

Settings tab

The Settings tab contains a drop-down list where you can choose:

• Manage Connection

  From here you can Pause or Restart the connection, view your plan type, view the list of users with access to the dashboard, check for orphaned mappings, enable Logplex logging, enable the event log and delete the connection.

• Manage Salesforce

  This page shows your current connection details (Salesforce organization ID, authenticating user and environment). You can also re-authorize the user credentials used to access Salesforce and view your current Salesforce storage usage.

• Database

The Database page summarizes the Heroku Postgres database settings used by Heroku Connect, including the database and schema names, host and port, as well as providing useful command line examples showing how to connect to the database using PSQL.

• Import/Export Configuration

It is possible to export your existing mapping configuration to or import a new configuration from a JSON file. This can be helpful when managing your Salesforce organizations.

Mapping objects

Creating a mapping

To create a new mapping:

1. Click the Create Mapping button on the Overview tab of the Heroku Connect dashboard.

2. You will see a list of objects that can be mapped from your Salesforce organization. Use the search box to filter the list and then click on the object that you want to map.

1. You will now be able to choose the synchronization mode and the fields that you want to map. Some fields in the list are pre-selected and cannot be removed from the mapping (such as Id and SystemModStamp) as they are required for Heroku Connect to perform synchronization.

1. Click Save and Heroku Connect will immediately create a table in your database corresponding to the Salesforce object, query for all records from Salesforce and insert them into the mapping table.

Editing a mapping

To edit an existing mapping:

1. Click on the object name in the Mappings list on the Overview tab of the Heroku Connect dashboard.
2. On the mapping detail page click the Edit button.
3. You will now be able to edit the synchronization mode as well as adding and removing fields from the mapping.
4. Click Save and Heroku Connect will update the mapped database table to add and remove columns and query Salesforce for data for any new fields to update existing records in the database.

Synchronization mode

When creating or editing a mapping you can also configure the method used to synchronize data with your Salesforce organization: polling or streaming.

Mappings are read-only by default: Heroku Connect will poll your Salesforce organization for changes and synchronize them to your database; however, changes made in your database will not be synchronized back to Salesforce.

To enable changes made in your database to synchronized back to your Salesforce organization tick the box in the Database → Salesforce section of the create/edit mapping page.

Polling mode

By default Heroku Connect will poll your Salesforce organization for changes every 10 minutes. You can configure the polling interval from two minutes (10 minutes in the demo plan) to 60 minutes in one minute increments. A shorter polling interval is suitable for mappings where data changes frequently and a longer polling interval is best for mappings that change less often as it reduces API usage and processing overheads.

Streaming mode

Streaming mode makes use of the Streaming API to notify Heroku Connect when data changes in your mapped objects. Heroku Connect makes use of these notifications to trigger polling, however it does not rely on the notifications to determine what data has changed, instead it uses the same method as polling mode. You can think of streaming mode as 'polling on demand’ whereas polling mode is 'polling at a fixed interval’.

In order to reduce API utilization Heroku Connect will not poll for changes more often than once every 10 seconds regardless of the number of notifications received. In addition if no notifications are received within the configured polling interval then Heroku Connect will automatically poll for changes: this is to ensure that your mappings remain in sync even if notifications fail to be received.

Salesforce integration

Heroku Connect employs a set of best practices that take into account data change volume and the many subtle details of Salesforce API operation to automatically choose the most efficient method to transfer data between your database and your Salesforce organization.

Salesforce offers a variety of APIs that are best suited to specific integration requirements and Heroku Connect utilizes two of them for data transfer: SOAP and Bulk. In addition the Streaming API is used to configure Push Topic notifications for mappings that use streaming mode.

SOAP API

Heroku Connect primarily uses the SOAP API for interactions with your Salesforce organization. The API has been optimized for operations involving a small number of records and is therefore used by Heroku Connect for the following tasks when there are up to 50,000 records to be processed:

• initially loading data from your Salesforce organization for a new mapping
• reloading all data into an existing mapping
• reading changes from your Salesforce organization

The SOAP API is also used for:

• writing changes to your Salesforce organization regardless of the number of records involved
• administrative tasks such as counting records and querying for mapping fields

Bulk API

The Bulk API is optimized for loading large sets of data making use of asynchronous processing to retrieve batches of records in parallel. Heroku Connect makes use of the Bulk API for the following tasks when there are more than 50,000 records to be processed:

• initially loading data from your Salesforce organization for a new mapping
• reloading all data into an existing mapping
• reading changes from your Salesforce organization

You can check your current Bulk API usage in Salesforce:

• Login to your Salesforce organization
• Go to Setup and type Bulk data in the Quick Find box
• Click on Bulk Data Load Jobs
• The Quota summary will show how many batches have been processed in the last 24 hours.

Streaming API

When you choose streaming mode for a mapping, Heroku Connect uses the Streaming API to create a Push Topic that will send notifications to trigger polling when data is changed in Salesforce.

Push Topics created by Heroku Connect have a hc_ naming prefix, for example hc_123, and will automatically be deleted either when you switch to polling mode or delete the mapping.

The following API limits need to be considered when configuring a mapping to use streaming mode:

• Push Topics created by Heroku Connect count towards the 'Maximum number of topics (PushTopic records) per organization’.
• Notifcations received by Heroku Connect count towards the 'Maximum number of events per day (24–hour period)’.

You can check your current notification usage in Salesforce:

• Login to your Salesforce organization
• Go to Setup and type Company Information in the Quick Find box
• Click on Company Information
• Streaming API Events, Last 24 Hours shows the number of notifications received and the maximum allowed

API call usage

The number of API calls used by Heroku Connect will depend on a number of factors:

• the total number of mappings
• the directionality of each mapping (read only or read/write)
• the synchronization mode of each mapping (polling or streaming) (see synchronization mode)

Heroku Connect will primarily make use of the SOAP API, only switching to the Bulk API for read operations involving over 50,000 records. Bulk API calls make use of multiple asynchronously processed batches of 150,000 records. As an example, Heroku Connect will use the following API calls to perform a load of one million records:

• 1x SOAP API call to retrieve the number of records
  this call will not count towards your daily SOAP API limit
• 7x Bulk API calls to retrieve the records in batches of 150,000
  these calls will count towards your daily Bulk API limit

Heroku Connect will exclusively use the SOAP API for writing to Salesforce in batches of 200 records: for example writing 10,000 records will require 50 SOAP API calls.

Reducing Bulk API calls

As Bulk API calls do count towards your daily limit and SOAP API calls do not it can be preferable to reduce the number of Bulk API calls made by Heroku Connect. For mappings that have a high data change volume then reducing the polling interval or switching to streaming mode (see synchronization mode) is a good way to achieve this.

However it is not recommended to simply set all mappings to streaming mode or to the minimum polling interval: you should choose settings that reflect the data change volumes of each mapping. Reducing the polling interval will also increase the load on your database as Heroku Connect will update the mapped table more often.

The first step in identifying usage of the Bulk API is to make use of the logs:

• navigate to the Logs tab
• select a mapping from the Object list
• ensure that the Log Type is Events
• select either All or Info for the Detail level

If you see the message Too many changes, falling back to Bulk API on a regular basis then the mapping is a good candidate for a reduced polling interval or streaming mode.

Missed Updates Due to Salesforce Transactions

It is possible that a small set of records may be locked in a transaction at the time of a poll and therefore not available to be synced. Heroku Connect handles this by performing a poll for Salesforce IDs that should have been included 2 minutes after successful poll completion. Any missed records will automatically be synced when detected. This “insurance” poll reconciles reconciles records in flight at time of last sync operation.

Database table structure

When you map objects Heroku Connect will create or update the database tables used to store data for the mapped object.

• Mapping tables use a lowercase version of the Salesforce object name, for example the Account Salesforce object is mapped to the account database table.

• Column names use a lowercase version of the Salesforce field name, for example the AccountNumber Salesforce field is mapped to the accountnumber database column.

• Creating a new mapping creates a new database table that Heroku Connect will automatically populate with data from Salesforce.

• Editing an existing mapping will modify the existing database table using ALTER TABLE SQL commands. Heroku Connect will populate any newly mapped fields with data from Salesforce.

System columns

In addition to the Salesforce fields you choose to map to database columns, Heroku Connect will automatically add the following system columns to the mapped tables:

Status information

The _hc_lastop and _hc_err columns can be used to aid in debugging or to surface sync status information in your application.

The _hc_lastop column will initially be blank before being updated to one of the following statuses as part of the sync process:

• PENDING - a new row in the database is awaiting sync to Salesforce.

• INSERTED - a new row in the database has been inserted into Salesforce: at this point the sfid will also be populated.

• UPDATED - an existing row in the database has been successfully updated in Salesforce.

• SYNCED - a new row has been synchronized from Salesforce.

• FAILED - Salesforce synchronization was unsuccessful.

When a failure occurs the _hc_err column will contain a JSON object with the following properties:

Mapped data types

The table below shows how Salesforce field types are mapped to columns in the database.

Encrypted strings

If the user credentials used to authorize Heroku Connect with Salesforce don’t have View Encrypted Data permission, then encrypted strings will be received from Salesforce in masked format.

It is possible to update the database with a new plain text value and Salesforce will take care of encryption when the new data is pushed from the database. The plain text value in the database will be overwritten with the masked format when the record is next updated with data from Salesforce.

Cross-object formula and roll-up summary fields

Formula and roll-up summary fields are calculated by Salesforce at query-time and can make use of other fields, functions and literal values. They can also refer to fields in parent objects via master-detail or lookup relationships: however this can cause problems when Heroku Connect is synchronizing data.

For example:

• The Contact object defines a formula field called name_formula__c that refers to the Name field of the related Account object
• The Name field of the related Account object is updated
• The value of name_formula__c reflects the change when the Contact object is queried
• The SystemModStamp of the Contact object is not changed therefore the new name_formula__c value is not synchronized to the database

Unsupported data types

The following types are not supported and will not be shown in the Mapped Fields list when creating or editing a mapping.

Base64 binary fields

Binary files stored in Attachment, Document and Scontrol objects use a Base64 encoded binary data type. This data type is currently unsupported in the Bulk API and is therefore also unsupported by Heroku Connect.

Compound fields

The Address and Geolocation compound field types cannot be mapped directly however it is possible to add the component fields to a mapping.

For example the Contact object has a MailingAddress Address field that can be mapped using the following fields:

Similarly a custom Geolocation field named OfficeLocation would be mapped using the following fields:

Querying mapped tables

Mapped tables can be queried like any other table in your Postgres database. You will need to qualify table names with the schema name you chose when provisioning the add-on, for example using the default salesforce schema name:

SELECT * FROM salesforce.account;

You may prefer to add the schema to the Postgres schema search path to allow tables to be queried without requiring fully qualified names, for example:

SET search_path TO salesforce,public;
SELECT * FROM account;

System tables

Heroku Connect creates several system tables within the schema:

• _sf_event_log

Updates received from Salesforce are logged in this table when the event log is enabled. The _sf_event_log includes details about the type of change, the time it occurred, the Salesforce ID of the record and a JSON representation of the changes.

• _sf_org_id

This table stores the unique Salesforce organization ID that Heroku Connect is currently authorized with.

• _trigger_log

The _trigger_log table is used to record updates that need to be written to Salesforce. Data remains in this table for up to one day allowing you to track updates that are pending, in progress or have already completed. If an error occurs while writing to Salesforce it will be logged in this table.

• _trigger_log_archive

Once data in the _trigger_log table is more than a day old it is moved into this archive table where it will be available for up to 31 days. Note: the archive is only available for 7 days demo plan.

• _trigger_last_id

This table stores the ID of the last row synchronized with Salesforce.

Resolving read errors

Heroku Connect requires “View All Data” permissions, or “View all Data” scoped to the mapped objects, for optimal operation. For Salesforce organizations with large record sets, some operations may not be possible without those permissions. The system will notify members or collaborators on your application if an operation requiring those permissions fails without them.

Heroku Connect supports read of all standard and custom objects. Knowledge Base objects, and standard or custom objects requiring special API handling or without required attributes such as SystemModStamp are not mappable. Heroku Connect attempts to detect and suppress these unmappable objects from presentation in the UI.

1. If Heroku Connect cannot query for a mapped object, this error or similar will display in the logs: 'INVALID_TYPE_FOR_OPERATION…’

2. If Heroku Connect cannot map an object because it requires object specific filters, this error or similar will display in the logs: 'MALFORMED_QUERY…’

3. If Heroku Connect cannot map an object because it lacks a required attribute, this error or similar will display in the logs: 'Cannot poll changes for table ’>fieldname<’ because it contains neither…’

Remove the ineligible object or field from the mapping to resolve these errors.

Updating data in Salesforce

By default your mappings will be in Read Only mode. Changes to the mapped database table will synchronize only after a switch to Read/Write mode.

On the Overview tab, click on the name of a mapped object and then click on the Edit button to view the Edit Mapping page. Ticking the Write to Salesforce any updates to your database box will ensure that all future updates are sent to Salesforce.

Heroku Connect will add database triggers to the mapped database table that will record all operations into the special table _trigger_log. Those changes will be detected by Heroku Connect and sent back to Salesforce.

To update Salesforce records, just execute any UPDATE table (cols) values (vals) statement against your database. Updates will be sent to Salesforce. Inserting records directly into the _trigger_log table is not supported.

Updates to Salesforce via Heroku Connect are attempted once. Each attempt to update a record may end in one of these states: SUCCESS, MERGED, IGNORED or FAILED.

• SUCCESS means the row was succesfully written to Salesforce.
• MERGED means that several changes were made to a single row within a single polling interval. When this happens, all of the changes will be merged into a single API request to Salesforce.
• IGNORED means that Connect did not attempt to write the record to Salesforce. This can happen if you update a record but don’t actually change any data that needs to get synced back to Salesforce.
• FAILED means that Connect tried to write the record, but failed. The sf_message column should contain an explanation for why it failed.

The _trigger_log table contents will be automatically archived after 24 hours.

Inserting records to Salesforce

To insert new records to Salesforce, just insert rows in your database tables. Note that the native Salesforce key (the 18 character Salesforce Id column) will be updated on your new row after the insert operation is complete.

Heroku Connect does not support Salesforce upsert operations. If you insert a record, such as a Contact, into Postgres, and then update that record before the insert is sent to Salesforce, Heroku Connect will merge updates that reference the same id into a single INSERT operation to Salesforce. The most recent value for any given field will persist.

This also works for DELETE, so if you insert a record and delete it immediately afterward, it will never be sent to Salesforce at all.

One caveat is that a record that has been through a merge prior to insertion into Salesforce will not trigger any workflow in Salesforce based on an intermediate state that was not written to Salesforce. For example, if you write a Lead object to your database with a state of “New” and then immediately update that database record to “Converted”, the record will just be written to Salesforce as “Converted”, and any workflows based on a Lead moving from “New” to “Converted” will not run.

After the initial insert has completed, and the SFID has been returned and associated with the record in the database, subsequent updates to that database record will be treated as updates to Salesforce.

If you plan to write objects to your database, we strongly recommend adding an External ID field to your object and populating it with a unique value (such as a GUID) when you first INSERT the record. This can be used to establish relationships between objects, as described below. It also helps protect against possible duplicate records. It’s important not to change the value of that ID after writing it to a record.

Relationships between objects

Heroku Connect supports master-detail and lookup relationships between objects using either Salesforce IDs or External IDs.

• To use Salesforce IDs you simply add the relevant foreign key fields to your mappings and write the Salesforce IDs to those mapped fields when creating or updating records. Heroku Connect will add an index to these fields in the database.
• External IDs provide a useful alternative in situations where, at the time of writing data to your database, you don’t have the Salesforce IDs to establish these relationships. An External ID can be a string or integer that can be used to identify records: for example you may choose to use a Postgres Sequence.

Master-detail relationships using External IDs

In order for Heroku Connect to support master-detail relationships using External IDs you need to add a custom text or number field to the master object in Salesforce and ensure that the External ID option is selected. You can then configure your mappings in Heroku Connect to include this field:

• Add the External ID field (e.g. ExternalId__c) to the master object mapping
• Add the related External ID field to the detail object mapping: it will be shown at the bottom of the field list and will have the form <ObjectName>__<FieldName>__c e.g. Account__ExternalId__c

To establish a relationship between two objects using the External ID fields you need to:

• Set the External ID field (e.g. externalid__c) in your master record to a unique value e.g. '1234’
• Set the related External ID field (e.g. account__externalid__c) in your detail record to the same value

Heroku Connect automatically re-orders and groups related records, writing all master records before writing detail records, in order to allow the relationship between objects to be established in Salesforce.

Example

A common use of a master-detail relationship using External IDs is to allow the creation of a new Account and related Contact object at the same time.

In this example the Account is the master object and the Contact is the detail object and as they both are being created by inserting new records into the database, neither will have a Salesforce ID available that could be used to establish the relationship between them.

1. Add a new custom integer field to the Account object in Salesforce named ExternalId and ensure the External ID option is selected
2. Create a new mapping for the Account object in Heroku Connect that includes the ExternalId__c field
3. Create a new mapping for the Contact object in Heroku Connect that includes the Account__ExternalId__c field that will be found at the bottom of the field list
4. Insert a new record into the mapped database table for Account ensuring the externalid__c field is set to a unique value, e.g. '1234’
5. Insert a new record into the mapped database table for Contact setting the account__externalid__c field to the same value you used in step 4

When the Account and Contact records are subsequently written to Salesforce they will automatically be related using the matching External ID value. Both records in the database will also be assigned Salesforce IDs in the same way unrelated objects normally are.

Lookup relationships using External IDs

Heroku Connect also supports lookup relationships where a parent object is related to a child using External IDs.

• In Salesforce:
  • Add a text or number custom field (e.g. ExternalId) to the parent object and ensure that the External ID option is selected
  • Add a new Lookup Relationship custom field (e.g. RelatedObject) to the child object that relates it to the parent object
• In Heroku Connect:
  • Add the External ID field (e.g. ExternalId__c) to the parent object mapping
  • Add the related External ID field to the child object mapping: it will be shown at the bottom of the field list and will have the form <ChildFieldName>__r__<ParentFieldName>__c e.g. RelatedObject__r__ExternalId__c

To establish a relationship between two objects using the External ID fields you need to:

• Set the External ID field (e.g. externalid__c) in your parent record to a unique value e.g. '1234’
• Set the related External ID field (e.g. relatedobject__r__externalid__c) in your child record to the same value

Related External ID field names

In May 2015 Heroku Connect added support for multiple lookup relationships from a child object to the same type of parent object and this involved a change to the naming convention for External IDs in the child mapping.

The old naming convention will continue to work correctly for mappings created before this change however you should update existing mappings to use the new naming convention as soon as possible to ensure they continue to work correctly in the future.

For example if you need to add an additional lookup relationship you will also need to update the mapped field for the existing lookup relationship to use the new field name.

• A Contact object has a lookup relationship to the Person__c custom object named PrimaryPerson
• The Person__c object has an External ID field named ExternalId
• The Person__c and Contact objects were both mapped in Heroku Connect before May 2015
• The Person__c mapping includes the ExternalId__c field
• The Contact mapping includes the related External ID field named Person__c__ExternalId__c (this is the old naming convention)

To add a new SecondaryPerson lookup relationship to the Contact mapping you need to make the following changes in Heroku Connect:

1. Edit the Contact mapping, remove the related external ID field named Person__c__ExternalId__c and save the changes
2. Go back to edit the Contact mapping, add the PrimaryPerson__r__ExternalId__c and SecondaryPerson__r__ExternalId__c fields and save the changes
3. Update any dependencies on the old person__c__externalid__c field in your application code to use the new primaryperson__r__externalid__c field name.

Polymorphic relationships using External IDs

A polymorphic relationship is a relationship where the referenced objects can be one of several different object types: for example an Event object can be related to an Account, or a Campaign, or an Opportunity through the What polymorphic relationship.

Two pieces of data are required to allow Salesforce to identify the related object in a polymorphic relationship: the object ID and the object type.

This includes External ID fields on the What and Who polymorphic relationships defined on Event and Task objects: these fields will not be available when creating and editing your mappings.

Resolving write errors

Database updates may fail to be propagated to Salesforce in the case that a record insert or update fails due to a Salesforce validation error or due to lack of write permission for the authenticating user.

In this case the error state and message are recorded in the database and reported through the Heroku Connect dashboard under the Logs tab. Here you can filter the logs to only show information for a specific mapped object. Your application should include logic to apply a corrected update or to delete the insert.

A common write error is the attempt to update a value for a readable, but non writeable per Salesforce validation rules, field. An example: Contact’s “Name” where “Name” is a compilation “First Name” and “Last Name” values. “First Name” and “Last Name” are the writable values. “Name” is a display value.

Salesforce schema changes

Adding fields

Adding new fields to objects in Salesforce will not impact Heroku Connect sync operations. If you choose to add the new field to an existing mapping Heroku Connect will automatically create a column in the mapped database table, retrieve data for the field from Salesforce and populate existing records in the database with that data.

Changing fields

If you need to change a field definition you should:

1. Edit the mapping and remove the field you will be changing.
2. Change the field definition in Salesforce.
3. Edit the mapping and add back the field you removed in step 1: the data type for the field should now reflect the changes you made in step 2.

Removing fields

When removing a field from a Salesforce object it is best practice to first remove the field from your mapping in Heroku Connect. If you choose to remove the field from the Salesforce object before removing it from your mapping then Heroku Connect will report an error the next time the mapping is polled for changes. To resolve the error you will need to edit the mapping and remove the missing fields: they will be highlighted in red. When you save the changes Heroku Connect will remove the columns from the mapped table and sync operations should resume.

Pausing synchronization

When performing certain tasks, such as upgrading your database, you may want to pause synchronization activity in Heroku Connect. To do this navigate to the Settings tab, Manage Connection and click the Pause button.

Heroku Connect will complete any pending operations before entering the paused state. When your connection is paused then changes to data in the database will continue to be added to the trigger log and changes made in Salesforce will not be polled. Push Topic notifications from for mappings using the streaming mode will also be ignored.

To resume synchronization return to the Settings tab, Manage Connection and click the Resume button. Heroku Connect will enter a recovery state: entries in the trigger log will be processed and pushed to Salesforce and changes made in Salesforce will be retrieved using polling (including those mappings that would normally use the Streaming API).

Salesforce organizations

As part of the provisioning process for Heroku Connect you will be required to authenticate to a Salesforce organization.

When you authenticate Heroku Connect to an organization then the mappings you create are specific to that organization: this includes both the structure of the underlying database tables and the data that is synchronized between Salesforce and the database. In order to reduce the potential for data conflict and corruption it is not possible to re-authenticate an existing Heroku Connect connection to a different Salesforce organization.

If you want to synchronize data from a new production or a refreshed sandbox organization you will need to:

1. Export your current configuration to a JSON file.

Navigate to the Settings tab, choose Import/Export Configuration, click the Export button and then on the confirmation page click Export to download the file.

1. Remove and re-provision the Heroku Connect add-on authenticating to the new Salesforce organization.

2. Import the JSON configuration file.

Navigate to the Settings tab, choose Import/Export Configuration, click the Import button and then click Choose file to locate the file to upload. Click the Upload button to begin the import.

On successful completion of the import Heroku Connect will begin syncing data from your new Salesforce organization into the database.

Login IP restrictions

If your Salesforce organization has org-wide or profile-based IP restrictions, you will need to extend your range of allowed IP addresses to include Heroku Connect. Please contact support if you need assistance with your IP ranges.

You can verify that Heroku Connect is logging in successfully by viewing the Login History for your Salesforce organization. You should see entries where the Username matches the email address you have used to authenticate and the Application is either Heroku Connect or Heroku Connect EU depending on the region where your instance is located.

Upgrading your database

Heroku Connect operates continuously on a single database, which requires a few steps in order to upgrade that database and allow sync operations to continue as expected. The upgrade itself is done using a follower database, and these instructions illustrate how Heroku Connect fits into that process.

1. Create your follower database and allow it to get mostly caught up with your lead database. This will minimize the amount of downtime required for the upgrade.

2. Pause your connection for the same reason that you put your application into maintenance mode, to prevent Heroku Connect from trying to write to your database during the upgrade.

3. Wait for your follower to catch up. Now that Heroku Connect and your application have both stopped writing to your database, the follower can get completely caught up. You can check its status using heroku pg:info.

4. Promote the follower to become the new lead database. This will make it accessible as DATABASE_URL.

5. Resume Heroku Connect using the same process used to pause it in step 2, and bring your application out of maintenance mode. Heroku Connect will automatically pick up the new DATABASE_URL for your application and resume synchronization using the new database.

Notifications

When errors occur that require user intervention Heroku Connect sends notifications to all users that have chosen to receive them. To view a list of users that will receive notifications navigate to the Settings tab, Manage Connection in the dashboard: the Access section lists users who have access to the Connect dashboard and is where the Notifications Enabled setting can be configured.

You will receive notifications for the errors summarized below. The notification message describes the error that has occurred and includes helpful information so that you can understand and resolve the problem. You will also receive notifications when an error that occurred previously has been resolved.

Bad configuration

A bad configuration error can occur when changes to the Salesforce object or Heroku Postgres table used by a mapping are changed. The mapping will not be synchronized until the error is resolved however other mappings should continue to synchronize as normal.

Change user permissions

This error occurs when the user credentials you use to authenticate to Salesforce don’t have the correct permissions to allow Heroku Connect to efficiently synchronize a large number of records. Re-authenticating with different Salesforce user credentials or modifying the permissions of the current user will resolve the error.

Connection error

A connection error can occur for a number of reasons and may prevent synchronization from occurring on one or more of your mappings. You will be asked to check the logs in the Heroku Connect dashboard in order to identify the exact nature of the error.

Database unavailable

If the Heroku Postgres database cannot be reached then Heroku Connect will be unable to synchronize any mappings. You will need to review your database configuration in order to resolve this problem.

Reauthorization required

If the user credentials used to authorize Heroku Connect with Salesforce expire or change then synchronization will stop for all mappings and you will need to reauthorize with Salesforce in order for it to restart.

Logplex logging

Heroku Connect can emit log messages to Logplex making them available to your application via the CLI or using logging add-ons. To enable Logplex logging navigate to Settings, Manage Connection and check the Enable Logplex Log option.

Log entries follow the standard log format with a source of app, a dyno of herokuconnect and a message that contains the log level (INFO, DEBUG, WARNING, ERROR or CRITICAL) along with a description of the action being carried out.

An example of the Logplex entries for a reload of the Contact mapping is shown below:

$ heroku logs --ps herokuconnect
2015-08-27T10:42:06+00:00 app[herokuconnect]: [INFO] RELOAD TABLE contact
2015-08-27T10:42:11+00:00 app[herokuconnect]: [INFO] Clearing table contact for load
2015-08-27T10:42:11+00:00 app[herokuconnect]: [INFO] Loading table contact via SOAP
2015-08-27T10:42:11+00:00 app[herokuconnect]: [INFO] Contact, QUERY, ↷SALESFORCE, 20 rows, (0.30 secs)
2015-08-27T10:42:11+00:00 app[herokuconnect]: [DEBUG] Contact, INSERT, ↓DATABASE, 20 rows, (0.03 secs), [soql]
2015-08-27T10:42:12+00:00 app[herokuconnect]: [DEBUG] Ensuring Heroku Connect triggers exist on contact
2015-08-27T10:42:12+00:00 app[herokuconnect]: [INFO] RELOAD COMPLETE

Recreating a Connection

If you’ve reloaded a sandbox organization or you need to upgrade from a hobby database to a professional one, you’ll need to delete your existing connection and create a new one. You can do that as follows:

1. Export your configuration from the Connect Dashboard. Click Settings, then click Import/Export Configuration, then click the Export Configuration button.
2. If you have any read/write mappings, make sure you aren’t writing any new data to those tables, and make sure all the mappings are in DATA_SYNCED to ensure all your data is present in Salesforce. You may need to click Settings, Manage Connection and click the Pause button to stop activity on the connection.
3. Click Settings, Manage Connection, and then click the Delete Connection button at the bottom of the page. This will delete the connection and remove the associated schema from your database.
4. Create a new connection from https://elements.heroku.com or heroku addons:create herokuconnect from the command line.
5. Authenticate your new connection, and import the config you exported from step 1.

Salesforce event log

The Salesforce event log allows you to monitor changes from your Salesforce organization.

To enable the event log navigate to Settings, Manage Connection and check the Enable Event Log option. When this option is enabled data change events received from Salesforce are stored in the _sf_event_log table: see System tables for more information.

Heroku External Objects

Heroku External Objects is available as an opt-in feature on Heroku Connect. Heroku External Objects provides an OData wrapper for a Heroku Postgres database that has been configured for use with Heroku Connect. This feature allows other web services to retrieve data from within the specified Heroku Postgres database using RESTful endpoints generated by the wrapper. Read support via OData 2.0 is GA, while read/write support via OData 4.0 is in Beta.

Used in tandem with Salesforce Connect, the feature permits data in a Heroku PG DB to be represented in a Salesforce deployment, where it can be viewed, including within Apex and Visualforce pages, searched, and related to other objects. It cannot be used in standard reports as the data is available by reference.

When Heroku External Objects is enabled on a Connection, a new element will appear in the main navigation: 'External Objects.’ Click through to access the settings page.

1. Any user can view the endpoint for the OData enabled Heroku External Objects data source
2. Credentials to the service can be sent or resent by any user, but will only be delivered via email to owners of the specified instance
3. Any user can also select which tables within the specified schema can be accessed via the service. Click to select a specific table within the schema.

All schema within the specified database will be available, including those schema not actively managed by Heroku Connect as part of the bi-directional synchronization service.

Security

Heroku Connect observes Salesforce secure practices by storing an OAuth token which can be revoked by the Salesforce admin.

Heroku Connect uses secure SSL connections whenever data moves from Salesforce to the Heroku Postgres database, or from the Heroku Postgres database to Salesforce.

Heroku Connect does not store data in flight.

More information on Heroku security can be found here.

Removing the add-on

You can remove the add-on using the Heroku dashboard or using the CLI.

$ heroku addons:destroy herokuconnect

 !    WARNING: Destructive Action
 !    This command will affect the app: sleepy-ocean-5276
 !    To proceed, type "sleepy-ocean-5276" or re-run this command with --confirm sleepy-ocean-5276

> sleepy-ocean-5276
Destroying rolling-gladly-8321 on sleepy-ocean-5276... done
Removing vars for HEROKUCONNECT from sleepy-ocean-5276 and restarting... done, v88

FAQ and Errors

More details about the cause and common resolutions to errors seen in Connect can be found here. While answers to the most frequently asked questions are available in the Heroku Connect FAQ article.

Support

All Heroku Connect support and runtime issues should be submitted via one of the Heroku Support channels.