@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

REFACTOR

The refactor will be a quite long process.

The aim of the refactor will be to allow

• for multiple database connections
• for a db connector allowing for easy commands db.open(), db.close, ...
• improve the response time of the encrypted database by removing the internal open and close database for each sqlite query
• moving to the latest androidx.sqlite.db.xxx

This was discussed lengthly in issue#1and issue#52

The first alpha release of the refactor will address the android platform only.

The test will be achieved on a Ionic/Angular app. For the other frameworks, it will require an update of the react-sqlite-hookand the vue-sqlite-hook.

Installation

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

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

Configuration

No configuration required for this plugin

Supported methods

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

Documentation (to be updated)

API_Documentation

ImportExportJson_Documentation

UpgradeDatabaseVersion_Documentation

Applications demonstrating the use of the plugin

Ionic/Angular

• angular-sqlite-app-refactor

Ionic/React (to come later)

Ionic/Vue (to come later)

Vue (to come later)

Usage

• see capacitor documentation

• In your Ionic/Angular App

define a service app/services/sqlite.service.ts

import { Injectable } from '@angular/core';

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

@Injectable({
  providedIn: 'root',
})
export class SQLiteService {
  handlerPermissions: any;
  sqlite: SQLiteConnection;
  isService: boolean = false;
  platform: string;

  constructor() {}
  /**
   * Plugin Initialization
   */
  initializePlugin(): Promise<boolean> {
    return new Promise(resolve => {
      this.platform = Capacitor.platform;
      console.log('*** platform ' + this.platform);
      const sqlitePlugin: any = CapacitorSQLite;
      if (this.platform === 'android') {
        this.handlerPermissions = sqlitePlugin.addListener(
          'androidPermissionsRequest',
          async (data: any) => {
            if (data.permissionGranted === 1) {
              this.handlerPermissions.remove();
              this.sqlite = new SQLiteConnection(sqlitePlugin);
              resolve(true);
            } else {
              console.log('Permission not granted');
              this.handlerPermissions.remove();
              this.sqlite = null;
              resolve(false);
            }
          },
        );
        try {
          sqlitePlugin.requestPermissions();
        } catch (e) {
          console.log('Error requesting permissions ' + JSON.stringify(e));
          resolve(false);
        }
      } else {
        this.sqlite = new SQLiteConnection(sqlitePlugin);
        resolve(true);
      }
    });
  }
  async echo(value: string): Promise<capEchoResult> {
    if (this.sqlite != null) {
      return await this.sqlite.echo(value);
    } else {
      return null;
    }
  }
  async createConnection(
    database: string,
    encrypted: boolean,
    mode: string,
    version: number,
  ): Promise<SQLiteDBConnection | null> {
    if (this.sqlite != null) {
      const db: SQLiteDBConnection = await this.sqlite.createConnection(
        database,
        encrypted,
        mode,
        version,
      );
      if (db != null) {
        return db;
      } else {
        return null;
      }
    } else {
      return null;
    }
  }
  async closeConnection(database: string): Promise<capSQLiteResult> {
    if (this.sqlite != null) {
      return await this.sqlite.closeConnection(database);
    } else {
      return null;
    }
  }
}

Then implement a component (for example the app/home/home.page.ts )

import { Component, AfterViewInit } from '@angular/core';
import { SQLiteService } from '../services/sqlite.service';
import { createSchema, twoUsers } from '../utils/no-encryption-utils';
import {
  createSchemaContacts,
  setContacts,
} from '../utils/encrypted-set-utils';

@Component({
  selector: 'app-home',
  templateUrl: 'home.page.html',
  styleUrls: ['home.page.scss'],
})
export class HomePage implements AfterViewInit {
  sqlite: any;
  platform: string;
  handlerPermissions: any;
  initPlugin: boolean = false;

  constructor(private _sqlite: SQLiteService) {}

  async ngAfterViewInit() {
    // Initialize the CapacitorSQLite plugin
    this.initPlugin = await this._sqlite.initializePlugin();
    const result: boolean = await this.runTest();
    if (result) {
      document.querySelector('.sql-allsuccess').classList.remove('display');
      console.log('$$$ runTest was successful');
    } else {
      document.querySelector('.sql-allfailure').classList.remove('display');
      console.log('$$$ runTest failed');
    }
  }

  async runTest(): Promise<boolean> {
    let result: any = await this._sqlite.echo('Hello World');
    console.log(' from Echo ' + result.value);
    // initialize the connection
    const db = await this._sqlite.createConnection(
      'testNew',
      false,
      'no-encryption',
      1,
    );
    const db1 = await this._sqlite.createConnection(
      'testSet',
      true,
      'secret',
      1,
    );
    // open db
    let ret: any = await db.open();
    if (!ret.result) {
      return false;
    }
    // create tables in db
    ret = await db.execute(createSchema);
    console.log('$$$ ret.changes.changes in db ' + ret.changes.changes);
    if (ret.changes.changes < 0) {
      return false;
    }
    // add two users in db
    ret = await db.execute(twoUsers);
    if (ret.changes.changes !== 2) {
      return false;
    }
    // select all users in db
    ret = await db.query('SELECT * FROM users;');
    if (
      ret.values.length !== 2 ||
      ret.values[0].name !== 'Whiteley' ||
      ret.values[1].name !== 'Jones'
    ) {
      return false;
    }
    // open db1
    ret = await db1.open();
    if (!ret.result) {
      return false;
    }
    // create tables in db1
    ret = await db1.execute(createSchemaContacts);
    console.log('$$$ ret.changes.changes in db1' + ret.changes.changes);
    if (ret.changes.changes < 0) {
      return false;
    }
    // load setContacts in db1
    ret = await db1.executeSet(setContacts);
    console.log('$$$ ret.changes.changes in db2' + ret.changes.changes);
    if (ret.changes.changes !== 5) {
      return false;
    }

    // select users where company is NULL in db
    ret = await db.query('SELECT * FROM users WHERE company IS NULL;');
    if (
      ret.values.length !== 2 ||
      ret.values[0].name !== 'Whiteley' ||
      ret.values[1].name !== 'Jones'
    ) {
      return false;
    }
    // add one user with statement and values
    let sqlcmd: string = 'INSERT INTO users (name,email,age) VALUES (?,?,?)';
    let values: Array<any> = ['Simpson', '[e-mail]', 69];
    ret = await db.run(sqlcmd, values);
    console.log();
    if (ret.changes.lastId !== 3) {
      return false;
    }
    // add one user with statement
    sqlcmd =
      `INSERT INTO users (name,email,age) VALUES ` +
      `("Brown","[e-mail]",15)`;
    ret = await db.run(sqlcmd);
    if (ret.changes.lastId !== 4) {
      return false;
    }

    ret = await this._sqlite.closeConnection('testNew');
    if (!ret.result) {
      return false;
    }
    ret = await this._sqlite.closeConnection('testSet');
    if (!ret.result) {
      return false;
    } else {
      return true;
    }
  }
}

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.9.0-alpha.1 (2020-11-22) REFACTOR

Added Features Android platform only

• createConnection method
• closeConnection method
• SQLiteConnection Interface & Class
• SQLiteDBConnection Interface & Class