my previous article, we went through how easy it was to call a Twitter API, searching for a string and storing that tweet in a SQLite database stored on the IBM i using Node.js.

Before we get around to analysing the records we have stored over the last month or so, we need a method of retreiving this information, so in this article I am going to use Node.js to create an API to retreive the tweets we stored in a SQLite database.

This api will only include three endpoints.  They are:

• Get all tweets – Uses HTTP GET method
• Get an individual tweet – passing the Tweet ID to the API – Uses GET method
• Delete a tweet (Not really necessary for this exercise, but I’ve included just to show how to add an API endpoint with a different HTTP method.) – Uses Delete method.

I will be using the Node Express framework to provide functionality for this API.  See my previous PowerWire articles, and videos, if you need a refresh on this excellent framework.

Coding the API

Using the Node Express framework, we add a new route to the /routes folder.  We will call it API.JS

Here we can see the modules the API will use.

1. Use the debug module for explanation purposes
2. Use the SQLite module as middleware to access our SQLite database
3. Point to our database
4. Bit of error handling, just in case any users go to the root of the API

Adding Tweets Endpoint

The first endpoint we will code, is the endpoint to get all the tweets we have stored in our SQLite database. 

This endpoint will be called tweets and will be accessed by the URI of https://your_IBMi:3003/tweets

1. Define the endpoint
2. A quick debug to show we are in this function
3. Execute the SQL statement
4. Error handling
5. Show the resultset to the console
6. Return to caller the success message and all rows found

Adding Tweet Endpoint

This endpoint will get an individual tweet.  You must pass the tweet ID to the API.

1. Specify SQL statement using substitution variables to stop any SQL injection
2. Pick up the parameter passed to the endpoint
3. Another quick debug
4. Execute the SQL statement using the tweet ID as a parameter
5. Error handling
6. Return the requested tweet

Adding Delete Endpoint

This endpoint will delete a specific tweet.

1. The delete a tweet endpoint
2. Pick up the ID of the tweet passed to the endpoint
3. Execute the SQL statement, again using parameter markers
4. Check for errors, if any, send a HTTP status of 400 (bad request) and the error
5. If successful, return the message deleted and a count of how many rows were deleted

Make API reachable

Next, we need to make this route reachable, so we add the API router details of the api.js to the app.js file. 

This can be seen in the figure below.

Testing

So, what do we use to test this API?

For the getting all tweets and the individual tweet, I could use a browser to perform a HTTP get to test those two endpoints, but as the delete a tweet method uses the DELETE HTTP method, I will be using a great utility called Postman. 

We use this for all our API testing. It can be downloaded from https://www.postman.com/downloads/

Let us now use Postman to checkout our three end points.

Firstly, let me test the get all tweets endpoint.

Fire up Postman and enter the URI of https://your IBMi:3003/tweets and hit the Send button.

That endpoint sends back the message ‘success’ and a data variable containing all the tweets in an array.

That is all working fine, now the individual tweet endpoint.

Very similar to the first test, except we pass in the id of the tweet we need, and the endpoint is called tweet.

https://your_IBMi:3003/api/tweet/1371391008198385664

Oh, that is a surprise, one of my tweets 😊

And finally, let me use Postman to check if the delete endpoint is working. In Postman, this time we change the HTTP method dropdown to Delete and the URI to https://your_IBMi:3003/api/tweet/1371391008198385664 

Press the Send button

The result set returned shows a message of deleted with a change count of one, meaning one record was deleted by the API.

Conclusion

We can see from this article; how easy it is to separate your data handling from your application. 

This API can now be used by any application that requires access to the tweets we have stored, so RPG, PHP etc can all access our SQLite database.  The way forward!

My next article will show how easily SQLite can be used to analyse the information we have stored from Twitter by using this API.

If you have any questions, either on this article, or anything else open source, use the comments below, or send me a message on twitter @AndyYouens

Andy Youens is an IBM i consultant/instructor at Milton Keynes, UK-based FormaServe Systems with over 40 years IBM midrange experience. 

IBM Champion 2021