SQLite compiled into JavaScript via Emscripten | Hacker News Reader

SQLite compiled into JavaScript via Emscripten

github.com

199 pointssea6ear55 comments

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.

http://sqlteaching.com => cool site! Playing with it now.

Impressive work. Should be a megahit. That 50 hours was very well spent.

Sorry to go offtopic, but what a cool looking site!

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...

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.

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-enabl...

https://www.khanacademy.org/computing/computer-programming/s...

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

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.

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.

>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?

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.

SQLite is proably saving files in emscripten's virtual filesystem (emscripten's implementation of fopen() etc. by default makes them work on memory), and the sql.js API just reads and writes the file there.

there is an VFS layer in sqlite, I haven't used it, but it seems like you can override block I/O operations.

All good points, but I think the primary use case (in a browser) doesn't experience these downsides nearly as much (JS is single threaded in the browser so concurrent access is not a big deal). It would be nice to use knex with it though..

Build a VFS layer that uses something like 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.

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.

"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.

The problem is that it isn't so much about an interface-compatible version of $project in Rust - the value of things like SQLite is in the huge amount of testing and perf work that's been done over the years. You could do a toy reimplementation, but the value wouldn't be there.

Sure the value would be there! After a huge amount of testing and perf work done over the course of 15 years...

Mozilla has a license for the full test suite (which AFAIK uses the public interface), and Rust interoperates nicely with C...

The perf works would remain to be done, though, after the whole thing is made to work.

That's the exact line I was thinking of, It'd be curious to see how reimplementing an existing C-api library looks in Rust.

One of the huge benefits is the lifetime analysis which you lose soon as you cross the FFI boundary.

AFAIK the SQLite API is rather tight. You mostly pass in and get back strings (SQL and rows of tuples, respectively).

Luckily a huge amount of that testing work has been captured in a comprehensive test suite for SQLite that any alternative implementation could easily use to ensure compatibility.

Last time I checked, the majority of the test suite was proprietary.

You can buy a license for the test suite and run it against your own implementation.

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).

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.

SQLite is rather heavily used in the embedded world, which uses a lot of architectures that llvm hasn't been ported to. Hitachi's SH, NEC's v850, etc are all targets people pay the sqlite maintainers for test suites, porting, etc. A rewrite in rust would eliminate a lot of income the developers of sqlite count on, because the toolchain just doesn't have the breadth of targets that c does.

"Nobody should bother developing an alternative to SQLite because 1% of SQLite's users won't be able to run it" isn't a valid argument against developing an alternative any more than "Microsoft shouldn't bother developing Windows 95 because some users still have a 286" would have been in 1993.

Nobody is saying that those who need SQLite as it exists today shouldn't continue to fund its development. It's just that writing off the development of alternatives as unrealistic because they'll only be runnable by 99% of SQLite's install base as opposed to 100% of it is silly.

If that 1% of people are the ones paying money for it, why shouldn't the developers focus on their needs?

Kind of ridiculous that someone downvoted this. Aren't downvotes supposed to suggest that the comment detracted from the conversation? In what way am I detracting from the discussion? Disagreeing isn't a reason to downvote.

Disagreeing isn't a reason to downvote.

Why not? There's no rule on HN against it, and the founder of HN thinks it's ok, since upvotes are used to indicate agreement.

Ignore individual downvotes. They're often mistakes, or people in bad moods, or etc. They usually get corrected, although as you've seen not enough people provide corrective upvotes.

Downvote for disagreement has a bit of history on HN.

PG has said gently conflicting things:

Downvoting to disagree is a problem that needs action: https://news.ycombinator.com/item?id=1057347

Downvoting to disagree is fine if people are also upvoting to agree: http://news.ycombinator.com/item?id=117171

Worry about excessive downvotes is a long term problem on HN. Here's a post from seven years ago: https://news.ycombinator.com/item?id=214398

I'm not implying anything about what SQLite's development priorities should be. I'm arguing against the idea that developing a SQLite alternative in Rust is infeasible for portability reasons.

>"Nobody should bother developing an alternative to SQLite because 1% of SQLite's users won't be able to run it"

...and a 80% or more of the rest 99% wont bother to run it, because all it buys them is hassle, as SQLite is present everywhere, well maintained by the guy who wrote it, with lots of books on it, interfaces for all their favorites languages and environments, tested thoroughly by its dev, and battle proven.

Of course somebody can write it in Rust, and do whatever they like. It might even catch on eventually.

But merely "it will be more secure because of advanced compiler technology" is not that enticing an argument -- especially if people don't have many security issues with it in the first place.

Now if it was a Rust-based Sendmail...

At this point you're arguing against a lot of things I've never said. Obviously an alternative to SQLite will have to be compelling enough to motivate its use in ways that likely exceed just memory safety concerns. That is true of any proposed replacement for any piece of software. The point is simply that, if you want to write a SQLite alternative, Rust is a reasonable language to write it in.

>At this point you're arguing against a lot of things I've never said.

Yeah, didn't say you said those.

But since I started this subthread, I'm merely expanding and explaining further the thigs I said initially.

>The point is simply that, if you want to write a SQLite alternative, Rust is a reasonable language to write it in.

Oh, on that I agree.

I was mainly questioning the need to rewrite in the first place (and especially for this particular software, being an embedded db et al), not whether Rust as a language is a good choice to do so.

That said, the less mature ecosystem/toolchain etc, is still an issue for some projects that would still benefit from Rust -- e.g. for somebody used to work with VS C++, etc.

For one, you're still making breaking changes to it. Then there's issues like a full blown debugger, the compiler is slow, etc. Maturity. I'm not even going into IDE terittory which Windows guys will want.

SQLite is used in tons of software. Why add a Rust compiler dependency on its build stage, if SQLite already serves them well and they already work in C/C++ land for the rest of the code of their project?

Rust isn't making breaking changes anymore. I think people are getting hung up on "theoretically breaking" changes that don't break anybody in practice and are equivalent to the "theoretically breaking" changes that C and C++ (and every other actively developed language) continue to make all the time.

> SQLite is used in tons of software. Why add a Rust compiler dependency on its build stage, if SQLite already serves them well and they already work in C/C++ land for the rest of the code of their project?

Because a replacement might have more features/better performance/improved security? These are the same reasons you might look for alternatives for any piece of software!

>Rust isn't making breaking changes anymore. I think people are getting hung up on "theoretically breaking" changes that don't break anybody in practice and are equivalent to the "theoretically breaking" changes that C and C++ (and every other actively developed language) continue to make all the time.

That's good to know. In the language OK, not even in the standard lib?

>Because a replacement might have more features/better performance/improved security? These are the same reasons you might look for alternatives for any piece of software!

Most of that (more features, better performance) are orthogonal to Rust. Features obviously (if anything a port will probably have even LESS features initially, as Sqlite got most of its features piecemeal too).

And how is better speed assured with the Rust rewrite considering that Sqlite is pretty fast C already?

At best it might be the same speed (and if it's an algorithmic issue that slows down Sqlite, then of course they can also fix their C code and overcome that).

Which leaves us with "better security". Is it compelling enough to change software? Depends, I say.

There is a complete sqlite rewrite to C# - https://code.google.com/p/csharp-sqlite/

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...

The problem is that WebSQL was just SQLite, and SQLite only has a single implementation, it's basically implementation-defined.

Welp, There you go, https://github.com/nukep/llamadb

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.

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.

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

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

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).

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.

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.

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

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

ITT: people advertising their SQL-teaching websites.

Reply on news.ycombinator.com