@capacitor-community/sqlite

SQLITE DATABASE

@capacitor-community/sqlite

CAPACITOR 3

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

Maintainers

Maintainer	GitHub	Social
Quéau Jean Pierre	jepiqueau

CAPACITOR 3 (Master)

🚨 Since release 3.2.3-1 ->> 🚨

The initWebStore and saveToStore methods have been added to the Web platform.

• The initWebStore has been added to fix the issue#172 and since then is MANDATORY

...
if(platform === "web") {
  await customElements.whenDefined('jeep-sqlite');
  const jeepSqliteEl = document.querySelector('jeep-sqlite');
  if(jeepSqliteEl != null) {
    await sqliteConnection.initWebStore()
    ...
  }
}
...

• the saveToStore allows to perform intermediate save of the database in case the browser needs to delete the cache.

🚨 Since release 3.2.2-2 ->> 🚨

The executeSet method accepts now no values, see below

const setIssue170: Array<capSQLiteSet>  = [
  { statement: "DROP TABLE IF EXISTS issue170", values: [] },
  { statement: "CREATE TABLE issue170 (src VARCHAR(255))", values: [] },
  { statement: "INSERT INTO issue170 (src) values (?)", values: ["google.com"] },
]

🚨 Since release 3.2.0-5 ->> 🚨

The Web plugin is now implemented based on the stencil companion jeep-sqlite@0.0.7 which is using sql.js@1.5.0 for database queries and localeforage@1.9.0for database persistency.

🚨 Since release 3.2.0-3 ->> 🚨

The Electron plugin is now based on @capacitor-community/electron@4.0.3 thanks to the hard and heavy work from Mike Summerfeldt IT-MikeS 👏 🙏

🚨 Since release 3.2.0-2 ->> 🚨 🚨 !!! for Electron developper, the Electron plugin is back !!! 🚨

Based on sqlite3, so without encryption The two listeners sqliteImportProgressEvent and sqliteExportProgressEvent are not available.

🚨 Since release 3.1.2 ->> 🚨

Thanks to Nirajhinge and Chris, an example of using the TypeORM driver in a Ionic/Vue app has been developed see https://github.com/jepiqueau/vue-typeorm-app.

🚨 Since release 3.0.0-rc.2 ->> 🚨

Thanks to Chris, a driver to TypeORM is now available

🚨 Since release 3.0.0-beta.13 ->> 🚨

• GlobalSQLite secret and newsecret are deprecated

• The user can now set its own secure secret (passphrase)

  • use setEncryptionSecret ONCE to migrate encrypted databases from secret to secure stored secret

  • use changeEncryptionSecret to change your secure stored secret

• iOS used KeyChain service to store the secret

• Android used Encrypted SharedPreferences to store the secret, the minimun sdk should be set to 23 (limitation from Google)

🚨 Since release 3.0.0-beta.13 ->> 🚨

🚨 Since release 3.0.0-beta.11 ->> 🚨

• Checking of types has been removed in all methods of the plugin both iOS & Android. This has been achieved to allow the use of others RDBMS types. The type checking is now under the responsability of the developers.

• NULL values are now returned as null

• values for the query method is now an Array of any.

• option to disable transaction for the execute, executeSet, run.

🚨 Since release 3.0.0-beta.11 <<- 🚨

The test has been achieved on:

• a Ionic/Angular app

• a Ionic/React app

• a Ionic/Vue app

REFACTOR (Move to branch 2.9.x)

The 2.9.x is now 🛑 NOT MAINTAINED ANYMORE 🛑.

The refactor offers now (since 2.9.0-beta.1) all the features that the previous was offering. It has been a quite heavy process, hoping that the developpers will take benefit from it.

The main reasons for it:

• multiple database connections
• db connector allowing for easy commands db.open(), db.close, ...
• improve the response time of the encrypted database by removing the internal open and close database for each sqlite query
• moving to the latest androidx.sqlite.db.xxx
• offering encryption for Electron platform by using @journeyapps/sqlcipher on MacOs, !!! NOT ON WINDOWS !!!
• cleaning and aligning the code between platforms
• allowing developers to develop typeorm or spatialite drivers.

This was discussed lengthly in issue#1 and issue#52

Refactor available for Android, iOS and Electron platforms.

The test has been achieved on:

• a Ionic/Angular app

• a Ionic/React app

• a Ionic/Vue app

Other frameworks will be tested later

• Stencil

@INITIAL 🛑 (Move to branch 2.4.x)

The 2.4.x is now 🛑 NOT MAINTAINED ANYMORE 🛑.

To install it

npm i --save @capacitor-community/sqlite@initial

The test has been achieved on:

• a Ionic/Angular app

• a Ionic/React app

• a Ionic/Vue app

Browser Support

The plugin follows the guidelines from the Capacitor Team,

• Capacitor Browser Support

meaning that it will not work in IE11 without additional JavaScript transformations, e.g. with Babel.

Installation

npm install @capacitor-community/sqlite
npm run build
npx cap add android
npx cap add ios
npx cap add @capacitor-community/electron

and do when you update

npx cap sync
npx cap sync @capacitor-community/electron

Web

For Angular framework

• copy manually the file sql-wasm.wasm from nodes_modules/sql.js/dist/sql-wasm.wasm to the src/assets folder of YOUR_APP

For Vue & React frameworks

• copy manually the file sql-wasm.wasm from nodes_modules/sql.js/dist/sql-wasm.wasm to the public/assets folder of YOUR_APP

IOS

• on iOS, no further steps needed.

Android

• On Android, no further steps needed.

Electron

• On Electron, go to the Electron folder of YOUR_APPLICATION

cd electron
npm install --save sqlite3
npm install --save-dev @types/sqlite3
npm run build

Build & Run

npm run build
npx cap copy
npx cap copy web
npx cap copy @capacitor-community/electron

Web

• Angular

ionic serve

• Vue

npm run serve

• React

npm run start

IOS

npx cap open ios

Android

npx cap open android

Electron

npx cap open @capacitor-community/electron

Issues

issues

Configuration

No configuration required for this plugin

Supported methods

Name	Android	iOS	Electron	Web
createConnection	✅	✅	✅	✅
closeConnection	✅	✅	✅	✅
isConnection	✅	✅	✅	✅
open (non-encrypted DB)	✅	✅	✅	✅
open (encrypted DB)	✅	✅	❌	❌
close	✅	✅	✅	✅
getVersion	✅	✅	✅	✅
execute	✅	✅	✅	✅
executeSet	✅	✅	✅	✅
run	✅	✅	✅	✅
query	✅	✅	✅	✅
deleteDatabase	✅	✅	✅	✅
importFromJson	✅	✅	✅	✅
exportToJson	✅	✅	✅	✅
createSyncTable	✅	✅	✅	✅
setSyncDate	✅	✅	✅	✅
getSyncDate	✅	✅	✅	✅
isJsonValid	✅	✅	✅	✅
isDBExists	✅	✅	✅	✅
addUpgradeStatement	✅	✅	✅	✅
copyFromAssets	✅	✅	✅	✅
isDBOpen	✅	✅	✅	✅
isDatabase	✅	✅	✅	✅
isTableExists	✅	✅	✅	✅
getDatabaseList	✅	✅	✅	✅
addSQLiteSuffix	✅	✅	❌	❌
deleteOldDatabases	✅	✅	❌	❌
checkConnectionsConsistency	✅	✅	✅	✅
isSecretStored	✅	✅	❌	❌
setEncryptionSecret	✅	✅	❌	❌
changeEncryptionSecret	✅	✅	❌	❌
initWebStore	❌	❌	❌	✅
saveToStore	❌	❌	❌	✅

Supported SQLite Types

-Datatypes In SQLite Version 3

Documentation

API

• API_Documentation

• API_Connection_Wrapper_Documentation

• API_DB_Connection_Wrapper_Documentation

• ImportExportJson_Documentation

• UpgradeDatabaseVersion_Documentation

• MigratingCordovaDatabases_Documentation

• TypeORM_Documentation

• Web_Documentation

Framework's Usage

• Ionic/Angular_Usage_Documentation

• Ionic/React_Usage_Documentation

• Ionic/Vue_Usage_Documentation

Applications demonstrating the use of the plugin

Ionic/Angular

• angular-sqlite-app-starter

Ionic/React

• react-sqlite-app-starter

Ionic/Vue

• vue-sqlite-app-starter

Vue

• vue-sqlite-app

Usage

• see capacitor documentation

Dependencies

The IOS and Android codes are using SQLCipher allowing for database encryption. The Electron code is using sqlite3. The Web code is using the Stencil component jeep-sqlite based on sql.js and localforage.

Contributors ✨

Thanks goes to these wonderful people (emoji key):

Jean Pierre Quéau 💻

Paul Antoine 💻

Karyfars 💻

Chriswep 📖

Nirajhinge 💻

Dirk Gausmann 💻

Mike Summerfeldt 💻

Peakcool 💻

This project follows the all-contributors specification. Contributions of any kind welcome!