Cordova is a popularly used Framework that facilitates developers to build cross-platform mobile applications with web technologies. But one of the main issues that mobile applications face is the Internet connectivity. It is necessary to address the offline and online nature of the app for a smooth user experience. In order to do so, the popular approach is to store the important data that the application needs to work better. One popular approach is to use local storage by integrating an SQL database in the Cordova Application. It is a reliable approach to store and manage data efficiently. This guide will guide you to add an SQL database to your Cordova project for storing and managing data.
Steps for SQL Database Integration
Before we go further, make sure you have the basic Cordova setup and a working project. If not, you can see our blog which guides you through setting up a Cordova project. //link for the Cordova setup blog.
- Open the Cordova folder in your Cordova project.
- Install the SQLite plugin by running the following syntax:
cordova plugin add cordova-sqlite-storage
- Initialize the SQLite Database in the deviceReady event. This plugin can be accessed as window.sqlitePlugin in the project.
document.addEventListener("deviceready", () =>{
window.db = window.sqlitePlugin.openDatabase({name: "mySql.db", location: "default"},function(){
console.log('Populated database OK');
}, function(err){
console.log("Error" , err);
}
);
});Code language: JavaScript (javascript)We have initialized the SQL db in device device-ready event at the start of the application. Also, we have initialized the DB in a window object so that it can be accessed globally throughout the application.
- Now that the DB has been initialized, you can interact with it and can create tables according to your needs. For Example, we will be creating a user table that will store the name and age of the user.
window.db.transaction(function(tx) {
tx.executeSql('CREATE TABLE IF NOT EXISTS UserTable(name text, age integer)')
}, function(error) {
console.log('ERROR: ' + error.message);
}, function() {
console.log('created user table’);
});Code language: PHP (php)In the above code, we are accessing the database using a window. db and are performing a transaction to create a table “user table” using the executeSql command.
The Sqlite plugin can perform similar transactions just like we do on SQL like Create, Update, Drop, Alter, etc. You can perform any command according to your needs and the project requirements.
- We will perform another transaction to insert entries in the user table using the Insert command.
function insertData(name , age){
window.db.transaction(
function(tx) {
tx.executeSql(
"INSERT INTO UserTable VALUES (?1,?2)",
[name, age]
);
},
function(error) {
console.log("ERROR: " + error);
}
);
}Code language: JavaScript (javascript)The above code has a function that takes name and age as parameters and inserts them into the user’s table.
- We can fetch the data from the table using the Select command.
fetchData() {
let items;
window.db.transaction(function (tx) {
tx.executeSql(
"SELECT * FROM UserTable",
[],
function (tx, rs) {
for (var i = 0; i < rs.rows.length; i++) {
var record = rs.rows.item(i);
console.log("Record " + (i + 1) + ": " + JSON.stringify(record));
items.push(JSON.stringify(record.assetItem));
}
}
);
},function (tx, error) {
console.log("ERROR: " + error.message);
});
}Code language: JavaScript (javascript)In the above code, we have a fetchData function that basically returns an array of the records inserted in the user’s table. We fetched all the data entries in the user table using *(means all).
We then pushed the records of the table into an array called items and returned it.
Similarly, you can use other SQL queries like update delete, or alter in the same way we use in normal SQL.
Conclusion
By adding an SQL plugin to your Cordova app, we opened possibilities to store data locally and access it. By adding this functionality in your app, data can now be fetched in offline mode as well by storing essential data in the SQL database locally and using it when there’s no internet and later can be updated or stored in your actual database whenever the device gets the internet connection back. Thus we finally learned how we can use SQLite storage in the Cordova app and how we can add and fetch data in the SQL table.