Few years ago I worked on a project where I had to get hold of the 31,102 verses of the English Bible, and use it to develop an Android app that helps people memorize Bible verses. This app used a memorization technique called spaced repetition which is popularly used in the flash card app – Anki.
One of the main problems that I faced while developing the app was to find a way to fetch the texts of these 31,102 verses and use them in the application. The Bible data that was available was in JSON format and was properly formatted by book, chapter and verse, so no problem there. The only problem was that we did not know how to load this data from the file to the working memory of the mobile app. We tried various solutions. We first tried parsing the entire JSON on the app start and then storing it in a global variable that can be accessed from any file. This had a lot of performance issues. We then tried putting the entire JSON as plain text in the Java .class file. If you were thinking how that went, to put in one sentence: “multi-line strings before Java 12”.
Long story short, we ended up using the SQLite local database to store all the data and queried them as needed. Fast forward to now, with the knowledge of REST APIs that do the querying work on its end, creating an API would have been a better idea.
Although it is too late go back in time and fix things, we sure can create a Bible API right now. So that’s exactly what I did.
Why GraphQL?
I’ve had an interest on GraphQL lately after seeing how its been used even in real-world client projects in my company. Although REST is the standard when it comes to APIs, GraphQL has been in the market for quite some time.
Why Hasura?
I was part of an internship programmme with Hasura in my 3rd year of college. At the time, the company had just started thinking of choosing GraphQL as their niche. Since I was familiar with their tools, thought I could give it a shot.
With this background, let me tell you how I did it.
Step 1: Deploy the Hasura GraphQL engine in Heroku for Free
Deploying the Hasura GraphQL engine is straightforward. Click on Try it out in 30 seconds on Heroku on hasura.io, and follow the steps.
Step 2: Start adding the tables into the underlying Postgres database
Here is the part where I had to decide on the database schema. The database schema should not only be logical, but should also support the kind of queries that we would make once the API is ready.
So here is the schema that I decided on:
The languages table contains
- id – an auto-incrementing integer that acts as the primary key
- name – the name of the language (Eg. English, Tamil, French)
- iso_code – the ISO 639-2 code corresponding to the language (Eg. eng, tam, fre). This field is for future development purposes, where we might need to filter languages using their iso codes.
The versions table contains
- id – an auto-incrementing integer that acts as the primary key
- name – the name of the Bible version/translation (Eg. King James Version, Tyndalle Bible, Bishops Bible)
- language_id – the foreign key denoting the language the Bible version is on.
The verses table contains
- id – an auto-incrementing integer that acts as the primary key
- book_name – The name of the book the verse belongs to (Eg. Genesis, Joel, Mark)
- chapter_number – The chapter number the verse belong to in the Book (Eg. 1, 33, 119)
- verse_number – The verse number in the chapter (Eg. 1, 33, 88)
- version_id – the foreign key denoting the version of the Bible the verse belongs to
Step 3: Start inserting rows on to the tables
It is easy to insert data on to the first 2 tables, namely: languages and versions.
Adding data to the verses table is the trickiest part of the entire API creation process.
The Bible data to be added was found here: https://github.com/aruljohn/Bible-kjv
After downloading all the 66 individual JSON files, I realized that I cannot simply insert each verse into the database one by one (there are a total of 31,102 verses in the Bible). The only way to add data into the database was through the GraphQL mutation methods that were pre-generated based on our table creation in Step 2. Looking through the docs, I found that there was a way to bulk insert multiple rows of same type of data.
So I wrote a script that reads all these 66 JSON files and creates payloads that could be copy-pasted in the mutation method.
var fs = require('fs');
var bookNames = ["Genesis", "Exodus", "Leviticus", "Numbers", "Deuteronomy", "Joshua", "Judges", "Ruth", "1Samuel", "2Samuel", "1Kings", "2Kings", "1Chronicles", "2Chronicles", "Ezra", "Nehemiah", "Esther", "Job", "Psalms", "Proverbs", "Ecclesiastes", "SongofSolomon", "Isaiah", "Jeremiah", "Lamentations", "Ezekiel", "Daniel", "Hosea", "Joel", "Amos", "Obadiah", "Jonah", "Micah", "Nahum", "Habakkuk", "Zephaniah", "Haggai", "Zechariah", "Malachi", "Matthew", "Mark", "Luke", "John", "Acts", "Romans", "1Corinthians", "2Corinthians", "Galatians", "Ephesians", "Philippians", "Colossians", "1Thessalonians", "2Thessalonians", "1Timothy", "2Timothy", "Titus", "Philemon", "Hebrews", "James", "1Peter", "2Peter", "1John", "2John", "3John", "Jude", "Revelation"]
for (var book=1; book < bookNames.length; book++) {
console.log('Index:' + book);
let bible = {};
let finalJson = [];
let data = fs.readFileSync(`${bookNames[book]}.json`);
console.log(`Read ${bookNames[book]}`);
bible = JSON.parse(data);
for (var chapter=0; chapter < bible.chapters.length; chapter++) {
for (var verse=0; verse < bible.chapters[chapter].verses.length; verse++) {
var json = { book_name: bible.book, chapter_number: chapter+1, verse_number: verse+1, text: bible.chapters[chapter].verses[verse].text, version_id: 1 };
finalJson.push(json);
}
}
for (var i=0; i<finalJson.length; i++) {
var stringifiedJson = JSON.stringify(finalJson[i])
.replace('"book_name"', 'book_name')
.replace('"chapter_number"', 'chapter_number')
.replace('"verse_number"', 'verse_number')
.replace('"text"', 'text')
.replace('"version_id"', 'version_id')
.replace("}", "},");
fs.appendFileSync('formatted.json', stringifiedJson, function (err) {
if (err) throw err;
console.log('Saved!');
});
}
console.log(`Completed ${bible.book}`);
}
This created the payloads for all the 31,102 verses in a new file called formatted.json. And yeah, since GraphQL is not pure JSON, it wasn’t exactly a valid json file.
And we are not done yet. I cannot simple put all these payloads directly onto the GraphiQL console. I splitted them to three batches of around 10,000 payloads each and put them in the mutation method.
Step 4: There is no more steps. The GraphQL API is ready.
This is one of the coolest parts of the API creation process. The entire schema and all the resolvers are auto-generated when you create the tables, so you don’t have to write them on your own. There are of course still some steps that you need to go through to actually secure your endpoint and restrict API access.
Here is an example query for starters:
If you’d like to go through the GraphQL Bible API yourself, feel free to ping me or put your email in the comment box below. I will send you the password to the console.
Until next time!
Joel Kingsley 