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 <span class="hljs-keyword">start</span> todo blank
cd todo
ionic cordova <span class="hljs-keyword">plugin</span> <span class="hljs-keyword">add</span> cordova-sqlite-<span class="hljs-keyword">storage</span>
npm <span class="hljs-keyword">install</span> <span class="hljs-comment">--save @ionic-native/sqlite</span>

After plugin installation, we need to declare inside app.module.ts

<span class="hljs-keyword">import</span> { NgModule } <span class="hljs-keyword">from</span> <span class="hljs-string">'@angular/core'</span>; 
<span class="hljs-keyword">import</span> { BrowserModule } <span class="hljs-keyword">from</span> <span class="hljs-string">'@angular/platform-browser'</span>; 
<span class="hljs-keyword">import</span> { RouteReuseStrategy } <span class="hljs-keyword">from</span> <span class="hljs-string">'@angular/router'</span>; 

<span class="hljs-keyword">import</span> { IonicModule, IonicRouteStrategy } <span class="hljs-keyword">from</span> <span class="hljs-string">'@ionic/angular'</span>; 
<span class="hljs-keyword">import</span> { SplashScreen } <span class="hljs-keyword">from</span> <span class="hljs-string">'@ionic-native/splash-screen/ngx'</span>; 
<span class="hljs-keyword">import</span> { StatusBar } <span class="hljs-keyword">from</span> <span class="hljs-string">'@ionic-native/status-bar/ngx'</span>; 

<span class="hljs-keyword">import</span> { AppComponent } <span class="hljs-keyword">from</span> <span class="hljs-string">'./app.component'</span>; 
<span class="hljs-keyword">import</span> { AppRoutingModule } <span class="hljs-keyword">from</span> <span class="hljs-string">'./app-routing.module'</span>; 

<span class="hljs-keyword">import</span> { SQLite } <span class="hljs-keyword">from</span> <span class="hljs-string">'@ionic-native/sqlite/ngx'</span>; 

@NgModule({
  declarations: [AppComponent], 
  entryComponents: [], 
  imports: [BrowserModule, IonicModule.forRoot({ mode: <span class="hljs-string">'md'</span> }), AppRoutingModule], 
  providers: [

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

  ], 
  bootstrap: [AppComponent]
})
<span class="hljs-keyword">export</span> <span class="hljs-class"><span class="hljs-keyword">class</span> <span class="hljs-title">AppModule</span> { }</span>

Creating Database & Table

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

  init() {
    <span class="hljs-keyword">this</span>.sqlite.create({
      name: <span class="hljs-string">"todoApp"</span>,
      location: <span class="hljs-string">'default'</span>
    }).<span class="hljs-keyword">then</span>(<span class="hljs-function"><span class="hljs-params">(res)</span> =></span> {

      <span class="hljs-keyword">this</span>.conObj = res;

      <span class="hljs-keyword">this</span>.conObj.executeSql(<span class="hljs-string">"CREATE TABLE IF NOT EXISTS todo (id integer primary key,createdAt VARCHAR(255), title VARCHAR(255), isDone BOOLEAN)"</span>, []).<span class="hljs-keyword">then</span>(<span class="hljs-function"><span class="hljs-params">()</span> =></span> {
        <span class="hljs-built_in">console</span>.log(<span class="hljs-string">"Table Created"</span>);
      }).<span class="hljs-keyword">catch</span>(<span class="hljs-function"><span class="hljs-params">(err)</span> =></span> {
        <span class="hljs-built_in">console</span>.log(err);
        <span class="hljs-built_in">console</span>.log(<span class="hljs-string">"Table Creation Error"</span> + err);
      })
    }).<span class="hljs-keyword">catch</span>(err => {
      <span class="hljs-built_in">console</span>.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();
    <span class="hljs-keyword">this</span>.conObj.executeSql(<span class="hljs-string">"INSERT INTO todo (createdAt, title, isDone) VALUES (?,?,?)"</span>, [createdAt, task, <span class="hljs-number">0</span>]).<span class="hljs-keyword">then</span>(<span class="hljs-function"><span class="hljs-params">()</span> =></span> {
      <span class="hljs-built_in">console</span>.log(<span class="hljs-string">"Inserted"</span>)
      <span class="hljs-keyword">this</span>.readAllData();
    }).<span class="hljs-keyword">catch</span>(<span class="hljs-function"><span class="hljs-params">(err)</span> =></span> {
      <span class="hljs-built_in">console</span>.log(err);
      <span class="hljs-built_in">console</span>.log(<span class="hljs-string">"Insert Error"</span> + 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(<span class="hljs-string">"SELECT * FROM todo"</span>, []).then(res => {
      console.<span class="hljs-built_in">log</span>(res);

      <span class="hljs-keyword">let</span> tmp = [];
      <span class="hljs-keyword">for</span> (<span class="hljs-keyword">let</span> i = <span class="hljs-number">0</span>; i < res.<span class="hljs-built_in">rows</span>.length; i++) {
        tmp.<span class="hljs-keyword">push</span>({ id: res.<span class="hljs-built_in">rows</span>.item(i).id, createdAt: res.<span class="hljs-built_in">rows</span>.item(i).createdAt, <span class="hljs-built_in">title</span>: res.<span class="hljs-built_in">rows</span>.item(i).<span class="hljs-built_in">title</span>, isDone: res.<span class="hljs-built_in">rows</span>.item(i).isDone })
      }
      this.todos = tmp;
      console.<span class="hljs-built_in">log</span>(this.todos);

    }).catch(() => {
      console.<span class="hljs-built_in">log</span>(<span class="hljs-string">"READ ERROR"</span>);
    })
  }

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
<span class="xml"><span class="hljs-tag"><<span class="hljs-name">ion-header</span>></span>
  <span class="hljs-tag"><<span class="hljs-name">ion-toolbar</span>></span>

    <span class="hljs-tag"><<span class="hljs-name">ion-title</span>></span>
      codesundar.com
    <span class="hljs-tag"></<span class="hljs-name">ion-title</span>></span>
    <span class="hljs-tag"><<span class="hljs-name">ion-buttons</span> <span class="hljs-attr">slot</span>=<span class="hljs-string">"end"</span>></span>
      <span class="hljs-tag"><<span class="hljs-name">ion-button</span> (<span class="hljs-attr">click</span>)=<span class="hljs-string">"init()"</span>></span>Init<span class="hljs-tag"></<span class="hljs-name">ion-button</span>></span>
    <span class="hljs-tag"></<span class="hljs-name">ion-buttons</span>></span>

  <span class="hljs-tag"></<span class="hljs-name">ion-toolbar</span>></span>
<span class="hljs-tag"></<span class="hljs-name">ion-header</span>></span>

<span class="hljs-tag"><<span class="hljs-name">ion-content</span>></span>
  <span class="hljs-tag"><<span class="hljs-name">ion-list</span>></span>

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

  <span class="hljs-tag"></<span class="hljs-name">ion-list</span>></span>
  <span class="hljs-tag"><<span class="hljs-name">ion-fab</span> <span class="hljs-attr">vertical</span>=<span class="hljs-string">"bottom"</span> <span class="hljs-attr">horizontal</span>=<span class="hljs-string">"end"</span> <span class="hljs-attr">slot</span>=<span class="hljs-string">"fixed"</span>></span>

    <span class="hljs-tag"><<span class="hljs-name">ion-fab-button</span> (<span class="hljs-attr">click</span>)=<span class="hljs-string">"showPop()"</span>></span>
      <span class="hljs-tag"><<span class="hljs-name">ion-icon</span> <span class="hljs-attr">name</span>=<span class="hljs-string">"add"</span>></span><span class="hljs-tag"></<span class="hljs-name">ion-icon</span>></span>
    <span class="hljs-tag"></<span class="hljs-name">ion-fab-button</span>></span>

  <span class="hljs-tag"></<span class="hljs-name">ion-fab</span>></span>
<span class="hljs-tag"></<span class="hljs-name">ion-content</span>></span></span>

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) {
    <span class="hljs-built_in">console</span>.log(id);
    <span class="hljs-keyword">this</span>.conObj.executeSql(<span class="hljs-string">"UPDATE todo SET isDone=? WHERE id=?"</span>, [<span class="hljs-number">1</span>, id]).<span class="hljs-keyword">then</span>(<span class="hljs-function"><span class="hljs-params">()</span> =></span> {
      <span class="hljs-built_in">console</span>.log(<span class="hljs-string">"Updated"</span>)
      <span class="hljs-keyword">this</span>.readAllData();
    }).<span class="hljs-keyword">catch</span>(<span class="hljs-function"><span class="hljs-params">(err)</span> =></span> {
      <span class="hljs-built_in">console</span>.log(err);
      <span class="hljs-built_in">console</span>.log(<span class="hljs-string">"Update Error"</span> + err);
    })
  }

Delete Data

  <span class="hljs-keyword">delete</span>(id) {
    <span class="hljs-built_in">console</span>.log(id);
    <span class="hljs-keyword">this</span>.conObj.executeSql(<span class="hljs-string">"DELETE FROM todo WHERE id=?"</span>, [id]).<span class="hljs-keyword">then</span>(<span class="hljs-function"><span class="hljs-params">()</span> =></span> {
      <span class="hljs-built_in">console</span>.log(<span class="hljs-string">"Deleted"</span>)
      <span class="hljs-keyword">this</span>.readAllData();
    }).<span class="hljs-keyword">catch</span>(<span class="hljs-function"><span class="hljs-params">(err)</span> =></span> {
      <span class="hljs-built_in">console</span>.log(err);
      <span class="hljs-built_in">console</span>.log(<span class="hljs-string">"Delete Error"</span> + err);
    })
  }

Download ionic SQLite example

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

Reference