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:

tns create ExampleProject
cd ExampleProject
tns platform add ios
tns platform add android

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:

tns plugin add nativescript-sqlite

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.js, app/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:

var Observable = require("data/observable").Observable;
var Sqlite = require("nativescript-sqlite");

function createViewModel(database) {
    var viewModel = new Observable();
    viewModel.firstname = "";
    viewModel.lastname = "";

    viewModel.insert = function() {
        database.execSQL("INSERT INTO people (firstname, lastname) VALUES (?, ?)", [this.firstname, this.lastname]).then(id => {
            console.log("INSERT RESULT", id);
        }, error => {
            console.log("INSERT ERROR", error);
        });
    }

    viewModel.select = function() {
        database.all("SELECT * FROM people").then(rows => {
            for(var row in rows) {
                console.log("RESULT", rows[row]);
            }
        }, error => {
            console.log("SELECT ERROR", error);
        });
    }

    return viewModel;
}

exports.createViewModel = createViewModel;

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:

var Sqlite = require("nativescript-sqlite");
var createViewModel = require("./main-view-model").createViewModel;

function onNavigatingTo(args) {
    var page = args.object;
    (new Sqlite("my.db")).then(db => {
        db.execSQL("CREATE TABLE IF NOT EXISTS people (id INTEGER PRIMARY KEY AUTOINCREMENT, firstname TEXT, lastname TEXT)").then(id => {
            page.bindingContext = createViewModel(db);
        }, error => {
            console.log("CREATE TABLE ERROR", error);
        });
    }, error => {
        console.log("OPEN DB ERROR", error);
    });
}

exports.onNavigatingTo = onNavigatingTo;

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:

<Page xmlns="http://schemas.nativescript.org/tns.xsd" navigatingTo="onNavigatingTo">
    <StackLayout>
        <TextField id="firstname" hint="First Name" text="{{ firstname }}" />
        <TextField id="lastname" hint="Last Name" text="{{ lastname }}" />
        <StackLayout orientation="horizontal">
            <Button text="Insert" tap="{{ insert }}" />
            <Button text="Select" tap="{{ select }}" />
        </StackLayout>
    </StackLayout>
</Page>

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 C#, JavaScript, Golang and a variety of frameworks such as Angular, NativeScript, and Unity. Nic writes about his development experiences related to making web and mobile development easier to understand.