How to make SQLite in ionic-native use cordova-sqlite-ext?


#1

With SqlStorage removed in Ionic 2 RC0, something like this is not possible anymore:

As noted on this page, ionic-native’s SQLite requires cordova-sqlite-storage. Unfortunately, cordova-sqlite-storage doesn’t support dealing with pre-populated databases, that is:
openDatabase({name: "my.db", location: 'default', createFromLocation: 1});

cordova-sqlite-ext comes with support for pre-populated databases.

In my app, I have cordova-sqlite-ext installed (cordova-sqlite-storage is NOT installed). Apparently, SQLite is unable to find the sqlite plugin.

The error that I get in console is:

EXCEPTION: Uncaught (in promise): ReferenceError: sqlitePlugin is not defined

Here is my code:

import { Injectable } from '@angular/core';
import { SQLite } from 'ionic-native';

@Injectable()
export class DbService {

    //
    // Class variables
    //
    private database: SQLite = new SQLite();
    
    constructor() {
        // Open the existing sqlite database file
        this.database.openDatabase({
            name: 'my.db',
            createFromLocation: 1
        });
    }
}

Does anyone know how to make SQLite use cordova-sqlite-ext instead of cordova-sqlite-storage?


#2

Try waiting for Platform.ready() before using the Cordova plugin. (It’s the same for all Cordova plugins.)


#3

Can confirm. This works flawlessly.


#4

Thanks for the tip. I will try that and get back.

This issue aside, I have one related question:
Does anyone know how to make ionic build copy my database file (my.db) to the root of final build? Simply placing my.db in “src” folder doesn’t result in my.db being copied to “www” folder.

(I am not sure what values does the “location” property in openDatabase() takes apart from “default”. That is, I know putting my.db in src/assets will work but I’m not sure how to specify the path /assets/my.db in openDatabase()).


#5

I confirm that the Platform.ready() solution works!

Additionally, I was able to get my sqlite database file to be copied into build’s www with the help of this forum thread:

Thanks all for your inputs.


#6

You can place your .db inside www folder now. Just update ionic-app-scripts

All you need to do is:

npm install @ionic/app-scripts@latest --save-dev


#7

Hello all!

i am trying to work with a pre-populated sqlite db and i’m starting to go crazy…
i don’t get it: cordova-sqlite-ext works without cordova-sqlite-storage? or is it an extention and requires the main plugin to work?
on a fresh ionic starter sidemenu template, this is my plugin list:

cordova-plugin-console 1.0.4 "Console"
cordova-plugin-device 1.1.3 "Device"
cordova-plugin-splashscreen 4.0.0 "Splashscreen"
cordova-plugin-statusbar 2.2.0 "StatusBar"
cordova-plugin-whitelist 1.3.0 "Whitelist"
cordova-sqlite-ext 0.10.2 "Cordova sqlite storage plugin with extras"
ionic-plugin-keyboard 2.2.1 “Keyboard”

i’ve put my pre-populated data.db (50kb) in www folder.
i have this in my app.component.ts

import { SQLite } from 'ionic-native';
...
export class MyApp {
...
private db: SQLite = new SQLite();
...
this.platform.ready().then(() => {
      StatusBar.hide();
      Splashscreen.hide();

      this.db.openDatabase({
        name: "data.db",
        location: "default",
        createFromLocation: 1
      }).then(() => {
        this.db.executeSql("SELECT * from streets", []).then((data) => {
          console.log("Data received: ", data);
        }, (error) => {
          console.error("Unable to execute sql", error);
        })
      }, (error) => {
        console.error("Unable to open database", error);
      });

    });
...

when i build/emulate, i get this errors in the simulator system.log:

Dec 9 14:16:58 Mac-mini capitals4[82735]: OPEN database: data.db
Dec 9 14:16:58 Mac-mini capitals4[82735]: CDVPlugin class SQLitePlugin (pluginName: SQLitePlugin) does not exist.
Dec 9 14:16:58 Mac-mini capitals4[82735]: ERROR: Plugin ‘SQLitePlugin’ not found, or is not a CDVPlugin. Check your plugin mapping in config.xml.

if i add the cordova-sqlite-storage, when i build/emulate, Library/LocalDatabase/data.db is an empty file and i get the “Unable to execute sql” error (obviously, because the db is empty…)

i don’t know what i am doing wrong.


#8

cordova-sqlite-ext is a superset of cordova-sqlite-storage. So you don’t need to have the latter for the former to work. For me, the Platform.ready() solution worked. I can see you have done the same.

In your logs, “OPEN database: data.db” means that your db file was indeed opened (by the plugin?). I see the same message emitted by the sqlite plugin in my app.


#9

yes it can open a db. but it is empty.
my code creates an empty db only when i use cordova-sqlite-storage.
so… i think i did something wrong and can’t “convince” my app to use only cordova-sqlite-ext… do i have to modify an xml or a cnf something?


#10

I installed cordova-sqlite-ext ( without cordova-sqlite-storage)
Placed a sqlite db file in www folder of the app
Used the following code within platform ready:
this.database.openDatabase({
name: ‘my.db’,
location: ‘default’, //if this is not given, opendatabase error will be thrown
createFromLocation: 1
});

and entered ‘ionic run ios’ in the terminal

Ionic automatically added cordova-sqlite-storage plugin in my app

and I finally ended up with opendatabase error. I’m not sure if the application is using sqlite-ext or sqlite-storage, as both are present.

Frozenxis,
have you got it working?


#11

Your database file may not be carrying to the build. Check my previous post.


#12

Hi imagame,
Went through that post, not quite getting it right (completely noob). Here’s what I have done so far:
copied my.db file to assets folder.
Under platform ready code stays same:
this.database.openDatabase({
name: ‘my.db’,
location: ‘default’, //if this is not given, opendatabase error will be thrown
createFromLocation: 1
});
How do i point assets/my.db as location while opening the db?

i have package.json. Your post suggests that we need to add config under package.json:
“config”: {
“ionic_rollup”: “./config/rollup.config.js”
}
That’s just a snippet. What needs to go in there? I’m not sure what roll up deployment is.

Thanks for your help.


#13

I have manually copied db file under www folder for now.

This is my code:


public openDB(): void{
        this.db.openDatabase({
            name: "test.db", //test.db is inside 'www' folder
            createFromLocation: 0 //i tried both 0 and 1, i get same error as shown below in screenshot
       }).then(() => {
            this.refresh();
            alert('db open');
         }, (error) => {
            alert("Unable to open database"+ JSON.stringify(error));
        });
    }

This is the message I get. db location isn’t set right:


#14

hi. i abandoned my project a little. made something else with Firebase

i didn’t find a solution by myself so far

sorry


#15

Even if you manually put your db file inside the www folder, there’s no guarantee it will be copied to the final build apk. At least, that’s what was happening with me.

I had to leverage Ionic’s config script system to get past that problem. Basically, Ionic provides you the option to create a custom config file to specify extra files you want copied in the build.

Just create copy.config.js inside the config folder in your app’s root. Here’s my config file’s content. Try reusing this. (You will need to have your db file in the src folder)

module.exports = {
  include: [
    {
      src: '{{SRC}}/assets/',
      dest: '{{WWW}}/assets/'
    },
    {
      src: '{{SRC}}/index.html',
      dest: '{{WWW}}/index.html'
    },
    {
      src: '{{SRC}}/manifest.json',
      dest: '{{WWW}}/manifest.json'
    },
    {
      src: '{{SRC}}/service-worker.js',
      dest: '{{WWW}}/service-worker.js'
    },
    {
      src: 'node_modules/ionic-angular/polyfills/polyfills.js',
      dest: '{{BUILD}}/polyfills.js'
    },
    {
      src: 'node_modules/ionicons/dist/fonts/',
      dest: '{{WWW}}/assets/fonts/'
    },
    {
      src: '{{SRC}}/my.db',
      dest: '{{WWW}}/my.db'
    }
  ]
};

Read Existing SQLite Database File and Load Data