README
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.
Argument | Description | Type |
---|---|---|
config |
Database configuration. | SQLiteDatabaseConfig |
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.
Argument | Description | Type |
---|---|---|
statement |
Statement to execute. | SQLiteStatement |
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.
Argument | Description | Type |
---|---|---|
statements |
Statements to execute. | SQLiteStatement[] |
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.
Argument | Description | Type |
---|---|---|
statement |
Statement to prepare. | SQLiteStatement |
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]]