These docs are out of date.We’ve made significant changes and improvements to the product since these docs were written. As such, they don’t reflect how Sequin works today.We’re keeping them up here a bit longer before permanently archiving them.Go to docs home.

Build Airtable integrations using AWS Lambda

Lets build a custom API endpoint to power internal tools using Airtable with AWS Lambda. The API will allow you to GET all the relevant data from an Airtable CRM with just one API call. Then, you can use this API to surface the account data in your Airtable CRM across your company - from support, to finance, and more.

What is AWS Lambda

AWS Lambda lets you run code in the cloud without needing to set up or manage a server. There are two big benefits of using AWS Lambda:

You don’t need to manage a server. You’re responsible for just the code inside a function – the rest is taken care of for you. No need to mess with configuring servers, deploys, or (to a large extent) worrying about scale.
Lambdas are economical, especially for the kinds of lighter-weight workloads you’ll typically run on Airtable. You only pay when your function is running. And on the AWS free tier you can trigger a Lambda 1 million times per month and not spend a penny.

In short, Lambda abstracts all the admin work so all you need to think about is what you want your code to do. And because Lambda supports runtime environments like Node.js, Python, and Ruby (as well as their full ecosystem of libraries) you can build just about anything. So with a Lambda, you might write a function to pull data from your Airtable base, clean it up, and send it to another application. Or, you might find and update fields in your Airtable base when some sort of event occurs or form is submitted. If this sounds like what you would otherwise do with tools like Zapier, Parabola, or Integromat - then you are getting the picture. The big difference here is how flexible, reliable, and inexpensive Lambda is compared to these low-code alternatives. Moreover, Airtable’s growing support for developer tools makes a Lambda even more powerful. You can easily trigger a lambda with an automation, scripting block or custom app. The possibilities are growing day by day.

The plot

Before we dive in, let’s set the stage for the Lambda you are going to build in this tutorial. Let’s say you you’re using Airtable as a CRM. You have a list of accounts, opportunities (potential new sales), their estimated value, and information about the sales process so far:

You want to expose all the details for an account - including all the contacts, interactions, and opportunities - to your support tool (and soon enough other internal tools) so that your team can service accounts faster:

To do so, your going to build and deploy a custom API that receives a GET request with an account_id as a parameter:

$ curl https://your-new-api.com/{{account_id}}

The API will trigger your Lambda function (running Node.js) to parse the account_id, find the account in Airtable, retrieve all its associated data, and then return a nicely formatted JSON object with all the account details:

{
  account_id: 'recfMv4w8AudcLx80',
  account_name: 'Timbershadow',
  company_website: 'https://www.example.com',
  company_linkedin: 'http://linkedin.com/in/thisisanexample',
  hq_address: null,
  size: '5000-10000',
  opportunities: [
    {
      id: 'recYaxf6rCmyjBJch',
      opportunity_name: 'Timbershadow expansion',
      status: 'Closed—won',
      priority: 'Medium',
      owner: 'Casey Park',
      estimated_value: '6154',
      proposal_deadline: null,
      expected_close_date: null
    }
  ],
  contacts: [
    {
      id: 'rec6z3PxeLWjRpbVD',
      contact_name: 'Scott Brewer',
      title: 'Deputy sheriff',
      department: 'Human resources',
      email: 'scott@example.com',
      phone: '(123) 456-7890',
      linkedin: 'http://linkedin.com/in/thisisanexample',
      vip: false
    }
  ],
  interactions: [
    {
      id: 'recII25g22TMgpecj',
      interaction: 'Timbershadow expansion—Demo',
      type: 'Demo',
      interaction_time: 2020-08-24T20:00:00.000Z
    },
    {
      id: 'recXxIWShP2H20whK',
      interaction: 'Timbershadow expansion—Discovery',
      type: 'Discovery',
      interaction_time: 2020-08-13T16:00:00.000Z
    }
  ]
}

To make the Lambda function easier to build you’ll use a Sequin real-time replica database to query Airtable for the data you need using SQL. This makes your function faster by removing the need to make four separate API calls using the the Airtable REST API with a filterBy parameter to get the same data set. All together, your integration will look something like this:

Airtable Setup

First, get Airtable set up by adding the CRM template to your Airtable workspace:

Log into your Airtable workspace and then open the sales CRM template in a new tab.
Click the Use template button to add the sales CRM template to your workspace.

Sequin Setup

Now, let’s turn the sales CRM base we just added to Airtable into a Postgres database that works seamlessly with AWS Lambda using Sequin:

Go to https://console.sequin.io/signup and create an account.
Connect your Airtable base by going through the tutorial or clicking the Add Base button.

You’ll be prompted to enter your Airtable API key. After that, select the Sales CRM base and all it’s tables. Then click Create.

Sequin will provision you a Postgres database on AWS and begin syncing all the data in your Airtable base to it. You’ll be provided with credentials for you new database. Keep these handy as you’ll use them to connect your Sequin database to your Lambda function.

AWS Setup

You’ll need an Amazon Web Services account to use AWS Lambda. This AWS help article will walk you through the steps of creating you account. For this tutorial, I recommend using a personal account that you feel comfortable experimenting with. To do so:

Create your account by entering an email, password, and account name.
When prompted, choose to create a Personal account.
Add a payment method (while a credit card is necessary to create an account, AWS comes with a generous free tier)
Enter and verify your personal phone number.
Choose the Developer support plan for now.

It might be a little hyperbolic to say, but you now have access to one of the most powerful tools in existence today. Your new AWS account comes with a generous free tier which includes 1 million Lambda requests per month. So again, while you will need to enter a credit card, you’ll need to run some real volume before you’ll have to start paying.

Initialize your Airtable Lambda

With Airtable, Sequin, and AWS all set up you are now ready to create the actual Lambda function. For this tutorial, you’ll write a Node.js Lambda function. Because this function needs to access the Sequin Postgres database you created earlier, you’ll install the node-postgres library using npm. To use an npm package in a Lambda, it’s easiest to initialize the function on your computer, zip it up, and then upload it to the AWS console.

If you don’t have it already, install Node.js and npm on your computer.

Create a new directory called sync-inc-tutorial and run npm init -y to easily add a package.json to your directory.

$ mkdir sync-inc-tutorial
$ cd sync-inc-tutorial
$ npm init -y

Now, install pg using npm:

$ npm install pg --save

And lastly, add an index.js to your directory:

$ touch index.js

The component parts of a working Node.js function are now in place. The last step is to zip up the function so you can upload it to AWS Lambda:

$ zip -r function.zip index.js node_modules package-lock.json package.json

This last command to compress your function is a little complex, so here is a quick breakdown:

zip instructs your computer to create a zipped file
-r instructs the zip command to do it’s job by recursively compressing files and directories
function.zip is the name of the zipped file that will be created in the process.
index.js node_modules package-lock.json package.json is the list of all the files and directories that need to be compressed.

Now, let’s set up the Lambda in the AWS console. Navigate to the AWS Lambda Functions page using the search bar at the top of the page:

Click the orange Create function button:

Select “Author from scratch” and then name the function sync-inc-tutorial. You can leave everything else in the default state (including the runtime of Node.js). Then click the orange Create function button:

Once your Lambda is created, you’ll be presented with a console where you can configure your Lambda, add triggers, and edit your function. To keep things easy, from here on out, you’ll build the rest of you Lambda right on this page. To initialize your Lambda, upload the function you just zipped up. Click the Upload from button and select .zip file:

After uploading function.zip, you’ll see that your function is not only ready to be built upon - but it’s already been deployed:

Build the Airtable Lambda function

As you have learned, a Lambda is simply a function that runs in the cloud. Now, you are ready to write that function. To get your bearings, you’ll start with a very simple Lambda function and run a test to see how things work end-to-end.

Export a handler function

The first step in writing your Lambda is to tell AWS which function to run when the Lambda is triggered by an event. This function is called the handler function. You’ll note partway down the page where you can define the handler:

Here, you can see that that default handler is simply index.handler where index is the name of the file and handler is the name of the function being exported from that file. For this tutorial, there is no reason to deviate from the default - so edit the index.js file to export a function called handler which takes in an event as a argument and simply returns the event:

exports.handler = async function (event) {
  return event;
};

Setup a test event

To test the simple Lambda function you just wrote, you’ll create a test event the replicates the real world event you expect to trigger your Lambda. As you’ll recall, you are going to trigger your Lambda with an API call, or more specifically an AWS API Gateway event. The event is going to include an account_id, the id of an account in your Airtable CRM:

$ curl https://your-new-api.com/{{account_id}}

Then your Lambda will take this account_id and return all the details of the account including the associated contacts, opportunities, and interactions. So let’s test your Lambda function by passing in an API Gateway test event that includes the account_id as a parameter. As a first step, you need a real account_id from your Airtable CRM. To get one, open up your Airtable CRM in a new tab, select the Accounts table, right click on an account and select Copy record URL:

Now, go back to AWS and click the Test button. Select Configure test event:

In the modal that appears, select Create a new test event. In the event template search and select API Gateway Proxy and name your event apiEvent. Customize the event to include an account_id as a parameter. You’ll see that on line 15 you can edit the pathParameters object to include the key account_id. For the value - paste the URL you retrieved from Airtable and delete everything but the record_id (which starts with rec). Your test event should look like this:

When you are done, click the orange Create button. To test your Lambda function you’ll need to first deploy the changes you made by clicking the orange Deploy button and then initiate your test by clicking the Test button:

When you run the test, your Lambda function will be triggered and the results will appear in a new Execution Result tab. If everything is working properly, you should see your test account_id comes right through:

It’s worth noting, when you develop a Lambda function in the AWS console as you are doing here, there is no “dev” environment built in. To test your code you need to deploy it straight to production - so bear this in mind when making edits to existing Lambda functions.

Connect to your Sequin database

You now know how a simplistic Lambda functions behaves end-to-end. You know the shape of the event object that will trigger your function and how it passes through your function. It’s time to make your Lambda function do some work for you. To do so, you need to extract the account_id from the event trigger and retrieve the details of the account from Airtable. As a first step, you’ll setup the connection to your Sequin database, which contains all this information. You’ll connect to your Sequin database using the node-postgres library you added to your function before you uploaded it to AWS. Before the handler function, require pg, define a new client, and open up a connection:

const { Client } = require("pg");
const client = new Client({
  host: "evening-soiree.syncincdb.com",
  user: "YOU_SYNC_INC_DB_USER",
  database: "YOU_SYNC_INC_DB_NAME",
  password: process.env.PG_PASSWORD,
  port: 5432,
});
client.connect();

exports.handler = async function (event) {
  return event;
};

As you can see, to securely connect to your Sequin database you’ll need to retrieve your credentials from Sequin and then create a new environment variable to store the database password. First, to get your credentials, simply open the Sequin console in a new tab and click the black Connect button on your Sales CRM resource. You’ll find all your credentials at the bottom of the modal:

Next, to protect your database credentials, you should use an environment variable (i.e. process.env.PG_PASSWORD ) to store your database password. This keeps the password out of the code, which you might one day commit to version control (eg git). To set the environment variable in AWS Lambda click on the Configuration tab and then select Environment variables followed by Edit:

In the modal that appears, click Add environment variable, enter the name of your variable (in this case PG_PASSWORD), and then enter the database password you retrieved from Sequin. Click Save:

Your Lambda function should now look something like this:

Query your Airtable data using SQL

With the database connection in place, you’re ready to access your Airtable CRM data using SQL. As another refresher, you want to show your support agents all the details of an account, including an account’s associated contacts, opportunities, and interactions. You’ll need to write a SQL query to extract all this information by joining all the tables in the CRM. Here’s what that will look like:

SELECT
	accounts.id AS "account_id",
	accounts.name AS "account_name",
	accounts.company_website,
	accounts.company_linkedin,
	accounts.hq_address,
	accounts.size,
	contacts.id AS "contact_id",
	contacts.name AS "contact_name",
	contacts.title,
	contacts.department,
	contacts.email,
	contacts.phone,
	contacts.linkedin,
	contacts.vip,
	opportunities.id AS "opportunity_id",
	opportunities.opportunity_name,
	opportunities.status,
	opportunities.priority,
	opportunities."owner" ->> 'name' AS "owner",
	opportunities.estimated_value,
	opportunities.proposal_deadline,
	opportunities.expected_close_date,
	interactions.id AS "interaction_id",
	interactions.interaction,
	interactions."type",
	interactions.date_and_time AS "interaction_time",
	interactions.status[1]
FROM
	accounts
	LEFT JOIN contacts ON contacts.id = ANY (accounts.contacts)
	LEFT JOIN opportunities ON opportunities.id = ANY (accounts.opportunities)
	LEFT JOIN interactions ON interactions.id = ANY (opportunities.interactions)
WHERE
	accounts.id = '<<account_id>>';

This query is a JOIN across four tables with three noteworthy Postgres flourishes:

opportunities."owner" ->> 'name' AS "owner": The opportunities column is a Collaborator field type over on Airtable. The Collaborator type lets you choose from a list of collaborators on a given base. This type is represented as a jsonb column in your Postgres database that includes information about the collaborator like their name and email. The ->> is how we access properties inside of a jsonb column in Postgres.
interactions.status[1]: The status column is a “Select” field type over on Airtable. These are represented as text[] (text array) columns in Postgres, as they can contain multiple values. Here, this query just grabs the first value from the array.
ANY(): The Airtable CRM uses linked records to associate accounts, contacts, opportunities, and interactions to one another. In Postgres, these linked records are stored as text[] (text array) because they can contain more than one value (i.e. an account can have many contacts). So to perform a complete JOIN on an array, you use ANY() to match each value of the array with the other table.

You can learn more about querying your Airtable base using SQL in the Sequin docs or in this helpful guide about Postgres Arrays.

As another incremental step, run the SQL query above inside your Lambda function and print the results to see everything flowing together. Here’s the strategy you’ll use:

First, extract the account_id from the event that’s passed into the handler function.
Then, define your query using the SQL query above.
Execute the query by passing in the account_id as a variable.
Finally, for testing purposes, log the results.

Here is what your handler() function should look like now:

exports.handler = async function (event) {
  let account_id = event.pathParameters.account_id;
  let query = `
SELECT
	accounts.id AS "account_id",
	accounts.name AS "account_name",
	accounts.company_website,
	accounts.company_linkedin,
	accounts.hq_address,
	accounts.size,
	contacts.id AS "contact_id",
	contacts.name AS "contact_name",
	contacts.title,
	contacts.department,
	contacts.email,
	contacts.phone,
	contacts.linkedin,
	contacts.vip,
	opportunities.id AS "opportunity_id",
	opportunities.opportunity_name,
	opportunities.status,
	opportunities.priority,
	opportunities. "owner" ->> 'name' AS "owner",
	opportunities.estimated_value,
	opportunities.proposal_deadline,
	opportunities.expected_close_date,
	interactions.id AS "interaction_id",
	interactions.interaction,
	interactions. "type",
	interactions.date_and_time AS "interaction_time",
	interactions.status [1]
FROM
	accounts
	LEFT JOIN contacts ON contacts.id = ANY (accounts.contacts)
	LEFT JOIN opportunities ON opportunities.id = ANY (accounts.opportunities)
	LEFT JOIN interactions ON interactions.id = ANY (opportunities.interactions)
WHERE
	accounts.id = $1;`;

  let res = await client.query(query, [account_id]);
  console.log(res.rows);
};

Note that the only parameter in your SQL query is account_id. The query string includes the placeholder $1, which is populated by the variable account_id in the second argument to query().

Test your function by first deploying your updates by clicking the orange Deploy button and then clicking the Test button. In the Execution Results tab you should see all the CRM data for your test account_id printed in the Function Logs:

Format your result

Let’s add some logic to your Lambda function to handle errors and properly format your data. First, equip your function to handle some basic errors. If the event trigger doesn’t include an account_id you want to throw a 400. If an account_id is defined, you want to run the query. If the query succeeds you want to return a 200 with the results. And if the query returns an error you want to catch the error and throw a 500:

if (!account_id) {
  return {
    statusCode: 400,
    body: JSON.stringify("Invalid path, expected account_id"),
  };
}
try {
  let res = await client.query(query, [account_id]);
  return {
    statusCode: 200,
    body: JSON.stringify(res.rows),
  };
} catch (e) {
  return {
    statusCode: 500,
    body: JSON.stringify(e.stack),
  };
}

With the basic scaffolding in place, you can now format the response of a successful query so it’s easier for your support tool to ingest this data. We’ll do this with a helper function outside the handler() function that will properly format all the data related to an account into one, clean object. Below is the completed Lambda function that is cleaned up by pulling the query definition outside the Lambda function and appending the helper function to the end:

const { Client } = require("pg");
const client = new Client({
  user: "rut95qj06qeo103",
  host: "evening-soiree.syncincdb.com",
  database: "db5g51mvho7513b",
  password: process.env.PG_PASSWORD,
  port: 5432,
});
const query = `
SELECT
	accounts.id AS "account_id",
	accounts.name AS "account_name",
	accounts.company_website,
	accounts.company_linkedin,
	accounts.hq_address,
	accounts.size,
	contacts.id AS "contact_id",
	contacts.name AS "contact_name",
	contacts.title,
	contacts.department,
	contacts.email,
	contacts.phone,
	contacts.linkedin,
	contacts.vip,
	opportunities.id AS "opportunity_id",
	opportunities.opportunity_name,
	opportunities.status,
	opportunities.priority,
	opportunities. "owner" ->> 'name' AS "owner",
	opportunities.estimated_value,
	opportunities.proposal_deadline,
	opportunities.expected_close_date,
	interactions.id AS "interaction_id",
	interactions.interaction,
	interactions. "type",
	interactions.date_and_time AS "interaction_time",
	interactions.status [1]
FROM
	accounts
	LEFT JOIN contacts ON contacts.id = ANY (accounts.contacts)
	LEFT JOIN opportunities ON opportunities.id = ANY (accounts.opportunities)
	LEFT JOIN interactions ON interactions.id = ANY (opportunities.interactions)
WHERE
	accounts.id = $1;`;

client.connect();

exports.handler = async function (event) {
  let account_id = event.pathParameters.account_id;
  if (!account_id) {
    return {
      statusCode: 400,
      body: JSON.stringify("Invalid path, expected account_id"),
    };
  }
  try {
    let res = await client.query(query, [account_id]);
    let cleanResponse = formatResponseHelper(res);
    return {
      statusCode: 200,
      body: JSON.stringify(cleanResponse),
    };
  } catch (e) {
    return {
      statusCode: 500,
      body: JSON.stringify(e.stack),
    };
  }
};

let formatResponseHelper = (res) => {
  let uniqueById = (objects) =>
    objects.reduce((memo, obj) => {
      if (memo.find((o) => o.id == obj.id)) {
        return memo;
      } else {
        return memo.concat(obj);
      }
    }, []);

  let allInteractions = res.rows.map(
    ({ interaction_id, interaction, type, interaction_time }) => {
      return { id: interaction_id, interaction, type, interaction_time };
    }
  );

  let allContacts = res.rows.map(
    ({
      contact_id,
      contact_name,
      title,
      department,
      email,
      phone,
      linkedin,
      vip,
    }) => {
      return {
        id: contact_id,
        contact_name,
        title,
        department,
        email,
        phone,
        linkedin,
        vip,
      };
    }
  );

  let allOpportunities = res.rows.map(
    ({
      opportunity_id,
      opportunity_name,
      status,
      priority,
      owner,
      estimated_value,
      proposal_deadline,
      expected_close_date,
    }) => {
      return {
        id: opportunity_id,
        opportunity_name,
        status,
        priority,
        owner,
        estimated_value,
        proposal_deadline,
        expected_close_date,
      };
    }
  );

  let formattedResponse = {
    account_id: res.rows[1].account_id,
    account_name: res.rows[1].account_name,
    company_website: res.rows[1].company_website,
    company_linkedin: res.rows[1].company_linkedin,
    hq_address: res.rows[1].hq_address,
    size: res.rows[1].size,
    opportunities: uniqueById(allOpportunities),
    contacts: uniqueById(allContacts),
    interactions: uniqueById(allInteractions),
  };

  return formattedResponse;
};

You can once again deploy and test your Lambda function to make sure everything is working:

Connect the AWS API Gateway

You’ve created a Lambda function that extracts and formats the data your customer support team needs. It is already deployed in the cloud and is ready to go. Now, you just need to wire it up to an API endpoint. The endpoint will be an API GET request via AWS’ API Gateway. It’s a speedy process that just requires some clicking (shout out to the amazing AWS UX team - if you exist :).

Create the API Gateway

First, add a trigger to your Lambda by expanding the Function overview section and clicking + Add trigger:

Add AWS API Gateway as Trigger for Airtable Lambda

On the “Add trigger” screen, select API Gateway from the list:

Configure the new API by selecting to Create an API. The type should be HTTP API and you can leave the security Open for now. When everything is set, click the orange Add button:

AWS will now set up a new API Gateway, connect it to your Lambda function, and set the permissions. When the process is done you’ll return to the configuration page of your Lambda function.

Configure the API Gateway

You now need to make a small alteration to the API so that it accepts account_id as a parameter. Click on the name of your newly created API to open the API Gateway settings in a new tab:

Click the Routes tab and then click on the default ANY method. Because this default endpoint doesn’t include the account_id parameter, you can delete it by clicking the Delete button:

With the default endpoint removed, create a new endpoint by clicking Create:

The new endpoint you need to create should accept account_id as a parameter. Because your API is going to return all the details of the account, a GET method makes sense. So define a new endpoint by selecting GET as the method and typing /{account_id} as the path. Once configured, click Create:

Now, you need to integrate this endpoint with your Lambda function. Click the GET method under the new route you just created and click Attach integration:

Select sync-inc-tutorial from the drop down:

Your new API endpoint is now configured. Head back to the tab with your Lambda function, make sure you are on the Triggers page of the Configuration tab and click the refresh button. You’ll now see both the new API endpoint and a warning indicator next to the old endpoint you deleted. To keep things clean, select the old endpoint and delete it:

The last step is to test that your new API works. Copy the URL for your API, and curl it in your terminal using the same account_id you’ve been using in your Lambda test event:

$ curl https://{{YOUR_API_GATEWAY_URL_HERE}}.amazonaws.com/default/recfMv4w8AudcLx80

You can pipe the results into jq to make the results look pretty:

You just built a completely custom API that returns all the data for an account with one API call. Now that the endpoint is ready, you can feed into your support app and, voila.

Overall

Auth

Streams

Mutations

How to

AWS Lambdas

Build Airtable integrations using AWS Lambda

What is AWS Lambda

The plot

Airtable Setup

Sequin Setup

AWS Setup

Initialize your Airtable Lambda

Build the Airtable Lambda function

Export a handler function

Setup a test event

Connect to your Sequin database

Query your Airtable data using SQL

Format your result

Connect the AWS API Gateway

Create the API Gateway

Configure the API Gateway

Overall

Auth

Streams

Mutations

How to

Documentation Index

​Build Airtable integrations using AWS Lambda

​What is AWS Lambda

​The plot

​Airtable Setup

​Sequin Setup

​AWS Setup

​Initialize your Airtable Lambda

​Build the Airtable Lambda function

​Export a handler function

​Setup a test event

​Connect to your Sequin database

​Query your Airtable data using SQL

​Format your result

​Connect the AWS API Gateway

​Create the API Gateway

​Configure the API Gateway

Build Airtable integrations using AWS Lambda

What is AWS Lambda

The plot

Airtable Setup

Sequin Setup

AWS Setup

Initialize your Airtable Lambda

Build the Airtable Lambda function

Export a handler function

Setup a test event

Connect to your Sequin database

Query your Airtable data using SQL

Format your result

Connect the AWS API Gateway

Create the API Gateway

Configure the API Gateway