Morioh
Login
Jade Bird

Jade Bird

4 years ago

How to Build an Ionic 5 CRUD App with SQLite Database

In this tutorial, we will learn how to create Ionic 5 Angular CRUD application and implement SQLite Native plugin to store the data in the SQLite Database. We will create the Create, Read, Update and Delete operation to manage the data in the database. Moreover, we will also learn to load the dummy data from the sql database file using HttpClient service.

SQLite is an open-source relational database i.e. used to perform database operations on android devices such as storing, manipulating or retrieving persistent data from the database. It is embedded in android by-default. So, there is no need to perform any database setup or administration task.

Here, we are going to see the example of sqlite to store and fetch the data. Data is displayed in the logcat. For displaying data on the spinner or listview, move to the next page. Storing data on a user device is an essential topic that we are going to cover in this Ionic 4 hybrid mobile app tutorial.

Table of Contents

  • Prerequisite
  • Install New Ionic 5 Application
  • Adding Fake SQL Data
  • Configure Routes
  • Install & Configure Packages – sqlite, sqlite-porter, cordova-sqlite-storage
  • Create CRUD Service with SQLite
  • Implement Reactive Forms in Ionic 5
  • Add, Display & Delete Data from SQLite Database
  • Update Data from SQLite Database
  • Run Ionic App in Device
  • Conclusion

Ionic 5 SQLite Database Example

Prerequisite

We must have following tools, frameworks, and packages to get started with this tutorial.

  • Latest Node
  • Ionic CLI
  • Ionic 5
  • Angular
  • Firebase FCM
  • Postman
  • Text Editor
  • @ionic-native/sqlit
  • Cordova SQLite Storage

Follow this tutorial on: How to Download and Install Node.js and npm

Install New Ionic 4 Application

We are going to work with Ionic 4 Native SQLite plugin, and It is a robust plugin for storing the data and primarily used with Ionic framework. It works on the same SQL pattern tables and rows. We write SQL queries to fetch the data from the database. It works on the user’s device memory, and the good thing is It doesn’t have storage limitation to store the data.

Install the latest version of Ionic CLI and Cordova globally installed on your device, run the below command.

sudo npm install -g cordova ionic

Run the following command to verify the Ionic CLI version.

ionic -v

# 5.4.15

Run the following command to create a brand new blank Ionic 4 Angular project.

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

Go inside the Ionic project directory.

cd ionic-sqlite-app

Start the app in the browser.

ionic serve --lab

Adding Fake SQL Data

Let’s add some dummy data, and we can add even data in JSON format; however, in this article, i will teach how to add SQL data.

Create a SQL file inside assets/dump.sql folder and insert the following data in it.

CREATE TABLE IF NOT EXISTS songtable(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    artist_name TEXT, 
    song_name TEXT
);

INSERT or IGNORE INTO songtable(id, artist_name, song_name) VALUES (1, 'Justin Bieber', 'Yummy');
INSERT or IGNORE INTO songtable(id, artist_name, song_name) VALUES (2, 'Jonas Brothers', 'What A Man Gotta Do');
INSERT or IGNORE INTO songtable(id, artist_name, song_name) VALUES (3, 'Life Is Good', 'Future');
INSERT or IGNORE INTO songtable(id, artist_name, song_name) VALUES (4, 'Lauv', 'Tattoos Together');
INSERT or IGNORE INTO songtable(id, artist_name, song_name) VALUES (5, 'Heavy Steppers', 'Whateva');
INSERT or IGNORE INTO songtable(id, artist_name, song_name) VALUES (6, 'DigDat 2020', 'Ei8ht Mile');
INSERT or IGNORE INTO songtable(id, artist_name, song_name) VALUES (7, 'Blackbear', 'me & ur ghost');
INSERT or IGNORE INTO songtable(id, artist_name, song_name) VALUES (8, 'Hailee Steinfeld', 'Wrong Direction');

As you can see, we defined the SQL table, in which there is an ID which is set to auto-increment. The artist_name and song_name values belong to the string data type.

Configure Routes

Now, we require to generate a page. On this page, we show the song data, and a user can perform the update operation as well. Secondly, we will make the small change for the song route in the app routing file.

Run the command from terminal to create the page.

ng generate page song

Open the app-routing.module.ts file and add the following code in it.

// app-routing.module.ts

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

const routes: Routes = [
  { path: '', redirectTo: 'home', pathMatch: 'full' },
  { path: 'home', loadChildren: () => import('./home/home.module').then( m => m.HomePageModule)},
  {
    path: 'song/:id',
    loadChildren: () => import('./song/song.module').then( m => m.SongPageModule)
  }
];

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

export class AppRoutingModule { }

We have completed almost every configuration required for this tutorial; let’s start writing the core logic for storing data in the SQLite database.

Install & Configure Packages – sqlite, sqlite-porter, cordova-sqlite-storage

Run the following commands to install the required packages such as sqlite-porter, cordova-sqlite-storage, @ionic-native/sqlite.

$ npm install @ionic-native/sqlite
$ ionic cordova plugin add cordova-sqlite-storage

$ npm install @ionic-native/sqlite-porter
$ ionic cordova plugin add uk.co.workingedge.cordova.plugin.sqliteporter

This SQLite Porter plugin is used to import and export to and from a SQLite database using either SQL or JSON.

Next, import and register the above plugins in the application’s main app module file, this will allow us to access all the methods and services of the SQLite Database.

Don’t forget to add the HttpClientModule as we will be making the HTTP request to render the data locally from the SQL data file.

Open app.module.ts file and place the following code inside of it.

// app.module.ts

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

import { IonicModule, IonicRouteStrategy } from '@ionic/angular';
import { SplashScreen } from '@ionic-native/splash-screen/ngx';
import { StatusBar } from '@ionic-native/status-bar/ngx';

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

// plugins
import { SQLite } from '@ionic-native/sqlite/ngx';
import { HttpClientModule } from '@angular/common/http';
import { SQLitePorter } from '@ionic-native/sqlite-porter/ngx';

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

export class AppModule {}

Create CRUD Service with SQLite

Run the following command to create a song class file.

ng g class services/song

Define the data types for the Song object in services/song.ts file.

export class Song {
  id: number;
  artist_name: string;
  song_name: string;
}

Angular service keeps all the essential functions and methods at one place that we don’t want to define in our application repetitively. Create a service file inside the service folder by using the command below.

ng generate service services/db

Open the services/db.service.ts file replace with the following code.

// db.service.ts

import { Injectable } from '@angular/core';
import { Platform } from '@ionic/angular';
import { Song } from './song';
import { HttpClient } from '@angular/common/http';
import { BehaviorSubject, Observable } from 'rxjs';
import { SQLitePorter } from '@ionic-native/sqlite-porter/ngx';
import { SQLite, SQLiteObject } from '@ionic-native/sqlite/ngx';

@Injectable({
  providedIn: 'root'
})

export class DbService {
  private storage: SQLiteObject;
  songsList = new BehaviorSubject([]);
  private isDbReady: BehaviorSubject<boolean> = new BehaviorSubject(false);

  constructor(
    private platform: Platform, 
    private sqlite: SQLite, 
    private httpClient: HttpClient,
    private sqlPorter: SQLitePorter,
  ) {
    this.platform.ready().then(() => {
      this.sqlite.create({
        name: 'positronx_db.db',
        location: 'default'
      })
      .then((db: SQLiteObject) => {
          this.storage = db;
          this.getFakeData();
      });
    });
  }

  dbState() {
    return this.isDbReady.asObservable();
  }

  fetchSongs(): Observable<Song[]> {
    return this.songsList.asObservable();
  }

    // Render fake data
    getFakeData() {
      this.httpClient.get(
        'assets/dump.sql', 
        {responseType: 'text'}
      ).subscribe(data => {
        this.sqlPorter.importSqlToDb(this.storage, data)
          .then(_ => {
            this.getSongs();
            this.isDbReady.next(true);
          })
          .catch(error => console.error(error));
      });
    }

  // Get list
  getSongs(){
    return this.storage.executeSql('SELECT * FROM songtable', []).then(res => {
      let items: Song[] = [];
      if (res.rows.length > 0) {
        for (var i = 0; i < res.rows.length; i++) { 
          items.push({ 
            id: res.rows.item(i).id,
            artist_name: res.rows.item(i).artist_name,  
            song_name: res.rows.item(i).song_name
           });
        }
      }
      this.songsList.next(items);
    });
  }

  // Add
  addSong(artist_name, song_name) {
    let data = [artist_name, song_name];
    return this.storage.executeSql('INSERT INTO songtable (artist_name, song_name) VALUES (?, ?)', data)
    .then(res => {
      this.getSongs();
    });
  }

  // Get single object
  getSong(id): Promise<Song> {
    return this.storage.executeSql('SELECT * FROM songtable WHERE id = ?', [id]).then(res => { 
      return {
        id: res.rows.item(0).id,
        artist_name: res.rows.item(0).artist_name,  
        song_name: res.rows.item(0).song_name
      }
    });
  }

  // Update
  updateSong(id, song: Song) {
    let data = [song.artist_name, song.song_name];
    return this.storage.executeSql(`UPDATE songtable SET artist_name = ?, song_name = ? WHERE id = ${id}`, data)
    .then(data => {
      this.getSongs();
    })
  }

  // Delete
  deleteSong(id) {
    return this.storage.executeSql('DELETE FROM songtable WHERE id = ?', [id])
    .then(_ => {
      this.getSongs();
    });
  }
}

Import required API at the top part of the database service file.

The Song class is imported, which allows us to define the data type for the Song object.

The SQLite database is created when the app is ready using the Platform API. In response, we are also loading the dummy song data by calling the getFakeData() method.

To get the current database state, we imported RxJS BehaviorSubject and Observable properties and can extract the current database state by merely subscribing to them.

The getFakeData() method renders the fake data from the dump.sql file. We access the dummy data by making the Http GET request and then subscribe to the response and also setting the database state to true.

The executeSql() function allows us to make SQL queries to interacts with the SQL database, and we are using it to Get, Send, Update, or Delete the data from the storage.

Implement Reactive Forms in Ionic 4

Working with Reactive Forms in Ionic requires to import and register ReactiveFormsModule API in every Ionic page that we are going to work with. For example, in this tutorial, we will need the form in the Home and Song page to add or update the data.

Open your home.module.ts and song.module.ts files and import ReactiveFormsModule in the header part and register this API in the imports array.

import { FormsModule, ReactiveFormsModule } from '@angular/forms';

@NgModule({
  imports: [
    ReactiveFormsModule,
    FormsModule
  ]
})

Add, Display & Delete Data from SQLite Database

Now, we will learn how to fetch, add and remove data from the SQLite database. Open the home.page.ts file and import the following services in the header section.

import { Component, OnInit } from '@angular/core';
import { FormGroup, FormBuilder } from "@angular/forms";
import { DbService } from './../services/db.service';
import { ToastController } from '@ionic/angular';
import { Router } from "@angular/router";

The FormGroup and FormBuilder are used to create the Reactive Form to get the user entered data.

  constructor(
    private db: DbService,
    public formBuilder: FormBuilder,
    private toast: ToastController,
    private router: Router
  ) { }

Inject the services in the constructor. Define Reactive Form by declaring the mainForm: FormGroup, to display the song data, also declare Data array.

mainForm: FormGroup;
Data: any[] = []

To show the data, we need to use the DbService that we injected in the constructor. Set the method in the ngOnInit life cycle hook.

Check the database state if its true then render the song list using the fetchSongs() method.

The mainForm is the main object that initiates the main form to add the data in the SQLite database.

ngOnInit() {
  this.db.dbState().subscribe((res) => {
    if(res){
      this.db.fetchSongs().subscribe(item => {
        this.Data = item
      })
    }
  });

  this.mainForm = this.formBuilder.group({
    artist: [''],
    song: ['']
  })
}

The storeData() method stores the data in the storage when a user clicks on the submit button.

storeData() {
  this.db.addSong(
    this.mainForm.value.artist,
    this.mainForm.value.song
  ).then((res) => {
    this.mainForm.reset();
  })
}

Here is the method to delete the data from the SQL database; we also used the Ionic Toast service to display the data delete the message when data is removed.

deleteSong(id){
  this.db.deleteSong(id).then(async(res) => {
    let toast = await this.toast.create({
      message: 'Song deleted',
      duration: 2500
    });
    toast.present();      
  })
}

Here is the completed code in the src/home.page.ts file.

// home.page.ts

import { Component, OnInit } from '@angular/core';
import { FormGroup, FormBuilder } from "@angular/forms";
import { DbService } from './../services/db.service';
import { ToastController } from '@ionic/angular';
import { Router } from "@angular/router";

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

export class HomePage implements OnInit {
  mainForm: FormGroup;
  Data: any[] = []

  constructor(
    private db: DbService,
    public formBuilder: FormBuilder,
    private toast: ToastController,
    private router: Router
  ) { }

  ngOnInit() {
    this.db.dbState().subscribe((res) => {
      if(res){
        this.db.fetchSongs().subscribe(item => {
          this.Data = item
        })
      }
    });

    this.mainForm = this.formBuilder.group({
      artist: [''],
      song: ['']
    })
  }

  storeData() {
    this.db.addSong(
      this.mainForm.value.artist,
      this.mainForm.value.song
    ).then((res) => {
      this.mainForm.reset();
    })
  }

  deleteSong(id){
    this.db.deleteSong(id).then(async(res) => {
      let toast = await this.toast.create({
        message: 'Song deleted',
        duration: 2500
      });
      toast.present();      
    })
  }

}

Open the home.page.html file and place the following code inside of it. It contains the form and song data list.

// home.page.html

<ion-header>
  <ion-toolbar>
    <ion-title>Ionic 4 SQLite CRUD Example</ion-title>
  </ion-toolbar>
</ion-header>

<ion-content>
  <ion-list lines="full">
    <form [formGroup]="mainForm" (ngSubmit)="storeData()">
      <ion-item>
        <ion-label position="floating">Artist name</ion-label>
        <ion-input formControlName="artist" type="text" required></ion-input>
      </ion-item>

      <ion-item>
        <ion-label position="floating">Song name</ion-label>
        <ion-input formControlName="song" type="text" required>
        </ion-input>
      </ion-item>

      <ion-row>
        <ion-col>
          <ion-button type="submit" color="primary" shape="full" expand="block">
            Add Song
          </ion-button>
        </ion-col>
      </ion-row>
    </form>
  </ion-list>

    <ion-list>
    <ion-list-header>
      Songs
    </ion-list-header>

    <ion-item lines="inset" *ngFor="let data of Data">
      <ion-label>
        <h5>{{data.artist_name}}</h5>
        <p>{{data.song_name}}</p>
      </ion-label>

      <div class="item-note" item-end>
        <ion-icon name="create" style="zoom:2.0" [routerLink]="['/song/', data.id]"></ion-icon>        
        <ion-icon name="trash" style="zoom:2.0" (click)="deleteSong(data.id)"></ion-icon>
      </div>
    </ion-item>
  </ion-list>
</ion-content>

We declared the click method and passed the deleteSong(id) method with the data-id.

Update Data from SQLite Database

Open the song.page.html file and replace with the following code.

// song.page.html

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

<ion-content>
  <ion-list lines="full">
    <form [formGroup]="editForm" (ngSubmit)="saveForm()">
      <ion-item>
        <ion-label position="floating">Artist name</ion-label>
        <ion-input formControlName="artist_name" type="text" required></ion-input>
      </ion-item>

      <ion-item>
        <ion-label position="floating">Song name</ion-label>
        <ion-input formControlName="song_name" type="text" required>
        </ion-input>
      </ion-item>

      <ion-row>
        <ion-col>
          <ion-button type="submit" color="primary" shape="full" expand="block">
            Update
          </ion-button>
        </ion-col>
      </ion-row>
    </form>
  </ion-list>
</ion-content>

Open the song.page.ts file and replace with the following code.

// song.page.ts

import { Component, OnInit } from '@angular/core';
import { FormGroup, FormBuilder } from "@angular/forms";
import { DbService } from './../services/db.service'
import { ActivatedRoute, Router } from "@angular/router";

@Component({
  selector: 'app-song',
  templateUrl: './song.page.html',
  styleUrls: ['./song.page.scss'],
})
export class SongPage implements OnInit {
  editForm: FormGroup;
  id: any;

  constructor(
    private db: DbService,
    private router: Router,
    public formBuilder: FormBuilder,
    private actRoute: ActivatedRoute
  ) {
    this.id = this.actRoute.snapshot.paramMap.get('id');

    this.db.getSong(this.id).then(res => {
      this.editForm.setValue({
        artist_name: res['artist_name'],
        song_name: res['song_name']
      })
    })
  }

  ngOnInit() {
    this.editForm = this.formBuilder.group({
      artist_name: [''],
      song_name: ['']
    })
  }

  saveForm(){
    this.db.updateSong(this.id, this.editForm.value)
    .then( (res) => {
      console.log(res)
      this.router.navigate(['/home']);
    })
  }

}

Set the form state when the Ionic page is loaded by using the getSong() method, it takes id as a parameter. We are extracting the song id using the ActivatedRoute API. The updateSong() method takes the song object and id to update the data in the SQLite database.

Run Ionic App in Device

To run the app in the Android device we need to use the following commands.

ionic cordova platform add android

ionic cordova run android --livereload

Conclusion

We have completed the Ionic 5 SQLite Database tutorial, in this tutorial, we learned how to create a CRUD application from scratch and store the data in the SQLite database. We learned to load the fake data from the SQL file locally using the HTTPClient API.

I hope you loved this tutorial and share it with others. You can get the complete code of this tutorial on this GitHub repository.

#angular #ionic #mobile-apps #databases #sql

How to Build an Ionic 5 CRUD App with SQLite Database
42.95 GEEK