DEV Community

Rajitha Gunathilake
Rajitha Gunathilake

Posted on • Updated on

Using Airtable as a database

Hi everyone,

I recently used Airtable and found out that it has an awesome API, so we can use it as a simple yet useful way as a database.

so let's get started

first, we will create an npm project and spin up an expressjs server

if you are new to express check out my post on hello world in express.

so after initializing the project lets install dependencies to start the server.

  • body-parser - a middleware to parse the body of incoming requests
  • cors - ho handle cors headers
  • express - to spin up our expressjs server
  • morgan - a middleware utility tool that logs the server events (this is not essential but useful for debugging)
  • node-fetch - fetch API for node environment

npm i express cors morgan body-parser node-fetch

npm deps

so after installing everything lets create our app.js file.
here we will create our express server

const express = require("express");
const app = express();
const cors = require("cors");
const bp = require("body-parser");
const fetch = require("node-fetch");

app.use(cors());
app.use(bp.urlencoded({ extended: false }));
app.use(bp.json());
app.use(require("morgan")("dev"));

const port = process.env.PORT || 5000;


app.listen(port, () => {
  console.log("listning on " + port);
});


Enter fullscreen mode Exit fullscreen mode

and run this by node app.js

server running

now our server-side is up let's peek to Airtable.

create a Airtable account, and create a new base.

create a base

next name it

name base

and then open a new base. and you will see something similar to this.

Air table table

now customize it as you like. I will add two fields name and country.
and I will add few records so when we fetch data it has somethings to show.

after everything, mine looks like this.

Airtable populated

now let's head to account to get our API KEY which we will use to authenticate with the Airtable API.

api key

we also need our base id to identify our base and table name . to get those data, visit the API docs page.

Api docs

select base you created

select base

now copy base id and table name from the docs. base id is led by app and table name is the name you gave when you customize the table.

docs

after creating an API key and getting necessary information,
let's head back to our server.

I created a separate folder for configs and keys, but you can also directly use the API key in the code since this is development purposes only. but make sure you don't commit your keys with the code.

Airtable gives us 4 basic operations with the API

  • Read
  • Create
  • Update
  • Delete

Reading Table

I will create a get route http://localhost:5000/view to view existing data in our table

const express = require("express");
const app = express();
const cors = require("cors");
const bp = require("body-parser");
const fetch = require("node-fetch");

app.use(cors());
app.use(bp.urlencoded({ extended: false }));
app.use(bp.json());
app.use(require("morgan")("dev"));

const AIRTABLEAPI = require("./config/env").airtableapikey; // import airtable api key 
const AIRTABLEBASEID = require("./config/env").airtablebaseid;// import airtable base  id 
const AIRTABLETABLENAME = "seriescharacters"; // table name

const port = process.env.PORT || 5000;

app.get("/view", (req, res) => {

//we need to send a "GET" request with our base id table name and our API key to get the existing data on our table. 

  fetch(
    `https://api.airtable.com/v0/${AIRTABLEBASEID}/${AIRTABLETABLENAME}?view=Grid%20view`,
    {
      headers: { Authorization: `Bearer ${AIRTABLEAPI}` } // API key
    }
  )
    .then((res) => res.json())
    .then((result) => {
      console.log(result);
      res.json(result);
    })
    .catch((err) => {
      console.log(err);
    });
});
Enter fullscreen mode Exit fullscreen mode

if we send a GET request to http://localhost:5000/view via postman we will get a response with our existing data in seriescharacters table

postman output

console output

console

Create new record

now let's add a new record by creating a POST route http://localhost:5000/create.

create handler

app.post("/create", (req, res) => {
  console.log(req.body);

  var datain = req.body;

  var payload = {
    records: [
      {
        fields: datain,
      },
    ],
  };

//we need to send a "POST" request with our base id, table name, our API key, and send a body with the new data we wish to add.

  fetch(`https://api.airtable.com/v0/${AIRTABLEBASEID}/${AIRTABLETABLENAME}`, {
    method: "post", // make sure it is a "POST request"
    body: JSON.stringify(payload),
    headers: {
      Authorization: `Bearer ${AIRTABLEAPI}`,   // API key
      "Content-Type": "application/json",  // we will recive a json object
    },
  })
    .then((res) => res.json())
    .then((result) => {
      console.log(result);
      res.json(result);
    })
    .catch((err) => {
      console.log(err);
    });
});

Enter fullscreen mode Exit fullscreen mode

if we send a POST request to http://localhost:5000/create with our data via postman we will get a response with our data including the one we just added seriescharacters table.

Post man create

also, we can see the updated table in real-time from Airtable.

Alt Text

Updating a record

update handler

app.post("/update", (req, res) => {
  console.log(req.body);

  var datain = req.body;

  var payload = {
    records: [
      {
        id: datain.id,
        fields: datain.updatedata,
      },
    ],
  };

//to update a record we have to send the new record with it's the id to Airtable API. 


  fetch(`https://api.airtable.com/v0/${AIRTABLEBASEID}/${AIRTABLETABLENAME}`, {
    method: "patch", // make sure it is a "PATCH request"
    body: JSON.stringify(payload),
    headers: {
      Authorization: `Bearer ${AIRTABLEAPI}`, // API key
      "Content-Type": "application/json",
    },
  })
    .then((res) => res.json())
    .then((result) => {
      console.log(result);
      res.json(result);
    })
    .catch((err) => {
      console.log(err);
    });
});

Enter fullscreen mode Exit fullscreen mode

if we send a POST request to http://localhost:5000/update with our data via postman we will get a response with the updated record.

postman update

Deleting a record

now let's delete a record by creating a POST route http://localhost:5000/delete.

delete handler

app.post("/delete", (req, res) => {
  console.log(req.body);

//we need to send a "DELETE" request with our base id table name, the id of the record we wish to delete and our API key to get the existing data on our table. 

  fetch( `https://api.airtable.com/v0/${AIRTABLEBASEID}/${AIRTABLETABLENAME}/${req.body.id}`,
    {
      method: "delete", // make sure it is a "DELETE request"
      // body: JSON.stringify(payload),
      headers: {
        Authorization: `Bearer ${AIRTABLEAPI}`, // API key
        //   "Content-Type": "application/json",
      },
    }
  )
    .then((res) => res.json())
    .then((result) => {
      console.log(result);
      res.json(result);
    })
    .catch((err) => {
      console.log(err);
    });
});

Enter fullscreen mode Exit fullscreen mode

if we send a POST request to http://localhost:5000/delete with the id of the record we need to delete via postman we will get a response with delete record id and deleted flag.

postman delete

We successfully went through all the CRUD operations in Airtable 😎.

This is very useful if you have a spreadsheet and you need to update data programmatically from your apps .plus Airtable has many more features other than spreadsheets, so you can suit your needs.

final app.js file

Thank you for reading until the end

Stay safe

Cheers 🥂 , Have a Nice Day.

Top comments (4)

Collapse
 
ronaldvonk profile image
ronald-vonk

I was looking for a way to 'work around' Airtable's own API, because I wanted to embed the fetching in html. In your blog I found the perfect example and got it working! Thanks

Collapse
 
rizkyrajitha profile image
Rajitha Gunathilake

glad it helped ✌

Collapse
 
jeybuddhdev profile image
Jaynish Buddhdev

Hey @rizkyrajitha , I really like your Article. Thank you for writing this.

I just wanted to know, Airtable has some Rate Limits on every package. How can we handle that?

Collapse
 
rizkyrajitha profile image
Rajitha Gunathilake

hi
thanks ✌.
yeah it have some limits ,
for example maximum rows we can get in single request is 100 , so if you want more than that we have to use a recursive pagination mechanism.

another limitation is that api is limited to 5 requests per second , in such occasion we have to slow down .

likewise solution depends on the limitation itself .