Simon Willison’s Weblog

db-to-sqlite 1.0 release. I’ve released version 1.0 of my db-to-sqlite tool, which lets you create a SQLite database copy of any database supported by SQLAlchemy (I’ve tested it against MySQL and PostgreSQL). The tool has a bunch of new features: you can use --redact to redact specific columns, specify --table multiple times to copy a subset of tables, and the --all option now efficiently adds all foreign keys at the end of the import. The project now has unit tests which run against MySQL and PostgreSQL in Travis CI. Also included in the README: a shell one-liner for creating a local SQLite copy of a remote Heroku Postgres database based on extracting the connection string from a Heroku config environment variable.

# 1:35 am / projects, datasette, sqlite, mysql, postgresql, heroku

Choose Boring Technology (via) The definitive write-up of Dan McKinley’s presentation on why you should mostly use “boring” technology rather than going after the latest shiniest stack components. There’s so much accumulated wisdom in here. I particularly like how Dan owns up to having introduced Scala and MongoDB at Etsy before eventually helping remove them and go back to something less exciting and far more predictable. Also neat: the site is generated using Dan’s better-keynote-export tool which helps turn Keynote presentations into a flat web page with notes and images.

# 6:09 pm / dan-mckinley, boring-technology

PugSQL. Interesting new twist on a definitely-not-an-ORM library for Python. With PugSQL you define SQL queries in files, give them names and then load them into a module which allows you to execute them as Python methods with keyword arguments. You can mark statements as only returning a single row (or a single scalar value) with a comment at the top of their file.

# 6:19 pm / orm, sql, dan-mckinley, python

How FZF and ripgrep improved my workflow (via) I’m already a keen user of ripgrep (a crazy-fast grep alternative) but fzf was new to me: it’s a CLI utility that lets you pipe in a list of strings, then gives you a typeahead search interface to search and select a string before returning the selected string to stdout when you hit enter. This means you can pipe it together with other tools to add a dynamic selection step, which has all kinds of delightful combinations. “vi $(find . | fzf)” for example opens vi against the file you selected.

# 5:51 pm / unix, ripgrep

Datasette 0.29 (via) I shipped Datasette 0.29! • ASGI all the way down! Plus a new asgi_wrapper plugin hook letting plugins do all kinds of powerful new things • New mechanism for secret plugin configuration options • Facet by date • ?_through= for joins through m2m tables. Much more.

# 4:26 am / projects, datasette

datasette-auth-github (via) My first big ASGI plugin for Datasette: datasette-auth-github adds the ability to require users to authenticate against the GitHub OAuth API. You can whitelist specific users, or you can restrict access to members of specific GitHub organizations or teams. While it’s structured as a Datasette plugin it also includes ASGI middleware which can be applied to any ASGI application.

# 4:28 am / asgi, oauth, datasette, projects, github

datasette-cors (via) My other Datasette ASGI plugin: this one wraps my asgi-cors project and lets you configure CORS access from a list of domains (or a set of domain wildcards) so you can make JavaScript calls to a Datasette instance from a specific set of other hosts.

# 4:30 am / projects, asgi, datasette, cors

Details of the Cloudflare outage on July 2, 2019 (via) Best retrospective I’ve read in a long time. The outage was caused by a backtracking regex rule that was added to the Web Application Firewall project, which rolls out globally and skips most of Cloudflare’s regular graduar rollout process (delightfully animal themed, named DOG for the dogfooding PoP that their employees use, PIG for the Guinea Pig PoPs reserved for free customers, then Canary for the final step) so that they can deploy counter-measures to newly discovered vulnerabilities as quickly as possible—but the real value in the retro is that it provides an extremely deep insight into how Cloudflare organize, test and manage their changes. Really interesting stuff.

# 5:36 pm / cloudflare, operations, regular-expressions

Unlocking the Department of State’s foreign military training data for good this time (via) I’m so excited about this: Security Force Monitor used Datasette to publish a 200,000 row database of training engagements between the US military and foreign military units, based on their own massive efforts to clean up the official data (from thousands of PDF files). This is pretty much my dream use-case for Datasette, and their future goals are inspiring: “Our hope is that when the next report arrives in a short few months, we will be able to turn it into machine readable data and pass it around the sector in minutes, rather than months.”

# 4:42 am / datasette

healthkit-to-sqlite. Ever since I got an Apple Watch I’ve been itching to get my hands on the step tracking and health data that it’s been collecting for me. I know it’s there in a SQLite database on my wrist, but I couldn’t figure out how to get it! A few days ago I stumbled across the “Export Health Data” button in the iOS Health app, and it turns out it creates a zip file containing XML with a full dump of the data collected by Apple Health. healthkit-to-sqlite is the tool I’ve built that can read that export and use it to create a SQLite database ready to be queried and explored with Datasette. It’s a pretty basic implementation but it’s already giving me access to over 3 million rows of data. Lots of potential here for interesting work with personal analytics.

# 3:34 am / dogsheep, health, datasette, sqlite

When a rewrite isn’t: rebuilding Slack on the desktop. Slack appear to have pulled off the almost impossible: finishing a complete, incremental rewrite of their core product. They moved from jQuery to React over the course of two years, constantly shipping new features as they went along. The biggest gain was in rewriting their code to support multiple workspaces, which means desktop client users no longer have to run a separate copy of Electron for every workspace they are signed into.

# 6:30 pm / rewrites, slack, jquery, react, electron

Targeted diagnostic logging in production (via) Will Sargent defines diagnostic logging as “debug logging statements with an audience”, and proposes controlling this style if logging via a feature flat system to allow detailed logging to be turned on in production against a selected subset if users in order to help debug difficult problems. Lots of great background material in the topic of observability here too.

# 5:44 am / observability, logging

Using memory-profiler to debug excessive memory usage in healthkit-to-sqlite. This morning I figured out how to use the memory-profiler module (and mprof command line tool) to debug memory usage of Python processes. I added the details, including screenshots, to this GitHub issue. It helped me knock down RAM usage for my healthkit-to-sqlite from 2.5GB to just 80MB by making smarter usage of the ElementTree pull parser.

# 8:25 am / memory, xml, profiling, python, elementtree

Repository driven development (via) I’m already a big fan of keeping documentation and code in the same repo so you can update them both from within the same code review, but this takes it even further: in repository driven development every aspect of the code and configuration needed to define, document, test and ship a service live in the service repository—all the way down to the configurations for reporting dashboards. This sounds like heaven.

# 8:41 am / git