Datasette 1.0a31 adds write‑query support and shared stored queries

What’s claimed

The Datasette team announced datasette 1.0a31, an alpha release that “adds the ability to execute write queries” and “renames canned queries to stored queries, now sharable across users.” The blog post highlights an animated demo where a user runs templated INSERT, UPDATE, or DELETE statements directly from the web UI, and it points out that stored queries can be saved privately or made public to other members of the same Datasette instance.

What’s actually new

Feature	How it works	Where to see it
SQL write queries	Users with the execute-write permission can submit arbitrary SQL that modifies data. The UI presents a simple form where placeholders (e.g., {{name}}) are replaced with values entered in a modal dialog. Under the hood the request is passed to SQLite’s execute method inside a transaction that is rolled back on error.	Datasette 1.0a31 release notes
Stored queries (formerly canned queries)	A query definition (SQL text, description, and optional template variables) can be saved as a JSON record in the internal metadata.json. The record can be marked private or public. Public entries appear in the Stored Queries tab for any authenticated user with read access to the database.	Blog post on stored queries
Permission model	Permissions are expressed via the metadata.json permissions key. The new execute-write flag is added alongside existing view‑query and view‑metadata flags. Permissions can be granted per‑database or per‑table, but they are still static – there is no UI for role‑based delegation beyond editing the JSON file.	Permissions documentation

The demo in the blog post shows a user opening a table, clicking Run query, selecting a stored template, filling in a name, and hitting Execute. The UI reports the number of rows affected and offers a link to view the updated table.

Limitations and practical concerns

1. Alpha stability – As an alpha release, the write‑query path has not been hardened against malformed input. A malformed template can raise SQLite exceptions that surface as HTTP 500 errors. The team advises only using it on trusted internal deployments.
2. Permission granularity – The permission system is still file‑based. Changing who can write requires editing metadata.json and restarting the server, which is cumbersome for larger teams that need dynamic role management.
3. No conflict handling – Writes are executed in a single transaction without any optimistic‑locking or version checks. Concurrent users could overwrite each other’s changes without warning, a scenario that would be problematic for collaborative data‑entry workflows.
4. Limited audit trail – While the UI shows the number of rows affected, there is no built‑in logging of who executed which write query or what values were supplied. Users needing compliance‑grade traceability will have to add custom middleware or external logging.
5. Template security – The templating syntax is deliberately simple, but because it interpolates user‑supplied values directly into SQL, developers must ensure that placeholder values are properly escaped. The current implementation relies on SQLite’s parameter binding, but complex expressions inside the template can bypass that safety net.

Bottom line

Datasette 1.0a31 expands the tool from a read‑only explorer to a modest data‑entry interface, and the rename to stored queries clarifies that these are reusable, shareable query snippets. The feature set is useful for small teams that already use Datasette for internal dashboards and need a quick way to let non‑technical staff edit rows without writing custom code. However, the release is still an alpha, and the permission model, audit capabilities, and concurrency safeguards are far from production‑ready. Teams should treat the write‑query feature as a convenience layer rather than a replacement for a full‑featured ORM or API layer.

---

For a deeper look at the underlying implementation, see the GitHub pull request that introduced the write‑query endpoint.