Importing & exporting SQLite data for Ionic Framework mobile apps

January 23, 2018, 9:00 am Categories:

Categories

It's a common question I hear from developers all the time - how to import existing SQLite data into their Ionic mobile apps?

Over the course of this tutorial we're going to use the following plugins (along with a little help from Angular's HttpClient module and PHP) to import pre-existing SQLite data from a remote source to populate an SQLite database within an Ionic Framework application:

Along the way we'll explore importing data as SQL and JSON as well as exporting that in the form of SQL only.

Let's start with our sample data - technologies.sql:

CREATE TABLE IF NOT EXISTS technologies (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, description TEXT NOT NULL);
INSERT INTO technologies(name, description) VALUES("Angular", "Frontend development framework created by Google");
INSERT INTO technologies(name, description) VALUES("TypeScript", "Superset of JavaScript");
INSERT INTO technologies(name, description) VALUES("HTML5", "A collection of different JavaScript APIs and new HTML markup syntax");
INSERT INTO technologies(name, description) VALUES("Sass", "CSS Preprocessor for writing stylesheets with a programmatic approach");
INSERT INTO technologies(name, description) VALUES("Apache Cordova", "Hybrid application development framework");

As well as in the form of JSON - technologies.json:

{  
   "structure":{
       "tables":{
           "technologies":"([id] PRIMARY KEY, [name], [description])"
       }
   },
   "data":{
   	  "inserts": {
   	     "technologies": [
   	        {"id":1,"name":"Angular","description":"Front-end development framwork created by Google"},
   	        {"id":2,"name":"TypeScript","description":"Superset of JavaScript"},
   	        {"id":3,"name":"HTML5","description":"A collection of different JavaScript API's and new HTML markup syntax"},
   	        {"id":4,"name":"Sass","description":"CSS Pre-processor for writing stylesheets with a programmatic approach"},
   	        {"id":5,"name":"Apache Cordova","description":"Hybrid application development framework"}
   	     ]
   	  }
   }
}

The JSON data structure required for the SQLite Porter plugin is described here.

With the SQL data - in both SQL and JSON form - now in place let's turn our attention to creating the foundation for our Ionic application.

Starting point

Open up your Ionic CLI and run the following commands to create the ionic-importa application, create the relevant services and install the necessary platforms, plugins and npm packages:

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

Our next step involves configuring the application's root module - ionic-importa/src/app/app.module.ts - with the necessary plugin, component and service declarations:

import { BrowserModule } from '@angular/platform-browser';
import { HttpClientModule } from '@angular/common/http';
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 { DatabaseProvider } from '../providers/database/database';

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



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

Now we can move onto the coding for the DatabaseProvider service which will handle creating the application SQLite database, importing and exporting SQL data and importing JSON data.

Database management

Our DatabaseProvider service will be used to manage the following requirements for the ionic-importa application:

  • Creating/opening the application SQLite database
  • Checking for existing data within the specified table
  • Retrieve all existing records
  • Importing SQL data
  • Exporting SQL data
  • Importing JSON data
  • Clearing existing tables and data from the SQLite database

These are handled within the ionic-importa/src/providers/database/database.ts service (with a little help supplied by methods from the SQLite Porter plugin for importing and exporting data) as follows:

import { HttpClient } from '@angular/common/http';
import { Injectable } from '@angular/core';
import { Platform } from 'ionic-angular';
import { SQLite, SQLiteObject } from '@ionic-native/sqlite';
import { SQLitePorter } from '@ionic-native/sqlite-porter';


@Injectable()
export class DatabaseProvider {

  
   /**
    * @name _DB
    * @type {object}
    * @private
    * @description     Defines an object for handling interfacing with the
    				           SQLite plugin
    */
   private _DB 	: SQLiteObject;




   /**
    * @name _DB_NAME
    * @type {object}
    * @private
    * @description     Defines the name of the SQLite database to be created
    */
   private _DB_NAME : string 		= "ionic.db";




   constructor(public http 		   : HttpClient,
               private _PLAT       : Platform,
   			   private _SQL        : SQLite,
   			   private _PORTER     : SQLitePorter) 
   {  }




   /**
    * @public
    * @method init
    * @description          Creates the SQLite database
    * @return {none}
    */
   init() : void
   {
       // Define the application SQLite database
       this._SQL.create({
          name 		  : this._DB_NAME,
          location 	  : 'default'
       })
       .then((db: SQLiteObject) =>
       {   
          // Associate the database handler object with the _DB private property
          this._DB = db;   
       })
       .catch((e) =>
       {
          console.log(e);
       });
   }




   /**
    * @public
    * @method dataExistsCheck
    * @param tableName    {String}          Name of table we want to check for data
    * @description          Checks that data exists within the specified SQLite table
    * @return {Promise}
    */
   dataExistsCheck(tableName : string) : Promise
   {
      return new Promise((resolve, reject) =>
      {
         this._DB.executeSql('SELECT count(*) AS numRows FROM ' + tableName, {})
         .then((data : any) =>
         {
            var numRows = data.rows.item(0).numRows;
            resolve(numRows);
         })
         .catch((e) =>
         {
            reject(e);
         });
      });
   }




   /**
    * @public
    * @method retrieveAllRecords
    * @description          Retrieves all stored records from the technologies SQLite table
    * @return {Promise}
    */
   retrieveAllRecords() : Promise
   {
      return new Promise((resolve, reject) =>
      {

         this._DB.executeSql('SELECT id, name, description FROM technologies', {})
         .then((data : any) =>
         {
            let items : any 	= [];
            if(data.rows.length > 0)
            {
               var k;

               // iterate through returned records and push as nested objects into
               // the items array
               for(k = 0; k < data.rows.length; k++)
               {
                  items.push(
                  {
	                 id 			    : data.rows.item(k).id,
	                 name 			    : data.rows.item(k).name,
	                 description 	    : data.rows.item(k).description
                  });
               }
            }
            resolve(items);
         })
         .catch((error : any) =>
         {
            reject(error);
         });

      });
   }




   /**
    * @public
    * @method importSQL
    * @param sql    {String}          The SQL data to be imported
    * @description          Imports the supplied SQL data to the application database
    * @return {Promise}
    */
   importSQL(sql 	: any) : Promise
   {
      return new Promise((resolve, reject) =>
      {
         this._PORTER.importSqlToDb(this._DB, sql)
         .then((data) =>
         {
            resolve(data);
         })
         .catch((e) =>
         {
            reject(e);
         });
      });      
   }




   /**
    * @public
    * @method exportAsSQL
    * @description          Exports SQL data from the application database
    * @return {Promise}
    */
   exportAsSQL() : Promise
   {
      return new Promise((resolve, reject) =>
      {
         this._PORTER
         .exportDbToSql(this._DB)
         .then((data) =>
         {
            resolve(data);
         })
         .catch((e) =>
         {
            reject(e);
         });
      });
   }




   /**
    * @public
    * @method importJSON
    * @param json    {String}          The JSON data to be imported
    * @description          Imports the supplied JSON data to the application database
    * @return {Promise}
    */
   importJSON(json : any) : Promise
   {
      return new Promise((resolve, reject) =>
      {
         this._PORTER
         .importJsonToDb(this._DB, json)
         .then((data) =>
         {
            resolve(data);
         })
         .catch((e) =>
         {
            reject(e);
         });
      });
   }




   /**
    * @public
    * @method clear
    * @description          Removes all tables/data from the application database
    * @return {Promise}
    */
   clear() : Promise
   {
      return new Promise((resolve, reject) =>
      {
         this._PORTER
         .wipeDb(this._DB)
         .then((data) =>
         {
            resolve(data);
         }) 
         .catch((error) =>
         {
            reject(error);
         }); 
      });
   }


}

With our database handling logic in place we can now start to implement this within the application's HomePage component to facilitate SQLite database creation and population with remote data (as well as exporting said data if required).

Crafting the HomePage component logic

Our HomePage component, working in tandem with the component view template, will manage the following tasks for the ionic-importa application:

  • Checking to see if records for the application database already exists and, if so, loading those for display
  • Providing the user with the option to populate the database with SQL or JSON data
  • Retrieving the SQL or JSON data for populating the database with
  • Importing the retrieved SQL or JSON data
  • Providing the user with the option to export the database SQL to a remote PHP script for writing to a SQL file

This functionality is handled within dedicated methods defined within the ionic-importa/src/pages/home/home.ts component class as follows (these should be fairly easy to understand/make sense of with the commenting and naming conventions used throughout the class):

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

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


   /**
    * @name hasData
    * @type {Boolean}
    * @public
    * @description     Flag used in the template to control display of database records
    */
   public hasData : boolean 		= false;



   /**
    * @name technologies
    * @type {object}
    * @public
    * @description     Stores all of the retrieved database table records
    */
   public technologies : any;



   /**
    * @name dataImported
    * @type {Boolean}
    * @public
    * @description     Flag used to determine whether data has been imported into the SQLite database
    */
   public dataImported : boolean 	= false;



   /**
    * @name _SQL_NAME
    * @type {String}
    * @private
    * @description     Name of the SQL file
    */
   private _SQL_NAME : string 		= 'technologies.sql';



   /**
    * @name _SQL_URI
    * @type {String}
    * @private
    * @description     Location of the SQL file
    */
   private _SQL_URI : string 		= encodeURI("http://REMOTE-URI-HERE/technologies.sql");



   /**
    * @name _JSON_NAME
    * @type {String}
    * @private
    * @description     Name of the JSON file
    */
   private _JSON_NAME : string 		= 'technologies.json';



   /**
    * @name _JSON_URI
    * @type {String}
    * @private
    * @description     Location of the JSON file
    */
   private _JSON_URI : string 		= encodeURI("http://REMOTE-URI-HERE/technologies.json");




   /**
    * @name _REMOTE_URI
    * @type {String}
    * @private
    * @description     The address of the remote PHP script
    */
   private _REMOTE_URI : string		= "http://REMOTE-URI-HERE/parse-data.php";


   

   constructor(public navCtrl 	: NavController,
               private _ALERT   : AlertController,
               private _HTTP    : HttpClient,
   			   private _DB    	: DatabaseProvider,
   			   private _PLAT    : Platform) 
   { }




   /**
    * @public
    * @method ionViewDidLoad
    * @description         Check whether data exists on template view load
    * @return {none}
    */
   ionViewDidLoad() : void
   {
      this._PLAT
      .ready()
      .then(() =>
      {
         setTimeout(() =>
         {
            this._DB
            .dataExistsCheck('technologies')
            .then((data) =>
            {
               this.loadRecords();      
            })
            .catch((error) =>
            {
               console.dir(error);
            });
         }, 1500);
      });
   }




   /**
    * @public
    * @method loadRecords
    * @description         Retrieve records from database and, on success, set hasData flag to true
    * @return {none}
    */
   loadRecords() : void
   {
      this._DB
      .retrieveAllRecords()
      .then((data : any) =>
      {
         this.hasData 		= true;
         this.technologies 	= data;
      })
      .catch((error : any) =>
      {
         console.dir(error);
      });
   }



   
   /**
    * @public
    * @method import
    * @description         Display an alert window allowing the user to select their import type
    * @return {none}
    */
   import() : void
   {
      this._DB
      .dataExistsCheck('technologies')
      .then((data : any) =>
      {
         // If we have existing data then wipe the database
         if(data > 0)
         {
            this._DB
            .clear()
            .then((data) => 
            {
               // If database successfully wiped of tables/data call the importAlert method
               this.hasData = false;
               this.importAlert();
            })
            .catch((error) =>
            {
               console.dir(error);
            });
         }
         // If we don't have existing data just call the importAlert method
         else
         {
            this.importAlert();
         }
      })
      .catch((error) =>
      {
         this.importAlert();
      });         
   }




   /**
    * @public
    * @method retrieveSQLFile
    * @description         Retrieve remote SQL file using Angular HttpClient get method
    * @return {none}
    */
   retrieveSQLFile() : void
   {
      this._HTTP
      .get(this._SQL_URI, {responseType: 'text'})
      .subscribe((data) =>
      {
         this.importSQL(data);
      },
      (error) =>
      {
         console.dir(error);
      });
   }




   /**
    * @public
    * @method retrieveJSONFile
    * @description         Retrieve remote JSON file using Angular HttpClient get method
    * @return {none}
    */
   retrieveJSONFile() : void
   {
      this._HTTP
      .get(this._JSON_URI)
      .subscribe((data) =>
      {
         this.importJSON(data);
      },
      (error) =>
      {
         console.dir(error);
      });
   }




   /**
    * @public
    * @method importSQL
    * @param sqlFile     {any}      The SQL file data to be imported
    * @description         Import SQL file
    * @return {none}
    */
   importSQL(sqlFile 	: any) : void
   {
      this._DB
      .importSQL(sqlFile)
      .then((res) =>
      {
         this.dataImported = true;
         this.loadRecords();
      })
      .catch((error) =>
      {
         console.dir(error);
      });
   }




   /**
    * @public
    * @method importJSON
    * @param jsonFile     {any}      The JSON file data to be imported
    * @description         Import JSON file
    * @return {none}
    */
   importJSON(jsonFile  : any) : void
   {
      this._DB
      .importJSON(jsonFile)
      .then((res) =>
      {
         this.dataImported = true;
         this.loadRecords();
      })
      .catch((error) =>
      {
         console.dir(error);
      });
   }




   /**
    * @public
    * @method importAlert
    * @description         Display an Alert Window allowing the user to select their data import type: SQL or JSON 
    * @return {none}
    */
   importAlert() : void
   {
      let alert : any = this._ALERT.create({
         title 		: 'Import data',
         subTitle 	: 'Please select which import option you prefer',
         buttons 	: [
            {
            	text 		: 'JSON',
            	handler 	: () =>
            	{
            	   this.retrieveJSONFile();
            	} 
            },
            {
            	text 		: 'SQL',
            	handler 	: () =>
            	{
            	   this.retrieveSQLFile();
            	} 
            }
         ]
      });
      alert.present();
   }




   /**
    * @public
    * @method exportAlert
    * @description         Display an Alert Window allowing the user to select their data export type: currently only SQL 
    * @return {none}
    */
   exportAlert() : void
   {
      let alert : any = this._ALERT.create({
         title 		: 'Export data',
         subTitle 	: 'Please select which export option you prefer',
         buttons 	: [
            {
            	text 		: 'SQL',
            	handler 	: () =>
            	{
            	   this.exportToSQL();
            	} 
            }
         ]
      });
      alert.present();
   }




   /**
    * @public
    * @method exportToSQL
    * @description         Handles the export of SQL data using the DatabaseProvider service
    * @return {none}
    */
   exportToSQL() : void
   {
      this._DB
      .exportAsSQL()
      .then((res) =>
      {
         let fileName : any 		= Date.now() + '.sql';
         this.parseAndUploadSQL(fileName, res);
      })
      .catch((error) =>
      {
         console.dir(error);
      });
   }




   /**
    * @public
    * @method parseAndUploadSQL
    * @param fileName     {String}      The file name for the exported SQL data
    * @param fileName     {String}      The exported SQL data
    * @description        Posts the exported SQL data to the remote PHP script using Angular's HttpClient module
    * @return {none}
    */
   parseAndUploadSQL(fileName : string, sqlData : any)
   {
      let headers 	: any		= new HttpHeaders({ 'Content-Type': 'application/octet-stream' }),
          options 	: any		= { "name" : fileName, "data" : sqlData };

      this._HTTP
      .post(this._REMOTE_URI, JSON.stringify(options), headers)
      .subscribe((res : any) =>
      {
         console.dir(res);       
      },
      (error : any) =>
      {
         console.dir(error);
      });
   }


}

With the logic in place all that remains now is to add the necessary templating for the HomePage component.

Defining the View

The required markup for the ionic-importa/src/pages/home/home.html template is relatively simple and consists of the following elements:

  • A data import button (which triggers an alert box offering the user the choice of importing their data as either SQL or JSON)
  • Displaying existing records from the SQLite database table
  • A data export button (which triggers an alert box allowing the user to export their SQLite database structure/records in the form of SQL)

All of which is structured in the following way:

<ion-header>
  <ion-navbar>
    <ion-title>
      Ionic Importa
    </ion-title>
  </ion-navbar>
</ion-header>

<ion-content padding>
  

   <button 
      ion-button 
      block 
      color="primary" 
      (click)="import()">Import your data</button>


  <ion-list *ngIf="hasData">
  	<ion-card 
  	   padding 
  	   *ngFor="let technology of technologies">
  		<h2>{{ technology.name }}</h2>
  		<p>{{ technology.description }}</p>
  	</ion-card>
  </ion-list>


  <button *ngIf="hasData"  
      ion-button 
      margin-top 
      block 
      color="primary" 
      (click)="exportAlert()">Export your data</button>

</ion-content>

As you can see this is relatively lean and simple (so we won't need to deconstruct what is happening at each stage as this should be fairly self-explanatory from the markup structure) which means we can now turn our attention towards the PHP scripting that handles writing our exported SQL data to a SQL file.

Server side magic

With a liberal sprinkling of PHP we're going to take the posted SQL string data from the Ionic application and write that to a SQL file in a specified subdirectory (named uploads) on our remotely hosted server - courtesy of the following file handling functions provided by the language:

  • fopen - Opens a file or URL
  • fwrite - Writes the contents of a string to the file stream specified by the fopen function
  • fclose - Closes the file handle specified with fopen

Our script - parse-data.php - will handle the receipt and parsing of the posted data, file handling and conversion as well as informing the user of the success/failure of the operation:

<?php

   // Retrieve posted data
   $posted        = file_get_contents("php://input");

   // Decode from JSON
   $obj           =  json_decode($posted);

   // Retrieve the file name and string data
   $fileName      =  strip_tags($obj->name);
   $fileData      =  strip_tags($obj->data);



   try {

      // Open a file stream/pointer
      $handler       = fopen('uploads/' . $fileName, "a+");


      // Attempt to write the string data to the specified file pointer
      if(!fwrite($handler, $fileData))
      {
         echo json_encode(array('message' => 'Error! The supplied data was NOT written to ' . $fileName));
      }

      // If all has gone well attempt to close the file stream
      if(!fclose($handler))
      {
         echo json_encode(array('message' => 'Error! The file was not closed properly!'));
      }

      // If we get this far the operation has succeeded - let the user know :)
      echo json_encode(array('message' => 'The file ' . $fileName . ' was successfully uploaded'));

   } 
   catch(Exception $e)
   {
      echo json_encode(array('message' => 'Fail!'));
   }


?>

That should be all you need with regards to processing such exports with PHP but if you find yourself running into CORS related errors please view the following resource for guidance on how to resolve such issues.

If your PHP script returns errors relating to Content-Type headers and/or allowed methods then consider adding the following to the top of your script:

header('Access-Control-Allow-Methods: GET, PUT, POST, DELETE, OPTIONS');
header('Access-Control-Allow-Headers: Content-Type, Content-Range, Content-Disposition, Content-Description');

Before we wrap up

At this point you may have the following question - why didn't I use the Ionic Native File plugin to generate the SQL file followed by the File Transfer plugin to upload that to the PHP script/remote server?

There are two reasons why I excluded their usage.

Firstly, I kept encountering errors with uploading the file when using the File Transfer plugin. No matter what I configured on the server (I.e. Cors access, headers for specifying the Content-Type and allowed methods, directory permissions, PHP settings etc) these errors just wouldn't go away - which made persisting with the File Transfer plugin a headache I wasn't prepared to continue enduring.

Secondly, why go to all the trouble of using plugins IF simpler and quicker alternatives are available (I.e. exporting the data to be posted as a string instead of a binary file and simply uploading that using the post method of the Angular HttpClient module)?

Make of my rationale what you will (and if any of my readers DO know of solutions to upload issues with the File Transfer plugin let me know!!)

In summary

Importing SQL data to an Ionic application is pretty straightforward thanks to the methods supplied by the SQLite Porter plugin. Simply choose the correctly formatted SQL or JSON file as your data source and the plugin will handle building your database structure and populating the created tables with the relevant data.

One thing that I DELIBERATELY neglected to do with this tutorial was check for network connectivity before requesting the remote data file to be imported. This is a pretty important step to take when downloading remote assets for your applications and I've left this for you as functionality to implement in such scenarios.

Hopefully you found this tutorial useful and can take the techniques and approaches that were covered and apply them within your own projects.

Please feel free to share your thoughts, issues or general feedback using the comment section below.

If you enjoyed what you read here and are interested in receiving further updates on new articles and forthcoming e-books then please consider signing up to my FREE mailing list.

Tags

Categories

Post a comment

All comments are welcome and the rules are simple - be nice and do NOT engage in trolling, spamming, abusiveness or illegal behaviour. If you fail to observe these rules you will be permanently banned from being able to comment.

Top