@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

REFACTOR 🚀

A refactoring has been started more than a month ago to reach the following objectives:

• multiple database connections
• 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
• offering encryption for Electron platform by using @journeyapps/sqlcipher
• cleaning and aligning the code between platforms
• allowing developers to develop easily typeorm or spatialite drivers.

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

It is now available in a beta release 2.9.0-beta.1 for all platforms (Android, iOS & Electron).

Developers are encouraged to start looking at it and using it as this will have some impacts on yours developments. The interface to the plugin is now achieved through the use of connection wrappers

• API_Connection_Wrapper_Documentation

• API_DB_Connection_Wrapper_Documentation

As you will see it is a Majorchange and the release will become a 3.0.0 as soon as capacitor@3.0.0 will be released. So both interfaces to the plugin will be kept and maintained to that stage, after this, the refactor interface will be released as the master and maintained.

The test has been achieved on:

• a Ionic/Angular app

• a Ionic/React app

Other frameworks will be tested later

• Ionic/Vue will require an update of the vue-sqlite-hook.
• Stencil

When you will find issues, please report them with the REFACTOR word at the start of the issue title.

To install it

npm i --save @capacitor-community/sqlite@refactor

Hope you will enjoy it.

Maintainers

Maintainer	GitHub	Social
Quéau Jean Pierre	jepiqueau

Browser Support

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.

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.4 (2020-12-26)

Chore

• update to @capacitor/core@2.4.4

Bug Fixes

• Fix README
• Fix setSyncDate (iOS)
• Fix exportToJson (Android)
• Fix lastId to Int64 (iOS)