search

sql-js / sql.js

A javascript library to run SQLite on the web.
http://sql.js.org
Other
12.59k stars 1.06k forks source link

advantages of using SQLjs over SQLite native in NodeJS #350

Open daonsh opened 4 years ago

daonsh commented 4 years ago

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

kaizhu256 commented 4 years ago

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.

AnandarajT commented 4 years ago

Performance wise which is better?

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

lovasoa commented 4 years ago

Using the native sqlite is faster and lighter.

rishabbhattacharyya commented 4 years ago

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

kaizhu256 commented 4 years ago

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.

elmpp commented 4 years ago

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