sections_fts
792 rows
This data as json, CSV (advanced)
| Link | rowid ▼ | title | content | sections_fts | rank |
|---|---|---|---|---|---|
| 1 | 1 | Custom pages and templates | Datasette provides a number of ways of customizing the way data is displayed. | 181 | |
| 2 | 2 | CSS classes on the <body> | Every default template includes CSS classes in the body designed to support custom styling. The index template (the top level page at / ) gets this: <body class="index"> The database template ( /dbname ) gets this: <body class="db db-dbname"> The custom SQL template ( /dbname?sql=... ) gets this: <body class="query db-dbname"> A stored query template ( /dbname/queryname ) gets this: <body class="query db-dbname query-queryname"> The table template ( /dbname/tablename ) gets: <body class="table db-dbname table-tablename"> The row template ( /dbname/tablename/rowid ) gets: <body class="row db-dbname table-tablename"> The db-x and table-x classes use the database or table names themselves if they are valid CSS identifiers. If they aren't, we strip any invalid characters out and append a 6 character md5 digest of the original name, in order to ensure that multiple tables which resolve to the same stripped character version still have different CSS classes. Some examples: "simple" => "simple" "MixedCase" => "MixedCase" "-no-leading-hyphens" => "no-leading-hyphens-65bea6" "_no-leading-underscores" => "no-leading-underscores-b921bc" "no spaces" => "no-spaces-7088d7" "-" => "336d5e" "no $ characters" => "no--characters-59e024" <td> and <th> elements also get custom CSS classes reflecting the database column they are representing, for example: <table> <thead> <tr> <th class="col-id" scope="col">id</th> <th class="col-name" scope="col">name</th> </tr> </thead> <tbody> <tr> <td class="col-id"><a href="...">1</a></td> <td class="col-name">SMITH</td> </tr> </tbody> </table> | 181 | |
| 3 | 3 | Writing custom CSS | Custom templates need to take Datasette's default CSS into account. The pattern portfolio at /-/patterns ( example here ) is a useful reference for understanding the available CSS classes. The core class is particularly useful - you can apply this directly to a <input> or <button> element to get Datasette's default form styles, or you can apply it to a containing element (such as <form> ) to apply those styles to all of the form elements within it. | 181 | |
| 4 | 4 | Linking to static assets | Use the static() template function to create cache-busting URLs to static assets from your custom templates. It returns a URL with a ?_hash= parameter based on the file contents and takes the base_url setting into account. When the hash in the URL matches the current file contents, Datasette will serve the static asset with a far-future immutable Cache-Control header. When Datasette is run using --reload , the file contents are hashed every time the template is rendered, so edits to static files will update their URLs without restarting Datasette. Without --reload , the hash is cached for the lifetime of the Datasette process. For Datasette's bundled static assets, pass the path to the asset: <link rel="stylesheet" href="{{ static('app.css') }}"> For plugin static assets, pass the plugin name using plugin= and a path relative to the plugin's static/ directory: <script src="{{ static('plugin.js', plugin='datasette_plugin_name') }}" defer></script> For files served from a directory mounted using --static assets:static-files/ , pass the mount point name using mount= and a path relative to that mounted directory: <link rel="stylesheet" href="{{ static('styles.css', mount='assets') }}"> <script src="{{ static('app.js', mount='assets') }}" defer></script> You can also use urls.path() if you want to link to a mounted file without adding a content hash: <link rel="stylesheet" href="{{ urls.path('/assets/styles.css') }}"> | 181 | |
| 5 | 5 | Serving files from a directory | Datasette can serve static files from a directory for you, using the --static option. Consider the following directory structure: metadata.json static-files/styles.css static-files/app.js You can start Datasette using --static assets:static-files/ to serve those files from the /assets/ mount point: datasette --config datasette.yaml --static assets:static-files/ --memory The following URLs will now serve the content from those CSS and JS files: http://localhost:8001/assets/styles.css http://localhost:8001/assets/app.js You can reference those files from datasette.yaml like this, see custom CSS and JavaScript for more details: [[[cog from metadata_doc import config_example config_example(cog, """ extra_css_urls: - /assets/styles.css extra_js_urls: - /assets/app.js """) ]]] [[[end]]] | 181 | |
| 6 | 6 | Publishing static assets | The datasette publish command can be used to publish your static assets, using the same syntax as above: datasette publish cloudrun mydb.db --static assets:static-files/ This will upload the contents of the static-files/ directory as part of the deployment, and configure Datasette to correctly serve the assets from /assets/ . | 181 | |
| 7 | 7 | Custom templates | By default, Datasette uses default templates that ship with the package. You can over-ride these templates by specifying a custom --template-dir like this: datasette mydb.db --template-dir=mytemplates/ Datasette will now first look for templates in that directory, and fall back on the defaults if no matches are found. The variables made available to each template are documented on the Template context page. Variables documented there are a stable API: custom templates that use them will keep working in future Datasette releases, up until the next major version. It is also possible to over-ride templates on a per-database, per-row or per- table basis. The lookup rules Datasette uses are as follows: Index page (/): index.html Database page (/mydatabase): database-mydatabase.html database.html Custom query page (/mydatabase?sql=...): query-mydatabase.html query.html Stored query page (/mydatabase/query-name): query-mydatabase-query-name.html query-mydatabase.html query.html Table page (/mydatabase/mytable): table-mydatabase-mytable.html table.html Row page (/mydatabase/mytable/id): row-mydatabase-mytable.html row.html Table of rows and columns include on table page: _table-table-mydatabase-mytable.html _table-mydatabase-mytable.html _table.html Table of rows and columns include on row page: _table-row-mydatabase-mytable.html _table-mydatabase-mytable.html _table.html If a table name has spaces or other unexpected characters in it, the template filename will follow the same rules as our custom <body> CSS classes - for example, a table called "Food Trucks" will attempt to load the following templates: … | 181 | |
| 8 | 8 | Custom pages | You can add templated pages to your Datasette instance by creating HTML files in a pages directory within your templates directory. For example, to add a custom page that is served at http://localhost/about you would create a file in templates/pages/about.html , then start Datasette like this: datasette mydb.db --template-dir=templates/ You can nest directories within pages to create a nested structure. To create a http://localhost:8001/about/map page you would create templates/pages/about/map.html . | 181 | |
| 9 | 9 | Path parameters for pages | You can define custom pages that match multiple paths by creating files with {variable} definitions in their filenames. For example, to capture any request to a URL matching /about/* , you would create a template in the following location: templates/pages/about/{slug}.html A hit to /about/news would render that template and pass in a variable called slug with a value of "news" . If you use this mechanism don't forget to return a 404 if the referenced content could not be found. You can do this using {{ raise_404() }} described below. Templates defined using custom page routes work particularly well with the sql() template function from datasette-template-sql or the graphql() template function from datasette-graphql . | 181 | |
| 10 | 10 | Custom headers and status codes | Custom pages default to being served with a content-type of text/html; charset=utf-8 and a 200 status code. You can change these by calling a custom function from within your template. For example, to serve a custom page with a 418 I'm a teapot HTTP status code, create a file in pages/teapot.html containing the following: {{ custom_status(418) }} <html> <head><title>Teapot</title></head> <body> I'm a teapot </body> </html> To serve a custom HTTP header, add a custom_header(name, value) function call. For example: {{ custom_status(418) }} {{ custom_header("x-teapot", "I am") }} <html> <head><title>Teapot</title></head> <body> I'm a teapot </body> </html> You can verify this is working using curl like this: curl -I 'http://127.0.0.1:8001/teapot' HTTP/1.1 418 date: Sun, 26 Apr 2020 18:38:30 GMT server: uvicorn x-teapot: I am content-type: text/html; charset=utf-8 | 181 | |
| 11 | 11 | Returning 404s | To indicate that content could not be found and display the default 404 page you can use the raise_404(message) function: {% if not rows %} {{ raise_404("Content not found") }} {% endif %} If you call raise_404() the other content in your template will be ignored. | 181 | |
| 12 | 12 | Custom redirects | You can use the custom_redirect(location) function to redirect users to another page, for example in a file called pages/datasette.html : {{ custom_redirect("https://github.com/simonw/datasette") }} Now requests to http://localhost:8001/datasette will result in a redirect. These redirects are served with a 302 Found status code by default. You can send a 301 Moved Permanently code by passing 301 as the second argument to the function: {{ custom_redirect("https://github.com/simonw/datasette", 301) }} | 181 | |
| 13 | 13 | Custom error pages | Datasette returns an error page if an unexpected error occurs, access is forbidden or content cannot be found. You can customize the response returned for these errors by providing a custom error page template. Content not found errors use a 404.html template. Access denied errors use 403.html . Invalid input errors use 400.html . Unexpected errors of other kinds use 500.html . If a template for the specific error code is not found a template called error.html will be used instead. If you do not provide that template Datasette's default error.html template will be used. The error template will be passed the following context: status - integer The integer HTTP status code, e.g. 404, 500, 403, 400. error - string Details of the specific error, usually a full sentence. title - string or None A title for the page representing the class of error. This is often None for errors that do not provide a title separate from their error message. | 181 | |
| 14 | 14 | 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 pyproject.toml file like this: [project] name = "datasette-my-plugin" # ... [project.optional-dependencies] test = ["pytest", "pytest-asyncio"] You can then install the test dependencies like so: pip install -e '.[test]' Then run the tests using pytest like so: pytest | 181 | |
| 15 | 15 | 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. | 181 | |
| 16 | 16 | Using Datasette's fixtures database | Datasette's own test suite uses a SQLite database with tables that exercise features such as compound primary keys, foreign keys, sortable columns, facets, full-text search and binary data. You can use those same tables in your plugin tests using the populate_fixture_database(conn) helper in datasette.fixtures : Be aware that future Datasette releases may change details of these tables, so try not to rely on their exact structure in your own tests. populate_fixture_database(conn) Populates an existing SQLite connection with the fixture tables. For an in-memory test database: from datasette.app import Datasette from datasette.fixtures import populate_fixture_database import pytest import pytest_asyncio @pytest_asyncio.fixture async def datasette(): datasette = Datasette() db = datasette.add_memory_database("fixtures") await db.execute_write_fn(populate_fixture_database) await datasette.invoke_startup() return datasette @pytest.mark.asyncio async def test_facetable(datasette): response = await datasette.client.get( "/fixtures/facetable.json?_shape=array" ) assert response.status_code == 200 | 181 | |
| 17 | 17 | Automatic cleanup of Datasette instances | Installing Datasette also installs a small pytest plugin that automatically calls .close() on any Datasette() instance constructed during a test. This helps prevent large test suites from running out of file descriptors or leaking background threads from the hundreds of instances they may build up across a session. The plugin closes: Instances created in the body of a test function. Instances created inside function-scoped pytest fixtures (the default scope — @pytest.fixture with no scope= argument, or scope="function" ). The plugin deliberately does not close: Instances created inside higher-scoped fixtures ( scope="session" , "module" , "class" or "package" ). Those fixtures are typically designed to produce a single Datasette that is shared across many tests, and closing it automatically would break the tests that run after the first. In practice this means downstream projects rarely need to call ds.close() themselves — function-scoped fixtures and inline test code are both covered automatically, while long-lived shared fixtures keep working as before. If you need to opt out of this behavior, add the following to your pytest.ini (or equivalent): [pytest] datasette_autoclose = false | 181 | |
| 18 | 18 | Using datasette.client in tests | The datasette.client mechanism is designed for use in tests. It provides access to a pre-configured HTTPX async client instance that can make GET, POST and other HTTP requests against a Datasette instance from inside a test. A simple test looks like this: @pytest.mark.asyncio async def test_homepage(): ds = Datasette(memory=True) response = await ds.client.get("/") html = response.text assert "<h1>" in html Or for a JSON API: @pytest.mark.asyncio async def test_actor_is_null(): ds = Datasette(memory=True) response = await ds.client.get("/-/actor.json") assert response.json() == {"ok": True, "actor": None} To make requests as an authenticated actor, create a signed ds_cookie using the datasette.client.actor_cookie() helper function and pass it in cookies= like this: @pytest.mark.asyncio async def test_signed_cookie_actor(): ds = Datasette(memory=True) cookies = {"ds_actor": ds.client.actor_cookie({"id": "root"})} response = await ds.client.get("/-/actor.json", cookies=cookies) assert response.json() == {"ok": True, "actor": {"id": "root"}} | 181 | |
| 19 | 19 | 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. | 181 | |
| 20 | 20 | 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… | 181 | |
| 21 | 21 | Testing outbound HTTP calls with pytest-httpx | If your plugin makes outbound HTTP calls - for example datasette-auth-github or datasette-import-table - you may need to mock those HTTP requests in your tests. The pytest-httpx package is a useful library for mocking calls. It can be tricky to use with Datasette though since it mocks all HTTPX requests, and Datasette's own testing mechanism uses HTTPX internally. To avoid breaking your tests, you can return ["localhost"] from the non_mocked_hosts() fixture. As an example, here's a very simple plugin which executes an HTTP response and returns the resulting content: from datasette import hookimpl from datasette.utils.asgi import Response import httpx @hookimpl def register_routes(): return [ (r"^/-/fetch-url$", fetch_url), ] async def fetch_url(datasette, request): if request.method == "GET": return Response.html(""" <form action="/-/fetch-url" method="post"> <input name="url"><input type="submit"> </form>""") vars = await request.post_vars() url = vars["url"] return Response.text(httpx.get(url).text) Here's a test for that plugin that mocks the HTTPX outbound request: from datasette.app import Datasette import pytest @pytest.fixture def non_mocked_hosts(): # This ensures httpx-mock will not affect Datasette's own # httpx calls made in the tests by datasette.client: return ["localhost"] async def test_outbound_http_call(httpx_mock): httpx_mock.add_response( url="https://www.example.com/", text="Hello world", ) datasette = Datasette([], memory=True) response = await datasette.client.post( "/-/fetch-url", data={"url": "https://www.example.com/"}, ) assert response.text == "Hello world" outbound_request = httpx_mock.get_request() assert ( outbound_request.url == "https://www.example.com/" ) | 181 | |
| 22 | 22 | 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 datasette.pm.register() and datasette.pm.unregister() like this: from datasette import hookimpl from datasette.app import Datasette 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), ] datasette = Datasette() try: # The test implementation goes here datasette.pm.register(TestPlugin(), name="undo") response = await datasette.client.get("/error") assert response.status_code == 500 finally: datasette.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: import pytest import pytest_asyncio from datasette import hookimpl from datasette.app import Datasette @pytest_asyncio.fixture async def datasette_with_plugin(): class TestPlugin: __name__ = "TestPlugin" @hookimpl def register_routes(self): return [ (r"^/error$", lambda: 1 / 0), ] datasette = Datasette() datasette.pm.register(TestPlugin(), name="undo") try: yield datasette finally: datasette.pm.unregister(name="undo") datasette.close() 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 | 181 | |
| 23 | 23 | Contributing | Datasette is an open source project. We welcome contributions! This document describes how to contribute to Datasette core. You can also contribute to the wider Datasette ecosystem by creating new Plugins . | 181 | |
| 24 | 24 | General guidelines | main should always be releasable . Incomplete features should live in branches. This ensures that any small bug fixes can be quickly released. The ideal commit should bundle together the implementation, unit tests and associated documentation updates. The commit message should link to an associated issue. New plugin hooks should only be shipped if accompanied by a separate release of a non-demo plugin that uses them. New user-facing views and documentation should be added or updated alongside their implementation. The /docs folder includes pages for plugin hooks and built-in views—please ensure any new hooks or views are reflected there so the documentation tests continue to pass. | 181 | |
| 25 | 25 | Setting up a development environment | If you have Python 3.10 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 quickest way to set up a development environment is to use uv . From the repository root you can run the tests directly: cd datasette uv run pytest This will create a local .venv/ and install Datasette plus its development dependencies. If you prefer to manage your own virtual environment with pip, create and activate one and then install the development dependency group: python3 -m venv ./venv source venv/bin/activate python3 -m pip install -e . --group dev | 181 | |
| 26 | 26 | Running the tests | Once you have done this, you can run the Datasette unit tests from inside your datasette/ directory using pytest like so: uv run pytest You can run the tests faster using multiple CPU cores with pytest-xdist like this: uv run 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: uv run pytest -m "serial" | 181 | |
| 27 | 27 | Running Playwright tests | Datasette includes a small number of browser automation tests using Playwright . These tests are skipped by default, so you can run the main test suite with uv run pytest without installing Playwright or any browser binaries. The Playwright tests use a separate dependency group. The easiest way to run them is using just . First install the browser engine you want to test against. Chromium is used by default: just playwright-install Then run the Playwright test module: just playwright You can also run the same tests against Firefox or WebKit by installing that browser engine and passing it to just playwright : just playwright-install firefox just playwright firefox just playwright-install webkit just playwright webkit To install every supported browser engine and run the tests against all of them, use: just playwright-install-all just playwright-all You can pass extra pytest options after the browser name: just playwright chromium -k permissions just playwright-all -x You can add the --headed option to have Playwright open a browser window that you can see while it runs the tests. This only works if you specify a browser, for example: just playwright firefox --headed Combine this with -k to watch a specific test: just playwright chromium --headed -k test_insert_row If you are not using just , the equivalent uv run commands are: uv run --group playwright playwright install chromium uv run --group playwright pytest tests/test_playwright.py --playwright --browser chromium | 181 | |
| 28 | 28 | Using fixtures | To run Datasette itself, type datasette . You're going to need at least one SQLite database. A quick way to get started is to use the fixtures database that Datasette uses for its own tests. You can create a copy of that database by running this command: uv run python tests/fixtures.py fixtures.db Now you can run Datasette against the new fixtures database like so: uv run datasette fixtures.db This will start a server at http://127.0.0.1:8001/ . Any changes you make in the datasette/templates or datasette/static folder will be picked up immediately (though you may need to do a force-refresh in your browser to see changes to CSS or JavaScript). If you want to change Datasette's Python code you can use the --reload option to cause Datasette to automatically reload any time the underlying code changes: uv run datasette --reload fixtures.db This also enables development mode for static asset cache busting, described in Linking to static assets . You can also use the fixtures.py script to recreate the testing version of metadata.json used by the unit tests. To do that: uv run python tests/fixtures.py fixtures.db fixtures-metadata.json Or to output the plugins used by the tests, run this: uv run python tests/fixtures.py fixtures.db fixtures-metadata.json fixtures-plugins Test tables written to fixtures.db - metadata written to fixtures-metadata.json Wrote plugin: fixtures-plugins/register_output_renderer.py Wrote plugin: fixtures-plugins/view_name.py Wrote plugin: fixtures-plugins/my_plugin.py Wrote plugin: fixtures-plugins/messages_output_renderer.py Wrote plugin: fixtures-plugins/my_plugin_2.py Then run Datasette like this: uv run datasette fixtures.db -m fixtures-metadata.json --plugins-dir=fixtures-plugins/ | 181 | |
| 29 | 29 | Debugging | Any errors that occur while Datasette is running while display a stack trace on the console. You can tell Datasette to open an interactive pdb (or ipdb , if present) debugger session if an error occurs using the --pdb option: uv run datasette --pdb fixtures.db For ipdb , first run this: uv run datasette install ipdb | 181 | |
| 30 | 30 | Code formatting | Datasette uses opinionated code formatters: Black for Python and Prettier for JavaScript. These formatters are enforced by Datasette's continuous integration: if a commit includes Python or JavaScript code that does not match the style enforced by those tools, the tests will fail. When developing locally, you can verify and correct the formatting of your code using these tools. If you are using Just the quickest way to run these is like so: just black just prettier Or run both at the same time: just format | 181 | |
| 31 | 31 | Running Black | Black is installed as part of the development dependency group. To test that your code complies with Black, run the following in your root datasette repository checkout: uv run black . --check All done! ✨ 🍰 ✨ 95 files would be left unchanged. If any of your code does not conform to Black you can run this to automatically fix those problems: uv run black . reformatted ../datasette/app.py All done! ✨ 🍰 ✨ 1 file reformatted, 94 files left unchanged. | 181 | |
| 32 | 32 | blacken-docs | The blacken-docs command applies Black formatting rules to code examples in the documentation. Run it like this: uv run blacken-docs -l 60 docs/*.rst | 181 | |
| 33 | 33 | Prettier | To install Prettier, install Node.js and then run the following in the root of your datasette repository checkout: npm install This will install Prettier in a node_modules directory. You can then check that your code matches the coding style like so: npm run prettier -- --check > prettier > prettier 'datasette/static/*[!.min].js' "--check" Checking formatting... [warn] datasette/static/plugins.js [warn] Code style issues found in the above file(s). Forgot to run Prettier? You can fix any problems by running: npm run fix | 181 | |
| 34 | 34 | Editing and building the documentation | Datasette's documentation lives in the docs/ directory and is deployed automatically using Read The Docs . The documentation is written using reStructuredText. You may find this article on The subset of reStructuredText worth committing to memory useful. You can build it locally once you have installed the development dependency group (which includes Sphinx and related tools) and then running make html directly in the docs/ directory: cd docs/ uv run make html This will create the HTML version of the documentation in docs/_build/html . You can open it in your browser like so: open _build/html/index.html Any time you make changes to a .rst file you can re-run make html to update the built documents, then refresh them in your browser. For added productivity, you can use use sphinx-autobuild to run Sphinx in auto-build mode. This will run a local webserver serving the docs that automatically rebuilds them and refreshes the page any time you hit save in your editor. sphinx-autobuild is included in the development dependency group. In your docs/ directory you can start the server by running the following: uv run make livehtml Now browse to http://localhost:8000/ to view the documentation. Any edits you make should be instantly reflected in your browser. | 181 | |
| 35 | 35 | Running Cog | Some pages of documentation (in particular the CLI reference ) are automatically updated using Cog . To update these pages, run the following command: uv run cog -r docs/*.rst | 181 | |
| 36 | 36 | Documented template contexts | Datasette's documented template contexts are part of the public API for custom templates. They are defined as dataclasses next to the view code that renders them, for example DatabaseContext and QueryContext in datasette/views/database.py . Every documented context class inherits from datasette.views.Context . Fields that are added directly by view code should be declared as dataclass fields with help metadata, which is used to generate Template context . Fields resolved through the page extras system should use from_extra() so their documentation comes from the matching Extra class. Use documented_template on each context class to record the canonical template named in the generated documentation. This should be a string such as "database.html" . Runtime template selection still happens in the view code, since most pages consider more specific template names before falling back to the canonical one. When a context field contains repeated structured data, prefer a small nested dataclass over an anonymous dictionary. For example, a field containing table summaries should be annotated as list[DatabaseTable] where DatabaseTable is a dataclass describing the keys and value types. This keeps the Python contract and generated documentation clear. JSON responses and ?_context=1 debug output will convert nested dataclasses back to JSON objects at the response boundary. | 181 | |
| 37 | 37 | Continuously deployed demo instances | The demo instance at latest.datasette.io is re-deployed automatically to Google Cloud Run for every push to main that passes the test suite. This is implemented by the GitHub Actions workflow at .github/workflows/deploy-latest.yml . Specific branches can also be set to automatically deploy by adding them to the on: push: branches block at the top of the workflow YAML file. Branches configured in this way will be deployed to a new Cloud Run service whether or not their tests pass. The Cloud Run URL for a branch demo can be found in the GitHub Actions logs. | 181 | |
| 38 | 38 | Release process | Datasette releases are performed using tags. When a new release is published on GitHub, a GitHub Action workflow will perform the following: Run the unit tests against all supported Python versions. If the tests pass... Build a Docker image of the release and push a tag to https://hub.docker.com/r/datasetteproject/datasette Re-point the "latest" tag on Docker Hub to the new image Build a wheel bundle of the underlying Python source code Push that new wheel up to PyPI: https://pypi.org/project/datasette/ If the release is an alpha, navigate to https://readthedocs.org/projects/datasette/versions/ and search for the tag name in the "Activate a version" filter, then mark that version as "active" to ensure it will appear on the public ReadTheDocs documentation site. To deploy new releases you will need to have push access to the main Datasette GitHub repository. Datasette follows Semantic Versioning : major.minor.patch We increment major for backwards-incompatible releases. Datasette is currently pre-1.0 so the major version is always 0 . We increment minor for new features. We increment patch for bugfix releass. Alpha and beta releases may have an additional a0 or b0 prefix - the integer component will be incremented with each subsequent alpha or beta. To release a new version, first create a commit that updates the version number in datasette/version.py and the the changelog with highlights of the new version. An example commit can be seen here : # Update changelog git commit -m " Release 0.51a1 Ref… | 181 | |
| 39 | 39 | Alpha and beta releases | Alpha and beta releases are published to preview upcoming features that may not yet be stable - in particular to preview new plugin hooks. You are welcome to try these out, but please be aware that details may change before the final release. Please join discussions on the issue tracker to share your thoughts and experiences with on alpha and beta features that you try out. | 181 | |
| 40 | 40 | Releasing bug fixes from a branch | If it's necessary to publish a bug fix release without shipping new features that have landed on main a release branch can be used. Create it from the relevant last tagged release like so: git branch 0.52.x 0.52.4 git checkout 0.52.x Next cherry-pick the commits containing the bug fixes: git cherry-pick COMMIT Write the release notes in the branch, and update the version number in version.py . Then push the branch: git push -u origin 0.52.x Once the tests have completed, publish the release from that branch target using the GitHub Draft a new release form. Finally, cherry-pick the commit with the release notes and version number bump across to main : git checkout main git cherry-pick COMMIT git push | 181 | |
| 41 | 41 | 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. | 181 | |
| 42 | 42 | Full-text search | SQLite includes a powerful mechanism for enabling full-text search against SQLite records. Datasette can detect if a table has had full-text search configured for it in the underlying database and display a search interface for filtering that table. Here's an example search : Datasette automatically detects which tables have been configured for full-text search. | 181 | |
| 43 | 43 | 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 | 181 | |
| 44 | 44 | Advanced SQLite search queries | SQLite full-text search includes support for a variety of advanced queries , including AND , OR , NOT and NEAR . By default Datasette disables these features to ensure they do not cause errors or confusion for users who are not aware of them. You can disable this escaping and use the advanced queries by adding &_searchmode=raw to the table page query string. If you want to enable these operators by default for a specific table, you can do so by adding "searchmode": "raw" to the metadata configuration for that table, see Configuring full-text search for a table or view . If that option has been specified in the table metadata but you want to over-ride it and return to the default behavior you can append &_searchmode=escaped to the query string. | 181 | |
| 45 | 45 | Configuring full-text search for a table or view | If a table has a corresponding FTS table set up using the content= argument to CREATE VIRTUAL TABLE shown below, Datasette will detect it automatically and add a search interface to the table page for that table. You can also manually configure which table should be used for full-text search using query string parameters or table configuration in datasette.yaml (see fts_table / fts_pk / searchmode ). You can set the associated FTS table for a specific table and you can also set one for a view - if you do that, the page for that SQL view will offer a search option. Use ?_fts_table=x to over-ride the FTS table for a specific page. If the primary key was something other than rowid you can use ?_fts_pk=col to set that as well. This is particularly useful for views, for example: https://latest.datasette.io/fixtures/searchable_view?_fts_table=searchable_fts&_fts_pk=pk The fts_table metadata property can be used to specify an associated FTS table. If the primary key column in your table which was used to populate the FTS table is something other than rowid , you can specify the column to use with the fts_pk property. The "searchmode": "raw" property can be used to default the table to accepting SQLite advanced search operators, as described in Advanced SQLite search queries . Here is an example which enables full-text search (with SQLite advanced search operators) for a display_ads view which is defined against the ads table and hence needs to run FTS against the ads_fts table, using the id as the primary key: [[[cog from metadata_doc import config_example config_example(cog, { "databases": { "russian-ads": { "tables": { "display_ads": { "fts_table": "ads_fts", "fts_pk": "id", "searchmode": "raw" } } } } }) ]]] [[[end]]] | 181 | |
| 46 | 46 | 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 | 181 | |
| 47 | 47 | Enabling full-text search for a SQLite table | Datasette takes advantage of the external content mechanism in SQLite, which allows a full-text search virtual table to be associated with the contents of another SQLite table. To set up full-text search for a table, you need to do two things: Create a new FTS virtual table associated with your table Populate that FTS table with the data that you would like to be able to run searches against | 181 | |
| 48 | 48 | Configuring FTS using sqlite-utils | sqlite-utils is a CLI utility and Python library for manipulating SQLite databases. You can use it from Python code to configure FTS search, or you can achieve the same goal using the accompanying command-line tool . Here's how to use sqlite-utils to enable full-text search for an items table across the name and description columns: sqlite-utils enable-fts mydatabase.db items name description | 181 | |
| 49 | 49 | Configuring FTS using csvs-to-sqlite | If your data starts out in CSV files, you can use Datasette's companion tool csvs-to-sqlite to convert that file into a SQLite database and enable full-text search on specific columns. For a file called items.csv where you want full-text search to operate against the name and description columns you would run the following: csvs-to-sqlite items.csv items.db -f name -f description | 181 | |
| 50 | 50 | Configuring FTS by hand | We recommend using sqlite-utils , but if you want to hand-roll a SQLite full-text search table you can do so using the following SQL. To enable full-text search for a table called items that works against the name and description columns, you would run this SQL to create a new items_fts FTS virtual table: CREATE VIRTUAL TABLE "items_fts" USING FTS4 ( name, description, content="items" ); This creates a set of tables to power full-text search against items . The new items_fts table will be detected by Datasette as the fts_table for the items table. Creating the table is not enough: you also need to populate it with a copy of the data that you wish to make searchable. You can do that using the following SQL: INSERT INTO "items_fts" (rowid, name, description) SELECT rowid, name, description FROM items; If your table has columns that are foreign key references to other tables you can include that data in your full-text search index using a join. Imagine the items table has a foreign key column called category_id which refers to a categories table - you could create a full-text search table like this: CREATE VIRTUAL TABLE "items_fts" USING FTS4 ( name, description, category_name, content="items" ); And then populate it like this: INSERT INTO "items_fts" (rowid, name, description, category_name) SELECT items.rowid, items.name, items.description, categories.name FROM items JOIN categories ON items.category_id=categories.id; You can use this technique to populate the full-text search index from any combination of tables and joins that makes sense for your project. | 181 | |
| 51 | 51 | FTS versions | There are three different versions of the SQLite FTS module: FTS3, FTS4 and FTS5. You can tell which versions are supported by your instance of Datasette by checking the /-/versions page. FTS5 is the most advanced module but may not be available in the SQLite version that is bundled with your Python installation. Most importantly, FTS5 is the only version that has the ability to order by search relevance without needing extra code. If you can't be sure that FTS5 will be available, you should use FTS4. | 181 | |
| 52 | 52 | Upgrade guide | 181 | ||
| 53 | 53 | Datasette 0.X -> 1.0 | This section reviews breaking changes Datasette 1.0 has when upgrading from a 0.XX version. For new features that 1.0 offers, see the Changelog . | 181 | |
| 54 | 54 | New URL for SQL queries | Prior to 1.0a14 the URL for executing a SQL query looked like this: /databasename?sql=select+1 # Or for JSON: /databasename.json?sql=select+1 This endpoint served two purposes: without a ?sql= it would list the tables in the database, but with that option it would return results of a query instead. The URL for executing a SQL query now looks like this: /databasename/-/query?sql=select+1 # Or for JSON: /databasename/-/query.json?sql=select+1 This isn't a breaking change. API calls to the older /databasename?sql=... endpoint will redirect to the new databasename/-/query?sql=... endpoint. Upgrading to the new URL is recommended to avoid the overhead of the additional redirect. | 181 | |
| 55 | 55 | Metadata changes | Metadata was completely revamped for Datasette 1.0. There are a number of related breaking changes, from the metadata.yaml file to Python APIs, that you'll need to consider when upgrading. | 181 | |
| 56 | 56 | Before Datasette 1.0, the metadata.yaml file became a kitchen sink if a mix of metadata, configuration, and settings. Now metadata.yaml is strictly for metadata (ex title and descriptions of database and tables, licensing info, etc). Other settings have been moved to a datasette.yml configuration file, described in Configuration . To start Datasette with both metadata and configuration files, run it like this: datasette --metadata metadata.yaml --config datasette.yaml # Or the shortened version: datasette -m metadata.yml -c datasette.yml | 181 | ||
| 57 | 57 | Upgrading an existing | The datasette-upgrade plugin can be used to split a Datasette 0.x.x metadata.yaml (or .json ) file into separate metadata.yaml and datasette.yaml files. First, install the plugin: datasette install datasette-upgrade Then run it like this to produce the two new files: datasette upgrade metadata-to-config metadata.json -m metadata.yml -c datasette.yml | 181 | |
| 58 | 58 | Metadata "fallback" has been removed | Certain keys in metadata like license used to "fallback" up the chain of ownership. For example, if you set an MIT to a database and a table within that database did not have a specified license, then that table would inherit an MIT license. This behavior has been removed in Datasette 1.0. Now license fields must be placed on all items, including individual databases and tables. | 181 | |
| 59 | 59 | The | In Datasette 0.x plugins could implement a get_metadata() plugin hook to customize how metadata was retrieved for different instances, databases and tables. This hook could be inefficient, since some pages might load metadata for many different items (to list a large number of tables, for example) which could result in a large number of calls to potentially expensive plugin hook implementations. As of Datasette 1.0a14 (2024-08-05), the get_metadata() hook has been deprecated: # ❌ DEPRECATED in Datasette 1.0 @hookimpl def get_metadata(datasette, key, database, table): pass Instead, plugins are encouraged to interact directly with Datasette's in-memory metadata tables in SQLite using the following methods on the Datasette class : get_instance_metadata() and set_instance_metadata() get_database_metadata() and set_database_metadata() get_resource_metadata() and set_resource_metadata() get_column_metadata() and set_column_metadata() A plugin that stores or calculates its own metadata can implement the startup(datasette) hook to populate those items on startup, and then call those methods while it is running to persist any new metadata changes. | 181 | |
| 60 | 60 | The | As of Datasette 1.0a14 , the root level /metadata.json endpoint has been removed. Metadata for tables will become available through currently in-development extras in a future alpha. | 181 | |
| 61 | 61 | The | As of Datasette 1.0a14 , the .metadata() method on the Datasette Python API has been removed. Instead, one should use the following methods on a Datasette class: get_instance_metadata() get_database_metadata() get_resource_metadata() get_column_metadata() | 181 | |
| 62 | 62 | Datasette 1.0a20 plugin upgrade guide | Datasette 1.0a20 makes some breaking changes to Datasette's permission system. Plugins need to be updated if they use any of the following : The register_permissions() plugin hook - this should be replaced with register_actions The permission_allowed() plugin hook - this should be upgraded to use permission_resources_sql() . The datasette.permission_allowed() internal method - this should be replaced with datasette.allowed() Logic that grants access to the "root" actor can be removed. | 181 | |
| 63 | 63 | Permissions are now actions | The register_permissions() hook shoud be replaced with register_actions() . Old code: @hookimpl def register_permissions(datasette): return [ Permission( name="explain-sql", abbr=None, description="Can explain SQL queries", takes_database=True, takes_resource=False, default=False, ), Permission( name="annotate-rows", abbr=None, description="Can annotate rows", takes_database=True, takes_resource=True, default=False, ), Permission( name="view-debug-info", abbr=None, description="Can view debug information", takes_database=False, takes_resource=False, default=False, ), ] The new Action does not have a default= parameter. Here's the equivalent new code: from datasette import hookimpl from datasette.permissions import Action from datasette.resources import DatabaseResource, TableResource @hookimpl def register_actions(datasette): return [ Action( name="explain-sql", description="Explain SQL queries", resource_class=DatabaseResource, ), Action( name="annotate-rows", description="Annotate rows", resource_class=TableResource, ), Action( name="view-debug-info", description="View debug information", ), ] The abbr= is now optional and defaults to None . For actions that apply to specific resources (like databases or tables), specify the resource_class instead of takes_parent and takes_child . Note that view-debug-info does not specify a resource_class because it applies globally. | 181 | |
| 64 | 64 | permission_allowed() hook is replaced by permission_resources_sql() | The following old code: @hookimpl def permission_allowed(action): if action == "permissions-debug": return True Can be replaced by: from datasette.permissions import PermissionSQL @hookimpl def permission_resources_sql(action): return PermissionSQL.allow(reason="datasette-allow-permissions-debug") A .deny(reason="") class method is also available. For more complex permission checks consult the documentation for that plugin hook: https://docs.datasette.io/en/latest/plugin_hooks.html#permission-resources-sql-datasette-actor-action | 181 | |
| 65 | 65 | Using datasette.allowed() to check permissions instead of datasette.permission_allowed() | The internal method datasette.permission_allowed() has been replaced by datasette.allowed() . The old method looked like this: can_debug = await datasette.permission_allowed( request.actor, "view-debug-info", ) can_explain_sql = await datasette.permission_allowed( request.actor, "explain-sql", resource="database_name", ) can_annotate_rows = await datasette.permission_allowed( request.actor, "annotate-rows", resource=(database_name, table_name), ) Note the confusing design here where resource could be either a string or a tuple depending on the permission being checked. The new keyword-only design makes this a lot more clear: from datasette.resources import DatabaseResource, TableResource can_debug = await datasette.allowed( actor=request.actor, action="view-debug-info", ) can_explain_sql = await datasette.allowed( actor=request.actor, action="explain-sql", resource=DatabaseResource(database_name), ) can_annotate_rows = await datasette.allowed( actor=request.actor, action="annotate-rows", resource=TableResource(database_name, table_name), ) | 181 | |
| 66 | 66 | Root user checks are no longer necessary | Some plugins would introduce their own custom permission and then ensure the "root" actor had access to it using a pattern like this: @hookimpl def register_permissions(datasette): return [ Permission( name="upload-dbs", abbr=None, description="Upload SQLite database files", takes_database=False, takes_resource=False, default=False, ) ] @hookimpl def permission_allowed(actor, action): if action == "upload-dbs" and actor and actor.get("id") == "root": return True This is no longer necessary in Datasette 1.0a20 - the "root" actor automatically has all permissions when Datasette is started with the datasette --root option. The permission_allowed() hook in this example can be entirely removed. | 181 | |
| 67 | 67 | Root-enabled instances during testing | When writing tests that exercise root-only functionality, make sure to set datasette.root_enabled = True on the Datasette instance. Root permissions are only granted automatically when Datasette is started with datasette --root or when the flag is enabled directly in tests. | 181 | |
| 68 | 68 | Target the new APIs exclusively | Datasette 1.0a20’s permission system is substantially different from previous releases. Attempting to keep plugin code compatible with both the old permission_allowed() and the new allowed() interfaces leads to brittle workarounds. Prefer to adopt the 1.0a20 APIs ( register_actions , permission_resources_sql() , and datasette.allowed() ) outright and drop legacy fallbacks. | 181 | |
| 69 | 69 | Fixing async with httpx.AsyncClient(app=app) | Some older plugins may use the following pattern in their tests, which is no longer supported: app = Datasette([], memory=True).app() async with httpx.AsyncClient(app=app) as client: response = await client.get("http://localhost/path") The new pattern is to use ds.client like this: ds = Datasette([], memory=True) response = await ds.client.get("/path") | 181 | |
| 70 | 70 | Migrating from metadata= to config= | Datasette 1.0 separates metadata (titles, descriptions, licenses) from configuration (settings, plugins, queries, permissions). Plugin tests and code need to be updated accordingly. | 181 | |
| 71 | 71 | Update test constructors | Old code: ds = Datasette( memory=True, metadata={ "databases": { "_memory": {"queries": {"my_query": {"sql": "select 1", "title": "My Query"}}} }, "plugins": { "my-plugin": {"setting": "value"} } } ) New code: ds = Datasette( memory=True, config={ "databases": { "_memory": {"queries": {"my_query": {"sql": "select 1", "title": "My Query"}}} }, "plugins": { "my-plugin": {"setting": "value"} } } ) | 181 | |
| 72 | 72 | Update datasette.metadata() calls | The datasette.metadata() method has been removed. Use these methods instead: Old code: try: title = datasette.metadata(database=database)["queries"][query_name]["title"] except (KeyError, TypeError): pass New code: try: query_info = await datasette.get_query(database, query_name) if query_info and "title" in query_info: title = query_info["title"] except (KeyError, TypeError): pass | 181 | |
| 73 | 73 | Update render functions to async | If your plugin's render function needs to call datasette.get_query() or other async Datasette methods, it must be declared as async: Old code: def render_atom(datasette, request, sql, columns, rows, database, table, query_name, view_name, data): # ... if query_name: title = datasette.metadata(database=database)["queries"][query_name]["title"] New code: async def render_atom(datasette, request, sql, columns, rows, database, table, query_name, view_name, data): # ... if query_name: query_info = await datasette.get_query(database, query_name) if query_info and "title" in query_info: title = query_info["title"] | 181 | |
| 74 | 74 | Update query URLs in tests | Datasette now redirects ?sql= parameters from database pages to the query view: Old code: response = await ds.client.get("/_memory.atom?sql=select+1") New code: response = await ds.client.get("/_memory/-/query.atom?sql=select+1") | 181 | |
| 75 | 75 | Datasette 1.0a25: | datasette.create_token() is now an async method (previously it was synchronous). The restrict_all , restrict_database , and restrict_resource keyword arguments have been replaced by a single restrictions parameter that accepts a TokenRestrictions object. Old code: token = datasette.create_token( actor_id="user1", restrict_all=["view-instance", "view-table"], restrict_database={"docs": ["view-query"]}, restrict_resource={ "docs": { "attachments": ["insert-row", "update-row"] } }, ) New code: from datasette.tokens import TokenRestrictions token = await datasette.create_token( actor_id="user1", restrictions=( TokenRestrictions() .allow_all("view-instance") .allow_all("view-table") .allow_database("docs", "view-query") .allow_resource("docs", "attachments", "insert-row") .allow_resource("docs", "attachments", "update-row") ), ) The datasette create-token CLI command is unchanged. | 181 | |
| 76 | 76 | CSRF protection is now header-based | Datasette's Cross-Site Request Forgery protection no longer uses tokens. The previous asgi-csrf mechanism - which set a ds_csrftoken cookie and required a matching <input type="hidden" name="csrftoken"> in every form - has been replaced with an ASGI middleware that inspects the browser-set Sec-Fetch-Site and Origin headers, following the approach described in Filippo Valsorda's research and implemented in Go 1.25's http.CrossOriginProtection . This works identically on HTTPS, HTTP, and localhost. Non-browser clients (curl, Python requests , server-to-server scripts) do not send Sec-Fetch-Site or Origin and are passed through unchanged - CSRF is a browser-only attack. Requests that carry an explicit Authorization: Bearer ... header are also exempt from the CSRF check, because bearer tokens are not ambient browser credentials: a malicious cross-origin page cannot cause the browser to attach a target site's bearer token unless the attacker's JavaScript already possesses it. This exemption is narrow - it covers the Bearer scheme only, not Basic or Digest - and it does not depend on the --cors setting. The exemption is about CSRF classification, not browser read access; CORS still controls the latter. | 181 | |
| 77 | 77 | What you can remove | You can now delete any of the following from your plugins and custom templates: Hidden CSRF form fields: <input type="hidden" name="csrftoken" value="{{ csrftoken() }}"> The csrftoken() template helper (and request.scope["csrftoken"]() for plugins that call it from Python) still exists as a compatibility shim. It now returns a per-request random string rather than a cookie-bound signed value. Datasette no longer validates this token, and no ds_csrftoken cookie is set. Important for plugin authors: if your plugin previously used request.scope["csrftoken"]() or the ds_csrftoken cookie as a security primitive (for example, signing a URL and later comparing it to the cookie), the invariant that the token equals request.cookies["ds_csrftoken"] no longer holds. Replace those flows with signed, short-lived action URLs or explicit non-ambient credentials. Manual CSRF token extraction in tests, e.g.: # No longer needed csrftoken = response.cookies["ds_csrftoken"] cookies["ds_csrftoken"] = csrftoken post_data["csrftoken"] = csrftoken The ds_csrftoken cookie is no longer set at all. The csrftoken_from= argument of the Datasette test client's .post() method is now a no-op and can be removed from your test code. | 181 | |
| 78 | 78 | Breaking changes | The skip_csrf plugin hook has been removed. Existing plugins that still declare a skip_csrf hookimpl will continue to load - pluggy silently ignores unknown hook names - but the hook is no longer consulted by core, so the flows it previously unlocked will now be blocked (or allowed) purely on the basis of the new header check. The new middleware already covers the common cases that skip_csrf was written for: Browser-initiated JSON POSTs automatically get Sec-Fetch-Site: same-origin and pass the check. Non-browser API clients (curl, requests , server-to-server scripts) do not send browser security headers and are passed through. Requests with an explicit Authorization: Bearer ... header are exempt from the CSRF check (see above). If your plugin previously used skip_csrf to accept cross-origin browser POSTs, replace that flow with an authentication mechanism that does not rely on ambient browser credentials. Safe patterns include: Requiring an Authorization: Bearer ... API token on the endpoint. Requiring a non-ambient credential in the request body (a webhook secret, HMAC signature, signed capability URL, OAuth client credential, or similar). Issuing a short-lived signed URL that encodes the actor, the action, and an expiry, and verifying the signature on req… | 181 | |
| 79 | 79 | Security properties | For defense-in-depth the ds_actor and ds_messages cookies continue to be set with SameSite=Lax (Datasette's long-standing default). This means a genuine cross-site POST from an attacker's page would arrive without the user's authentication cookie even if the header check somehow failed. | 181 | |
| 80 | 80 | Getting started | 181 | ||
| 81 | 81 | Play with a live demo | The best way to experience Datasette for the first time is with a demo: datasette.io/global-power-plants provides a searchable database of power plants around the world, using data from the World Resources Institude rendered using the datasette-cluster-map plugin. fivethirtyeight.datasettes.com shows Datasette running against over 400 datasets imported from the FiveThirtyEight GitHub repository . | 181 | |
| 82 | 82 | 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. | 181 | |
| 83 | 83 | Datasette in your browser with Datasette Lite | Datasette Lite is Datasette packaged using WebAssembly so that it runs entirely in your browser, no Python web application server required. You can pass a URL to a CSV, SQLite or raw SQL file directly to Datasette Lite to explore that data in your browser. This example link opens Datasette Lite and loads the SQL Murder Mystery example database from Northwestern University Knight Lab . | 181 | |
| 84 | 84 | Try Datasette without installing anything with Codespaces | GitHub Codespaces offers a free browser-based development environment that lets you run a development server without installing any local software. Here's a demo project on GitHub which you can use as the basis for your own experiments: github.com/datasette/datasette-studio The README file in that repository has instructions on how to get started. | 181 | |
| 85 | 85 | 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", ... } ] } | 181 | |
| 86 | 86 | CSV export | Any Datasette table, view or custom SQL query can be exported as CSV. To obtain the CSV representation of the table you are looking, click the "this data as CSV" link. You can also use the advanced export form for more control over the resulting file, which looks like this and has the following options: download file - instead of displaying CSV in your browser, this forces your browser to download the CSV to your downloads directory. expand labels - if your table has any foreign key references this option will cause the CSV to gain additional COLUMN_NAME_label columns with a label for each foreign key derived from the linked table. In this example the city_id column is accompanied by a city_id_label column. stream all rows - by default CSV files only contain the first max_returned_rows records. This option will cause Datasette to loop through every matching record and return them as a single CSV file. You can try that out on https://latest.datasette.io/fixtures/facetable?_size=4 | 181 | |
| 87 | 87 | URL parameters | The following options can be used to customize the CSVs returned by Datasette. ?_header=off This removes the first row of the CSV file specifying the headings - only the row data will be returned. ?_stream=on Stream all matching records, not just the first page of results. See below. ?_dl=on Causes Datasette to return a content-disposition: attachment; filename="filename.csv" header. | 181 | |
| 88 | 88 | Streaming all records | The stream all rows option is designed to be as efficient as possible - under the hood it takes advantage of Python 3 asyncio capabilities and Datasette's efficient pagination to stream back the full CSV file. Since databases can get pretty large, by default this option is capped at 100MB - if a table returns more than 100MB of data the last line of the CSV will be a truncation error message. You can increase or remove this limit using the max_csv_mb config setting. You can also disable the CSV export feature entirely using allow_csv_stream . | 181 | |
| 89 | 89 | JavaScript plugins | Datasette can run custom JavaScript in several different ways: Datasette plugins written in Python can use the extra_js_urls() or extra_body_script() plugin hooks to inject JavaScript into a page Datasette instances with custom templates can include additional JavaScript in those templates The extra_js_urls key in datasette.yaml can be used to include extra JavaScript There are no limitations on what this JavaScript can do. It is executed directly by the browser, so it can manipulate the DOM, fetch additional data and do anything else that JavaScript is capable of. Custom JavaScript has security implications, especially for authenticated Datasette instances where the JavaScript might run in the context of the authenticated user. It's important to carefully review any JavaScript you run in your Datasette instance. | 181 | |
| 90 | 90 | The datasette_init event | Datasette emits a custom event called datasette_init when the page is loaded. This event is dispatched on the document object, and includes a detail object with a reference to the datasetteManager object. Your JavaScript code can listen out for this event using document.addEventListener() like this: document.addEventListener("datasette_init", function (evt) { const manager = evt.detail; console.log("Datasette version:", manager.VERSION); }); | 181 | |
| 91 | 91 | datasetteManager | The datasetteManager object VERSION - string The version of Datasette plugins - Map() A Map of currently loaded plugin names to plugin implementations registerPlugin(name, implementation) Call this to register a plugin, passing its name and implementation makeColumnField(context) Calls the makeColumnField() hook on registered plugins, returning the first custom insert/edit field control that matches the provided field context. This is used internally by Datasette's row insert and edit dialogs. selectors - object An object providing named aliases to useful CSS selectors, listed below | 181 | |
| 92 | 92 | JavaScript plugin objects | JavaScript plugins are blocks of code that can be registered with Datasette using the registerPlugin() method on the datasetteManager object. The implementation object passed to this method should include a version key defining the plugin version, and one or more of the following named functions providing the implementation of the plugin: | 181 | |
| 93 | 93 | makeJumpSections(context) | This method should return a JavaScript array of objects defining additional sections to be added to the blank state of the / jump menu, before the user starts typing a search. It should return an array of objects, each with the following: id - string A unique string ID for the section, for example agent-chat render(node, context) - function A function that will be called with a DOM node to render the section into Datasette passes a context object to both makeJumpSections(context) and render(node, context) . It has the following keys: navigationSearch The <navigation-search> custom element instance. container - only for render() The .results-container element used by the jump menu. input - only for render() The .search-input element used by the jump menu. This example shows how a plugin might add a button for starting a new chat: document.addEventListener('datasette_init', function(ev) { ev.detail.registerPlugin('agent-plugin', { version: 0.1, makeJumpSections: (context) => { return [ { id: 'agent-chat', render: (node, context) => { node.innerHTML = '<b… | 181 | |
| 94 | 94 | makeAboveTablePanelConfigs() | This method should return a JavaScript array of objects defining additional panels to be added to the top of the table page. Each object should have the following: id - string A unique string ID for the panel, for example map-panel label - string A human-readable label for the panel render(node) - function A function that will be called with a DOM node to render the panel into This example shows how a plugin might define a single panel: document.addEventListener('datasette_init', function(ev) { ev.detail.registerPlugin('panel-plugin', { version: 0.1, makeAboveTablePanelConfigs: () => { return [ { id: 'first-panel', label: 'First panel', render: node => { node.innerHTML = '<h2>My custom panel</h2><p>This is a custom panel that I added using a JavaScript plugin</p>'; } } ] } }); }); When a page with a table loads, all registered plugins that implement makeAboveTablePanelConfigs() will be called and panels they return will be added to the top of the table page. | 181 | |
| 95 | 95 | makeColumnActions(columnDetails) | This method, if present, will be called when Datasette is rendering the cog action menu icons that appear at the top of the table view. By default these include options like "Sort ascending/descending" and "Facet by this", but plugins can return additional actions to be included in this menu. The method will be called with a columnDetails object with the following keys: columnName - string The name of the column columnNotNull - boolean True if the column is defined as NOT NULL columnType - string The SQLite data type of the column isPk - boolean True if the column is part of the primary key It should return a JavaScript array of objects each with a label and onClick property: label - string The human-readable label for the action onClick(evt) - function A function that will be called when the action is clicked The evt object passed to the onClick is the standard browser event object that triggered the click. This example plugin adds two menu items - one to copy … | 181 | |
| 96 | 96 | makeColumnField(context) | This method, if present, can provide a custom form field for a column in Datasette's row insert and edit dialogs. It is designed for plugins that register custom column types using the Python register_column_types() plugin hook. For example, a plugin that defines a file column type can use makeColumnField() to replace a plain text input with a file picker, and a plugin that defines a rich text column type can use it to enhance the field with an editor. Datasette calls makeColumnField(context) on each registered JavaScript plugin when it renders an editable insert/edit field. Plugins should inspect the context object and only return a control object if they can handle that field. Otherwise, use a bare return; . The first plugin to return a truthy control object is used for that field. Plugins are called in registration order. If a plugin raises an exception, Datasette logs the error to the browser console and continues to the next plugin. The row dialog tracks the value that will be sent to the insert/update API. The context object describes the column and form environment; custom controls should read and write field values using the field helper object passed to render(field) . | 181 | |
| 97 | 97 | Context object | makeColumnField(context) is called with a context object describing the field. The current context object has these keys: mode - string "insert" or "edit" . database - string or null The database name. table - string or null The table name. tableUrl - string or null The path to the table page, including any configured base URL prefix . column - string The column name. columnType - object or null The configured Datasette column type for this column, if one exists. This is null if no column type has been configured. If present, this object has exactly these keys: type - string The registered column type name , matching the name attribute of the Python ColumnType subclass. … | 181 | |
| 98 | 98 | Returned control object | A plugin that wants to handle a field should return an object. Datasette currently recognizes these properties: useTextarea - boolean, optional If true, Datasette creates a <textarea> as the underlying field.input before calling render() . If omitted, Datasette chooses either an <input> or <textarea> based on the column type and current value. render(field) - function Called once to render the custom field UI. field is a helper object described below. The recommended pattern is to return a DOM node from render() . Datasette appends that node to field.root , a <div> inside the control area for that field in the row insert/edit dialog. A plugin can alternatively manipulate field.root directly and return nothing. focus(field) - function, optional Called when Datasette wants to focus this field, for example when focusing the first editable field in the dialog. Use this to focus the most useful interactive element inside the custom UI. destroy(field) - function, optional Called when Datasette tears down the insert/edit form. Use this to remove event listeners, close nested pickers, revoke object URLs, clear timers, or release other resources. | 181 | |
| 99 | 99 | The field helper object | The field object passed to render(field) , focus(field) and destroy(field) provides stable IDs, DOM elements and value helpers for integrating with the row insert/edit dialog: context - object The original context object passed to makeColumnField() . id - string The ID Datasette assigned to field.input , the backing <input> or <textarea> element. labelId - string The ID of the visible field label. descriptionId - string The ID of the field metadata/help text. This metadata can include details such as Primary key , Required , Current value: NULL or Custom type: file . root - HTMLElement The empty <div> container created by Datasette for this custom field. It is inside the control area for the field in the row insert/edit dialog, next to the field label and above the field metadata. Datasette appends the DOM node returned by render(field) to this element. Plugins can alternatively manipulate this element directly and return nothing from render(field) . input - HTMLInputElement or HTMLTextAreaElement … | 181 | |
| 100 | 100 | Submitted value contract | The field.setValue() method accepts the following value types: string number boolean null These values are used as column values in requests to the insert rows and update row JSON APIs. Plugins should not pass objects or arrays to field.setValue() . If a column stores structured data in SQLite, such as JSON in a TEXT column, the plugin should serialize that data first and submit the serialized string. Client-side parsing can still be useful for validation or editor state, but the submitted value should match the SQLite value Datasette should write. | 181 |
Advanced export
JSON shape: default, array, newline-delimited
CREATE VIRTUAL TABLE "sections_fts" USING FTS5 (
"title", "content",
tokenize='porter',
content="sections"
);