SQLITE DATABASE
@capacitor-community/sqlite
CAPACITOR 5
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 |
| ----------------- | ----------------------------------------- | ------ |
| Quéau Jean Pierre | [jepiqueau](https://github.com/jepiqueau) | |
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:
```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](https://capacitorjs.com/docs/updating/5-0)
- [Releases](https://github.com/capacitor-community/sqlite/blob/master/info_releases.md)
- [Changelog](https://github.com/capacitor-community/sqlite/blob/master/CHANGELOG.md)
- [Issues](https://github.com/capacitor-community/sqlite/issues)
- [Capacitor documentation](https://capacitorjs.com/docs/)
- [Datatypes In SQLite Version 3](https://www.sqlite.org/datatype3.html)
- [IncrementalUpgradeDatabaseVersion](https://github.com/capacitor-community/sqlite/blob/master/docs/IncrementalUpgradeDatabaseVersion.md)
## Tutorials Blog
- [JeepQ Capacitor Plugin Tutorials](https://jepiqueau.github.io/)
## Web Quirks
The plugin follows the guidelines from the `Capacitor Team`,
- [Capacitor Browser Support](https://capacitorjs.com/docs/v3/web#browser-support)
Meaning that it will not work in IE11 without additional JavaScript transformations, e.g. with [Babel](https://babeljs.io/).
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](https://github.com/capacitor-community/sqlite/issues/301) and [SO question](https://stackoverflow.com/questions/63291529/how-to-fix-more-than-one-file-was-found-with-os-independent-path-build-data-pro) 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
```
```
## 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](https://github.com/capacitor-community/sqlite/issues/349 "you'll get an error ERR_REQUIRE_ESM") 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](https://github.com/capacitor-community/sqlite/blob/master/docs/API.md)
- [API Connection Wrapper](https://github.com/capacitor-community/sqlite/blob/master/docs/APIConnection.md)
- [API DB Connection Wrapper](https://github.com/capacitor-community/sqlite/blob/master/docs/APIDBConnection.md)
- [Import-Export Json](https://github.com/capacitor-community/sqlite/blob/master/docs/ImportExportJson.md)
- [Upgrade Database Version](https://github.com/capacitor-community/sqlite/blob/master/docs/UpgradeDatabaseVersion.md)
- [Migrating Cordova Databases](https://github.com/capacitor-community/sqlite/blob/master/docs/MigratingCordovaDatabases.md)
- [Type ORM](https://github.com/capacitor-community/sqlite/blob/master/docs/TypeORM-Usage.md)
- [TypeORM-From-5.6.0](https://github.com/capacitor-community/sqlite/blob/master/docs/TypeORM-Usage-From-5.6.0.md)
- [Web Usage](https://github.com/capacitor-community/sqlite/blob/master/docs/Web-Usage.md)
- [Non Conformed Databases](https://github.com/capacitor-community/sqlite/blob/master/docs/NonConformedDatabases.md)
- [Biometric Authentication](https://github.com/capacitor-community/sqlite/blob/master/docs/Biometric-Authentication.md)
- [Enable SQLite Schema Error Syntax Highlighting](https://github.com/capacitor-community/sqlite/blob/master/docs/SyntaxScanner-For-SQLite-Code.md)
- [Electron Better SQLite3](https://github.com/capacitor-community/sqlite/blob/master/docs/ElectronBetterSQLite3.md)
- [Enable minified build on Android](https://github.com/capacitor-community/sqlite/blob/master/docs/AndroidMinify.md)
## Applications demonstrating the use of the plugin and related documentation
### Ionic/Angular
- [Web ionic7-angular-sqlite-app](https://github.com/jepiqueau/blog-tutorials-apps/tree/main/SQLite/Part-1/ionic7-angular-sqlite-app) Ionic 7 Angular 16 Capacitor 5 SQLite CRUD operations for Web.
- [Native ionic7-angular-sqlite-app](https://github.com/jepiqueau/blog-tutorials-apps/tree/main/SQLite/Part-2/ionic7-angular-sqlite-app) Ionic 7 Angular 16 Capacitor 5 SQLite CRUD operations for iOS, Android and Electron.
- [angular-sqlite-synchronize-app](https://github.com/jepiqueau/angular-sqlite-synchronize-app) (Not Updated)
### Ionic/Angular TypeORM app (Not Updated)
- [ionic-sqlite-typeorm-app](https://github.com/jepiqueau/ionic-sqlite-typeorm-app)
### Ionic/React
- [Web ionic7-react-sqlite-app](https://github.com/jepiqueau/blog-tutorials-apps/tree/main/SQLite/Part-1/ionic7-react-sqlite-app) Ionic7 React18.2.0 Vite4.3.9 Capacitor 5 SQLite CRUD operations for Web.
- [Web ionic7-react-sqlite-app](https://github.com/jepiqueau/blog-tutorials-apps/tree/main/SQLite/Part-2/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](https://github.com/cosentino/capacitor-sqlite-react-typeorm-app)
### Ionic/Vue
- [Web ionic7-vue-sqlite-app](https://github.com/jepiqueau/blog-tutorials-apps/tree/main/SQLite/Part-1/ionic7-vue-sqlite-app) Ionic7 Vue3.2.45 Vite4.3.9 Capacitor 5 SQLite CRUD operations for Web.
- [Web ionic7-vue-sqlite-app](https://github.com/jepiqueau/blog-tutorials-apps/tree/main/SQLite/Part-2/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](https://github.com/jepiqueau/vue-sqlite-app)
### Vue TypeORM app (Not Updated)
- [vue-typeorm-app](https://github.com/jepiqueau/vue-typeorm-app)
### SolidJS+Vite (Not Updated)
- [solidjs-vite-sqlite-app](https://github.com/jepiqueau/capacitor-solid-sqlite)
### Nuxt3 + Kysely
- [nuxt3-capacitor-sqlite-kysely-example](https://github.com/DawidWetzler/nuxt3-capacitor-sqlite-kysely-example)
### Quasar
- [Web quasar-sqlite-app](https://github.com/jepiqueau/blog-tutorials-apps/tree/main/SQLite/Part-1/quasar-sqlite-app) Quasar2.6.0 Capacitor 5 SQLite CRUD operations for Web.
- [Native quasar-sqlite-app](https://github.com/jepiqueau/blog-tutorials-apps/tree/main/SQLite/Part-2/quasar-sqlite-app) Quasar2.6.0 Capacitor 5 SQLite CRUD operations for iOS, Android and Electron.
### SvelteKit
- [Web/Native vite-sveltekit-capacitor-sqlite](https://github.com/jepiqueau/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](https://allcontributors.org/docs/en/emoji-key)):
This project follows the [all-contributors](https://github.com/all-contributors/all-contributors) specification. Contributions of any kind welcome!