Using Sequin to connect to APIs with SQL

With Sequin, developers can build on top of third-party services like Salesforce or HubSpot using SQL. Sequin runs a real-time sync process that pulls data from an API into a Postgres database. Any time a record changes in the API, that change is synced to the database. Likewise, Sequin intercepts mutations made to records in the database and applies them to the API first before committing them to the database.
You can use Sequin to build Airplane tasks on top of third-party services using a PostgreSQL resource. Ultimately, this means you can use SQL tasks or JavaScript/Python tasks with a SQL built-in to interact with third-party APIs, even joining this API data with your internal data. And because all of the data is stored in Postgres, you don't need to worry about rate limits or pagination.

Create a Sequin sync

Before you can use Sequin with Airplane, you'll need to create a Sequin sync to your Postgres resource:
Step 1: After signing up for Sequin, connect Sequin to the API you want to sync:
Step 2: Configure your schema by selecting the tables and columns you want to sync.
Step 3: Sequin will prompt you to connect to a Postgres database. You can choose to connect an Airplane managed Postgres database a free demo Postgres instance hosted by Sequin, or your own Postgres database.
Step 4: Click Create and Sequin will begin syncing your data.
Sequin will provide you with the connection instructions for your Postgres database. Keep this tab open, as you'll need it to configure your Airplane resource unless you chose to use an Airplane managed Postgres database in which case you're already connected!
For more details on setting up a Sequin sync, see this guide.

Create an Airplane resource

If you didn't choose to use an Airplane managed Postgres database, you'll need to create an Airplane resource to connect to the database.
Sequin uses a Postgres Proxy to interface with your Sequin-synced tables. The Proxy lets Sequin capture inserts, updates, and deletes made to the database database and commit them to the API.
You can treat this proxy as a regular Postgres database and enter the connection details in the resource configuration.
Step 1: Go to your Airplane dashboard and click the databases icon labeled "Resources." Click the "+" icon in the environment where you want to create the resource.
Step 2: Select PostgreSQL.
Step 3: This opens up the Create new resource slide-out. Give the connection a name (like "salesforce-sequin") and paste the values for host, database name, database username, and database password from the Connection instructions tab of your Sequin dashboard.
Step 4: Click Test connection to ensure it's working, then click Save.

Use the Postgres resource in Airplane

Now that you have a resource connected to your Sequin-synced Postgres database, you can connect it to a a task to read and write data to the API. See Using resources in tasks for instructions on how to connect a resource to a task. You can connect your resource to a SQL task or a JavaScript/Python task with a SQL built-in.
For example, you can write a task that queries Salesforce to find all the Salesforce Contacts belonging to a given account:
Step 1: Follow the SQL getting started guide to initialize a new SQL task.
Step 2: Update the generated task definition to connect to the resource you created in the previous step, and configure your task to take a single parameter account_id:
# in list_account_contacts.task.yaml
- slug: account_id
name: Account ID
type: shorttext
required: true
resource: my_postgres_resource
account_id: "{{params.account_id}}"
Step 3: Update the generated SQL query to query the Salesforce Contact table, filtering by account_id:
-- list_account_contacts.sql
select * from
where account_id = :account_id
limit 100;
You can also write a task that inserts a new contact into Salesforce:
-- insert_contact.sql
insert into (first_name, last_name, email)
values (:first_name, :last_name, :email);
Step 4: Once your tasks are complete, run airplane deploy to deploy them onto Airplane!

Next steps

With Sequin, you can build Airplane apps on top of sources like Salesforce, Airtable, and HubSpot. To learn more, check out our docs on SQL tasks and read more about how Sequin works.