API.md

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 used in conjunction 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 https://github.com/capacitor-community/sqlite/issues/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

API Plugin

initWebStore() => Promise<void>

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

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

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

isSecretStored() => Promise<capSQLiteResult>

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

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

clearEncryptionSecret() => Promise<void>

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

isInConfigEncryption() => Promise<capSQLiteResult>

isInConfigBiometricAuth() => Promise<capSQLiteResult>

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

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

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

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

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

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

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

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

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

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

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

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

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

getDatabaseList() => Promise<capSQLiteValues>

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

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

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

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

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

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

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

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

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

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

Listeners (NOT AVAILABLE FOR ELECTRON PLATFORM)

Available since 3.0.0-beta.12

The listeners are attached to the plugin.

capJsonProgressListener

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