Ionic + SQlite Example

[danishionic]

I have seen may examples for Ionic with sqlite db but all with ionic 2. if anyone has any idea / link for sqlite with ionic 3 please reply.

[Sujan12]

They are pretty much the same as there is not difference between Ionic 2 and 3 - it’s just a higher version number.

[rapropos]

Unless you absolutely need joins and complex query logic, do your best to make ionic-storage meet your needs instead of dealing with SQLite manually. Development will be much smoother for you.

[rwildcard]

I have been swimming through article after article of “not quite what I am looking for” answers. I found a video in french. Here it is: Ionic 3 Store Data

That being said, here is how to setup your code to make ionic 3 cordova sqlite workable.

1. Import your native sqlite by running these 2 commands in your npm or cmd prompt.

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

2. Import into your app.module.ts

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

3. Add as a provider in your app.module.ts

   providers: [
   …
   SQLite,
   …
   ]

4. Create a new folder(if you want to make it a different component) and make a database ts file. For the sake of ease I called mine database.ts

5. Add the following code (Please note this is not real code that I use. Just an example. Usernames and passwords should not be stored in this way)

   import { Injectable } from ‘@angular/core’;
   import { SQLite, SQLiteObject } from ‘@ionic-native/sqlite’;

   @Injectable()
   export class Database {

    theConsole: string = "Console Messages";
   
    options: any = {
        name: data.db,
        location: 'default',
        createFromLocation: 1
    }
   
    private db: SQLiteObject;
   
    constructor(private sqlite: SQLite) {
   
        this.connectToDb();
    }
    
    private connectToDb():void {
       
        this.sqlite.create(this.options)
            .then((db: SQLiteObject) => {
   
                this.db = db;
                var sql = 'create table IF NOT EXISTS `user` (username VARCHAR(255), password VARCHAR(255))';
                //IF you move the below statment out of here then the db variable will not be initialized
                //before you can use it to execute the SQL. 
                this.db.executeSql(sql, {})
                .then(() => this.theConsole += 'Executed SQL' + sql)
                .catch(e => this.theConsole += "Error: " + JSON.stringify(e));
            })
            .catch(e => this.theConsole += JSON.stringify(e));
        
    }
   
     addUser(username, password):void {
       
        var sql = "INSERT INTO `user` (username,password) VALUES ('"+username+"','"+ password+"')";
       
        this.db.executeSql(sql,{})
        .then(() => this.theConsole += "\n" + 'Executed SQL' + sql)
        .catch(e => this.theConsole += "Error: " + JSON.stringify(e));
    
         
    }
    getDealer() {
        var sql = "SELECT * FROM user";
        
        this.db.executeSql(sql, {})
            .then((result) => {
                this.theConsole += JSON.stringify(result);
                if (result.rows.length > 0) {
                    this.theConsole += 'Result' + result.rows.item(0);
                }
                this.theConsole += "\n" + result.rows.item(0).username+ result.rows.item(0).password;
                this.theConsole +=  "\n" +'Rows' + result.rows.length;
               
            })
   
            .catch(e => this.theConsole += JSON.stringify(e));
    }
   
    getConsoleMessages() {
        return this.theConsole;
    }
   

   }

Then you just have to import the database component(Class) into one of your pages and you can access the database by running these functions or by making your own RunSQL function that you can throw whatever you want into it.

What is really the part that confused me from ionic’s website was the fact that they were showing the create but not the reuse of the SQLiteObject.
By adding the:

private db: SQLiteObject;

to my code in the declaration of class variables and in the initializing of the db object:

 ...
 this.sqlite.create(this.options)
            .then((db: SQLiteObject) => {

                this.db = db;
 ...

I was able to reuse the db variable without having to open the db connection over and over again.

6. Import your component class into a page

   import { Database } from ‘…/…/data/database’;

I used this site: ionic native sqlite to get the main understanding of how to set it up and the french video mentioned earlier. I wish I would have found I hopefully can help others hitting this same sqlite wall. I wish I would have found what I found today sooner. I hopefully can help others hitting this same sqlite wall.

[rapropos]

Please don’t store passwords anywhere in Ionic apps. At best, it’s useless. At worst, it could lead to users having their online banking accounts compromised.

[rwildcard]

I changed my code for this example. In my project the database doesn’t have usernames and passwords. I just used it as an example that users would understand. Good warning though. I have edited my #5 point to announce your warning. I wouldn’t want to mislead anyone.

[Aanand9]

I have an Ionic 3 app which uses SQLite database. Can I use/share the same database data for another Ionic 3 app?

[massimodicorato]

The import instruction : import { SQLite, SQLiteObject} from ‘@ionic-native/sqlite’;

returns errors when run :

Refused to apply style from ‘http://localhost:4400/build/main.css’ because its MIME type (‘text/html’) is not a supported stylesheet MIME type, and strict MIME checking is enabled.etc

The plugin cordova and @ionic-native/sqlite are istalled .

why ??

[bagdeabhishek95]

If you are declaring ‘db’ variable as private, how can use it in a different component? It should be public, right? Please clear me if I’m wrong. I’m not very clear with the concepts.

[rapropos]

@bagdeabhishek95

If you are declaring ‘db’ variable as private, how can use it in a different component?

Through public methods exposed by the service provider that holds the private db property.

[rwildcard]

Hi Aanand9,

To my knowledge this would compromise security of apps if other apps could connect to their database.

You would have to have a separate database for each App.

[rwildcard]

Hey Missimo…is this still a problem? Are you sure it is related to the SQLite object? Something seems off.

[iamaroot]

this not working

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

this worked

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

[ishaquemujtaba]

this is happen due to upgration of ionic 3 to ionic 4,
cheer’s

[ipsjolly]

In Ionic 4 SQLite plugin allows adding application Database storage in device memory.

Check the tutorial link here