Ionic 4 | Complete SQLite CRUD Tutorial for Ionic 4 Angular Applications


Storage of data on a user device is an important requirement for mobile applications. For a hybrid application like Ionic, we have WebViews where we can access browser storage available in the form of Local or Session storage. But these have limitations in terms of availability of memory which can store only 10mb of data.

In Ionic we have a Native Plugin SQLite which can be easily integrated with the application and have no limitation on storage of data, it depends on device memory.

SQLite is SQL based database where we can easily create databases, tables, and store or retrieve complex data from it by running SQL queries.

SQL queries are very easy to understand and complex transactions on data can be done smoothly.

In this article, we will discuss How to Integrate SQLite Native plugin in Ionic 4 application and also create a demo application which will Create Database, Create a new Table, Insert Rows, Retrieve Rows, Delete Rows from a table.

Let’s get started with a new Ionic Application

Create a new Ionic 4 Application

To start we will create a new blank application using Ionic CLI. Here we will use the latest version of CLI v.5.2.0

Make sure to update it by running following NPM command

Install SQLite Cordova and Native Plugin

To use SQLite services in the application run following NPM commands to install Cordova and Ionic’s Native wrapper.

Add SQLite in Module

Next, we will include SQLite by importing it into app’s main module file then add in providers array. Replace the following code in app.module.ts file.

Use SQLite in Application

Now we are ready with the configuration part. Let’s implement it in our one of the component.

In Home component, we will create a new SQLite Database then create a new Table in it.

In component (home.page.ts)  import SQLite and SQLiteObject module then add in component’s class constructor as shown below:

Now add some variables to keep some values required in component class for Database transactions.

SQLite has two important methods which are used during database transactions.

create(): Used to create a new Database with provided name and location. Returns promise.

executeSql(): After creating Database, run SQL queries using this method.  We will Create a table, Insert New Rows, Get all table rows and Delete rows based on ID using this method.

Create Table in SQLite

To Create a new table we will use following SQL query

IF NOT EXISTS is used to check if a table already exists in DB, if yes it will ignore the query.

pid is the PRIMARY KEY of type INTEGER

Name is another column to save Varchar type value.

You can read more on Create table SQL query here

createTable() method in component class will create a new table

Insert New Row in Table

To insert a new row run following SQL query

Here we have comma separated column names, Primary key is not added as it will be autoincremented by self. VALUES takes inputs corresponding to colum names in the same order.
insertRow() method will take Input values then insert new row in the table.

Fetch/ Retrieve Table Rows

To get all rows from table use SELECT query

getRows() method will fetch all rows then loop over the result to push in row_data

Delete Row from SQLite table

Run following DELETE SQL query to remove a row with matching pid

deleteRow() method will take item object then delete a matching row from the table.

 

Let’s a look on the complete home.page.ts file

Home Page Template

Add some action buttons to Create Database and Table. An Ionic Grid will traverse table rows to show rows with a Delete action button. Also, take Name input from a user to insert new row in the table.

Replace following HTML template in the home.page.html file

That’s it now the app is ready to be run. Make sure to run in a real device as SQLite Database is available in the real native device.

Run following NPM command to run in USB connected device after adding platform Android.

The app will look like this


Thanks for visiting :) Let me know if you have some suggestions or feedback for this post in the comment section. Looking for any tutorial or demo on FreakyJolly, just drop me a message here

Leave a Reply

avatar
  Subscribe  
Notify of