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

  • LLMOps Essentials: A Practical Guide To Operationalizing Large Language Models

    When you engage with ChatGPT or any other Generative AI tool, you just type and enter your query and Tada!! You get your answer in seconds. Ever wondered how it happens and how it is so quick? Let’s peel back the curtain of the LLMs a bit. What actually happens behind the screen is a […]

  • Building Intelligent AI Models For Enterprise Success: Insider Strategies 

    Just picture a world where machines think and learn like us. It might sound like a scene straight out of a sci-fi movie, right? Well, guess what? We are already living in that world now. Today, data, clever algorithms, and AI models are changing the way businesses operate. AI models are serving as a brilliant […]

  • Introducing Google Vids in Workspace: Your Ultimate AI-Powered Video Creation Tool

    Hey there, fellow content creators and marketing gurus! Are you tired of drowning in a sea of emails, images, and marketing copy, struggling to turn them into eye-catching video presentations? Fear not, because Google has just unveiled its latest innovation at the Cloud Next conference in Las Vegas: Google Vids- Google’s AI Video Creation tool! […]

  • Achieve High ROI With Expert Enterprise Application Development

    Nowadays modern-day enterprises encounter no. of challenges such as communication breakdown, inefficient business processes, data fragmentation, data security risks, legacy system integration with modern applications, supply chain management issues, lack of data analytics and business intelligence, inefficient customer relationship management, and many more. Ignoring such problems within an organization can adversely impact various aspects of […]

  • State Management with Currying in React.js

    Dive into React.js state management made easy with currying. Say goodbye to repetitive code and hello to streamlined development. Explore the simplicity and efficiency of currying for your React components today!

  • How Much Does It Cost to Develop an App in 2024?

    The price of bringing your app to life typically ranges from $20,000 to $200,000. This cost varies based on factors like which platform you choose, the complexity of features you want, and the size of your intended audience. However, costs can climb even higher for more complex projects, reaching up to $350,000.