How to Use Airtable as a Production Database (Analyzing Airtable Performance)
Tags: airtable, api, database, autocode
Published: 2020-12-11 07:56:03

---
title: How to Use Airtable as a Production Database (Analyzing Airtable Performance)
published: true
description: How and when to use Airtable as a production database with examples for querying, inserting, updating, and deleting via API
tags: airtable, api, database, autocode
cover_image: https://dev-to-uploads.s3.amazonaws.com/i/5fxhmkorg87ji2i8rlfw.png
---

I've been an [Airtable](https://airtable.com) user for around three years now, and over that span I've recommended it to more people than any other SaaS product. It "Just Works™" &mdash; while it has its limitations and doesn't provide all the flexibility of a relational database, the sheer quality of the UX often makes up for any shortcomings. Like [Google Sheets](https://dev.to/hacubu/how-to-use-google-sheets-as-a-database-responsibly-3ohk), Airtable particularly shines as a backend for apps where non-technical team members might need to examine or edit data, and the structure it provides through features like types, access control, views, and computed fields make working with it pleasant.

In this article, we'll go over:

- How to use Airtable as a database with some pseudo-join capabilities
- How to set up an HTTP-accessible API for your Airtable base that you can send requests to from a frontend
- Some trade-offs to consider when selecting Airtable for your project (like how the Airtable API scales)

![The example Airtable base](https://dev-to-uploads.s3.amazonaws.com/i/z2cyjcrjk6frlsltanx6.png)

```shell
# Returns all records from the "My Books" table with genre "Fantasy", case-sensitive
$ curl --request GET --url \
  'https://YOUR_USERNAME.api.stdlib.com/airtable-db-examples@dev/select/my_books/genre/is/?query=Fantasy'
```
```json
[
  {
    "Id": 1,
    "Title": "Rhythm of War",
    "Genre": "Fantasy",
    "Published On": "2020-11-17",
    "Author Id": ["rec00000000000000"],
    "Author": ["Brandon Sanderson"]
  },
  {
    "Id": 5,
    "Title": "The Doors of Stone",
    "Genre": "Fantasy",
    "Published On": null,
    "Author Id": ["rec00000000000000"],
    "Author": ["Patrick Rothfuss"]
  }
]
```

To host the sample app and handle authentication to the Airtable API, we'll use [Autocode](https://autocode.com), a Node.js app platform and editor with built-in autocomplete. Both [Airtable](https://airtable.com/pricing/) and [Autocode](https://autocode.com/pricing/) are free to start, so let's dive in!

# TL;DR (30s)

First, create your own copy of the [demo Airtable base by clicking here](https://airtable.com/addBaseFromShare/shrkGIiwUFcIz4Te4) and following the prompt.

Once you've done that, [click here to open the Airtable cookbook in Autocode](https://autocode.com/app/airtable/airtable-db-examples/), then press the green button to install the cookbook to your Autocode account. When asked to link an Airtable base, supply your API key and select the base you just cloned.

After that, you're live! You can try hitting some of the endpoints from your browser or cURL right away (the URLs will look something like `https://YOUR_USERNAME.api.stdlib.com/airtable-db-examples@dev/select/my_books/genre/is/?query=Fantasy`. You can also check out the [README for the Airtable cookbook](https://autocode.com/app/airtable/airtable-db-examples/) for more concrete examples.

# Limitations

Now that we've got a basic example set up, let's talk about some things you should consider when choosing between Airtable and other solutions.

## Performance

To gauge performance, I ran three experiments: **single record retrieval**, **full table retrieval**, and a **50,000 record table query test**.

### Single Record Retrieval

#### Methodology

For this experiment, I created tables containing various numbers of records with a with an autonumber field called `Key` and a text field called `Value`. I populated each `Value` with a single character. I then changed an arbitrary record's `Value` field to the string `"testing"`, then queried for records matching that `Value` using the [airtable.query](https://autocode.com/lib/airtable/query).

#### Results

![Single record retrieval test results](https://dev-to-uploads.s3.amazonaws.com/i/apiwaq9iyazghds99cfz.png)

The Airtable API performed spectacularly on this test, retrieving a single record in about the same time for table sizes from 10 to 50,000 records. There was a small increase in query time as the number of records in the table grew, but overall stayed fairly consistent. This result suggests that the Airtable API is well optimized for queries that return a small number of results.

### Full Table Retrieval

#### Methodology

For this experiment, I used the same tables as the previous experiment, but left the `where` parameter blank in my [airtable.query](https://autocode.com/lib/airtable/query) API call to select for all records in the table.

#### Results

![Full table retrieval test results](https://dev-to-uploads.s3.amazonaws.com/i/h4eb42q10lovd16dpkus.png)

The Airtable API performed worse on this test, crossing the 2s mark after retrieving 500 records and quickly becoming impractically slow after ~1,000 records.

### 50,000 Record Table Queries

#### Methodology

For this experiment, I wanted to see if querying with parameters would perform better than retrieving an entire table. Starting with the same table structure, I added a third field that I populated with the following (hacky) formula to simulate an even distribution of numbers (Airtable does not have a built-in random function):

```
MOD((MOD(VALUE(RECORD_ID()), 50000) + 1) * {Key}, 50000)
```

`{Key}` is the autonumbered field we defined earlier. `RECORD_ID()`, a function returning a random looking id that Airtable internally assigns each record, did not contain enough entropy on its own to evenly distribute numbers, and often evaluated to zero when evaluated as a number using the `VALUE()` Airtable formula operator, hence the hack of adding 1.

In practice, this formula returned values spread out sufficiently to ensure a roughly even distribution from 0 to 50,000 for each record, though using the generated values as random numbers for any important application is not recommended. I then queried the base for records where this third computed field was below a certain threshold to simulate a query matching a given number of "randomly selected" records.

**Sidenote:** For more on Airtable formula operators, [check out their official reference here](https://support.airtable.com/hc/en-us/articles/203255215-Formula-field-reference). The endpoints in the sample app all use [KeyQL parameters](https://github.com/FunctionScript/KeyQL) to make queries, but if you'd prefer to query Airtable using formulas, you can use [this endpoint on Autocode](https://autocode.com/lib/airtable/query/#records-find-formula).


#### Results

![50,000 record table query test results](https://dev-to-uploads.s3.amazonaws.com/i/l3mqzfz87yzr417oj71t.png)

The results for this test were similar to the full table retrieval test, showing that performance correlated mostly with the number of returned records.

### Conclusions

The Airtable API's performance scales, surprisingly, with the number of records returned in a given query rather than the size of the table. Even at table sizes at plan limits, the Airtable API performs well for queries that return small numbers of records. Unfortunately, Airtable formulas don't have a built-in concept of pagination, and the [KeyQL parameters translate to formulas](https://github.com/FunctionScript/KeyQL) under the hood, so the KeyQL limit parameter does not get around this issue.

Adding extra fields to the records in the table seemed to have a negligible impact on performance.

## Capacity and Throughput

[Airtable's top non-enterprise plan](https://support.airtable.com/hc/en-us/articles/115010928147-Airtable-plans#pro) is $24 a month and has an upper limit of 50,000 records per base. For free plan users, this limit is 1,200 records per base. 

Even for projects where you expect to exceed these limits, it may be better to avoid premature optimization and choose Airtable for ease-of-use, migrating if and when scale becomes a problem.

Airtable sets a [rate limit of 5 requests per second per base](https://community.airtable.com/t/api-request-daily-limit/34175).

## Relationships Between Tables

### Linked Record Fields

Airtable allows you to associate records in one table with records in another through a special type of field called a [linked record field](https://support.airtable.com/hc/en-us/articles/360021502354#h_0c3d1a65-1f35-4e67-9b14-6213d4d2bca9). In the [example base](https://autocode.com/app/airtable/airtable-db-examples/) from the TL;DR above, the `Author Id` field on the `My Books` table is an example of a linked record field, associating records from the `My Books` table with those from the `Authors` table.

If you're familiar with frameworks like [Ruby on Rails](https://rubyonrails.org/) or [Django](https://www.djangoproject.com/), you'll immediately think of a linked record as creating a relationship between the two tables. And it does, with the following caveats:

1. You can't select the field you're joining on. It must be the primary field for the table you're joining to.
1. When querying via API, all relationships are represented as many-to-many and are returned as arrays.
1. You can't make queries that use joins.

**\#3** is a particular bummer if you were hoping to use Airtable as a drop-in replacement for something like [Postgres](https://www.postgresql.org/). However, there are some neat tricks we can use!

### Lookup Fields

You may have also noticed in the example base that there is a field called `Author` in the `My Books` table. `Author` is another special type of field called a [lookup field](https://support.airtable.com/hc/en-us/articles/360042312194-Lookup-field-overview) which, as the name implies, allows a record in one table to lookup data from another field. In this case, the `Author` field on records in `My Books` contains the value of the `Name` field for the proper linked record in the `Authors` table. 

![The example Airtable base](https://dev-to-uploads.s3.amazonaws.com/i/z2cyjcrjk6frlsltanx6.png)

Those familiar with database theory will red-flag this as [denormalization of data](https://en.wikipedia.org/wiki/Database_normalization) &mdash; essentially caching copies of a field in two places. This is usually difficult to deal with, but Airtable populates and updates lookup fields automatically, handling the complexity for you.

Airtable returns the value of lookup fields along with other types of fields on a record. You can use the [airtable.query](https://autocode.com/lib/airtable/query) API to query records by the value of a lookup field like this:

```javascript
let bookQueryResult = await lib.airtable.query['@1.0.0'].select({
  table: 'My Books',
  where: [{
    'Author__contains': query
  }]
});
```

**Note:** As previously mentioned, Airtable treats lookup fields as arrays, even if they only contain one value. Therefore, you should prefer `contains` or `icontains` when querying them with [KeyQL](https://github.com/FunctionScript/KeyQL).

Through smart use of lookup fields, you can achieve results similar to basic joins in a traditional relational database. Of course, the more lookup fields you add, the more complex your base will become. If you find yourself needing to do this too often, it might make sense to make multiple queries for data from different tables or to choose a different database solution.

# How It Works

[Autocode automatically handles authentication](https://docs.autocode.com/introduction-to-autocode/components-of-autocode/api-authentication-management/) between your app and your Airtable account. When you link an Airtable base, Autocode associates your provided Airtable key and the linked base to your app's token (see the `const lib = require('lib')({token: process.env.STDLIB_SECRET_TOKEN})` line at the top of all the endpoints in the sample cookbook). When you call an Airtable API with `await lib.airtable`, Autocode makes the appropriate credentials available to the API, allowing it act as a proxy and use your credentials to call Airtable on your behalf.

You'll notice each endpoint contains Node.js code that calls a method from the [airtable.query API](https://autocode.com/lib/airtable/query/).

```javascript
let bookQueryResult = await lib.airtable.query['@1.0.0'].select({
  table: 'My Books',
  where: [{
    'Genre__is': query
  }]
});
```

Though there is an Airtable endpoint on Autocode that allows you to use [Airtable formulas directly](https://autocode.com/lib/airtable/query/#records-find-formula), the API calls in the sample project use the [KeyQL query language](https://github.com/FunctionScript/KeyQL). Check it out if you're interested in seeing more ways you can use KeyQL, including other available operators, and how it converts parameters to Airtable formulas.

# Calling Endpoints

Because these API endpoints are accessible via HTTP, so you can make calls to them via fetch, cURL, or whatever other HTTP client you prefer. You can use your web browser directly:

![Airtable records returned from calling API endpoint from the web browser](https://dev-to-uploads.s3.amazonaws.com/i/1xfinq574v7aic88rit1.png)

And you can even use the same [lib-node](https://github.com/stdlib/lib-node) package that the endpoints use to call the Airtable APIs:

![Calling API endpoints with the lib package](https://dev-to-uploads.s3.amazonaws.com/i/95ehwwp75lezndy58kdk.png)

Your endpoints will respond to either GET or POST requests. Parameters are parsed from the querystring for GET requests and the request body for POST requests. Each endpoint has default parameters set for the sake of clarity.

# Endpoints

For a full explanation of each endpoint in the example cookbook, along with usage examples, check out the [Autocode Airtable cookbook page](https://autocode.com/app/airtable/airtable-db-examples/).

# Thank You!

If you have any questions or feedback, the best thing to do is to join the Autocode community Slack channel. You can get an invite from the question mark tab in the top bar on [autocode.com](https://autocode.com). You can also reach out to me directly on Twitter [@Hacubu](https://twitter.com/hacubu).

If you want to stay up to date on the latest from Autocode, you can follow [@AutocodeHQ](https://twitter.com/AutocodeHQ). Happy hacking!