Here's a new TIL on using SQL functions in Google Sheets to fetch data from Datasette.

Datasette 1.0a27 changes how CSRF protection works in a way that simplifies form and API integration, and introduces a new RenameTableEvent for when a table is renamed by a SQL query.

Datasette 1.0a26 adds a column_types system allowing columns to carry additional type information such as email or url, or extra types defined by plugins. This alpha also adds a UI for selecting and sorting visible columns on a table page.

Datasette 1.0a25 adds write_wrapper() and register_token_handler() plugin hooks.

Datasette 1.0a24 introduces a new await request.form(files=True) method for accepting file uploads, plus various other changes.

Datasette 0.65.2 and Datasette 1.0a21 have been released with a security fix for an open redirect error, see this advisory. The latest Datasette alphas also include a breaking change to Datasette's permission system, described in detail in A new SQL-powered permissions system in Datasette 1.0a20.

Datasette 1.0a17 is the latest Datasette 1.0 alpha release, with bug fixes and small feature improvements from the last few months.

Python 3.13 was released today. Datasette 1.0a16 is compatible with Python 3.13, but Datasette 0.64.8 was not. The new Datasette 0.65 release fixes compatibility with the new version of Python.

Datasette 1.0a14 includes some breaking changes to how metadata works for plugins, described in detail in the new upgrade guide. See also the annotated release notes that accompany this release.

Datasette 1.0a10 is a focused alpha that changes some internal details about how Datasette handles transactions. The datasette.execute_write_fn() internal method now wraps the function in a database transaction unless you pass transaction=False.

Stored queries can now be edited and deleted through the web interface, and the JSON API ?_extra= mechanism has been extended to cover row and query pages in addition to tables. This release also fixes two security issues: a SQL injection vulnerability involving table and column names that contain ], and an open redirect.

Editing and deleting stored queries

The stored query page gained a "Query actions" menu with Edit this query and Delete this querylinks for actors with the necessary permissions. The owner of a query can always edit or delete it; for queries that are not private, any actor with the update-query or delete-query permission can do so too. Private queries remain editable and deletable only by their owner. See Stored queries for details. (#2735)

?_extra= support for row and query pages

Row and query JSON pages now support the same ?_extra= mechanism as table pages. Row pages can request extras such as foreign_key_tables, query, metadata and database_color; arbitrary SQL and stored query pages can request extras such as columns, query, metadata and private. The implementation was refactored into a registry of extra classes shared by all three page types.

New generated reference documentation describes every ?_extra= parameter available on table, row and query JSON pages, with example output captured from a live Datasette instance at documentation build time. See Expanding JSON responses for the full list.

Other improvements and fixes to the extras mechanism:

• ?_extra= values can be separated by commas as well as repeated, e.g. ?_extra=count,next_url. Previously a comma-separated value that included columns failed to include the columns key in the response.
• The ?_extra=private extra on arbitrary SQL query pages now correctly reflects whether the SQL execution permission is private to the current actor - it previously always returned false.
• The ?_extra=query extra on query pages now reports the named parameters that were actually bound when the query executed, including parameters declared in a stored query's params list. Magic _-prefixed parameters are no longer echoed back with unbound values taken from the querystring.
• Extras that exist to serve the HTML interface (filters, actions, display_rows) are no longer advertised or reachable through the JSON API, where requesting them previously returned a 500 serialization error.
• The pre-1.0 ?_extras= (plural) parameter on row pages has been removed - use ?_extra=foreign_key_tables instead.

Security fixes

• Fixed a SQL injection vulnerability in datasette.utils.escape_sqlite(). Identifiers were wrapped in [brackets] without escaping any ] characters they contained - SQLite has no mechanism for escaping ] inside bracket quoting, so a table or column name containing ] could break out of the identifier and inject arbitrary SQL. Identifiers containing ] are now quoted using double quotes instead. (#2677)
• Fixed an open redirect vulnerability. Requesting a path such as /\example.com/ produced a redirect with a Location: /\example.com header - browsers normalize backslashes to forward slashes, turning that into the protocol-relative URL //example.com and redirecting the user off-site. Any run of leading slashes and backslashes in a redirect path is now collapsed to a single slash. (#2680)

Bug fixes

• can_render() callbacks registered by the register_output_renderer() plugin hook now receive the result rows and columns for stored queries. Previously renderers that inspect the available columns - such as datasette-atom and datasette-ics - never appeared as export options on stored query pages. (#2711)
• Fixed a 500 error from the /-/check permission debugging endpoint when checking query actions such as view-query, update-query and delete-query. (#2756)
• Write queries that use a named parameter called :sql no longer fail with an error. (#2761)
• db.execute_isolated_fn() now works against immutable databases, using a read-only connection that bypasses the write thread. It previously always attempted to open a writable connection, which would fail - breaking features built on top of it, such as the SQL analysis step used when storing a query. An exception raised while opening the connection for an isolated function no longer crashes the write thread. (#2768)
• Facet counts are now displayed on the same line as the facet value instead of wrapping onto a second line. (#2754)
• Datasette's pytest plugin no longer imports the rest of Datasette at pytest startup time. This means plugin test suites using pytest-cov now correctly record coverage of code that runs when datasette modules are first imported.

Driven by the needs of Datasette Agent's human-in-the-loop ask_user() feature, made the following improvements to how tool calls work:

• Tool implementations can declare a parameter named llm_tool_call in order to be passed the llm.ToolCall object for the current invocation. This allows them to access the current llm_tool_call.tool_call_id. See Accessing the tool call from inside a tool. #1480
• Every tool call is now guaranteed a unique tool_call_id - providers that do not supply one get a synthesized tc_-prefixed ULID. #1481
• Tools can raise a llm.PauseChain exception to cleanly pause the tool chain, useful for things like waiting for human approval. The exception propagates to the caller with .tool_call and .tool_results (completed sibling results) attached, and no model call is made with a placeholder result. See Pausing a chain from inside a tool. #1482
• Failure semantics for concurrent tool execution: async sibling tool calls always run to completion before a pause or hook exception propagates. #1482
• Chains can now resume from a messages= history ending in unresolved tool calls: the calls are executed through the normal before_call/after_call machinery before the first model call, skipping any that already have results. The execute_tool_calls() method also accepts a new optional tool_calls_list= argument for executing an explicit list of ToolCall objects in place of the calls requested by the response. See Resuming a chain with pending tool calls. #1482
• Fixed a bug where the async tool executor silently dropped calls to tools not present in tools= - these now return Error: tool "..." does not exist results, matching the sync executor. #1483

SQLite INSERT ... RETURNING clauses are now supported by /db/-/execute-write, plus several fixes relating to the base_url setting.

• INSERT/UPDATE/DELETE statements that use SQLite's RETURNING clause now work correctly in the new /db/-/execute-write interface. Datasette fetches returned rows before committing the write transaction, displays them in the HTML UI and includes them in the "rows" key for the JSON API response. (#2762, #2763)
• Database.execute_write() now returns an ExecuteWriteResult object instead of the raw sqlite3.Cursor returned by conn.execute(). The new object exposes .rowcount, .lastrowid, .description, .truncated and .fetchall(), and adds return_all= and returning_limit=options for controlling how rows from RETURNING statements are buffered. (#2763)
• Fixed the /-/jump navigation search endpoint when Datasette is served with a configured base_url. (#2757)
• Fixed JSON and CSV export links, plus Link: alternate headers, on table, row and query pages when base_url is configured. These could previously be prefixed twice. (#2759)
• Fixed several other base_url handling bugs, including the API explorer form actions and share links, the /-/patterns development page, permanent redirects such as /- to /-/ and database query redirects from /<database>?sql=... to /<database>/-/query?sql=....

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-write for 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 for INSERT, UPDATE and DELETE statements 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-row and delete-row permission set. SQL functions are allowed and are not separately permission-gated. (#2748)
• User-supplied write SQL rejects both VACUUM operations and writes to SQLite virtual or shadow tables. These restrictions also apply to untrusted stored write queries; trusted queries in datasette.yml skip these filters. (#2748)

Stored queries

• The previous "canned queries" feature has been renamed and expanded into stored queries. Queries configured in datasette.yaml are now loaded into a new queries table 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() and datasette.count_queries(). These replace the removed datasette.get_canned_query() and datasette.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/store form. (#2735)
• The database page now shows a count and preview of stored queries, capped at five, and links to new paginated query lists at /-/queries and /<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-query or delete-query permission. 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.yaml are trusted by default, so they can execute with view-query permission alone. They can opt out of that behavior using is_trusted: false but cannot be made private; private queries are only available for user-created stored queries. (#2735)
• New store-query, update-query and delete-query permissions, plus updated semantics for view-query. Trusted stored queries can still execute with view-query alone; 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)

Bug fixes

• Fixed a bug where visiting /<database>/-/query without a ?sql= parameter returned a 500 error. (#2743)
• The datasette inspect command now correctly records row counts for tables with more than 10,000 rows. (#2712)

The "Jump to" menu, activated by hitting / or through the application menu, can now be extended by plugins.

• New "Jump to..." menu item, always visible, for triggering the previously undocumented / menu. (#2725)
• The / jump-to search interface now covers databases, views, canned queries and plugin-provided items in addition to tables. The endpoint backing it has been renamed from /-/tables to /-/jump.
• New jump_items_sql(datasette, actor, request) plugin hook, allowing plugins to contribute additional items to the jump-to menu by returning SQL. JumpSQL queries run against Datasette's internal database by default, or can target another database using the optional database=argument. (#2731)
• datasette.jump.JumpSQL.menu_item() is a shortcut for adding individual jump menu items that are not backed by resources in the internal catalog.
• New makeJumpSections() JavaScript plugin hook, allowing plugins to add custom blank-state sections to the jump-to menu before the user has typed a query.
• Debug menu links now appear in the jump-to menu instead of the top-right app menu, with descriptions for each debug item.
• Dropped Janus as a dependency, previously used to manage the write queue. This should not have any impact on plugin developers or end-users. (#1752)
• Fixed a bug where stale tables and other related resources were not removed from catalog_*tables when a database was removed. (#2723)
• New documented datasette.fixtures.populate_fixture_database(conn) helper for creating the fixture database tables used by Datasette's own tests, intended for plugin test suites.
• Keyboard accessibility and ARIA roles for actions menus, thanks pintaste. (#2727)