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 |

Installation

> [!IMPORTANT]
> This plugin uses the SQLCipher library (even for unencrypted databases), which is subject to the Encryption Export Regulations and may require you to submit a year-end self-classification report to the U.S. government. Read more here.

``
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:

`ts
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

Web Debugging Tools

When using the Web platform, SQLite databases are backed by jeep-sqlite and stored in IndexedDB, which can be difficult to inspect during development.

For easier debugging, there is a Chrome DevTools extension that allows browsing, querying, and exporting jeep-sqlite databases directly from IndexedDB:

- Jeep SQLite Browser (Chrome DevTools Extension)
- Chrome Web Store: https://chromewebstore.google.com/detail/jeep-sqlite-browser/ocgeealadeabmhponndjebghfkbfbnch
- GitHub: https://github.com/pinguluk/jeep-sqlite-browser

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 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 21
Android Gradle Plugin Version 8.7.2
In variables.gradle

`
minSdkVersion = 23
compileSdkVersion = 35
targetSdkVersion = 35
`
In AndroidManifest.xml
`
android:allowBackup="false"
android:fullBackupContent="false"
android:dataExtractionRules="@xml/data_extraction_rules"
`
In res/xml create a file data_extraction_rules.xml containing:
`















`



Electron Quirks

- On Electron, go to the Electron folder of YOUR_APPLICATION

`bash
cd electron
npm install --save better-sqlite3-multiple-ciphers
npm install --save electron-json-storage
npm install --save jszip
npm install --save node-fetch@2.6.7
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 Electron@25.8.4 till further notice so do:
`bash
npm install --save-dev electron@25.8.4
npm uninstall --save-dev electron-rebuild
npm install --save-dev @electron/rebuild
npm install --save-dev electron-builder@24.6.4
`
- 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) | ✅ | ✅ | ✅ | ❌ | since 4.1.0-7 |
| closeConnection (ReadWrite) | ✅ | ✅ | ✅ | ✅ |
| closeConnection (ReadOnly) | ✅ | ✅ | ✅ | ❌ | since 4.1.0-7 |
| isConnection (ReadWrite) | ✅ | ✅ | ✅ | ✅ |
| isConnection (ReadOnly) | ✅ | ✅ | ✅ | ❌ | since 4.1.0-7 |
| open (non-encrypted DB) | ✅ | ✅ | ✅ | ✅ |
| open (encrypted DB) | ✅ | ✅ | ✅ | ❌ |
| close | ✅ | ✅ | ✅ | ✅ |
| getUrl | ✅ | ✅ | ❌ | ❌ |
| getVersion | ✅ | ✅ | ✅ | ✅ |
| execute | ✅ | ✅ | ✅ | ✅ |
| executeSet | ✅ | ✅ | ✅ | ✅ |
| run | ✅ | ✅ | ✅ | ✅ |
| query | ✅ | ✅ | ✅ | ✅ |
| deleteDatabase | ✅ | ✅ | ✅ | ✅ |
| importFromJson | ✅ | ✅ | ✅ | ✅ |
| exportToJson | ✅ | ✅ | ✅ | ✅ |
| deleteExportedRows | ✅ | ✅ | ✅ | ✅ |
| createSyncTable | ✅ | ✅ | ✅ | ✅ |
| setSyncDate | ✅ | ✅ | ✅ | ✅ |
| getSyncDate | ✅ | ✅ | ✅ | ✅ |
| isJsonValid | ✅ | ✅ | ✅ | ✅ |
| isDBExists | ✅ | ✅ | ✅ | ✅ |
| addUpgradeStatement | ✅ | ✅ | ✅ | ✅ | Modified 4.1.0-6 |
| copyFromAssets | ✅ | ✅ | ✅ | ✅ |
| isDBOpen | ✅ | ✅ | ✅ | ✅ |
| isDatabase | ✅ | ✅ | ✅ | ✅ |
| isTableExists | ✅ | ✅ | ✅ | ✅ |
| getTableList | ✅ | ✅ | ✅ | ✅ |
| getDatabaseList | ✅ | ✅ | ✅ | ✅ |
| getMigratableDbList | ✅ | ✅ | ❌ | ❌ |
| addSQLiteSuffix | ✅ | ✅ | ❌ | ❌ |
| deleteOldDatabases | ✅ | ✅ | ❌ | ❌ |
| moveDatabasesAndAddSuffix | ✅ | ✅ | ❌ | ❌ |
| checkConnectionsConsistency | ✅ | ✅ | ✅ | ✅ |
| isSecretStored | ✅ | ✅ | ✅ | ❌ |
| setEncryptionSecret | ✅ | ✅ | ✅ | ❌ |
| changeEncryptionSecret | ✅ | ✅ | ✅ | ❌ |
| clearEncryptionSecret | ✅ | ✅ | ✅ | ❌ |
| checkEncryptionSecret | ✅ | ✅ | ✅ | ❌ |
| initWebStore | ❌ | ❌ | ❌ | ✅ |
| saveToStore | ❌ | ❌ | ❌ | ✅ |
| getNCDatabasePath | ✅ | ✅ | ❌ | ❌ |
| createNCConnection | ✅ | ✅ | ❌ | ❌ |
| closeNCConnection | ✅ | ✅ | ❌ | ❌ |
| isNCDatabase | ✅ | ✅ | ❌ | ❌ |
| transaction | ✅ | ✅ | ✅ | ✅ |
| getFromHTTPRequest | ✅ | ✅ | ✅ | ✅ | since 4.2.0 |
| isDatabaseEncrypted | ✅ | ✅ | ✅ | ❌ | since 4.6.2-2 |
| isInConfigEncryption | ✅ | ✅ | ✅ | ❌ | since 4.6.2-2 |
| isInConfigBiometricAuth | ✅ | ✅ | ❌ | ❌ | since 4.6.2-2 |
| getFromLocalDiskToStore | ❌ | ❌ | ❌ | ✅ | since 4.6.3 |
| saveToLocalDisk | ❌ | ❌ | ❌ | ✅ | since 4.6.3 |
| beginTransaction | ✅ | ✅ | ✅ | ✅ | since 5.0.7 |
| commitTransaction | ✅ | ✅ | ✅ | ✅ | since 5.0.7 |
| rollbackTransaction | ✅ | ✅ | ✅ | ✅ | since 5.0.7 |
| isTransactionActive | ✅ | ✅ | ✅ | ✅ | since 5.0.7 |

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

$3

- 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)

$3

- ionic-sqlite-typeorm-app

$3

- 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.

$3

- capacitor-sqlite-react-typeorm-app

$3

- 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.

$3

- vue-sqlite-app

$3

- vue-typeorm-app

$3

- solidjs-vite-sqlite-app

$3

- nuxt3-capacitor-sqlite-kysely-example

$3

- 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.

$3

- Web/Native vite-sveltekit-capacitor-sqlite

Dependencies

The iOS and Android codes are using SQLCipher allowing for database encryption.
The iOS code 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.