Our website is made possible by displaying online advertisements to our visitors. Please consider supporting us by disabling your ad blocker.

Using SQLite In A NativeScript Angular Mobile App

TwitterFacebookRedditLinkedInHacker News

Quite a bit of time ago when I first started using NativeScript, I wrote a tutorial around using a SQLite database with it. Now just to be clear, this was with vanilla NativeScript, before Angular was available. Heck, the previous article was using JavaScript and not even TypeScript.

Well, times have changed and I figured it would be a good idea to revisit this NativeScript SQLite tutorial, but this time give it some TypeScript and Angular flair.

Let’s start by creating a fresh NativeScript project with Angular. From the Command Prompt (Windows) or Terminal (Mac and Linux), execute the following:

tns create SqlProject --ng
cd SqlProject
tns platform add ios
tns platform add android

The --ng flag in the above command means that we are creating an Angular with TypeScript project. While we’re adding the iOS platform, we won’t be able to build for iOS unless we’re using a Mac with Xcode installed.

Like with the previous vanilla NativeScript tutorial, we’re going to be using the SQLite plugin by Nathaneal Anderson. This plugin can be installed by executing the following command:

tns plugin add nativescript-sqlite

There is a catch, however. The free version of the plugin does not ship with TypeScript type definitions. The good thing is that this will only slow us down, not stop us from using SQLite in an Angular project.

The project we create is going to be simple. It will be a single page application and the data that we insert into the database will be static. This means we will not be creating a form that collects user input. However, it should give you more than enough of an idea to accomplish this on your own.

Open the project’s app/app.component.ts file and include the following TypeScript code. Don’t worry, we’re going to break it down after.

import {Component} from "@angular/core";
var Sqlite = require("nativescript-sqlite");

@Component({
    selector: "my-app",
    templateUrl: "app.component.html",
})
export class AppComponent {

    private database: any;
    public people: Array<any>;

    public constructor() {
        this.people = [];
        (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 => {
                this.database = db;
            }, error => {
                console.log("CREATE TABLE ERROR", error);
            });
        }, error => {
            console.log("OPEN DB ERROR", error);
        });
    }

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

    public fetch() {
        this.database.all("SELECT * FROM people").then(rows => {
            this.people = [];
            for(var row in rows) {
                this.people.push({
                    "firstname": rows[row][1],
                    "lastname": rows[row][2]
                });
            }
        }, error => {
            console.log("SELECT ERROR", error);
        });
    }

}

So what does this monster TypeScript snippet do?

Because we don’t have available type definitions for the SQLite plugin, we have to import it as if it were a JavaScript library:

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

At this point the library will work as normal, but we’ll be missing any auto complete and things like that. Not the end of the world, or even a big deal.

Inside the AppComponent class we have two variables. The database variable will hold our open SQLite instance and people will hold the results of our query.

public constructor() {
    this.people = [];
    (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 => {
            this.database = db;
        }, error => {
            console.log("CREATE TABLE ERROR", error);
        });
    }, error => {
        console.log("OPEN DB ERROR", error);
    });
}

Inside the constructor method we open a local database called my.db and create a single table if it doesn’t already exist. After the table is created, we store the open database instance for further use.

Now we have methods for fetching or creating new data with SQLite.

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

When the insert method is called, data will be added to the database. In our example my first and last name will be added in a static fashion, but it can easily be changed to something dynamic. After inserting the data, the database will be queried.

public fetch() {
    this.database.all("SELECT * FROM people").then(rows => {
        this.people = [];
        for(var row in rows) {
            this.people.push({
                "firstname": rows[row][1],
                "lastname": rows[row][2]
            });
        }
    }, error => {
        console.log("SELECT ERROR", error);
    });
}

Because we plan to have our data show up in a list, we need to parse the results into an object then push the object into our array. You’ll notice the results above use array index one and two. The zero index is the primary key which we don’t really need to store in this example.

With the logic in place, now we can take a look at the UI. Open the project’s app/app.component.html file and include the following markup:

<ActionBar title="SQL App">
    <ActionItem text="Add" ios.position="right" (tap)="insert()"></ActionItem>
    <ActionItem text="Refresh" ios.position="left" (tap)="fetch()"></ActionItem>
</ActionBar>
<StackLayout>
    <ListView [items]="people">
        <template let-person="item">
            <Label text="{{ person.firstname }} {{ person.lastname }}"></Label>
        </template>
    </ListView>
</StackLayout>

The above UI has an action bar with two buttons. One button will add new data into the database and the other will refresh the ListView. The ListView is populated from the people array where we display the first name and last name of every array item in a Label element.

Conclusion

You just saw how to use SQLite in a NativeScript Angular Android and iOS mobile application. While we were able to get the job done, there are better ways to do this. For example, the database interaction should probably happen inside an Angular provider.

In a previous article I demonstrated how to use the same SQLite plugin, but in a vanilla NativeScript project. If you’re interested in using a NoSQL database rather than a relational database, I also wrote a tutorial that made use of Couchbase in an Angular NativeScript application.

A video version of the article can be seen below.

Nic Raboy

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.