7 ms·

SQLite compiled into JavaScript via Emscripten

199 points by sea6ear 11y ago

cheyne 11y ago
Been using this library for a while now. Works great, I based my app https://www.lightroomdashboard.com https://www.lightroomdashboard.com on it
- bambax 11y ago
  Wow, really amazed with your app!! My catalog is 500Mo and was analysed super fast! Love the filters too and the ability to restrict the period (it would be even nicer if we could move some sliders to analyse a specific period). Anyway, this is totally amazing! ;-) --- Now this is really, completely OT, but since you seem to be an expert with Lightroom catalogs, I wonder if you would know the answer to the following problem. I had been using Lightroom 3 for a very long time, with newer cameras the RAWs of which it couldn't read, so I converted the RAW files to DNG using the Adobe utility. This was fine but used double disk space. Now I finally upgraded Lightroom; I still have the original RAW files and was wondering if I could somehow replace the pointers to the DNGs with the original RAWs in the .lrcat catalog file; would that work, or completely break everything? (The point being, to get rid of the DNG files on disk).
kgen 11y ago
SQL.js is a great library -- we use it on http://sqlbolt.com/ http://sqlbolt.com/ to teach SQL right in the browser in a platform agnostic way.
omegote 11y ago
ITT: people advertising their SQL-teaching websites.
SEJeff 11y ago
FYI this is what powers the interactive SQL engine for the SQL classes on the Khan Academy. If you've never seen them, it is really cool. http://www.brianbondy.com/blog/168/sql-on-khan-academy-enabled-by-sqlite-sqljs-asmjs-and-emscripten http://www.brianbondy.com/blog/168/sql-on-khan-academy-enabl... https://www.khanacademy.org/computing/computer-programming/sql https://www.khanacademy.org/computing/computer-programming/s...
myoon 11y ago
Unfortunately, there are a few major downsides to sql.js: 1. Not API compatible with node-sqlite3, so you can't just drop it in and use it with knex or other wrappers. 2. Doesn't support in-place editing of a db. You have to load the entire DB into memory, modify it, and then save it back, making it unsuitable for any concurrent application. I love the idea of the project though, and it would be awesome not to have to deal with compiling sqlite or using node-pregyp to build embedded chromium apps.
- vetrom 11y ago
  Build a VFS layer that uses something like https://code.google.com/p/limbo-machine/wiki/JS https://code.google.com/p/limbo-machine/wiki/JS to talk over a websocket or http message proxy to a file store and you get in-place editing. It will probably be hell for the 'usual' usecase of nonconcurrent in-page use however. Also, LOL locking, which is the real utility of SQLite's VFS layer.
- andrewstuart2 11y ago
  I think the whole point is that it runs in a browser, where you can't install sqlite otherwise. If you're running node, you can almost certainly run a native binary of sqlite.
- bobajeff 11y ago
  >Doesn't support in-place editing of a db. I wonder why that is. Is it just a fuction they simply didn't create a binding for? Or is it some performance concern?
  - AndrewGaspar 11y ago
    Not OP, but I imagine it's a couple things: 1) No clear mapping between file APIs that SQLite uses and browser file APIs. 2) SQLite uses blocking file I/O internally which is generally a big no-no for JavaScript due to it being single threaded.
    - TazeTSchnitzel 11y ago
deleted 11y ago
[deleted]
pygy_ 11y ago
What I would love to see is a SQLite rewrite in Rust. Just enough to support WebSQL and appease Mozilla's concern about the lack of alternative implementations.
- greggman 11y ago
  I thought the issue with SQL as a web standard period was it's under specified. So, rather than put SQL in the browser and then run into all kinds of incompatible edge cases they went with something simpler and easier to define and get right. You can then make your own library (SQL if you prefer) and be more likely to get the exact behavior you want across browsers. https://hacks.mozilla.org/2010/06/beyond-html5-database-apis-and-the-road-to-indexeddb/ https://hacks.mozilla.org/2010/06/beyond-html5-database-apis...
  - TazeTSchnitzel 11y ago
    The problem is that WebSQL was just SQLite, and SQLite only has a single implementation, it's basically implementation-defined.
- saidajigumi 11y ago
  "What I would love to see is a $PROJ rewrite in Rust." has kinda become a mantra of mine, particularly for things like SQLite which are core dependencies of so many other things.
  - coldtea 11y ago
    Yeah, let's rewrite $PROJ in a language few people use, with a lacking toolchain, and that few people will be bothered to use said rewrite, since it will make their life difficult to connect to it (they'd need a Rust toolchain to compile it for one).
    - pcwalton 11y ago
      How is the Rust toolchain lacking? "Few people will use a Rust library because they'd need a Rust compiler" doesn't make sense to me, because odds are good that the Rust compiler can target the platform you used to post this comment.
timeu 11y ago
Would be interesting to see how this benchmarks against the in-memory db of lovefield (https://github.com/google/lovefield https://github.com/google/lovefield)
rhc2104 11y ago
Sql.js is awesome. It powers sqlteaching.com (on the client side), and the whole site took about 50 hours of work between my wife and I. Most of that time was on the curriculum.
- amorphid 11y ago
  http://sqlteaching.com http://sqlteaching.com => cool site! Playing with it now.
  - tomcampbell 11y ago
    Impressive work. Should be a megahit. That 50 hours was very well spent.
- thejosh 11y ago
  Sorry to go offtopic, but what a cool looking site!
- wavefunction 11y ago
  nice site, I'm going to share it with my friends and family who might be interested in learning about databases but not technically savvy enough to setup and admin an entire server by themselves...
- smcl 11y ago
  I circulated this through my workplace (cca 900 people). We've got a lot of mostly-non-tech people (testers and analysts) who have some interaction with our product via SQL where reports or UI aren't available. They usually create simple SELECT queries, or re-use more complex prewritten ones. I think many people will use this to get a better understanding of how everything they're using fits together. I've no doubt it will be a hit, thank you very much for taking the time to create this.
seanalltogether 11y ago
I wonder how well this would work for shipping game assets in the browser. One of the nice things I miss about flash was having a single compiled object to store on the server and distribute to end users which contained all the assets and sounds needed for the game. Stuffing all your assets into a sqlite database and sending that over the wire would keep everything nice and tidy.
- azakai 11y ago
  SQLite does mean you'll have overhead in retrieving that data, though. If you want to be able to do SQL queries on it, it's worth it, but otherwise it's probably not. If you just want a single file, you can bundle all the assets into one and emit some metadata so you know where each file starts and ends. Emscripten's file packager does this, for example.
inglor 11y ago
I wrote an SQL injection tutorial using this a while ago: benjamingr.github.io/PizzaHack
SunboX 11y ago
Did someone try to compile Zebra Crossing (ZXing) to JavaScript using Emscripten? I searched a lot, but sadly didn't find anyone who succeeded. And I don't have enough knowledge about C++ to try it myself.
- nly 11y ago
  The C++ version of ZXing is no longer maintained. Unless you really need everything clientside, I'd just go wrap the Java library in a RPC like Thrift and bolt it in to your backend.
mizchief2 11y ago
I'm curious as to how effective this could be used in an available-offline kind of application. If you could store the DB in local storage then use behind the scenes syncs to keep your local DB up to date as your internet connection goes up and down. Although in most cases having a JSON store would probably be easier for that role, but could see this being useful if your app needs to allow for raw sql queries offline.
PSeitz 11y ago
I would be very interested in benchmarks comparing it to the native version

199 points by sea6ear 11y ago

Been using this library for a while now. Works great, I based my app https://www.lightroomdashboard.com https://www.lightroomdashboard.com on it

bambax 11y ago
Wow, really amazed with your app!! My catalog is 500Mo and was analysed super fast! Love the filters too and the ability to restrict the period (it would be even nicer if we could move some sliders to analyse a specific period). Anyway, this is totally amazing! ;-) --- Now this is really, completely OT, but since you seem to be an expert with Lightroom catalogs, I wonder if you would know the answer to the following problem. I had been using Lightroom 3 for a very long time, with newer cameras the RAWs of which it couldn't read, so I converted the RAW files to DNG using the Adobe utility. This was fine but used double disk space. Now I finally upgraded Lightroom; I still have the original RAW files and was wondering if I could somehow replace the pointers to the DNGs with the original RAWs in the .lrcat catalog file; would that work, or completely break everything? (The point being, to get rid of the DNG files on disk).

kgen 11y ago

SQL.js is a great library -- we use it on http://sqlbolt.com/ http://sqlbolt.com/ to teach SQL right in the browser in a platform agnostic way.

omegote 11y ago

ITT: people advertising their SQL-teaching websites.

SEJeff 11y ago

FYI this is what powers the interactive SQL engine for the SQL classes on the Khan Academy. If you've never seen them, it is really cool. http://www.brianbondy.com/blog/168/sql-on-khan-academy-enabled-by-sqlite-sqljs-asmjs-and-emscripten http://www.brianbondy.com/blog/168/sql-on-khan-academy-enabl... https://www.khanacademy.org/computing/computer-programming/sql https://www.khanacademy.org/computing/computer-programming/s...

myoon 11y ago

Unfortunately, there are a few major downsides to sql.js: 1. Not API compatible with node-sqlite3, so you can't just drop it in and use it with knex or other wrappers. 2. Doesn't support in-place editing of a db. You have to load the entire DB into memory, modify it, and then save it back, making it unsuitable for any concurrent application. I love the idea of the project though, and it would be awesome not to have to deal with compiling sqlite or using node-pregyp to build embedded chromium apps.

vetrom 11y ago
Build a VFS layer that uses something like https://code.google.com/p/limbo-machine/wiki/JS https://code.google.com/p/limbo-machine/wiki/JS to talk over a websocket or http message proxy to a file store and you get in-place editing. It will probably be hell for the 'usual' usecase of nonconcurrent in-page use however. Also, LOL locking, which is the real utility of SQLite's VFS layer.
andrewstuart2 11y ago
I think the whole point is that it runs in a browser, where you can't install sqlite otherwise. If you're running node, you can almost certainly run a native binary of sqlite.
bobajeff 11y ago
>Doesn't support in-place editing of a db. I wonder why that is. Is it just a fuction they simply didn't create a binding for? Or is it some performance concern?
- AndrewGaspar 11y ago
  Not OP, but I imagine it's a couple things: 1) No clear mapping between file APIs that SQLite uses and browser file APIs. 2) SQLite uses blocking file I/O internally which is generally a big no-no for JavaScript due to it being single threaded.
  - TazeTSchnitzel 11y ago

deleted 11y ago

[deleted]

pygy_ 11y ago

What I would love to see is a SQLite rewrite in Rust. Just enough to support WebSQL and appease Mozilla's concern about the lack of alternative implementations.

greggman 11y ago
I thought the issue with SQL as a web standard period was it's under specified. So, rather than put SQL in the browser and then run into all kinds of incompatible edge cases they went with something simpler and easier to define and get right. You can then make your own library (SQL if you prefer) and be more likely to get the exact behavior you want across browsers. https://hacks.mozilla.org/2010/06/beyond-html5-database-apis-and-the-road-to-indexeddb/ https://hacks.mozilla.org/2010/06/beyond-html5-database-apis...
- TazeTSchnitzel 11y ago
  The problem is that WebSQL was just SQLite, and SQLite only has a single implementation, it's basically implementation-defined.
saidajigumi 11y ago
"What I would love to see is a $PROJ rewrite in Rust." has kinda become a mantra of mine, particularly for things like SQLite which are core dependencies of so many other things.
- coldtea 11y ago
  Yeah, let's rewrite $PROJ in a language few people use, with a lacking toolchain, and that few people will be bothered to use said rewrite, since it will make their life difficult to connect to it (they'd need a Rust toolchain to compile it for one).
  - pcwalton 11y ago
    How is the Rust toolchain lacking? "Few people will use a Rust library because they'd need a Rust compiler" doesn't make sense to me, because odds are good that the Rust compiler can target the platform you used to post this comment.

timeu 11y ago

Would be interesting to see how this benchmarks against the in-memory db of lovefield (https://github.com/google/lovefield https://github.com/google/lovefield)

rhc2104 11y ago

Sql.js is awesome. It powers sqlteaching.com (on the client side), and the whole site took about 50 hours of work between my wife and I. Most of that time was on the curriculum.

amorphid 11y ago
http://sqlteaching.com http://sqlteaching.com => cool site! Playing with it now.
- tomcampbell 11y ago
  Impressive work. Should be a megahit. That 50 hours was very well spent.
thejosh 11y ago
Sorry to go offtopic, but what a cool looking site!
wavefunction 11y ago
nice site, I'm going to share it with my friends and family who might be interested in learning about databases but not technically savvy enough to setup and admin an entire server by themselves...
smcl 11y ago
I circulated this through my workplace (cca 900 people). We've got a lot of mostly-non-tech people (testers and analysts) who have some interaction with our product via SQL where reports or UI aren't available. They usually create simple SELECT queries, or re-use more complex prewritten ones. I think many people will use this to get a better understanding of how everything they're using fits together. I've no doubt it will be a hit, thank you very much for taking the time to create this.

seanalltogether 11y ago

I wonder how well this would work for shipping game assets in the browser. One of the nice things I miss about flash was having a single compiled object to store on the server and distribute to end users which contained all the assets and sounds needed for the game. Stuffing all your assets into a sqlite database and sending that over the wire would keep everything nice and tidy.

azakai 11y ago
SQLite does mean you'll have overhead in retrieving that data, though. If you want to be able to do SQL queries on it, it's worth it, but otherwise it's probably not. If you just want a single file, you can bundle all the assets into one and emit some metadata so you know where each file starts and ends. Emscripten's file packager does this, for example.

inglor 11y ago

I wrote an SQL injection tutorial using this a while ago: benjamingr.github.io/PizzaHack

SunboX 11y ago

Did someone try to compile Zebra Crossing (ZXing) to JavaScript using Emscripten? I searched a lot, but sadly didn't find anyone who succeeded. And I don't have enough knowledge about C++ to try it myself.

nly 11y ago
The C++ version of ZXing is no longer maintained. Unless you really need everything clientside, I'd just go wrap the Java library in a RPC like Thrift and bolt it in to your backend.

mizchief2 11y ago

I'm curious as to how effective this could be used in an available-offline kind of application. If you could store the DB in local storage then use behind the scenes syncs to keep your local DB up to date as your internet connection goes up and down. Although in most cases having a JSON store would probably be easier for that role, but could see this being useful if your app needs to allow for raw sql queries offline.

PSeitz 11y ago

I would be very interested in benchmarks comparing it to the native version