Airtable DB: How to literally use Airtable as a Database with Node.js
Tags: javascript, node, postgres, tutorial
Published: 2021-04-29 22:29:26

Airtable can be the perfect database for [prototypes](https://dev.to/syncinc/the-ultimate-prototyping-stack-six-hacks-for-rapid-prototyping-on-airtable-e6a). 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](https://sequin.io), 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](https://airtable.com/templates/local-business/expDrHGuyjSQlrKTq/inventory-tracking) as an example data set:

![Inventory base](https://docs.sequin.io/assets/node/002_inventory_base.png)

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:

![Data flow](https://docs.sequin.io/assets/node/001_data_flow.png)

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

1. Log in to your [Airtable workspace](https://airtable.com/) and then open the [inventory tracking template](https://airtable.com/templates/local-business/expDrHGuyjSQlrKTq/inventory-tracking) in a new tab.
2. Click the **Use Template** button to add the inventory tracking template to your workspace.

![Add the template to your workspace](https://docs.sequin.io/assets/metabase/005_add_template.png)

## 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:

![Airtable to Sequin to Metabase data flow](https://docs.sequin.io/assets/metabase/002_create_account.png)

**Step 2:** Connect the Inventory Tracking base you just created to Sequin using the tutorial or check out the [Quickstart guide](https://docs.sequin.io/get-started). 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**:

![Add resource](https://docs.sequin.io/assets/metabase/006_sync_flow.gif)

**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.

![Airtable to Sequin to Metabase data flow](https://docs.sequin.io/assets/metabase/007_credentials.png)
## 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](https://nodejs.org/en/) 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`:

```bash
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](https://node-postgres.com/) 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](https://www.npmjs.com/package/dotenv) to manage environment variables.
- `node-fetch` — You'll use the [node-fetch library](https://www.npmjs.com/package/node-fetch) to make HTTP requests using the Sequin proxy.

Install these libraries and their dependencies by running the following:

```bash
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:

```bash
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:

```.env
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](https://app.sequin.io) 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:

```js
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:

```js
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:

![Run script](https://docs.sequin.io/assets/node/003_run_script.png)

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:

```js
...

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](/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](https://www.postgresql.org/docs/9.1/arrays.html) 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:

![Run script](https://docs.sequin.io/assets/node/004_run_script_again.png)

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](/reference#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:

```js
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](https://airtable.com/account) and the [API docs](https://airtable.com/api) (just select the "Inventory Management" base and you'll see your `Base ID` in green.) Add these to your `.env` file:

```.env
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`:

```js
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:

```js
...

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](/reference#one-way-data-flow).

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:

```js
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 ✨

![Results](https://docs.sequin.io/assets/node/005_new_orders.png)

## 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:

```js
...

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:

```js
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.