Welcome to the 3rd issue of this SQLite newsletter! Big thanks to all of you who have subscribed, and I hope you continue to find this useful!
As a reminder - this is a newsletter created by Alex Garcia about SQLite - new tools, guides, best practices, and other stuff. There is at most 1 email per week, with many gaps in between.
This week: sqlite-http, an extension for making HTTP requests, all in SQLite! Check out the project on Github to learn more, our try it in your browser in the blogpost, Introducing sqlite-http: A SQLite extension for making HTTP requests .
-- Using the SQLite CLI .load ./http0 select http_get_body('https://api.github.com/repos/sqlite/sqlite') ->> '$.description' as description; /* ┌───────────────────────────────────────────────┐ │ description │ ├───────────────────────────────────────────────┤ │ Official Git mirror of the SQLite source tree │ └───────────────────────────────────────────────┘ */
See the documentation for a full API reference.
There's many reasons why I'm excited about sqlite-http: The main reason being that getting data from HTTP endpoints unlocks so many more datasets that can be loaded into a SQLite database easily. The blogpost "Scraping JSON, HTML, and ZIP Files with Pure SQLite" goes into this more, including an example of loading an archive of John Carmack's
.plan files into a SQLite full-text-search database, all in 3 SQL commands!
-- scrape the data from a github ZIPfile archive create table carmack_plans as select -- "john-carmack-plan-master/archive/1996-02-18.md" -> "1996-02-18" substr( name, length("john-carmack-plan-master/archive/") + 1, length("YYYY-MM-DD") ) as date, cast(data as text) as contents from zipfile( http_get_body('https://github.com/oliverbenns/john-carmack-plan/archive/refs/heads/master.zip') ) where name like "%archive/%.md"; -- load into into a fts5 virtual table create virtual table fts_carmack_plans using fts5(contents); insert into fts_carmack_plans(rowid, contents) select rowid, contents from carmack_plans; -- query your new search engine! select * from fts_carmack_plans('teleport') order by rank;
create virtual table census_county_population using statement(( select value ->> '$' as state, value ->> '$' as county, cast(value ->> '$' as integer) as population from json_each( http_get_body( printf( 'https://api.census.gov/data/2020/acs/acs5?get=B01001_001E&for=county:*&in=state:%s', :state ) ) ) -- 1st element is headers, ex. '["B01001_001E","state","county"]' where key != 0 )); select * from census_county_population('36') -- New York order by 3 desc limit 5; /* ┌───────┬────────┬────────────┐ │ state │ county │ population │ ├───────┼────────┼────────────┤ │ 36 │ 047 │ 2576771 │ │ 36 │ 081 │ 2270976 │ │ 36 │ 061 │ 1629153 │ │ 36 │ 103 │ 1481364 │ │ 36 │ 005 │ 1427056 │ └───────┴────────┴────────────┘ */
Or how you can create "local lambda functions" to supplement SQL scripts with data processing done in a Python, Node, Deno, or any server
with suntimes as ( select rowid as city, -- Deno server running on port 3001 that performs sunset calculations http_post_body( 'http://localhost:3001/suncalc', null, json_object( 'longitude', longitude, 'latitude', latitude, -- ho ho ho 'date', '2022-12-25' ) ) as suncalc_times from cities ), final as ( select cities.rowid as city, suncalc_times ->> '$.sunrise' as sunrise, suncalc_times ->> '$.sunset' as sunset, from suntimes left join cities on cities.rowid = suntimes.city ) select * from final;
Give them a read! The combination of sqlite-http and other SQLite extensions are slowly replacing Panda's
pd.read_html(), and other Pandas functions to me.
Simon Willison started his Datasette Substack up again with an intro to "Datasette Lite", which is the original Datasette project compiled to WebAssembly with Pyodide. Pretty exciting to see a server-side tool compiled to WASM!
Ben Johnson's How SQLite Helps You Do ACID, the 2nd post in his SQLite internals series.
That's it for this week! Here's a preview for what's to come in the next few weeks:
And as always, if you ever want to chat about SQLite tooling or have questions, feel free to email me, DM me on Twitter, or file an issue in any Github repo!