7 ms·

SQL.js: SQLite Compiled to JavaScript

183 points by chmaynard 6y ago

jitl 6y ago
SQLite has a system that abstracts various low-level storage operations called VFS - this is the layer that allows SQLite to be portable across different operating systems and environments: https://www.sqlite.org/vfs.html https://www.sqlite.org/vfs.html Recently I’ve wondered how possible it would be to implement a SQLite VFS on top of IndexedDB - and, would such a VFS be competitive in speed to using IndexedDB directly? Or, would it be equivalent to use Emscripten’s existing POSIX-ish filesystem backed by IndexedDB? An IndexedDB VFS would allow sql.js to durably persist data in the browser.
- WrtCdEvrydy 6y ago
  Wouldn't it be better if you could directly store this data to cookies or localStorage? Edit: The concern is always about allowing it to be easy to delete junk data.
  - tracker1 6y ago
    IndexedDB should perform better without locking the browser context.
    - edoceo 6y ago
      Yea, my mobile Chrome (pixel 3) hang on the UI thread a lot, can't even scroll till it yields.
      - tracker1 6y ago
        I get a lot of that on mobile advertising on my phone (Android). It causes jittery behavior trying to just scroll... usually open in brave and it works a little better (opinions on brave itself aside, it just happens to work better for me than other adblock options).
  - shakna 6y ago
    The file size limits on cookies and localStorage might make it a bit impractical for the kinds of things you'd want to store in a database. You won't be caching many image files that way, for example.
  - jitl 6y ago
    Cookies are not a good spot for bulk data because each request to the server includes all the cookies. You wouldn’t want your whole SQL database included in each fetch() request. localStorage is not durable. At Notion, we observed Chrome localStorage losing writes under load from multiple async writers. IndexedDB is the most durable option, but has quite an annoying and error-prone API, which is why it would be nice to paper over it with SQLite so browser code can use the same schemas and queries as native clients.
    - ravenstine 6y ago
      > localStorage is not durable Side tangent: Why in 2020 do we find the state of localStorage to be acceptable? There's two problems that I see with localStorage: - It's too easy for someone to blow it away and lose data for a web app - Not enough capacity to be useful for a lot of things. (10 megabytes per domain) The design of localStorage is basically flawed, IMO. It should have been two things: volatileStorage and permanentStorage. volatileStorage would basically be exactly what localStorage is today, being very limited and not requiring any permissions. permanentStorage would be like localStorage except it would require explicit permission, allow unlimited storage, and would be more difficult to accidentally delete(separate delete dialog from Clear History). As far as I know, we don't have anything like my proposed permanentStorage outside of web extensions, which leaves localStorage in a weird area where it's only really useful for local app settings, even though such sparse data is easy enough to just store on a server in the first place. It would still be useful for truly offline-first apps, or apps that don't require accounts, but then this space of apps is still crippled by limited storage capacity.
      - nicoburns 6y ago
        indexedDB is the closest thing to permanent storage. This works quite well in Chrome/Firefox. But Apple don't persist it properly (presumably because they don't want webapps competing with their iOS ecosystem).
        Veen 6y ago
        > presumably because they don't want webapps competing with their iOS ecosystem Or because trackers are using indexeddb to bypass Safari's anti-tracking and privacy measures.
- tracker1 6y ago
  Similarly, would like to see SQLite work over a WASI's File System interface... best would be the ability to also support multi-process, but I don't know that it would be possible.
- RcouF1uZ4gsC 6y ago
  > Recently I’ve wondered how possible it would be to implement a SQLite VFS on top of IndexedDB What is interesting about this approach is that at least for Firefox, IndexDB is implemented using SQLite. So ultimately this approach is SQLite running in SQLite with an IndexDB layer in the middle.
  - jitl 6y ago
    Yeah, the the teetering architecture tower is certainly worth an eye-roll given that WebSQL - basically SQLite direct access - was almost accepted as a standard, but was ultimately blocked by Mozilla [https://en.m.wikipedia.org/wiki/Web_SQL_Database https://en.m.wikipedia.org/wiki/Web_SQL_Database] while they pushed this IndexedDB monstrosity. I am not a fan of the IndexedDB API.
    - postalrat 6y ago
      I'll be a big fan of IndexedDB if this ever becomes widely supported https://wicg.github.io/indexed-db-observers/EXPLAINER.html https://wicg.github.io/indexed-db-observers/EXPLAINER.html
    - kbrosnan 6y ago
      It was blocked because the standards bodies require two or more independent implementations. All Mozilla said was that if they were to implement WebSQL they would use SQLite and there would not be two different implementations. Why should have Mozilla had the burden of implanting WebSQL in a different database?
      - sroussey 6y ago
        This is a case for making exceptions.
        orf 6y ago
        No it's not. WebSQL would have become "WebSQLite", quirks and all. That's not good for anyone. Any quirks in SQLite (of which there are _many_) would immediately become unfixable parts of the spec.
- bob1029 6y ago
  What if SSDs just had an alternative interface that talked SQL? Presumably you could bake SQLite into the microcontroller and optimize perfectly for the storage scenario. Having your journaling aware of how long your supercapacitor will last in the worst case could open the door for some wild optimizations.
  - wtallis 6y ago
    There have been some projects to develop SSDs that natively present a key-value storage interface, and standardization of that is pretty far along. So those drives can function as a backend for eg. a RocksDB substitute with a minimal software frontend. But a full SQL database engine on the SSD would fall under the umbrella of computational storage, and so far everyone with the resources to put any of that into production has datasets that don't fit on a single drive. So there's less utility in having the drive speaking proper SQL, but a lot of active research into how to usefully offload some of the DB work onto compute resources that reside on the SSD itself. (Also, microcontroller is a bit odd to use to refer to the main controller chip inside a SSD, especially a high-end enterprise SSD. It gives a completely misleading indication of scale.)
    - bob1029 6y ago
      I do remember reading about Samsung making some of these types of drives. Key-value is a pretty powerful foundation for any sort of database. https://www.snia.org/keyvalue https://www.snia.org/keyvalue https://www.anandtech.com/show/14839/samsung-announces-standardscompliant-keyvalue-ssd-prototype https://www.anandtech.com/show/14839/samsung-announces-stand...
      - slaymaker1907 6y ago
        Key/value is already kind of used by SSDs anyway since they use virtual addressing.
- inbx0 6y ago
  Google has a project called Lovefield [1] that is a SQL-like thing on top of IndexedDB. Not exactly SQLite and I'm not sure what its status is now but might be of interest. [1]: https://github.com/google/lovefield https://github.com/google/lovefield
- antimatter15 6y ago
  I've actually prototyped this recently, but never got around to packaging it up into a library. There are a few neat adjacent things that it can be used for (e.g. opening big SQLite files on disk without reading it all to memory), building something like Datasette (https://github.com/simonw/datasette https://github.com/simonw/datasette) that can run queries on data hosted as static files, or for (as you suggested) using SQLite in a browser with persistence. For that particular use case, there's a bit of complexity related to mutexes and stuff in trying to prevent simultaneous browser tabs doing write operations from corrupting the database.
- saurik 6y ago
  What you really want for this is SQLite4--which added an abstraction layer below the database and above the VFS which could use a key-value store--but sadly I think it ended up being scrapped :(. https://sqlite.org/src4/doc/trunk/www/design.wiki https://sqlite.org/src4/doc/trunk/www/design.wiki
dzonga 6y ago
don't know if I'm remembering correctly, wasn't there an sql interface in browsers once before, then it got gutted ?
- CarelessExpert 6y ago
  Yup: https://en.wikipedia.org/wiki/Web_SQL_Database https://en.wikipedia.org/wiki/Web_SQL_Database
peterburkimsher 6y ago
I'm using SQL.js and it's great! A few weeks ago, I built a program to help find jobs in New Zealand and Australia. It scrapes the Seek website, and allows me to make more complex queries (e.g. does not contain "right to live and work in this location") https://ifeelseek.github.io/ https://ifeelseek.github.io/ Currently I'm running the scraper on a spare laptop manually every day or two. I'd rather set it up to work with Github Actions, but didn't get around to that yet.
- bobochan 6y ago
  Very cool. I use it to teach SQL. I added a CodeMirror region for editing SQL code and have buttons that load a few example databases. The advantage of this is that students do not have to install anything, they can just visit the web site, and they can practice destructive steps like deleting rows or a table, and then just reset everything with a button click. Discovering SQL.js was a huge moment.
bstar77 6y ago
I've had so many problems compiling the sql driver for Electron. I will definitely be giving this a try.
malkia 6y ago
Another user of sqlite in the browser (WebAssembly) is the Perfetto UI (for visualizating tracing data - e.g. the chrome://tracing format + others) - https://github.com/google/perfetto/blob/master/docs/README.md#trace-visualization https://github.com/google/perfetto/blob/master/docs/README.m...
gary_bernhardt 6y ago
We use SQL.js to power the SQL course at https://www.executeprogram.com https://www.executeprogram.com. We've encountered a couple of relatively minor hiccups, but overall it's been great. We've had thousands of users go through that course with no reported problems that are attributable to SQL.js.
singularity2001 6y ago
what happened to the pure web assembly port https://github.com/ryan-codingintrigue/sql-wasm https://github.com/ryan-codingintrigue/sql-wasm ? How does it compare?
- atmin 6y ago
  sql.js does this > sql.js uses emscripten to compile SQLite to webassembly (or to javascript code for compatibility with older browsers) > By default, sql.js uses wasm

183 points by chmaynard 6y ago

jitl 6y ago

SQLite has a system that abstracts various low-level storage operations called VFS - this is the layer that allows SQLite to be portable across different operating systems and environments: https://www.sqlite.org/vfs.html https://www.sqlite.org/vfs.html Recently I’ve wondered how possible it would be to implement a SQLite VFS on top of IndexedDB - and, would such a VFS be competitive in speed to using IndexedDB directly? Or, would it be equivalent to use Emscripten’s existing POSIX-ish filesystem backed by IndexedDB? An IndexedDB VFS would allow sql.js to durably persist data in the browser.

WrtCdEvrydy 6y ago
Wouldn't it be better if you could directly store this data to cookies or localStorage? Edit: The concern is always about allowing it to be easy to delete junk data.
- tracker1 6y ago
  IndexedDB should perform better without locking the browser context.
  - edoceo 6y ago
    Yea, my mobile Chrome (pixel 3) hang on the UI thread a lot, can't even scroll till it yields.
    - tracker1 6y ago
      I get a lot of that on mobile advertising on my phone (Android). It causes jittery behavior trying to just scroll... usually open in brave and it works a little better (opinions on brave itself aside, it just happens to work better for me than other adblock options).
- shakna 6y ago
  The file size limits on cookies and localStorage might make it a bit impractical for the kinds of things you'd want to store in a database. You won't be caching many image files that way, for example.
- jitl 6y ago
  Cookies are not a good spot for bulk data because each request to the server includes all the cookies. You wouldn’t want your whole SQL database included in each fetch() request. localStorage is not durable. At Notion, we observed Chrome localStorage losing writes under load from multiple async writers. IndexedDB is the most durable option, but has quite an annoying and error-prone API, which is why it would be nice to paper over it with SQLite so browser code can use the same schemas and queries as native clients.
  - ravenstine 6y ago
    > localStorage is not durable Side tangent: Why in 2020 do we find the state of localStorage to be acceptable? There's two problems that I see with localStorage: - It's too easy for someone to blow it away and lose data for a web app - Not enough capacity to be useful for a lot of things. (10 megabytes per domain) The design of localStorage is basically flawed, IMO. It should have been two things: volatileStorage and permanentStorage. volatileStorage would basically be exactly what localStorage is today, being very limited and not requiring any permissions. permanentStorage would be like localStorage except it would require explicit permission, allow unlimited storage, and would be more difficult to accidentally delete(separate delete dialog from Clear History). As far as I know, we don't have anything like my proposed permanentStorage outside of web extensions, which leaves localStorage in a weird area where it's only really useful for local app settings, even though such sparse data is easy enough to just store on a server in the first place. It would still be useful for truly offline-first apps, or apps that don't require accounts, but then this space of apps is still crippled by limited storage capacity.
    - nicoburns 6y ago
      indexedDB is the closest thing to permanent storage. This works quite well in Chrome/Firefox. But Apple don't persist it properly (presumably because they don't want webapps competing with their iOS ecosystem).
      - Veen 6y ago
        > presumably because they don't want webapps competing with their iOS ecosystem Or because trackers are using indexeddb to bypass Safari's anti-tracking and privacy measures.
tracker1 6y ago
Similarly, would like to see SQLite work over a WASI's File System interface... best would be the ability to also support multi-process, but I don't know that it would be possible.
RcouF1uZ4gsC 6y ago
> Recently I’ve wondered how possible it would be to implement a SQLite VFS on top of IndexedDB What is interesting about this approach is that at least for Firefox, IndexDB is implemented using SQLite. So ultimately this approach is SQLite running in SQLite with an IndexDB layer in the middle.
- jitl 6y ago
  Yeah, the the teetering architecture tower is certainly worth an eye-roll given that WebSQL - basically SQLite direct access - was almost accepted as a standard, but was ultimately blocked by Mozilla [https://en.m.wikipedia.org/wiki/Web_SQL_Database https://en.m.wikipedia.org/wiki/Web_SQL_Database] while they pushed this IndexedDB monstrosity. I am not a fan of the IndexedDB API.
  - postalrat 6y ago
    I'll be a big fan of IndexedDB if this ever becomes widely supported https://wicg.github.io/indexed-db-observers/EXPLAINER.html https://wicg.github.io/indexed-db-observers/EXPLAINER.html
  - kbrosnan 6y ago
    It was blocked because the standards bodies require two or more independent implementations. All Mozilla said was that if they were to implement WebSQL they would use SQLite and there would not be two different implementations. Why should have Mozilla had the burden of implanting WebSQL in a different database?
    - sroussey 6y ago
      This is a case for making exceptions.
      - orf 6y ago
        No it's not. WebSQL would have become "WebSQLite", quirks and all. That's not good for anyone. Any quirks in SQLite (of which there are _many_) would immediately become unfixable parts of the spec.
bob1029 6y ago
What if SSDs just had an alternative interface that talked SQL? Presumably you could bake SQLite into the microcontroller and optimize perfectly for the storage scenario. Having your journaling aware of how long your supercapacitor will last in the worst case could open the door for some wild optimizations.
- wtallis 6y ago
  There have been some projects to develop SSDs that natively present a key-value storage interface, and standardization of that is pretty far along. So those drives can function as a backend for eg. a RocksDB substitute with a minimal software frontend. But a full SQL database engine on the SSD would fall under the umbrella of computational storage, and so far everyone with the resources to put any of that into production has datasets that don't fit on a single drive. So there's less utility in having the drive speaking proper SQL, but a lot of active research into how to usefully offload some of the DB work onto compute resources that reside on the SSD itself. (Also, microcontroller is a bit odd to use to refer to the main controller chip inside a SSD, especially a high-end enterprise SSD. It gives a completely misleading indication of scale.)
  - bob1029 6y ago
    I do remember reading about Samsung making some of these types of drives. Key-value is a pretty powerful foundation for any sort of database. https://www.snia.org/keyvalue https://www.snia.org/keyvalue https://www.anandtech.com/show/14839/samsung-announces-standardscompliant-keyvalue-ssd-prototype https://www.anandtech.com/show/14839/samsung-announces-stand...
    - slaymaker1907 6y ago
      Key/value is already kind of used by SSDs anyway since they use virtual addressing.
inbx0 6y ago
Google has a project called Lovefield [1] that is a SQL-like thing on top of IndexedDB. Not exactly SQLite and I'm not sure what its status is now but might be of interest. [1]: https://github.com/google/lovefield https://github.com/google/lovefield
antimatter15 6y ago
I've actually prototyped this recently, but never got around to packaging it up into a library. There are a few neat adjacent things that it can be used for (e.g. opening big SQLite files on disk without reading it all to memory), building something like Datasette (https://github.com/simonw/datasette https://github.com/simonw/datasette) that can run queries on data hosted as static files, or for (as you suggested) using SQLite in a browser with persistence. For that particular use case, there's a bit of complexity related to mutexes and stuff in trying to prevent simultaneous browser tabs doing write operations from corrupting the database.
saurik 6y ago
What you really want for this is SQLite4--which added an abstraction layer below the database and above the VFS which could use a key-value store--but sadly I think it ended up being scrapped :(. https://sqlite.org/src4/doc/trunk/www/design.wiki https://sqlite.org/src4/doc/trunk/www/design.wiki

dzonga 6y ago

don't know if I'm remembering correctly, wasn't there an sql interface in browsers once before, then it got gutted ?

CarelessExpert 6y ago
Yup: https://en.wikipedia.org/wiki/Web_SQL_Database https://en.wikipedia.org/wiki/Web_SQL_Database

peterburkimsher 6y ago

I'm using SQL.js and it's great! A few weeks ago, I built a program to help find jobs in New Zealand and Australia. It scrapes the Seek website, and allows me to make more complex queries (e.g. does not contain "right to live and work in this location") https://ifeelseek.github.io/ https://ifeelseek.github.io/ Currently I'm running the scraper on a spare laptop manually every day or two. I'd rather set it up to work with Github Actions, but didn't get around to that yet.

bobochan 6y ago
Very cool. I use it to teach SQL. I added a CodeMirror region for editing SQL code and have buttons that load a few example databases. The advantage of this is that students do not have to install anything, they can just visit the web site, and they can practice destructive steps like deleting rows or a table, and then just reset everything with a button click. Discovering SQL.js was a huge moment.

bstar77 6y ago

I've had so many problems compiling the sql driver for Electron. I will definitely be giving this a try.

malkia 6y ago

Another user of sqlite in the browser (WebAssembly) is the Perfetto UI (for visualizating tracing data - e.g. the chrome://tracing format + others) - https://github.com/google/perfetto/blob/master/docs/README.md#trace-visualization https://github.com/google/perfetto/blob/master/docs/README.m...

gary_bernhardt 6y ago

We use SQL.js to power the SQL course at https://www.executeprogram.com https://www.executeprogram.com. We've encountered a couple of relatively minor hiccups, but overall it's been great. We've had thousands of users go through that course with no reported problems that are attributable to SQL.js.

singularity2001 6y ago

what happened to the pure web assembly port https://github.com/ryan-codingintrigue/sql-wasm https://github.com/ryan-codingintrigue/sql-wasm ? How does it compare?

atmin 6y ago
sql.js does this > sql.js uses emscripten to compile SQLite to webassembly (or to javascript code for compatibility with older browsers) > By default, sql.js uses wasm