API from SQL database

I have searched and searched for concrete info on this and have not found what I need. My question is this:

Do I need a server API to fetch new data from a SQL database that receives data 24/7?

I am capable of making Chart.js charts from external API’s, as well as updating this chart from this same API using the js setInterval() function.

I can also easily create charts from database data. No problem with any of that, but what I cannot discertain is how to fetch new database data overtime.

It would seem to me that I must have a server API to update a chart, but I’m not certain of this, which is why I ask here.

Thanks for any comments or suggestions.

Is the data staying internal (i.e. you’re using your own data) or is it crossing between internal and external?

If it’s strictly internal, then no, an API isn’t needed. If dealing with external customers, then an API may be a good idea because it allows you to control the narrative of what data is available and how.

Thank you for responding to my query.

Background is this: I have created a microcontroller that gathers the following data from two water tanks: tank level, temperature and humidity, for example. This data is sent to an external database 24/7. I would like to create a chart that displays the water levels from both tanks.

That said, this data would not be shared with anyone other than myself. So, a public API is not what I need. I just want to way to update my chart every 5 seconds or so, 24/7.

What I am confused about is whether I need an API to update the chart or do I simply get the data from the database? All I know about API’s are the public ones which I have used. These API’s update themselves somehow, which is extremely useful.

If I could update the chart without an API that would be fine, but I would have to learn how to fetch the latest database data to do so. I’m not adverse to learning, but I would really like to know whether my approach to this problem is the correct one. Of late, I seem to be chasing my tail to solve this.

Thanks so much for your help.

There are two methods of achieving real time updates on a web page periodically without reloading the page.

1.) long polling
2.) web sockets

Long polling is a legacy alternative to web sockets. Web sockets would be the more robust, modern approach but is typically more involved than long polling implementations.

Both approaches require communicating with a back-end API. The long polling solution pulls data using a periodic http request. The web socket approach hands over control to the back-end to push data achieving a true real time, synced update.

Long polling requires just a standard http request (AJAX) to fetch data on an interval. The learning curve is low with JavaScript knowledge and an ability to build a service api, rest layer. On the other hand, the the web socket approach varies in complexity depending on the back-end technologies and infrastructure being used. Some languages/technologies are easier than others to implement web sockets.

I would also proceed with caution using a SQL database for this. A database solution that better supports big data with high read and write efficiency like Cassandra would be a more enterprise, robust, scalable solution over a relational database.

1 Like

I don’t think enterprise is what we need here. The database already exists, let’s keep that as a given. It can always be swapped out later if it needs to be. One problem at a time.

With regards to long polling and web sockets, those are good solutions if you don’t know when new information will be available, but in this case we do know; it’s being updated constantly, so every time you do a request you will obtain new information. So there is no need for long poll and/or web sockets here.

A periodic (using setInterval in JS) HTTP request to an endpoint that spits out JSON for the period that the graph displays would be a good first start I think. You can iterate from there if you like.

1 Like

Thank you so much for your in-depth response. My apologies for the slow response, but I never received notification of new posts.

I will take a hard look at all that you suggested. While I never assumed that this would be an easy task, I am surprised that I have never come across a clear explanation as to how this is accomplished. Without this knowledge, my path to solving this issue was uncertain to say the least.

I appreciate your comments and suggestions.