Airtable provides a developer platform which allows you to create, read, update and delete records in a given base. Using our scripting block and Custom Blocks platforms, we’ve seen organizations create in-app widgets and plugins which allow users to send data to other services, such as AWS DynamoDB. Doing this creates a centralized hub where they can aggregate large volumes of data and make that data available to other AWS services.
Using AWS with Airtable
For many teams, AWS is their preferred development environment and is the backbone of many of their internal and customer facing systems. By integrating Airtable with your AWS environment, you can make your Airtable data available to these other services.
NOTE
This guide focuses on pushing data into DynamoDB via an AWS Lambda. By changing the Lambda code, you could also use this set up to move data into other AWS services, such as RDS, Redshift or S3.What you will need
- Access to an AWS account and environment
- Access to an Airtable account and Airtable API Key
- An Airtable base (we recommend starting with our Sales CRM template)
- Basic software development/programming skills. The code in this guide will be in JavaScript but it is possible to replicate this set up in any language.
Setup
There are a few sections to setting up your AWS instance. First we need to create the base where we want our data to live. Then we need to create the Dynamo instance where our data will live. After that, we can build the AWS Lambda and API Gateway environments which will then host the bulk of our code. Finally, we can configure our Airtable base to make API requests to this API to push data and actions into Dynamo.
Creating Airtable Base
Create your Airtable base where your data lives (we recommend starting with this Sales CRM template). On each table, create the fields for the data you want to capture.
Creating Dynamo Database
- In your AWS account, navigate to Services -> DynamoDB
- Click on Tables, and then Create table
- Give your table a name, and a primary key field name.
- The primary key defines what makes a given record unique. You should use the same value here as you do in your Airtable base to define unique records.
- Write this name down because we will need it later when we create our Lambda
- We generally call ours “record_id”
- Press “Create”
Creating Lambda and API Gateway to Handle Requests
1. Navigate to Services -> Lambda
2. Click “Create function”
3. Select Author from scratch
- Provide the function a name
- We chose Node.js 12.x, but you could use any language and framework you want.
- Then create the function
- We will come back here and actually add code in a little bit
4. Within your AWS account, navigate to Services -> API Gateway
- Click Create API -> REST API
- Leave all of these options as their default and give your API a name
5. Now we can create the resources for our API. We need two resources. Click Actions->Create Resource:
- Name: table
- Resource Path: table_name
Underneath the /{table_name} we want a resource for ID so that a user can update/delete an existing item by making a request to /{table_name}/{id}
- Create Resource
- Name: ID
- Resource Path: {id}
6. Then we can create methods under each of these. Our Lambda code will handle most of the routing logic based on HTTP method, so in this case:
Click on {table_name}
- Actions->Add Method
- Select ANY
- Integration Type: Lambda
- Use Lambda Proxy Integration: checked
- Lamda Function: the name of your lambda function
- Save
Click on {id}
- Actions->Add Method
- Select ANY
- Integration Type: Lambda
- Use Lambda Proxy Integration: checked
- Lamda Function: the name of your lambda function
7. Click Actions->Deploy API
- This step is required anytime you make changes to the API Gateway set up
- On first set up, it will ask you to set up a Deployment stage. We call ours “default”
- Keep record of the Invoke URL as that’s what we actually use to make API requests
8. You can try making a request via cURL or Postman: curl --location --request GET 'https://{{invoke_url}}/asdasdasd'
Add Lambda to IAM Role
Before Lambda can write data to your Dynamo DB, you have to grant it that permission. This can be done in the IAM service where we assign the Lambda to a role which gives it the ability to issue CRUD operations to your Dynamo DB instance.
- Go to Services -> IAM
- Go to Roles -> create new role
- Select AWS service and Lambda
- There is already a default policy for DynamoDB access called AmazonDynamoDBFullAccess. Find it and check the policy.
- Walk through the rest of the creation process and apply additional data as necessary
- Go back to your Lambda and click on Permissions
- Next to Execution role click “Edit”
- Select the policy you just created and press save
Write Lambda Code
We can now go back to our Lambda and update its code to actually manage these incoming operations. We will configure:
- Create new record
- Update existing record
- Delete existing record
Create will use the /{table_name} endpoint and take a JSON body payload. Update and delete will use the /{table_name}/{id} endpoint to perform that action on a specific record. Sample code: https://github.com/TheF1rstPancake/airtable_lambda_dynamodb
TIP
You can download that repository as a ZIP and then upload the ZIP directly into Lambda
Our code also assumes you have the following environment variables configured which you can set in the Environment variables section underneath the Lamda code editor.
- CORS_ORIGIN: *
- The domains you allow cross-origin requests from
- If you know the subset of domains, you can put them here to decrease exposure
- IS_CORS: true
- Can set to false if you do not want to allow cross origin requests
- PRIMARY_KEY: record_id
- The DynamoDB key name that you set when creating your Dynamo instance
TIP
Allowing cross-origin requests is necessary for this solution to work, but you should familiarize yourself with the risks before doing so.Configure Airtable Base With Scripts and Buttons
Airtable’s scripting block allows you to write JavaScript (or TypeScript) code and deploy it into your base. This enables you to further optimize your workflows by injecting custom logic into your application.
Scripting block supports the ability to make web-requests to other services. Using this we can now make API calls out to our AWS API Gateway + Lambda application to create and delete data within our DynamoDB database.
In this use-case, we want users to consciously make the decision to push updates into DynamoDB. Airtable is the execution platform where users plan, draft and collaborate on content. Once it’s ready to publish into our DyanmoDB instance, the user can push a button to send that record into DynamoDB. If the user needs to pull the information from DynamoDB because it’s no longer relevant, they should be able to push another button to trigger that deletion.
Airtable also supports a button field which can be configured to take a number of different actions after the user clicks on it. One of those actions is the ability to pass a record’s details to a script and then execute the script with those details.
To set this up you’ll first need to create your scripts and then create the button fields which can be used to trigger those scripts.
Update Script
Create a new scripting block in your base and use the following code:
const base_url = '{{YOUR AWS API GATEWAY URL}}';
const dynamo_table_name = '{{YOUR DYNAMODB TABLE NAME}}';
const primary_dynamo_field = '{{YOUR DYNAMODB PRIMARY FIELD/KEY NAME}}';
// build the URL
const lambda_url = base_url + dynamo_table_name;
// get our different tables
const table = base.getTable('Table 1');
const foreign_table = base.getTable('Table 2');
// get the record from the button action,
// or allow the user to run the script and pick a specific record
const record = await input.recordAsync("Select record to update", table);
/**
* For a given record, convert it into a JSON object
* This function iterates over all fields in a record and converts it into a format that we can then send to Dynamo
* Most of the logic is managing linked record fields.
* We recursively call the function on any linked records, so that we get all of the linked records' information as well
*/
async function recordToJson(table, record, linked_table_id) {
var p = {};
for(var f of table.fields) {
if(f.type === 'multipleRecordLinks' && f.options.linkedTableId !== linked_table_id) {
var foreign = base.getTable(f.options.linkedTableId);
var res = await foreign.selectRecordsAsync();
var foreign_recs = record.getCellValue(f.id);
foreign_recs = foreign_recs === null ? [] : foreign_recs
p[f.name] = [];
for(var r of foreign_recs){
p[f.name].push(await recordToJson(foreign, res.getRecord(r.id), table.id));
}
p[f.name] = JSON.stringify(p[f.name]);
} else if (f.type !== 'button' && f.type !== 'multipleRecordLinks') {
p[f.name] = record.getCellValueAsString(f);
}
}
return p;
}
// build the payload to send and then send to our Lambda
var payload = await recordToJson(table, record);
var p = await fetch(
lambda_url,
{
method:'POST',
body: JSON.stringify(payload),
headers: {
'Content-Type': 'application/json'
}
}
);
var resp = await p.json();
output.markdown('Update sent!');
output.inspect(resp);
// then write the last updated time for this record back to the table
output.markdown('Updating time sent');
await table.updateRecordAsync(
record.id,
{
'Last Pushed to Dynamo': (new Date()).toISOString()
}
);
output.markdown('## Done!');
Delete Script
Create another scripting block and use the following code:
const base_url = '{{YOUR AWS API GATEWAY URL}}';
const dynamo_table_name = '{{YOUR DYNAMODB TABLE NAME}}';
const primary_dynamo_field = '{{YOUR DYNAMODB PRIMARY FIELD/KEY NAME}}';
const table = base.getTable('Table 1');
const record = await input.recordAsync("Select record to update", table);
const url = `${base_url}/${dynamo_table_name}/${record.getCellValueAsString(primary_dynamo_field)}`;
var p = await fetch(
url,
{
method:'DELETE',
headers: {
'Content-Type': 'application/json'
}
}
);
var resp = await p.json();
output.markdown('Record deleted!');
output.inspect(resp);
await table.deleteRecordAsync(record.id);
output.markdown('Deleted record from base');
Create the Button Fields
To wrap this all up, we can now create two button fields:
- Create/Update Record
- Delete Record
When creating the field:
- Action: Run Script
- Dashboard: the dashboard you installed the scripting blocks under
- Block: the corresponding update/delete script
You can then trigger these scripts by clicking on the buttons and see the corresponding data in your AWS DynamoDB instance.
Example Base
This base is an example of what this looks like when everything is connected. You can drop your AWS Lambda details in after creating them.
What next?
There are many different ways to go about integrating AWS Lambda/Dynamo DB and Airtable! Would love to see how you and your team approach this integration and what specifically you’re trying to leverage Airtable for.
Feel free to post at our Community Page about your integration.