Documentation improvements collection

[tobiasmuecksch]

In this issue, I want to collect topics and improvements for the documentation.

• Simplified getting started with platform specific hints
• Simplified "Singleton service" with better readability
• Platform specific quirks and hints
  • e.g. on web we have to call saveToStore programmatically. closeConnection also does saveToStore
• What is the difference between run, query, execute and executeSet?
• Detailed information on connection management
• Detailed information on how db versioning works

FAQ:

• How to drop a database? (advice: close connection afterwards)

Maybe we could implement some documentation website with a framework like https://github.com/facebook/docusaurus

[tobiasmuecksch]

Issues to consider for information gathering:

• bug: npx cap sync @capacitor-community/electron breaks configuration #255
• bug: cannot create tables with standard sql statements #254
• bug: deleteDatabase() does not work #247
• web: .createConnection() "Not implemented on web" #244
• Electron error TypeError: Cannot read properties of undefined (reading 'CapacitorSQLite') #242
• Error: echo "Not implemented on web." #241

[tobiasmuecksch]

@jepiqueau Could you give me some explanation for this question?

"What is the difference between run, query, execute and executeSet?"

[jepiqueau]

@tobiasmuecksch
Query is for select statement
Execute is for executing a set of raw statement given in a string.
Run is for executing one raw statement Insert, Update, Delete with given values meaning that you prépare the statement and bind the values if any
ExecuteSet is to run on or more raw statements and bind them with values or an array of values .
Hope this clarify

[jepiqueau]

@tobiasmuecksch on Electron you do not need to use saveToStore. This is only for the Web platform

[tobiasmuecksch]

@tobiasmuecksch on Electron you do not need to use saveToStore. This is only for the Web platform

My bad. I fixed the issue description.

[jepiqueau]

@tobiasmuecksch i just issue a new Ionic/Angular app demonstrating how to use the @capacitor-community/sqlite plugin to synchronize with a remote server database by using the import & export of Json Object. The deleted rows are now well managed. Look at angular-sqlite-synchronize-app, hope this will clarify

[rakibul111]

@jepiqueau can you please guide me how can I use this sqlite plugin with nextjs-ionic setup.
Note: I don't know typescript

[jepiqueau]

@rakibul111 exactely the same way that for react, vue, solidjs or angular.

[jepiqueau]

@rakibul111
Npm i -- save @capacitor/core
Npm i --save-dev @capacitor/cli
Npm i --save @capacitor/ios and Android
Npx cap init yourappname yourappid
Npm run build
Npx cap add ios
Npx cap add android
Npx cap sync
Npm run build
Npx cap copy
Npx cap open ios
Npx cap open android
Thatis it

[rakibul111]

@jepiqueau where to setup for jeep-sqlite?

[rakibul111]

@jepiqueau Uncaught (in promise) RuntimeError: Aborted(LinkError: WebAssembly.instantiate(): Import #33 module="a" function="I" error: function import requires a callable). Build with -s ASSERTIONS=1 for more info.
at L (jeep-sqlite.entry.js:2591:71)
at jeep-sqlite.entry.js:2641:387

[tobiasmuecksch]

This is not the right issue for this discussion.
Please open a new issue for that.

[jepiqueau]

@rakibul111 as mentioned by @tobiasmuecksch this is not the right place. Any how delete the sql-wasm.wasm file from your assets folder and copy the one from the node_modules/sql.js/dist/sql_wasm.wasm to your asset folder this should solve the issue

[jhuenges]

I also have a suggestion for the documentation:

What happens if the user deletes the app?

• Is the encryption secret kept?
• Are the databases and tables kept?
• If the users re-installs the app, can they continue were they left off?
• Are there differences between iOS and Android how this is done?

Were is the database stored on the phone?

• Is it included in the backup?
• If so: Can it be excluded from the backup?

@jepiqueau If you can answer these questions, I can create a pull request to add this information.

[jepiqueau]

@jhuenges thanks for your question showing à n interest in the plugin i will answer to those questions and you will be free to create a PR for this as i am quite busy by the maintenance and new features implementation

[jhuenges]

I did some research and with my limited knowledge I came to the conclusion that:

What happens if the user deletes the app?

• Is the encryption secret kept?
  • iOS: Yes, since it is stored in the KeyChain
  • Android: No, since everything in the KeyStore related to the app is deleted if the app is deleted
• Are the databases and tables kept?
  • They are deleted (?)
• If the users re-installs the app, can they continue were they left off?
  • No
• Are there differences between iOS and Android how this is done?
  • Yes, see above

Were is the database stored on the phone?

• Were is the database stored on the phone?
  • ?
• Is it included in the backup?
  • Depending on where it is stored, it can be included or excluded

Disclaimer: This is my first adventure into Swift and Java and the iOS and Android ecosystem. So I have little confidence in what I gathered. Any confirmation or correction is greatly appreciated!

[jepiqueau]

@jhuenges thanks for this and sorry to have not find the time to answer you. Your research is pretty find. The database are stored in the location that you define in the capacitor.config.ts file.on iOS if the folder is Library the databases are not backup on iCloud. If it id Documents they are. On Android read the Android quirks and the change in the manifest file to not backup them.

[mburger81]

@jepiqueau first of all thx you for this great plugin,

I would like to make a question to you, we need to use the plugin with native encryption. For me it's not clear, even because I don't find the informations on the documentation how is the best practice to encrypt the database.

In the documentation I can see the capacitor configuration parameters, like iosIsEncryption and androidIsEncryption, so i think this is the point where we should decide if a database is encrypted or not?

But at the other way, I see in the createConnection command two parameters, encrypted and mode.

So my first question is, how matters the encrypted parameter of the createConnection command with the iosIsEncryption parameters from the capacitor configuration?

And my second question is, Im not really sure how to use the mode parameter. Do I have to switch this mode between the first DB open and the second DB open? Is it possible to show a best practice workflow to use your plugin with sqlite encrypted?

thx a lot

[jepiqueau]

@mburger81 A documentation has been added DatabaseEncryption. Hope this will help you to understand the workflow

[mmouterde]

I'm just starting with this sqlite plugin, I hope this will be relevant.

My point is about the IncrementalUpgrade feature documentation.

• The main readme.md shows a broken link
• the page IncrementalUpgradeDatabaseVersion is really clear about the proposal, but less about the final implementation. Is there an example somewhere else ?
• The page UpgradeDatabaseVersion seems more relative to the implementation but mentions an openDB function I can't found.

During my tests on web platform my addUpgradeStatement does not throw error, but has no effect ?! As well as with an empty database as with an existing version.

[jepiqueau]

@mmouterde look at ionic7-angular-sqlite-app and at the blog tutorials-app at https://github.com/jepiqueau

[mmouterde]

This tuto is not really easy to follow as the final workflow is split into several services.
Anyway, that a great, complete and verbose work that helps me. thanks @jepiqueau !

If it could help someone, Here is my working resulting version in a single file src/services/databaseService.
databaseService.init() is simply called from App.vue.

Tested with Quasar+vue3+vite+capacitor (web+android)

import { Platform } from 'quasar';
import { CapacitorSQLite, SQLiteConnection, SQLiteDBConnection } from '@capacitor-community/sqlite';
import { version1 } from 'src/migrations/version1';
import { JeepSqlite } from 'jeep-sqlite/dist/components/jeep-sqlite';

const DATABASE_NAME = 'db_name'
const DATABASE_CURRENT_VERSION = 1;

const sqlite: SQLiteConnection = new SQLiteConnection(CapacitorSQLite);

export async function init() {
    customElements.define('jeep-sqlite', JeepSqlite);
    window.addEventListener('DOMContentLoaded', async () => {
        try {
            if (!Platform.is.capacitor) {
                const jeepSqlite = document.createElement('jeep-sqlite');
                document.body.appendChild(jeepSqlite);
                await customElements.whenDefined('jeep-sqlite');
                await sqlite.initWebStore();
            }

            await CapacitorSQLite.addUpgradeStatement({ database: DATABASE_NAME, upgrade: [version1] });

            const ret = await sqlite.checkConnectionsConsistency();
            const isConn = (await sqlite.isConnection(DATABASE_NAME, false)).result;
            let db: SQLiteDBConnection;
            if (ret.result && isConn) {
                db = await sqlite.retrieveConnection(DATABASE_NAME, false);
            } else {
                db = await sqlite.createConnection(
                    DATABASE_NAME,
                    false,
                    'no-encryption',
                    DATABASE_CURRENT_VERSION,
                    false,
                );
            }
            await db.open();
        } catch (err) {
            console.log(`Error: ${err}`);
            throw new Error(`Error: ${err}`);
        }
    });
}

<template>
    <q-layout>
        <router-view />
    </q-layout>
</template>

<script lang="ts">
import { defineComponent } from 'vue';
import { init } from 'src/services/databaseService';
export default defineComponent({ name: 'App'});
init();
</script>

To going back to the goal of this issue, I guess IncrementalUpgrade part is still confusing as :

• openDB function does not exists.
• "It has to be called prior to open the database with the new version number" => not only prior to open but also even prior checkConnectionsConsistency
• According to my recent experience the Vue Usage part as described here is not working. According to @jepiqueau tutorial, jeep-sqlite/loader seems not relevant any more ?!

[jepiqueau]

@mmouterde Sorry the documentation has not been updated. i focus my attention in providing tutorials. look at link to tuorials and specifically for vue3 at Vue3-Web and Vue3-Native. These tutorials are based on the latest version of Ionic7 and are using Vue with Vite. The upgrade statement must be defined prior to any connection to the database and are read and implemented when you make the connection to the database with the desired version number. This should help you to start.

[jepiqueau]

@mmouterde What is important is to define the service as unique throughout your application and i do not know how to do that with Quasar as the App.Vue i s something like this

<template>
  <router-view />
</template>

<script lang="ts">
import { defineComponent } from 'vue';
import { Capacitor } from '@capacitor/core';
import { JeepSqlite } from 'jeep-sqlite/dist/components/jeep-sqlite';

export default defineComponent({
  name: 'App',
});
</script>

so if i define a setup method i cannot use this.

[mmouterde]

Hum, ok, in this case it would be better to use boot files hook from Quasar.
from src/boot/sqlite.ts

import { boot } from 'quasar/wrappers';
import { init } from 'src/services/databaseService';

export default boot(() => {
    return init();
});

And then configure boot scripts option in quasar.config.js :

   boot: ['i18n', 'axios', 'sqlite' /*<=*/ ],

According to my tests, the DOMContentLoaded event has already occurred when boot scripts run. I had to remove the window.addEventListener('DOMContentLoaded',...) from my previous init() implementation :

import { Platform } from 'quasar';
import { CapacitorSQLite, SQLiteConnection, SQLiteDBConnection } from '@capacitor-community/sqlite';
import { version1 } from 'src/migrations/version1';
import { JeepSqlite } from 'jeep-sqlite/dist/components/jeep-sqlite';

const DATABASE_NAME = 'db_name'
const DATABASE_CURRENT_VERSION = 1;

const sqlite: SQLiteConnection = new SQLiteConnection(CapacitorSQLite);

export async function init() {
        customElements.define('jeep-sqlite', JeepSqlite);
        try {
            if (!Platform.is.capacitor) {
                const jeepSqlite = document.createElement('jeep-sqlite');
                document.body.appendChild(jeepSqlite);
                await customElements.whenDefined('jeep-sqlite');
                await sqlite.initWebStore();
            }

            await CapacitorSQLite.addUpgradeStatement({ database: DATABASE_NAME, upgrade: [version1] });

            const ret = await sqlite.checkConnectionsConsistency();
            const isConn = (await sqlite.isConnection(DATABASE_NAME, false)).result;
            let db: SQLiteDBConnection;
            if (ret.result && isConn) {
                db = await sqlite.retrieveConnection(DATABASE_NAME, false);
            } else {
                db = await sqlite.createConnection(
                    DATABASE_NAME,
                    false,
                    'no-encryption',
                    DATABASE_CURRENT_VERSION,
                    false,
                );
            }
            await db.open();
        } catch (err) {
            console.log(`Error: ${err}`);
            throw new Error(`Error: ${err}`);
        }
}

[jepiqueau]

@mmouterde In fact i did this and it works

<template>
  <router-view />
</template>

<script lang="ts">
import { defineComponent, createApp, onMounted } from 'vue';
import { Capacitor } from '@capacitor/core';
import { JeepSqlite } from 'jeep-sqlite/dist/components/jeep-sqlite';
import SqliteService from './services/sqliteService';
import DbVersionService from './services/dbVersionService';
import StorageService from './services/storageService';
import InitializeAppService from './services/initializeAppService';

export default defineComponent({
  name: 'App',
  setup() {
    customElements.define('jeep-sqlite', JeepSqlite);
    const app = createApp({});
    const platform = Capacitor.getPlatform();
    // Set the platform as global properties on the app
    app.config.globalProperties.$platform = platform;
    console.log('platform', app.config.globalProperties.$platform);
    // Define and instantiate the required services
    const sqliteServ = new SqliteService();
    const dbVersionServ = new DbVersionService();
    const storageServ = new StorageService(sqliteServ, dbVersionServ);
    // Set the services as global properties on the app
    app.config.globalProperties.$sqliteServ = sqliteServ;
    app.config.globalProperties.$dbVersionServ = dbVersionServ;
    app.config.globalProperties.$storageServ = storageServ;
    //Define and instantiate the InitializeAppService
    const initAppServ = new InitializeAppService(sqliteServ, storageServ);

    const initApp = async () => {
      try {
        await initAppServ.initializeApp();
      } catch (error) {
        console.error('App Initialization error:', error);
      }
    };
    onMounted(async () => {
      if (platform != 'web') {
        await initApp();
      } else {
        window.addEventListener('DOMContentLoaded', async () => {
          const jeepEl = document.createElement('jeep-sqlite');
          document.body.appendChild(jeepEl);
          customElements
            .whenDefined('jeep-sqlite')
            .then(async () => {
              await initApp();
            })
            .catch((err) => {
              console.error('jeep-sqlite creation error:', err);
            });
        });
      }
    });
    return {};
  },
});
</script>

the services are the same than in ionic7-vue-sqlite-app, the models and the upgrades are also the same

[jepiqueau]

@mmouterde you can remove the line
window.addEventListener('DOMContentLoaded', async () => {as it is done in the onMounted method it is working too

[jepiqueau]

@mmouterde look at quasar-sqlite-app, For now only the web part is in the tutorial the native part will come next . Hope this will help you

[jepiqueau]

@tobiasmuecksch and all coming to that issue on documentation

To all, I know that the documentation is not up-to-date and part of it will be removed soon as it is difficult to maintain as Capacitor, the plugin and the Frameworks are continuously updated.
So i will advise you to visit tutorials which are all compatible with Capacitor 5, Ionic 7 and @capacitor-community/sqlite@5.4.x. If some of you want to see others features, feel free to mention them or to participate in that effort, by creating new tutorials.