Simon Willison's Weblog: datasette-desktop

Weeknotes: Datasette, sqlite-utils, Datasette Desktop

2022-07-20T23:13:01+00:00

A flurry of releases this week, including a new Datasette alpha and a fixed Datasette Desktop.

datasette 0.62a1

Work on Datasette Cloud continues - the last 10% of the work needed for the beta launch is trending towards taking 90% of the time. It's been driving all sorts of fixes to the wider Datasette ecosystem, which is nice.

I ran into a bug which would have been easier to investigate using Sentry. The datasette-sentry plugin wasn't working right, and it turned out I needed a new handle_exception() plugin hook to fix it. This was the impetus I needed to push out a new Datasette alpha - I like to get new hooks into an alpha as quickly as possible so I can depend on that Datasette version from alpha releases of plugins.

Here are some other highlights from the alpha's release notes:

The render_cell() plugin hook is now also passed a row argument, representing the sqlite3.Row object that is being rendered. (#1300)

A neat thing about Pluggy is that you can add new arguments to existing plugin hooks without breaking plugins that already use them.

New --nolock option for ignoring file locks when opening read-only databases. (#1744)

Since the very start of the project Datasette has suggested trying the following command to start exploring your Google Chrome history, which is stored using SQLite:

datasette ~/Library/Application\ Support/Google/Chrome/Default/History

I'm not sure when this changed, but I tried running the command recently and got the following error:

sqlite3.OperationalError: database is locked

Since Datasette opens databases in read-only mode I didn't see why a lock like this should be respected. It turns out SQLite can be told to ignore locks like so:

sqlite3.connect(
    "file:places.sqlite?mode=ro&nolock=1",
    uri=True
)

So I added a --nolock option to Datasette which does exactly that:

datasette ~/Library/Application\ Support/Google/Chrome/Default/History --nolock

Datasette now has a Discord community.

Inspired by 6 More Things I Learned Building Snowpack to 20,000 Stars (Part 2) by Fred K. Schott I finally setup a chat community for Datasette, using Discord.

It's attracted 88 members already! You can join it here. I wrote detailed notes on how I configured it in this issue.

Database file downloads now implement conditional GET using ETags. (#1739)

This is a change I made to support Datasette Lite - I noticed that the WASM version of Datasette was downloading a fresh database every time, so I added ETag support to encourage browsers to avoid a duplicate download and use a cached copy of the database file instead, provided it hasn't changed.

Datasette Desktop

Datasette Desktop was hanging on launch. Paul Everitt figured out a fix, which it took me way too long to get around to applying.

I finally shipped that in Datasette Desktop 0.2.2, but I wanted to reduce the chances of this happening again as much as possible. Datasette Desktop's Elecron tests used the spectron test harness, but that's marked as deprecated.

I'm a big fan of Playwright and I was optimistic to see that it has support for testing Electron apps. I figured out how to use that with Datasette Desktop and run the tests in GitHub Actions: I wrote up what I learned in a TIL, Testing Electron apps with Playwright and GitHub Actions.

sqlite-utils 3.28

Annotated release notes:

New table.duplicate(new_name) method for creating a copy of a table with a matching schema and row contents. Thanks, David. (#449)

New sqlite-utils duplicate data.db table_name new_name CLI command for Duplicating tables. (#454)

davidleejy suggested the table.duplicate() method and contributed an implementation. This was the impetus for pushing out a fresh release.

I added the CLI equivalent, sqlite-utils duplicate.

sqlite_utils.utils.rows_from_file() is now a documented API. It can be used to read a sequence of dictionaries from a file-like object containing CSV, TSV, JSON or newline-delimited JSON. It can be passed an explicit format or can attempt to detect the format automatically. (#443)

sqlite_utils.utils.TypeTracker is now a documented API for detecting the likely column types for a sequence of string rows, see Detecting column types using TypeTracker. (#445)

sqlite_utils.utils.chunks() is now a documented API for splitting an iterator into chunks. (#451)

I have a policy that any time I need to use an undocumented method from sqlite-utils in some other project I file an issue to add that to the documented API surface.

I had used rows_from_file() and TypeTracker in datasette-socrata.

sqlite-utils enable-fts now has a --replace option for replacing the existing FTS configuration for a table. (#450)

The create-index, add-column and duplicate commands all now take a --ignore option for ignoring errors should the database not be in the right state for them to operate. (#450)

This was inspired by my TIL Ignoring errors in a section of a Bash script - a trick I had to figure out because one of my scripts needed to add columns and enable FTS but only if those changes had not been previously applied.

In looking into that I spotted inconsistencies in the design of the sqlite-utils commands, so I fixed those as much as I could while still maintaining backwards compatibility with the 3.x releases.

Releases this week

s3-ocr: 0.5 - (5 releases total) - 2022-07-19
Tools for running OCR against files stored in S3
datasette-graphql: 2.1.1 - (36 releases total) - 2022-07-18
Datasette plugin providing an automatic GraphQL API for your SQLite databases
datasette-sentry: 0.2a0 - (3 releases total) - 2022-07-18
Datasette plugin for configuring Sentry
datasette: 0.62a1 - (112 releases total) - 2022-07-18
An open source multi-tool for exploring and publishing data
sqlite-utils: 3.28 - (102 releases total) - 2022-07-15
Python CLI utility and library for manipulating SQLite databases
datasette-publish-vercel: 0.14 - (21 releases total) - 2022-07-13
Datasette plugin for publishing data using Vercel
datasette-app: 0.2.2 - (4 releases total) - 2022-07-13
The Datasette macOS application
datasette-app-support: 0.11.6 - (19 releases total) - 2022-07-12
Part of https://github.com/simonw/datasette-app

TIL this week

Tags: datasette, weeknotes, datasette-cloud, sqlite-utils, annotated-release-notes, datasette-desktop

Weeknotes number 100

2021-09-19T01:34:37+00:00

This entry marks my 100th weeknotes, which I've managed to post once a week (plus or minus a few days) consistently since 13th September 2019.

I started writing weeknotes to add some accountability to the work I was doing during my JSK fellowship year at Stanford. The fellowship ended over a year ago but I've stuck to the habit - I've been finding it really helpful as a structured approach to thinking about my work every week, and it occasionally helps motivate me to get things done enough that I have something I can write about!

Datasette Desktop 0.2.0

My big achievement this week was Datasette Desktop 0.2.0 (and the 0.2.1 patch release that followed). I published annotated release notes for that a few days ago. I'm really pleased with the release - I think Datasette as a desktop application is going to significantly increase the impact of the project.

I also sent out an issue of the Datasette Newsletter promoting the new desktop application.

Datasette Desktop for Windows

I did a quick research spike to investigate the feasibility of publishing a Windows version of Datasette Desktop. To my surprise, I managed to get a working prototype going with just half a small amount of work:

So that was one heck of a lot easier than I expected... pic.twitter.com/BDa4gvkgnd
- Simon Willison (@simonw) September 15, 2021

Electron claims to solve cross-platform development and it seems to uphold that claim pretty well!

I'm still quite a bit of work away from having a release: I need to learn how to build and sign Windows installers. But this is a very promising first step.

json-flatten

I've started thinking about how I can enable Datasette Desktop users to configure plugins without having to hand-edit plugin configuration JSON (the current mechanism).

This made me take another look at a small library I released a couple of years ago, json-flatten, which turns a nested JSON object into a set of flat key/value pairs suitable for editing using an HTML form and then unflattens that data later on.

>>> import json_flatten
>>> json_flatten.flatten({"foo": {"bar": [1, True, None]}})
{'foo.bar.[0]$int': '1', 'foo.bar.[1]$bool': 'True', 'foo.bar.[2]$none': 'None'}
>>> json_flatten.unflatten(_)
{'foo': {'bar': [1, True, None]}}

It turns out a few people have been using the library, and had filed issues - I released version 0.2 with a couple of fixes.

TIL this week

Releases this week

datasette-statistics: 0.1.1 - (2 releases total) - 2021-09-16
SQL statistics functions for Datasette
json-flatten: 0.2 - 2021-09-14
Python functions for flattening a JSON object to a single dictionary of pairs, and unflattening that dictionary back to a JSON object
datasette-app: 0.2.1 - (3 releases total) - 2021-09-13
The Datasette macOS application
datasette-app-support: 0.11.5 - (18 releases total) - 2021-09-13
Part of https://github.com/simonw/datasette-app
datasette-write: 0.2 - (3 releases total) - 2021-09-11
Datasette plugin providing a UI for executing SQL writes against the database
datasette-schema-versions: 0.2 - (2 releases total) - 2021-09-11
Datasette plugin that shows the schema version of every attached database
datasette-import-table: 0.3 - (6 releases total) - 2021-09-08
Datasette plugin for importing tables from other Datasette instances

Tags: projects, datasette, weeknotes, datasette-desktop

Datasette Desktop 0.2.0: The annotated release notes

2021-09-13T23:30:24+00:00

Datasette Desktop is a new macOS desktop application version of Datasette, an "open source multi-tool for exploring and publishing data" built on top of SQLite. I released the first version last week - I've just released version 0.2.0 (and a 0.2.1 bug fix) with a whole bunch of critical improvements.

You can see the release notes for 0.2.0 here, but as I've done with Datasette in the past I've decided to present an annotated version of those release notes providing further background on each of the new features.

The plugin directory

A new plugin directory for installing new plugins and upgrading or uninstalling existing ones. Open it using the "Plugins -> Install and Manage Plugins..." menu item. #74

This was the main focus for the release. Plugins are a key component of both Datasette and Datasette Desktop: my goal is for Datasette to provide a robust core for exploring databases, with a wide array of plugins that support any additional kind of visualization, exploration or data manipulation capability that a user might want.

Datasette Desktop goes as far as bundling an entire standalone Python installation just to ensure that plugins will work correctly, and invisibly sets up a dedicated Python virtual environment for plugins to install into when you first run the application.

The first version of the app allowed users to install plugins by pasting their name into a text input field. Version 0.2.0 is a whole lot more sophisticated: the single input field has been replaced by a full plugin directory interface that shows installed v.s. available plugins and provides "Install", "Upgrade" and "Uninstall" buttons depending on the state of the plugin.

When I set out to build this I knew I wanted to hit this JSON API on datasette.io to fetch the list of plugins, and I knew I wanted a simple searchable index page. The I realized I also wanted faceted search, so I could filter for installed vs not-yet-installed plugins.

Datasette's built-in table interface already implements faceted search! So I decided to use that, with some custom templates to add the install buttons and display the plugins in a more suitable format.

The first challenge was getting the latest list of plugins into my Datasette instance. I built this into the datasette-app-support plugin using the startup() plugin hook - every time the server starts up it hits that API and populates an in-memory table with the returned data.

The data from the API is then extended with four extra columns:

"installed" is set to "installed" or "not installed" depending on whether the plugin has already been installed by the user
"Installed_version" is the currently installed version of the plugin
"upgrade" is the string "upgrade available" or None - allowing the user to filter for just plugins that can be upgraded
"default" is set to 1 if the plugin is a default plugin that came with Datasette

The data needed to build the plugin table is gathered by these three lines of code:

plugins = httpx.get(
     "https://datasette.io/content/plugins.json?_shape=array"
).json()
# Annotate with list of installed plugins
installed_plugins = {
    plugin["name"]: plugin["version"]
    for plugin in (await datasette.client.get("/-/plugins.json")).json()
}
default_plugins = (os.environ.get("DATASETTE_DEFAULT_PLUGINS") or "").split()

The first line fetches the full list of known plugins from the Datasette plugin directory

The second makes an internal API call to the Datasette /-/plugins.json endpoint using the datasette.client mechanism to discover what plugins are currently installed and their versions.

The third line loads a space-separated list of default plugins from the DATASETTE_DEFAULT_PLUGINS environment variable.

That last one deserves further explanation. Datasette Desktop now ships with some default plugins, and the point of truth for what those are lives in the Electron app codebase - because that's where the code responsible for installing them is.

Five plugins are now installed by default: datasette-vega, datasette-cluster-map, datasette-pretty-json, datasette-edit-schema and datasette-configure-fts. #81

The plugin directory needs to know what these defaults are so it can avoid showing the "uninstall" button for those plugins. Uninstalling them currently makes no sense because Datasette Desktop installs any missing dependencies when the app starts, which would instantly undo the user's uninstall action decision.

An environment variable felt like the most straight-forward way to expose that list of default plugins to the underlying Datasette server!

I plan to make default plugins uninstallable in the future but doing so require a mechanism for persisting user preference state which I haven't built yet (see issue #101).

A log on the loading screen

The application loading screen now shows a log of what is going on. #70

The first time you launch the Datasette Desktop application it creates a virtual environment and installs datasette, datasette-app-support and the five default plugins (plus their dependencies) into that environment.

This can take quite a few seconds, during which the original app would show an indeterminate loading indicator.

Personally I hate loading indicators which don't show the difference between something that's working and something that's eternally hung. Since I can't estimate how long it will take, I decided to pipe the log of what the pip install command is doing to the loading screen itself.

For most users this will be meaningless, but hopefully will help communicate "I'm installing extra stuff that I need". Advanced users may find this useful though, especially for bug reporting if something goes wrong.

Under the hood I implemented this using a Node.js EventEmitter. I use the same trick to forward server log output to the "Debug -> Show Sever Log" interface.

Example CSV files

The welcome screen now invites you to try out the application by opening interesting example CSV files, taking advantage of the new "File -> Open CSV from URL..." feature. #91

Previously Datasette Desktop wouldn't do anything at all until you opened up a CSV or SQLite database, and I have a hunch that unlike me most people don't have good examples of those to hand at all times!

The new welcome screen offers example CSV files that can be opened directly from the internet. I implemented this using a new API at datasette.io/content/example_csvs (add .json for the JSON version) which is loaded by code running on that welcome page.

I have two examples at the moment, for the Squirrel Census and the London Fire Brigade's animal rescue data. I'll be adding more in the future.

The API itself is a great example of the Baked Data architectural pattern in action: the data itself is stored in this hand-edited YAML file, which is compiled to SQLite every time the site is deployed.

To get this feature working I added a new "Open CSV from URL" capability to the app, which is also available in the File menu. Under the hood this works by passing the provided URL to the new /-/open-csv-from-url API endpoint. The implementation of this was surprisingly fiddly as I wanted to consume the CSV file using an asynchronous HTTP client - I ended up using an adaption of some example code from the aiofile README.

Recently opened files and "Open with Datasette"

Recently opened .db and .csv files can now be accessed from the new "File -> Open Recent" menu. Thanks, Kapilan M! #54

This was the project's first external contribution! Kapilan M figured out a way to hook into the macOS "recent files" mechanism from Electron, and I expanded that to cover SQLite database in addition to CSV files.

When a recent file is selected, Electron fires the "open-file" event. This same event is fired when a file is opened using "Open With -> Datasette" or dragged onto the application's dock.

This meant I needed to tell the difference between a CSV or a SQLite database file, which I do by checking if the first 16 bytes of the file match the SQLite header of SQLite format 3\0.

.db and .csv files can now be opened in Datasette starting from the Finder using "Right Click -> Open With -> Datasette". #40

Registering Datasette as a file handler for .csv and .db was not at all obvious. It turned out to involve adding the following to the Electron app's package.json file:

  "build": {
    "appId": "io.datasette.app",
    "mac": {
      "category": "public.app-category.developer-tools",
      "extendInfo": {
        "CFBundleDocumentTypes": [
          {
            "CFBundleTypeExtensions": [
              "csv",
              "tsv",
              "db"
            ],
            "LSHandlerRank": "Alternate"
          }
        ]
      }

The Debug Menu

A new Debug menu can be enabled using Datasette -> About Datasette -> Enable Debug Menu".

The debug menu existed previously in development mode, but with 0.2.0 I decided to expose it to end users. I didn't want to show it to people who weren't ready to see it, so you have to first enable it using a button on the about menu.

The most interesting option there is "Run Server Manually".

Most of the time when you are using the app there's a datasette Python server running under the hood, but it's entirely managed by the Node.js child_process module.

When developing the application (or associated plugins) it can be useful to manually run that server rather than having it managed by the app, so you can see more detailed error messages or even add the --pdb option to drop into a debugger should something go wrong.

To run that server, you need the Electron app to kill its own version... and you then need to know things like what port it was running on and which environment variables it was using.

Here's what you see when you click the "Run Server Manually" debug option:

Here's that command in full:

DATASETTE_API_TOKEN="0ebb45444ba4cbcbacdbcbb989bb0cd3aa10773c0dfce73c0115868d0cee2afa" DATASETTE_SECRET="4a8ac89d0d269c31d99059933040b4511869c12dfa699a1429ea29ee3310a850" DATASETTE_DEFAULT_PLUGINS="datasette datasette-app-support datasette-vega datasette-cluster-map datasette-pretty-json datasette-edit-schema datasette-configure-fts datasette-leaflet" /Users/simon/.datasette-app/venv/bin/datasette --port 8002 --version-note xyz-for-datasette-app --setting sql_time_limit_ms 10000 --setting max_returned_rows 2000 --setting facet_time_limit_ms 3000 --setting max_csv_mb 0

This is a simulation of the command that the app itself used to launch the server. Pasting that into a terminal will produce an exact copy of the original process - and you can add --pdb or other options to further customize it.

Bonus: Restoring the in-memory database on restart

This didn't make it into the formal release notes, but it's a fun bug that I fixed in this release.

Datasette Desktop defaults to opening CSV files in an in-memory database. You can import them into an on-disk database too, but if you just want to start exploring CSV data in Datasette I decided an in-memory database would be a better starting point.

There's one problem with this: installing a plugin requires a Datasette server restart, and restarting the server clears the content of that in-memory database, causing any tables created from imported CSVs to disappear. This is confusing!

You can follow my progress on this in issue #42: If you open a CSV and then install a plugin the CSV table vanishes. I ended up solving it by adding code that dumps the "temporary" in-memory database to a file on disk before a server restart, restarts the server, then copies that disk backup into memory again.

This works using two custom API endpoints added to the datasette-app-support plugin:

POST /-/dump-temporary-to-file with {"path": "/path/to/backup.db"} dumps the contents of that in-memory temporary database to the specified file.
POST /-/restore-temporary-from-file with {"path": "/path/to/backup.db"} restors the content back again.

These APIs are called from the startOrRestart() method any time the server restarts, using a file path generated by Electron using the following:

backupPath = path.join(
  app.getPath("temp"),
  `backup-${crypto.randomBytes(8).toString("hex")}.db`
);

The file is deleted once it has been restored.

After much experimentation, I ended up using the db.backup(other_connection) method that was added to Python's sqlite3 module in Python 3.7. Since Datasette Desktop bundles its own copy of Python 3.9 I don't have to worry about compatibility with older versions at all.

The rest is in the milestone

If you want even more detailed notes on what into the release, each new feature is included in the 0.2.0 milestone, accompanied by a detailed issue with screenshots (and even a few videos) plus links to the underlying commits.

Tags: projects, releasenotes, datasette, annotated-release-notes, electron, datasette-desktop

Datasette Desktop - a macOS desktop application for Datasette

2021-09-08T19:15:46+00:00

I just released version 0.1.0 of the new Datasette macOS desktop application, the first version that end-users can easily install. I would very much appreciate your help testing it out!

Datasette Desktop

Datasette is "an open source multi-tool for exploring and publishing data". It's a Python web application that lets you explore data held in SQLite databases, plus a growing ecosystem of plugins for visualizing and manipulating those databases.

Datasette is aimed at data journalists, museum curators, archivists, local governments, scientists, researchers and anyone else who has data that they wish to explore and share with the world.

There's just one big catch: since it's a Python web application, those users have needed to figure out how to install and run Python software in order to use it. For people who don't live and breath Python and the command-line this turns out to be a substantial barrier to entry!

Datasette Desktop is my latest attempt at addressing this problem. I've packaged up Datasette, SQLite and a full copy of Python such that users can download and uncompress a zip file, drag it into their /Applications folder and start using Datasette, without needing to know that there's a Python web server running under the hood (or even understand what a Python web server is).

Please try it out, and send me feedback and suggestions on GitHub.

What the app does

This initial release has a small but useful set of features:

Open an existing SQLite database file and offer all of Datasette's functionality, including the ability to explore tables and to execute arbitrary SQL queries.
Open a CSV file and offer the Datasette table interface (example here). By default this uses an in-memory database that gets cleared when the app shuts down, or you can...
Import CSV files into tables in on-disk SQLite databases (including creating a new blank database first).
By default the application runs a local web server which only accepts connections from your machine... but you can change that in the "File -> Access Control" menu to allow connections from anyone on your network. This includes Tailscale networks too, allowing you to run the application on your home computer and then access it securely from other devices such as your mobile phone anywhere in the world.
You can install plugins! This is the most exciting aspect of this initial release: it's already in a state where users can customize it and developers can extend it, either with Datasette's existing plugins (69 and counting) or by writing new ones.

How the app works

There are three components to the app:

A macOS wrapper application
Datasette itself
The datasette-app-support plugin

The first is the macOS application itself. This is currently written with Electron, and bundles a full copy of Python 3.9 (based on python-build-standalone by Gregory Szorc). Bundling Python is essential: the principal goal of the app is to allow people to use Datasette who aren't ready to figure out how to install their own Python environment. Having an isolated and self-contained Python is also a great way of avoiding making XKCD 1987 even worse.

The macOS application doesn't actually include Datasette itself. Instead, on first launch it creates a new Python virtual environment (currently in ~/.datasette-app/venv, feedback on that location welcome) and installs the other two components: Datasette and the datasette-app-support plugin.

Having a dedicated virtual environment is what enables the "Install Plugin" menu option. When a plugin is installed the macOS application runs pip install name-of-plugin and then restarts the Datasette server process, causing it to load that new plugin.

The datasette-app-support plugin is designed exclusively to work with this application. It adds API endpoints that the Electron shell can use to trigger specific actions, such as "import from this CSV file" or "attach this SQLite database" - these are generally triggered by macOS application menu items.

It also adds a custom authentication mechanism. The user of the app should have special permissions: only they should be able to import a CSV file from anywhere on their computer into Datasette. But for the "network share" feature I want other users to be able to access the web application.

An interesting consequence of installing Datasette on first-run rather than bundling it with the application is that the user will be able to upgrade to future Datasette releases without needing to re-install the application itself.

How I built it

I've been building this application completely in public over the past two weeks, writing up my notes and research in GitHub issues as I went (here's the initial release milestone).

I had to figure out a lot of stuff!

First, Electron. Since almost all of the user-facing interface is provided by the existing Datasette web application, Electron was a natural fit: I needed help powering native menus and bundling everything up as an installable application, which Electron handles extremely well.

I also have ambitions to get a Windows version working in the future, which should share almost all of the same code.

Electron also has fantastic initial developer onboarding. I'd love to achieve a similar level of quality for Datasette some day.

The single biggest challenge was figuring out how to bundle a working copy of the Datasette Python application to run inside the Electron application.

My initial plan (touched on last week) was to compile Datasette and its dependencies into a single executable using PyInstaller or PyOxidizer or py2app.

These tools strip down a Python application to the minimal required set of dependencies and then use various tricks to compress that all into a single binary. They are really clever. For many projects I imagine this would be the right way to go.

I had one big problem though: I wanted to support plugin installation. Datasette plugins can have their own dependencies, and could potentially use any of the code from the Python standard library. This means that a stripped-down Python isn't actually right for this project: I need a full installation, standard library and all.

Telling the user they had to install Python themselves was an absolute non-starter: the entire point of this project is to make Datasette available to users who are unwilling or unable to jump through those hoops.

Gregory Szorc built PyOxidizer, and as part of that he built python-build-standalone:

This project produces self-contained, highly-portable Python distributions. These Python distributions contain a fully-usable, full-featured Python installation as well as their build artifacts (object files, libraries, etc).

Sounds like exactly what I needed! I opened a research issue, built a proof-of-concept and decided to commit to that as the approach I was going to use. Here's a TIL that describes how I'm doing this: Bundling Python inside an Electron app

(I find GitHub issue threads to be the ideal way of exploring these kinds of areas. Many of my repositories have a research label specifically to track them.)

The last key step was figuring out how to sign the application, so I could distribute it to other macOS users without them facing this dreaded dialog:

It turns out there are two steps to this these days: signing the code with a developer certificate, and then "notarizing" it, which involves uploading the bundle to Apple's servers, having them scan it for malicious code and attaching the resulting approval to the bundle.

I was expecting figuring this out to be a nightmare. It ended up not too bad: I spent two days on it, but most of the work ended up being done by electron-builder - one of the biggest advantages of working within the Electron ecosystem is that a lot of people have put a lot of effort into these final steps.

I was adamant that my eventual signing and notarization solution should be automated using GitHub Actions: nothing defangs a frustrating build process more than good automation! This made things a bit harder because all of the tutorials and documentation assumed you were working with a GUI, but I got there in the end. I wrote this all up as a TIL: Signing and notarizing an Electron app for distribution using GitHub Actions (see also Attaching a generated file to a GitHub release using Actions).

What's next

I announced the release last night on Twitter and I've already started getting feedback. This has resulted in a growing number of issues under the usability label.

My expectation is that most improvements made for the benefit of Datasette Desktop will benefit the regular Datasette web application too.

There's also a strategic component to this. I'm investing a lot of development work in Datasette, and I want that work to have the biggest impact possible. Datasette Desktop is an important new distribution channel, which also means that any time I add a new feature to Datasette or build a new plugin the desktop application should see the same benefit as the hosted web application.

If I'm unlucky I'll find this slows me down: every feature I build will need to include consideration as to how it affects the desktop application.

My intuition currently is that this trade-off will be worthwhile: I don't think ensuring desktop compatibility will be a significant burden, and the added value from getting new features almost for free through a whole separate distribution channel should hopefully be huge!

TIL this week

Releases this week

datasette-import-table: 0.3 - (6 releases total) - 2021-09-08
Datasette plugin for importing tables from other Datasette instances
datasette-app: Datasette Desktop 0.1.0 - 2021-09-08
Electron app wrapping Datasette
datasette-app-support: 0.6 - (8 releases total) - 2021-09-07
Part of https://github.com/simonw/datasette-app
pids: 0.1.2 - 2021-09-07
A tiny Python library for generating public IDs from integers

Tags: projects, datasette, weeknotes, github-actions, electron, datasette-desktop

Datasette Desktop 0.1.0

2021-09-08T05:14:32+00:00

Datasette Desktop 0.1.0

This is the first installable version of the new Datasette Desktop macOS application I’ve been building. Please try it out and leave feedback on Twitter or on the GitHub Discussions thread linked from the release notes.

Via @simonw

Tags: projects, datasette, electron, datasette-desktop

Building a desktop application for Datasette (and weeknotes)

2021-08-30T05:13:54+00:00

This week I started experimenting with a desktop application version of Datasette - with the goal of providing people who aren't comfortable with the command-line the ability to get Datasette up and running on their own personal computers.

Update 8th September 2021: I made a bunch more progress over the week following this post, see Datasette Desktop—a macOS desktop application for Datasette for details or download the app to try it out.

Why a desktop application?

On Monday I kicked off an enormous Twitter conversation when I posted:

I wonder how much of the popularity of R among some communities in comparison to Python comes down to the fact that with R you can install the RStudio desktop application and you're ready to go

This ties into my single biggest complaint about Python: it's just too hard for people to get started with. Setting up a Python development environment for the first time remains an enormous barrier to entry.

I later put this in stronger terms:

The more I think about this the more frustrated I get, thinking about the enormous amount of human potential that's squandered because the barriers to getting started learning to program are so much higher than they need to be

Which made me think of glass houses. My own Datasette project has exactly the same problem: to run it locally you need to install Python and then install Datasette! Mac users can use Homebrew, but telling newcomers to install Homebrew first isn't particularly welcoming either.

Ideally, I'd like people to be able to install a regular desktop application and start using Datasette that way, without even needing to know that it's written in Python.

There's been an open issue to get Datasette running as a standalone binary using PyInstaller since November 2017, with quite a bit of research.

But I want a UI as well: I don't want to have to teach new users how to install and run a command-line application if I can avoid it.

So I decided to spend some time researching Electron to see how hard it would be to make a basic Datasette desktop application a reality.

Progress so far

The code I've written so far can be found in the simonw/datasette.app repository on GitHub. The app so far does the following:

Run a datasette server on localhost attached to an available port (found using portfinder) which terminates when the app quits.
Open a desktop window showing that Datasette instance once the server has started.
Allow additional windows onto the same instance to be opened using the "New Window" menu option or the Command+N keyboard shortcut.
Provides an "Open Database..." menu option (and Command+O shortcut) which brings up a file picker to allow the user to select a SQLite database file to open - once selected, this is attached to the Datasette instance and any windows showing the Datasette homepage are reloaded.

Here's a video demo showing these features in action:

It's very much an MVP, but I'm encouraged by the progress so far. I think this is enough of a proof of concept to be worth turning this into an actual usable product.

How this all works

There are two components to the application.

The first is a thin Electron shell, responsible for launching the Python server, managing windows and configuring the various desktop menu options used to configure it. The code for that lives in main.js.

The second is a custom Datasette plugin that adds extra functionality needed by the application. Currently this consists of a tiny bit of extra CSS to make the footer stick to the bottom of the window, and a custom API endpoint at /-/open-database-file which is called by the menu option for opening a new database.

Initial impressions of Electron

I know it's cool to knock Electron, but in this case it feels like exactly the right tool for the job. Datasette is already a web application - what I need is a way to hide the configuration of that web application behind an icon, and re-present the interface in a way that feels more like a desktop application.

This is my first time building anything with Electron - here are some of my initial impressions.

The initial getting started workflow is really good. I started out with their Quick Start and was up and running with a barebones application that I could start making changes to in just a few minutes.
The documentation is pretty good, but it leans more towards being an API reference. I found myself googling for examples of different things I wanted to do pretty often.
The automated testing situation isn't great. I'm using Spectron and Mocha for my initial (very thin) tests - I got them up and running in GitHub Actions, but I've already run into some limitations:
- For some reason each time I run the tests an Electron window (and datasette Python process) is left running. I can't figure out why this is.
- There doesn't appear to be a way for tests to trigger menu items, which is frustrating because most of the logic I've written so far deals with menu items! There is an open issue for this dating back to May 2016.
I haven't yet managed to package my app. This is clearly going to be the biggest challenge.

Up next: packaging the app

I was hoping to get to this before writing up my progress in these weeknotes, but it looks like it's going to be quite a challenge.

In order to produce an installable macOS app (I'll dive into Windows later) I need to do the following:

Build a standalone Datasette executable, complete with the custom plugin, using PyInstaller
Sign that binary with an Apple developer certificate
Build an Electron application that bundles a copy of that datasette binary
Sign the resulting Electron application

I'm expecting figuring this out to be a long-winded and frustrating experience, which is more the fault of Apple than of Electron. I'm tracking my progress on this in issue #7.

Datasette 0.59a2

I pushed out a new alpha of Datasette earlier this week, partly driven by work I was doing on datasette.app.

The biggest new feature in this release is a new plugin hook: register_commands() - which lets plugins add additional commands to Datasette, e.g. datasette verify name-of-file.db.

I released a new plugin that exercises this hook called datasette-verify. Past experience has shown me that it's crucial to ship an example plugin alongside a new hook, to help confirm that the hook design is fit for purpose.

It turns out I didn't need this for datasette.app after all, but it's still a great capability to have!

sqlite-utils 3.17

Quoting the release notes in full:

The sqlite-utils memory command has a new --analyze option, which runs the equivalent of the analyze-tables command directly against the in-memory database created from the incoming CSV or JSON data. (#320)

sqlite-utils insert-files now has the ability to insert file contents in to TEXT columns in addition to the default BLOB. Pass the --text option or use content_text as a column specifier. (#319)

evernote-to-sqlite 0.3.2

As a follow-up to last week's work on my personal Dogsheep, I decided to re-import my Evernote notes... and found out that Evernote has changed their export mechanism in ways that broke my tool. Most concerningly their exported XML is even less well-formed than it used to be. This new release works around that.

TIL this week

Searching all columns of a table in Datasette

Releases this week

datasette-verify: 0.1 - 2021-08-28
Verify that files can be opened by Datasette
datasette: 0.59a2 - (97 releases total) - 2021-08-28
An open source multi-tool for exploring and publishing data
evernote-to-sqlite: 0.3.2 - (5 releases total) - 2021-08-26
Tools for converting Evernote content to SQLite
sqlite-utils: 3.17 - (86 releases total) - 2021-08-24
Python CLI utility and library for manipulating SQLite databases

Tags: projects, datasette, weeknotes, sqlite-utils, electron, datasette-desktop