Integrate with HubSpot using Prisma
Tags: prisma, tutorial, postgres
Published: 2023-04-06 20:56:17

Sequin lets you sync your HubSpot data to your database in real-time. You can then use ORMs, like [Prisma](https://www.prisma.io/), to rapidly build your HubSpot integration.

In this playbook, you'll learn how to set up HubSpot to work with Prisma using Sequin. You'll then write your first queries using the Prisma client and explore the development lifecycle as you run migrations and scale your integration.

![Data flow from HubSpot to Postgres to Prisma](https://docs.sequin.io/assets/hubspot-prisma/001_hubspot_sequin_prisma.png)

## Starting with an existing project

You're likely already using Prisma as your ORM. So this playbook starts from an existing Prisma project and shows you how to add HubSpot data to your stack.

We assume you've already followed [Prisma's quickstart](https://www.prisma.io/docs/getting-started/quickstart) to create your TypeScript project, install Prisma, connect your database, introspect your schema, and query with the Prisma Client.

Specifically, this playbook builds on top of an existing Prisma project connected to a PostgreSQL database with one schema called `public` containing two tables: `users` and `orgs`. Each `user` is a part of an `org` — represented in the database with a foreign key relationship:

![Original schema](https://docs.sequin.io/assets/hubspot-prisma/002_initial_erd.png)

This schema is represented as a Prisma model in the `schema.prisma` file:

```js
generator client {
  provider        = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model orgs {
  id                  Int     @id @default(autoincrement())
  name                String?
  subscription_status String?
  users               users[]
}

model users {
  id         Int     @id @default(autoincrement())
  first_name String?
  last_name  String?
  email      String?
  org_id     Int?
  org        orgs?   @relation(fields: [org_id], references: [id], onDelete: NoAction, onUpdate: NoAction, map: "users_orgs_id_fk")
}
```

Each table has been translated into a model. And the foreign key [relationship](https://www.prisma.io/docs/concepts/components/prisma-schema/relations) between `users` and `orgs` has been defined with a `@relation` scalar.

From the foundation of this existing Prisma project, you'll now add your HubSpot schema to Prisma using Sequin.

## Setup your HubSpot sync

To build a sync between HubSpot and your database, Sequin will guide you through the process of authenticating with HubSpot, selecting the data you want to sync, and connecting to your database. Read our [HubSpot setup guide](/integrations/hubspot/setup) for step-by-step instructions.

For the purposes of this playbook, you'll want to sync at least two HubSpot objects mapped together with an `association`. For instance, you can sync the `Contact` object, `Deal` object, and `Contact with Deal` associations:

![Select objects](https://docs.sequin.io/assets/hubspot-prisma/003_select_objects.png)

To get comfortable with your schema and Prisma's workflows, you also don't need to sync every HubSpot property. In the Sequin Console, configure your sync to include a handful of properties:

![Select columns](https://docs.sequin.io/assets/hubspot-prisma/004_select_properties.png)

For instance, sync some standard properties for the `Contact` object. For reference, these are the `Contact` properties used in the remainder of this playbook:

- `industry`
- `associatedcompanyid`
- `numemployees`
- `website`
- `company`
- `jobtitle`
- `lastname`
- `firstname`
- `user_id`

> **Note:** As a helpful example, we include a custom property called `user_id`, which maps each HubSpot `contact` to a `user`. We'll explore this relationship more later in the playbook.

Do the same for the `Deals` object. Here are the `Deal` properties used in this playbook:

- `createdate`
- `closedate`
- `pipeline`
- `dealstage`
- `amount`
- `dealname`
- `hs_priority`

> **Tip:** You can also edit and lock the column name for each property you sync. These tools allow you to create simple naming conventions in your database and buffer your database from breaking changes in HubSpot.

With HubSpot configured in Sequin, you can now [connect your database](/self-hosted) to Sequin and create your sync.

After you click **Create**, Sequin will begin backfilling all the `Contacts`, `Deals`, and `Contact - Deal` associations in your HubSpot instance to your database.

Within a minute, you'll see the new `hubspot` schema and tables in your database:

![Complete schema](https://docs.sequin.io/assets/hubspot-prisma/005_full_schema.png)

You can now configure Prisma to work with your HubSpot data.

## Add HubSpot to your Prisma Schema

Next, you'll update your `prisma.schema` file and then re-generate the Prisma Client to work with the HubSpot data in your database.

Before you do, it's worth building a mental model of this process.

Your database now contains two schemas:

1. **A `public` schema** that _you_ own and control. You're probably used to using [Prisma's Migrate tools](https://www.prisma.io/docs/concepts/components/prisma-migrate/mental-model#what-is-prisma-migrate) to make changes to this schema. In Prisma, these are called "model/entity-first migrations." (Some Prisma users prefer to `create` new tables and `update` columns in the database via SQL. These are called "database-first migrations.")
2. **A `hubspot` schema** that _Sequin_ owns and controls to maintain your HubSpot sync. All migrations in this schema are done directly to the database via SQL commands that originate from Sequin. So, these are database-first migrations _that Sequin runs_. If you add or drop columns in this schema using Prisma, you'll break the sync. Hence, all migrations in this schema are performed in the Sequin console. After Sequin applies the migrations to your database, you'll add the changes to Prisma.

So, when working with your `hubpost` schema, you'll always follow a **database-first migration** pattern in Prisma. This means you'll `pull` the schema into Prisma models as opposed to pushing the schema from Prisma models into your database. Here is how.

![Flow diagram](https://docs.sequin.io/assets/hubspot-prisma/006_migration_flow.png)

### Turn on multiple schema support

Your database now contains two schemas. To configure Prisma to work with more than one schema, you need to turn on [multi schema support](https://www.prisma.io/docs/guides/database/multi-schema).

To do so, update your `schema.prisma` file as follows:

```js
generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["multiSchema"]
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
  schemas  = ["public", "hubspot"]
}

model orgs {
  id                  Int     @id @default(autoincrement())
  name                String?
  subscription_status String?
  users               users[]

  @@schema("public")
}

model users {
  id         Int     @id @default(autoincrement())
  first_name String?
  last_name  String?
  email      String?
  org_id     Int?
  org        orgs?   @relation(fields: [org_id], references: [id], onDelete: NoAction, onUpdate: NoAction, map: "users_orgs_id_fk")

  @@schema("public")
}
```

1. Add `previewFeatures = ["multiSchema"]` in the `generator` block. This turns on multi schema support.
2. List your schemas in the `datasource` block. In this case: `schemas  = ["public", "hubspot"]`
3. Designate which schema each of your Prisma models belongs to with a `@@schema('public')` attribute

Prisma is now ready to handle multiple schemas when you begin to introspect your database in the next step.

### Introspect your database

The Prisma CLI provides [introspection tools](https://www.prisma.io/docs/concepts/components/introspection#introspection-with-an-existing-schema) to automatically update your `schema.prisma` models to reflect the schema in your database.

To do so, ensure you have the [Prisma CLI installed](https://www.prisma.io/docs/concepts/components/prisma-cli/installation), navigate to the root directory of your project, and run the following command:

```bash
prisma db pull
```

Prisma will then retrieve the schema from your database and map your tables, columns, indexes, and constraints into Prisma models, fields, indexes, and attributes in your `schema.prisma` file.

> **Caution:** If you've manually altered your `schema.prisma` file, some changes will be [over-written](https://www.prisma.io/docs/concepts/components/introspection#introspection-with-an-existing-schema) when you run `db pull`. This includes any manually created `@relation` scalars you've defined in your model. You can instead introspect just your `hubspot` schema to avoid any issues. Learn how in the [Prisma docs](https://www.prisma.io/docs/concepts/components/introspection#introspecting-only-a-subset-of-your-database-schema).

After introspecting your database, your `schema.prisma` file will be updated to include your `hubspot` schema and the underlying `contact`, `deal`, and `associations_contact_deal ` models:

```js
generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["multiSchema"]
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
  schemas  = ["hubspot", "public"]
}

model orgs {
  id                  Int     @id @default(autoincrement())
  name                String?
  subscription_status String?
  users               users[]

  @@schema("public")
}

model users {
  id         Int       @id @default(autoincrement())
  first_name String?
  last_name  String?
  email      String?
  org_id     Int?
  org        orgs?     @relation(fields: [org_id], references: [id], onDelete: NoAction, onUpdate: NoAction, map: "users_orgs_id_fk")
  contact    contact[]

  @@schema("public")
}

model associations_contact_deal {
  contact_id       String
  contact          contact? @relation(fields: [contact_id], references: [id])
  deal_id          String
  deal             deal?    @relation(fields: [deal_id], references: [id])
  sync_hash        String?  @map("_sync_hash")
  sync_inserted_at DateTime @default(now()) @map("_sync_inserted_at") @db.Timestamp(6)
  sync_updated_at  DateTime @default(now()) @map("_sync_updated_at") @db.Timestamp(6)
  updated_at       DateTime @default(now()) @map("_updated_at") @db.Timestamp(6)
  labels           String[]

  @@id([contact_id, deal_id], map: "associations_contact_deal_pk")
  @@schema("hubspot")
}

model contact {
  id                        String                      @id(map: "CONTACT_pk")
  sync_inserted_at          DateTime                    @default(now()) @map("_sync_inserted_at") @db.Timestamp(6)
  sync_updated_at           DateTime                    @default(now()) @map("_sync_updated_at") @db.Timestamp(6)
  updated_at                DateTime                    @default(now()) @map("_updated_at") @db.Timestamp(6)
  associatedcompanyid       Decimal?                    @db.Decimal
  company                   String?
  firstname                 String?
  industry                  String?
  jobtitle                  String?
  lastname                  String?
  numemployees              String?
  user_id                   Int?
  user                      users?                      @relation(fields: [user_id], references: [id])
  website                   String?
  associations_contact_deal associations_contact_deal[]

  @@index([updated_at], map: "_updated_at_idx")
  @@schema("hubspot")
}

model deal {
  id                        String                      @id(map: "DEAL_pk")
  sync_inserted_at          DateTime                    @default(now()) @map("_sync_inserted_at") @db.Timestamp(6)
  sync_updated_at           DateTime                    @default(now()) @map("_sync_updated_at") @db.Timestamp(6)
  updated_at                DateTime                    @default(now()) @map("_updated_at") @db.Timestamp(6)
  amount                    Decimal?                    @db.Decimal
  closedate                 DateTime?                   @db.Timestamp(6)
  createdate                DateTime?                   @db.Timestamp(6)
  dealname                  String?
  dealstage                 String?
  hs_priority               String?
  pipeline                  String?
  associations_contact_deal associations_contact_deal[]

  @@schema("hubspot")
}
```

### Define relationships

Out of the box, the schema generated via `prisma db pull` is almost entirely workable. But because Sequin doesn't enforce foreign key constraints, Prisma can't detect the relationships that exist across your `contact` and `deal` tables via the `associations_contact_deal` relation table. You'll add these relationships to your `schema.prisma` file.

To define a many-to-many relationship using a [relation table](https://www.prisma.io/docs/concepts/components/prisma-schema/relations/many-to-many-relations#relation-tables), you need to tell Prisma that the `deal_id` and `contact_id` fields in the `associations_contact_deal` model relate to the `id` field on the `deal` and `contact` models respectively. You'll do this by adding the two relation scalars:

```js
model associations_contact_deal {
  contact          contact  @relation(fields: [contact_id], references: [id])
  contact_id       String
  deal             deal     @relation(fields: [deal_id], references: [id])
  deal_id          String
  sync_hash        String?  @map("_sync_hash")
  sync_inserted_at DateTime @default(now()) @map("_sync_inserted_at") @db.Timestamp(6)
  sync_updated_at  DateTime @default(now()) @map("_sync_updated_at") @db.Timestamp(6)
  updated_at       DateTime @default(now()) @map("_updated_at") @db.Timestamp(6)
  labels           String[]

  @@id([contact_id, deal_id], map: "associations_contact_deal_pk")
  @@schema("hubspot")
}
```

Additionally, on the `deal` and `contact` models, you need to define the other side of this many-to-many relationship by pointing a new field back to the `associations_contact_deal` model:

```js
model deal {
  id               String                      @id(map: "DEAL_pk")
  sync_inserted_at DateTime                    @default(now()) @map("_sync_inserted_at") @db.Timestamp(6)
  sync_updated_at  DateTime                    @default(now()) @map("_sync_updated_at") @db.Timestamp(6)
  updated_at       DateTime                    @default(now()) @map("_updated_at") @db.Timestamp(6)
  amount           Decimal?                    @db.Decimal
  closedate        DateTime?                   @db.Timestamp(6)
  createdate       DateTime?                   @db.Timestamp(6)
  dealname         String?
  dealstage        String?
  hs_priority      String?
  pipeline         String?
  contacts         associations_contact_deal[]

  @@schema("hubspot")
}

model contact {
  id                  String                      @id(map: "CONTACT_pk")
  sync_inserted_at    DateTime                    @default(now()) @map("_sync_inserted_at") @db.Timestamp(6)
  sync_updated_at     DateTime                    @default(now()) @map("_sync_updated_at") @db.Timestamp(6)
  updated_at          DateTime                    @default(now()) @map("_updated_at") @db.Timestamp(6)
  associatedcompanyid Decimal?                    @db.Decimal
  company             String?
  firstname           String?
  industry            String?
  jobtitle            String?
  lastname            String?
  numemployees        String?
  user_id             Int?
  website             String?
  deals               associations_contact_deal[]

  @@index([updated_at], map: "_updated_at_idx")
  @@schema("hubspot")
}
```

As you may recall, you can also define a [one-to-one relationship](https://www.prisma.io/docs/concepts/components/prisma-schema/relations/one-to-one-relations) between the `user_id` field in the `contact` model with the `id` field on the `users` model. This will make querying across your internal data model and HubSpot data possible. To do so, add one more relation scalar to your `contact` model:

```js
model contact {
  id                  String                      @id(map: "CONTACT_pk")
  sync_inserted_at    DateTime                    @default(now()) @map("_sync_inserted_at") @db.Timestamp(6)
  sync_updated_at     DateTime                    @default(now()) @map("_sync_updated_at") @db.Timestamp(6)
  updated_at          DateTime                    @default(now()) @map("_updated_at") @db.Timestamp(6)
  associatedcompanyid Decimal?                    @db.Decimal
  company             String?
  firstname           String?
  industry            String?
  jobtitle            String?
  lastname            String?
  numemployees        String?
  user_id             Int?
  user                users?                      @relation(fields: [user_id], references: [id])
  website             String?
  deals               associations_contact_deal[]

  @@index([updated_at], map: "_updated_at_idx")
  @@schema("hubspot")
}
```

### Generate your Prisma Client

All the relationships in your data model are now defined in your `schema.prisma` file. The last step before writing your first query is to re-generate your Prisma client:

```bash
prisma generate
```

## Query HubSpot using the Prisma Client

Prisma gives you a modern, intuitive API for querying your HubSpot data. For instance, you can return all your HubSpot deals in one simple query:

```js index.js
async function get_all_deals() {
  const deals = await prisma.deal.findMany();

  console.log(deals);
}
```

No pagination. No authentication token. No metering your requests through a rate limiter.

Or, you can return all the deals at a certain stage as well as all the contacts associated with those deals:

```js index.js
async function get_qualified_deals() {
  const deals = await prisma.deal.findMany({
    where: {
      dealstage: "qualifiedtobuy",
    },
    include: {
      contacts: {
        include: {
          contact: true,
        },
      },
    },
  });

  console.log(deals);
}
```

You'll note that in your IDE, you get helpful type warnings and type-ahead support as you write these queries—niceties missing from the HubSpot API and SDK.

More impactful to your productivity, you can query HubSpot and your internal data together. For instance, you can query for all the `deals` associated to a specific `user` in your `public` schema:

```js
async function get_user_deals() {
  const user_deals = await prisma.users.findUnique({
    where: { email: eric@sequin.io },
    include: {
      contact: {
        include: {
          deals: {
            include: {
              deal: true,
            },
          },
        },
      },
    },
  });

  console.log(user_deals);
}
```

This query builds on the relationships you defined in your `schema.prisma` file to return the deals related to `eric@sequin.io`. In one Prisma query, you do the work of a SQL query paired with three nested calls to the HubSpot API.

## Migrations

Inevitably, the data you need from HubSpot will change, and you'll need to migrate your Prisma schema. While a more [comprehensive guide to migrations](https://rtpg.co/2021/06/07/changes-checklist.html) should be referenced to avoid downtime in your application, here are the order of operations to consider when using Sequin with Prisma.

As noted above, your `hubspot` schema is managed by Sequin. For simplicity, all migrations start in the Sequin Console and are applied to Prisma as database-first migrations. As a result, you will not use Prisma Migrate when making changes to the HubSpot objects and properties syncing to your database.

Here are some common scenarios:

### Adding or removing a HubSpot property

**Step 1:** If you're about to remove a HubSpot property, first remove it from your app and your Prisma Client.

**Step 2:** Add/remove the property to/from your sync by editing your table and column mapping in the Sequin Console.

When you click **Update**, Sequin will immediately migrate your database to add the new property as a new column or drop the column related to the property you removed. In the case where you added a new property, Sequin will begin the backfill process, syncing the property to your database.

**Step 3:** If you are adding a new property, you'll now **manually** add the field associated with the new column/property in the appropriate model in your `schema.prisma` file. It is easier to do this by hand as opposed to introspecting your database so that you can preserve the other relationship scalars.

**Step 4:** Run the `prisma generate` command in your terminal to update the Prisma Client.

### Adding or removing a HubSpot Object

**Step 1:** If you're about to remove a HubSpot object, first remove it from your app and your Prisma Client.

**Step 2:** Add/remove the object to/from your sync by editing your table and column mapping in the Sequin Console.

When you click **Update**, Sequin will run a migration to create or drop the tables related to your change.

**Step 3:** If you are adding a new HubSpot object to your sync, you'll now add these new tables as models in your `schema.prisma` file. You have two options:

1. **Manually** add or remove the appropriate models.
2. **⚠️ Partial Introspect Workaround ⚠️:** Partially introspect your database by first saving your existing `schema.prisma` file. Then follow Prisma's guide for [introspecting just a subset of your database](https://www.prisma.io/docs/concepts/components/introspection#introspecting-only-a-subset-of-your-database-schema). In this case, you'll create a new database user that can only access the new tables added to your database by Sequin. Then, append the new models generated by your introspection to your saved `prisma.schema` file. This is often a more straightforward path when adding an object to your HubSpot sync.

**Step 4:** Run the `prisma generate` command in your terminal to update the Prisma Client.

## Development lifecycle

You aren't using Prisma Migrate to track the migrations related to changes in your `hubspot` schema and the underlying Sequin sync. So how does this all work across dev and staging environments?

To support development and staging environments, you'll set up a dev HubSpot account and a dev Sequin sync. You'll then use Sequin's **copy config** setting to copy changes from the dev sync and database to your production sync and database. Here is the flow.

![Migrations](https://docs.sequin.io/assets/hubspot-prisma/007_dev_flow.png)


**Step 1:** Setup a [HubSpot development account](https://developers.hubspot.com/) to create a new HubSpot instance you can use for development.

**Step 2:** Sync your HubSpot development account as a new sync in Sequin. Follow the steps at the [start of this guide](#setup-your-hubspot-sync).

**Step 3:** Make changes and migrations using this development account and sync.

**Step 4:** When you are ready to deploy these changes to production, copy the changes from your development sync in Sequin to your production sync:

1. First, make sure any new properties or settings in your dev HubSpot instance have been applied to your production instance.
2. Then, click to edit your production sync in Sequin. In the **Select tables and columns** section, click the **Copy config from...** button. Select your dev sync to apply your development sync configuration to your production sync.
3. Click **Update**.

Sequin will run a migration to apply any schema changes.

**Step 5:** In Prisma, update your **schema.prisma** file to reflect your dev environment, then run the `prisma generate` command to update your client.