@capacitor-community/sqlite

SQLITE DATABASE

@capacitor-community/sqlite

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

Installation

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

iOS

• On iOS, no further steps are needed.

Android

• On Android, register the plugin in your main activity:

import com.getcapacitor.community.database.sqlite.CapacitorSQLite;

public class MainActivity extends BridgeActivity {

  @Override
  public void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);

    // Initializes the Bridge
    this.init(
        savedInstanceState,
        new ArrayList<Class<? extends Plugin>>() {

          {
            // Additional plugins you've installed go here
            // Ex: add(TotallyAwesomePlugin.class);
            add(CapacitorSQLite.class);
          }
        }
      );
  }
}

Electron

• On Electron, go to the Electron folder of YOUR_APPLICATION

npm install --save sqlite3
npm install --save-dev @types/sqlite3
npm install --save-dev electron-rebuild

Modify the Electron package.json file by adding a script "postinstall"

  "scripts": {
    "electron:start": "electron ./",
    "postinstall": "electron-rebuild -f -w sqlite3"
  },

Execute the postinstall script

npm run postinstall

Electron databases location

• There are by default under User/Databases/APP_NAME/

Then build YOUR_APPLICATION

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

Configuration

No configuration required for this plugin

Supported methods

Name	Android	iOS	Electron	Web
open (non-encrypted DB)	✅	✅	✅	❌
open (encrypted DB)	✅	✅	❌	❌
close	✅	✅	✅	❌
execute	✅	✅	✅	❌
executeSet	✅	✅	✅	❌
run	✅	✅	✅	❌
query	✅	✅	✅	❌
deleteDatabase	✅	✅	✅	❌
importFromJson	✅	✅	✅	❌
exportToJson	✅	✅	✅	❌
createSyncTable	✅	✅	✅	❌
setSyncDate	✅	✅	✅	❌
isJsonValid	✅	✅	✅	❌
isDBExists	✅	✅	✅	❌
addUpgradeStatement	✅	✅	✅	❌

Documentation

API_Documentation

ImportExportJson_Documentation

UpgradeDatabaseVersion_Documentation

Applications demonstrating the use of the plugin

Ionic/Angular

• angular-sqlite-app-starter

• test-angular-jeep-capacitor-plugins

Ionic/React

• react-sqlite-app-starter

Ionic/Vue

• vue-sqlite-app-starter

Vue

• vue-sqlite-app

Usage

• see capacitor documentation

• In your code

 import { Plugins, Capacitor } from '@capacitor/core';
 import '@capacitor-community/sqlite';
 const { CapacitorSQLite } = Plugins;

 @Component( ... )
 export class MyPage {
  _sqlite: any;
  _platform: string;
  _isPermission: boolean = true;

  ...

  ngAfterViewInit()() {
    this._platform = Capacitor.platform;
    this._sqlite = CapacitorSQLite;
    if (this._platform === 'android') {
      const handlerPermissions = this.sqlite.addListener(
            'androidPermissionsRequest', async (data:any) => {
        if (data.permissionGranted === 1) {
          this._isPermission = true;
        } else {
          this._isPermission = false;
        }
      });
      try {
        this.sqlite.requestPermissions();
      } catch (e) {
        console.log('Error requesting permissions!' + JSON.stringify(e));
      }
    }
    ...

  }

  async testSQLitePlugin(): Promise<void> {
      if(!this._isPermission) {
        console.log("Android permissions not granted");
        return;
      }
      let result:any = await this._sqlite.open({database:"testsqlite"});
      retOpenDB = result.result;
      if(retOpenDB) {
        // Create Tables if not exist
        let sqlcmd: string = `
        BEGIN TRANSACTION;
        CREATE TABLE IF NOT EXISTS users (
          id INTEGER PRIMARY KEY NOT NULL,
          email TEXT UNIQUE NOT NULL,
          name TEXT,
          FirstName TEXT,
          age INTEGER,
          MobileNumber TEXT
        );
        PRAGMA user_version = 1;
        COMMIT TRANSACTION;
        `;
        var retExe: any = await this._sqlite.execute({statements:sqlcmd});
        console.log('retExe ',retExe.changes.changes);
        // Insert some Users
        sqlcmd = `
        BEGIN TRANSACTION;
        DELETE FROM users;
        INSERT INTO users (name,email,age) VALUES ("Whiteley","Whiteley.com",30);
        INSERT INTO users (name,email,age) VALUES ("Jones","Jones.com",44);
        COMMIT TRANSACTION;
        `;
        retExe = await this._sqlite.execute({statements:sqlcmd});
        // will print the changes  2 in that case
        console.log('retExe ',retExe.changes.changes);
        // Select all Users
        sqlcmd = "SELECT * FROM users";
        const retSelect: any = await this._sqlite.query({statement:sqlcmd,values:[]});
        console.log('retSelect.values.length ',retSelect.values.length);
        const row1: any = retSelect.values[0];
        console.log("row1 users ",JSON.stringify(row1))
        const row2: any = retSelect.values[1];
        console.log("row2 users ",JSON.stringify(row2))

        // Insert a new User with SQL and Values

        sqlcmd = "INSERT INTO users (name,email,age) VALUES (?,?,?)";
        let values: Array<any>  = ["Simpson","[e-mail]",69];
        var retRun: any = await this._sqlite.run({statement:sqlcmd,values:values});
        console.log('retRun ',retRun.changes.changes,retRun.changes.lastId);

        // Select Users with age > 35
        sqlcmd = "SELECT name,email,age FROM users WHERE age > ?";
        retSelect = await this._sqlite.query({statement:sqlcmd,values:["35"]});
        console.log('retSelect ',retSelect.values.length);

        // Execute a Set of raw SQL Statements
        let set: Array<any>  = [
          { statement:"INSERT INTO users (name,FirstName,email,age,MobileNumber) VALUES (?,?,?,?,?);",
            values:["Blackberry","Peter","[e-mail]",69,"4405060708"]
          },
          { statement:"INSERT INTO users (name,FirstName,email,age,MobileNumber) VALUES (?,?,?,?,?);",
            values:["Jones","Helen","[e-mail]",42,"4404030201"]
          },
          { statement:"INSERT INTO users (name,FirstName,email,age,MobileNumber) VALUES (?,?,?,?,?);",
            values:["Davison","Bill","[e-mail]",45,"4405162732"]
          },
          { statement:"INSERT INTO users (name,FirstName,email,age,MobileNumber) VALUES (?,?,?,?,?);",
            values:["Brown","John","[e-mail]",35,"4405243853"]
          },
          { statement:"UPDATE users SET age = ? , MobileNumber = ? WHERE id = ?;",
            values:[51,"4404030237",2]
          }
        ];
        result = await this._sqlite.executeSet({set:set});
        console.log("result.changes.changes ",result.changes.changes);
        if(result.changes.changes != 5) return;


       ...
      } else {
        console.log("Error: Open database failed");
        return;
      }
   }
   ...
 }

Dependencies

The IOS and Android codes are using SQLCipher allowing for database encryption The Electron code use sqlite3

Contributors ✨

Thanks goes to these wonderful people (emoji key):

Jean Pierre Quéau 💻

Paul Antoine 💻

Karyfars 💻

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

Changelog

2.4.3-1 (2020-11-23)

Chore

• update to @capacitor/core@2.4.3

Bug Fixes

• Fix issue#56 INSERT null value on iOS