The Complete Developer's Guide to Airtable
Tags: javascript, tutorial, programming, webdev
Published: 2021-01-14 19:43:16

You might have missed [the memo](https://blog.airtable.com/airtable-platform-launch-automations-sync-apps/): 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](https://airtable.com/developers) 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](https://blog.sequin.io/dev-guide/031_in_or_on.png)

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 - [Sequin](https://sequin.io).

As you decide whether to build _in_ on _on_ Airtable, you should consider what [Airtable plan](https://airtable.com/pricing) 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](https://blog.sequin.io/dev-guide/020_pricing.png)

> 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](https://airtable.com/developers/scripting/api/cell_values). 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](https://blog.sequin.io/dev-guide/021_duplicate.png)

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](https://airtable.com/developers/scripting) 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](https://airtable.com/marketplace/category/scripts). 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](https://airtable.com/developers/scripting/examples)).

### 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**](https://support.airtable.com/hc/en-us/articles/360043041074-Scripting-app-overview) in your base.

Then, to access information about the base in a script you'll use the [base model](https://airtable.com/developers/scripting/api/base).

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

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

Or, get the number of tables in the base:

```js
console.log(`It 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](https://airtable.com/developers/scripting/api/table). So when you want to work with a table you retrieve it from the base:

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

Once you have a table loaded into your script, you can access its [views](https://airtable.com/developers/scripting/api/view), [fields](https://airtable.com/developers/scripting/api/field) and [records](https://airtable.com/developers/scripting/api/record).

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:

```js
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](https://airtable.com/developers/scripting/api/record#get-cell-value). As so:

```js
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](https://blog.sequin.io/dev-guide/032_script_example.png)

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](https://airtable.com/developers/scripting). 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](https://airtable.com/developers/scripting/api/input). All input methods are asynchronous, so you'll always prefix each function call with `await`.

For instance, to ask the user their name:

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

![Name prompt](https://blog.sequin.io/dev-guide/033_name_prompt.png)

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](https://airtable.com/developers/scripting/api/fetch) from third-party APIs.

```js
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](https://airtable.com/developers/scripting/api/fetch#differences-from-fetch-in-the-browser).

**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](https://airtable.com/developers/scripting/api/output). 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!":

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

![Output](https://blog.sequin.io/dev-guide/034_output.png)

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](https://airtable.com/templates/sales-and-customers/expvjTzYAZareV1pt/sales-crm) 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](https://blog.sequin.io/dev-guide/001_add_app.png)

Select the **Scripting** app.

![Scripting app](https://blog.sequin.io/dev-guide/002_select_scripting.png)

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

![Scripting app](https://blog.sequin.io/dev-guide/003_select_dash.png)

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

```js
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:

```js
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`:

```js
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:

```js
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](https://blog.sequin.io/dev-guide/004_run_script.png)

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()`](https://airtable.com/developers/scripting/api/input#config)) and output data to the next step of the automation ([using `output.set()`](https://airtable.com/developers/scripting/api/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](https://airtable.com/templates/sales-and-customers/expvjTzYAZareV1pt/sales-crm) 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](https://blog.sequin.io/dev-guide/005_automations.png)

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](https://blog.sequin.io/dev-guide/006_set_trigger.png)

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

![Set view](https://blog.sequin.io/dev-guide/007_set_view.png)

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](https://blog.sequin.io/dev-guide/008_run_script.png)

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](https://blog.sequin.io/dev-guide/009_set_ins.png)

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

```js
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](https://blog.sequin.io/dev-guide/010_finish_script.png)

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

![Email step](https://blog.sequin.io/dev-guide/011_email_step.png)

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

![Turn on](https://blog.sequin.io/dev-guide/012_turn_on.png)

---

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](https://airtable.com/developers/apps) gives you the ability to build complete [React.js](https://reactjs.org) 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](https://airtable.com/developers/apps/guides/changing-base-schema) 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](https://airtable.com/developers/apps/api/UI/components/Box) (and [Figma kit](https://www.figma.com/community/file/862805330899066752)) so you can build an interface and manage state.
- **Hooks** - With first-class support for [React hooks](https://airtable.com/developers/apps/api/UI/hooks/useBase), 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](https://airtable.com/marketplace).

### Build an Airtable App

Let's extend the previous example by building an Airtable App on [the Sales CRM template](https://airtable.com/templates/sales-and-customers/expvjTzYAZareV1pt/sales-crm) 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](https://blog.sequin.io/dev-guide/013_new_app.png)

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

![Set new app](https://blog.sequin.io/dev-guide/014_setup_app.png)

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:

```bash
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](https://blog.sequin.io/dev-guide/015_terminal.png)

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](https://blog.sequin.io/dev-guide/016_edit_app.png)

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

![Hello world](https://blog.sequin.io/dev-guide/017_hello_world.png)

> 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](https://code.visualstudio.com)). 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:

```js
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](https://blog.sequin.io/dev-guide/018_app_button.png)

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:

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

Then, pull in the records from base:

```js
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:

```js
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:

```js
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:

```js
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:

```js
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.

```js
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:

```js
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:

```js
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:

```js
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](https://blog.sequin.io/dev-guide/019_final_app.png)

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 [Sequin](https://sequin.io).

## The REST API

The [Airtable REST API](https://airtable.com/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](https://airtable.com/account) page:

![API key](https://blog.sequin.io/dev-guide/022_api_key.png)

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

```bash
$ 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](https://blog.sequin.io/dev-guide/023_base_id.png)

### API Clients

Airtable has published an official API client - [airtable.js](https://github.com/Airtable/airtable.js). This library works in both Node.js and in the browser. You'll also find other community libraries for [Ruby](https://github.com/sirupsen/airrecord), [.NET](https://github.com/ngocnicholas/airtable.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 Sequin can be helpful.

### Methods

The [Airtable REST API docs](https://airtable.com/api) 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](https://airtable.com/templates/sales-and-customers/expvjTzYAZareV1pt/sales-crm) we've been using by opening the base, clicking the **HELP** button, and selecting **API documentation**:

![API docs](https://blog.sequin.io/dev-guide/024_get_to_docs.png)

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:

```bash
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](https://support.airtable.com/hc/en-us/articles/203255215-Formula-Field-Reference). 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 Sequin is a great alternative.

**Retrieve a record**

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

```bash
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:

```bash
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:

```bash
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:

```bash
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:

```bash
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](https://airtable.com/templates/sales-and-customers/expvjTzYAZareV1pt/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:

```bash
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:

```json
{
  "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 Sequin comes in.

## Sequin

[Sequin](https://sequin.io) 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 Sequin

Let's say you want to connect [the Sales CRM template](https://airtable.com/templates/sales-and-customers/expvjTzYAZareV1pt/sales-crm) we've been using to Sequin:

> Note: follow the [quickstart guide](https://docs.sequin.io) for more details

First, create a Sequin account at https://app.sequin.io/signup

![Sequin Account](https://blog.sequin.io/dev-guide/025_sync_account.png)

Click the **Add Base** Button":

![Add base](https://blog.sequin.io/dev-guide/026_add_base.png)

Then **enter your Airtable API key**. Again, you'll find your API key in the middle of your [accounts](https://airtable.com/account) 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](https://blog.sequin.io/dev-guide/027_set_sync.png)

In a couple seconds, Sequin 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](https://blog.sequin.io/dev-guide/028_finish.png)

### Reads

With a Postgres database, you can now [connect to your Airtable data from any standard SQL client](https://docs.sequin.io/reference#connection-options). 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 Sequin's [cheat sheet](https://docs.sequin.io/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:

```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](https://blog.sequin.io/dev-guide/029_results.png)

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

```sql
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](https://blog.sequin.io/dev-guide/030_results_two.png)

### Writes

When using Sequin, 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 obsessing about rate limits.

---

Sequin 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 Sequin, 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!