What do you do if you have a site you want to update once in a while? It’s not a blog, so you don’t want WordPress. It’s also not worth building a customized backend. In developer-speak, we would think about a remote data hosting to store the data for semi-dynamic websites or applications.
So we made a free open source tool APITable for that, which converts tables to JSON API.
Take a look at the below Notice app — you will want to centralize your information somewhere in cloud storage so that you can update and synchronize to all client apps (be it websites / mobile apps).
The default solution might be hosting on your own server with Django or Node.js to serve as an endpoint for providing an API. But doesn’t that sound a bit overkill?
Imagine a WordPress for applications
It’d be nice if there is something similar to WordPress that can be used as a CMS (Content Management System) for your application — something with a simple GUI for editing and the backend is updated for you.
Why can’t it be simple and user-friendly?
Rather than setting up a dedicated server for an API endpoint for some relatively static data, there are some easier choices:
- Hosting a static JSON file at S3;
- Using a mock API service to mock an endpoint
However, it requires extra development effort to update data from the endpoint.
Hence when we think about the question:
“What should the next generation of APIs be like?”
Why can’t the API data be just as intuitive as we view it? Our solution was an APITable which does one thing: Table → JSON API
There are several reasons we believe APITable can do the right job as a simple API endpoint:
No need to learn about databases
Database knowledge is basic for developers, but it might not be that obvious to those who want to update the data. The table view encapsulates complex operations such defining schema and writing raw queries. The data editor can manage useful information directly instead of dealing with the technical part of the API endpoint.
Instant API
No deployment needed. One you’ve updated the table, the data will be available to your API clients instantly. You can also add / drop columns directly from the spreadsheet-like editor.
No SDK required
JSON API is developer friendly. Whether your front-end is a website, iOS or Android mobile app — you can find a neat parser to convert the API returned data to the data type you need.
You can also directly curl the API directly. You don’t need to stick to any SDK for this simple API service.
Edit data directly in tables
We love data in a tabular form for these reasons: You have a quick overview in a glance; what you edit is what you get — you do not need to navigate into sub-layers to edit the detail of a product, just do it directly in the table.
Challenges
The spreadsheet library is powered by the Hansontable open source project, we can achieve rows and columns editing easily. Besides building the application UI, there are more technical challenges synchronizing the data worth sharing.
We have used Skygear.io as our application backend, which serves as the cloud database of our tables.
Save and load records in batches
Firstly, loading a big table from the cloud in a single query may induce a slow loading speed (we used the Hong Kong bus routes and fares dataset in testing, which has 1000+ rows of records). We also considered that API users might not need all data at the same time. Hence we introduced Pagination in both the spreadsheet and API endpoint.
There is also a performance problem when multiple rows are saved. Consider the case of copying and pasting data into the spreadsheet, which triggers a multi-row update. If we save row by row, it will take many requests to save all the record updates. Hence upon saving, there is a trick behind the scene:
- Distinguish between whether a row is updated or deleted;
- Each added row needs to be updated to a queue, and those to be deleted into another queue;
- Save the update and delete queue in a batch.
After we packed the records together, only 2 requests are needed.
Simple authorization
You might not want your API to be exposed to everyone. We’ve also designed for people who needs authentication for the API — the more simple and effective way is to require a valid token when accessing the endpoint.
To manage who can access data from your table, you can create and revoke token at the APITable dashboard.
Who’s APITable made for?
It as originally designed for exposing APIs for simple mobile applications — but here are the other use cases we can think of:
- Indie developers: fast prototyping for apps to demonstrate the ability of remote fetching for data;
- Startups: building MVPs or simple newsfeed applications;
- QA testers: storing data fixture or dummy data for app testing purpose.
APITable is a free, open-source project available on GitHub we made to help indie developers and even those who just need an handy API endpoint. Let us know what you think!
