Introducing datasette-write-ui: a Datasette plugin for editing, inserting, and deleting rows

Ever since Datasette was released nearly six years ago, its focus was on a singular idea: fast, safe, read-only queries.

For some developers, this is an odd choice - SQLite is an OLTP database, so why not support a few INSERT INTO or UPDATE statements?

The reasons, as laid out in that original blog post, are short and simple. For one, only handling read-only connections greatly reduces security risks. Datasette has SQL code execution as a first-class feature, so limiting any potential risk is important.

Plus, Datasette is a tool for publishing and exploring data. If you're investigating a government data dump or analyzing your city's annual budget, you don't want to edit data anyway!

But the years have gone by, many more people use Datasette in their day-to-day work and in their personal projects, and the feature request always come up: how can I edit data in my SQLite database with Datasette?

As of December 2022, the alpha release of Datasette 1.0 introduces a powerful new feature: API endpoints to insert, update, and delete data in your databases! With just some authentication hoops to jump through and a few curl -X POST requests, you can send edit requests to your SQLite databases with Datasette.

But of course, not everyone can do a curl -X POST or requests.post. Datasette is explicitly a tool for a wide audience, including:

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

Source: datasette.io

Not all of those users know how to manually make an HTTP request!

So datasette-write-ui was born. Using the foundational Datasette write API, this plugin offers new UI elements inside a Datasette instance to insert, edit, and delete rows in your Datasette tables. This allows for a new set of users to edit their SQLite databases on the fly, pushing along the capabilities of what Datasette can do.

Let's try it out!

Usage

First things first: this new plugin only work with the newest alpha build of Datasette, which you can install with:

pip install datasette==1.0a3

Once that is installed, you can install datasette-write-ui with:

datasette install datasette-write-ui

The datasette-write-ui plugin adds new UI elements to your Datasette instance, specifically in table pages. For users with the permission to insert and edit rows, they will see the following updates:

The first change, at the bottom: a button to insert a new row into your table! Once clicked, you'll be greeted with a form with fields for each insert-able column in your table.

The automatically-generated form works for most simple tables: TEXT columns have textarea inputs, INT and FLOAT columns use numeric inputs. There isn't quite support for BLOB columns or NULL-able columns, but will in the future.

Next up: A new settings icon on individual rows! When clicked, you'll see a menu of different "actions" you can perform for that specific row. Let's try out the "Edit row" action:

Once clicked, a similar "edit row" form will appear, pre-populating the row's values into the appropriate fields.

And finally, you can't spell CRUD without the Delete. The "Delete row" action will, after confirmation, delete your row entirely from the database.

datasette-write-ui Internals

The datasette-write-ui plugin is unique compared to most Datasette plugins. For one, most of the core logic and features is implemented client-side in JavaScript. Most Datasette plugins out there are a mixture of server-side Python and Jinja logic, but datasette-write-ui requires finer detail in the various modals, dynamic forms, and new buttons that make up the plugin.

Additionally, datasette-write-ui is mainly written in TypeScript, a clear departure from typical Datasette plugins today. Datasette itself uses very little JavaScript and favors vanilla JavaScript in plain old <script> tags, mainly for simplicity. There are no Webpack for Bite dependencies in sight in the build process!

But datasette-write-ui is a bit different - there are several API requests going on, dynamic UI elements, and typed form fields to handle. In projects like these, I like to have types in my JavaScript as a small sanity check that my future updates won't break anything.

But TypeScript is heavy! npm install typescript alone adds a whopping 38MB to my node_modules/ folder, and tsc can take a long time compile code. Is there a way to have a TypeScript project without pulling in some complicated typescript/webpack dependencies?

Yes! The esbuild tool can bundle + minify our TypeScript code to JavaScript. Thankfully, npm install esbuild adds a single 9MB binary to our node_modules, which is much easier to grok and maintain than the typescript NPM package.

To bundle our TypeScript code for use in Datasette, we use this single script in our Justfile:

./node_modules/.bin/esbuild \
  --bundle --minify --format=esm \
  datasette_write_ui/table.ts \
  --outfile=datasette_write_ui/static/table.min.js

The types in table.ts just automatically stripped by esbuild, and we bundle and minify it to table.min.js.

Now, esbuild doesn't actually do type-checking. For that, we just rely on VSCode's TypeScript plugin in development. Not the best, but for this small project it's enough. There are some bleeding-edge alternative TypeScript checkers that are single-binary compiled programs similar to esbuild, like stc, but we didn't need it for this project.

In the future

Many updates will be coming to the datasette-write-ui plugin in the near future! Including:

• Nullable columns. Columns that can be NULL will include a checkbox to explicitly state if a value should be NULL. #9
• Custom inputs. Date, timestamps, custom dropdowns, emails, currencies and more! #8
• JavaScript Plugins, for even more extensibility and customization, once the Datasette JavaScript Plugin API is ready.

Alex’s work on Datasette and Datasette Cloud is sponsored by Fly.io - thanks very much, team Fly!