sections
582 rows sorted by content descending
This data as json, CSV (advanced)
page >30 ✖
- changelog 145
- authentication 26
- internals 25
- settings 21
- contributing 13
- sql_queries 12
- cli-reference 11
- metadata 11
- json_api 10
- installation 9
- introspection 9
- plugin_hooks 9
- custom_templates 7
- writing_plugins 7
- configuration 6
- full_text_search 6
- plugins 6
- spatialite 6
- deploying 5
- getting_started 5
- performance 5
- publish 5
- facets 4
- testing_plugins 4
- binary_data 3
- javascript_plugins 3
- pages 3
- ecosystem 2
- index 2
- csv_export 1
- …
id | page | ref | title | content ▲ | breadcrumbs | references |
---|---|---|---|---|---|---|
internals:internals-datasette-urls | internals | internals-datasette-urls | datasette.urls | The datasette.urls object contains methods for building URLs to pages within Datasette. Plugins should use this to link to pages, since these methods take into account any base_url configuration setting that might be in effect. datasette.urls.instance(format=None) Returns the URL to the Datasette instance root page. This is usually "/" . datasette.urls.path(path, format=None) Takes a path and returns the full path, taking base_url into account. For example, datasette.urls.path("-/logout") will return the path to the logout page, which will be "/-/logout" by default or /prefix-path/-/logout if base_url is set to /prefix-path/ datasette.urls.logout() Returns the URL to the logout page, usually "/-/logout" datasette.urls.static(path) Returns the URL of one of Datasette's default static assets, for example "/-/static/app.css" datasette.urls.static_plugins(plugin_name, path) Returns the URL of one of the static assets belonging to a plugin. datasette.urls.static_plugins("datasette_cluster_map", "datasette-cluster-map.js") would return "/-/static-plugins/datasette_cluster_map/datasette-cluster-map.js" datasette.urls.static(path) … | ["Internals for plugins", "Datasette class"] | [] |
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"] | [] |
changelog:permission-checks-now-consider-opinions-from-every-plugin | changelog | permission-checks-now-consider-opinions-from-every-plugin | Permission checks now consider opinions from every plugin | The datasette.permission_allowed() method previously consulted every plugin that implemented the permission_allowed() plugin hook and obeyed the opinion of the last plugin to return a value. ( #2275 ) Datasette now consults every plugin and checks to see if any of them returned False (the veto rule), and if none of them did, it then checks to see if any of them returned True . This is explained at length in the new documentation covering How permissions are resolved . | ["Changelog", "1.0a9 (2024-02-16)"] | [{"href": "https://github.com/simonw/datasette/issues/2275", "label": "#2275"}] |
testing_plugins:testing-datasette-client | testing_plugins | testing-datasette-client | Using datasette.client in tests | The datasette.client mechanism is designed for use in tests. It provides access to a pre-configured HTTPX async client instance that can make GET, POST and other HTTP requests against a Datasette instance from inside a test. A simple test looks like this: @pytest.mark.asyncio async def test_homepage(): ds = Datasette(memory=True) response = await ds.client.get("/") html = response.text assert "<h1>" in html Or for a JSON API: @pytest.mark.asyncio async def test_actor_is_null(): ds = Datasette(memory=True) response = await ds.client.get("/-/actor.json") assert response.json() == {"actor": None} To make requests as an authenticated actor, create a signed ds_cookie using the datasette.client.actor_cookie() helper function and pass it in cookies= like this: @pytest.mark.asyncio async def test_signed_cookie_actor(): ds = Datasette(memory=True) cookies = {"ds_actor": ds.client.actor_cookie({"id": "root"})} response = await ds.client.get("/-/actor.json", cookies=cookies) assert response.json() == {"actor": {"id": "root"}} | ["Testing plugins"] | [{"href": "https://www.python-httpx.org/async/", "label": "HTTPX async client"}] |
custom_templates:publishing-static-assets | custom_templates | publishing-static-assets | Publishing static assets | The datasette publish command can be used to publish your static assets, using the same syntax as above: datasette publish cloudrun mydb.db --static assets:static-files/ This will upload the contents of the static-files/ directory as part of the deployment, and configure Datasette to correctly serve the assets from /assets/ . | ["Custom pages and templates"] | [] |
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"] | [] |
settings:setting-publish-secrets | settings | setting-publish-secrets | Using secrets with datasette publish | The datasette publish and datasette package commands both generate a secret for you automatically when Datasette is deployed. This means that every time you deploy a new version of a Datasette project, a new secret will be generated. This will cause signed cookies to become invalid on every fresh deploy. You can fix this by creating a secret that will be used for multiple deploys and passing it using the --secret option: datasette publish cloudrun mydb.db --service=my-service --secret=cdb19e94283a20f9d42cca5 | ["Settings"] | [] |
changelog:id53 | changelog | id53 | 0.47.3 (2020-08-15) | The datasette --get command-line mechanism now ensures any plugins using the startup() hook are correctly executed. ( #934 ) | ["Changelog"] | [{"href": "https://github.com/simonw/datasette/issues/934", "label": "#934"}] |
changelog:id70 | changelog | id70 | 0.36 (2020-02-21) | The datasette object passed to plugins now has API documentation: Datasette class . ( #576 ) New methods on datasette : .add_database() and .remove_database() - documentation . ( #671 ) prepare_connection() plugin hook now takes optional datasette and database arguments - prepare_connection(conn, database, datasette) . ( #678 ) Added three new plugins and one new conversion tool to the The Datasette Ecosystem . | ["Changelog"] | [{"href": "https://github.com/simonw/datasette/issues/576", "label": "#576"}, {"href": "https://github.com/simonw/datasette/issues/671", "label": "#671"}, {"href": "https://github.com/simonw/datasette/issues/678", "label": "#678"}] |
cli-reference:id1 | cli-reference | id1 | CLI reference | The datasette CLI tool provides a number of commands. Running datasette without specifying a command runs the default command, datasette serve . See datasette serve for the full list of options for that command. [[[cog from datasette import cli from click.testing import CliRunner import textwrap def help(args): title = "datasette " + " ".join(args) cog.out("\n::\n\n") result = CliRunner().invoke(cli.cli, args) output = result.output.replace("Usage: cli ", "Usage: datasette ") cog.out(textwrap.indent(output, ' ')) cog.out("\n\n") ]]] [[[end]]] | [] | [] |
contributing:contributing-formatting-blacken-docs | contributing | contributing-formatting-blacken-docs | blacken-docs | The blacken-docs command applies Black formatting rules to code examples in the documentation. Run it like this: blacken-docs -l 60 docs/*.rst | ["Contributing", "Code formatting"] | [{"href": "https://pypi.org/project/blacken-docs/", "label": "blacken-docs"}] |
changelog:running-datasette-behind-a-proxy | changelog | running-datasette-behind-a-proxy | Running Datasette behind a proxy | The base_url configuration option is designed to help run Datasette on a specific path behind a proxy - for example if you want to run an instance of Datasette at /my-datasette/ within your existing site's URL hierarchy, proxied behind nginx or Apache. Support for this configuration option has been greatly improved ( #1023 ), and guidelines for using it are now available in a new documentation section on Running Datasette behind a proxy . ( #1027 ) | ["Changelog", "0.51 (2020-10-31)"] | [{"href": "https://github.com/simonw/datasette/issues/1023", "label": "#1023"}, {"href": "https://github.com/simonw/datasette/issues/1027", "label": "#1027"}] |
changelog:new-plugin-hook-asgi-wrapper | changelog | new-plugin-hook-asgi-wrapper | New plugin hook: asgi_wrapper | The asgi_wrapper(datasette) plugin hook allows plugins to entirely wrap the Datasette ASGI application in their own ASGI middleware. ( #520 ) Two new plugins take advantage of this hook: datasette-auth-github adds a authentication layer: users will have to sign in using their GitHub account before they can view data or interact with Datasette. You can also use it to restrict access to specific GitHub users, or to members of specified GitHub organizations or teams . datasette-cors allows you to configure CORS headers for your Datasette instance. You can use this to enable JavaScript running on a whitelisted set of domains to make fetch() calls to the JSON API provided by your Datasette instance. | ["Changelog", "0.29 (2019-07-07)"] | [{"href": "https://github.com/simonw/datasette/issues/520", "label": "#520"}, {"href": "https://github.com/simonw/datasette-auth-github", "label": "datasette-auth-github"}, {"href": "https://help.github.com/en/articles/about-organizations", "label": "organizations"}, {"href": "https://help.github.com/en/articles/organizing-members-into-teams", "label": "teams"}, {"href": "https://github.com/simonw/datasette-cors", "label": "datasette-cors"}, {"href": "https://developer.mozilla.org/en-US/docs/Web/HTTP/CORS", "label": "CORS headers"}] |
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"] | [] |
spatialite:id1 | spatialite | id1 | SpatiaLite | The SpatiaLite module for SQLite adds features for handling geographic and spatial data. For an example of what you can do with it, see the tutorial Building a location to time zone API with SpatiaLite . To use it with Datasette, you need to install the mod_spatialite dynamic library. This can then be loaded into Datasette using the --load-extension command-line option. Datasette can look for SpatiaLite in common installation locations if you run it like this: datasette --load-extension=spatialite --setting default_allow_sql off If SpatiaLite is in another location, use the full path to the extension instead: datasette --setting default_allow_sql off \ --load-extension=/usr/local/lib/mod_spatialite.dylib | [] | [{"href": "https://www.gaia-gis.it/fossil/libspatialite/index", "label": "SpatiaLite module"}, {"href": "https://datasette.io/tutorials/spatialite", "label": "Building a location to time zone API with SpatiaLite"}] |
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"}] |
internals:internals-response | internals | internals-response | Response class | The Response class can be returned from view functions that have been registered using the register_routes(datasette) hook. The Response() constructor takes the following arguments: body - string The body of the response. status - integer (optional) The HTTP status - defaults to 200. headers - dictionary (optional) A dictionary of extra HTTP headers, e.g. {"x-hello": "world"} . content_type - string (optional) The content-type for the response. Defaults to text/plain . For example: from datasette.utils.asgi import Response response = Response( "<xml>This is XML</xml>", content_type="application/xml; charset=utf-8", ) The quickest way to create responses is using the Response.text(...) , Response.html(...) , Response.json(...) or Response.redirect(...) helper methods: from datasette.utils.asgi import Response html_response = Response.html("This is HTML") json_response = Response.json({"this_is": "json"}) text_response = Response.text( "This will become utf-8 encoded text" ) # Redirects are served as 302, unless you pass status=301: redirect_response = Response.redirect( "https://latest.datasette.io/" ) Each of these responses will use the correct corresponding content-type - text/html; charset=utf-8 , application/json; charset=utf-8 or text/plain; charset=utf-8 respectively. Each of the helper methods take optional status= and headers= argument… | ["Internals for plugins"] | [] |
changelog:alter-table-support-for-create-insert-upsert-and-update | changelog | alter-table-support-for-create-insert-upsert-and-update | Alter table support for create, insert, upsert and update | The JSON write API can now be used to apply simple alter table schema changes, provided the acting actor has the new alter-table permission. ( #2101 ) The only alter operation supported so far is adding new columns to an existing table. The /db/-/create API now adds new columns during large operations to create a table based on incoming example "rows" , in the case where one of the later rows includes columns that were not present in the earlier batches. This requires the create-table but not the alter-table permission. When /db/-/create is called with rows in a situation where the table may have been already created, an "alter": true key can be included to indicate that any missing columns from the new rows should be added to the table. This requires the alter-table permission. /db/table/-/insert and /db/table/-/upsert and /db/table/row-pks/-/update all now also accept "alter": true , depending on the alter-table permission. Operations that alter a table now fire the new alter-table event . | ["Changelog", "1.0a9 (2024-02-16)"] | [{"href": "https://github.com/simonw/datasette/issues/2101", "label": "#2101"}] |
changelog:id67 | changelog | id67 | 0.38 (2020-03-08) | The Docker build of Datasette now uses SQLite 3.31.1, upgraded from 3.26. ( #695 ) datasette publish cloudrun now accepts an optional --memory=2Gi flag for setting the Cloud Run allocated memory to a value other than the default (256Mi). ( #694 ) Fixed bug where templates that shipped with plugins were sometimes not being correctly loaded. ( #697 ) | ["Changelog"] | [{"href": "https://hub.docker.com/r/datasetteproject/datasette", "label": "Docker build"}, {"href": "https://github.com/simonw/datasette/issues/695", "label": "#695"}, {"href": "https://github.com/simonw/datasette/issues/694", "label": "#694"}, {"href": "https://github.com/simonw/datasette/issues/697", "label": "#697"}] |
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"] | [] |
writing_plugins:writing-plugins-tracing | writing_plugins | writing-plugins-tracing | Tracing plugin hooks | The DATASETTE_TRACE_PLUGINS environment variable turns on detailed tracing showing exactly which hooks are being run. This can be useful for understanding how Datasette is using your plugin. DATASETTE_TRACE_PLUGINS=1 datasette mydb.db Example output: actor_from_request: { 'datasette': <datasette.app.Datasette object at 0x100bc7220>, 'request': <asgi.Request method="GET" url="http://127.0.0.1:4433/">} Hook implementations: [ <HookImpl plugin_name='codespaces', plugin=<module 'datasette_codespaces' from '.../site-packages/datasette_codespaces/__init__.py'>>, <HookImpl plugin_name='datasette.actor_auth_cookie', plugin=<module 'datasette.actor_auth_cookie' from '.../datasette/datasette/actor_auth_cookie.py'>>, <HookImpl plugin_name='datasette.default_permissions', plugin=<module 'datasette.default_permissions' from '.../datasette/default_permissions.py'>>] Results: [{'id': 'root'}] | ["Writing plugins"] | [] |
changelog:permissions-fix-for-the-upsert-api | changelog | permissions-fix-for-the-upsert-api | Permissions fix for the upsert API | The /database/table/-/upsert API had a minor permissions bug, only affecting Datasette instances that had configured the insert-row and update-row permissions to apply to a specific table rather than the database or instance as a whole. Full details in issue #2262 . To avoid similar mistakes in the future the datasette.permission_allowed() method now specifies default= as a keyword-only argument. | ["Changelog", "1.0a9 (2024-02-16)"] | [{"href": "https://github.com/simonw/datasette/issues/2262", "label": "#2262"}] |
authentication:allowdebugview | authentication | allowdebugview | The /-/allow-debug tool | The /-/allow-debug tool lets you try out different "action" blocks against different "actor" JSON objects. You can try that out here: https://latest.datasette.io/-/allow-debug | ["Authentication and permissions", "Permissions"] | [{"href": "https://latest.datasette.io/-/allow-debug", "label": "https://latest.datasette.io/-/allow-debug"}] |
binary_data:binary-linking | binary_data | binary-linking | Linking to binary downloads | The .blob output format is used to return binary data. It requires a _blob_column= query string argument specifying which BLOB column should be downloaded, for example: https://latest.datasette.io/fixtures/binary_data/1.blob?_blob_column=data This output format can also be used to return binary data from an arbitrary SQL query. Since such queries do not specify an exact row, an additional ?_blob_hash= parameter can be used to specify the SHA-256 hash of the value that is being linked to. Consider the query select data from binary_data - demonstrated here . That page links to the binary value downloads. Those links look like this: https://latest.datasette.io/fixtures.blob?sql=select+data+from+binary_data&_blob_column=data&_blob_hash=f3088978da8f9aea479ffc7f631370b968d2e855eeb172bea7f6c7a04262bb6d These .blob links are also returned in the .csv exports Datasette provides for binary tables and queries, since the CSV format does not have a mechanism for representing binary data. | ["Binary data"] | [{"href": "https://latest.datasette.io/fixtures/binary_data/1.blob?_blob_column=data", "label": "https://latest.datasette.io/fixtures/binary_data/1.blob?_blob_column=data"}, {"href": "https://latest.datasette.io/fixtures?sql=select+data+from+binary_data", "label": "demonstrated here"}, {"href": "https://latest.datasette.io/fixtures.blob?sql=select+data+from+binary_data&_blob_column=data&_blob_hash=f3088978da8f9aea479ffc7f631370b968d2e855eeb172bea7f6c7a04262bb6d", "label": "https://latest.datasette.io/fixtures.blob?sql=select+data+from+binary_data&_blob_column=data&_blob_hash=f3088978da8f9aea479ffc7f631370b968d2e855eeb172bea7f6c7a04262bb6d"}] |
cli-reference:cli-datasette-get | cli-reference | cli-datasette-get | datasette --get | The --get option to datasette serve (or just datasette ) specifies the path to a page within Datasette and causes Datasette to output the content from that path without starting the web server. This means that all of Datasette's functionality can be accessed directly from the command-line. For example: datasette --get '/-/versions.json' | jq . { "python": { "version": "3.8.5", "full": "3.8.5 (default, Jul 21 2020, 10:48:26) \n[Clang 11.0.3 (clang-1103.0.32.62)]" }, "datasette": { "version": "0.46+15.g222a84a.dirty" }, "asgi": "3.0", "uvicorn": "0.11.8", "sqlite": { "version": "3.32.3", "fts_versions": [ "FTS5", "FTS4", "FTS3" ], "extensions": { "json1": null }, "compile_options": [ "COMPILER=clang-11.0.3", "ENABLE_COLUMN_METADATA", "ENABLE_FTS3", "ENABLE_FTS3_PARENTHESIS", "ENABLE_FTS4", "ENABLE_FTS5", "ENABLE_GEOPOLY", "ENABLE_JSON1", "ENABLE_PREUPDATE_HOOK", "ENABLE_RTREE", "ENABLE_SESSION", "MAX_VARIABLE_NUMBER=250000", "THREADSAFE=1" ] } } You can use the --token TOKEN option to send an API token with the simulated request. Or you can make a request as a specific actor by passing a JSON representation of that actor to --actor : datasette --memory --actor '{"id": "root"}' --get '/-/actor.json' The exit code of datasette --get will be 0 if the request succeeds and 1 if the request produced an HTTP status code other than 200 - e.g. a 404 or 500 error. This lets you use datasette --get / to run tests against a Datasette application in a continuous integration environment such as GitHub Actions. | ["CLI reference", "datasette serve"] | [] |
changelog:v1-0-a11 | changelog | v1-0-a11 | 1.0a11 (2024-02-19) | The "replace": true argument to the /db/table/-/insert API now requires the actor to have the update-row permission. ( #2279 ) Fixed some UI bugs in the interactive permissions debugging tool. ( #2278 ) The column action menu now aligns better with the cog icon, and positions itself taking into account the width of the browser window. ( #2263 ) | ["Changelog"] | [{"href": "https://github.com/simonw/datasette/issues/2279", "label": "#2279"}, {"href": "https://github.com/simonw/datasette/issues/2278", "label": "#2278"}, {"href": "https://github.com/simonw/datasette/issues/2263", "label": "#2263"}] |
full_text_search:full-text-search-table-view-api | full_text_search | full-text-search-table-view-api | The table page and table view API | Table views that support full-text search can be queried using the ?_search=TERMS query string parameter. This will run the search against content from all of the columns that have been included in the index. Try this example: fara.datasettes.com/fara/FARA_All_ShortForms?_search=manafort SQLite full-text search supports wildcards. This means you can easily implement prefix auto-complete by including an asterisk at the end of the search term - for example: /dbname/tablename/?_search=rob* This will return all records containing at least one word that starts with the letters rob . You can also run searches against just the content of a specific named column by using _search_COLNAME=TERMS - for example, this would search for just rows where the name column in the FTS index mentions Sarah : /dbname/tablename/?_search_name=Sarah | ["Full-text search"] | [{"href": "https://fara.datasettes.com/fara/FARA_All_ShortForms?_search=manafort", "label": "fara.datasettes.com/fara/FARA_All_ShortForms?_search=manafort"}] |
changelog:id33 | changelog | id33 | 0.55 (2021-02-18) | Support for cross-database SQL queries and built-in support for serving via HTTPS. The new --crossdb command-line option causes Datasette to attach up to ten database files to the same /_memory database connection. This enables cross-database SQL queries, including the ability to use joins and unions to combine data from tables that exist in different database files. See Cross-database queries for details. ( #283 ) --ssl-keyfile and --ssl-certfile options can be used to specify a TLS certificate, allowing Datasette to serve traffic over https:// without needing to run it behind a separate proxy. ( #1221 ) The /:memory: page has been renamed (and redirected) to /_memory for consistency with the new /_internal database introduced in Datasette 0.54. ( #1205 ) Added plugin testing documentation on Using pdb for errors thrown inside Datasette . ( #1207 ) The official Datasette Docker image now uses Python 3.7.10, applying the latest security fix for that Python version. ( #1235 ) | ["Changelog"] | [{"href": "https://github.com/simonw/datasette/issues/283", "label": "#283"}, {"href": "https://github.com/simonw/datasette/issues/1221", "label": "#1221"}, {"href": "https://github.com/simonw/datasette/issues/1205", "label": "#1205"}, {"href": "https://github.com/simonw/datasette/issues/1207", "label": "#1207"}, {"href": "https://hub.docker.com/r/datasetteproject/datasette", "label": "official Datasette Docker image"}, {"href": "https://www.python.org/downloads/release/python-3710/", "label": "the latest security fix"}, {"href": "https://github.com/simonw/datasette/issues/1235", "label": "#1235"}] |
spatialite:making-use-of-a-spatial-index | spatialite | making-use-of-a-spatial-index | Making use of a spatial index | SpatiaLite spatial indexes are R*Trees. They allow you to run efficient bounding box queries using a sub-select, with a similar pattern to that used for Searches using custom SQL . In the above example, the resulting index will be called idx_museums_point_geom . This takes the form of a SQLite virtual table. You can inspect its contents using the following query: select * from idx_museums_point_geom limit 10; Here's a live example: timezones-api.datasette.io/timezones/idx_timezones_Geometry pkid xmin xmax ymin ymax 1 -8.601725578308105 -2.4930307865142822 4.162120819091797 10.74019718170166 2 … | ["SpatiaLite"] | [{"href": "https://timezones-api.datasette.io/timezones/idx_timezones_Geometry", "label": "timezones-api.datasette.io/timezones/idx_timezones_Geometry"}] |
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"] | [] |
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"] | [] |
sql_queries:fragment | sql_queries | fragment | fragment | Some plugins, such as datasette-vega , can be configured by including additional data in the fragment hash of the URL - the bit that comes after a # symbol. You can set a default fragment hash that will be included in the link to the canned query from the database index page using the "fragment" key. This example demonstrates both fragment and hide_sql : [[[cog config_example(cog, """ databases: fixtures: queries: neighborhood_search: fragment: fragment-goes-here hide_sql: true sql: |- select neighborhood, facet_cities.name, state from facetable join facet_cities on facetable.city_id = facet_cities.id where neighborhood like '%' || :text || '%' order by neighborhood; """) ]]] [[[end]]] See here for a demo of this in action. | ["Running SQL queries", "Canned queries", "Additional canned query options"] | [{"href": "https://github.com/simonw/datasette-vega", "label": "datasette-vega"}, {"href": "https://latest.datasette.io/fixtures#queries", "label": "See here"}] |
plugins:plugins-configuration-secret | plugins | plugins-configuration-secret | Secret configuration values | Some plugins may need configuration that should stay secret - API keys for example. There are two ways in which you can store secret configuration values. As environment variables . If your secret lives in an environment variable that is available to the Datasette process, you can indicate that the configuration value should be read from that environment variable like so: [[[cog config_example(cog, { "plugins": { "datasette-auth-github": { "client_secret": { "$env": "GITHUB_CLIENT_SECRET" } } } }) ]]] [[[end]]] As values in separate files . Your secrets can also live in files on disk. To specify a secret should be read from a file, provide the full file path like this: [[[cog config_example(cog, { "plugins": { "datasette-auth-github": { "client_secret": { "$file": "/secrets/client-secret" } } } }) ]]] [[[end]]] If you are publishing your data using the datasette publish family of commands, you can use the --plugin-secret option to set these secrets at publish time. For example, using Heroku 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 This will set the necessary environment variables and add the following to the deployed metadata.yaml : [[[cog config_example(cog, { "plugins": { "datasette-auth-github": { "client_id": { "$env": "DATASETTE_AUTH_GITHUB_CLIENT_ID" }, "client_secret": { "$env": "DATASETTE_AUTH_GITHUB_CLIENT_SECRET" } … | ["Plugins", "Plugin configuration"] | [] |
contributing:contributing-documentation-cog | contributing | contributing-documentation-cog | Running Cog | Some pages of documentation (in particular the CLI reference ) are automatically updated using Cog . To update these pages, run the following command: cog -r docs/*.rst | ["Contributing", "Editing and building the documentation"] | [{"href": "https://github.com/nedbat/cog", "label": "Cog"}] |
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"}] |
changelog:csrf-protection | changelog | csrf-protection | CSRF protection | Since writable canned queries are built using POST forms, Datasette now ships with CSRF protection ( #798 ). This applies automatically to any POST request, which means plugins need to include a csrftoken in any POST forms that they render. They can do that like so: <input type="hidden" name="csrftoken" value="{{ csrftoken() }}"> | ["Changelog", "0.44 (2020-06-11)"] | [{"href": "https://github.com/simonw/datasette/issues/798", "label": "#798"}] |
introspection:jsondataview-versions | introspection | jsondataview-versions | /-/versions | Shows the version of Datasette, Python and SQLite. Versions example : { "datasette": { "version": "0.60" }, "python": { "full": "3.8.12 (default, Dec 21 2021, 10:45:09) \n[GCC 10.2.1 20210110]", "version": "3.8.12" }, "sqlite": { "extensions": { "json1": null }, "fts_versions": [ "FTS5", "FTS4", "FTS3" ], "compile_options": [ "COMPILER=gcc-6.3.0 20170516", "ENABLE_FTS3", "ENABLE_FTS4", "ENABLE_FTS5", "ENABLE_JSON1", "ENABLE_RTREE", "THREADSAFE=1" ], "version": "3.37.0" } } | ["Introspection"] | [{"href": "https://latest.datasette.io/-/versions", "label": "Versions example"}] |
introspection:jsondataview-actor | introspection | jsondataview-actor | /-/actor | Shows the currently authenticated actor. Useful for debugging Datasette authentication plugins. { "actor": { "id": 1, "username": "some-user" } } | ["Introspection"] | [] |
introspection:jsondataview-metadata | introspection | jsondataview-metadata | /-/metadata | Shows the contents of the metadata.json file that was passed to datasette serve , if any. Metadata example : { "license": "CC Attribution 4.0 License", "license_url": "http://creativecommons.org/licenses/by/4.0/", "source": "fivethirtyeight/data on GitHub", "source_url": "https://github.com/fivethirtyeight/data", "title": "Five Thirty Eight", "databases": { } } | ["Introspection"] | [{"href": "https://fivethirtyeight.datasettes.com/-/metadata", "label": "Metadata example"}] |
introspection:jsondataview-config | introspection | jsondataview-config | /-/config | Shows the configuration for this instance of Datasette. This is generally the contents of the datasette.yaml or datasette.json file, which can include plugin configuration as well. Config example : { "settings": { "template_debug": true, "trace_debug": true, "force_https_urls": true } } Any keys that include the one of the following substrings in their names will be returned as redacted *** output, to help avoid accidentally leaking private configuration information: secret , key , password , token , hash , dsn . | ["Introspection"] | [{"href": "https://latest.datasette.io/-/config", "label": "Config example"}] |
introspection:jsondataview-settings | introspection | jsondataview-settings | /-/settings | Shows the Settings for this instance of Datasette. Settings example : { "default_facet_size": 30, "default_page_size": 100, "facet_suggest_time_limit_ms": 50, "facet_time_limit_ms": 1000, "max_returned_rows": 1000, "sql_time_limit_ms": 1000 } | ["Introspection"] | [{"href": "https://fivethirtyeight.datasettes.com/-/settings", "label": "Settings example"}] |
introspection:jsondataview-threads | introspection | jsondataview-threads | /-/threads | Shows details of threads and asyncio tasks. Threads example : { "num_threads": 2, "threads": [ { "daemon": false, "ident": 4759197120, "name": "MainThread" }, { "daemon": true, "ident": 123145319682048, "name": "Thread-1" }, ], "num_tasks": 3, "tasks": [ "<Task pending coro=<RequestResponseCycle.run_asgi() running at uvicorn/protocols/http/httptools_impl.py:385> cb=[set.discard()]>", "<Task pending coro=<Server.serve() running at uvicorn/main.py:361> wait_for=<Future pending cb=[<TaskWakeupMethWrapper object at 0x10365c3d0>()]> cb=[run_until_complete.<locals>.<lambda>()]>", "<Task pending coro=<LifespanOn.main() running at uvicorn/lifespan/on.py:48> wait_for=<Future pending cb=[<TaskWakeupMethWrapper object at 0x10364f050>()]>>" ] } | ["Introspection"] | [{"href": "https://latest.datasette.io/-/threads", "label": "Threads example"}] |
introspection:jsondataview-databases | introspection | jsondataview-databases | /-/databases | Shows currently attached databases. Databases example : [ { "hash": null, "is_memory": false, "is_mutable": true, "name": "fixtures", "path": "fixtures.db", "size": 225280 } ] | ["Introspection"] | [{"href": "https://latest.datasette.io/-/databases", "label": "Databases example"}] |
introspection:jsondataview-plugins | introspection | jsondataview-plugins | /-/plugins | Shows a list of currently installed plugins and their versions. Plugins example : [ { "name": "datasette_cluster_map", "static": true, "templates": false, "version": "0.10", "hooks": ["extra_css_urls", "extra_js_urls", "extra_body_script"] } ] Add ?all=1 to include details of the default plugins baked into Datasette. | ["Introspection"] | [{"href": "https://san-francisco.datasettes.com/-/plugins", "label": "Plugins example"}] |
cli-reference:cli-help-publish-help | cli-reference | cli-help-publish-help | datasette publish | Shows a list of available deployment targets for publishing data with Datasette. Additional deployment targets can be added by plugins that use the publish_subcommand(publish) hook. [[[cog help(["publish", "--help"]) ]]] Usage: datasette publish [OPTIONS] COMMAND [ARGS]... Publish specified SQLite database files to the internet along with a Datasette-powered interface and API Options: --help Show this message and exit. Commands: cloudrun Publish databases to Datasette running on Cloud Run heroku Publish databases to Datasette running on Heroku [[[end]]] | ["CLI reference"] | [] |
changelog:id39 | changelog | id39 | 0.52.4 (2020-12-05) | Show pysqlite3 version on /-/versions , if installed. ( #1125 ) Errors output by Datasette (e.g. for invalid SQL queries) now go to stderr , not stdout . ( #1131 ) Fix for a startup error on windows caused by unnecessary from os import EX_CANTCREAT - thanks, Abdussamet Koçak. ( #1094 ) | ["Changelog"] | [{"href": "https://github.com/coleifer/pysqlite3", "label": "pysqlite3"}, {"href": "https://github.com/simonw/datasette/issues/1125", "label": "#1125"}, {"href": "https://github.com/simonw/datasette/issues/1131", "label": "#1131"}, {"href": "https://github.com/simonw/datasette/issues/1094", "label": "#1094"}] |
settings:setting-default-allow-sql | settings | setting-default-allow-sql | default_allow_sql | Should users be able to execute arbitrary SQL queries by default? Setting this to off causes permission checks for execute-sql to fail by default. datasette mydatabase.db --setting default_allow_sql off Another way to achieve this is to add "allow_sql": false to your datasette.yaml file, as described in Controlling the ability to execute arbitrary SQL . This setting offers a more convenient way to do this. | ["Settings", "Settings"] | [] |
settings:setting-allow-download | settings | setting-allow-download | allow_download | Should users be able to download the original SQLite database using a link on the database index page? This is turned on by default. However, databases can only be downloaded if they are served in immutable mode and not in-memory. If downloading is unavailable for either of these reasons, the download link is hidden even if allow_download is on. To disable database downloads, use the following: datasette mydatabase.db --setting allow_download off | ["Settings", "Settings"] | [] |
settings:setting-allow-signed-tokens | settings | setting-allow-signed-tokens | allow_signed_tokens | Should users be able to create signed API tokens to access Datasette? This is turned on by default. Use the following to turn it off: datasette mydatabase.db --setting allow_signed_tokens off Turning this setting off will disable the /-/create-token page, described here . It will also cause any incoming Authorization: Bearer dstok_... API tokens to be ignored. | ["Settings", "Settings"] | [] |
settings:setting-suggest-facets | settings | setting-suggest-facets | suggest_facets | Should Datasette calculate suggested facets? On by default, turn this off like so: datasette mydatabase.db --setting suggest_facets off | ["Settings", "Settings"] | [] |
binary_data:binary-plugins | binary_data | binary-plugins | Binary plugins | Several Datasette plugins are available that change the way Datasette treats binary data. datasette-render-binary modifies Datasette's default interface to show an automatic guess at what type of binary data is being stored, along with a visual representation of the binary value that displays ASCII strings directly in the interface. datasette-render-images detects common image formats and renders them as images directly in the Datasette interface. datasette-media allows Datasette interfaces to be configured to serve binary files from configured SQL queries, and includes the ability to resize images directly before serving them. | ["Binary data"] | [{"href": "https://github.com/simonw/datasette-render-binary", "label": "datasette-render-binary"}, {"href": "https://github.com/simonw/datasette-render-images", "label": "datasette-render-images"}, {"href": "https://github.com/simonw/datasette-media", "label": "datasette-media"}] |
configuration:configuration-reference-settings | configuration | configuration-reference-settings | Settings | Settings can be configured in datasette.yaml with the settings key: [[[cog from metadata_doc import config_example import textwrap config_example(cog, textwrap.dedent( """ # inside datasette.yaml settings: default_allow_sql: off default_page_size: 50 """).strip() ) ]]] [[[end]]] The full list of settings is available in the settings documentation . Settings can also be passed to Datasette using one or more --setting name value command line options.` | ["Configuration", null] | [] |
settings:setting-cache-size-kb | settings | setting-cache-size-kb | cache_size_kb | Sets the amount of memory SQLite uses for its per-connection cache , in KB. datasette mydatabase.db --setting cache_size_kb 5000 | ["Settings", "Settings"] | [{"href": "https://www.sqlite.org/pragma.html#pragma_cache_size", "label": "per-connection cache"}] |
changelog:id51 | changelog | id51 | 0.49 (2020-09-14) | See also Datasette 0.49: The annotated release notes . Writable canned queries now expose a JSON API, see JSON API for writable canned queries . ( #880 ) New mechanism for defining page templates with custom path parameters - a template file called pages/about/{slug}.html will be used to render any requests to /about/something . See Path parameters for pages . ( #944 ) register_output_renderer() render functions can now return a Response . ( #953 ) New --upgrade option for datasette install . ( #945 ) New datasette --pdb option. ( #962 ) datasette --get exit code now reflects the internal HTTP status code. ( #947 ) New raise_404() template function for returning 404 errors. ( #964 ) datasette publish heroku now deploys using Python 3.8.5 Upgraded CodeMirror to 5.57.0. ( #948 ) Upgraded code style to Black 20.8b1. ( #958 ) Fixed bug where selected facets were not correctly persisted in hidden form fields on the table page. ( #963 ) Renamed the default error template from 500.html to error.html . Custom error pages are now documented, see Custom error pages . ( #965 ) | ["Changelog"] | [{"href": "https://simonwillison.net/2020/Sep/15/datasette-0-49/", "label": "Datasette 0.49: The annotated release notes"}, {"href": "https://github.com/simonw/datasette/issues/880", "label": "#880"}, {"href": "https://github.com/simonw/datasette/issues/944", "label": "#944"}, {"href": "https://github.com/simonw/datasette/issues/953", "label": "#953"}, {"href": "https://github.com/simonw/datasette/issues/945", "label": "#945"}, {"href": "https://github.com/simonw/datasette/issues/962", "label": "#962"}, {"href": "https://github.com/simonw/datasette/issues/947", "label": "#947"}, {"href": "https://github.com/simonw/datasette/issues/964", "label": "#964"}, {"href": "https://codemirror.net/", "label": "CodeMirror"}, {"href": "https://github.com/simonw/datasette/issues/948", "label": "#948"}, {"href": "https://github.com/simonw/datasette/issues/958", "label": "#958"}, {"href": "https://github.com/simonw/datasette/issues/963", "label": "#963"}, {"href": "https://github.com/simonw/datasette/issues/965", "label": "#965"}] |
changelog:id58 | changelog | id58 | 0.45 (2020-07-01) | See also Datasette 0.45: The annotated release notes . Magic parameters for canned queries, a log out feature, improved plugin documentation and four new plugin hooks. | ["Changelog"] | [{"href": "https://simonwillison.net/2020/Jul/1/datasette-045/", "label": "Datasette 0.45: The annotated release notes"}] |
changelog:id60 | changelog | id60 | 0.44 (2020-06-11) | See also Datasette 0.44: The annotated release notes . Authentication and permissions, writable canned queries, flash messages, new plugin hooks and more. | ["Changelog"] | [{"href": "https://simonwillison.net/2020/Jun/12/annotated-release-notes/", "label": "Datasette 0.44: The annotated release notes"}] |
cli-reference:cli-help-publish-heroku-help | cli-reference | cli-help-publish-heroku-help | datasette publish heroku | See Publishing to Heroku . [[[cog help(["publish", "heroku", "--help"]) ]]] Usage: datasette publish heroku [OPTIONS] [FILES]... Publish databases to Datasette running on Heroku Options: -m, --metadata FILENAME Path to JSON/YAML file containing metadata to publish --extra-options TEXT Extra options to pass to datasette serve --branch TEXT Install datasette from a GitHub branch e.g. main --template-dir DIRECTORY Path to directory containing custom templates --plugins-dir DIRECTORY Path to directory containing custom plugins --static MOUNT:DIRECTORY Serve static files from this directory at /MOUNT/... --install TEXT Additional packages (e.g. plugins) to install --plugin-secret <TEXT TEXT TEXT>... Secrets to pass to plugins, e.g. --plugin- secret datasette-auth-github client_id xxx --version-note TEXT Additional note to show on /-/versions --secret TEXT Secret used for signing secure values, such as signed cookies --title TEXT Title for metadata --license TEXT License label for metadata --license_url TEXT License URL for metadata --source TEXT Source label for metadata --source_url TEXT Source URL for metadata --about TEXT About label for metadata --about_url TEXT About URL for metadata -n, --name TEXT Application name to use when deploying --tar TEXT --tar option to pass to Heroku, e.g. --tar=/usr/local/bin/gtar --generate-dir DIRECTORY Output generated application files and stop without deploying --h… | ["CLI reference"] | [] |
cli-reference:cli-help-publish-cloudrun-help | cli-reference | cli-help-publish-cloudrun-help | datasette publish cloudrun | See Publishing to Google Cloud Run . [[[cog help(["publish", "cloudrun", "--help"]) ]]] Usage: datasette publish cloudrun [OPTIONS] [FILES]... Publish databases to Datasette running on Cloud Run Options: -m, --metadata FILENAME Path to JSON/YAML file containing metadata to publish --extra-options TEXT Extra options to pass to datasette serve --branch TEXT Install datasette from a GitHub branch e.g. main --template-dir DIRECTORY Path to directory containing custom templates --plugins-dir DIRECTORY Path to directory containing custom plugins --static MOUNT:DIRECTORY Serve static files from this directory at /MOUNT/... --install TEXT Additional packages (e.g. plugins) to install --plugin-secret <TEXT TEXT TEXT>... Secrets to pass to plugins, e.g. --plugin- secret datasette-auth-github client_id xxx --version-note TEXT Additional note to show on /-/versions --secret TEXT Secret used for signing secure values, such as signed cookies --title TEXT Title for metadata --license TEXT License label for metadata --license_url TEXT License URL for metadata --source TEXT Source label for metadata --source_url TEXT Source URL for metadata --about TEXT About label for metadata --about_url TEXT About URL for metadata -n, --name TEXT Application name to use when building --service TEXT Cloud Run service to deploy (or over-write) --spatialite Enable SpatialLite extension --show-files Output the generated Dockerfile and metad… | ["CLI reference"] | [] |
changelog:id11 | changelog | id11 | 0.63 (2022-10-27) | See Datasette 0.63: The annotated release notes for more background on the changes in this release. | ["Changelog"] | [{"href": "https://simonwillison.net/2022/Oct/27/datasette-0-63/", "label": "Datasette 0.63: The annotated release notes"}] |
changelog:id183 | changelog | id183 | 0.13 (2017-11-24) | Search now applies to current filters. Combined search into the same form as filters. Closes #133 Much tidier design for table view header. Closes #147 Added ?column__not=blah filter. Closes #148 Row page now resolves foreign keys. Closes #132 Further tweaks to select/input filter styling. Refs #86 - thanks for the help, @natbat! Show linked foreign key in table cells. Added UI for editing table filters. Refs #86 Hide FTS-created tables on index pages. Closes #129 Add publish to heroku support [Jacob Kaplan-Moss] datasette publish heroku mydb.db Pull request #104 Initial implementation of ?_group_count=column . URL shortcut for counting rows grouped by one or more columns. ?_group_count=column1&_group_count=column2 works as well. SQL generated looks like this: select "qSpecies", count(*) as "count" from Street_Tree_List group by "qSpecies" order by "count" desc limit 100 Or for two columns like this: select "qSpecies", "qSiteInfo", count(*) as "count" from Street_Tree_List group by "qSpecies", "qSiteInfo" order by "count" desc limit 100 Refs #44 Added --build=mas… | ["Changelog"] | [{"href": "https://github.com/simonw/datasette/issues/133", "label": "#133"}, {"href": "https://github.com/simonw/datasette/issues/147", "label": "#147"}, {"href": "https://github.com/simonw/datasette/issues/148", "label": "#148"}, {"href": "https://github.com/simonw/datasette/issues/132", "label": "#132"}, {"href": "https://github.com/simonw/datasette/issues/86", "label": "#86"}, {"href": "https://github.com/simonw/datasette/issues/86", "label": "#86"}, {"href": "https://github.com/simonw/datasette/issues/129", "label": "#129"}, {"href": "https://github.com/simonw/datasette/issues/104", "label": "#104"}, {"href": "https://github.com/simonw/datasette/issues/44", "label": "#44"}, {"href": "https://github.com/simonw/datasette/issues/131", "label": "#131"}, {"href": "https://github.com/simonw/datasette/issues/117", "label": "#117"}, {"href": "https://github.com/simonw/datasette/issues/115", "label": "#115"}, {"href": "https://github.com/simonw/datasette/issues/115", "label": "#115"}, {"href": "https://github.com/simonw/datasette/issues/107", "label": "#107"}, {"href": "https://github.com/simonw/datasette/issues/114", "label": "#114"}, {"href": "https://github.com/simonw/datasette/issues/110", "label": "#110"}] |
changelog:binary-data | changelog | binary-data | Binary data | SQLite tables can contain binary data in BLOB columns. Datasette now provides links for users to download this data directly from Datasette, and uses those links to make binary data available from CSV exports. See Binary data for more details. ( #1036 and #1034 ). | ["Changelog", "0.51 (2020-10-31)"] | [{"href": "https://github.com/simonw/datasette/issues/1036", "label": "#1036"}, {"href": "https://github.com/simonw/datasette/issues/1034", "label": "#1034"}] |
binary_data:binary | binary_data | binary | Binary data | SQLite tables can contain binary data in BLOB columns. Datasette includes special handling for these binary values. The Datasette interface detects binary values and provides a link to download their content, for example on https://latest.datasette.io/fixtures/binary_data Binary data is represented in .json exports using Base64 encoding. https://latest.datasette.io/fixtures/binary_data.json?_shape=array [ { "rowid": 1, "data": { "$base64": true, "encoded": "FRwCx60F/g==" } }, { "rowid": 2, "data": { "$base64": true, "encoded": "FRwDx60F/g==" } }, { "rowid": 3, "data": null } ] | [] | [{"href": "https://latest.datasette.io/fixtures/binary_data", "label": "https://latest.datasette.io/fixtures/binary_data"}, {"href": "https://latest.datasette.io/fixtures/binary_data.json?_shape=array", "label": "https://latest.datasette.io/fixtures/binary_data.json?_shape=array"}] |
installation:installation-extensions | installation | installation-extensions | A note about extensions | SQLite supports extensions, such as SpatiaLite for geospatial operations. These can be loaded using the --load-extension argument, like so: datasette --load-extension=/usr/local/lib/mod_spatialite.dylib Some Python installations do not include support for SQLite extensions. If this is the case you will see the following error when you attempt to load an extension: Your Python installation does not have the ability to load SQLite extensions. In some cases you may see the following error message instead: AttributeError: 'sqlite3.Connection' object has no attribute 'enable_load_extension' On macOS the easiest fix for this is to install Datasette using Homebrew: brew install datasette Use which datasette to confirm that datasette will run that version. The output should look something like this: /usr/local/opt/datasette/bin/datasette If you get a different location here such as /Library/Frameworks/Python.framework/Versions/3.10/bin/datasette you can run the following command to cause datasette to execute the Homebrew version instead: alias datasette=$(echo $(brew --prefix datasette)/bin/datasette) You can undo this operation using: unalias datasette If you need to run SQLite with extension support for other Python code, you can do so by install Python itself using Homebrew: brew install python Then executing Python using: /usr/local/opt/python@3/libexec/bin/python A more convenient way to work with this version of Python may be to use it to create a virtual environment: /usr/local/opt/python@3/libexec/bin/python -m venv datasette-venv Then activate it like this: source datasette-venv/bin/activate Now running python and pip will work against a version of … | ["Installation"] | [] |
internals:database-execute-write | internals | database-execute-write | await db.execute_write(sql, params=None, block=True) | SQLite only allows one database connection to write at a time. Datasette handles this for you by maintaining a queue of writes to be executed against a given database. Plugins can submit write operations to this queue and they will be executed in the order in which they are received. This method can be used to queue up a non-SELECT SQL query to be executed against a single write connection to the database. You can pass additional SQL parameters as a tuple or dictionary. The method will block until the operation is completed, and the return value will be the return from calling conn.execute(...) using the underlying sqlite3 Python library. If you pass block=False this behavior changes to "fire and forget" - queries will be added to the write queue and executed in a separate thread while your code can continue to do other things. The method will return a UUID representing the queued task. Each call to execute_write() will be executed inside a transaction. | ["Internals for plugins", "Database class"] | [] |
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"}] |
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"}] |
full_text_search:full-text-search-advanced-queries | full_text_search | full-text-search-advanced-queries | Advanced SQLite search queries | SQLite full-text search includes support for a variety of advanced queries , including AND , OR , NOT and NEAR . By default Datasette disables these features to ensure they do not cause errors or confusion for users who are not aware of them. You can disable this escaping and use the advanced queries by adding &_searchmode=raw to the table page query string. If you want to enable these operators by default for a specific table, you can do so by adding "searchmode": "raw" to the metadata configuration for that table, see Configuring full-text search for a table or view . If that option has been specified in the table metadata but you want to over-ride it and return to the default behavior you can append &_searchmode=escaped to the query string. | ["Full-text search"] | [{"href": "https://www.sqlite.org/fts5.html#full_text_query_syntax", "label": "a variety of advanced queries"}] |
changelog:v0-28-register-output-renderer | changelog | v0-28-register-output-renderer | register_output_renderer plugins | Russ Garrett implemented a new Datasette plugin hook called register_output_renderer ( #441 ) which allows plugins to create additional output renderers in addition to Datasette's default .json and .csv . Russ's in-development datasette-geo plugin includes an example of this hook being used to output .geojson automatically converted from SpatiaLite. | ["Changelog", "0.28 (2019-05-19)"] | [{"href": "https://github.com/simonw/datasette/pull/441", "label": "#441"}, {"href": "https://github.com/russss/datasette-geo", "label": "datasette-geo"}, {"href": "https://github.com/russss/datasette-geo/blob/d4cecc020848bbde91e9e17bf352f7c70bc3dccf/datasette_plugin_geo/geojson.py", "label": "an example"}] |
internals:internals-tracer | internals | internals-tracer | datasette.tracer | Running Datasette with --setting trace_debug 1 enables trace debug output, which can then be viewed by adding ?_trace=1 to the query string for any page. You can see an example of this at the bottom of latest.datasette.io/fixtures/facetable?_trace=1 . The JSON output shows full details of every SQL query that was executed to generate the page. The datasette-pretty-traces plugin can be installed to provide a more readable display of this information. You can see a demo of that here . You can add your own custom traces to the JSON output using the trace() context manager. This takes a string that identifies the type of trace being recorded, and records any keyword arguments as additional JSON keys on the resulting trace object. The start and end time, duration and a traceback of where the trace was executed will be automatically attached to the JSON object. This example uses trace to record the start, end and duration of any HTTP GET requests made using the function: from datasette.tracer import trace import httpx async def fetch_url(url): with trace("fetch-url", url=url): async with httpx.AsyncClient() as client: return await client.get(url) | ["Internals for plugins"] | [{"href": "https://latest.datasette.io/fixtures/facetable?_trace=1", "label": "latest.datasette.io/fixtures/facetable?_trace=1"}, {"href": "https://datasette.io/plugins/datasette-pretty-traces", "label": "datasette-pretty-traces"}, {"href": "https://latest-with-plugins.datasette.io/github/commits?_trace=1", "label": "a demo of that here"}] |
cli-reference:cli-help-help | cli-reference | cli-help-help | datasette --help | Running datasette --help shows a list of all of the available commands. [[[cog help(["--help"]) ]]] Usage: datasette [OPTIONS] COMMAND [ARGS]... Datasette is an open source multi-tool for exploring and publishing data About Datasette: https://datasette.io/ Full documentation: https://docs.datasette.io/ Options: --version Show the version and exit. --help Show this message and exit. Commands: serve* Serve up specified SQLite database files with a web UI create-token Create a signed API token for the specified actor ID inspect Generate JSON summary of provided database files install Install plugins and packages from PyPI into the same... package Package SQLite files into a Datasette Docker container plugins List currently installed plugins publish Publish specified SQLite database files to the internet... uninstall Uninstall plugins and Python packages from the Datasette... [[[end]]] Additional commands added by plugins that use the register_commands(cli) hook will be listed here as well. | ["CLI reference"] | [] |
internals:id1 | internals | id1 | .get_internal_database() | Returns a database object for reading and writing to the private internal database . | ["Internals for plugins", "Datasette class"] | [] |
plugin_hooks:plugin-asgi-wrapper | plugin_hooks | plugin-asgi-wrapper | asgi_wrapper(datasette) | Return an ASGI middleware wrapper function that will be applied to the Datasette ASGI application. This is a very powerful hook. You can use it to manipulate the entire Datasette response, or even to configure new URL routes that will be handled by your own custom code. You can write your ASGI code directly against the low-level specification, or you can use the middleware utilities provided by an ASGI framework such as Starlette . This example plugin adds a x-databases HTTP header listing the currently attached databases: from datasette import hookimpl from functools import wraps @hookimpl def asgi_wrapper(datasette): def wrap_with_databases_header(app): @wraps(app) async def add_x_databases_header( scope, receive, send ): async def wrapped_send(event): if event["type"] == "http.response.start": original_headers = ( event.get("headers") or [] ) event = { "type": event["type"], "status": event["status"], "headers": original_headers + [ [ b"x-databases", ", ".join( datasette.databases.keys() ).encode("utf-8"), ] ], } await send(event) await app(scope, receive, wrapped_send) return add_x_databases_header return wrap_with_databases_header Examples: datasette-cors , datasette-pyinstrument , datasette-total-page-time | ["Plugin hooks"] | [{"href": "https://asgi.readthedocs.io/", "label": "ASGI"}, {"href": "https://www.starlette.io/middleware/", "label": "Starlette"}, {"href": "https://datasette.io/plugins/datasette-cors", "label": "datasette-cors"}, {"href": "https://datasette.io/plugins/datasette-pyinstrument", "label": "datasette-pyinstrument"}, {"href": "https://datasette.io/plugins/datasette-total-page-time", "label": "datasette-total-page-time"}] |
plugin_hooks:plugin-register-facet-classes | plugin_hooks | plugin-register-facet-classes | register_facet_classes() | Return a list of additional Facet subclasses to be registered. The design of this plugin hook is unstable and may change. See issue 830 . Each Facet subclass implements a new type of facet operation. The class should look like this: class SpecialFacet(Facet): # This key must be unique across all facet classes: type = "special" async def suggest(self): # Use self.sql and self.params to suggest some facets suggested_facets = [] suggested_facets.append( { "name": column, # Or other unique name # Construct the URL that will enable this facet: "toggle_url": self.ds.absolute_url( self.request, path_with_added_args( self.request, {"_facet": column} ), ), } ) return suggested_facets async def facet_results(self): # This should execute the facet operation and return results, again # using self.sql and self.params as the starting point facet_results = [] facets_timed_out = [] facet_size = self.get_facet_size() # Do some calculations here... for column in columns_selected_for_facet: try: facet_results_values = [] # More calculations... facet_results_values.append( { "value": value, "label": label, "count": count, "toggle_url": self.ds.absolute_url( self.request, toggle_path ), "selected": selected, } ) facet_results.append( { "name": column, "results": facet_results_values, "trunc… | ["Plugin hooks"] | [{"href": "https://github.com/simonw/datasette/issues/830", "label": "issue 830"}, {"href": "https://github.com/simonw/datasette/blob/main/datasette/facets.py", "label": "datasette/facets.py"}] |
changelog:id154 | changelog | id154 | 0.17 (2018-04-13) | Release 0.17 to fix issues with PyPI | ["Changelog"] | [] |
testing_plugins:testing-plugins-fixtures | testing_plugins | testing-plugins-fixtures | Using pytest fixtures | Pytest fixtures can be used to create initial testable objects which can then be used by multiple tests. A common pattern for Datasette plugins is to create a fixture which sets up a temporary test database and wraps it in a Datasette instance. Here's an example that uses the sqlite-utils library to populate a temporary test database. It also sets the title of that table using a simulated metadata.json configuration: from datasette.app import Datasette import pytest import sqlite_utils @pytest.fixture(scope="session") def datasette(tmp_path_factory): db_directory = tmp_path_factory.mktemp("dbs") db_path = db_directory / "test.db" db = sqlite_utils.Database(db_path) db["dogs"].insert_all( [ {"id": 1, "name": "Cleo", "age": 5}, {"id": 2, "name": "Pancakes", "age": 4}, ], pk="id", ) datasette = Datasette( [db_path], metadata={ "databases": { "test": { "tables": { "dogs": {"title": "Some dogs"} } } } }, ) return datasette @pytest.mark.asyncio async def test_example_table_json(datasette): response = await datasette.client.get( "/test/dogs.json?_shape=array" ) assert response.status_code == 200 assert response.json() == [ {"id": 1, "name": "Cleo", "age": 5}, {"id": 2, "name": "Pancakes", "age": 4}, ] @pytest.mark.asyncio async def test_example_table_html(datasette): response = await datasette.client.get("/test/dogs") assert ">Some dogs</h1>" in response.text Here the datasette() function defines the fixture, which is than automatically passed to the two test functions based on pytest automatically matching their datasette function parameters. The @pytest.fixture(scope="session") line here ensures the fixture is reused for the full pytest execution session. This… | ["Testing plugins"] | [{"href": "https://docs.pytest.org/en/stable/fixture.html", "label": "Pytest fixtures"}, {"href": "https://sqlite-utils.datasette.io/en/stable/python-api.html", "label": "sqlite-utils library"}] |
internals:datasette-permissions | internals | datasette-permissions | .permissions | Property exposing a dictionary of permissions that have been registered using the register_permissions(datasette) plugin hook. The dictionary keys are the permission names - e.g. view-instance - and the values are Permission() objects describing the permission. Here is a description of that object . | ["Internals for plugins", "Datasette class"] | [] |
internals:datasette-databases | internals | datasette-databases | .databases | Property exposing a collections.OrderedDict of databases currently connected to Datasette. The dictionary keys are the name of the database that is used in the URL - e.g. /fixtures would have a key of "fixtures" . The values are Database class instances. All databases are listed, irrespective of user permissions. | ["Internals for plugins", "Datasette class"] | [] |
changelog:authentication | changelog | authentication | Authentication | Prior to this release the Datasette ecosystem has treated authentication as exclusively the realm of plugins, most notably through datasette-auth-github . 0.44 introduces Authentication and permissions as core Datasette concepts ( #699 ). This enables different plugins to share responsibility for authenticating requests - you might have one plugin that handles user accounts and another one that allows automated access via API keys, for example. You'll need to install plugins if you want full user accounts, but default Datasette can now authenticate a single root user with the new --root command-line option, which outputs a one-time use URL to authenticate as a root actor ( #784 ): datasette fixtures.db --root http://127.0.0.1:8001/-/auth-token?token=5b632f8cd44b868df625f5a6e2185d88eea5b22237fd3cc8773f107cc4fd6477 INFO: Started server process [14973] INFO: Waiting for application startup. INFO: Application startup complete. INFO: Uvicorn running on http://127.0.0.1:8001 (Press CTRL+C to quit) Plugins can implement new ways of authenticating users using the new actor_from_request(datasette, request) hook. | ["Changelog", "0.44 (2020-06-11)"] | [{"href": "https://github.com/simonw/datasette-auth-github", "label": "datasette-auth-github"}, {"href": "https://github.com/simonw/datasette/issues/699", "label": "#699"}, {"href": "https://github.com/simonw/datasette/issues/784", "label": "#784"}] |
authentication:authentication-actor-matches-allow | authentication | authentication-actor-matches-allow | actor_matches_allow() | Plugins that wish to implement this same "allow" block permissions scheme can take advantage of the datasette.utils.actor_matches_allow(actor, allow) function: from datasette.utils import actor_matches_allow actor_matches_allow({"id": "root"}, {"id": "*"}) # returns True The currently authenticated actor is made available to plugins as request.actor . | ["Authentication and permissions"] | [] |
writing_plugins:writing-plugins-building-urls | writing_plugins | writing-plugins-building-urls | Building URLs within plugins | Plugins that define their own custom user interface elements may need to link to other pages within Datasette. This can be a bit tricky if the Datasette instance is using the base_url configuration setting to run behind a proxy, since that can cause Datasette's URLs to include an additional prefix. The datasette.urls object provides internal methods for correctly generating URLs to different pages within Datasette, taking any base_url configuration into account. This object is exposed in templates as the urls variable, which can be used like this: Back to the <a href="{{ urls.instance() }}">Homepage</a> See datasette.urls for full details on this object. | ["Writing plugins"] | [] |
writing_plugins:writing-plugins-cookiecutter | writing_plugins | writing-plugins-cookiecutter | Starting an installable plugin using cookiecutter | Plugins that can be installed should be written as Python packages using a setup.py file. The quickest way to start writing one an installable plugin is to use the datasette-plugin cookiecutter template. This creates a new plugin structure for you complete with an example test and GitHub Actions workflows for testing and publishing your plugin. Install cookiecutter and then run this command to start building a plugin using the template: cookiecutter gh:simonw/datasette-plugin Read a cookiecutter template for writing Datasette plugins for more information about this template. | ["Writing plugins"] | [{"href": "https://github.com/simonw/datasette-plugin", "label": "datasette-plugin"}, {"href": "https://cookiecutter.readthedocs.io/en/stable/installation.html", "label": "Install cookiecutter"}, {"href": "https://simonwillison.net/2020/Jun/20/cookiecutter-plugins/", "label": "a cookiecutter template for writing Datasette plugins"}] |
changelog:id69 | changelog | id69 | 0.37 (2020-02-25) | Plugins now have a supported mechanism for writing to a database, using the new .execute_write() and .execute_write_fn() methods. Documentation . ( #682 ) Immutable databases that have had their rows counted using the inspect command now use the calculated count more effectively - thanks, Kevin Keogh. ( #666 ) --reload no longer restarts the server if a database file is modified, unless that database was opened immutable mode with -i . ( #494 ) New ?_searchmode=raw option turns off escaping for FTS queries in ?_search= allowing full use of SQLite's FTS5 query syntax . ( #676 ) | ["Changelog"] | [{"href": "https://github.com/simonw/datasette/issues/682", "label": "#682"}, {"href": "https://github.com/simonw/datasette/pull/666", "label": "#666"}, {"href": "https://github.com/simonw/datasette/issues/494", "label": "#494"}, {"href": "https://www.sqlite.org/fts5.html#full_text_query_syntax", "label": "FTS5 query syntax"}, {"href": "https://github.com/simonw/datasette/issues/676", "label": "#676"}] |
changelog:secret-plugin-configuration-options | changelog | secret-plugin-configuration-options | Secret plugin configuration options | Plugins like datasette-auth-github need a safe way to set secret configuration options. Since the default mechanism for configuring plugins exposes those settings in /-/metadata a new mechanism was needed. Secret configuration values describes how plugins can now specify that their settings should be read from a file or an environment variable: { "plugins": { "datasette-auth-github": { "client_secret": { "$env": "GITHUB_CLIENT_SECRET" } } } } These plugin secrets can be set directly using datasette publish . See Custom metadata and plugins for details. ( #538 and #543 ) | ["Changelog", "0.29 (2019-07-07)"] | [{"href": "https://github.com/simonw/datasette-auth-github", "label": "datasette-auth-github"}, {"href": "https://github.com/simonw/datasette/issues/538", "label": "#538"}, {"href": "https://github.com/simonw/datasette/issues/543", "label": "#543"}] |
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)"] | [] |
changelog:register-routes-plugin-hooks | changelog | register-routes-plugin-hooks | register_routes() plugin hooks | Plugins can now register new views and routes via the register_routes(datasette) plugin hook ( #819 ). View functions can be defined that accept any of the current datasette object, the current request , or the ASGI scope , send and receive objects. | ["Changelog", "0.44 (2020-06-11)"] | [{"href": "https://github.com/simonw/datasette/issues/819", "label": "#819"}] |
internals:internals-datasette-client | internals | internals-datasette-client | datasette.client | Plugins can make internal simulated HTTP requests to the Datasette instance within which they are running. This ensures that all of Datasette's external JSON APIs are also available to plugins, while avoiding the overhead of making an external HTTP call to access those APIs. The datasette.client object is a wrapper around the HTTPX Python library , providing an async-friendly API that is similar to the widely used Requests library . It offers the following methods: await datasette.client.get(path, **kwargs) - returns HTTPX Response Execute an internal GET request against that path. await datasette.client.post(path, **kwargs) - returns HTTPX Response Execute an internal POST request. Use data={"name": "value"} to pass form parameters. await datasette.client.options(path, **kwargs) - returns HTTPX Response Execute an internal OPTIONS request. await datasette.client.head(path, **kwargs) - returns HTTPX Response Execute an internal HEAD request. await datasette.client.put(path, **kwargs) - returns HTTPX Response Execute an internal PUT request. await datasette.client.patch(path, **kwargs) - returns HTTPX Response … | ["Internals for plugins", "Datasette class"] | [{"href": "https://www.python-httpx.org/", "label": "HTTPX Python library"}, {"href": "https://requests.readthedocs.io/", "label": "Requests library"}, {"href": "https://www.python-httpx.org/async/", "label": "HTTPX Async documentation"}] |
plugins:plugins-configuration | plugins | plugins-configuration | Plugin configuration | Plugins can have their own configuration, embedded in a configuration file . Configuration options for plugins live within a "plugins" key in that file, which can be included at the root, database or table level. Here is an example of some plugin configuration for a specific table: [[[cog from metadata_doc import config_example config_example(cog, { "databases": { "sf-trees": { "tables": { "Street_Tree_List": { "plugins": { "datasette-cluster-map": { "latitude_column": "lat", "longitude_column": "lng" } } } } } } }) ]]] [[[end]]] This tells the datasette-cluster-map column which latitude and longitude columns should be used for a table called Street_Tree_List inside a database file called sf-trees.db . | ["Plugins"] | [] |
writing_plugins:writing-plugins-extra-hooks | writing_plugins | writing-plugins-extra-hooks | Plugins that define new plugin hooks | Plugins can define new plugin hooks that other plugins can use to further extend their functionality. datasette-graphql is one example of a plugin that does this. It defines a new hook called graphql_extra_fields , described here , which other plugins can use to define additional fields that should be included in the GraphQL schema. To define additional hooks, add a file to the plugin called datasette_your_plugin/hookspecs.py with content that looks like this: from pluggy import HookspecMarker hookspec = HookspecMarker("datasette") @hookspec def name_of_your_hook_goes_here(datasette): "Description of your hook." You should define your own hook name and arguments here, following the documentation for Pluggy specifications . Make sure to pick a name that is unlikely to clash with hooks provided by any other plugins. Then, to register your plugin hooks, add the following code to your datasette_your_plugin/__init__.py file: from datasette.plugins import pm from . import hookspecs pm.add_hookspecs(hookspecs) This will register your plugin hooks as part of the datasette plugin hook namespace. Within your plugin code you can trigger the hook using this pattern: from datasette.plugins import pm for ( plugin_return_value ) in pm.hook.name_of_your_hook_goes_here( datasette=datasette ): # Do something with plugin_return_value pass Other plugins will then be able to register their own implementations of your hook using this syntax: from datasette import hookimpl @hookimpl def name_of_your_hook_goes_here(datasette): return "Response from this plugin hook" These plugin implementations can accept 0 or more of the named arguments that you defined in your hook specification. | ["Writing plugins"] | [{"href": "https://github.com/simonw/datasette-graphql", "label": "datasette-graphql"}, {"href": "https://github.com/simonw/datasette-graphql/blob/main/README.md#adding-custom-fields-with-plugins", "label": "described here"}, {"href": "https://pluggy.readthedocs.io/en/stable/#specs", "label": "Pluggy specifications"}] |
writing_plugins:writing-plugins-packaging | writing_plugins | writing-plugins-packaging | Packaging a plugin | Plugins can be packaged using Python setuptools. You can see an example of a packaged plugin at https://github.com/simonw/datasette-plugin-demos The example consists of two files: a setup.py file that defines the plugin: from setuptools import setup VERSION = "0.1" setup( name="datasette-plugin-demos", description="Examples of plugins for Datasette", author="Simon Willison", url="https://github.com/simonw/datasette-plugin-demos", license="Apache License, Version 2.0", version=VERSION, py_modules=["datasette_plugin_demos"], entry_points={ "datasette": [ "plugin_demos = datasette_plugin_demos" ] }, install_requires=["datasette"], ) And a Python module file, datasette_plugin_demos.py , that implements the plugin: from datasette import hookimpl import random @hookimpl def prepare_jinja2_environment(env): env.filters["uppercase"] = lambda u: u.upper() @hookimpl def prepare_connection(conn): conn.create_function( "random_integer", 2, random.randint ) Having built a plugin in this way you can turn it into an installable package using the following command: python3 setup.py sdist This will create a .tar.gz file in the dist/ directory. You can then install your new plugin into a Datasette virtual environment or Docker container using pip : pip install datasette-plugin-demos-0.1.tar.gz To learn how to upload your plugin to PyPI for use by other people, read the PyPA guide to Packaging and distributing projects . | ["Writing plugins"] | [{"href": "https://github.com/simonw/datasette-plugin-demos", "label": "https://github.com/simonw/datasette-plugin-demos"}, {"href": "https://pypi.org/", "label": "PyPI"}, {"href": "https://packaging.python.org/tutorials/distributing-packages/", "label": "Packaging and distributing projects"}] |
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"}] |
cli-reference:cli-help-package-help | cli-reference | cli-help-package-help | datasette package | Package SQLite files into a Datasette Docker container, see datasette package . [[[cog help(["package", "--help"]) ]]] Usage: datasette package [OPTIONS] FILES... Package SQLite files into a Datasette Docker container Options: -t, --tag TEXT Name for the resulting Docker container, can optionally use name:tag format -m, --metadata FILENAME Path to JSON/YAML file containing metadata to publish --extra-options TEXT Extra options to pass to datasette serve --branch TEXT Install datasette from a GitHub branch e.g. main --template-dir DIRECTORY Path to directory containing custom templates --plugins-dir DIRECTORY Path to directory containing custom plugins --static MOUNT:DIRECTORY Serve static files from this directory at /MOUNT/... --install TEXT Additional packages (e.g. plugins) to install --spatialite Enable SpatialLite extension --version-note TEXT Additional note to show on /-/versions --secret TEXT Secret used for signing secure values, such as signed cookies -p, --port INTEGER RANGE Port to run the server on, defaults to 8001 [1<=x<=65535] --title TEXT Title for metadata --license TEXT License label for metadata --license_url TEXT License URL for metadata --source TEXT Source label for metadata --source_url TEXT Source URL for metadata --about TEXT About label for metadata --about_url TEXT About URL for metadata --help Show this message and exit. [[[end]]] | ["CLI reference"] | [] |
cli-reference:cli-help-inspect-help | cli-reference | cli-help-inspect-help | datasette inspect | Outputs JSON representing introspected data about one or more SQLite database files. If you are opening an immutable database, you can pass this file to the --inspect-data option to improve Datasette's performance by allowing it to skip running row counts against the database when it first starts running: datasette inspect mydatabase.db > inspect-data.json datasette serve -i mydatabase.db --inspect-file inspect-data.json This performance optimization is used automatically by some of the datasette publish commands. You are unlikely to need to apply this optimization manually. [[[cog help(["inspect", "--help"]) ]]] Usage: datasette inspect [OPTIONS] [FILES]... Generate JSON summary of provided database files This can then be passed to "datasette --inspect-file" to speed up count operations against immutable database files. Options: --inspect-file TEXT --load-extension PATH:ENTRYPOINT? Path to a SQLite extension to load, and optional entrypoint --help Show this message and exit. [[[end]]] | ["CLI reference"] | [] |
cli-reference:cli-help-plugins-help | cli-reference | cli-help-plugins-help | datasette plugins | Output JSON showing all currently installed plugins, their versions, whether they include static files or templates and which Plugin hooks they use. [[[cog help(["plugins", "--help"]) ]]] Usage: datasette plugins [OPTIONS] List currently installed plugins Options: --all Include built-in default plugins --requirements Output requirements.txt of installed plugins --plugins-dir DIRECTORY Path to directory containing custom plugins --help Show this message and exit. [[[end]]] Example output: [ { "name": "datasette-geojson", "static": false, "templates": false, "version": "0.3.1", "hooks": [ "register_output_renderer" ] }, { "name": "datasette-geojson-map", "static": true, "templates": false, "version": "0.4.0", "hooks": [ "extra_body_script", "extra_css_urls", "extra_js_urls" ] }, { "name": "datasette-leaflet", "static": true, "templates": false, "version": "0.2.2", "hooks": [ "extra_body_script", "extra_template_vars" ] } ] | ["CLI reference"] | [] |
deploying:deploying-openrc | deploying | deploying-openrc | Running Datasette using OpenRC | OpenRC is the service manager on non-systemd Linux distributions like Alpine Linux and Gentoo . Create an init script at /etc/init.d/datasette with the following contents: #!/sbin/openrc-run name="datasette" command="datasette" command_args="serve -h 0.0.0.0 /path/to/db.db" command_background=true pidfile="/run/${RC_SVCNAME}.pid" You then need to configure the service to run at boot and start it: rc-update add datasette rc-service datasette start | ["Deploying Datasette"] | [{"href": "https://www.alpinelinux.org/", "label": "Alpine Linux"}, {"href": "https://www.gentoo.org/", "label": "Gentoo"}] |
contributing:contributing-running-tests | contributing | contributing-running-tests | Running the tests | Once you have done this, you can run the Datasette unit tests from inside your datasette/ directory using pytest like so: pytest You can run the tests faster using multiple CPU cores with pytest-xdist like this: pytest -n auto -m "not serial" -n auto detects the number of available cores automatically. The -m "not serial" skips tests that don't work well in a parallel test environment. You can run those tests separately like so: pytest -m "serial" | ["Contributing"] | [{"href": "https://docs.pytest.org/", "label": "pytest"}, {"href": "https://pypi.org/project/pytest-xdist/", "label": "pytest-xdist"}] |
publish:cli-publish | publish | cli-publish | datasette publish | Once you have created a SQLite database (e.g. using csvs-to-sqlite ) you can deploy it to a hosting account using a single command. You will need a hosting account with Heroku or Google Cloud . Once you have created your account you will need to install and configure the heroku or gcloud command-line tools. | ["Publishing data"] | [{"href": "https://github.com/simonw/csvs-to-sqlite/", "label": "csvs-to-sqlite"}, {"href": "https://www.heroku.com/", "label": "Heroku"}, {"href": "https://cloud.google.com/", "label": "Google Cloud"}] |
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"}] |
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: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"}] |
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 );