Airtable as a database for frontend technologies

Video screencast: step by step

See transcript

Transcription

so uh we want to use a table as a backend

so to do that let’s go to my blog and here at the very end we see this

element that we’re gonna try to implement

so this is a subscription box where people can enter

their email so for instance like this and then they subscribe and it says

thank you and right now what this has done is to

send this data to error table so this data would be accessible and we

would use error table as a backend so let’s see how to do that

the first step is to go to airtable.com and create a new base

base is how our table calls tables so let’s say

start from scratch and then we’re gonna call it like

email source descriptions we can change like the color of it like

if we want let’s get the default settings

and then here we can add a column so there’s some by default

name nodes attachment status so these two

attachments and status we won’t need so we just delete it

and then we’re gonna need a field to store the emails

so let’s call it email and then one more field with the date that the record was

created so for that the table has already won here

created time uh including time and we’re gonna choose

here 24 hour format or even just 12 hours

if you prefer we created uh we’re also gonna leave this notes

filled because it might be useful here to add

like something next to that user like if it’s

a test user or if we are i don’t know there’s any

other information that we want to to attach to the user

the data is ready so now we want to generate the api

to do that we come to airtable.com api and here we see the options

for a rest api basically we want to select one of the

bases the one we just created is this one email subscriptions

and that’s it our table generated this api docs

so here we can see like example call requests to get

a listing of the information we can also see

examples on how to create new records and it’s all with a very

nice documentation example responses the responses are

based on on real data we have on the table so

it’s pretty self-explanatory now we move on to the client part

for this i prepare an example here um let me first say that this example is

not secure but i will explain all the process so

this will be the first obvious solution to use a table from the

front end as you can see here there’s a strange

comment by saying if using jekyll and then wraps

html into one protag so don’t intend html

so i’m gonna use jekyll to integrate this but this is just html

javascript and css so really you can use any system you

you would like this strange comment here refers to the fact that we would

probably have the html like this it looks a lot better but uh

jekyll interprets this indentation here as code so it will render it as

something similar to a pre uh tag

pretag so something like this it could be render something like this

because we don’t want that we can we can use it like this but before

saving it we will leave it it was so that jackie

is happy with it the client code so what do we have in

here let’s see um apart from the comment we have

some inclusion of jquery as a library some html here

and a script tag for javascript it actually

should be like this so let’s go step by step to see what this

this does here we can see that the script tag is

including a local copy of jquery here there’s a template there is a jkill

specific this is liquid tag syntax but the important thing is that this is

a jquery version that is sitting in this folder

if we would not like that because maybe it’s a static website where we don’t

have control of some of the assets we can simply look for a

cd and jquery like a content delivery network that serves jquery

for instance here we can see several i just pick up the latest

with a slim minifight and copy it and then we can just add it like this so

this would work exactly the same then we have the html so here we can see

different html tags it’s quite simple so apart from title and subtitle

explaining why people should sign up

it’s also divided in between a top and bottom sections

there’s an input uh to include the email with a placeholder all very standard

html some small disclaimer and the only thing

maybe interesting here would be that we use

this class before and after to distinguish be between

what we show before the user has signed up and what we show after

as we see in this after section we have a hidden class

which is hiding all this block we will see later

with the css how we are doing this but the

html is quite simple so we have the html the jquery included and later we’ll see

also the jquery but we still need to find a way to say

how we want this to display so this is done with css as you know

and in this file there’s no css why because on jekyll usually the

css is included in this other file this dot

s css and here we just we’re using like a css grid to style

everything as as we want it’s kind of a slightly long

file but there’s not much mystery to it so

basically you could use either this scss available on my website

which is it embeds with this indentation it’s a bit shorter or if you prefer you

can also have the compiled version that is

this one it’s css for real so it doesn’t need a compile step

even if you would want to keep this isolated you could even

add here a style tag and just oops and just

i’m getting there and just paste it here between this

style and the closing style so that would work

too uh it looks a bit ugly but it would make

it like independent of any other code that

you have when you include this this snippet

so because i’m using jekyll i prefer to keep it in the styles file objective

users so i remove this and i know that it’s in

these other styles as css but feel free to to do

whatever works for you and we get to in my opinion the most

interesting part of this file which is this script tag with some javascript in

it so let’s comment it like line by line so

you get an idea of what this is doing first of all it’s using jquery a lot of

people prefer never to use jquery i think in

this case it’s like a quick way to get some functionality but it could be

easily translated in a way that it could not

use jquery for me i find it more convenient for small snippets like this

so i’m just including it the first line

here we see that it’s like a document ready

this is when basically on an unload not an unload but when the dom

is ready let’s run this piece of code i included a few console.logs to the

back to see if it’s actually triggering but

it’s of course not necessary then what we’re doing here is looking

for something with a class subscribe to newsletter

which is going to be the top class of our component

we can see in the html that is the class we use

so of course nothing else should be should have this class

and we’re looking for the button inside this class so that would be

the subscribe button here okay so this selector is saying when there’s a click

run all of this inside so basically we want when the user clicks subscribe

we initiate a request right so how do we do this

first we have here a variable a constant actually holding the the container

so this will point to this div here with subscriber newsletter then we

have another selector that we keep in this constant

which is the value of this input here here there’s another the back statement

that i can remove and going on we are checking that the

email contains an add sign so it’s a very simple

checking but at least it stops some some like worthless records

from being recorded and here’s the ajax request so what

we’re doing here is actually talking directly to

to our table first we’re defining this data

constant the the fields we’re sending our table expects things to be sent in

this fields object so we’re sending the email field which

will match the column that we have in our table and then

there’s this type cast through that just helps

airtable not return a specific error so if there’s some errors when

submitting the the request sometimes having this

typecast true helps then here there’s a standard hx request

from from jquery we’re using

the url with our table name so in this case email subscribers

then here you would have to use your real base id

so as we will see this is one of the problems of this approach

there is a post data and then here we’re transforming the previous

constant data into a way that we can send it in

proposed finally there’s some headers with authorization and content type and

in authorization here where it says my api key

you should also remove it with your private api key

and this is one of the problems that the key could be then

shared as we will see later so i don’t recommend this approach

but just for illustration purposes we’re doing it

finally here this like the handler what happens when the

when the function is successful when the request is successful

so we execute this function here there’s another back that i can remove

and then we’re just doing very simple we’re basically

hiding the previous block so this before block what we were showing before

signing up and we are removing the hidden class

so we are showing this thank you for subscribing

so why is this such a bad idea why am i saying all the time that this is

insecure as we just saw here we are putting the

base id and our private api key in the source

code of the client so this javascript is

loaded on the browser of the person visiting our website

this could be one of the easiest ways of using

error table and it is so tempting i actually found it very

interesting because we can just use the generated api to directly

call anything so we could use this url with a post or any of the other methods

directly from the client and that’s really practical but

as we can see if we go to uh one of the usages

which is on my website again here we see the table but if we do

view page source here we will see our source code as it is

here the source code is modified but if we would just include this

you could just see there my private api key

my base id all things that explicitly our table tells us not to share with

anybody in the documentation it is true that there is ways in your

table to limit the permissions but as we will see later they are not

very granular so in general we have a red only permission and a permission

that can do anything we can do so anybody with this

key will be able to check all your data in our table to modify

tables probably delete them create new ones so something that we

have to be very careful about we will see how to improve on this

so how do we improve it well the first thing is

instead of calling airtable directly which as we’ve seen

list the api keys and the base id we’re going to call

something on a server and that server is going to

act as a proxy and query airtable for us in this way only that server needs to

know our api key but not the client code so we’ll start

by modifying what we had until now to make it a bit safer

most of the code we can keep as it is because the html is exactly the same

the javascript is still on load the name of all the

selectors it’s all the same but we have to change some things

so the first thing we will change is this url will need to point to to

another server to an endpoint so the way we can do this is

first of all we need to have like a running server or we can test it locally

with a locally running server so let’s say if we have a locally

running server you can use localhost like this

and then select a path in this case i’m going to use this path because

the name of my of my blog to know that this newsletter endpoint

is related to my blog so here you can do it in many ways

basically if you’re familiar with django with any other frameworks and with

back-end you just need to provide an endpoint in

in some domain you own in some app that you have

running and then when you test it you can run

you can test it here with localhost and the port where your

server is running for instance something like this and

once it is deployed to production this would be a website like for

instance let’s say here could be like this

website or in my case in this website i have

a jackie static uh generated jkill site

so i will just put this endpoint in a different domain that i own

here you should put your own domain so for now i will leave it like this

and we will debug it later uh other things that we have to take

into account is these parameters uh here in the data section and before

we’re using these fields and this type cast

because error table was expecting them like this but now we don’t need to do

this anymore so we can just the information that is

important to us which in this case is the email of the subscriber

so you just pass the email like this everything else stays the same the email

is in the same input so nothing changes there

and the only thing is that we don’t need this stringify

because the data is not so it will work already like this

then an additional thing is that these headers that we remember were a big

problem because we’re sharing our api key

these headers will now be sent from the back end so what we’re going to do is to

remove them here and later we will add them to the button

so for now we hide this address and with this we have client code

that is not sharing anymore the base id or our private api key let’s see how

how to continue so first of all we’re going to include this file that we

created which i called subscribe to newsletter

insecure.html and we’re going to include it

for another version that is just called subscribe to newsletter

i remove the indentation for jekyll it’s not needed if you use some other system

and i have my code here and we need to include this

so there might be many ways for some of you it may be just copy pasting this

into some other template it depends on which framework you use

in my case what i do is i have to go to the

jekyll article that you see here and then i just include here the

subscribe to newsletter i include other files as well other

images and this is just one line that i can reuse in every post

to get always at the end of the post this subscribe to my newsletter

now to make it a bit more specific uh let’s just try to run this on a local

server just so that we see how this works

i’m gonna have a server running locally so i can comment this line

and let’s see which port but basically here i run this custom command

doesn’t matter this is running a jackie and we can see here

that is running here on the port 4000

so what i do is i change this with a 4 000

and then i’m gonna be able to check that page

okay so here’s the page is a local page and at the very end we can see here the

article we open the developer tools and then we should see

here when we post what what we are posting

so let’s say i post this email i subscribe and then of

course this is an error you can go to the network tab and then

we should be able to see everything that was sent so this is the

url that we’re calling and the email that was being passed

correctly what we’re gonna do now is actually

implement this part in the back end so that this doesn’t

error out that actually responds to the request and then this is

locked successfully this back end will proxy the request to

our table so this record will end up in the table

that we created before in our table

the next step is to configure the url for this

i’m using jungle but whichever framework or system you use

will have a way to add a new endpoint so here you can see for

instance i have several endpoints here from other applications like this

one belongs to a calculator app and the only one that we can focus

now on is this first one so i just added here a new url

with find the podcast api and how to seo newsletter

this should match the route that we put before

so in this case this was the tesla so the real url so this one so this is

the url that we are having in the back end

for this as we can see here we reference this url

and we as we said then we reference a view that doesn’t exist yet

that we will create i called it airtableproxy

and then i passed several methods to the view these are optional

but i find it interesting to use like a bit easier

when it’s defined straight in the urls so here i define the method that it will

accept so only post no listing of emails only

adding new emails no update no patch uh no put

only post and the uh table base here replace the base id uh

with with this placeholder but here you would

have to place your your base id and then i give the error

table table so base the email subscribers

that’s the name of our base and just a name for django to know which url is

this so let’s move on and see how to create

this this view we’re gonna have the the class that is

gonna manage our url so in the previous video

we added this url and now we want to to create this class this airtable proxy

class so i’m using django but in in whatever

other framework you use this is going to be similar so you can copy most of the

ideas it will just be a different syntax a different programming language

but for now let’s implement it in so i’m using the the class view

django allows you to use like functions as a view or or classes

so to use the class-based view i need to create a class like this

with a method in this case it’s going to be post

okay something like this here i write pass

because there will be and we’ll fill it up

okay so here’s the method okay now we need to add um

basically like a cross size request for the jerry

accent to this because it will be in a different domain so we

we have right now the website on one domain on

hooter.com and then we have this on find the podcast

if it would be on the same one it could not be necessary

but because the whole idea is to have a static site on one website and then deal

with the dynamic requests in another it might be needed so for us

it’s definitely needed so i just need to add this crm xm

cr csrf exam with these options this patch

so this intercepts all of them and then here i can also add this

okay so we have right now this um if you remember let’s close some

files here we have our url here and we’re passing

this param parameters right so we need to create also these

parameters in the in the class so let’s go ahead

let’s forget about the post for now but at least these ones

we will need so let’s go ahead and add them here

so basically here we will have our base id

here we have the name of the table then we are still missing something we are

missing the headers that we will need to do the request

we’re gonna do the request in the body of this function and here we

need the headers to send in this request so in this case

it’s gonna be like an authorization header authorization

so if you remember this is the same the same code that we had before in the

front end we have these authorization headers now

it’s the server that we’ll be sending them and because of

that because it’s the server nobody will be able to see this so they

should be protected from the from um yeah from people that want to

steal them let’s say so here this is the application

application json okay then this is the data we’re going

to need to the request some of them we’re passing from the url

we can configure this we could here pass

nothing and then just do everything from the other place or we could have like

one single of these classes that works for more than one method and pass

here like the tables maybe one you could even pass an array of them so for now i

said it like this so we get an overview from the urls

file and from the views file we also see like some default values and some

use use cases then let’s go on to the actual request

so the request we’re gonna use the request

library in python it’s very easy to use has a very nice

interface so that’s why we’re choosing this one but

you can use any other one so first let’s come up with a url and here

the ul is the one we had before in the front end so that was

kpi or table.com p0 then the name of our base

slash name of our data then we apply some format here to fill

in those placeholders and that’s going to be like so and here

we are referencing these variables right so base and table

to that i’m going to use cell and

and done okay so this will be the proper url

then we can add a test here i mean if we want we could filter here

let the the methods that are allowed so for

instance for now i am using this which is like the actual

method so for instance if people call this url i only want it to work to work

if they call it using post because i don’t want to redirect to

every table anything else than posts so to do that i’m going to use this

thing that we added before our table method post so i say

if this equals their table method then we continue now okay

so what do we need then we have an email parameter that we’re sending

so let’s get that email parameter here i see the default value if the

email parameter is not set and then i say if the email

is different than the empty string then we can do the the request to error table

so let’s write it down if we remember our table wanted

this format with the fields object and then inside the field object

it had the email and then to avoid some common errors in

your table it was easier to just add typecast value so this is the data we’re

gonna send and we still didn’t do the request so let’s do that now

using the request library we wanna do a post

with the url the data passing as json depending on

the method we can pass json data the slide differences in how it is

encoded so for this case we want this one

json equals data and we also need to pass the headers because they do the

authentication okay and that is the the request now we only

need to deal with the response the response is json so let’s

manipulation response here inside angle and then we just

return like something i mean doesn’t matter as long

as it goes through but let’s say we’re for this

just uh okay and if he didn’t get into into this if so this will only be

reached if we didn’t return then we can return

i don’t know so with this we should have the url

configured pointing to this class view

and the class view working so this would finish the

the back end so let’s see how the backend works

we put the code into production and now let’s test how it works

so first we go to the website where this box is

and we’re gonna see the requests

okay here in the network tab then at the same time we wanna see

what happens on the air table side so we’ll go to our table.com

we select our our base and then here we can see the current records it’s just

the test that i did and nothing else let’s close this for

now okay so now when we

enter a test we see there was a post

it pointed to the right url and we should check here in request

we can see that it did send the email we entered it’s also thanking you for

subscribing and then when we go to our table we

should see exactly here it is so it has added this record here

so it seems to be working as intended now this is working already but let’s

see as a cherry on top what other things

airtable allows us to do so airtable has this concept of

automations here i have a disabled automation but

we’re going to create it from scratch so one of the most famous automations we

can do one of the easiest also is the

this one an email automation so what is this basically

we we choose a trigger that will start the automation

and a result or an action of this automation

in this case as you can see we can see here when our record matches

some conditions so we could choose when somebody signs up with a dot edu address

send a different email maybe we can i mean anything that happens inside a

table can be a trigger so basically we add here when a record

is created so when somebody signs up to our newsletter

we need to select the table we select our table

at any point we can test it but i just said this is done so when a record is

created is the trigger what is the action

the action will be to send an email so let’s just select here airtable send

email then here you could put your email you

can put this one for instance um then new sign up

newsletter that’s it and then here we could add

yeah probably the record itself so the email for instance

so here we have just a simple email here we can use markdown with a more

complicated email but basically just an email saying who

signed up we could also add the email on the

on the subject maybe that’s a good idea so here we would have it

yep then this automation is off let’s turn it on

exactly so here it is turned on and then let’s test it let’s test the

automation so for this we go again to

our website where this is implemented you can see in the developer

tools the request and this time i’m gonna send from

like goodbye at example.com and then i’m gonna send it so this sends

the post thanks us the request is here

with the right email now let’s see the airtable site

it added it as we expected and now let’s see if we receive it

received the result of this automation which should be an

email and it took a while but here it arrived

so basically we can see that the subject matches the rule we set

you sign up to newsletter you sent out the newsletter and the email and the

body also matches what we wrote in the body

i hope you enjoyed it and this allows you to play with the different

possibilities of using airtable as a backend


Airtable is great. You get a table in the cloud1, 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 CRUD2 commands.

Here we can see one of the examples autogenerated.

# Example request

# the real base id has been replaced by YOUR_BASE_ID here
curl "https://api.airtable.com/v0/YOUR_BASE_ID/email_subscribers\
  ?maxRecords=3&view=Grid%20view" \
  -H "Authorization: Bearer YOUR_API_KEY"

Example response

{
    "records": [
        {
            "id": "recEMja01ECRf0HVr",
            "fields": {
                "email": "j@mejuto.co",
                "Notes": "myself\n",
                "Created": "2021-01-14T15:08:32.000Z"
            },
            "createdTime": "2021-01-14T15:08:32.000Z"
        },
        {
            "id": "recmpTXG8tJRFZqxJ",
            "fields": {
                "email": "hello@example.com",
                "Created": "2021-02-01T14:16:38.000Z"
            },
            "createdTime": "2021-02-01T14:16:38.000Z"
        }
    ],
    "offset": "recmpTXG8tJRFZqxJ"
}


	

Generated rest api with curl commands, endpoints, and responses

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

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

  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 5hugo, ghost, etc. — or platform — plain website, Wordpress. Anywhere that can serve html.

A newsletter subscription box implemented with Airtable and javascript

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

Creating a new base in Airtable

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.

Adding columns to a base in Airtable

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

Choosind a base in Airtable

Just select an existing base to generate an api.

And here is our api:

Automatically generated rest api in Airtable

The autogenerated api docs

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

  • 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

How to get Airtable's api key

Clicking on the asterisks shows the key

Subscribe to newsletter: the frontend

a simple — but insecure — implementation

<!--
    if using jekyll, indents wrap html into pre tag
    so dont indent html
-->
<script type="text/javascript" src="/assets/js/jquery-3.5.0.min.js"></script>

<div class="subscribe-to-newsletter">
<div class="top">
<h3 class="title">Receive new articles in your inbox</h3>
<h4 class="subtitle">Never more than once a week</h4>
</div>
<div class="bottom">
<div class="before">
<div class="input">
<input type="email" placeholder="name@domain.com" value=""/>
</div>
<div class="button">
<button>Subscribe</button>
</div>
<div class="disclaimer">
<small>I will not share your email with third-parties</small>
</div>
</div>
<div class="after hidden">
<div class="input-cont">
<p class="input">Thank you for subscribing!</p>
</div>
</div>
</div>
</div>

<script type="text/javascript">
    $(document).ready(function () {
        console.log("airtable");
        $('.subscribe-to-newsletter button').bind('click', function () {
            const cont = $(this).closest('.subscribe-to-newsletter');
            const emailVal = $(cont).find('.input input').val();
            console.log("click", emailVal);

            if (emailVal.indexOf('@') !== -1) {
                const data = {fields: {email: emailVal}, typecast: true};
                $.ajax({
                    // email subscribers is the table's name
                    url: 'https://api.airtable.com/v0/BASE_ID/email_subscribers',
                    type: 'post',
                    data: JSON.stringify(data),
                    // api key
                    headers: {
                        "Authorization": 'Bearer MY_API_KEY',
                        "Content-Type": 'application/json'
                    },
                    dataType: 'json',
                    success: function (data) {
                        console.info(data);
                        $(cont).find('.before').addClass('hidden');
                        $(cont).find('.after').removeClass('hidden');
                    }
                });
            }
        });
    });
</script>
	

Html and javascript in subscribe_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
.subscribe-to-newsletter {
  width: 100%;
  height: 283px;
  background: #F5CDB4;
  padding: 25px;

  display: grid;
  /*@formatter:off*/
  grid: 'top'
        'bottom';
  /*@formatter:on*/
  grid-template-rows: 1fr;
  grid-template-columns: 1fr;
  grid-gap: 0;

  h3, h4 {
    padding: 0;
    margin: 0;
  }

  .top {
    grid-area: top;

    display: grid;
    /*@formatter:off*/
    grid: 'title .'
          'subtitle .';
    /*@formatter:on*/
    grid-template-rows: minmax(min-content, 124px);
    grid-template-columns: minmax(min-content, 488px);

    .title {
      grid-area: title;
      font-family: Roboto;
      font-style: normal;
      font-weight: bold;
      font-size: 52px;
      line-height: 61px;

      color: #74A089;
    }

    .subtitle {
      grid-area: subtitle;
      font-family: Open Sans;
      font-style: normal;
      font-weight: normal;
      font-size: 14px;
      line-height: 19px;

      /* identical to box height */

      color: #15454E;
    }
  }

  .bottom {
    grid-area: bottom;

    .before, .after {
      display: grid;
      /*@formatter:off*/
      grid: '. input subscribe'
          '. disclaimer disclaimer';
      /*@formatter:on*/
      grid-template-rows: minmax(min-content, 45px) 1fr;
      grid-template-columns: 1fr 236px 140px;
    }

    .before {
      .input {
        grid-area: input;

        input {
          font-family: Open Sans;
          font-style: normal;
          font-weight: normal;
          font-size: 16px;
          line-height: 22px;

          color: #425466;
          padding: 0.8rem;
          background-color: #FFFFFF;
          border: 1px solid #425466;
          box-sizing: border-box;
          border-radius: 5px;
        }
      }

      .button {
        grid-area: subscribe;

        button {
          margin-left: 14px;
          background-color: #15454E;
          mix-blend-mode: normal;
          border-radius: 15px;
          padding: 0.8rem;
          font-family: Open Sans;
          font-style: normal;
          font-weight: bold;
          font-size: 18px;
          line-height: 25px;
          text-align: center;

          color: #FFFFFF;
          // animation on hover
          -webkit-transition: background-color 500ms ease;
          -ms-transition: background-color 500ms ease;
          transition: background-color 500ms ease;
        }

        button:hover {
          background-color: #74A089;
        }
      }

      .disclaimer {
        grid-area: disclaimer;

        small {
          font-family: Open Sans;
          font-style: normal;
          font-weight: normal;
          font-size: 14px;
          line-height: 19px;

          /* identical to box height */

          color: #425466;
        }
      }
    }

    .after {
      .input-cont {
        grid-area: input;

        .input {
          font-family: Open Sans;
          font-style: normal;
          font-weight: bold;
          font-size: 26px;
          line-height: 35px;

          color: #15454E;
        }
      }
    }
  }

  .before.hidden,
  .after.hidden {
    display: none;
  }
}

	

Styling in file style.scss

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.

View source modal dialog

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

How to solve it

How to solve it 6, 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 data7.

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

Overview of Airtable permissions

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.

  • HEAD, OPTIONS, TRACE, CONNECT These are other http verbs that are not used by the rest api generated by Airtable.

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 8, 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.

# urls.py
# ...
url(r'^/api/newsletter/$',
    # replace yourapp with the name of your app
    yourapp.AirtableProxy.as_view(airtable_method='POST',
                                  airtable_base='YOUR_BASE_ID',
                                  airtable_table='email_subscribers'),
    name='airtable_proxy'),
# ...

	

The url we will accept and the view it will use

Next, we create the view that we chose above

# views.py
from django.views.decorators.csrf import csrf_exempt
from django.http import JsonResponse
import requests
from django.utils.decorators import method_decorator
from django.views import View

@method_decorator(csrf_exempt, name='dispatch')
class AirtableProxy(View):
    airtable_base = 'YOUR_BASE_ID'
    # the name of your table
    airtable_table = 'email_subscribers'
    headers = {
        "Authorization": 'Bearer YOUR_API_KEY',
        "Content-Type": 'application/json'
    }

    @csrf_exempt
    def post(self, request):
        url = 'https://api.airtable.com/v0/{base}/{table}'.format(
            base=self.airtable_base,
            table=self.airtable_table,
        )

        if request.method == self.airtable_method:
            email = request.POST.get('email', '')
            if email != '':
                data = {'fields': {'email': email}, 'typecast': True}
                r = requests.post(url, json=data, headers=self.headers)
                return JsonResponse({'ok': 1})
        return JsonResponse({'ko': 1})
	

View class in your view file views.py

  • 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

Getting the api key in Airtable

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.

<!--
    if using jekyll, indents wrap html into pre tag
    so dont indent html
-->
<script type="text/javascript" src="/assets/js/jquery-3.5.0.min.js"></script>

<div class="subscribe-to-newsletter">
<div class="top">
<h3 class="title">Receive new articles in your inbox</h3>
<h4 class="subtitle">Never more than once a week</h4>
</div>
<div class="bottom">
<div class="before">
<div class="input">
<input type="email" placeholder="name@domain.com" value=""/>
</div>
<div class="button">
<button>Subscribe</button>
</div>
<div class="disclaimer">
<small>I will not share your email with third-parties</small>
</div>
</div>
<div class="after hidden">
<div class="input-cont">
<p class="input">Thank you for subscribing!</p>
</div>
</div>
</div>
</div>

<script type="text/javascript">
    $(document).ready(function () {
        $('.subscribe-to-newsletter button').bind('click', function () {
            const cont = $(this).closest('.subscribe-to-newsletter');
            const emailVal = $(cont).find('.input input').val();
            console.log("click", emailVal);

            if (emailVal.indexOf('@') !== -1) {
                //const data = {fields: {email: emailVal}, typecast: true};
                const data = {email: emailVal};
                $.ajax({
                    url: 'https://findthepodcast.com/api/mejutoco/newsletter/',
                    //url: 'http://127.0.0.1:8000/findthepodcast/api/mejutoco/newsletter/',
                    type: 'post',
                    //data: JSON.stringify(data),
                    data: data,
                    headers: {},
                    dataType: 'json',
                    success: function (data) {
                        $(cont).find('.before').addClass('hidden');
                        $(cont).find('.after').removeClass('hidden');
                    },
                    error: function (data) {
                        // TODO: it should not error if it is ok
                        $(cont).find('.before').addClass('hidden');
                        $(cont).find('.after').removeClass('hidden');
                    }
                });
            }
        });
    });
</script>
	

Html and javascript in subscribe_to_newsletter.html

Demo

A newsletter susbcription box on jekyll

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.

Url in network tab in developer tools

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)

Request parameters in network tab in developer tools

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 9

Airtable email automation. Send an email when a new record is added

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

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


  1. Of course, it is in Airtable’s servers, but as far as we are concerned it is stored somewhere we do not have to care about. 

  2. Create Read Update Delete 

  3. Other ways to generate RESTful api’s — sometimes Graphql too — are Hasura and Postgrest 

  4. Minimum Viable Product, one of the many Enterpreneur’s obsessions. 

  5. Static Site Generator: a program that takes certain content — usually Markdown — and outputs static html. The html can then be easily served. Pros: scalability, simplicity. Cons: dynamic behaviour is limited to javascript or needs third-party services (another server). 

  6. How to solve it. Great book on problem solving, by George Pólya (still a maths book; not a page turner) Always open to good books recommendations. 

  7. My personal impression is it looks like Airtable is focused more on offering a no-code tool for collaborators — as we see from the roles Owner, Editor, Commenter — more than for the use case I have in mind for this article, which is to build a programmatic api to use from the frontend. Maybe through the views functionality, something similar could be added. 

  8. As you probably know, Django is one of the main Python frameworks It is named after the great Jazz guitarrist Django Reinhardt. 

  9. Table or base, please make up your mind. With love. 

  10. This use case is covered by Airtable. On a paid plan we can have several roles and a role can be read-only, which is exactly this. For more granularity, a proxy would still be necessary. See more at Roles and Permissions