home mzrnsh logo

Using Airtable as a Jekyll website database

Can you use Airtable as the database for a Jekyll website? Yes, but it may not work as you expect:

At the end of the day, all Jekyll does is generate a bunch of static HTML files. Anything non-static happens at build step. So yes, you can access your Airtable data, but not in real time. Each time you update your Airtable base, the website will need to be rebuilt to reflect those changes.

Okay, but why use a dynamic database for a static site? Reasons may vary. For most projects, you probably won’t need this but sometimes it makes sense. For example, things might become cumbersome if your website grows too data-heavy for a static site, or if you need to collaborate with non-developers. This is where Airtable comes in: instead of manually updating lengthy data files, you can work and collaborate on your data in Airtable.

Since this method makes use of the Jekyll-native _data folder approach, most existing Jekyll projects should be able rely on it. If you stick with the attribute names present in your _data files and reuse them in Airtable you shouldn’t even need to touch any of your template files.

I love when tutorials go with real-world examples instead of FooBar or to-do list projects, so for this guide we’ll create a real website called UpToDate, which will keep us up to date with the latest releases of the libraries, frameworks or programming languages we care about.

Okay, Let’s go!

1. Create a new Jekyll project

If you are new to Jekyll or need a refresher, check out my in-depth guide on starting a new Jekyll site. Otherwise, these quick steps should suffice.

Initiate a new Jekyll project from you terminal:

jekyll new uptodate

CD to your project and bundle the gems:

cd uptodate
bundle

Let’s now update the default config. Open the _config.yml file and change its content to your liking. For UpToDate, it will look like this:

# Update default values
title: UpToDate
email: keep-me@up-to.date
description: >-
  Keep track of the latest releases of your favorite
  libraries, frameworks and programming languages
baseurl: ''
url: https://up-to.date

# Put my own twitter and github usernames
twitter_username: mzrnsh
github_username:  mzrnsh

# Keep the default theme and plugins
theme: minima
plugins:
  - jekyll-feed

Let’s confirm there are no issues so far:

bundle exec jekyll serve

Go to http://localhost:4000. If you see something like this, you’re ready for the next step:

Jekyll ready

2. Display some data the native way

Before we throw in Airtable, let’s add some data the native way and display it on a page to make sure it all worked before we started breaking things.

Add _data folder to the project’s root, and create a YAML file in it. In our case, it will be _data/things.yml file as we will be tracking the version numbers of all sorts of things. Let’s add a couple entries to it:

- name: Ruby on Rails
  version: 7.0.2.3

- name: Tailwind CSS
  version: 3.0.24

- name: Font Awesome
  version: 6.1.0

Next let’s display this data on the homepage. Open the index.markdown file and change its content to something like this:

---
layout: home
---

{% for thing in site.data.things %}
  - {{ thing.name }}: {{ thing.version }}
{% endfor %}

Since I am using the default Minima theme, the homepage comes with a blog section. Let’s get rid of it. The proper way to do this is overriding the default layout, or using a new layout but that is beyond the scope of this tutorial. Instead, let’s just delete the demo blog post file from the _posts directory. With no posts left, the theme will hide the blog section and the homepage will look nice and clean:

UpToDate - clean homepage

3. Create an Airtable base

With the initial version of UpToDate looking and working as intended, we can now start working on our Airtable integration.

First we need an Airtable base that houses the same data as we are displaying on our homepage. Head over to Airtable dashboard and create a new base. Here’s what it looks like for UpToDate:

UpToDate Airtable base

Mind the capitalization of the table and column names. We will need to refer some of those values as strings in later steps.

I created this base manually as I have only 3 entries. If you have a lot more data, Airtable has various import tools to make things easier for you.

4. Add Airtable API credentials

To speak with the Airtable API, we will need to use an API key and the base ID. Ideally, we don’t want to make them a part of the source code. Instead, we want to use environment variables. Let’s use the jekyll-dotenv gem for this:

echo "gem 'jekyll-dotenv'" >> Gemfile
bundle

If using git, make sure to include .env file in your .gitignore:

echo ".env" >> .gitignore

Now create the .env file in the root directory with the following variables:

AIRTABLE_API_KEY='YOUR_API_KEY'
AIRTABLE_BASE='YOUR_AIRTABLE_BASE_ID'
AIRTABLE_TABLE='YOUR_AIRTABLE_TABLE_NAME'

Replace the sample values YOUR_API_KEY, YOUR_AIRTABLE_BASE_ID, and YOUR_AIRTABLE_TABLE_NAME with the actual values from your Airtable.

You can generate the API key on your Airtable account page.

And you can grab the Airtable base ID from the URL in your browser address bar. Open the desired base and copy the part of the URL right after airtable.com/:

Airtable base ID

As for Airtable table name, it’s the name you gave to your table. For UpToDate it is “things” string:

Airtable table name

Important: Airtable API keys are unscoped, meaning they have the same permissions as your user account, across all the bases you have access to. If this concerns you, the ‘official’ way around it is to create a new account with limited permissions and generate an API key from that account.

5. Create a custom Jekyll plugin for Airtable

First let’s install the airtable and activesupport gems that we will need for our plugin:

echo "gem 'airtable'" >> Gemfile
echo "gem 'activesupport'" >> Gemfile
bundle

Next, we will be casually creating a custom Jekyll plugin! If you’ve already done this, you know there’s nothing scary about it. If not, see for yourself:

Create _plugins directory in the project root and a file named airtable.rb in it. That’s it, technically speaking, we already have a custom plugin. Now let’s make it do something.

Open the freshly created _plugins/airtable.rb file in the editor and paste the following:

require 'dotenv/load'
require 'airtable'
require 'active_support/all'

airtable = Airtable::Client.new(ENV['AIRTABLE_API_KEY'])
table = airtable.table(ENV['AIRTABLE_BASE'], ENV['AIRTABLE_TABLE'])

File.open("_data/#{ENV['AIRTABLE_TABLE']}.yml", 'w') do |file|
  data = table.records.map(&:attributes)
  warning = "# Do not edit this file manually \n"

  file.write(warning, data.to_yaml)
end

Let’s read the code above line-by-line to make sure everything is clear:

Okay, let’s now confirm our plugin works. Start the Jekyll server, or if it’s already running, restart it:

bundle exec jekyll serve

Did the command run without errors? Nice!

Now open your data file and check if it was updated. From something like this:

- name: Ruby on Rails
  version: 7.0.2.3

- name: Tailwind CSS
  version: 3.0.24

- name: Font Awesome
  version: 6.1.0

It should have changed to something like this:

# Do not edit this file manually
---
- !ruby/hash:ActiveSupport::HashWithIndifferentAccess
  version: 3.0.24
  name: Tailwind CSS
  id: rec41IcxHOE3f9KU1
- !ruby/hash:ActiveSupport::HashWithIndifferentAccess
  version: 7.0.2.3
  name: Ruby on Rails
  id: recXh3RJKAfcsM4Kx
- !ruby/hash:ActiveSupport::HashWithIndifferentAccess
  version: 6.1.0
  name: Font Awesome
  id: recpx0BkIPC8VWkD9

Yes? Great! 🎉 Your Jekyll site is now connected to Airtable each time you build or serve it, the fresh data will be fetched from Airtable.

6. Automate rebuilds [coming soon]

Most guides of this kind end here, and honestly, that often leaves me with a bad taste in my mouth. See, this guide is not complete. It says nothing on how to automate website builds whenever the data is updated. Yet you would need something like that in almost all scenarios I can think of. So I will update this article once I have that figured out. If you’d like to get notified, subscribe to my newsletter below.


Newsletter, sort of

I write occasionally about being a freelancer, being a founder and my tech stack: Ruby on Rails, Shopify, Jekyll.

Expect ~5 articles per year

Powered by weightless.so