How to Use Ionic SQLite Queries & Pre-Populated Database

Although the Ionic Storage package is great to store almost any kind of data, many of the readers wrote they are using MySQL on the backend side and need some more equivalent storage inside their Ionic app so we’ll go for an Ionic SQLite App today.

In this post we will see how to easily import SQL data into our Ionic app, and how to work with queries to retrieve or modify our stored data!

Note: If you have some sort of MySQL database you should still work on a REST API to deliver the data and don’t work directly on the database.

Setting up our Ionic SQLite App

We start with a blank Ionic app and install the Cordova SQLite plugin to access the database. Additional we add the SQLite Porter plugin as well as the according Ionic Native NPM packages so we can populated our SQL database from e.g. a given SQL dump. Go ahead and run:

ionic start devdacticSql blank
cd devdacticSql
ionic g provider database
ionic cordova plugin add uk.co.workingedge.cordova.plugin.sqliteporter
ionic cordova plugin add cordova-sqlite-storage
npm install --save @ionic-native/sqlite-porter @ionic-native/sqlite

We also create a provider for all of our database interaction right here.

As said before we want to fill our database with some initial values. You can do this of course with the standard Ionic SQLite wrapper but you would either have to prepare each statement accordingly or split up your SQL dump into the right format, which is sometimes not a good idea.

The SQLite Porter plugin would also allow to import JSON data, but here we stick to SQL.

In our case it’s enough to have a SQL dump like the one below, so for testing create a file at src/assets/dummyDump.sql and insert:

CREATE TABLE IF NOT EXISTS developer(id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT,skill TEXT,yearsOfExperience INTEGER);
INSERT INTO developer(name, skill, yearsOfExperience) VALUES ('Simon', 'Ionic', '4');
INSERT INTO developer(name, skill, yearsOfExperience) VALUES ('Jorge', 'Firebase', '2');
INSERT INTO developer(name, skill, yearsOfExperience) VALUES ('Max', 'Startup', '5');

We just create one new table and insert some data sets.

Next we need to add everything we installed to our module and load the plugins and stuff, so change your src/app/app.module.ts to:

import { BrowserModule } from '@angular/platform-browser';
import { ErrorHandler, NgModule } from '@angular/core';
import { IonicApp, IonicErrorHandler, IonicModule } from 'ionic-angular';
import { SplashScreen } from '@ionic-native/splash-screen';
import { StatusBar } from '@ionic-native/status-bar';

import { MyApp } from './app.component';
import { HomePage } from '../pages/home/home';

import { IonicStorageModule } from '@ionic/storage';
import { HttpModule } from '@angular/http';

import { DatabaseProvider } from '../providers/database/database';

import { SQLitePorter } from '@ionic-native/sqlite-porter';
import { SQLite } from '@ionic-native/sqlite';

@NgModule({
  declarations: [
    MyApp,
    HomePage
  ],
  imports: [
    BrowserModule,
    HttpModule,
    IonicStorageModule.forRoot(),
    IonicModule.forRoot(MyApp)
  ],
  bootstrap: [IonicApp],
  entryComponents: [
    MyApp,
    HomePage
  ],
  providers: [
    StatusBar,
    SplashScreen,
    {provide: ErrorHandler, useClass: IonicErrorHandler},
    DatabaseProvider,
    SQLitePorter,
    SQLite
  ]
})
export class AppModule {}

Now we are all set up to use our database!

Writing the SQL Database Provider

The provider will act as some middle layer for all interactions to the database, just like in other examples where we perform REST calls or have any kind of specific business logic.

Once our platform is ready we create or open a new SQLite database on our device, and once we are ready we can fill the database with our SQL dump. But we only do this on initial startup, because otherwise we would insert the initial data over and over again.

You might also notice the BehaviourSubject we are using, perhaps this is new to you. Basically it’s kind of an Observable and we can emit new values to the subscribers by calling next() on it. We use this mechanism to let our class later know that the database is ready for interaction so we can prevent race conditions between platform load, open database and so on.

To fill our database we use the previously added SQLitePorter where we pass in the database and also the content of our SQL dump which we need to load before.

Before we go through the last functions, open your src/providers/database/database.ts and insert:

import { Injectable } from '@angular/core';
import { Platform } from 'ionic-angular';
import { SQLite, SQLiteObject } from '@ionic-native/sqlite';
import { SQLitePorter } from '@ionic-native/sqlite-porter';
import { Http } from '@angular/http';
import 'rxjs/add/operator/map';
import { BehaviorSubject } from 'rxjs/Rx';
import { Storage } from '@ionic/storage';

@Injectable()
export class DatabaseProvider {
  database: SQLiteObject;
  private databaseReady: BehaviorSubject<boolean>;

  constructor(public sqlitePorter: SQLitePorter, private storage: Storage, private sqlite: SQLite, private platform: Platform, private http: Http) {
    this.databaseReady = new BehaviorSubject(false);
    this.platform.ready().then(() => {
      this.sqlite.create({
        name: 'developers.db',
        location: 'default'
      })
        .then((db: SQLiteObject) => {
          this.database = db;
          this.storage.get('database_filled').then(val => {
            if (val) {
              this.databaseReady.next(true);
            } else {
              this.fillDatabase();
            }
          });
        });
    });
  }

  fillDatabase() {
    this.http.get('assets/dummyDump.sql')
      .map(res => res.text())
      .subscribe(sql => {
        this.sqlitePorter.importSqlToDb(this.database, sql)
          .then(data => {
            this.databaseReady.next(true);
            this.storage.set('database_filled', true);
          })
          .catch(e => console.error(e));
      });
  }

  addDeveloper(name, skill, years) {
    let data = [name, skill, years]
    return this.database.executeSql("INSERT INTO developer (name, skill, yearsOfExperience) VALUES (?, ?, ?)", data).then(data => {
      return data;
    }, err => {
      console.log('Error: ', err);
      return err;
    });
  }

  getAllDevelopers() {
    return this.database.executeSql("SELECT * FROM developer", []).then((data) => {
      let developers = [];
      if (data.rows.length > 0) {
        for (var i = 0; i < data.rows.length; i++) {
          developers.push({ name: data.rows.item(i).name, skill: data.rows.item(i).skill, yearsOfExperience: data.rows.item(i).yearsOfExperience });
        }
      }
      return developers;
    }, err => {
      console.log('Error: ', err);
      return [];
    });
  }

  getDatabaseState() {
    return this.databaseReady.asObservable();
  }

}

To subscribe to the Database ready state we use our own Behaviour subject and return it as an Observable to whoever wants to subscribe.

To get all stored developers in our database we perform a simple SELECT statement against our table and transform the result from the SQL statement to a useful array which we can return to our calling class!

If we now want to add a developer, we can again perform a SQL query to insert the new data into our database.

Basically, you can now write any SQL query you can come up with to search data or perform whatever action you need to do!

Loading and Showing SQL Data

It’s time to actually use our new provider, so first of all we craft small view with a few input fields to capture new data for the database.

Below the inputs we show a list of all developers, which will be gathered from our database (hopefully). Go ahead and change your src/pages/home/home.html to:

<ion-header>
  <ion-navbar>
    <ion-title>
      Developer Data
    </ion-title>
  </ion-navbar>
</ion-header>

<ion-content padding>
  <ion-item>
    <ion-label stacked>What's your name?</ion-label>
    <ion-input [(ngModel)]="developer.name" placeholder="Developer Name"></ion-input>
  </ion-item>
  <ion-item>
    <ion-label stacked>What's your special skill?</ion-label>
    <ion-input [(ngModel)]="developer.skill" placeholder="Special Skill"></ion-input>
  </ion-item>
  <ion-item>
    <ion-label stacked>How long have you been working?</ion-label>
    <ion-input [(ngModel)]="developer.yearsOfExperience" placeholder="Years of experience"></ion-input>
  </ion-item>
  <button ion-button full (click)="addDeveloper()">Add Developer Info</button>

  <ion-list>
    <ion-item *ngFor="let dev of developers">
      <h2>{{ dev.name }}</h2>
      <p>{{ dev.yearsOfExperience }} years of {{ dev.skill }} Experience!</p>
    </ion-item>
  </ion-list>
</ion-content>

Nothing really special here. Now we only need to connect the according functions and everything should work.

As we have most of the logic inside our provider, the class of our view is quite small.

Remember the BehaviourSubject? Inside our constructor we subscribe to the database state, and once the value changes to true we try to load our initial developer data. By doing this we prevent that the constructor calls the load before the database is actually ready, which would result in an error and no data inside our view.

When we add a developer we expect the database to be ready already, we just call the according function addDeveloper() of our provider to insert a new row into our SQLite database.

Open your src/pages/home/home.ts and change it to:

import { DatabaseProvider } from './../../providers/database/database';
import { Component } from '@angular/core';
import { NavController, Platform } from 'ionic-angular';

@Component({
  selector: 'page-home',
  templateUrl: 'home.html'
})
export class HomePage {
  developer = {};
  developers = [];

  constructor(public navCtrl: NavController, private databaseprovider: DatabaseProvider, private platform: Platform) {
    this.databaseprovider.getDatabaseState().subscribe(rdy => {
      if (rdy) {
        this.loadDeveloperData();
      }
    })
  }

  loadDeveloperData() {
    this.databaseprovider.getAllDevelopers().then(data => {
      this.developers = data;
    })
  }

  addDeveloper() {
    this.databaseprovider.addDeveloper(this.developer['name'], this.developer['skill'], parseInt(this.developer['yearsOfExperience']))
    .then(data => {
      this.loadDeveloperData();
    });
    this.developer = {};
  }

}

That’s all, now you only need to make sure you run this app on a device/simulator as we make use of the underlying SQLite database which is not available inside your browser if you use the preview or lab function!

Conclusion

With the right plugins we are able to build an Ionic SQLite app to import SQL data (or even JSON structure) into the SQLite database of a device. The Ionic Storage plugin is a bit easier to use and also works inside our browser but has dropped support for the query() function, so if you really on this function you might need to access your database directly through these plugins.

You can also find a video version of this article below!

Happy Coding, Simon

How to Use Ionic SQLite Queries & Pre-Populated Database Last update: 2017-06-20

Setting up our Ionic SQLite App

Writing the SQL Database Provider

Loading and Showing SQL Data

Conclusion