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

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:

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

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

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

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