The Complete Developer's Guide to Airtable

2021-01-14|⌛️28 min

You might have missed the memo: Airtable is ready for developers.

In the span of a year, Airtable went from a simple REST API to now supporting scripting, a custom apps SDK, built in automations, and a small but growing ecosystem of third-party tools and services.

As a developer looking to build on Airtable, where should you start? And what is the developer experience like?

This guide aims to help developers navigate Airtable and build great applications on this growing platform.

In or on Airtable?

Who is your user and what do they need? This age-old question is still the first one to ask as you begin to consider which Airtable developer tools to use.

At a high-level, you can classify Airtable's suite of developer tools as either supporting use cases that happen inside the Airtable interface (i.e. in Airtable) or outside Airtable in another app or tool (i.e. on Airtable).

in or on diagram

When you are building in Airtable, the user is logged into Airtable and using your software within the Airtable interface. For any code you want to run in Airtable you'll be using either scripts, automations, or the custom app SDK.

Note: Yes, I know, automations can trigger actions outside of Airtable. But by and large, the end user is in Airtable.

If you are building on Airtable, then you are building for users outside of Airtable. This might be a custom internal tool, a dashboard built in Google Data Studio, a public Next.js website, or inside another SaaS application all together. In these use cases, you'll be using the Airtable REST API directly or using a tool like the one I helped build - Sync Inc.

As you decide whether to build in on on Airtable, you should consider what Airtable plan your users are on as well. The REST API is available on every plan to every Airtable user. Automations are available on every plan, but capped at different limits. Meanwhile, scripts and custom apps are only available on pro and enterprise plans.

pricing

Note: Airtable Apps were formerly known as Airtable Blocks. The new name is slowly catching on but you'll still see the term blocks pop up here and there.

Lastly, as your considering whether to build in or on Airtable, consider the functionality you need. When building in Airtable, you will face a couple constraints when working with 3rd party APIs, caching data, or manipulating the UI.

Airtable's data quirks

It's worth touching on Airtable's data model.

From a developer's perspective, Airtable is basically a hosted database fused to an easy interface for creating and managing data. This easy-to-use interface means the database schema is super flexible. Tables, columns, and field types can emerge, change, or disappear at anytime. Airtable is highly valuable because of this flexibility, but it also makes developing on Airtable a bit more unpredictable.

Additionally, as a data store, Airtable supports all sorts of data types. Most of these data types are familiar and predictable.

However, two of these data types - lookups and formulas - can take the form of any other type. This makes sense given how Airtable works: if a formula is concatenating text, then its result is a string. If it is summing numbers, then its result is a number. This means the data type of these fields is a black box, sort of like the any type in TypeScript.

As a #protip to deal with Airtable's data flexibility, I highly recommend developing on a "staging" copy of the Airtable base you are working with. This helps reduce the likelihood that an end user will change data as you build. And, of course, this allows you to break things, a hallmark of a great development process. Airtable can duplicate a base remarkably fast (especially when you think about what's happening under the hood!) - so use this to your advantage.

duplicate

So: Which of the many options should you use to build on Airtable? Let's first consider building in Airtable with Airtable scripts.

Airtable scripts: little record robots

Airtable scripts are short JavaScript snippets that allow you to manipulate data in your Airtable base.

You can do just about anything to the data in your base that is made possible with the standard JavaScript library. There are a couple limits:

  1. You can't import other JS libraries. You can copy and paste smaller, minified libraries that fit into one file - but it's usually a stretch.
  2. You can't touch the DOM.
  3. You can't change the schema of the base. You can't, for example, create new tables or views.

To use scripts, you need to add the scripting app (f.k.a scripting block) to your base. This means you need to be on either the Pro or Enterprise Airtable plans.

It's also worth noting that Airtable now allows developers to share (no selling yet!) scripts in the Airtable marketplace. So if you write a killer script that is agnostic to a base, the entire community can benefit. In the marketplace you'll find all sorts of great script examples (in addition to those in the docs).

Elements of a script

Before diving into a hands-on example, unpacking the building blocks of Airtable scripts will set the foundation for the rest of this guide.

Getting data from the base

Virtually every script (or automation/app for that matter) is going to start off by pulling data from an Airtable base.

Airtable follows a pretty straightforward relational model. Let's briefly step through it:

An Airtable workspace can contain many Airtable bases. Your script will run within one of these bases.

To add a script to a base, you'll install the Scripting App in your base.

Then, to access information about the base in a script you'll use the base model.

For instance, if you pop open the scripting app, you can quickly retrieve the name of the Airtable base:

console.log(`The name of my base is ${base.name}.`);

Or, get the number of tables in the base:

console.log(`This base contains ${base.tables.length} tables.`);

As the prior query indicates, a base can contain many tables. You can interact with tables using the table model. So when you want to work with a table you retrieve it from the base:

let table = base.getTable("Tasks");

Once you have a table loaded into your script, you can access its views, fields and records.

A view is simply a filtered set of data in the table. So let's say you want to just pull all the records from a particular view:

let table = base.getTable("Tasks");
let view = table.getView("Todo");
let query = await view.selectRecordsAsync();

The variable query is now going to contain all the records from the Todo view.

Now, when you want to inspect just one Todo record, you'll use the getCellValue() function. As so:

let table = base.getTable("Tasks");
let view = table.getView("Todo");
let query = await view.selectRecordsAsync();
let record = query.records[0];
console.log(record.getCellValue("Description"));

Script example

This quickly outlines the practical methods for pulling data from the base. You'll find that Airtable scripts includes some other models to get information on the user (a.k.a collaborator), session and more in the docs. But retrieving tables and records is the crux of working with data in Airtable.

Collecting input from the user

Beyond pulling data from the Airtable base, you'll also want to retrieve inputs from the user.

You may want to prompt the user for which table they want to evaluate in the script or which file they want to import. To do so, you'll use the input object. All input methods are asynchronous, so you'll always prefix each function call with await.

For instance, to ask the user their name:

let name = await input.textAsync("What is your name?");
output.text(`Your name is ${name}.`);

Name prompt

You can have users enter text, click a button, select a table, view, field, or even a record. Combined, these inputs allow your script to interact with the user in all sorts of ways.

Fetching data from an API

In addition to collecting data from the Airtable base and user, you can also fetch data from third-party APIs.

let response = await fetch("https://api.github.com/orgs/Airtable");
console.log(await response.json());

If the API you are calling requires authentication, your authentication token will be sitting right in the script. Keep in mind that users can view the underlying code in your script! If you don't trust the user, don't use an API fetch in your script.

Lastly, when using fetch, consider that Airtable is not providing you with full fledge browser fetch.

Presenting data to the user

Last but not least, after collecting data from the Airtable base, user or third-party API you'll then process that data and either update data in the base (using the table model functions of createRecordAsync(), updateRecordAsync(), or deleteRecordAsync()) or present data to the user.

To present a value to the user, you'll use the output object. You might output information as the scripts run to keep your user informed or to present a final results. Here is a simple "Hello, world!":

output.markdown("Hello, *world*!");

Output

You can present the user plain text, markdown, or a table.

Writing a script

Now, let's write a quick script to put these ideas to work.

To play along with this example (and make this post more enjoyable), you can add this Sales CRM base template to your workspace by clicking the Use Template button.

This template base is a simple Airtable CRM for tracking sales. As an example, let's say you want to write a script to calculate the current value of all open opportunities in the sales pipeline. This will give you a sense of how much potential revenue is available to the company. To do so, you want to sum up the Estimated Value for all deals that are active - that is, not yet won nor lost.

First, add the scripting app to the base by clicking the APPS button and selecting + Install an app:

Add app

Select the Scripting app.

Scripting app

Apps live in Airtable dashboards. So click Install app and select the Sales CRM HQ dashboard.

Scripting app

The scripting app will now open. Start with a blank slate by deleting the Hello, World example that is pre-loaded.

Now, write your script. Initiate a variable that will store the total value of the pipeline. You can call it pipeline_value:

let pipeline_value = 0;

Next, pull in all the records from the Opportunities table. To do so, you first pull the Opportunities table from the base and then query that table for all the records:

let pipeline_value = 0;
let table = base.getTable("Opportunities");
let queryResult = await table.selectRecordsAsync();

You might have noticed that the Airtable scripting block comes with some helpful autocomplete in the editor. Pretty nice right?

Now, you want to sum up the Estimated value for any open opportunity - that is any record that does not have a status of Closed-won or Closed-lost. To do so, you will iterate through the array of records and check the status. If the status is not Closed-won or Closed-lost you'll add it to the pipeline_value:

let pipeline_value = 0;
let table = base.getTable("Opportunities");
let queryResult = await table.selectRecordsAsync();
queryResult.records.map((record) => {
let status = record.getCellValue("Status").name;
if (status !== "Closed—won" && status !== "Closed—lost") {
pipeline_value += record.getCellValue("Estimated value");
}
});

Finally, present the pipeline_value to the user using output. Maybe jazz it up with a little markdown:

let pipeline_value = 0;
let table = base.getTable("Opportunities");
let queryResult = await table.selectRecordsAsync();
queryResult.records.map((record) => {
let status = record.getCellValue("Status").name;
if (status !== "Closed—won" && status !== "Closed—lost") {
pipeline_value += record.getCellValue("Estimated value");
}
});
output.markdown(`# ${pipeline_value}`);

With the script complete, click the blue Run button in the upper right corner and watch the script run.

run

It looks like there is $324,244 in the pipeline. (That's nothing to sneeze at.)


With a little JavaScript, Airtable scripts are a powerful way to manipulate the data in your base. But for developers, manually triggering a script isn't that juicy. This is where automations enter the picture.

Automations: Triggers and actions

Airtable automations bring Zapier functionality right into Airtable. With the ease of a couple clicks you can create a trigger and then fire off an action. Nothing special for a developer. In fact, the standard "trigger-action" flow is so common at this point I'm not going to cover it here.

Where your developer ears might perk up is when you hear that the subsequent action triggered by an automation can be an Airtable script. That's right, just like the ones you just learned about.

The ins and outs of automation scripts

The primary differences between a standard Airtable script and an automation script is the input and the output.

In a standard Airtable script you might collect inputs from the user, process the data, and then output the result to the user.

In an automation script, you cannot collect inputs from the user or output data to the user. Instead, you collect inputs from the prior trigger (using input.config()) and output data to the next step of the automation (using output.set()).

Note: Right now, an automation script can not be the initial trigger. For this kind of functionality, you'll want to use the App SDK.

Inputs and outputs are formatted as key-value pairs. With the key being a string and the value being passed in from the prior trigger.

Writing an automation script

As an example, let's add an automation script to the Sales CRM template we used previously when learning about Airtable scripts.

For this automation, let's say that when an opportunity becomes Closed-won, you want to calculate the salesperson's 15% commission and then send an email to your finance team with the value so they can reward the salesperson right away.

First, click the Automations button in the top right and click the blue Create an Automation button:

Automations

You can give the automation a name (perhaps something like "Commission Calculator") and click the Choose a trigger button.

You want to trigger the automation when an opportunity's status changes to Close-won - so an easy way to detect this change is to fire the event when a record enters the Closed-won view of the Opportunities table. To do so, select When record enters view as the trigger:

Set trigger

Configure the trigger as shown below and click test so you have a sample record to work with. Then, click Done:

Set view

Now, you want to add an action that calculates the commission for the deal. Click the blue Add action button and select Run script:

Run script

The scripting window will appear. First, set the inputs for this script. For this step of the automation, you really only need to calculate the commission.

Click the Add input variable in the top right corner and add an estimated_value key that captures the "Estimated value" of the record that triggers the automation:

Set ins

Now, write the script to collect the inputs, calculate the commission, and output the the commission value:

let inputConfig = input.config();
let commission = inputConfig.estimated_value * 0.15;
output.set("commision", commission);

Click to test the script, check that the result looks correct, and click Finish Editing.

Finish automation

Finally, configure the last step of the automation by setting up the email action:

Email step

Test your script and turn it on. Then sit back as the sales machine you just built starts running!

Turn on


Automations can seamlessly take care of tedious tasks and extend your Airtable base in new ways. But with scripts and automations alone, you can't build a tailored workflow or manipulate the overarching structure of an Airtable base.

This is where the App SDK comes in.

App SDK: Tailored Workflows

The Airtable App SDK gives you the ability to build complete React.js applications that run inside Airtable.

The App SDK allows for all the functionality of an Airtable script with several key additions:

  • Schema CRUD - With the SDK you can now create, update and delete tables, fields and views!
  • User Permissions - The App SDK surfaces the permissions of the Airtable user, so your software can perform risky behavior (like deleting a table!) confidently.
  • UI Library - The SDK comes with a complete React component library (and Figma kit) so you can build an interface and manage state.
  • Hooks - With first-class support for React hooks, the App SDK not only makes it easier to load data from the Airtable base, but your app can also immediately re-render when underlying data in the base changes.
  • JS Library Support - Plus, you can now import any JS library into your app.

You can build a custom app just for you and your team. Or, you can share the app via GitHub or the Airtable marketplace.

Build an Airtable App

Let's extend the previous example by building an Airtable App on the Sales CRM template we now know well. This app will help you track when commissions have been paid out. Just like the Airtable automation you built, you will first retrieve records from the Closed-won view of the Opportunities table. You'll then calculate the commission for each opportunity and load the results into a new table where you can check-off when commissions have been paid out.

This example will give you a good sense of the new UI components, hooks, and write capabilities made possible in the Airtable App SDK.

Airtable App dev environment

Unlike Airtable scripts and automations, Airtable Apps are built outside of Airtable in a local development environment. You then expose your local development environment to Airtable as you build or deploy your app when it's finished.

Airtable has done a wonderful job making this flow seamless:

To get started, click the APPS button and select Install an app. In the modal that appears, select Build a custom app:

Start new app

Name your new application "Commissions Calculator", select the Hello World JS template and then click the blue Create app button:

Set new app

Airtable is going to set up the app and then prompt you to install the Airtable Blocks CLI (a historical quirk: "Apps" used to be called "Blocks"). Follow the instructions by opening up your terminal and entering the following command:

npm install -g @airtable/blocks-cli

After you have installed the CLI, click continue and Airtable will now present you with the three steps for running your new app in the developer environment. You can copy and paste the commands into your terminal as needed:

  1. Initialize your Airtable App: block init ...
  2. Navigate to the app's dirrectory: cd commissions_calculator
  3. Start the development server with block run

Your terminal should now show that the block is running locally at https://localhost:9000:

Terminal

To see your app render in Airtable, head back to the browser and click Continue. You'll be asked to enter the URL for your development server. Enter https://localhost:9000:

Edit app

Click Start editing app and you should now see an app load with hello world!

Hello world

Note: You may receive an error from Airtable prompting you to open the URL for your development server in a new tab and navigate through some security dialog boxes. Just follow the instructions Airtable presents and you'll be up and running in a couple clicks.

Code up the commissions app

Open up the directory containing the Airtable App you just initialized in a your favorite IDE (I prefer VSCode). Navigate to the frontend folder and open the index.js file. This is the file that will house our Airtable App.

Start with a clean slate by deleting the Hello, world sample code.

Let's begin with the UI of the app. For this example, we can keep the UI simple since digging into React is a little out of scope for this post (we mostly want to highlight the Airtable development experience). So for simplicity, trigger the app with a button click by importing the Button component from the Airtable library:

import { initializeBlock, Button } from "@airtable/blocks/ui";
import React from "react";
function HelloWorldApp() {
return (
<div>
<Button
onClick={() => createNewTable()}
variant="primary"
size="large"
icon="plusFilled"
>
Calculate Commissions
</Button>
</div>
);
}
initializeBlock(() => <HelloWorldApp />);

Now, when you save the file, you'll see your app in Airtable re-renders with the button:

App button

Let's take a moment to appreciate what Airtable is doing here.

First, your importing a complete component library, the likes of Material or Antd. Except the components here are styled to look native to Airtable.

Next, notice how Airtable has replaced the standard ReactDOM.render() with their initializeBlock() as the place where you render the root of your app.

And when you save the app on your local machine, the app updates live in Airtable so you can immediately see your changes. Delightful. Kudos to the Airtable team.

With the button in place, use the useBase and useRecords hooks to pull in data from the Airtable base.

To do so, first import these additional hooks from the Airtable library:

import {
initializeBlock,
Button,
useBase,
useRecords,
} from "@airtable/blocks/ui";

Then, pull in the records from base:

function HelloWorldApp() {
const base = useBase();
const opps = base.getTableByName("Opportunities");
const view = opps.getViewByName("Closed—won");
const queryResult = view.selectRecords();
const records = useRecords(queryResult);
// ...

Just like with scripts, you are first loading in the base, then the table, then the view, and then the records.

With the data loaded into the app, add a helper function at the end of the file to calculate commissions from the opportunities you just collected:

function getCommissions(records) {
let commissions = [];
records.map((r) =>
commissions.push({
fields: {
Deal: r.getCellValue("Opportunity name"),
Salesperson: r.getCellValue("Owner").name,
Value: r.getCellValue("Estimated value"),
Commission: r.getCellValue("Estimated value") * 0.15,
},
})
);
return commissions;
}

The getCommissions() function is iterating through all the records to create a subset of records with just the fields you need while also calculating the commission value. Here is the full app thus far:

import {
initializeBlock,
Button,
useBase,
useRecords,
} from "@airtable/blocks/ui";
import React from "react";
function HelloWorldApp() {
const base = useBase();
const opps = base.getTableByName("Opportunities");
const view = opps.getViewByName("Closed—won");
const queryResult = view.selectRecords();
const records = useRecords(queryResult);
let commissions = getCommissions(records);
return (
<div>
<Button
onClick={() => createNewTable()}
variant="primary"
size="large"
icon="plusFilled"
>
Calculate Commissions
</Button>
</div>
);
}
function getCommissions(records) {
let commissions = [];
records.map((r) =>
commissions.push({
fields: {
Deal: r.getCellValue("Opportunity name"),
Salesperson: r.getCellValue("Owner").name,
Value: r.getCellValue("Estimated value"),
Commission: r.getCellValue("Estimated value") * 0.15,
},
})
);
return commissions;
}
initializeBlock(() => <HelloWorldApp />);

Now, you just need to create a new table and fill it with the commission records you just calculated.

To create a new table, start by importing the FieldType model from the Airtable library:

import { FieldType } from "@airtable/blocks/models";

Next, use the createNewTable() function to define the name and fields in the new table you want to create:

async function createNewTable() {
const tableName = "Commissions";
const fields = [
{ name: "Deal", type: FieldType.SINGLE_LINE_TEXT },
{ name: "Salesperson", type: FieldType.SINGLE_LINE_TEXT },
{
name: "Value",
type: FieldType.CURRENCY,
options: { precision: 2, symbol: "$" },
},
{
name: "Commission",
type: FieldType.CURRENCY,
options: { precision: 2, symbol: "$" },
},
{
name: "Paid",
type: FieldType.CHECKBOX,
options: { icon: "check", color: "greenBright" },
},
];
if (base.checkPermissionsForCreateTable(tableName, fields)) {
await base.createTableAsync(tableName, fields);
}
}

This function first gives the new table a name of Commissions. It then defines the fields in the table as an array of objects. Last, it checks to see that a new table can be created in the base before actually creating the table.

Set the button you built earlier to trigger the createNewTable() function onClick and save the file.

return (
<div>
<Button
onClick={() => createNewTable()}
variant="primary"
size="large"
icon="plusFilled"
>
Calculate Commissions
</Button>
</div>
);
}

Go back to Airtable and test the app by clicking the Calculate Commissions button. You should see a Commissions table pop into the base. Nice!

As a last step, we want to load the commission records into this new table. To do so, add another helper function at the end of the file to create new records:

async function createNewRecordsAsync(table, records) {
if (table.hasPermissionToCreateRecords(records)) {
await table.createRecordsAsync(records);
}
}

Similar to the createNewTable() function, createNewRecordsAsync() first checks if new records can be created. Then it proceeds to create the records.

As a last step, you need to trigger the createNewRecordsAsync() function after the new commissions table is created. To do so, call createNewRecordsAsync() at the end of the createNewTable() function:

async function createNewTable() {
// ...
if (base.checkPermissionsForCreateTable(tableName, fields)) {
await base.createTableAsync(tableName, fields);
const commissionsTable = base.getTableByNameIfExists("Commissions");
await createNewRecordsAsync(commissionsTable, commissions);
}
}

These last two lines complete the app. getTableByNameIfExists() is retrieving the new commissions table you create with the createNewTable() function. Then, you call createNewRecordsAsync() to load in the commission records. Here is the complete app:

import { FieldType } from "@airtable/blocks/models";
import {
initializeBlock,
Button,
useBase,
useRecords,
} from "@airtable/blocks/ui";
import React from "react";
function HelloWorldApp() {
const base = useBase();
const opps = base.getTableByName("Opportunities");
const view = opps.getViewByName("Closed—won");
const queryResult = view.selectRecords();
const records = useRecords(queryResult);
let commissions = getCommissions(records);
async function createNewTable() {
const tableName = "Commissions";
const fields = [
{ name: "Deal", type: FieldType.SINGLE_LINE_TEXT },
{ name: "Salesperson", type: FieldType.SINGLE_LINE_TEXT },
{
name: "Value",
type: FieldType.CURRENCY,
options: { precision: 2, symbol: "$" },
},
{
name: "Commission",
type: FieldType.CURRENCY,
options: { precision: 2, symbol: "$" },
},
{
name: "Paid",
type: FieldType.CHECKBOX,
options: { icon: "check", color: "greenBright" },
},
];
if (base.checkPermissionsForCreateTable(tableName, fields)) {
await base.createTableAsync(tableName, fields);
const commissionsTable = base.getTableByNameIfExists("Commissions");
await createNewRecordsAsync(commissionsTable, commissions);
}
}
return (
<div>
<Button
onClick={() => createNewTable()}
variant="primary"
size="large"
icon="plusFilled"
>
Calculate Commissions
</Button>
</div>
);
}
function getCommissions(records) {
let commissions = [];
records.map((r) =>
commissions.push({
fields: {
Deal: r.getCellValue("Opportunity name"),
Salesperson: r.getCellValue("Owner").name,
Value: r.getCellValue("Estimated value"),
Commission: r.getCellValue("Estimated value") * 0.15,
},
})
);
return commissions;
}
async function createNewRecordsAsync(table, records) {
console.log("I have been called");
if (table.hasPermissionToCreateRecords(records)) {
await table.createRecordsAsync(records);
}
}
initializeBlock(() => <HelloWorldApp />);

Save the file and head back to Airtable. If in your testing you created a temporary Commissions table - make sure you delete it. Then click the big blue button in your app. Voila!

Final app

To deploy the app, head back to the terminal and run block release. Your app will be uploaded to Airtable and deployed onto their servers so anyone can use your app without your dev environment running.


As I hope this example illustrates, the Airtable App SDK is full-featured. You can build and deploy complete apps with all sorts of bells and whistles. And because React.js is at the core - the developer experience is very similar to building any modern React app.

The big limitation: these apps (and scripts, and automations) can only run in Airtable. What if you want to build on Airtable - for users outside the Airtable interface? For these use cases you need to use the REST API or new services like Sync Inc.

The REST API

The Airtable REST API is the original gangster of Airtable's developer tools. It's been around for several years and hasn't changed (it's still on v0).

The REST API is best understood as a records API - in that you can create, read, update, and delete records. You can't touch tables, views, or fields at all. In fact, you can't even list tables. So when using the REST API, you'll still make any schema changes in the Airtable interface.

First things first: to use the REST API you'll need to authenticate and find your Base ID.

Authentication and Base ID

The REST API uses token-based authentication. You'll find your API key on your accounts page:

API key

You'll then pass along the key in a standard authorization bearer token header:

$ curl https://api.airtable.com/v0/{{BASE-ID}}/{{TABLE_NAME} \
-H "Authorization: Bearer {{YOUR_API_KEY}}"

Every API call will include the Base ID for the Airtable base you wish to work with. You'll find this Base ID in the Airtable docs: https://airtable.com/api

Simply select your base and the Base ID will be presented in the middle of the page:

Base ID

API Clients

Airtable has published an official API client - airtable.js. This library works in both Node.js and in the browser. You'll also find other community libraries for Ruby, .NET and more.

These clients make interfacing with the API easier. Most notably, they can help you navigate the API rate limits.

Rate Limits

The Airtable API is limited to 5 API calls per second per base. If you exceed this limit you'll receive a 429 status code and be put in 30 second timeout.

This rate limit is really of no consequence when creating, updating or deleting records as long as you have simple retry logic (and again, the client libraries come with this). However, on even simple read use cases, this limit is a bit of a constraint.

As the official docs explain, "If you anticipate a higher read volume, we recommend using a caching proxy." As we'll see in a bit, this is where a tool like Sync Inc can be helpful.

Methods

The Airtable REST API docs are an amazing resource. Airtable dynamically creates docs for each base in your workspace with specific examples and guidance.

You can easily see the specific docs for the Sales CRM template we've been using by opening the base, clicking the HELP button, and selecting API documentation:

API docs

Below is a practical summary of the API as you consider building on it.

List records

To retrieve a list of records from a table, you'll make a GET request:

curl https://api.airtable.com/v0/{{YOUR_BASE_ID}}/{{URL_ENCODED_TABLE_NAME}} \
-H "Authorization: Bearer {{YOUR_API_KEY}}"

You can optionally pass along several parameters:

  • fields: If you don't want every field in the table, you can specify the ones you would like.
  • filterByFormula: You can filter the results with a formula. This is a bit tricky. I recommend first writing and testing the formula in Airtable and then adding it to your API call. For instance, add a temporary formula field to the table you wish to query. Write the formula and then verify it returns true for the field you expect.
  • maxRecords: Limit the number of records returned.
  • pageSize: If you want less than 100 records per page, define it here.
  • sort: Specify the field you want to sort by and the direction of the sort.
  • view: If you want to return records from a specific view.
  • cellFormat: Whether you want cell values returned as strings or JSON. (tip - just stick with JSON).
  • timeZone: The timezone that should be used when formatting dates.
  • userLocale: The user locale that should be used when formatting dates.

The list records endpoint is paginated. If more than 100 results are available, then the response from Airtable will include an offset. To fetch the next page, you'll include this offset in the next request.

Pagination paired with the rate limit is a bit of a squeeze. You'll only be able to return 100 records per call. And you can only make 5 calls per second. So at most, you can retrieve 500 records in a second. That might not sound too bad, but even on a small base with many tables, you can hit this limit fast.

You can reduce the number of records you need to retrieve (and hence the number of API calls) by properly filtering - so getting comfortable with Airtable formulas can be very helpful if you want to optimize your reads. Otherwise, a database from Sync Inc is a great alternative.

Retrieve a record

You can retrieve a specific record by passing the record_id along with a GET request:

curl https://api.airtable.com/v0/{{YOUR_BASE_ID}}/{{URL_ENCODED_TABLE_NAME}}/{{RECORD_ID}} \
-H "Authorization: Bearer {{YOUR_API_KEY}}"

Create record

You can create up to 10 records per POST request:

curl -v -X POST https://api.airtable.com/v0/{{YOUR_BASE_ID}}/{{URL_ENCODED_TABLE_NAME}} \
-H "Authorization: Bearer {{YOUR_API_KEY}}" \
-H "Content-Type: application/json" \
--data '{
"records": [
{
"fields": {"fieldName": "cellValue"}
},
{
"fields": {"fieldName": "cellValue"}
}
]
}'

The one thing to keep in mind is that each record you want to create needs to be a single object with one fields key that contains all the fields and cell values for the records. It is a nuance that caught me up at first.

Update record

You can update specific fields of up to 10 records by making a PATCH request:

curl -v -X PATCH https://api.airtable.com/v0/{{YOUR_BASE_ID}}/{{URL_ENCODED_TABLE_NAME}} \
-H "Authorization: Bearer {{YOUR_API_KEY}}" \
-H "Content-Type: application/json" \
--data '{
"records": [
{
"id": "{{RECORD-ID}}",
"fields": {
"fieldName": "cellValue"
}
},
{
"id": "{{RECORD-ID}}",
"fields": {
"fieldName": "cellValue"
}
}
]
}'

This will only change the fields you specify in the call, leaving the rest untouched.

Or, you can make a destructive update with a PUT request. This will clear all unspecified cell values:

curl -v -X PUT https://api.airtable.com/v0/{{YOUR_BASE_ID}}/{{URL_ENCODED_TABLE_NAME}} \
-H "Authorization: Bearer {{YOUR_API_KEY}}" \
-H "Content-Type: application/json" \
--data '{
"records": [
{
"id": "{{RECORD-ID}}",
"fields": {
"fieldName": "cellValue"
}
},
{
"id": "{{RECORD-ID}}",
"fields": {
"fieldName": "cellValue"
}
}
]
}'

Delete record

Finally, you can delete up to 10 records at a time with a DELETE request:

curl -v -X DELETE https://api.airtable.com/v0/{{YOUR_BASE_ID}}/{{URL_ENCODED_TABLE_NAME}} \
-H "Authorization: Bearer YOUR_API_KEY" \
-G \
--data-urlencode 'records[]={{RECORD-ID}}' \
--data-urlencode 'records[]={{RECORD-ID}}'

Query the API for commissions

As an example, let's look at the API call you would make to return the data needed to calculate commissions for salespeople in the Sales CRM you now know and love. We'll skip the process of setting up a Node server and just focus on the API request.

To calculate the commissions, you'll recall that you need to retrieve the Opportunity name, Owner and Estimated value from the Closed–won view of the Opportunities table. So the GET request will look something like:

curl "https://api.airtable.com/v0/{{YOUR_BASE_ID}}/Opportunities?view=Closed%E2%80%94won&fields%5B%5D=Opportunity%20name&fields%5B%5D=Owner&fields%5B%5D=Estimated%20value" \
-H "Authorization: Bearer {{YOUR_API_KEY}}"

Peeking the the URL encoding, you can see the specific view and fields being requested in the call. Since you are working with a small set of records, no need to worry about filtering or pagination.

When you execute the API request, the results return in a JSON object:

{
"records": [
{
"id": "rec5omR4ZMAvb7Z1D",
"fields": {
"Opportunity name": "Sunlight renewal",
"Estimated value": 17573,
"Owner": {
"id": "usrk1jS7Zz8mKn9m7",
"email": "katherineduh+collab3@demo.htable.com",
"name": "Ari Ramírez-Medina"
}
},
"createdTime": "2017-07-21T00:56:36.000Z"
},
{
"id": "recYNtWMqTT0sSTVh",
"fields": {
"Opportunity name": "Acepolly second use case",
"Estimated value": 18049,
"Owner": {
"id": "usrTqRt8glK7HcmEg",
"email": "katherineduh+collab8@demo.htable.com",
"name": "Casey Park"
}
},
"createdTime": "2017-07-21T01:02:39.000Z"
},
......
{
"id": "rec7S8KgDkju7486V",
"fields": {
"Opportunity name": "JJ second team",
"Estimated value": 20068,
"Owner": {
"id": "usrTqRt8glK7HcmEg",
"email": "katherineduh+collab8@demo.htable.com",
"name": "Casey Park"
}
},
"createdTime": "2017-07-21T01:02:39.000Z"
}
]
}

You would then parse this JSON object, calculate the commissions, and present the final data to the user.


The Airtable REST API allows you to create, read, update, and delete records in your Airtable base.

But the Airtable API comes with all the limitations of a third-party API. You have to obey rate limits, paginate responses, and figure out how to express queries using their somewhat cryptic filterByFormula expression. What if you want to skip the API altogether? That's where Sync Inc comes in.

Sync Inc

Sync Inc is a third-party service that syncs your entire Airtable base to a Postgres follower database in real-time. In seconds, you'll implement the very cache that Airtable recommends for any app that requires heavy reads. And because all your data is in Postgres, you can just use plain SQL to query your data (instead of fussing with an API).

Setup Sync Inc

Let's say you want to connect the Sales CRM template we've been using to Sync Inc:

Note: follow the quickstart guide for more details

First, create a Sync Inc account at https://app.syncinc.so/signup

Sync Inc Account

Click the Add Base Button":

Add base

Then enter your Airtable API key. Again, you'll find your API key in the middle of your accounts page.

Once you've entered your API key, select the Sales CRM base you want to sync. Then look to see that Sync all tables is toggled on and click Create:

Add base

In a couple seconds, Sync Inc will provision you a Postgres database that contains all the data in your Airtable base. Synced in real-time. You'll immediately receive the credentials for your database:

Finish Sync

Reads

With a Postgres database, you can now connect to your Airtable data from any standard SQL client. This could be the psql command-line app, a SQL GUI client like TablePlus, or from virtually any programming language.

Then you can query your data using SQL. This includes JOIN, UNION, and all the rest. You can check out Sync Inc's cheat sheet for some tips on querying Airtable's data structures in Postgres.

Above all, SQL allows you to access all the data in the base at once - so you no longer need to tediously write nested API calls or worry about rate limits.

As an example, let's say we want to retrieve the same commissions data we've been using throughout this post in SQL:

SELECT
opportunities.opportunity_name AS "Opportunity",
opportunities.owner ->> 'name' AS "Sales Rep",
opportunities.estimated_value AS "Deal Value",
(opportunities.estimated_value * 0.15) AS "Commission"
FROM opportunities
WHERE opportunities.status = 'Closed—won';

In SQL, we can quickly select and calculate the data we need in just a couple lines. And the results are available almost instantaneously:

SQL results

In fact, I can take this query one step further and calculate the exact checks I need to cut for each sales rep:

SELECT
opportunities.owner ->> 'name' AS "Sales Rep",
SUM(opportunities.estimated_value * 0.15) AS "Commission"
FROM opportunities
WHERE opportunities.status = 'Closed—won'
GROUP BY "Sales Rep";

SQL results 2

Writes

When using Sync Inc, you'll still create, update, and delete records through the REST API.

These operations are usually way simpler than reads, as you're not fiddling with query parameters or obssesing about rate limits.


Sync Inc makes it easy to treat Airtable as a database - because it literally turns Airtable into a Postgres database. With SQL in your tool belt, building on Airtable requires no new syntax, pagination, or rate limits.

It's a great alternative to the REST API for any application that needs to read data from Airtable.

Conclusion

Airtable is ready for developers to build on its platform. With scripts, automations, the app SDK, REST API and new tools like Sync Inc, a developer can now build in and on Airtable confidently. I hope this guide has shown you a new side of Airtable - one that is full of possibility with code. So start building!

Skip the API. Create a real-time follower Postgres database with all your Airtable data. Get started free.