Rajitha Gunathilake
Posted on May 17, 2020
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
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
now our server-side is up let's peek to Airtable.
create a Airtable account, and create a new base.
next name it
and then open a new base. and you will see something similar to this.
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.
now let's head to account to get our API KEY which we will use to authenticate with the Airtable API.
we also need our base id to identify our base and table name . to get those data, visit the API docs page.
select base you created
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.
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 we will get a response with our existing data in seriescharacters table
console output
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 we will get a response with our data including the one we just added seriescharacters table.
also, we can see the updated table in real-time from Airtable.
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 we will get a response with the updated record.
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 we will get a response with delete record id and deleted flag.
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.
Posted on May 17, 2020
Join Our Newsletter. No Spam, Only the good stuff.
Sign up to receive the latest update from our blog.