Last updated 06 August 2015
Table of Contents
- How Heroku Connect works
- Provisioning the add-on
- The Heroku Connect dashboard
- Mapping objects
- Synchronization frequency
- Salesforce integration
- Database table structure
- System tables
- Resolving read errors
- Accessing the synchronized tables
- Updating data in Salesforce
- Inserting records to Salesforce
- Relationships between objects
- Resolving write errors
- Schema changes
- Pausing synchronization
- Salesforce organizations
- Upgrading your database
- Salesforce event log and webhooks
- Heroku External Objects
- Using Heroku Connect and the demo Rails app
- 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 apps 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
Contact sales for more information on the Heroku Connect commercial plan.
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
SysModStamp 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 inflight 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
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
To provision the demo plan of Heroku Connect:
$ heroku addons:create herokuconnect:demo Creating rolling-gladly-8321... done Adding rolling-gladly-8321 to sleepy-ocean-5276... done Setting HEROKUCONNECT_SCHEMA, HEROKUCONNECT_TENANT_KEY, HEROKUCONNECT_URL and restarting sleepy-ocean-5276... done, v5 Use 'heroku addons:open herokuconnect' to finish setup Use `heroku addons:docs herokuconnect` to view documentation.
The commercial plan, called ‘danketsu’, can be provisioned using:
$ heroku addons:create herokuconnect:danketsu Creating building-coyly-6991... done Adding building-coyly-6991 to sleepy-ocean-5276... done Setting HEROKUCONNECT_SCHEMA, HEROKUCONNECT_TENANT_KEY, HEROKUCONNECT_URL and restarting sleepy-ocean-5276... done, v5 Use 'heroku addons:open herokuconnect' to finish setup Use `heroku addons:docs herokuconnect` to view documentation.
If you don’t specify a plan (using
heroku addons:create herokuconnect) then you will default to the demo plan that is limited to a maximum of 10,000 synchronized rows across all mappings. If you are entitled to use the commercial plan you will automatically be upgraded or you can upgrade using the CLI:
$ heroku addons:upgrade herokuconnect Upgrading to herokuconnect on sleepy-ocean-5276... done, v5 (free) Plan change successful Use `heroku addons:docs herokuconnect` to view documentation.
If you attach Heroku Connect to an app, you will need a Postgres database attached to the same app to act as a target for Heroku Connect before you can complete setup. Visit Heroku Postgres for information on creating a new database.
Heroku Connect instances are located in the same region (US or EU) as the app they are attached to. Once Heroku Connect has been provisioned you can complete the setup process by navigating to the Heroku Connect add-on from the Heroku Dashboard or by opening it in a browser from the CLI:
$ heroku addons:open herokuconnect
To complete configuration of Heroku Connect, follow these steps:
- Choose the DATABASE_URL or specify another Heroku Postgres database attached to the app from the menu options. If there are no matches found, you’ll need to attach a database to the Heroku app prior to completing the Heroku Connect setup process.
- Choose a new, unique schema name to hold the synchronized tables. The default schema is salesforce. You may elect to put your tables in an existing schema, including the default public schema, as long as that schema is empty.
- Complete the database setup process.
Authenticate to the Salesforce organization. Heroku Connect will possess the same read/write entitlements as the authenticating user.
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).
It is recommended that you use a dedicated Integration User for Heroku Connect whose read/write permissions are calibrated to the degree of access required for successful synchronization.
Some API optimizations for dealing with large Salesforce tables are not available without the View All Data permission, or View All Data permission for a particular object. Creating and authenticating a dedicated Heroku Connect Integration User with a System Administrator is the most permissive option, but may not be required.
Choose one or more Salesforce objects to synchronize.
If you plan on running the Heroku Connect demo Rails app, please map your Account and Contact objects.
By default your connections will read data from SF -> DB. DB -> SF will not be automatically enabled, nor automatically queued for write. If you want to make changes to your Salesforce data you must first enable DB -> SF via the Heroku Connect application.
Once initial setup is complete, the
HEROKUCONNECT_SCHEMA values will be available in your app’s config vars. They contain the connection information for the synchronized database. It is recommended that your application code connect to the synchronized database by consulting these variables at runtime.
$ heroku config:get HEROKUCONNECT_URL postgresql://user:pass@host/database $ heroku config:get HEROKUCONNECT_SCHEMA salesforce
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 app. See Collaborating with Other Developers on Your App for more information on how to manage the users who have access to your app.
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 shown in orange for each data set. 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 records.
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 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.
Once you have authenticated to your Salesforce organization you can start mapping objects. Note that read sync operations SF -> DB involving more than 20,000 records for an object may require the authenticating user to possess the “View All Data” permission for the mapped object, and “API Enabled” permissions for that Salesforce organization. A System Administrator role will automatically enable both.
Click the Create Mapping button from the Overview tab.
The list of objects defined in your Salesforce organization will be displayed. Select an object from the list and click Next. Now the list of fields defined for the object will be displayed. You can choose which fields you want to synchronize to the database.
Heroku Connect supports all standard and custom objects except those that are not queryable via the API, or which require the use of object specific filter criteria to retrieve results. KnowledgeArticleVersion is an example of an object that requires the use of additional query criteria, “PublishStatus=Online”, and thus is not currently supported in Heroku Connect.
Pre-selected fields in the list are required to enable synchronization and may not be deselected. Once you click Save, Heroku Connect will immediately build a table corresponding to the Salesforce object in your database, query for all records from Salesforce and insert them into the database.
Navigate to the Overview tab to monitor the synchronization progress. You can click on an object row to open a detailed log of activity.
By default, Heroku Connect requests updates from Salesforce (reads) via polls every 10 minutes. Users may set polling frequency to any interval between 2 and 60 minutes when creating a mapping, and may change this frequency at any time. Shorter polling intervals may result in higher API utilization. Higher polling intervals will reduce API utilization.
The recommended synchronization mode from Salesforce to Heroku Postgres is Event Driven for those objects that support Streaming API connections. Streaming API connections are available for all custom objects and many frequently used standard objects such as Account, Contact, Case, Campaign, Lead and Opportunity. Some standard objects, such as ContractLineItem, or Tasks that are created or updated using the Lead Conversion method, do not have Push Topics available. Additional information about Push Topics may be found in the Force.com Streaming API Developer’s Guide
If the object is not eligible for Push Topic creation, the user will be given polling configuration options for the mapping. If Event driven mode is available for an object and the user saves the mapping in Event Driven mode, Heroku Connect will create a Push Topic with a name that looks like this: HC_123. This naming convention can help users identify which Push Topics in a given Salesforce deployment are in use by Heroku Connect.
Push Topics notify Heroku Connect of change events, triggering polls to Force.com on event. The polling rate does not exceed once every 10 seconds. Minimum sync latency for any object is 10 seconds. Heroku Connect destroys the Push Topics it creates when a user changes from Event driven to Polling mode, or deletes the relevant mapping entirely.
Heroku Connect will proactively poll every 10 minutes for objects in Event driven mode that have not received an event during the last 10 minutes.
By default, Heroku Connect instances in Read/Write mode poll for database changes (writes) every 10 seconds.
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 polling frequency of each mapping and its use of Streaming mode (see Synchronization frequency)
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 frequency) 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.
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 app.
_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.
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 app 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.
Accessing the synchronized tables
Connect to the database via the same credentials that you provided during setup. The synchronized tables will appear in the schema you specified at setup. By default this schema will be named
salesforce. To query a table, qualify the table name with the schema name:
select * from salesforce.account;
If you are using the
psql command line client, you can set your search path for easier reference to your tables:
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.
From 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. 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 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 before May 2015 that contains lookup relationship fields using the old naming convention you will need to edit the file to use the new naming convention before attempting to import it. Continuing with the above example an old JSON configuration file would contain a field named
Person__c__ExternalId__c in the
Contact mapping: you would need to edit the JSON file to rename it
PrimaryPerson__r__ExternalId__c before attempting to import the configuration.
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.
Changes to object schema coming from Salesforce are considered an exceptional event. User intervention will be required if schema changes impact an existing mapping. If a field mapped in Heroku Connect is removed from Salesforce, Heroku Connect will fall into an error state until the field is removed from the mapping. The corresponding column in the database will be dropped and synchronization may proceed.
Additive changes will not impact Heroku Connect. If a new field is added to Salesforce, simply update the Heroku Connect configuration and save if you wish to synchronize it. The corresponding column is created in the database and the missing data is queried from Salesforce and updated in the database.
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 app 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.
If you choose to preserve your existing database tables when removing the add-on you will need to use a different schema name when re-provisioning.
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 app 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 app 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.
Salesforce event log and webhooks
The event log and webhooks are currently beta features.
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.
Using Heroku Connect and the demo Rails app
Download the Heroku Connect Rails demo app:
$ git clone https://github.com/heroku/herokuconnectdemo.git $ cd herokuconnectdemo
Create a Heroku app and deploy the demo application code.
$ heroku apps:create $ git push heroku master $ heroku run rake db:migrate
This will automatically provision a Heroku Postgres add-on to the application.
Now add the Heroku Connect add-on, and open it to finish the provisioning process:
$ heroku addons:create herokuconnect $ heroku addons:open herokuconnect
In the Heroku Connect application, select the
DATABASE_URL for your app. Once
the provision process is done, map your
During provisioning, the
HEROKUCONNECT_URL config var will have been added to your
app’s configuration. Restart the demo app, and then open it in your browser:
$ heroku ps:restart $ heroku apps:open
The demo application does not enforce any security, which means that anyone can access the data it is showing. Don’t run the app against a Heroku Connect database that contains sensitive data.
To access your data through ActiveRecord on your local machine, you just need to configure your local environment:
$ export HEROKUCONNECT_SCHEMA=`heroku config:get HEROKUCONNECT_SCHEMA` $ export HEROKUCONNECT_URL=`heroku config:get HEROKUCONNECT_URL` $ export DATABASE_URL=`heroku config:get DATABASE_URL` $ rails console Loading development environment (Rails 3.2.13) > Account.count => 25 >
Customizing the sample app
The sample app contains a reusable mechanism for accessing the Heroku Connect database tables. An abstract
base class is defined in
salesforce_model.rb that connects to the Heroku Connect database:
class SalesforceModel < ActiveRecord::Base self.abstract_class = true establish_connection ENV['HEROKUCONNECT_URL'] # ... end
And each class to access a Heroku Connect table inherits from the base class:
class Account < SalesforceModel self.table_name = ENV['HEROKUCONNECT_SCHEMA'] + '.account' has_many :contacts, :primary_key => "sfid", :foreign_key => "accountid" # ... end
Note how we related Accounts to Contacts via the
sfid column. This is the actual Salesforce id. The records in the Heroku Connect database also use Rails-standard autoincrement
id columns, so specifying the foreign key like this isn’t strictly necessary.
Removing the add-on
To remove the add-on using the Heroku Connect dashboard navigate to the Settings tab, Manage Connection and click the Delete Connection button.
You will be given the option to either preserve or delete the database schema and data contained within the mapped tables. If you choose to delete the schema and data it cannot be recovered unless you have a backup of your database.
You can also remove the add-on 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
Using the CLI to remove the add-on preserves the database schema and data.
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.
I changed my Salesforce data model but I don’t see the changes in the mappings menu.
Open the Mapping to refresh the list of available fields for an object. Fields in error will be highlighted in red.
I hit “Pause” to stop syncing, but some rows still synchronized- what is happening?
When you “Pause” a Heroku Connect instance, Heroku Connect will finish execution of any jobs in flight. It will not initiate any new synchronization activities such as polling for new changes from Force.com or writing DB changes to Force.com, but is already a job in flight, it will complete before the Pause kicks in.
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 app, 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 app 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 app because it is an app 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.