31:[{"id":"9e2223b5b0b0269e5c55e61fa232c78c7089983670cdd21c18543f98abca5820ef498086c1fa42ba3bc16316be98cbd0c2c3b69ce0ebecfde32cf5359292e100","href":"https://static.observableusercontent.com/files/9e2223b5b0b0269e5c55e61fa232c78c7089983670cdd21c18543f98abca5820ef498086c1fa42ba3bc16316be98cbd0c2c3b69ce0ebecfde32cf5359292e100","name":"DALL·E 2022-08-10 11.35.03.png","type":"image/png","lastModified":1660156536979.508,"size":1353533},{"id":"63affb34654bfeafd411ab4e3fcd2274d1eccc5a8f34c77933783218b7d1805d12f54e63ad5cb14e631dae04abf44a4eb3ff0d83fbf4694c746d4054d9d5a19e","href":"https://static.observableusercontent.com/files/63affb34654bfeafd411ab4e3fcd2274d1eccc5a8f34c77933783218b7d1805d12f54e63ad5cb14e631dae04abf44a4eb3ff0d83fbf4694c746d4054d9d5a19e","name":"DALL·E 2022-08-10 11.34.47 - Okapi in a conference room, with a laser pointer, pointing to a whiteboard, claymation.png","type":"image/png","lastModified":1660156538979.046,"size":1410540},{"id":"05cd697b6430ea6e59a866774cc50461cb305f8b88d2821206d1e11469c2e8c2cbefe3cd5aa49bfeed0aee3449cae67fd7830fe707e180350170413b04dc6f99","href":"https://static.observableusercontent.com/files/05cd697b6430ea6e59a866774cc50461cb305f8b88d2821206d1e11469c2e8c2cbefe3cd5aa49bfeed0aee3449cae67fd7830fe707e180350170413b04dc6f99","name":"DALL·E 2022-08-10 11.35.18 - An ape working on a painting that contains HTML code.png","type":"image/png","lastModified":1660156534670.318,"size":1300971}] 2f:{"title":"Introducing sqlite-http: A SQLite extension for making HTTP requests","cells":[{"id":0,"value":"# Introducing sqlite-http: A SQLite extension for making HTTP requests","pinned":false,"hidden":false,"mode":"md"},{"id":3,"value":"[`sqlite-http`](https://github.com/asg017/sqlite-http) is a new SQLite extension that allows you to create HTTP requests in SQLite. \n\nThink of `fetch()`, `requests.get()`, or `curl`, but entirely in SQL!\n\n```sql\nselect request_url, response_status, response_headers \nfrom http_get('http://httpbin.org/get');\n```\n","pinned":false,"hidden":false,"mode":"md"},{"id":399,"value":"

","pinned":false,"hidden":false,"mode":"html"},{"id":421,"value":" Tried out DALL·E to generate blog post thumbnails... was pretty fun!","pinned":false,"hidden":false,"mode":"md"},{"id":239,"value":"## Demo\nsqlite-http is written in Go, which can't be easily compiled to WASM (unlike [sqlite-lines](https://observablehq.com/@asg017/introducing-sqlite-lines)). But we can load it into [Datasette](https://datasette.io/) and try it out in our browswer!\n\nFirst off, we will import the [Datasette Client for Observable](https://observablehq.com/@ambassadors/datasette-client) notebook and load in a URL to our [Datasette instance](https://sqlite-extension-examples.fly.dev/data). ","pinned":false,"hidden":false,"mode":"md"},{"id":191,"value":"import { DatasetteClient } from \"@ambassadors/datasette-client\"","pinned":true,"hidden":false,"mode":"ojs"},{"id":194,"value":"db = new DatasetteClient(\"https://sqlite-extension-examples.fly.dev/data\")","pinned":true,"hidden":false,"mode":"ojs"},{"id":291,"value":"Let's verify that it loaded correctly, and that we can call the sqlite-http functions in SQL.","pinned":false,"hidden":false,"mode":"md"},{"id":287,"value":"select http_version(), http_headers('X-Name', 'Alex');","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:db"},{"id":296,"value":"^The above uses Observable's [SQL Cells](https://observablehq.com/@observablehq/sql-cell)\n\nSweet! Now, most functions included in sqlite-http aren't accessible in this Datasette instance. That's because including functions like `http_get()` or `http_post_body()` would mean bad actors could use my server as a vector for DDoS attacks, so the \"[No network](https://github.com/asg017/sqlite-http/blob/main/docs.md#no-net)\" version of sqlite-http is loaded in this instance. ","pinned":false,"hidden":false,"mode":"md"},{"id":302,"value":"Let's play with the `snapshots`, table, which has data loaded in from [`http_get()`](https://github.com/asg017/sqlite-http/blob/main/docs.md#http_get):","pinned":false,"hidden":false,"mode":"md"},{"id":304,"value":"select * from snapshots;","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:db"},{"id":322,"value":"Let's narrow down to a few fields:","pinned":false,"hidden":false,"mode":"md"},{"id":310,"value":"Inputs.table(\n db.sql`select \n request_method, \n request_url, \n json_extract(timings, '$.start') as started,\n response_status, \n length(response_body)\n \nfrom snapshots`\n)","pinned":true,"hidden":false,"mode":"ojs"},{"id":324,"value":"Cool, so a GET request on a bus API that happened in August 10th, and returned 33KB, nice! Let's look at the headers with [`http_headers_each`](https://github.com/asg017/sqlite-http/blob/main/docs.md#http_headers_each):","pinned":false,"hidden":false,"mode":"md"},{"id":326,"value":"Inputs.table(\n db.sql`select name, value from snapshots join http_headers_each(response_headers)`\n)","pinned":false,"hidden":false,"mode":"ojs"},{"id":329,"value":"And now we know more: the server is running some Microsft ASP.NET server, which looks like Version 4, which came out [~10 years ago](https://en.wikipedia.org/wiki/ASP.NET), yikes...\n\nLet's take a look at the response body:","pinned":false,"hidden":false,"mode":"md"},{"id":333,"value":"JSON.parse((await db.sql`select response_body from snapshots`)[0].response_body)","pinned":true,"hidden":false,"mode":"ojs"},{"id":338,"value":"It's JSON! Let's use SQLite's [JSON functions](https://www.sqlite.org/json1.html) to parse out all the stops the object:","pinned":false,"hidden":false,"mode":"md"},{"id":340,"value":"select \n -- This version of Datasette is too old for the hip new \"->>\" syntax :(\n json_extract(value, '$.StopId') as stop_id,\n json_extract(value, '$.Description') as description,\n json_extract(value, '$.Latitude') as latitude,\n json_extract(value, '$.Longitude') as longitude\n \nfrom snapshots\njoin json_each(snapshots.response_body, '$.Stops')","pinned":true,"hidden":false,"mode":"sql","output":"stops","database":"var:db"},{"id":347,"value":"Cool, now let's throw these lat/longs in a quick leaflet map.","pinned":false,"hidden":false,"mode":"md"},{"id":349,"value":"import {L} from \"@observablehq/hello-leaflet\"","pinned":true,"hidden":false,"mode":"ojs"},{"id":352,"value":"viewof map = {\n const container = html`

`;\n yield container;\n const icon = L.icon({\n iconUrl:\n \"https://cdnjs.cloudflare.com/ajax/libs/leaflet/1.8.0/images/marker-icon.png\"\n });\n const map = L.map(container).setView([33.99, -118.07], 12);\n container.value = map;\n L.tileLayer(\"https://{s}.tile.openstreetmap.org/{z}/{x}/{y}.png\", {\n attribution:\n \"© OpenStreetMap contributors\"\n }).addTo(map);\n\n for (const stop of stops) {\n L.marker([stop.latitude, stop.longitude], {\n icon,\n title: stop.description\n }).addTo(map);\n }\n yield container;\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":382,"value":"Try it yourself! Try extracting the data for \"Vehicles\" from the same response body here:","pinned":false,"hidden":false,"mode":"md"},{"id":384,"value":"select response_body\n-- hint: json_each will be very useful here!\nfrom snapshots;","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:db"},{"id":10,"value":"## Use-Cases\n","pinned":false,"hidden":false,"mode":"md"},{"id":12,"value":"### A Makeshift SQL API \n\nMost APIs in the wild don't offer a SQL flavor - go figure! Sure, you can hack away at a Python or Node script that consumes data from an API, but the declarative style of SQL code can be a nice experience. \n\nTake the official Census API for example. It's only a JSON API, which you can consume with sqlite-http like so:\n\n```sql\nselect \n value ->> '$[1]' as state,\n value ->> '$[2]' as county,\n cast(value ->> '$[0]' as integer) as population\nfrom json_each(\n http_get_body(\n 'https://api.census.gov/data/2020/acs/acs5?get=B01001_001E&for=county:*&in=state:06'\n )\n)\n-- 1st element is headers, ex. '[\"B01001_001E\",\"state\",\"county\"]'\nwhere key != 0;\n/*\n┌───────┬────────┬────────────┐\n│ state │ county │ population │\n├───────┼────────┼────────────┤\n│ 06 │ 001 │ 1661584 │\n│ 06 │ 003 │ 1159 │\n│ 06 │ 007 │ 223344 │\n|*****************************|\n└───────┴────────┴────────────┘\n*/\n```\n","pinned":false,"hidden":false,"mode":"md"},{"id":182,"value":"That's great! Having the data in a table form instead of a weird nested JSON arrays is already way more helpful. \n\nBut typing that long query over and over again can be repetitive, especially if you want data on multiple state. \n\nOne solution: use [define](https://github.com/nalgeon/sqlean/issues/27#issuecomment-1004347222), a SQLite extension for creating table functions in pure SQL in SQLite. It's part of the [sqlean project](https://github.com/nalgeon/sqlean), and it allows us to abstract the above query into a single line of re-usable code.\n\nLet's create a new table function called `census_county_population()` that takes in state FIPS code and outputs the same data as above, like so:","pinned":false,"hidden":false,"mode":"md"},{"id":38,"value":"```sql\n\ncreate virtual table census_county_population using statement((\n select \n value ->> '$[1]' as state,\n value ->> '$[2]' as county,\n cast(value ->> '$[0]' as integer) as population\n from json_each(\n http_get_body(\n printf(\n 'https://api.census.gov/data/2020/acs/acs5?get=B01001_001E&for=county:*&in=state:%s',\n :state\n )\n )\n )\n -- 1st element is headers, ex. '[\"B01001_001E\",\"state\",\"county\"]'\n where key != 0 \n));\n```\n","pinned":false,"hidden":false,"mode":"md"},{"id":186,"value":"Now using this table function is very easy!\n\n```sql\nselect *\nfrom census_county_population('36') -- New York\norder by 3 desc\nlimit 5;\n/*\n┌───────┬────────┬────────────┐\n│ state │ county │ population │\n├───────┼────────┼────────────┤\n│ 36 │ 047 │ 2576771 │\n│ 36 │ 081 │ 2270976 │\n│ 36 │ 061 │ 1629153 │\n│ 36 │ 103 │ 1481364 │\n│ 36 │ 005 │ 1427056 │\n└───────┴────────┴────────────┘\n*/\n```\n","pinned":false,"hidden":false,"mode":"md"},{"id":26,"value":"### As an Archiving Tool\n\nWith the `http_get()`, `http_post()`, and `http_do()` table functions, you can save _everything_ from a request. Status codes, headers, the bodies themselves, timestamp of various network-level events. \n\nSaving it to a table is simple too, using `\"create table x as\"` syntax like so:\n\n```sql\n-- initialize a table\ncreate table snapshots as\n select * from http_get('https://google.com');\n\n-- To add more rows later on\ninsert into snapshots\n select * from http_get('https://google.com');\n```\n\nThis is especially useful with you want to archive or track a specific website or resource. Once I was using sqlite-http to track a JSON API of a local bus line every 5 minutes. Downstream in the pipeline, I noticed some NULL values, and when I dug into the raw responses returned from `http_get()`, I noticed the API would return a `503 Service Unavailable` between 1am and 6am. Since I was saving those responses for an audit log, it was easy to notice and easy to fix!","pinned":false,"hidden":false,"mode":"md"},{"id":8,"value":"### Local \"Lambda\" functions in SQL","pinned":false,"hidden":false,"mode":"md"},{"id":44,"value":"Doing all data processing or parsing in only SQLite can be challenging. Even small tasks like \"parse out the date from the string '20061010'\" can be challenging using only SQLite's builtin functions, let alone tasks like \"parse this [wikitext](https://en.wikipedia.org/wiki/Help:Wikitext)\" or \"find the average color in this image\". \n\nOne strategy I use when coming across similar tasks: \"lambda\" functions. I'll run a local server with Python/Node/whatever that can perform the complex calculations, and returning data as JSON for SQLite to hook into. \n\n","pinned":false,"hidden":false,"mode":"md"},{"id":74,"value":"Let's step through an example: say you want to explore the sunrise/sunset times of various cities in the United States. Calculating this is [relatively straightforward](https://en.wikipedia.org/wiki/Sunrise_equation), but not easy to do in pure SQLite. \n\nCreating a full SQLite extension is time consuming, but other 3rd party tools like Node.js or Deno have this readily available: the [suncalc](https://en.wikipedia.org/wiki/Sunrise_equation) package can easily calculate this given a latitude, longitude, and date.\n\nSo, we'll create a tiny [Deno](https://deno.land/) server that we can POST latitude/longitude/dates to, that will return the [sunlight times](https://github.com/mourner/suncalc#sunlight-times) as JSON. Then, in our SQLite script, we'll use `http_post_body()` to POST to our server and save the responses in a SQLite table for further analysis.","pinned":false,"hidden":false,"mode":"md"},{"id":83,"value":"#### Step 1: Setup the Deno server","pinned":false,"hidden":false,"mode":"md"},{"id":114,"value":"I choose Deno here only because the example is short and sweet, but this technique can be used with any programming language that can run a server!\n\nWe'll need a server that takes in a small JSON payload as input (containing a latitude, longitude, and date), and outputs what the suncalc API returns. Thanksfully, the code to do this isn't very long:","pinned":false,"hidden":false,"mode":"md"},{"id":76,"value":"```typescript\nimport { serve } from \"https://deno.land/std@0.148.0/http/server.ts\";\nimport suncalc from \"https://cdn.skypack.dev/suncalc\";\n\nconst port = 3001;\n\nconst suntimesRoute = new URLPattern({ pathname: \"/suncalc\" });\n\ninterface SuntimePostParams {\n latitude: number;\n longitude: number;\n date: string;\n}\n\n// user POSTs JSON object SuntimePostParams to \"/suntimes\", return suncalc.getTimes response\nasync function handleSuntimes(request: Request): Promise {\n const { latitude, longitude, date } =\n (await request.json()) as SuntimePostParams;\n const times = suncalc.getTimes(new Date(date), latitude, longitude);\n return Promise.resolve(new Response(JSON.stringify(times), { status: 200 }));\n}\n\nawait serve(\n (request: Request): Promise => {\n if (suntimesRoute.test(request.url)) return handleSuntimes(request);\n return Promise.resolve(new Response(\"not found :(\", { status: 404 }));\n },\n { port }\n);\n\n```","pinned":false,"hidden":false,"mode":"md"},{"id":118,"value":"To try it out, save this as `suncalc_server.ts`, and run:\n\n```\ndeno run --allow-net suncalc_server.ts\n```\n\nAnd test with:\n\n```bash\ncurl -X POST http://localhost:3001/suncalc --data '{\"longitude\": -118.1498, \"latitude\": 34.0414, \"date\": \"2020-03-01\"}'\n\n{\"solarNoon\":\"2020-02-29T20:06:30.047Z\",\"nadir\":\"2020-02-29T08:06:30.047Z\",\"sunrise\":\"2020-02-29T14:23:21.169Z\",\"sunset\":\"2020-03-01T01:49:38.925Z\",\"sunriseEnd\":\"2020-02-29T14:25:57.425Z\",\"sunsetStart\":\"2020-03-01T01:47:02.669Z\",\"dawn\":\"2020-02-29T13:58:13.242Z\",\"dusk\":\"2020-03-01T02:14:46.852Z\",\"nauticalDawn\":\"2020-02-29T13:29:12.312Z\",\"nauticalDusk\":\"2020-03-01T02:43:47.782Z\",\"nightEnd\":\"2020-02-29T13:00:13.973Z\",\"night\":\"2020-03-01T03:12:46.121Z\",\"goldenHourEnd\":\"2020-02-29T14:56:58.474Z\",\"goldenHour\":\"2020-03-01T01:16:01.620Z\"}\n```\n","pinned":false,"hidden":false,"mode":"md"},{"id":88,"value":"#### Step 2: Get a bunch of lat/longs","pinned":false,"hidden":false,"mode":"md"},{"id":131,"value":"Now that we have a function for calculating sun times, let's use it on some actual data! Let's get the top 1000 US cities (including lat/longs!) according to [a random Gist](https://gist.github.com/Miserlou/c5cd8364bf9b2420bb29) and load it into a table.","pinned":false,"hidden":false,"mode":"md"},{"id":56,"value":"```sql\ncreate table cities as \n select\n value ->> '$.city' as name,\n value ->> '$.latitude' as latitude,\n value ->> '$.longitude' as longitude,\n value ->> '$.population' as population,\n value ->> '$.state' as state\n from json_each(\n http_get_body('https://gist.githubusercontent.com/Miserlou/c5cd8364bf9b2420bb29/raw/2bf258763cdddd704f8ffd3ea9a3e81d25e2c6f6/cities.json')\n );\n```","pinned":false,"hidden":false,"mode":"md"},{"id":204,"value":"select * from cites;","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:db"},{"id":100,"value":"#### Step 3: Use our \"lambda\" function with http_post_body","pinned":false,"hidden":false,"mode":"md"},{"id":138,"value":"Now let's bring it all together! Let's use our running server to calculate sun times for 100 of these cities. This only requires one single SQL query to calculate, but we'll break it up to understand it more. \n\nFirst, let's save the result into a table with the `CREATE TABLE x AS` syntax. We'll calculate sun times for this upcoming Christmas dates, so we'll call in `christmas_suntimes`. ","pinned":false,"hidden":false,"mode":"md"},{"id":141,"value":"\n```sql\ncreate table christmas_suntimes as \n```","pinned":false,"hidden":false,"mode":"md"},{"id":143,"value":"To hit our API, all we need is a single `http_post_body` call to send it to our server. We use the builtin `json_object()` function to create a JSON object of a city's longitude and latitude, and hardcode the 2022 Christmas date. This will sequentially hit our server with 1000 requests, which takes a few seconds.","pinned":false,"hidden":false,"mode":"md"},{"id":62,"value":"\n```sql\n with suntimes as (\n select rowid as city,\n http_post_body(\n 'http://localhost:3001/suncalc', \n null, \n json_object(\n 'longitude', longitude,\n 'latitude', latitude,\n -- ho ho ho \n 'date', '2022-12-25'\n )\n ) as suncalc_times\n from cities\n ),\n```\n","pinned":false,"hidden":false,"mode":"md"},{"id":145,"value":"Finally, we will extract specific values from the returned JSON object into columns. It may seem verbose, but this code was mostly generated from the [JSON to SQLite helper](https://observablehq.com/@asg017/json-to-sqlite-helper) tool.","pinned":false,"hidden":false,"mode":"md"},{"id":54,"value":"\n```sql\n final as (\n select\n cities.rowid as city,\n suncalc_times ->> '$.nadir' as nadir,\n suncalc_times ->> '$.nightEnd' as night_end,\n suncalc_times ->> '$.nauticalDawn' as nautical_dawn,\n suncalc_times ->> '$.dawn' as dawn,\n suncalc_times ->> '$.sunrise' as sunrise,\n suncalc_times ->> '$.sunriseEnd' as sunrise_end,\n suncalc_times ->> '$.goldenHourEnd' as golden_hour_end,\n suncalc_times ->> '$.solarNoon' as solar_noon,\n suncalc_times ->> '$.goldenHour' as golden_hour,\n suncalc_times ->> '$.sunsetStart' as sunset_start,\n suncalc_times ->> '$.sunset' as sunset,\n suncalc_times ->> '$.dusk' as dusk,\n suncalc_times ->> '$.nauticalDusk' as nautical_dusk,\n suncalc_times ->> '$.night' as night\n from suntimes\n left join cities on cities.rowid = suntimes.city\n )\n select * from final;\n```\n","pinned":false,"hidden":false,"mode":"md"},{"id":148,"value":"Now let's take a look at what the final `christmas_suntimes` table looks like:","pinned":false,"hidden":false,"mode":"md"},{"id":212,"value":"select * from christmas_suntimes;","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:db"},{"id":151,"value":"Perfect! We can join the `city` column on `cities.rowid` when needed, and all suntime values are in their own columns.\n\nHere's an example query: Which US cities will have the longest sunlight duration on christmas?","pinned":false,"hidden":false,"mode":"md"},{"id":216,"value":"select \n cities.name,\n cities.state,\n (\n strftime('%s', sunset) - \n strftime('%s', sunrise)\n ) as daylight_seconds\nfrom christmas_suntimes\nleft join cites as cities on cities.rowid = christmas_suntimes.city\norder by 3 desc\nlimit 10;","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:db"},{"id":235,"value":"Southern states like Florida and Texas, with Hawaii up top, makes sense!\n\nHow about cities with the shortest sunsets?","pinned":false,"hidden":false,"mode":"md"},{"id":226,"value":"select \n cities.name,\n cities.state,\n (strftime('%s', sunset) - strftime('%s', sunrise)) as daylight_seconds\nfrom christmas_suntimes\nleft join cites as cities on cities.rowid = christmas_suntimes.city\norder by 3 asc\nlimit 10;","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:db"},{"id":156,"value":"Sweet, northern states like Washington and North Dakota, with Alaska up top, matches what we expect!","pinned":false,"hidden":false,"mode":"md"},{"id":21,"value":"## Stay in the loop!\n\nThis is the second post in my [SQLite blog series](https://observablehq.com/@asg017/a-new-sqlite-blog-series), where I talk about new tools and SQLite extensions that I am making, guides, and how-to's. Consider subscribing to the [email newsletter](https://buttondown.email/alexgarcia)! **No more than one (1) post a week**.\n\n\n\nAlternatively, there is an [RSS feed](https://buttondown.email/alexgarcia/rss), or you can [follow me on Twitter](https://twitter.com/agarcia_me).\n\nNext up: Building SQLite extensions in Go, new SQLite extensions for parsing URLs and paths","pinned":false,"hidden":false,"mode":"md"}],"files":"$W31","stdlib":"1","titling":"auto"}