11 ms·

SQLite 3 Fiddle

765 points by sgbeal 4y ago

sgbeal 4y ago
Richard Hipp, of sqlite fame, just announced the project's new WASM-based "fiddle" app in the sqlite forum (https://sqlite.org/forum/forumpost/5cfd681451 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.")
- vkoskiv 4y ago
  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 4y ago
    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 4y ago
      The exception to this appears to be "Show HN" posts. E.g. https://news.ycombinator.com/item?id=31516108 https://news.ycombinator.com/item?id=31516108
- tinus_hn 4y ago
  This is from the FAQ: How do I make a link in a text submission? You can't. This is to prevent people from submitting a link with their comments in a privileged position at the top of the page. If you want to submit a link with comments, just submit it, then add a regular comment.
  - Abishek_Muthian 4y ago
    You can do so with Show HN, Although the faq[1] doesn't explicitly state that. But in this case, Richard Hipp himself should post it as Show HN. [1] https://news.ycombinator.com/showhn.html https://news.ycombinator.com/showhn.html
- solarkraft 4y ago
  It's just another comment, but as a valuable one it is certain to be featured prominently through votes.
- deleted 4y ago
  [deleted]
- deleted 4y ago
  [deleted]
Kudovs 4y ago
I can see this being useful for technical interviews and quick demos.
- niek_pas 4y ago
  Please don't interview potential hires by having them do work while you look over their shoulder.
  - frakkingcylons 4y ago
    Pair programming in an interview is one of the most useful ways to evaluate candidates in my experience.
  - edgyquant 4y ago
    So algorithmic white board interviews are bad because they aren’t a measure of day to day productivity and now programming interviews are bad because reasons. How should I determine if engineers are a good fit then?
    - niek_pas 4y ago
      In my view, you should allow candidates to work on an assignment of your choosing to be handed in by a deadline you set.
status200 4y ago
Looks like a great sandbox, perfect for testing ideas and queries without having to spin up an environment or log in to something like Big Query
stefan_ 4y ago
We can finally get rid of IndexedDB, and it's only 900KiB extra bundle size!
- sgbeal 4y ago
  > and it's only 900KiB extra bundle size! Depending on how it's compiled, yes. It can be as large as 2MB when built with no optimizations.
  - mwint 4y ago
    For production use, presumably you’d use an optimized binary
    - sgbeal 4y ago
      > For production use, presumably you’d use an optimized binary That particular one is built with -Oz (optimized for the smallest size). Development itself is done with -O0 (no optimizations) because the compile time difference is something like 2-3 seconds vs 15-20.
- josephg 4y ago
  330kb over the wire with brotli. Thats sizable, but definitely usable in a heavier web app. Honestly thats really impressive given it includes an entire SQL engine.
  - sgbeal 4y ago
    > 330kb over the wire with brotli. Thats sizable, but definitely usable in a heavier web app. My almost-most-used website (boardgamegeek.com) currently serves 500-odd kb of minified CSS and 2MB of minified JS. That is to say: in the context of modern websites, 300-800kb arguably rates as a mere pittance.
- sgbeal 4y ago
  > We can finally get rid of IndexedDB, Not quite yet: indexeddb can be stored persistently whereas wasm-side sqlite dbs cannot (unless they're stored _in_ an indexeddb). Someday the JS filesystem APIs may offer such a thing, but right now there is no standard filesystem-like API which offers client-side persistence. The only half-workaround for the time being is the ability to upload and download the db image (something both sql.js and sqlite3's fiddle support).
tiffanyh 4y ago
Given that this SQLite variant is WASM-based, how does this relate to Cloudflare recent D1 announcement where you can use Workers (WASM) to connect to an SQLite datastore?
- sgbeal 4y ago
  > Given that this SQLite variant is WASM-based, how does this relate to Cloudflare recent D1 announcement where you can use Workers (WASM) to connect to an SQLite datastore? They're conceptually similar but, while i'm intimately familiar with the fiddle app, i don't know enough about D1 to know _how_ similar they are or are not. Note, also, that fiddle is analog to the sqlite3 shell app, not the library API. The library API itself is a separate wasm sub-project.
SahAssar 4y ago
Hasn't sql.js (sqlite compiled to wasm/js with emscripten) been around for a long time (at least a couple of years)? What makes this different other than coming from the actual sqlite team?
- sgbeal 4y ago
  sql.js is quite similar but not quite the same thing. They produce a high-level wrapper of the C API, but they do not offer a binding of the shell app (which is what fiddle is). The latter requires, due to how emscripten wraps the C function fgets(), hacking the shell in order to get sane input semantics. sql.js has, however, been a great point of reference and inspiration in building fiddle.
chrismorgan 4y ago
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 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 4y ago
  > 898KB even without compression. Pretty good. gzip'd it's roughly half of that but that particular web server (althttpd) doesn't (yet?) know how to compress output payloads.
  - motiejus 4y ago
    It makes sense to pre-compress the static assets. Then the server can load the compressed ones directly, without requiring to link to the compressor, and of course pay the price of online compression. I create two extra files for each static file (for some extensions): file file.gz <-- with zopfli file.br <-- with brotli And the web server, depending on the `Accept-Encoding` header, would serve the right file. The wasm module: $ ls -lh fiddle-module.wasm* -rw-r--r-- 1 motiejus motiejus 779K May 26 15:25 fiddle-module.wasm -rw-r--r-- 1 motiejus motiejus 323K May 26 15:25 fiddle-module.wasm.br -rw-r--r-- 1 motiejus motiejus 357K May 26 18:00 fiddle-module.wasm.gz
    - sgbeal 4y ago
simonw 4y ago
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 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 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 https://lite.datasette.io - more details on how that works here: https://simonwillison.net/2022/May/4/datasette-lite/ https://simonwillison.net/2022/May/4/datasette-lite/
- kungfufrog 4y ago
  Holy smokes, all of this is very cool! Nice work!
- g3 4y ago
  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%27import%20sqlite3;%20db%20=%20sqlite3.connect(%22:memory:%22);%20print(db.execute(%22select%204%20*%205%22).fetchall())%27 https://copy.sh/v86/?profile=archlinux&c=python%20-ic%20%27i...
  - kzrdude 4y ago
    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 4y ago
      `event.key` returns the key with the mappings, e.g. "E" `event.keyCode` returns the physical key, e.g. "KeyE"
xwowsersx 4y ago
I've noticed a large number of stories on HN related to SQLite over the past few weeks. Maybe it's just random or I'm only now just noticing it, but is there some renewed/newfound interest in SQLite lately? If so, what's behind that?
- Jarwain 4y ago
  I find topics tend to surge and wane, and sqlite is a popular one. They don't always surge for a reason other than "someone posted something new about it and sent people down rabbit holes"
- IshKebab 4y ago
  Yeah definitely renewed interest. I think there's been a collective realisation that it's pretty good, and it has received some notable new features recently like JSON queries and proper type checking.
- sgbeal 4y ago
  > ... is there some renewed/newfound interest in SQLite lately? If so, what's behind that? Though i share that observation, in this particular case it's very likely coincidence. i've worked with Richard since 2008 on his Fossil SCM project, so have been "in that circle" for a long time without having ever actually contributed directly to sqlite. About two weeks ago the topic of wasm came up in a dev chat and it sounded to me like something interesting to experiment with (me being Fossil's "JavaScript Guy"), so i ran with it.
- krferriter 4y ago
  I think some people are realizing that SQLite has everything they need in their database use case, and they don't need a heavier, more complicated client-server database with more features.
  - bob1029 4y ago
    This is exactly the reason. Some of us went off the beaten path and proved that you can actually put many users on a carefully-tuned SQLite instance (despite their official docs at the time). In many cases, you are able to exceed the capabilities of a one-node hosted DB solution due to latency reduction of in-process happiness.
alephnan 4y ago
Happy to see some zero-dependency VanillaJS. https://sqlite.org/fiddle/fiddle.js 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 4y ago
  > Happy to see some zero-dependency VanillaJS. You're welcome :). Frankly, since the HTML5-related JS changes were standardized, jquery is no longer the "absolute must-have" it was prior to that, and i strive to use only vanilla, standards-conforming JS wherever feasible (with diminishingly little tolerance for non-conforming browsers). Edit: that license disclaimer is the standard one for the sqlite project, though, not mine.
yurivish 4y ago
See also: http://sqlime.org http://sqlime.org Which is another nice WASM-based browser SQLite user interface.
- sgbeal 4y ago
  > ... another nice WASM-based browser SQLite user interface. Thank you for pointing that one out. Every conceptually similar project is a great source of ideas. sqlite's fiddle app is literally less than 2 weeks old so still has lots of room left for feature creep ;).
dinvlad 4y ago
This throws me back to the good ol' days of fitting all software in a few dozens/hundreds of KBs on Speccy and similar systems. Current software engineering practices are so wasteful on computer resources, and imho unnecessarily so, when we keep in mind what was possible on those systems with 1000x less compute power decades ago.
- ReptileMan 4y ago
  My little pony: linking is magic. But with harddisks and bandwidth (aside from mobile) being limitless we just decided to stop bothering. The only device on which I am remotely space constraint is my MacBook. And that is mostly apple business decision.
- sgbeal 4y ago
  > This throws me back to the good ol' days of fitting all software in a few dozens/hundreds of KBs on Speccy and similar systems. And yet very few developers would want to go back to the days when we had no syntax highlighting and editors couldn't hold more than 64kb of text at a time and the OS could only run one thing at a time. Times change (thankfully). The 64kb machines now live in the domain of hobby projects, not machines for "getting things done." (There's a rather active group of folks who still hack on the C64, C128, and similar machines. Search for the "MEGA65" to find a recent (late 2021/early 2022) machine which was built solely for that crowd.)
  - dinvlad 4y ago
    Sadly so :-) I felt much more productive coding without all those bells and whistles than nowadays - I wonder why..
  - CRConrad 4y ago
    > And yet very few developers would want to go back to the days when we had no syntax highlighting and editors couldn't hold more than 64kb of text at a time and the OS could only run one thing at a time. Syntax highlighting, multi-megabyte file editing, and multitasking were all standard on ordinary software development PCs twenty years ago; perhaps closer to twenty-five. And those ordinary software development PCs had eight, sixteen or thirty-two MEGAbytes of RAM -- not gigabytes, like today. If you're saying syntax highlighting, multi-megabyte file editing, and multitasking are the reason we need three orders of magnitude more RAM to get the machine to do the same thing (only often more slowly) now as then, you're either selling something or you just don't know what you're talking about.
forrestthewoods 4y ago
Confession: I have 15 years dev experience and have never written a single line of SQL code. What’s a good tutorial for someone who knows how to program but doesn’t know the various SQL commands and gotchas?
- enjalot 4y ago
  some tutorials i've collected recently * https://mode.com/sql-tutorial/sql-joins/ https://mode.com/sql-tutorial/sql-joins/ * https://github.com/NUKnightLab/sql-mysteries https://github.com/NUKnightLab/sql-mysteries * https://selectstarsql.com/ https://selectstarsql.com/ * https://sqlbolt.com/ https://sqlbolt.com/ * https://www.craft.do/s/VzvaPiNX6jvxX5 https://www.craft.do/s/VzvaPiNX6jvxX5
- krylon 4y ago
  https://sqlbolt.com/ https://sqlbolt.com/ might be a good starting point.
- cryptonector 4y ago
  https://www.oreilly.com/library/view/sql-pocket-guide/9781492090397/ https://www.oreilly.com/library/view/sql-pocket-guide/978149... That's the best little book that will teach you the most.
- CRConrad 4y ago
  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 https://news.ycombinator.com/item?id=31355809
rathboma 4y ago
This is nice, but more of a console than a fiddle. When iterating on SQL for Beekeeper Studio I've been using https://dbfiddle.uk https://dbfiddle.uk for a few weeks, it's super good. Supports PSQL, MySQL, Oracle, etc.
- sgbeal 4y ago
  > This is nice, but more of a console than a fiddle. It's a binding of the sqlite3 console app, so that's an appropriate contrast. We're not trying to supplant such services as sqlfiddle and the wild world of wasm is entirely new to everyone currently working on the sqlite3 project, so we still have tons to learn and improve upon.
  - rathboma 4y ago
    Yep! It's a great project whatever one calls it :-).
samwillis 4y ago
This awesome, I'm convinced WASM SQLite is the future of in-browser strorage for web apps/PWAs. Assuming this is normal WASM SQLite any persistence will be by flushing to LocalStorage/IndexedDB. There won't be any ACID compliance. There is a project called "Absurd SQL" to back SQLite with a custom block based FS on top of IndexedDB. It is somewhat absurd but it works incredibly well! https://github.com/jlongster/absurd-sql https://github.com/jlongster/absurd-sql I think combining WASM SQLite with the session extension (https://www.sqlite.org/sessionintro.html 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 4y ago
  > There is a project called "Absurd SQL" to back SQLite with a custom block based FS on top of IndexedDB. It is somewhat absurd but it works incredibly well! (The fiddle dev here...) That is, AFAIK, the current state of the art for persisting sqlite wasm-side dbs. The JS-side APIs for providing such support are currently evolving within the browsers. With any luck, we'll have widespread JS-native persistent storage for sqlite within the next couple of/few years. (Those things take time to develop and, just as importantly, propagate through the browser ecosystem.) > I think combining WASM SQLite with the session extension ... would be a super interesting way to build an eventually consistent datastore for web apps. You could do all transactions locally and sync back and fourth just the changes. FWIW, that's what i personally envision will likely be the most compelling use case for the sqlite wasm builds. The fiddle app is _just_ the sqlite3 shell, not the sqlite3 library, but we've also created a plain-jane wasm binding of the C library and written an OO-style API on top of that (roughly akin to sql.js and friends, with different design decisions in some places). We're working on an API conceptually similar to sql.js's "worker" API, where the sqlite3 wasm can run in a WebWorker and the main thread can talk to it via WebWorker messages. That said, for the type of jobs sqlite will likely be doing in web clients, the workloads will be fast enough that running the wasm build in the main thread is unlikely to lead to any usability issues (blocked UI). Nobody's going to be using a 20gb db in such an app (because the browser won't let them) and sqlite is blazing fast for small data sets. Even so, the core APIs are agnostic of whether they're running in the UI thread or a WebWorker, so the client can decide for themselves how to plug it in.
cryptonector 4y ago
Nice! Ideas for future improvement: - make the shell prompt interactive - bundle various $EDITOR choices Heh.
- sgbeal 4y ago
  > make the shell prompt interactive On my dev system i have a build of fiddle which uses the jquery.terminal plugin as the main interface (with a button to toggle between it and the current UI), but it relies on 3rd-party code which is not approved for inclusion into the core sqlite3 repository (plus it's huge: all of the competent terminal-like APIs we evaluated so far bring 300-500kb of minimized JS dependencies). Reimplementing such a console, minus the jquery/etc dependencies, is beyond my current ambitions but has not been ruled out entirely long-term. Baby steps. > bundle various $EDITOR choices That would require a _proper_ terminal for those editors to live in, as opposed to a terminal lookalike such as jquery.terminal. xterm.js could presumably do it but is even bigger than jquery.terminal. No doubt that's _possible_ in wasm, but such features are _way_ beyond any current aspirations or scope for this app.
  - cryptonector 4y ago
    What you do is build a very cut-down Linux system, bubybox style, as wasm, and deliver that.
    - sgbeal 4y ago
      > What you do is build a very cut-down Linux system, bubybox style, as wasm, and deliver that. Though i'd very much like to see such a thing, that's well, well, _well_ outside the scope of this project. If someone wants to create such a fiddle environment, _fantastic_, and i'll support them in any way i can in terms of necessary tweaks to the underlying fiddle-related code, but the surrounding bits would be someone else's itch to scratch.
    - jansommer 4y ago
      Displaying unicode characters could be a problem here. I haven't managed to get it working when building a BusyBox powered Buildroot Linux, and in my search I've found another one who've tried and failed as well.
longrod 4y ago
SQLite is one of the most underrated databases around and it's sometimes baffling to know how much software actually relies on it.
pjot 4y ago
Very cool! I’m currently on mobile, but was excited when `shift + enter` still executed the sql!
- sgbeal 4y ago
  > I’m currently on mobile, but was excited when `shift + enter` still executed the sql! You got lucky - that was untested on non-keyboarded machines ;). There's a "run" button at the bottom intended primarily for mobile use BUT the UI is definitely not well-optimized for mobile devices yet. They are a target, certainly, but optimizing for them is well down the TODO list.
netcraft 4y ago
see also https://dbfiddle.uk/?rdbms=sqlite_3.27 https://dbfiddle.uk/?rdbms=sqlite_3.27
coder543 4y ago
I played with it for a minute, and the feature I want is just a checkbox to reset the database each time I run the query. It's nice to be able to iteratively build up a sequence of queries on the input, including creating tables, inserting items, etc. But, I don't even see a manual way to clear the database (which maybe should be a button too?) without refreshing the page... and refreshing the page forgets all of my preferences. (it still manages to keep the query input, it looks like, which is a start... but maybe that's just my browser trying to be helpful.)
- sgbeal 4y ago
  > I played with it for a minute, and the feature I want is a checkbox to reset the database each time I run the query. That's a good idea. There are tons of options i'd _like_ to add to it but have not simply for UI space's sake. The real limit on the UI is "how many options can we fit while still leaving room for input and output." Nobody involved in the development effort is a particularly strong UI developer (i can say that because i'm the one who wrote that UI ;), and assistance in prettying it up and improving the U/X would certainly be appreciated. > But, I don't even see a manual way to clear the database without refreshing the page... and refreshing the page forgets all of my preferences. Storing of the preferences in localStorage is on my TODO list. The underlying mini-API for it is in place, i just haven't yet dedicated the few hours to plug it all in and test it. Baby steps. > (it still manages to keep the query input, it looks like, which is a start... but maybe that's just my browser trying to be helpful.) That it keeps the query input is a _browser-specific quirk_, not an explicit feature. By and large, that quirk (Firefox, right?) is a huge pain in the butt in web development because it forces the developer to do a full reload on each hit, bypassing all caching.
  - coder543 4y ago
    > By and large, that quirk (Firefox, right?) Yep, definitely Firefox. In this case, my first thought was that the webpage was "doing the right thing", but then I realized the browser was probably doing it. Either way, it is the behavior I would expect in this case, so I consider that a win, but I understand it can be challenging for web developers under other circumstances. > assistance in prettying it up and improving the U/X would certainly be appreciated. My understanding is that the SQLite team typically isn't very open to outside contribution. UI/UX historically isn't my strong suite either, though, but I have been trying to work on that lately.
yread 4y ago
This is fairly easy to recreate on your computer. What I would like to fiddle with are compilation options, extensions, pragmas and their combinations. Make it easy to also see the io ops and cpu load per query and it becomes interesting
- sgbeal 4y ago
  > This is fairly easy to recreate on your computer. It is trivial to. The only tiny hiccup is that the wasm file cannot be served via a file:// URL due to XHR restrictions, so you'll need a local webserver for it. No big deal, but a slight nuisance. Richard added a new feature to his althttpd web server just to support this very dev effort. From the fiddle source directory, do: althttpd -page fiddle.html and it will start up a web server and launch your browser with that page. althttpd only works for Unix-style OSes, but ... that's okay :). > What I would like to fiddle with are compilation options, extensions, pragmas and their combinations. The build process for fiddle is literally a single compile command (using the emscripten compiler, emcc), and build time with the -O0 optimization level is fast (a few seconds on my modest i3-based NUC computer), so such fiddling is trivial to arrange for. If you do so, and find anything interesting, please post about it in the sqlite forum.
newbieuser 4y ago
anyone running a large scale saas job with rqlite/dqlite?
- jansommer 4y ago
  I think it's a deal breaker that rqlite only supports deterministic statements [0] and the way dqlite works is by having to run inside your application, meaning that you no longer can scale to zero. Ideally someone would implement replication as a separate process, like litestream but with better guarantees like rqlite/dqlite, but until that happens, I don't think they're of much use in a SaaS, unless it's to be an in process database alternative to Postgres and the likes, where users only access is through something like a rest api. [0] https://www.sqlite.org/deterministic.html https://www.sqlite.org/deterministic.html
  - benbjohnson 4y ago
    Litestream author here. I'm actually working on a next iteration of Litestream that'll have some configurable guarantees (e.g. synchronous replication, async replication w/ a maximum time window). I could possibly add distributed consensus (e.g. Raft) but I'm not sure how much benefit that would have over rqlite/dqlite.
    - jansommer 4y ago
      Sounds super cool! Really appreciatw the work you're doing. What I'm imagining in regards to Raft is this: 5 or more servers, each running unikernels in tiny virtual machines, with a JavaScript interpreter or similar to run untrusted user code. Each unikernel is then booted on every request, perhaps with a cool down period before being shut down. This would work similar to GCP, Azure etc. and performance would be great because of how fast unikernels boot. The unikernels would also be compiled with SQLite, with each database per customer stored on the host, and with restrictions on the virtual machine to only allow access to the database associated with the caller. Whenever untrusted code is executed, it can insert/update/read/delete from the database on the host. If the host then handled replication, that means I can just shut down the vm when the code has been run, and start a clean one for the next customer. A guarantee that the data have been safely replicated would be essential here, since another server might handle the next request, with the expectation that the data is in a new state.
Psychosecurity 4y ago
This lets you fiddle around with SQLite from the browser
polishdude20 4y ago
I've been looking into creating a better MySQL fiddle. How do people go about doing that? Do you need to rewrite the whole database engine in wasm or something?
notforsaleldn 4y ago
[dead]
ogogmad 4y ago
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.
tlhunter 4y ago
This is super useful. I love the web apps that do one thing well. regex101.com is one of my favorite tools in the category and the fiddle apps are amongst my favorites. With SQLite 3 Fiddle, pressing Ctrl+Enter executes every single query in the input field. This means you either need to delete old entries and lose your place or manually prefix them with double hyphens. As a nice-to-have feature, I'd love it if the tool followed the approach that Kibana Elasticsearch / DBeaver uses. With that approach, if text is selected, that text is executed as a query. And, if a cursor is in the middle of a query, only that query is executed instead of the whole thing. To run everything you select it all first (Ctrl+A Ctrl+Enter).

765 points by sgbeal 4y ago

Richard Hipp, of sqlite fame, just announced the project's new WASM-based "fiddle" app in the sqlite forum (https://sqlite.org/forum/forumpost/5cfd681451 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.")

vkoskiv 4y ago
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 4y ago
  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 4y ago
    The exception to this appears to be "Show HN" posts. E.g. https://news.ycombinator.com/item?id=31516108 https://news.ycombinator.com/item?id=31516108
tinus_hn 4y ago
This is from the FAQ: How do I make a link in a text submission? You can't. This is to prevent people from submitting a link with their comments in a privileged position at the top of the page. If you want to submit a link with comments, just submit it, then add a regular comment.
- Abishek_Muthian 4y ago
  You can do so with Show HN, Although the faq[1] doesn't explicitly state that. But in this case, Richard Hipp himself should post it as Show HN. [1] https://news.ycombinator.com/showhn.html https://news.ycombinator.com/showhn.html
solarkraft 4y ago
It's just another comment, but as a valuable one it is certain to be featured prominently through votes.
deleted 4y ago
[deleted]
deleted 4y ago
[deleted]

Kudovs 4y ago

I can see this being useful for technical interviews and quick demos.

niek_pas 4y ago
Please don't interview potential hires by having them do work while you look over their shoulder.
- frakkingcylons 4y ago
  Pair programming in an interview is one of the most useful ways to evaluate candidates in my experience.
- edgyquant 4y ago
  So algorithmic white board interviews are bad because they aren’t a measure of day to day productivity and now programming interviews are bad because reasons. How should I determine if engineers are a good fit then?
  - niek_pas 4y ago
    In my view, you should allow candidates to work on an assignment of your choosing to be handed in by a deadline you set.

status200 4y ago

Looks like a great sandbox, perfect for testing ideas and queries without having to spin up an environment or log in to something like Big Query

stefan_ 4y ago

We can finally get rid of IndexedDB, and it's only 900KiB extra bundle size!

sgbeal 4y ago
> and it's only 900KiB extra bundle size! Depending on how it's compiled, yes. It can be as large as 2MB when built with no optimizations.
- mwint 4y ago
  For production use, presumably you’d use an optimized binary
  - sgbeal 4y ago
    > For production use, presumably you’d use an optimized binary That particular one is built with -Oz (optimized for the smallest size). Development itself is done with -O0 (no optimizations) because the compile time difference is something like 2-3 seconds vs 15-20.
josephg 4y ago
330kb over the wire with brotli. Thats sizable, but definitely usable in a heavier web app. Honestly thats really impressive given it includes an entire SQL engine.
- sgbeal 4y ago
  > 330kb over the wire with brotli. Thats sizable, but definitely usable in a heavier web app. My almost-most-used website (boardgamegeek.com) currently serves 500-odd kb of minified CSS and 2MB of minified JS. That is to say: in the context of modern websites, 300-800kb arguably rates as a mere pittance.
sgbeal 4y ago
> We can finally get rid of IndexedDB, Not quite yet: indexeddb can be stored persistently whereas wasm-side sqlite dbs cannot (unless they're stored _in_ an indexeddb). Someday the JS filesystem APIs may offer such a thing, but right now there is no standard filesystem-like API which offers client-side persistence. The only half-workaround for the time being is the ability to upload and download the db image (something both sql.js and sqlite3's fiddle support).

tiffanyh 4y ago

Given that this SQLite variant is WASM-based, how does this relate to Cloudflare recent D1 announcement where you can use Workers (WASM) to connect to an SQLite datastore?

sgbeal 4y ago
> Given that this SQLite variant is WASM-based, how does this relate to Cloudflare recent D1 announcement where you can use Workers (WASM) to connect to an SQLite datastore? They're conceptually similar but, while i'm intimately familiar with the fiddle app, i don't know enough about D1 to know _how_ similar they are or are not. Note, also, that fiddle is analog to the sqlite3 shell app, not the library API. The library API itself is a separate wasm sub-project.

SahAssar 4y ago

Hasn't sql.js (sqlite compiled to wasm/js with emscripten) been around for a long time (at least a couple of years)? What makes this different other than coming from the actual sqlite team?

sgbeal 4y ago
sql.js is quite similar but not quite the same thing. They produce a high-level wrapper of the C API, but they do not offer a binding of the shell app (which is what fiddle is). The latter requires, due to how emscripten wraps the C function fgets(), hacking the shell in order to get sane input semantics. sql.js has, however, been a great point of reference and inspiration in building fiddle.

chrismorgan 4y ago

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 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 4y ago
> 898KB even without compression. Pretty good. gzip'd it's roughly half of that but that particular web server (althttpd) doesn't (yet?) know how to compress output payloads.
- motiejus 4y ago
  It makes sense to pre-compress the static assets. Then the server can load the compressed ones directly, without requiring to link to the compressor, and of course pay the price of online compression. I create two extra files for each static file (for some extensions): file file.gz <-- with zopfli file.br <-- with brotli And the web server, depending on the `Accept-Encoding` header, would serve the right file. The wasm module: $ ls -lh fiddle-module.wasm* -rw-r--r-- 1 motiejus motiejus 779K May 26 15:25 fiddle-module.wasm -rw-r--r-- 1 motiejus motiejus 323K May 26 15:25 fiddle-module.wasm.br -rw-r--r-- 1 motiejus motiejus 357K May 26 18:00 fiddle-module.wasm.gz
  - sgbeal 4y ago

simonw 4y ago

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 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 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 https://lite.datasette.io - more details on how that works here: https://simonwillison.net/2022/May/4/datasette-lite/ https://simonwillison.net/2022/May/4/datasette-lite/

kungfufrog 4y ago
Holy smokes, all of this is very cool! Nice work!
g3 4y ago
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%27import%20sqlite3;%20db%20=%20sqlite3.connect(%22:memory:%22);%20print(db.execute(%22select%204%20*%205%22).fetchall())%27 https://copy.sh/v86/?profile=archlinux&c=python%20-ic%20%27i...
- kzrdude 4y ago
  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 4y ago
    `event.key` returns the key with the mappings, e.g. "E" `event.keyCode` returns the physical key, e.g. "KeyE"

xwowsersx 4y ago

I've noticed a large number of stories on HN related to SQLite over the past few weeks. Maybe it's just random or I'm only now just noticing it, but is there some renewed/newfound interest in SQLite lately? If so, what's behind that?

Jarwain 4y ago
I find topics tend to surge and wane, and sqlite is a popular one. They don't always surge for a reason other than "someone posted something new about it and sent people down rabbit holes"
IshKebab 4y ago
Yeah definitely renewed interest. I think there's been a collective realisation that it's pretty good, and it has received some notable new features recently like JSON queries and proper type checking.
sgbeal 4y ago
> ... is there some renewed/newfound interest in SQLite lately? If so, what's behind that? Though i share that observation, in this particular case it's very likely coincidence. i've worked with Richard since 2008 on his Fossil SCM project, so have been "in that circle" for a long time without having ever actually contributed directly to sqlite. About two weeks ago the topic of wasm came up in a dev chat and it sounded to me like something interesting to experiment with (me being Fossil's "JavaScript Guy"), so i ran with it.
krferriter 4y ago
I think some people are realizing that SQLite has everything they need in their database use case, and they don't need a heavier, more complicated client-server database with more features.
- bob1029 4y ago
  This is exactly the reason. Some of us went off the beaten path and proved that you can actually put many users on a carefully-tuned SQLite instance (despite their official docs at the time). In many cases, you are able to exceed the capabilities of a one-node hosted DB solution due to latency reduction of in-process happiness.

alephnan 4y ago

Happy to see some zero-dependency VanillaJS. https://sqlite.org/fiddle/fiddle.js 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 4y ago
> Happy to see some zero-dependency VanillaJS. You're welcome :). Frankly, since the HTML5-related JS changes were standardized, jquery is no longer the "absolute must-have" it was prior to that, and i strive to use only vanilla, standards-conforming JS wherever feasible (with diminishingly little tolerance for non-conforming browsers). Edit: that license disclaimer is the standard one for the sqlite project, though, not mine.

yurivish 4y ago

See also: http://sqlime.org http://sqlime.org Which is another nice WASM-based browser SQLite user interface.

sgbeal 4y ago
> ... another nice WASM-based browser SQLite user interface. Thank you for pointing that one out. Every conceptually similar project is a great source of ideas. sqlite's fiddle app is literally less than 2 weeks old so still has lots of room left for feature creep ;).

dinvlad 4y ago

This throws me back to the good ol' days of fitting all software in a few dozens/hundreds of KBs on Speccy and similar systems. Current software engineering practices are so wasteful on computer resources, and imho unnecessarily so, when we keep in mind what was possible on those systems with 1000x less compute power decades ago.

ReptileMan 4y ago
My little pony: linking is magic. But with harddisks and bandwidth (aside from mobile) being limitless we just decided to stop bothering. The only device on which I am remotely space constraint is my MacBook. And that is mostly apple business decision.
sgbeal 4y ago
> This throws me back to the good ol' days of fitting all software in a few dozens/hundreds of KBs on Speccy and similar systems. And yet very few developers would want to go back to the days when we had no syntax highlighting and editors couldn't hold more than 64kb of text at a time and the OS could only run one thing at a time. Times change (thankfully). The 64kb machines now live in the domain of hobby projects, not machines for "getting things done." (There's a rather active group of folks who still hack on the C64, C128, and similar machines. Search for the "MEGA65" to find a recent (late 2021/early 2022) machine which was built solely for that crowd.)
- dinvlad 4y ago
  Sadly so :-) I felt much more productive coding without all those bells and whistles than nowadays - I wonder why..
- CRConrad 4y ago
  > And yet very few developers would want to go back to the days when we had no syntax highlighting and editors couldn't hold more than 64kb of text at a time and the OS could only run one thing at a time. Syntax highlighting, multi-megabyte file editing, and multitasking were all standard on ordinary software development PCs twenty years ago; perhaps closer to twenty-five. And those ordinary software development PCs had eight, sixteen or thirty-two MEGAbytes of RAM -- not gigabytes, like today. If you're saying syntax highlighting, multi-megabyte file editing, and multitasking are the reason we need three orders of magnitude more RAM to get the machine to do the same thing (only often more slowly) now as then, you're either selling something or you just don't know what you're talking about.

forrestthewoods 4y ago

Confession: I have 15 years dev experience and have never written a single line of SQL code. What’s a good tutorial for someone who knows how to program but doesn’t know the various SQL commands and gotchas?

enjalot 4y ago
some tutorials i've collected recently * https://mode.com/sql-tutorial/sql-joins/ https://mode.com/sql-tutorial/sql-joins/ * https://github.com/NUKnightLab/sql-mysteries https://github.com/NUKnightLab/sql-mysteries * https://selectstarsql.com/ https://selectstarsql.com/ * https://sqlbolt.com/ https://sqlbolt.com/ * https://www.craft.do/s/VzvaPiNX6jvxX5 https://www.craft.do/s/VzvaPiNX6jvxX5
krylon 4y ago
https://sqlbolt.com/ https://sqlbolt.com/ might be a good starting point.
cryptonector 4y ago
https://www.oreilly.com/library/view/sql-pocket-guide/9781492090397/ https://www.oreilly.com/library/view/sql-pocket-guide/978149... That's the best little book that will teach you the most.
CRConrad 4y ago
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 https://news.ycombinator.com/item?id=31355809

rathboma 4y ago

This is nice, but more of a console than a fiddle. When iterating on SQL for Beekeeper Studio I've been using https://dbfiddle.uk https://dbfiddle.uk for a few weeks, it's super good. Supports PSQL, MySQL, Oracle, etc.

sgbeal 4y ago
> This is nice, but more of a console than a fiddle. It's a binding of the sqlite3 console app, so that's an appropriate contrast. We're not trying to supplant such services as sqlfiddle and the wild world of wasm is entirely new to everyone currently working on the sqlite3 project, so we still have tons to learn and improve upon.
- rathboma 4y ago
  Yep! It's a great project whatever one calls it :-).

samwillis 4y ago

This awesome, I'm convinced WASM SQLite is the future of in-browser strorage for web apps/PWAs. Assuming this is normal WASM SQLite any persistence will be by flushing to LocalStorage/IndexedDB. There won't be any ACID compliance. There is a project called "Absurd SQL" to back SQLite with a custom block based FS on top of IndexedDB. It is somewhat absurd but it works incredibly well! https://github.com/jlongster/absurd-sql https://github.com/jlongster/absurd-sql I think combining WASM SQLite with the session extension (https://www.sqlite.org/sessionintro.html 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 4y ago
> There is a project called "Absurd SQL" to back SQLite with a custom block based FS on top of IndexedDB. It is somewhat absurd but it works incredibly well! (The fiddle dev here...) That is, AFAIK, the current state of the art for persisting sqlite wasm-side dbs. The JS-side APIs for providing such support are currently evolving within the browsers. With any luck, we'll have widespread JS-native persistent storage for sqlite within the next couple of/few years. (Those things take time to develop and, just as importantly, propagate through the browser ecosystem.) > I think combining WASM SQLite with the session extension ... would be a super interesting way to build an eventually consistent datastore for web apps. You could do all transactions locally and sync back and fourth just the changes. FWIW, that's what i personally envision will likely be the most compelling use case for the sqlite wasm builds. The fiddle app is _just_ the sqlite3 shell, not the sqlite3 library, but we've also created a plain-jane wasm binding of the C library and written an OO-style API on top of that (roughly akin to sql.js and friends, with different design decisions in some places). We're working on an API conceptually similar to sql.js's "worker" API, where the sqlite3 wasm can run in a WebWorker and the main thread can talk to it via WebWorker messages. That said, for the type of jobs sqlite will likely be doing in web clients, the workloads will be fast enough that running the wasm build in the main thread is unlikely to lead to any usability issues (blocked UI). Nobody's going to be using a 20gb db in such an app (because the browser won't let them) and sqlite is blazing fast for small data sets. Even so, the core APIs are agnostic of whether they're running in the UI thread or a WebWorker, so the client can decide for themselves how to plug it in.

cryptonector 4y ago

Nice! Ideas for future improvement: - make the shell prompt interactive - bundle various $EDITOR choices Heh.

sgbeal 4y ago
> make the shell prompt interactive On my dev system i have a build of fiddle which uses the jquery.terminal plugin as the main interface (with a button to toggle between it and the current UI), but it relies on 3rd-party code which is not approved for inclusion into the core sqlite3 repository (plus it's huge: all of the competent terminal-like APIs we evaluated so far bring 300-500kb of minimized JS dependencies). Reimplementing such a console, minus the jquery/etc dependencies, is beyond my current ambitions but has not been ruled out entirely long-term. Baby steps. > bundle various $EDITOR choices That would require a _proper_ terminal for those editors to live in, as opposed to a terminal lookalike such as jquery.terminal. xterm.js could presumably do it but is even bigger than jquery.terminal. No doubt that's _possible_ in wasm, but such features are _way_ beyond any current aspirations or scope for this app.
- cryptonector 4y ago
  What you do is build a very cut-down Linux system, bubybox style, as wasm, and deliver that.
  - sgbeal 4y ago
    > What you do is build a very cut-down Linux system, bubybox style, as wasm, and deliver that. Though i'd very much like to see such a thing, that's well, well, _well_ outside the scope of this project. If someone wants to create such a fiddle environment, _fantastic_, and i'll support them in any way i can in terms of necessary tweaks to the underlying fiddle-related code, but the surrounding bits would be someone else's itch to scratch.
  - jansommer 4y ago
    Displaying unicode characters could be a problem here. I haven't managed to get it working when building a BusyBox powered Buildroot Linux, and in my search I've found another one who've tried and failed as well.

longrod 4y ago

SQLite is one of the most underrated databases around and it's sometimes baffling to know how much software actually relies on it.

pjot 4y ago

Very cool! I’m currently on mobile, but was excited when `shift + enter` still executed the sql!

sgbeal 4y ago
> I’m currently on mobile, but was excited when `shift + enter` still executed the sql! You got lucky - that was untested on non-keyboarded machines ;). There's a "run" button at the bottom intended primarily for mobile use BUT the UI is definitely not well-optimized for mobile devices yet. They are a target, certainly, but optimizing for them is well down the TODO list.

netcraft 4y ago

see also https://dbfiddle.uk/?rdbms=sqlite_3.27 https://dbfiddle.uk/?rdbms=sqlite_3.27

coder543 4y ago

I played with it for a minute, and the feature I want is just a checkbox to reset the database each time I run the query. It's nice to be able to iteratively build up a sequence of queries on the input, including creating tables, inserting items, etc. But, I don't even see a manual way to clear the database (which maybe should be a button too?) without refreshing the page... and refreshing the page forgets all of my preferences. (it still manages to keep the query input, it looks like, which is a start... but maybe that's just my browser trying to be helpful.)

sgbeal 4y ago
> I played with it for a minute, and the feature I want is a checkbox to reset the database each time I run the query. That's a good idea. There are tons of options i'd _like_ to add to it but have not simply for UI space's sake. The real limit on the UI is "how many options can we fit while still leaving room for input and output." Nobody involved in the development effort is a particularly strong UI developer (i can say that because i'm the one who wrote that UI ;), and assistance in prettying it up and improving the U/X would certainly be appreciated. > But, I don't even see a manual way to clear the database without refreshing the page... and refreshing the page forgets all of my preferences. Storing of the preferences in localStorage is on my TODO list. The underlying mini-API for it is in place, i just haven't yet dedicated the few hours to plug it all in and test it. Baby steps. > (it still manages to keep the query input, it looks like, which is a start... but maybe that's just my browser trying to be helpful.) That it keeps the query input is a _browser-specific quirk_, not an explicit feature. By and large, that quirk (Firefox, right?) is a huge pain in the butt in web development because it forces the developer to do a full reload on each hit, bypassing all caching.
- coder543 4y ago
  > By and large, that quirk (Firefox, right?) Yep, definitely Firefox. In this case, my first thought was that the webpage was "doing the right thing", but then I realized the browser was probably doing it. Either way, it is the behavior I would expect in this case, so I consider that a win, but I understand it can be challenging for web developers under other circumstances. > assistance in prettying it up and improving the U/X would certainly be appreciated. My understanding is that the SQLite team typically isn't very open to outside contribution. UI/UX historically isn't my strong suite either, though, but I have been trying to work on that lately.

yread 4y ago

This is fairly easy to recreate on your computer. What I would like to fiddle with are compilation options, extensions, pragmas and their combinations. Make it easy to also see the io ops and cpu load per query and it becomes interesting

sgbeal 4y ago
> This is fairly easy to recreate on your computer. It is trivial to. The only tiny hiccup is that the wasm file cannot be served via a file:// URL due to XHR restrictions, so you'll need a local webserver for it. No big deal, but a slight nuisance. Richard added a new feature to his althttpd web server just to support this very dev effort. From the fiddle source directory, do: althttpd -page fiddle.html and it will start up a web server and launch your browser with that page. althttpd only works for Unix-style OSes, but ... that's okay :). > What I would like to fiddle with are compilation options, extensions, pragmas and their combinations. The build process for fiddle is literally a single compile command (using the emscripten compiler, emcc), and build time with the -O0 optimization level is fast (a few seconds on my modest i3-based NUC computer), so such fiddling is trivial to arrange for. If you do so, and find anything interesting, please post about it in the sqlite forum.

newbieuser 4y ago

anyone running a large scale saas job with rqlite/dqlite?

jansommer 4y ago
I think it's a deal breaker that rqlite only supports deterministic statements [0] and the way dqlite works is by having to run inside your application, meaning that you no longer can scale to zero. Ideally someone would implement replication as a separate process, like litestream but with better guarantees like rqlite/dqlite, but until that happens, I don't think they're of much use in a SaaS, unless it's to be an in process database alternative to Postgres and the likes, where users only access is through something like a rest api. [0] https://www.sqlite.org/deterministic.html https://www.sqlite.org/deterministic.html
- benbjohnson 4y ago
  Litestream author here. I'm actually working on a next iteration of Litestream that'll have some configurable guarantees (e.g. synchronous replication, async replication w/ a maximum time window). I could possibly add distributed consensus (e.g. Raft) but I'm not sure how much benefit that would have over rqlite/dqlite.
  - jansommer 4y ago
    Sounds super cool! Really appreciatw the work you're doing. What I'm imagining in regards to Raft is this: 5 or more servers, each running unikernels in tiny virtual machines, with a JavaScript interpreter or similar to run untrusted user code. Each unikernel is then booted on every request, perhaps with a cool down period before being shut down. This would work similar to GCP, Azure etc. and performance would be great because of how fast unikernels boot. The unikernels would also be compiled with SQLite, with each database per customer stored on the host, and with restrictions on the virtual machine to only allow access to the database associated with the caller. Whenever untrusted code is executed, it can insert/update/read/delete from the database on the host. If the host then handled replication, that means I can just shut down the vm when the code has been run, and start a clean one for the next customer. A guarantee that the data have been safely replicated would be essential here, since another server might handle the next request, with the expectation that the data is in a new state.

Psychosecurity 4y ago

This lets you fiddle around with SQLite from the browser

polishdude20 4y ago

I've been looking into creating a better MySQL fiddle. How do people go about doing that? Do you need to rewrite the whole database engine in wasm or something?

notforsaleldn 4y ago

[dead]

ogogmad 4y ago

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.

tlhunter 4y ago

This is super useful. I love the web apps that do one thing well. regex101.com is one of my favorite tools in the category and the fiddle apps are amongst my favorites. With SQLite 3 Fiddle, pressing Ctrl+Enter executes every single query in the input field. This means you either need to delete old entries and lose your place or manually prefix them with double hyphens. As a nice-to-have feature, I'd love it if the tool followed the approach that Kibana Elasticsearch / DBeaver uses. With that approach, if text is selected, that text is executed as a query. And, if a cursor is in the middle of a query, only that query is executed instead of the whole thing. To run everything you select it all first (Ctrl+A Ctrl+Enter).