Airtable DB: How to literally use Airtable as a Database with Node.js

Airtable can be the perfect database for prototypes. You can load it up with data in a couple minutes. Anyone on your team can use it. It's relational. It comes with all the integrations you could dream of. It all feels so intuitive.

So you create your tables and start adding your data. In a couple minutes, you have a working form AND workflow.

Now to get the data out of Airtable and into your UI.

You begin to tinker with the Airtable REST API. The docs are dynamically generated to your base. Amazing! Copy...paste... Then you hit the rate limit. Pagination. Sorting. And a strange filterBy parameter. You are now down a 2 hour rabbit hole 😰

After feeling this same let down, we walked away from Airtable thinking, "Airtable can be the best database for prototypes...if only it was queryable through SQL."

So we decided to build a tool, Sequin, to turn Airtable into a Postgres database.

In this tutorial we'll show you how to use Node.js, pg, and Sequin to build on your Airtable data remarkably fast - starting with script. Let's dig in.

Airtable Setup

For this tutorial, we'll be using Airtable's Inventory Tracking Template as an example data set:

Let's say you want to write a Node script to quickly determine which products are running low and then automatically create purchase orders for those items. You'll use Node.js to handle the logic and Sequin to interface with Airtable to make reading and writing data easier. The data flow will look something like this:

To get started, add the Airtable inventory tracking template to your Airtable workspace:

1. Log in to your Airtable workspace and then open the inventory tracking template in a new tab.
2. Click the Use Template button to add the inventory tracking template to your workspace.

Sequin Setup

Now, use Sequin to provision a Postgres database that contains all the data in the inventory tracker base:

Step 1: Go to https://app.sequin.io/signup and create a Sequin account:

Step 2: Connect the Inventory Tracking base you just created to Sequin using the tutorial or check out the Quickstart guide. It's as easy as copying and pasting your API Key into Sequin, selecting the inventory tracker base you just added to your workspace, and clicking Create:

Step 3: Sequin will immediately provision you a Postgres database and begin syncing all the data in the inventory tracker base. You'll be provided with credentials for you new database. Keep these handy as you'll use them to connect your Sequin database to Node.js.

Node.js Setup

For this tutorial, we'll be using the latest, stable release of Node.js - which at the time of writing is version 14.16.1. If you don't already have Node installed on your machine, go to Nodejs.org and follow the instructions for your operating system.

Open up your terminal and create a new directory for this project. You can call it something like sync_inc_tutorial. Then, navigate into that directory you just created and initialize npm by running npm init -y:

mkdir sync_inc_tutorial
cd sync_inc_tutorial
npm init -y

Your directory will now contain a package.json and a node_modules directory so you can add additional libraries. You'll be using three libraries in this tutorial:

• pg — The Node-postgres library makes it easy to connect to your Sequin Postgres database and query your data.
• dotenv — To keep your Sequin database password and Airtable API key out of version control, you'll use the dotenv library to manage environment variables.
• node-fetch — You'll use the node-fetch library to make HTTP requests using the Sequin proxy.

Install these libraries and their dependencies by running the following:

npm install pg --save
npm install dotenv --save
npm install node-fetch --save

Finally, add a .env and index.js file to the directory to complete your setup:

touch .env index.js

With everything setup, open the directory in your IDE of choice.

Reading Data

You'll read data from Airtable through your Sequin database. Because your Sequin database is a standard, cloud-hosted Postgres database — you'll connect, authenticate, and query using pg and SQL.

First, take care of some housekeeping and set up your environment variables. By using environment variables you'll keep your database and API passwords out of version control.

Open up the .env file and define a new environment variable called PG_PASSWORD and set the value to the password for your Sequin database:

PG_PASSWORD = "▒▒▒▒▒_YOUR_PASSWORD_HERE_▒▒▒▒▒"

Reminder: You can retrieve the credentials for your Sequin database at any time by navigating back to the Sequin console and clicking the black Connect button on your Inventory Tracker resource. You'll find the raw credentials you need at the bottom of the page.

Now, configure the connection to your Sequin database. Open index.js and add the following:

require("dotenv").config();
const { Client } = require("pg");

const client = new Client({
    host: "evening-soiree.syncincdb.com",
    user: "ru▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒",
    database: "db▒▒▒▒▒▒▒▒▒▒▒",
    password: process.env.PG_PASSWORD,
    port: 5432
});
client.connect();

This is all the code you need to establish a secure connection to your Sequin database. Stepping through it:

• First, you are requiring dotenv, which loads the PG_PASSWORD enviroment variable.
• Next, you are requiring pg and then creating a new Postgres client that connects to your Sequin database. To do so, you are defining the host, user, database, password (which is referencing the environment variable), and port for your database. You'll copy and paste these values right from the Sequin connect page for the resource you created earlier.
• Finally, with client.connect() you are connecting to the database.

With your database connection established, let's make sure it works by running a simple SQL query that pulls in the entire Product Inventory table. Add the following function:

let getProducts = async function () {
    let query = `SELECT * FROM product_inventory;`;
    let res = await client.query(query);
    console.log(res.rows);
};

getProducts();

This is the structure of a simple Postgres query in Node:

• First, you create an async function since the pg client will return a promise.
• Next, you define your query as a string literal using SQL.
• Then, you execute the query using await client.query(query) and set the results to the variable res.
• Finally, you log the results of the query.

Save index.js and return to your terminal. Make sure you are in your sync_inc_tutorial directory and run $ node index.js. You'll see all the records from the Product Inventory table printed in clean JSON:

And just like that, you've retrieved all your Airtable data using SQL.

In this tutorial, we don't need every field from the Product Inventory table in order to determine if a product's inventory is running low. So instead of SELECT *, define the exact data you need:

...

let query = `SELECT
    product_inventory.id as "product_id",
    product_inventory.manufacturer[1] as "manufacturer_id",
    product_inventory.product_name[1],
    (product_inventory.units_ordered[1]::integer - product_inventory.units_sold[1]::integer) as "inventory" 
    FROM product_inventory;`

...

Because you have a complete Postgres server at your disposal, you can do all sorts of powerful data manipulations right in your SQL statement. You can learn more in our Cheat sheet - but breaking this query down:

• First, you're selecting the id of the product and giving the returned column an alias of "product_id".
• On the next two lines, you are retrieving the manufacturer_id and the name of the product. These fields are stored as Postgres arrays in your Sequin database because in Airtable they are linked records and multi-select fields which can contain many values. So here, the [1] syntax is extracting the value from the array.
• Lastly, you are calculating the available inventory right in your SQL statement by subtracting the units_sold from the units_ordered. Again, both these fields are in arrays because they are Airtable lookups (hence the [1] syntax). To run the calculation you are casting these values to integers: ::integer.

When you save and run the script in your terminal (i.e. $ node index.js) you'll see you now have the exact data you need in a clean structure:

You can quickly begin to see the power of using SQL. Unlike Airtable.js or the API alone, with SQL, you can use the full power of Postgres to retrieve and format your data. Because databases and SQL are purpose-built for this task, it takes far less effort.

Writing Data

For any product that is running low on inventory, we want to automatically place a new purchase order to replenish our stock by adding a record to the Purchase Orders table.

Sequin promotes a one-way data flow: read from the Sequin database and write through the Sequin API proxy.

When we write through the Sequin proxy, Sequin will take care of request throttling AND ensure all new updates, creates, and deletes appear in Airtable and your Sequin database simultaneously.

Before we create the function to write data through the Sequin proxy, we need to do a little housekeeping by adding a helper function to our script that calculates which products need to be replenished.

In index.js make the following adjustments:

1. In the getProducts() function, replace conole.log(res.rows) statement with return res.rows;. Now this function actually returns a list of products.
2. Next, add a helper function, findProductsToOrder. This function first calls getProducts() and then returns just the product that are running low using the filter method (in this case, we're saying any product with less than 20 items in inventory is low).

Once complete, here is how your script will look:

require("dotenv").config();
const { Client } = require("pg");
const client = new Client({
  host: "evening-soiree.syncincdb.com",
  user: "rutpt6ojav7g7oh",
  database: "dbd3bt2ddssgox2",
  password: process.env.PG_PASSWORD,
  port: 5432,
});
client.connect();

let getProducts = async function () {
  let query = `SELECT
    product_inventory.id as "product_id",
    product_inventory.manufacturer[1] as "manufacturer_id", 
    product_inventory.product_name[1], 
    (product_inventory.units_ordered[1]::integer - product_inventory.units_sold[1]::integer) as "inventory" 
    FROM product_inventory;`;

  let res = await client.query(query);
  return res.rows;
};

let findProductsToOrder = async function () {
  let products = await getProducts();
  return products.filter((p) => p.inventory < 20);
};

Now that you know which products need to be replenished with a new purchase order, set up the Sequin proxy to write these purchase orders back to Airtable.

To use the Sequin Proxy, you craft HTTP requests to the Airtable API like you normally would. Except, you prepend proxy.sequin.io/ to the beginning of the hostname.

As with any Airtable API request, you'll need your Airtable API key to authenticate the request and a Base ID. Retrieve these two values from your Airtable accounts page and the API docs (just select the "Inventory Management" base and you'll see your Base ID in green.) Add these to your .env file:

PG_PASSWORD = "▒▒▒▒▒_YOUR_PASSWORD_HERE_▒▒▒▒▒"
AIRTABLE_BASE = "▒▒▒▒▒_YOUR_BASE_ID_HERE_▒▒▒▒▒"
AIRTABLE_API_KEY = "▒▒▒▒▒_YOUR_API_KEY_HERE_▒▒▒▒▒

Head back to index.js. In this tutorial, we'll use node-fetch to make HTTP requests. At the the top of index.js, declare fetch:

require('dotenv').config()
const fetch = require('node-fetch');
const { Client } = require("pg")
...

Now, create a new function, placeOrder(), that will use the Sequin proxy to write new purchase orders back to Airtable:

...

let placeOrder = async function(product) {
    let body = {
        "fields": {
                "Manufacturer": [`${product.manufacturer_id}`],
                "Product": [`${product.product_id}`],
                "Quantity": 50 - product.inventory,
                "Paid?": false,
                "Status": "Order Sent"
            }
        }

    let res = await fetch(`https://proxy.sequin.io/api.airtable.com/v0/${process.env.AIRTABLE_BASE}/Purchase%20Orders`, {
        method: "post",
        headers: {
            "Authorization": `Bearer ${process.env.AIRTABLE_API_KEY}`,
            "Content-Type": "application/json"
        },
        body: JSON.stringify(body)
    })

    console.log(res);
}

...

Stepping through this function:

• The function will take in a product object as an argument.
• First, the function defines the body of the HTTP request you'll send to the Sequin proxy. The field names and values match what you'll find in the Airtable docs.
• Next, you make the fetch request. The URL points to the Sequin proxy and the path indicates the base and table you want to write to. The method is POST since you are writing new records to the table.

Note that the request is formatted identically to a standard Airtable POST request, from the body to the headers. Only the host (proxy.sequin.io) differs.

As such, instead of using fetch, you can still use Airtable.js with the Sequin proxy. You just need to set the endpointUrl to https://proxy.sequin.io/api.airtable.com. You can learn how in the Sequin reference.

Now, add one more helper function to your script called replenishInventory. In this function you'll iterate through each product that needs to be replenished and then call the placeOrder() function to add the purchase order in Airtable (and your Sequin database simultaneously). Here is the complete state of your script:

require("dotenv").config();
const fetch = require("node-fetch");
const { Client } = require("pg");
const client = new Client({
  host: "evening-soiree.syncincdb.com",
  user: "rutpt6ojav7g7oh",
  database: "dbd3bt2ddssgox2",
  password: process.env.PG_PASSWORD,
  port: 5432,
});
client.connect();

let getProducts = async function () {
  let query = `SELECT
    product_inventory.id as "product_id",
    product_inventory.manufacturer[1] as "manufacturer_id", 
    product_inventory.product_name[1], 
    (product_inventory.units_ordered[1]::integer - product_inventory.units_sold[1]::integer) as "inventory" 
    FROM product_inventory;`;

  let res = await client.query(query);
  return res.rows;
};

let findProductsToOrder = async function () {
  let products = await getProducts();
  return products.filter((p) => p.inventory < 20);
};

let placeOrder = async function (product) {
  let body = {
    fields: {
      Manufacturer: [`${product.manufacturer_id}`],
      Product: [`${product.product_id}`],
      Quantity: 50 - product.inventory,
      "Paid?": false,
      Status: "Order Sent",
    },
  };

  let res = await fetch(
    `https://proxy.sequin.io/api.airtable.com/v0/${process.env.AIRTABLE_BASE}/Purchase%20Orders`,
    {
      method: "post",
      headers: {
        Authorization: `Bearer ${process.env.AIRTABLE_API_KEY}`,
        "Content-Type": "application/json",
      },
      body: JSON.stringify(body),
    }
  );

  console.log(res);
};

let replenishInventory = async function () {
  let products = await findProductsToOrder();
  products.forEach((product) => placeOrder(product));
};

replenishInventory();

Go back to your terminal and execute your script again. In the console you'll see each response from your fetch. And when you look at Airtable - you'll see all your new purchase orders ✨

Read after Write

You've now pulled in all the products in your Airtable base via Sequin, determined which products need to be replenished, and then used the Sequin proxy to create new purchase orders. Now, let's add one more function to show the newly created purchase orders in the console to let the user know everything is working (and show off read after writes).

Create one more function, confirmOrders(), that queries your Sequin database for new purchase orders:

...

let confirmOrders = async function() {
    await replenishInventory()

    let query = `SELECT * FROM purchase_orders WHERE purchase_orders.created_time::DATE = now()::DATE;`
    let res = await client.query(query);
    console.log(res.rows);
}

...

This function should look fairly familiar now:

• First, you await replenishInventory() which will pull in all the products, calculate which need to be replenished, and place purchase orders.
• Then, you define a new SQL query that pulls in all the details from any purchase orders that are created today. This is a crude way to see all your new purchase orders.
• Last, you log the results.

Here is your complete script:

require("dotenv").config();
const fetch = require("node-fetch");
const { Client } = require("pg");
const client = new Client({
  host: "evening-soiree.syncincdb.com",
  user: "rutpt6ojav7g7oh",
  database: "dbd3bt2ddssgox2",
  password: process.env.PG_PASSWORD,
  port: 5432,
});
client.connect();

let getProducts = async function () {
  let query = `SELECT
    product_inventory.id as "product_id",
    product_inventory.manufacturer[1] as "manufacturer_id", 
    product_inventory.product_name[1], 
    (product_inventory.units_ordered[1]::integer - product_inventory.units_sold[1]::integer) as "inventory" 
    FROM product_inventory;`;

  let res = await client.query(query);
  return res.rows;
};

let findProductsToOrder = async function () {
  let products = await getProducts();
  return products.filter((p) => p.inventory < 20);
};

let placeOrder = async function (product) {
  let body = {
    fields: {
      Manufacturer: [`${product.manufacturer_id}`],
      Product: [`${product.product_id}`],
      Quantity: 50 - product.inventory,
      "Paid?": false,
      Status: "Order Sent",
    },
  };

  let res = await fetch(
    `https://proxy.sequin.io/api.airtable.com/v0/${process.env.AIRTABLE_BASE}/Purchase%20Orders`,
    {
      method: "post",
      headers: {
        Authorization: `Bearer ${process.env.AIRTABLE_API_KEY}`,
        "Content-Type": "application/json",
      },
      body: JSON.stringify(body),
    }
  );

  console.log(res);
};

let replenishInventory = async function () {
  let products = await findProductsToOrder();
  products.forEach((product) => placeOrder(product));
};

let confirmOrders = async function () {
  await replenishInventory();
  let query = `SELECT * FROM purchase_orders WHERE purchase_orders.created_time::DATE = now()::DATE;`;
  let res = await client.query(query);
  console.log(res.rows);
};

confirmOrders();

Save your script and go into Airtable (delete the any purchase orders created in the prior step). Then run your script again. You'll now see that in Airtable the purchase orders are created - and in the console, you get a read out of all the new purchase orders. Everything is working and the inventory is healthy.

This shows the full power of the one-way data flow. As you write data through the proxy, your Sequin database is immediately updated. Any subsequent reads reflect all your changes - in real-time.

Conclusion

Sequin transforms Airtable into the proper database you always wanted it to be. Using SQL you can query all your Airtable data natively. No pagination, rate limits, or funky syntax. Plus, with full SQL you have easy tools to manipulate your data. Then, with the Sequin proxy, you again no longer have to worry about API quotas and can just work with your data.