How to sync Postgres and Salesforce

I. Introduction

A. Overview of Postgres and Salesforce

Postgres: the most popular database in the world

Postgres (or the PostgreSQL database) is one of the most popular, scalable databases in the world. Millions of developers choose to build their apps and sites on top of OLTP data stored in Postgres. As a relational database, Postgres is easy to understand for anybody coming from the world of spreadsheets.  And since it's been around since the ‘90s as an open source project, Postgres has a robust global community, which means that answers to any question are usually just a Google search away. 

Salesforce: the most popular CRM in the world

Salesforce (SFDC) is the most popular CRM on the planet, with over 150 thousand companies relying on it to run their sales teams. From stores on Main Street to Fortune 500 companies, Salesforce has become the primary database on every step of the sales funnel, from lead to conversion.

B. Benefits of Implementing Two-Way Sync

There are many reasons why you would want to keep SFDC and Postgresql data in sync. Let’s dive into a few examples.

Make data flow between you and your users in real-time

Let’s say you’re a real-estate company, and you’re running your customer-facing app on top of Postgres. Your users note down their preferences – location, square footage, etc – in your web app, which gets stored in Postgres. However, you also have a broker team actively talking with your users, marking down their preferences in SFDC during these chats. With data entry happening in two different places, you need to keep your CRM data in sync with your web app data. Imagine how powerful it would be for your brokers to note down a customer’s preference in SFDC during a call, then be able to show those filtered preferences live on the web app!

Build apps on Postgres rather than SFDC

Let’s face it: SFDC is complex. Becoming a power SFDC user is a far steeper learning curve than becoming a power Postgres user. SFDC consultants routinely charge hundreds of dollars per hour for bespoke SFDC customizations - customizations that in-house engineering teams rarely want to or can easily build. Compare that to Postgres, which every software or data engineer worth their salt is familiar with. By syncing SFDC with Postgres real-time, you can avoid the costly complexity inherent in building on top of SFDC directly, and leverage the massive open-source Postgres community. You’ll not only develop faster, but you’ll save money on expensive third-party consultants. 

Analyze data with SQL rather than SOQL

SQL is the lingua franca of data analysis. Compare that to SOQL, SFDC’s object query language, and you’ll feel like the latter seems more like Esperanto. Just as it’s easier to build apps on Postgres than SFDC, it is far easier to perform quality data analysis on CRM data using SQL than SOQL. 

This will allow data engineers to glean SFDC insights quicker, meaning your Head of Sales can keep track of Sales team performance more easily and patch up a leaky funnel.

And again, since Postgres is a long-running open source project, there are tons of resources online to help answer any SQL error woes.

Save money on SFDC seats

If you’re trying to sync SFDC with other sources, you are probably relying on its Web Services API, which is available on the Enterprise ($150 / seat) and Unlimited ($300 / seat) plans. For growing orgs, even those flush with cash, this SFDC bill can come with a bit of sticker shock. 

By getting Salesforce data into Postgres and either giving users direct access or building an internal tool on top of it, you could save tons of money on SFDC seats. 

Centralize your SFDC API use

If you plan to run many applications that rely on SFDC data, you have two choices: plug them in directly to the SFDC API, or plug them into a Postgres database that syncs with the SFDC API. 

There are a few benefits to the latter approach:

1. API updates: if SFDC updates its API, you only need to rewrite your script once, because SFDC is only syncing with a single external destination. Note: if you’re using Bracket to do it, we make those updates for you.
2. System failure: in the event of system failure - i.e., the sync with SFDC stops - apps running on Postgres can continue to function during the downtime, rather than running into failed API calls.
3. Debugging: if you notice that data is not being synced to Salesforce objects, you know that the culprit must be the Postgres sync, which quickly narrows down and speeds up the debugging process.

II. Setting Up the Two-Way Sync

In this section, we’ll talk through how you can use Bracket to set up fast two-way syncs between SFDC and Postgres in a matter of minutes.

A. Connecting SFDC

1. Head to app.usebracket.com and either create an account or sign in.
2. Select SFDC as either your primary or secondary source. The only difference between the two is that the Primary Source wins any potential merge conflicts in a two-way sync. 
3. Choose your account type, then log in to SFDC using your credentials
4. Select the SFDC table you’d like to sync. That’s it!

B. Connecting Postgres

1. Select Postgres as either the primary or secondary source, whichever you didn’t select for SFDC in the steps above.
2. Enter the connection details for a Postgres user. You can either use an existing Postgres user, or create a new user for Bracket. If you create a new user, make sure to give that user `grant` and `create` permissions on the Postgres database. 
3. Optionally, you can choose to connect with SSL=require or via an SSH tunnel.
4. Either allow Bracket to generate the Postgres table for you (recommended), or connect to an existing Postgres table.
5. Choose your sync direction and make sure the field mapping is accurate. Then just name your sync, choose your sync frequency, and voila!

C. Testing and running syncs

1. On your sync overview page, you can run a quick test run to verify that the sync will work as expected.
2. After a successful test run, you can either click “Run once” to schedule a single sync, or toggle the sync to active to start ongoing syncs.

‍

III. Maintaining the Two-Way Sync

In this section, we’ll talk through how to keep your SFDC <> Postgres sync running smoothly. 

A. Managing fields

As every data engineer knows, schemas change over time. After setting up a sync between SFDC and Postgres, it’s totally possible that you’ll need to remove, add, or adjust the fields being synced. 

Bracket makes this easy for you in two ways: 1) alerting you as soon as a sync hits an error due to schema changes, and 2) letting you know exactly what field caused the problem. 

After getting those alerts, adjusting the sync is as easy as updating the field mapping section on the web app (see below). 

In other words, no need to dig several layers deep into a broken sync only to find that a field was renamed. Bracket alerts you from the start.

B. Managing deleted records

Another common problem data engineers face when setting up two way syncs is what to do with records deleted from one side. Do you orphan these records in the opposing source? Do you delete them automatically? Do you manually review each one to make sure it’s valid?

Different use cases call for different measures, and luckily Bracket offers you all three options out of the box. Just set your delete policy, and forget about it (unless you’re manually reviewing deletes, that is). 

One more benefit: just like with field management, you’ll get alerted if there are any issues with deleted fields. For example, sometimes foreign key constraints in Postgres will prevent record deletes.

‍

IV. Conclusion

A. The benefits of Salesforce <> Postgres syncs

In summary, there are a number of benefits unlocked by syncing SFDC with Postgres in real-time. By leveraging both, you can build your user-facing apps on top of Postgres, a highly performant and popular database, while empowering your business users to receive and send data to users through SFDC as a GUI.

Not only does this keep each team in familiar tools, but it also 1) speeds up downstream analytics by letting you run SQL rather than SOQL, 2) cuts costs by reducing SFDC seats, and 3) improves your app’s reliability by limiting reliance on the Salesforce API.

B. Build or buy?

When it comes to syncing, the question of “build or buy” is a tricky one, which we’ll cover in a future blog post. But if you’re interested in trying out Bracket’s two-way sync, you can get started for free at app.usebracket.com.

‍