Simon Willison’s Weblog

Subscribe

Using SQLite and Datasette with Fly Volumes

15th February 2022

A few weeks ago, Fly announced Free Postgres Databases as part of the free tier of their hosting product. Their announcement included this snippet:

The lede is “free Postgres” because that’s what matters to full stack apps. You don’t have to use these for Postgres. If SQLite is more your jam, mount up to 3GB of volumes and use “free SQLite.” Yeah, we’re probably underselling that.

I don’t know if I’ve ever been nerd sniped so effectively (they knew what they were doing).

This has the potential to address one of the big challenges in deploying Datasette, my open source web framework for building dynamic applications on top of SQLite.

Datasette currently has plenty of good answers for publishing read-only databases online—Cloud Run, Heroku and Vercel are three great options. But the moment you want to write back to that SQLite database you’re in for a harder time.

Accepting writes requires a working read-write filesystem, and that generally means spinning up a virtual machine—and having to take responsibility for keeping a full Linux instance fed and updated. That’s a much bigger commitment than deploying a container to something like Cloud Run.

Fly Volumes are exactly what I’ve been waiting for here. They provide persistent storage for Fly apps—a volume you can mount as a directory inside a containerized application that will persist data through subsequent deploys.

Add a free tier, and I finally have something I can point people towards when they want to try building something persistent on top of Datasette without taking on the burden of maintaining their own virtual server somewhere, or committing to spend money before they’ve evaluated if this is going to work for them or not.

datasette-publish-fly

I built the first version of the datasette-publish-fly plugin nearly two years ago, as a tool for publishing read-only databases to Fly in a similar way to Cloud Run or Vercel.

(That plugin actually broke a few months ago without me noticing due to some changes made to the flyctl utility that it shells out to—figuring out what had happened and wanting to avoid surprises like this in the future lead me to develop the Help scraping technique I described last week.)

This week I released datasette-publish-fly 1.1 with the option to attach volumes to the published instances and start serving mutable, persistent SQLite databases!

Basic usage looks like this:

datasette publish fly \
--app my-new-fly-application \
--create-volume 1 \
--create-db my-new-db

This will create a new Fly application on your account called my-new-fly-application, create a 1GB volume for that application, mount the volume and then create an empty SQLite database file in that volume called my-new-db.db.

A blank database file isn’t very interesting! Datasette’s Writable canned queries feature can be used to configure SQL queries that write to the database. Let’s try that now, by creating a metadata.yml file containing the following:

plugins:
  # Create messages table in messages.db on startup
  datasette-init:
    messages:
      tables:
        messages:
          columns:
            id: integer
            message: text
            datetime: text
          pk: id
databases:
  messages:
    queries:
      # /messages/add_message query
      add_message:
        sql: |-
          INSERT INTO messages (
            message, datetime
          ) VALUES (
            :message, :_now_datetime_utc
          )
        write: true

Now we can run the deploy like this:

datasette publish fly \
--app messages-demo \
--create-volume 1 \
--create-db messages \
--install datasette-init \
--metadata metadata.yml

This example uses the datasette-init plugin to create the messages table if it doesn’t exist, and configures a canned query that can insert rows into that table (using the _now_datetime_utc magic parameter to populate the current datetime.)

Once deployed, the following URL provides a form that can be used to add messages:

https://messages-demo.fly.dev/messages/add_message

Animated demo showing the add_messages form - new messages added there show up with their date in the messages table

As a general rule though, writable databases become more interesting when you combine them with Datasette plugins.

Using Fly volumes with datasette-tiddlywiki

datasette-tiddlywiki is a plugin that bundles a full install of TiddlyWiki, configured to use Datasette as a storage backend. I introduced this new plugin back in December.

Version 0.2 of the plugin, which I released yesterday, adds authentication. TiddlyWiki instances can now be viewed by anyone but can only be edited by users with the edit-tiddlywiki permisson, which is available to the root user by default and can be granted to more users using the Datasette permissions system.

Here’s how to deploy datasette-tiddlywiki to Fly, with an authenticated account that can then be used to edit the wiki.

We’re going to use the datasette-auth-passwords plugin to authenticate users. We need to install that locally in order to generate a password:

datasette install datasette-auth-passwords

Installing this plugin adds a new command to Datasette called hash-password—we can use that like so:

% datasette hash-password
Password: 
Repeat for confirmation: 
pbkdf2_sha256$260000$b1ec52979ecf0c4810e3e22ea63c119e$AjaNnyuXzDXDRK/ZQjyn881J5GnVouKxI8B3DFu/C+M=

(The example hash shown here is for the password “password”, so don’t use it for anything!)

Having created that password hash, we can use it to deploy our TiddlyWiki instance.

# First, install datasette-publish-fly
datasette install datasette-publish-fly

# Now deploy an instance with datasette-tiddlywiki
datasette publish fly \
--app simon-tiddlywiki \
--create-volume 1 \
--create-db tiddlywiki \
--install datasette-auth-passwords \
--install datasette-tiddlywiki \
--plugin-secret datasette-auth-passwords root_password_hash 'pbkdf2_sha256$2600...'

The --app value here is the name of the app on Fly, which needs to be globally unique—so pick your own value here.

The full password hash needs to be pasted in with single quotes on that last line.

Running this will churn away for a little bit deploying the application:

Screenshot of the terminal output from running datasette publish fly

It can take a few minutes after the script finishes running for the application to become available—Fly are working at speeding this up. If you visit it too early you may see a TLS connection error.

Once the application is live, the following pages will be available:

Sign in and visit the wiki and you’ll be able to add and edit records there!

Animated GIF showing TiddlyWiki in action

Since the plugin saves data to an underlying SQLite table, you can visit /tiddlywiki/tiddlers to see the data that has been stored.

Other plugins to try

Here are some other interesting plugins that make use of Datasette’s internal methods for writing to a database:

datasette-auth-github and datasette-auth-tokens can be used to provide additional ways of authenticating with an instance.

Releases this week

TIL this week

Posted 15th February 2022 at 2:17 am · Follow me on Mastodon or Twitter or subscribe to my newsletter

More recent articles

This is Using SQLite and Datasette with Fly Volumes by Simon Willison, posted on 15th February 2022.

Next: Google Drive to SQLite

Previous: Help scraping: track changes to CLI tools by recording their --help using Git