Ionic 2 storage doesn’t have query method

[aggarwalankush]

I'm updating my app to Ionic 2 rc0 and using import {Storage} from "@ionic/storage"; But it doesn't have query method. It was there in beta 11 to query SQLite. How to query SQLite in rc0?

[bergmito]

Try it

import { SQLite } from 'ionic-native';

let db = new SQLite();
db.openDatabase({
name: 'data.db',
location: 'default' // the location field is required
}).then(() => {
db.executeSql('create table danceMoves(name VARCHAR(32))', {}).then(() => {

}, (err) => {
console.error('Unable to execute sql: ', err);
});
}, (err) => {
console.error('Unable to open database: ', err);
});

[aggarwalankush]

@bergmito Storage uses SQLite behind the scenes so why not query method because it was available in beta 11. I don't see any reason to remove it in rc0.

[okonon]

@aggarwalankush clearly something has changed in beta 11 i used to do:

import { SqlStorage, Storage } from 'ionic-angular';
...
storage = new Storage(SqlStorage);

But after rc0 upgrade am getting following error:
/node_modules/ionic-angular/index"' has no exported member 'SqlStorage'.
@jgw96 did you guys get rid of SqlStorage ?

[aggarwalankush]

@okonon I also used to do same but now we have @ionic/storage which automatically uses Sqlite behind the scenes, I don't know where is query method? @mlynch implemented that in this commit, maybe he can answer 👍

[baffleinc]

+1 - was using Storage.query() heavily!

[stephanowallace]

I'm having the same problem. In my app I have a "service" with a lot of queries that access and change data in my database (sqlite). I use the query method and return promises (it isn't working anymore too), will I have to change all my code because of this change?

[rafaelss95]

Same here. I'm using the query method for everything.

[danbucholtz]

Hi everyone,

I have passed this info along to the team and will keep you posted on this.

Thanks,
Dan

[chmdznr]

Additional Error (ionic rc 0)
When using example code from:
http://ionicframework.com/docs/v2/native/sqlite/
Got these errors (already try it on browser, emulator, ios device.. all the same) :
ERROR: ReferenceError: sqlitePlugin is not defined at http://localhost:8100/build/main.js:78458:13 at new t (http://localhost:8100/build/polyfills.js:3:15614) at SQLite.openDatabase (http://localhost:8100/build/main.js:78457:16) at new PCounter (http://localhost:8100/build/main.js:79945:24) at DebugAppView._View_HomePage_Host0.createInternal (HomePage_Host.ngfactory.js:20:24) at DebugAppView.AppView.create (http://localhost:8100/build/main.js:10658:21) at DebugAppView.create (http://localhost:8100/build/main.js:10870:44) at ComponentFactory.create (http://localhost:8100/build/main.js:6276:36) at NavControllerBase._viewInit (http://localhost:8100/build/main.js:46701:44) at NavControllerBase._transition (http://localhost:8100/build/main.js:46686:18)

[richardshergold]

I got the same - looking forward to hearing what we need to do!

[danielabbatt]

Yes agreed, rightly or wrongly I was relying on Storage as an abstraction later to interact with a DB using .query - It would nicely abstract to websql or sqlite depending on whether I was working with "ionic serve" in the browser or against an actual device.

[davidnmbond]

Another project broken here. Following this decision, how should we be abstracting queryable storage to work in both browser (for debugging with ionic serve) and the device environments?

This seems like a pretty fundamental bit of functionality that Ionic 2 just lost :(

[edismooth]

I hope they will restore it ASAP - a lot of projects are using that functionality.

[richardshergold]

@bergmito have you tried that code in RC.0 ? I have tried it (straight from the docs) and it doesn't work for me?

[aldo-roman]

This is urgent. RC0 is useless without Storage query method :/

[aggarwalankush]

Hi All,

I can't upgrade to rc0 😢 because of unavailability of query method so I'm unsure about below issue.

Following issue is not regarding query method, it's regarding get and set methods

Till beta 11, storage was in ionic-angular and it used to use kv as table name. f817ac0#diff-7e4a48359dfcc9528da611464b28ec11L87

Now from rc, storage has been moved @ionic/storage and as per my understanding, it uses _ionickv as table name https://github.com/driftyco/ionic-storage/blob/master/src/storage.ts#L42

But my app is in production with beta 11 and there would be lot of user data in kv table. So after upgrading to rc, how do I migrate kv table data to _ionickv?

Let me know if I can provide more details. @jgw96 @danbucholtz @mlynch

-Thanks

[larssn]

I'm having to downgrade to beta 11 to get this method back. I can't use a simple key/value store in my app sadly.

[drantunes]

This is a important issue to solve!

[bergmito]

@richardshergold yes. Here worked!! You add

providers: [
Storage
]

in app.module.ts?

[drantunes]

I tried replace by SQLite, but it's not working here. An error related to NgModule :(

[aldo-roman]

try replacing

this.storage.query("...")
with
this.db.executeSql("...", {})

you need two variables:
storage: Storage (get, set)
db: SQLite (executeSql)

[drantunes]

@aldo-roman ReferenceError: sqlitePlugin is not defined.
I tried insert SQLite in AppModule, in the providers array, but not working

[NickStemerdink]

@drantunes did you add the plugin?
$ ionic plugin add cordova-sqlite-storage

[drantunes]

@NickStemerdink yes.

The ionic plugin list returns cordova-sqlite-storage 1.4.7 as installed.
I think this issue is related to NgModule

[NickStemerdink]

I guess you are missing the abstraction layer in your example. See gist in this comment #8269 (comment) for more information.

That will take care of initializing the correct database. The other methods will just refer to that database.

So after implementing the code from the gist, your code would look like:

export class StorageService(sql: Sql) {
    getUsers() {
        return this.sql.query('SELECT * FROM users').then((data) => {
           // ...
        });
    }
}

[klochko7]

NickStemerdink, thanks.

[daveshirman]

@NickStemerdink: Thanks, very helpful of you. Good man.

[iursevla]

I don't want to create another issue, but at the same time i hope it's not offtopic.
How can we use pre-populated databases now on RC0?

[baffleinc]

To build on top of @NickStemerdink's epic work, if anybody needs a batch function you can add it to the sql service like this:

batch(query:string, objects:any[]):Promise<any>{
      return new Promise((resolve, reject) => {
        try{
          let queries:any = _.map(objects, (object) => [query, object]);
          this._db.sqlBatch(queries, function(){
            resolve();
          });
        } catch (err) {
          reject({err:err})
        }
      });
    }

Usage:

let objects = _.map(entries, entry => [entry.name, entry.id]);
        this.sql.batch(`insert into table (name, id) values (?,?)`, objects);

[juliandavidmr]

Its works!!!!!
I use this ExamplePage

Thanks @NickStemerdink

[seand88]

@NickStemerdink
thanks for the workaround...

I agree with @mlynch and the ionic team for reasons in taking this out.
What I think would be a good solution is to have an available interface for storage that supports sql like before RC0

Sql is a such a common solution and is the main storage engine for both ios/android
Why not implement something like https://github.com/kripken/sql.js where we get sql as a javascript lib in the browser, then on mobile it falls back to native sqlite?

I think while some apps use key/value stores it definitely is not applicable to all applications and I don't think that the framework should be that opinionated to not have a good sql interface available for both in browser and app testing.

[jkobylec]

Agree with @seand88 this would be quite valuable for when we've otherwise coded our apps to be testable in the browser but direct SQLite makes the most sense for the persistence model on the actual device.

There just aren't alternatives with comparable secondary indexing performance that work seamlessly on the browser and the device—it's all on top of SQLite (or flat files that get rewritten wholesale...) in the device anyway, typically implementing interfaces that are really meant for massively parallel computing environments and not a mobile device. (For instance, the PouchDB developers themselves would tell you not to use PouchDB if you're not syncing with a CouchDB backend.)

Naturally the browser performance in this case would not be so critical since we'd be tuning for performance on the device; it seems we mainly just want the ability to functionally test in the browser that we don't get with the "more native" platforms.

Perhaps this could be done as a community effort?

Conversely, could this just be a matter of not being "good enough" at testing/debugging exclusively through emulators/devices? Maybe we just need to see the light and wouldn't miss the ability to test and debug in the browser?

[seand88]

@jkobylec
lets make this a community effort like you said.
I put the interface into one file for simplicity for now and removed the storage engine interface since we know its going to be sqlite.

I placed it in a repo here.
https://github.com/seand88/ionic2-sql-interface

Havent published an ionic module before but I will be working on integrating sql.js for the browser testing then can publish it as a module.

This should be a drop in replacement of one file for anyone else having issues with this. See the readme in the repo for instructions.

[jkobylec]

Thanks @seand88!

One thing I'd definitely want to set up in the build process if going the sql.js route in the future is to only include sql.js for the browser using ionic serve and not for device/emulator builds since sql.js is a quite hefty chunk of script (2 MB) that would be relevant only for in-browser testing. Does the new RC0 build model provide a straightforward way to configure something like this?

[seand88]

@jkobylec
no idea i haven't looked into the build system internals yet.

[OmarHassan25]

Dears,
I am using the solution of websql on browsers and sqlite on real devices, My problem is the transaction callbacks success and fail, They are reversed on the browser fail called instead of success and vice versa.

Example:
`
db.transaction((tx: any) => {
let vals, sql;
tx.executeSql(sql, vals, undefined, fail);

    },success , fail);
  });`

on real devices success is called on success cases and fail is called on failure, but on browser fail is called for success and success is called on failure !!!!!!11

Am i misunderstood something ?
Anyone can help in this ?

[nebarf]

@baffleinc Your solution about batch query works well on mobile devices, not in browser cause WebSQL doesn't support batch queries. I modified the function like this:

batch(query:string, objects:any[]): Promise<any> {
    return new Promise((resolve, reject) => {
      try {
        this._db.transaction((tx) => {
          var promises = objects.map((object) => {
            tx.executeSql(query, object);
          });
          Promise.all([promises])
            .then(() => {
              resolve();
            })
            .catch(err => {
              reject({ err: err });
            });
          },
          (err) => reject({ err: err }));
      } catch (err) {
        reject({ err: err });
      }
    });
  }

[baffleinc]

Hey, that will work but is not true batchSQL. you will notice significant
performance problems with 1000s of records!

I actually have an abstractor that does the above if on desktop, and uses
batchsql on mobile. Thinking of moving to pouchdb though!
On Wed., 2 Nov. 2016 at 5:29 am, Francesco notifications@github.com wrote:

@baffleinc https://github.com/baffleinc Your solution about batch query
works well on mobile devices, not in browser cause WebSQL doesn't support
batch queries. I modified the function like this:

batch(query:string, objects:any[]): Promise {
return new Promise((resolve, reject) => {
try {
this._db.transaction((tx) => {
var promises = objects.map((object) => {
tx.executeSql(query, object);
});
Promise.all([promises])
.then(() => {
resolve();
})
.catch(err => {
reject({ err: err });
});
},
(err) => reject({ err: err }));
} catch (err) {
reject({ err: err });
}
});
}

—
You are receiving this because you were mentioned.

Reply to this email directly, view it on GitHub
#8269 (comment),
or mute the thread
https://github.com/notifications/unsubscribe-auth/ABD99PMcyark1I7exbR6V38a_whOxo06ks5q54UhgaJpZM4KJScG
.

[ryaa]

Hi,

Why SQLite calls transaction method has only one input param
transaction(fn: any): Promise;

while cordova plugin (https://github.com/litehelpers/Cordova-sqlite-storage) also has success and error callbacks
Sample below

  db.transaction(function(tx) {
    tx.executeSql('CREATE TABLE IF NOT EXISTS DemoTable (name, score)');
    tx.executeSql('INSERT INTO DemoTable VALUES (?,?)', ['Alice', 101]);
    tx.executeSql('INSERT INTO DemoTable VALUES (?,?)', ['Betty', 202]);
  }, function(error) {
    console.log('Transaction ERROR: ' + error.message);
  }, function() {
    console.log('Populated database OK');
  });

[luchillo17]

How did you get SQLite without the plugin?

[ryaa]

ionic-native has this - see https://ionicframework.com/docs/v2/native/sqlite/

[anrtero]

Are You guys really thinking that most of the people using sql database with their apps are glad to implement this or similar kind of "tune-up" in their code instead of importing Storage and SqlStorage plugin? If You answered no, then I would like to know why didn't You also remove the ionic serve command? Because it was pretty useful for many of us to speed up the development. Now it is at least more useless than before this change..

[flyingangel]

SQLite not work on browser
Storage don't support queries
= fail ionic update, can't really test anything on browser

Question : how do you make typescript accept "window.openDatabase" and "window.sqlitePlugin" because when I compile it keep saying error property is undefined ?

[anrtero]

@flyingangel Here is an example for you

[flyingangel]

@anrtero Thanks it definitely save my day :)
The fallback really works on browser.

[aggarwalankush]

I updated sql example gist to wait for platform ready event. Without it, my production apps were unintentionally using WebSQL because sqlitePlugin is not available before platform ready event.

[martingra]

any solution? I want to keep using the query method!

[seand88]

@martingra I placed it in a repo here.
https://github.com/seand88/ionic2-sql-interface

This will work fine for now testing in browser until chrome removes websql.

[aggarwalankush]

@seand88 you solutions might suffer race condition. You should wait for platform.ready event before checking win.sqliteplugin liked I did here https://gist.github.com/aggarwalankush/0b700328e797e22a1d9994cb35afdf09#file-sql-ts-L15

[hiranivipul]

@aggarwalankush Its working fine. Can this code work in native ?

[aggarwalankush]

@hiranivipul yes it will use sqlite in physical device and WebSQL in browser.

Just remember to install cordova sqlite plugin with following command-

$ ionic plugin add cordova-sqlite-storage

[ste4net]

@aggarwalankush Your example is awesome.
But doing some tests i see that win.sqlitePlugin is initialized also debugging with Ripple.

[ionitron-bot]

Thanks for the issue! This issue is being locked to prevent comments that are not relevant to the original issue. If this is still an issue with the latest version of Ionic, please create a new issue and ensure the template is fully filled out.