4 ms·
Exploring Tweets with SQLite and WASM
- sktrdie 6y agoCool but we’ve had in browser databases since ages. Things like indexedDb and WebSql. But I guess it’s a cool WebAssembly experiment nonetheless.
- divyenduz 6y agoOh absolutely, just an experiment, I am really curious about doing things like video editing etc on the client side. I did use this to delete a lot of old tweets though.
- banana_giraffe 6y agoSELECT substr(date,1,7) as d, count(*), min(likes+retweets) FROM tweets GROUP BY d ORDER BY d DESC; Yep, looks like you mostly tossed tweets over a year old that didn't meet some threshold. Very cool to play with in the browser.
- divyenduz 6y agoYeah, I am slowly removing myself from the internet, I don’t think that I follow anyone on any social network where ever I exist. This is also a great way to backup tweets etc. Of course I have multiple SQLite files with all the data, whenever I want to delete “more”, I just take a new Twitter dump.
- Tade0 6y agoWebSQL never reached proper standardization because there was a requirement of at least two independent implementations for that and all the available ones were based on... SQLite.
- detaro 6y agoAll being based on SQLite wouldn't have been the problem. But it specified a database "accepting the same SQL dialect as SQLite version XYZ", and that's a fairly obvious no-go (and nobody involved thought it was worth the effort to do a better specification).
- reader_mode 6y agoWebSQL died and shipping a locked database version compiled with features you want is usually better than relying on every browser DB implementation and version supporting the stuff you want to use. The apps that need SQL aren't too sensitive to initial load/download time increase by adding a MB of WASM which can later be handled by caching.
- kevingadd 6y agoIMO, anyone given the choice between IndexedDB and "compile SQLite to webassembly and use that" would pick SQLite in a heartbeat. IndexedDB is awful in almost every possible way. It's slow, the API sucks, it's limited, etc
- rasz 6y agomozilla killed WebSql because it lacked "developer aesthetics", now we got shit indexedDb nobody uses without middleware to ease the pain. "Aesthetics and Web Developers" https://hacks.mozilla.org/2010/06/beyond-html5-database-apis-and-the-road-to-indexeddb/ https://hacks.mozilla.org/2010/06/beyond-html5-database-apis...
- devwastaken 6y agoWebSQL is deprecated IIRC - the problem with complex databasing like SQL is that users are never meant to be querying directly on it. SQLite has a number of functions that are pointers to your own functions to override/add behavior. If a user could simply query that - poof you're SOL. Which means to be safe from the host system you have to virtualize the execution somehow - WASM is exactly that. Therefore if you wanted sqlite in a browser, you can simply compile it to wasm.
- gkorland 6y agoThe question that keeps bother us, what is the WASM perform impact compared to native SQLite ?
- divyenduz 6y agoPerformance? Not sure TBH, wasn't a primary aim for me. The slowest bit of this experiment is to load the wasm file in the browser.
- bionhoward 6y agoCan this extend to rqlite? It would be pretty epic
- divyenduz 6y agoHa, what’s your aim with that? Making changes in the browser and have them saved somewhere? I am pretty sure I can make this upload to s3 or something to emulate that But a part of this experiment was its offline nature, where you can play around with tweets etc without having them uploaded to a server. Interesting idea though about having them “sync”
- bionhoward 6y agoThis could enable offline-first sync
- divyenduz 6y agoI agree, would be an interesting POC, on my backlog
- simonw 6y agoI have a CLI tool (written in Python) called twitter-to-sqlite which imports Tweets into a SQLite database - it's a really fun source of data to play with: https://datasette.io/tools/twitter-to-sqlite https://datasette.io/tools/twitter-to-sqlite
- fiddlerwoaroof 6y agoI've been using your Datasette tools a bunch, they're really great for quick data analysis. Recently I've been play with doing an `sqlite foo.db dump` and then loading the dump into sql.js as the "DB" for statically-hosted websites. It's probably impractical because the sqlite.wasm file is a couple megs, but it's a fun proof of concept.
- simonw 6y agoThat sounds like a really useful trick.
- fiddlerwoaroof 6y agoI came up with it because I was trying to figure out how to host data from datasette on GitHub Pages.
- divyenduz 6y agoAha, amazing, I have played with datasette in past, great work. I will link this tool back in the blog post today if you don’t mind :)
- nathanwallace 6y agoI wrote a Postgres FDW in Go to query Twitter using SQL - https://hub.steampipe.io/plugins/turbot/twitter https://hub.steampipe.io/plugins/turbot/twitter (Steampipe is an open source CLI to query cloud APIs using SQL. No DB required!)