datasette 1.0a42

on Python PyPI

A redesigned homepage, redesigned table, row and query pages, a new "Export data" dialog with TSV support, a column statistics feature, and Python 3.11 is now the minimum supported version.

Python 3.11 or higher

• Datasette now requires Python 3.11 or higher. This allowed the removal of several compatibility shims. (#2998)
• Datasette is now tested on Python 3.11, 3.12, 3.13, 3.14, and 3.15.

Export data dialog and TSV

• Table, query and row pages replace the "This data as..." links and the "Advanced export" box with an Export data button. This opens a dialog listing every available format - TSV, CSV, JSON, JSON array, newline-delimited JSON, JSON object and any plugin formats - each with Copy, View, Download and Copy URL actions. Old #export links open the dialog. See CSV and TSV export. (#3037)
• New TSV export, for pasting into Excel and Google Sheets. TSV uses the same streaming code, options and URL parameters as CSV, with Excel-compatible quoting.
• register_output_renderer dictionaries can now include optional label and description keys, shown in the Export data dialog, plus is_binary and is_webpage flags that control which actions the dialog offers for that format.

Column statistics

• The column action menu has a new Column statistics item, available to actors with the execute-sql permission. It opens a dialog showing counts of rows, nulls and distinct values, a breakdown of storage types, numeric statistics with a histogram, box plot and percentiles, text length statistics, a chart of dates grouped by month, year or decade, and the most common values. Every query it runs is listed, with a link to open it in the SQL editor. (#3038)
• Column statistics are also available for the results of arbitrary SQL queries and stored queries.

Table page redesign

• A refreshed design for tables on the table, row and query pages, with right-aligned numbers and shadows indicating horizontally scrollable content. (#3032)
• Values taller than three lines are clamped to a preview with an "expand" button. Column headers offer an "expand all" toggle.
• A pin in the primary key column header freezes the header row and primary key columns while scrolling. On query pages it freezes just the header row. The choice is remembered.
• Tables that span more than one page now show a pagination bar, for example "Rows 101-150 of 1,761", with "Back to first page" and "Next page" buttons. This uses a new ?_count=before option for the count endpoint, which counts the rows before a ?_next= cursor. (#3043)
• The row count is now shown in the table page heading, and the pagination bar sits directly above the table. (#3048)
• The CREATE TABLE or CREATE VIEW statement is now collapsed into a "Table schema" or "View definition" disclosure at the bottom of the page.
• Facet values now show a faint bar indicating their share of the rows. Suggested facets and facet results have been restyled.
• New Clear filters button next to "Apply filters", shown when column filters are applied.

Row page redesign

• The row page now shows the row as a list with one field per line, each with a button to copy its value, in place of a single-row table. Long values are clamped with a toggle to show them in full, and the tables that reference the row are listed below it. (#3052)
• Backwards incompatible: the row page now includes a new _row_fields.html template instead of _table.html. A custom _table.html template that applies to every table no longer changes the row page - provide a _row_fields.html template instead. Templates for a specific table, _table-mydatabase-mytable.html and _table-row-mydatabase-mytable.html, still apply to the row page. See Custom templates.
• The row page no longer contains a table.rows-and-columns element, so plugin JavaScript that looks for that table on row pages will not find it. The .above-table-panel element plugins can attach to is still present.

Homepage redesign

• The homepage now shows one searchable catalog of tables, views and stored queries across every database, instead of grouping them by database. (#3049)
• A search box, focused when the page loads, matches table and view names, column names, table descriptions and the names, titles, descriptions and SQL of stored queries. Results update as you type and can be navigated with the arrow keys and Enter.
• Tables, views, queries and databases you visit are recorded in the browser's localStorage, separately for each signed-in actor, and shown in a Recently visited section. The "Recent" list in the Jump to menu now uses the same history.
• Database filter buttons (?database=) are shown when more than one database is attached. The table list is built from the internal catalog_* tables so it stays fast with thousands of tables, and is paginated 30 at a time.
• New makeHomepageSections() JavaScript plugin hook for adding sections to the homepage. Items added to Jump to using the jump_items_sql(datasette, actor, request) plugin hook are included in search results.
• The stored query browser at /-/queries has been redesigned to match the new homepage.
• Backwards incompatible: the index.html template context has changed and is now documented in Template context. Custom index.html templates that used keys such as tables_and_views_truncated or table_rows_sum will need to be updated. The /.json output is unchanged.

Other changes

• App, database, table, row and column action menus have been redesigned to share positioning, dismissal and keyboard navigation behavior. See the new /-/patterns/menus page. (#2984)
• The /-/jump endpoint now supports ?type= filtering, for example ?type=table&type=database. Items now include the URL arguments used to build their URL as fields, hidden tables are excluded, and stored queries use their title as their display name. (#3024, #3025)
• The internal catalog_tables table has a new hidden boolean column, populated for tables that Datasette treats as hidden from default table lists.
• Error responses now use the JSON error format when the request sends an Accept: application/json or Content-Type: application/json header, not just for URLs ending in .json. Thanks, kokokoXUY. (#1949, #2961)
• HTML table pages no longer send a rel="next" HTTP Link header. (#3046)
• edit-tools.js, export-dialog.js and column-stats.js are now loaded on demand, the first time they are needed, rather than on every page. The table page now only includes the editing tools loader if the actor can insert, alter, update or delete, indicated by a new row_mutation_ui template context variable. (#3042)

Bug fixes

• execute-write operations now work against tables with FTS index triggers, such as those created by sqlite-utils enable_fts(create_triggers=True). The SQL analysis table now shows why an operation was rejected.
• The about and about_url metadata are now displayed even if no source or license is set. (#512, #2985)
• The query page heading now says "returning more than N rows" when results are truncated.
• A stored query that references a missing :_actor_X key now raises a KeyError naming that key.
• Facet counts on the second and later pages of a table now count every matching row, and facet links no longer keep the ?_next= pagination cursor. Thanks, b6tang. (#3021, #3026)
• The ?_json= option now decodes JSON columns on table pages, no longer replaces row values when combined with ?_extra=columns, and works with ?_shape=arrays and ?_shape=arrayfirst. Thanks, IlyaKhalafi. (#3003, #3005)
• Views with a BLOB column no longer return a 500 error, and CSV exports of those views now include a link to download each value. (#2601)
• Fixed a bug where Datasette running with base_url produced links and redirects that repeated the prefix, such as /prefix/prefix/, when the prefix was included in the request path. This affected filter and sort redirects, column sort links, next page links, facet links and the query page. (#2347, #1633)
• Sorting a table that has no primary key by a column hidden using ?_nocol= or ?_col= no longer returns a 500 error when the results span more than one page. Thanks, feiiiiii5. (#2974, #2975)
• Full-text search now works for FTS tables that declare their content table unquoted or with single quotes or backticks, for example content=documents. Previously these tables showed no search box and ?_search= was ignored. Thanks, b84330808. (#1991, #3011)
• datasette.plugin_config(plugin_name, database=...) now falls back to the top-level plugin configuration for a database that has no configuration of its own for that plugin. Thanks, pujitha24. (#2959, #3041)