bug: file is not a database error while opening an existing encrypted sqlite database in electron app running in windows 10 #638
Description
Please see below for a potential FIX.
Plugin version:
"@capacitor-community/sqlite": "^7.0.0",
Platform(s):
electron (running on Windows 10)
"@capacitor-community/electron": "^5.0.1",
Current behavior:
Created a simple ionic app with latest ionic, angular, capacitor version. It has simple code/UI to create unencrypted & encrypted db and read unencrypted & encrypted db. Creating/Reading unencrypted db works fine in android & electron. Also, creating encrypted db works fine in android & electron. Also, reading encrypted db works fine in android, but give file is not a database error in electron platform running in Windows.
Expected behavior:
Should able to open encrypted sqlite db in electron.
Steps to reproduce:
Demo app is here https://github.com/veeru-d/sqlite-electron-v2. It provides all the steps.
I followed https://github.com/capacitor-community/sqlite but it didn't work.
getting "CapacitorSQLite" plugin is not implemented on electron error.
then tried suggestions per #636 (comment) and #636 (comment)
with this creating/reading unencrypted db works fine.
Related code:
clone and run this demo app: https://github.com/veeru-d/sqlite-electron-v2. README provides all the steps.
async readEncryptedData() {
const sqlite = new SQLiteConnection(CapacitorSQLite);
console.log('readEncryptedData: sqlite=', JSON.stringify(sqlite));
const { result: isSecretStored } = await CapacitorSQLite.isSecretStored();
console.log('readEncryptedData: isSecretStored=', isSecretStored);
if (!isSecretStored) {
await CapacitorSQLite.setEncryptionSecret({
passphrase: this.passphrase,
});
console.log('readEncryptedData: set encryption secret successfully');
}
////////// Initialization
const readOnly = true; // must be true to access an existing db
const db = await sqlite.createConnection(
this.encryptedDbName,
true,
'secret',
0,
readOnly
);
console.log('readEncryptedData: db=', JSON.stringify(db));
await db.open(); ////// <<========== FAILS HERE
//////////
////////// read rows
const query = 'SELECT * FROM todos;';
const result = await db.query(query);
console.log(
'readEncryptedData: query result=',
JSON.stringify(result.values)
);
//////////
////////// cleanup
await db.close();
await sqlite.closeConnection(this.encryptedDbName, readOnly);
//////////
}
Other information:
cmd-line error:
...
called ipcMain.handle: CapacitorSQLite-isSecretStored
called ipcMain.handle: CapacitorSQLite-createConnection
&&& Databases path: C:\Users\Public\sqlite-electron\todoencSQLite.db
called ipcMain.handle: CapacitorSQLite-open
Error occurred in handler for 'CapacitorSQLite-open': Error: Open: Error: Open: Error: OpenOrCreateDatabase DbChanges: file is not a database
at CapacitorSQLite.open (D:\ezeescore\sqlite-electron-github-v2\sqlite-electron-v2\electron\node_modules\@capacitor-community\sqlite\electron\dist\plugin.js:5371:20)
at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
at async WebContents.<anonymous> (node:electron/js2c/browser_init:2:89579)
Capacitor doctor:
$ npx cap doctor
Capacitor Doctor
Latest Dependencies:
@capacitor/cli: 7.2.0
@capacitor/core: 7.2.0
@capacitor/android: 7.2.0
@capacitor/ios: 7.2.0
Installed Dependencies:
@capacitor/ios: not installed
@capacitor/cli: 7.2.0
@capacitor/core: 7.2.0
@capacitor/android: 7.2.0