API PLUGIN DOCUMENTATION

@capacitor-community/sqlite

Capacitor community plugin for Native and Electron SQLite Databases. For Native and Electron platforms, databases could be encrypted with SQLCipher

Plugin Wrappers

To easy the way to use the @capacitor-community/sqlite plugin and its ability to be use in conjonction with other plugins (typeorm, spatialite, ...), two connection wrappers have been associated.

API_Connection_Wrapper_Documentation
API_DB_Connection_Wrapper_Documentation

SQLite Commands Within the Plugin

SQLite Data Definition Language commands (such as CREATE, ALTER, DROP) should be executed using the execute plugin method.
SQLite Transaction Control commands (including BEGIN TRANSACTION, COMMIT, ROLLBACK) should also be executed using the execute plugin method.
SQLite Data Manipulation Language commands (like INSERT, UPDATE, DELETE, REPLACE) should use the run plugin method if they involve bind values. They can utilize either the execute or run plugin methods if no bind values are involved.
SQLite Data Query Language commands (SELECT) should be executed using the query plugin method.
SQLite Special commands (PRAGMA) should be executed using the execute plugin method.

Databases Location

The plugin add a suffix "SQLite" and an extension ".db" to the database name given as options in the capConnectionOptions or capSQLiteOptions ie (fooDB -> fooDBSQLite.db). If the name given contains the extension .db it will be removed ie (foo.db) will become internally (fooSQLite.db) after adding the suffix.

Android

in data/data/YOUR_PACKAGE/databases

IOS

in the Documents folder of YOUR_APPLICATION

or in the folder specified by the capacitor.config.ts file of YOUR_APPLICATION since 3.3.3-2 In that case the databases will not be not visible to iTunes and not backed up to iCloud.

const config: CapacitorConfig = {
  appId: 'io.ionic.starter',
  appName: 'testreact',
  webDir: 'build',
  bundledWebRuntime: false,
  plugins: {
    CapacitorSQLite: {
      "iosDatabaseLocation": "Library/CapacitorDatabase"
    }
  }
};

Electron

since 2.4.2-1 the databases location is : User/Databases/APP_NAME/
since 3.4.1 the databases location can be set in the config.config.ts as followed:
- for sharing databases between users:
```
plugins: {
  CapacitorSQLite: {
    electronMacLocation: "/YOUR_DATABASES_PATH",
    electronWindowsLocation: "C:\\ProgramData\\CapacitorDatabases",
    electronLinuxLocation: "/home/CapacitorDatabases"
  }
}
```
- for only the user in its Home folder: User/Databases/APP_NAME/
```
Plugins: {
  CapacitorSQLite: {
    electronMacLocation: "Databases",
    electronWindowsLocation: "Databases",
    electronLinuxLocation: "Databases"
  }
}
```
  You can replace "Databases" by your "YOUR_DATABASES_LOCATION", but it MUST not have any "/" or "\" characters.
For existing databases, YOU MUST COPY old databases to the new location You MUST remove the Electron folder and add it again.

Web

the database is stored in Web browser INDEXEDDB storage as a localforage store under the jeepSqliteStore name and databases table name.

Comments within SQL statements

see Comments within SQL
Some examples

 const setContacts: Array<capSQLiteSet>  = [
   { statement:"INSERT INTO contacts /* Contact Simpson */ (name,FirstName,email,company,age,MobileNumber) VALUES (?,?,?,?,?,?);",
     values:["Simpson","Tom","Simpson@example.com",,69,"4405060708"]
   },
   { statement:"INSERT INTO contacts /* three more contacts */ (name,FirstName,email,company,age,MobileNumber) VALUES (?,?,?,?,?,?) -- Add Jones, Whiteley and Brown;",
     values:[
       ["Jones","David","Jones@example.com",,42.1,"4404030201"],
       ["Whiteley","Dave","Whiteley@example.com",,45.3,"4405162732"],
       ["Brown","John","Brown@example.com",,35,"4405243853"]
     ]
   },
   { statement:"UPDATE contacts SET age = ? , MobileNumber = ? WHERE id = ? -- Update Jones Contact;",
     values:[51.4,"4404030202",6]
   }
 ];
 const setMessages: Array<capSQLiteSet>  = [
   { statement:`
   /* Define the messages table */
   CREATE TABLE IF NOT EXISTS messages (
     id INTEGER PRIMARY KEY NOT NULL,
     contactid INTEGER, -- key to contacts(id)
     title TEXT NOT NULL,
     body TEXT NOT NULL,
     last_modified INTEGER DEFAULT (strftime('%s', 'now')),
     FOREIGN KEY (contactid) REFERENCES contacts(id) ON DELETE SET DEFAULT
   );`,
     values:[]
   },
 ];

 let insertQuery = 'INSERT INTO contacts (name,FirstName,email,company,age,MobileNumber) VALUES (?, ?, ?, ?, ?, ?) -- Add Sue Hellen;';
 let bindValues = ["Hellen","Sue","sue.hellen@example.com",,42,"4406050807"];
 let ret = await db.run(insertQuery, bindValues);
 console.log(`>>> run ret 1: ${JSON.stringify(ret)}`)
 insertQuery = `INSERT INTO contacts /* some contacts */ (name,FirstName,email,company,age,MobileNumber) VALUES 
     ('Doe','John','john.doe@example.com', 'IBM', 30, '4403050926'), -- add Doe
     ('Watson','Dave','dave.watson@example.com','Apple', 30, '4407050932') /* add Watson */,
     ('Smith', 'Jane', 'jane.smith@example.com', 'IBM', 27, '33607556142') /* Add Smith */-- End of add contact;`;
 bindValues = [];
 ret = await db.run(insertQuery, bindValues);
 console.log(`>>> run ret 2: ${JSON.stringify(ret)}`)

 let selectQuery = "SELECT * /* all columns */ FROM contacts WHERE company = 'IBM' -- for company IBM;";

 ret = await db.query(selectQuery);
 console.log(`>>> query "IBM" ret: ${JSON.stringify(ret)}`)

 ret = await db.executeSet(setContacts);
 console.log(`>>> executeSet 1 ret: ${JSON.stringify(ret)}`)

 selectQuery = "SELECT email /* only email */ FROM contacts WHERE company ISNULL -- for company not given;";


 ret = await db.executeSet(setMessages);
 console.log(`>>> executeSet 2 ret: ${JSON.stringify(ret)}`)

Unexpected behaviours

Unexpected or erroneous behaviour users of this library have encountered.

1. Running multiple update statements in one statement

The Problem:

In #393 a user of this library experienced bugs when running a statement that itself contained multiple update statements.

The statement executed fine on the web version of this library (sql-wasm.wasm).

But on android and IOS only some updates took place, some updates were ignored and did not take effect in the database.

The Solution:

When running multiple update statements and experiencing such errors, try running them in separate single statements and await (Promise) each statement to finish running before running the next statement.

Note that in general in SQLite this is not recommended, since it makes your queries take a bit longer.

Write-Ahead Logging (WAL)

Electron, Web platforms only WAL journal_mode is implemented
Both WAL and WAL2 journal_mode are implemented
Android WAL2 is set by default, so you do not need to set it up

Error Return values

For all methods, a message containing the error message will be returned
For execute and run commands, {changes:{changes: -1}} will be returned in changes
For query command, an empty array will be returned in values

Defining your own secret and newsecret keys (encryption only)

in IOS, go to the Pod/Development Pods/capacitor-sqlite/GlobalSQLite.swift file
in Android, go to capacitor-sqlite/java/com.jeep.plugin.capacitor/cdssUtils/GlobalSQLite.java and update the default values before building your app.
in Electron, go to YOUR_APP/electron/plugins/plugin.js-xxxx.js and search for class GlobalSQLite and modify the this.secretand this.newsecret parameters.

Methods Index

initWebStore()
saveToStore(...)
getFromLocalDiskToStore(...)
saveToLocalDisk(...)
isSecretStored()
setEncryptionSecret(...)
changeEncryptionSecret(...)
clearEncryptionSecret()
checkEncryptionSecret(...)
createConnection(...)
closeConnection(...)
echo(...)
open(...)
close(...)
beginTransaction(...)
commitTransaction(...)
rollbackTransaction(...)
isTransactionActive(...)
getUrl(...)
getVersion(...)
execute(...)
executeSet(...)
run(...)
query(...)
isDBExists(...)
isDBOpen(...)
isDatabaseEncrypted(...)
isInConfigEncryption()
isInConfigBiometricAuth()
isDatabase(...)
isTableExists(...)
deleteDatabase(...)
isJsonValid(...)
importFromJson(...)
exportToJson(...)
createSyncTable(...)
setSyncDate(...)
getSyncDate(...)
deleteExportedRows(...)
addUpgradeStatement(...)
copyFromAssets(...)
getFromHTTPRequest(...)
getDatabaseList()
getTableList(...)
getMigratableDbList(...)
addSQLiteSuffix(...)
deleteOldDatabases(...)
moveDatabasesAndAddSuffix(...)
checkConnectionsConsistency(...)
getNCDatabasePath(...)
createNCConnection(...)
closeNCConnection(...)
isNCDatabase(...)
Interfaces

* [Listeners](#listeners)

API Plugin

CapacitorSQLitePlugin Interface

initWebStore()

initWebStore() => Promise<void>

Initialize the web store

Since: 3.2.3-1

saveToStore(...)

saveToStore(options: capSQLiteOptions) => Promise<void>

Save database to the web store

Param	Type	Description
`options`	`capSQLiteOptions`	: capSQLiteOptions

Since: 3.2.3-1

getFromLocalDiskToStore(...)

getFromLocalDiskToStore(options: capSQLiteLocalDiskOptions) => Promise<void>

Get database from local disk and save it to store

Param	Type	Description
`options`	`capSQLiteLocalDiskOptions`	: capSQLiteLocalDiskOptions

Since: 4.6.3

saveToLocalDisk(...)

saveToLocalDisk(options: capSQLiteOptions) => Promise<void>

Save database to local disk

Param	Type	Description
`options`	`capSQLiteOptions`	: capSQLiteOptions

Since: 4.6.3

isSecretStored()

isSecretStored() => Promise<capSQLiteResult>

Check if a passphrase exists in a secure store

Returns: Promise<capSQLiteResult>

Since: 3.0.0-beta.13

setEncryptionSecret(...)

setEncryptionSecret(options: capSetSecretOptions) => Promise<void>

Store a passphrase in a secure store Update the secret of previous encrypted databases with GlobalSQLite !!! Only to be used once if you wish to encrypt database !!!

Param	Type	Description
`options`	`capSetSecretOptions`	capSetSecretOptions

Since: 3.0.0-beta.13

changeEncryptionSecret(...)

changeEncryptionSecret(options: capChangeSecretOptions) => Promise<void>

Change the passphrase in a secure store Update the secret of previous encrypted databases with passphrase in secure store

Param	Type	Description
`options`	`capChangeSecretOptions`	capChangeSecretOptions

Since: 3.0.0-beta.13

clearEncryptionSecret()

clearEncryptionSecret() => Promise<void>

Clear the passphrase in the secure store

Since: 3.5.1

checkEncryptionSecret(...)

checkEncryptionSecret(options: capSetSecretOptions) => Promise<capSQLiteResult>

Check encryption passphrase

Param	Type
`options`	`capSetSecretOptions`

Returns: Promise<capSQLiteResult>

Since: 4.6.1

createConnection(...)

createConnection(options: capConnectionOptions) => Promise<void>

create a database connection

Param	Type	Description
`options`	`capConnectionOptions`	capConnectionOptions

Since: 2.9.0 refactor

closeConnection(...)

closeConnection(options: capSQLiteOptions) => Promise<void>

close a database connection

Param	Type	Description
`options`	`capSQLiteOptions`	capSQLiteOptions

Since: 2.9.0 refactor

echo(...)

echo(options: capEchoOptions) => Promise<capEchoResult>

Echo a given string

Param	Type	Description
`options`	`capEchoOptions`	: capEchoOptions

Returns: Promise<capEchoResult>

Since: 0.0.1

open(...)

open(options: capSQLiteOptions) => Promise<void>

Opens a SQLite database. Attention: This re-opens a database if it's already open!

Param	Type	Description
`options`	`capSQLiteOptions`	: capSQLiteOptions

Since: 0.0.1

close(...)

close(options: capSQLiteOptions) => Promise<void>

Close a SQLite database

Param	Type	Description
`options`	`capSQLiteOptions`	: capSQLiteOptions

Since: 0.0.1

beginTransaction(...)

beginTransaction(options: capSQLiteOptions) => Promise<capSQLiteChanges>

Begin Database Transaction

Param	Type
`options`	`capSQLiteOptions`

Returns: Promise<capSQLiteChanges>

Since: 5.0.7

commitTransaction(...)

commitTransaction(options: capSQLiteOptions) => Promise<capSQLiteChanges>

Commit Database Transaction

Param	Type
`options`	`capSQLiteOptions`

Returns: Promise<capSQLiteChanges>

Since: 5.0.7

rollbackTransaction(...)

rollbackTransaction(options: capSQLiteOptions) => Promise<capSQLiteChanges>

Rollback Database Transaction

Param	Type
`options`	`capSQLiteOptions`

Returns: Promise<capSQLiteChanges>

Since: 5.0.7

isTransactionActive(...)

isTransactionActive(options: capSQLiteOptions) => Promise<capSQLiteResult>

Is Database Transaction Active

Param	Type
`options`	`capSQLiteOptions`

Returns: Promise<capSQLiteResult>

Since: 5.0.7

getUrl(...)

getUrl(options: capSQLiteOptions) => Promise<capSQLiteUrl>

GetUrl get the database Url

Param	Type	Description
`options`	`capSQLiteOptions`	: capSQLiteOptions

Returns: Promise<capSQLiteUrl>

Since: 3.3.3-4

getVersion(...)

getVersion(options: capSQLiteOptions) => Promise<capVersionResult>

Get a SQLite database version

Param	Type	Description
`options`	`capSQLiteOptions`	: capSQLiteOptions

Returns: Promise<capVersionResult>

Since: 3.2.0

execute(...)

execute(options: capSQLiteExecuteOptions) => Promise<capSQLiteChanges>

Execute a Batch of Raw Statements as String

Param	Type	Description
`options`	`capSQLiteExecuteOptions`	: capSQLiteExecuteOptions

Returns: Promise<capSQLiteChanges>

Since: 0.0.1

executeSet(...)

executeSet(options: capSQLiteSetOptions) => Promise<capSQLiteChanges>

Execute a Set of Raw Statements as Array of CapSQLiteSet

Param	Type	Description
`options`	`capSQLiteSetOptions`	: capSQLiteSetOptions

Returns: Promise<capSQLiteChanges>

Since: 2.2.0-2

run(...)

run(options: capSQLiteRunOptions) => Promise<capSQLiteChanges>

Execute a Single Statement

Param	Type	Description
`options`	`capSQLiteRunOptions`	: capSQLiteRunOptions

Returns: Promise<capSQLiteChanges>

Since: 0.0.1

query(...)

query(options: capSQLiteQueryOptions) => Promise<capSQLiteValues>

Query a Single Statement

Param	Type	Description
`options`	`capSQLiteQueryOptions`	: capSQLiteQueryOptions

Returns: Promise<capSQLiteValues>

Since: 0.0.1

isDBExists(...)

isDBExists(options: capSQLiteOptions) => Promise<capSQLiteResult>

Check if a SQLite database exists with opened connection

Param	Type	Description
`options`	`capSQLiteOptions`	: capSQLiteOptions

Returns: Promise<capSQLiteResult>

Since: 2.0.1-1

isDBOpen(...)

isDBOpen(options: capSQLiteOptions) => Promise<capSQLiteResult>

Check if a SQLite database is opened

Param	Type	Description
`options`	`capSQLiteOptions`	: capSQLiteOptions

Returns: Promise<capSQLiteResult>

Since: 3.0.0-beta.5

isDatabaseEncrypted(...)

isDatabaseEncrypted(options: capSQLiteOptions) => Promise<capSQLiteResult>

Check if a SQLite database is encrypted

Param	Type	Description
`options`	`capSQLiteOptions`	: capSQLiteOptions

Returns: Promise<capSQLiteResult>

Since: 4.6.2-2

isInConfigEncryption()

isInConfigEncryption() => Promise<capSQLiteResult>

Check encryption value in capacitor.config

Returns: Promise<capSQLiteResult>

Since: 4.6.2-2

isInConfigBiometricAuth()

isInConfigBiometricAuth() => Promise<capSQLiteResult>

Check encryption value in capacitor.config

Returns: Promise<capSQLiteResult>

Since: 4.6.2-2

isDatabase(...)

isDatabase(options: capSQLiteOptions) => Promise<capSQLiteResult>

Check if a SQLite database exists without connection

Param	Type	Description
`options`	`capSQLiteOptions`	: capSQLiteOptions

Returns: Promise<capSQLiteResult>

Since: 3.0.0-beta.5

isTableExists(...)

isTableExists(options: capSQLiteTableOptions) => Promise<capSQLiteResult>

Check if a table exists in a SQLite database

Param	Type	Description
`options`	`capSQLiteTableOptions`	: capSQLiteTableOptions

Returns: Promise<capSQLiteResult>

Since: 3.0.0-beta.5

deleteDatabase(...)

deleteDatabase(options: capSQLiteOptions) => Promise<void>

Delete a SQLite database

Param	Type	Description
`options`	`capSQLiteOptions`	: capSQLiteOptions

Since: 0.0.1

isJsonValid(...)

isJsonValid(options: capSQLiteImportOptions) => Promise<capSQLiteResult>

Is Json Object Valid

Param	Type	Description
`options`	`capSQLiteImportOptions`	: capSQLiteImportOptions

Returns: Promise<capSQLiteResult>

Since: 2.0.1-1

importFromJson(...)

importFromJson(options: capSQLiteImportOptions) => Promise<capSQLiteChanges>

Import from Json Object

Param	Type	Description
`options`	`capSQLiteImportOptions`	: capSQLiteImportOptions

Returns: Promise<capSQLiteChanges>

Since: 2.0.0-3

exportToJson(...)

exportToJson(options: capSQLiteExportOptions) => Promise<capSQLiteJson>

Export to Json Object

Param	Type	Description
`options`	`capSQLiteExportOptions`	: capSQLiteExportOptions

Returns: Promise<capSQLiteJson>

Since: 2.0.1-1

createSyncTable(...)

createSyncTable(options: capSQLiteOptions) => Promise<capSQLiteChanges>

Create a synchronization table

Param	Type	Description
`options`	`capSQLiteOptions`	: capSQLiteOptions

Returns: Promise<capSQLiteChanges>

Since: 2.0.1-1

setSyncDate(...)

setSyncDate(options: capSQLiteSyncDateOptions) => Promise<void>

Set the synchronization date

Param	Type	Description
`options`	`capSQLiteSyncDateOptions`	: capSQLiteSyncDateOptions

Since: 2.0.1-1

getSyncDate(...)

getSyncDate(options: capSQLiteOptions) => Promise<capSQLiteSyncDate>

Get the synchronization date

Param	Type	Description
`options`	`capSQLiteOptions`	: capSQLiteOptions

Returns: Promise<capSQLiteSyncDate>

Since: 2.9.0

deleteExportedRows(...)

deleteExportedRows(options: capSQLiteOptions) => Promise<void>

Remove rows with sql_deleted = 1 after an export

Param	Type
`options`	`capSQLiteOptions`

Since: 3.4.3-2

addUpgradeStatement(...)

addUpgradeStatement(options: capSQLiteUpgradeOptions) => Promise<void>

Add the upgrade Statement for database version upgrading

Param	Type	Description
`options`	`capSQLiteUpgradeOptions`	: capSQLiteUpgradeOptions

Since: 2.4.2-6 iOS & Electron 2.4.2-7 Android

copyFromAssets(...)

copyFromAssets(options: capSQLiteFromAssetsOptions) => Promise<void>

Copy databases from public/assets/databases folder to application databases folder

Param	Type	Description
`options`	`capSQLiteFromAssetsOptions`	: capSQLiteFromAssets since 3.2.5-2

Since: 2.9.0 refactor

getFromHTTPRequest(...)

getFromHTTPRequest(options: capSQLiteHTTPOptions) => Promise<void>

Get database or zipped database(s) from url

Param	Type	Description
`options`	`capSQLiteHTTPOptions`	: capSQLiteHTTPOptions

Since: 4.1.1

getDatabaseList()

getDatabaseList() => Promise<capSQLiteValues>

Get the database list

Returns: Promise<capSQLiteValues>

Since: 3.0.0-beta.5

getTableList(...)

getTableList(options: capSQLiteOptions) => Promise<capSQLiteValues>

Get the database's table list

Param	Type
`options`	`capSQLiteOptions`

Returns: Promise<capSQLiteValues>

Since: 3.4.2-3

getMigratableDbList(...)

getMigratableDbList(options: capSQLitePathOptions) => Promise<capSQLiteValues>

Get the Migratable database list

Param	Type	Description
`options`	`capSQLitePathOptions`	: capSQLitePathOptions // only iOS & Android since 3.2.4-2

Returns: Promise<capSQLiteValues>

Since: 3.0.0-beta.5

addSQLiteSuffix(...)

addSQLiteSuffix(options: capSQLitePathOptions) => Promise<void>

Add SQLIte Suffix to existing databases

Param	Type	Description
`options`	`capSQLitePathOptions`	: capSQLitePathOptions

Since: 3.0.0-beta.5

deleteOldDatabases(...)

deleteOldDatabases(options: capSQLitePathOptions) => Promise<void>

Delete Old Cordova databases

Param	Type	Description
`options`	`capSQLitePathOptions`	: capSQLitePathOptions

Since: 3.0.0-beta.5

moveDatabasesAndAddSuffix(...)

moveDatabasesAndAddSuffix(options: capSQLitePathOptions) => Promise<void>

Moves databases to the location the plugin can read them, and adds sqlite suffix This resembles calling addSQLiteSuffix and deleteOldDatabases, but it is more performant as it doesn't copy but moves the files

Param	Type	Description
`options`	`capSQLitePathOptions`	: capSQLitePathOptions

checkConnectionsConsistency(...)

checkConnectionsConsistency(options: capAllConnectionsOptions) => Promise<capSQLiteResult>

Check Connection Consistency JS <=> Native return true : consistency, connections are opened return false : no consistency, connections are closed

Param	Type	Description
`options`	`capAllConnectionsOptions`	: capAllConnectionsOptions

Returns: Promise<capSQLiteResult>

Since: 3.0.0-beta.11

getNCDatabasePath(...)

getNCDatabasePath(options: capNCDatabasePathOptions) => Promise<capNCDatabasePathResult>

get a non conformed database path

Param	Type	Description
`options`	`capNCDatabasePathOptions`	capNCDatabasePathOptions

Returns: Promise<capNCDatabasePathResult>

Since: 3.3.3-1

createNCConnection(...)

createNCConnection(options: capNCConnectionOptions) => Promise<void>

create a non conformed database connection

Param	Type	Description
`options`	`capNCConnectionOptions`	capNCConnectionOptions

Since: 3.3.3-1

closeNCConnection(...)

closeNCConnection(options: capNCOptions) => Promise<void>

close a non conformed database connection

Param	Type	Description
`options`	`capNCOptions`	capNCOptions

Since: 3.3.3-1

isNCDatabase(...)

isNCDatabase(options: capNCOptions) => Promise<capSQLiteResult>

Check if a non conformed database exists without connection

Param	Type	Description
`options`	`capNCOptions`	: capNCOptions

Returns: Promise<capSQLiteResult>

Since: 3.3.3-1

Interfaces

capSQLiteOptions

Prop	Type	Description
`database`	`string`	The database name
`readonly`	`boolean`	Set to true (database in read-only mode) / false

capSQLiteLocalDiskOptions

Prop	Type	Description
`overwrite`	`boolean`	Set the overwrite mode for saving the database from local disk to store "true"/"false" default to "true"

capSQLiteResult

Prop	Type	Description
`result`	`boolean`	result set to true when successful else false

capSetSecretOptions

Prop	Type	Description
`passphrase`	`string`	The passphrase for Encrypted Databases

capChangeSecretOptions

Prop	Type	Description
`passphrase`	`string`	The new passphrase for Encrypted Databases
`oldpassphrase`	`string`	The old passphrase for Encrypted Databases

capConnectionOptions

Prop	Type	Description
`database`	`string`	The database name
`version`	`number`	The database version
`encrypted`	`boolean`	Set to true (database encryption) / false
`mode`	`string`	Set the mode for database encryption ["encryption", "secret", "newsecret"]
`readonly`	`boolean`	Set to true (database in read-only mode) / false

capEchoResult

Prop	Type	Description
`value`	`string`	String returned

capEchoOptions

Prop	Type	Description
`value`	`string`	String to be echoed

capSQLiteChanges

Prop	Type	Description
`changes`	`Changes`	a returned Changes

Changes

Prop	Type	Description
`changes`	`number`	the number of changes from an execute or run command
`lastId`	`number`	the lastId created from a run command
`values`	`any[]`	values when RETURNING

capSQLiteUrl

Prop	Type	Description
`url`	`string`	a returned url

capVersionResult

Prop	Type	Description
`version`	`number`	Number returned

capSQLiteExecuteOptions

Prop	Type	Description	Since
`database`	`string`	The database name
`statements`	`string`	The batch of raw SQL statements as string
`transaction`	`boolean`	Enable / Disable transactions default Enable (true)	3.0.0-beta.10
`readonly`	`boolean`	ReadOnly / ReadWrite default ReadWrite (false)	4.1.0-7
`isSQL92`	`boolean`	Compatibility SQL92 !!! ELECTRON ONLY default (true)	5.0.7

capSQLiteSetOptions

Prop	Type	Description	Since
`database`	`string`	The database name
`set`	`capSQLiteSet[]`	The batch of raw SQL statements as Array of capSQLLiteSet
`transaction`	`boolean`	Enable / Disable transactions default Enable (true)	3.0.0-beta.10
`readonly`	`boolean`	ReadOnly / ReadWrite default ReadWrite (false)	4.1.0-7
`returnMode`	`string`	return mode default 'no' value 'all' value 'one' for Electron platform	5.0.5-3
`isSQL92`	`boolean`	Compatibility SQL92 !!! ELECTRON ONLY default (true)	5.0.7

capSQLiteSet

Prop	Type	Description
`statement`	`string`	A statement
`values`	`any[]`	the data values list as an Array

capSQLiteRunOptions

Prop	Type	Description	Since
`database`	`string`	The database name
`statement`	`string`	A statement
`values`	`any[]`	A set of values for a statement
`transaction`	`boolean`	Enable / Disable transactions default Enable (true)	3.0.0-beta.10
`readonly`	`boolean`	ReadOnly / ReadWrite default ReadWrite (false)	4.1.0-7
`returnMode`	`string`	return mode default 'no' value 'all' value 'one' for Electron platform	5.0.5-3
`isSQL92`	`boolean`	Compatibility SQL92 !!! ELECTRON ONLY default (true)	5.0.7

capSQLiteValues

Prop	Type	Description
`values`	`any[]`	the data values list as an Array iOS the first row is the returned ios_columns name list

capSQLiteQueryOptions

Prop	Type	Description	Since
`database`	`string`	The database name
`statement`	`string`	A statement
`values`	`any[]`	A set of values for a statement Change to any[]	3.0.0-beta.11
`readonly`	`boolean`	ReadOnly / ReadWrite default ReadWrite (false)	4.1.0-7
`isSQL92`	`boolean`	Compatibility SQL92 !!! ELECTRON ONLY default (true)	5.0.7

capSQLiteTableOptions

Prop	Type	Description	Since
`database`	`string`	The database name
`table`	`string`	The table name
`readonly`	`boolean`	ReadOnly / ReadWrite default ReadWrite (false)	4.1.0-7

capSQLiteImportOptions

Prop	Type	Description
`jsonstring`	`string`	Set the JSON object to import

capSQLiteJson

Prop	Type	Description
`export`	`JsonSQLite`	an export JSON object

JsonSQLite

Prop	Type	Description
`database`	`string`	The database name
`version`	`number`	The database version
`overwrite`	`boolean`	Delete the database prior to import (default false)
`encrypted`	`boolean`	Set to true (database encryption) / false
`mode`	`string`	* Set the mode ["full", "partial"]
`tables`	`JsonTable[]`	* Array of Table (JsonTable)
`views`	`JsonView[]`	* Array of View (JsonView)

JsonTable

Prop	Type	Description
`name`	`string`	The database name
`schema`	`JsonColumn[]`	* Array of Schema (JsonColumn)
`indexes`	`JsonIndex[]`	* Array of Index (JsonIndex)
`triggers`	`JsonTrigger[]`	* Array of Trigger (JsonTrigger)
`values`	`any[][]`	* Array of Table data

JsonColumn

Prop	Type	Description
`column`	`string`	The column name
`value`	`string`	The column data (type, unique, ...)
`foreignkey`	`string`	The column foreign key constraints
`constraint`	`string`	the column constraint

JsonIndex

Prop	Type	Description
`name`	`string`	The index name
`value`	`string`	The value of the index can have the following formats: email email ASC email, MobileNumber email ASC, MobileNumber DESC
`mode`	`string`	the mode (Optional) UNIQUE

JsonTrigger

Prop	Type	Description
`name`	`string`	The trigger name
`timeevent`	`string`	The trigger time event fired
`condition`	`string`	The trigger condition
`logic`	`string`	The logic of the trigger

JsonView

Prop	Type	Description
`name`	`string`	The view name
`value`	`string`	The view create statement

capSQLiteExportOptions

Prop	Type	Description	Since
`database`	`string`	The database name
`jsonexportmode`	`string`	Set the mode to export JSON Object: "full" or "partial"
`readonly`	`boolean`	ReadOnly / ReadWrite default ReadWrite (false)	4.1.0-7
`encrypted`	`boolean`	Encrypted When your database is encrypted Choose the export Json Object Encrypted (true) / Unencrypted (false) default false	5.0.8

capSQLiteSyncDateOptions

Prop	Type	Description	Since
`database`	`string`	The database name
`syncdate`	`string`	Set the synchronization date Format yyyy-MM-dd'T'HH:mm:ss.SSSZ
`readonly`	`boolean`	ReadOnly / ReadWrite default ReadWrite (false)	4.1.0-7

capSQLiteSyncDate

Prop	Type	Description
`syncDate`	`number`	the synchronization date

capSQLiteUpgradeOptions

Prop	Type	Description
`database`	`string`	The database name
`upgrade`	`capSQLiteVersionUpgrade[]`	The upgrade options for version upgrade Array of length 1 to easiest the iOS plugin

capSQLiteVersionUpgrade

Prop	Type
`toVersion`	`number`
`statements`	`string[]`

capSQLiteFromAssetsOptions

Prop	Type	Description
`overwrite`	`boolean`	Set the overwrite mode for the copy from assets "true"/"false" default to "true"

capSQLiteHTTPOptions

Prop	Type	Description
`url`	`string`	The url of the database or the zipped database(s)
`overwrite`	`boolean`	Set the overwrite mode for the copy from assets "true"/"false" default to "true"

capSQLitePathOptions

Prop	Type	Description
`folderPath`	`string`	The folder path of existing databases If not given folder path is "default"
`dbNameList`	`string[]`	The database name's list to be copied and/or deleted since 3.2.4-1 If not given all databases in the specify folder path

capAllConnectionsOptions

Prop	Type	Description	Since
`dbNames`	`string[]`	the dbName of all connections	3.0.0-beta.10
`openModes`	`string[]`	the openMode ("RW" read&write, "RO" readonly) of all connections	4.1.0

capNCDatabasePathResult

Prop	Type	Description
`path`	`string`	String returned

capNCDatabasePathOptions

Prop	Type	Description
`path`	`string`	the database path
`database`	`string`	The database name

capNCConnectionOptions

Prop	Type	Description
`databasePath`	`string`	The database path
`version`	`number`	The database version

capNCOptions

Prop	Type	Description
`databasePath`	`string`	The database path

Listeners (NOT AVAILABLE FOR ELECTRON PLATFORM)

Available since 3.0.0-beta.12

The listeners are attached to the plugin.

Listener	Type	Description
sqliteImportProgressEvent	capJsonProgressListener	Emitted at different steps of the `importFromJson` process
sqliteExportProgressEvent	capJsonProgressListener	Emitted at different steps of the `exportToJson` process

capJsonProgressListener

Prop	Type	Description
`progress`	`string`	progress message

Remote Server databases Synchronization Process

The @capacitor-community/sqlite plugin provides a toolbox to help developpers to create a synchronization process in their applications.

importFromJson()
exportToJson()
createSyncTable()
setSyncDate()

It is Mandatory for this process to happen, that each table contains in their schema:

a first column with an id defined as:
a last column with a name last_modified
a trigger to update the last_modified field

as below:

CREATE TABLE IF NOT EXISTS [tableName] (
id INTEGER PRIMARY KEY NOT NULL,
...
last_modified INTEGER DEFAULT (strftime('%s', 'now'))
);

...
...
CREATE TRIGGER users_trigger_last_modified AFTER UPDATE ON [tableName]
FOR EACH ROW WHEN NEW.last_modified < OLD.last_modified
BEGIN
   UPDATE [tableName] SET last_modified= (strftime('%s', 'now')) WHERE id=OLD.id;
END;

Your Application has to manage the synchronization process:

transfer of the whole databases when Online for the first time using http requests and convert the response to Json Object as described in ImportExportJson_Documentation
use the ìmportFromJson method with a mode full
work Offline and add tables and/or data to tables
use the exportToJson method with a mode partial (meaning exporting everything since the last sync date)
manage the transfer back to the server
update the synchronization date locally when the transfer has been completed
and so on with partial importFromJson and exportToJson

Files

API.md

Latest commit

History

API.md

File metadata and controls

API PLUGIN DOCUMENTATION

Plugin Wrappers

SQLite Commands Within the Plugin

Databases Location

Android

IOS

Electron

Web

Comments within SQL statements

Unexpected behaviours

1. Running multiple update statements in one statement

Write-Ahead Logging (WAL)

Error Return values

Defining your own secret and newsecret keys (encryption only)

Methods Index

API Plugin

initWebStore()

saveToStore(...)

getFromLocalDiskToStore(...)

saveToLocalDisk(...)

isSecretStored()

setEncryptionSecret(...)

changeEncryptionSecret(...)

clearEncryptionSecret()

checkEncryptionSecret(...)

createConnection(...)

closeConnection(...)

echo(...)

open(...)

close(...)

beginTransaction(...)

commitTransaction(...)

rollbackTransaction(...)

isTransactionActive(...)

getUrl(...)

getVersion(...)

execute(...)

executeSet(...)

run(...)

query(...)

isDBExists(...)

isDBOpen(...)

isDatabaseEncrypted(...)

isInConfigEncryption()

isInConfigBiometricAuth()

isDatabase(...)

isTableExists(...)

deleteDatabase(...)

isJsonValid(...)

importFromJson(...)

exportToJson(...)

createSyncTable(...)

setSyncDate(...)

getSyncDate(...)

deleteExportedRows(...)

addUpgradeStatement(...)

copyFromAssets(...)

getFromHTTPRequest(...)

getDatabaseList()

getTableList(...)

getMigratableDbList(...)

addSQLiteSuffix(...)

deleteOldDatabases(...)

moveDatabasesAndAddSuffix(...)

checkConnectionsConsistency(...)

getNCDatabasePath(...)

createNCConnection(...)

closeNCConnection(...)

isNCDatabase(...)

Interfaces

capSQLiteOptions

capSQLiteLocalDiskOptions

capSQLiteResult

capSetSecretOptions