How to develop mobile app in browser with Native SQLite

I have been working on a complete rewrite of an Ionic1 app to Ionic 3. We had already spent huge time for development in Ionic 1 with simulators and devices. Our app needed complete offline support and had a huge dependency on SQLite. However, testing the app with SQLite was very much time consuming and slow. To overcome this I came up with the following solution which allowed us to develop app in browser with ionic serve command and live reload.

WebSQL & Ionic Native API

If we observe the API of Ionic Native SQLite, it’s very similar to the WebSql supported by browsers.

Step 1:
Create an ionic provider
run ionic g provider Sql
It will create a new folder in providers folder called ‘sql’. Now add the following code to it:

//sql.ts
import { Injectable } from '@angular/core';
import { SQLite } from '@ionic-native/sqlite';
import { Platform } from 'ionic-angular';
import { isEmpty, isNull, keys, sortBy, get, parseInt } from 'lodash';
import { browserDBInstance } from './browser';

declare var window: any;
const SQL_DB_NAME = process.env.IONIC_ENV === 'dev' ? '__broswer.db' : '__native.db';

@Injectable()
export class SqlProvider {

  dbInstance: any;

  constructor(public sqlite: SQLite, private platform: Platform) {
    this.init();
  }

  async init() {
    if (!this.platform.is('cordova')) {
      let db = window.openDatabase(SQL_DB_NAME, '1.0', 'DEV', 5 * 1024 * 1024);
      this.dbInstance = browserDBInstance(db);
    } else {
      this.dbInstance = await this.sqlite.create({
        name: SQL_DB_NAME,
        location: 'default'
      });
    }
  }
}

The above code checks if the platform is ‘cordova’ or not. It the current platform is not ‘cordova’, we assume we are running in browser. Line 21 create a browser WebSql DB object. Line number 22, call the `broswerDbInstance(db)` function which accepts the browser WebSql db object.

Next step is to create an object which is compatible with Ionic Native SQlite object. That is, it should have functions like executeSql, sqlBatch, etc.
Now create a new file in sql folder called `browser.ts` and the following code.

//browser.ts
export const browserDBInstance = (db) => {

  return {
    executeSql: (sql) => {
      return new Promise((resolve, reject) => {
        db.transaction((tx) => {
          tx.executeSql(sql, [], (tx, rs) => {
            resolve(rs)
          });
        });
      })
    },
    sqlBatch: (arr) => {
      return new Promise((r, rr) => {
        let batch = [];
        db.transaction((tx) => {
          for (let i = 0; i < arr.length; i++) {
            batch.push(new Promise((resolve, reject) => {
              tx.executeSql(arr[i], [], () => { resolve(true) })
            }))
            Promise.all(batch).then(() => r(true));
          }
        });
      })
    }
  }
}

One thing I noticed that the most used API from the native SQLite were executeSQL and sqlBatch, hence I have implemented only two api’s. If you want to add mode, you can implement other API’s like addTransaction etc. All api’s returns a promise.

To use the newly created db in code, you need to inject the SqlProvider into any page, component or other providers like below.:

// HomePage.ts
import { IonicPage } from 'ionic-angular';
import { SqlProvider } from '../path/to/sql-provider';

@IonicPage()
@Component({
  selector: 'page-home',
  templateUrl: 'home.html',
})
export class HomePage {

  constructor(private sql:SqlProvider) {
  }
  
  async ionViewDidLoad () {
     await this.sql.dbInstance.executeSql('CREATE TABLE IF NOT EXISTS user(id INTEGER PRIMARY KEY, name)');
     await this.sql.dbInstance.executeSql(`INSERT INTO user(id, user) VALUES (1, 'Suraj')`);
     let users = await this.sqlInstance.executeSql('SELECT * FROM user');
     console.log(users);
  }
}

That’s it. Now you can run ionic serve and start development in browser itself. The created DB can be seen in Applications tab in chrome dev tool.

Hope this helps someone who likes to do faster development in browser. I will add more posts about how to handle DB migrations in hybrid mobile apps, create a DB layer to handle all DB operations, how to make your app offline compatible etc.

Leave a Reply