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.

Datasette 1.0a9 adds basic alter table support to the JSON API, tweaks how permissions works and introduces some new plugin debugging utilities.

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)