"Hosting SQLite databases on Github Pages" is absolutely brilliant: it adds a virtual filesystem to SQLite-compiled-to-WebAssembly in order to fetch pages from the database using HTTP range requests phiresky.github.io/blog/2021/host…
Check out this demo: I run the SQL query "select country_code, long_name from wdi_country order by rowid desc limit 100" and it fetches just 54.2KB of new data (across 49 small HTTP requests) to return 100 results - from a statically hosted database file that's 668.8MB!
Looks like the core magic here is only around 300 lines of (devastatingly clever) code github.com/phiresky/sql.j…
phiresky points out that my "order by row id desc" demo is actually a worst case scenario - if you try the same thing without the desc it only takes 6 requests!

select country_code, long_name from wdi_country
order by rowid limit 100


• • •

Missing some Tweet in this thread? You can try to force a refresh

Keep Current with Simon Willison

Simon Willison Profile picture

Stay in touch and get notified when new unrolls are available from this author!

Read all threads

This Thread may be Removed Anytime!


Twitter may remove this content at anytime! Save it as PDF for later use!

Try unrolling a thread yourself!

how to unroll video
  1. Follow @ThreadReaderApp to mention us!

  2. From a Twitter thread mention us with a keyword "unroll"
@threadreaderapp unroll

Practice here first or read more on our help page!

More from @simonw

3 May
What are the options for "serverless" PostgreSQL like these days? My definition of serverless here is that you don't have to spend any money at all if you're not getting any DB traffic, and cost then scales up as the traffic and storage you are using increases
Aurora PostgreSQL is the most obvious option, though it's not clear to me if you have to keep at least one instance running for it or if it fully "scales to zero" for projects that aren't receiving any traffic at all
Consensus in replies seems to be that this doesn't actually exist yet - scale-to-zero for a relational database server like PostgreSQL is evidently a whole lot harder than scale-to-zero for a stateless web application server as seen with things like Google Cloud Run
Read 6 tweets
1 May
The feature I most want from Twitter is "view this user's tweets without showing their retweets"
Note that this isn't the same thing as turning off someone's retweets - I want to go to the profile of someone I don't even follow and see what they actually say, not just a stream of stuff they have retweeted
Read 4 tweets
12 Mar
SQLite 3.35.0 is a significant release: has a bunch of really valuable new features in it
First up... math functions! sqlite.org/lang_mathfunc.… - sin/tan/cos/acos/asin, sqrt/pow/mod/log2, floor/ceil etc

I've wanted these a few times in the past and had to load them in as custom Python functions - great to have them in SQLite proper

SQLite's alter table has been pretty limited in the future, but those limitations are dropping off version by version

I built a bunch of tools for advanced alter table in sqlite-utils - glad to see them slowly going obsolete simonwillison.net/2020/Sep/23/sq…
Read 8 tweets
12 Mar
These days I'm so keen on having every commit link to an underlying issue thread that I'll sometimes write some code, then file an issue and close it with a commit a few seconds later
The real value here is having somewhere to continue the discussion around a change. I'll frequently add screenshots and links-to-documentation to an already closed issue. Also great to link to from release notes.
It almost becomes an out-of-band documentation and commentary thread - somewhere you can really dig into the reason for a change without fear of clogging up the code with comments that will inevitably become out-of-date in the future
Read 5 tweets
11 Mar
Built tableau-to-sqlite github.com/simonw/tableau…
github.com/bertrandmartel… by Bertrand Martel does all of the actual work, I just added a paper-thin wrapper around it that writes the resulting Pandas DataFrames out to a SQLite database
Read 5 tweets

Did Thread Reader help you today?

Support us! We are indie developers!

This site is made by just two indie developers on a laptop doing marketing, support and development! Read more about the story.

Become a Premium Member ($3/month or $30/year) and get exclusive features!

Become Premium

Too expensive? Make a small donation by buying us coffee ($5) or help with server cost ($10)

Donate via Paypal Become our Patreon

Thank you for your support!

Follow Us on Twitter!