Leveraging the Airtable API

airtable logo on an orange background

If you’re anything like me as a web developer, you probably have a slew of half-finished side projects that you were really excited about, only to lose steam and give up on. To be honest, "half-finished" is probably pretty generous for me…half-started is more accurate.

One of the most challenging things in bootstrapping a new app to create an MVP of your next great idea is handling data. Writing static JSON while creating a data structure on the fly eats up time and isn’t realistic in terms of scaffolding future iterations. Setting up a custom database and API is even more time-consuming. Trying to set up even a basic database and API can take the wind out of your sails and leave you feeling frustrated. I often find myself thinking, "I want to build the IDEA, not be stuck in the weeds of data structure." 

One tool we’ve leveraged to solve this problem on some of our Labs initiatives is Airtable. I know what you might be thinking. Isn’t Airtable just a glorified version of Excel or Google Sheets? The short answer is yes but bear with me while I give you the long answer. Most of us forget that the entire purpose of Excel is to present data in compelling ways. Excel power users know that you can use Excel as a database.

Airtable markets itself as having the "familiarity of a spreadsheet, and the power of a database." Its default functionality is to be a database, first and foremost. However, with views, automation, apps, sync, and integrations, it offers loads of features that can be leveraged in an array of ways.

The Airtable API

Once you create a base with the data you need to get up and running on your app, it provides a self-documenting API. Yes, you read that correctly. From a developer perspective, this is one of the absolute best features of Airtable.

Setting out to build an MVP with your next great idea can be as easy as entering your test data into a spreadsheet — creating and configuring the schema as you go — then popping over to the documentation specific to your project.

A Savas Use Case

One project where we leveraged Airtable’s API is Should I Ask For Gender. The Airtable base for this project includes five different tables:

  • Questions
  • Answers
  • Options
  • Resources

Every table has at least one field referencing other tables. For example, the questions table references data in the options table. The self-documenting API provides us with a description of each field, example values, and example responses from the API. The documentation also provides examples of the basic CRUD operations (create, retrieve, update, and delete) for each table as well as documentation related to rate limits, authentication, and error codes.

Here is an example of some of the code Airtable's API documentation automatically generates for you:

var Airtable = require('airtable');
var base = new Airtable({apiKey: 'YOUR_API_KEY'}).base('YOUR_BASE_ID');

base('questions').select({
    // Selecting the first 3 records in Grid view:
    maxRecords: 3,
    view: "Grid view"
}).eachPage(function page(records, fetchNextPage) {
    // This function (`page`) will get called for each page of records.

    records.forEach(function(record) {
        console.log('Retrieved', record.get('question'));
    });

    // To fetch the next page of records, call `fetchNextPage`.
    // If there are more records, `page` will get called again.
    // If there are no more records, `done` will get called.
    fetchNextPage();

}, function done(err) {
    if (err) { console.error(err); return; }
});

OUTPUT
Retrieved How are you going to use gender information?
Retrieved Are you offering an app or services that serve a particular sex/gender?
Retrieved Are you advertising an app or services that serve a particular sex/gender?

 

Interacting with the API

While you can write your own functions and processes to interact with the API, Airtable also provides an official JavaScript client that takes care of all the hard work for you (as shown in the above example). In Should I Ask for Gender, we used airtable-json, which bills itself simply as: "When you want Airtable records to be in a raw JSON format, and that's all you care about".

One airtable-json feature we really appreciated was its handling of references to other tables in the same base. Thanks to the populate key, we were able to create clean JSON with all referenced data included. This made it easier on our end to consume the data and display it properly within the app.

let questions = await airtableJson({
    auth_key: 'AUTH_KEY',
    base_name: 'BASE_NAME',
    primary: 'questions',
    view: 'Grid view',
    populate: [
      {
        local: 'options',
        other: 'options',
      },
      {
        local: 'referencing_options',
        other: 'options',
      },
    ],
  });

 

Using the API in production

One disadvantage of Airtable’s API is that it is limited to 5 requests per second, per base. Exceeding that rate will result in a 429 status and having to wait 30 seconds until subsequent requests will succeed. This obviously creates some challenges when deploying an app to production and supporting interactions at scale. There are a couple of ways to approach using the Airtable API that gets around the rate limit.

Generate static data files

Since the data on Should I Ask for Gender is static, we decided to query the API during our build process to generate static JSON files we could deploy with our app. This allowed us to avoid any issues with the rate limit, make a faster/more performant app, and add progressive web app support.

Use a caching proxy or sync with a database

In its API documentation, Airtable suggests using a caching proxy if you anticipate exceeding their rate limits. Here are some great existing resources for creating a middleman:

  • Node.js Airtable API Proxy: A simple Glitch project that provides you the building blocks for creating your own proxy.
  • NoCodeAPI: Interact with the Airtable API (or others) without having to write any code.
  • Sync Inc.: Replicate your Airtable data to a production-grade Postgres database to get all the power of SQL.

Airtable is a great solution to get a database up and running quickly for your next project. Even with their rate limit, the infrastructure folks have built around Airtable makes it a viable and appealing option for many different use cases.