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.

• Sets force_https=true in fly.toml. #31
• Fix for Volume could not be found bug. #32
• Compatible with app-scoped deploy tokens. #34

• Fix for compatibility with sqlite-utils 4.x. #85

This release includes security fixes for permissions, SQL construction and caching, backported to the stable 0.65.x branch.

See 1.0a39 for the full set of security fixes and other improvements in the 1.0 alpha series.

The Datasette blog has more details on these releases.

Some of the security fixes include:

• Table and view permission checks now take SQLite's case-insensitive names into account. See Controlling access to specific tables and views.
• Table filters using ?_through= require permission to view the intermediate table.
• Fixed SQL identifier escaping for primary-key column names from untrusted database schemas, including row lookups and pagination.
• Full-text search index detection now uses parameterized SQL and treats wildcard characters in table names literally.
• Private and personalized dynamic responses now use Cache-Control: private, no-store. Anonymous dynamic responses vary by Cookie and Authorization.
• SQLite extension loading is disabled after extensions supplied using --load-extension have been loaded.

Other improvements and fixes

• Backported the non-blocking write task ID fixes from #2861 by Zain Dana Harper (HarperZ9). execute_write_fn(block=False) now returns a unique UUID for each call, including when num_sql_threads=0.

See 0.65.4 for fixes that have been backported to the stable 0.65.x branch.

The Datasette blog has more details on these releases.

Some of the security fixes include:

• Table and view permission checks now take SQLite's case-insensitive names into account. See How permissions are resolved.
• Viewing a full-text search index table now checks you have permission to view the table from which it draws its content.
• Viewing SQLite statistics tables (sqlite_stat1 through sqlite_stat4) is now denied by a default.
• Table schema display now obeys the view-table permission.
• Table filters using ?_through= require permission to view the intermediate table.
• Foreign-key target and suggestion APIs, incoming foreign-key relationships and their row counts now respect view-table permission.
• Row endpoints check permissions before resolving primary keys, to avoid revealing the existence of an otherwise invisible primary key.
• Improved permission checks for the create-table API. See The JSON write API.
• The write SQL interface now checks view-table permission for tables referenced by CREATE VIEW statements.
• Fixed SQL identifier escaping for column names from untrusted database schemas.
• Fixed HTML escaping for column names from untrusted database schemas.
• URL columns now render links only for validated HTTP or HTTPS URLs.
• Private and personalized dynamic responses now use Cache-Control: private, no-store. Anonymous dynamic responses vary by Cookie and Authorization.
• Actor cookies now respect expire_after.
• Restricted actors can no longer create API tokens.
• Stored-query create, edit and delete forms now block framing to prevent clickjacking.
• Configuration secret redaction now matches key names case-insensitively.
• SQLite extension loading is disabled after extensions supplied using --load-extension have been loaded.

Other improvements and fixes

• db.execute_write() now has a default execution time limit of 2,000ms. Plugins can override this using time_limit_ms= or disable it using time_limit_ms=None. This limit is independent of the sql_time_limit_ms setting for read queries.
• Application startup now runs through ASGI lifespan events before requests are accepted, with a first-request fallback for hosts without lifespan support. Thanks, Alex Garcia. (#2887)
• datasette serve now runs startup hooks and Uvicorn on the same event loop, preserving background tasks started by plugins. The minimum Uvicorn version is now 0.29. Thanks, Alex Garcia. (#2886)
• Non-blocking writes using execute_write_fn(..., block=False) now return a distinct task UUID for every call and work correctly with num_sql_threads=0. Thanks, Zain Dana Harper. (#2860, #2859)
• Dropping a table now disables its full-text search index first. (#2874)
• Fixed CREATE VIEW SQL analysis on Python 3.10.

• New OpenAI model: gpt-6-astra for GPT-6 Astra.

New features:

• llm logs --usage Markdown output now includes the response duration in milliseconds and as a human-readable duration. llm logs --short includes a new duration_ms field. #1653

Bug fixes:

• Significantly improved the performance of llm logs for long conversations by caching repeated message and model lookups. #1654
• Dynamically generated OpenAI options classes are now cached, avoiding repeated Pydantic class construction by plugins such as llm-openrouter. Thanks, waveplate. #1651
• Invalid schema DSL passed to llm prompt --schema now produces a clean command-line error instead of a Python traceback. Thanks, ikatyal2110. #1647
• llm --extract now recognizes fenced code blocks in responses that use CRLF line endings. Thanks, mameikagou. #1644
• monotonic_ulid() now remains monotonic if the system clock moves backwards or concurrent calls observe timestamps out of order. Thanks, Dylan Pulver. #1641
• Declared the typing-extensions package as a direct dependency, and added tests to protect against accidental missing dependencies. Thanks, Vansh Taneja. #1622

New features:

• Upgraded to the OpenAI Python library 3.x and switched the HTTP client dependency from httpx to httpx2. #1608, #1631
• llm embed and llm embed-multi now accept --key. The Python EmbeddingModel.embed(), EmbeddingModel.embed_multi(), Collection.embed() and Collection.embed_multi() methods accept key= too, passing the resolved per-call key to embedding plugins without changing shared model state. Existing plugins that read self.key continue to work through a compatibility fallback. Thanks, ChrisJr404. #757, #1620
• llm logs now includes the output of server-side tool calls, shown in a Tool results section within the response. These results are also included in llm logs --json and llm logs --short output, with a new server_executed key distinguishing them from locally executed tool results. #1629
• llm prompt -t/--template can now be repeated to combine templates in order. This allows model configuration and options from one template to be used with a prompt from another.
• Expanded the llm prompt --help documentation for --schema and --schema-multi with details and examples of the supported schema DSL.
• schema_dsl() now raises descriptive ValueError exceptions for unknown field types and duplicate field names, instead of silently treating unknown types as strings or overwriting earlier fields. #1607, #1616
• Reasoning stream events that contain provider metadata but no text are now preserved as ReasoningPart objects. This allows opaque state such as Anthropic signatures and redacted thinking data to round-trip correctly. simonw/llm-anthropic#81
• Reasoning-capable Responses API models now support a reasoning_summary option with auto, concise, and detailed values. This can be used with llm openai endpoint --responses. #1600
• Conversation prompts now validate that attachments are supported by the selected model before execution, for both synchronous and asynchronous conversations. Thanks, Daniel Peng. #1626, #1628

Bug fixes:

• llm logs --data-ids now sets conversation_id to the ID of the conversation instead of incorrectly duplicating the response ID. Thanks, K Merchant. #1598, #1613
• Fixed llm aliases list raising a ValueError when no aliases are defined. Thanks, Taraka Abhiram. #1602
• llm embed-multi now reuses an existing collection's stored embedding model when no default embedding model is configured, and no longer masks unrelated ValueError exceptions with a missing-model error. #1523
• llm tools -m MODEL no longer prints a redundant message when the model has no server-side tools.

This version pins LLM to use OpenAI Python versions less than v3.0.0, ensuring fresh LLM installations work without needing to also install an extra httpx dependency. #1630

• Fix for No module named 'typing_extensions' crashing bug accidentally shipped in version 4.2. #842

• New table.checks, table.column_checks and table.table_checks introspection properties expose column-level and table-level CHECK constraints. (#834)
• New sqlite_utils.ANY marker type for creating and introspecting SQLite ANY columns. The Python API and CLI can create, add and transform these columns, and table.transform() and table.extract() now preserve ANY columns and their values in STRICT tables. (#790)
• table.default_values now unescapes doubled single quotes in string defaults, so a default such as 'O''Brien' is returned as "O'Brien". Thanks, ikatyal2110. (#811)
• table.default_values now decodes unquoted TRUE, FALSE and NULL default literals as True, False and None respectively. (#836)
• table.enable_fts(..., tokenize=...) and sqlite-utils enable-fts --tokenize now safely quote the tokenizer argument, preventing a crafted value from injecting additional SQL. Thanks, Bunlong Heng. (#828)
• rows_where(), pks_and_rows_where(), search() and search_sql() now support offset= without requiring limit=. The sqlite-utils rows --offset option now works without --limit too. Thanks, ethanhawkes-gif. (#816, #821)
• Empty or whitespace-only input passed to rows_from_file() is now handled as an empty CSV file instead of raising csv.Error. Thanks, Rami Abdelrazzaq. (#808, #837)
• sqlite-utils convert --dry-run now works for table and column names containing closing square brackets. (#829)
• table.indexes and table.xindexes now work for table, index and column names containing double quotes. This also fixes table.transform() for tables with those identifiers. Thanks, nyxst4ck. (#824, #825)
• Improved type annotations throughout the package and added Pyright regression checks to CI. (#833)
• Changing a TEXT column to INTEGER, FLOAT or REAL using table.transform() or sqlite-utilstransform now converts exact empty strings to NULL. Previously they remained empty strings in the numeric column. Thanks, ikatyal2110. (#488, #805)

table.transform() can handle many more edge-cases:

• table.transform() now preserves column-level and composite UNIQUE constraints, including constraint names, collations, sort order and ON CONFLICT behavior. Renaming columns updates those constraints, while dropping any constituent column removes the entire constraint. (#762)
• table.transform() now preserves AUTOINCREMENT primary keys and their sequence high-water marks. Previously a transform removed AUTOINCREMENT and could reuse deleted row IDs. (#602)
• table.transform() now preserves CHECK constraints, including comments within their expressions. Renaming a column rewrites identifier references in checks without changing string literals or function names. Dropping a column drops a check owned by that column, and raises TransformError if a remaining check depends on it. (#762)
• table.transform() now preserves comments immediately before or after column definitions. These comments move with the column if it is renamed or reordered, and are removed if the column is dropped. (#762)
• table.transform(rename=...) now preserves explicit indexes on renamed columns by dropping and recreating those indexes against the new column names. Previously this raised a TransformError. (#822)
• table.transform() now works for tables that are referenced by views. Previously the ALTER TABLE... RENAME TO step raised no such table if a view referenced the table being transformed. View definitions are left unchanged - see Tables referenced by views. This also fixes a bug where transform(keep_table=...) silently rewrote dependent views to point at the frozen backup table instead of the live one. (#831)

• Documented API for uploading files. #2
• No longer depends on Starlette for file uploads. #13
• Fixed a bug where a corrupt SQLite database could still be swapped in.

• Upgraded for compatability with Datasette 1.0a37.

• Fix for sqlite-utils 4.0. Thanks, Alex Garcia. #44

This release fixes a SQL injection security issue that affects Datasette instances that serve a mixture of public and private tables in the same database, with access configured using the Datasette permissions system.

Site administrators who serve private tables in this way are advised to disable the execute-sql permission permission on that database to prevent users from accessing private tables using raw SQL queries. The bug that has been fixed would have allowed users with access to any public table to execute SQL injection attacks despite that restriction, giving them read-only access to data in private tables in the same database.

This fix is also available in Datasette 0.65.3.

This release fixes a SQL injection security issue that affects Datasette instances that serve a mixture of public and private tables in the same database, with access configured using the Datasette permissions system.

Site administrators who serve private tables in this way are advised to disable the execute-sql permission on that database to prevent users from accessing private tables using raw SQL queries. The bug that has been fixed would have allowed users with access to any public table to execute SQL injection attacks despite that restriction, giving them read-only access to data in private tables in the same database.

This fix is also available in Datasette 1.0a38.