How to integrate with HubSpot using your Postgres database

HubSpot is the source of truth for your customer data. It's where your [Sales, Marketing, and Support teams sell, market, and support your product. Here's the puzzle: that customer data can't only live in HubSpot. It's application data too.

HubSpot integrations take many forms. For instance, when a user logs into your product for the first time, you might want to create a new customer in HubSpot. When a customer purchases a new subscription, you might want to flag their HubSpot deal for Customer Success. When a deal closes, you might want to update a user's authorization in your application. In each case, a change in one system requires a corresponding change — or several — in the other. Your HubSpot data and product data are deeply intertwined.

HubSpot's API is expansive, but its limitations make these integration stories tedious. You'll need to manage rotating tokens, meter your API calls to avoid rate limits, navigate a complex relational model, and scrounge for data to make up for missing webhooks.

Sequin's solution is to sync your HubSpot data, live, to the database already at the heart of your app.

HubSpot API limitations

Like many CRMs, HubSpot places some constraints on its API to ensure its service is reliable, secure, and proprietary. While I understand the purpose of these constraints, a quick word of caution to my fellow developers as you embrace on an integration.

First, you'll need to carefully manage your API usage. Miscount and you'll see a couple (or hundreds) of 429 - RATE LIMIT errors in your logs. Even moderate API use is enough to exhaust either HubSpot's daily request limit or their ten-second maximum "burst" rate.

You'll also need to navigate HubSpot's associations model for object relationships. To update or retrieve one customer record, you may need to make five nested API calls to the search, contacts, associations, companies, and deals endpoints. Have a couple customers to pull? Prepare for another 429!

And for many integrations, you'll want HubSpot to notify you about changes — say, when a new support ticket is filed. Too bad! HubSpot's Webhooks API is limited to Contacts, Companies, Deals, and Conversations. To detect when data changes in HubSpot and then trigger some business logic, like an email, you'll need to poll continuously for updates... and handle more 429 errors!

Of course, the API works as advertised. None of those limitations are insurmountable, given time and careful monitoring, but you can avoid them entirely by syncing your HubSpot data to your database. Let's explore this approach.

Why sync HubSpot to Postgres

Having your complete HubSpot customer data in your database gives you more options to build and iterate.

With a sync, you can manage your HubSpot API quota in one place, trading quota utilization for sync latency. Using /search endpoints, you can limit your requests to objects that've changed, reading their updates in pages of 100 objects at a time.

With just a third of your quota allocated to reads you can keep at least 20 objects in sync with just a minute or less of latency.

A sync separates the process of retrieving data from querying the data. Simple SQL queries can return a complete customer record, joined across several tables, and deliver it at a production-ready pace — much faster than the five sequential API calls that'd be required to produce the same joined record. Unsure what data you need? You're free to experiment on SQL data without impacting your API quota.

Your team can use their favorite database tools to work with HubSpot just as they'd work with any other application data. Internal tools, reports, killer features; the sky's the limit.

What's the best way to build and maintain a sync between HubSpot and your database?

Building a HubSpot to Postgres sync

Building a sync with the HubSpot API is a multi step process. Here is a high level overview of the system:

Authentication and Token Management

With any integration, you'll need to authenticate your requests to the HubSpot API using OAuth access tokens (since API keys are sunsetting).

You'll first need to generate the token with an OAuth handshake:

const hubspotClient = new hubspot.Client({});

const token = "token";

try {
  const apiResponse = await hubspotClient.oauth.accessTokensApi.getAccessToken(token);
  console.log(JSON.stringify(apiResponse.body, null, 2));
} catch (e) {
  e.message === 'HTTP request failed'
    ? console.error(JSON.stringify(e.response, null, 2))
    : console.error(e)
}

HubSpot access tokens are short lived, so you'll then need to check the token's expiration and refresh as needed:

const hubspot = require('@hubspot/api-client');

const hubspotClient = new hubspot.Client({});

const token = "token";

try {
  const apiResponse = await hubspotClient.oauth.refreshTokensApi.getRefreshToken(token);
  console.log(JSON.stringify(apiResponse.body, null, 2));
} catch (e) {
  e.message === 'HTTP request failed'
    ? console.error(JSON.stringify(e.response, null, 2))
    : console.error(e)
}

Keep in mind that generating and refreshing access tokens counts against your API rate limit.

Quota Management

The HubSpot API has both a "burst" and "daily" API limit. These limits increase if you purchase a higher limit or enterprise plan.

When you hit the rate limit, you'll receive a 429 error response. If you're getting nervous, track of your daily usage by calling the limit endpoint with your API key (a.k.a hapikey):

https://api.hubapi.com/integrations/v1/limit/daily?hapikey=YOUR_API_KEY

Conveniently, HubSpot also includes your plan's daily request limit and the remaining quota among the headers of each API response.

To stay within the usage limits, it's best to implement a quota management system to throttle's your API calls. In addition to some helpful examples in the HubSpot developer forums, you'll find existing libraries you can use to manage your quota.

Backfill: paginate each endpoint

With authentication and quota management in place, you can begin to paginate through the API endpoints to load your historic data into your database.

For instance, to backfill contacts you'll call the list endpoint:

const hubspot = require('@hubspot/api-client');

const hubspotClient = new hubspot.Client({"apiKey":"YOUR_HUBSPOT_API_KEY"});

const limit = 100;
const after = undefined;
const properties = undefined;
const propertiesWithHistory = undefined;
const associations = undefined;
const archived = false;

try {
  const apiResponse = await hubspotClient.crm.contacts.basicApi.getPage(limit, after, properties, propertiesWithHistory, associations, archived);
  console.log(JSON.stringify(apiResponse.body, null, 2));
} catch (e) {
  e.message === 'HTTP request failed'
    ? console.error(JSON.stringify(e.response, null, 2))
    : console.error(e)
}

As you retrieve contacts from each page, store the pagination_id in case the process running the backfill should fail and you need to restart the process.

Sync: polling and cursors

With the historic data loaded into your database, you'll then poll the search endpoint for each object your syncing using the hs_lastmodifieddate as the search property.

curl https://api.hubapi.com/crm/v3/objects/contacts/search?hapikey=YOUR_HUBSPOT_API_KEY \
  --request POST \
  --header "Content-Type: application/json" \
  --data '{
    "filterGroups":[
      {
        "filters":[
          {
            "propertyName": "hs_lastmodifieddate",
            "operator": "GTE",
            "value": "2022-06-16 18:12:06.469752"
          }
        ]
      }
    ]
  }'

You'll store the most recently synced hs_lastmodifieddate as a cursor for each object type to ensure you only retrieve newly created or updated contacts, deals, etc. from their respective /search endpoints.

Keep in mind that search endpoints are rate-limited to 4 request per second per token.

Schema migrations

HubSpot supports both custom objects and custom properties. As HubSpot administrators create, update, or delete these objects, the shape of the API response will change. You'll want to handle schema migrations to accommodate these changes.

The complete system

You'll build several distinct services to sync HubSpot to your Postgres database:

In the end, it's a system you can reliably use to query the data you need when you need it, with Postgres niceties like like PG_NOTIFY to trigger business logic when HubSpot entities change, while avoiding 429 status codes.

Getting a HubSpot to Postgres sync

Alternatively, skip all this hassle with Sequin.

We're a completely managed service that goes to extreme lengths to sync HubSpot to your Postgres database in real time. We've done all the heavy lifting: managing tokens, enforcing rate limits, backfilling existing data, and syncing all new and updated objects to your database as they change.

Here are the steps for syncing HubSpot to your Postgres database using Sequin:

1. Create a Sequin account: Go to https://app.sequin.io/to signup and create an account.

2. Connect your HubSpot account: Create a new HubSpot sync, and generate a token for Sequin by entering your HubSpot credentials:

   

3. Select the objects and properties to sync: Pick the objects and properties you want to sync — Sequin supports custom objects and properties out of the box. Importantly, you can edit and lock the column names in your database to ensure your SQL queries don't break when properties are renamed in HubSpot.

   

4. Connect your database: Connect to your Postgres database (we support tunneling and VPC peering) or simply click "Start" to begin syncing your data to a Sequin-hosted demo database.

   

5. Sync: Sequin will persistently sync your HubSpot data to your database in real-time. As your integration evolves, you can return to the dashboard and click to sync additional tables and columns.

Next steps

Every API has quirks. Setting up a sync between HubSpot and your Postgres lets you work around these limitations, but generates some additional complexity.

We're building Sequin to help you skip the limits of the API and skip the tedium of writing all this glue code.