4

I'm trying to open an sqlite database file using node-sqlite3 in my Angular + Electron app.

Despite I made sure that the file exists and Electron can read it, when I try to create the sqlite database using:

import * as sqlite from 'sqlite3';

// ...

const path = `${__dirname}/assets/sqlite.db`;

const fs = require('electron').remote.require('fs');
console.log(path);
if (fs.existsSync(AppConfig.sqlitePath)) {
  console.log('the file exists');
} else {
  console.log('the file does not not');
}

const myDb = new sqlite.Database(path, sqlite.OPEN_READONLY, (error) => {
  console.log(error);
});

The file exists, since I got the right console.log message and it looks like:

/tmp/.mount_xxxxxx/resources/app.asar.unpacked/dist/assets/sqlite.db

However, from new sqlite.Database(...) I get the following error, as the file didn't exist:

Error: SQLITE_CANTOPEN: unable to open database file

Francesco Borzi
  • 56,083
  • 47
  • 179
  • 252
  • May I asked why did you put the database inside assets folder? – Hoang Subin Feb 27 '20 at 02:25
  • @HoangTranSon just because I wanted it to be included in the `dist` directory on app build. I know I could just use a different path by adding a new entry in my `angular.json` file, but I just wanted to have a working prototype quickly and the `assets` folder is being included by default. – Francesco Borzi Feb 27 '20 at 10:07
  • May I asked how can you control security problem when you put that file in `assets` folder? I am facing same issue with react and the workaround now is put the db file into `public` folder. It looks like not a good way. – Hoang Subin Feb 27 '20 at 10:53
  • 1
    @HoangTranSon Sorry, I don't have any security concerns as my sqlite DB does not contain any sensitive data at all and it's already public – Francesco Borzi Feb 27 '20 at 22:53

3 Answers3

4

I was tipped by artiebits to look at asarUnpack and indeed that was the right way to go.

I solved by:

1) adding the following to my electron-builder.json:

"asarUnpack": [
  "dist/assets/sqlite.db"
],

2) replacing:

const path = `${__dirname}/assets/sqlite.db`;

with:

const path = `${__dirname}/assets/sqlite.db`.replace('app.asar', 'app.asar.unpacked');
Francesco Borzi
  • 56,083
  • 47
  • 179
  • 252
0

Another solution here using the extraResources parameter: https://github.com/electron/electron/issues/4644#issuecomment-637030403

# packages.json
"build": {
    ...
    "extraResources": [
      "databaseFolder"
    ],
   ...
}

Make sure to check if the database file exists then sync

const path = require('path');
const fse = require('fs-extra')
const Sequelize = require('sequelize');
const dbPath = path.resolve(process.resourcesPath, 'databaseFolder')
const dbFile = path.resolve(dbPath, 'database.sqlite')

const sequelize = new Sequelize({
  dialect: 'sqlite',
  storage: dbFile
})

if (!fse.existsSync(dbPath)) {
  fse.ensureDirSync(dbPath)
  fse.ensureFileSync(dbFile)
  sequelize.sync({ force: true })
}
Pedro Casado
  • 1,705
  • 1
  • 21
  • 43
0

Use the absolute address of the database file

Example:

Linux:

const path = "/home/brunodev/Documents/vscode/Git/backend/src/database/database.db"

Windows:

const path = "C:/Users/brunodev/Documents/vscode/Git/backend/src/database/database.db"

user14869304
  • 1