Use A Pre-Populated SQLite Database With NativeScript And Angular

I recently wrote about how to use a SQLite database within a NativeScript Android and iOS application that was built with Angular. This was more or less a revisit to the vanilla NativeScript tutorial on the same subject I had written earlier in the year. What happens when you have a massive amount of data that you’d like to save your user from needing to download before using your application? Can a SQLite database be pre-populated and included within an application?

To keep the flow going, I figured it would be a good idea to demonstrate how to ship a NativeScript Angular application with a pre-filled SQLite database rather than populating it on-the-fly.

To be fair, I wrote how to do this very thing in a vanilla NativeScript application. While it can be accomplished in pretty much the same manner, I figured it would be a good idea to revisit this for clarity.

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

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

The --ng tag above says to create an Angular project. While we’re adding the iOS build platform, you cannot actually build for iOS unless you’re using a Mac with Xcode installed.

With the project created, we need to add the SQLite plugin created by Nathanael Anderson. This can be done by executing the following:

tns plugin add nativescript-sqlite

Before we go any further with the project, we need to come up with a SQLite database that contains some data. There are plenty of tools floating around the internet for this, but my favorite is DB Browser for SQLite.

Download DB Browser and choose to create a file called populated.db after you open it and you’re prompted.

With the application running, choose to create a new table with an id that is an auto incrementing primary key integer, a firstname that is a text value, and a lastname that is also a text value. The name of this table should be person, but in reality, you can name it whatever you want as long as you remember it.

With the table created, you need to add data into it. This can be done by browsing for data and choosing to add a new record. It is very important that you choose to write data when you’re done, otherwise any changes you make will not be saved.

While our populated.db file probably doesn’t have many records in it, you could potentially have a lot.

When it comes to including this database file in our project, file placement is incredibly important. Copy populated.db into the project’s app directory. When we copy it via application code, the SQLite plugin will take the database file from the app directory and place it where it needs to be.

Now let’s take a look at how to use this pre-populated database within the NativeScript Angular application.

Take a look at the following TypeScript component:

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 constructor() {
        if (!Sqlite.exists("populated.db")) {
            Sqlite.copyDatabase("populated.db");
        }
        (new Sqlite("populated.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);
        });
    }

}

This component is part of what was created in the previous tutorial I wrote. However, take a look at the following three lines:

if (!Sqlite.exists("populated.db")) {
    Sqlite.copyDatabase("populated.db");
}

The above three lines is all it takes to copy a pre-filled database from the project’s app directory into the appropriate Android and iOS working spaces. If the database populated.db does not exist where appropriate it will be copied, otherwise it will be ignored.

Once the SQLite database has copied over, the constructor method will continue as normal.

Conclusion

You just saw how to deploy an NativeScript application that uses Angular with a pre-populated SQLite database. This is the Angular version of the vanilla NativeScript tutorial I wrote on the same topic. If you want to dive deeper into SQLite with Angular in NativeScript, I strongly encourage you to check out my tutorial on the subject.

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.