If you’re looking for a quick and easy way to persist your application data, look no further than Airtable. Airtable is basically an excel sheet like database that has tons of flexibility and a GUI for inspecting/creating data.
I’ve used Airtable as the DB for a few of my demo apps recently, and I love it. There are other great options like Firebase, for example, but Airtable seems the easiest for me. In this article, let’s explore how to create a base (Airtable’s word for a database) and then perform CRUD (Create, Read, Update, and Delete) operations against it in two different ways, raw HTTP requests and the Airtable JavaScript package on NPM.
As with most products these days, you can sign up for free with Airtable. They do have a paid tier, but the free tier is more than enough and will probably last you a while. Sign up here
Now that you’re signed up, let’s create our first base. You should be taken to a screen that displays all of your existing bases. I already have a few create that you can see here.
For demo purposes, we are going to use an existing template instead of creating one from scratch. Click on Add a base.
Then, choose Start With a Template.
Let’s use the Project Tracker
template. After a few seconds, you should see your new base listed. Go ahead and open it up. Once you do, notice there is lots of dummy data in here.
I would suggest taking a few minutes to poke around here. A couple of things to notice while you do.
Just based on this dummy data, you get a quick feel for how cool Airtable is!
The documentation for Airtable is another plus. It walks through examples of all of the CRUD operations that you can perform on your data. You can find the API Documentation here.
From there, select the base that we created a few minutes ago. The cool thing is that the documentation will now be specific to the data that is in that base.
One thing to take a look at at the top of the docus page is the Rate Limit
section. This explains the limitation on how many requests per second you can make to the API.
“The API is limited to 5 requests per second per base”
You can also read more about the Pricing here.
Like every other API, you’ll need some sort of private credential to pass along in your API requests for Airtable to verify it. In this case, the private credential is an API Key.
If you scroll to the Authentication
section, it will tell you a little bit more.
As we scroll through the docs, I’ve got the show API Key
checkbox checked. This is one of the easiest ways to find out what your key is. Although I’m sharing mine in these images, I’m going to change it by the time you read this.
Never share your API keys with anyone
Your API Key will need to be passed as a bearer token
in your Airtable requests. More on that in a second.
As your scroll down through the docs you’ll get to a section that shows you the details of the fields that you have in your base. If you started with the same template referenced above, this section is called Design Projects Table
.
I just wanted to make one clarification for working with Airtable. Fields
are referring to the properties that you specifically define for each one of your table. There are some properties that Airtable creates automatically (record id and created timestamp), but these are not included in fields.
You’ll see this in action below.
Here’s how the API calls work. I’m using the specific details for my example
You put all of this together and you get something like this.
https://api.airtable.com/v0/appu53qW5mpKdJAW2/Design%20projects
As mentioned earlier, you’ll need to pass your API Key as a bearer token
as well.
Again, the docs are really good.
Scroll down to the List Records
section, and you can see how what the HTTTP request looks like to retrieve a list of records.
In the URL that they give you, can you see the different parts that we referenced above? Also included are a few query parameters for filtering.
https://api.airtable.com/v0/appu53qW5mpKdJAW2/Design%20projects?maxRecords=3&view=All%20projects
Let’s give this a test. I’m going to use Postman for testing these HTTP requests.Inside of Postman, I choose to make a Get
request, pass it the URL, and then include the API Key in the Headers
section.
Each response from Airtable will return an object that has a property of records
which will hold all of the records that we have queried or udpated. It’s nice and consistent!
To retrieve a specific record, we simply pass the ID of the record we are looking for on to the end of the API and remove the query string parameter. You can grab one of the IDs out of the return body from the previous request. Here’s my URL.
https://api.airtable.com/v0/appu53qW5mpKdJAW2/Design%20projects
You should get a response like this.
To create a record, we use the same base URL that we have been using.
https://api.airtable.com/v0/appu53qW5mpKdJAW2/Design%20projects
The difference is that this becomes a POST
request and we include the record(s) we want to include in the body of the request. To pass JSON data in Postman, I usually select raw
and then JSON (application/json).
Similar to the response that we get back from Airtable, we send an object that has a property called records
and then a record for each item we want to create.
Notice in the return data that the record has two new properties, id
and createdTime
. This lets you know that the record was created successfully. You can also view your new record in the GUI to verify.
There are two different ways to update a record.
You’ll have to determine which of these makes more sense for you, but I’m going to show an example using Patch
. Again, same base URL.
https://api.airtable.com/v0/appu53qW5mpKdJAW2/Design%20projects
The only thing I’m going to update in the body is the name to Testy Test Updated!!
.
{
"records": [
{
"id":"recBW7Qan7p8eedRy",
"fields": {
"Name": "Testy Test Updated!!"
}
}
]
}
Check the GUI to see the updated record.
Lastly, we can delete a record. We do this by sending a DELETE
request and putting the record id on the end of the url.
https://api.airtable.com/v0/appu53qW5mpKdJAW2/Design%20projects?records[]=rec3u2SyIm7ZCktXU
The response that comes back verifies the record was deleted. You could also look in the GUI again.
Alright, we got most of the details covered. Now, let’s take a quick look at the Airtable package on NPM to see how to accomplish the same thing using JavaScript.
Since we are repeating steps, I’ll skip images and just show code snippets.
In your favorite editor, open a folder and create a file called app.js.
Then run the following command to initialize your project as a JavaScript project.
npm init
Then install the Airtable package.
npm install airtable
Now we need to initialize the Airtable object in our file.
const Airtable = require('airtable');
const base = new Airtable({ apiKey: '<YOUR_API_KEY>' }).base(
'appqvc1jKHBIRRbSy'
);
const table = base('Design projects');
For our CRUD operations, we will use the table
variable from now on. Each call that we make will return a promise. I’m using the async/await
pattern instead of .then()
and .catch()
to handle the returned promise.
Call the select()
function which takes an object where you can include query parameters. Earlier, we used maxRecords
and view
, so we can do the same here.
After calling select()
we also need to call firstPage()
. This is because of pagination. If we have lots of record in our table, we probably don’t want them all at one time, so we can call firstPage()
to get the first page of results.
const getRecords = async () => {
const records = await table
.select({ maxRecords: 3, view: 'All projects' })
.firstPage();
console.log(records);
};
To get a specific record, we can call find()
and pass the ID of the record we are looking for.
const getRecordById = async (id) => {
const record = await table.find(id);
console.log(record);
};
If you log out the results here, you might notice that there is a ton of information that gets returned. One trick I use is to strip out all of the extra data except for id
and fields.
I created a minify function to do this.
const minifyRecord = (record) => {
return {
id: record.id,
fields: record.fields,
};
};
With minify in place, my log output goes from this.
to this…
Much cleaner right?
To create a record, we call the create()
function and pass an object to represent the fields of the record.
const createRecord = async (fields) => {
const createdRecord = await table.create(fields);
console.log(minifyRecord(createdRecord));
};
To update a record, call the update()
function and pass the ID of the record and the fields you want to update.
const updateRecord = async (id, fields) => {
const updatedRecord = await table.update(id, fields);
console.log(minifyRecord(updatedRecord));
};
To delete a record, call the destroy()
and pass the ID of the record you want to delete.
const deleteRecord = async (id) => {
const deletedRecord = await table.destroy(id);
console.log(minifyRecord(deletedRecord));
};
Anytime you make HTTP requests, you need to be prepared for handling errors. In each of the examples above, you should surround the API call with a try/catch
so that you can handle errors if there are any.
const deleteRecord = async (id) => {
try {
const deletedRecord = await table.destroy(id);
console.log(minifyRecord(deletedRecord));
} catch (err) {
console.error(err);
}
};
Hopefully, you can see how quick and easy it is to use Airtable in your applications. If you’re using JavaScript, it’s a no-brainer because the NPM package is so easy to work with. Not to mention, the overall documentation is really good as well.
Question for you. What kind of DB are you using for your applications? Let me know in the comments!
#javascript #web-development