datasette

This release focuses on performance, in particular against large tables, and introduces some minor breaking changes for CSS styling in Datasette plugins.

• Removed the unit conversions feature and its dependency, Pint. This means Datasette is now compatible with the upcoming Python 3.13. (#2400, #2320)
• The datasette --pdb option now uses the ipdb debugger if it is installed. You can install it using datasette install ipdb. Thanks, Tiago Ilieve. (#2342)
• Fixed a confusing error that occurred if metadata.json contained nested objects. (#2403)
• Fixed a bug with ?_trace=1 where it returned a blank page if the response was larger than 256KB. (#2404)
• Tracing mechanism now also displays SQL queries that returned errors or ran out of time. datasette-pretty-traces 0.5 includes support for displaying this new type of trace. (#2405)
• Fixed a text spacing with table descriptions on the homepage. (#2399)
• Performance improvements for large tables:
  • Suggested facets now only consider the first 1000 rows. (#2406)
  • Improved performance of date facet suggestion against large tables. (#2407)
  • Row counts stop at 10,000 rows when listing tables. (#2398)
  • On table page the count stops at 10,000 rows too, with a "count all" button to execute the full count. (#2408)
• New .dicts() internal method on Results that returns a list of dictionaries representing the results from a SQL query: (#2414)

  rows  =  (await  db.execute("select * from t")).dicts()
  

• Default Datasette core CSS that styles inputs and buttons now requires a class of "core" on the element or a containing element, for example <form class="core">. (#2415)
• Similarly, default table styles now only apply to <table class="rows-and-columns">. (#2420)

• Datasette now defaults to hiding SQLite "shadow" tables, as seen in extensions such as SQLite FTS and sqlite-vec. Virtual tables that it makes sense to display, such as FTS core tables, are no longer hidden. Thanks, Alex Garcia. (#2296)
• Fixed bug where running Datasette with one or more -s/--setting options could over-ride settings that were present in datasette.yml. (#2389)
• The Datasette homepage is now duplicated at /-/, using the default index.html template. This ensures that the information on that page is still accessible even if the Datasette homepage has been customized using a custom index.html template, for example on sites like datasette.io. (#2393)
• Failed CSRF checks now display a more user-friendly error page. (#2390)
• Fixed a bug where the json1 extension was not correctly detected on the /-/versions page. Thanks, Seb Bacon. (#2326)
• Fixed a bug where the Datasette write API did not correctly accept Content-Type: application/json; charset=utf-8. (#2384)
• Fixed a bug where Datasette would fail to start if metadata.yml contained a queries block. (#2386)

This alpha introduces significant changes to Datasette's Metadata system, some of which represent breaking changes in advance of the full 1.0 release. The new Upgrade guide document provides detailed coverage of those breaking changes and how they affect plugin authors and Datasette API consumers.

• The /databasename?sql= interface and JSON API for executing arbitrary SQL queries can now be found at /databasename/-/query?sql=. Requests with a ?sql= parameter to the old endpoints will be redirected. Thanks, Alex Garcia. (#2360)
• Metadata about tables, databases, instances and columns is now stored in Datasette's internal database. Thanks, Alex Garcia. (#2341)
• Database write connections now execute using the IMMEDIATE isolation level for SQLite. This should help avoid a rare SQLITE_BUSY error that could occur when a transaction upgraded to a write mid-flight. (#2358)
• Fix for a bug where canned queries with named parameters could fail against SQLite 3.46. (#2353)
• Datasette now serves E-Tag headers for static files. Thanks, Agustin Bacigalup. (#2306)
• Dropdown menus now use a z-index that should avoid them being hidden by plugins. (#2311)
• Incorrect table and row names are no longer reflected back on the resulting 404 page. (#2359)
• Improved documentation for async usage of the track_event(datasette, event) hook. (#2319)
• Fixed some HTTPX deprecation warnings. (#2307)
• Datasette now serves a <html lange="en"> attribute. Thanks, Charles Nepote. (#2348)
• Datasette's automated tests now run against the maximum and minimum supported versions of SQLite: 3.25 (from September 2018) and 3.46 (from May 2024). Thanks, Alex Garcia. (#2352)
• Fixed an issue where clicking twice on the URL output by datasette --root produced a confusing error. (#2375)

• Security improvement: 404 pages used to reflect content from the URL path, which could be used to display misleading information to Datasette users. 404 errors no longer display additional information from the URL. (#2359)
• Backported a better fix for correctly extracting named parameters from canned query SQL against SQLite 3.46.0. (#2353)

• Fixed a bug where canned queries with named parameters threw an error when run against SQLite 3.46.0. (#2353)

Each of the key concepts in Datasette now has an actions menu, which plugins can use to add additional functionality targeting that entity.

• Plugin hook: view_actions() for actions that can be applied to a SQL view. (#2297)
• Plugin hook: homepage_actions() for actions that apply to the instance homepage. (#2298)
• Plugin hook: row_actions() for actions that apply to the row page. (#2299)
• Action menu items for all of the *_actions() plugin hooks can now return an optional "description" key, which will be displayed in the menu below the action label. (#2294)
• Plugin hooks documentation page is now organized with additional headings. (#2300)
• Improved the display of action buttons on pages that also display metadata. (#2286)
• The header and footer of the page now uses a subtle gradient effect, and options in the navigation menu are better visually defined. (#2302)
• Table names that start with an underscore now default to hidden. (#2104)
• pragma_table_list has been added to the allow-list of SQLite pragma functions supported by Datasette. select * from pragma_table_list() is no longer blocked. (#2104)

• New query_actions() plugin hook, similar to table_actions() and database_actions(). Can be used to add a menu of actions to the canned query or arbitrary SQL query page. (#2283)
• New design for the button that opens the query, table and database actions menu. (#2281)
• "does not contain" table filter for finding rows that do not contain a string. (#2287)
• Fixed a bug in the makeColumnActions(columnDetails) JavaScript plugin mechanism where the column action menu was not fully reset in between each interaction. (#2289)

• The "replace": true argument to the /db/table/-/insert API now requires the actor to have the update-row permission. (#2279)
• Fixed some UI bugs in the interactive permissions debugging tool. (#2278)
• The column action menu now aligns better with the cog icon, and positions itself taking into account the width of the browser window. (#2263)

The only changes in this alpha correspond to the way Datasette handles database transactions. (#2277)

• The database.execute_write_fn() method has a new transaction=True parameter. This defaults to True which means all functions executed using this method are now automatically wrapped in a transaction - previously the functions needed to roll transaction handling on their own, and many did not.
• Pass transaction=False to execute_write_fn() if you want to manually handle transactions in your function.
• Several internal Datasette features, including parts of the JSON write API, had been failing to wrap their operations in a transaction. This has been fixed by the new transaction=True default.

This alpha release adds basic alter table support to the Datasette Write API and fixes a permissions bug relating to the /upsert API endpoint.

Alter table support for create, insert, upsert and update

The JSON write API can now be used to apply simple alter table schema changes, provided the acting actor has the new alter-table permission. (#2101)

The only alter operation supported so far is adding new columns to an existing table.

• The /db/-/create API now adds new columns during large operations to create a table based on incoming example "rows", in the case where one of the later rows includes columns that were not present in the earlier batches. This requires the create-table but not the alter-table permission.
• When /db/-/create is called with rows in a situation where the table may have been already created, an "alter": true key can be included to indicate that any missing columns from the new rows should be added to the table. This requires the alter-table permission.
• /db/table/-/insert and /db/table/-/upsert and /db/table/row-pks/-/update all now also accept "alter": true, depending on the alter-table permission.

Operations that alter a table now fire the new alter-table event.

Permissions fix for the upsert API

The /database/table/-/upsert API had a minor permissions bug, only affecting Datasette instances that had configured the insert-row and update-row permissions to apply to a specific table rather than the database or instance as a whole. Full details in issue #2262.

To avoid similar mistakes in the future the datasette.permission_allowed() method now specifies default= as a keyword-only argument.

Permission checks now consider opinions from every plugin

The datasette.permission_allowed() method previously consulted every plugin that implemented the permission_allowed() plugin hook and obeyed the opinion of the last plugin to return a value. (#2275)

Datasette now consults every plugin and checks to see if any of them returned False (the veto rule), and if none of them did, it then checks to see if any of them returned True.

This is explained at length in the new documentation covering How permissions are resolved.

Other changes

• The new DATASETTE_TRACE_PLUGINS=1 environment variable turns on detailed trace output for every executed plugin hook, useful for debugging and understanding how the plugin system works at a low level. (#2274)
• Datasette on Python 3.9 or above marks its non-cryptographic uses of the MD5 hash function as usedforsecurity=False, for compatibility with FIPS systems. (#2270)
• SQL relating to Datasette's internal database now executes inside a transaction, avoiding a potential database locked error. (#2273)
• The /-/threads debug page now identifies the database in the name associated with each dedicated write thread. (#2265)
• The /db/-/create API now fires a insert-rows event if rows were inserted after the table was created. (#2260)

This alpha release continues the migration of Datasette's configuration from metadata.yaml to the new datasette.yaml configuration file, introduces a new system for JavaScript plugins and adds several new plugin hooks.

See Datasette 1.0a8: JavaScript plugins, new plugin hooks and plugin configuration in datasette.yaml for an annotated version of these release notes.

Configuration

• Plugin configuration now lives in the datasette.yaml configuration file, passed to Datasette using the -c/--config option. Thanks, Alex Garcia. (#2093)

  datasette  -c  datasette.yaml
  

  Where datasette.yaml contains configuration that looks like this:

  plugins:
    datasette-cluster-map:
      latitude_column: xlat
      longitude_column: xlon
  

• Previously plugins were configured in metadata.yaml, which was confusing as plugin settings were unrelated to database and table metadata.

• The -s/--setting option can now be used to set plugin configuration as well. See Configuration via the command-line for details. (#2252)

  The above YAML configuration example using -s/--setting looks like this:

  datasette  mydatabase.db\
    -s  plugins.datasette-cluster-map.latitude_column  xlat\
    -s  plugins.datasette-cluster-map.longitude_column  xlon
  

• The new /-/config page shows the current instance configuration, after redacting keys that could contain sensitive data such as API keys or passwords. (#2254)

• Existing Datasette installations may already have configuration set in metadata.yaml that should be migrated to datasette.yaml. To avoid breaking these installations, Datasette will silently treat table configuration, plugin configuration and allow blocks in metadata as if they had been specified in configuration instead. (#2247) (#2248) (#2249)

Note that the datasette publish command has not yet been updated to accept a datasette.yaml configuration file. This will be addressed in #2195 but for the moment you can include those settings in metadata.yaml instead.

JavaScript plugins

Datasette now includes a JavaScript plugins mechanism, allowing JavaScript to customize Datasette in a way that can collaborate with other plugins.

This provides two initial hooks, with more to come in the future:

• makeAboveTablePanelConfigs() can add additional panels to the top of the table page.

• makeColumnActions() can add additional actions to the column menu.

Thanks Cameron Yick for contributing this feature. (#2052)

Plugin hooks

• New jinja2_environment_from_request(datasette, request, env) plugin hook, which can be used to customize the current Jinja environment based on the incoming request. This can be used to modify the template lookup path based on the incoming request hostname, among other things. (#2225)

• New family of template slot plugin hooks: top_homepage, top_database, top_table, top_row, top_query, top_canned_query. Plugins can use these to provide additional HTML to be injected at the top of the corresponding pages. (#1191)

• New track_event() mechanism for plugins to emit and receive events when certain events occur within Datasette. (#2240)

  • Plugins can register additional event classes using register_events(datasette).

  • They can then trigger those events with the datasette.track_event(event) internal method.

  • Plugins can subscribe to notifications of events using the track_event(datasette, event) plugin hook.

  • Datasette core now emits login, logout, create-token, create-table, drop-table, insert-rows, upsert-rows, update-row, delete-row events, documented here.

• New internal function for plugin authors: await db.execute_isolated_fn(fn), for creating a new SQLite connection, executing code and then closing that connection, all while preventing other code from writing to that particular database. This connection will not have the prepare_connection() plugin hook executed against it, allowing plugins to perform actions that might otherwise be blocked by existing connection configuration. (#2218)

Documentation

• Documentation describing how to write tests that use signed actor cookies using datasette.client.actor_cookie(). (#1830)

• Documentation on how to register a plugin for the duration of a test. (#2234)

• The configuration documentation now shows examples of both YAML and JSON for each setting.

Minor fixes

• Datasette no longer attempts to run SQL queries in parallel when rendering a table page, as this was leading to some rare crashing bugs. (#2189)

• Fixed warning: DeprecationWarning: pkg_resources is deprecated as an API (#2057)

• Fixed bug where ?_extra=columns parameter returned an incorrectly shaped response. (#2230)

• Fixed a bug where CSV export with expanded labels could fail if a foreign key reference did not correctly resolve. (#2214)

• Dropped dependency on click-default-group-wheel, which could cause a dependency conflict. (#2197)

• Fix for a crashing bug caused by viewing the table page for a named in-memory database. #2189

• Fix for a crashing bug caused by viewing the table page for a named in-memory database. #2189

• New plugin hook: actors_from_ids(datasette, actor_ids) and an internal method to accompany it, await .actors_from_ids(actor_ids). This mechanism is intended to be used by plugins that may need to display the actor who was responsible for something managed by that plugin: they can now resolve the recorded IDs of actors into the full actor objects. (#2181)
• DATASETTE_LOAD_PLUGINS environment variable for controlling which plugins are loaded by Datasette. (#2164)
• Datasette now checks if the user has permission to view a table linked to by a foreign key before turning that foreign key into a clickable link. (#2178)
• The execute-sql permission now implies that the actor can also view the database and instance. (#2169)
• Documentation describing a pattern for building plugins that themselves define further hooks for other plugins. (#1765)
• Datasette is now tested against the Python 3.12 preview. (#2175)

• When restrictions are applied to API tokens, those restrictions now behave slightly differently: applying the view-table restriction will imply the ability to view-database for the database containing that table, and both view-table and view-database will imply view-instance. Previously you needed to create a token with restrictions that explicitly listed view-instance and view-database and view-table in order to view a table without getting a permission denied error. (#2102)
• New datasette.yaml (or .json) configuration file, which can be specified using datasette -c path-to-file. The goal here to consolidate settings, plugin configuration, permissions, canned queries, and other Datasette configuration into a single single file, separate from metadata.yaml. The legacy settings.json config file used for Configuration directory mode has been removed, and datasette.yaml has a "settings" section where the same settings key/value pairs can be included. In the next future alpha release, more configuration such as plugins/permissions/canned queries will be moved to the datasette.yaml file. See #2093 for more details. Thanks, Alex Garcia.
• The -s/--setting option can now take dotted paths to nested settings. These will then be used to set or over-ride the same options as are present in the new configuration file. (#2156)
• New --actor '{"id": "json-goes-here"}' option for use with datasette --get to treat the simulated request as being made by a specific actor, see datasette --get. (#2153)
• The Datasette _internal database has had some changes. It no longer shows up in the datasette.databases list by default, and is now instead available to plugins using the datasette.get_internal_database(). Plugins are invited to use this as a private database to store configuration and settings and secrets that should not be made visible through the default Datasette interface. Users can pass the new --internal internal.db option to persist that internal database to disk. Thanks, Alex Garcia. (#2157).

This alpha fixes a security issue with the /-/api API explorer. On authenticated Datasette instances (instances protected using plugins such as datasette-auth-passwords) the API explorer interface could reveal the names of databases and tables within the protected instance. The data stored in those tables was not revealed.

For more information and workarounds, read the security advisory. The issue has been present in every previous alpha version of Datasette 1.0: versions 1.0a0, 1.0a1, 1.0a2 and 1.0a3.

Also in this alpha:

• The new datasette plugins --requirements option outputs a list of currently installed plugins in Python requirements.txt format, useful for duplicating that installation elsewhere. (#2133)
• Writable canned queries can now define a on_success_message_sql field in their configuration, containing a SQL query that should be executed upon successful completion of the write operation in order to generate a message to be shown to the user. (#2138)
• The automatically generated border color for a database is now shown in more places around the application. (#2119)
• Every instance of example shell script code in the documentation should now include a working copy button, free from additional syntax. (#2140)

This alpha release previews the updated design for Datasette's default JSON API. (#782)

The new default JSON representation for both table pages (/dbname/table.json) and arbitrary SQL queries (/dbname.json?sql=...) is now shaped like this:

{
  "ok": true,
  "rows": [
    {
      "id": 3,
      "name": "Detroit"
    },
    {
      "id": 2,
      "name": "Los Angeles"
    },
    {
      "id": 4,
      "name": "Memnonia"
    },
    {
      "id": 1,
      "name": "San Francisco"
    }
  ],
  "truncated": false
}

Tables will include an additional "next" key for pagination, which can be passed to ?_next= to fetch the next page of results.

The various ?_shape= options continue to work as before - see Different shapes for details.

A new ?_extra= mechanism is available for tables, but has not yet been stabilized or documented. Details on that are available in #262.

Smaller changes

• Datasette documentation now shows YAML examples for Metadata by default, with a tab interface for switching to JSON. (#1153)
• register_output_renderer(datasette) plugins now have access to error and truncated arguments, allowing them to display error messages and take into account truncated results. (#2130)
• render_cell() plugin hook now also supports an optional request argument. (#2007)
• New Justfile to support development workflows for Datasette using Just.
• datasette.render_template() can now accepts a datasette.views.Context subclass as an alternative to a dictionary. (#2127)
• datasette install -e path option for editable installations, useful while developing plugins. (#2106)
• When started with the --cors option Datasette now serves an Access-Control-Max-Age: 3600 header, ensuring CORS OPTIONS requests are repeated no more than once an hour. (#2079)
• Fixed a bug where the _internal database could display None instead of null for in-memory databases. (#1970)

• Added pip and setuptools as explicit dependencies. This fixes a bug where Datasette could not be installed using Rye. (#2065)