Datasette now offers users with the necessary permissions the ability to both execute write queries against their database and to save stored queries (renamed from "canned queries") both privately and for use by other members of their Datasette instance.
The ability to write is controlled by the new execute-write-sql permission, but the user also needs the relevant insert-row/update-row/delete-row/create-table/etc permissions for the query they are trying to execute.
Write SQL UI
- New "Write to this database" interface at
/<database>/-/execute-writefor running arbitrary writable SQL against mutable databases. The form extracts named parameters, analyzes the SQL, shows the table operations that will be attempted, includes starter templates forINSERT,UPDATEandDELETEstatements and links to a newly inserted row when a single-row insert succeeds. This is also available as a JSON API. (#2742) - Added the new execute-write-sql permission for running arbitrary writable SQL. Execution is also gated by table-level permissions such as insert-row, update-row and delete-row, and writes to attached databases are rejected. (#2742)
- The write SQL analyzer now uses a deny-by-default model for unsupported operations. Reads from source tables require view-table permission, schema changes require create-table, alter-table or drop-table as appropriate, and row mutation statements require the full
insert-row,update-rowanddelete-rowpermission set. SQL functions are allowed and are not separately permission-gated. (#2748) - User-supplied write SQL rejects both
VACUUMoperations and writes to SQLite virtual or shadow tables. These restrictions also apply to untrusted stored write queries; trusted queries indatasette.ymlskip these filters. (#2748)
Stored queries
- The previous "canned queries" feature has been renamed and expanded into stored queries. Queries configured in
datasette.yamlare now loaded into a newqueriestable in Datasette's internal database, alongside user-created stored queries. (#2735) - New stored query management API methods available to plugins:
datasette.add_query(),datasette.update_query(),datasette.remove_query(),datasette.get_query(),datasette.list_queries()anddatasette.count_queries(). These replace the removeddatasette.get_canned_query()anddatasette.get_canned_queries()methods. (#2735) - Users with store-query and execute-sql permission can create stored queries from the SQL query page or the new
GET /<database>/-/queries/storeform. (#2735) - The database page now shows a count and preview of stored queries, capped at five, and links to new paginated query lists at
/-/queriesand/<database>/-/queries. Those pages support search. (#2735) - Stored queries created by users default to private and untrusted. Private stored queries can only be viewed, updated or deleted by their owner, even if another actor has broad
view-query,update-queryordelete-querypermission. Untrusted stored queries execute using the permissions of the actor running them. See Stored queries and Trusted stored queries for details. (#2735) - Configured queries from
datasette.yamlare trusted by default, so they can execute withview-querypermission alone. They can opt out of that behavior usingis_trusted: falsebut cannot be made private; private queries are only available for user-created stored queries. (#2735) - New
store-query,update-queryanddelete-querypermissions, plus updated semantics for view-query. Trusted stored queries can still execute withview-queryalone; untrusted read queries also require execute-sql and untrusted writable queries require execute-write-sql plus the relevant table-level write permissions. (#2735)
Plugin API changes
- The
top_canned_query()plugin hook has been renamed to top_stored_query(). (#2747) - The
canned_queries()plugin hook has been removed. Plugins can use the new stored query management methods together with startup() to register queries. (#2735)