Share connection across Vue components using Object API

[jeremyburton]

Hi. I'm new to using @capacitor-community/sqlite and am having some difficulties integrating it into my setup.

I'm building an iOS/Android app with Vue 3 and Capacitor 3. Note that I'm not using Ionic Vue, just a standard Vue CLI scaffolded project. Also, I'm using the Options API in my Vue components, rather than the Composition API (and I'm not using Typescript).

I've managed to create a connection to an SQLite db and can create tables and perform SELECT, INSERT etc. However I'm stuck on how to share a single SQLite database connection across all of my Vue components. I am not using vue-sqlite-hook or $existingConn as I don't know if/how these can be implemented without using Vue's Composition API.

I thought perhaps I could instead create a database connection as a utility that I import into all my components but I've been unable to get this to work. Any suggestions would be much appreciated. Thanks!

[jepiqueau]

@jeremyburton thanks for using the plug-in I am in vacation time right now so I cannot test anything. I do not know why you make your life difficult in not using the latest technologies coming with Vue. The vue-sqlite-app-starter demonstrates how to do that. If you do not want to use Ionic/Vue you can do that by removing all the Ionic features

[jeremyburton]

@jepiqueau thanks so much for getting back to me whilst you're on vacation. I've been using Vue for a few years and have built up a strong mental model (and lots of my own components) using the options API. I'm sure I'll want to learn to use the composition API at some point but until now I haven't needed it and it wasn't an option until quite recently. If, when you're back from your vacation, you have the time, it would be really useful to see an example that demonstrates how to use the plugin in a way that is less framework-specific (e.g. by importing a plain javascript service). Thanks again and enjoy your vacation!

[jepiqueau]

@jeremyburton Did you try to create the connection in the main.js file with a variable app.config.globalProperties$connection pointing to it and share it in your components like

         const connection = app?.appContext.config.globalProperties.$connection;

i think this may work but it is possible with one connection, otherwise you may do it for the CapacitorSqlite plugin and share the plugin. this is some ideas not tested. Can you revert to me if it is solving your issue

[jeremyburton]

@jepiqueau thanks for replying again. I have managed to share the initial sqlite connection as a global property in main.js:

...
import { CapacitorSQLite, SQLiteConnection } from "@capacitor-community/sqlite";
const sqliteConnection = new SQLiteConnection(CapacitorSQLite);

const app = createApp(App).use(router);
app.config.globalProperties.$sqliteConnection = sqliteConnection;

router.isReady().then(() => {
  app.mount("#app");
});

And I can access this global property in components:

const sqliteConnection = this.$sqliteConnection;
const db = await sqliteConnection.createConnection("MY_DB", false);

However, I now get a "connection already exists" error when I connect in this way from multiple components. I understand why I get this error but I'm not sure how I should access an already open db connection. Sorry, I know this is very basic stuff but I'm new to SQLite. Thanks again for your help.

[jepiqueau]

@jeremyburton on each component you can check if a connection exists isConnection if yes you do not create it otherwise you create it. It also may happen that you need to run checkConnectionConsistency if it return false you recreate the connection

[jeremyburton]

@jepiqueau thanks, makes sense, but I'm not sure how to access the connection if it already exists:

const sqliteConnection = this.$sqliteConnection;
let db;
if (sqliteConnection.isConnection("MY_DB")) {
  // Get existing db connection
  db = // WHAT GOES HERE? 
} else {
  // Create a connection to the db
  db = await sqliteConnection.createConnection("MY_DB", false);
}

[jeremyburton]

@jepiqueau is this right?

if (sqliteConnection.isConnection("MY_DB")) {
  // Get existing db connection
  db = await sqliteConnection.retrieveConnection("MY_DB");
} else {
  // Create a connection to the lollysticks db
  db = await sqliteConnection.createConnection("MY_DB", false);
}

[jepiqueau]

@jeremyburton yes it is right

[jepiqueau]

@jeremyburton in fact you should do If (!sqlite.connection.isConnection("MY_DB") || !checkConnectionConsistency()) { // create connection } else { // retrieve connection }

[jeremyburton]

@jepiqueau I'm trying to get this working but .isConnection always returns false and I've not been able to retrieve the connection.

      let db;
      if (
        !sqliteConnection.isConnection("MY_DB") ||
        !sqliteConnection.checkConnectionConsistency()
      ) {
        // Create a connection
        db = await sqliteConnection.createConnection("MY_DB", false);
      } else {
        // Get existing connection
        db = await sqliteConnection.retrieveConnection("MY_DB");
      }

In the Xcode console I get:

[log] - isConnection false
[error] - {}

[jepiqueau]

@jeremyburton can you share your code or a small replicate which can demonstrate the issue that will avoid me to start from scratch as now I do not understand why it is not working

[jeremyburton]

@jepiqueau thanks once again for your ongoing help with this. I've created a basic version of my code for you to take a look at: https://github.com/jeremyburton/vue-capacitor-sqlite. The initDB() method in the App component is supposed to seed the database and then in the Home component I'm trying to read from the database. I may well have misunderstood the correct sequencing of these database calls but I think that is a separate issue to the problem I'm having with retrieving an exisiting connection.

[jepiqueau]

@jeremyburton I cannot install your app for the time being as I get a timeout in npm install due to a really poor connection where I am right now. I do not think in the App.Vue you need to test if there is a connection as obviously there is none when you mount the app. So just create the connection. As you did not close the connection at the end of App which is right when you enter home the connection should be there and the db is still opened as you did not do db.close () in App which is also right.so in Home in case the connection was lost for any internal reason your test is correct but after createConnection you need a db.open () but when you retrieveConnection ("MY_DB") you do not need to do a db.open () as it was done in App.vue like this it should work

[jeremyburton]

@jepiqueau Thanks, I have made the changes you suggested but this does not resolve all the issues. The initDB() method in App component is being executed AFTER the getTeams() method in the Home component. The initDB() method works as expected but the getTeams() method appears to fail when .isConnection("MY_DB") returns false. I have updated the code so you can try it yourself when you are able to. Please feel free to leave this until you get back from your vacation. I can work on something else for now.

Logging from Xcode:

2021-06-25 12:17:40.912404+0100 App[5233:63111] DiskCookieStorage changing policy from 2 to 0, cookie file: file:///Users/jeremy/Library/Developer/CoreSimulator/Devices/C32C0DCE-1121-4CE7-BDE4-3F9A26A7F26E/data/Containers/Data/Application/568E042B-4808-4890-9435-A96BAAED481B/Library/Cookies/app.clevercrow.vuecapacitorsqlite.binarycookies
⚡️  Loading app at capacitor://localhost...
2021-06-25 12:17:41.225690+0100 App[5233:63111] WF: === Starting WebFilter logging for process App
2021-06-25 12:17:41.225857+0100 App[5233:63111] WF: _userSettingsForUser : (null)
2021-06-25 12:17:41.225997+0100 App[5233:63111] WF: _WebFilterIsActive returning: NO
⚡️  [log] - Check if we have an exisiting connection...
⚡️  [log] - isConnection false
⚡️  To Native ->  CapacitorSQLite createConnection 24022815
⚡️  [log] - Make initial connection to db...
databaseName: MY_DBSQLite.db 
database path /Users/jeremy/Library/Developer/CoreSimulator/Devices/C32C0DCE-1121-4CE7-BDE4-3F9A26A7F26E/data/Containers/Data/Application/568E042B-4808-4890-9435-A96BAAED481B/Documents/MY_DBSQLite.db
⚡️  TO JS undefined
⚡️  WebView loaded
⚡️  [log] - >>> in SQLiteDBConnection dbName MY_DB
⚡️  To Native ->  CapacitorSQLite open 24022816
⚡️  [log] - >>> in SQLiteDBConnection open dbName MY_DB
⚡️  TO JS undefined
⚡️  To Native ->  CapacitorSQLite execute 24022817
⚡️  TO JS {"changes":{"changes":0}}
⚡️  To Native ->  CapacitorSQLite execute 24022818
⚡️  TO JS {"changes":{"changes":0}}
⚡️  To Native ->  CapacitorSQLite execute 24022819
⚡️  TO JS {"changes":{"changes":4}}
⚡️  To Native ->  CapacitorSQLite execute 24022820
⚡️  TO JS {"changes":{"changes":12}}

[jepiqueau]

@jeremyburton I will come back to you when I will have found the solution it is related to Vue and async component

[codeluggage]

Hi @jeremyburton - how about leveraging async during the mount step so the connection is available from the beginning?

• Use the db everywhere, instead of the SQLiteConnection
• Force the app to wait until the connection is established before mounting (and stuff begins to render and be visible to the user)

import { CapacitorSQLite, SQLiteConnection } from "@capacitor-community/sqlite";
const sqliteConnection = new SQLiteConnection(CapacitorSQLite);

const app = createApp(App).use(router);

router.isReady().then(async () => {
  app.config.globalProperties.$db = await sqliteConnection.createConnection("MY_DB", false);
  app.mount("#app");
});

In regards to testing (off device), I can recommend decoupling the use of db directly and instead exposing the regular interactions you make (select, update, etc), so they can be dependency injected during testing. Still figuring this part out myself, though :)

[jepiqueau]

@jeremyburton you may do this but if you want to use several databases in your app it will not work. The problem is more specific to Vue. The Home.Vue is execute before the initialization of the sqlite.connection in the App.Vue this is why it is returning false. It is the problem of async component where you should embedded them into a Suspense Tag. I am pretty sure that if you create a Team.Vue and you route to that Vue after that the Home.Vue is mounted it will work. You do nothing in the Home.Vue. I saw this when I was pressing on the About.Vue anD come back to the Home.Vue the code was well executed. I think also your code is more Vue2 than Vue3. Hope this will give you some directions to look at. You should have a look at the vue-sqlite-app-starter to help you even if it is in typescript I define Vue calling component with the Suspense tag good luck I am still travelling

[jepiqueau]

@jeremyburton you can find the proposed solution at https://github.com/jepiqueau/vue-capacitor-sqlite-issue137.git hope this will help you to find your way for your real app. It was to find the proper way in Vue to deal with async methods and component. The Home setup was run before the database was created that why await sqliteConnection.isConnection("MY_DB") was always returning false. I let you closing the issue. Good luck

[jepiqueau]

@jeremyburton Did you had time to have a look

[jeremyburton]

@jepiqueau Sorry, I've had a few busy days. I haven't had a chance to implement your solution yet but I did read through the code and I can see how it solves the problem. I did try using the solution suggested by @codeluggage and that worked for me. I'm not sure I'll ever need to access multiple databases in this app but I'll probably go with your solution just in case. If you're okay to wait a couple of days, I'll come back and close this issue when I've had the chance to implement the proposed solution and test it out. Many thanks to both of you for your help with this issue and I look forward to getting stuck into using the plugin in my app.

[jeremyburton]

@codeluggage do you have any code you can share with me to show how you have decoupled the plugin implementation from your db calls so that you can test off device? That would be super helpful as I'm not too sure of the best way to do this.

[codeluggage]

@codeluggage do you have any code you can share with me to show how you have decoupled the plugin implementation from your db calls so that you can test off device? That would be super helpful as I'm not too sure of the best way to do this.

Ha, you and me both! 😉

I've been through so many "sqlite in memory" projects recently, but each has had some kind of issue. The final solution I ended up with to TDD with Cypress was to use https://github.com/agershun/alasql for in-memory sqlite when the app is not running on-device.

In general the same schemas will work, but I have not been able to use https://github.com/sql-js/sql.js to read from disk (wasm issues in one way or another - when I did get past loading wasm, loading a basic sqlite file hit memory errors). Instead, I exported data to use for testing to csv, put those in public/assets and loaded them with Alasql.

I can post some relevant snippets if you'd like to go on the same adventure :joy:

[jeremyburton]

@codeluggage that sounds like a whole new world of pain that I could probably do without. But thanks for your overview and I now know who to bug for help if I do choose to go down this route at some point 😉

[jepiqueau]

@jeremyburton i will close the issue. Feel free to re-open if necessary

[jeremyburton]

@jepiqueau I have attempted to implement your solution but the execution of the async Teams component fails. You can see my code here: https://github.com/jeremyburton/vue-capacitor-sqlite

Also, your suggested solution makes use of Suspense which is an experimental Vue 3 feature and the official docs state this should not be used in production.

[jepiqueau]

@jeremyburton i update my app https://github.com/jepiqueau/vue-capacitor-sqlite-issue137.git where i remove the tag Suspense. I also look at https://github.com/jeremyburton/vue-capacitor-sqlite to make it working do the following ( i forgot that we did not use vue-sqlite-hook)

• in Home.vue remove Suspense

<template>
  <div class="home">
    <Teams />
  </div>
</template>

<script>
import { defineAsyncComponent } from "vue";
export default {
  name: "Home",
  components: {
    Teams: defineAsyncComponent(() => import("@/components/Teams.vue")),
  },
};
</script>

<style>
.home {
  margin: 4rem 0;
}
</style>

• in Team.vue the method is sqliteConnection.checkConnectionsConsistency()

<template>
  <div class="home">
    <h1>Teams</h1>
    <ul v-if="teams">
      <li v-for="team in teams" :key="team.id">
        {{ team.name }}
      </li>
    </ul>
  </div>
</template>

<script>
export default {
  name: "Teams",
  data() {
    return {
      teams: [],
    };
  },
  async mounted() {
    this.teams = await this.getTeams();
  },
  methods: {
    async getTeams() {
      // Retrieve exisiting sqlite connection
      const sqliteConnection = this.$sqliteConnection;
      let db;
      console.log("Check if we have an exisiting db connection...");

      if (
        !(await sqliteConnection.isConnection("MY_DB")).result ||
        !(await sqliteConnection.checkConnectionsConsistency()).result
      ) {
        // Create a connection to the MY_DB db
        console.log("Create a connection to the db...");
        db = await sqliteConnection.createConnection("MY_DB", false);
        // Open the MY_DB db
        await db.open();
      } else {
        // Retrieve existing db connection
        console.log("Retrieve exisiting db connection...");
        db = await sqliteConnection.retrieveConnection("MY_DB");
      }
      // Query the MY_DB db for all teams
      let res = await db.query("SELECT * FROM teams");
      console.log(`res.values: ${JSON.stringify(res.values)}`);
      // Return teams
      return res.values;
    },
  },
};
</script>

with this it works

[jeremyburton]

in Team.vue the method is sqliteConnection.checkConnectionsConsistency()

@jepiqueau thank you so much! I'm delighted to finally have this working. Thanks again for all your help :-)