SQLite and table creation

Ionic Framework ionic-v3
1

Hi,

I know that I need to import Storage and SqlStorage.

I know that I need to initialize my db: this.db = new Storage(SqlStorage);

I should be able to execute query like this.db.query('select * from table_name), etc.

What I don’t understand is the table creation!

After I did my new Storage, I tried to execute this line below in my constructor:

this.db.query('create table myTable (id integer)');

When looking in the Chrome developer tool, I can see the _ionicstorage (normal, I did not put any database name) and the “kv” table (where that table comes from?). Why ‘myTable’ is not there?

What am I missing?

Also, I noticed in the Chrome developer tool that warning:

Storage: SQLite plugin not installed, falling back to WebSQL. Make sure to install cordova-sqlite-storage in production!

Do I really need to add the SQLite plugin to my app or it is to the Chrome dev tool?

Thanks

1 Like
2

Just noticed from Ionic source code, that warning is from sql.ts.

So, like I can see, I need to add the SQLite plugin to make it work on the phone. Make sense?

On the desktop browser, it will open a local database (window.opendatabase).

But does not explain how to create a table. :frowning:

3

Even I have the same issue, can someone please help us?

4

As far as i see your query method isn’t wrong, but for some reason (maybe a bug) the query from ionic 2 doesn’t work, i had to fall back to the database transaction method and to transaction.executeSql(sql, bindings):

this.db._strategy._db.transaction((tx) => {

  tx.executeSql(sql, bindings, (tx, success) => {...}, (tx, error) => {...});
}

Note: This is a workaround while the sqlStorage query function is fixed, besides the only way to make multiple transactional queries in 1 transaction, like having an entire transaction for creating each table in a database.

Note 2: Use when possible CREATE TABLE IF NOT EXISTS as the table will be created if not exist and if it does it does nothing, that way you don’t have to check before hand if the table exist.

5

@luchillo17, Do I need to add Cordova extension to be able to use SQlite?

7

Basically yes, here is the platform docs for storage component: http://ionicframework.com/docs/v2/platform/storage/

8

thanks @luchillo17!

I was in the API section for SQL Storage and did not notice the Platform section was talking about Storage!

1 Like
9

Me either, but i supposed it would need it to be able to use the SqLite in mobile just like ionic 1 needed, so i spend half a day searching for a way to, and it was just under our noses :smile: .

10

@luchillo17, have you succeeded to do something with SQLStorage?

I am using the typescript version and what I noticed is this line below gives asked me for more:

this.storage = new Storage(SqlStorage);

First, it says it is missing a parameter after the SqlStorage (options)

Secondly, the first parameter should be a StorageEngine type and not a SqlStorage. I saw in the source code that SqlStorage extends StorageEngine, so it should be ok, right?

What have you done in your side? Can you show me a bit of your constructor?

Thanks

11

SqLite is making a great heavy lifting in my app:

import {Storage, SqlStorage, IonicPlatform} from 'ionic/ionic';

import {Injectable} from 'angular2/angular2';
import {Http, HTTP_BINDINGS, Headers} from 'angular2/http';
@Injectable()
export class DB {
  constructor(private http: Http) {
    this.db = new Storage(SqlStorage, {name: 'dbName here'});
  }
}
12

@luchillo17, are you testing with the help of the browser or on the phone only? I am asking that because I can’t see anything on the browser. It is always _ionicstorage db in Web Sql (because SQlite can’t work)

Just want to add that the SQlite with key, value works. It does not create my table ‘test’ if I do this:

this.storage.query("create table if not exists test(id integer primary key unique)");

Also, if I use the key, value instead of table, can I save object and retrieve them?

13

I’m sorry, i actually wasn’t able to make it work with the query that storage provided, i exposed the database from it and used the transactional query to do all, even single queries, aside of working it also makes consistent my code:

getDB() {

  return this.db._strategy._db;
}

queryTx(tx, query, bindings) {
  return new Promise((resolve, reject) => {
    var self_ref = this;
    tx.executeSql(query, bindings,
      (tx, success) => {
        resolve(self_ref.fetchAll(success));
      },
      (tx, error) => {
        reject(error);
      }
    );
  });
}

queryAll(SQL) {
  return new Promise((resolve, reject) => {
    var promises = [];
    var self_ref = this;
    this.getDB().transaction((tx) => {
      for (var i = 0; i < SQL.query.length; i++) {
        promises.push(
          self_ref.queryTx(tx, SQL.query[i], SQL.args[i])
        );
      }
      return Promise.all(promises).then((success) => {
        resolve(success);
      }, (err) => {
        reject(err);
      });
    });
  });
};

Note that getDB() returns the db object that the sqlStorage generates, so basically i’m bypassing the query method to be able to make transactional queries together, i asked for help here in the forum but nobody could figure out a solution.

14

If you still need help with SQLite in Ionic 2, I made a tutorial on the subject. It includes a video tutorial as well:

Best,

3 Likes
15

Hey @nicraboy, very nice tutorial! I would like to know if I will be able to test it on the Chrome desktop or I need to push my code into my phone to make it work?

[UPDATE]: I just fixed my issue and can see my table and data inside my table within Chrome. Thanks again for your help and good tutorial. Keep doing tutorials! :smile:

1 Like
16

Can I use pre-filled sqlite database for SqlStorage?

17

I’m looking forward to see How can I use prefilled sqlitedb too.
I just like to migrate my sqlite data to pouchdb on ionic2.

18

Have you got any solution to use pre filled sqlitedb

19

Please I actually created the dB with the tables but when I needed the dB in another page, it’s giving me no provider for storage error both on device and ionic serve.
Please how do I solve this. Using sqlstorage across the entire application.
Thanks!!

20

Is it possible to see the data in browser if only saving key and values (not a table)?

21

Yes, you’ll see something similar below. Tables will be under _ionicstorage.

image
Screen Shot 2016-06-28 at 6.17.22 PM.png824×592 68 KB

1 Like