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:

  1. 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.
  2. 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:

Airtable CRM Template

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:

Support tool

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 Sync Inc 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 to Lambda

Airtable Setup

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

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

Add the sales CRM template

Sync Inc 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 Sync Inc:

  1. Go to https://app.syncinc.so/signup and create an account.

  2. Connect your Airtable base by going through the tutorial or clicking the Add Base button.

Add base to Sync Inc

  1. 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.

Click create in Sync Inc

  1. Sync Inc 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 Sync Inc database to your Lambda function.

Get your Sync Inc DB credentials

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:

  1. Create your account by entering an email, password, and account name.
  2. When prompted, choose to create a Personal account.
  3. Add a payment method (while a credit card is necessary to create an account, AWS comes with a generous free tier)
  4. Enter and verify your personal phone number.
  5. 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, Sync Inc, 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 Sync Inc 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:

  1. `zip` instructs your computer to create a zipped file
  2. `-r` instructs the `zip` command to do it's job by recursively compressing files and directories
  3. `function.zip` is the name of the zipped file that will be created in the process.
  4. `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:

Navigate to Lambda

Click the orange Create function button:

Create new function

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:

Create new function

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:

Upload zip

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

Lambda ready

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:

Lambda ready

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:

Get test id

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

Add 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:

Create test

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:

Deploy and test

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:

Run test

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 Sync Inc 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 Sync Inc database, which contains all this information.

You'll connect to your Sync Inc 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 Sync Inc database you'll need to retrieve your credentials from Sync Inc and then create a new environment variable to store the database password.

First, to get your credentials, simply open the Sync Inc 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:

DB credentials

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:

Navigate to environment variables

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 Sync Inc. Click Save:

Set environment variables

Your Lambda function should now look something like this:

Function with pg

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 Sync Inc 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:

  1. First, extract the `account_id` from the event that's passed into the `handler` function.
  2. Then, define your `query` using the SQL query above.
  3. Execute the query by passing in the `account_id` as a variable.
  4. 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 `accout_id` printed in the Function Logs:

Test results

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 formattedResonse = {
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 formattedResonse;
};

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

Final function

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:

Add AWS API Gateway as Trigger for Airtable Lambda

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:

Add AWS API Gateway as Trigger for Airtable Lambda

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:

Add AWS API Gateway as Trigger for Airtable Lambda

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:

Add AWS API Gateway as Trigger for Airtable Lambda

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

Add AWS API Gateway as Trigger for Airtable Lambda

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:

Add AWS API Gateway as Trigger for Airtable Lambda

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:

Add AWS API Gateway as Trigger for Airtable Lambda

Select `sync-inc-tutorial` from the drop down:

Add AWS API Gateway as Trigger for Airtable Lambda

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:

Add AWS API Gateway as Trigger for Airtable Lambda

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:

Add AWS API Gateway as Trigger for Airtable Lambda

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.

Airtable App
Basedash

Was this helpful?