/angular-sqlite-app-starter

Ionic/Angular SQLite Application Starter

Primary LanguageJavaScriptMIT LicenseMIT


Ionic/Angular SQLite App Starter

angular-sqlite-app-starter

Ionic/Angular application demonstrating the use of the

@capacitor-community/sqlite


Maintainers

Maintainer GitHub Social
Quéau Jean Pierre jepiqueau

Installation

To start building your App using this Starter App, clone this repo to a new directory:

git clone https://github.com/jepiqueau/angular-sqlite-app-starter.git 
cd angular-sqlite-app-starter
git remote rm origin
  • then install it
npm install
  • then go to the building process
npm run build
npx cap update
npm run build
npx cap copy
npx cap copy @capacitor-community/electron
npx cap copy web

the capacitor config parameters are:

"appId": "com.example.app.capacitor"
"appName": "angular-sqlite-app-starter"

Building Web Code

The @capacitor-community/sqlite is not implemented for Web Browsers. if you run

npx cap serve

you will get the following messages:

SQLite Plugin not available for Web Platform

Building Native Project

IOS

npx cap open ios

Once Xcode launches, you can build your finally app binary through the standard Xcode workflow.

Android

npx cap open android

Once Android Studio launches, you can build your app through the standard Android Studio workflow.

Test SQLite access

The @capacitor-community/sqlite test is accessible in the Tab2 of the Application by clicking on the SQLite test button.

The application uses a service class as a wrapper to the @capacitor-community/sqlite plugin

Resulting Output

Open Database successful
Execute Creation Tables successful
Execute Insert Users successful
Execute Insert Messages successful
Query Two Users successful
Query Two Messages successful
Create One User with sqlcmd and values successful
Create One User with sqlcmd successful
Query Four Users successful
Query Users age > 30 successful
Closing the database was successful
The test database was successful
The test to encrypt the database was successful
The test encrypted database was successful
The test wrong password was successful
The test new password was successful
The test new password database was successful
The set of tests was successful

At the end of the test, two databases should have been created and both are encrypted,

  • for the first one the secret is sqlite secret
  • for the second one the secret is sqlite new secret
test-sqliteSQLite.db
test-encryptedSQLite.db

Changing the 'secret' and 'new secret'

IOS

In Xcode, before building your app,

  • Go to the Pods/Development Pods/CapacitorCommunitySqlite folder,
  • Modify the secret and newsecretstrings in the GlobalSQLite.swift file.

Android

In Android Studio, before building your app,

  • Go to the capacitor-community-sqlite/java/com.getcapacitor.community.database.sqlite/cdssUtilsfolder,
  • Modify the secret and newsecretstrings in the GlobalSQLite.java file.

Angular Service

A Angular Service has been defined as a wrapper to the @capacitor-community/sqlite plugin.

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

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

@Injectable({
  providedIn: 'root'
})
export class SQLiteService {
  sqlite: any;
  isService: boolean = false;
  platform: string;
  constructor() {
  }
  /**
   * Plugin Initialization
   */
  async initializePlugin(): Promise<void> {
    const info = await Device.getInfo();
    this.platform = info.platform;
    console.log("*** platform " + this.platform)
    this.sqlite = CapacitorSQLite;
    this.isService = true;

    if(this.platform === "android") {
      try {
        await this.sqlite.requestPermissions();
      } catch (e) {
        console.log("Error requesting permissions " + e);
        this.isService = false;
      }
    }
 
  }
  /**
   * Get Echo 
   * @param value string 
   */
  async getEcho(value:string): Promise<any> {
    if (this.isService) {
      return await this.sqlite.echo({value:"Hello from JEEP"});
    } else {
      return Promise.resolve("");
    }
  }
  /**
   * Open a Database
   * @param dbName string
   * @param _encrypted boolean optional 
   * @param _mode string optional
   */  
  async openDB(dbName:string,_encrypted?:boolean,_mode?:string): Promise<any> {
    if(this.isService) {
      const encrypted:boolean = _encrypted ? _encrypted : false;
      const mode: string = _mode ? _mode : "no-encryption";
      return await this.sqlite.open({database:dbName,encrypted:encrypted,mode:mode});
    } else {
      return Promise.resolve({result:false,message:"Service not started"});
    }
  }
  /**
   * Execute a set of Raw Statements
   * @param statements string 
   */
  async execute(statements:string): Promise<any> {
    if(this.isService && statements.length > 0) {
      return await this.sqlite.execute({statements:statements});
    } else {
      return Promise.resolve({changes:-1,message:"Service not started"});
    }
  }
  /**
   * Execute a Single Raw Statement
   * @param statement string
   */
  async run(statement:string,_values?:Array<any>): Promise<any> {
    if(this.isService && statement.length > 0) {
      const values: Array<any> = _values ? _values : [];
      return  await this.sqlite.run({statement:statement,values:values});
    } else {
      return Promise.resolve({changes:-1,message:"Service not started"});
    }
  }
  /**
   * Query a Single Raw Statement
   * @param statement string
   * @param values Array<string> optional
   */
  async query(statement:string,_values?:Array<string>): Promise<any> {
    const values: Array<any> = _values ? _values : [];
    if(this.isService && statement.length > 0) {
      return await this.sqlite.query({statement:statement,values:values});
    } else {
      return Promise.resolve({values:[],message:"Service not started"});
    }

  } 
  /**
   * Close the Database
   * @param dbName string
   */
  async close(dbName:string): Promise<any> {
    if(this.isService) {
      return await this.sqlite.close({database:dbName});
    } else {
      return Promise.resolve({result:false,message:"Service not started"});
    }
  }
  /**
   * Delete the Database file
   * @param dbName string
   */
  async deleteDB(dbName:string): Promise<any> {
    if(this.isService) {
      return await this.sqlite.deleteDatabase({database:dbName});
    } else {
      return Promise.resolve({result:false,message:"Service not started"});
    }
  }
}

Starting an App from Scratch

The process described below follows the instructions provided in the Capacitor Documentation

New Ionic/Angular Project

ionic start mySQLiteApp tabs --type=angular --capacitor
cd ./mySQLiteApp

Initialize Capacitor

npx cap init mySQLiteApp com.example.app

Your App information [appName] [appId] can be whathever you would like. Here we choose for the example [mySQLiteApp] [com.example.app]

Install @capacitor-community/sqlite plugin

npm install --save @capacitor-community/sqlite@latest

Add an Angular Service

ng g service sqlite

In your favorite editor open the sqlite.services.ts file under the src/appfolder and input the code as described above

Access the Angular Service in your App Angular Components

Import in your Angular Component

import { Component, AfterViewInit } from '@angular/core';
import { SQLiteService } from '../services/sqlite.service';

Inject the SQLiteService in your Angular Component Constructor

  constructor(private _SQLiteService: SQLiteService) {
  }

Initialize CapacitorSQLite plugin

  async ngAfterViewInit() {
        // Initialize the CapacitorSQLite plugin
        await this._SQLiteService.initializePlugin();

    ...
  }

Usage of the CapacitorSQLite plugin in Angular Component Methods

async fooMethod(): Promise<void> {
    ...
    if(this._SQLiteService.isService) {
      // open the database
      let resOpen = await this._SQLiteService.openDB("fooDB"); 
      if(resOpen.result) {
            ...

            ...
      }

    } else {
        console.log('CapacitorSQLite Plugin: Initialization Failed');
    }
    ...
}

When the database is open, use the other methods provided by the Angular Service to execute, run, query SQLite raw statements

Build your App

npm run build

Add Platforms

npx cap add ios
npx cap add android

Building and Syncing your App with Capacitor

npm run build
npx cap copy
npx cap copy web

Open IDE to Build, Run and Deploy

IOS

npx cap open ios

Once Xcode launches, you can build your finally app binary through the standard Xcode workflow.

Android

npx cap open android

Once Android launches,

  • Edit the MainActivity.java and add the following import:
import com.getcapacitor.community.database.sqlite.CapacitorSQLite;
  • Add the CapacitorSQLite declaration in the this.init method
add(CapacitorSQLite.class);
  • you can then build your app through the standard Android Studio workflow.

Electron

In your application folder add the @capacitor-community/electron

npm i @capacitor-community/electron
npx cap add @capacitor-community/electron

In 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": {
    "build": "tsc",
    "electron:start": "npm run build && electron ./",
    "electron:pack": "npm run build && electron-builder build --dir",
    "electron:build-windows": "npm run build && electron-builder build --windows",
    "electron:build-mac": "npm run build && electron-builder build --mac",
    "postinstall": "electron-rebuild -f -w sqlite3"
  },

Execute the postinstall script

npm run postinstall

and then build the application

 npx cap sync @capacitor-community/electron
 npm run build
 npx cap copy @capacitor-community/electron
 npx cap open @capacitor-community/electron

The datastores created are under User/Databases/YOUR_APP_NAME/

When @capacitor-community/sqlite is updated

Follow this process:

npm install --save @capacitor-community/sqlite@latest
npx cap update
npx cap sync @capacitor-community/electron
npm run build
npx cap copy
 npx cap copy @capacitor-community/electron
npx cap copy web
npx cap open ios
npx cap open android
npx cap open @capacitor-community/electron

Contributors ✨

Thanks goes to these wonderful people (emoji key):


Jean Pierre Quéau
💻

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