How to sync your customers' Salesforce data to Postgres

One of the most common use case for using a Unified CRM API is to sync your customers' CRM data into your application's database. This allows you, as an application developer, to do things like create segmentations across all your customers' CRM data, generate sales lead scores, join it with your application data, and to read the data in a local database rather than incur remote data-read costs.

There are many CRM tools, like Salesforce and Hubspot, and application databases, like Postgres and MySQL.

In this tutorial, we will go through using Supaglue to sync your customers' Salesforce standard objects into a sample application's Postgres database using Railway.

What is Salesforce?

Salesforce is a cloud-based CRM platform that is the go-to solution for companies to understand and manage their customers. Its flexibility and ecosystem have made it the market leader in the CRM space.

What is Postgres?

PostgreSQL is a SQL database that has all of the features that you require from a relational database.

What is Prisma?

Prisma is a next-generation ORM that can be used to access a database, like PostgreSQL, in Node.js and Typescript applications.

What is Railway?

Railway is a simple deployment platform that focuses on giving developers a deployment plane that increases developer efficiency.

Prerequisites:

1. A Salesforce Connected App.

2. A Salesforce account to transfer its customer data from.

4. A Supaglue cloud account, self-hosted, or local instance using docker-compose.

Overview

In this tutorial we will deploy a service (typescript-syncer) that will listen for a webhook event fired from your Supaglue instance when it finishes syncing CRM data to itself. Then it will sync the data to its Postgres database.

Step 1: Deploy typescript-syncer

typescript-syncer is a simple open-source Typescript application that uses Node.js Express as a web server, Postgres as an application database, axios as a http client, and Prisma as an ORM to sync CRM data from Supaglue to your application's Postgres database.

Step 1a: We'll be using a Railway Template (Supaglue-Postgres-Syncer) to 1-click deploy typescript-syncer to the cloud. Get started using the link below:

Deploy typescript-syncer with Railway

Step 1b: You'll need to enter two environment variables and then click on "Deploy":

• API_HOST: the URL of your Supaglue instance.
• API_KEY: the API key you generated in your Supaglue Management Portal.

Step 1c: Upon a successful deploy, copy the provisioned Railway domain by clicking on the GitHub card and then URL under the "Deployments" tab:

You will need this URL (highlighted above) to be registered as webhook endpoint in your Supaglue instance.

Note: the URL should start with https:// otherwise Railway will return a 301 redirect.

Step 2: Register the Railway URL with your Supaglue instance

Next, we will register the Railway URL we copied in Step 1 as a webhook endpoint in your Supaglue instance. Doing this will result in your Supaglue instance to call the Railway URL upon successful syncs. The frequency of your Supaglue syncs from Salesforce is configured in your Supaglue's Management Portal by setting the sync period in seconds.

Step 2a: Use Supaglue's Postman Collection to register the webhook.

(Alternatively, you can use Supaglue's Management API Reference to construct a curl that would look something like this gist.)

Under the "Body" tab, enter the Railway URL you copied during Step 1d above as the "url" field. It would look something like the following:

https://{YOUR_RAILWAY_URL}/supaglue_sync_webhook

Step 2b: Set {{baseUrl}} and {{apiKey}} variables in Postman so you can direct it to make calls to your specific Supaglue instance.

You can set these variables using the "Environments" tab on the left, then click on the "Development" environment.

You will need to fork the Supaglue Public Postman Collection to modify the values: click on "Fork" and name it something.

Step 2c: Replace the variables for baseUrl and apiKey:

• {{baseUrl}: your Supaglue instance's URL.
• {{apiKey}}: your Supaglue's API key.

Click on "Save".

Step 2d: Click "Save", then go back to the "Create Webhook" tab, and finally press "Send". If it was successful, you should receive the body back as a response:

Step 3: Make a manual request to the Railway URL to trigger a sync

You can wait for a Supaglue sync to complete, but to speed things up for this tutorial, lets manually curl to the Railway URL, which will simulate a "SYNC_SUCCESS" webhook event, to initiate a sync so we don't need to wait for your Supaglue instance to finishing syncing from Salesforce.

curl -XPOST https://{YOUR_RAILWAY_URL}/supaglue_sync_webhook -H 'content-type: application/json' -d '{"type":"SYNC_SUCCESS", "customer_id": "{SUPAGLUE_CUSTOMER_ID}"}'

Replace the following variables:

• {YOUR_RAILWAY_URL}: the Railway URL you were provisioned in Step 1.
• {SUPAGLUE_CUSTOMER_ID}: the Supaglue customer ID you want to initiate a sync for.

Step 4: Monitor the sync progress

In your Railway app, navigate to the logs by clicking on "View Logs", then "Deploy Logs":

Verify that syncs have started and finished.

Step 5: Issue queries against Postgres for CRM data

Now you can run SQL queries against the synced CRM data in Postgres. In Railway, navigate to the "Postgres" card and click on the "Query" tab.

Try issuing the following query to select the first name and last name from all synced CRM contacts:

select blob->>'first_name' as firstname, blob->>'last_name' as lastname from crm_contacts;

(Optional) Step 6: Update typescript-syncer to write to your Postgres

In the steps above we launched typescript-syncer to the cloud which syncs CRM data from your Supaglue instance to the typescript-syncer project's Postgres database. To start using it with your application, the next steps are to modify the Railway template to write into your application's Postgres. You will need to do three things to make that happen:

1. Add Supaglue CRM object tables into your Postgres database (Step 6a below)
2. Modify the Railway project's environments variables to write to your Postgres instead of typescript-syncer's Postgres (Step 6b below).
3. (Optional): If your Postgres is behind a firewall, you will have to expose port for your Railway typescript-syncer project to talk to it.

Step 6a: Add the following tables into your Postgres:

CREATE TABLE "crm_contacts" ( "id" TEXT NOT NULL, "blob" JSONB NOT NULL,
CONSTRAINT "crm_contacts_pkey" PRIMARY KEY ("id"));

CREATE TABLE "crm_opportunities" ( "id" TEXT NOT NULL, "blob" JSONB NOT NULL,
CONSTRAINT "crm_opportunities_pkey" PRIMARY KEY ("id"));

CREATE TABLE "crm_leads" ( "id" TEXT NOT NULL, "blob" JSONB NOT NULL,
CONSTRAINT "crm_leads_pkey" PRIMARY KEY ("id"));

CREATE TABLE "crm_users" ( "id" TEXT NOT NULL, "blob" JSONB NOT NULL,
CONSTRAINT "crm_users_pkey" PRIMARY KEY ("id"));

CREATE TABLE "crm_accounts" ( "id" TEXT NOT NULL, "blob" JSONB NOT NULL,
CONSTRAINT "crm_accounts_pkey" PRIMARY KEY ("id"));

Step 6b: Update your Railway app's Postgres environment variables to point to your Postgres:

• DATABASE_URL: your Postgres connection string.
• PGDATABASE: your Postgres database name.
• PGHOST: your Postgres host URL.
• PGPASSWORD: your Postgres password.
• PGPORT: your Postgres port.
• PGUSER: your Postgres user.

Step 6c: Manually call the Railway project's /supaglue_sync_webhook endpoint to trigger a sync to your application's Postgres.

If everything worked, at this point you should have your customers' CRM data in your application's Postgres database. You can run segmentation queries or generate lead scores with them, join them against your own application object tables, and more.

Have questions? Reach out to us on Slack!

Have feature requests or bugs? Log a GitHub Issue.

‍