Offline storage in UI5 using WebSQL
Introduction
The very first thing I need to say about this is that I know that WebSQL is an outdated/ deprecated technology and is NOT my first choice for offline storage.
In fact if you haven't decided on which technology to use for offline storage in UI5 and you're reading this blog I suggest you turn around and go find a blog on using IndexedDB.
Still here? Great because you're here simply because you've been forced to use WebSQL after your likely many protests and perhaps against your better judgement. I would still suggest that you aim to move away from WebSQL if at all possible but I also understand that those coming from an ABAP background probably feel very much at home here.
So wasting no further time on that rant let's move on to getting started with WebSQL in UI5.
The scenario
You need to access the data of your application while you're offline, actually maybe you want to do it while you're online but want to make use of local storage because your online calls are taking too long. Whatever your reasoning you need to grab data and you want to do it from the browser because of one, both or more of the above reasons.
Application setup
As always we're going to start from a blank application with nothing really setup in it, I make use of ExpressUI5 (disclaimer I made this) to quickly create my new UI5 application.
WebSQL is actually native to browsers (well some browsers, did I mention it's deprecated?) so unlike quite a few of our other posts we're not going to make use of any external libraries. Having said that this post is going to simply outline the use of WebSQL in our UI5 application and if I get enough interest for a part 2 I will go over some more useful helpers/ files that I've created similar to those seen in my promise series. (part1, part2, and part3)
Creating our localdb
Creating a localDB is relatively easy, and we can do so in our function as defined below:
That's it! that's how easy it is to create our local DB but also open that DB if we give the same parameters. I believe that certain browsers (such as Safari) will ask for permission to create this localDB but others such as chrome do not appear to ask but that is likely due to the relatively small size.
So perfect, we've actually got a local DB and we can check that our local DB is there by opening up chrome dev tools, clicking the application tab and inspecting inside of the 'web sql' section as shown below:
See how I've got a local DB called 'nathan' as defined in my code? We've just created out first local DB using WebSQL although there are currently no tables/ any useful information but it's there.
Interacting with our local DB
So now that we've got our LocalDB we can interact with it, all of our interactions be they create, insert, read, delete etc statements is that they are all passed through the same function of transaction and then inside of those transactions we execute our SQL queries.
So an example of that would be as below:
and then we can confirm that our blog table exists by again looking inside of our chrome dev tools, look at application, web sql, nathan and then we should see a table called BLOG as below:
Inserting into our local DB
To insert into our localDB we would execute some more SQL statements so in this case lets do just that:
So with the above we can see that we've straight away moved into using dynamic variables, where the second argument is an array which will then match it's self up to the question marks as seen in the SQL query. This isn't matched by name but rather by position in the array, but I kept the same name for ease of use.
Again we can go and check out our insert in chrome dev tools and we see the following:
Reading from our local DB
Very similar to the previous calls we're going to make a new function and in that function we're going to get back the contents of our BLOG table. This part is slightly more complicated as we get back our rows, but we'll discuss the output after we have a look at our call:
So our interaction with the local DB now has 3 parameters, first our SQL query, 2nd our parameters (but in this case we're not using any) and lastly is our callback.
In this callback we've simple console.logged the response from our local DB which actually looks like this:
So we actually get some information from our response as well as an array of the rows of data that we wanted, so to access that we'd simply access the rows in res.rows and have access to all of the data inserted so far.
From this I hope you have an understanding of how easy it can be to use websql within UI5, I never said that it wasn't easy but it's still deprecated.
I won't bother to show off a DELETE statement because at this point we're simply repeating the same sections but with slightly different SQL statements.
Taking this further
This has been a very basic introduction to WebSQL and how to use it in UI5, I've not really touched on anything that's very specific in UI5 here because it's actually not really very specific to UI5.
If this blog gets any attention at all I will make a part 2, where I'll show off my some of my more typical helper files that I create to make our projects cleaner, easier to use and re-usable across multiple projects and allow for a nice and easy solution.
So if you want a part 2 to this series reach our to me in the comments or message me on twitter and I'll definitely look to consider it but for now I don't want to spend too much time on a deprecated tech if no one wants it.
I will probably look to make a future series that makes use of indexedDB in UI5 and maybe some other offline storage technologies just to try them all out.
···
