Using Airtable as a database
Tags: airtable, express, node, database
Published: 2020-05-17 12:44:44

Hi everyone,

I recently used [Airtable](https://airtable.com/) 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. 

{% link https://dev.to/rizkyrajitha/build-your-first-expressjs-server-from-scratch-52c3 %}

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](https://en.wikipedia.org/wiki/Cross-origin_resource_sharing) 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](https://dev-to-uploads.s3.amazonaws.com/i/xqk2hrsecb88607ixcdt.PNG)


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);
});


```

and run this by `node app.js`

![server running](https://dev-to-uploads.s3.amazonaws.com/i/3bs1ssm6yeb6lb0a4o2v.PNG)

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

create a [Airtable](https://airtable.com/) account, and create a new base.

![create a base](https://dev-to-uploads.s3.amazonaws.com/i/wrvi8sgprk654r5f66fe.PNG)


next name it 

![name base](https://dev-to-uploads.s3.amazonaws.com/i/7u965hzl7i076qhxv6u1.PNG)

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

![Air table table](https://dev-to-uploads.s3.amazonaws.com/i/92v4vmjlhrrjl0e2j4p2.PNG)

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](https://dev-to-uploads.s3.amazonaws.com/i/gaa6bnkbsbgjex7w0vg9.PNG)

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

![api key](https://dev-to-uploads.s3.amazonaws.com/i/dgw5cil5i4ux03cylkwa.jpg)

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

![Api docs](https://dev-to-uploads.s3.amazonaws.com/i/cl0tu2oc99i1or7olagl.jpg)

select base you created 

![select base](https://dev-to-uploads.s3.amazonaws.com/i/gpz0t3m49cs2izbbbygt.jpg)

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 ](https://dev-to-uploads.s3.amazonaws.com/i/yriyf9ivndjaa6xwd0tb.jpg)



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);
    });
});
```

if we send a `GET` request to `http://localhost:5000/view` via [postman](https://www.postman.com/) we will get a response with our existing data in `seriescharacters` table

![postman output](https://dev-to-uploads.s3.amazonaws.com/i/f667qg9bmr77yxw8cnjn.PNG)

console output 

![console](https://dev-to-uploads.s3.amazonaws.com/i/kihkf34hwft7opnliagr.PNG)

### 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);
    });
});

```

if we send a `POST` request to `http://localhost:5000/create` with our data via [postman](https://www.postman.com/) we will get a response with our data including the one we just added `seriescharacters` table.

![Post man create](https://dev-to-uploads.s3.amazonaws.com/i/npuxxo2vrkbl3k52q2vy.jpg)

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

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/9fqbqp0x98xcsdft9ng8.PNG)

### 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);
    });
});

```

if we send a `POST` request to `http://localhost:5000/update` with our data via [postman](https://www.postman.com/) we will get a response with the updated record.

![postman update](https://dev-to-uploads.s3.amazonaws.com/i/0pjavy4biqwe2vl9gh15.jpg)

### 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);
    });
});

```

if we send a `POST` request to `http://localhost:5000/delete` with the id of the record we need to delete via [postman](https://www.postman.com/) we will get a response with delete record id and deleted flag.

![postman delete](https://dev-to-uploads.s3.amazonaws.com/i/2cwzqwdqgoib36nhpunj.jpg)


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

{% gist https://gist.github.com/RizkyRajitha/041bb41f46647a444abd3724a3cb4266 %}

## Thank you for reading until the end
## Stay safe
## Cheers 🥂 , Have a Nice Day.