How to Use Airtable as a Production Database in 30 Seconds
This Autocode app provides an example of how to use Airtable as a database, completely accessible with an API! All the
endpoints are accessible via HTTP and contain sample code for inserting, updating, deleting, and querying for rows
using various methods from the airtable.query
API from Autocode's Standard Library. Deploy your own working version of this
app in just a few clicks, then fork it to a sandbox to customize it however
you'd like and use it as inspiration for your own use cases.
# 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"]
}
]
TL;DR (30s)
Before pressing the install button, you'll need to clone your own copy of the
template Airtable base by clicking here
to add the base to your own Airtable account.
Once you've done that, install the app to your Autocode account by pressing the
green button above. When prompted to link an Airtable base, follow the instructions
in the flow to link your Airtable account, then select the base you just cloned.
After that, your app should be ready to go! Try accessing a few of the endpoints
via their URLs and see what is returned/what happens to your new database.
You can check out the Calling Endpoints section below for example calls.
How It Works
When you link your cloned Airtable base to your app and install it to your account, Autocode
automatically handles authentication between your app and your Airtable account
using your app's token (see the const lib = require('lib')({token: process.env.STDLIB_SECRET_TOKEN})
line at the top of all the endpoints).
Airtable bases contain tables, and tables contain records. The example base in
this cookbook contains two tables, one named My Books and one named Authors.
For the actual queries, each endpoint 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
}]
});
Which will return:
[
{
"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"]
}
]
Airtable is a relational database, and expresses relationships between tables (joins)
using special fields called linked record fields. In the cloned base,
My Books has one such field called Author Id.
One limitation of Airtable is that you can't directly pull in data from joined
tables — you'll notice in the above response that Author Id contains an
Airtable record id rather than data from the Authors table. However, you can
use another special field type called a lookup field to cache data from
one table in another. In the example base, the lookup field Author caches the
name of the author matching the Author Id of a book. Airtable will
automatically keep lookup fields up to date, so there's no direct management required!
The functions/select/my_books/author/icontains.js endpoint in this cookbook
shows an example of how to make queries against a lookup field.
You'll notice that both the linked record field Author Id and the lookup field
Author in the cloned base are returned as arrays. This is because Airtable
treats all joins as many-to-many. You can add logic in your endpoints to
flatten the array if you'd prefer.
For more information on Airtable types, check out
Airtable's official documentation.
These API calls use the KeyQL query language.
If you're interested in a deep dive, you can check it out for more examples!
Calling Endpoints
As previously mentioned, these 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 Google Sheets 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.
You can find examples for each endpoint below.
Endpoints
functions/select/my_books/author/icontains.js
This endpoint is an example of an icontains KeyQL query. The icontains and contains operators
work differently when querying array fields and string or text fields. For
array fields, they check to see whether some element in the array matches the query,
and for string or text fields they check whether the query is contained within.
In this case, because Author is a lookup field and therefore classified by
Airtable as an array, the query looks for rows in the linked Airtable base's
My Books table where the Author array field contains
the query parameter (case-insensitively). From the sample base, it returns:
$ curl --request GET --url \
'https://YOUR_USERNAME.api.stdlib.com/airtable-db-examples@dev/select/my_books/author/icontains/?query=N.K.%20Jemisin'
[
{
"Id": 3,
"Title": "The Stone Sky",
"Genre": "Science Fiction",
"Published On": "2017-08-15",
"Author Id": ["rec00000000000000"],
"Author": ["N.K. Jemisin"]
}
]
functions/select/my_books/genre/is.js
This endpoint is an example of a is KeyQL query. It looks for rows in
the linked Airtable base's My Books table where the Genre field
(case-sensitively) matches the parameter query. From the sample base, it returns:
$ 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"]
}
]
functions/select/my_books/published_on/date_lt.js
This endpoint is an example of a date_lt KeyQL query. It looks for rows in
the linked Airtable base's My Books table where the Published On field is
after the query parameter, formatted as 2000/01/01. From the sample base, it returns:
$ curl --request GET --url \
'https://YOUR_USERNAME.api.stdlib.com/airtable-db-examples@dev/select/my_books/published_on/date_lt/?query=2000/01/01'
[
{
"Id": 4,
"Title": "Kon-Tiki",
"Genre": "Nonfiction",
"Published On": "1947-11-02",
"Author Id": ["rec00000000000000"],
"Author": ["Thor Heyerdahl"]
},
{
"Id": 2,
"Title": "The Three Musketeers",
"Genre": "Historical Fiction",
"Published On": "1844-03-01",
"Author Id": ["rec00000000000000"],
"Author": ["Alexandre Dumas"]
}
]
functions/insert/my_books.js
This endpoint is an example of an insert query. It passes the input parameters
into the fieldsets parameter of the airtable.query.insert
API. For example, to add The Count of Monte Cristo to the books list, you
could make the following request (all parameters are lower-case):
$ curl --request POST \
--header "Content-Type: application/json" \
--data '{"title":"The Count of Monte Cristo","author":"Alexandre Dumas","genre":"Historical Fiction","publishedOn":"01/01/1844"}' \
--url 'https://YOUR_USERNAME.api.stdlib.com/airtable-db-examples@dev/insert/my_books/'
Note: Autocode APIs do not respond exclusively one HTTP method over another, and instead
rely on descriptive naming to avoid confusion over functionality.
functions/update/my_books.js
This endpoint is an example of an update query. It books where the title exactly
match the title parameter, and updates other fields based on the other parameters. It uses the
airtable.query.update API.
For example, to update the Published On field of The Doors of Stone to
2021/05/01 in your base, you could make the following request:
$ curl --request POST \
--header "Content-Type: application/json" \
--data '{"title":"The Doors of Stone","publishedOn":"2021/05/01"}' \
--url 'https://YOUR_USERNAME.api.stdlib.com/airtable-db-examples@dev/update/my_books/'
Note: This can affect multiple rows if more than one row matches the query conditions.
functions/delete/my_books.js
This endpoint is an example of a delete query. It removes rows
of people whose titles exactly match the title parameter. It uses the
airtable.query.delete API.
For example, to remove Kon-Tiki from your base, you could make the following
request:
$ curl --request GET --url \
'https://YOUR_USERNAME.api.stdlib.com/airtable-db-examples@dev/delete/my_books/?title=Kon-Tiki'
Note: This can affect multiple rows if more than one row matches the query conditions.
Thank You!
If you have any questions or feedback, the best thing to do is to join our community Slack
channel. You can get an invite from the Community tab in the top bar.
If you want to stay up to date on the latest from Autocode, you can also follow
us at @AutocodeHQ. Happy hacking!
