I've been an Airtable 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™" — 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, 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)
# 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'
[
{
"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, a Node.js app platform and editor with built-in autocomplete. Both Airtable and Autocode 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 and following the prompt.
Once you've done that, click here to open the Airtable cookbook in Autocode, 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 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.
Results
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 API call to select for all records in the table.
Results
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. The endpoints in the sample app all use KeyQL parameters to make queries, but if you'd prefer to query Airtable using formulas, you can use this endpoint on Autocode.
Results
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 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 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.
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. In the example base 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 or Django, you'll immediately think of a linked record as creating a relationship between the two tables. And it does, with the following caveats:
- You can't select the field you're joining on. It must be the primary field for the table you're joining to.
- When querying via API, all relationships are represented as many-to-many and are returned as arrays.
- 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. 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 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.
Those familiar with database theory will red-flag this as denormalization of data — 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 API to query records by the value of a lookup field like this:
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.
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 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.
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, the API calls in the sample project use the KeyQL query language. 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:
And you can even use the same lib-node package that the endpoints use to call the Airtable APIs:
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.
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. You can also reach out to me directly on Twitter @Hacubu.
If you want to stay up to date on the latest from Autocode, you can follow @AutocodeHQ. Happy hacking!
Top comments (1)
Great study, @hacubu !
Thanks for sharing it