[HN Gopher] SQLite 3 Fiddle
___________________________________________________________________
SQLite 3 Fiddle
Author : sgbeal
Score : 734 points
Date : 2022-05-26 14:18 UTC (1 days 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:
| This lets you fiddle around with SQLite from the browser
| 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!
| jitl wrote:
| > 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 wrote:
| 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 Buble, 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 Buble
| 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 Buble 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 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, ...
| aidenn0 wrote:
| Just checked on my system: 1.9MB for libdb-5 vs 1.2MB for
| libsqlite3 on My Machine(TM)
| [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 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 wrote:
| 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 wrote:
| > 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 wrote:
| "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 wrote:
| > (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 wrote:
| 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.
| 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.
| sgbeal wrote:
| > 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.
| 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
| sgbeal wrote:
| > 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.
| 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.
| edgyquant wrote:
| 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 wrote:
| In my view, you should allow candidates to work on an
| assignment of your choosing to be handed in by a deadline
| you set.
| newbieuser wrote:
| anyone running a large scale saas job with rqlite/dqlite?
| jansommer wrote:
| 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.
|
| [0] https://www.sqlite.org/deterministic.html
| benbjohnson wrote:
| 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 wrote:
| 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.
| otoolep wrote:
| 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).
| 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).
| CRConrad wrote:
| Store it in a _really_ big cookie? ;-)
| 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.
| edgyquant wrote:
| To be fair, during that time the NoSQL crowd was saying that
| about all relational databases.
| bdlowery wrote:
| "Sqlite doesn't scale"
|
| https://twitter.com/levelsio/status/1520357256373874688
|
| Dude gets 172 million requests a month.
| 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.
| rrix2 wrote:
| 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 wrote:
| > 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.
| 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.")
| [deleted]
| [deleted]
| 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.
| Abishek_Muthian wrote:
| 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.
|
| [1] https://news.ycombinator.com/showhn.html
| 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.
| CRConrad wrote:
| Pretty much precisely that question got a few answers
| (including one from me) here just a couple of weeks ago:
| https://news.ycombinator.com/item?id=31355809
| tlhunter wrote:
| 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).
| 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.)
| CRConrad wrote:
| > 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 wrote:
| > 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 wrote:
| 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 wrote:
| > 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
| ;).
| 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?
| ogogmad wrote:
| 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.
| 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?
| loxias wrote:
| > 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 wrote:
| 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.
| 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 ][.
| rasz wrote:
| > 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!
| alephnan wrote:
| > 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?
| edgyquant wrote:
| Not all applications need a centralized DB and even those
| that do likely have client only data.
| kristopolous wrote:
| Not the use-case. Here's a javascript database I made between
| 2011 and 2016 that is pretty web use-case specific.
|
| 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.
| sgbeal wrote:
| > 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.
| 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.
| sgbeal wrote:
| > 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 wrote:
| 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.
| notforsaleldn wrote:
___________________________________________________________________
(page generated 2022-05-27 23:02 UTC)