sections
582 rows sorted by title descending
This data as json, CSV (advanced)
page 29 ✖
- changelog 144
- internals 25
- authentication 14
- json_api 11
- introspection 9
- full_text_search 8
- contributing 7
- custom_templates 7
- configuration 5
- facets 5
- installation 5
- spatialite 5
- deploying 4
- sql_queries 4
- getting_started 3
- metadata 3
- plugins 3
- writing_plugins 3
- binary_data 2
- index 2
- pages 2
- performance 2
- plugin_hooks 2
- settings 2
- cli-reference 1
- csv_export 1
- ecosystem 1
- events 1
- publish 1
id | page | ref | title ▲ | content | breadcrumbs | references |
---|---|---|---|---|---|---|
plugins:plugins-installing | plugins | plugins-installing | Installing plugins | If a plugin has been packaged for distribution using setuptools you can use the plugin by installing it alongside Datasette in the same virtual environment or Docker container. You can install plugins using the datasette install command: datasette install datasette-vega You can uninstall plugins with datasette uninstall : datasette uninstall datasette-vega You can upgrade plugins with datasette install --upgrade or datasette install -U : datasette install -U datasette-vega This command can also be used to upgrade Datasette itself to the latest released version: datasette install -U datasette You can install multiple plugins at once by listing them as lines in a requirements.txt file like this: datasette-vega datasette-cluster-map Then pass that file to datasette install -r : datasette install -r requirements.txt The install and uninstall commands are thin wrappers around pip install and pip uninstall , which ensure that they run pip in the same virtual environment as Datasette itself. | ["Plugins"] | [] |
spatialite:installing-spatialite-on-os-x | spatialite | installing-spatialite-on-os-x | Installing SpatiaLite on OS X | The easiest way to install SpatiaLite on OS X is to use Homebrew . brew update brew install spatialite-tools This will install the spatialite command-line tool and the mod_spatialite dynamic library. You can now run Datasette like so: datasette --load-extension=spatialite | ["SpatiaLite", "Installation"] | [{"href": "https://brew.sh/", "label": "Homebrew"}] |
spatialite:installing-spatialite-on-linux | spatialite | installing-spatialite-on-linux | Installing SpatiaLite on Linux | SpatiaLite is packaged for most Linux distributions. apt install spatialite-bin libsqlite3-mod-spatialite Depending on your distribution, you should be able to run Datasette something like this: datasette --load-extension=/usr/lib/x86_64-linux-gnu/mod_spatialite.so If you are unsure of the location of the module, try running locate mod_spatialite and see what comes back. | ["SpatiaLite", "Installation"] | [] |
installation:id1 | installation | id1 | Installation | If you just want to try Datasette out you don't need to install anything: see Try Datasette without installing anything using Glitch There are two main options for installing Datasette. You can install it directly on to your machine, or you can install it using Docker. If you want to start making contributions to the Datasette project by installing a copy that lets you directly modify the code, take a look at our guide to Setting up a development environment . Basic installation Datasette Desktop for Mac Using Homebrew Using pip Advanced installation options Using pipx Installing plugins using pipx Upgrading packages using pipx Using Docker Loading SpatiaLite Installing plugins A note about extensions | [] | [] |
spatialite:spatialite-installation | spatialite | spatialite-installation | Installation | ["SpatiaLite"] | [] | |
json_api:tableinsertview | json_api | tableinsertview | Inserting rows | This requires the insert-row permission. A single row can be inserted using the "row" key: POST /<database>/<table>/-/insert Content-Type: application/json Authorization: Bearer dstok_<rest-of-token> { "row": { "column1": "value1", "column2": "value2" } } If successful, this will return a 201 status code and the newly inserted row, for example: { "rows": [ { "id": 1, "column1": "value1", "column2": "value2" } ] } To insert multiple rows at a time, use the same API method but send a list of dictionaries as the "rows" key: POST /<database>/<table>/-/insert Content-Type: application/json Authorization: Bearer dstok_<rest-of-token> { "rows": [ { "column1": "value1", "column2": "value2" }, { "column1": "value3", "column2": "value4" } ] } If successful, this will return a 201 status code and a {"ok": true} response body. The maximum number rows that can be submitted at once defaults to 100, but this can be changed using the max_insert_rows setting. To return the newly inserted rows, add the "return": true key to the request body: { "rows": [ { "column1": "value1", "column2": "value2" }, { "column1": "value3", "column2": "value4" } ], "return": true } This will return the same "rows" key as the single row example above. There is a small performance penalty for using this option. If any of your rows have a primary key that is already in use, you will get an error and none of the rows will be inserted: { "ok": false, "errors": [ "UNIQUE constraint failed: new_table… | ["JSON API", "The JSON write API"] | [] |
authentication:authentication-ds-actor-expiry | authentication | authentication-ds-actor-expiry | Including an expiry time | ds_actor cookies can optionally include a signed expiry timestamp, after which the cookies will no longer be valid. Authentication plugins may chose to use this mechanism to limit the lifetime of the cookie. For example, if a plugin implements single-sign-on against another source it may decide to set short-lived cookies so that if the user is removed from the SSO system their existing Datasette cookies will stop working shortly afterwards. To include an expiry, add a "e" key to the cookie value containing a base62-encoded integer representing the timestamp when the cookie should expire. For example, here's how to set a cookie that expires after 24 hours: import time from datasette.utils import baseconv expires_at = int(time.time()) + (24 * 60 * 60) response = Response.redirect("/") response.set_cookie( "ds_actor", datasette.sign( { "a": {"id": "cleopaws"}, "e": baseconv.base62.encode(expires_at), }, "actor", ), ) The resulting cookie will encode data that looks something like this: { "a": { "id": "cleopaws" }, "e": "1jjSji" } | ["Authentication and permissions", "The ds_actor cookie"] | [] |
changelog:improved-support-for-spatialite | changelog | improved-support-for-spatialite | Improved support for SpatiaLite | The SpatiaLite module for SQLite adds robust geospatial features to the database. Getting SpatiaLite working can be tricky, especially if you want to use the most recent alpha version (with support for K-nearest neighbor). Datasette now includes extensive documentation on SpatiaLite , and thanks to Ravi Kotecha our GitHub repo includes a Dockerfile that can build the latest SpatiaLite and configure it for use with Datasette. The datasette publish and datasette package commands now accept a new --spatialite argument which causes them to install and configure SpatiaLite as part of the container they deploy. | ["Changelog", "0.23 (2018-06-18)"] | [{"href": "https://www.gaia-gis.it/fossil/libspatialite/index", "label": "SpatiaLite module"}, {"href": "https://github.com/r4vi", "label": "Ravi Kotecha"}, {"href": "https://github.com/simonw/datasette/blob/master/Dockerfile", "label": "Dockerfile"}] |
spatialite:importing-shapefiles-into-spatialite | spatialite | importing-shapefiles-into-spatialite | Importing shapefiles into SpatiaLite | The shapefile format is a common format for distributing geospatial data. You can use the spatialite command-line tool to create a new database table from a shapefile. Try it now with the North America shapefile available from the University of North Carolina Global River Database project. Download the file and unzip it (this will create files called narivs.dbf , narivs.prj , narivs.shp and narivs.shx in the current directory), then run the following: spatialite rivers-database.db SpatiaLite version ..: 4.3.0a Supported Extensions: ... spatialite> .loadshp narivs rivers CP1252 23032 ======== Loading shapefile at 'narivs' into SQLite table 'rivers' ... Inserted 467973 rows into 'rivers' from SHAPEFILE This will load the data from the narivs shapefile into a new database table called rivers . Exit out of spatialite (using Ctrl+D ) and run Datasette against your new database like this: datasette rivers-database.db \ --load-extension=/usr/local/lib/mod_spatialite.dylib If you browse to http://localhost:8001/rivers-database/rivers you will see the new table... but the Geometry column will contain unreadable binary data (SpatiaLite uses a custom format based on WKB ). The easiest way to turn this into semi-readable data is to use the SpatiaLite AsGeoJSON function. Try the following using the SQL query interface at http://localhost:8001/rivers-database : select *, AsGeoJSON(Geometry) from rivers limit 10; This will give you back an additional column of GeoJSON. You can copy and paste GeoJSON from this column into the debugging tool at geojson.io to visualize it on a map. To see a more interesting example, try ordering the records with the longest geometry first. Since there are 467,000 rows in the table you will first need to increase the SQL time limit imposed by Datasette: datasette rivers-database.db \ --load-e… | ["SpatiaLite"] | [{"href": "https://en.wikipedia.org/wiki/Shapefile", "label": "shapefile format"}, {"href": "http://gaia.geosci.unc.edu/rivers/", "label": "Global River Database"}, {"href": "https://www.gaia-gis.it/gaia-sins/BLOB-Geometry.html", "label": "a custom format based on WKB"}, {"href": "https://geojson.io/", "label": "geojson.io"}] |
spatialite:importing-geojson-polygons-using-shapely | spatialite | importing-geojson-polygons-using-shapely | Importing GeoJSON polygons using Shapely | Another common form of polygon data is the GeoJSON format. This can be imported into SpatiaLite directly, or by using the Shapely Python library. Who's On First is an excellent source of openly licensed GeoJSON polygons. Let's import the geographical polygon for Wales. First, we can use the Who's On First Spelunker tool to find the record for Wales: spelunker.whosonfirst.org/id/404227475 That page includes a link to the GeoJSON record, which can be accessed here: data.whosonfirst.org/404/227/475/404227475.geojson Here's Python code to create a SQLite database, enable SpatiaLite, create a places table and then add a record for Wales: import sqlite3 conn = sqlite3.connect("places.db") # Enable SpatialLite extension conn.enable_load_extension(True) conn.load_extension("/usr/local/lib/mod_spatialite.dylib") # Create the masic countries table conn.execute("select InitSpatialMetadata(1)") conn.execute( "create table places (id integer primary key, name text);" ) # Add a MULTIPOLYGON Geometry column conn.execute( "SELECT AddGeometryColumn('places', 'geom', 4326, 'MULTIPOLYGON', 2);" ) # Add a spatial index against the new column conn.execute("SELECT CreateSpatialIndex('places', 'geom');") # Now populate the table from shapely.geometry.multipolygon import MultiPolygon from shapely.geometry import shape import requests geojson = requests.get( "https://data.whosonfirst.org/404/227/475/404227475.geojson" ).json() # Convert to "Well Known Text" format wkt = shape(geojson["geometry"]).wkt # Insert and commit the record conn.execute( "INSERT INTO places (id, name, geom) VALUES(null, ?, GeomFromText(?, 4326))", ("Wales", wkt), ) conn.commit() | ["SpatiaLite"] | [{"href": "https://pypi.org/project/Shapely/", "label": "Shapely"}, {"href": "https://whosonfirst.org/", "label": "Who's On First"}, {"href": "https://spelunker.whosonfirst.org/id/404227475/", "label": "spelunker.whosonfirst.org/id/404227475"}, {"href": "https://data.whosonfirst.org/404/227/475/404227475.geojson", "label": "data.whosonfirst.org/404/227/475/404227475.geojson"}] |
internals:internals-shortcuts | internals | internals-shortcuts | Import shortcuts | The following commonly used symbols can be imported directly from the datasette module: from datasette import Response from datasette import Forbidden from datasette import NotFound from datasette import hookimpl from datasette import actor_matches_allow | ["Internals for plugins"] | [] |
performance:performance-immutable-mode | performance | performance-immutable-mode | Immutable mode | If you can be certain that a SQLite database file will not be changed by another process you can tell Datasette to open that file in immutable mode . Doing so will disable all locking and change detection, which can result in improved query performance. This also enables further optimizations relating to HTTP caching, described below. To open a file in immutable mode pass it to the datasette command using the -i option: datasette -i data.db When you open a file in immutable mode like this Datasette will also calculate and cache the row counts for each table in that database when it first starts up, further improving performance. | ["Performance and caching"] | [] |
authentication:authentication-permissions-explained | authentication | authentication-permissions-explained | How permissions are resolved | The datasette.permission_allowed(actor, action, resource=None, default=...) method is called to check if an actor is allowed to perform a specific action. This method asks every plugin that implements the permission_allowed(datasette, actor, action, resource) hook if the actor is allowed to perform the action. Each plugin can return True to indicate that the actor is allowed to perform the action, False if they are not allowed and None if the plugin has no opinion on the matter. False acts as a veto - if any plugin returns False then the permission check is denied. Otherwise, if any plugin returns True then the permission check is allowed. The resource argument can be used to specify a specific resource that the action is being performed against. Some permissions, such as view-instance , do not involve a resource. Others such as view-database have a resource that is a string naming the database. Permissions that take both a database name and the name of a table, view or canned query within that database use a resource that is a tuple of two strings, (database_name, resource_name) . Plugins that implement the permission_allowed() hook can decide if they are going to consider the provided resource or not. | ["Authentication and permissions", "Permissions"] | [] |
metadata:metadata-hiding-tables | metadata | metadata-hiding-tables | Hiding tables | You can hide tables from the database listing view (in the same way that FTS and SpatiaLite tables are automatically hidden) using "hidden": true : [[[cog metadata_example(cog, { "databases": { "database1": { "tables": { "example_table": { "hidden": True } } } } }) ]]] [[[end]]] | ["Metadata"] | [] |
pages:databaseview-hidden | pages | databaseview-hidden | 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 Hiding tables . *_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. | ["Pages and API endpoints", "Database"] | [] |
performance:http-caching | performance | http-caching | HTTP caching | If your database is immutable and guaranteed not to change, you can gain major performance improvements from Datasette by enabling HTTP caching. This can work at two different levels. First, it can tell browsers to cache the results of queries and serve future requests from the browser cache. More significantly, it allows you to run Datasette behind a caching proxy such as Varnish or use a cache provided by a hosted service such as Fastly or Cloudflare . This can provide incredible speed-ups since a query only needs to be executed by Datasette the first time it is accessed - all subsequent hits can then be served by the cache. Using a caching proxy in this way could enable a Datasette-backed visualization to serve thousands of hits a second while running Datasette itself on extremely inexpensive hosting. Datasette's integration with HTTP caches can be enabled using a combination of configuration options and query string arguments. The default_cache_ttl setting sets the default HTTP cache TTL for all Datasette pages. This is 5 seconds unless you change it - you can set it to 0 if you wish to disable HTTP caching entirely. You can also change the cache timeout on a per-request basis using the ?_ttl=10 query string parameter. This can be useful when you are working with the Datasette JSON API - you may decide that a specific query can be cached for a longer time, or maybe you need to set ?_ttl=0 for some requests for example if you are running a SQL order by random() query. | ["Performance and caching"] | [{"href": "https://varnish-cache.org/", "label": "Varnish"}, {"href": "https://www.fastly.com/", "label": "Fastly"}, {"href": "https://www.cloudflare.com/", "label": "Cloudflare"}] |
getting_started:getting-started | getting_started | getting-started | Getting started | [] | [] | |
contributing:general-guidelines | contributing | general-guidelines | General guidelines | main should always be releasable . Incomplete features should live in branches. This ensures that any small bug fixes can be quickly released. The ideal commit should bundle together the implementation, unit tests and associated documentation updates. The commit message should link to an associated issue. New plugin hooks should only be shipped if accompanied by a separate release of a non-demo plugin that uses them. | ["Contributing"] | [] |
full_text_search:id1 | full_text_search | id1 | 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. | [] | [{"href": "https://www.sqlite.org/fts3.html", "label": "a powerful mechanism for enabling full-text search"}, {"href": "https://register-of-members-interests.datasettes.com/regmem/items?_search=hamper&_sort_desc=date", "label": "an example search"}] |
changelog:foreign-key-expansions | changelog | foreign-key-expansions | Foreign key expansions | When Datasette detects a foreign key reference it attempts to resolve a label for that reference (automatically or using the Specifying the label column for a table metadata option) so it can display a link to the associated row. This expansion is now also available for JSON and CSV representations of the table, using the new _labels=on query string option. See Expanding foreign key references for more details. | ["Changelog", "0.23 (2018-06-18)"] | [] |
getting_started:getting-started-tutorial | getting_started | getting-started-tutorial | Follow a tutorial | Datasette has several tutorials to help you get started with the tool. Try one of the following: Exploring a database with Datasette shows how to use the Datasette web interface to explore a new database. Learn SQL with Datasette introduces SQL, and shows how to use that query language to ask questions of your data. Cleaning data with sqlite-utils and Datasette guides you through using sqlite-utils to turn a CSV file into a database that you can explore using Datasette. | ["Getting started"] | [{"href": "https://datasette.io/tutorials", "label": "tutorials"}, {"href": "https://datasette.io/tutorials/explore", "label": "Exploring a database with Datasette"}, {"href": "https://datasette.io/tutorials/learn-sql", "label": "Learn SQL with Datasette"}, {"href": "https://datasette.io/tutorials/clean-data", "label": "Cleaning data with sqlite-utils and Datasette"}, {"href": "https://sqlite-utils.datasette.io/", "label": "sqlite-utils"}] |
changelog:flash-messages | changelog | flash-messages | Flash messages | Writable canned queries needed a mechanism to let the user know that the query has been successfully executed. The new flash messaging system ( #790 ) allows messages to persist in signed cookies which are then displayed to the user on the next page that they visit. Plugins can use this mechanism to display their own messages, see .add_message(request, message, type=datasette.INFO) for details. You can try out the new messages using the /-/messages debug tool, for example at https://latest.datasette.io/-/messages | ["Changelog", "0.44 (2020-06-11)"] | [{"href": "https://github.com/simonw/datasette/issues/790", "label": "#790"}, {"href": "https://latest.datasette.io/-/messages", "label": "https://latest.datasette.io/-/messages"}] |
changelog:features | changelog | features | Features | Now tested against Python 3.11. Docker containers used by datasette publish and datasette package both now use that version of Python. ( #1853 ) --load-extension option now supports entrypoints. Thanks, Alex Garcia. ( #1789 ) Facet size can now be set per-table with the new facet_size table metadata option. ( #1804 ) The truncate_cells_html setting now also affects long URLs in columns. ( #1805 ) The non-JavaScript SQL editor textarea now increases height to fit the SQL query. ( #1786 ) Facets are now displayed with better line-breaks in long values. Thanks, Daniel Rech. ( #1794 ) The settings.json file used in Configuration directory mode is now validated on startup. ( #1816 ) SQL queries can now include leading SQL comments, using /* ... */ or -- ... syntax. Thanks, Charles Nepote. ( #1860 ) SQL query is now re-displayed when terminated with a time limit error. ( #1819 ) The inspect data mechanism is now used to speed up server startup - thanks, Forest Gregg. ( #1834 ) In Configuration directory mode databases with filenames ending in .sqlite or .sqlite3 are now automatically added to the Datasette instance. ( #1646 ) Breadcrumb navigation display now respects the current user's permissions. ( #1831 ) | ["Changelog", "0.63 (2022-10-27)"] | [{"href": "https://github.com/simonw/datasette/issues/1853", "label": "#1853"}, {"href": "https://github.com/simonw/datasette/pull/1789", "label": "#1789"}, {"href": "https://github.com/simonw/datasette/issues/1804", "label": "#1804"}, {"href": "https://github.com/simonw/datasette/issues/1805", "label": "#1805"}, {"href": "https://github.com/simonw/datasette/issues/1786", "label": "#1786"}, {"href": "https://github.com/simonw/datasette/pull/1794", "label": "#1794"}, {"href": "https://github.com/simonw/datasette/issues/1816", "label": "#1816"}, {"href": "https://github.com/simonw/datasette/issues/1860", "label": "#1860"}, {"href": "https://github.com/simonw/datasette/issues/1819", "label": "#1819"}, {"href": "https://github.com/simonw/datasette/issues/1834", "label": "#1834"}, {"href": "https://github.com/simonw/datasette/issues/1646", "label": "#1646"}, {"href": "https://github.com/simonw/datasette/issues/1831", "label": "#1831"}] |
changelog:id14 | changelog | id14 | Features | Datasette is now compatible with Pyodide . This is the enabling technology behind Datasette Lite . ( #1733 ) Database file downloads now implement conditional GET using ETags. ( #1739 ) HTML for facet results and suggested results has been extracted out into new templates _facet_results.html and _suggested_facets.html . Thanks, M. Nasimul Haque. ( #1759 ) Datasette now runs some SQL queries in parallel. This has limited impact on performance, see this research issue for details. New --nolock option for ignoring file locks when opening read-only databases. ( #1744 ) Spaces in the database names in URLs are now encoded as + rather than ~20 . ( #1701 ) <Binary: 2427344 bytes> is now displayed as <Binary: 2,427,344 bytes> and is accompanied by tooltip showing "2.3MB". ( #1712 ) The base Docker image used by datasette publish cloudrun , datasette package and the official Datasette image has been upgraded to 3.10.6-slim-bullseye . ( #1768 ) Canned writable queries against immutable databases now show a warning message. ( #1728 ) datasette publish cloudrun has a new --timeout option which can be used to increase the time limit applied by the Google Cloud build environment. Thanks, Tim Sherratt. ( #1717 ) datasette publish cloudrun has new --min-instances and --max-instances options. ( #1779 ) | ["Changelog", "0.62 (2022-08-14)"] | [{"href": "https://pyodide.org/", "label": "Pyodide"}, {"href": "https://lite.datasette.io/", "label": "Datasette Lite"}, {"href": "https://github.com/simonw/datasette/issues/1733", "label": "#1733"}, {"href": "https://github.com/simonw/datasette/issues/1739", "label": "#1739"}, {"href": "https://github.com/simonw/datasette/pull/1759", "label": "#1759"}, {"href": "https://github.com/simonw/datasette/issues/1727", "label": "this research issue"}, {"href": "https://github.com/simonw/datasette/issues/1744", "label": "#1744"}, {"href": "https://github.com/simonw/datasette/issues/1701", "label": "#1701"}, {"href": "https://github.com/simonw/datasette/issues/1712", "label": "#1712"}, {"href": "https://hub.docker.com/datasetteproject/datasette", "label": "official Datasette image"}, {"href": "https://github.com/simonw/datasette/issues/1768", "label": "#1768"}, {"href": "https://github.com/simonw/datasette/issues/1728", "label": "#1728"}, {"href": "https://github.com/simonw/datasette/pull/1717", "label": "#1717"}, {"href": "https://github.com/simonw/datasette/issues/1779", "label": "#1779"}] |
facets:facets-in-query-strings | facets | facets-in-query-strings | 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… | ["Facets"] | [] |
facets:facets-metadata | facets | facets-metadata | Facets in metadata | You can turn facets on by default for specific tables by adding them to a "facets" key in a Datasette Metadata file. 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 metadata_example metadata_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. You can specify array or date facets in metadata using JSON objects with a single key of array or date and a value specifying the column, like this: [[[cog metadata_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 metadata_example(cog, { "databases": { "sf-trees": { "tables": { "Street_Tree_List": { "facets": ["qLegalStatus"], "facet_size": 10 } } } } }) ]]] [[[end]]] | ["Facets"] | [] |
facets:id1 | facets | id1 | 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. | [] | [{"href": "https://congress-legislators.datasettes.com/legislators/legislator_terms?_facet=type&_facet=party&_facet=state&_facet_size=10", "label": "an example"}] |
changelog:v0-28-faceting | changelog | v0-28-faceting | Faceting improvements, and faceting plugins | Datasette Facets provide an intuitive way to quickly summarize and interact with data. Previously the only supported faceting technique was column faceting, but 0.28 introduces two powerful new capabilities: facet-by-JSON-array and the ability to define further facet types using plugins. Facet by array ( #359 ) is only available if your SQLite installation provides the json1 extension. Datasette will automatically detect columns that contain JSON arrays of values and offer a faceting interface against those columns - useful for modelling things like tags without needing to break them out into a new table. See Facet by JSON array for more. The new register_facet_classes() plugin hook ( #445 ) can be used to register additional custom facet classes. Each facet class should provide two methods: suggest() which suggests facet selections that might be appropriate for a provided SQL query, and facet_results() which executes a facet operation and returns results. Datasette's own faceting implementations have been refactored to use the same API as these plugins. | ["Changelog", "0.28 (2019-05-19)"] | [{"href": "https://github.com/simonw/datasette/issues/359", "label": "#359"}, {"href": "https://github.com/simonw/datasette/pull/445", "label": "#445"}] |
changelog:faceting | changelog | faceting | Faceting | The number of unique values in a facet is now always displayed. Previously it was only displayed if the user specified ?_facet_size=max . ( #1556 ) Facets of type date or array can now be configured in metadata.json , see Facets in metadata . Thanks, David Larlet. ( #1552 ) New ?_nosuggest=1 parameter for table views, which disables facet suggestion. ( #1557 ) Fixed bug where ?_facet_array=tags&_facet=tags would only display one of the two selected facets. ( #625 ) | ["Changelog", "0.60 (2022-01-13)"] | [{"href": "https://github.com/simonw/datasette/issues/1556", "label": "#1556"}, {"href": "https://github.com/simonw/datasette/issues/1552", "label": "#1552"}, {"href": "https://github.com/simonw/datasette/issues/1557", "label": "#1557"}, {"href": "https://github.com/simonw/datasette/issues/625", "label": "#625"}] |
changelog:facet-by-date | changelog | facet-by-date | Facet by date | If a column contains datetime values, Datasette can now facet that column by date. ( #481 ) | ["Changelog", "0.29 (2019-07-07)"] | [{"href": "https://github.com/simonw/datasette/issues/481", "label": "#481"}] |
facets:id3 | facets | id3 | 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 | ["Facets"] | [{"href": "https://latest.datasette.io/fixtures/facetable?_facet_date=created", "label": "latest.datasette.io/fixtures/facetable?_facet_date=created"}] |
facets:id2 | facets | id2 | 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 | ["Facets"] | [{"href": "https://latest.datasette.io/fixtures/facetable?_facet_array=tags", "label": "latest.datasette.io/fixtures/facetable?_facet_array=tags"}] |
full_text_search:full-text-search-fts-versions | full_text_search | full-text-search-fts-versions | 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. | ["Full-text search"] | [] |
json_api:expand-foreign-keys | json_api | expand-foreign-keys | Expanding foreign key references | Datasette can detect foreign key relationships and resolve those references into labels. The HTML interface does this by default for every detected foreign key column - you can turn that off using ?_labels=off . You can request foreign keys be expanded in JSON using the _labels=on or _label=COLUMN special query string parameters. Here's what an expanded row looks like: [ { "rowid": 1, "TreeID": 141565, "qLegalStatus": { "value": 1, "label": "Permitted Site" }, "qSpecies": { "value": 1, "label": "Myoporum laetum :: Myoporum" }, "qAddress": "501X Baker St", "SiteOrder": 1 } ] The column in the foreign key table that is used for the label can be specified in metadata.json - see Specifying the label column for a table . | ["JSON API"] | [] |
events:id1 | events | id1 | 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. 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 -- Restricted database permissions for this token. … | [] | [] |
plugin_hooks:plugin-event-tracking | plugin_hooks | plugin-event-tracking | Event tracking | Datasette includes an internal mechanism for tracking notable events. This can be used for analytics, but can also be used by plugins that want to listen out for when key events occur (such as a table being created) and take action in response. Plugins can register to receive events using the track_event plugin hook. They can also define their own events for other plugins to receive using the register_events() plugin hook , combined with calls to the datasette.track_event() internal method . | ["Plugin hooks"] | [] |
full_text_search:full-text-search-enabling | full_text_search | full-text-search-enabling | 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 | ["Full-text search"] | [{"href": "https://www.sqlite.org/fts3.html#_external_content_fts4_tables_", "label": "external content"}] |
json_api:json-api-cors | json_api | json-api-cors | Enabling CORS | If you start Datasette with the --cors option, each JSON endpoint will be served with the following additional HTTP headers: [[[cog from datasette.utils import add_cors_headers import textwrap headers = {} add_cors_headers(headers) output = "\n".join("{}: {}".format(k, v) for k, v in headers.items()) cog.out("\n::\n\n") cog.out(textwrap.indent(output, ' ')) cog.out("\n\n") ]]] Access-Control-Allow-Origin: * Access-Control-Allow-Headers: Authorization, Content-Type Access-Control-Expose-Headers: Link Access-Control-Allow-Methods: GET, POST, HEAD, OPTIONS Access-Control-Max-Age: 3600 [[[end]]] This allows JavaScript running on any domain to make cross-origin requests to interact with the Datasette API. If you start Datasette without the --cors option only JavaScript running on the same domain as Datasette will be able to access the API. Here's how to serve data.db with CORS enabled: datasette data.db --cors | ["JSON API"] | [] |
contributing:contributing-documentation | contributing | contributing-documentation | Editing and building the documentation | Datasette's documentation lives in the docs/ directory and is deployed automatically using Read The Docs . The documentation is written using reStructuredText. You may find this article on The subset of reStructuredText worth committing to memory useful. You can build it locally by installing sphinx and sphinx_rtd_theme in your Datasette development environment and then running make html directly in the docs/ directory: # You may first need to activate your virtual environment: source venv/bin/activate # Install the dependencies needed to build the docs pip install -e .[docs] # Now build the docs cd docs/ make html This will create the HTML version of the documentation in docs/_build/html . You can open it in your browser like so: open _build/html/index.html Any time you make changes to a .rst file you can re-run make html to update the built documents, then refresh them in your browser. For added productivity, you can use use sphinx-autobuild to run Sphinx in auto-build mode. This will run a local webserver serving the docs that automatically rebuilds them and refreshes the page any time you hit save in your editor. sphinx-autobuild will have been installed when you ran pip install -e .[docs] . In your docs/ directory you can start the server by running the following: make livehtml Now browse to http://localhost:8000/ to view the documentation. Any edits you make should be instantly reflected in your browser. | ["Contributing"] | [{"href": "https://readthedocs.org/", "label": "Read The Docs"}, {"href": "https://simonwillison.net/2018/Aug/25/restructuredtext/", "label": "The subset of reStructuredText worth committing to memory"}, {"href": "https://pypi.org/project/sphinx-autobuild/", "label": "sphinx-autobuild"}] |
changelog:v0-29-medium-changes | changelog | v0-29-medium-changes | Easier custom templates for table rows | If you want to customize the display of individual table rows, you can do so using a _table.html template include that looks something like this: {% for row in display_rows %} <div> <h2>{{ row["title"] }}</h2> <p>{{ row["description"] }}<lp> <p>Category: {{ row.display("category_id") }}</p> </div> {% endfor %} This is a backwards incompatible change . If you previously had a custom template called _rows_and_columns.html you need to rename it to _table.html . See Custom templates for full details. | ["Changelog", "0.29 (2019-07-07)"] | [] |
json_api:tabledropview | json_api | tabledropview | Dropping tables | To drop a table, make a POST to /<database>/<table>/-/drop . This requires the drop-table permission. POST /<database>/<table>/-/drop Content-Type: application/json Authorization: Bearer dstok_<rest-of-token> Without a POST body this will return a status 200 with a note about how many rows will be deleted: { "ok": true, "database": "<database>", "table": "<table>", "row_count": 5, "message": "Pass \"confirm\": true to confirm" } If you pass the following POST body: { "confirm": true } Then the table will be dropped and a status 200 response of {"ok": true} will be returned. Any errors will return {"errors": ["... descriptive message ..."], "ok": false} , and a 400 status code for a bad input or a 403 status code for an authentication or permission error. | ["JSON API", "The JSON write API"] | [] |
ecosystem:dogsheep | ecosystem | dogsheep | Dogsheep | Dogsheep is a collection of tools for personal analytics using SQLite and Datasette. The project provides tools like github-to-sqlite and twitter-to-sqlite that can import data from different sources in order to create a personal data warehouse. Personal Data Warehouses: Reclaiming Your Data is a talk that explains Dogsheep and demonstrates it in action. | ["The Datasette Ecosystem"] | [{"href": "https://dogsheep.github.io/", "label": "Dogsheep"}, {"href": "https://datasette.io/tools/github-to-sqlite", "label": "github-to-sqlite"}, {"href": "https://datasette.io/tools/twitter-to-sqlite", "label": "twitter-to-sqlite"}, {"href": "https://simonwillison.net/2020/Nov/14/personal-data-warehouses/", "label": "Personal Data Warehouses: Reclaiming Your Data"}] |
changelog:documentation | changelog | documentation | 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. | ["Changelog", "1.0a8 (2024-02-07)"] | [{"href": "https://github.com/simonw/datasette/issues/1830", "label": "#1830"}, {"href": "https://github.com/simonw/datasette/issues/2234", "label": "#2234"}] |
changelog:id12 | changelog | id12 | Documentation | New tutorial: Cleaning data with sqlite-utils and Datasette . Screenshots in the documentation are now maintained using shot-scraper , as described in Automating screenshots for the Datasette documentation using shot-scraper . ( #1844 ) More detailed command descriptions on the CLI reference page. ( #1787 ) New documentation on Running Datasette using OpenRC - thanks, Adam Simpson. ( #1825 ) | ["Changelog", "0.63 (2022-10-27)"] | [{"href": "https://datasette.io/tutorials/clean-data", "label": "Cleaning data with sqlite-utils and Datasette"}, {"href": "https://shot-scraper.datasette.io/", "label": "shot-scraper"}, {"href": "https://simonwillison.net/2022/Oct/14/automating-screenshots/", "label": "Automating screenshots for the Datasette documentation using shot-scraper"}, {"href": "https://github.com/simonw/datasette/issues/1844", "label": "#1844"}, {"href": "https://github.com/simonw/datasette/issues/1787", "label": "#1787"}, {"href": "https://github.com/simonw/datasette/pull/1825", "label": "#1825"}] |
changelog:id16 | changelog | id16 | Documentation | Examples in the documentation now include a copy-to-clipboard button. ( #1748 ) Documentation now uses the Furo Sphinx theme. ( #1746 ) Code examples in the documentation are now all formatted using Black. ( #1718 ) Request.fake() method is now documented, see Request object . New documentation for plugin authors: Registering a plugin for the duration of a test . ( #903 ) | ["Changelog", "0.62 (2022-08-14)"] | [{"href": "https://github.com/simonw/datasette/issues/1748", "label": "#1748"}, {"href": "https://github.com/pradyunsg/furo", "label": "Furo"}, {"href": "https://github.com/simonw/datasette/issues/1746", "label": "#1746"}, {"href": "https://github.com/simonw/datasette/issues/1718", "label": "#1718"}, {"href": "https://github.com/simonw/datasette/issues/903", "label": "#903"}] |
json_api:json-api-discover-alternate | json_api | json-api-discover-alternate | Discovering the JSON for a page | Most of the HTML pages served by Datasette provide a mechanism for discovering their JSON equivalents using the HTML link mechanism. You can find this near the top of the source code of those pages, looking like this: <link rel="alternate" type="application/json+datasette" href="https://latest.datasette.io/fixtures/sortable.json"> The JSON URL is also made available in a Link HTTP header for the page: Link: https://latest.datasette.io/fixtures/sortable.json; rel="alternate"; type="application/json+datasette" | ["JSON API"] | [] |
json_api:json-api-shapes | json_api | json-api-shapes | Different shapes | The _shape parameter can be used to access alternative formats for the rows key which may be more convenient for your application. There are three options: ?_shape=objects - "rows" is a list of JSON key/value objects - the default ?_shape=arrays - "rows" is a list of lists, where the order of values in each list matches the order of the columns ?_shape=array - a JSON array of objects - effectively just the "rows" key from the default representation ?_shape=array&_nl=on - a newline-separated list of JSON objects ?_shape=arrayfirst - a flat JSON array containing just the first value from each row ?_shape=object - a JSON object keyed using the primary keys of the rows _shape=arrays looks like this: { "ok": true, "next": null, "rows": [ [3, "Detroit"], [2, "Los Angeles"], [4, "Memnonia"], [1, "San Francisco"] ] } _shape=array looks like this: [ { "id": 3, "name": "Detroit" }, { "id": 2, "name": "Los Angeles" }, { "id": 4, "name": "Memnonia" }, { "id": 1, "name": "San Francisco" } ] _shape=array&_nl=on looks like this: {"id": 1, "value": "Myoporum laetum :: Myoporum"} {"id": 2, "value": "Metrosideros excelsa :: New Zealand Xmas Tree"} {"id": 3, "value": "Pinus radiata :: Monterey Pine"} _shape=arrayfirst looks like this: [1, 2, 3] _shape=object looks like this: { "1": { "id": 1, "value": "Myoporum laetum :: Myoporum" }, "2": { "id": 2, "value": "Metrosideros excelsa :… | ["JSON API"] | [] |
writing_plugins:writing-plugins-designing-urls | writing_plugins | writing-plugins-designing-urls | Designing URLs for your plugin | You can register new URL routes within Datasette using the register_routes(datasette) plugin hook. Datasette's default URLs include these: /dbname - database page /dbname/tablename - table page /dbname/tablename/pk - row page See Pages and API endpoints and Introspection for more default URL routes. To avoid accidentally conflicting with a database file that may be loaded into Datasette, plugins should register URLs using a /-/ prefix. For example, if your plugin adds a new interface for uploading Excel files you might register a URL route like this one: /-/upload-excel Try to avoid registering URLs that clash with other plugins that your users might have installed. There is no central repository of reserved URL paths (yet) but you can review existing plugins by browsing the plugins directory . If your plugin includes functionality that relates to a specific database you could also register a URL route like this: /dbname/-/upload-excel Or for a specific table like this: /dbname/tablename/-/modify-table-schema Note that a row could have a primary key of - and this URL scheme will still work, because Datasette row pages do not ever have a trailing slash followed by additional path components. | ["Writing plugins"] | [{"href": "https://datasette.io/plugins", "label": "plugins directory"}] |
deploying:deploying-fundamentals | deploying | deploying-fundamentals | 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. | ["Deploying Datasette"] | [] |
deploying:deploying-buildpacks | deploying | deploying-buildpacks | 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. | ["Deploying Datasette"] | [{"href": "https://www.heroku.com/", "label": "Heroku"}, {"href": "https://www.digitalocean.com/docs/app-platform/", "label": "DigitalOcean App Platform"}, {"href": "https://scalingo.com/", "label": "Scalingo"}, {"href": "https://buildpacks.io/", "label": "Buildpacks standard"}, {"href": "https://github.com/simonw/buildpack-datasette-demo", "label": "simonw/buildpack-datasette-demo"}] |
plugins:deploying-plugins-using-datasette-publish | plugins | deploying-plugins-using-datasette-publish | Deploying plugins using datasette publish | The datasette publish and datasette package commands both take an optional --install argument. You can use this one or more times to tell Datasette to pip install specific plugins as part of the process: datasette publish cloudrun mydb.db --install=datasette-vega You can use the name of a package on PyPI or any of the other valid arguments to pip install such as a URL to a .zip file: datasette publish cloudrun mydb.db \ --install=https://url-to-my-package.zip | ["Plugins", "Installing plugins"] | [] |
deploying:deploying | deploying | deploying | 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. | [] | [] |
json_api:rowdeleteview | json_api | rowdeleteview | Deleting a row | To delete a row, make a POST to /<database>/<table>/<row-pks>/-/delete . This requires the delete-row permission. POST /<database>/<table>/<row-pks>/-/delete Content-Type: application/json Authorization: Bearer dstok_<rest-of-token> <row-pks> here is the tilde-encoded primary key value of the row to delete - or a comma-separated list of primary key values if the table has a composite primary key. If successful, this will return a 200 status code and a {"ok": true} response body. Any errors will return {"errors": ["... descriptive message ..."], "ok": false} , and a 400 status code for a bad input or a 403 status code for an authentication or permission error. | ["JSON API", "The JSON write API"] | [] |
authentication:authentication-permissions-allow | authentication | authentication-permissions-allow | Defining permissions with "allow" blocks | The standard way to define permissions in Datasette is to use an "allow" block in the datasette.yaml file . This is a JSON document describing which actors are allowed to perform a permission. The most basic form of allow block is this ( allow demo , deny demo ): [[[cog from metadata_doc import config_example import textwrap config_example(cog, textwrap.dedent( """ allow: id: root """).strip(), "YAML", "JSON" ) ]]] [[[end]]] This will match any actors with an "id" property of "root" - for example, an actor that looks like this: { "id": "root", "name": "Root User" } An allow block can specify "deny all" using false ( demo ): [[[cog from metadata_doc import config_example import textwrap config_example(cog, textwrap.dedent( """ allow: false """).strip(), "YAML", "JSON" ) ]]] [[[end]]] An "allow" of true allows all access ( demo ): [[[cog from metadata_doc import config_example import textwrap config_example(cog, textwrap.dedent( """ allow: true """).strip(), "YAML", "JSON" ) ]]] [[[end]]] Allow keys can provide a list of values. These will match any actor that has any of those values ( allow demo , deny demo ): [[[cog from metadata_doc import config_example import textwrap config_example(cog, textwrap.dedent( """ allow: id: - simon - cleopaws """).strip(), "YAML", "JSON" ) ]]] [[[end]]] This will match any actor with an "id" of either "simon" or "cleopaws" . Actors can have properties that feature a list of values. These will be matched against the list of values in an allow block. Consider the following actor: { "id": "simon"… | ["Authentication and permissions", "Permissions"] | [{"href": "https://latest.datasette.io/-/allow-debug?actor=%7B%22id%22%3A+%22root%22%7D&allow=%7B%0D%0A++++++++%22id%22%3A+%22root%22%0D%0A++++%7D", "label": "allow demo"}, {"href": "https://latest.datasette.io/-/allow-debug?actor=%7B%22id%22%3A+%22trevor%22%7D&allow=%7B%0D%0A++++++++%22id%22%3A+%22root%22%0D%0A++++%7D", "label": "deny demo"}, {"href": "https://latest.datasette.io/-/allow-debug?actor=%7B%0D%0A++++%22id%22%3A+%22root%22%0D%0A%7D&allow=false", "label": "demo"}, {"href": "https://latest.datasette.io/-/allow-debug?actor=%7B%0D%0A++++%22id%22%3A+%22root%22%0D%0A%7D&allow=true", "label": "demo"}, {"href": "https://latest.datasette.io/-/allow-debug?actor=%7B%0D%0A++++%22id%22%3A+%22cleopaws%22%0D%0A%7D&allow=%7B%0D%0A++++%22id%22%3A+%5B%0D%0A++++++++%22simon%22%2C%0D%0A++++++++%22cleopaws%22%0D%0A++++%5D%0D%0A%7D", "label": "allow demo"}, {"href": "https://latest.datasette.io/-/allow-debug?actor=%7B%0D%0A++++%22id%22%3A+%22pancakes%22%0D%0A%7D&allow=%7B%0D%0A++++%22id%22%3A+%5B%0D%0A++++++++%22simon%22%2C%0D%0A++++++++%22cleopaws%22%0D%0A++++%5D%0D%0A%7D", "label": "deny demo"}, {"href": "https://latest.datasette.io/-/allow-debug?actor=%7B%0D%0A++++%22id%22%3A+%22simon%22%2C%0D%0A++++%22roles%22%3A+%5B%0D%0A++++++++%22staff%22%2C%0D%0A++++++++%22developer%22%0D%0A++++%5D%0D%0A%7D&allow=%7B%0D%0A++++%22roles%22%3A+%5B%0D%0A++++++++%22developer%22%0D%0A++++%5D%0D%0A%7D", "label": "allow demo"}, {"href": "https://latest.datasette.io/-/allow-debug?actor=%7B%0D%0A++++%22id%22%3A+%22cleopaws%22%2C%0D%0A++++%22roles%22%3A+%5B%22dog%22%5D%0D%0A%7D&allow=%7B%0D%0A++++%22roles%22%3A+%5B%0D%0A++++++++%22developer%22%0D%0A++++%5D%0D%0A%7D", "label": "deny demo"}, {"href": "https://latest.datasette.io/-/allow-debug?actor=%7B%0D%0A++++%22id%22%3A+%22simon%22%0D%0A%7D&allow=%7B%0D%0A++++%22id%22%3A+%22*%22%0D%0A%7D", "label": "allow demo"}, {"href": "https://latest.datasette.io/-/allow-debug?actor=%7B%0D%0A++++%22bot%22%3A+%22readme-bot%22%0D%0A%7D&allow=%7B%0D%0A++++%22id%22%3A+%22*%22%0D%0A%7D", "label": "deny demo"… |
json_api:json-api-default | json_api | json-api-default | Default representation | The default JSON representation of data from a SQLite table or custom query looks 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 } "ok" is always true if an error did not occur. The "rows" key is a list of objects, each one representing a row. The "truncated" key lets you know if the query was truncated. This can happen if a SQL query returns more than 1,000 results (or the max_returned_rows setting). For table pages, an additional key "next" may be present. This indicates that the next page in the pagination set can be retrieved using ?_next=VALUE . | ["JSON API"] | [] |
contributing:contributing-debugging | contributing | contributing-debugging | Debugging | Any errors that occur while Datasette is running while display a stack trace on the console. You can tell Datasette to open an interactive pdb debugger session if an error occurs using the --pdb option: datasette --pdb fixtures.db | ["Contributing"] | [] |
internals:internals-internal | internals | internals-internal | Datasette's internal database | Datasette maintains an "internal" SQLite database used for configuration, caching, and storage. Plugins can store configuration, settings, and other data inside this database. By default, Datasette will use a temporary in-memory SQLite database as the internal database, which is created at startup and destroyed at shutdown. Users of Datasette can optionally pass in a --internal flag to specify the path to a SQLite database to use as the internal database, which will persist internal data across Datasette instances. Datasette maintains tables called catalog_databases , catalog_tables , catalog_columns , catalog_indexes , catalog_foreign_keys with details of the attached databases and their schemas. These tables should not be considered a stable API - they may change between Datasette releases. The internal database is not exposed in the Datasette application by default, which means private data can safely be stored without worry of accidentally leaking information through the default Datasette interface and API. However, other plugins do have full read and write access to the internal database. Plugins can access this database by calling internal_db = datasette.get_internal_database() and then executing queries using the Database API . Plugin authors are asked to practice good etiquette when using the internal database, as all plugins use the same database to store data. For example: Use a unique prefix when creating tables, indices, and triggers in the internal database. If your plugin is called datasette-xyz , then prefix names with datasette_xyz_* . Avoid long-running write statements that may stall or block other plugins that are trying to write at the same time. Use temporary tables or shared in-memory attached databases when possible. … | ["Internals for plugins"] | [] |
getting_started:getting-started-datasette-lite | getting_started | getting-started-datasette-lite | Datasette in your browser with Datasette Lite | Datasette Lite is Datasette packaged using WebAssembly so that it runs entirely in your browser, no Python web application server required. You can pass a URL to a CSV, SQLite or raw SQL file directly to Datasette Lite to explore that data in your browser. This example link opens Datasette Lite and loads the SQL Murder Mystery example database from Northwestern University Knight Lab . | ["Getting started"] | [{"href": "https://lite.datasette.io/", "label": "Datasette Lite"}, {"href": "https://lite.datasette.io/?url=https%3A%2F%2Fraw.githubusercontent.com%2FNUKnightLab%2Fsql-mysteries%2Fmaster%2Fsql-murder-mystery.db#/sql-murder-mystery", "label": "example link"}, {"href": "https://github.com/NUKnightLab/sql-mysteries", "label": "Northwestern University Knight Lab"}] |
internals:internals-datasette | internals | internals-datasette | Datasette class | This object is an instance of the Datasette class, passed to many plugin hooks as an argument called datasette . You can create your own instance of this - for example to help write tests for a plugin - like so: from datasette.app import Datasette # With no arguments a single in-memory database will be attached datasette = Datasette() # The files= argument can load files from disk datasette = Datasette(files=["/path/to/my-database.db"]) # Pass metadata as a JSON dictionary like this datasette = Datasette( files=["/path/to/my-database.db"], metadata={ "databases": { "my-database": { "description": "This is my database" } } }, ) Constructor parameters include: files=[...] - a list of database files to open immutables=[...] - a list of database files to open in immutable mode metadata={...} - a dictionary of Metadata config_dir=... - the configuration directory to use, stored in datasette.config_dir | ["Internals for plugins"] | [] |
installation:installation-datasette-desktop | installation | installation-datasette-desktop | Datasette Desktop for Mac | Datasette Desktop is a packaged Mac application which bundles Datasette together with Python and allows you to install and run Datasette directly on your laptop. This is the best option for local installation if you are not comfortable using the command line. | ["Installation", "Basic installation"] | [{"href": "https://datasette.io/desktop", "label": "Datasette Desktop"}] |
index:datasette | index | datasette | Datasette | An open source multi-tool for exploring and publishing data Datasette is a tool for exploring and publishing data. It helps people take data of any shape or size and publish that as an interactive, explorable website and accompanying API. Datasette is aimed at data journalists, museum curators, archivists, local governments and anyone else who has data that they wish to share with the world. It is part of a wider ecosystem of tools and plugins dedicated to making working with structured data as productive as possible. Explore a demo , watch a presentation about the project or Try Datasette without installing anything using Glitch . Interested in learning Datasette? Start with the official tutorials . Support questions, feedback? Join the Datasette Discord . | [] | [{"href": "https://pypi.org/project/datasette/", "label": null}, {"href": "https://docs.datasette.io/en/stable/changelog.html", "label": null}, {"href": "https://pypi.org/project/datasette/", "label": null}, {"href": "https://github.com/simonw/datasette/actions?query=workflow%3ATest", "label": null}, {"href": "https://github.com/simonw/datasette/blob/main/LICENSE", "label": null}, {"href": "https://hub.docker.com/r/datasetteproject/datasette", "label": null}, {"href": "https://datasette.io/discord", "label": null}, {"href": "https://pypi.org/project/datasette/", "label": null}, {"href": "https://docs.datasette.io/en/stable/changelog.html", "label": null}, {"href": "https://pypi.org/project/datasette/", "label": null}, {"href": "https://github.com/simonw/datasette/actions?query=workflow%3ATest", "label": null}, {"href": "https://github.com/simonw/datasette/blob/main/LICENSE", "label": null}, {"href": "https://hub.docker.com/r/datasetteproject/datasette", "label": null}, {"href": "https://datasette.io/discord", "label": null}, {"href": "https://fivethirtyeight.datasettes.com/fivethirtyeight", "label": "Explore a demo"}, {"href": "https://static.simonwillison.net/static/2018/pybay-datasette/", "label": "a presentation about the project"}, {"href": "https://datasette.io/tutorials", "label": "the official tutorials"}, {"href": "https://datasette.io/discord", "label": "Datasette Discord"}] |
metadata:database-level-metadata | metadata | database-level-metadata | 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 | ["Metadata", "Metadata reference"] | [] |
internals:database-constructor | internals | database-constructor | Database(ds, path=None, is_mutable=True, is_memory=False, memory_name=None) | The Database() constructor can be used by plugins, in conjunction with .add_database(db, name=None, route=None) , to create and register new databases. The arguments are as follows: ds - Datasette class (required) The Datasette instance you are attaching this database to. path - string Path to a SQLite database file on disk. is_mutable - boolean Set this to False to cause Datasette to open the file in immutable mode. is_memory - boolean Use this to create non-shared memory connections. memory_name - string or None Use this to create a named in-memory database. Unlike regular memory databases these can be accessed by multiple threads and will persist an changes made to them for the lifetime of the Datasette server process. The first argument is the datasette instance you are attaching to, the second is a path= , then is_mutable and is_memory are both optional arguments. | ["Internals for plugins", "Database class"] | [] |
internals:internals-database-introspection | internals | internals-database-introspection | Database introspection | The Database class also provides properties and methods for introspecting the database. db.name - string The name of the database - usually the filename without the .db prefix. db.size - integer The size of the database file in bytes. 0 for :memory: databases. db.mtime_ns - integer or None The last modification time of the database file in nanoseconds since the epoch. None for :memory: databases. db.is_mutable - boolean Is this database mutable, and allowed to accept writes? db.is_memory - boolean Is this database an in-memory database? await db.attached_databases() - list of named tuples Returns a list of additional databases that have been connected to this database using the SQLite ATTACH command. Each named tuple has fields seq , name and file . await db.table_exists(table) - boolean Check if a table called table exists. await db.view_exists(view) - boolean … | ["Internals for plugins", "Database class"] | [] |
internals:internals-database | internals | internals-database | Database class | Instances of the Database class can be used to execute queries against attached SQLite databases, and to run introspection against their schemas. | ["Internals for plugins"] | [] |
pages:databaseview | pages | databaseview | Database | Each database has a page listing the tables, views and canned 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 global-power-plants.datasettes.com/global-power-plants The JSON version of this page provides programmatic access to the underlying data: fivethirtyeight.datasettes.com/fivethirtyeight.json global-power-plants.datasettes.com/global-power-plants.json | ["Pages and API endpoints"] | [{"href": "https://fivethirtyeight.datasettes.com/fivethirtyeight", "label": "fivethirtyeight.datasettes.com/fivethirtyeight"}, {"href": "https://global-power-plants.datasettes.com/global-power-plants", "label": "global-power-plants.datasettes.com/global-power-plants"}, {"href": "https://fivethirtyeight.datasettes.com/fivethirtyeight.json", "label": "fivethirtyeight.datasettes.com/fivethirtyeight.json"}, {"href": "https://global-power-plants.datasettes.com/global-power-plants.json", "label": "global-power-plants.datasettes.com/global-power-plants.json"}] |
custom_templates:customization-custom-templates | custom_templates | customization-custom-templates | 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 Canned query page (/mydatabase/canned-query): query-mydatabase-canned-query.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: <!-… | ["Custom pages and templates", "Publishing static assets"] | [{"href": "https://github.com/simonw/datasette/blob/main/datasette/templates/_table.html", "label": "can be seen here"}] |
writing_plugins:writing-plugins-custom-templates | writing_plugins | writing-plugins-custom-templates | Custom templates | If your plugin has a templates/ directory, Datasette will attempt to load templates from that directory before it uses its own default templates. The priority order for template loading is: templates from the --template-dir argument, if specified templates from the templates/ directory in any installed plugins default templates that ship with Datasette See Custom pages and templates for more details on how to write custom templates, including which filenames to use to customize which parts of the Datasette UI. Templates should be bundled for distribution using the same package_data mechanism in setup.py described for static assets above, for example: package_data = ( { "datasette_plugin_name": [ "templates/my_template.html", ], }, ) You can also use wildcards here such as templates/*.html . See datasette-edit-schema for an example of this pattern. | ["Writing plugins"] | [{"href": "https://github.com/simonw/datasette-edit-schema", "label": "datasette-edit-schema"}] |
custom_templates:custom-pages-redirects | custom_templates | custom-pages-redirects | 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) }} | ["Custom pages and templates"] | [] |
custom_templates:customization | custom_templates | customization | Custom pages and templates | Datasette provides a number of ways of customizing the way data is displayed. | [] | [] |
custom_templates:id1 | custom_templates | id1 | 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 . | ["Custom pages and templates", "Publishing static assets"] | [] |
publish:publish-custom-metadata-and-plugins | publish | publish-custom-metadata-and-plugins | Custom metadata and plugins | datasette publish accepts a number of additional options which can be used to further customize your Datasette instance. You can define your own Metadata and deploy that with your instance like so: datasette publish cloudrun --service=my-service mydatabase.db -m metadata.json If you just want to set the title, license or source information you can do that directly using extra options to datasette publish : datasette publish cloudrun mydatabase.db --service=my-service \ --title="Title of my database" \ --source="Where the data originated" \ --source_url="http://www.example.com/" You can also specify plugins you would like to install. For example, if you want to include the datasette-vega visualization plugin you can use the following: datasette publish cloudrun mydatabase.db --service=my-service --install=datasette-vega If a plugin has any Secret configuration values you can use the --plugin-secret option to set those secrets at publish time. For example, using Heroku with datasette-auth-github you might run the following command: datasette publish heroku my_database.db \ --name my-heroku-app-demo \ --install=datasette-auth-github \ --plugin-secret datasette-auth-github client_id your_client_id \ --plugin-secret datasette-auth-github client_secret your_client_secret | ["Publishing data", "datasette publish"] | [{"href": "https://github.com/simonw/datasette-vega", "label": "datasette-vega"}, {"href": "https://github.com/simonw/datasette-auth-github", "label": "datasette-auth-github"}] |
custom_templates:custom-pages-headers | custom_templates | custom-pages-headers | 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 | ["Custom pages and templates"] | [] |
custom_templates:custom-pages-errors | custom_templates | custom-pages-errors | 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. | ["Custom pages and templates", "Custom redirects"] | [{"href": "https://github.com/simonw/datasette/blob/main/datasette/templates/error.html", "label": "default error.html template"}] |
configuration:configuration-reference-css-js | configuration | configuration-reference-css-js | Custom CSS and JavaScript | Datasette can load additional CSS and JavaScript files, configured in datasette.yaml like this: [[[cog from metadata_doc import config_example config_example(cog, """ extra_css_urls: - https://simonwillison.net/static/css/all.bf8cd891642c.css extra_js_urls: - https://code.jquery.com/jquery-3.2.1.slim.min.js """) ]]] [[[end]]] The extra CSS and JavaScript files will be linked in the <head> of every page: <link rel="stylesheet" href="https://simonwillison.net/static/css/all.bf8cd891642c.css"> <script src="https://code.jquery.com/jquery-3.2.1.slim.min.js"></script> You can also specify a SRI (subresource integrity hash) for these assets: [[[cog config_example(cog, """ extra_css_urls: - url: https://simonwillison.net/static/css/all.bf8cd891642c.css sri: sha384-9qIZekWUyjCyDIf2YK1FRoKiPJq4PHt6tp/ulnuuyRBvazd0hG7pWbE99zvwSznI extra_js_urls: - url: https://code.jquery.com/jquery-3.2.1.slim.min.js sri: sha256-k2WSCIexGzOj3Euiig+TlR8gA0EmPjuc79OEeY5L45g= """) ]]] [[[end]]] This will produce: <link rel="stylesheet" href="https://simonwillison.net/static/css/all.bf8cd891642c.css" integrity="sha384-9qIZekWUyjCyDIf2YK1FRoKiPJq4PHt6tp/ulnuuyRBvazd0hG7pWbE99zvwSznI" crossorigin="anonymous"> <script src="https://code.jquery.com/jquery-3.2.1.slim.min.js" integrity="sha256-k2WSCIexGzOj3Euiig+TlR8gA0EmPjuc79OEeY5L45g=" crossorigin="anonymous"></script> Modern browsers will only execute the stylesheet or JavaScript if the SRI hash matches the content served. You can generate hashes using www.srihash.org Items in "extra_js_urls" can specify "module": true if they reference JavaScript that uses JavaScript modules . This configuration: [[[cog config_example(cog, """ extra_js_urls: - url: http… | ["Configuration", null] | [{"href": "https://www.srihash.org/", "label": "www.srihash.org"}, {"href": "https://developer.mozilla.org/en-US/docs/Web/JavaScript/Guide/Modules", "label": "JavaScript modules"}] |
sql_queries:id3 | sql_queries | id3 | Cross-database queries | SQLite has the ability to run queries that join across multiple databases. Up to ten databases can be attached to a single SQLite connection and queried together. Datasette can execute joins across multiple databases if it is started with the --crossdb option: datasette fixtures.db extra_database.db --crossdb If it is started in this way, the /_memory page can be used to execute queries that join across multiple databases. References to tables in attached databases should be preceded by the database name and a period. For example, this query will show a list of tables across both of the above databases: select 'fixtures' as database, * from [fixtures].sqlite_master union select 'extra_database' as database, * from [extra_database].sqlite_master Try that out here . | ["Running SQL queries"] | [{"href": "https://latest.datasette.io/_memory?sql=select%0D%0A++%27fixtures%27+as+database%2C+*%0D%0Afrom%0D%0A++%5Bfixtures%5D.sqlite_master%0D%0Aunion%0D%0Aselect%0D%0A++%27extra_database%27+as+database%2C+*%0D%0Afrom%0D%0A++%5Bextra_database%5D.sqlite_master", "label": "Try that out here"}] |
json_api:tablecreateview-example | json_api | tablecreateview-example | Creating a table from example data | Instead of specifying columns directly you can instead pass a single example row or a list of rows . Datasette will create a table with a schema that matches those rows and insert them for you: POST /<database>/-/create Content-Type: application/json Authorization: Bearer dstok_<rest-of-token> { "table": "creatures", "rows": [ { "id": 1, "name": "Tarantula" }, { "id": 2, "name": "Kākāpō" } ], "pk": "id" } Doing this requires both the create-table and insert-row permissions. The 201 response here will be similar to the columns form, but will also include the number of rows that were inserted as row_count : { "ok": true, "database": "data", "table": "creatures", "table_url": "http://127.0.0.1:8001/data/creatures", "table_api_url": "http://127.0.0.1:8001/data/creatures.json", "schema": "CREATE TABLE [creatures] (\n [id] INTEGER PRIMARY KEY,\n [name] TEXT\n)", "row_count": 2 } You can call the create endpoint multiple times for the same table provided you are specifying the table using the rows or row option. New rows will be inserted into the table each time. This means you can use this API if you are unsure if the relevant table has been created yet. If you pass a row to the create endpoint with a primary key that already exists you will get an error that looks like this: { "ok": false, "errors": [ "UNIQUE constraint failed: creatures.id" ] } You can avoid this error by passing the same "ignore": true or "replace": true options to the create endpoint as you can to the insert endpoint . To use the "replace": true option you will also need the update-row permission. Pass "alter": true to automatically add any missing columns to t… | ["JSON API", "The JSON write API"] | [] |
json_api:tablecreateview | json_api | tablecreateview | Creating a table | To create a table, make a POST to /<database>/-/create . This requires the create-table permission. POST /<database>/-/create Content-Type: application/json Authorization: Bearer dstok_<rest-of-token> { "table": "name_of_new_table", "columns": [ { "name": "id", "type": "integer" }, { "name": "title", "type": "text" } ], "pk": "id" } The JSON here describes the table that will be created: table is the name of the table to create. This field is required. columns is a list of columns to create. Each column is a dictionary with name and type keys. name is the name of the column. This is required. type is the type of the column. This is optional - if not provided, text will be assumed. The valid types are text , integer , float and blob . pk is the primary key for the table. This is optional - if not provided, Datasette will create a SQLite table with a hidden rowid column. If the primary key is an integer column, it will be configured to automatically increment for each new record. If you set this to id without including an id column in the list of columns , Datasette will create an auto-incrementing integer ID column for you. pks can be used instead of pk to create a compound primary key. It should be a JSON list of column names to use in that primary key. … | ["JSON API", "The JSON write API"] | [] |
changelog:cookie-methods | changelog | cookie-methods | Cookie methods | Plugins can now use the new response.set_cookie() method to set cookies. A new request.cookies method on the :ref:internals_request` can be used to read incoming cookies. | ["Changelog", "0.44 (2020-06-11)"] | [] |
plugins:plugins-datasette-load-plugins | plugins | plugins-datasette-load-plugins | Controlling which plugins are loaded | Datasette defaults to loading every plugin that is installed in the same virtual environment as Datasette itself. You can set the DATASETTE_LOAD_PLUGINS environment variable to a comma-separated list of plugin names to load a controlled subset of plugins instead. For example, to load just the datasette-vega and datasette-cluster-map plugins, set DATASETTE_LOAD_PLUGINS to datasette-vega,datasette-cluster-map : export DATASETTE_LOAD_PLUGINS='datasette-vega,datasette-cluster-map' datasette mydb.db Or: DATASETTE_LOAD_PLUGINS='datasette-vega,datasette-cluster-map' \ datasette mydb.db To disable the loading of all additional plugins, set DATASETTE_LOAD_PLUGINS to an empty string: export DATASETTE_LOAD_PLUGINS='' datasette mydb.db A quick way to test this setting is to use it with the datasette plugins command: DATASETTE_LOAD_PLUGINS='datasette-vega' datasette plugins This should output the following: [ { "name": "datasette-vega", "static": true, "templates": false, "version": "0.6.2", "hooks": [ "extra_css_urls", "extra_js_urls" ] } ] | ["Plugins"] | [] |
authentication:authentication-permissions-execute-sql | authentication | authentication-permissions-execute-sql | Controlling the ability to execute arbitrary SQL | Datasette defaults to allowing any site visitor to execute their own custom SQL queries, for example using the form on the database page or by appending a ?_where= parameter to the table page like this . Access to this ability is controlled by the execute-sql permission. The easiest way to disable arbitrary SQL queries is using the default_allow_sql setting when you first start Datasette running. You can alternatively use an "allow_sql" block to control who is allowed to execute arbitrary SQL queries. To prevent any user from executing arbitrary SQL queries, use this: [[[cog config_example(cog, """ allow_sql: false """) ]]] [[[end]]] To enable just the root user to execute SQL for all databases in your instance, use the following: [[[cog config_example(cog, """ allow_sql: id: root """) ]]] [[[end]]] To limit this ability for just one specific database, use this: [[[cog config_example(cog, """ databases: mydatabase: allow_sql: id: root """) ]]] [[[end]]] | ["Authentication and permissions", "Access permissions in "] | [{"href": "https://latest.datasette.io/fixtures", "label": "the database page"}, {"href": "https://latest.datasette.io/fixtures/facetable?_where=_city_id=1", "label": "like this"}] |
changelog:control-http-caching-with-ttl | changelog | control-http-caching-with-ttl | Control HTTP caching with ?_ttl= | You can now customize the HTTP max-age header that is sent on a per-URL basis, using the new ?_ttl= query string parameter. You can set this to any value in seconds, or you can set it to 0 to disable HTTP caching entirely. Consider for example this query which returns a randomly selected member of the Avengers: select * from [avengers/avengers] order by random() limit 1 If you hit the following page repeatedly you will get the same result, due to HTTP caching: /fivethirtyeight?sql=select+*+from+%5Bavengers%2Favengers%5D+order+by+random%28%29+limit+1 By adding ?_ttl=0 to the zero you can ensure the page will not be cached and get back a different super hero every time: /fivethirtyeight?sql=select+*+from+%5Bavengers%2Favengers%5D+order+by+random%28%29+limit+1&_ttl=0 | ["Changelog", "0.23 (2018-06-18)"] | [{"href": "https://fivethirtyeight.datasettes.com/fivethirtyeight?sql=select+*+from+%5Bavengers%2Favengers%5D+order+by+random%28%29+limit+1", "label": "/fivethirtyeight?sql=select+*+from+%5Bavengers%2Favengers%5D+order+by+random%28%29+limit+1"}, {"href": "https://fivethirtyeight.datasettes.com/fivethirtyeight?sql=select+*+from+%5Bavengers%2Favengers%5D+order+by+random%28%29+limit+1&_ttl=0", "label": "/fivethirtyeight?sql=select+*+from+%5Bavengers%2Favengers%5D+order+by+random%28%29+limit+1&_ttl=0"}] |
contributing:id1 | contributing | id1 | Contributing | Datasette is an open source project. We welcome contributions! This document describes how to contribute to Datasette core. You can also contribute to the wider Datasette ecosystem by creating new Plugins . | [] | [] |
contributing:contributing-continuous-deployment | contributing | contributing-continuous-deployment | Continuously deployed demo instances | The demo instance at latest.datasette.io is re-deployed automatically to Google Cloud Run for every push to main that passes the test suite. This is implemented by the GitHub Actions workflow at .github/workflows/deploy-latest.yml . Specific branches can also be set to automatically deploy by adding them to the on: push: branches block at the top of the workflow YAML file. Branches configured in this way will be deployed to a new Cloud Run service whether or not their tests pass. The Cloud Run URL for a branch demo can be found in the GitHub Actions logs. | ["Contributing"] | [{"href": "https://latest.datasette.io/", "label": "latest.datasette.io"}, {"href": "https://github.com/simonw/datasette/blob/main/.github/workflows/deploy-latest.yml", "label": ".github/workflows/deploy-latest.yml"}] |
index:contents | index | contents | Contents | Getting started Play with a live demo Follow a tutorial Datasette in your browser with Datasette Lite Try Datasette without installing anything using Glitch Using Datasette on your own computer Installation Basic installation Datasette Desktop for Mac Using Homebrew Using pip Advanced installation options Using pipx Using Docker A note about extensions Configuration Configuration via the command-line datasette.yaml reference Settings Plugin configuration Permissions configuration Canned queries configuration Custom CSS and JavaScript The Datasette Ecosystem sqlite-utils Dogsheep CLI reference datasette --help datasette serve datasette --get datasette serve --help-settings datasette plugins datasette install datasette uninstall datasette publish datasette publish cloudrun datasette publish heroku datasette package datasette inspect datasette create-token Pages and API endpoints Top-level index Database Hidden tables Table Row Publishing data datasette publish Publishing to Google Cloud Run Publishing to Heroku Publishing to Vercel Publishing to Fly Custom metadata and plugins datasette package Deploying Datasette Deployment fundamentals Running Datasette using systemd Running Datasette using OpenRC Deploying using buildpacks Running Datasette behind a proxy Nginx proxy configuration Apache proxy configuration JSON API Default representation Different shapes Pagination Special JSON arguments Table arguments Column filter arguments Special table arguments Expanding foreign key references Discovering the JSON for a page Enabling CORS The JSON write API Inserting rows Upserting rows Updating a row Deleting a row Creating a table Creating a table from example data Dropping tables Running SQL queries Named parameters Views Canned queries Canned query parameters Additional canned query options Writable canned queries Magic parameters JSON API for writable canned queries Pagination Cross-database queries Authentication and permissions Actors Using the "root" actor Permissions How permissions are resolved Defining permiss… | ["Datasette"] | [] |
settings:setting-secret | settings | setting-secret | Configuring the secret | Datasette uses a secret string to sign secure values such as cookies. If you do not provide a secret, Datasette will create one when it starts up. This secret will reset every time the Datasette server restarts though, so things like authentication cookies and API tokens will not stay valid between restarts. You can pass a secret to Datasette in two ways: with the --secret command-line option or by setting a DATASETTE_SECRET environment variable. datasette mydb.db --secret=SECRET_VALUE_HERE Or: export DATASETTE_SECRET=SECRET_VALUE_HERE datasette mydb.db One way to generate a secure random secret is to use Python like this: python3 -c 'import secrets; print(secrets.token_hex(32))' cdb19e94283a20f9d42cca50c5a4871c0aa07392db308755d60a1a5b9bb0fa52 Plugin authors make use of this signing mechanism in their plugins using .sign(value, namespace="default") and .unsign(value, namespace="default") . | ["Settings"] | [] |
full_text_search:full-text-search-table-or-view | full_text_search | full-text-search-table-or-view | 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 Metadata . 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 metadata_example metadata_example(cog, { "databases": { "russian-ads": { "tables": { "display_ads": { "fts_table": "ads_fts", "fts_pk": "id", "searchmode": "raw" } } } } }) ]]] [[[end]]] | ["Full-text search"] | [{"href": "https://latest.datasette.io/fixtures/searchable_view?_fts_table=searchable_fts&_fts_pk=pk", "label": "https://latest.datasette.io/fixtures/searchable_view?_fts_table=searchable_fts&_fts_pk=pk"}] |
full_text_search:configuring-fts-using-sqlite-utils | full_text_search | configuring-fts-using-sqlite-utils | 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 | ["Full-text search", "Enabling full-text search for a SQLite table"] | [{"href": "https://sqlite-utils.datasette.io/", "label": "sqlite-utils"}, {"href": "https://sqlite-utils.datasette.io/en/latest/python-api.html#enabling-full-text-search", "label": "it from Python code"}, {"href": "https://sqlite-utils.datasette.io/en/latest/cli.html#configuring-full-text-search", "label": "using the accompanying command-line tool"}] |
full_text_search:configuring-fts-using-csvs-to-sqlite | full_text_search | configuring-fts-using-csvs-to-sqlite | 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 | ["Full-text search", "Enabling full-text search for a SQLite table"] | [{"href": "https://github.com/simonw/csvs-to-sqlite", "label": "csvs-to-sqlite"}] |
full_text_search:configuring-fts-by-hand | full_text_search | configuring-fts-by-hand | 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. | ["Full-text search", "Enabling full-text search for a SQLite table"] | [{"href": "https://sqlite-utils.datasette.io/", "label": "sqlite-utils"}] |
configuration:configuration-cli | configuration | configuration-cli | Configuration via the command-line | The recommended way to configure Datasette is using a datasette.yaml file passed to -c/--config . You can also pass individual settings to Datasette using the -s/--setting option, which can be used multiple times: datasette mydatabase.db \ --setting settings.default_page_size 50 \ --setting settings.sql_time_limit_ms 3500 This option takes dotted-notation for the first argument and a value for the second argument. This means you can use it to set any configuration value that would be valid in a datasette.yaml file. It also works for plugin configuration, for example for datasette-cluster-map : datasette mydatabase.db \ --setting plugins.datasette-cluster-map.latitude_column xlat \ --setting plugins.datasette-cluster-map.longitude_column xlon If the value you provide is a valid JSON object or list it will be treated as nested data, allowing you to configure plugins that accept lists such as datasette-proxy-url : datasette mydatabase.db \ -s plugins.datasette-proxy-url.paths '[{"path": "/proxy", "backend": "http://example.com/"}]' This is equivalent to a datasette.yaml file containing the following: [[[cog from metadata_doc import config_example import textwrap config_example(cog, textwrap.dedent( """ plugins: datasette-proxy-url: paths: - path: /proxy backend: http://example.com/ """).strip() ) ]]] [[[end]]] | ["Configuration"] | [{"href": "https://datasette.io/plugins/datasette-cluster-map", "label": "datasette-cluster-map"}, {"href": "https://datasette.io/plugins/datasette-proxy-url", "label": "datasette-proxy-url"}] |
settings:config-dir | settings | config-dir | Configuration directory mode | Normally you configure Datasette using command-line options. For a Datasette instance with custom templates, custom plugins, a static directory and several databases this can get quite verbose: datasette one.db two.db \ --metadata=metadata.json \ --template-dir=templates/ \ --plugins-dir=plugins \ --static css:css As an alternative to this, you can run Datasette in configuration directory mode. Create a directory with the following structure: # In a directory called my-app: my-app/one.db my-app/two.db my-app/datasette.yaml my-app/metadata.json my-app/templates/index.html my-app/plugins/my_plugin.py my-app/static/my.css Now start Datasette by providing the path to that directory: datasette my-app/ Datasette will detect the files in that directory and automatically configure itself using them. It will serve all *.db files that it finds, will load metadata.json if it exists, and will load the templates , plugins and static folders if they are present. The files that can be included in this directory are as follows. All are optional. *.db (or *.sqlite3 or *.sqlite ) - SQLite database files that will be served by Datasette datasette.yaml - Configuration for the Datasette instance metadata.json - Metadata for those databases - metadata.yaml or metadata.yml can be used as well inspect-data.json - the result of running datasette inspect *.db --inspect-file=inspect-data.json from the configuration directory - any database files listed here will be treated as immutable, so they should not be changed while Datasette is running templates/ - a directory containing Custom templates … | ["Settings"] | [] |
changelog:configuration | changelog | configuration | 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. | ["Changelog", "1.0a8 (2024-02-07)"] | [{"href": "https://github.com/simonw/datasette/issues/2093", "label": "#2093"}, {"href": "https://github.com/simonw/datasette/issues/2252", "label": "#2252"}, {"href": "https://github.com/simonw/datasette/issues/2254", "label": "#2254"}, {"href": "https://github.com/simonw/datasette/issues/2247", "label": "#2247"}, {"href": "https://github.com/simonw/datasette/issues/2248", "label": "#2248"}, {"href": "https://github.com/simonw/datasette/issues/2249", "label": "#2249"}, {"href": "https://github.com/simonw/datasette/issues/2195", "label": "#2195"}] |
configuration:id1 | configuration | id1 | Configuration | Datasette offers several ways to configure your Datasette instances: server settings, plugin configuration, authentication, and more. Most configuration can be handled using a datasette.yaml configuration file, passed to datasette using the -c/--config flag: datasette mydatabase.db --config datasette.yaml This file can also use JSON, as datasette.json . YAML is recommended over JSON due to its support for comments and multi-line strings. | [] | [] |
json_api:column-filter-arguments | json_api | column-filter-arguments | Column filter arguments | You can filter the data returned by the table based on column values using a query string argument. ?column__exact=value or ?_column=value Returns rows where the specified column exactly matches the value. ?column__not=value Returns rows where the column does not match the value. ?column__contains=value Rows where the string column contains the specified value ( column like "%value%" in SQL). ?column__notcontains=value Rows where the string column does not contain the specified value ( column not like "%value%" in SQL). ?column__endswith=value Rows where the string column ends with the specified value ( column like "%value" in SQL). ?column__startswith=value Rows where the string column starts with the specified value ( column like "value%" in SQL). ?column__gt=value Rows which are greater than the specified value. ?column__gte=value Rows which… | ["JSON API", "Table arguments"] | [] |
metadata:metadata-column-descriptions | metadata | metadata-column-descriptions | 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 . | ["Metadata"] | [{"href": "https://latest.datasette.io/fixtures/roadside_attractions", "label": "latest.datasette.io/fixtures/roadside_attractions"}] |
changelog:code-formatting-with-black-and-prettier | changelog | code-formatting-with-black-and-prettier | Code formatting with Black and Prettier | Datasette adopted Black for opinionated Python code formatting in June 2019. Datasette now also embraces Prettier for JavaScript formatting, which like Black is enforced by tests in continuous integration. Instructions for using these two tools can be found in the new section on Code formatting in the contributors documentation. ( #1167 ) | ["Changelog", "0.54 (2021-01-25)"] | [{"href": "https://github.com/psf/black", "label": "Black"}, {"href": "https://prettier.io/", "label": "Prettier"}, {"href": "https://github.com/simonw/datasette/issues/1167", "label": "#1167"}] |
contributing:contributing-formatting | contributing | contributing-formatting | Code formatting | Datasette uses opinionated code formatters: Black for Python and Prettier for JavaScript. These formatters are enforced by Datasette's continuous integration: if a commit includes Python or JavaScript code that does not match the style enforced by those tools, the tests will fail. When developing locally, you can verify and correct the formatting of your code using these tools. | ["Contributing"] | [{"href": "https://github.com/psf/black", "label": "Black"}, {"href": "https://prettier.io/", "label": "Prettier"}] |
authentication:permissions-plugins | authentication | permissions-plugins | Checking permissions in plugins | Datasette plugins can check if an actor has permission to perform an action using the datasette.permission_allowed(...) method. Datasette core performs a number of permission checks, documented below . Plugins can implement the permission_allowed(datasette, actor, action, resource) plugin hook to participate in decisions about whether an actor should be able to perform a specified action. | ["Authentication and permissions"] | [] |
changelog:id1 | changelog | id1 | Changelog | [] | [] |
Advanced export
JSON shape: default, array, newline-delimited, object
CREATE TABLE [sections] ( [id] TEXT PRIMARY KEY, [page] TEXT, [ref] TEXT, [title] TEXT, [content] TEXT, [breadcrumbs] TEXT, [references] TEXT );