cordova-sqlite-utility v1.1.0

Cordova SQLite Utility

Utilities to make working with cordova-sqlite-storage a little easier. Features include:

• Promise based API.
• Named parameters for SQLite statements.
• Safely handle JavaScript type conversion to SQLite counterparts.
• Store SQL statements for reuse.

Installation

npm install --save cordova-sqlite-utility

API

SQLite

Open

Open or create a SQLite database file.

When opened, the database is set as the current database and subsequent calls on that SQLite instance are executed against that database automatically.

import { SQLite } from 'cordova-sqlite-utility';

const sqlite = new SQLite();

sqlite
  .open({ name: 'Awesome.db' })
  .then((db) => console.log('Database opened!'));

Close

Close the current database.

sqlite.close().then(() => console.log('Database closed!'));

Remove

Delete the current database.

sqlite.remove().then(() => console.log('Database deleted!'));

Execute

Execute a SQL statement on the current database.

INSERT, UPDATE, and DELETE statements return a SQLiteResult object. SELECT statements return an array of objects. Use the generic overload to specify the return object type (T[]) for SELECT statements.

sqlite.execute <
  IAwesome >
  {
    sql: 'SELECT * FROM AwesomeTable WHERE id = @id',
    params: {
      id: 83,
    },
  }.then((data) => console.log(data));
// => IAwesome[]

sqlite
  .execute({
    sql: 'INSERT INTO AwesomeTable (name) VALUES (@name)',
    params: {
      name: 'Yoda',
    },
  })
  .then((result) => console.log(result));
// => { insertId: 1, rowsAffected: 1 }

Since the statement is prepared with SQLite.prepare before execution, the sql property can be either a stored statement (set via SQLiteStore.set) or inline SQL.

sqlite.execute <
  IAwesome >
  {
    sql: 'Awesome_ReadById',
    params: {
      id: 83,
    },
  }.then((data) => console.log(data));
// => IAwesome[]

Batch

Execute a batch of SQL statements on the current database.

sqlite
  .batch([
    {
      sql: 'CREATE TABLE IF NOT EXISTS AwesomeTable (id, name)',
    },
    {
      sql: 'INSERT INTO AwesomeTable VALUES (@name)',
      params: { name: 'Luke Skywalker' },
    },
    {
      sql: 'INSERT INTO AwesomeTable VALUES (@name)',
      params: { name: 'Darth Vader' },
    },
  ])
  .then(() => console.log('Batch complete!'));

Like execute, the batch method prepares statements with SQLite.prepare. So, the sql property can be either a stored statement (set via SQLiteStore.set) or inline SQL.

SQLiteStore

Store SQL statements for reuse.

Set

Add SQL statements to the store.

import { SQLiteStore } from 'cordova-sqlite-utility';

SQLiteStore.set({
  Awesome_Create: 'INSERT INTO AwesomeTable VALUES (@name)',
  Awesome_ReadById: 'SELECT * FROM AwesomeTable WHERE id = @id',
});

Storing SQL statements in files is often more manageable. If you'd like to keep your SQL statements in files, like this ...

./sqlite
  Awesome_ReadById.sql
  Awesome_Create.sql
  ...

... then sqlite-cordova-devtools can ready all you SQL files for easy addition to the store.

import { SQLiteStore } from 'cordova-sqlite-utility';

SQLiteStore.set(window['_sqlite']);

Get

Get a stored SQL statement by name.

import { SQLiteStore } from 'cordova-sqlite-utility';

const sql = SQLiteStore.get('Awesome_ReadById');
// => 'SELECT * FROM AwesomeTable WHERE id = @id'

SQLiteUtility

Utility methods can be used outside of the API, for direct use with sqlitePlugin.

Prepare

Safely transform named parameters and unsupported data types.

import { SQLiteUtility } from 'cordova-sqlite-utility';

const prepared = SQLiteUtility.prepare({
  sql: 'SELECT * FROM AwesomeTable WHERE id = @id and isActive = @isActive',
  params: {
    id: 83,
    isActive: true,
  },
});

console.log(prepared);
// => ['SELECT * FROM AwesomeTable WHERE id = ? and isActive = ?', [83, 1]]

db.executeSql(...prepared, (results) => {
  console.log(results);
});

The statement's sql property is used to first check the SQLite store for a stored statement. If no stored statement is found the value itself is used.

const prepared = SQLiteUtility.prepare({
  sql: 'Awesome_ReadById',
  params: {
    id: 83,
  },
});

console.log(prepared);
// => ['SELECT * FROM AwesomeTable WHERE id = ?', [83]]