How To add SQL database to store data in Cordova?

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.

Recent Post

  • Reinforcement Learning: From Q-Learning to Deep Q-Networks

    In the ever-evolving field of artificial intelligence (AI), Reinforcement Learning (RL) stands as a pioneering technique enabling agents (entities or software algorithms) to learn from interactions with an environment. Unlike traditional machine learning methods reliant on labeled datasets, RL focuses on an agent’s ability to make decisions through trial and error, aiming to optimize its […]

  • Understanding AI Predictions with LIME and SHAP- Explainable AI Techniques

    As artificial intelligence (AI) systems become increasingly complex and pervasive in decision-making processes, the need for explainability and interpretability in AI models has grown significantly. This blog provides a comprehensive review of two prominent techniques for explainable AI: Local Interpretable Model-agnostic Explanations (LIME) and Shapley Additive Explanations (SHAP). These techniques enhance transparency and accountability by […]

  • Building and Deploying a Custom Machine Learning Model: A Comprehensive Guide

    Machine Learning models are algorithms or computational models that act as powerful tools. Simply put, a Machine Learning model is used to automate repetitive tasks, identify patterns, and derive actionable insights from large datasets. Due to these hyper-advanced capabilities of Machine Learning models, it has been widely adopted by industries such as finance and healthcare.  […]

  • Mastering Conversational UX: Best Practices for AI-Driven Chatbots

    In today’s digital landscape, where customer engagement reigns supreme, traditional marketing strategies are giving way to more interactive and personalized approaches. The rise of conversational interfaces, often powered by Artificial Intelligence (AI) and Natural Language Processing (NLP), has transformed how businesses interact with their audiences. Whether through AI-driven chatbots on websites, virtual assistants on mobile […]

  • Mastering React Hooks for Infinite Scroll: An Advanced Tutorial

    Discover how to implement infinite scroll in React using the power of Hooks for seamless data loading. This advanced tutorial walks you through creating a dynamic user experience with real-world examples and best practices. What is Infinite Scrolling? Infinite scrolling is a widespread interaction design pattern you might’ve noticed in popular apps such as Instagram, […]

  • Advantages of Permissioned Blockchains for Efficiency, Security, and Collaboration

    In the last decade, blockchain has emerged as a robust technology in the digital landscape. Blockchains are continuously transforming various industries by redefining data management, data security, and decentralized collaboration. Blockchain gained popularity with the emergence of cryptocurrencies. Let’s take a look back to the year 2017 when Japan recognized Bitcoin as a legal currency […]

Click to Copy