How to access a local db on windows through electron


#1

Hello,

I am new to electron, and I am trying to create a desktop web application to access a local database. I already have a sqlite database on the machine that I need to be able to read/write to through a web application. I am able to achieve this on a mac, but my main customers run on a windows platform. I have tried multiple different options to achieve this, but I always run into all kinds of errors when I try to make this work on a windows platform. I have yet to be able to find a working example of this. The closest I could find was from the following link:

http://kripken.github.io/sql.js/GUI/

I basically need that example in the form of a web application. The closest I’ve come to making it work on windows has given me the following error:

Uncaught Error: EPERM: operation not permitted, write

My searches have led me in circles trying to figure this out. Any help in a solution for this would be greatly appreciated.

=====================================================


EPERM: operation not permitted, rename
#2

So just so I have this correct. You would like to write a native application, with electron, that can access a local database?

When you start an electron app you have a main process and a renderer process. What helped me figure out how to go about this is thinking of these two processes as a web app’s backend and front-end. The front-end or renderer process cannot directly mingle with the local database. Only the main process can do that. Therefore you will have to use the ipc module (Inter-process communication?) that electronjs provides to hook everything up.

It is kind of similar to how ajax works for webapps. Do you need an example?


#3

That’s not true. As a matter of fact, I recommend specifically to access a database through the renderer process. See:


#4

An example would be wonderful! I’ve been looking for a working example.


#5

Thanks for the link, I will look into this now!


#6

Ahh I see, thanks for the clarification. What I have been doing until now is passing my data objects through ipc to the main process.


#7

Is there a working example of something like this somewhere? I read through that link and I’m not quite sure I’m understanding the application to my current issue.


#8

Do you have a working example of an application accessing the local database?


#9

It’s highly dependent on what you mean by “local database”. Can you please give a little more information as to what you’re trying to do? (BTW, accessing a database from Electron is a really popular topic here. You may find what you’re looking for by simply searching for “electron db”.)


#10

Basically, I already have a sqlite database on the customers machine. I am needing to be able to read/write sql statements to that database on occasion. The databases could range from very small, to very large sets of data in them. The sql.js module seems to do precisely what I need, but doesn’t seem to want to play well with windows, so I was looking for alternatives or maybe seeing if I’m doing something wrong. I’ve been searching around for days and days trying to come up with a solution to this issue. I started in nw.js but ran into versioning issues that I couldn’t resolve and then I found Electron, which I love, but run into different issues when trying to make things work on a windows machine. I looked into pouchDB before, but I also could not get that to working on windows either.


#11

Have you taken a look at the sqlite3 package?

https://www.npmjs.com/package/sqlite3


#12

Yea, I tried sqlite3 before and ran into issues on windows where it would give me errors like
"Uncaught Error: Cannot find module ‘/Users/batDev/Desktop/nw-sqlite/node_modules/sqlite3/lib/binding/node-webkit-v0.8.6-darwin-ia32/node_sqlite3.node’ module.js:343"

It looks as though he’s updated since the last time I tried, so I will try that again and see if I have any luck.


#13

After installing sql.js with npm install --save sql.js this works for me in Electron 0.34.3.

var fs = require('fs')
var sql = require('sql.js')
var bfr = fs.readFileSync('/tmp/db.sqlite')
var db = new sql.Database(bfr)
db.each('SELECT * FROM test', function (row) {
  console.log(row)
})

#14

This works for you in a windows environment as well? I will try it out now, thanks!


#15

I don’t have a Windows environment but I think sql.js is just javascript and should work everywhere. Good luck.


#16

Okay, so I created a basic electron app.

main.js
Kept this the default code from git clone from electron.

index.html

<!DOCTYPE html>
<html>
  <head>
    <title>Hello World!</title>
  </head>
  <body>
    <h1>Hello World!</h1>
    We are using node <script>document.write(process.versions.node)</script>,
    Chrome <script>document.write(process.versions.chrome)</script>,
    and Electron <script>document.write(process.versions.electron)</script>.
    <script>require('./db.js')</script>
  </body>
</html>

db.js

var fs = require('fs');
var sql = require('sql.js');
var bfr = fs.readFileSync('./tmp/db.sqlite');
var db = new sql.Database(bfr);
db.each('SELECT * FROM test', function (row) {
  console.log(row);
});

I did npm install --save sql.js like you said, using Electron 0.34.3.
Here is my folder structure:

This works fine if I run npm run start but if I try to build for mac even, I get an error saying
Uncaught Error: ENOENT: no such file or directory, open ‘./tmp/db.sqlite’

I feel like I’m missing something really obvious here. I look into the package contents > Resources > app > tmp and I can see the db.sqlite file there.
I feel a facepalm moment coming up. I’m still learning this stuff, so I might just have things setup wrong.


#17

Have you checked the process.cwd()? Perhaps ./tmp/db.sqlite isn’t pointing to where you think it is?


#18

Looks like something wrong with the path to your database. You can use path.resolve to get a full path:

var path = require('path')
var bfr = fs.readFileSync(path.resolve(__dirname, 'tmp', 'db.sqlite'))

#19

I should’ve just waited for @john to reply :laughing:


#20

That fixed it! Thanks John! Now to test to see if it runs on windows.