# Ionic 2 - SQLite - SqlStorage - sqlBatch

**URL:** <https://forum.ionicframework.com/t/ionic-2-sqlite-sqlstorage-sqlbatch/54643>\
**Category:** ionic-v3\
**Created:** [June 13, 2016, 8:57am UTC](https://forum.ionicframework.com/t/ionic-2-sqlite-sqlstorage-sqlbatch/54643 "2016-06-13T08:57:43Z")\
**Posts on this page:** 17\
**Page:** 1

<div class="post-metadata">

**Author:** ![dsartiano](https://avatars.discourse-cdn.com/v4/letter/d/c4cdca/32.png) [@dsartiano](https://forum.ionicframework.com/u/dsartiano)\
**Post date:** [June 13, 2016, 8:57am UTC](https://forum.ionicframework.com/t/ionic-2-sqlite-sqlstorage-sqlbatch/54643/1 "2016-06-13T08:57:43Z")

</div>

Hi,  
I’m using Ionic 2 with the cordova-sqlite-storage plugin.

In the costructor of my Provider I have the instance of the Storage:

```
this.storage = new Storage(SqlStorage, {name: 'my-sqlite-database'})

```

I’m able to read/write in the database with the method query:

```
 this.storage.query(`SELECT * FROM sqlite_master WHERE type = 'table' and name = '${tablename}'`).then(....)

```

But I am not able to use the method sqlBatch documented here:

```
 http://ionicframework.com/docs/v2/native/sqlite/

```

and here:

```
 https://github.com/litehelpers/Cordova-sqlite-storage

```

How can I use sqlBatch method?

Thanks in advance,  
Daniele

---

<div class="post-metadata">

**Author:** ![rapropos](https://sea1.discourse-cdn.com/ionicframework/user_avatar/forum.ionicframework.com/rapropos/32/100227_2.png) [@rapropos](https://forum.ionicframework.com/u/rapropos)\
**Post date:** [June 13, 2016, 7:17pm UTC](https://forum.ionicframework.com/t/ionic-2-sqlite-sqlstorage-sqlbatch/54643/2 "2016-06-13T19:17:36Z")

</div>

Are you running in a device or browser? I don’t think it’s going to work in a browser.

---

<div class="post-metadata">

**Author:** ![dsartiano](https://avatars.discourse-cdn.com/v4/letter/d/c4cdca/32.png) [@dsartiano](https://forum.ionicframework.com/u/dsartiano)\
**Post date:** [June 14, 2016, 1:39pm UTC](https://forum.ionicframework.com/t/ionic-2-sqlite-sqlstorage-sqlbatch/54643/3 "2016-06-14T13:39:16Z")

</div>

The app is for device, but I’m using the browser for the development.  
Is there a way to do multiple queries for time (cross platform: browser + devices)?

Daniele

---

<div class="post-metadata">

**Author:** ![rapropos](https://sea1.discourse-cdn.com/ionicframework/user_avatar/forum.ionicframework.com/rapropos/32/100227_2.png) [@rapropos](https://forum.ionicframework.com/u/rapropos)\
**Post date:** [June 14, 2016, 7:11pm UTC](https://forum.ionicframework.com/t/ionic-2-sqlite-sqlstorage-sqlbatch/54643/4 "2016-06-14T19:11:04Z")

</div>

I use this function:

```auto
 private _batch(stmts: any[]): Promise<any> {
    let rv: Promise<any> = new Promise((resolve, reject) => {
      let wincb = (dbrv: any) => {
        resolve(dbrv);
      };

      let losecb = (tx: any, err: Error) => {
        reject(err);
        return false;
      };

      this._db.transaction((tx: any) => {
        let nstmts = stmts.length;
        for (let ix = 0; ix < nstmts; ix++) {
          let sql:string;
          let phs:any[];
          if (Array.isArray(stmts[ix])) {
            sql = stmts[ix][0];
            phs = stmts[ix][1];
          } else {
            sql = stmts[ix];
            phs = [];
          }
          tx.executeSql(sql, phs, undefined, losecb);
        }
      }, losecb, wincb);
    });
    return rv;
  }

```

db is initialized like this:

```auto
   if (win.sqlitePlugin) {
      let openparms = {
        name: _SQLITE_DB_NAME,
        location: 2, // local backup
        createFromLocation: 0
      };
      this._db = win.sqlitePlugin.openDatabase(openparms);
    } else {
      console.warn('Storage: SQLite plugin not installed, falling back to WebSQL. Make sure to install cordova-sqlite-storage in production!');
      this._db = win.openDatabase(_SQLITE_DB_NAME, '1.0', 'database', 5 * 1024 * 1024);
    }

```

`_batch` is called with an array of statements. They can either be a single string of SQL or an array beginning with the SQL and then containing placeholders. Called like so:

```auto
this._batch([
  "CREATE TABLE version (version INT NOT NULL PRIMARY KEY)",
  ["INSERT INTO version (version) VALUES ($1)", [1]],
]);

```

---

<div class="post-metadata">

**Author:** ![jfoscue](https://avatars.discourse-cdn.com/v4/letter/j/f19dbf/32.png) [@jfoscue](https://forum.ionicframework.com/u/jfoscue)\
**Post date:** [July 28, 2016, 8:12pm UTC](https://forum.ionicframework.com/t/ionic-2-sqlite-sqlstorage-sqlbatch/54643/5 "2016-07-28T20:12:37Z")

</div>

Pardon my inexperience but how is this.\_db defined? Is it an SqlStorage like this?  
\_db: SqlStorage;

---

<div class="post-metadata">

**Author:** ![rapropos](https://sea1.discourse-cdn.com/ionicframework/user_avatar/forum.ionicframework.com/rapropos/32/100227_2.png) [@rapropos](https://forum.ionicframework.com/u/rapropos)\
**Post date:** [July 28, 2016, 8:18pm UTC](https://forum.ionicframework.com/t/ionic-2-sqlite-sqlstorage-sqlbatch/54643/6 "2016-07-28T20:18:37Z")

</div>

> [@jfoscue](#):
>
> how is this.\_db defined? Is it an SqlStorage like this?

No, it’s an `any` because it is initialized differently depending on whether we have SQLite or not. I’m not a big fan of the SQLStorage API, for reasons discussed [here](https://forum.ionicframework.com/t/ionic-2-sqlstorage-transactions-how-to-use/38506).

---

<div class="post-metadata">

**Author:** ![luchillo17](https://sea1.discourse-cdn.com/ionicframework/user_avatar/forum.ionicframework.com/luchillo17/32/78209_2.png) [@luchillo17](https://forum.ionicframework.com/u/luchillo17)\
**Post date:** [July 28, 2016, 8:42pm UTC](https://forum.ionicframework.com/t/ionic-2-sqlite-sqlstorage-sqlbatch/54643/7 "2016-07-28T20:42:06Z")

</div>

Jaja i’m actually making a tutorial about `SqlStorage` instead of `ionic-native` due to `SqlStorage` managing the use of `WebSql` as fallback when in browser for development and web versions of the app.

Maybe when i’m finished it should solve most problems you guys have.

---

<div class="post-metadata">

**Author:** ![rapropos](https://sea1.discourse-cdn.com/ionicframework/user_avatar/forum.ionicframework.com/rapropos/32/100227_2.png) [@rapropos](https://forum.ionicframework.com/u/rapropos)\
**Post date:** [July 28, 2016, 8:43pm UTC](https://forum.ionicframework.com/t/ionic-2-sqlite-sqlstorage-sqlbatch/54643/8 "2016-07-28T20:43:49Z")

</div>

I don’t think this is something that can be solved by a tutorial. `query` is insufficient to do proper transaction processing.

---

<div class="post-metadata">

**Author:** ![luchillo17](https://sea1.discourse-cdn.com/ionicframework/user_avatar/forum.ionicframework.com/luchillo17/32/78209_2.png) [@luchillo17](https://forum.ionicframework.com/u/luchillo17)\
**Post date:** [July 28, 2016, 8:44pm UTC](https://forum.ionicframework.com/t/ionic-2-sqlite-sqlstorage-sqlbatch/54643/9 "2016-07-28T20:44:47Z")

</div>

Oh yes, i don’t rely on the query method at all, i call the database transaction directly, and redefine the query as a one line transaction.

---

<div class="post-metadata">

**Author:** ![rapropos](https://sea1.discourse-cdn.com/ionicframework/user_avatar/forum.ionicframework.com/rapropos/32/100227_2.png) [@rapropos](https://forum.ionicframework.com/u/rapropos)\
**Post date:** [July 28, 2016, 8:50pm UTC](https://forum.ionicframework.com/t/ionic-2-sqlite-sqlstorage-sqlbatch/54643/10 "2016-07-28T20:50:44Z")

</div>

How do you access “the database transaction directly” from an SqlStorage object without violating encapsulation and access control? The `_db` member is private.

---

<div class="post-metadata">

**Author:** ![luchillo17](https://sea1.discourse-cdn.com/ionicframework/user_avatar/forum.ionicframework.com/luchillo17/32/78209_2.png) [@luchillo17](https://forum.ionicframework.com/u/luchillo17)\
**Post date:** [July 28, 2016, 8:58pm UTC](https://forum.ionicframework.com/t/ionic-2-sqlite-sqlstorage-sqlbatch/54643/11 "2016-07-28T20:58:08Z")

</div>

Well i do violate that private property, say i have this in my DB class, in telling that db var is type `any` typescript doesn’t complain about the private var `db._strategy._db` which is the one that allows to acces the transaction method:

```auto
export class DB {
  public db: any = new Storage(SqlStorage, {name: 'sonoramobile'});

  getDB() {
    return this.db._strategy._db;
  }

  queryTx(tx: any, query: string, bindings: any[], raw: boolean) {
    return new Promise((resolve, reject) => {
      tx.executeSql(query, bindings,
        (tx, success) => {
          if (raw) {
            return resolve(success)
          }
          resolve(this.fetchAll(success));
        },
        (tx, error) => {
          reject(error);
          return true
        }
      );
    });
  }
  queryAll(SQL: any, raw: boolean = false) {
    return new Promise((resolve, reject) => {
      var promises = [];
      this.getDB().transaction((tx) => {
        for (var i = 0; i < SQL.query.length; i++) {
          promises.push(
            this.queryTx(tx, SQL.query[i], SQL.args[i], raw)
          );
        }
        return Promise.all(promises).then((success) => {
          resolve(success);
        }, (err) => {
          reject(err);
        })
      })
    })
  }
}

```

---

<div class="post-metadata">

**Author:** ![rapropos](https://sea1.discourse-cdn.com/ionicframework/user_avatar/forum.ionicframework.com/rapropos/32/100227_2.png) [@rapropos](https://forum.ionicframework.com/u/rapropos)\
**Post date:** [July 28, 2016, 9:04pm UTC](https://forum.ionicframework.com/t/ionic-2-sqlite-sqlstorage-sqlbatch/54643/12 "2016-07-28T21:04:58Z")

</div>

Yeah, that’s not acceptable to me. I would rather roll my own solution based on what _is_ publicly available than to circumvent type-checking and access control in order to poke around in internals where I’m not wanted. The entire point of creating access controls and concepts like interface/implementation firewalls is to prevent the bugs that are caused by approaches like this. I wish you well in your endeavors, but can’t get onboard that train.

---

<div class="post-metadata">

**Author:** ![luchillo17](https://sea1.discourse-cdn.com/ionicframework/user_avatar/forum.ionicframework.com/luchillo17/32/78209_2.png) [@luchillo17](https://forum.ionicframework.com/u/luchillo17)\
**Post date:** [July 28, 2016, 9:09pm UTC](https://forum.ionicframework.com/t/ionic-2-sqlite-sqlstorage-sqlbatch/54643/13 "2016-07-28T21:09:41Z")

</div>

I guess that’s true, we’re not supposed to reach that property but there’s a few petitions to ionic to expose a transaction method or something with no response, so i’m currently using SqlStorage only to open the database, everything else is just using directly WebSql when in web, and SqLite plugin in mobile.

I’m basically telling SqlStorage “gth, i use you to open the database, then i don’t care if you even exist”.

---

<div class="post-metadata">

**Author:** ![dsartiano](https://avatars.discourse-cdn.com/v4/letter/d/c4cdca/32.png) [@dsartiano](https://forum.ionicframework.com/u/dsartiano)\
**Post date:** [July 29, 2016, 7:15am UTC](https://forum.ionicframework.com/t/ionic-2-sqlite-sqlstorage-sqlbatch/54643/14 "2016-07-29T07:15:09Z")

</div>

What about this way from [https://github.com/driftyco/ionic/issues/6594](https://github.com/driftyco/ionic/issues/6594) :

` private storage = new Storage(SqlStorage, { name: 'myDatabase, location: ‘default’ });

```
this.storage.query("SELECT 'one request for transaction'")
        .then((response) => {
            response.tx.executeSql("CREATE TABLE IF NOT EXISTS USER (id integer primary key, name text");
            response.tx.executeSql("CREATE TABLE IF NOT EXISTS PROJECT (id integer primary key, name text");
        });`
```

---

<div class="post-metadata">

**Author:** ![luchillo17](https://sea1.discourse-cdn.com/ionicframework/user_avatar/forum.ionicframework.com/luchillo17/32/78209_2.png) [@luchillo17](https://forum.ionicframework.com/u/luchillo17)\
**Post date:** [July 29, 2016, 12:57pm UTC](https://forum.ionicframework.com/t/ionic-2-sqlite-sqlstorage-sqlbatch/54643/15 "2016-07-29T12:57:38Z")

</div>

I don’t like that i have to do a sql request just to get the transaction object, in most cases one would need to make an empty query, that would introduce a few overhead since each transaction would need to add an extra unwanted query.

Here’s the request for exposing transaction api made in ionic repo, if all of you are kind to give it a thumbs up to encourage ionic staff to expose the transaction api: [#5245](https://github.com/driftyco/ionic/issues/5245)

---

<div class="post-metadata">

**Author:** ![rapropos](https://sea1.discourse-cdn.com/ionicframework/user_avatar/forum.ionicframework.com/rapropos/32/100227_2.png) [@rapropos](https://forum.ionicframework.com/u/rapropos)\
**Post date:** [July 29, 2016, 6:11pm UTC](https://forum.ionicframework.com/t/ionic-2-sqlite-sqlstorage-sqlbatch/54643/16 "2016-07-29T18:11:50Z")

</div>

> [@dsartiano](#):
>
> What about this way

A major problem with that is that `query` should be able to use `readTransaction`internally. It would not make sense to force an operation that is inherently read-only to use, let alone return, a writeable transaction object.

---

<div class="post-metadata">

**Author:** ![alemdg](https://avatars.discourse-cdn.com/v4/letter/a/45deac/32.png) [@alemdg](https://forum.ionicframework.com/u/alemdg)\
**Post date:** [September 28, 2021, 10:35pm UTC](https://forum.ionicframework.com/t/ionic-2-sqlite-sqlstorage-sqlbatch/54643/18 "2021-09-28T22:35:54Z")

</div>

Thanks you very much!, this was the real solution.

best regards