Advice: DB Heavy App


#1

Hi electron coders!

I am in need of some guidance - I have created a desktop app that loads tables that are 100Mb with the help of
AngularJS
BootStrap
AngularGrid(loads my large Json data fast!)

But now I want to go beyond loading static tables and I would like to be able to query a JSON or use a relational DB to process tables that are a few hundred megs in size about ~2-5mill rows(All data needs to be offline). I don’t want to load all that data at once but instead query it without the user waiting for it to load.

I don’t really care what DB to use as long as I can do the above.
I couldn’t get SQLite3 to work with electron, json query languages fail since they need to load the whole file…any suggestions on how to approach it?

Thanks!
Edit: Grammer, Clarity


Backend Choice for large data offline app
#2

You should be able to use SQLite; it may be a native module and need to compiled against the node version specific to the version of Electron you use. electron-rebuild can help with that.

Other options are to use one of the “web databases” built-in to Electron like IndexedDB or something node compatible like LevelDB; but basically any database you can access from node should be accessible from Electron.


#3

If you want easy access to JSON documents from within your Electron app I would suggest to go to PouchDB (www.pouchdb.org). Its a CouchDB compatible document-oriented database.


#4

PouchDB (the LevelDB backend) works fine if you just have a few thousand records and no need for fast secondary indexes. For large datasets the performance is unacceptable.


#5

This is what I have found out as well.
Pouch DB is great for the small stuff


#6

Thank you for the reply!
So I was kinda confused about IndexedDB is there a good example of how to Import/implement it it?

Setting up DB’s is kind of new to me, I mostly did front end stuff till this point.

PS I will look into LevelDB


#7

Dexie is a lightweight wrapper for IndexedDB.


#8

For PouchDB performance enhancements check out this recent blog entry: http://pouchdb.com/2015/09/01/pouchdb-4.0.1-gotta-go-fast.html.

For secondary indexes they have support via a separate plugin: http://nolanlawson.github.io/pouchdb-find/

When installed in Electron it is using the LevelDB Backend for the database, but can also be configured to use IndexedDB or LocalStorage (via adapter plugins).

It supports also binary attachments… no probs so far in our scenario.


#9

Thank for the great suggestions!

I’ll investigate them all! :smile:


#10

I’m using Google’s Lovefield for offline storage. It is also a library built atop IndexedDB, but I like the APIs a bit more than Dexie. Both are solid options.


#11

To offer a little more evidence than “performance is unacceptable” here are some numbers using my real dataset (size on disk is ~1GB). couch is using PouchDB with the HTTP adapter to connect to a local CouchDB instance; pouch is using the LevelDB adapter. The databases are replicas but replication was not running during the test.

$ node test.js
couch doc_count 119,581
pouch doc_count 119,581
couch 1 completed in 108.996 seconds
couch 2 completed in 0.035 seconds
couch 3 completed in 0.016 seconds
pouch 1 completed in 125.411 seconds
pouch 2 completed in 14.225 seconds
pouch 3 completed in 13.847 seconds

$ uname -a
Darwin sortie.local 13.4.0 Darwin Kernel Version 13.4.0: Wed Mar 18 16:20:14 PDT 2015; root:xnu-2422.115.14~1/RELEASE_X86_64 x86_64
$ node -v
v2.3.1
$ npm view pouchdb version
5.0.0
$ couchdb -V
couchdb - Apache CouchDB 1.6.1

couch 1 and pouch 1 are the number of seconds it takes to build and query the index. couch 2/3 and pouch 2/3 are number of seconds to run the same query with a pre-built index.

Even the PouchDB developers don’t recommend it for the use case described by @Aryeh.


#12

@bradstewart have you used Lovefileld with Angular 2?


#13

Negative. I’ve never used Angular 2 at all actually.


#14

@bradstewart do you have a sample electron app that you would be willing to share. I am having trouble running lovefield methods in index.js. I can create the schema and run commands if I include my lovefield specific code in a script tag nested in my main html page. I am sure I am overlooking something, so any help would be appreciated. Thanks.


#15

What solution do you choose /tried ?
I also need a database that works with millions of rows, and I will love it if I could configure it to sync between 3, 4 or more computers that are on the same local network.


#16

Hi, stewart. Do you think Lovefield is good enough for a serious product? It seems great, but pretty unknown.


#17

Define serious.

Google is using it. IndexedDB can certainly handle a reasonable amount of
data, and I haven’t had any issues with it/Lovefield storing ~50k records
on the client.


#18

Alright, thank you.
When I am say serious, I mean it is for production, not for play-around.
BTW, are you using it for electron app?

I have done some researches after seeing your recommendation. It indeed looks great. But I have some concerns.

  1. Accroding to the Lovefield team, only the page on the same domain can have access to the same DB. I don’t whether in Electron, it is working for all the windows. Because they have no domain at all.
  2. Wheter it is possible to access the DB from the main process easily? I have imagined that I can do this by IPC, but it’s kinda cumbersome.

Hope you can help me out.
Choosing a suitable DB is killing me.
Thanks in advance.


#19

Did anyone tried NeDB https://github.com/louischatriot/nedb ?


#20

It seems like Realm is coming to Electron: https://github.com/realm/realm-js/pull/667

We used Realm in our mobile app (native on iOS and Android) and it was crazy fast even with huge (hundreds of MB) datasets. Being able to use it in our desktop apps as well would be awesome.