Capacitor SQLite DB issues (Electron)

Wappler General Bugs
1

Wappler Version: 6.0 Beta 16
Operating System: Windows 11
Server Model: Capacitor / Electron
Database Type: SQLite
Electron Package json:
“@capacitor-community/electron”: “^4.1.2”,
“@capacitor-community/sqlite”: “^4.8.0”,
“@capacitor/cli”: “^5.5.1”,
“@capacitor/core”: “^5.5.1”,

I’m not sure how to get past this error. I had an existing SQLite db located in the CapacitorDatabases directory that had a typo in one of the columns. I updated the column in Wappler, but the change was not reflecting in the installed apps db, so I renamed the db, then moved it to another folder hoping that it would create a new db. Instead I’m getting this error message. This db was created months ago, so it appears something may have changed during an update to one of the related files causing new updates to not alter the db.

Error: Error invoking remote method 'CapacitorSQLite-open': Error: Open: Error: Open: Error: Open: RestoreFileName: DeleteFileName: delete filePath failed DeleteFilePath: Error: EBUSY: resource busy or locked, unlink 'C:\Users\Keith\CapacitorDatabases\Brightyard\brightyard_localSQLite.db'

It starts to create a new DB file, with the same name, brightyard_localSQLite.db, but also creates an additional “backup” db and both are empty.
image

image
image1493×137 7.25 KB

If I put the old DB file back into the folder, then the error goes away, but I still have the column that’s incorrectly named, so I’m not sure how to correct it.

2

It looks like this may be limited to Windows. Still doesn't explain why a column rename doesn't update the existing db.

3

It doesn’t look like any updates (additions, deletion, renames) of fields are added to the existing db of the installed Electron app. Even uninstalling and reinstalling has no effect.

4

I have also attemped to change the location of the databases in capacitor.config.json.

image

The newly built app attempts to use it, but throws the same error.

image
image1702×417 35.3 KB

It also shows the same two files with no data. Not sure why it keeps creating a backup

image
image921×172 4.57 KB

5

Here’s an error where it should create a new table, but doesn’t.

image
image1377×229 8.42 KB

Here’s the upgrade statement showing the table (GmailHistory) it should create.

image
image1890×191 12.1 KB

6

I’ve tested building a completely new project, adding new fields and new tables to sqlite, rebuliding and packing electron, and installing new the electron app on machine. It all appears to work correctly, so the issue seems to be related to my older project, but I’m not sure what could be outdated and causing the db to not get updated. I can even delete/remove the new db file and Electron will recreate a new one in the CapacitorDatabases directory without any issues.

7

@patrick, any idea how I might dig into this further?

image
image1915×555 44.6 KB

api.js:22 points to this function, but I’m not sure how to figure out what is triggering the CapacitorSQLite-open or how to troubleshoot it.

image

8

Not sure if it’s related but may be worth a try…

I had to update the version of sqlite manually to get it to work.

9

Thanks @jmartland22! Fortunately I’m able to build/pack the app, it’s during runtime that I’m experiencing the issue. Either way I did test 5.4.0, but it didn’t appear to help for this issue.

10

Here’s a slightly different error. I experimented with creating a new database connection. With one simple table (email) containing two fields. I also created a test page with nothing on it except for an App Flow which inserts a record into the db and then performs a query to get the records. I’m not even triggering the flow, so the only event that’s happening on the page is it’s attempting to initialize the db.

image
image1910×569 25 KB

Here’s the page code.

<!doctype html>
<html>
<head>
    <script src="dmxAppConnect/dmxAppConnect.js"></script>
    <meta charset="UTF-8">
    <title>Untitled Document</title>
    <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
    <link rel="stylesheet" href="bootstrap/5/css/bootstrap.min.css" />
    <script src="capacitor.js"></script>
    <link rel="stylesheet" href="css/style.css" />
    <script src="dmxAppConnect/dmxCapacitorSQLite/dmxCapacitorSQLite.js" defer></script>
    <script src="js/connections/test.js" defer></script>
    <script src="js/routes.js" defer></script>
</head>

<body is="dmx-app" id="untitled">
    <button id="btn1" class="btn" dmx-on:click="flow1.run()">Button</button>
    <script is="dmx-flow" id="flow1" type="text/dmx-flow" src="app/flows/gmail/testflow.json"></script>
    <script src="bootstrap/5/js/bootstrap.bundle.min.js"></script>
</body>
</html>
11

Ok, got past the most recent error by comparing against another completely new project. The issue was that I had “Use SQLite” enabled in Project Options. I’m not sure what that is supposed to do, but disabling it fixed the Cannot read properties of undefined (reading 'addUpgradeStatement')

image
image846×153 5.49 KB

12

At the moment, this appears to be due to a corrupted database structure created in the Database Manager. I have no idea what was corrupted, or how, since it was built entirely using Wappler and worked well in SQLite for some period of time and also worked with PouchDB more recently, at least the structure.