Published on

Introduction to Airtables

Authors
airtable

BACKGROUND

In my day-to-day interaction with programming projects, most of my clients specify that some part of their data should be stored on an excel sheet as part of their data backup and warehousing strategy. This process takes most of my time as I have to interact with two or more data sources as per the scope of the given project.
As I did my research, I came across this awesome platform AIRTABLE, which could have made my work easier by providing me with a data storage facility, an API endpoint to interact with my data programmatically, and a beautiful well-presented UI interface that my clients could interact with their records.

In this tutorial, I will be showcasing how you can perform CRUD operations to your data on the Airtable platform.

Please use my referral link to create your new account as a token of appreciation for my effort. LINK

What is Airtable

Airtable is an easy-to-use online platform for creating and sharing relational databases. The user interface is simple, colorful, friendly, and allows anyone to spin up a database in minutes. You can store, organize, and collaborate on information about anything

In this article, We will explore how to manipulate a base(Airtable's word for a database) through its simple REST API to perform the basic CRUD (Create, Read, Update, and Delete) operations on the data stored.

AIRTABLE'S KEY CONCEPTS

  • A Base: A base is a database that contains all of the information you need for a specific project, and is made up of one or more tables.
    Bases are made up of one or more tables, which are essentially like different sheets or tabs of a spreadsheet.

  • A Table: This is one set of data, organized in columns

  • A View: This is a particular representation of a table. You can have the same data presented in different views.

Getting Started

After creating an Airtable account, you will be presented with a list of sample bases(Databases) that you can use to quick start a new project. You can also start from scratch by selecting a workspace, and clicking the Add a base or create a base button as displayed in the image below.

introduction

Incase you have existing data in a spreadsheet, you can also import the data to airtable.

Authentication

Airtable comes with a simple REST API to perform the basic CRUD operations on the data stored. You'll need to have a base i.e. a table/ sheet set up before you can start to programmatically manipulate the database.

After creating your table/sheet structure, you will need to get an API Key for verification purposes when accessing the base(Database) programmatically. This can be accessed by clicking the account link that is found under the user icon image as displayed below.

icon

After clicking the icon, you will be redirected to your account's settings page where you will find your API key as shown below. Copy the key and store it in a safe place as we shall use it in the coming steps.

Do not share your KEYS with anyone. In case they get exposed, kindly delete and regenerate new API keys.

account

The last thing we need to do before we can interact with our base programmatically is to create a table that we can manipulate from our codebase. The name of our table will be Users and the properties inside the table will be Name - Long Text, Email - Email and Country - Single line text as displayed below.

structure

😀 Talk is cheap, show me the code 😀

APPLICATION CODEBASE SETUP

A basic Node application contains a .js file and a package.json file. which is used to list your project dependencies and start scripts.

To generate a package.json file in your application, navigate to your project folder via the terminal or git-bash for windows users and type the following command :

npm init || npm init -y

Using npm init without the flag -y will generate some questions that will be able to give a clear description of your application and its dependencies.

Installing Dependencies

Run this command to install the required dependencies :

npm i express cors  body-parser airtable dotenv

The above command will install the following dependencies :

  • express : A web application framework for Node.js.
  • body-parser : A middleware that handles post requests in express.
  • airtable : Nodejs airtable library.
  • cors : Handles cors headers.
  • dotenv : A module that loads environment variables from a .env file into process.env.

After the installation, lets create our app.js file. This is where we shall setup our express server.

Add the following code to the created file :

const express = require('express')
const app = express()
const cors = require('cors')
const bodyParser = require('body-parser')

app.use(cors())
app.use(bodyParser.urlencoded({ extended: false }))

app.get('/', (req, res) => res.send('Hello from Airtable'))

const PORT = process.env.PORT || 5000

app.listen(PORT, () => {
  console.log(`Server running on ${PORT}`)
})

To start the server use the command :

node app.js

When you visit the Url: localhost:5000, you should be able to see the string 'Hello from Airtable' running on your browser.

To interact with Airtable programmatically, we will have to bring the API key, Airtable base id, and Airtable table name into the project. As this can be sensitive information that needs not be shared with everyone, create a .env file in the root project folder and fill in the values associated with each key.

All the information can be obtained from your airtable project as we did the setup above.

AIRTABLE_API_KEY = xxxxxx
AIRTABLE_BASE_ID = xxxxxx
AIRTABLE_TABLE_NAME = xxxxxx

To interact with Airtable within our application without having to repeat the same code every time in different files, we will create a utils folder where we shall put the airtable.js file.

This will enable us to achieve the principle of don't repeat yourself by reusing the file anytime we need to make use of it.

If you have to repeat a block of code, then it should be a function. Don't repeat yourself.

Create an airtable.js file inside the utils folder and add the following to it.

require('dotenv').config()
const Airtable = require('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)

module.exports = {
  base,
  table,
}

From the code above, I imported the dotenv package to access the environment variables stored in the .env file. I then initialized and configured the Airtable library which enabled me to create and export variables that will enable one to access the base and table globally within the application.

To start interacting with the base, navigate back to the server file app.js and import the configurations above by adding the following in the imports section.

const { table } = require('./utils/airtable')

CRUD (Create, Read, Update, Delete)

Let’s now see how to perform some very common data operations.

Create a record (User)

You can add a new record by calling the create() method on the table object. You pass an object with the field names (you don’t need to pass all the fields, and the ones missing will be kept empty). In this case, if there is an error we just log in, and then we output the id of the row just entered.

app.post('/create', async (req, res) => {
  await table.create(
    {
      Name: ' Ian',
      Email: 'test@gmail.com',
      Country: 'Kenya',
    },
    (err, record) => {
      if (err) {
        console.error(err)
        return
      }
      console.log(record.getId())
      return res.json(record)
    }
  )
})

When we visit postman and make a post request to the /create endpoint the above data will be posted to the spreadsheet we created.

Get/Read A Specific Record

You can get a specific record by using the find() method on the table object, passing a record id:

// GET A SINGLE RECORD
app.get('/single', async (req, res) => {
  const id = 'record_id'
  table.find(id, (err, record) => {
    if (err) {
      console.error(err)
      return
    }
    /* here we have the record object we can inspect */
    console.log(record)
    return res.json(records)
  })
})

From the record object returned by find() one can get the following record content:

record.get('Name')
record.get('Email')
record.get('Country')

GET ALL RECORDS

app.get('/all', async (req, res) => {
  const records = await table.select({}).firstPage()
  console.log(records)
  return res.json(records)
})

Update a Record

To update a record, call the update() function and pass the ID of the record and the fields you want to update.

app.patch('/update', async (req, res) => {
  const id = 'record_id'
  await table.update(
    id,
    {
      username: 'John',
    },
    (err, record) => {
      if (err) {
        console.error(err)
        return
      }
      console.log(record.get('username'))
      return res.json(record.get('username'))
    }
  )
})

Delete a Record

To delete a record, call the destroy() and pass the ID of the record you want to delete.

app.delete('/delete', async (req, res) => {
  const id = 'rectHNeBP4u36mL6m'
  await table.destroy(id, (err, record) => {
    if (err) {
      console.error(err)
      return
    }
    console.log('Deleted record')
    return res.json('Deleted Record')
  })
})

Pagination

If you do have more than 100 items in your table, to get access to the other records you have to use the pagination functionality. Instead of calling firstPage() you call eachPage(), which accepts 2 functions as arguments.

I hope this article has been able to enable you to understand and get started with Airtable.

The source code to this article can be found Here

For further clarification, feel free to contact or comment.