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 .
Similar to sqlite-html from last week, it’s written in Go, made possible with Riyaz Ali’s SQLite Go library.
-- 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;
The Introducing sqlite-http blog post has some other use-cases for sqilte-http, like pairing it with define to create a makeshift SQL API for the Census API:
create virtual table census_county_population using statement((
select
value ->> '$[1]' as state,
value ->> '$[2]' as county,
cast(value ->> '$[0]' 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_json('https://...'), 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.
0x6b released libgsqlite, a SQLite extension for reading Google Sheets as a virtual table. This a fork of Sergey Khabibullin’s xlite, so pretty cool to see Rust come up in more SQLite extensions.
That’s it for this week! Here’s a preview for what’s to come in the next few weeks:
If these emails are getting repetitive, feel free to instead follow me on Twitter or follow the RSS feed for this series.
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!