Healthy Corners

# Working with Airtable

NOTE

The official Airtable documentation refers to the columns of a table as "fields". For convenience, we generally refer to these as columns.

We use Airtable as the backing database for all of our applications. To start off, we recommend reading through this section of the admin guide which covers Airtable basics, how to interact via the UI, and an overview of our PROD base.

Here, we'll introduce how we interact with Airtable in terms of development. Specifically, we use helper functions as a wrapper API for the official Airtable client, generated by the airtable-schema-generator package (opens new window).

WARNING

Updating the Airtable base configuration, or the "schema", (i.e creating/renaming/removing tables, adding/removing/renaming columns) is generally a BREAKING CHANGE - live apps will be UNUSABLE.

Also, because all three applications are backed by the same PROD Airtable base and use the schema-generator package, schema updates must always be made & deployed to all applications.

Details in the next section.

# Making changes to the Airtable base(s)

The airtable-schema-generator package relies on the local schema.js matching that of the actual Airtable base it's referencing. Thus, the schema generator must be re-run whenever there are schema updates to keep the local files in sync.

When breaking changes are made, schema updates must be merged and deployed via an over-the-air update before the Airtable schema can actually be changed via the UI.

Generally speaking, it's best to create a new development branch with solely the schema updates because schema changes affect all branches immediately. Creating a separate PR for schema updates isolates those changes and is also good practice.

TIP

The nice thing about having both [DEV] and [PROD] bases is that we can merge the schema updates, update the Airtable [DEV] base (i.e make table and column changes), merge associated code/feature updates, and test main locally (connected to [DEV]) before pushing the schema updates to [PROD] and then making the Airtable [PROD] base changes.

WARNING: Don't forget to update the [PROD] base once updates have been pushed!

# Breaking changes

  • DELETING TABLES, RENAMING A TABLE: Deleting any table will cause the app to break, as the table depends on all tables in some capacity or another.

  • CHANGING COLUMN TYPE: can also cause the code to break, and one should look through how a particular column is used in the codebase before making such a change.

  • DELETING COLUMNS, RENAMING A COLUMN: As all columns are used by the app at some point to access information, and changing column names, case etc. would break things.

TIP

As you can tell, most changes are breaking. The only changes that aren't are ADDING A TABLE or ADDING A COLUMN to a table, since none of the live code attempts (and fails) to use information in the new column/table.

However, you must create a separate PR for the schema update and make sure it is merged before your new feature's PR is merged. Otherwise, the schema in main will be out of sync and your feature will not work as expected.

*Updating the schema in the same PR is technically correct as well, but we'll lose the electronic trail of schema updates in the commit history.

# Airtable Schema Generator

The Airtable Schema Generator is a tool Blueprint developed alongside this web app (used by both our project and the People Power project (opens new window)) that generates helper functions on top of the basic Airtable JavaScript client.

It generates these helper functions in accordance with the specified Airtable base's Schema. The full details on how it works, usage, as well as important caveats and all the settings available can be found on its README (opens new window).

Here, we introduce briefly how it works and include details about how it is configured for our repositories.

# Overview

The schema generator outputs 4 files:

# airtable.js

This file interacts directly with the Airtable JavaScript client and exposes the core CRUD functions (opens new window). It also contains the logic that translates the Airtable column names to JS-formatted names. "First Name" becomes "firstName", "Project Group" becomes "projectGroupId" (assuming it is a one-to-one relation field).

# request.js

This file contains helper functions for every crud operation for every table. It's a convenience layer over airtable.js

# schema.js

This file is a schema mapping that describes all the table and column names in a JS format. The app should never use this file directly, but it is necessary for airtable.js to translate records to JS-readable variable names. It should be used as a reference to know what a column name has been translated to.

# schema.json

This is a JSON version of schema.js for reference.

# Writing code

The generated files use the toAirtableFormat / fromAirtableFormat to convert between table/column names in Airtable's format and table/column names in JavaScript-conventional camelCase. This is done implicitly (because airtable.js is a generated file), but if you look into airtable.js you will be able to see both these functions and their invocation in the different airtable.js CRUD calls.

These functions also do some other conversions (like converting Announcements to announcementIds for example), with the goal of making the naming more descriptive/explicit (in the above example, for instance, Announcements actually referred to an array of announcements, which might be confusing).

To see these conversions explicitly, you can see the mapping from table/column names to their camelCase counterparts in schema.js. This is also the file that toAirtableFormat and fromAirtableFormat perform the conversion based on.

WARNING

The schema generator does not play nicely with plural-named tables; there are some special-cases in both our repos where the automatic pluralization/depluralization does not work properly. Thus, some variable names are misspelled when generated by the schema. Always sanity-check schema.js if nothing else seems wrong and Airtable code is not working.

What this means in development is you must refer to table and column names in conventional JavaScript camelCase and in the format specified in schema.js. There should be plenty of examples to reference in the codebase. Referring to a table or column exactly as they are named in Airtable will not work.

TIP

See PR #48 (opens new window) (customer) and PR #10 (opens new window) (clerk) for when we first refactored the existing code to use the wrapper API.

# Running the generator

TIP

The generator does not need to be run regularly. Ideally, schema updates are few and far between (especially since they are often breaking!).

When you first clone the repos, you do NOT need to run the generator. The lib/airtable/*.js files have been generated and are already committed. There's no need to regenerate unless the schema changes.

The local schema files are (re)generated with the command yarn generate-schema. For convenience, all of our repos are configured to use the auto-headless mode so there is no user interaction required. Thus, you must fill in the AIRTABLE_EMAIL and AIRTABLE_PASSWORD sections of your local .env.generator file (customer/clerk repos) or .env file (node repo) to allow automated login on Airtable.

NOTE

These credentials are not saved anywhere other than your own computer. This allows the schema generator to automatically scrape your schema from Airtable. If you're curious about what happens when the generator code runs, change auto-headless in the schema generator's settings in package.json to auto.

# Post-generation modifications

Because the customer and clerk repos use a custom file to store and auto-switch environment variables between [DEV] and [PROD] instead of a traditional .env file, the schema generator doesn't know how to find the AIRTABLE_BASE_ID or the REACT_APP_AIRTABLE_API_KEY in airtable.js.

Thus, after regenerating the local files, you must copy this code into airtable.js.

// Add import to the top of the file
import getEnvVars from '../../environment';

// Replace the following lines in `lib/airtable.js` (starting with this one)
const ENDPOINT_URL = 'https://api.airtable.com';

const { BASE_ID, AIRTABLE_API_KEY } = getEnvVars();

Airtable.configure({
  endpointUrl: ENDPOINT_URL,
  apiKey: AIRTABLE_API_KEY,
});

const base = Airtable.base(BASE_ID);

This code can also be found as a block comment in environment.example.

Note that the node repo's copy of airtable.js contains some custom helper functions, so the settings default to overwrite: false. The following only applies if it is set to overwrite: true.

The node repo uses the dotenv-safe package to locate environment variables.

Thus, after regenerating the files, you must add this line to airtable.js (we did so after the import lines):

require('dotenv-safe').config({ allowEmptyValues: true });

# Configuration

The schema generator's settings live in package.json. For the most part, they do not need to be edited.

# dccentralkitchen

    "airtable-schema-generator": {
    "output": "lib/airtable",
    "mode": "auto-headless",
    "envFileName": ".env.generator",
    "schemaMeta": {
      "Customers": {
        "lookupFields": [
          "Phone Number"
        ]
      }
    }
  }
  • output: where to output generated files
  • mode: how to get the data from Airtable
  • envFileName: in the customer and clerk repos, we use a different env file (environment.js), so we use a special .env.generator file for schema-generator-related environment variables.
  • schemaMeta describes any extra helper functions we want to create. In this case, we want to create a helper function that allows us to look up Customers by their Phone Number. This extra function is then created in request.js.

# dccentralkitchen-clerks

    "airtable-schema-generator": {
    "output": "lib/airtable",
    "mode": "auto-headless",
    "envFileName": ".env.generator",
    "schemaMeta": {
      "Customers": {
        "lookupFields": [
          "Phone Number"
        ]
      }
    }
  }
  • output: where to output generated files
  • mode: how to get the data from Airtable
  • envFileName: in the customer and clerk repos, we use a different env file (environment.js), so we use a special .env.generator file for schema-generator-related environment variables.
  • schemaMeta describes any extra helper functions we want to create. In this case, we want to create a helper function that allows us to look up Customers by their Phone Number. This extra function is then created in request.js.

# dccentralkitchen-node

    "airtable-schema-generator": {
    "output": "lib/airtable",
    "mode": "auto-headless",
    "overwrite": "false",
    "schemaMeta": {
      "Stores": {
        "lookupFields": [
          "Store Name"
        ]
      },
      "Products": {
        "lookupFields": [
          "Full Name"
        ]
      }
    }
  • output: where to output generated files
  • mode: how to get the data from Airtable
  • overwrite signifies that we do not want to overwrite airtable.js. This is set to false here only because in the Node.js app, we created some extra functions in airtable.js that the current version of the generator package doesn't have yet. However, if the schema generator package is updated, it's crucial to grab the latest version of airtable.js by setting this flag to true and re-running the generator.
  • schemaMeta describes any extra helper functions we want to create. In this case, we want to create a helper function that allows us to look up Stores by their Store Name (similar for Products). This extra function is then created in request.js.

Note that this repo actually has multiple copies of the generated files - one per base (lib/airtable, lib/airtable-prod, lib/airtable-test). When you run the generator, you must manually update AIRTABLE_BASE_ID in .env and output in package.json when updating the respective schemas.

# References

If you're curious about the official Airtable JavaScript client, check out https://airtable.com/BASE_ID/api/docs (opens new window) for the API documentation specific to each base (e.g https://airtable.com/app4fXK49bqcjDMEo/api/docs (opens new window) for the PROD base).

Updated (Promise-based) syntax used in the generated airtable.js: https://github.com/Airtable/airtable.js/blob/master/CHANGELOG.md (opens new window)

← Deploying Updates →