Sync Inc

Build Node.js Apps that query Airtable using SQL

Sync Inc makes it easy to work with all your Airtable data in Node.js.

In this tutorial, we'll show you how to quickly read and write data to Airtable using the Sync Inc database and proxy in a Node.js script. Let's dive in.

Airtable Setup

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

Inventory base

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 Sync Inc to interface with Airtable to make reading and writing data easier. The data flow will look something like this:

Data flow

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.

Add the template to your workspace

Sync Inc Setup

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

Step 1: Go to https://app.syncinc.so/signup and create a Sync Inc account:

Airtable to Sync Inc to Metabase data flow

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

Add resource

Step 3: Sync Inc 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 Sync Inc database to Node.js.

Airtable to Sync Inc to Metabase data flow

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 Sync Inc Postgres database and query your data.
  • `dotenv` β€” To keep your Sync Inc 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 Sync Inc 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 Sync Inc database. Because your Sync Inc 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 Sync Inc database:

PG_PASSWORD = "β–’β–’β–’β–’β–’_YOUR_PASSWORD_HERE_β–’β–’β–’β–’β–’"

Reminder: You can retrieve the credentials for your Sync Inc database at any time by navigating back to the Sync Inc 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 Sync Inc 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 Sync Inc 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 Sync Inc 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 Sync Inc 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:

Run script

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 Sync Inc 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

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.

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

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

Before we create the function to write data through the Sync Inc 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 Sync Inc proxy to write these purchase orders back to Airtable.

To use the Sync Inc Proxy, you craft HTTP requests to the Airtable API like you normally would. Except, you prepend `proxy.syncinc.so/` 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 Sync Inc 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.syncinc.so/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 Sync Inc 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 Sync Inc 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.syncinc.so`) differs.

As such, instead of using `fetch`, you can still use Airtable.js with the Sync Inc proxy. You just need to set the `endpointUrl` to `https://proxy.syncinc.so/api.airtable.com`. You can learn how in the Sync Inc 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 Sync Inc 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.syncinc.so/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

Read after Write

You've now pulled in all the products in your Airtable base via Sync Inc, determined which products need to be replenished, and then used the Sync Inc 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 Sync Inc 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.syncinc.so/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 Sync Inc database is immediately updated. Any subsequent reads reflect all your changes - in realtime.

Conclusion

Sync Inc 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 Sync Inc proxy, you again no longer have to worry about API quotas and can just work with your data.

Metabase
PowerBI

Was this helpful?