Datasette’s new JSON write API: The first alpha of Datasette 1.0

https://simonwillison.net/2022/Dec/2/datasette-write-api/

by simonw • 4 years ago

226 18 4 years ago

simonw8• 4 years ago

I'm really pleased with the Hacker News scraping demo in this - it's an extension of the scraper I wrote back in March, using shot-scraper to execute JavaScript in headless Chrome and write the resulting JSON back to a Git repo: https://simonwillison.net/2022/Mar/14/scraping-web-pages-sho...

My new demo also then pipes that data up to Datasette using curl -X POST - this script here: https://github.com/simonw/scrape-hacker-news-by-domain/blob/...

etrepum• 4 years ago

Just a small note on the shot-scraper post (very cool stuff!): You can use Array.from(xs, fn) instead of Array.from(xs).map(fn). Same semantics but it happens in one pass with less garbage.

simonw8• 4 years ago

I keep on forgetting that shortcut, thanks!

pedrovhb2• 4 years ago

That is really cool, and I'll definitely be using it!

Since I found out about sqlite-utils being able to trivially put JSON into a sqlite database without needing to stop and write out a whole schema, I've used it a few times. In the process I did think to myself, "why isn't there anything that just lets me dump JSON into an endpoint?". I didn't get around to doing it, and I actually thought maybe it wasn't such a great idea if it hadn't been done yet, because it seemed so obvious. Glad to see I'm not crazy :)

The flexibility and ease of getting different output formats (including charts!) from Datasette is something I hadn't considered, and it looks amazing!

simonw8• 4 years ago

Something I've been contemplating is if I should add a mode that lets you make unauthenticated write API calls from localhost - like Redis and MongoDB do.

It would need to be an explicit opt-in thing because I've seen what happens if you release software that is open by default, and it's not good!

fbdab1033• 4 years ago

I like the idea of having some way to do unauthenticated calls, but would it be possible to layer on some additional granularity or would that be asking too much when the security infrastructure already exists?

That is, I might want to be able to append data without any ceremony (logs?), but schema changes or data mutation would require credentials.

pedrovhb2• 4 years ago

Or a bit more generically, having a configurable allowlist of hosts, so I can make my ESP32 just a dumb "sensors on a microcontroller" thing pushing data to a RPi on my local home network, which has static IPs provided to certain devices via DHCP. Having to add an auth header isn't a great deal of work, but it'd be handy to be able to try things out quickly. IMO the great thing about all this is how frictionless it is.

fbdab1033• 4 years ago

Very satisfied Datasette user here. There is no better way to immediately share structured data with a minimum of infrastructure. I have personally used it on tables with 20-50 million rows without issue.

As hinted in the opening, I am wondering if I could utilize the new API as a low-performance alternative to Redis. All of the SQLite guarantees, easy network access, and optionally backups through Litestream.

simonw8• 4 years ago

I'd never really thought about it as an alternative for Redis, but yeah if you can handle the huge performance decrease (which for many projects I imagine wouldn't matter in the slightest) that could maybe work pretty well!

fbdab1033• 4 years ago

Heh, well I am a solo developer with very low performance requirements so fewer moving pieces is the name of the game. Redis Just Works, but is yet another thing to manage. I know how to query SQL – I interface with Redis so infrequently I have to lookup the commands every time.

If/when the “library” feature gets incorporated, I could even imagine something crazy like using a library of SQLite databases to handle different backing services. ‘Redis’ cache server, job scheduler, log endpoint. Madness, but these are the idle thoughts before the caffeine has fully circulated.

mongol• 4 years ago

I am a little lazy so only skimmed the article: is there UPSERT support?

simonw8• 4 years ago

Not yet, but I hope to ship that in the next alpha in a few days time: https://github.com/simonw/datasette/issues/1878

nose-wuzzy-pad• 4 years ago

Just dropping in (long time HN lurker, first time HN poster) to say thanks for such a great capability in datasette and datasette-lite. I use sqlite_utils in numerous solutions to insert/upsert various json data into sqlite databases for various purposes. I use datasette/datasette-lite to host data products for consumers who need rapid insight but are not "sql jockeys."

I think this mutation capability would be great for some of my data logging workflows. I push json formatted telemetry data from different types of devices/sensors over tailscale to a central collector. Combined with clickhouse's adapter to sqlite databases, this could make for a very nice self-contained data reporting framework where all the source data flows through datasette to a set of sqlite files organized by topic. It will be important to me in deploying new event senders to have straightforward authentication I can bake pre-authenticated connection schemes into standard images that are installed without customization. Alternatively, configuring datasette so a specific set of ip addresses / cidrs is just granted insert to specific dbs/tables would work fine.

Thanks again. Great stuff!

Datasette’s new JSON write API: The first alpha of Datasette 1.0

Related Stories