docs
id | page | ref | title | content | breadcrumbs | references |
---|---|---|---|---|---|---|
settings:setting-max-csv-mb | settings | setting-max-csv-mb | max_csv_mb | The maximum size of CSV that can be exported, in megabytes. Defaults to 100MB. You can disable the limit entirely by settings this to 0: datasette mydatabase.db --setting max_csv_mb 0 | ["Settings", "Settings"] | [] |
settings:setting-max-insert-rows | settings | setting-max-insert-rows | max_insert_rows | Maximum rows that can be inserted at a time using the bulk insert API, see Inserting rows . Defaults to 100. You can increase or decrease this limit like so: datasette mydatabase.db --setting max_insert_rows 1000 | ["Settings", "Settings"] | [] |
settings:setting-max-returned-rows | settings | setting-max-returned-rows | max_returned_rows | Datasette returns a maximum of 1,000 rows of data at a time. If you execute a query that returns more than 1,000 rows, Datasette will return the first 1,000 and include a warning that the result set has been truncated. You can use OFFSET/LIMIT or other methods in your SQL to implement pagination if you need to return more than 1,000 rows. You can increase or decrease this limit like so: datasette mydatabase.db --setting max_returned_rows 2000 | ["Settings", "Settings"] | [] |
settings:setting-max-signed-tokens-ttl | settings | setting-max-signed-tokens-ttl | max_signed_tokens_ttl | Maximum allowed expiry time for signed API tokens created by users. Defaults to 0 which means no limit - tokens can be created that will never expire. Set this to a value in seconds to limit the maximum expiry time. For example, to set that limit to 24 hours you would use: datasette mydatabase.db --setting max_signed_tokens_ttl 86400 This setting is enforced when incoming tokens are processed. | ["Settings", "Settings"] | [] |
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"] | [] |
settings:setting-secret | settings | setting-secret | Configuring the secret | Datasette uses a secret string to sign secure values such as cookies. If you do not provide a secret, Datasette will create one when it starts up. This secret will reset every time the Datasette server restarts though, so things like authentication cookies and API tokens will not stay valid between restarts. You can pass a secret to Datasette in two ways: with the --secret command-line option or by setting a DATASETTE_SECRET environment variable. datasette mydb.db --secret=SECRET_VALUE_HERE Or: export DATASETTE_SECRET=SECRET_VALUE_HERE datasette mydb.db One way to generate a secure random secret is to use Python like this: python3 -c 'import secrets; print(secrets.token_hex(32))' cdb19e94283a20f9d42cca50c5a4871c0aa07392db308755d60a1a5b9bb0fa52 Plugin authors make use of this signing mechanism in their plugins using .sign(value, namespace="default") and .unsign(value, namespace="default") . | ["Settings"] | [] |
settings:setting-sql-time-limit-ms | settings | setting-sql-time-limit-ms | sql_time_limit_ms | By default, queries have a time limit of one second. If a query takes longer than this to run Datasette will terminate the query and return an error. If this time limit is too short for you, you can customize it using the sql_time_limit_ms limit - for example, to increase it to 3.5 seconds: datasette mydatabase.db --setting sql_time_limit_ms 3500 You can optionally set a lower time limit for an individual query using the ?_timelimit=100 query string argument: /my-database/my-table?qSpecies=44&_timelimit=100 This would set the time limit to 100ms for that specific query. This feature is useful if you are working with databases of unknown size and complexity - a query that might make perfect sense for a smaller table could take too long to execute on a table with millions of rows. By setting custom time limits you can execute queries "optimistically" - e.g. give me an exact count of rows matching this query but only if it takes less than 100ms to calculate. | ["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"] | [] |
settings:setting-truncate-cells-html | settings | setting-truncate-cells-html | truncate_cells_html | In the HTML table view, truncate any strings that are longer than this value. The full value will still be available in CSV, JSON and on the individual row HTML page. Set this to 0 to disable truncation. datasette mydatabase.db --setting truncate_cells_html 0 | ["Settings", "Settings"] | [] |
settings:using-setting | settings | using-setting | Using --setting | Datasette supports a number of settings. These can be set using the --setting name value option to datasette serve . You can set multiple settings at once like this: datasette mydatabase.db \ --setting default_page_size 50 \ --setting sql_time_limit_ms 3500 \ --setting max_returned_rows 2000 Settings can also be specified in the database.yaml configuration file . | ["Settings"] | [] |
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"] | [] |
spatialite:querying-polygons-using-within | spatialite | querying-polygons-using-within | Querying polygons using within() | The within() SQL function can be used to check if a point is within a geometry: select name from places where within(GeomFromText('POINT(-3.1724366 51.4704448)'), places.geom); The GeomFromText() function takes a string of well-known text. Note that the order used here is longitude then latitude . To run that same within() query in a way that benefits from the spatial index, use the following: select name from places where within(GeomFromText('POINT(-3.1724366 51.4704448)'), places.geom) and rowid in ( SELECT pkid FROM idx_places_geom where xmin < -3.1724366 and xmax > -3.1724366 and ymin < 51.4704448 and ymax > 51.4704448 ); | ["SpatiaLite"] | [] |
spatialite:spatial-indexing-latitude-longitude-columns | spatialite | spatial-indexing-latitude-longitude-columns | Spatial indexing latitude/longitude columns | Here's a recipe for taking a table with existing latitude and longitude columns, adding a SpatiaLite POINT geometry column to that table, populating the new column and then populating a spatial index: import sqlite3 conn = sqlite3.connect("museums.db") # Lead the spatialite extension: conn.enable_load_extension(True) conn.load_extension("/usr/local/lib/mod_spatialite.dylib") # Initialize spatial metadata for this database: conn.execute("select InitSpatialMetadata(1)") # Add a geometry column called point_geom to our museums table: conn.execute( "SELECT AddGeometryColumn('museums', 'point_geom', 4326, 'POINT', 2);" ) # Now update that geometry column with the lat/lon points conn.execute( """ UPDATE museums SET point_geom = GeomFromText('POINT('||"longitude"||' '||"latitude"||')',4326); """ ) # Now add a spatial index to that column conn.execute( 'select CreateSpatialIndex("museums", "point_geom");' ) # If you don't commit your changes will not be persisted: conn.commit() conn.close() | ["SpatiaLite"] | [] |
spatialite:spatialite-installation | spatialite | spatialite-installation | Installation | ["SpatiaLite"] | [] | |
sql_queries:canned-queries-json-api | sql_queries | canned-queries-json-api | JSON API for writable canned queries | Writable canned queries can also be accessed using a JSON API. You can POST data to them using JSON, and you can request that their response is returned to you as JSON. To submit JSON to a writable canned query, encode key/value parameters as a JSON document: POST /mydatabase/add_message {"message": "Message goes here"} You can also continue to submit data using regular form encoding, like so: POST /mydatabase/add_message message=Message+goes+here There are three options for specifying that you would like the response to your request to return JSON data, as opposed to an HTTP redirect to another page. Set an Accept: application/json header on your request Include ?_json=1 in the URL that you POST to Include "_json": 1 in your JSON body, or &_json=1 in your form encoded body The JSON response will look like this: { "ok": true, "message": "Query executed, 1 row affected", "redirect": "/data/add_name" } The "message" and "redirect" values here will take into account on_success_message , on_success_message_sql , on_success_redirect , on_error_message and on_error_redirect , if they have been set. | ["Running SQL queries", "Canned queries"] | [] |
sql_queries:canned-queries-magic-parameters | sql_queries | canned-queries-magic-parameters | Magic parameters | Named parameters that start with an underscore are special: they can be used to automatically add values created by Datasette that are not contained in the incoming form fields or query string. These magic parameters are only supported for canned queries: to avoid security issues (such as queries that extract the user's private cookies) they are not available to SQL that is executed by the user as a custom SQL query. Available magic parameters are: _actor_* - e.g. _actor_id , _actor_name Fields from the currently authenticated Actors . _header_* - e.g. _header_user_agent Header from the incoming HTTP request. The key should be in lower case and with hyphens converted to underscores e.g. _header_user_agent or _header_accept_language . _cookie_* - e.g. _cookie_lang The value of the incoming cookie of that name. _now_epoch The number of seconds since the Unix epoch. _now_date_utc The date in UTC, e.g. 2020-06-01 _now_datetime_utc The ISO 8601 datetime in UTC, e.g. 2020-06-24T18:01:07Z _random_chars_* - e.g. … | ["Running SQL queries", "Canned queries"] | [] |
sql_queries:canned-queries-options | sql_queries | canned-queries-options | Additional canned query options | Additional options can be specified for canned queries in the YAML or JSON configuration. | ["Running SQL queries", "Canned queries"] | [] |
sql_queries:canned-queries-writable | sql_queries | canned-queries-writable | Writable canned queries | Canned queries by default are read-only. You can use the "write": true key to indicate that a canned query can write to the database. See Access to specific canned queries for details on how to add permission checks to canned queries, using the "allow" key. [[[cog config_example(cog, { "databases": { "mydatabase": { "queries": { "add_name": { "sql": "INSERT INTO names (name) VALUES (:name)", "write": True } } } } }) ]]] [[[end]]] This configuration will create a page at /mydatabase/add_name displaying a form with a name field. Submitting that form will execute the configured INSERT query. You can customize how Datasette represents success and errors using the following optional properties: on_success_message - the message shown when a query is successful on_success_message_sql - alternative to on_success_message : a SQL query that should be executed to generate the message on_success_redirect - the path or URL the user is redirected to on success on_error_message - the message shown when a query throws an error on_error_redirect - the path or URL the user is redirected to on error For example: [[[cog config_example(cog, { "databases": { "mydatabase": { "queries": { "add_name": { "sql": "INSERT INTO names (name) VALUES (:name)", "params": ["name"], "write": Tru… | ["Running SQL queries", "Canned queries"] | [] |
sql_queries:hide-sql | sql_queries | hide-sql | hide_sql | Canned queries default to displaying their SQL query at the top of the page. If the query is extremely long you may want to hide it by default, with a "show" link that can be used to make it visible. Add the "hide_sql": true option to hide the SQL query by default. | ["Running SQL queries", "Canned queries", "Additional canned query options"] | [] |
sql_queries:id1 | sql_queries | id1 | Canned queries | As an alternative to adding views to your database, you can define canned queries inside your datasette.yaml file. Here's an example: [[[cog from metadata_doc import config_example, config_example config_example(cog, { "databases": { "sf-trees": { "queries": { "just_species": { "sql": "select qSpecies from Street_Tree_List" } } } } }) ]]] [[[end]]] Then run Datasette like this: datasette sf-trees.db -m metadata.json Each canned query will be listed on the database index page, and will also get its own URL at: /database-name/canned-query-name For the above example, that URL would be: /sf-trees/just_species You can optionally include "title" and "description" keys to show a title and description on the canned query page. As with regular table metadata you can alternatively specify "description_html" to have your description rendered as HTML (rather than having HTML special characters escaped). | ["Running SQL queries"] | [] |
sql_queries:id2 | sql_queries | id2 | Pagination | Datasette's default table pagination is designed to be extremely efficient. SQL OFFSET/LIMIT pagination can have a significant performance penalty once you get into multiple thousands of rows, as each page still requires the database to scan through every preceding row to find the correct offset. When paginating through tables, Datasette instead orders the rows in the table by their primary key and performs a WHERE clause against the last seen primary key for the previous page. For example: select rowid, * from Tree_List where rowid > 200 order by rowid limit 101 This represents page three for this particular table, with a page size of 100. Note that we request 101 items in the limit clause rather than 100. This allows us to detect if we are on the last page of the results: if the query returns less than 101 rows we know we have reached the end of the pagination set. Datasette will only return the first 100 rows - the 101st is used purely to detect if there should be another page. Since the where clause acts against the index on the primary key, the query is extremely fast even for records that are a long way into the overall pagination set. | ["Running SQL queries"] | [] |
sql_queries:sql | sql_queries | sql | Running SQL queries | Datasette treats SQLite database files as read-only and immutable. This means it is not possible to execute INSERT or UPDATE statements using Datasette, which allows us to expose SELECT statements to the outside world without needing to worry about SQL injection attacks. The easiest way to execute custom SQL against Datasette is through the web UI. The database index page includes a SQL editor that lets you run any SELECT query you like. You can also construct queries using the filter interface on the tables page, then click "View and edit SQL" to open that query in the custom SQL editor. Note that this interface is only available if the execute-sql permission is allowed. See Controlling the ability to execute arbitrary SQL . Any Datasette SQL query is reflected in the URL of the page, allowing you to bookmark them, share them with others and navigate through previous queries using your browser back button. You can also retrieve the results of any query as JSON by adding .json to the base URL. | [] | [] |
testing_plugins:testing-plugins-datasette-test-instance | testing_plugins | testing-plugins-datasette-test-instance | Setting up a Datasette test instance | The above example shows the easiest way to start writing tests against a Datasette instance: from datasette.app import Datasette import pytest @pytest.mark.asyncio async def test_plugin_is_installed(): datasette = Datasette(memory=True) response = await datasette.client.get("/-/plugins.json") assert response.status_code == 200 Creating a Datasette() instance like this as useful shortcut in tests, but there is one detail you need to be aware of. It's important to ensure that the async method .invoke_startup() is called on that instance. You can do that like this: datasette = Datasette(memory=True) await datasette.invoke_startup() This method registers any startup(datasette) or prepare_jinja2_environment(env, datasette) plugins that might themselves need to make async calls. If you are using await datasette.client.get() and similar methods then you don't need to worry about this - Datasette automatically calls invoke_startup() the first time it handles a request. | ["Testing plugins"] | [] |
testing_plugins:testing-plugins-pdb | testing_plugins | testing-plugins-pdb | Using pdb for errors thrown inside Datasette | If an exception occurs within Datasette itself during a test, the response returned to your plugin will have a response.status_code value of 500. You can add pdb=True to the Datasette constructor to drop into a Python debugger session inside your test run instead of getting back a 500 response code. This is equivalent to running the datasette command-line tool with the --pdb option. Here's what that looks like in a test function: def test_that_opens_the_debugger_or_errors(): ds = Datasette([db_path], pdb=True) response = await ds.client.get("/") If you use this pattern you will need to run pytest with the -s option to avoid capturing stdin/stdout in order to interact with the debugger prompt. | ["Testing plugins"] | [] |
testing_plugins:testing-plugins-register-in-test | testing_plugins | testing-plugins-register-in-test | Registering a plugin for the duration of a test | When writing tests for plugins you may find it useful to register a test plugin just for the duration of a single test. You can do this using pm.register() and pm.unregister() like this: from datasette import hookimpl from datasette.app import Datasette from datasette.plugins import pm import pytest @pytest.mark.asyncio async def test_using_test_plugin(): class TestPlugin: __name__ = "TestPlugin" # Use hookimpl and method names to register hooks @hookimpl def register_routes(self): return [ (r"^/error$", lambda: 1 / 0), ] pm.register(TestPlugin(), name="undo") try: # The test implementation goes here datasette = Datasette() response = await datasette.client.get("/error") assert response.status_code == 500 finally: pm.unregister(name="undo") To reuse the same temporary plugin in multiple tests, you can register it inside a fixture in your conftest.py file like this: from datasette import hookimpl from datasette.app import Datasette from datasette.plugins import pm import pytest import pytest_asyncio @pytest_asyncio.fixture async def datasette_with_plugin(): class TestPlugin: __name__ = "TestPlugin" @hookimpl def register_routes(self): return [ (r"^/error$", lambda: 1 / 0), ] pm.register(TestPlugin(), name="undo") try: yield Datasette() finally: pm.unregister(name="undo") Note the yield statement here - this ensures that the finally: block that unregisters the plugin is executed only after the test function itself has completed. Then in a test: @pytest.mark.asyncio async def test_error(datasette_with_plugin): response = await datasette_with_plugin.client.get("/error") assert response.status_code == 500 | ["Testing plugins"] | [] |
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-configuration | writing_plugins | writing-plugins-configuration | Writing plugins that accept configuration | When you are writing plugins, you can access plugin configuration like this using the datasette plugin_config() method. If you know you need plugin configuration for a specific table, you can access it like this: plugin_config = datasette.plugin_config( "datasette-cluster-map", database="sf-trees", table="Street_Tree_List" ) This will return the {"latitude_column": "lat", "longitude_column": "lng"} in the above example. If there is no configuration for that plugin, the method will return None . If it cannot find the requested configuration at the table layer, it will fall back to the database layer and then the root layer. For example, a user may have set the plugin configuration option inside datasette.yaml like so: [[[cog from metadata_doc import metadata_example metadata_example(cog, { "databases": { "sf-trees": { "plugins": { "datasette-cluster-map": { "latitude_column": "xlat", "longitude_column": "xlng" } } } } }) ]]] [[[end]]] In this case, the above code would return that configuration for ANY table within the sf-trees database. The plugin configuration could also be set at the top level of datasette.yaml : [[[cog metadata_example(cog, { "plugins": { "datasette-cluster-map": { "latitude_column": "xlat", "longitude_column": "xlng" } } }) ]]] [[[end]]] Now that datasette-cluster-map plugin configuration will apply to every table in every database. | ["Writing plugins"] | [] |
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"] | [] |
installation:installing-plugins | installation | installing-plugins | Installing plugins | If you want to install plugins into your local Datasette Docker image you can do so using the following recipe. This will install the plugins and then save a brand new local image called datasette-with-plugins : docker run datasetteproject/datasette \ pip install datasette-vega docker commit $(docker ps -lq) datasette-with-plugins You can now run the new custom image like so: docker run -p 8001:8001 -v `pwd`:/mnt \ datasette-with-plugins \ datasette -p 8001 -h 0.0.0.0 /mnt/fixtures.db You can confirm that the plugins are installed by visiting http://127.0.0.1:8001/-/plugins Some plugins such as datasette-ripgrep may need additional system packages. You can install these by running apt-get install inside the container: docker run datasette-057a0 bash -c ' apt-get update && apt-get install ripgrep && pip install datasette-ripgrep' docker commit $(docker ps -lq) datasette-with-ripgrep | ["Installation", "Advanced installation options", "Using Docker"] | [{"href": "http://127.0.0.1:8001/-/plugins", "label": "http://127.0.0.1:8001/-/plugins"}, {"href": "https://datasette.io/plugins/datasette-ripgrep", "label": "datasette-ripgrep"}] |
installation:loading-spatialite | installation | loading-spatialite | Loading SpatiaLite | The datasetteproject/datasette image includes a recent version of the SpatiaLite extension for SQLite. To load and enable that module, use the following command: docker run -p 8001:8001 -v `pwd`:/mnt \ datasetteproject/datasette \ datasette -p 8001 -h 0.0.0.0 /mnt/fixtures.db \ --load-extension=spatialite You can confirm that SpatiaLite is successfully loaded by visiting http://127.0.0.1:8001/-/versions | ["Installation", "Advanced installation options", "Using Docker"] | [{"href": "http://127.0.0.1:8001/-/versions", "label": "http://127.0.0.1:8001/-/versions"}] |
plugin_hooks:plugin-hook-prepare-jinja2-environment | plugin_hooks | plugin-hook-prepare-jinja2-environment | prepare_jinja2_environment(env, datasette) | env - jinja2 Environment The template environment that is being prepared datasette - Datasette class You can use this to access plugin configuration options via datasette.plugin_config(your_plugin_name) This hook is called with the Jinja2 environment that is used to evaluate Datasette HTML templates. You can use it to do things like register custom template filters , for example: from datasette import hookimpl @hookimpl def prepare_jinja2_environment(env): env.filters["uppercase"] = lambda u: u.upper() You can now use this filter in your custom templates like so: Table name: {{ table|uppercase }} This function can return an awaitable function if it needs to run any async code. Examples: datasette-edit-templates | ["Plugin hooks"] | [{"href": "http://jinja.pocoo.org/docs/2.10/api/#custom-filters", "label": "register custom\n template filters"}, {"href": "https://datasette.io/plugins/datasette-edit-templates", "label": "datasette-edit-templates"}] |
getting_started:getting-started-your-computer | getting_started | getting-started-your-computer | Using Datasette on your own computer | First, follow the Installation instructions. Now you can run Datasette against a SQLite file on your computer using the following command: datasette path/to/database.db This will start a web server on port 8001 - visit http://localhost:8001/ to access the web interface. Add -o to open your browser automatically once Datasette has started: datasette path/to/database.db -o Use Chrome on OS X? You can run datasette against your browser history like so: datasette ~/Library/Application\ Support/Google/Chrome/Default/History --nolock The --nolock option ignores any file locks. This is safe as Datasette will open the file in read-only mode. Now visiting http://localhost:8001/History/downloads will show you a web interface to browse your downloads data: http://localhost:8001/History/downloads.json will return that data as JSON: { "database": "History", "columns": [ "id", "current_path", "target_path", "start_time", "received_bytes", "total_bytes", ... ], "rows": [ [ 1, "/Users/simonw/Downloads/DropboxInstaller.dmg", "/Users/simonw/Downloads/DropboxInstaller.dmg", 13097290269022132, 626688, 0, ... ] ] } http://localhost:8001/History/downloads.json?_shape=objects will return that data as JSON in a more convenient format: { ... "rows": [ { "start_time": 13097290269022132, "interrupt_reason": 0, "hash": "", "id": 1, "site_url": "", "referrer": "https://www.dropbox.com/downloading?src=index", ... } ] } | ["Getting started"] | [{"href": "http://localhost:8001/", "label": "http://localhost:8001/"}, {"href": "http://localhost:8001/History/downloads", "label": "http://localhost:8001/History/downloads"}, {"href": "http://localhost:8001/History/downloads.json", "label": "http://localhost:8001/History/downloads.json"}, {"href": "http://localhost:8001/History/downloads.json?_shape=objects", "label": "http://localhost:8001/History/downloads.json?_shape=objects"}] |
writing_plugins:writing-plugins-one-off | writing_plugins | writing-plugins-one-off | Writing one-off plugins | The quickest way to start writing a plugin is to create a my_plugin.py file and drop it into your plugins/ directory. Here is an example plugin, which adds a new custom SQL function called hello_world() which takes no arguments and returns the string Hello world! . from datasette import hookimpl @hookimpl def prepare_connection(conn): conn.create_function( "hello_world", 0, lambda: "Hello world!" ) If you save this in plugins/my_plugin.py you can then start Datasette like this: datasette serve mydb.db --plugins-dir=plugins/ Now you can navigate to http://localhost:8001/mydb and run this SQL: select hello_world(); To see the output of your plugin. | ["Writing plugins"] | [{"href": "http://localhost:8001/mydb", "label": "http://localhost:8001/mydb"}] |
changelog:id88 | changelog | id88 | 0.27 (2019-01-31) | New command: datasette plugins ( documentation ) shows you the currently installed list of plugins. Datasette can now output newline-delimited JSON using the new ?_shape=array&_nl=on query string option. Added documentation on The Datasette Ecosystem . Now using Python 3.7.2 as the base for the official Datasette Docker image . | ["Changelog"] | [{"href": "http://ndjson.org/", "label": "newline-delimited JSON"}, {"href": "https://hub.docker.com/r/datasetteproject/datasette/", "label": "Datasette Docker image"}] |
changelog:id85 | changelog | id85 | 0.28 (2019-05-19) | A salmagundi of new features! | ["Changelog"] | [{"href": "https://adamj.eu/tech/2019/01/18/a-salmagundi-of-django-alpha-announcements/", "label": "salmagundi"}] |
changelog:asgi | changelog | asgi | ASGI | ASGI is the Asynchronous Server Gateway Interface standard. I've been wanting to convert Datasette into an ASGI application for over a year - Port Datasette to ASGI #272 tracks thirteen months of intermittent development - but with Datasette 0.29 the change is finally released. This also means Datasette now runs on top of Uvicorn and no longer depends on Sanic . I wrote about the significance of this change in Porting Datasette to ASGI, and Turtles all the way down . The most exciting consequence of this change is that Datasette plugins can now take advantage of the ASGI standard. | ["Changelog", "0.29 (2019-07-07)"] | [{"href": "https://asgi.readthedocs.io/", "label": "ASGI"}, {"href": "https://github.com/simonw/datasette/issues/272", "label": "Port Datasette to ASGI #272"}, {"href": "https://www.uvicorn.org/", "label": "Uvicorn"}, {"href": "https://github.com/huge-success/sanic", "label": "Sanic"}, {"href": "https://simonwillison.net/2019/Jun/23/datasette-asgi/", "label": "Porting Datasette to ASGI, and Turtles all the way down"}] |
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"}] |
internals:internals-request | internals | internals-request | Request object | The request object is passed to various plugin hooks. It represents an incoming HTTP request. It has the following properties: .scope - dictionary The ASGI scope that was used to construct this request, described in the ASGI HTTP connection scope specification. .method - string The HTTP method for this request, usually GET or POST . .url - string The full URL for this request, e.g. https://latest.datasette.io/fixtures . .scheme - string The request scheme - usually https or http . .headers - dictionary (str -> str) A dictionary of incoming HTTP request headers. Header names have been converted to lowercase. .cookies - dictionary (str -> str) A dictionary of incoming cookies .host - string The host header from the incoming request, e.g. latest.datasette.io or localhost . .path - string The path of the request excluding the query string, e.g. /fixtures . .full_path - string The path of the… | ["Internals for plugins"] | [{"href": "https://asgi.readthedocs.io/en/latest/specs/www.html#connection-scope", "label": "ASGI HTTP connection scope"}] |
plugin_hooks:plugin-hook-skip-csrf | plugin_hooks | plugin-hook-skip-csrf | skip_csrf(datasette, scope) | datasette - Datasette class You can use this to access plugin configuration options via datasette.plugin_config(your_plugin_name) , or to execute SQL queries. scope - dictionary The ASGI scope for the incoming HTTP request. This hook can be used to skip CSRF protection for a specific incoming request. For example, you might have a custom path at /submit-comment which is designed to accept comments from anywhere, whether or not the incoming request originated on the site and has an accompanying CSRF token. This example will disable CSRF protection for that specific URL path: from datasette import hookimpl @hookimpl def skip_csrf(scope): return scope["path"] == "/submit-comment" If any of the currently active skip_csrf() plugin hooks return True , CSRF protection will be skipped for the request. | ["Plugin hooks"] | [{"href": "https://asgi.readthedocs.io/en/latest/specs/www.html#http-connection-scope", "label": "ASGI scope"}] |
installation:installation-homebrew | installation | installation-homebrew | Using Homebrew | If you have a Mac and use Homebrew , you can install Datasette by running this command in your terminal: brew install datasette This should install the latest version. You can confirm by running: datasette --version You can upgrade to the latest Homebrew packaged version using: brew upgrade datasette Once you have installed Datasette you can install plugins using the following: datasette install datasette-vega If the latest packaged release of Datasette has not yet been made available through Homebrew, you can upgrade your Homebrew installation in-place using: datasette install -U datasette | ["Installation", "Basic installation"] | [{"href": "https://brew.sh/", "label": "Homebrew"}] |
spatialite:installing-spatialite-on-os-x | spatialite | installing-spatialite-on-os-x | Installing SpatiaLite on OS X | The easiest way to install SpatiaLite on OS X is to use Homebrew . brew update brew install spatialite-tools This will install the spatialite command-line tool and the mod_spatialite dynamic library. You can now run Datasette like so: datasette --load-extension=spatialite | ["SpatiaLite", "Installation"] | [{"href": "https://brew.sh/", "label": "Homebrew"}] |
plugin_hooks:plugin-hook-register-commands | plugin_hooks | plugin-hook-register-commands | register_commands(cli) | cli - the root Datasette Click command group Use this to register additional CLI commands Register additional CLI commands that can be run using datsette yourcommand ... . This provides a mechanism by which plugins can add new CLI commands to Datasette. This example registers a new datasette verify file1.db file2.db command that checks if the provided file paths are valid SQLite databases: from datasette import hookimpl import click import sqlite3 @hookimpl def register_commands(cli): @cli.command() @click.argument( "files", type=click.Path(exists=True), nargs=-1 ) def verify(files): "Verify that files can be opened by Datasette" for file in files: conn = sqlite3.connect(str(file)) try: conn.execute("select * from sqlite_master") except sqlite3.DatabaseError: raise click.ClickException( "Invalid database: {}".format(file) ) The new command can then be executed like so: datasette verify fixtures.db Help text (from the docstring for the function plus any defined Click arguments or options) will become available using: datasette verify --help Plugins can register multiple commands by making multiple calls to the @cli.command() decorator. Consult the Click documentation for full details on how to build a CLI command, including how to define arguments and options. Note that register_commands() plugins cannot used with the --plugins-dir mechanism - they need to be installed into the same virtual environment as Datasette using pip install . Provided it has a setup.py file (see Packaging a plugin ) you can run pip install directly against the directory in which you are developing your plugin like so: pip install -e path/t… | ["Plugin hooks"] | [{"href": "https://click.palletsprojects.com/en/latest/commands/#callback-invocation", "label": "Click command group"}, {"href": "https://click.palletsprojects.com/", "label": "Click documentation"}, {"href": "https://datasette.io/plugins/datasette-auth-passwords", "label": "datasette-auth-passwords"}, {"href": "https://datasette.io/plugins/datasette-verify", "label": "datasette-verify"}] |
publish:publish-cloud-run | publish | publish-cloud-run | Publishing to Google Cloud Run | Google Cloud Run allows you to publish data in a scale-to-zero environment, so your application will start running when the first request is received and will shut down again when traffic ceases. This means you only pay for time spent serving traffic. Cloud Run is a great option for inexpensively hosting small, low traffic projects - but costs can add up for projects that serve a lot of requests. Be particularly careful if your project has tables with large numbers of rows. Search engine crawlers that index a page for every row could result in a high bill. The datasette-block-robots plugin can be used to request search engine crawlers omit crawling your site, which can help avoid this issue. You will first need to install and configure the Google Cloud CLI tools by following these instructions . You can then publish one or more SQLite database files to Google Cloud Run using the following command: datasette publish cloudrun mydatabase.db --service=my-database A Cloud Run service is a single hosted application. The service name you specify will be used as part of the Cloud Run URL. If you deploy to a service name that you have used in the past your new deployment will replace the previous one. If you omit the --service option you will be asked to pick a service name interactively during the deploy. You may need to interact with prompts from the tool. Many of the prompts ask for values that can be set as properties for the Google Cloud SDK if you want to avoid the prompts. For example, the default region for the deployed instance can be set using the command: gcloud config set run/region us-central1 You should replace us-central1 with your desired region . Alternately, you can specify the region by setting the CLOUDSDK_RUN_REGION environment… | ["Publishing data", "datasette publish"] | [{"href": "https://cloud.google.com/run/", "label": "Google Cloud Run"}, {"href": "https://datasette.io/plugins/datasette-block-robots", "label": "datasette-block-robots"}, {"href": "https://cloud.google.com/sdk/", "label": "these instructions"}, {"href": "https://cloud.google.com/sdk/docs/properties", "label": "set as properties for the Google Cloud SDK"}, {"href": "https://cloud.google.com/about/locations", "label": "region"}, {"href": "https://cloud.google.com/run/docs/mapping-custom-domains", "label": "mapping custom domains"}] |
changelog:v0-28-publish-cloudrun | changelog | v0-28-publish-cloudrun | datasette publish cloudrun | Google Cloud Run is a brand new serverless hosting platform from Google, which allows you to build a Docker container which will run only when HTTP traffic is received and will shut down (and hence cost you nothing) the rest of the time. It's similar to Zeit's Now v1 Docker hosting platform which sadly is no longer accepting signups from new users. The new datasette publish cloudrun command was contributed by Romain Primet ( #434 ) and publishes selected databases to a new Datasette instance running on Google Cloud Run. See Publishing to Google Cloud Run for full documentation. | ["Changelog", "0.28 (2019-05-19)"] | [{"href": "https://cloud.google.com/run/", "label": "Google Cloud Run"}, {"href": "https://hyperion.alpha.spectrum.chat/zeit/now/cannot-create-now-v1-deployments~d206a0d4-5835-4af5-bb5c-a17f0171fb25?m=MTU0Njk2NzgwODM3OA==", "label": "no longer accepting signups"}, {"href": "https://github.com/simonw/datasette/pull/434", "label": "#434"}] |
contributing:contributing-upgrading-codemirror | contributing | contributing-upgrading-codemirror | Upgrading CodeMirror | Datasette bundles CodeMirror for the SQL editing interface, e.g. on this page . Here are the steps for upgrading to a new version of CodeMirror: Install the packages with: npm i codemirror @codemirror/lang-sql Build the bundle using the version number from package.json with: node_modules/.bin/rollup datasette/static/cm-editor-6.0.1.js \ -f iife \ -n cm \ -o datasette/static/cm-editor-6.0.1.bundle.js \ -p @rollup/plugin-node-resolve \ -p @rollup/plugin-terser Update the version reference in the codemirror.html template. | ["Contributing"] | [{"href": "https://codemirror.net/", "label": "CodeMirror"}, {"href": "https://latest.datasette.io/fixtures", "label": "this page"}] |
facets:id1 | facets | id1 | Facets | Datasette facets can be used to add a faceted browse interface to any database table. With facets, tables are displayed along with a summary showing the most common values in specified columns. These values can be selected to further filter the table. Here's an example : Facets can be specified in two ways: using query string parameters, or in metadata.json configuration for the table. | [] | [{"href": "https://congress-legislators.datasettes.com/legislators/legislator_terms?_facet=type&_facet=party&_facet=state&_facet_size=10", "label": "an example"}] |
changelog:id23 | changelog | id23 | 0.59.3 (2021-11-20) | Fixed numerous bugs when running Datasette behind a proxy with a prefix URL path using the base_url setting. A live demo of this mode is now available at datasette-apache-proxy-demo.datasette.io/prefix/ . ( #1519 , #838 ) ?column__arraycontains= and ?column__arraynotcontains= table parameters now also work against SQL views. ( #448 ) ?_facet_array=column no longer returns incorrect counts if columns contain the same value more than once. | ["Changelog"] | [{"href": "https://datasette-apache-proxy-demo.datasette.io/prefix/", "label": "datasette-apache-proxy-demo.datasette.io/prefix/"}, {"href": "https://github.com/simonw/datasette/issues/1519", "label": "#1519"}, {"href": "https://github.com/simonw/datasette/issues/838", "label": "#838"}, {"href": "https://github.com/simonw/datasette/issues/448", "label": "#448"}] |
ecosystem:ecosystem | ecosystem | ecosystem | The Datasette Ecosystem | Datasette sits at the center of a growing ecosystem of open source tools aimed at making it as easy as possible to gather, analyze and publish interesting data. These tools are divided into two main groups: tools for building SQLite databases (for use with Datasette) and plugins that extend Datasette's functionality. The Datasette project website includes a directory of plugins and a directory of tools: Plugins directory on datasette.io Tools directory on datasette.io | [] | [{"href": "https://datasette.io/", "label": "Datasette project website"}, {"href": "https://datasette.io/plugins", "label": "Plugins directory on datasette.io"}, {"href": "https://datasette.io/tools", "label": "Tools directory on datasette.io"}] |
changelog:id37 | changelog | id37 | 0.53 (2020-12-10) | Datasette has an official project website now, at https://datasette.io/ . This release mainly updates the documentation to reflect the new site. New ?column__arraynotcontains= table filter. ( #1132 ) datasette serve has a new --create option, which will create blank database files if they do not already exist rather than exiting with an error. ( #1135 ) New ?_header=off option for CSV export which omits the CSV header row, documented here . ( #1133 ) "Powered by Datasette" link in the footer now links to https://datasette.io/ . ( #1138 ) Project news no longer lives in the README - it can now be found at https://datasette.io/news . ( #1137 ) | ["Changelog"] | [{"href": "https://datasette.io/", "label": "https://datasette.io/"}, {"href": "https://github.com/simonw/datasette/issues/1132", "label": "#1132"}, {"href": "https://github.com/simonw/datasette/issues/1135", "label": "#1135"}, {"href": "https://github.com/simonw/datasette/issues/1133", "label": "#1133"}, {"href": "https://datasette.io/", "label": "https://datasette.io/"}, {"href": "https://github.com/simonw/datasette/issues/1138", "label": "#1138"}, {"href": "https://datasette.io/news", "label": "https://datasette.io/news"}, {"href": "https://github.com/simonw/datasette/issues/1137", "label": "#1137"}] |
installation:installation-datasette-desktop | installation | installation-datasette-desktop | Datasette Desktop for Mac | Datasette Desktop is a packaged Mac application which bundles Datasette together with Python and allows you to install and run Datasette directly on your laptop. This is the best option for local installation if you are not comfortable using the command line. | ["Installation", "Basic installation"] | [{"href": "https://datasette.io/desktop", "label": "Datasette Desktop"}] |
writing_plugins:writing-plugins-designing-urls | writing_plugins | writing-plugins-designing-urls | Designing URLs for your plugin | You can register new URL routes within Datasette using the register_routes(datasette) plugin hook. Datasette's default URLs include these: /dbname - database page /dbname/tablename - table page /dbname/tablename/pk - row page See Pages and API endpoints and Introspection for more default URL routes. To avoid accidentally conflicting with a database file that may be loaded into Datasette, plugins should register URLs using a /-/ prefix. For example, if your plugin adds a new interface for uploading Excel files you might register a URL route like this one: /-/upload-excel Try to avoid registering URLs that clash with other plugins that your users might have installed. There is no central repository of reserved URL paths (yet) but you can review existing plugins by browsing the plugins directory . If your plugin includes functionality that relates to a specific database you could also register a URL route like this: /dbname/-/upload-excel Or for a specific table like this: /dbname/tablename/-/modify-table-schema Note that a row could have a primary key of - and this URL scheme will still work, because Datasette row pages do not ever have a trailing slash followed by additional path components. | ["Writing plugins"] | [{"href": "https://datasette.io/plugins", "label": "plugins directory"}] |
plugin_hooks:plugin-register-output-renderer | plugin_hooks | plugin-register-output-renderer | register_output_renderer(datasette) | datasette - Datasette class You can use this to access plugin configuration options via datasette.plugin_config(your_plugin_name) Registers a new output renderer, to output data in a custom format. The hook function should return a dictionary, or a list of dictionaries, of the following shape: @hookimpl def register_output_renderer(datasette): return { "extension": "test", "render": render_demo, "can_render": can_render_demo, # Optional } This will register render_demo to be called when paths with the extension .test (for example /database.test , /database/table.test , or /database/table/row.test ) are requested. render_demo is a Python function. It can be a regular function or an async def render_demo() awaitable function, depending on if it needs to make any asynchronous calls. can_render_demo is a Python function (or async def function) which accepts the same arguments as render_demo but just returns True or False . It lets Datasette know if the current SQL query can be represented by the plugin - and hence influence if a link to this output format is displayed in the user interface. If you omit the "can_render" key from the dictionary every query will be treated as being supported by the plugin. When a request is received, the "render" callback function is called with zero or more of the following arguments. Datasette will inspect your callback function and pass arguments that match its function signature. datasette - Datasette class For accessing plugin configuration and executing queries. columns - list of strings The names of the columns … | ["Plugin hooks"] | [{"href": "https://datasette.io/plugins/datasette-atom", "label": "datasette-atom"}, {"href": "https://datasette.io/plugins/datasette-ics", "label": "datasette-ics"}, {"href": "https://datasette.io/plugins/datasette-geojson", "label": "datasette-geojson"}, {"href": "https://datasette.io/plugins/datasette-copyable", "label": "datasette-copyable"}] |
plugin_hooks:plugin-register-routes | plugin_hooks | plugin-register-routes | register_routes(datasette) | datasette - Datasette class You can use this to access plugin configuration options via datasette.plugin_config(your_plugin_name) Register additional view functions to execute for specified URL routes. Return a list of (regex, view_function) pairs, something like this: from datasette import hookimpl, Response import html async def hello_from(request): name = request.url_vars["name"] return Response.html( "Hello from {}".format(html.escape(name)) ) @hookimpl def register_routes(): return [(r"^/hello-from/(?P<name>.*)$", hello_from)] The view functions can take a number of different optional arguments. The corresponding argument will be passed to your function depending on its named parameters - a form of dependency injection. The optional view function arguments are as follows: datasette - Datasette class You can use this to access plugin configuration options via datasette.plugin_config(your_plugin_name) , or to execute SQL queries. request - Request object The current HTTP request. scope - dictionary The incoming ASGI scope dictionary. send - function The ASGI send function. receive - function The ASGI receive function. The view funct… | ["Plugin hooks"] | [{"href": "https://datasette.io/plugins/datasette-auth-github", "label": "datasette-auth-github"}, {"href": "https://datasette.io/plugins/datasette-psutil", "label": "datasette-psutil"}] |
changelog:v1-0-a4 | changelog | v1-0-a4 | 1.0a4 (2023-08-21) | This alpha fixes a security issue with the /-/api API explorer. On authenticated Datasette instances (instances protected using plugins such as datasette-auth-passwords ) the API explorer interface could reveal the names of databases and tables within the protected instance. The data stored in those tables was not revealed. For more information and workarounds, read the security advisory . The issue has been present in every previous alpha version of Datasette 1.0: versions 1.0a0, 1.0a1, 1.0a2 and 1.0a3. Also in this alpha: The new datasette plugins --requirements option outputs a list of currently installed plugins in Python requirements.txt format, useful for duplicating that installation elsewhere. ( #2133 ) Writable canned queries can now define a on_success_message_sql field in their configuration, containing a SQL query that should be executed upon successful completion of the write operation in order to generate a message to be shown to the user. ( #2138 ) The automatically generated border color for a database is now shown in more places around the application. ( #2119 ) Every instance of example shell script code in the documentation should now include a working copy button, free from additional syntax. ( #2140 ) | ["Changelog"] | [{"href": "https://datasette.io/plugins/datasette-auth-passwords", "label": "datasette-auth-passwords"}, {"href": "https://github.com/simonw/datasette/security/advisories/GHSA-7ch3-7pp7-7cpq", "label": "the security advisory"}, {"href": "https://github.com/simonw/datasette/issues/2133", "label": "#2133"}, {"href": "https://github.com/simonw/datasette/issues/2138", "label": "#2138"}, {"href": "https://github.com/simonw/datasette/issues/2119", "label": "#2119"}, {"href": "https://github.com/simonw/datasette/issues/2140", "label": "#2140"}] |
plugin_hooks:plugin-hook-actor-from-request | plugin_hooks | plugin-hook-actor-from-request | actor_from_request(datasette, request) | datasette - Datasette class You can use this to access plugin configuration options via datasette.plugin_config(your_plugin_name) , or to execute SQL queries. request - Request object The current HTTP request. This is part of Datasette's authentication and permissions system . The function should attempt to authenticate an actor (either a user or an API actor of some sort) based on information in the request. If it cannot authenticate an actor, it should return None . Otherwise it should return a dictionary representing that actor. Here's an example that authenticates the actor based on an incoming API key: from datasette import hookimpl import secrets SECRET_KEY = "this-is-a-secret" @hookimpl def actor_from_request(datasette, request): authorization = ( request.headers.get("authorization") or "" ) expected = "Bearer {}".format(SECRET_KEY) if secrets.compare_digest(authorization, expected): return {"id": "bot"} If you install this in your plugins directory you can test it like this: curl -H 'Authorization: Bearer this-is-a-secret' http://localhost:8003/-/actor.json Instead of returning a dictionary, this function can return an awaitable function which itself returns either None or a dictionary. This is useful for authentication functions that need to make a database query - for example: from datasette import hookimpl @hookimpl def actor_from_request(datasette, request): async def inner(): token = request.args.get("_token") if not token: return None # Look up ?_token=xxx in sessions table result = await datasette.get_database().execute( "select count(*) from sessions where … | ["Plugin hooks"] | [{"href": "https://datasette.io/plugins/datasette-auth-tokens", "label": "datasette-auth-tokens"}, {"href": "https://datasette.io/plugins/datasette-auth-passwords", "label": "datasette-auth-passwords"}] |
configuration:configuration-cli | configuration | configuration-cli | Configuration via the command-line | The recommended way to configure Datasette is using a datasette.yaml file passed to -c/--config . You can also pass individual settings to Datasette using the -s/--setting option, which can be used multiple times: datasette mydatabase.db \ --setting settings.default_page_size 50 \ --setting settings.sql_time_limit_ms 3500 This option takes dotted-notation for the first argument and a value for the second argument. This means you can use it to set any configuration value that would be valid in a datasette.yaml file. It also works for plugin configuration, for example for datasette-cluster-map : datasette mydatabase.db \ --setting plugins.datasette-cluster-map.latitude_column xlat \ --setting plugins.datasette-cluster-map.longitude_column xlon If the value you provide is a valid JSON object or list it will be treated as nested data, allowing you to configure plugins that accept lists such as datasette-proxy-url : datasette mydatabase.db \ -s plugins.datasette-proxy-url.paths '[{"path": "/proxy", "backend": "http://example.com/"}]' This is equivalent to a datasette.yaml file containing the following: [[[cog from metadata_doc import config_example import textwrap config_example(cog, textwrap.dedent( """ plugins: datasette-proxy-url: paths: - path: /proxy backend: http://example.com/ """).strip() ) ]]] [[[end]]] | ["Configuration"] | [{"href": "https://datasette.io/plugins/datasette-cluster-map", "label": "datasette-cluster-map"}, {"href": "https://datasette.io/plugins/datasette-proxy-url", "label": "datasette-proxy-url"}] |
cli-reference:cli-help-install-help | cli-reference | cli-help-install-help | datasette install | Install new Datasette plugins. This command works like pip install but ensures that your plugins will be installed into the same environment as Datasette. This command: datasette install datasette-cluster-map Would install the datasette-cluster-map plugin. [[[cog help(["install", "--help"]) ]]] Usage: datasette install [OPTIONS] [PACKAGES]... Install plugins and packages from PyPI into the same environment as Datasette Options: -U, --upgrade Upgrade packages to latest version -r, --requirement PATH Install from requirements file -e, --editable TEXT Install a project in editable mode from this path --help Show this message and exit. [[[end]]] | ["CLI reference"] | [{"href": "https://datasette.io/plugins/datasette-cluster-map", "label": "datasette-cluster-map"}] |
plugin_hooks:plugin-hook-extra-body-script | plugin_hooks | plugin-hook-extra-body-script | extra_body_script(template, database, table, columns, view_name, request, datasette) | Extra JavaScript to be added to a <script> block at the end of the <body> element on the page. This takes the same arguments as extra_template_vars(...) The template , database , table and view_name options can be used to return different code depending on which template is being rendered and which database or table are being processed. The datasette instance is provided primarily so that you can consult any plugin configuration options that may have been set, using the datasette.plugin_config(plugin_name) method documented above. This function can return a string containing JavaScript, or a dictionary as described below, or a function or awaitable function that returns a string or dictionary. Use a dictionary if you want to specify that the code should be placed in a <script type="module">...</script> element: @hookimpl def extra_body_script(): return { "module": True, "script": "console.log('Your JavaScript goes here...')", } This will add the following to the end of your page: <script type="module">console.log('Your JavaScript goes here...')</script> Example: datasette-cluster-map | ["Plugin hooks", "Page extras"] | [{"href": "https://datasette.io/plugins/datasette-cluster-map", "label": "datasette-cluster-map"}] |
plugin_hooks:plugin-hook-query-actions | plugin_hooks | plugin-hook-query-actions | query_actions(datasette, actor, database, query_name, request, sql, params) | datasette - Datasette class You can use this to access plugin configuration options via datasette.plugin_config(your_plugin_name) , or to execute SQL queries. actor - dictionary or None The currently authenticated actor . database - string The name of the database. query_name - string or None The name of the canned query, or None if this is an arbitrary SQL query. request - Request object The current HTTP request. sql - string The SQL query being executed params - dictionary The parameters passed to the SQL query, if any. Populates a "Query actions" menu on the canned query and arbitrary SQL query pages. This example adds a new query action linking to a page for explaining a query: from datasette import hookimpl import urllib @hookimpl def query_actions(datasette, database, query_name, sql): # Don't explain an explain if sql.lower().startswith("explain"): return return [ { "href": datasette.u… | ["Plugin hooks", "Action hooks"] | [{"href": "https://datasette.io/plugins/datasette-create-view", "label": "datasette-create-view"}] |
plugin_hooks:plugin-hook-row-actions | plugin_hooks | plugin-hook-row-actions | row_actions(datasette, actor, request, database, table, row) | datasette - Datasette class You can use this to access plugin configuration options via datasette.plugin_config(your_plugin_name) , or to execute SQL queries. actor - dictionary or None The currently authenticated actor . request - Request object or None The current HTTP request. database - string The name of the database. table - string The name of the table. row - sqlite.Row The SQLite row object being displayed on the page. Return links for the "Row actions" menu shown at the top of the row page. This example displays the row in JSON plus some additional debug information if the user is signed in: from datasette import hookimpl @hookimpl def row_actions(datasette, database, table, actor, row): if actor: return [ { "href": datasette.urls.instance(), "label": f"Row details for {actor['id']}", "description": json.dumps( dict(row), default=repr ), }, ] Example: datasette-enrichments | ["Plugin hooks", "Action hooks"] | [{"href": "https://datasette.io/plugins/datasette-enrichments", "label": "datasette-enrichments"}] |
plugin_hooks:plugin-hook-track-event | plugin_hooks | plugin-hook-track-event | track_event(datasette, event) | datasette - Datasette class You can use this to access plugin configuration options via datasette.plugin_config(your_plugin_name) . event - Event Information about the event, represented as an instance of a subclass of the Event base class. This hook will be called any time an event is tracked by code that calls the datasette.track_event(...) internal method. The event object will always have the following properties: name : a string representing the name of the event, for example logout or create-table . actor : a dictionary representing the actor that triggered the event, or None if the event was not triggered by an actor. created : a datatime.datetime object in the timezone.utc timezone representing the time the event object was created. Other properties on the event will be available depending on the type of event. You can also access those as a dictionary using event.properties() . The events fired by Datasette core are documented here . This example plugin logs details of all events to standard error: from datasette import hookimpl import json import sys @hookimpl def track_event(event): name = event.name actor = event.actor properties = event.properties() msg = json.dumps( { "name": name, "actor": actor, "properties": properties, } ) print(msg, file=sys.stderr, flush=True) T… | ["Plugin hooks", "Event tracking"] | [{"href": "https://datasette.io/plugins/datasette-events-db", "label": "datasette-events-db"}] |
plugin_hooks:plugin-hook-database-actions | plugin_hooks | plugin-hook-database-actions | database_actions(datasette, actor, database, request) | datasette - Datasette class You can use this to access plugin configuration options via datasette.plugin_config(your_plugin_name) , or to execute SQL queries. actor - dictionary or None The currently authenticated actor . database - string The name of the database. request - Request object The current HTTP request. Populates an actions menu on the database page. This example adds a new database action for creating a table, if the user has the edit-schema permission: from datasette import hookimpl @hookimpl def database_actions(datasette, actor, database): async def inner(): if not await datasette.permission_allowed( actor, "edit-schema", resource=database, default=False, ): return [] return [ { "href": datasette.urls.path( "/-/edit-schema/{}/-/create".format( database ) ), "label": "Create a table", } ] return inner Example: datasette-graphql , datasette-edit-schema | ["Plugin hooks", "Action hooks"] | [{"href": "https://datasette.io/plugins/datasette-graphql", "label": "datasette-graphql"}, {"href": "https://datasette.io/plugins/datasette-edit-schema", "label": "datasette-edit-schema"}] |
plugin_hooks:plugin-hook-table-actions | plugin_hooks | plugin-hook-table-actions | table_actions(datasette, actor, database, table, request) | datasette - Datasette class You can use this to access plugin configuration options via datasette.plugin_config(your_plugin_name) , or to execute SQL queries. actor - dictionary or None The currently authenticated actor . database - string The name of the database. table - string The name of the table. request - Request object or None The current HTTP request. This can be None if the request object is not available. This example adds a new table action if the signed in user is "root" : from datasette import hookimpl @hookimpl def table_actions(datasette, actor, database, table): if actor and actor.get("id") == "root": return [ { "href": datasette.urls.path( "/-/edit-schema/{}/{}".format( database, table ) ), "label": "Edit schema for this table", "description": "Add, remove, rename or alter columns for this table.", } ] Example: datasette-graphql | ["Plugin hooks", "Action hooks"] | [{"href": "https://datasette.io/plugins/datasette-graphql", "label": "datasette-graphql"}] |
changelog:id17 | changelog | id17 | 0.61.1 (2022-03-23) | Fixed a bug where databases with a different route from their name (as used by the datasette-hashed-urls plugin ) returned errors when executing custom SQL queries. ( #1682 ) | ["Changelog"] | [{"href": "https://datasette.io/plugins/datasette-hashed-urls", "label": "datasette-hashed-urls plugin"}, {"href": "https://github.com/simonw/datasette/issues/1682", "label": "#1682"}] |
performance:performance-hashed-urls | performance | performance-hashed-urls | datasette-hashed-urls | If you open a database file in immutable mode using the -i option, you can be assured that the content of that database will not change for the lifetime of the Datasette server. The datasette-hashed-urls plugin implements an optimization where your database is served with part of the SHA-256 hash of the database contents baked into the URL. A database at /fixtures will instead be served at /fixtures-aa7318b , and a year-long cache expiry header will be returned with those pages. This will then be cached by both browsers and caching proxies such as Cloudflare or Fastly, providing a potentially significant performance boost. To install the plugin, run the following: datasette install datasette-hashed-urls Prior to Datasette 0.61 hashed URL mode was a core Datasette feature, enabled using the hash_urls setting. This implementation has now been removed in favor of the datasette-hashed-urls plugin. Prior to Datasette 0.28 hashed URL mode was the default behaviour for Datasette, since all database files were assumed to be immutable and unchanging. From 0.28 onwards the default has been to treat database files as mutable unless explicitly configured otherwise. | ["Performance and caching"] | [{"href": "https://datasette.io/plugins/datasette-hashed-urls", "label": "datasette-hashed-urls plugin"}] |
plugin_hooks:plugin-hook-filters-from-request | plugin_hooks | plugin-hook-filters-from-request | filters_from_request(request, database, table, datasette) | request - Request object The current HTTP request. database - string The name of the database. table - string The name of the table. datasette - Datasette class You can use this to access plugin configuration options via datasette.plugin_config(your_plugin_name) , or to execute SQL queries. This hook runs on the table page, and can influence the where clause of the SQL query used to populate that page, based on query string arguments on the incoming request. The hook should return an instance of datasette.filters.FilterArguments which has one required and three optional arguments: return FilterArguments( where_clauses=["id > :max_id"], params={"max_id": 5}, human_descriptions=["max_id is greater than 5"], extra_context={}, ) The arguments to the FilterArguments class constructor are as follows: where_clauses - list of strings, required A list of SQL fragments that will be inserted into the SQL query, joined by the and operator. These can include :named parameters which will be populated using data in params . params - dictionary, optional Additional keyword arguments to be used when the query is executed. These should match any :arguments in the where clauses. … | ["Plugin hooks"] | [{"href": "https://datasette.io/plugins/datasette-leaflet-freedraw", "label": "datasette-leaflet-freedraw"}] |
plugin_hooks:plugin-hook-permission-allowed | plugin_hooks | plugin-hook-permission-allowed | permission_allowed(datasette, actor, action, resource) | datasette - Datasette class You can use this to access plugin configuration options via datasette.plugin_config(your_plugin_name) , or to execute SQL queries. actor - dictionary The current actor, as decided by actor_from_request(datasette, request) . action - string The action to be performed, e.g. "edit-table" . resource - string or None An identifier for the individual resource, e.g. the name of the table. Called to check that an actor has permission to perform an action on a resource. Can return True if the action is allowed, False if the action is not allowed or None if the plugin does not have an opinion one way or the other. Here's an example plugin which randomly selects if a permission should be allowed or denied, except for view-instance which always uses the default permission scheme instead. from datasette import hookimpl import random @hookimpl def permission_allowed(action): if action != "view-instance": # Return True or False at random return random.random() > 0.5 # Returning None falls back to default permissions This function can alternatively return an awaitable function which itself returns True , False or None . You can use this option if you need to execute additional database queries using await datasette.execute(...) . Here's an example that allows users to view the admin_log table only if their actor id is present in the admin_users table. It aso disallows arbitrary SQL queries for the staff… | ["Plugin hooks"] | [{"href": "https://datasette.io/plugins/datasette-permissions-sql", "label": "datasette-permissions-sql"}] |
plugin_hooks:plugin-hook-render-cell | plugin_hooks | plugin-hook-render-cell | render_cell(row, value, column, table, database, datasette, request) | Lets you customize the display of values within table cells in the HTML table view. row - sqlite.Row The SQLite row object that the value being rendered is part of value - string, integer, float, bytes or None The value that was loaded from the database column - string The name of the column being rendered table - string or None The name of the table - or None if this is a custom SQL query database - string The name of the database datasette - Datasette class You can use this to access plugin configuration options via datasette.plugin_config(your_plugin_name) , or to execute SQL queries. request - Request object The current request object If your hook returns None , it will be ignored. Use this to indicate that your hook is not able to custom render this particular value. If the hook returns a string, that string will be rendered in the table cell. If you want to return HTML markup you can do so by returning a jinja2.Markup object. You can also return an awaitable function which returns a value. Datasette will loop through… | ["Plugin hooks"] | [{"href": "https://datasette.io/plugins/datasette-render-binary", "label": "datasette-render-binary"}, {"href": "https://datasette.io/plugins/datasette-render-markdown", "label": "datasette-render-markdown"}, {"href": "https://datasette.io/plugins/datasette-json-html", "label": "datasette-json-html"}] |
plugin_hooks:plugin-hook-startup | plugin_hooks | plugin-hook-startup | startup(datasette) | This hook fires when the Datasette application server first starts up. Here is an example that validates required plugin configuration. The server will fail to start and show an error if the validation check fails: @hookimpl def startup(datasette): config = datasette.plugin_config("my-plugin") or {} assert ( "required-setting" in config ), "my-plugin requires setting required-setting" You can also return an async function, which will be awaited on startup. Use this option if you need to execute any database queries, for example this function which creates the my_table database table if it does not yet exist: @hookimpl def startup(datasette): async def inner(): db = datasette.get_database() if "my_table" not in await db.table_names(): await db.execute_write( """ create table my_table (mycol text) """ ) return inner Potential use-cases: Run some initialization code for the plugin Create database tables that a plugin needs on startup Validate the configuration for a plugin on startup, and raise an error if it is invalid If you are writing unit tests for a plugin that uses this hook and doesn't exercise Datasette by sending any simulated requests through it you will need to explicitly call await ds.invoke_startup() in your tests. An example: @pytest.mark.asyncio async def test_my_plugin(): ds = Datasette() await ds.invoke_startup() # Rest of test goes here Examples: datasette-saved-queries , datasette-init | ["Plugin hooks"] | [{"href": "https://datasette.io/plugins/datasette-saved-queries", "label": "datasette-saved-queries"}, {"href": "https://datasette.io/plugins/datasette-init", "label": "datasette-init"}] |
plugin_hooks:plugin-hook-canned-queries | plugin_hooks | plugin-hook-canned-queries | canned_queries(datasette, database, actor) | datasette - Datasette class You can use this to access plugin configuration options via datasette.plugin_config(your_plugin_name) , or to execute SQL queries. database - string The name of the database. actor - dictionary or None The currently authenticated actor . Use this hook to return a dictionary of additional canned query definitions for the specified database. The return value should be the same shape as the JSON described in the canned query documentation. from datasette import hookimpl @hookimpl def canned_queries(datasette, database): if database == "mydb": return { "my_query": { "sql": "select * from my_table where id > :min_id" } } The hook can alternatively return an awaitable function that returns a list. Here's an example that returns queries that have been stored in the saved_queries database table, if one exists: from datasette import hookimpl @hookimpl def canned_queries(datasette, database): async def inner(): db = datasette.get_database(database) if await db.table_exists("saved_queries"): results = await db.execute( "select name, sql from saved_queries" ) return { result["name"]: {"sql": result["sql"]} for result in results } return inner The actor parameter can be used to include the currently authenticated actor in your decision. Here's an example that returns saved queries that were saved by that actor: from datasette import hookimpl @hookimpl def canned_queries… | ["Plugin hooks"] | [{"href": "https://datasette.io/plugins/datasette-saved-queries", "label": "datasette-saved-queries"}] |
plugin_hooks:plugin-hook-menu-links | plugin_hooks | plugin-hook-menu-links | menu_links(datasette, actor, request) | datasette - Datasette class You can use this to access plugin configuration options via datasette.plugin_config(your_plugin_name) , or to execute SQL queries. actor - dictionary or None The currently authenticated actor . request - Request object or None The current HTTP request. This can be None if the request object is not available. This hook allows additional items to be included in the menu displayed by Datasette's top right menu icon. The hook should return a list of {"href": "...", "label": "..."} menu items. These will be added to the menu. It can alternatively return an async def awaitable function which returns a list of menu items. This example adds a new menu item but only if the signed in user is "root" : from datasette import hookimpl @hookimpl def menu_links(datasette, actor): if actor and actor.get("id") == "root": return [ { "href": datasette.urls.path( "/-/edit-schema" ), "label": "Edit schema", }, ] Using datasette.urls here ensures that links in the menu will take the base_url setting into account. Examples: datasette-search-all , datasette-graphql | ["Plugin hooks"] | [{"href": "https://datasette.io/plugins/datasette-search-all", "label": "datasette-search-all"}, {"href": "https://datasette.io/plugins/datasette-graphql", "label": "datasette-graphql"}] |
getting_started:getting-started-tutorial | getting_started | getting-started-tutorial | Follow a tutorial | Datasette has several tutorials to help you get started with the tool. Try one of the following: Exploring a database with Datasette shows how to use the Datasette web interface to explore a new database. Learn SQL with Datasette introduces SQL, and shows how to use that query language to ask questions of your data. Cleaning data with sqlite-utils and Datasette guides you through using sqlite-utils to turn a CSV file into a database that you can explore using Datasette. | ["Getting started"] | [{"href": "https://datasette.io/tutorials", "label": "tutorials"}, {"href": "https://datasette.io/tutorials/explore", "label": "Exploring a database with Datasette"}, {"href": "https://datasette.io/tutorials/learn-sql", "label": "Learn SQL with Datasette"}, {"href": "https://datasette.io/tutorials/clean-data", "label": "Cleaning data with sqlite-utils and Datasette"}, {"href": "https://sqlite-utils.datasette.io/", "label": "sqlite-utils"}] |
changelog: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"}] |
internals:internals-tilde-encoding | internals | internals-tilde-encoding | Tilde encoding | Datasette uses a custom encoding scheme in some places, called tilde encoding . This is primarily used for table names and row primary keys, to avoid any confusion between / characters in those values and the Datasette URLs that reference them. Tilde encoding uses the same algorithm as URL percent-encoding , but with the ~ tilde character used in place of % . Any character other than ABCDEFGHIJKLMNOPQRSTUVWXYZ abcdefghijklmnopqrstuvwxyz0123456789_- will be replaced by the numeric equivalent preceded by a tilde. For example: / becomes ~2F . becomes ~2E % becomes ~25 ~ becomes ~7E Space becomes + polls/2022.primary becomes polls~2F2022~2Eprimary Note that the space character is a special case: it will be replaced with a + symbol. datasette.utils. tilde_encode s : str str Returns tilde-encoded string - for example /foo/bar -> ~2Ffoo~2Fbar datasette.utils. tilde_decode s : str str Decodes a tilde-encoded string, so ~2Ffoo~2Fbar -> /foo/bar | ["Internals for plugins", "The datasette.utils module"] | [{"href": "https://developer.mozilla.org/en-US/docs/Glossary/percent-encoding", "label": "URL percent-encoding"}] |
plugin_hooks:plugin-hook-extra-js-urls | plugin_hooks | plugin-hook-extra-js-urls | extra_js_urls(template, database, table, columns, view_name, request, datasette) | This takes the same arguments as extra_template_vars(...) This works in the same way as extra_css_urls() but for JavaScript. You can return a list of URLs, a list of dictionaries or an awaitable function that returns those things: from datasette import hookimpl @hookimpl def extra_js_urls(): return [ { "url": "https://code.jquery.com/jquery-3.3.1.slim.min.js", "sri": "sha384-q8i/X+965DzO0rT7abK41JStQIAqVgRVzpbzo5smXKp4YfRvH+8abtTE1Pi6jizo", } ] You can also return URLs to files from your plugin's static/ directory, if you have one: @hookimpl def extra_js_urls(): return ["/-/static-plugins/your-plugin/app.js"] Note that your-plugin here should be the hyphenated plugin name - the name that is displayed in the list on the /-/plugins debug page. If your code uses JavaScript modules you should include the "module": True key. See Custom CSS and JavaScript for more details. @hookimpl def extra_js_urls(): return [ { "url": "/-/static-plugins/your-plugin/app.js", "module": True, } ] Examples: datasette-cluster-map , datasette-vega | ["Plugin hooks", "Page extras"] | [{"href": "https://developer.mozilla.org/en-US/docs/Web/JavaScript/Guide/Modules", "label": "JavaScript modules"}, {"href": "https://datasette.io/plugins/datasette-cluster-map", "label": "datasette-cluster-map"}, {"href": "https://datasette.io/plugins/datasette-vega", "label": "datasette-vega"}] |
changelog:javascript-modules | changelog | javascript-modules | JavaScript modules | JavaScript modules were introduced in ECMAScript 2015 and provide native browser support for the import and export keywords. To use modules, JavaScript needs to be included in <script> tags with a type="module" attribute. Datasette now has the ability to output <script type="module"> in places where you may wish to take advantage of modules. The extra_js_urls option described in Custom CSS and JavaScript can now be used with modules, and module support is also available for the extra_body_script() plugin hook. ( #1186 , #1187 ) datasette-leaflet-freedraw is the first example of a Datasette plugin that takes advantage of the new support for JavaScript modules. See Drawing shapes on a map to query a SpatiaLite database for more on this plugin. | ["Changelog", "0.54 (2021-01-25)"] | [{"href": "https://developer.mozilla.org/en-US/docs/Web/JavaScript/Guide/Modules", "label": "JavaScript modules"}, {"href": "https://github.com/simonw/datasette/issues/1186", "label": "#1186"}, {"href": "https://github.com/simonw/datasette/issues/1187", "label": "#1187"}, {"href": "https://datasette.io/plugins/datasette-leaflet-freedraw", "label": "datasette-leaflet-freedraw"}, {"href": "https://simonwillison.net/2021/Jan/24/drawing-shapes-spatialite/", "label": "Drawing shapes on a map to query a SpatiaLite database"}] |
changelog:id52 | changelog | id52 | 0.48 (2020-08-16) | Datasette documentation now lives at docs.datasette.io . db.is_mutable property is now documented and tested, see Database introspection . The extra_template_vars , extra_css_urls , extra_js_urls and extra_body_script plugin hooks now all accept the same arguments. See extra_template_vars(template, database, table, columns, view_name, request, datasette) for details. ( #939 ) Those hooks now accept a new columns argument detailing the table columns that will be rendered on that page. ( #938 ) Fixed bug where plugins calling db.execute_write_fn() could hang Datasette if the connection failed. ( #935 ) Fixed bug with the ?_nl=on output option and binary data. ( #914 ) | ["Changelog"] | [{"href": "https://docs.datasette.io/", "label": "docs.datasette.io"}, {"href": "https://github.com/simonw/datasette/issues/939", "label": "#939"}, {"href": "https://github.com/simonw/datasette/issues/938", "label": "#938"}, {"href": "https://github.com/simonw/datasette/issues/935", "label": "#935"}, {"href": "https://github.com/simonw/datasette/issues/914", "label": "#914"}] |
changelog:id174 | changelog | id174 | 0.14 (2017-12-09) | The theme of this release is customization: Datasette now allows every aspect of its presentation to be customized either using additional CSS or by providing entirely new templates. Datasette's metadata.json format has also been expanded, to allow per-database and per-table metadata. A new datasette skeleton command can be used to generate a skeleton JSON file ready to be filled in with per-database and per-table details. The metadata.json file can also be used to define canned queries , as a more powerful alternative to SQL views. extra_css_urls / extra_js_urls in metadata A mechanism in the metadata.json format for adding custom CSS and JS urls. Create a metadata.json file that looks like this: { "extra_css_urls": [ "https://simonwillison.net/static/css/all.bf8cd891642c.css" ], "extra_js_urls": [ "https://code.jquery.com/jquery-3.2.1.slim.min.js" ] } Then start datasette like this: datasette mydb.db --metadata=metadata.json The CSS and JavaScript files will be linked in the <head> of every page. You can also specify a SRI (subresource integrity hash) for these assets: { "extra_css_urls": [ { "url": "https://simonwillison.net/static/css/all.bf8cd891642c.css", "sri": "sha384-9qIZekWUyjCyDIf2YK1FRoKiPJq4PHt6tp/ulnuuyRBvazd0hG7pWbE99zvwSznI" } ], "extra_js_urls": [ { "url": "https://code.jquery.com/jquery-3.2.1.slim.min.js", "sri": "sha256-k2WSCIexGzOj3Euiig+TlR8gA0EmPjuc79OEeY5L45g=" } ] } Modern browsers will only execute the stylesheet or JavaScript if the SRI hash … | ["Changelog"] | [{"href": "https://docs.datasette.io/en/stable/custom_templates.html", "label": "to be customized"}, {"href": "https://docs.datasette.io/en/stable/metadata.html", "label": "metadata.json format"}, {"href": "https://docs.datasette.io/en/stable/sql_queries.html#canned-queries", "label": "canned queries"}, {"href": "https://www.srihash.org/", "label": "https://www.srihash.org/"}, {"href": "https://github.com/simonw/datasette/issues/153", "label": "#153"}, {"href": "https://github.com/simonw/datasette/issues/153", "label": "#153"}, {"href": "https://github.com/simonw/datasette/issues/160", "label": "#160"}, {"href": "https://github.com/simonw/datasette/issues/164", "label": "#164"}, {"href": "https://github.com/simonw/datasette/issues/165", "label": "#165"}, {"href": "https://github.com/simonw/datasette/issues/130", "label": "#130"}, {"href": "https://github.com/simonw/datasette/issues/168", "label": "#168"}, {"href": "https://github.com/channelcat/sanic/releases/tag/0.7.0", "label": "https://github.com/channelcat/sanic/releases/tag/0.7.0"}, {"href": "https://github.com/simonw/datasette/issues/171", "label": "#171"}] |
changelog:id152 | changelog | id152 | 0.18 (2018-04-14) | This release introduces support for units , contributed by Russ Garrett ( #203 ). You can now optionally specify the units for specific columns using metadata.json . Once specified, units will be displayed in the HTML view of your table. They also become available for use in filters - if a column is configured with a unit of distance, you can request all rows where that column is less than 50 meters or more than 20 feet for example. Link foreign keys which don't have labels. [Russ Garrett] This renders unlabeled FKs as simple links. Also includes bonus fixes for two minor issues: In foreign key link hrefs the primary key was escaped using HTML escaping rather than URL escaping. This broke some non-integer PKs. Print tracebacks to console when handling 500 errors. Fix SQLite error when loading rows with no incoming FKs. [Russ Garrett] This fixes an error caused by an invalid query when loading incoming FKs. The error was ignored due to async but it still got printed to the console. Allow custom units to be registered with Pint. [Russ Garrett] Support units in filters. [Russ Garrett] Tidy up units support. [Russ Garrett] Add units to exported JSON … | ["Changelog"] | [{"href": "https://docs.datasette.io/en/stable/metadata.html#specifying-units-for-a-column", "label": "support for units"}, {"href": "https://github.com/simonw/datasette/issues/203", "label": "#203"}, {"href": "https://pint.readthedocs.io/en/latest/", "label": "pint"}] |
changelog:id145 | changelog | id145 | 0.19 (2018-04-16) | This is the first preview of the new Datasette plugins mechanism. Only two plugin hooks are available so far - for custom SQL functions and custom template filters. There's plenty more to come - read the documentation and get involved in the tracking ticket if you have feedback on the direction so far. Fix for _sort_desc=sortable_with_nulls test, refs #216 Fixed #216 - paginate correctly when sorting by nullable column Initial documentation for plugins, closes #213 https://docs.datasette.io/en/stable/plugins.html New --plugins-dir=plugins/ option ( #212 ) New option causing Datasette to load and evaluate all of the Python files in the specified directory and register any plugins that are defined in those files. This new option is available for the following commands: datasette serve mydb.db --plugins-dir=plugins/ datasette publish now/heroku mydb.db --plugins-dir=plugins/ datasette package mydb.db --plugins-dir=plugins/ Start of the plugin system, based on pluggy ( #210 ) Uses https://pluggy.readthedocs.io/ originally created for the py.test project We're starting with two plugin hooks: prepare_connection(conn) This is called when a new SQLite connection is created. It can be used to register custom SQL functions. prepare_jinja2_environment(env) This is called with the Jinja2 environment. It can be used to register custom template tags and filters. An example plugin which… | ["Changelog"] | [{"href": "https://docs.datasette.io/en/stable/plugins.html", "label": "the documentation"}, {"href": "https://github.com/simonw/datasette/issues/14", "label": "the tracking ticket"}, {"href": "https://github.com/simonw/datasette/issues/216", "label": "#216"}, {"href": "https://github.com/simonw/datasette/issues/216", "label": "#216"}, {"href": "https://github.com/simonw/datasette/issues/213", "label": "#213"}, {"href": "https://docs.datasette.io/en/stable/plugins.html", "label": "https://docs.datasette.io/en/stable/plugins.html"}, {"href": "https://github.com/simonw/datasette/issues/212", "label": "#212"}, {"href": "https://github.com/simonw/datasette/issues/14", "label": "#210"}, {"href": "https://pluggy.readthedocs.io/", "label": "https://pluggy.readthedocs.io/"}, {"href": "https://github.com/simonw/datasette-plugin-demos", "label": "https://github.com/simonw/datasette-plugin-demos"}, {"href": "https://github.com/simonw/datasette/issues/14", "label": "#14"}] |
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"}] |
testing_plugins:id1 | testing_plugins | id1 | Testing plugins | We recommend using pytest to write automated tests for your plugins. If you use the template described in Starting an installable plugin using cookiecutter your plugin will start with a single test in your tests/ directory that looks like this: from datasette.app import Datasette import pytest @pytest.mark.asyncio async def test_plugin_is_installed(): datasette = Datasette(memory=True) response = await datasette.client.get("/-/plugins.json") assert response.status_code == 200 installed_plugins = {p["name"] for p in response.json()} assert ( "datasette-plugin-template-demo" in installed_plugins ) This test uses the datasette.client object to exercise a test instance of Datasette. datasette.client is a wrapper around the HTTPX Python library which can imitate HTTP requests using ASGI. This is the recommended way to write tests against a Datasette instance. This test also uses the pytest-asyncio package to add support for async def test functions running under pytest. You can install these packages like so: pip install pytest pytest-asyncio If you are building an installable package you can add them as test dependencies to your setup.py module like this: setup( name="datasette-my-plugin", # ... extras_require={"test": ["pytest", "pytest-asyncio"]}, tests_require=["datasette-my-plugin[test]"], ) You can then install the test dependencies like so: pip install -e '.[test]' Then run the tests using pytest like so: pytest | [] | [{"href": "https://docs.pytest.org/", "label": "pytest"}, {"href": "https://www.python-httpx.org/", "label": "HTTPX"}, {"href": "https://pypi.org/project/pytest-asyncio/", "label": "pytest-asyncio"}] |
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"}] |
contributing:devenvironment | contributing | devenvironment | Setting up a development environment | If you have Python 3.8 or higher installed on your computer (on OS X the quickest way to do this is using homebrew ) you can install an editable copy of Datasette using the following steps. If you want to use GitHub to publish your changes, first create a fork of datasette under your own GitHub account. Now clone that repository somewhere on your computer: git clone git@github.com:YOURNAME/datasette If you want to get started without creating your own fork, you can do this instead: git clone git@github.com:simonw/datasette The next step is to create a virtual environment for your project and use it to install Datasette's dependencies: cd datasette # Create a virtual environment in ./venv python3 -m venv ./venv # Now activate the virtual environment, so pip can install into it source venv/bin/activate # Install Datasette and its testing dependencies python3 -m pip install -e '.[test]' That last line does most of the work: pip install -e means "install this package in a way that allows me to edit the source code in place". The .[test] option means "use the setup.py in this directory and install the optional testing dependencies as well". | ["Contributing"] | [{"href": "https://docs.python-guide.org/starting/install3/osx/", "label": "is using homebrew"}, {"href": "https://github.com/simonw/datasette/fork", "label": "create a fork of datasette"}] |
plugin_hooks:plugin-hook-prepare-connection | plugin_hooks | plugin-hook-prepare-connection | prepare_connection(conn, database, datasette) | conn - sqlite3 connection object The connection that is being opened database - string The name of the database datasette - Datasette class You can use this to access plugin configuration options via datasette.plugin_config(your_plugin_name) This hook is called when a new SQLite database connection is created. You can use it to register custom SQL functions , aggregates and collations. For example: from datasette import hookimpl import random @hookimpl def prepare_connection(conn): conn.create_function( "random_integer", 2, random.randint ) This registers a SQL function called random_integer which takes two arguments and can be called like this: select random_integer(1, 10); Examples: datasette-jellyfish , datasette-jq , datasette-haversine , datasette-rure | ["Plugin hooks"] | [{"href": "https://docs.python.org/2/library/sqlite3.html#sqlite3.Connection.create_function", "label": "register custom SQL functions"}, {"href": "https://datasette.io/plugins/datasette-jellyfish", "label": "datasette-jellyfish"}, {"href": "https://datasette.io/plugins/datasette-jq", "label": "datasette-jq"}, {"href": "https://datasette.io/plugins/datasette-haversine", "label": "datasette-haversine"}, {"href": "https://datasette.io/plugins/datasette-rure", "label": "datasette-rure"}] |
internals:database-results | internals | database-results | Results | The db.execute() method returns a single Results object. This can be used to access the rows returned by the query. Iterating over a Results object will yield SQLite Row objects . Each of these can be treated as a tuple or can be accessed using row["column"] syntax: info = [] results = await db.execute("select name from sqlite_master") for row in results: info.append(row["name"]) The Results object also has the following properties and methods: .truncated - boolean Indicates if this query was truncated - if it returned more results than the specified page_size . If this is true then the results object will only provide access to the first page_size rows in the query result. You can disable truncation by passing truncate=False to the db.query() method. .columns - list of strings A list of column names returned by the query. .rows - list of sqlite3.Row This property provides direct access to the list of rows returned by the database. You can access specific rows by index using results.rows[0] . .first() - row or None Returns the first row in the results, or None if no rows were returned. .single_value() Returns the value of the first column of the first row of results - but only if the query returned a single row wit… | ["Internals for plugins", "Database class"] | [{"href": "https://docs.python.org/3/library/sqlite3.html#row-objects", "label": "Row objects"}] |
internals:database-execute-write-many | internals | database-execute-write-many | await db.execute_write_many(sql, params_seq, block=True) | Like execute_write() but uses the sqlite3 conn.executemany() method. This will efficiently execute the same SQL statement against each of the parameters in the params_seq iterator, for example: await db.execute_write_many( "insert into characters (id, name) values (?, ?)", [(1, "Melanie"), (2, "Selma"), (2, "Viktor")], ) Each call to execute_write_many() will be executed inside a transaction. | ["Internals for plugins", "Database class"] | [{"href": "https://docs.python.org/3/library/sqlite3.html#sqlite3.Cursor.executemany", "label": "conn.executemany()"}] |
internals:database-execute-write-script | internals | database-execute-write-script | await db.execute_write_script(sql, block=True) | Like execute_write() but can be used to send multiple SQL statements in a single string separated by semicolons, using the sqlite3 conn.executescript() method. Each call to execute_write_script() will be executed inside a transaction. | ["Internals for plugins", "Database class"] | [{"href": "https://docs.python.org/3/library/sqlite3.html#sqlite3.Cursor.executescript", "label": "conn.executescript()"}] |
ecosystem:dogsheep | ecosystem | dogsheep | Dogsheep | Dogsheep is a collection of tools for personal analytics using SQLite and Datasette. The project provides tools like github-to-sqlite and twitter-to-sqlite that can import data from different sources in order to create a personal data warehouse. Personal Data Warehouses: Reclaiming Your Data is a talk that explains Dogsheep and demonstrates it in action. | ["The Datasette Ecosystem"] | [{"href": "https://dogsheep.github.io/", "label": "Dogsheep"}, {"href": "https://datasette.io/tools/github-to-sqlite", "label": "github-to-sqlite"}, {"href": "https://datasette.io/tools/twitter-to-sqlite", "label": "twitter-to-sqlite"}, {"href": "https://simonwillison.net/2020/Nov/14/personal-data-warehouses/", "label": "Personal Data Warehouses: Reclaiming Your Data"}] |
spatialite:importing-shapefiles-into-spatialite | spatialite | importing-shapefiles-into-spatialite | Importing shapefiles into SpatiaLite | The shapefile format is a common format for distributing geospatial data. You can use the spatialite command-line tool to create a new database table from a shapefile. Try it now with the North America shapefile available from the University of North Carolina Global River Database project. Download the file and unzip it (this will create files called narivs.dbf , narivs.prj , narivs.shp and narivs.shx in the current directory), then run the following: spatialite rivers-database.db SpatiaLite version ..: 4.3.0a Supported Extensions: ... spatialite> .loadshp narivs rivers CP1252 23032 ======== Loading shapefile at 'narivs' into SQLite table 'rivers' ... Inserted 467973 rows into 'rivers' from SHAPEFILE This will load the data from the narivs shapefile into a new database table called rivers . Exit out of spatialite (using Ctrl+D ) and run Datasette against your new database like this: datasette rivers-database.db \ --load-extension=/usr/local/lib/mod_spatialite.dylib If you browse to http://localhost:8001/rivers-database/rivers you will see the new table... but the Geometry column will contain unreadable binary data (SpatiaLite uses a custom format based on WKB ). The easiest way to turn this into semi-readable data is to use the SpatiaLite AsGeoJSON function. Try the following using the SQL query interface at http://localhost:8001/rivers-database : select *, AsGeoJSON(Geometry) from rivers limit 10; This will give you back an additional column of GeoJSON. You can copy and paste GeoJSON from this column into the debugging tool at geojson.io to visualize it on a map. To see a more interesting example, try ordering the records with the longest geometry first. Since there are 467,000 rows in the table you will first need to increase the SQL time limit imposed by Datasette: datasette rivers-database.db \ --load-e… | ["SpatiaLite"] | [{"href": "https://en.wikipedia.org/wiki/Shapefile", "label": "shapefile format"}, {"href": "http://gaia.geosci.unc.edu/rivers/", "label": "Global River Database"}, {"href": "https://www.gaia-gis.it/gaia-sins/BLOB-Geometry.html", "label": "a custom format based on WKB"}, {"href": "https://geojson.io/", "label": "geojson.io"}] |
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"}] |
full_text_search:full-text-search-custom-sql | full_text_search | full-text-search-custom-sql | Searches using custom SQL | You can include full-text search results in custom SQL queries. The general pattern with SQLite search is to run the search as a sub-select that returns rowid values, then include those rowids in another part of the query. You can see the syntax for a basic search by running that search on a table page and then clicking "View and edit SQL" to see the underlying SQL. For example, consider this search for manafort is the US FARA database : /fara/FARA_All_ShortForms?_search=manafort If you click View and edit SQL you'll see that the underlying SQL looks like this: select rowid, Short_Form_Termination_Date, Short_Form_Date, Short_Form_Last_Name, Short_Form_First_Name, Registration_Number, Registration_Date, Registrant_Name, Address_1, Address_2, City, State, Zip from FARA_All_ShortForms where rowid in ( select rowid from FARA_All_ShortForms_fts where FARA_All_ShortForms_fts match escape_fts(:search) ) order by rowid limit 101 | ["Full-text search"] | [{"href": "https://fara.datasettes.com/fara/FARA_All_ShortForms?_search=manafort", "label": "manafort is the US FARA database"}, {"href": "https://fara.datasettes.com/fara?sql=select%0D%0A++rowid%2C%0D%0A++Short_Form_Termination_Date%2C%0D%0A++Short_Form_Date%2C%0D%0A++Short_Form_Last_Name%2C%0D%0A++Short_Form_First_Name%2C%0D%0A++Registration_Number%2C%0D%0A++Registration_Date%2C%0D%0A++Registrant_Name%2C%0D%0A++Address_1%2C%0D%0A++Address_2%2C%0D%0A++City%2C%0D%0A++State%2C%0D%0A++Zip%0D%0Afrom%0D%0A++FARA_All_ShortForms%0D%0Awhere%0D%0A++rowid+in+%28%0D%0A++++select%0D%0A++++++rowid%0D%0A++++from%0D%0A++++++FARA_All_ShortForms_fts%0D%0A++++where%0D%0A++++++FARA_All_ShortForms_fts+match+escape_fts%28%3Asearch%29%0D%0A++%29%0D%0Aorder+by%0D%0A++rowid%0D%0Alimit%0D%0A++101&search=manafort", "label": "View and edit SQL"}] |
pages:indexview | pages | indexview | Top-level index | The root page of any Datasette installation is an index page that lists all of the currently attached databases. Some examples: fivethirtyeight.datasettes.com global-power-plants.datasettes.com register-of-members-interests.datasettes.com Add /.json to the end of the URL for the JSON version of the underlying data: fivethirtyeight.datasettes.com/.json global-power-plants.datasettes.com/.json register-of-members-interests.datasettes.com/.json | ["Pages and API endpoints"] | [{"href": "https://fivethirtyeight.datasettes.com/", "label": "fivethirtyeight.datasettes.com"}, {"href": "https://global-power-plants.datasettes.com/", "label": "global-power-plants.datasettes.com"}, {"href": "https://register-of-members-interests.datasettes.com/", "label": "register-of-members-interests.datasettes.com"}, {"href": "https://fivethirtyeight.datasettes.com/.json", "label": "fivethirtyeight.datasettes.com/.json"}, {"href": "https://global-power-plants.datasettes.com/.json", "label": "global-power-plants.datasettes.com/.json"}, {"href": "https://register-of-members-interests.datasettes.com/.json", "label": "register-of-members-interests.datasettes.com/.json"}] |
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"}] |
plugins:plugins-installed | plugins | plugins-installed | Seeing what plugins are installed | You can see a list of installed plugins by navigating to the /-/plugins page of your Datasette instance - for example: https://fivethirtyeight.datasettes.com/-/plugins You can also use the datasette plugins command: datasette plugins Which outputs: [ { "name": "datasette_json_html", "static": false, "templates": false, "version": "0.4.0" } ] [[[cog from datasette import cli from click.testing import CliRunner import textwrap, json cog.out("\n") result = CliRunner().invoke(cli.cli, ["plugins", "--all"]) # cog.out() with text containing newlines was unindenting for some reason cog.outl("If you run ``datasette plugins --all`` it will include default plugins that ship as part of Datasette:\n") cog.outl(".. code-block:: json\n") plugins = [p for p in json.loads(result.output) if p["name"].startswith("datasette.")] indented = textwrap.indent(json.dumps(plugins, indent=4), " ") for line in indented.split("\n"): cog.outl(line) cog.out("\n\n") ]]] If you run datasette plugins --all it will include default plugins that ship as part of Datasette: [ { "name": "datasette.actor_auth_cookie", "static": false, "templates": false, "version": null, "hooks": [ "actor_from_request" ] }, { "name": "datasette.blob_renderer", "static": false, "templates": false, "version": null, "hooks": [ "register_output_renderer" ] }, { "name": "datasette.default_magic_parameters", "static": false, "templates": false, "version": null, "hooks": [ "register_magic_parameters" ] }, { "name": "datasette.default_menu_links", "static": false, "templates": false, "version": null, "hooks": [ "menu_links" ] }, { "name… | ["Plugins"] | [{"href": "https://fivethirtyeight.datasettes.com/-/plugins", "label": "https://fivethirtyeight.datasettes.com/-/plugins"}] |
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"}] |
pages:databaseview | pages | databaseview | Database | Each database has a page listing the tables, views and canned queries available for that database. If the execute-sql permission is enabled (it's on by default) there will also be an interface for executing arbitrary SQL select queries against the data. Examples: fivethirtyeight.datasettes.com/fivethirtyeight global-power-plants.datasettes.com/global-power-plants The JSON version of this page provides programmatic access to the underlying data: fivethirtyeight.datasettes.com/fivethirtyeight.json global-power-plants.datasettes.com/global-power-plants.json | ["Pages and API endpoints"] | [{"href": "https://fivethirtyeight.datasettes.com/fivethirtyeight", "label": "fivethirtyeight.datasettes.com/fivethirtyeight"}, {"href": "https://global-power-plants.datasettes.com/global-power-plants", "label": "global-power-plants.datasettes.com/global-power-plants"}, {"href": "https://fivethirtyeight.datasettes.com/fivethirtyeight.json", "label": "fivethirtyeight.datasettes.com/fivethirtyeight.json"}, {"href": "https://global-power-plants.datasettes.com/global-power-plants.json", "label": "global-power-plants.datasettes.com/global-power-plants.json"}] |
json_api:json-api-special | json_api | json-api-special | Special JSON arguments | Every Datasette endpoint that can return JSON also accepts the following query string arguments: ?_shape=SHAPE The shape of the JSON to return, documented above. ?_nl=on When used with ?_shape=array produces newline-delimited JSON objects. ?_json=COLUMN1&_json=COLUMN2 If any of your SQLite columns contain JSON values, you can use one or more _json= parameters to request that those columns be returned as regular JSON. Without this argument those columns will be returned as JSON objects that have been double-encoded into a JSON string value. Compare this query without the argument to this query using the argument ?_json_infinity=on If your data contains infinity or -infinity values, Datasette will replace them with None when returning them as JSON. If you pass _json_infinity=1 Datasette will instead return them as Infinity or -Infinity which is invalid JSON but can be processed by some custom JSON parsers. ?_timelimit=MS Sets a custom time limit for the query in ms. You can use this for optimistic queries where you would like Datasette to give up if the query takes too long, for example if you want to implement autocomplete search but only… | ["JSON API"] | [{"href": "https://fivethirtyeight.datasettes.com/fivethirtyeight.json?sql=select+%27{%22this+is%22%3A+%22a+json+object%22}%27+as+d&_shape=array", "label": "this query without the argument"}, {"href": "https://fivethirtyeight.datasettes.com/fivethirtyeight.json?sql=select+%27{%22this+is%22%3A+%22a+json+object%22}%27+as+d&_shape=array&_json=d", "label": "this query using the argument"}] |
changelog:csv-export | changelog | csv-export | CSV export | Any Datasette table, view or custom SQL query can now be exported as CSV. Check out the CSV export documentation for more details, or try the feature out on https://fivethirtyeight.datasettes.com/fivethirtyeight/bechdel%2Fmovies If your table has more than max_returned_rows (default 1,000) Datasette provides the option to stream all rows . This option takes advantage of async Python and Datasette's efficient pagination to iterate through the entire matching result set and stream it back as a downloadable CSV file. | ["Changelog", "0.23 (2018-06-18)"] | [{"href": "https://fivethirtyeight.datasettes.com/fivethirtyeight/bechdel%2Fmovies", "label": "https://fivethirtyeight.datasettes.com/fivethirtyeight/bechdel%2Fmovies"}] |
changelog:control-http-caching-with-ttl | changelog | control-http-caching-with-ttl | Control HTTP caching with ?_ttl= | You can now customize the HTTP max-age header that is sent on a per-URL basis, using the new ?_ttl= query string parameter. You can set this to any value in seconds, or you can set it to 0 to disable HTTP caching entirely. Consider for example this query which returns a randomly selected member of the Avengers: select * from [avengers/avengers] order by random() limit 1 If you hit the following page repeatedly you will get the same result, due to HTTP caching: /fivethirtyeight?sql=select+*+from+%5Bavengers%2Favengers%5D+order+by+random%28%29+limit+1 By adding ?_ttl=0 to the zero you can ensure the page will not be cached and get back a different super hero every time: /fivethirtyeight?sql=select+*+from+%5Bavengers%2Favengers%5D+order+by+random%28%29+limit+1&_ttl=0 | ["Changelog", "0.23 (2018-06-18)"] | [{"href": "https://fivethirtyeight.datasettes.com/fivethirtyeight?sql=select+*+from+%5Bavengers%2Favengers%5D+order+by+random%28%29+limit+1", "label": "/fivethirtyeight?sql=select+*+from+%5Bavengers%2Favengers%5D+order+by+random%28%29+limit+1"}, {"href": "https://fivethirtyeight.datasettes.com/fivethirtyeight?sql=select+*+from+%5Bavengers%2Favengers%5D+order+by+random%28%29+limit+1&_ttl=0", "label": "/fivethirtyeight?sql=select+*+from+%5Bavengers%2Favengers%5D+order+by+random%28%29+limit+1&_ttl=0"}] |
publish:publish-fly | publish | publish-fly | Publishing to Fly | Fly is a competitively priced Docker-compatible hosting platform that supports running applications in globally distributed data centers close to your end users. You can deploy Datasette instances to Fly using the datasette-publish-fly plugin. pip install datasette-publish-fly datasette publish fly mydatabase.db --app="my-app" Consult the datasette-publish-fly README for more details. | ["Publishing data", "datasette publish"] | [{"href": "https://fly.io/", "label": "Fly"}, {"href": "https://fly.io/docs/pricing/", "label": "competitively priced"}, {"href": "https://github.com/simonw/datasette-publish-fly", "label": "datasette-publish-fly"}, {"href": "https://github.com/simonw/datasette-publish-fly/blob/main/README.md", "label": "datasette-publish-fly README"}] |