Airtable As A Database For Middleman [Tutorial]

Middleman is one of my favorite static site generators. The fact that Middleman lacks a database is both an asset and a downside. In this tutorial, I'll show you how to fetch data from your Airtable spreadsheet and display it on your middleman website.

Why Integrate Airtable and Middleman?

Airtable is the perfect minimum viable database for static sites. It's a souped up version of Google Sheets that helps your organize data. Airtable has a robust API that we'll leverage in this tutorial.

Dependencies & Requirements

• A middleman project
• An Airtable account
• npm
• Node.js

How To Integrate Middleman and Airtable

Step 1: Setting Up An Airtable Base

An Airtable base is "just" a spreadsheet (it's actually so much more).

Create an Airtable account here.

Next, create a new base and select start from scratch. Your fresh base should look something like this (sans the entries):

Note that in this example the name of the base is books and the table is named booktable. Note both these names.

Visit Airtable's API docs here. If you're logged in, Airtable will prompt you to select the base you want to use for the API and then tweak the documentation so that it uses your actual API key. In my case, I selected the base "books."

Once you're looking at Airtable's docs, note your API key and the spreadsheet ID (this is a string associated with your Airtable base):

var base = Airtable.base('SPREADSHEET_ID');
apiKey = 'YOUR_API_KEY'

Step 2: Javascript To Fetch Data From Airtable

Create index.js and drop it in the root folder of your middleman project.

In index.js, put the following:

  var Airtable = require('airtable');
  var jsonfile = require('jsonfile');
  var fileBook = 'data/books.json';
  var bookJson = [];
  var bookJsonTest = [];
  var base = new Airtable({
    apiKey: 'MY_API_KEY'
  }).base('MY_SPREADSHEET_ID');
    
  base('MY_TABLE_NAME').select({
    // MY_TABLE_NAME is "booktable" for this example
    maxRecords: 4,
    //sort
    sort: [{
      field: "publication_date",
      direction: "desc"
    }]
  }).eachPage(function page(records, fetchNextPage) {
    // This function (`page`) will get called for each page of records.
    records.forEach(function(record) {
      bookJson.push(record._rawJson.fields);
    });
    fetchNextPage();
  }, function done(error) {
    if (error) {
      console.log(error);
    }
    jsonfile.writeFile(fileBook, bookJson, function(err) {
      console.error(err)
    });
    console.log('Success!');
  });

Step 3: Integrating With Middleman

OK, now we are ready to initialize package.json which will let npm know what the name of your package is as well as what dependencies it uses.

$ cd middleman_project
$ npm init

npm init will prompt you to answer some questions about your project. You can just hit enter - the defaults will work.

Note that "main": "index.js" should match the filename for the javascript above that you added to your root folder.

You end up with something like:

{
  "name": "middleman_project",
  "version": "1.0.0",
  "description": "",
  "main": "index.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1
  },
  "author": "",
  "license": "ISC"
}

Now install the dependencies:

$ npm install node && npm install airtable && npm install jsonfile

Now that we've installed the node_packages, your directory structure should look something like:

|-config.rb
|-config.ru
|-Gemfile
|-Gemfile.lock
|-index.js
|-node_modules
|-package.json
|-source
|-data # this folder added in the next step

Almost done. First, we'll make the data folder. Finally, we'll run our index.js script to fetch our books spreadsheet from Airtable and copy the data into /data/books.json.

$ mkdir data # from your project root
$ node index.js # make some API calls!

To print the results, you can type:

$ python -m json.tool data/books.json

In my case, the output was:

[
   {
     "author": "David Foster Wallace",
     "publication_date": "1996-01-01",
     "title": "Infinite Jest"
   },
   {
     "author": "Orson Scott Card",
     "publication_date": "1980-01-01",
     "title": "Ender's Game"
   },
   {
     "author": "Saul Bellow",
     "publication_date": "1964-01-01",
     "title": "Herzog"
   }
 ]

This json should now be located in middleman_project/data/.

Step 4: Displaying Our JSON in Middleman

Middleman supports data files right out of the box. .json, .yml, and .yaml are all supported.

For this example, we'll use a live example - a curated directory of form design tools.

Here's some documentation on how to use data files with middleman, straight from the horse's mouth.

A Live Example

You can see this Airtable + Middleman combo in action at FormCandy. This static site is a curated directory of form design tools, but all information is stored in an Airtable spreadsheet.

Let's say my data file is located at data/list.json.

Then I could add the following loop to any template to output that json. Here's how my list.json file was structured:

{
  "entries": [{
    "class": "formvalidation",
    "title": "FormValidation.io",
    "link": "formvalidation.io",
    "categories": "form validation",
    "description": "Best jQuery plugin to validate form fields. Designed for Bootstrap 3/4, Foundation 5/6, Pure, Semantic UI, UIKit, and more."
  }]
}

Add this to your example.html.erb

<% data.list.entries.each do |f| %>
  <div class="row">
    <div class="col m2">
      <img src="//logo.clearbit.com/<%= f[" link "] %>">
    </div>
    <div class="col m10">
      <h3>
        <a href="http://www.<%= f[" link "] %>">
          <%= f["title"] %>
        </a>
      </h3>
      <p>
        <%= f["description"] %>
      </p>
      <p>
        <span class="emphasis">Categories:</span>
        <%= f["categories"] %>.
      </p>
    </div>
  </div>
<% end %>

And here's the output:

Clearbit has a useful free logo API that returns a logo if you provide the url. So this HTML:

<img src="//logo.clearbit.com/apple.com">

Generates the following: