How to Use Airtable as a Database

Airtable is a spreadsheet / database hybrid. If you are familiar with Airtable you will already know that it is a spreadsheet on steroids. It can also be a convenient way to access a basic database for your app. In this article I will explain how to create a simple node.js app to interact with the Airtable database API.

Step 1. Create a Base

First log in or create an Airtable user account.

A base is an individual spreadsheet / database where you can store your data. Bases are organized within workspaces. On the Airtable website, within your workspace, click on Add a base to add a new base.

Let's create a simple todo list. Open your base. Modify it so that it has three columns: todo, completed, and user. The todo field describes the todo, like: Buy milk. The completed field is a checkbox to indicate if the todo task has been done or not. The user field should identify the author of the todo, like: someone@gmail.com. The fields 'todo' and 'user' can be 'single line text' field type. The field 'completed' should be 'checkbox' field type. No need to add any todos at this point. We will do so later, programmatically.

Step 2. Get Your API Connection Data

We want to modify the data via the api. To access the api go to https://airtable.com/api. If you are logged into your account, your base should be listed on this page.

Seen at Airtable.com/api.

Click on your base to open the api documentation. A unique documentation page for your selected base should open. On this page you will find the information you will need to connect your base to your app. As Airtable puts it: your Airtable base will provide its own API to create, read, update, and destroy records.

API for the Todo App base.

Get the following information:

1. The table name.
2. The base id.
3. The API Key.

Step 3. Create a Node.js Project

On your computer, use a terminal to create a directory for this project.

mkdir airtable_nodejs
cd airtable_nodejs

Initialize a Node.js project.

npm init

Create two files in your project directory: index.js and .env.

Now install two node packages in your project: airtable and dotenv.

npm install airtable dotenv

We are using the dotenv package to manage the .env file. The .env file is meant to store environment variables, such as API keys, so that they don't need to be hard coded into the javascript files. Inside the .env paste your api connection data.

AIRTABLE_API_KEY = <your airtable api key>
AIRTABLE_BASE_ID = <your airtable base id>
AIRTABLE_TABLE_NAME = <your airtable table name>

Inside of the index.js file, write the following code to make the connection data variables available inside the index.js file.

require("dotenv").config();

Require the airtable package.

const Airtable = require("airtable");

Configure Airtable.

Airtable.configure({ apiKey: process.env.AIRTABLE_API_KEY });
const base = Airtable.base(process.env.AIRTABLE_BASE_ID);
const table = base(process.env.AIRTABLE_TABLE_NAME);

In order to create a todo we will add the following function.

const createTodo = async (todo, user) => {
  console.log(item, user);
  try {
    newRecords = await table.create([{ fields: { todo, user } }]);
    console.log(newRecords[0].id, newRecords[0].fields);
  } catch (error) {
    console.log(error);
  }
};

We will need to gather data for the todos. The interface for this app will be the command line terminal, so lets require the readline node package. Readline will allow command line input.

const rl = require("readline").createInterface({
  input: process.stdin,
  output: process.stdout,
});

Since readline must wait for the user's input, it can be considered asynchronous. We should pause our app while waiting for the input. This is why we will wrap each call to readline in a promise.

const prompt_todo = () => {
  return new Promise((resolve, reject) => {
    rl.question("Your todo: ", (todo) => {
      resolve(todo);
    });
  });
};

const prompt_user = () => {
  return new Promise((resolve, reject) => {
    rl.question("User: ", (usr) => {
      resolve(usr);
    });
  });
};

By calling the promises within an asynchronous function we can 'await' each promise. This way the program will pause until each question is answered. Once the user inputs all of the data, the program will proceed to create the todo.

const create = async () => {
  const todo = await prompt_todo();
  const user = await prompt_user();
  rl.close();
  createTodo(todo, user);
};

You can add functionality for read, update, and delete as well.

//read
table.select({}).firstPage();
//update
table.update([{ id, fields }]);
//delete
table.destroy([id]);

Finally call the create command to kick off the program.

create();

Run this program by entering node index.js in the terminal.

Get the source code for this project here.

Here linked is a simple todo app created in Next.js that utilizes Airtable's API. This app features a gui user interface, and authentication using NextAuth.