Ionic 5 SQLite Database CRUD Offline Mobile App Tutorial

 Ionic 5 Angular SQLite database tutorial; throughout this step-by-step guide, you will find out how to create SQLite CRUD operation in the Ionic Angular app with the SQLite package’s help.

Ideally, SQLite is robust database exclusively used to create database system in smaller devices which allow us to perform SQL queries and build a simple app with CRUD operations.

Generically, in this quick guide, we will demonstrate how to create an SQLite database, create a table and insert into device database, add or insert data into the table, fetch, get or read data from the table, delete a row from the table, and update the row from the table in ionic sqlite offline mobile app.

Ionic 5 SQLite Offline CRUD Mobile App Example

• Step 1: Configure Ionic Environment
• Step 2: Update Routes
• Step 3: Install SQLite Plugin
• Step 4: Update App Module Class
• Step 6: Create SQLite CRUD Service
• Step 7: Implement Create and Delete
• Step 8: Get Data Collection and Display List
• Step 9: Implement Update or Edit
• Step 10: Test Ionic Crud App

Configure Ionic Environment

Ionic CLI is a must-have tool; therefore, begin with installing it. Run the command to launch the installation process:

npm install -g @ionic/cli

or

npm install -g cordova ionic

Bash

Next, install the new blank ionic angular app:

ionic start ionic-sqlite-crud blank --type=angular

Bash

Head over to project folder:

cd ionic-sqlite-crud

Bash

Next, generate ionic pages or components with the help of the suggested command. Ensure to delete the Home page because we don’t need that.

ng generate page create

ng generate page edit

Bash

Update Routes

Next, head over to the app routing file; add the id property in the edit route considerably set the create as a default route.

Update app-routing.module.ts file:

import { NgModule } from '@angular/core';
import { PreloadAllModules, RouterModule, Routes } from '@angular/router';

const routes: Routes = [
  {
    path: '',
    redirectTo: 'create',
    pathMatch: 'full'
  },
  {
    path: 'create',
    loadChildren: () => import('./create/create.module').then( m => m.CreatePageModule)
  },  
  {
    path: 'edit/:id',
    loadChildren: () => import('./edit/edit.module').then( m => m.EditPageModule)
  },
];

@NgModule({
  imports: [
    RouterModule.forRoot(routes, { preloadingStrategy: PreloadAllModules })
  ],
  exports: [RouterModule]
})

export class AppRoutingModule { }

TypeScript

Install SQLite Plugin

This is the most quintessential step; as per the instruction, we need to install the SQLite plus Ionic native core plugins.

Open the terminal, type command and then execute.

npm install @ionic-native/sqlite

ionic cordova plugin add cordova-sqlite-storage

npm i @ionic-native/core

Bash

Update App Module Class

Further, we need to import the SQLite plugin into the app module class, and it gives us access to its methods and functions to use throughout the app.

Update app.module.ts file:

import { NgModule } from '@angular/core';
import { BrowserModule } from '@angular/platform-browser';
import { RouteReuseStrategy } from '@angular/router';

import { IonicModule, IonicRouteStrategy } from '@ionic/angular';

import { AppComponent } from './app.component';
import { AppRoutingModule } from './app-routing.module';

// plugins
import { SQLite } from '@ionic-native/sqlite/ngx';

@NgModule({
  declarations: [AppComponent],
  entryComponents: [],
  imports: [BrowserModule, IonicModule.forRoot(), AppRoutingModule],
  providers: [
    SQLite,
    { 
      provide: RouteReuseStrategy, 
      useClass: IonicRouteStrategy 
    }
  ],
  bootstrap: [AppComponent],
})

export class AppModule {}

TypeScript

Create SQLite CRUD Service

Angular service is better for code manageability and reusability paradigm. Consequently, generate the service for ionic offline crud mobile app using the following command.

ng g service crud

Bash

In the next step, we have to create and add, read, update and delete methods. Most importantly, we have to invoke the SQLite database connection in the Ionic app from the ionic, angular service file.

The sqlite offers create method, which takes database name and location and initializes the SQLite database connection.

Above command manifested crud.service.ts file, open it and update the suggested code within the file.

import { Injectable } from '@angular/core';
import { Platform } from '@ionic/angular';
import { SQLite, SQLiteObject } from '@ionic-native/sqlite/ngx';

@Injectable({
  providedIn: 'root'
})

export class CrudService {
  
  private dbInstance: SQLiteObject;
  readonly db_name: string = "remotestack.db";
  readonly db_table: string = "userTable";
  USERS: Array <any> ;

  constructor(
    private platform: Platform,
    private sqlite: SQLite    
  ) { 
    this.databaseConn();
  }

    // Create SQLite database 
    databaseConn() {
        this.platform.ready().then(() => {
          this.sqlite.create({
              name: this.db_name,
              location: 'default'
            }).then((sqLite: SQLiteObject) => {
              this.dbInstance = sqLite;
              sqLite.executeSql(`
                  CREATE TABLE IF NOT EXISTS ${this.db_table} (
                    user_id INTEGER PRIMARY KEY, 
                    name varchar(255),
                    email varchar(255)
                  )`, [])
                .then((res) => {
                  // alert(JSON.stringify(res));
                })
                .catch((error) => alert(JSON.stringify(error)));
            })
            .catch((error) => alert(JSON.stringify(error)));
        });   
    }

    // Crud
    public addItem(n, e) {
      // validation
      if (!n.length || !e.length) { 
        alert('Provide both email & name');
        return;
      }
      this.dbInstance.executeSql(`
      INSERT INTO ${this.db_table} (name, email) VALUES ('${n}', '${e}')`, [])
        .then(() => {
          alert("Success");
          this.getAllUsers();
        }, (e) => {
          alert(JSON.stringify(e.err));
        });
    }

    getAllUsers() {
      this.dbInstance.executeSql(`SELECT * FROM ${this.db_table}`, []).then((res) => {
        this.USERS = [];
        if (res.rows.length > 0) {
          for (var i = 0; i < res.rows.length; i++) {
            this.USERS.push(res.rows.item(i));
          }
          return this.USERS;
        }
      },(e) => {
        alert(JSON.stringify(e));
      });
    }

    // Get user
    getUser(id): Promise<any> {
      return this.dbInstance.executeSql(`SELECT * FROM ${this.db_table} WHERE user_id = ?`, [id])
      .then((res) => { 
        return {
          user_id: res.rows.item(0).user_id,
          name: res.rows.item(0).name,  
          email: res.rows.item(0).email
        }
      });
    }

    // Update
    updateUser(id, name, email) {
      let data = [name, email];
      return this.dbInstance.executeSql(`UPDATE ${this.db_table} SET name = ?, email = ? WHERE user_id = ${id}`, data)
    }  

    // Delete
    deleteUser(user) {
      this.dbInstance.executeSql(`
      DELETE FROM ${this.db_table} WHERE user_id = ${user}`, [])
        .then(() => {
          alert("User deleted!");
          this.getAllUsers();
        })
        .catch(e => {
          alert(JSON.stringify(e))
        });
    }

}

TypeScript

We have to perform multiple tasks using service file, first import Platform, SQLite and SQLiteObject modules. These are essential for creating and setting up the SQLite database connection when the platform is ready.

To handle CRUD operations we are using SQLite, that allows writing MYSql queries with executeSql() method. Moreover, we created SQLite database connection, created database, created and inserted table into the database. Also, addItem(), getAllUsers(), getUser(), updateUser() and deleteUser() for handling CRUD events.

Implement Create and Delete

In this step, you we will learn to implement how to create and display list feature in ionic page using the SQLite.

In this step, we will add a basic form to let users enter data such as name and email using ngModel. Use CrudService by importing and adding into the constructor method, access methods creating user, fetching users list from the SQLite database and deleting user object from the SQLite db.

Add code in create.page.ts file:

import { Component, OnInit } from '@angular/core';
import { CrudService } from '../crud.service';

@Component({
  selector: 'app-create',
  templateUrl: './create.page.html',
  styleUrls: ['./create.page.scss'],
})

export class CreatePage implements OnInit {

  nameVal: string = "";
  emailVal: string = "";

  constructor(
   private crud: CrudService
  ) {
    this.crud.databaseConn(); 
  }

  ngOnInit() { }

  ionViewDidEnter() {  
    this.crud.getAllUsers()
  }
   
  createUser(){
    this.crud.addItem(this.nameVal, this.emailVal);
  }
   
  remove(user) {
    this.crud.deleteUser(user);
  }
  
}

TypeScript

To display the data collection, use the ngFor directive to iterate over the USERS collection and show it into the ionic item UI component.

Add code in create.page.html file:

<ion-header>
  <ion-toolbar>
    <ion-title>Ionic 5 SQLite Storage Example</ion-title>
  </ion-toolbar>
</ion-header>

<ion-content>

  <!-- Create -->
  <ion-item>
    <ion-label position="floating">Name</ion-label>
    <ion-input [(ngModel)]="nameVal"></ion-input>
  </ion-item>
  
  <ion-item>
    <ion-label position="floating">Email</ion-label>
    <ion-input [(ngModel)]="emailVal"></ion-input>
  </ion-item>

  <ion-button color="success" expand="block" (click)="createUser()">
    Add User
  </ion-button>

  
  <!-- Read -->
  <ion-item *ngFor="let user of crud.USERS">
    <ion-label>
      <h2><strong>{{ user.name }}</strong></h2>
      <p>{{ user.email }}</p>
    </ion-label>

    <div class="item-note" item-end>
      <ion-icon color="primary" name="create" style="zoom:1.3" [routerLink]="['/edit/', user.user_id]"></ion-icon>
      
      <ion-icon color="danger" name="trash" style="zoom:1.3" (click)="remove(user.user_id)"></ion-icon>
    </div>
  </ion-item>
  
</ion-content>

Markup

Implement Update or Edit

Lastly, we will implement the edit or update feature so make sure to create the form and insert the blog values by using the angular service.

Additionally, we used the ActivatedRoute api to get the user id from the url, and it is being passed to the getUser() method. This is getting the single user object from the SQLite database, likewise using the updateUser() method to update the user data.

Update edit.page.ts file:

import { Component, OnInit } from '@angular/core';
import { CrudService } from '../crud.service';
import { ActivatedRoute, Router } from "@angular/router";

@Component({
  selector: 'app-edit',
  templateUrl: './edit.page.html',
  styleUrls: ['./edit.page.scss'],
})

export class EditPage implements OnInit {
  id: any;
  nameVal: string = "";
  emailVal: string = "";

  constructor(
    private router: Router,
    private activatedRoute: ActivatedRoute,
    private crud: CrudService
  ) { 
    this.id = this.activatedRoute.snapshot.paramMap.get('id');

    this.crud.getUser(this.id).then((res) => {
      this.nameVal = res['name'];
      this.emailVal = res['email']; 
    })
  }

  ngOnInit() { }

  onUpdate() {
     this.crud.updateUser(this.id, this.nameVal, this.emailVal).then(() => {
        this.router.navigate(['/create']);
     })
  }

}

TypeScript

Open and update edit.page.html file:

<ion-header>
  <ion-toolbar>
    <ion-buttons slot="start">
      <ion-back-button></ion-back-button>
    </ion-buttons>
    <ion-title>Edit</ion-title>
  </ion-toolbar>
</ion-header>

<ion-content>
  <ion-item>
    <ion-label position="floating">Name</ion-label>
    <ion-input [(ngModel)]="nameVal"></ion-input>
  </ion-item>

  <ion-item>
    <ion-label position="floating">Email</ion-label>
    <ion-input [(ngModel)]="emailVal"></ion-input>
  </ion-item>

  <ion-button color="dark" expand="block" (click)="onUpdate()">
    Update
  </ion-button>
</ion-content>

Markup

Test Ionic Crud App

In the eventual step, you need to follow the recommended instructions to start the ionic crud example app:

Include the platform:

# iOS
ionic cordova platform add ios

# Android
ionic cordova platform add android

Bash

Thereafter, create the runnable build:

# iOS
ionic cordova build ios

# Android
ionic cordova build android

Bash

Ultimately, run the app on the device:

# iOS
ionic cordova run ios -l

# Android
ionic cordova run android -l

Bash

Conclusion

So this was it; the Ionic 5 SQLite CRUD mobile app tutorial is done for now; In this example, we described how to create an Ionic CRUD mobile app using SQLite database plugins. Implementing the SQLite database in the Ionic app gives easy access to SQLite database methods that we can use to store and manage the data for building offline mobile apps.

Not just that, we also comprehended the crud app building process step-by-step. That consists of creating an app, installing plugins, setting up plugins to access database methods, creating the angular service for reusable code, and implementing the crud operations in ionic pages.

Download the project code from GitHub.