Use SQLite To Save Data In A Telerik NativeScript App

Not too long ago I wrote a tutorial regarding saving data in a NativeScript mobile application using the application settings module that closely resembled that of HTML5 local storage.  If you’re not familiar with the application settings module, it is persisted storage using key value pairs.  What if you wanted a storage option that was a bit more query friendly?  Like other hybrid app platforms, NativeScript supports SQLite for persisted data as well.

Both iOS and Android supports SQLite and since Telerik NativeScript can interface directly with native APIs, it becomes possible to use SQLite.  We don’t need to write all the interface logic by hand because there happens to be a nice plugin available to make our life easier.

We’re going to see how to make use of SQLite in an Android and iOS NativeScript application using the available SQLite plugin.

Let’s start by creating a fresh NativeScript application.  Using the Command Prompt (Windows) or Terminal (Mac and Linux), execute the following commands:

Something important to note in the above commands.  If you’re not using a Mac, you cannot add and build for the iOS platform.

With the project created and platforms added, it is time to include the SQLite plugin in our project.  We are going to make use of the NativeScript SQLite plugin by Nathanael Anderson.

With the project as our current working directory for our Command Prompt or Terminal, execute the following command:

At this time we can begin the development of our application.  To keep things simple the application is not going to do much beyond basic queries.  It is to keep things easy to understand.

We’re going to restrict development to the app/main-page.jsapp/main-view-model.js, and app/main-page.xml files.  The XML file will be our UI, and the JavaScript files will be our data model and logic files.

Let’s start by working with our data model which will be responsible for directly interfacing with the database.  Open app/main-view-model.js and replace all the code with the following:

Let’s break this file down.

This file contains our two-way data bindings between our front end form and the SQLite database.  The form only has two fields being firstname and lastname which are both string values.  Our UI will also have two buttons, insert and select, both of which also appear in our view model.

The database that will be used is passed in from the page controller, otherwise known as app/main-page.js.  The insert function will take the first name and last name entered and insert it into the database table people.  The select function will select all rows from the people table and print them to the console.

Now let’s take a look at the page controller.  Open app/main-page.js and replace all code with the following:

When the screen loads a database found at my.db will be opened.  Once the database has been opened, a people table will be created, if it doesn’t already exist.  If created successfully, the view model we created previously will be initialized.

Finally we can take a look at the UI file found at app/main-page.xml.  Open this file and replace everything with the following code:

This UI file has three sets of fields stacked vertically.  The two text fields are bound to the firstname and lastname observable in the view model.  The button set is horizontally stacked and also bound to the same observable.

At this point the application should be ready to try.  When you click insert, the data in the text fields will be saved.  When you click select, the data will be presented in the console logs.

Conclusion

We just saw an alternative method to storing data in a Telerik NativeScript application that was not key-value storage through the application settings module.  SQLite is a lot easier for querying and is overall a better solution to use than the alternative.

The documentation for the SQLite plugin has a lot of other very useful features and is worth a read.

A video version of this article can be seen below.

Nic Raboy

Nic Raboy is an advocate of modern web and mobile development technologies. He has experience in Java, JavaScript, Golang and a variety of frameworks such as Angular, NativeScript, and Apache Cordova. Nic writes about his development experiences related to making web and mobile development easier to understand.

  • Nixon Perinchery

    When i have the database to be altered in the new update of the app, how do i handle it, would i need to manually stamp a version to the DB and accordingly fire the alter commands to add columns to the database ? Also how do i make sure once done i dont keep checking for it again and again? like in native android app development that can be easily handled to be taken care on the update of the app

    • Per the official documentation you can set the version number for your database:

      https://github.com/NathanaelA/nativescript-sqlite#dbversion

      So essentially you will create your database and schema and assign a version. When you need to make schema changes you would increase the version number, kind of like a tag. When it comes to upgrading users who are using older database versions, just do checks on a per version basis and launch a particular upgrade script if they fall into that category.

      Best,

  • Carver

    Where is the database created once data is entered into it? I don’t see it in the project files.

    • SQLite databases are created in the protected partition of the device. They cannot be accessed directly unless your device is rooted or jailbroken and even then it is not super straightforward.

      Best,

  • Yuen-Wei Chia

    Hi, what should I do if I am facing redundant calls between the main-view-model.js with the list.js when I am trying to update ListView Items with the SQLQuery? P/S: I am working on iOS version.

    • I’m not sure I understand. Do you have an example of what you’re trying to do and what is actually happening?

      • Yuen-Wei Chia

        Yes I do have the example but how do I show you? Upload the files to here? Thanks!

        • Looking for you to explain it. Not really interested in code. I want to fully understand what is going on first.

          • Yuen-Wei Chia

            No problem. Do you have an email? I’m at outside, I might reply late.

      • Yuen-Wei Chia

        My listView ID is out of sync with the Sqlite database ID, which causes a problem as I need the correct ID to update,delete with my Sql query. Any ideas? 😥for example, I am able to delete my item in the List View but not the database because the id does not match at all….

        • When you say list id, are you referring to the index or the id that was returned from SQLite?

          You should not rely on the list row index to match the actual database id values. Instead think about populating your list with an object that contains the database primary key. When you wish to delete, look at the primary key value in the object for the particular list index. Should remain in sync that way.

          • Yuen-Wei Chia

            How do I find the primary key in the object for the list index so the swipe- delete plugin can work with it? Currently I can swipe-delete the list item but when I refresh the page, the item will come back because the database is still intact.