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:
- SQLite database is file format, you can thing about Microsoft Access.
- 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.
- 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!