Next post Previous post

Airtable

November 24, 2018 at 4:37 PM by Dr. Drang

I first heard of the Airtable database about a week ago from this Twitter thread involving Christina Warren, Ed Cormany, T.J. Luoma, and Rosemary Orchard. Already it’s my primary way of accessing my main projects database from work.

Airtable is a cloud-hosted database1 that can be accessed through a web interface, an iOS app, or programmatically through an API. I haven’t used the web interface much, but the iOS app is very well designed and the API is a wonder of clarity.

The database I moved into Airtable is a table of projects at work that I’ve been using in one form or another for over 20 years. It’s basically a list of jobs, storing the following information for each job:

For many years this was just a plain text file, one record per line and the fields separated by pipe (|) characters. A small set of Perl scripts were used to add and extract information. Then I imported it into a SQLite database and rewrote the scripts in Python using the sqlite3 library.

As I found myself wanting to search the database on my phone and iPad, I moved the database file to iCloud Drive and wrote some scripts in Pythonista to do the searching. This worked, but it was a little clumsy and the results, while correct, were ugly. I didn’t really want to put in the effort to make the output prettier, but I was resigning myself to get down to it when Airtable appeared in my Twitter feed.

It took basically no time to export the data from SQLite and import it into Airtable. Now I have a nice looking view of the project information (well, it’s nice looking when I haven’t scrambled the fields for anonymity).

Airtable database on iPhone

It’s easy to search by tapping the magnifying glass, but I often find the information I want (who’s the client on Project X? what project did I just open for Client Y?) sitting right in front of me when I open the app. The other fields can be seen by scrolling to the right.

When I first imported the data, all the fields were treated as either text or numbers. Airtable has a variety of other field types that can specialize these two generic types:

Airtable field types

Within the Number type, you can choose between integer and floating point, which can be quite handy.

To reduce the clutter, I have a filter that shows only the latest projects.

Airtable filter

And I sort them by number in inverse order because the projects I’m most likely to be looking for are the most recent.

Airtable sort

The API is easy to understand and has possibly the best documentation I’ve ever seen. The docs are formed with examples that are specific to your database; they include your fields and your records, not some made-up example that means nothing to you.

Airtable API example

Airtable doesn’t provide a Python library, but it’s easy to use the API through the Requests and JSON libraries:

python:
 1:  # Add record to Airtable database.
 2:  airURL = 'https://api.airtable.com/v0/ABC987654321/projects'
 3:  airHeaders = {
 4:    'Authorization': 'Bearer 12345ABCDE',
 5:    'Content-Type': 'application/json'}
 6:  payload = {'fields': {
 7:    'name': 'New Project,
 8:    'number': 9999,
 9:    'client': 'Jane Q. Client',
10:    'clientref': 'JQC1234',
11:    'directory': 'new.project',
12:    'box': 0,
13:    'email': 'jane@company.com'}}
14:  r = requests.post(airURL, headers=airHeaders, data=json.dumps(payload))

The identification code in the URL is specific to the database, and the authorization code that goes in the header is specific to the user. The JSON library’s dumps function takes a Python structure—in this case a nested dictionary—and turns it into a JSON string for POSTing. The response is stored in the r variable, from which we can extract the JSON data that tells us whether the request was a success.

For the small data sets I typically work with, the free version of Airtable, which limits you to 1200 records per database, is sufficient. I’m a little leery of using free services for job-critical data—I don’t want something I rely no to end up with the dreaded sunsetting post on Medium—but Airtable seems to be well funded for now. Also, I’m still keeping and updating the old SQLite database for safety.

With my first Airtable database under my belt, I’m eager to add more. Airtable markets itself for teams (because everything markets itself for teams nowadays), but don’t let that stop you from using it for personal databases. I have a few inventories for both work and home that could really use the structure of a database behind them.

  1. They say it’s “part spreadsheet, part database,” but aren’t all databases part spreadsheet (and all spreadsheets part database)? ↩

Next post Previous post