sections_fts
736 rows
This data as json, CSV (advanced)
| Link | rowid ▼ | title | content | sections_fts | rank |
|---|---|---|---|---|---|
| 1 | 1 | Deploying Datasette | The quickest way to deploy a Datasette instance on the internet is to use the datasette publish command, described in Publishing data . This can be used to quickly deploy Datasette to a number of hosting providers including Heroku, Google Cloud Run and Vercel. You can deploy Datasette to other hosting providers using the instructions on this page. | 6 | |
| 2 | 2 | Deployment fundamentals | Datasette can be deployed as a single datasette process that listens on a port. Datasette is not designed to be run as root, so that process should listen on a higher port such as port 8000. If you want to serve Datasette on port 80 (the HTTP default port) or port 443 (for HTTPS) you should run it behind a proxy server, such as nginx, Apache or HAProxy. The proxy server can listen on port 80/443 and forward traffic on to Datasette. | 6 | |
| 3 | 3 | Running Datasette using systemd | You can run Datasette on Ubuntu or Debian systems using systemd . First, ensure you have Python 3 and pip installed. On Ubuntu you can use sudo apt-get install python3 python3-pip . You can install Datasette into a virtual environment, or you can install it system-wide. To install system-wide, use sudo pip3 install datasette . Now create a folder for your Datasette databases, for example using mkdir /home/ubuntu/datasette-root . You can copy a test database into that folder like so: cd /home/ubuntu/datasette-root curl -O https://latest.datasette.io/fixtures.db Create a file at /etc/systemd/system/datasette.service with the following contents: [Unit] Description=Datasette After=network.target [Service] Type=simple User=ubuntu Environment=DATASETTE_SECRET= WorkingDirectory=/home/ubuntu/datasette-root ExecStart=datasette serve . -h 127.0.0.1 -p 8000 Restart=on-failure [Install] WantedBy=multi-user.target Add a random value for the DATASETTE_SECRET - this will be used to sign Datasette cookies such as the CSRF token cookie. You can generate a suitable value like so: python3 -c 'import secrets; print(secrets.token_hex(32))' This configuration will run Datasette against all database files contained in the /home/ubuntu/datasette-root directory. If that directory contains a metadata.yml (or .json ) file or a templates/ or plugins/ sub-directory those will automatically be loaded by Datasette - see Configuration directory mode for details. You can start the Datasette process running using the following: sudo systemctl daemon-reload sudo systemctl start datasette.service You will need to restart the Datasette service after making changes to its metadata.json configuration or adding a new database file to that directory. You can do that using: sudo systemctl restart datasette.service Once the … | 6 | |
| 4 | 4 | Running Datasette using OpenRC | OpenRC is the service manager on non-systemd Linux distributions like Alpine Linux and Gentoo . Create an init script at /etc/init.d/datasette with the following contents: #!/sbin/openrc-run name="datasette" command="datasette" command_args="serve -h 0.0.0.0 /path/to/db.db" command_background=true pidfile="/run/${RC_SVCNAME}.pid" You then need to configure the service to run at boot and start it: rc-update add datasette rc-service datasette start | 6 | |
| 5 | 5 | Deploying using buildpacks | Some hosting providers such as Heroku , DigitalOcean App Platform and Scalingo support the Buildpacks standard for deploying Python web applications. Deploying Datasette on these platforms requires two files: requirements.txt and Procfile . The requirements.txt file lets the platform know which Python packages should be installed. It should contain datasette at a minimum, but can also list any Datasette plugins you wish to install - for example: datasette datasette-vega The Procfile lets the hosting platform know how to run the command that serves web traffic. It should look like this: web: datasette . -h 0.0.0.0 -p $PORT --cors The $PORT environment variable is provided by the hosting platform. --cors enables CORS requests from JavaScript running on other websites to your domain - omit this if you don't want to allow CORS. You can add additional Datasette Settings options here too. These two files should be enough to deploy Datasette on any host that supports buildpacks. Datasette will serve any SQLite files that are included in the root directory of the application. If you want to build SQLite files or download them as part of the deployment process you can do so using a bin/post_compile file. For example, the following bin/post_compile will download an example database that will then be served by Datasette: wget https://fivethirtyeight.datasettes.com/fivethirtyeight.db simonw/buildpack-datasette-demo is an example GitHub repository showing a Datasette configuration that can be deployed to a buildpack-supporting host. | 6 | |
| 6 | 6 | Running Datasette behind a proxy | You may wish to run Datasette behind an Apache or nginx proxy, using a path within your existing site. You can use the base_url configuration setting to tell Datasette to serve traffic with a specific URL prefix. For example, you could run Datasette like this: datasette my-database.db --setting base_url /my-datasette/ -p 8009 This will run Datasette with the following URLs: http://127.0.0.1:8009/my-datasette/ - the Datasette homepage http://127.0.0.1:8009/my-datasette/my-database - the page for the my-database.db database http://127.0.0.1:8009/my-datasette/my-database/some_table - the page for the some_table table You can now set your nginx or Apache server to proxy the /my-datasette/ path to this Datasette instance. | 6 | |
| 7 | 7 | Nginx proxy configuration | Here is an example of an nginx configuration file that will proxy traffic to Datasette: daemon off; events { worker_connections 1024; } http { server { listen 80; location /my-datasette { proxy_pass http://127.0.0.1:8009/my-datasette; proxy_set_header Host $host; } } } You can also use the --uds option to Datasette to listen on a Unix domain socket instead of a port, configuring the nginx upstream proxy like this: daemon off; events { worker_connections 1024; } http { server { listen 80; location /my-datasette { proxy_pass http://datasette/my-datasette; proxy_set_header Host $host; } } upstream datasette { server unix:/tmp/datasette.sock; } } Then run Datasette with datasette --uds /tmp/datasette.sock path/to/database.db --setting base_url /my-datasette/ . | 6 | |
| 8 | 8 | Apache proxy configuration | For Apache , you can use the ProxyPass directive. First make sure the following lines are uncommented: LoadModule proxy_module lib/httpd/modules/mod_proxy.so LoadModule proxy_http_module lib/httpd/modules/mod_proxy_http.so Then add these directives to proxy traffic: ProxyPass /my-datasette/ http://127.0.0.1:8009/my-datasette/ ProxyPreserveHost On A live demo of Datasette running behind Apache using this proxy setup can be seen at datasette-apache-proxy-demo.datasette.io/prefix/ . The code for that demo can be found in the demos/apache-proxy directory. Using --uds you can use Unix domain sockets similar to the nginx example: ProxyPass /my-datasette/ unix:/tmp/datasette.sock|http://localhost/my-datasette/ The ProxyPreserveHost On directive ensures that the original Host: header from the incoming request is passed through to Datasette. Datasette needs this to correctly assemble links to other pages using the .absolute_url(request, path) method. | 6 | |
| 9 | 9 | Metadata | Data loves metadata. Any time you run Datasette you can optionally include a YAML or JSON file with metadata about your databases and tables. Datasette will then display that information in the web UI. Run Datasette like this: datasette database1.db database2.db --metadata metadata.yaml Your metadata.yaml file can look something like this: [[[cog from metadata_doc import metadata_example metadata_example(cog, { "title": "Custom title for your index page", "description": "Some description text can go here", "license": "ODbL", "license_url": "https://opendatacommons.org/licenses/odbl/", "source": "Original Data Source", "source_url": "http://example.com/" }) ]]] [[[end]]] Choosing YAML over JSON adds support for multi-line strings and comments. The above metadata will be displayed on the index page of your Datasette-powered site. The source and license information will also be included in the footer of every page served by Datasette. Any special HTML characters in description will be escaped. If you want to include HTML in your description, you can use a description_html property instead. | 6 | |
| 10 | 10 | Per-database and per-table metadata | Metadata at the top level of the file will be shown on the index page and in the footer on every page of the site. The license and source is expected to apply to all of your data. You can also provide metadata at the per-database or per-table level, like this: [[[cog metadata_example(cog, { "databases": { "database1": { "source": "Alternative source", "source_url": "http://example.com/", "tables": { "example_table": { "description_html": "Custom <em>table</em> description", "license": "CC BY 3.0 US", "license_url": "https://creativecommons.org/licenses/by/3.0/us/" } } } } }) ]]] [[[end]]] Each of the top-level metadata fields can be used at the database and table level. | 6 | |
| 11 | 11 | Source, license and about | The three visible metadata fields you can apply to everything, specific databases or specific tables are source, license and about. All three are optional. source and source_url should be used to indicate where the underlying data came from. license and license_url should be used to indicate the license under which the data can be used. about and about_url can be used to link to further information about the project - an accompanying blog entry for example. For each of these you can provide just the *_url field and Datasette will treat that as the default link label text and display the URL directly on the page. | 6 | |
| 12 | 12 | Column descriptions | You can include descriptions for your columns by adding a "columns": {"name-of-column": "description-of-column"} block to your table metadata: [[[cog metadata_example(cog, { "databases": { "database1": { "tables": { "example_table": { "columns": { "column1": "Description of column 1", "column2": "Description of column 2" } } } } } }) ]]] [[[end]]] These will be displayed at the top of the table page, and will also show in the cog menu for each column. You can see an example of how these look at latest.datasette.io/fixtures/roadside_attractions . | 6 | |
| 13 | 13 | Table configuration | Datasette supports a range of table-level configuration options including sort order, page size, facets, full-text search, column types, and more. These are now documented in the table configuration section of the configuration reference. For backwards compatibility these options can be specified in either metadata.yaml or datasette.yaml . | 6 | |
| 14 | 14 | Metadata reference | A full reference of every supported option in a metadata.json or metadata.yaml file. | 6 | |
| 15 | 15 | Top-level metadata | "Top-level" metadata refers to fields that can be specified at the root level of a metadata file. These attributes are meant to describe the entire Datasette instance. The following are the full list of allowed top-level metadata fields: title description description_html license license_url source source_url | 6 | |
| 16 | 16 | Database-level metadata | "Database-level" metadata refers to fields that can be specified for each database in a Datasette instance. These attributes should be listed under a database inside the "databases" field. The following are the full list of allowed database-level metadata fields: source source_url license license_url about about_url | 6 | |
| 17 | 17 | Table-level metadata | "Table-level" metadata refers to fields that can be specified for each table in a Datasette instance. These attributes should be listed under a specific table using the "tables" field. The following metadata fields are supported at the table level: source source_url license license_url about about_url columns (see Column descriptions ) Additionally, tables support a number of configuration options ( sort , sort_desc , size , sortable_columns , label_column , hidden , facets , facet_size , fts_table , fts_pk , searchmode , column_types ). See table configuration for full details. | 6 | |
| 18 | 18 | Events | Datasette includes a mechanism for tracking events that occur while the software is running. This is primarily intended to be used by plugins, which can both trigger events and listen for events. The core Datasette application triggers events when certain things happen. This page describes those events. Note that these events will not fire for changes made to a SQLite database by a process other than Datasette itself. Plugins can listen for events using the track_event(datasette, event) plugin hook, which will be called with instances of the following classes - or additional classes registered by other plugins . class datasette.events. LoginEvent actor : dict | None Event name: login A user (represented by event.actor ) has logged in. class datasette.events. LogoutEvent actor : dict | None Event name: logout A user (represented by event.actor ) has logged out. class datasette.events. CreateTokenEvent actor : dict | None expires_after : int | None restrict_all : list restrict_database : dict restrict_resource : dict Event name: create-token A user created an API token. Variables expires_after -- Number of seconds after which this token will expire. restrict_all -- Restricted permissions for this token. restrict_database -- R… | 6 | |
| 19 | 19 | Custom pages and templates | Datasette provides a number of ways of customizing the way data is displayed. | 6 | |
| 20 | 20 | CSS classes on the <body> | Every default template includes CSS classes in the body designed to support custom styling. The index template (the top level page at / ) gets this: <body class="index"> The database template ( /dbname ) gets this: <body class="db db-dbname"> The custom SQL template ( /dbname?sql=... ) gets this: <body class="query db-dbname"> A stored query template ( /dbname/queryname ) gets this: <body class="query db-dbname query-queryname"> The table template ( /dbname/tablename ) gets: <body class="table db-dbname table-tablename"> The row template ( /dbname/tablename/rowid ) gets: <body class="row db-dbname table-tablename"> The db-x and table-x classes use the database or table names themselves if they are valid CSS identifiers. If they aren't, we strip any invalid characters out and append a 6 character md5 digest of the original name, in order to ensure that multiple tables which resolve to the same stripped character version still have different CSS classes. Some examples: "simple" => "simple" "MixedCase" => "MixedCase" "-no-leading-hyphens" => "no-leading-hyphens-65bea6" "_no-leading-underscores" => "no-leading-underscores-b921bc" "no spaces" => "no-spaces-7088d7" "-" => "336d5e" "no $ characters" => "no--characters-59e024" <td> and <th> elements also get custom CSS classes reflecting the database column they are representing, for example: <table> <thead> <tr> <th class="col-id" scope="col">id</th> <th class="col-name" scope="col">name</th> </tr> </thead> <tbody> <tr> <td class="col-id"><a href="...">1</a></td> <td class="col-name">SMITH</td> </tr> </tbody> </table> | 6 | |
| 21 | 21 | Writing custom CSS | Custom templates need to take Datasette's default CSS into account. The pattern portfolio at /-/patterns ( example here ) is a useful reference for understanding the available CSS classes. The core class is particularly useful - you can apply this directly to a <input> or <button> element to get Datasette's default form styles, or you can apply it to a containing element (such as <form> ) to apply those styles to all of the form elements within it. | 6 | |
| 22 | 22 | Serving static files | Datasette can serve static files for you, using the --static option. Consider the following directory structure: metadata.json static-files/styles.css static-files/app.js You can start Datasette using --static assets:static-files/ to serve those files from the /assets/ mount point: datasette --config datasette.yaml --static assets:static-files/ --memory The following URLs will now serve the content from those CSS and JS files: http://localhost:8001/assets/styles.css http://localhost:8001/assets/app.js You can reference those files from datasette.yaml like this, see custom CSS and JavaScript for more details: [[[cog from metadata_doc import config_example config_example(cog, """ extra_css_urls: - /assets/styles.css extra_js_urls: - /assets/app.js """) ]]] [[[end]]] | 6 | |
| 23 | 23 | Publishing static assets | The datasette publish command can be used to publish your static assets, using the same syntax as above: datasette publish cloudrun mydb.db --static assets:static-files/ This will upload the contents of the static-files/ directory as part of the deployment, and configure Datasette to correctly serve the assets from /assets/ . | 6 | |
| 24 | 24 | Custom templates | By default, Datasette uses default templates that ship with the package. You can over-ride these templates by specifying a custom --template-dir like this: datasette mydb.db --template-dir=mytemplates/ Datasette will now first look for templates in that directory, and fall back on the defaults if no matches are found. It is also possible to over-ride templates on a per-database, per-row or per- table basis. The lookup rules Datasette uses are as follows: Index page (/): index.html Database page (/mydatabase): database-mydatabase.html database.html Custom query page (/mydatabase?sql=...): query-mydatabase.html query.html Stored query page (/mydatabase/query-name): query-mydatabase-query-name.html query-mydatabase.html query.html Table page (/mydatabase/mytable): table-mydatabase-mytable.html table.html Row page (/mydatabase/mytable/id): row-mydatabase-mytable.html row.html Table of rows and columns include on table page: _table-table-mydatabase-mytable.html _table-mydatabase-mytable.html _table.html Table of rows and columns include on row page: _table-row-mydatabase-mytable.html _table-mydatabase-mytable.html _table.html If a table name has spaces or other unexpected characters in it, the template filename will follow the same rules as our custom <body> CSS classes - for example, a table called "Food Trucks" will attempt to load the following templates: table-mydatabase-Food-Trucks-399138.html table.html You can find out which templates were considered for a specific page by viewing source on that page and looking for an HTML comment at the bottom. The comment will look something like this: <!-- Te… | 6 | |
| 25 | 25 | Custom pages | You can add templated pages to your Datasette instance by creating HTML files in a pages directory within your templates directory. For example, to add a custom page that is served at http://localhost/about you would create a file in templates/pages/about.html , then start Datasette like this: datasette mydb.db --template-dir=templates/ You can nest directories within pages to create a nested structure. To create a http://localhost:8001/about/map page you would create templates/pages/about/map.html . | 6 | |
| 26 | 26 | Path parameters for pages | You can define custom pages that match multiple paths by creating files with {variable} definitions in their filenames. For example, to capture any request to a URL matching /about/* , you would create a template in the following location: templates/pages/about/{slug}.html A hit to /about/news would render that template and pass in a variable called slug with a value of "news" . If you use this mechanism don't forget to return a 404 if the referenced content could not be found. You can do this using {{ raise_404() }} described below. Templates defined using custom page routes work particularly well with the sql() template function from datasette-template-sql or the graphql() template function from datasette-graphql . | 6 | |
| 27 | 27 | Custom headers and status codes | Custom pages default to being served with a content-type of text/html; charset=utf-8 and a 200 status code. You can change these by calling a custom function from within your template. For example, to serve a custom page with a 418 I'm a teapot HTTP status code, create a file in pages/teapot.html containing the following: {{ custom_status(418) }} <html> <head><title>Teapot</title></head> <body> I'm a teapot </body> </html> To serve a custom HTTP header, add a custom_header(name, value) function call. For example: {{ custom_status(418) }} {{ custom_header("x-teapot", "I am") }} <html> <head><title>Teapot</title></head> <body> I'm a teapot </body> </html> You can verify this is working using curl like this: curl -I 'http://127.0.0.1:8001/teapot' HTTP/1.1 418 date: Sun, 26 Apr 2020 18:38:30 GMT server: uvicorn x-teapot: I am content-type: text/html; charset=utf-8 | 6 | |
| 28 | 28 | Returning 404s | To indicate that content could not be found and display the default 404 page you can use the raise_404(message) function: {% if not rows %} {{ raise_404("Content not found") }} {% endif %} If you call raise_404() the other content in your template will be ignored. | 6 | |
| 29 | 29 | Custom redirects | You can use the custom_redirect(location) function to redirect users to another page, for example in a file called pages/datasette.html : {{ custom_redirect("https://github.com/simonw/datasette") }} Now requests to http://localhost:8001/datasette will result in a redirect. These redirects are served with a 302 Found status code by default. You can send a 301 Moved Permanently code by passing 301 as the second argument to the function: {{ custom_redirect("https://github.com/simonw/datasette", 301) }} | 6 | |
| 30 | 30 | Custom error pages | Datasette returns an error page if an unexpected error occurs, access is forbidden or content cannot be found. You can customize the response returned for these errors by providing a custom error page template. Content not found errors use a 404.html template. Access denied errors use 403.html . Invalid input errors use 400.html . Unexpected errors of other kinds use 500.html . If a template for the specific error code is not found a template called error.html will be used instead. If you do not provide that template Datasette's default error.html template will be used. The error template will be passed the following context: status - integer The integer HTTP status code, e.g. 404, 500, 403, 400. error - string Details of the specific error, usually a full sentence. title - string or None A title for the page representing the class of error. This is often None for errors that do not provide a title separate from their error message. | 6 | |
| 31 | 31 | Binary data | SQLite tables can contain binary data in BLOB columns. Datasette includes special handling for these binary values. The Datasette interface detects binary values and provides a link to download their content, for example on https://latest.datasette.io/fixtures/binary_data Binary data is represented in .json exports using Base64 encoding. https://latest.datasette.io/fixtures/binary_data.json?_shape=array [ { "rowid": 1, "data": { "$base64": true, "encoded": "FRwCx60F/g==" } }, { "rowid": 2, "data": { "$base64": true, "encoded": "FRwDx60F/g==" } }, { "rowid": 3, "data": null } ] | 6 | |
| 32 | 32 | Linking to binary downloads | The .blob output format is used to return binary data. It requires a _blob_column= query string argument specifying which BLOB column should be downloaded, for example: https://latest.datasette.io/fixtures/binary_data/1.blob?_blob_column=data This output format can also be used to return binary data from an arbitrary SQL query. Since such queries do not specify an exact row, an additional ?_blob_hash= parameter can be used to specify the SHA-256 hash of the value that is being linked to. Consider the query select data from binary_data - demonstrated here . That page links to the binary value downloads. Those links look like this: https://latest.datasette.io/fixtures.blob?sql=select+data+from+binary_data&_blob_column=data&_blob_hash=f3088978da8f9aea479ffc7f631370b968d2e855eeb172bea7f6c7a04262bb6d These .blob links are also returned in the .csv exports Datasette provides for binary tables and queries, since the CSV format does not have a mechanism for representing binary data. | 6 | |
| 33 | 33 | Binary plugins | Several Datasette plugins are available that change the way Datasette treats binary data. datasette-render-binary modifies Datasette's default interface to show an automatic guess at what type of binary data is being stored, along with a visual representation of the binary value that displays ASCII strings directly in the interface. datasette-render-images detects common image formats and renders them as images directly in the Datasette interface. datasette-media allows Datasette interfaces to be configured to serve binary files from configured SQL queries, and includes the ability to resize images directly before serving them. | 6 | |
| 34 | 34 | Pages and API endpoints | The Datasette web application offers a number of different pages that can be accessed to explore the data in question, each of which is accompanied by an equivalent JSON API. | 6 | |
| 35 | 35 | Top-level index | The root page of any Datasette installation is an index page that lists all of the currently attached databases. Some examples: fivethirtyeight.datasettes.com register-of-members-interests.datasettes.com Add /.json to the end of the URL for the JSON version of the underlying data: fivethirtyeight.datasettes.com/.json register-of-members-interests.datasettes.com/.json The index page can also be accessed at /-/ , useful for if the default index page has been replaced using an index.html custom template . The /-/ page will always render the default Datasette index.html template. | 6 | |
| 36 | 36 | Database | Each database has a page listing the tables, views and stored queries available for that database. If the execute-sql permission is enabled (it's on by default) there will also be an interface for executing arbitrary SQL select queries against the data. Examples: fivethirtyeight.datasettes.com/fivethirtyeight datasette.io/global-power-plants The JSON version of this page provides programmatic access to the underlying data: fivethirtyeight.datasettes.com/fivethirtyeight.json datasette.io/global-power-plants.json The returned object includes an "ok": true key alongside keys such as "database" , "tables" , "views" , "queries" and "metadata" . | 6 | |
| 37 | 37 | Hidden tables | Some tables listed on the database page are treated as hidden. Hidden tables are not completely invisible - they can be accessed through the "hidden tables" link at the bottom of the page. They are hidden because they represent low-level implementation details which are generally not useful to end-users of Datasette. The following tables are hidden by default: Any table with a name that starts with an underscore - this is a Datasette convention to help plugins easily hide their own internal tables. Tables that have been configured as "hidden": true using hidden . *_fts tables that implement SQLite full-text search indexes. Tables relating to the inner workings of the SpatiaLite SQLite extension. sqlite_stat tables used to store statistics used by the query optimizer. | 6 | |
| 38 | 38 | Queries | The /database-name/-/query page can be used to execute an arbitrary SQL query against that database, if the execute-sql permission is enabled. This query is passed as the ?sql= query string parameter. This means you can link directly to a query by constructing the following URL: /database-name/-/query?sql=SELECT+*+FROM+table_name Each configured stored query has its own page, at /database-name/query-name . Viewing this page will execute the query and display the results. In both cases adding a .json extension to the URL will return the results as JSON. | 6 | |
| 39 | 39 | Table | The table page is the heart of Datasette: it allows users to interactively explore the contents of a database table, including sorting, filtering, Full-text search and applying Facets . The HTML interface is worth spending some time exploring. As with other pages, you can return the JSON data by appending .json to the URL path, before any ? query string arguments. The query string arguments are described in more detail here: Table arguments You can also use the table page to interactively construct a SQL query - by applying different filters and a sort order for example - and then click the "View and edit SQL" link to see the SQL query that was used for the page and edit and re-submit it. Some examples: ../items lists all of the line-items registered by UK MPs as potential conflicts of interest. It demonstrates Datasette's support for Full-text search . ../antiquities-act%2Factions_under_antiquities_act is an interface for exploring the "actions under the antiquities act" data table published by FiveThirtyEight. ../global-power-plants?country_long=United+Kingdom&primary_fuel=Gas is a filtered table page showing every Gas power plant in the United Kingdom. It includes some default facets (configured using its metadata.json ) and uses the datasette-cluster-map plugin to show a map of the results. | 6 | |
| 40 | 40 | Row | Every row in every Datasette table has its own URL. This means individual records can be linked to directly. Table cells with extremely long text contents are truncated on the table view according to the truncate_cells_html setting. If a cell has been truncated the full length version of that cell will be available on the row page. Rows which are the targets of foreign key references from other tables will show a link to a filtered search for all records that reference that row. Here's an example from the Registers of Members Interests database: ../people/uk~2Eorg~2Epublicwhip~2Fperson~2F10001 Note that this URL includes the encoded primary key of the record. Here's that same page as JSON: ../people/uk~2Eorg~2Epublicwhip~2Fperson~2F10001.json | 6 | |
| 41 | 41 | Schemas | Datasette offers /-/schema endpoints to expose the SQL schema for databases and tables. | 6 | |
| 42 | 42 | Instance schema | Access /-/schema to see the complete schema for all attached databases in the Datasette instance. Use /-/schema.md to get the same information as Markdown. Use /-/schema.json to get the same information as JSON, which looks like this: { "schemas": [ { "database": "content", "schema": "create table posts ..." } } | 6 | |
| 43 | 43 | Database schema | Use /database-name/-/schema to see the complete schema for a specific database. The .md and .json extensions work here too. The JSON returns an object with "database" and "schema" keys. | 6 | |
| 44 | 44 | Table schema | Use /database-name/table-name/-/schema to see the schema for a specific table. The .md and .json extensions work here too. The JSON returns an object with "database" , "table" , and "schema" keys. | 6 | |
| 45 | 45 | Full-text search | SQLite includes a powerful mechanism for enabling full-text search against SQLite records. Datasette can detect if a table has had full-text search configured for it in the underlying database and display a search interface for filtering that table. Here's an example search : Datasette automatically detects which tables have been configured for full-text search. | 6 | |
| 46 | 46 | The table page and table view API | Table views that support full-text search can be queried using the ?_search=TERMS query string parameter. This will run the search against content from all of the columns that have been included in the index. Try this example: fara.datasettes.com/fara/FARA_All_ShortForms?_search=manafort SQLite full-text search supports wildcards. This means you can easily implement prefix auto-complete by including an asterisk at the end of the search term - for example: /dbname/tablename/?_search=rob* This will return all records containing at least one word that starts with the letters rob . You can also run searches against just the content of a specific named column by using _search_COLNAME=TERMS - for example, this would search for just rows where the name column in the FTS index mentions Sarah : /dbname/tablename/?_search_name=Sarah | 6 | |
| 47 | 47 | Advanced SQLite search queries | SQLite full-text search includes support for a variety of advanced queries , including AND , OR , NOT and NEAR . By default Datasette disables these features to ensure they do not cause errors or confusion for users who are not aware of them. You can disable this escaping and use the advanced queries by adding &_searchmode=raw to the table page query string. If you want to enable these operators by default for a specific table, you can do so by adding "searchmode": "raw" to the metadata configuration for that table, see Configuring full-text search for a table or view . If that option has been specified in the table metadata but you want to over-ride it and return to the default behavior you can append &_searchmode=escaped to the query string. | 6 | |
| 48 | 48 | Configuring full-text search for a table or view | If a table has a corresponding FTS table set up using the content= argument to CREATE VIRTUAL TABLE shown below, Datasette will detect it automatically and add a search interface to the table page for that table. You can also manually configure which table should be used for full-text search using query string parameters or table configuration in datasette.yaml (see fts_table / fts_pk / searchmode ). You can set the associated FTS table for a specific table and you can also set one for a view - if you do that, the page for that SQL view will offer a search option. Use ?_fts_table=x to over-ride the FTS table for a specific page. If the primary key was something other than rowid you can use ?_fts_pk=col to set that as well. This is particularly useful for views, for example: https://latest.datasette.io/fixtures/searchable_view?_fts_table=searchable_fts&_fts_pk=pk The fts_table metadata property can be used to specify an associated FTS table. If the primary key column in your table which was used to populate the FTS table is something other than rowid , you can specify the column to use with the fts_pk property. The "searchmode": "raw" property can be used to default the table to accepting SQLite advanced search operators, as described in Advanced SQLite search queries . Here is an example which enables full-text search (with SQLite advanced search operators) for a display_ads view which is defined against the ads table and hence needs to run FTS against the ads_fts table, using the id as the primary key: [[[cog from metadata_doc import config_example config_example(cog, { "databases": { "russian-ads": { "tables": { "display_ads": { "fts_table": "ads_fts", "fts_pk": "id", "searchmode": "raw" } } } } }) ]]] [[[end]]] | 6 | |
| 49 | 49 | Searches using custom SQL | You can include full-text search results in custom SQL queries. The general pattern with SQLite search is to run the search as a sub-select that returns rowid values, then include those rowids in another part of the query. You can see the syntax for a basic search by running that search on a table page and then clicking "View and edit SQL" to see the underlying SQL. For example, consider this search for manafort is the US FARA database : /fara/FARA_All_ShortForms?_search=manafort If you click View and edit SQL you'll see that the underlying SQL looks like this: select rowid, Short_Form_Termination_Date, Short_Form_Date, Short_Form_Last_Name, Short_Form_First_Name, Registration_Number, Registration_Date, Registrant_Name, Address_1, Address_2, City, State, Zip from FARA_All_ShortForms where rowid in ( select rowid from FARA_All_ShortForms_fts where FARA_All_ShortForms_fts match escape_fts(:search) ) order by rowid limit 101 | 6 | |
| 50 | 50 | Enabling full-text search for a SQLite table | Datasette takes advantage of the external content mechanism in SQLite, which allows a full-text search virtual table to be associated with the contents of another SQLite table. To set up full-text search for a table, you need to do two things: Create a new FTS virtual table associated with your table Populate that FTS table with the data that you would like to be able to run searches against | 6 | |
| 51 | 51 | Configuring FTS using sqlite-utils | sqlite-utils is a CLI utility and Python library for manipulating SQLite databases. You can use it from Python code to configure FTS search, or you can achieve the same goal using the accompanying command-line tool . Here's how to use sqlite-utils to enable full-text search for an items table across the name and description columns: sqlite-utils enable-fts mydatabase.db items name description | 6 | |
| 52 | 52 | Configuring FTS using csvs-to-sqlite | If your data starts out in CSV files, you can use Datasette's companion tool csvs-to-sqlite to convert that file into a SQLite database and enable full-text search on specific columns. For a file called items.csv where you want full-text search to operate against the name and description columns you would run the following: csvs-to-sqlite items.csv items.db -f name -f description | 6 | |
| 53 | 53 | Configuring FTS by hand | We recommend using sqlite-utils , but if you want to hand-roll a SQLite full-text search table you can do so using the following SQL. To enable full-text search for a table called items that works against the name and description columns, you would run this SQL to create a new items_fts FTS virtual table: CREATE VIRTUAL TABLE "items_fts" USING FTS4 ( name, description, content="items" ); This creates a set of tables to power full-text search against items . The new items_fts table will be detected by Datasette as the fts_table for the items table. Creating the table is not enough: you also need to populate it with a copy of the data that you wish to make searchable. You can do that using the following SQL: INSERT INTO "items_fts" (rowid, name, description) SELECT rowid, name, description FROM items; If your table has columns that are foreign key references to other tables you can include that data in your full-text search index using a join. Imagine the items table has a foreign key column called category_id which refers to a categories table - you could create a full-text search table like this: CREATE VIRTUAL TABLE "items_fts" USING FTS4 ( name, description, category_name, content="items" ); And then populate it like this: INSERT INTO "items_fts" (rowid, name, description, category_name) SELECT items.rowid, items.name, items.description, categories.name FROM items JOIN categories ON items.category_id=categories.id; You can use this technique to populate the full-text search index from any combination of tables and joins that makes sense for your project. | 6 | |
| 54 | 54 | FTS versions | There are three different versions of the SQLite FTS module: FTS3, FTS4 and FTS5. You can tell which versions are supported by your instance of Datasette by checking the /-/versions page. FTS5 is the most advanced module but may not be available in the SQLite version that is bundled with your Python installation. Most importantly, FTS5 is the only version that has the ability to order by search relevance without needing extra code. If you can't be sure that FTS5 will be available, you should use FTS4. | 6 | |
| 55 | 55 | Facets | Datasette facets can be used to add a faceted browse interface to any database table. With facets, tables are displayed along with a summary showing the most common values in specified columns. These values can be selected to further filter the table. Here's an example : Facets can be specified in two ways: using query string parameters, or in metadata.json configuration for the table. | 6 | |
| 56 | 56 | Facets in query strings | To turn on faceting for specific columns on a Datasette table view, add one or more _facet=COLUMN parameters to the URL. For example, if you want to turn on facets for the city_id and state columns, construct a URL that looks like this: /dbname/tablename?_facet=state&_facet=city_id This works for both the HTML interface and the .json view. When enabled, facets will cause a facet_results block to be added to the JSON output, looking something like this: { "state": { "name": "state", "results": [ { "value": "CA", "label": "CA", "count": 10, "toggle_url": "http://...?_facet=city_id&_facet=state&state=CA", "selected": false }, { "value": "MI", "label": "MI", "count": 4, "toggle_url": "http://...?_facet=city_id&_facet=state&state=MI", "selected": false }, { "value": "MC", "label": "MC", "count": 1, "toggle_url": "http://...?_facet=city_id&_facet=state&state=MC", "selected": false } ], "truncated": false } "city_id": { "name": "city_id", "results": [ { "value": 1, "label": "San Francisco", "count": 6, "toggle_url": "http://...?_facet=city_id&_facet=state&city_id=1", "selected": false }, { "value": 2, "label": "Los Angeles", "count": 4, "toggle_url": "http://...?_facet=city_id&_facet=state&city_id=2", "selected": false }, { "value": 3, "label": "Detroit", "count": 4, "toggle_url": "http://...?_facet=city_id&_facet=state&city_id=3", "selected": false }, { "value": 4, "label": "Memnonia", "count": 1, "toggle_url": "http://...?_facet=city_id&_facet=state&city_id=4", "selected": false } ], "truncated": false } } If Datasette detect… | 6 | |
| 57 | 57 | Facets in configuration | You can turn facets on by default for specific tables by adding a "facets" key to the table configuration in datasette.yaml . See also the table configuration reference for a quick overview. Here's an example that turns on faceting by default for the qLegalStatus column in the Street_Tree_List table in the sf-trees database: [[[cog from metadata_doc import config_example config_example(cog, { "databases": { "sf-trees": { "tables": { "Street_Tree_List": { "facets": ["qLegalStatus"] } } } } }) ]]] [[[end]]] Facets defined in this way will always be shown in the interface and returned in the API, regardless of the _facet arguments passed to the view. Facets defined in configuration will be displayed in the order they are listed. Any additional facets added via query string parameters (e.g. ?_facet=column_name ) will appear after the configured facets, sorted by the number of unique values. You can specify array or date facets using JSON objects with a single key of array or date and a value specifying the column, like this: [[[cog config_example(cog, { "facets": [ {"array": "tags"}, {"date": "created"} ] }) ]]] [[[end]]] You can change the default facet size (the number of results shown for each facet) for a table using facet_size : [[[cog config_example(cog, { "databases": { "sf-trees": { "tables": { "Street_Tree_List": { "facets": ["qLegalStatus"], "facet_size": 10 } } } } }) ]]] [[[end]]] | 6 | |
| 58 | 58 | Suggested facets | Datasette's table UI will suggest facets for the user to apply, based on the following criteria: For the currently filtered data are there any columns which, if applied as a facet... Will return 30 or less unique options Will return more than one unique option Will return less unique options than the total number of filtered rows And the query used to evaluate this criteria can be completed in under 50ms That last point is particularly important: Datasette runs a query for every column that is displayed on a page, which could get expensive - so to avoid slow load times it sets a time limit of just 50ms for each of those queries. This means suggested facets are unlikely to appear for tables with millions of records in them. | 6 | |
| 59 | 59 | Speeding up facets with indexes | The performance of facets can be greatly improved by adding indexes on the columns you wish to facet by. Adding indexes can be performed using the sqlite3 command-line utility. Here's how to add an index on the state column in a table called Food_Trucks : sqlite3 mydatabase.db SQLite version 3.19.3 2017-06-27 16:48:08 Enter ".help" for usage hints. sqlite> CREATE INDEX Food_Trucks_state ON Food_Trucks("state"); Or using the sqlite-utils command-line utility: sqlite-utils create-index mydatabase.db Food_Trucks state | 6 | |
| 60 | 60 | Facet by JSON array | If your SQLite installation provides the json1 extension (you can check using /-/versions ) Datasette will automatically detect columns that contain JSON arrays of values and offer a faceting interface against those columns. This is useful for modelling things like tags without needing to break them out into a new table. Example here: latest.datasette.io/fixtures/facetable?_facet_array=tags | 6 | |
| 61 | 61 | Facet by date | If Datasette finds any columns that contain dates in the first 100 values, it will offer a faceting interface against the dates of those values. This works especially well against timestamp values such as 2019-03-01 12:44:00 . Example here: latest.datasette.io/fixtures/facetable?_facet_date=created | 6 | |
| 62 | 62 | Changelog | 6 | ||
| 63 | 63 | Unreleased | 6 | ||
| 64 | 64 | 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 APIs: 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 browsers at /-/queries and /<database>/-/queries . Those browsers 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 ) 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 ) | 6 | |
| 65 | 65 | 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 and links to a newly inserted row when a single-row insert succeeds. ( #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 ) | 6 | |
| 66 | 66 | 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 ) | 6 | |
| 67 | 67 | Bug fixes | Fixed a bug where visiting /<database>/-/query without a ?sql= parameter returned a 500 error. ( #2743 ) | 6 | |
| 68 | 68 | 1.0a30 (2026-05-24) | 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 … | 6 | |
| 69 | 69 | 1.0a29 (2026-05-12) | New TokenRestrictions.abbreviated(datasette) utility method for creating "_r" dictionaries. ( #2695 ) Table headers and column options are now visible even if a table contains zero rows. ( #2701 ) Fixed bug with display of column actions dialog on Mobile Safari. ( #2708 ) Fixed bug where tests could crash with a segfault due to a race condition between Datasette.close() and Datasette.close() . ( #2709 ) | 6 | |
| 70 | 70 | 1.0a28 (2026-04-16) | Fixed a compatibility bug introduced in 1.0a27 where execute_write_fn() callbacks with a parameter name other than conn were seeing errors. ( #2691 ) The database.close() method now also shuts down the write connection for that database. New datasette.close() method for closing down all databases and resources associated with a Datasette instance. This is called automatically when the server shuts down. ( #2693 ) Datasette now includes a pytest plugin which automatically calls datasette.close() on temporary instances created in function-scoped fixtures and during tests. See Automatic cleanup of Datasette instances for details. This helps avoid running out of file descriptors in plugin test suites that were written before the Database(is_temp_disk=True) feature introduced in Datasette 1.0a27. ( #2692 ) | 6 | |
| 71 | 71 | 1.0a27 (2026-04-15) | 6 | ||
| 72 | 72 | CSRF protection no longer uses CSRF tokens | Datasette's token-based CSRF protection has been replaced with a mechanism based on the Sec-Fetch-Site and Origin request headers, which are supported by all modern browsers . See this article by Filippo Valsorda for more details of this approach. This removes the need for CSRF tokens in forms and AJAX requests. ( #2689 ) | 6 | |
| 73 | 73 | Renaming a table within Datasette will now fire a new RenameTableEvent , which plugins can use to react by updating ACL records or re-assigning comments or other associated records to the new table name. ( #2681 ) This event will not be fired if the table is renamed by SQL running in some other process. The datasette.track_event() method can now be called from within a write operation (using database.execute_write() and related methods) and the event will be fired after the write transaction has successfully committed. ( #2682 ) | 6 | ||
| 74 | 74 | Other changes | New actor= parameter for datasette.client methods, allowing internal requests to be made as a specific actor. This is particularly useful for writing automated tests. ( #2688 ) New Database(is_temp_disk=True) option, used internally for the internal database. This helps resolve intermittent database locked errors caused by the internal database being in-memory as opposed to on-disk. ( #2683 ) ( #2684 ) The /<database>/<table>/-/upsert API ( docs ) now rejects rows with null primary key values. ( #1936 ) Improved example in the API explorer for the /-/upsert endpoint ( docs ). ( #1936 ) The /<database>.json endpoint now includes an "ok": true key, for consistency with other JSON API responses. call_with_supported_arguments() is now documented as a supported public API. ( #2678 ) | 6 | |
| 75 | 75 | 1.0a26 (2026-03-18) | 6 | ||
| 76 | 76 | New | Table columns can now have custom column types assigned to them, using the new column_types table configuration option or at runtime using a new UI and POST /<database>/<table>/-/set-column-type JSON API. Built-in column types include url , email , and json , and plugins can register additional types using the new register_column_types() plugin hook. ( #2664 , #2671 ) Column types can customize HTML rendering, validate values written through the insert, update, and upsert APIs, and transform values returned by the JSON API. They can optionally restrict themselves to specific SQLite column types using sqlite_types . This feature also introduces a new set-column-type permission for assigning column types to a table. ( #2672 ) The render_cell() plugin hook now receives a column_type argument containing the assigned type instance, and a column type's own render_cell() method takes priority over the plugin hook chain. The datasette-files plugin will be the first to use this new feature. | 6 | |
| 77 | 77 | UI for selecting columns and their order | Table and view pages now include a dialog for selecting and re-ordering visible columns. ( #2661 ) | 6 | |
| 78 | 78 | Other changes | Fixed allowed_resources("view-query", actor) so actor-specific canned queries are returned correctly. Any plugin that defines a resources_sql() method on a Resource subclass needs to update to the new signature, see the resources_sql() method documentation for details. Column actions can now be accessed in mobile view via a new "Column actions" button. Previously they were not available on mobile because table headers are not displayed there. ( #2669 , #2670 ) Row pages now render foreign key values as links to the referenced row. ( #1592 ) The startup() plugin hook now fires after metadata and internal schema tables have been populated, so plugins can reliably inspect that state during startup. ( #2666 ) | 6 | |
| 79 | 79 | 1.0a25 (2026-02-25) | 6 | ||
| 80 | 80 | A new write_wrapper() plugin hook allows plugins to intercept and wrap database write operations. ( #2636 ) Plugins implement the hook as a generator-based context manager: @hookimpl def write_wrapper(datasette, database, request): def wrapper(conn): # Setup code runs before the write yield # Cleanup code runs after the write return wrapper | 6 | ||
| 81 | 81 | A new register_token_handler() plugin hook allows plugins to provide custom token backends for API authentication. ( #2650 ) This includes a backwards incompatible change : the datasette.create_token() internal method is now an async method. Consult the upgrade guide for details on how to update your code. | 6 | ||
| 82 | 82 | The render_cell() plugin hook now receives a pks parameter containing the list of primary key column names for the table being rendered. This avoids plugins needing to make redundant async calls to look up primary keys. ( #2641 ) | 6 | ||
| 83 | 83 | Other changes | Facets defined in metadata now preserve their configured order, instead of being sorted by result count. Request-based facets added via the _facet parameter are still sorted by result count and appear after metadata-defined facets. ( #2647 ) Fixed --reload incorrectly interpreting the serve command as a file argument. Thanks, Daniel Bates . ( #2646 ) | 6 | |
| 84 | 84 | 1.0a24 (2026-01-29) | 6 | ||
| 85 | 85 | Datasette now includes a request.form() method for parsing form submissions, including handling file uploads. ( #2626 ) This supports both application/x-www-form-urlencoded and multipart/form-data content types, and uses a new streaming multipart parser that processes uploads without buffering entire request bodies in memory. # Parse form fields (files are discarded by default) form = await request.form() username = form["username"] # Parse form fields AND file uploads form = await request.form(files=True) uploaded = form["avatar"] content = await uploaded.read() The returned FormData object provides dictionary-style access with support for multiple values per key via form.getlist("key") . Uploaded files are represented as UploadedFile objects with filename , content_type , size properties and async read() and seek() methods. Files smaller than 1MB are held in memory; larger files automatically spill to temporary files on disk. Configurable limits control maximum file size, request size, field counts and more. Several internal views (permissions debug, messages debug, create token) now use request.form() instead of request.post_vars() . request.post_vars() remains available for backwards compatibility but is no longer the recommended API for handling POST data. | 6 | ||
| 86 | 86 | The table JSON API now supports ?_extra=render_cell , which returns the rendered HTML for each cell as produced by the render_cell plugin hook . Only columns whose rendered output differs from the default are included. ( #2619 ) The row JSON API also gains ?_extra=render_cell and ?_extra=foreign_key_tables extras, bringing it closer to parity with the table API. The row JSON API now returns "ok": true in its response, for consistency with the table API. | 6 | ||
| 87 | 87 | The recommended development environment for Datasette now uses uv . You can now set up a development environment and run the test suite with just uv run pytest — no manual virtualenv or pip install step required. ( #2611 ) | 6 | ||
| 88 | 88 | Other changes | Plugins that raise datasette.utils.StartupError() during startup now display a clean error message instead of a full traceback. ( #2624 ) Schema refreshes are now throttled to at most once per second, providing a small performance increase. ( #2629 ) Minor performance improvement to remove_infinites — rows without infinity values now skip the list/dict reconstruction step. ( #2629 ) Filter inputs and the search input no longer trigger unwanted zoom on iOS Safari. Thanks, Daniel Olasubomi Sobowale . ( #2346 ) table_names() and get_all_foreign_keys() now return results in deterministic sorted order. ( #2628 ) Switched linting to ruff and fixed all lint errors. ( #2630 ) | 6 | |
| 89 | 89 | 1.0a23 (2025-12-02) | Fix for bug where a stale database entry in internal.db could cause a 500 error on the homepage. ( #2605 ) Cosmetic improvement to /-/actions page. ( #2599 ) | 6 | |
| 90 | 90 | 1.0a22 (2025-11-13) | datasette serve --default-deny option for running Datasette configured to deny all permissions by default . ( #2592 ) datasette.is_client() method for detecting if code is executing inside a datasette.client request . ( #2594 ) datasette.pm property can now be used to register and unregister plugins in tests . ( #2595 ) | 6 | |
| 91 | 91 | 1.0a21 (2025-11-05) | Fixes an open redirect security issue: Datasette instances would redirect to example.com/foo/bar if you accessed the path //example.com/foo/bar . Thanks to James Jefferies for the fix. ( #2429 ) Fixed datasette publish cloudrun to work with changes to the underlying Cloud Run architecture. ( #2511 ) New datasette --get /path --headers option for inspecting the headers returned by a path. ( #2578 ) New datasette.client.get(..., skip_permission_checks=True) parameter to bypass permission checks when making requests using the internal client. ( #2583 ) | 6 | |
| 92 | 92 | 0.65.2 (2025-11-05) | Fixes an open redirect security issue: Datasette instances would redirect to example.com/foo/bar if you accessed the path //example.com/foo/bar . Thanks to James Jefferies for the fix. ( #2429 ) Upgraded for compatibility with Python 3.14. Fixed datasette publish cloudrun to work with changes to the underlying Cloud Run architecture. ( #2511 ) Minor upgrades to fix warnings, including pkg_resources deprecation. | 6 | |
| 93 | 93 | 1.0a20 (2025-11-03) | This alpha introduces a major breaking change prior to the 1.0 release of Datasette concerning how Datasette's permission system works. | 6 | |
| 94 | 94 | Permission system redesign | Previously the permission system worked using datasette.permission_allowed() checks which consulted all available plugins in turn to determine whether a given actor was allowed to perform a given action on a given resource. This approach could become prohibitively expensive for large lists of items - for example to determine the list of tables that a user could view in a large Datasette instance each plugin implementation of that hook would be fired for every table. The new design uses SQL queries against Datasette's internal catalog tables to derive the list of resources for which an actor has permission for a given action. This turns an N x M problem (N resources, M plugins) into a single SQL query. Plugins can use the new permission_resources_sql(datasette, actor, action) hook to return SQL fragments which will be used as part of that query. Plugins that use any of the following features will need to be updated to work with this and following alphas (and Datasette 1.0 stable itself): Checking permissions with datasette.permission_allowed() - this method has been replaced with datasette.allowed() . Implementing the permission_allowed() plugin hook - this hook has been removed in favor of permission_resources_sql() . Using register_permissions() to register permissions - this hook has been removed in favor of register_actions() . Consult the v1.0a20 upgrade guide for further details on how to upgrade affected plugins. Plugins can now make use of two new internal methods to help resolve permission checks: datasette.allowed_resources() returns a PaginatedResources object with a .re… | 6 | |
| 95 | 95 | Other changes | The internal catalog_views table now tracks SQLite views alongside tables in the introspection database. ( #2495 ) Hitting the / brings up a search interface for navigating to tables that the current user can view. A new /-/tables endpoint supports this functionality. ( #2523 ) Datasette attempts to detect some configuration errors on startup. Datasette now supports Python 3.14 and no longer tests against Python 3.9. | 6 | |
| 96 | 96 | 1.0a19 (2025-04-21) | Tiny cosmetic bug fix for mobile display of table rows. ( #2479 ) | 6 | |
| 97 | 97 | 1.0a18 (2025-04-16) | Fix for incorrect foreign key references in the internal database schema. ( #2466 ) The prepare_connection() hook no longer runs for the internal database. ( #2468 ) Fixed bug where link: HTTP headers used invalid syntax. ( #2470 ) No longer tested against Python 3.8. Now tests against Python 3.13. FTS tables are now hidden by default if they correspond to a content table. ( #2477 ) Fixed bug with foreign key links to rows in databases with filenames containing a special character. Thanks, Jack Stratton . ( #2476 ) | 6 | |
| 98 | 98 | 1.0a17 (2025-02-06) | DATASETTE_SSL_KEYFILE and DATASETTE_SSL_CERTFILE environment variables as alternatives to --ssl-keyfile and --ssl-certfile . Thanks, Alex Garcia. ( #2422 ) SQLITE_EXTENSIONS environment variable has been renamed to DATASETTE_LOAD_EXTENSION . ( #2424 ) datasette serve environment variables are now documented here . The register_magic_parameters(datasette) plugin hook can now register async functions. ( #2441 ) Datasette is now tested against Python 3.13. Breadcrumbs on database and table pages now include a consistent self-link for resetting query string parameters. ( #2454 ) Fixed issue where Datasette could crash on metadata.json with nested values. ( #2455 ) New internal methods datasette.set_actor_cookie() and datasette.delete_actor_cookie() , described here . ( #1690 ) /-/permissions page now shows a list of all permissions registered by plugins. ( #1943 ) If a table has a single unique text column Datasette now detects that as the foreign key label for that table. ( #2458 ) The /-/permissions page now includes options for filtering or exclude permission checks recorded against the current user. ( #2460 ) Fixed a bug where replacing a database with a new one with the same name did not pick up the new database correctly. ( #2465 ) | 6 | |
| 99 | 99 | 0.65.1 (2024-11-28) | Fixed bug with upgraded HTTPX 0.28.0 dependency. ( #2443 ) | 6 | |
| 100 | 100 | 0.65 (2024-10-07) | Upgrade for compatibility with Python 3.13 (by vendoring Pint dependency). ( #2434 ) Dropped support for Python 3.8. | 6 |
Advanced export
JSON shape: default, array, newline-delimited
CREATE VIRTUAL TABLE [sections_fts] USING FTS5 (
[title], [content],
tokenize='porter',
content=[sections]
);