How to Sync Salesforce with AWS PostgreSQL Database?  

Salesforce with PostgreSQL

Salesforce provides an easy and quick way to create and manage the content centralized. While maintaining multilingual content and version control.  

There are many use cases or advantages: sync all the data from Salesforce to an external database. Keeping the external database up-to-date on every CRUD operation on Salesforce e.g. Data house, changelog, data backup, etc. To achieve this, Salesforce provides some built-in services, e.g., Object Triggers. 


In this article, we will understand a case study of a project. Here we synchronized the Salesforce with the AWS PostgreSQL database for various reasons.

 
We are using Salesforce as a backend and the mobile app’s admin panel. Mobile app is connected with Salesforce. Done by using the REST APIs. And fetching the content from Salesforce.

Let’s Have a Quick Glimpse at Salesforce REST APIs 

To use Salesforce content or data on external systems or apps outside of Salesforce, it provides REST API integration. This integration provides easy management of the content on the various platforms.  

REST APIs is exposed with Connected App in Salesforce.  

The Connected App will provide the client_id and client_secret. To authenticate the users use them together with Salesforce username and password.   

Salesforce Login API example 

We have connected the Mobile app with Salesforce by using Salesforce REST APIs. But as we grew and took a step forward. Now from having an MVP to a production-ready application, we felt a need to remove the Salesforce API integration. Along with this we need to develop a more robust, reliable, and secure solution for the following reasons.  
  1.  The first reason that encouraged us to remove the Salesforce API integration is that we had to store some crucial information in the Mobile app sources. E.g., client id, client secret, and most importantly, the Salesforce username and password. If someone can gain access to these details, they can log in to Salesforce. They can also mess up the database, which will affect all the app users they are using the app.  
  1. There is relatively connected between first and second. As I mentioned, we need to store the login details in the Mobile app sources. This is to be done if we decide to change the Salesforce username password. If Salesforce credentials are compromised or if it is asking to change the credentials after 90 days, then it will lead to change those credentials in the Mobile app sources. And this will push the app to the stores again. This will break the app for the existing users unless they update the app with the latest version.  
  1. Another reason is the limit of REST API rate limit. Salesforce only allows having 100,000 API calls (+ purchased API Call Add-Ons) for 24 hours. This is very low and insufficient for production-ready Mobile apps. This targets hundreds of thousands of users.   
  1. Sometimes we also saw the downtime on the Salesforce due to various reasons. E.g., scheduled maintenance of Salesforce, any issue with billing with Salesforce, etc. The downtime of the app may lead to a lack of trust in the users in the era of tough completion. So, it was not always a reliable solution. 

To overcome the above challenges with the Salesforce REST APIs, we decided to create a dedicated server with its database to work as a bridge between Salesforce and the Mobile app. We removed the direct connection between the Mobile app and Salesforce and bypassed this connection via AWS.   
 

Technology stack on AWS  

  1. API Gateway  
  1. Lambda Function  
  1. CloudWatch  
  1. RDS (PostgreSQL)  
  1. Systems Manager Parameter Store  

We choose to use AWS services, e.g., Lambda function and AWS RDS, over creating a custom backend application. Hosted on an EC2 instance due to its high availability, durability, and easy maintenance. Also, we used micro-service based architecture and created different micro-services for different functions.

System architecture and data flow 

As described in the above diagram, we have removed the direct connection of the Mobile app with Salesforce, and now the Mobile app gets the data from AWS.  

Data Extraction from Salesforce  

If the admin or the app manager makes any changes in Salesforce, automatic synced by AWS with the latest changes. To achieve this, we have used Object Triggers on Salesforce. Object triggers are the events that invoke every change of the object records (e.g., on insertion of any new record, on the deletion of a record, or on updating the record).    

On every object trigger the added, updated, or deleted record are sent by API Gateways.   

Transformation and Data Load

API Gateways are responsible for getting the data from the Salesforce and pass to the Lamba function. The Lamda function contains the core business logic responsible for extracting the data from API Gateway requests and processing it for different tables, and performing desired operations, e.g., insertion, deletion, and update.   

We have dedicated Lambda functions for INSERT, DELETE and UPDATE operations. Each can process the data and perform operations on the PostgreSQL database.   

Mobile App connectivity

With API Gateways Mobile app connected with the new dedicated server. Every time the mobile app makes an HTTP request by the API Gateway, the API Gateway redirects the request to the Lambda function, and the Lamda function performs the data operation as per the request. For example, if the app requests to fetch data, the lambda function will perform the queries accordingly on the PostgreSQL database and return the response via the API gateway.  

Since the Mobile app is now getting the data from the dedicated server, there is no need to store the Salesforce credentials on the Mobile app itself. It resolves our first challenge. Also, changing the Salesforce credentials will not affect the Mobile app. 

We have eliminated the Salesforce REST APIs, and the Mobile app is now in contact with the dedicated server, so it won’t matter how many users are using the app; it will not run into the API rate limit issue. 

That’s all!!

AWS RDS(PostgreSQL) database creates a replica of the Salesforce database successfully with synchronization. If you are also facing similar challenges with Salesforce integrations and need assistance or advice then feel free to leave a comment below or get in touch with us at https://www.zehntech.com/contact-us/

   

Related post