sql.js icon indicating copy to clipboard operation
sql.js copied to clipboard

Published 20 hours ago • verified publisher icon sql-js

advantages of using SQLjs over SQLite native in NodeJS

Open daonsh opened this issue 5 years ago • 6 comments
trafficstars

Hi,

Thanks for this project, it's very useful. I see in the homepage of SQLjs that it's recommended to use SQLite native if using server-side NodeJS code. However there is an example of using SQLjs with NodeJS.

Comparing the two options, I see several advantages to using SQLjs with NodeJS:

  1. Same database interface, if needing both server-side and client-side database
  2. Easier to test and migrate: can first develop all client-side code and easily move database to server for production uses.
  3. It's easier and faster to use in-memory database with SQLjs. SQLjs can load the database file directly into memory. With SQLite native what I see is that it's possible to make only an empty in-memory database and populate it with SQL commands (which will take time to run).

The only downside I see is that Emscripten code is usually at 50-60% of the performance of native code. So SQLjs is slower (how much?).

Is my reasoning correct? Especially regarding point #3 - I couldn't find a viable solution to load the file into memory with SQLite native (fast), while SQLjs can easily do this.

Thanks

daonsh avatar Feb 25 '20 20:02 daonsh

long-term i agree sql.js should be used instead of native in nodejs. however,

  1. webassembly is still experimental and hidden behind a flag in nodejs (making it unsuitable for production use). this hopefully will go away in 2 years with node v14.

  2. also, there's no way to incrementally persist a 1gb database in sql.js (you have to rewrite the entire blob to local filesystem everytime you update it). the webassembly-group will need to come up with new filesystem api's to overcome this limitation.

kaizhu256 avatar Feb 25 '20 20:02 kaizhu256

Performance wise which is better?

sql.js takes long time for me. Maybe I am missing something

AnandarajT avatar Jul 25 '20 14:07 AnandarajT

Using the native sqlite is faster and lighter.

lovasoa avatar Jul 25 '20 15:07 lovasoa

what's the maximum storage limitation of sql.js?

rishabbhattacharyya avatar Sep 17 '20 02:09 rishabbhattacharyya

depends on browser. for chrome it appears to be 4gb (https://v8.dev/blog/4gb-wasm-memory). My experience however, is chrome-browser (windows 10) starts to freeze and crash when working with datasets over 1gb.

kaizhu256 avatar Sep 17 '20 11:09 kaizhu256

Any reason why sql.js wouldn't be suitable for use with an ORM? (I'm using the sqlite support in typeorm). Would it be unrealistic to expect to expect the adapter to work with sql.js also?

E2A - i see comments here but perhaps he's mistaken

elmpp avatar Sep 17 '20 20:09 elmpp