Easily creating a backend for static sites with Airtable

Airtable is great. You get a table in the cloud, very easy to setup, and with many integrations.

I have been playing with one of its features, a RESTful api created from your base — Airtable calls bases tables. Airtable automatically generates example calls and CRUD commands.

Here we can see one of the examples autogenerated.

As soon as I tried this, I thought it was very powerful .

1. Can we use Airtables RESTful api as a backend, with only frontend? If this is viable, we could develop our site in React — for instance — and directly call any apis in Airtable. This would be a very quick way to build prototypes and MVPs.

2. Can we use rest api to add behaviour to a static site generator? Like jekyll

The mission

To test this I created a Subscribe-to-newsletter box that uses Airtable as a back-end.

• It will use only html, javascript and css (scss compiled to css)
• We are adding it to a Jekyll blog - this one, where this article was written. The same code should work in any other SSG -hugo, ghost, etc. - or platform - plain website, Wordpress. Anywhere that can serve html.

These are the results we are looking for

We will jump between the backend and the frontend to end up with the component above. Let’s start with the backend. It will be Airtable.

Subscribe to newsletter: the backend

Creating a new base

We can create a new table — or a base, as Airtable calls it — to store our beloved email subscribers. I called it email_subscribers

A new base, or table, for our data

Adding columns and its types

We will add the minimum information for an email subscriber

• email: Obviously we need to store this
• Created: A type of column that Airtable populates for us with the time the record was created
• Notes: This field is not strictly necessary, but we might want it to store extra information. In this case I commented on the fact that the first email is me, and not a subscriber.

The basic information we need for an email subscriber

Next, we generate a rest api from the base email_subscribers we just created. We go to https://airtable.com/api select the base

just select an existing base to generate an api.

And here is our api:

The autogenerated api docs

As you can see in the generated api, we will need to use

How to get the base id

• base id , included in the api already.
• api key

How to get the base id

• We need to replace the values in airtable_base = 'YOUR_BASE_ID' with our base’s id. You can find it in the url of the generatd rest api, or in any of the example calls:

https://airtable.com/YOUR_BASE_ID/api/docs#curl/table:email_subscribers:create

How to get our api key

You can get the api key in https://airtable.com/account

Clicking on the asterisks shows the key

Subscribe to newsletter: the frontend

a simple — but insecure — implementation

Html and javascript in susbcribe_to_newsletter.html

We can include it in any page or post.
{% include subscribe_to_newsletter.html %}
In jekyll, one line to include in our posts or pages

Or If you prefer you can get the plain css version

Why this is a bad idea

Some resources stop here. Job done! But this is a terrible idea in terms of security. Ok, so there is some risk of fingerprinting, or some obscure self-XSS vulnerability — you may think — Wrong! The security error is pretty much full access to anybody walking by.

The base id and airtable api key should both be secret, since anybody with access to them has the same access as we do and can modify, delete anything we can. The problem: sharing our base id and api key gives full access to anybody with them.

Anybody that can view source can hack us.

This dialog is the only thing between our data and our foes

How to solve it

How to solve it , then?

Airtable Roles and permissions

Either read only or modify, delete. Checking Airtable permissions we see that we can use the following roles:

• Owner/Creator
• Editor
• Commenter
• Read-only

Unfortunately, this basically means we can have an api key for a read-only or for total access to the base's data.

There have been suggestions to improve the granularity of permissions in Airtable, and I am sure that there will be eventual improvements there.

If you know of an alternative way to have more granular permissions — with different keys with access only to add records, for instance — feel free to contact me

Permissions for base access are often not granular enough for direct api usage

Since we either have a read-only api key - not useful to modify or append - or an (almost) access-to-everything one, we cannot share these api keys. Using them in our frontdoor code in javascript would allow anybody can get access to the "keys of the kingdom": our data.

The solution

We need something that limits the actions from airtable Airtable uses the following methods:

• GET Lists information in the table. We do not want to enable this or anybody could list the users that have subscribed to our newsletter.
• POST Creates new records. This is the only one we want to enable for our mission today.
• PATCH This is A PATCH request will only update the fields you specify, leaving the rest as they were.
• PUT This is used to update, deleting anything we are not updating. From the Airtable docs:

A PUT request will perform a destructive update and clear all unspecified cell values.

Usually this is not what we want, PATCH is recommended instead.

There are several Http method, we will allow only POST This way record listing, modification, deletion and table operations become impossible

We need to

• limit the accepted http verbs/methods to only POST
• Hide the name of base and api key

Proxying the requests

Here are the steps to proxy the requests:

• We are going to send the requests to a server -not to airtable -.
• That server will query Airtable in the following way:
• Only the POST method will be allowed - thus, only appending records.
• base id and api key will be secret, since the server's source code is not public.
• We will use these values to make a request to Airtable’s api and return to the user.

Option 1: Serverless proxyiing

(Or similar) Here one option is to contract a new Serverless service, like Cloudfare workers, lambda, or similar. This service can spin up a new function/server/endpoint on every request and redirect it to airtable.

It could be done, for let’s say an estimated 5$/month — since it is a service, it costs money. It is a very valid option that we might explore, but for this newsletter subscription widget, I have decided it is not worth the hassle. I will put it on an existing server instead.

If you are interested in this let me know and I will look into updating the article with this option

Option 2: own server, with django

Since I have already some servers I can use one of them to take part of the proxying to Airtable. A lot of my projects use Django , so we will use that. The solution will be very similar in other Python frameworks — flask, bottle — and other programming languages and frameworks — Go, rails. Anything that runs on the server-side really.

First we create an endpoint to accept requests. In this case it is '^/api/newsletter/$'. This means, if our server is at https://example.com, this will point at https://example.com/api/newsletter/ That is the url our javascript will need to call with the email.

The url we will accept and the view it will use

Next, we create the view that we chose above

• We need to replace the values in airtable_base = 'YOUR_BASE_ID' with our base’s id. You can find it in the url of the generatd rest api, or in any of the example calls:

https://airtable.com/YOUR_BASE_ID/api/docs#curl/table:email_subscribers:create

• We will also need to set the name of our base (table) here. We chose email_subscribers for ours, as we saw before.

# the name of your table
airtable_table = 'email_subscribers'

• Finally, we replace the api key for our secret api key:

headers = {
"Authorization": 'Bearer YOUR_API_KEY',
"Content-Type": 'application/json'
}

You can get the api key in https://airtable.com/account

Clicking on the asterisks shows the key

Please remember, anybody with this key can take full control of your tables and data in Airtable. Do not put it in your client’s javascript!

• If the server is running in a different domain, we need to add the csrf_exempt annotation of the view’s method. For example, your site is hosted at https://mejuto.co and the server serving the django requests is not under this domain. In my case, it is at https://findthepodcast.com, so you will see my requests will point to https://findthepodcast.com/api/mejutoco/newsletter/

Modified frontend (secure)

Now, that we have a backend proxying our requests to Airtable, we only need to modify or client code to call that.

Demo

Here is the final result

Network tab

We can use our browsers Network Tab in the developer tools (F12 or right-click inspect) to verify that we are not leaking any of:

• Name of base id
• secret api key

Checking the url

We can confirm that the url (highlighted in the screenshot) contains no base id or api key.

The url that we call

Checking the request parameters

Also, the only parameter sent is the email of the subscriber (highlighted in the screenshot below)

We only send the email to add it to our subscribers

None of the client code has access to our secret information. Confirmed!

Cherry on top: email notification

Once we use Airtable we have a lot of Automations available.

A useful one is to enable email notifying us when we get a new subscriber (when a new record gets added to our base).

Instead of checking Airtable’s base once a week, we can get an instant mood boost as soon as someone subscribes.

On our base view, we can open the Automations panel and add one to send an email. As a trigger we select When a record is created As a Table

When a user subscribes, we will receive an email

Other uses

We can use the same approach to build any dynamic behaviour like:

• A poll to our audience
• Any kind of form asking for input (Submitting images, documents, text)
• Managing attendance, supplies, calendars.
• As a readonly api to show data managed in Airtable.

Although, we did not show it, we can allow only the GET http method to get readonly access. This can allow us to manage content in Airtable, while using a frontend technology - like React, for instance - to query these endpoints and display the information.

For example, a restaurants website could be developed in React, and perform GET requests to our proxy, while the menu is managed on Airtable and can be changed every day, without code.

Limitations

We have created a quick newsletter signup to our static site generator using Airtable. Anybody can append more records to the table, but they cannot modify existing records, read them or delete this one or other tables.

Thanks for reading

Subscribe to the newsletter Here

Originally published at https://mejuto.co on January 1, 2021.