datasette.io

I've come around to almost the opposite approach. I pull all of the data I can get my hands on (from Twitter, GitHub, Swarm, Apple Health, Pocket, Apple Photos and more) into SQLite database tables that match the schema of the system that they are imported from. I call this family of scripts Dogsheep - https://dogsheep.github.io/ For my own personal Dogsheep (https://simonwillison.net/2020/Nov/14/personal-data-warehous...) that's 119 tables right now. Then I use SQL queries against those tables to extract and combine data in ways that are useful to me. If the schema of the systems I am importing from changes, I can update my queries to compensate for the change. This protects me from having to solve for a standard schema up front - I take whatever those systems give me. But it lets me combine and search across all of the data from dispara

My version of this is https://dogsheep.github.io/ - the idea is to pull your digital footprint from various different sources (Twitter, Foursquare, GitHub etc) into SQLite database files, then run Datasette on top to explore them. On top of that I built a search engine called Dogsheep Beta which builds a full-text search index across all of the different sources and lets you search in one place: https://github.com/dogsheep/dogsheep-beta You can see a live demonstration of that search engine on the Datasette website: https://datasette.io/-/beta?q=dogsheep The key difference I see with Apollo is that Dogsheep separates fetching of data from search and indexing, and uses SQLite as the storage format. I'm using a YAML configuration to define how the search index should work: https://github.com/simonw/datasette.io/blob/main/templates/d... - it

I've been exploring this technique more over the past year and I really like it - https://datasette.io (code at https://github.com/simonw/datasette.io ) is a more recent and much more complicated example. Extracting links from markdown and using them to populate some additional columns or tables at build time would be pretty straight forward.

I've been using my Datasette tool, originally intended for exploring databases, to build full-blown websites and it's working surprisingly well. It supports Jinja templates and runs on SQLite databases so it's actually a good fit for semi-static sites. Here's what Datasette looks like by default: - https://latest.datasette.io And here are three websites that are actually just Datasette with some custom templates and plugins: - https://datasette.io (data: https://datasette.io/content code: https://github.com/simonw/datasette.io ) - https://til.simonwillison.net (data: https://til.simonwillison.net/tils code: https://github.com/simonw/til ) - https://www.niche-museums.com (data: https://www.niche-museums.com/browse code: https://github.com/simonw/museums ) They're all hosted on either Vercel or Google Cloud Run at the cost of no more than a

I run SQLite in serverless environments (Cloud Run, Vercel, Heroku) for dozens of projects... but the trick is that they all treat the database as a read-only asset. If I want to deploy updated data, I build a brand new image and deploy the application bundled with the data. I tend to run the deploys for these (including the database build) in GitHub Actions workflows. This works really well, but only for applications that don't need to apply updates more than a few times an hour! If you have a constant stream of updates I still think you're better off using a hosted database like Heroku PostgreSQL or Google Cloud SQL. One example of a site I deploy like that is https://datasette.io/ - it's built and deployed by this GitHub Actions workflow here: https://github.com/simonw/datasette.io/blob/main/.github/wor...