[HN Gopher] SQLite 3 Fiddle
___________________________________________________________________
SQLite 3 Fiddle
Author : sgbeal
Score : 564 points
Date : 2022-05-26 14:18 UTC (8 hours ago)
(HTM) web link (sqlite.org)
(TXT) w3m dump (sqlite.org)
| tiffanyh wrote:
| 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 wrote:
| > 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.
| SahAssar wrote:
| 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 wrote:
| 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.
| pjot wrote:
| Very cool!
|
| I'm currently on mobile, but was excited when `shift + enter`
| still executed the sql!
| sgbeal wrote:
| > 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 wrote:
| chrismorgan wrote:
| 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.
| sgbeal wrote:
| > 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 wrote:
| 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.
| easrng wrote:
| 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 wrote:
| > 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 wrote:
| 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 wrote:
| > 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!
| sgbeal wrote:
| > 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 wrote:
| 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 wrote:
| > 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 wrote:
| 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.
| aidenn0 wrote:
| 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:
| root root 573376 2010-10-20 09:55 /usr/lib/libsqlite3.so.0.8.6
|
| > 573k for a complete and very fast and transactional SQL
| database. For comparison, libdb is: root root
| 1256548 2008-09-13 03:23 /usr/lib/libdb-4.6.so
|
| > ...more than twice as big, and it doesn't even have an SQL
| parser in it! Or if you want to be really horrified:
| root root 1995612 2009-02-03 13:54
| /usr/lib/libmysqlclient.so.15.0.0
|
| > 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/
| sgbeal wrote:
| > root root 573376 2010-10-20 09:55
| /usr/lib/libsqlite3.so.0.8.6
|
| 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.
| chasil wrote:
| You can keep using those. The database format is defined as
| a long-term storage format by the U.S. Library of Congress.
|
| 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.
| masklinn wrote:
| > 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, ...
| [deleted]
| coder543 wrote:
| 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 wrote:
| > 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 wrote:
| > 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.
| yread wrote:
| 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
| simonw wrote:
| 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.
|
| You can try that out in a REPL here:
| https://pyodide.org/en/stable/console.html
| import sqlite3 db = sqlite3.connect(":memory:")
| db.execute("select 4 * 5").fetchall()
|
| Or in a full client-side WASM Jupyter notebook interface using
| https://jupyterlite.readthedocs.io/en/latest/try/lab
|
| I 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/
| g3 wrote:
| Or, if you want to go one level deeper, v86 has a complete
| Linux environment with both python and sqlite running locally:
| https://copy.sh/v86/?profile=archlinux&c=python%20-ic%20%27i...
| kzrdude wrote:
| 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 wrote:
| `event.key` returns the key with the mappings, e.g. "E"
|
| `event.keyCode` returns the physical key, e.g. "KeyE"
| kungfufrog wrote:
| Holy smokes, all of this is very cool! Nice work!
| netcraft wrote:
| see also https://dbfiddle.uk/?rdbms=sqlite_3.27
| Kudovs wrote:
| I can see this being useful for technical interviews and quick
| demos.
| niek_pas wrote:
| Please don't interview potential hires by having them do work
| while you look over their shoulder.
| frakkingcylons wrote:
| Pair programming in an interview is one of the most useful
| ways to evaluate candidates in my experience.
| newbieuser wrote:
| anyone running a large scale saas job with rqlite/dqlite?
| status200 wrote:
| 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
| stefan_ wrote:
| We can finally get rid of IndexedDB, and it's only 900KiB extra
| bundle size!
| sgbeal wrote:
| > 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 wrote:
| For production use, presumably you'd use an optimized binary
| sgbeal wrote:
| > 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 wrote:
| 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 wrote:
| > 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 wrote:
| Are you suggesting IndexedDB has a limited life remaining?
| sgbeal wrote:
| > 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).
| xwowsersx wrote:
| 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?
| ludwigvan wrote:
| Probably a reaction to the ever growing complexity of software
| systems.
| fideloper wrote:
| Lots of new stuff happening lately, yep! Mostly around
| distributed systems?
|
| I've been keeping track of a few things here:
| https://gist.github.com/fideloper/ac9b81cee85003a59c8ad1a591...
| matthewaveryusa wrote:
| 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.
| dinvlad wrote:
| 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 :-)
| rzzzt wrote:
| 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
| dgb23 wrote:
| 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.
| Jarwain wrote:
| 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 wrote:
| 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.
| IshKebab wrote:
| 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.
| sgbeal wrote:
| > ... 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.
| krferriter wrote:
| 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 wrote:
| 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 wrote:
| Thanks. I thought that may have been the case, but I wasn't
| sure if there were also some recent improvements or
| developments.
| srcreigh wrote:
| 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.
| yurivish wrote:
| See also: http://sqlime.org
|
| Which is another nice WASM-based browser SQLite user interface.
| sgbeal wrote:
| > ... 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 ;).
| sgbeal wrote:
| 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 wrote:
| 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.
| solarkraft wrote:
| It's just another comment, but as a valuable one it is certain
| to be featured prominently through votes.
| vkoskiv wrote:
| For me when I post with both a link and a text body, the text
| part sticks to the top.
|
| Maybe dang can fix this one?
| frosted-flakes wrote:
| It's intentional. You can post a link _or_ a text post. Text
| posts can only include links if it 's a Show HN post.
| larwent wrote:
| The exception to this appears to be "Show HN" posts. E.g.
| https://news.ycombinator.com/item?id=31516108
| forrestthewoods wrote:
| 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?
| enjalot wrote:
| some tutorials i've collected recently * https://mode.com/sql-
| tutorial/sql-joins/ * https://github.com/NUKnightLab/sql-
| mysteries * https://selectstarsql.com/ * https://sqlbolt.com/ *
| https://www.craft.do/s/VzvaPiNX6jvxX5
| krylon wrote:
| https://sqlbolt.com/ might be a good starting point.
| cryptonector wrote:
| https://www.oreilly.com/library/view/sql-pocket-guide/978149...
|
| That's the best little book that will teach you the most.
| alephnan wrote:
| Happy to see some zero-dependency VanillaJS.
|
| https://sqlite.org/fiddle/fiddle.js 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 wrote:
| > 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.
| rathboma wrote:
| 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 wrote:
| > 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 wrote:
| Yep! It's a great project whatever one calls it :-).
| dinvlad wrote:
| 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.
| ReptileMan wrote:
| 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.
| sgbeal wrote:
| > 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.)
| dinvlad wrote:
| Sadly so :-) I felt much more productive coding without all
| those bells and whistles than nowadays - I wonder why..
| polishdude20 wrote:
| 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?
| longrod wrote:
| SQLite is one of the most underrated databases around and it's
| sometimes baffling to know how much software actually relies on
| it.
| samwillis wrote:
| 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!
|
| 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.
| sgbeal wrote:
| > 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 wrote:
| > 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 wrote:
| > 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.)
|
| > 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 ;).
| mrtesthah wrote:
| 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?
| samwillis wrote:
| The strange thing is that AbsurdSQL appears to be 10x more
| performant than IndexedDB:
| https://github.com/jlongster/absurd-sql#performance
|
| (from memory the was some discussion that it wasn't a
| completely fair comparison, but even so was still more
| performant)
| robocat wrote:
| 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 ][.
| cryptonector wrote:
| Nice!
|
| Ideas for future improvement: - make the shell
| prompt interactive - bundle various $EDITOR choices
|
| Heh.
| sgbeal wrote:
| > 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 wrote:
| What you do is build a very cut-down Linux system, bubybox
| style, as wasm, and deliver that.
___________________________________________________________________
(page generated 2022-05-26 23:00 UTC)