7 ms·

AlaSQL.js – JavaScript SQL Database for Browser and Node.js

120 points by truth_seeker 8y ago

untog 8y ago
This is very cool. But Github tells me that the minified version is still over 400KB. I really wish browsers hasn't decided against WebSQL and replaced it with IndexedDB.
- daleharvey 8y ago
  WebSQL literally just had a remote code execution bug showing exactly why it was right that browsers decided against WebSQL https://blade.tencent.com/magellan/index_en.html https://blade.tencent.com/magellan/index_en.html
  - Klathmon 8y ago
    Specifically SQLite had a remote code execution bug, and since all browsers that shipped WebSQL used SQLite as the backing engine, they all inherited the vulnerability and exposed it to all web-pages out there.
    - detaro 8y ago
      And WebSQL was more or less specified to be backed by SQLite, given its "specification" for the SQL used was "whatever SQLite x.y.z implements" (which was one of the reasons it was rejected)
  - tzs 8y ago
    Uhm...all major browsers have a long history of remote code execution bugs, and almost certainly still have remote code execution bugs that aren't publicly known yet. Heck, even if we just consider databases, the IndexedDB implementations in both Firefox and Chrome have had remote memory corruption bugs of the type that are often exploitable for remote code execution, although I don't know if anyone ever actually built such an exploit.
    - daleharvey 8y ago
      They all have different remote code execution bugs so those bugs can and should be fixed. The reason to not standardise on a specific single implementation is to make sure those bugs do not become part of the web platform
- Klathmon 8y ago
  As much as I also wish we could have a full SQL engine in browsers, it's just too much work. The web as a platform thrives on multiple implementations, even going so far as to require it for new standards in many areas. All implementations of WebSQL were basically a lightly wrapped SQLite, and that meant that a bug in SQLite was a bug in all browsers, and design decisions in SQLite would impact the entire web ecosystem, and that was consitered by many involved to be a bad tradeoff. IndexedDB is much simpler to spec out, much simpler to build multiple implementations of, and much simpler to maintain and improve over time. In a way it's punting the problem down to userland, but userland doesn't need to abide by the same rules and restrictions as a blessed standard does, so it makes sense to push more complexity down to that level. 400kb is a lot to pay, but it just goes to show how complex an SQL engine really is. Paying for that in the form of a JS bundle download is one thing, but having that complexity in every browser means a larger attack surface, larger maintenance surface, and at the end of the day still doesn't completely remove the need for things like AlaSQL.js (which may target a different implementation than WebSQL)
  - vandorjw 8y ago
    I think it is too bad. Almost all browsers except for Firefox use Chromium (blink) as their browser engine. That means that a bug in Chromium is a bug in almost all browsers, and design decisions in Chromium impact the entire web ecosystem. I agree with your above comment, and we can argue that working on a single web-engine might lead to less duplicated work for Front-End devs, and less bugs in the web overall. Given that sqlite is the most widely deployed DB already, making it part of web browsers doesn't seem like a huge deal to me. https://www.sqlite.org/mostdeployed.html https://www.sqlite.org/mostdeployed.html
    - Klathmon 8y ago
      But the bug aspect is only one part of it, the other is that it ties the future goals of the projects together. What happens when the web wants to implement some special optimization or security addition but SQLite doesn't want or support being used that way? What happens when SQLite makes a breaking change in a future version that might make sense for them, but would break a large number of web apps? Once something is in the web standards, it's VERY hard to drop support for it. SQLite might make sense now, but what about in a decade? No matter how you slice it, WebSQL has the ability to become a massive maintence liability, one that all major browsers agreed was too significant to codify into a standard.
- mzarate06 8y ago
  Agree. And while IndexedDB deserves credit, I cringe a bit every time I see a SQL implementation in JS. There's clearly a SQL need for developers, so I still feel we missed an opportunity w/WebSQL. To supplement, see my 2015 comment in response to Lovefield: https://news.ycombinator.com/item?id=10197672 https://news.ycombinator.com/item?id=10197672
  - virtualwhys 8y ago
    > And as a result, we're having to implement our own relational databases. In JavaScript. 4-5 years later. Now nearly 10 years later. The notion that a WebSQL-like library would be created on top of IndexedDB to replace the deprecated API has proven to be utterly groundless. It's hopeless, how on earth will a bloated JS library compete with the beast that backs WebSQL? Sqlite, a native C application, just blows everything else out the water. Table joins, window functions? Can't even think the thought with anything built on IndexedDB. Worse, in the same way that many languages these days compile to javascript, so too do server-side DSLs generate SQL (see Slick, Quill, Esqueleto, LINQ to SQL, etc.). Wouldn't it be nice to share the same model and sql statements on client and server? You know, offload complex aggregation tasks to a native C application designed specifically for this task, instead of manually doing it all by hand with reams of error prone boilerplate in javascript. Interestingly Chrome and Safari, the browsers that dominate the market, after all these years still ship with WebSQL. Perhaps there's still hope...
    - tracker1 8y ago
      > Interestingly Chrome and Safari, the browsers that dominate the market, after all these years still ship with WebSQL. Perhaps there's still hope... Maybe... I'll have to start experimenting again... when I first looked at it, IE was still too many people to ignore.
    - WorldMaker 8y ago
      > The notion that a WebSQL-like library would be created on top of IndexedDB to replace the deprecated API has proven to be utterly groundless. Uh, we came here to discuss AlaSQL.js which does implement a decent alternative to the WebSQL standard and does store to IndexedDB, when asked. It's probably a better alternative given its additions of JS functions and JSON operators. In the footnotes you can even find a WebSQL shim that recreates the old WebSQL API on top of AlaSQL (though it recommends just using the AlaSQL API if you can). I don't know what the benchmark characteristics of it are, as I haven't yet tried to use this library, but I doubt it is "hopeless". On the other side, SQLite itself directly runs very well with Emscripten (sql.js), and I've heard Web Assembly builds are getting really good, if you do need the "performance" of "real" SQLite. All of which seems to be further proof that SQLite is much better in "user land" than in the Browser's security footprint.
- z3t4 8y ago
  400KB is not much for a full database implementation, considering Google.com need's double as much to show a logo and a search field.
  - chiefalchemist 8y ago
    Yes. But that 400k is in addition to any other assets, payload, etc. That is the total page weight is higher. While Google tops out at a lighter side 400k.
- garyclarke27 8y ago
  Agreed - dropping WebSQL for much weaker inferior IndexDB made no sense to me. Instead of going backwards, there should be a choice for persistent data - Text cookies or Simple KV Doc store or Relational db. I don't buy the dogmatic arguments against SQLite which is Open Source software of the highest quality - even if a major bug is discovered, there is always the option to disable it, anyway surely surely the likes of Google would have every incentive to help fix it quickly.
- netghost 8y ago
  I suspect that a smaller builds could be generated. There's a lot of functionality like reading from a sqlite db or Excel file that you could probably drop to improve the package size depending on use case.
- da_chicken 8y ago
  Honestly, I'm more surprised that a WebAssembly version of SQLite isn't more commonly used.
  - untog 8y ago
    It would be huge, wouldn't it?
    - tracker1 8y ago
      Looks like it's around 2.6mb for the not gzipped worker compiled to JS... so would presume it'd be a bit less for wasm, then gzipped to around 800k. https://hackernoon.com/execute-millions-of-sql-statements-in-milliseconds-in-the-browser-with-webassembly-and-web-workers-3e0b25c3f1a6 https://hackernoon.com/execute-millions-of-sql-statements-in...
      - TheTaytay 8y ago
        I'm actually in the midst of converting SQL.js to use WASM primarily instead of asm.js (https://github.com/kripken/sql.js/pull/255 https://github.com/kripken/sql.js/pull/255). By disabling some less-known features and optimizing for size, the WASM build is currently 631KB. When compressed (zip), they are a total of 270KB. https://github.com/Taytay/sql.js/pull/1 https://github.com/Taytay/sql.js/pull/1
        tracker1 8y ago
        very cool.. much smaller than I'd expected... still as big as the entire application I'm currently working on ;-) I still wish WebSQL just had broader support in general.
- TAForObvReasons 8y ago
  > browsers The blame lies entirely with Mozilla/Firefox: https://nolanlawson.com/2014/04/26/web-sql-database-in-memoriam/ https://nolanlawson.com/2014/04/26/web-sql-database-in-memor...
  - WorldMaker 8y ago
    Microsoft was also a dissenting voice against WebSQL. It was a team effort (because this is about standards), not just Mozilla's "fault". SQLite is not a standard, and it would have been an IE6-level mistake to bake SQLite quirks and bugs into the web standard for the rest of time. (It doesn't matter that SQLite is open source, it's still a central source of failure that the web doesn't necessarily need in every browser for the rest of time.)
  - detaro 8y ago
    Just as "entirely" as the blame lies with those suggesting WebSQL not proposing something that meets the well-established and agreed on expectations for a web specification, and not fixing that issue when it was pointed out.
dukoid 8y ago
Persistence seems to be via files... So I suppose just as for Lovefield, all the data needs needs to be loaded before use? This also seems to make sharing between tabs tricky?
Keverw 8y ago
I am researching for a project idea I have and was actually looking up in memory SQL databases for node last night to store and sort data instead of creating a object and custom functions to filter it. I came across https://nanosql.io/ https://nanosql.io/ also which I am leaning more towards. Not to the point yet to play with either yet but it seemed like nanosql had better documentation and also less issues opened on Github right now. Kinda odd how when you look up things or think of things you see signs of them... Just wow'ed to see something I was looking at last night be #4 on the HN homepage... I know people talk about positivity and the law of attraction but starting to believe more and more in it where thoughts and focus becomes reality.
- estsauver 8y ago
  If it's on the backend, why not sqlite? It's pretty great!
  - Keverw 8y ago
    Just temporary data. However SQlite does have a In-Memory Database support, almost forgot about that. But probably SQlite would be a bit bloated to use for this part anyways. This being native to JS and even typescript support just seem nice... However SQlite is more battle tested, was watching a tech talk about it once and they even use it for airplane computers!
  - chatmasta 8y ago
    SQLite can also be compiled to wasm which has some cool use cases for syncing with backend data stores.
Elect2 8y ago
Maybe better for hybrid app dev.
babaganoosh89 8y ago
Does this use web workers to access the db? Otherwise I'd worry about queries blocking the main thread, especially as the database gets larger.
RobinL 8y ago
Interesting that Mike Bostock posted this today: https://beta.observablehq.com/@mbostock/sqlite https://beta.observablehq.com/@mbostock/sqlite I'm not sure how the two compare...

120 points by truth_seeker 8y ago

This is very cool. But Github tells me that the minified version is still over 400KB. I really wish browsers hasn't decided against WebSQL and replaced it with IndexedDB.

daleharvey 8y ago
WebSQL literally just had a remote code execution bug showing exactly why it was right that browsers decided against WebSQL https://blade.tencent.com/magellan/index_en.html https://blade.tencent.com/magellan/index_en.html
- Klathmon 8y ago
  Specifically SQLite had a remote code execution bug, and since all browsers that shipped WebSQL used SQLite as the backing engine, they all inherited the vulnerability and exposed it to all web-pages out there.
  - detaro 8y ago
    And WebSQL was more or less specified to be backed by SQLite, given its "specification" for the SQL used was "whatever SQLite x.y.z implements" (which was one of the reasons it was rejected)
- tzs 8y ago
  Uhm...all major browsers have a long history of remote code execution bugs, and almost certainly still have remote code execution bugs that aren't publicly known yet. Heck, even if we just consider databases, the IndexedDB implementations in both Firefox and Chrome have had remote memory corruption bugs of the type that are often exploitable for remote code execution, although I don't know if anyone ever actually built such an exploit.
  - daleharvey 8y ago
    They all have different remote code execution bugs so those bugs can and should be fixed. The reason to not standardise on a specific single implementation is to make sure those bugs do not become part of the web platform
Klathmon 8y ago
As much as I also wish we could have a full SQL engine in browsers, it's just too much work. The web as a platform thrives on multiple implementations, even going so far as to require it for new standards in many areas. All implementations of WebSQL were basically a lightly wrapped SQLite, and that meant that a bug in SQLite was a bug in all browsers, and design decisions in SQLite would impact the entire web ecosystem, and that was consitered by many involved to be a bad tradeoff. IndexedDB is much simpler to spec out, much simpler to build multiple implementations of, and much simpler to maintain and improve over time. In a way it's punting the problem down to userland, but userland doesn't need to abide by the same rules and restrictions as a blessed standard does, so it makes sense to push more complexity down to that level. 400kb is a lot to pay, but it just goes to show how complex an SQL engine really is. Paying for that in the form of a JS bundle download is one thing, but having that complexity in every browser means a larger attack surface, larger maintenance surface, and at the end of the day still doesn't completely remove the need for things like AlaSQL.js (which may target a different implementation than WebSQL)
- vandorjw 8y ago
  I think it is too bad. Almost all browsers except for Firefox use Chromium (blink) as their browser engine. That means that a bug in Chromium is a bug in almost all browsers, and design decisions in Chromium impact the entire web ecosystem. I agree with your above comment, and we can argue that working on a single web-engine might lead to less duplicated work for Front-End devs, and less bugs in the web overall. Given that sqlite is the most widely deployed DB already, making it part of web browsers doesn't seem like a huge deal to me. https://www.sqlite.org/mostdeployed.html https://www.sqlite.org/mostdeployed.html
  - Klathmon 8y ago
    But the bug aspect is only one part of it, the other is that it ties the future goals of the projects together. What happens when the web wants to implement some special optimization or security addition but SQLite doesn't want or support being used that way? What happens when SQLite makes a breaking change in a future version that might make sense for them, but would break a large number of web apps? Once something is in the web standards, it's VERY hard to drop support for it. SQLite might make sense now, but what about in a decade? No matter how you slice it, WebSQL has the ability to become a massive maintence liability, one that all major browsers agreed was too significant to codify into a standard.
mzarate06 8y ago
Agree. And while IndexedDB deserves credit, I cringe a bit every time I see a SQL implementation in JS. There's clearly a SQL need for developers, so I still feel we missed an opportunity w/WebSQL. To supplement, see my 2015 comment in response to Lovefield: https://news.ycombinator.com/item?id=10197672 https://news.ycombinator.com/item?id=10197672
- virtualwhys 8y ago
  > And as a result, we're having to implement our own relational databases. In JavaScript. 4-5 years later. Now nearly 10 years later. The notion that a WebSQL-like library would be created on top of IndexedDB to replace the deprecated API has proven to be utterly groundless. It's hopeless, how on earth will a bloated JS library compete with the beast that backs WebSQL? Sqlite, a native C application, just blows everything else out the water. Table joins, window functions? Can't even think the thought with anything built on IndexedDB. Worse, in the same way that many languages these days compile to javascript, so too do server-side DSLs generate SQL (see Slick, Quill, Esqueleto, LINQ to SQL, etc.). Wouldn't it be nice to share the same model and sql statements on client and server? You know, offload complex aggregation tasks to a native C application designed specifically for this task, instead of manually doing it all by hand with reams of error prone boilerplate in javascript. Interestingly Chrome and Safari, the browsers that dominate the market, after all these years still ship with WebSQL. Perhaps there's still hope...
  - tracker1 8y ago
    > Interestingly Chrome and Safari, the browsers that dominate the market, after all these years still ship with WebSQL. Perhaps there's still hope... Maybe... I'll have to start experimenting again... when I first looked at it, IE was still too many people to ignore.
  - WorldMaker 8y ago
    > The notion that a WebSQL-like library would be created on top of IndexedDB to replace the deprecated API has proven to be utterly groundless. Uh, we came here to discuss AlaSQL.js which does implement a decent alternative to the WebSQL standard and does store to IndexedDB, when asked. It's probably a better alternative given its additions of JS functions and JSON operators. In the footnotes you can even find a WebSQL shim that recreates the old WebSQL API on top of AlaSQL (though it recommends just using the AlaSQL API if you can). I don't know what the benchmark characteristics of it are, as I haven't yet tried to use this library, but I doubt it is "hopeless". On the other side, SQLite itself directly runs very well with Emscripten (sql.js), and I've heard Web Assembly builds are getting really good, if you do need the "performance" of "real" SQLite. All of which seems to be further proof that SQLite is much better in "user land" than in the Browser's security footprint.
z3t4 8y ago
400KB is not much for a full database implementation, considering Google.com need's double as much to show a logo and a search field.
- chiefalchemist 8y ago
  Yes. But that 400k is in addition to any other assets, payload, etc. That is the total page weight is higher. While Google tops out at a lighter side 400k.
garyclarke27 8y ago
Agreed - dropping WebSQL for much weaker inferior IndexDB made no sense to me. Instead of going backwards, there should be a choice for persistent data - Text cookies or Simple KV Doc store or Relational db. I don't buy the dogmatic arguments against SQLite which is Open Source software of the highest quality - even if a major bug is discovered, there is always the option to disable it, anyway surely surely the likes of Google would have every incentive to help fix it quickly.
netghost 8y ago
I suspect that a smaller builds could be generated. There's a lot of functionality like reading from a sqlite db or Excel file that you could probably drop to improve the package size depending on use case.
da_chicken 8y ago
Honestly, I'm more surprised that a WebAssembly version of SQLite isn't more commonly used.
- untog 8y ago
  It would be huge, wouldn't it?
  - tracker1 8y ago
    Looks like it's around 2.6mb for the not gzipped worker compiled to JS... so would presume it'd be a bit less for wasm, then gzipped to around 800k. https://hackernoon.com/execute-millions-of-sql-statements-in-milliseconds-in-the-browser-with-webassembly-and-web-workers-3e0b25c3f1a6 https://hackernoon.com/execute-millions-of-sql-statements-in...
    - TheTaytay 8y ago
      I'm actually in the midst of converting SQL.js to use WASM primarily instead of asm.js (https://github.com/kripken/sql.js/pull/255 https://github.com/kripken/sql.js/pull/255). By disabling some less-known features and optimizing for size, the WASM build is currently 631KB. When compressed (zip), they are a total of 270KB. https://github.com/Taytay/sql.js/pull/1 https://github.com/Taytay/sql.js/pull/1
      - tracker1 8y ago
        very cool.. much smaller than I'd expected... still as big as the entire application I'm currently working on ;-) I still wish WebSQL just had broader support in general.
TAForObvReasons 8y ago
> browsers The blame lies entirely with Mozilla/Firefox: https://nolanlawson.com/2014/04/26/web-sql-database-in-memoriam/ https://nolanlawson.com/2014/04/26/web-sql-database-in-memor...
- WorldMaker 8y ago
  Microsoft was also a dissenting voice against WebSQL. It was a team effort (because this is about standards), not just Mozilla's "fault". SQLite is not a standard, and it would have been an IE6-level mistake to bake SQLite quirks and bugs into the web standard for the rest of time. (It doesn't matter that SQLite is open source, it's still a central source of failure that the web doesn't necessarily need in every browser for the rest of time.)
- detaro 8y ago
  Just as "entirely" as the blame lies with those suggesting WebSQL not proposing something that meets the well-established and agreed on expectations for a web specification, and not fixing that issue when it was pointed out.

dukoid 8y ago

Persistence seems to be via files... So I suppose just as for Lovefield, all the data needs needs to be loaded before use? This also seems to make sharing between tabs tricky?

Keverw 8y ago

I am researching for a project idea I have and was actually looking up in memory SQL databases for node last night to store and sort data instead of creating a object and custom functions to filter it. I came across https://nanosql.io/ https://nanosql.io/ also which I am leaning more towards. Not to the point yet to play with either yet but it seemed like nanosql had better documentation and also less issues opened on Github right now. Kinda odd how when you look up things or think of things you see signs of them... Just wow'ed to see something I was looking at last night be #4 on the HN homepage... I know people talk about positivity and the law of attraction but starting to believe more and more in it where thoughts and focus becomes reality.

estsauver 8y ago
If it's on the backend, why not sqlite? It's pretty great!
- Keverw 8y ago
  Just temporary data. However SQlite does have a In-Memory Database support, almost forgot about that. But probably SQlite would be a bit bloated to use for this part anyways. This being native to JS and even typescript support just seem nice... However SQlite is more battle tested, was watching a tech talk about it once and they even use it for airplane computers!
- chatmasta 8y ago
  SQLite can also be compiled to wasm which has some cool use cases for syncing with backend data stores.

Elect2 8y ago

Maybe better for hybrid app dev.

babaganoosh89 8y ago

Does this use web workers to access the db? Otherwise I'd worry about queries blocking the main thread, especially as the database gets larger.

RobinL 8y ago

Interesting that Mike Bostock posted this today: https://beta.observablehq.com/@mbostock/sqlite https://beta.observablehq.com/@mbostock/sqlite I'm not sure how the two compare...