Code a Real-Time NativeScript App: SQLite

NativeScript is a framework for building cross-platform native mobile apps using XML, CSS, and JavaScript. In this series, we're trying out some of the cool things you can do with a NativeScript app: geolocation and Google Maps integration, SQLite database, Firebase integration, and push notifications. Along the way, we're building a fitness app with real-time capabilities that will use each of these features.

In this tutorial, you'll learn how to integrate a SQLite database into the app to store data locally. Specifically, we'll be storing the walking sessions data that we gathered in the previous tutorial.

What You'll Be Creating

Picking up from the previous tutorial, you'll be adding a tab view for displaying the different portions of the app. Previously our app just had the Tracking page, so we didn't need tabs. In this post, we'll be adding the Walks page. This page will display the user's walking sessions. A new data point will be added here every time the user tracks their walking session. There will also be a function for clearing the data.

Here's what the final output will look like:

Setting Up the Project

If you have followed the previous tutorial on geolocation, you can simply use the same project and build the features that we will be adding in this tutorial. Otherwise, you can create a new project and copy the starter files into your project's app folder.

1	tns create fitApp --appid "com.yourname.fitApp"

After that, you also need to install the geolocation and Google Maps plugins:

1	tns plugin add nativescript-geolocation
2	tns plugin add nativescript-google-maps-sdk

Once installed, you need to configure the Google Maps plugin. You can read the complete instructions on how to do this by reading the section on Installing the Google Maps Plugin in the previous tutorial.

Once all of those are done, you should be ready to follow along with this tutorial.

Running the Project

You can run the project by executing tns run android. But since this app will build on the geolocation functionality, I recommend you use a GPS emulator for quickly setting and changing your location. You can read about how to do so in the section on Running the App in the previous tutorial.

Installing the SQLite Plugin

The first thing that you need to do to start working with SQLite is to install the plugin:

1	tns plugin add nativescript-sqlite

This allows you to do things like connecting to a database and doing CRUD (create, read, update, delete) operations on it.

Connecting to the Database

Open the main-page.js file and import the SQLite plugin:

1	var Sqlite = require("nativescript-sqlite");

You can now connect to the database:

var db_name = "walks.db";

new Sqlite(db_name).then(db => {
    // next: create table for storing walks data
}, error => {
    
});

The walks.db file was created from the terminal using the touch command, so it's just an empty file. Copy it into the app folder.

If it successfully connected, the promise's resolve function will be executed. Inside that, we run the SQL statement for creating the walks table. To keep things simple, all we need to save is the total distance covered (in meters) and the total steps, as well as the start and end timestamps.

db.execSQL("CREATE TABLE IF NOT EXISTS walks (id INTEGER PRIMARY KEY AUTOINCREMENT, total_distance INTEGER, total_steps INTEGER, start_datetime DATETIME, end_datetime DATETIME)").then(id => {
    page.bindingContext = createViewModel(db);
}, error => {
    console.log("CREATE TABLE ERROR", error);
});

Once the query executes successfully, we pass the database instance (db) into the page context. This will allow us to use it from the main-view-model.js file later on.

Fetching Data

Now we're ready to work with the data. But since we'll be working with dates, we first need to install a library called fecha. This allows us to easily parse and format dates:

1	npm install --save fecha

Once it's installed, open the main-view-model.js file and include the library:

1	var fecha = require('fecha');

Next is the code for checking if geolocation is enabled. First, create a variable (walk_id) for storing the ID of a walking record. We need this because the app will immediately insert a new walk record into the walks table when the user starts location tracking. walk_id will store the ID that's auto-generated by SQLite so that we'll be able to update the record once the user stops tracking.

1	var walk_id;

Next, get the current month and year. We'll use it to query the table so it only returns records that are in the same month and year. This allows us to limit the number of records that appear in the UI.

1	var month = fecha.format(new Date(), 'MM'); //e.g 07
2	var year = fecha.format(new Date(), 'YYYY'); //e.g 2017

We also need a variable for storing the start timestamp. We'll use it later on to update the UI. This is because we're only querying the table once when the app is loaded, so we need to manually update the UI of any new data which becomes available. And since the starting timestamp will only have a value when the user starts tracking, we need to initialize it outside the scope so we can update or access its value later on.

1	var st_datetime; // start datetime

Initialize the walks data that will be displayed in the UI:

1	var walks = [];
2	viewModel.walks = [];
3	viewModel.has_walks = false;

Get the data from the walks table using the all() method. Here, we're supplying the month and the year as query parameters. The strftime() function is used to extract the month and year part of the start_datetime.

db.all(
    "SELECT * FROM walks WHERE strftime('%m', start_datetime) == ? AND strftime('%Y', start_datetime) == ? ORDER BY start_datetime DESC", 
    [month, year]
).then((err, rs) => {
    if(!err){
        // next: update the UI with the walks data

    }  
});

Once a success response is returned, we loop through the result set so that we can format the data correctly. Note that the indexes in which we access the individual values depend on the table structure that was described earlier in the main-page.js file. The first column is ID, the second is the total distance, and so on.

The formatted data is then pushed to the walks array and is used to update the UI. has_walks is used as a toggle for the UI so that we can show or hide things based on its value.

rs.forEach((w) => {
    let start_datetime = new Date(w[3]);
    let end_datetime = new Date(w[4]);
    
    walks.push({
        start: fecha.format(start_datetime, 'MMM D, h:mm'), // e.g Jun 5, 5:30
        end: fecha.format(end_datetime, 'h:mm a'), // e.g 6:30 pm
        distance: commafy(w[1]) + 'm', // e.g 2,000m
        steps: commafy(w[2]) // e.g 2,300
    });

});

if(walks.length){
    viewModel.set('has_walks', true);
}
viewModel.set('walks', walks);

This will supply the data for the ListView in the main-page.xml file:

<ListView items="{{ walks }}">   
    <ListView.itemTemplate>
        <GridLayout columns="2*,*,*" rows="auto" class="item item-row">
            <Label text="{{ start + ' - ' + end }}" textWrap="true" row="0" col="0"/>
            <Label text="{{ distance }}" textWrap="true" row="0" col="1" />
            <Label text="{{ steps }}" textWrap="true" row="0" col="2" />
        </GridLayout>
    </ListView.itemTemplate>
</ListView>

Saving Data

Once the user starts tracking, set the current datetime as the start_datetime and insert initial values into the table using the execSQL() function. Just like the all() function, this expects the SQL query as the first argument and an array of parameters as the second.

If the query is successful, it should return the auto-generated ID for the inserted record. We then assign it as the value for the walk_id so it can be used later on to update this specific record.

st_datetime = new Date();
var start_datetime = fecha.format(st_datetime, 'YYYY-MM-DD HH:mm:ss');

db.execSQL(
    "INSERT INTO walks (total_distance, total_steps, start_datetime) VALUES (?, ?, ?)", 
    [0, 0, start_datetime]
).then((id) => {
    walk_id = id; 
}, (e) => {
    dialogs.alert(e.message);
});

Once the user stops tracking, we again get the current timestamp and format it accordingly for storage:

1	var ed_datetime = new Date();
2	var end_datetime = fecha.format(ed_datetime, 'YYYY-MM-DD HH:mm:ss');

Since we've ordered the results from most to least recent, we use unshift() (instead of push()) to add the new item to the top of the walks array.

walks.unshift({
    start: fecha.format(st_datetime, 'MMM D, h:mm'),
    end: fecha.format(ed_datetime, 'h:mm a'),
    distance: commafy(total_distance) + 'm',
    steps: commafy(total_steps)
});

viewModel.set('walks', walks);
if(walks.length > 0){
    viewModel.set('has_walks', true);
}

After that, we once again we use the execSQL() function to update the record that we inserted earlier:

db.execSQL(
    "UPDATE walks SET end_datetime = ?, total_steps = ?, total_distance = ? WHERE id = ?", 
    [end_datetime, total_steps, total_distance, walk_id]
).then(
    (err, id) => {
        if(!err){
           // todo: add code for resetting the tracking UI here
        }
    }
);

Be sure to move the code for resetting the tracking UI (to reset the total distance and steps) inside the promise's resolve function so you can easily test whether the update query executed successfully or not.

Clearing Data

Deleting data is done by clicking on the Clear Data button below the list of walk data:

1	<ListView items="{{ walks }}">
2	...
3	</ListView>
4	<Button text="Clear Data" tap="{{ clearData }}" class="bg-danger" />

In the main-view-model.js file, add the code for deleting all the data from the walks table. If you're used to MySQL, you might be wondering why we're using the DELETE query instead of TRUNCATE for emptying the table. Well, that's because SQLite doesn't have the TRUNCATE function. That's why we have to use the DELETE query without supplying a condition so that it will delete all the records that are currently in the table.

viewModel.clearData = function() {

    dialogs.confirm("Are you sure you want to clear your data?").then((agree) => {
        
        if(agree){
            db.execSQL("DELETE FROM walks", []).then(
                (err) => {
                    if(!err){
                        dialogs.alert("Data has been cleared!");
                        walks = [];
                        viewModel.set('walks', []);
                        viewModel.set('has_walks', false);
                    }
                }
            ); 
        }
    });
    
}

Conclusion

In this tutorial, you've learned how to locally store data in your NativeScript apps using the SQLite plugin. As you have seen, SQLite allows you to reuse your existing SQL skills in managing a local database. It's important to note that not all functions that you're used to in MySQL are supported in SQLite. So it's always wise to consult the documentation if you're not sure whether a certain function is supported or not.

If you want to learn about other options for storing data in NativeScript apps, I recommend you read this article on Going Offline With NativeScript.

In the final post of this series, we'll add push notifications to our app.

In the meantime, check out some of our other posts on NativeScript and cross-platform mobile coding.

For a comprehensive introduction to NativeScript, try our video course Code a Mobile App With NativeScript. In this course, Keyvan Kasaei will show you step by step how to build a simple application. Along the way, you'll learn how to implement a simple app workflow with network requests, an MVVM architecture, and some of the most important NativeScript UI components. By the end, you'll understand why you should consider NativeScript for your next mobile app project.