898 KB even without compression. Pretty good for what it is. Properly compressed, it’d be well under half that on the wire.
(The server seems to be serving with only extremely mild compression, and none on the biggest part, the WASM. Feeding it all through `gzip -9` cuts it to 408 KB, of which 374 KB is the WASM. `brotli` cuts it to 352 KB, of which 323 KB is the WASM. The HTML, CSS and JavaScript are also largely unminified, and even a simple conservative pass roughly halves their gzipped size. No idea of the state of the WASM, I’m not going prodding inside it just now. I know that I’m never impressed with the JavaScript side of the bindings produced by Empscripten, Rust’s wasm-bindgen and any other such tools I’ve seen—with a little manual effort, I’ve very easily reduced 50 KB files—minified!—to under 5 KB. Optimising such things and immediately discarding the results is a strange hobby of mine.)
For comparison, the similar parts of https://sql.js.org/examples/GUI/index.html are a bit under 1.2 MB uncompressed (most notably excluding CodeMirror), around 450 KB on the wire.
aidenn0 673 days ago [-]
One of the better commentaries on just how light sqlite is was in apenwarr's redo FAQ[1]
> ... I think people underestimate how "lite" sqlite really is:
> The mysql client library is two megs, and it doesn't even have a database in it! People who think SQL databases are automatically bloated and gross have not yet actually experienced the joys of sqlite. SQL has a well-deserved bad reputation, but sqlite is another story entirely. It's excellent, and much simpler and better written than you'd expect.
There have been a lot of new features over the new years, the latest being window functions. Those features do not impact the database file format.
Embedded systems that don't need these features probably haven't upgraded.
masklinn 673 days ago [-]
> There have been a lot of new features over the new years, the latest being window functions.
Window functions were added in 3.25, 4 years ago.
Since then sqlite added a bunch of stuff like RETURNING clauses, upserts, json operators (and enabled json functions by default though they’d been there a while), update from, generated columns, …
aidenn0 673 days ago [-]
Just checked on my system: 1.9MB for libdb-5 vs 1.2MB for libsqlite3 on My Machine(TM)
sgbeal 673 days ago [-]
> I know that I’m never impressed with the JavaScript side of the bindings produced by Empscripten
i don't mind saying, having spent much of the past 2 weeks in and around that code, that much of the generated part of the JS/wasm "glue" is... Much of it looks like it was thrown together by someone who half-understood JS and was just glad that it worked, with little or no attention to detail and refinement. It could use some TLC.
chrismorgan 673 days ago [-]
Yeah, last time I looked Emscripten’s were definitely considerably worse than wasm-bindgen’s, but that was a few years ago.
There’s also just a lot of missed opportunity for things like abbreviating identifiers, plus stuff that’s completely normal for optimising compilers like GCC or LLVM like inlining and safe code reordering to eliminate completely unnecessary temporary variables and the likes, but for which absolutely no equivalent tooling exists in JavaScript. And I have no idea why that’s the case. I know of only two even vaguely interesting projects along these general lines: Google’s Closure Compiler (2009– but functionally I don’t think much interesting has happened in the last decade), but it’s too esoteric and requires too many compromises for most people to use it (and it didn’t help that it’s written in Java); and Facebook’s Prepack (2018), which tried doing partial evaluation but they given up on it before it really got anywhere useful. Everything else is just quite hopeless, almost never going beyond very simplistic syntactic transformations that don’t modify semantics.
jitl 673 days ago [-]
> almost never going beyond very simplistic syntactic transformations that don’t modify semantics.
I don’t know if this is advanced enough to not be completely hopeless but here’s what I see in my webpack config from 2018:
- hoisting and sharing common constant expressions including non-mutated object literals
- module, function, and variable names are all “abbreviated”
- there’s weird tricks of some kind going on for bound method definitions on classes
- Some elimination of “temporary variables” from object properties, arguments, or module imports
I think all those transforms are enough to substantially reduce the code size.
The real optimization like inlining, monomorphizing, hidden classes, etc are best left to the real compiler — which in Chrome/v8’s case is quite competitive with LLVM/GCC.
chrismorgan 673 days ago [-]
The things you are describing are what I’m calling simplistic syntactic transformations that don’t modify semantics.
As the slightest taste of the sort of thing I’m talking of:
• Any sane developer would be happy to rewrite `let x=a.c;a.b(x)` as `a.b(a.c)`, but that changes semantics (accessing a.b and a.c could have side-effects) and so no tooling short of Closure Compiler with appropriate hints is willing to do it. This sort of pattern is extremely common. End result: readable code is bigger than it should be.
• Tools like Terser are, on a good day, willing to inline functions that are used only once; but they only do this on free functions. Use methods, and they’re useless. Again, Closure Compiler with appropriate hints can do a bit better. End result: abstractions and good factoring are expensive, producing bigger and slower code.
• In fact, this generalises: state-of-the-art JavaScript tooling can do a little where everything is on the stack, but use objects and methods and such and they’re stymied. Inlining. Dead code removal. Name mangling. All things that work inside modules, function bodies, &c., but don’t work once you use objects and properties. Closure Compiler is the only tool that can do anything at all here, which is a real shame. Someone really should have built something like it atop TypeScript by now.
Going back a few years to where this was more prevalent and important, there was a significant difference of philosophy between Babel and Bublé, webpack and Rollup. Babel said “compile new constructs into something that does precisely the same thing”, at a significant code size, performance and readability cost, whereas Bublé said “compile new constructs into something small and fast that almost always does the same thing”, at a slight scope and compatibility cost. Webpack similarly did a robotic port of modules at a significant code size, performance and readability cost, whereas Rollup said “let’s unravel the now-unnecessary abstraction and produce efficient code”. In my view, Babel and webpack are profligate, doing the easy thing rather than the sensible thing; and Rollup does the sensible thing, at only a very slight compatibility cost (and Bublé did the mostly sensible thing, though at a much higher compatibility cost, but we don’t need it any more).
JavaScript engines are extraordinarily good at what they do, given the handicap that they start with. But if you can give them better code, they’ll fare better, especially before JITting occurs. I’m talking about giving them better-optimised code. Also about shipping smaller bundles.
Also, partial execution: if you haven’t worked with native code compilation, you might not realise just how good those things are, especially where mathematics is involved. I love the fact that, in benchmarking a Rust Base58 decoder I wrote recently, I had to use test::black_box on the input, or else decode_u64("jpXCZedGfVQ") evaluated to Ok(0xFFFFFFFFFFFFFFFF) at compile time (and it wasn’t even a const fn!). And Rust is only going further with varieties of guaranteed const evaluation. Prepack tried, but Facebook gave up on it for some reason. For me, I really just want something like Zig’s comptime in JavaScript (well, a compile-to-JavaScript JavaScript variant), as guaranteed partial execution.
sgbeal 673 days ago [-]
> definitely considerably worse than wasm-bindgen’s,
Thank you, wasm-bindgen is a new term for me (wasm as a whole is new to me since about 2 weeks). i'll add that to the list of tools to check out, as we're actively exploring different options and methodologies at this point for wasm/sqlite.
chrismorgan 673 days ago [-]
I doubt wasm-bindgen will be your cup of tea as it’s Rust stuff, though perhaps there may be value in looking at what it generates.
For myself, I progressively lean in the direction of burning all of these things down (when possible, and it must be admitted that Emscripten’s strength is that it makes legacy stuff work) and writing binding JavaScript manually, also with less of an FFI/dual-sided-bindings/skip-blithely-between-languages flavour and more deliberate, less RPCy techniques.
sgbeal 673 days ago [-]
> I doubt wasm-bindgen will be your cup of tea as it’s Rust stuff, though perhaps there may be value in looking at what it generates.
Yeah, Rust isn't part of the sqlite project's toolchain, but wasm is entirely new to the project and we're eager to learn more about it and to make sure that the JS/wasm code is not only usable but also "good code" (or at least "presentable" code!). Yes, we currently rely on emscripten's generated bits, but i've made an active effort to slowly trim down those dependencies as i get a clearer picture of where the borders between wasm, JS, emscripten, and client code lie. Ideally we wouldn't be dependent on one compiler, but currently we are. Baby steps!
easrng 673 days ago [-]
Closure Compiler still works, I just wish it could output modern JS. It transpiles down to either ES3 or ES5 and there's no way to turn that off. Unfortunately there doesn't seem to be a good alternative.
sgbeal 673 days ago [-]
> 898KB even without compression. Pretty good.
gzip'd it's roughly half of that but that particular web server (althttpd) doesn't (yet?) know how to compress output payloads.
motiejus 673 days ago [-]
It makes sense to pre-compress the static assets. Then the server can load the compressed ones directly, without requiring to link to the compressor, and of course pay the price of online compression.
I create two extra files for each static file (for some extensions):
file
file.gz <-- with zopfli
file.br <-- with brotli
And the web server, depending on the `Accept-Encoding` header, would serve the right file.
The wasm module:
$ ls -lh fiddle-module.wasm*
-rw-r--r-- 1 motiejus motiejus 779K May 26 15:25 fiddle-module.wasm
-rw-r--r-- 1 motiejus motiejus 323K May 26 15:25 fiddle-module.wasm.br
-rw-r--r-- 1 motiejus motiejus 357K May 26 18:00 fiddle-module.wasm.gz
sgbeal 673 days ago [-]
> And the web server, depending on the `Accept-Encoding` header, would serve the right file.
This web server (also part of the sqlite family of projects) doesn't yet know how to do that. https://sqlite.org/althttpd
chrismorgan 673 days ago [-]
Interesting comparing the sizes of different compressors. `gzip -9` gets to 374K, whereas you report zopfli taking it down to 357K.
And I had at first run `brotli -9`, being fairly confident in my memory that it had more than nine levels, but finding -h mentioning -# as 1–9, and not seeing --best (level 11) at the other end of the output, and that that’s the default (unlike gzip where I think the default is mostly 6), so that -9 actually makes it do a worse job. Sigh.
673 days ago [-]
sgbeal 673 days ago [-]
Richard Hipp, of sqlite fame, just announced the project's new WASM-based "fiddle" app in the sqlite forum (https://sqlite.org/forum/forumpost/5cfd681451), providing a way for users to run a slightly-hacked build of the sqlite3 shell app in their browsers (with no server-side backend).
(Edit: this was my first-ever HN post and i _thought_ that this comment was going to "stick" up top with the link to serve as an introduction/explanation. Didn't realize that it would be "just another comment.")
tinus_hn 673 days ago [-]
This is from the FAQ:
How do I make a link in a text submission?
You can't. This is to prevent people from submitting a link with their comments in a privileged position at the top of the page. If you want to submit a link with comments, just submit it, then add a regular comment.
Abishek_Muthian 673 days ago [-]
You can do so with Show HN, Although the faq[1] doesn't explicitly state that.
But in this case, Richard Hipp himself should post it as Show HN.
If you find this useful, you may also benefit from the fact that Python compiled to WASM (by Pyodide) also includes a working build of SQLite in the sqlite3 standard library module.
That's pretty trippy, I have my caps lock globally disabled (mapped to ctrl) in X config, but inside that v86 window, caps lock works. And it uses some hardcoded (US) keymap, too.
I'm surprised, I didn't think browsers would receive those detailed keycodes.
dorianmariefr 673 days ago [-]
`event.key` returns the key with the mappings, e.g. "E"
`event.keyCode` returns the physical key, e.g. "KeyE"
kungfufrog 673 days ago [-]
Holy smokes, all of this is very cool! Nice work!
samwillis 673 days ago [-]
This awesome, I'm convinced WASM SQLite is the future of in-browser strorage for web apps/PWAs.
Assuming this is normal WASM SQLite any persistence will be by flushing to LocalStorage/IndexedDB. There won't be any ACID compliance.
There is a project called "Absurd SQL" to back SQLite with a custom block based FS on top of IndexedDB. It is somewhat absurd but it works incredibly well!
I think combining WASM SQLite with the session extension (https://www.sqlite.org/sessionintro.html) would be a super interesting way to build an eventually consistent datastore for web apps. You could do all transactions locally and sync back and fourth just the changes.
sgbeal 673 days ago [-]
> There is a project called "Absurd SQL" to back SQLite with a custom block based FS on top of IndexedDB. It is somewhat absurd but it works incredibly well!
(The fiddle dev here...)
That is, AFAIK, the current state of the art for persisting sqlite wasm-side dbs. The JS-side APIs for providing such support are currently evolving within the browsers. With any luck, we'll have widespread JS-native persistent storage for sqlite within the next couple of/few years. (Those things take time to develop and, just as importantly, propagate through the browser ecosystem.)
> I think combining WASM SQLite with the session extension ... would be a super interesting way to build an eventually consistent datastore for web apps. You could do all transactions locally and sync back and fourth just the changes.
FWIW, that's what i personally envision will likely be the most compelling use case for the sqlite wasm builds. The fiddle app is _just_ the sqlite3 shell, not the sqlite3 library, but we've also created a plain-jane wasm binding of the C library and written an OO-style API on top of that (roughly akin to sql.js and friends, with different design decisions in some places). We're working on an API conceptually similar to sql.js's "worker" API, where the sqlite3 wasm can run in a WebWorker and the main thread can talk to it via WebWorker messages. That said, for the type of jobs sqlite will likely be doing in web clients, the workloads will be fast enough that running the wasm build in the main thread is unlikely to lead to any usability issues (blocked UI). Nobody's going to be using a 20gb db in such an app (because the browser won't let them) and sqlite is blazing fast for small data sets. Even so, the core APIs are agnostic of whether they're running in the UI thread or a WebWorker, so the client can decide for themselves how to plug it in.
samwillis 673 days ago [-]
> We're working on an API conceptually similar to sql.js's "worker" API
Thats interesting, so it will be an "official" WASM JS binding for SQLite? Is it available anywhere?
> With any luck, we'll have widespread JS-native persistent storage for sqlite within the next couple of/few years
If you are building an "official" WASM JS binding have you considered doing something similar to absurd SQL in order to achieve atomic persistence?
sgbeal 673 days ago [-]
> Thats interesting, so it will be an "official" WASM JS binding for SQLite?
That's not guaranteed/official yet but it has been discussed and seems to be where we're headed. In the end it's Richard's decision, though, not mine. (That said, i would like to see that happen but won't be soul-crushed if it doesn't because creating a wasm build of sqlite is _so easy_ to do that any project can create their own with little effort.)
It's currently in the sqlite3 trunk. After configuring the tree, run "make fiddle" or (cd ext/fiddle; make), though the latter approach requires GNU make, not some under-powered make-wannabe. (The top-level makefile is compatible with Lesser Makes.)
Edit: it requires emscripten and ext/fiddle/index.md provides an overview of how to get it going.
> If you are building an "official" WASM JS binding have you considered doing something similar to absurd SQL in order to achieve atomic persistence?
Not until we have JS APIs which are built for that type of thing. Absurd's approach is an interesting proof of concept but is aptly named. Absurd is a stopgap measure which will, with any luck, "soon" be obsoleted by ... how to put this... "less absurd" alternatives ;).
rasz 673 days ago [-]
> I'm convinced WASM SQLite is the future of in-browser strorage
Now imagine if we shipped native sqlite in all browsers, and provided native API for it, we could call it websql or something. Im sure the biggest proponents of open source world wide web would be all for it!
mrtesthah 673 days ago [-]
Is this some sort of competition to see how many redundant layered of abstraction we can add before the user starts to wonder why the responsiveness of the app has dropped to 1987 levels?
(from memory the was some discussion that it wasn't a completely fair comparison, but even so was still more performant)
loxias 673 days ago [-]
> responsiveness of the app has dropped to 1987 levels?
Donno about 1987, but everything I do other than locally develop code with emacs has a responsiveness far worse than I remember from 1997. Everything has to load some stupid javascript, everything has a delayed reaction to each click or scroll. Nothing just runs or just works anymore. The cost of a lower barrier to entry for "software" development. :/
edgyquant 673 days ago [-]
You and I remember ‘97 quite differently. It took 5-10 minutes to download .txt guides to video games (in 99 I downloaded ocarina of times walkthrough and that thing was a monster!)
Hell I remember waiting almost an hour to download the Spider-Man 2 trailer in 2002. Meanwhile now I’m watching 1080p YouTube videos while running my entire dev environment locally (database, front and backend + code editor) and still it takes a second at the most to load 99% of pages.
robocat 673 days ago [-]
Ironically, latency was far better in 1987 for plenty of users, even though you are implying it was worse. https://danluu.com/input-lag/
I would like to see latency figures for the dominant spreadsheet on the average computer over the years though, starting with VisiCalc on Apple ][.
alephnan 673 days ago [-]
> I'm convinced WASM SQLite is the future of in-browser strorage for web apps/PWAs.
In what use cases would it make sense to transfer the entire db file over the network, then pluck out the relevant rows into JSON, versus just doing that server side and transfer the JSON back?
Is the trade off that the server does less computation, but then pay for network bandwidth fees?
kristopolous 672 days ago [-]
Not the use-case. Here's a javascript database I made between 2011 and 2016 that is pretty web use-case specific.
Scrolling through the documentation should make some use-cases more obvious. Restructuring, sorting, filtering and modifying data in a fast-enough way that doesn't require thinking hard about things is pretty invaluable.
I was actually just using it earlier this month for the first time in a long while. I had forgotten how nice it was. Really made my job way easier but YMMV.
sgbeal 673 days ago [-]
> In what use cases would it make sense to transfer the entire db file over the network, then pluck out the relevant rows into JSON, versus just doing that server side and transfer the JSON back?
Lots of dbs are small - a few hundred kb, maybe a meg. Many sites, by comparison, serve _megabytes_ of CSS and/or JS (minified!). i just hit imdb dot com for example's sake: 7MB of Stuff to load the front page, with something like 1MB of it being JS code.
In addition, this type of app permits the user to inject a copy of a client-local db with no network traffic. In developing fiddle we've seen it work with dbs of approximately 80mb (haven't tested anything larger), but those limits are going to be very browser-/environment-dependent.
> Is the trade off that the server does less computation, but then pay for network bandwidth fees?
That's one tradeoff. Another is less coding effort needed to implement the client/server halves of the interaction. In conventional apps, every client-side operation on data results in a call to a server-side counterpart of that client-side operation. That needn't be the case for this style of app. For some apps, all of the code can now be client-side. For some, data might be synced/pushed to the server after changes are made, but there need not be a continual back-and-forth between the client and server.
Another point is data privacy: this sort of app need not send the db to anyone. The db can stay entirely on the client, so they can use private databases without any concern that the server might squirrel away a copy or leak a copy to other folks.
That said: the wasm approach, with a client-embedded db, is most definitely not a one-size-fits-all solution. It will serve a certain niche of applications, but not the majority of web app data needs.
edgyquant 673 days ago [-]
Not all applications need a centralized DB and even those that do likely have client only data.
xwowsersx 673 days ago [-]
I've noticed a large number of stories on HN related to SQLite over the past few weeks. Maybe it's just random or I'm only now just noticing it, but is there some renewed/newfound interest in SQLite lately? If so, what's behind that?
dgb23 673 days ago [-]
SQLite has been improving quite significantly over the years and continually impresses people in terms of performance, features and robustness.
When I first heard about it 10y ago, my boss (web shop) said it "isn't a real database", and that notion prevails in many circles. However more and more web developers recognize its benefits for production use. There are many use cases for it.
Most web projects should default to it IMO, because of its low operational costs and great performance, both in terms speed and reliability. It's incredibly straight forward to use, set up, back up and so on.
As an example: Wordpress, could absolutely default to SQLite as the vast majority of installations run on a single, shared host (apache/litespeed/nginx) and the data model is very simple and almost throughout stringly typed anyways. Running MySQL just adds friction and overhead for these use cases.
matthewaveryusa 673 days ago [-]
I think so. I attribute it to dqlite, rqlite and litestream that are making sqlite viable server side. It's always been viable server-side in tandem with a paxos/raft changestream (etcd streaming the changelog to an sqlite database.) but with the new projects providing out-of-the-box replication solutions it's even more-so viable.
It brings me so much joy to see sqlite get the love it deserves -- it's by far the best library I've ever used.
krferriter 673 days ago [-]
I think some people are realizing that SQLite has everything they need in their database use case, and they don't need a heavier, more complicated client-server database with more features.
bob1029 673 days ago [-]
This is exactly the reason. Some of us went off the beaten path and proved that you can actually put many users on a carefully-tuned SQLite instance (despite their official docs at the time). In many cases, you are able to exceed the capabilities of a one-node hosted DB solution due to latency reduction of in-process happiness.
xwowsersx 673 days ago [-]
Thanks. I thought that may have been the case, but I wasn't sure if there were also some recent improvements or developments.
Jarwain 673 days ago [-]
I find topics tend to surge and wane, and sqlite is a popular one.
They don't always surge for a reason other than "someone posted something new about it and sent people down rabbit holes"
heavyset_go 673 days ago [-]
It was a meme in the "webscale" era that SQLite couldn't scale to many users, and was effectively something to use in desktop and client-side applications like mobile apps.
There have been times I've been talked down to in the past for using SQLite, so I just learned to shut up about it around ideologues. Now the tide is turning when it comes to webscale assumptions, and some of those ideologues' ideas have been proven wrong in many aspects.
SQLite performs well in read-heavy loads, even in web apps with many users, so it can fit those use cases well.
I've been using it in a reverse search engine that gets a lot of traffic for going on 8 years now.
To be fair, during that time the NoSQL crowd was saying that about all relational databases.
heavyset_go 672 days ago [-]
Yeah, at the time SQLite existed both as an archetypal example of "relational databases bad" and a case of "especially this one that isn't even a 'real' RDBMS".
rzzzt 673 days ago [-]
I think it's this one (for context, the submission title was "Fly.io buys Litestream" before changing to the blog post title; you can still find it indexed that way in search engines): https://news.ycombinator.com/item?id=31318708
sgbeal 673 days ago [-]
> ... is there some renewed/newfound interest in SQLite lately? If so, what's behind that?
Though i share that observation, in this particular case it's very likely coincidence. i've worked with Richard since 2008 on his Fossil SCM project, so have been "in that circle" for a long time without having ever actually contributed directly to sqlite. About two weeks ago the topic of wasm came up in a dev chat and it sounded to me like something interesting to experiment with (me being Fossil's "JavaScript Guy"), so i ran with it.
srcreigh 673 days ago [-]
SQLite is quite famous for new developers as the default db in Rails and Django. There's a widespread misconception that SQLite is a toy DB and that it's necessary to switch to a real DB like Postgres or MySQL.
I suspect this fuels some of the hype--trying to correct the misconception.
rrix2 673 days ago [-]
well, let's be honest here: some of this is legitimate due to the nature of those frameworks, not necessarily sqlite. If you deploy a rails app with 8 process workers powered by a sqlite db even on a single host you're going to have database locking problems.
You have to be able to architect your application to send all write traffic to a single process and have a language that can keep up with your database while doing that. There are "better" architectures for this stuff now that will enable that which have come together in the last few years, and a resurgence in smaller indie projects willing to deploy sqlite on servers and do weird cool things like this fiddle or datasette
sgbeal 672 days ago [-]
> You have to be able to architect your application to send all write traffic to a single process ...
Counter-point: sqlite's sister project and SCM, the Fossil SCM, is a self-hosting sqlite client application and has been since 2007. Every hit on the fossil-scm.org website is its own standalone fossil process accessing the same copy of the same db file, all while the developers are actively pushing and pulling changes to/from that same db and while half a dozen or so folks are logged in to its /chat room (each instance of which is long-polling that same db 24/7).
In 14 years of using that db i've encountered _maybe_ two locking errors.
Similarly, sqlite3's own forum is a fossil instance hosting a single sqlite3 repository.
That all writes "have" to be channeled through a single manager is demonstrably not the case.
rrix2 669 days ago [-]
Does fossil or the SQLite forum use rails or Django?
SQLite 668 days ago [-]
No. The SQLite Forum is just an instance of Fossil. Fossil is written in C.
dinvlad 673 days ago [-]
I think this may also have to do with the indie community - SQLite seems to literally fit the bill much better than hosted DBs. A lot of what "the biggest companies" do doesn't go anywhere close to where most small (or even medium) teams' needs are. A lot of that additional complexity is simply not worth the extra effort and manpower (and oftentimes creates it!). "Lean programming" has become cool again :-)
IshKebab 673 days ago [-]
Yeah definitely renewed interest. I think there's been a collective realisation that it's pretty good, and it has received some notable new features recently like JSON queries and proper type checking.
ludwigvan 673 days ago [-]
Probably a reaction to the ever growing complexity of software systems.
fideloper 673 days ago [-]
Lots of new stuff happening lately, yep! Mostly around distributed systems?
The author disclaims copyright to this source code. In place of a legal notice, here is a blessing:
* May you do good and not evil.
* May you find forgiveness for yourself and forgive others.
* May you share freely, never taking more than you give.
sgbeal 673 days ago [-]
> Happy to see some zero-dependency VanillaJS.
You're welcome :). Frankly, since the HTML5-related JS changes were standardized, jquery is no longer the "absolute must-have" it was prior to that, and i strive to use only vanilla, standards-conforming JS wherever feasible (with diminishingly little tolerance for non-conforming browsers).
Edit: that license disclaimer is the standard one for the sqlite project, though, not mine.
Which is another nice WASM-based browser SQLite user interface.
sgbeal 673 days ago [-]
> ... another nice WASM-based browser SQLite user interface.
Thank you for pointing that one out. Every conceptually similar project is a great source of ideas. sqlite's fiddle app is literally less than 2 weeks old so still has lots of room left for feature creep ;).
SahAssar 673 days ago [-]
Hasn't sql.js (sqlite compiled to wasm/js with emscripten) been around for a long time (at least a couple of years)?
What makes this different other than coming from the actual sqlite team?
sgbeal 673 days ago [-]
sql.js is quite similar but not quite the same thing. They produce a high-level wrapper of the C API, but they do not offer a binding of the shell app (which is what fiddle is). The latter requires, due to how emscripten wraps the C function fgets(), hacking the shell in order to get sane input semantics. sql.js has, however, been a great point of reference and inspiration in building fiddle.
yread 673 days ago [-]
This is fairly easy to recreate on your computer. What I would like to fiddle with are compilation options, extensions, pragmas and their combinations. Make it easy to also see the io ops and cpu load per query and it becomes interesting
sgbeal 673 days ago [-]
> This is fairly easy to recreate on your computer.
It is trivial to. The only tiny hiccup is that the wasm file cannot be served via a file:// URL due to XHR restrictions, so you'll need a local webserver for it. No big deal, but a slight nuisance. Richard added a new feature to his althttpd web server just to support this very dev effort. From the fiddle source directory, do:
althttpd -page fiddle.html
and it will start up a web server and launch your browser with that page. althttpd only works for Unix-style OSes, but ... that's okay :).
> What I would like to fiddle with are compilation options, extensions, pragmas and their combinations.
The build process for fiddle is literally a single compile command (using the emscripten compiler, emcc), and build time with the -O0 optimization level is fast (a few seconds on my modest i3-based NUC computer), so such fiddling is trivial to arrange for. If you do so, and find anything interesting, please post about it in the sqlite forum.
longrod 673 days ago [-]
SQLite is one of the most underrated databases around and it's sometimes baffling to know how much software actually relies on it.
rathboma 673 days ago [-]
This is nice, but more of a console than a fiddle.
When iterating on SQL for Beekeeper Studio I've been using https://dbfiddle.uk for a few weeks, it's super good.
Supports PSQL, MySQL, Oracle, etc.
sgbeal 673 days ago [-]
> This is nice, but more of a console than a fiddle.
It's a binding of the sqlite3 console app, so that's an appropriate contrast. We're not trying to supplant such services as sqlfiddle and the wild world of wasm is entirely new to everyone currently working on the sqlite3 project, so we still have tons to learn and improve upon.
rathboma 673 days ago [-]
Yep! It's a great project whatever one calls it :-).
tiffanyh 673 days ago [-]
Given that this SQLite variant is WASM-based, how does this relate to Cloudflare recent D1 announcement where you can use Workers (WASM) to connect to an SQLite datastore?
sgbeal 673 days ago [-]
> Given that this SQLite variant is WASM-based, how does this relate to Cloudflare recent D1 announcement where you can use Workers (WASM) to connect to an SQLite datastore?
They're conceptually similar but, while i'm intimately familiar with the fiddle app, i don't know enough about D1 to know _how_ similar they are or are not.
Note, also, that fiddle is analog to the sqlite3 shell app, not the library API. The library API itself is a separate wasm sub-project.
status200 673 days ago [-]
Looks like a great sandbox, perfect for testing ideas and queries without having to spin up an environment or log in to something like Big Query
Kudovs 673 days ago [-]
I can see this being useful for technical interviews and quick demos.
niek_pas 673 days ago [-]
Please don't interview potential hires by having them do work while you look over their shoulder.
edgyquant 673 days ago [-]
So algorithmic white board interviews are bad because they aren’t a measure of day to day productivity and now programming interviews are bad because reasons. How should I determine if engineers are a good fit then?
niek_pas 672 days ago [-]
In my view, you should allow candidates to work on an assignment of your choosing to be handed in by a deadline you set.
frakkingcylons 673 days ago [-]
Pair programming in an interview is one of the most useful ways to evaluate candidates in my experience.
tlhunter 672 days ago [-]
This is super useful. I love the web apps that do one thing well. regex101.com is one of my favorite tools in the category and the fiddle apps are amongst my favorites.
With SQLite 3 Fiddle, pressing Ctrl+Enter executes every single query in the input field. This means you either need to delete old entries and lose your place or manually prefix them with double hyphens. As a nice-to-have feature, I'd love it if the tool followed the approach that Kibana Elasticsearch / DBeaver uses.
With that approach, if text is selected, that text is executed as a query. And, if a cursor is in the middle of a query, only that query is executed instead of the whole thing. To run everything you select it all first (Ctrl+A Ctrl+Enter).
coder543 673 days ago [-]
I played with it for a minute, and the feature I want is just a checkbox to reset the database each time I run the query.
It's nice to be able to iteratively build up a sequence of queries on the input, including creating tables, inserting items, etc. But, I don't even see a manual way to clear the database (which maybe should be a button too?) without refreshing the page... and refreshing the page forgets all of my preferences. (it still manages to keep the query input, it looks like, which is a start... but maybe that's just my browser trying to be helpful.)
sgbeal 673 days ago [-]
> I played with it for a minute, and the feature I want is a checkbox to reset the database each time I run the query.
That's a good idea. There are tons of options i'd _like_ to add to it but have not simply for UI space's sake. The real limit on the UI is "how many options can we fit while still leaving room for input and output." Nobody involved in the development effort is a particularly strong UI developer (i can say that because i'm the one who wrote that UI ;), and assistance in prettying it up and improving the U/X would certainly be appreciated.
> But, I don't even see a manual way to clear the database without refreshing the page... and refreshing the page forgets all of my preferences.
Storing of the preferences in localStorage is on my TODO list. The underlying mini-API for it is in place, i just haven't yet dedicated the few hours to plug it all in and test it. Baby steps.
> (it still manages to keep the query input, it looks like, which is a start... but maybe that's just my browser trying to be helpful.)
That it keeps the query input is a _browser-specific quirk_, not an explicit feature. By and large, that quirk (Firefox, right?) is a huge pain in the butt in web development because it forces the developer to do a full reload on each hit, bypassing all caching.
coder543 673 days ago [-]
> By and large, that quirk (Firefox, right?)
Yep, definitely Firefox. In this case, my first thought was that the webpage was "doing the right thing", but then I realized the browser was probably doing it. Either way, it is the behavior I would expect in this case, so I consider that a win, but I understand it can be challenging for web developers under other circumstances.
> assistance in prettying it up and improving the U/X would certainly be appreciated.
My understanding is that the SQLite team typically isn't very open to outside contribution. UI/UX historically isn't my strong suite either, though, but I have been trying to work on that lately.
sgbeal 673 days ago [-]
> Yep, definitely Firefox. In
To be clear, i wasn't badmouthing FF (all of fiddle's develoment so far, aside from occasional individual tests) has been in FF on Linux. That one particular FF feature kinda gets my goat, though, when i'm writing web apps ;).
> My understanding is that the SQLite team typically isn't very open to outside contribution.
It's not the contribution, per se, but actual code patches are tricky for the sqlite project because of licensing. sqlite is released into the public domain by its creators, but not all legal jurisdictions recognize public domain as a real thing. Thus Richard is extra-extra-careful to ensure that all of the code which goes in to the repository is added by someone who's signed a waiver validating that any code they added is not going to be a licensing issue, and only people Richard has come to know and trust are offered the option of signing that waiver. Often, when patches are posted by users, they can be used as a basis for equivalent patches but cannot be used as-is because of the potential for licensing fallout.
sgbeal 673 days ago [-]
> I played with it for a minute, and the feature I want is just a checkbox to reset the database each time I run the query.
After experimenting, i'm extremely hesitant to add the option to _automatically_ nuke the db each time input is submitted because, frankly, That Way Lies Madness and you are probably the only person who would use it. However, the app now has a button to nuke the db.
In related news: if any text in the input field is currently highlighted, only that text is submitted via the Run button or ctrl-/shift-enter.
That's not yet (as of this writing) deployed but will be the next time Richard updates the site.
coder543 673 days ago [-]
If you consider the design space, “resetting the state every time” is exactly how basically every other “fiddle” app works. If JSFiddle were just duplicating your code into the javascript VM each time you hit run, you’d be overwhelmed with pointless errors about duplicate identifiers immediately. The idea is to let you build up a reproducible, shareable outcome. So, if you want to share a demo of how you might create a few tables and perform join queries, you can easily and interactively assemble that if the state resets to zero each time you run the set of queries.
It’s probably a completely different way to use the application than you were considering if you don’t see the point of it, but I’ve actually used another tool exactly like this in a job interview that involved testing SQL knowledge.
Would it be useful for manipulating an existing SQLite database? Probably not. (Unless it reset to the state the database had when you imported it, instead of clearing it? It might work…)
> However, the app now has a button to nuke the db.
Awesome!
> In related news: if any text in the input field is currently highlighted, only that text is submitted via the Run button or ctrl-/shift-enter.
That would certainly be helpful towards the use case I’m describing, although I can imagine that UX being difficult to discover on purpose (and confusingly easy to discover on accident when you get weird errors as SQLite tries in vain to execute some invalid SQL fragment you accidentally had selected before you hit the run button).
sgbeal 673 days ago [-]
> If you consider the design space, “resetting the state every time” is exactly how basically every other “fiddle” app works.
This app's design space is exposing the sqlite3 shell app via the web, not to emulate every other fiddle environment. sqlite shell app's doesn't (with very good reason) behave that way and the fiddle frontend is just passing state between the user and that app.
> The idea is to let you build up a reproducible, shareable outcome.
Because this app has no server-side state, we have no way of _sharing_ the outcome. Large fiddle sites give you a short URL which refers to state in their server-side storage. This app completely lacks that (and we're definitely not going to add server-side state to this app). What we _might_ eventually do is offer the ability to base64-encode the SQL into a URL argument which can be shared, but URL length limits are very server-dependent so it would be impossible to portably share "large" fiddles that way.
> (and confusingly easy to discover on accident when you get weird errors as SQLite tries in vain to execute some invalid SQL fragment you accidentally had selected before you hit the run button).
There's a comment in the initial example SQL explaining that feature, so it'll _hopefully_ be noticed by people new to the app. (Noting that that's not, as of this writing, yet deployed on the production server.) Another feature under consideration is having it intermittently spit out random tips/reminders to the output area (when the db is not working - the app knows when that's happening), and the exec-selection feature would be one such tip.
infogulch 672 days ago [-]
"sharing the outcome" in fiddle apps often just means reproducing the input text expecting a reproducible output given consistent input, nothing more. This can be seen through the fact that sometimes the share feature doesn't use any server side storage (link shortener) at all and instead stores the entire text of the fiddle in the url itself, e.g. https://play.rust-lang.org/?version=stable&mode=debug&editio...
This is just to clarify GP's perspective, I understand that you may be going more for "instant local sqlite repl environment" than "playground to develop shareable snippets of sqlite commands".
sgbeal 673 days ago [-]
> (Unless it reset to the state the database had when you imported it, instead of clearing it? It might work…)
There's no way to know what the db's state was when it was imported without literally keeping a second copy of the whole db solely for comparison's sake, then writing the code to perform the comparison. In the browser storage space, how much space is available for storing such copies is unknown (an unknowable, AFAIK), so we can't simply keep extra copies willy-nilly like we possibly could in an out-of-browser client-side app.
That said: a user can start their session with "BEGIN" and use "ROLLBACK" to revert to the original state.
coder543 673 days ago [-]
Yeah, I was actually picturing such a feature with an existing database being implemented under the hood using a transaction block around the query input. This idea could also be extended to the most basic case of a completely empty database without modification, instead of just resetting the database, it would just use the same transaction logic.
The transaction would be left open until the user either decides to re-run the (presumably modified) query input (causing a rollback first) or to (presumably) end the session by downloading the database (causing a commit first). This behavior would only apply in the auto-resetting “fiddle mode”, of course.
Anyways, it was just an idea I was thinking about. I’m sure this idea is not flawless, and you probably have other, higher priority features in mind.
forrestthewoods 673 days ago [-]
Confession: I have 15 years dev experience and have never written a single line of SQL code.
What’s a good tutorial for someone who knows how to program but doesn’t know the various SQL commands and gotchas?
Can you use SQLite tables as data structures like Pandas dataframes or Numpy arrays? Can you use them as aggressively as hash tables? Just wondering how aggressively you can use SQLite. Like, can I write functions that take SQLite tables in and output SQLite tables? Can I use SQLite queries as alternatives to list comprehensions? The lack of a separate process and IPC makes SQLite seem like it could be yet another data structure in Python's "collections" module.
dinvlad 673 days ago [-]
This throws me back to the good ol' days of fitting all software in a few dozens/hundreds of KBs on Speccy and similar systems. Current software engineering practices are so wasteful on computer resources, and imho unnecessarily so, when we keep in mind what was possible on those systems with 1000x less compute power decades ago.
sgbeal 673 days ago [-]
> This throws me back to the good ol' days of fitting all software in a few dozens/hundreds of KBs on Speccy and similar systems.
And yet very few developers would want to go back to the days when we had no syntax highlighting and editors couldn't hold more than 64kb of text at a time and the OS could only run one thing at a time. Times change (thankfully). The 64kb machines now live in the domain of hobby projects, not machines for "getting things done." (There's a rather active group of folks who still hack on the C64, C128, and similar machines. Search for the "MEGA65" to find a recent (late 2021/early 2022) machine which was built solely for that crowd.)
CRConrad 673 days ago [-]
> And yet very few developers would want to go back to the days when we had no syntax highlighting and editors couldn't hold more than 64kb of text at a time and the OS could only run one thing at a time.
Syntax highlighting, multi-megabyte file editing, and multitasking were all standard on ordinary software development PCs twenty years ago; perhaps closer to twenty-five.
And those ordinary software development PCs had eight, sixteen or thirty-two MEGAbytes of RAM -- not gigabytes, like today. If you're saying syntax highlighting, multi-megabyte file editing, and multitasking are the reason we need three orders of magnitude more RAM to get the machine to do the same thing (only often more slowly) now as then, you're either selling something or you just don't know what you're talking about.
sgbeal 673 days ago [-]
> you're either selling something or you just don't know what you're talking about.
There's no need to be combative. i've been programming since the 80s (mid-90s professionally), and in _no way_ long to return to those days. It was fun while it lasted, but the dev tools, environments, and targets we have nowadays are a lot of fun in their own ways too, and many (perhaps most) of them wouldn't be possible/feasible with 16MB of memory on a 120mhz PC.
i'm selling nothing but the idea (for $0) that people expect far more from software now than then, and there's no way to run a modern app in such a limited environment.
CRConrad 672 days ago [-]
I'm giving away my ideas for free, so I'm morally superior to you! ;-D
Yeah, sorry, didn't mean to be more combative than that.
The idea I'm giving away is that 99.9% of the stuff people expect[1] from software nowadays is just plain unnecessary. Actually detrimental, in many (most?) cases: It just complicates things.
___
[1]: Or perhaps, I suspect, that software developers nowadays just think people expect from software, so they provide it without even reflecting whether it's really necessary at all.
sgbeal 672 days ago [-]
> Or perhaps, I suspect, that software developers nowadays just think people expect from software, so they provide it without even reflecting whether it's really necessary at all.
For the most part i blame that on the marketing folks rather than developers, but FOSS projects like this one have no marketing folks who i can push the blame off to ;).
CRConrad 669 days ago [-]
The marketroids are the ones that make devs think that, yes, but whichever way they get it, the devs end up with that exaggerated impression. Affects FOSS devs too; they can't make themselves selectively deaf only to marketroid-speak. Well, I guess in part one can, but given how pervasive it is throughout the industry, not entirely.
dinvlad 673 days ago [-]
Sadly so :-) I felt much more productive coding without all those bells and whistles than nowadays - I wonder why..
ReptileMan 673 days ago [-]
My little pony: linking is magic.
But with harddisks and bandwidth (aside from mobile) being limitless we just decided to stop bothering. The only device on which I am remotely space constraint is my MacBook. And that is mostly apple business decision.
stefan_ 673 days ago [-]
We can finally get rid of IndexedDB, and it's only 900KiB extra bundle size!
sgbeal 673 days ago [-]
> We can finally get rid of IndexedDB,
Not quite yet: indexeddb can be stored persistently whereas wasm-side sqlite dbs cannot (unless they're stored _in_ an indexeddb). Someday the JS filesystem APIs may offer such a thing, but right now there is no standard filesystem-like API which offers client-side persistence. The only half-workaround for the time being is the ability to upload and download the db image (something both sql.js and sqlite3's fiddle support).
CRConrad 673 days ago [-]
Store it in a really big cookie? ;-)
sgbeal 673 days ago [-]
> and it's only 900KiB extra bundle size!
Depending on how it's compiled, yes. It can be as large as 2MB when built with no optimizations.
mwint 673 days ago [-]
For production use, presumably you’d use an optimized binary
sgbeal 673 days ago [-]
> For production use, presumably you’d use an optimized binary
That particular one is built with -Oz (optimized for the smallest size). Development itself is done with -O0 (no optimizations) because the compile time difference is something like 2-3 seconds vs 15-20.
josephg 673 days ago [-]
330kb over the wire with brotli. Thats sizable, but definitely usable in a heavier web app.
Honestly thats really impressive given it includes an entire SQL engine.
sgbeal 673 days ago [-]
> 330kb over the wire with brotli. Thats sizable, but definitely usable in a heavier web app.
My almost-most-used website (boardgamegeek.com) currently serves 500-odd kb of minified CSS and 2MB of minified JS. That is to say: in the context of modern websites, 300-800kb arguably rates as a mere pittance.
mycall 673 days ago [-]
Are you suggesting IndexedDB has a limited life remaining?
polishdude20 673 days ago [-]
I've been looking into creating a better MySQL fiddle. How do people go about doing that? Do you need to rewrite the whole database engine in wasm or something?
- make the shell prompt interactive
- bundle various $EDITOR choices
Heh.
sgbeal 673 days ago [-]
> make the shell prompt interactive
On my dev system i have a build of fiddle which uses the jquery.terminal plugin as the main interface (with a button to toggle between it and the current UI), but it relies on 3rd-party code which is not approved for inclusion into the core sqlite3 repository (plus it's huge: all of the competent terminal-like APIs we evaluated so far bring 300-500kb of minimized JS dependencies). Reimplementing such a console, minus the jquery/etc dependencies, is beyond my current ambitions but has not been ruled out entirely long-term. Baby steps.
> bundle various $EDITOR choices
That would require a _proper_ terminal for those editors to live in, as opposed to a terminal lookalike such as jquery.terminal. xterm.js could presumably do it but is even bigger than jquery.terminal. No doubt that's _possible_ in wasm, but such features are _way_ beyond any current aspirations or scope for this app.
cryptonector 673 days ago [-]
What you do is build a very cut-down Linux system, bubybox style, as wasm, and deliver that.
sgbeal 673 days ago [-]
> What you do is build a very cut-down Linux system, bubybox style, as wasm, and deliver that.
Though i'd very much like to see such a thing, that's well, well, _well_ outside the scope of this project. If someone wants to create such a fiddle environment, _fantastic_, and i'll support them in any way i can in terms of necessary tweaks to the underlying fiddle-related code, but the surrounding bits would be someone else's itch to scratch.
jansommer 672 days ago [-]
Displaying unicode characters could be a problem here. I haven't managed to get it working when building a BusyBox powered Buildroot Linux, and in my search I've found another one who've tried and failed as well.
pjot 673 days ago [-]
Very cool!
I’m currently on mobile, but was excited when `shift + enter` still executed the sql!
sgbeal 673 days ago [-]
> I’m currently on mobile, but was excited when `shift + enter` still executed the sql!
You got lucky - that was untested on non-keyboarded machines ;). There's a "run" button at the bottom intended primarily for mobile use BUT the UI is definitely not well-optimized for mobile devices yet. They are a target, certainly, but optimizing for them is well down the TODO list.
Psychosecurity 673 days ago [-]
This lets you fiddle around with SQLite from the browser
newbieuser 673 days ago [-]
anyone running a large scale saas job with rqlite/dqlite?
jansommer 672 days ago [-]
I think it's a deal breaker that rqlite only supports deterministic statements [0] and the way dqlite works is by having to run inside your application, meaning that you no longer can scale to zero.
Ideally someone would implement replication as a separate process, like litestream but with better guarantees like rqlite/dqlite, but until that happens, I don't think they're of much use in a SaaS, unless it's to be an in process database alternative to Postgres and the likes, where users only access is through something like a rest api.
rqlite author here. I'm actually working to address that. But it does require parsing the SQLite statement before it's sent to the Raft log, and replacing the non-deterministic value with the value evaluated on the leader. It's all solvable (in principle).
benbjohnson 672 days ago [-]
Litestream author here. I'm actually working on a next iteration of Litestream that'll have some configurable guarantees (e.g. synchronous replication, async replication w/ a maximum time window). I could possibly add distributed consensus (e.g. Raft) but I'm not sure how much benefit that would have over rqlite/dqlite.
jansommer 672 days ago [-]
Sounds super cool! Really appreciatw the work you're doing. What I'm imagining in regards to Raft is this:
5 or more servers, each running unikernels in tiny virtual machines, with a JavaScript interpreter or similar to run untrusted user code. Each unikernel is then booted on every request, perhaps with a cool down period before being shut down. This would work similar to GCP, Azure etc. and performance would be great because of how fast unikernels boot.
The unikernels would also be compiled with SQLite, with each database per customer stored on the host, and with restrictions on the virtual machine to only allow access to the database associated with the caller.
Whenever untrusted code is executed, it can insert/update/read/delete from the database on the host. If the host then handled replication, that means I can just shut down the vm when the code has been run, and start a clean one for the next customer. A guarantee that the data have been safely replicated would be essential here, since another server might handle the next request, with the expectation that the data is in a new state.
notforsaleldn 672 days ago [-]
Rendered at 18:09:35 GMT+0000 (Coordinated Universal Time) with Vercel.
(The server seems to be serving with only extremely mild compression, and none on the biggest part, the WASM. Feeding it all through `gzip -9` cuts it to 408 KB, of which 374 KB is the WASM. `brotli` cuts it to 352 KB, of which 323 KB is the WASM. The HTML, CSS and JavaScript are also largely unminified, and even a simple conservative pass roughly halves their gzipped size. No idea of the state of the WASM, I’m not going prodding inside it just now. I know that I’m never impressed with the JavaScript side of the bindings produced by Empscripten, Rust’s wasm-bindgen and any other such tools I’ve seen—with a little manual effort, I’ve very easily reduced 50 KB files—minified!—to under 5 KB. Optimising such things and immediately discarding the results is a strange hobby of mine.)
For comparison, the similar parts of https://sql.js.org/examples/GUI/index.html are a bit under 1.2 MB uncompressed (most notably excluding CodeMirror), around 450 KB on the wire.
> ... I think people underestimate how "lite" sqlite really is:
> 573k for a complete and very fast and transactional SQL database. For comparison, libdb is: > ...more than twice as big, and it doesn't even have an SQL parser in it! Or if you want to be really horrified: > The mysql client library is two megs, and it doesn't even have a database in it! People who think SQL databases are automatically bloated and gross have not yet actually experienced the joys of sqlite. SQL has a well-deserved bad reputation, but sqlite is another story entirely. It's excellent, and much simpler and better written than you'd expect.1: https://redo.readthedocs.io/en/latest/FAQImpl/
In all fairness, though: sqlite3 was much smaller in 2010 ;). Similarly, libdb and libmysqlclient were probably smaller in 2008/2009.
My locally-installed copy of today's trunk version of libsqlite3 (pre-3.39), on x86_64, stripped of debug symbols, is 1.27MB.
https://www.sqlite.org/locrsf.html
There have been a lot of new features over the new years, the latest being window functions. Those features do not impact the database file format.
Embedded systems that don't need these features probably haven't upgraded.
Window functions were added in 3.25, 4 years ago.
Since then sqlite added a bunch of stuff like RETURNING clauses, upserts, json operators (and enabled json functions by default though they’d been there a while), update from, generated columns, …
i don't mind saying, having spent much of the past 2 weeks in and around that code, that much of the generated part of the JS/wasm "glue" is... Much of it looks like it was thrown together by someone who half-understood JS and was just glad that it worked, with little or no attention to detail and refinement. It could use some TLC.
There’s also just a lot of missed opportunity for things like abbreviating identifiers, plus stuff that’s completely normal for optimising compilers like GCC or LLVM like inlining and safe code reordering to eliminate completely unnecessary temporary variables and the likes, but for which absolutely no equivalent tooling exists in JavaScript. And I have no idea why that’s the case. I know of only two even vaguely interesting projects along these general lines: Google’s Closure Compiler (2009– but functionally I don’t think much interesting has happened in the last decade), but it’s too esoteric and requires too many compromises for most people to use it (and it didn’t help that it’s written in Java); and Facebook’s Prepack (2018), which tried doing partial evaluation but they given up on it before it really got anywhere useful. Everything else is just quite hopeless, almost never going beyond very simplistic syntactic transformations that don’t modify semantics.
I don’t know if this is advanced enough to not be completely hopeless but here’s what I see in my webpack config from 2018:
- hoisting and sharing common constant expressions including non-mutated object literals
- module, function, and variable names are all “abbreviated”
- there’s weird tricks of some kind going on for bound method definitions on classes
- Some elimination of “temporary variables” from object properties, arguments, or module imports
I think all those transforms are enough to substantially reduce the code size.
The real optimization like inlining, monomorphizing, hidden classes, etc are best left to the real compiler — which in Chrome/v8’s case is quite competitive with LLVM/GCC.
As the slightest taste of the sort of thing I’m talking of:
• Any sane developer would be happy to rewrite `let x=a.c;a.b(x)` as `a.b(a.c)`, but that changes semantics (accessing a.b and a.c could have side-effects) and so no tooling short of Closure Compiler with appropriate hints is willing to do it. This sort of pattern is extremely common. End result: readable code is bigger than it should be.
• Tools like Terser are, on a good day, willing to inline functions that are used only once; but they only do this on free functions. Use methods, and they’re useless. Again, Closure Compiler with appropriate hints can do a bit better. End result: abstractions and good factoring are expensive, producing bigger and slower code.
• In fact, this generalises: state-of-the-art JavaScript tooling can do a little where everything is on the stack, but use objects and methods and such and they’re stymied. Inlining. Dead code removal. Name mangling. All things that work inside modules, function bodies, &c., but don’t work once you use objects and properties. Closure Compiler is the only tool that can do anything at all here, which is a real shame. Someone really should have built something like it atop TypeScript by now.
Going back a few years to where this was more prevalent and important, there was a significant difference of philosophy between Babel and Bublé, webpack and Rollup. Babel said “compile new constructs into something that does precisely the same thing”, at a significant code size, performance and readability cost, whereas Bublé said “compile new constructs into something small and fast that almost always does the same thing”, at a slight scope and compatibility cost. Webpack similarly did a robotic port of modules at a significant code size, performance and readability cost, whereas Rollup said “let’s unravel the now-unnecessary abstraction and produce efficient code”. In my view, Babel and webpack are profligate, doing the easy thing rather than the sensible thing; and Rollup does the sensible thing, at only a very slight compatibility cost (and Bublé did the mostly sensible thing, though at a much higher compatibility cost, but we don’t need it any more).
JavaScript engines are extraordinarily good at what they do, given the handicap that they start with. But if you can give them better code, they’ll fare better, especially before JITting occurs. I’m talking about giving them better-optimised code. Also about shipping smaller bundles.
Also, partial execution: if you haven’t worked with native code compilation, you might not realise just how good those things are, especially where mathematics is involved. I love the fact that, in benchmarking a Rust Base58 decoder I wrote recently, I had to use test::black_box on the input, or else decode_u64("jpXCZedGfVQ") evaluated to Ok(0xFFFFFFFFFFFFFFFF) at compile time (and it wasn’t even a const fn!). And Rust is only going further with varieties of guaranteed const evaluation. Prepack tried, but Facebook gave up on it for some reason. For me, I really just want something like Zig’s comptime in JavaScript (well, a compile-to-JavaScript JavaScript variant), as guaranteed partial execution.
Thank you, wasm-bindgen is a new term for me (wasm as a whole is new to me since about 2 weeks). i'll add that to the list of tools to check out, as we're actively exploring different options and methodologies at this point for wasm/sqlite.
For myself, I progressively lean in the direction of burning all of these things down (when possible, and it must be admitted that Emscripten’s strength is that it makes legacy stuff work) and writing binding JavaScript manually, also with less of an FFI/dual-sided-bindings/skip-blithely-between-languages flavour and more deliberate, less RPCy techniques.
Yeah, Rust isn't part of the sqlite project's toolchain, but wasm is entirely new to the project and we're eager to learn more about it and to make sure that the JS/wasm code is not only usable but also "good code" (or at least "presentable" code!). Yes, we currently rely on emscripten's generated bits, but i've made an active effort to slowly trim down those dependencies as i get a clearer picture of where the borders between wasm, JS, emscripten, and client code lie. Ideally we wouldn't be dependent on one compiler, but currently we are. Baby steps!
gzip'd it's roughly half of that but that particular web server (althttpd) doesn't (yet?) know how to compress output payloads.
I create two extra files for each static file (for some extensions):
And the web server, depending on the `Accept-Encoding` header, would serve the right file.The wasm module:
This web server (also part of the sqlite family of projects) doesn't yet know how to do that. https://sqlite.org/althttpd
And I had at first run `brotli -9`, being fairly confident in my memory that it had more than nine levels, but finding -h mentioning -# as 1–9, and not seeing --best (level 11) at the other end of the output, and that that’s the default (unlike gzip where I think the default is mostly 6), so that -9 actually makes it do a worse job. Sigh.
(Edit: this was my first-ever HN post and i _thought_ that this comment was going to "stick" up top with the link to serve as an introduction/explanation. Didn't realize that it would be "just another comment.")
How do I make a link in a text submission?
You can't. This is to prevent people from submitting a link with their comments in a privileged position at the top of the page. If you want to submit a link with comments, just submit it, then add a regular comment.
But in this case, Richard Hipp himself should post it as Show HN.
[1] https://news.ycombinator.com/showhn.html
Maybe dang can fix this one?
You can try that out in a REPL here: https://pyodide.org/en/stable/console.html
Or in a full client-side WASM Jupyter notebook interface using https://jupyterlite.readthedocs.io/en/latest/try/labI also used Pyodide to get my Datasette SQLite Python web application running entirely in the browser at https://lite.datasette.io - more details on how that works here: https://simonwillison.net/2022/May/4/datasette-lite/
I'm surprised, I didn't think browsers would receive those detailed keycodes.
`event.keyCode` returns the physical key, e.g. "KeyE"
Assuming this is normal WASM SQLite any persistence will be by flushing to LocalStorage/IndexedDB. There won't be any ACID compliance.
There is a project called "Absurd SQL" to back SQLite with a custom block based FS on top of IndexedDB. It is somewhat absurd but it works incredibly well!
https://github.com/jlongster/absurd-sql
I think combining WASM SQLite with the session extension (https://www.sqlite.org/sessionintro.html) would be a super interesting way to build an eventually consistent datastore for web apps. You could do all transactions locally and sync back and fourth just the changes.
(The fiddle dev here...)
That is, AFAIK, the current state of the art for persisting sqlite wasm-side dbs. The JS-side APIs for providing such support are currently evolving within the browsers. With any luck, we'll have widespread JS-native persistent storage for sqlite within the next couple of/few years. (Those things take time to develop and, just as importantly, propagate through the browser ecosystem.)
> I think combining WASM SQLite with the session extension ... would be a super interesting way to build an eventually consistent datastore for web apps. You could do all transactions locally and sync back and fourth just the changes.
FWIW, that's what i personally envision will likely be the most compelling use case for the sqlite wasm builds. The fiddle app is _just_ the sqlite3 shell, not the sqlite3 library, but we've also created a plain-jane wasm binding of the C library and written an OO-style API on top of that (roughly akin to sql.js and friends, with different design decisions in some places). We're working on an API conceptually similar to sql.js's "worker" API, where the sqlite3 wasm can run in a WebWorker and the main thread can talk to it via WebWorker messages. That said, for the type of jobs sqlite will likely be doing in web clients, the workloads will be fast enough that running the wasm build in the main thread is unlikely to lead to any usability issues (blocked UI). Nobody's going to be using a 20gb db in such an app (because the browser won't let them) and sqlite is blazing fast for small data sets. Even so, the core APIs are agnostic of whether they're running in the UI thread or a WebWorker, so the client can decide for themselves how to plug it in.
Thats interesting, so it will be an "official" WASM JS binding for SQLite? Is it available anywhere?
> With any luck, we'll have widespread JS-native persistent storage for sqlite within the next couple of/few years
If you are building an "official" WASM JS binding have you considered doing something similar to absurd SQL in order to achieve atomic persistence?
That's not guaranteed/official yet but it has been discussed and seems to be where we're headed. In the end it's Richard's decision, though, not mine. (That said, i would like to see that happen but won't be soul-crushed if it doesn't because creating a wasm build of sqlite is _so easy_ to do that any project can create their own with little effort.)
> Is it available anywhere?
Source repo: https://sqlite.org/src
It's currently in the sqlite3 trunk. After configuring the tree, run "make fiddle" or (cd ext/fiddle; make), though the latter approach requires GNU make, not some under-powered make-wannabe. (The top-level makefile is compatible with Lesser Makes.)
Edit: it requires emscripten and ext/fiddle/index.md provides an overview of how to get it going.
> If you are building an "official" WASM JS binding have you considered doing something similar to absurd SQL in order to achieve atomic persistence?
Not until we have JS APIs which are built for that type of thing. Absurd's approach is an interesting proof of concept but is aptly named. Absurd is a stopgap measure which will, with any luck, "soon" be obsoleted by ... how to put this... "less absurd" alternatives ;).
Now imagine if we shipped native sqlite in all browsers, and provided native API for it, we could call it websql or something. Im sure the biggest proponents of open source world wide web would be all for it!
(from memory the was some discussion that it wasn't a completely fair comparison, but even so was still more performant)
Donno about 1987, but everything I do other than locally develop code with emacs has a responsiveness far worse than I remember from 1997. Everything has to load some stupid javascript, everything has a delayed reaction to each click or scroll. Nothing just runs or just works anymore. The cost of a lower barrier to entry for "software" development. :/
Hell I remember waiting almost an hour to download the Spider-Man 2 trailer in 2002. Meanwhile now I’m watching 1080p YouTube videos while running my entire dev environment locally (database, front and backend + code editor) and still it takes a second at the most to load 99% of pages.
I would like to see latency figures for the dominant spreadsheet on the average computer over the years though, starting with VisiCalc on Apple ][.
In what use cases would it make sense to transfer the entire db file over the network, then pluck out the relevant rows into JSON, versus just doing that server side and transfer the JSON back?
Is the trade off that the server does less computation, but then pay for network bandwidth fees?
https://github.com/kristopolous/db.js
Scrolling through the documentation should make some use-cases more obvious. Restructuring, sorting, filtering and modifying data in a fast-enough way that doesn't require thinking hard about things is pretty invaluable.
I was actually just using it earlier this month for the first time in a long while. I had forgotten how nice it was. Really made my job way easier but YMMV.
Lots of dbs are small - a few hundred kb, maybe a meg. Many sites, by comparison, serve _megabytes_ of CSS and/or JS (minified!). i just hit imdb dot com for example's sake: 7MB of Stuff to load the front page, with something like 1MB of it being JS code.
In addition, this type of app permits the user to inject a copy of a client-local db with no network traffic. In developing fiddle we've seen it work with dbs of approximately 80mb (haven't tested anything larger), but those limits are going to be very browser-/environment-dependent.
> Is the trade off that the server does less computation, but then pay for network bandwidth fees?
That's one tradeoff. Another is less coding effort needed to implement the client/server halves of the interaction. In conventional apps, every client-side operation on data results in a call to a server-side counterpart of that client-side operation. That needn't be the case for this style of app. For some apps, all of the code can now be client-side. For some, data might be synced/pushed to the server after changes are made, but there need not be a continual back-and-forth between the client and server.
Another point is data privacy: this sort of app need not send the db to anyone. The db can stay entirely on the client, so they can use private databases without any concern that the server might squirrel away a copy or leak a copy to other folks.
That said: the wasm approach, with a client-embedded db, is most definitely not a one-size-fits-all solution. It will serve a certain niche of applications, but not the majority of web app data needs.
When I first heard about it 10y ago, my boss (web shop) said it "isn't a real database", and that notion prevails in many circles. However more and more web developers recognize its benefits for production use. There are many use cases for it.
Most web projects should default to it IMO, because of its low operational costs and great performance, both in terms speed and reliability. It's incredibly straight forward to use, set up, back up and so on.
As an example: Wordpress, could absolutely default to SQLite as the vast majority of installations run on a single, shared host (apache/litespeed/nginx) and the data model is very simple and almost throughout stringly typed anyways. Running MySQL just adds friction and overhead for these use cases.
It brings me so much joy to see sqlite get the love it deserves -- it's by far the best library I've ever used.
They don't always surge for a reason other than "someone posted something new about it and sent people down rabbit holes"
There have been times I've been talked down to in the past for using SQLite, so I just learned to shut up about it around ideologues. Now the tide is turning when it comes to webscale assumptions, and some of those ideologues' ideas have been proven wrong in many aspects.
SQLite performs well in read-heavy loads, even in web apps with many users, so it can fit those use cases well. I've been using it in a reverse search engine that gets a lot of traffic for going on 8 years now.
https://twitter.com/levelsio/status/1520357256373874688
Dude gets 172 million requests a month.
Though i share that observation, in this particular case it's very likely coincidence. i've worked with Richard since 2008 on his Fossil SCM project, so have been "in that circle" for a long time without having ever actually contributed directly to sqlite. About two weeks ago the topic of wasm came up in a dev chat and it sounded to me like something interesting to experiment with (me being Fossil's "JavaScript Guy"), so i ran with it.
I suspect this fuels some of the hype--trying to correct the misconception.
You have to be able to architect your application to send all write traffic to a single process and have a language that can keep up with your database while doing that. There are "better" architectures for this stuff now that will enable that which have come together in the last few years, and a resurgence in smaller indie projects willing to deploy sqlite on servers and do weird cool things like this fiddle or datasette
Counter-point: sqlite's sister project and SCM, the Fossil SCM, is a self-hosting sqlite client application and has been since 2007. Every hit on the fossil-scm.org website is its own standalone fossil process accessing the same copy of the same db file, all while the developers are actively pushing and pulling changes to/from that same db and while half a dozen or so folks are logged in to its /chat room (each instance of which is long-polling that same db 24/7).
In 14 years of using that db i've encountered _maybe_ two locking errors.
Similarly, sqlite3's own forum is a fossil instance hosting a single sqlite3 repository.
That all writes "have" to be channeled through a single manager is demonstrably not the case.
I've been keeping track of a few things here: https://gist.github.com/fideloper/ac9b81cee85003a59c8ad1a591...
https://sqlite.org/fiddle/fiddle.js
You're welcome :). Frankly, since the HTML5-related JS changes were standardized, jquery is no longer the "absolute must-have" it was prior to that, and i strive to use only vanilla, standards-conforming JS wherever feasible (with diminishingly little tolerance for non-conforming browsers).
Edit: that license disclaimer is the standard one for the sqlite project, though, not mine.
Which is another nice WASM-based browser SQLite user interface.
Thank you for pointing that one out. Every conceptually similar project is a great source of ideas. sqlite's fiddle app is literally less than 2 weeks old so still has lots of room left for feature creep ;).
What makes this different other than coming from the actual sqlite team?
It is trivial to. The only tiny hiccup is that the wasm file cannot be served via a file:// URL due to XHR restrictions, so you'll need a local webserver for it. No big deal, but a slight nuisance. Richard added a new feature to his althttpd web server just to support this very dev effort. From the fiddle source directory, do:
althttpd -page fiddle.html
and it will start up a web server and launch your browser with that page. althttpd only works for Unix-style OSes, but ... that's okay :).
> What I would like to fiddle with are compilation options, extensions, pragmas and their combinations.
The build process for fiddle is literally a single compile command (using the emscripten compiler, emcc), and build time with the -O0 optimization level is fast (a few seconds on my modest i3-based NUC computer), so such fiddling is trivial to arrange for. If you do so, and find anything interesting, please post about it in the sqlite forum.
When iterating on SQL for Beekeeper Studio I've been using https://dbfiddle.uk for a few weeks, it's super good.
Supports PSQL, MySQL, Oracle, etc.
It's a binding of the sqlite3 console app, so that's an appropriate contrast. We're not trying to supplant such services as sqlfiddle and the wild world of wasm is entirely new to everyone currently working on the sqlite3 project, so we still have tons to learn and improve upon.
They're conceptually similar but, while i'm intimately familiar with the fiddle app, i don't know enough about D1 to know _how_ similar they are or are not.
Note, also, that fiddle is analog to the sqlite3 shell app, not the library API. The library API itself is a separate wasm sub-project.
With SQLite 3 Fiddle, pressing Ctrl+Enter executes every single query in the input field. This means you either need to delete old entries and lose your place or manually prefix them with double hyphens. As a nice-to-have feature, I'd love it if the tool followed the approach that Kibana Elasticsearch / DBeaver uses.
With that approach, if text is selected, that text is executed as a query. And, if a cursor is in the middle of a query, only that query is executed instead of the whole thing. To run everything you select it all first (Ctrl+A Ctrl+Enter).
It's nice to be able to iteratively build up a sequence of queries on the input, including creating tables, inserting items, etc. But, I don't even see a manual way to clear the database (which maybe should be a button too?) without refreshing the page... and refreshing the page forgets all of my preferences. (it still manages to keep the query input, it looks like, which is a start... but maybe that's just my browser trying to be helpful.)
That's a good idea. There are tons of options i'd _like_ to add to it but have not simply for UI space's sake. The real limit on the UI is "how many options can we fit while still leaving room for input and output." Nobody involved in the development effort is a particularly strong UI developer (i can say that because i'm the one who wrote that UI ;), and assistance in prettying it up and improving the U/X would certainly be appreciated.
> But, I don't even see a manual way to clear the database without refreshing the page... and refreshing the page forgets all of my preferences.
Storing of the preferences in localStorage is on my TODO list. The underlying mini-API for it is in place, i just haven't yet dedicated the few hours to plug it all in and test it. Baby steps.
> (it still manages to keep the query input, it looks like, which is a start... but maybe that's just my browser trying to be helpful.)
That it keeps the query input is a _browser-specific quirk_, not an explicit feature. By and large, that quirk (Firefox, right?) is a huge pain in the butt in web development because it forces the developer to do a full reload on each hit, bypassing all caching.
Yep, definitely Firefox. In this case, my first thought was that the webpage was "doing the right thing", but then I realized the browser was probably doing it. Either way, it is the behavior I would expect in this case, so I consider that a win, but I understand it can be challenging for web developers under other circumstances.
> assistance in prettying it up and improving the U/X would certainly be appreciated.
My understanding is that the SQLite team typically isn't very open to outside contribution. UI/UX historically isn't my strong suite either, though, but I have been trying to work on that lately.
To be clear, i wasn't badmouthing FF (all of fiddle's develoment so far, aside from occasional individual tests) has been in FF on Linux. That one particular FF feature kinda gets my goat, though, when i'm writing web apps ;).
> My understanding is that the SQLite team typically isn't very open to outside contribution.
It's not the contribution, per se, but actual code patches are tricky for the sqlite project because of licensing. sqlite is released into the public domain by its creators, but not all legal jurisdictions recognize public domain as a real thing. Thus Richard is extra-extra-careful to ensure that all of the code which goes in to the repository is added by someone who's signed a waiver validating that any code they added is not going to be a licensing issue, and only people Richard has come to know and trust are offered the option of signing that waiver. Often, when patches are posted by users, they can be used as a basis for equivalent patches but cannot be used as-is because of the potential for licensing fallout.
After experimenting, i'm extremely hesitant to add the option to _automatically_ nuke the db each time input is submitted because, frankly, That Way Lies Madness and you are probably the only person who would use it. However, the app now has a button to nuke the db.
In related news: if any text in the input field is currently highlighted, only that text is submitted via the Run button or ctrl-/shift-enter.
That's not yet (as of this writing) deployed but will be the next time Richard updates the site.
It’s probably a completely different way to use the application than you were considering if you don’t see the point of it, but I’ve actually used another tool exactly like this in a job interview that involved testing SQL knowledge.
Would it be useful for manipulating an existing SQLite database? Probably not. (Unless it reset to the state the database had when you imported it, instead of clearing it? It might work…)
> However, the app now has a button to nuke the db.
Awesome!
> In related news: if any text in the input field is currently highlighted, only that text is submitted via the Run button or ctrl-/shift-enter.
That would certainly be helpful towards the use case I’m describing, although I can imagine that UX being difficult to discover on purpose (and confusingly easy to discover on accident when you get weird errors as SQLite tries in vain to execute some invalid SQL fragment you accidentally had selected before you hit the run button).
This app's design space is exposing the sqlite3 shell app via the web, not to emulate every other fiddle environment. sqlite shell app's doesn't (with very good reason) behave that way and the fiddle frontend is just passing state between the user and that app.
> The idea is to let you build up a reproducible, shareable outcome.
Because this app has no server-side state, we have no way of _sharing_ the outcome. Large fiddle sites give you a short URL which refers to state in their server-side storage. This app completely lacks that (and we're definitely not going to add server-side state to this app). What we _might_ eventually do is offer the ability to base64-encode the SQL into a URL argument which can be shared, but URL length limits are very server-dependent so it would be impossible to portably share "large" fiddles that way.
> (and confusingly easy to discover on accident when you get weird errors as SQLite tries in vain to execute some invalid SQL fragment you accidentally had selected before you hit the run button).
There's a comment in the initial example SQL explaining that feature, so it'll _hopefully_ be noticed by people new to the app. (Noting that that's not, as of this writing, yet deployed on the production server.) Another feature under consideration is having it intermittently spit out random tips/reminders to the output area (when the db is not working - the app knows when that's happening), and the exec-selection feature would be one such tip.
This is just to clarify GP's perspective, I understand that you may be going more for "instant local sqlite repl environment" than "playground to develop shareable snippets of sqlite commands".
There's no way to know what the db's state was when it was imported without literally keeping a second copy of the whole db solely for comparison's sake, then writing the code to perform the comparison. In the browser storage space, how much space is available for storing such copies is unknown (an unknowable, AFAIK), so we can't simply keep extra copies willy-nilly like we possibly could in an out-of-browser client-side app.
That said: a user can start their session with "BEGIN" and use "ROLLBACK" to revert to the original state.
The transaction would be left open until the user either decides to re-run the (presumably modified) query input (causing a rollback first) or to (presumably) end the session by downloading the database (causing a commit first). This behavior would only apply in the auto-resetting “fiddle mode”, of course.
Anyways, it was just an idea I was thinking about. I’m sure this idea is not flawless, and you probably have other, higher priority features in mind.
What’s a good tutorial for someone who knows how to program but doesn’t know the various SQL commands and gotchas?
That's the best little book that will teach you the most.
And yet very few developers would want to go back to the days when we had no syntax highlighting and editors couldn't hold more than 64kb of text at a time and the OS could only run one thing at a time. Times change (thankfully). The 64kb machines now live in the domain of hobby projects, not machines for "getting things done." (There's a rather active group of folks who still hack on the C64, C128, and similar machines. Search for the "MEGA65" to find a recent (late 2021/early 2022) machine which was built solely for that crowd.)
Syntax highlighting, multi-megabyte file editing, and multitasking were all standard on ordinary software development PCs twenty years ago; perhaps closer to twenty-five.
And those ordinary software development PCs had eight, sixteen or thirty-two MEGAbytes of RAM -- not gigabytes, like today. If you're saying syntax highlighting, multi-megabyte file editing, and multitasking are the reason we need three orders of magnitude more RAM to get the machine to do the same thing (only often more slowly) now as then, you're either selling something or you just don't know what you're talking about.
There's no need to be combative. i've been programming since the 80s (mid-90s professionally), and in _no way_ long to return to those days. It was fun while it lasted, but the dev tools, environments, and targets we have nowadays are a lot of fun in their own ways too, and many (perhaps most) of them wouldn't be possible/feasible with 16MB of memory on a 120mhz PC.
i'm selling nothing but the idea (for $0) that people expect far more from software now than then, and there's no way to run a modern app in such a limited environment.
Yeah, sorry, didn't mean to be more combative than that.
The idea I'm giving away is that 99.9% of the stuff people expect[1] from software nowadays is just plain unnecessary. Actually detrimental, in many (most?) cases: It just complicates things.
___
[1]: Or perhaps, I suspect, that software developers nowadays just think people expect from software, so they provide it without even reflecting whether it's really necessary at all.
For the most part i blame that on the marketing folks rather than developers, but FOSS projects like this one have no marketing folks who i can push the blame off to ;).
But with harddisks and bandwidth (aside from mobile) being limitless we just decided to stop bothering. The only device on which I am remotely space constraint is my MacBook. And that is mostly apple business decision.
Not quite yet: indexeddb can be stored persistently whereas wasm-side sqlite dbs cannot (unless they're stored _in_ an indexeddb). Someday the JS filesystem APIs may offer such a thing, but right now there is no standard filesystem-like API which offers client-side persistence. The only half-workaround for the time being is the ability to upload and download the db image (something both sql.js and sqlite3's fiddle support).
Depending on how it's compiled, yes. It can be as large as 2MB when built with no optimizations.
That particular one is built with -Oz (optimized for the smallest size). Development itself is done with -O0 (no optimizations) because the compile time difference is something like 2-3 seconds vs 15-20.
Honestly thats really impressive given it includes an entire SQL engine.
My almost-most-used website (boardgamegeek.com) currently serves 500-odd kb of minified CSS and 2MB of minified JS. That is to say: in the context of modern websites, 300-800kb arguably rates as a mere pittance.
Ideas for future improvement:
Heh.On my dev system i have a build of fiddle which uses the jquery.terminal plugin as the main interface (with a button to toggle between it and the current UI), but it relies on 3rd-party code which is not approved for inclusion into the core sqlite3 repository (plus it's huge: all of the competent terminal-like APIs we evaluated so far bring 300-500kb of minimized JS dependencies). Reimplementing such a console, minus the jquery/etc dependencies, is beyond my current ambitions but has not been ruled out entirely long-term. Baby steps.
> bundle various $EDITOR choices
That would require a _proper_ terminal for those editors to live in, as opposed to a terminal lookalike such as jquery.terminal. xterm.js could presumably do it but is even bigger than jquery.terminal. No doubt that's _possible_ in wasm, but such features are _way_ beyond any current aspirations or scope for this app.
Though i'd very much like to see such a thing, that's well, well, _well_ outside the scope of this project. If someone wants to create such a fiddle environment, _fantastic_, and i'll support them in any way i can in terms of necessary tweaks to the underlying fiddle-related code, but the surrounding bits would be someone else's itch to scratch.
I’m currently on mobile, but was excited when `shift + enter` still executed the sql!
You got lucky - that was untested on non-keyboarded machines ;). There's a "run" button at the bottom intended primarily for mobile use BUT the UI is definitely not well-optimized for mobile devices yet. They are a target, certainly, but optimizing for them is well down the TODO list.
Ideally someone would implement replication as a separate process, like litestream but with better guarantees like rqlite/dqlite, but until that happens, I don't think they're of much use in a SaaS, unless it's to be an in process database alternative to Postgres and the likes, where users only access is through something like a rest api.
[0] https://www.sqlite.org/deterministic.html
5 or more servers, each running unikernels in tiny virtual machines, with a JavaScript interpreter or similar to run untrusted user code. Each unikernel is then booted on every request, perhaps with a cool down period before being shut down. This would work similar to GCP, Azure etc. and performance would be great because of how fast unikernels boot.
The unikernels would also be compiled with SQLite, with each database per customer stored on the host, and with restrictions on the virtual machine to only allow access to the database associated with the caller.
Whenever untrusted code is executed, it can insert/update/read/delete from the database on the host. If the host then handled replication, that means I can just shut down the vm when the code has been run, and start a clean one for the next customer. A guarantee that the data have been safely replicated would be essential here, since another server might handle the next request, with the expectation that the data is in a new state.