Ionic SQLite Tutorial

In this tutorial, we're going to learn how to implement cordova-sqlite-stroage plugin with ionic with step by step tutorial

What you'll learn?

  • Create a Todo App
  • Complete CRUD operation using SQLite

Let's start

Let's create a new ionic project, then add the required platform and plugins.

ionic start todo blank
cd todo
ionic cordova plugin add cordova-sqlite-storage
npm install --save @ionic-native/sqlite

After plugin installation, we need to declare inside 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'; 

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

@NgModule({
  declarations: [AppComponent], 
  entryComponents: [], 
  imports: [BrowserModule, IonicModule.forRoot({ mode: 'md' }), AppRoutingModule], 
  providers: [

    StatusBar,
    SplashScreen,
    SQLite,
    { provide: RouteReuseStrategy, useClass: IonicRouteStrategy }

  ], 
  bootstrap: [AppComponent]
})
export class AppModule { }

Creating Database & Table

Let's create a database & table for inserting todo tasks.

  init() {
    this.sqlite.create({
      name: "todoApp",
      location: 'default'
    }).then((res) => {

      this.conObj = res;

      this.conObj.executeSql("CREATE TABLE IF NOT EXISTS todo (id integer primary key,createdAt VARCHAR(255), title VARCHAR(255), isDone BOOLEAN)", []).then(() => {
        console.log("Table Created");
      }).catch((err) => {
        console.log(err);
        console.log("Table Creation Error" + err);
      })
    }).catch(err => {
      console.log(err);
    });

  }

Code Explanation:

  • Whenever the init() called, we're creating a database name called todoApp with a default location
  • This returns database connection object.using the connection objec we can execute required sql queries
  • Here, we have created a table called todo & created fields id , createdAt , title and isDone

Insert Data

After the database & table creation, let's write a code for inserting the data into table

  insert(task) {
    let createdAt = Date.now();
    this.conObj.executeSql("INSERT INTO todo (createdAt, title, isDone) VALUES (?,?,?)", [createdAt, task, 0]).then(() => {
      console.log("Inserted")
      this.readAllData();
    }).catch((err) => {
      console.log(err);
      console.log("Insert Error" + err);
    })
  }

Code Explanation:

  • We have used conObj to execute our insert query
  • We have also passed the data into insert query as params such as createdAt as current timestamp, and task title from alert input, 0 for isDone

Reading Data

let's write a code for reading the data from table

  readAllData() {
    this.conObj.executeSql("SELECT * FROM todo", []).then(res => {
      console.log(res);

      let tmp = [];
      for (let i = 0; i < res.rows.length; i++) {
        tmp.push({ id: res.rows.item(i).id, createdAt: res.rows.item(i).createdAt, title: res.rows.item(i).title, isDone: res.rows.item(i).isDone })
      }
      this.todos = tmp;
      console.log(this.todos);

    }).catch(() => {
      console.log("READ ERROR");
    })
  }

Code Explanation:

  • we have used SELECT query to read all data from table & assigned the input to this.todos
  • Using this.todos variable, we can display the data into layout
<ion-header>
  <ion-toolbar>

    <ion-title>
      codesundar.com
    </ion-title>
    <ion-buttons slot="end">
      <ion-button (click)="init()">Init</ion-button>
    </ion-buttons>

  </ion-toolbar>
</ion-header>

<ion-content>
  <ion-list>

    <ion-item-sliding *ngFor="let item of todos">
      <ion-item>
        <ion-label>
          <ion-text>
            <p style="color: #777">{{item.createdAt | date:'MMM dd, yyy'}}</p>
          </ion-text>
          <ion-text>
            <h3>{{item.title}}</h3>
          </ion-text>
        </ion-label>
        <ion-badge slot="end" *ngIf="item.isDone == 1">completed</ion-badge>
      </ion-item>
      <ion-item-options side="end">
        <ion-item-option (click)="update(item.id)">Done</ion-item-option>
        <ion-item-option color="danger" (click)="delete(item.id)">Delete</ion-item-option>
      </ion-item-options>
    </ion-item-sliding>

  </ion-list>
  <ion-fab vertical="bottom" horizontal="end" slot="fixed">

    <ion-fab-button (click)="showPop()">
      <ion-icon name="add"></ion-icon>
    </ion-fab-button>

  </ion-fab>
</ion-content>

Code Explanation

  • Here, we have created a ion-list & display item using ion-item-sliding
  • When user swipe from right to left, this display options for Done button & Delte button

Updating Data

  update(id) {
    console.log(id);
    this.conObj.executeSql("UPDATE todo SET isDone=? WHERE id=?", [1, id]).then(() => {
      console.log("Updated")
      this.readAllData();
    }).catch((err) => {
      console.log(err);
      console.log("Update Error" + err);
    })
  }

Delete Data

  delete(id) {
    console.log(id);
    this.conObj.executeSql("DELETE FROM todo WHERE id=?", [id]).then(() => {
      console.log("Deleted")
      this.readAllData();
    }).catch((err) => {
      console.log(err);
      console.log("Delete Error" + err);
    })
  }

Download ionic SQLite example

You can download the latest source code for ionic SQLite tutorial here.

Reference