SQLITE DATABASE

@capacitor-community/sqlite

Capacitor community plugin for Native and Electron SQLite Databases. - In Native, databases could be encrypted with `SQLCipher` - In Electron, databases could be encrypted with `better-sqlite3-multiple-ciphers`

Maintainers

Maintainer	GitHub	Social
Robin Genz	robingenz	@robin_genz

To install:

npm install --save @capacitor-community/sqlite
npx cap sync

yarn add @capacitor-community/sqlite
npx cap sync

pnpm install --save @capacitor-community/sqlite
pnpm install --save jeep-sqlite
pnpm install --save sql.js
npx cap sync

then add plugin to main capacitor.config.ts file:

import { CapacitorConfig } from '@capacitor/cli';

const config: CapacitorConfig = {
  appId: 'com.jeep.app.ionic7.angular.sqlite',
  appName: 'ionic7-angular-sqlite-starter',
  webDir: 'www',
  server: {
    androidScheme: 'https'
  },
  plugins: {
    CapacitorSQLite: {
      iosDatabaseLocation: 'Library/CapacitorDatabase',
      iosIsEncryption: true,
      iosKeychainPrefix: 'angular-sqlite-app-starter',
      iosBiometric: {
        biometricAuth: false,
        biometricTitle : "Biometric login for capacitor sqlite"
      },
      androidIsEncryption: true,
      androidBiometric: {
        biometricAuth : false,
        biometricTitle : "Biometric login for capacitor sqlite",
        biometricSubTitle : "Log in using your biometric"
      },
      electronIsEncryption: true,
      electronWindowsLocation: "C:\\ProgramData\\CapacitorDatabases",
      electronMacLocation: "/Volumes/Development_Lacie/Development/Databases",
      electronLinuxLocation: "Databases"
    }
  }
};
export default config;

More Reading:

• Updating to Capacitor 5
• Releases
• Changelog
• Issues
• Capacitor documentation
• Datatypes In SQLite Version 3
• IncrementalUpgradeDatabaseVersion

Tutorials Blog

• JeepQ Capacitor Plugin Tutorials

Web Quirks

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. You'll need the usual capacitor/android/react npm script to build and copy the assets folder.

For Angular framework

• Copy manually the file sql-wasm.wasm from node_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 node_modules/sql.js/dist/sql-wasm.wasm to the public/assets folder of YOUR_APP

Android Quirks

• In case you get the following error when building your app in Android Studio: x files found with path 'build-data.properties'. You can you add the following code to app/build.gradle:

    packagingOptions {
        exclude 'build-data.properties'
    }

See #301 and SO question for more information.

• Check/Add the following: Gradle JDK version 17 Android Gradle Plugin Version 8.0.0 In variables.gradle

  minSdkVersion = 22
  compileSdkVersion = 33
  targetSdkVersion = 33
  

  In AndroidManifest.xml

      <application
        android:allowBackup="false"
        android:fullBackupContent="false"
        android:dataExtractionRules="@xml/data_extraction_rules"
  

  In res/xml create a file data_extraction_rules.xml containing:

  <?xml version="1.0" encoding="utf-8"?>
  <data-extraction-rules>
      <cloud-backup>
        <exclude domain="root" />
        <exclude domain="database" />
        <exclude domain="sharedpref" />
        <exclude domain="external" />
      </cloud-backup>
      <device-transfer>
        <exclude domain="root" />
        <exclude domain="database" />
        <exclude domain="sharedpref" />
        <exclude domain="external" />
      </device-transfer>
  </data-extraction-rules>
  

Electron Quirks

• On Electron, go to the Electron folder of YOUR_APPLICATION

cd electron
npm install --save better-sqlite3-multiple-ciphers
npm install --save electron-json-storage
npm install --save jszip
npm install --save [email protected]
npm install --save crypto
npm install --save crypto-js
npm install --save-dev @types/better-sqlite3
npm install --save-dev @types/electron-json-storage
npm install --save-dev @types/crypto-js

• Important: node-fetch version must be <=2.6.7; otherwise you'll get an error running the app.

• Important: if you are using @capacitor-community/electron v5

  • you have to stick to [email protected] till further notice so do:

npm install --save-dev [email protected]
npm uninstall --save-dev electron-rebuild
npm install --save-dev @electron/rebuild
npm install --save-dev [email protected]

• in electron folder open the tsconfig.json file and add "skipLibCheck": true,

IOS Quirks

• on iOS, no further steps needed.

Supported Methods by Platform

Name	Android	iOS	Electron	Web
createConnection (ReadWrite)	✅	✅	✅	✅
createConnection (ReadOnly)	✅	✅	✅	❌
closeConnection (ReadWrite)	✅	✅	✅	✅
closeConnection (ReadOnly)	✅	✅	✅	❌
isConnection (ReadWrite)	✅	✅	✅	✅
isConnection (ReadOnly)	✅	✅	✅	❌
open (non-encrypted DB)	✅	✅	✅	✅
open (encrypted DB)	✅	✅	✅	❌
close	✅	✅	✅	✅
getUrl	✅	✅	❌	❌
getVersion	✅	✅	✅	✅
execute	✅	✅	✅	✅
executeSet	✅	✅	✅	✅
run	✅	✅	✅	✅
query	✅	✅	✅	✅
deleteDatabase	✅	✅	✅	✅
importFromJson	✅	✅	✅	✅
exportToJson	✅	✅	✅	✅
deleteExportedRows	✅	✅	✅	✅
createSyncTable	✅	✅	✅	✅
setSyncDate	✅	✅	✅	✅
getSyncDate	✅	✅	✅	✅
isJsonValid	✅	✅	✅	✅
isDBExists	✅	✅	✅	✅
addUpgradeStatement	✅	✅	✅	✅
copyFromAssets	✅	✅	✅	✅
isDBOpen	✅	✅	✅	✅
isDatabase	✅	✅	✅	✅
isTableExists	✅	✅	✅	✅
getTableList	✅	✅	✅	✅
getDatabaseList	✅	✅	✅	✅
getMigratableDbList	✅	✅	❌	❌
addSQLiteSuffix	✅	✅	❌	❌
deleteOldDatabases	✅	✅	❌	❌
moveDatabasesAndAddSuffix	✅	✅	❌	❌
checkConnectionsConsistency	✅	✅	✅	✅
isSecretStored	✅	✅	✅	❌
setEncryptionSecret	✅	✅	✅	❌
changeEncryptionSecret	✅	✅	✅	❌
clearEncryptionSecret	✅	✅	✅	❌
checkEncryptionSecret	✅	✅	✅	❌
initWebStore	❌	❌	❌	✅
saveToStore	❌	❌	❌	✅
getNCDatabasePath	✅	✅	❌	❌
createNCConnection	✅	✅	❌	❌
closeNCConnection	✅	✅	❌	❌
isNCDatabase	✅	✅	❌	❌
transaction	✅	✅	✅	✅
getFromHTTPRequest	✅	✅	✅	✅
isDatabaseEncrypted	✅	✅	✅	❌
isInConfigEncryption	✅	✅	✅	❌
isInConfigBiometricAuth	✅	✅	❌	❌
getFromLocalDiskToStore	❌	❌	❌	✅
saveToLocalDisk	❌	❌	❌	✅
beginTransaction	✅	✅	✅	✅
commitTransaction	✅	✅	✅	✅
rollbackTransaction	✅	✅	✅	✅
isTransactionActive	✅	✅	✅	✅

Documentation & APIs

• API

• API Connection Wrapper

• API DB Connection Wrapper

• Import-Export Json

• Upgrade Database Version

• Migrating Cordova Databases

• Type ORM

• TypeORM-From-5.6.0

• Web Usage

• Non Conformed Databases

• Biometric Authentication

• Enable SQLite Schema Error Syntax Highlighting

• Electron Better SQLite3

• Enable minified build on Android

Applications demonstrating the use of the plugin and related documentation

Ionic/Angular

• Web ionic7-angular-sqlite-app Ionic 7 Angular 16 Capacitor 5 SQLite CRUD operations for Web.

• Native ionic7-angular-sqlite-app Ionic 7 Angular 16 Capacitor 5 SQLite CRUD operations for iOS, Android and Electron.

• angular-sqlite-synchronize-app (Not Updated)

Ionic/Angular TypeORM app (Not Updated)

• ionic-sqlite-typeorm-app

Ionic/React

• Web ionic7-react-sqlite-app Ionic7 React18.2.0 Vite4.3.9 Capacitor 5 SQLite CRUD operations for Web.

• Web ionic7-react-sqlite-app Ionic7 React18.2.0 Vite4.3.9 Capacitor 5 SQLite CRUD operations for iOS, Android and Electron.

Ionic/React Capacitor SQLite + TypeORM Example App

• capacitor-sqlite-react-typeorm-app

Ionic/Vue

• Web ionic7-vue-sqlite-app Ionic7 Vue3.2.45 Vite4.3.9 Capacitor 5 SQLite CRUD operations for Web.

• Web ionic7-vue-sqlite-app Ionic7 Vue3.2.45 Vite4.3.9 Capacitor 5 SQLite CRUD operations for iOS, Android and Electron.

Vue (Not Updated)

• vue-sqlite-app

Vue TypeORM app (Not Updated)

• vue-typeorm-app

SolidJS+Vite (Not Updated)

• solidjs-vite-sqlite-app

Nuxt3 + Kysely

• nuxt3-capacitor-sqlite-kysely-example

Quasar

• Web quasar-sqlite-app Quasar2.6.0 Capacitor 5 SQLite CRUD operations for Web.

• Native quasar-sqlite-app Quasar2.6.0 Capacitor 5 SQLite CRUD operations for iOS, Android and Electron.

SvelteKit

• Web/Native vite-sveltekit-capacitor-sqlite

Dependencies

The iOS and Android codes are using SQLCipher allowing for database encryption. The iOS codes is using ZIPFoundation for unzipping assets files The Electron code is using better-sqlite3-multiple-ciphers , electron-json-storage and node-fetch from 5.0.4. The Web code is using the Stencil component jeep-sqlite based on sql.js, localforage. and jszip

Contributors ✨

Thanks goes to these wonderful people (emoji key):

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

Credits

A big thank you to Jean Pierre Quéau, who maintained this plugin until version 6.0.0.