Ionic Framework

Easy way to work with SQLite database in Ionic Framework

เรื่องที่เกี่ยวข้อง - , ,

Due to critical change in SQLite plugin, I updated the project to fix error about openDatabase() and openDB(). please read instruction in repo carefully

Before go to code… you need to know these things.

For every web developer who just settled on Ionic Framework: Your database in mobile application is similar to Database server, but not exactly the same:

  1. SQLite database is file format, you can thing about Microsoft Access.
  2. Database file won’t run all the time like the server ones does. It will be connected and execute SQL statement, then close the connection when it’s finished.
  3. If people uninstall the application, the database will gone.

So for best practice. You shouldn’t keep important data in local (app) database, but should commit it to web service to maintain data with database server instead.

People assume you’ll keep their data in the server (or Cloud, ah whatever) so they’ll remove your application without any worry.

So let’s start using SQLite database

I put an example, clean, very easy to build Ionic project to demonstrate how to work with SQLite database in my Github. The explanation is going below.

You can create your Ionic project then follow the instruction. I assume you already install ngCordova into your project already.

1. Install SQLite Plugin

First of all, run this command to install SQLite plugin to your project

cordova plugin add https://github.com/brodysoft/Cordova-SQLitePlugin.git

then put ng-cordova.min.js  into your index.html  file.

Noted that ng-cordova.min.js  must be included before cordova.js .

 <script src="lib/ngCordova/dist/ng-cordova.min.js"></script>
 <script src="cordova.js"></script>

2. Create a ‘db’ instance

In this example, we will create an instance (yes, a variable) to manage SQLite’s connection and execute SQL statement.

We named it as ‘db‘ then put it to the top of app.js .

// Database instance.
var db;

3. Create/Connect to database file

ok, let start using $cordovaSQLite  to create/open connection to our database file. By using $cordovaSQLite.openDB()  in run()  method

// Include dependency: ngCordova
angular.module('starter', ['ionic', 'ngCordova'])

.run(function($ionicPlatform, $cordovaSQLite) {
    $ionicPlatform.ready(function() {

        // Important!!
        // 
        // Instantiate database file/connection after ionic platform is ready.
        // 
        db = $cordovaSQLite.openDB({name:"nextflow.db", location:'default'});
        $cordovaSQLite.execute(db, 'CREATE TABLE IF NOT EXISTS Messages (id INTEGER PRIMARY KEY AUTOINCREMENT, message TEXT)');

    });
})

So we named our first database file as nextflow.db .

// Include dependency: ngCordova
angular.module('starter', ['ionic', 'ngCordova'])

.run(function($ionicPlatform, $cordovaSQLite) {
    $ionicPlatform.ready(function() {

        // Important!!
        // 
        // Instantiate database file/connection after ionic platform is ready.
        // 
        db = $cordovaSQLite.openDB("nextflow.db");
        $cordovaSQLite.execute(db, 'CREATE TABLE IF NOT EXISTS Messages (id INTEGER PRIMARY KEY AUTOINCREMENT, message TEXT)');

    });
})

Then we execute a SQL statement to create a table in our database.

// Include dependency: ngCordova
angular.module('starter', ['ionic', 'ngCordova'])

.run(function($ionicPlatform, $cordovaSQLite) {
    $ionicPlatform.ready(function() {

        // Important!!
        // 
        // Instantiate database file/connection after ionic platform is ready.
        // 
        db = $cordovaSQLite.openDB("nextflow.db");
        $cordovaSQLite.execute(db, 'CREATE TABLE IF NOT EXISTS Messages (id INTEGER PRIMARY KEY AUTOINCREMENT, message TEXT)');

    });
})

4. Create a web form to get input

So we are ready to save our data into local database. We are going to put a input element, with 2 buttons, Save and Load in index.html

<ion-content padding="true">
    <div class="list">
        <label class="item item-input">
            <span class="input-label">Message: </span>
            <input type="text" ng-model="newMessage">
        </label>
    </div>
    <div>
        <button class="button button-block" ng-click="save(newMessage)">
            Save
        </button>
        <button class="button button-block" ng-click="load()">
            Load
        </button>
        <p>{{ statusMessage }}</p>
    </div>
</ion-content>

5.Saving data to SQLite database

We are creating a method in controller, save()  method.

So Save button can invoke it and execute an INSERT statement. By using execute() method of $cordovaSQLite .

You’ll feel familiar with SQL statement here. Because SQLite use the SQL standard similar to MySQL, MS SQL Server, and Oracle.

$scope.save = function(newMessage) {

    $cordovaSQLite.execute(db, 'INSERT INTO Messages (message) VALUES (?)', [newMessage])
        .then(function(result) {
            $scope.statusMessage = "Message saved successful, cheers!";
        }, function(error) {
            $scope.statusMessage = "Error on saving: " + error.message;
        })

}

6. Load data from SQLite database

For Load button, we create load()  method. Now we are going to execute SQL statement with $cordovaSQLite  again, but this time, it’s SELECT statement.

$scope.load = function() {

        // Execute SELECT statement to load message from database.
        $cordovaSQLite.execute(db, 'SELECT * FROM Messages ORDER BY id DESC')
            .then(
                function(result) {

                    if (result.rows.length > 0) {

                        $scope.newMessage = result.rows.item(0).message;
                        $scope.statusMessage = "Message loaded successful, cheers!";
                    }
                },
                function(error) {
                    $scope.statusMessage = "Error on loading: " + error.message;
                }
            );
    }

Noted that the value from database will come in result.rows .

$scope.load = function() {

        // Execute SELECT statement to load message from database.
        $cordovaSQLite.execute(db, 'SELECT * FROM Messages ORDER BY id DESC')
            .then(
                function(result) {

                    if (result.rows.length > 0) {

                        $scope.newMessage = result.rows.item(0).message;
                        $scope.statusMessage = "Message loaded successful, cheers!";
                    }
                },
                function(error) {
                    $scope.statusMessage = "Error on loading: " + error.message;
                }
            );
    }

and we can access record’s value as an object’s property.

$scope.load = function() {

        // Execute SELECT statement to load message from database.
        $cordovaSQLite.execute(db, 'SELECT * FROM Messages ORDER BY id DESC')
            .then(
                function(result) {

                    if (result.rows.length > 0) {

                        $scope.newMessage = result.rows.item(0).message;
                        $scope.statusMessage = "Message loaded successful, cheers!";
                    }
                },
                function(error) {
                    $scope.statusMessage = "Error on loading: " + error.message;
                }
            );
    }

7. OK, let run this Ionic app

So everything should ready now. Build the project then run this on your iOS or Android device.

Please noted that you need to ‘close‘ the application’s process to test that data still exists after you reopen the application.

 

You can see all code in Project ‘One Word Save’ in my Github repository.

Cheers to Ionic team and great framework!

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Menu