Skip to main content

Foreign Keys / External IDs

Efficiently sync related tables from postgres to salesforce and maintain their relationships without needing code.

When you have two tables in your postgres database with foreign keys, you can use those fields to relate your records when they sync to salesforce.

Here's a walkthrough with generic objects:

  • Account (parent)

  • License__c (child, with a lookup field Account__c pointing at Account).

Prerequisites

  • Both objects already have their own table mapping on the same connection (accounts ↔ Account, licenses ↔ License__c).

  • Salesforce has at least one custom field on Account marked External ID (e.g. External_Id__c) — the one Salesforce-side prerequisite that can't be automated from here; an org admin has to check that box in Setup.

Configuring the relationship (one-time, per lookup field)

  1. Open the child mapping (licenses), go into Edit Mappings. Under Relationships, find the lookup field (Account__c, shown with a link icon) and click Configure.

  2. In the dialog: pick the related table mapping - accounts (Account).

  3. A second dropdown appears, "External ID field on Account", populated from that object's describe(). Pick External_Id__c.

    • If accounts doesn't have this identifier configured yet, picking it here sets it up automatically (writes External_Id__c: { postgresColumn: "id" } into the parent's mapping) — no separate trip to the parent's settings needed.

  4. PostgreSQL Foreign Key Column auto-fills as account__r_external_id__c (editable, e.g. to keep an existing account_id column name instead).

  5. Save. That's it — the relationship is now described entirely by two ordinary field mappings, one on each side.

What happens automatically after that

  • Schema sync: next time the sync runs, if the FK column doesn't exist on licenses yet, it creates it using accounts.id's actual Postgres type (UUID, BIGINT, whatever it really is), not a guess from the Salesforce field.

  • Pull (Salesforce → Postgres): the worker's SOQL query traverses the relationship (Account__r.External_Id__c), and since that field already mirrors the parent's local id, the value drops straight into the child's FK column, a real, usable Postgres foreign key, no join or lookup required.

  • Push (Postgres → Salesforce): when a new child row is inserted with a real account_id pointing at an existing accounts row, the worker sends Salesforce {"Account__r": {"External_Id__c": <that id>}} - Salesforce resolves the lookup by external ID, so the write works even if the parent hasn't synced to Salesforce yet (or its sfid isn't known locally).

The payoff for whoever's writing to Postgres directly

A developer (or any app) writing to licenses just does a normal INSERT ... account_id = <some accounts.id> ... - exactly like they would against any other foreign-keyed table, no Salesforce awareness required. Both directions of sync stay correct regardless of which table happens to sync first.

Did this answer your question?