How a non-coder created an API (and some questions)

APIs fascinate me. That you can ping an unknown system and get it to share its treasure-trove with you, for you to play around with, is something that I find utterly fascinating. The first APIs that I ever used were the Twitter APIs and the IBM Watson ones; I totally loved (love?) them.

The other thing that I am fascinated by is Indology. However, APIs and Indology do not meet. While there are plenty of digital resources, freely available, on ancient India, none of these resources make available the data (treasure?) held in them in a manner that can be consumed by apps and programs.

I had the books, I had the data. But I had no idea about how to make APIs (I'd only ever used them to make bots and apps). So, I hunted around for an easy solution and found sheetlabs.com. Thus was born the vedic APIs.

That's the non-coder way to go about making APIs, I guess. In this post, I'd like to ask you coders a couple of questions (and also to shout out about my APIs because I am so thrilled about them :) ).

Question 1

Are nested arrays and lists a good idea, or no?
My APIs are designed to return JSON data like this, for example:

[
    {
        "mandal": 1,
        "sukta": 2,
        "sungby": "poet_name_1",
        "sungfor": "god_name_1",
        "meter": "meter_name_1"
    },
    {
        "mandal": 1,
        "sukta": 2,
        "sungby": "poet_name_2",
        "sungfor": "god_name_1",
        "meter": "meter_name_1"
    },
    {
        "mandal": 1,
        "sukta": 2,
        "sungby": "poet_name_2",
        "sungfor": "god_name_2",
        "meter": "meter_name_1"
    },
    {
        "mandal": 1,
        "sukta": 2,
        "sungby": "poet_name_2",
        "sungfor": "god_name_3",
        "meter": "meter_name_2"
    }
]

Is it better if they're like this? And, why so?

[
    {
        "mandal": 1,
        "sukta": 2,
        "sungby": ["poet_name_1", "poet_name2"],
        "sungfor": ["god_name_1", "god_name2", "god_name_3"],
        "meter": ["meter_name_1", "meter_name_2", "meter_name_3", "meter_name_4"]
    }
]

Question 2

What could be a low-cost setup to host my own database server that'll have the data tables, and then making them available through web calls? One option that I had thought about was getting a Raspberry Pi to host a PostgreSQL server, and throwing the database open for API calls. Advisable? Other options?

Question 3

What are the steps to convert a database table into an API? Dumb question?

I have tons of data on the Mahabharat that I want to turn into APIs, but the spreadsheets will go beyond the free limit of sheetlabs.com. Plus, some of the columns in one sheet could be linked to columns in another sheet. And I am looking for a more permanent solution anyway, so...thoughts?

Comments

[Mat]

I wonder if there is another non-coder way of doing it that would be less restrictive than sheetlabs. Spreadsheets can be easily saved as CSV, so if you find a tool that converts CSVs to JSON APIs that would do the job.

But I guess the 'coder way' is to create a web application, instead of using an existing service to provide your API. You wouldn't normally expose the actual database to the outside world.

One way of doing that is something like django rest framework. It might be a steep learning curve though because it's made for web developers and it builds on top of another python library (django). Roughly what you'd have to do would be:

• Create URL patterns for your rv and vs endpoints. This connects the URLs users enter with the code that handles their requests
• Create python classes (models) that describe the tables in your database (this turns a database row into a python object)
• Write a serializer for each one that turns that python object into a JSON object
• Create a viewset for each one, that has a list method, like the first example here: django-rest-framework.org/api-guid...

If you use this, then you get a lot of stuff for free out of the box. For example, it will generate some API documentation for you, the same way sheetlabs does.

Alternatively you could build something with flask, which is a simpler web framework. Here's an example of how you could build an API in it: blog.miguelgrinberg.com/post/desig...

In this case you could use any python database library to connect to your DB, then you would write some code to transform the database data into a list of dictionaries, which you can pass to jsonify to get the JSON output.

For hosting, maybe have a look at heroku when you have something that works on your computer. I think their free tier support databases with up to 10,000 rows in. I'd recommend against trying to host it yourself on a raspberry pi because it'll take more effort to keep the site running and you run the risk of people hacking you.

[Anindita Basu]

Thank you for your thoughtful reply, Mat. I have tried (and abandoned) the django route as too complicated for me, but looks like Flask is doable. I'll try it out.

[Mark Schmale]

For question 1 I guess this is to some extent a matter of opinion. I personally would pick the second option using the nested structure because the information is more compact and the relations are more visible.

Question 2: You could have a look at google firebase which seems to offer a lot of DB data for free. AFAIK you can even use google docs sheets via a JSON API. If you want a hosted solution something like a cheap VM from someone like Digital Ocean might be a good start.

Question 3: For a start, thinks like json-server (node.js) might be an option. You could use an online service or a little script to convert your spreadsheet via CSV to JSON and let JSON-server just do its thing. It provides a REST API on simple JSON files.

If you want to do more and really need a DB to back your API I'm currently not aware of any good out of the box no-code solution which turns your DB into a REST-API.

Hope that helpes…

[Anindita Basu]

Thank you Mark. I'll look at Google firebase and node.js.