Last updated 29 September 2015
Table of Contents
- How Heroku Connect works
- Provisioning the add-on
- The Heroku Connect dashboard
- Mapping objects
- Salesforce integration
- Database table structure
- Resolving read errors
- Updating data in Salesforce
- Inserting records to Salesforce
- Relationships between objects
- Resolving write errors
- Salesforce schema changes
- Pausing synchronization
- Salesforce organizations
- Upgrading your database
- Logplex logging
- Salesforce event log and webhooks
- Heroku External Objects
- Removing the add-on
Heroku Connect is an add-on that synchronizes data between a 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 language or web framework like Rails, Django, Node.js, Play or PHP.
$ heroku addons:create herokuconnect:demo
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 relational table. Similarly, any changes made to data in the relational table may be synchronized to Salesforce.
Heroku Connect uses
SystemModStamp to detect changes to Salesforce objects selected for synchronization from Salesforce to the database. Heroku Connect uses database triggers to record changes to be sent from the 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.
When a record is updated in the database, only the modified columns of that row are updated in Salesforce. This helps to prevent update conflicts in cases where the columns updated by Heroku Connect are distinct from those fields typically updated in Salesforce.
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.
There are two plans for Heroku Connect:
This is the default plan when provisioning the add-on and provides access to all features of Heroku Connect. It is however limited to a maximum of 10,000 synchronized rows across all mappings.
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.
The danketsu plan will appear as ‘free’ in the add-ons list for your application in the Heroku Connect dashboard. You should check the plan name is danketsu if you wish to verify that you are using the paid plan.
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:demo rolling-gladly-8321 free
Create a new instance
Heroku Connect can be attached to your application using:
$ heroku addons:create herokuconnect Creating rolling-gladly-8321... done 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
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:danksetsu
Heroku Connect instances are located in the same region (US or EU) as the application that they are attached to.
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.
To complete configuration of Heroku Connect click the Begin Setup 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_URLconfig 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
salesforcehowever you may choose to put your tables in an existing schema, including
public, as long as no tables exist in the schema.
You must not use a SQL Keyword as a schema name.
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.
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.
When authenticating to a production organization it will not be possible to change the organization at a later date: see Salesforce organizations for more information.
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.
Using a different database schema name for each organization allows a single Heroku Postgres database to be used with multiple Heroku Connect add-ons.
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
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:
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.
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.
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.
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.
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
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 may occur.
The Settings tab contains a drop-down list where you can choose:
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.
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.
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.
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.
Creating a mapping
To create a new mapping:
Click the Create Mapping button on the Overview tab of the Heroku Connect dashboard.
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.
Heroku Connect supports all standard and custom objects that can be retrieved via the SOAP and Bulk APIs except for those that require the use of object specific filter criteria. For example
KnowledgeArticleVersionrequires the use of additional query criteria,
PublishStatus=Onlineand is therefore cannot currently be mapped in Heroku Connect.
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
SystemModStamp) as they are required for Heroku Connect to perform synchronization.
It is also recommended that you map the
Namefield, if available, to improve your experience when using the Sync Explorer.
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:
- Click on the object name in the Mappings list on the Overview tab of the Heroku Connect dashboard.
- On the mapping detail page click the Edit button.
- You will now be able to edit the synchronization mode as well as adding and removing fields from the mapping.
- 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.
Mappings are read-only be 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.
Heroku Connect will poll your database every 10 seconds: this interval cannot be changed.
By default Heroku Connect will poll your Salesforce organization for changes every 10 minutes. You can configure the polling interval from two minutes 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 makes use of Force.com 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 may think of streaming mode as ‘polling on demand’ whereas polling mode is ‘polling at a fixed interval’.
Streaming mode is available for all custom objects and many standard objects: for those objects that do not support streaming you will not be able to select this mode when creating or editing a mapping. Heroku Connect is also subject to the Streaming API limits that apply to your Salesforce organization.
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 a 10 minute time period Heroku Connect will automatically poll for changes: this is to ensure that your mappings remain in sync even if notifications fail to be received.
Heroku Connect will create a Push Topic with a
hc_ naming prefix when you choose streaming mode for a mapping, for example
hc_123. The Push Topic will be deleted either when you switch to polling mode or delete the mapping.
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.
Heroku Connect can only be used with Salesforce editions that have API access. Some plan types, including trial versions, do not have API access by default and cannot be used with Heroku Connect.
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 20,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
SOAP API calls made by Heroku Connect do not count towards your API usage limits.
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 20,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
Bulk API calls do count towards the standard limit of 5,000 calls per day.
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 20,000 records. Bulk API calls make use of multiple asynchronously processed batches of 250,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
- 4x Bulk API calls to retrieve the records in batches of 250,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.
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
AccountSalesforce object is mapped to the
Column names use a lowercase version of the Salesforce field name, for example the
AccountNumberSalesforce field is mapped to the
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 TABLESQL commands. Heroku Connect will populate any newly mapped fields with data from Salesforce.
You should avoid creating mapped tables yourself. Heroku Connect will not replace an existing table when creating a new mapping and therefore, if the table is not created correctly, you may experience errors when syncing data. If you are using an ORM framework to model your mappings you should ensure that the framework does not attempt to create the underlying tables in the database.
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:
|Column name||Database Type||Description|
||integer||A unique, auto-incrementing integer primary key|
||varchar(18)||The Salesforce object
||timestamp||The date and time (in the UTC time zone) that the Salesforce object was last modified and used by Heroku Connect when polling for updates|
||boolean||Used to track the IsDeleted field from Salesforce allowing Heroku Connect to handle deletes when polling for updates|
||varchar(32)||Indicates the last sync operation performed on the record|
||varchar(1024)||If the last sync operation resulted in an error then this column will contain a JSON object containing more information about the error|
These columns are used by Heroku Connect to track and report on sync operations and must not be removed from the table. While you may read from the columns to aid in debugging you must not write to these columns as you will likely cause errors with Heroku Connect sync operations.
_hc_err columns can be used to aid in debugging or to surface sync status information in your application.
_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
sfidwill 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:
||The Salesforce operation that was attempted|
||The source of the error, set to
||The message that was returned by Salesforce describing the error|
Mapped data types
The table below shows how Salesforce field types are mapped to columns in the database.
Heroku Connect does not support un-typed or compound fields such as Address (the individual fields that make up a compound field can be mapped e.g. Street, City, Country). Fields with unsupported types will not be shown in the Mapped Fields list when creating or editing a mapping.
|Salesforce Type||Database Type||Notes|
|Auto Number||varchar||Length of field is provided by Salesforce based on field configuration|
|DateTime||timestamp without time zone||Times are stored as UTC|
|Encrypted String||varchar||Length of field is provided by Salesforce based on field configuration - see below for more information on how these fields are handled|
|External Lookup Relationship||varchar||Length of field is provided by Salesforce based on field configuration|
|Formula||see notes||The database type used is determined by the formula return type (for example a Checkbox return type will use a boolean database type)|
|ID||varchar(18)||Automatically mapped as
|Phone||varchar(40)||Includes formatting e.g. (650) 555-0100|
|Picklist||varchar||Length of field is provided by Salesforce based on picklist items|
|Picklist (Multi-Select)||varchar(4099)||Multiple selections are returned as a semi-colon delimited list
|Roll-Up Summary||double precision|
|Text||varchar||Length of field is provided by Salesforce based on field configuration|
|Text Area (Long)||text|
|Text Area (Rich)||text|
|URL||varchar||Length of field is provided by Salesforce based on field configuration|
The Geolocation Salesforce type is not supported however it is possible to map the Latitude and Longitude components that make up the Geolocation. They will be mapped as
double precision columns in the database.
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.
For example an encrypted credit card number would be stored in the database as
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.
To allow unencrypted values to be received from Salesforce you must enable View Encrypted Data permission for the user credentials used to authorize Heroku Connect.
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;
When setting the search path you must include the
public schema as it contains shared functions used by Heroku Connect.
Heroku Connect creates several system tables within the schema:
Updates received from Salesforce are logged in this table when the event log is enabled. The
_sf_event_logincludes details about the type of change, the time it occurred, the Salesforce ID of the record and a JSON representation of the changes.
This table stores the unique Salesforce organization ID that Heroku Connect is currently authorized with.
_trigger_logtable is used to record updates that need to be written to Salesforce. Data remains in this table for up to 7 days 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.
Once data in the
_trigger_logtable is older than 7 days it is moved into this archive table where it will be available for up to 31 days.
This table stores the ID of the last row synchronized with Salesforce.
These tables are used by Heroku Connect to track sync operations and must not be removed from the schema. While you may read from the tables to aid in debugging you must not write to these tables as you will likely cause errors with Heroku Connect sync operations.
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.
If Heroku Connect cannot query for a mapped object, this error or similar will display in the logs: ‘INVALID_TYPE_FOR_OPERATION…’
If Heroku Connect cannot map an object because it requires object specific filters, this error or similar will display in the logs: ‘MALFORMED_QUERY…’
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: SUCCESSFUL, IGNORED, or FAILED. Ignored and Failed updates to Salesforce are retained in the Heroku Postgres
_trigger_log table. Successful updates may be truncated from the
_trigger_log table. The ‘_trigger_log’ table is automatically archived by Heroku Connect.
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 record id, if there is no sfId
- Combine values and send to Salesforce as a single INSERT. The most recent value for any given field will persist
- This also works for DELETE, so that if you delete the record then it will never be sent to SF 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. An example would be a Lead that first existed in a “New” state in the DB, and was “Converted” in the database before being inserted as “New,” would be inserted as “Lead” that is “Converted” in Salesforce without ever having passed through the “New” stage in Salesforce.
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.
In the case of Person Account objects, there may be additional fields that require specification.
Heroku Connect does not provide a mechanism for validating field level updates against picklist values. Heroku Connect does not provide Metadata API support.
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 fields to your mappings and write the Salesforce IDs to those mapped fields when creating or updating records.
- 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 uniquely 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
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.
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.
- Add a new custom integer field to the
Accountobject in Salesforce named
ExternalIdand ensure the External ID option is selected
- Create a new mapping for the
Accountobject in Heroku Connect that includes the
- Create a new mapping for the
Contactobject in Heroku Connect that includes the
Account__ExternalId__cfield that will be found at the bottom of the field list
- Insert a new record into the mapped database table for
externalid__cfield is set to a unique value, e.g. ‘1234’
- Insert a new record into the mapped database table for
account__externalid__cfield to the same value you used in step 4
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
- Add a text or number custom field (e.g.
- 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
- Add the External ID field (e.g.
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.
Legacy relationship fields using the old naming convention are shown below the main field list under the Legacy External-Id Relationship Fields heading.
For example if you need to add an additional lookup relationship you will need also need to update the mapped field for the existing lookup relationship to use the new field name.
Contactobject has a lookup relationship to the
Person__ccustom object named
Person__cobject has an External ID field named
Contactobjects were both mapped in Heroku Connect before May 2015
Person__cmapping includes the
Contactmapping 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:
- Edit the
Contactmapping, remove the related external ID field named
Person__c__ExternalId__cand save the changes
- Go back to edit the
Contactmapping, add the
SecondaryPerson__r__ExternalId__cfields and save the changes
- Update any dependencies on the old
person__c__externalid__cfield in your application code to use the new
If you have a JSON configuration file that you exported from Heroku Connect that contains lookup relationship fields using the old naming convention you should ensure that you export a new configuration file after updating your mappings to use the new field names.
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.
Heroku Connect does not support mapping External IDs for polymorphic relationships as the functionality to allow both the object ID and object type to be specified for these relationships is not currently available.
This includes External ID fields on the
Who polymorphic relationships defined on
Task objects: these fields will not be available when creating and editing your mappings.
This limitation only applies to polymorphic relationships using External IDs: if you are using Salesforce IDs then it is possible to establish a polymorphic relationship using Connect by mapping the
WhoId fields and setting them to the appropriate Salesforce ID of the related object.
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 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.
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.
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 API 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).
Any changes made to data in the database or in Salesforce while your connection is paused should automatically be synchronized when your connection is resumed.
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 a production 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 production organization. If you want to move your application to a new production organization you will need to:
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.
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 re-syncing data from your new Salesforce organization into the database.
Salesforce sandbox organizations can be periodically refreshed: this involves creating a new snapshot of data from the production organization and, at the point of activation, will replace the current sandbox with a new organization that has a new unique ID.
When you authenticate Heroku Connect to a sandbox organization you will have the ability to re-authenticate to the new organization when you activate your new sandbox:
- Navigate to Settings, Manage Salesforce.
- Click the Authorize button.
- Review the confirmation page and click the Authorize button.
- Complete the Salesforce login process to authenticate to your organization.
Unlike production organizations it is possible for Heroku Connect to be re-authorized to a new sandbox: you will need to do this after activating the refreshed sandbox organization in Salesforce.
Login IP restrictions
Heroku Connect makes use of a security token that allows it to bypass any IP restrictions you have configured in your Salesforce organization: you should not have to whitelist Heroku IP addresses in order to use Heroku Connect.
When you authenticate Heroku Connect to your Salesforce organization you will have to do so from an IP address that is not restricted. On successful authentication Heroku Connect is issued with a security token that allows it to communicate with your organization.
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.
These steps ensure that the new database is an exact copy of your current database. Heroku Connect will not create tables or transfer data to an empty database, so be sure to follow the proper upgrade process.
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.
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.
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
Promote the follower to become the new lead database. This will make it accessible as
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_URLfor your application and resume synchronization using the new database.
If you’re upgrading from a hobby database, you won’t be able to create a follower. Instead, use the PG Copy process instead to copy data into the new database. Simply pause Heroku Connect at the same time as you put your application into maintenance mode, and resume it again when you reactivate your application.
When errors occur that require user intervention Heroku Connect sends notifications by email to the user who initially provisioned the add-on.
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.
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.
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.
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.
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.
The Loglex logging feature is currently in beta: the format of log messages is likely to change in future releases of Heroku Connect.
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 (
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
Salesforce event log and webhooks
The event log and webhooks features are deprecated and will be removed from a future release of Heroku Connect.
The Salesforce event log allows you to monitor changes from your Salesforce organization and write code that is triggered when these changes occur using webhooks.
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.
You can also enter a URL that will receive notifications when events are received: Heroku Connect will send a HTTP POST request to this URL with a JSON body containing the following data:
||string||A string representation of the operation being performed:
||integer||The ID of the first record in the
||integer||The ID of the last record in the
||array||An array containing details of the objects that this notification relates to with each item containing a two element array with the Salesforce object name and Salesforce ID of the object, for example:
The HTTP POST request body does not include details of the changes being processed. Your code that handles notifications via the webhook will need to query the
_sf_event_log table to get more detailed information about the change.
A simple example application that demonstrates how to consume webhook notifications can be found here.
Heroku External Objects
Heroku External Objects is currently GA as an opt-in feature on Heroku Connect. Heroku External Objects provides an oData wrapper for the Heroku Postgres database that Heroku Connect maintains a Connection for. This feature allows other web services to retrieve data from within the specified Heroku Postgres DB using RESTful endpoints generated by the wrapper.
Used in tandem with Salesforce1 Lightning Connect, the feature permits data in a Heroku PG DB to be represented in a Salesforce deployment, where it can be viewed, including viewed within Apex and Visual Force pages, searched, and related to other objects. It cannot be edited or used in standard reports; 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.
- Any user can view the endpoint for the oData enabled Heroku External Objects data source
- 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
- User must also select which tables within the specified source/ 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.
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.
Mapped tables will be dropped from your Postgres database when removing the add-on: you should ensure you have an up-to-date backup of your database before proceeding.
$ 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
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 you wish to remove when using the CLI.
What does Restart do?
Use the Restart button located in Settings tab, Manage Connection to attempt restart of an instance in an error state. This is a non-destructive action: Heroku Connect will restart all processes from last completion time.
What does Reload do?
When you RELOAD an object, Heroku Connect repopulates the table in the Postgres DB with a fresh set of data from Salesforce. All data in the Postgres table that has not been written to Salesforce will be overwritten. This is a destructive action. Attempt recovery of an object in a SYSTEM ERROR state through Restart first.
Why can’t the SOAP API be used for all Salesforce interactions?
As SOAP API calls do not count towards your daily usage limits it may seem like a good idea for Heroku Connect to simply use the SOAP API for all interactions with your Salesforce organization. Unfortunately the SOAP API is not optimized for use with large data volumes: the main differences being that it lacks the asynchronous background processing capabilities of the Bulk API and therefore requires more processing overhead and server round-trips in order to transfer data. Although the Bulk API is the best choice for processing large data volumes you can take steps to reduce its usage: see Reducing Bulk API calls for more information.
Why were rows still synchronized after I had paused my connection?
When you pause synchronization Heroku Connect will finish processing any active synchronization tasks. Any further changes made either in your database or in Salesforce after you pause will not be synchronized until you resume your connection.
Why aren’t all of my Events or Tasks being synchronized?
Event and Task objects can be archived by Salesforce which results in the
IsArchived flag being set. Unfortunately the Salesforce Bulk API does not allow archived Events and Tasks to be retrieved which in turn means Heroku Connect cannot synchronize them.
Why is there a difference between the Salesforce and database row counts for my mapping?
Row counts can differ between Salesforce and your database for a number of reasons, including:
- Errors reading or writing data: for example due to validation errors in Salesforce or constraints in your database.
- Records are no longer visible to Heroku Connect: for example archived Events and Tasks.
- The database row count will sometimes be an estimate: to improve performance for mapped tables with a high number of records (in the order of several million) Heroku Connect will not perform a full row count after every sync operation but will instead use an estimation method that can be calculated very quickly.
I’m trying to map an object, but it’s grayed out in the menu. Why can’t I select it?
If an object has been recently removed, the removal operation may still be underway. Otherwise, the object may not be eligible for mapping.
I removed the add-on but I saved my data. How do I access it?
If you removed the Heroku Connect add-on from a Heroku application, but opted to save the data in the underlying database, you may continue to access the data in the database the same way you would access data in any other Heroku Postgres database.
If I delete the Heroku application that Heroku Connect and the Heroku Postgres database were connected to, what happens to Heroku Connect? What happens to the database?
Heroku Connect will be deleted along with the Heroku application because it is an application specific add-on. The Heroku Postgres database will likewise be destroyed if it is an app-specific resource, but will not be destroyed if it is attached to other apps. However the Heroku Connect synchronization will no longer be running on it.
How do I change the address I get notifications at?
Notifications are sent to the email address assigned to the user that initially provisioned the add-on. For more information see Notifications.
I want to sync images between Salesforce and Postgres. Can Heroku Connect sync images?
Heroku Connect does not support image or file sync. If you want to manage a reference to an image in Salesforce, you can store the image on S3 or another service and sync the URI between Salesforce and Postgres.
How do I push records to Salesforce that were written to the DB, but errored out when Heroku Connect tried to sync them back to Salesforce?
In your database, run something like this:
update _trigger_log set state = 'NEW' where state = 'FAILED' and table_name = '<your table name>';`
That will reset any errored updates. One caveat: the trigger log is periodically trimmed, so you should check to make sure all your updates are in the
_trigger_log table still.
After 30 seconds a sweeper will run and find any “old”
_trigger_log rows that are state NEW and resubmit them.
If your updates are gone from the
_trigger_log and you are updating records without an SFID, then you could do the following: update any rows in your original table that have
sfid column = null. You need to find some column you can update to a new value, so something like:
update contact set fax = '510-555-5555' where contact.sfid is null
This will generate a new set of updates to the
_trigger_log, but in the absence of the sfId value they will be treated as inserts.
Heroku Connect does not support deletes on OpportunityContactRole table as Salesforce does not support IsDeleted functionality on that table. Workarounds are using a custom object to replace the OpportunityContactRole table or periodically reloading that table.