datasette Documentation

brew install datasette

pip install datasette

datasette serve path/to/database.db

datasette ~/Library/Application\ Support/Google/Chrome/Default/History --nolock

{
    "title": "Five Thirty Eight",
    "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"
}

datasette serve fivethirtyeight.db -m metadata.json

datasette publish heroku database.db

datasette publish cloudrun database.db

docker build -t datasette-apache-proxy-demo . \
  --build-arg DATASETTE_REF=c617e1769ea27e045b0f2907ef49a9a1244e577d

docker run -p 5000:80 datasette-apache-proxy-demo

flyctl apps create --name datasette-apache-proxy-demo

flyctl deploy --build-arg DATASETTE_REF=main

DATASETTE_REF=main ./deploy-to-cloud-run.sh

datasette --root

http://127.0.0.1:8001/-/auth-token?token=786fc524e0199d70dc9a581d851f466244e114ca92f33aa3b42a139e9388daa7
INFO:     Started server process [25801]
INFO:     Waiting for application startup.
INFO:     Application startup complete.
INFO:     Uvicorn running on http://127.0.0.1:8001 (Press CTRL+C to quit)

{
    "id": "root"
}

Is this **actor** allowed to perform this **action**, optionally against this particular **resource**?

datasette --default-deny data.db --root

datasette --default-deny data.db --config datasette.yaml

allow:
  id: alice

.. code-block:: yaml

    allow:
      id: root

.. code-block:: json

    {
      "allow": {
        "id": "root"
      }
    }

{
    "id": "root",
    "name": "Root User"
}

.. code-block:: yaml

    allow: false

.. code-block:: json

    {
      "allow": false
    }

.. code-block:: yaml

    allow: true

.. code-block:: json

    {
      "allow": true
    }

.. code-block:: yaml

    allow:
      id:
      - simon
      - cleopaws

.. code-block:: json

    {
      "allow": {
        "id": [
          "simon",
          "cleopaws"
        ]
      }
    }

{
      "id": "simon",
      "roles": ["staff", "developer"]
  }

.. code-block:: yaml

    allow:
      roles:
      - developer

.. code-block:: json

    {
      "allow": {
        "roles": [
          "developer"
        ]
      }
    }

.. code-block:: yaml

    allow:
      id: "*"

.. code-block:: json

    {
      "allow": {
        "id": "*"
      }
    }

.. code-block:: yaml

    allow:
      unauthenticated: true

.. code-block:: json

    {
      "allow": {
        "unauthenticated": true
      }
    }

.. code-block:: yaml

    allow:
      id:
      - simon
      - cleopaws
      role: ops

.. code-block:: json

    {
      "allow": {
        "id": [
          "simon",
          "cleopaws"
        ],
        "role": "ops"
      }
    }

.. code-block:: yaml


        title: My private Datasette instance
        allow:
          id: root

.. code-block:: json

    {
      "title": "My private Datasette instance",
      "allow": {
        "id": "root"
      }
    }

.. code-block:: yaml


        title: My entirely inaccessible instance
        allow: false

.. code-block:: json

    {
      "title": "My entirely inaccessible instance",
      "allow": false
    }

.. code-block:: yaml


        databases:
          private:
            allow:
              id: "*"

.. code-block:: json

    {
      "databases": {
        "private": {
          "allow": {
            "id": "*"
          }
        }
      }
    }

.. code-block:: yaml


        databases:
          bakery:
            tables:
              users:
                allow:
                  id: '*'

.. code-block:: json

    {
      "databases": {
        "bakery": {
          "tables": {
            "users": {
              "allow": {
                "id": "*"
              }
            }
          }
        }
      }
    }

If you are restricting access to specific tables you should also use the ``"allow_sql"`` block to prevent users from bypassing the limit with their own SQL queries - see :ref:`authentication_permissions_execute_sql`.

.. code-block:: yaml


        databases:
          dogs:
            queries:
              add_name:
                sql: INSERT INTO names (name) VALUES (:name)
                write: true
                allow:
                  id:
                  - root

.. code-block:: json

    {
      "databases": {
        "dogs": {
          "queries": {
            "add_name": {
              "sql": "INSERT INTO names (name) VALUES (:name)",
              "write": true,
              "allow": {
                "id": [
                  "root"
                ]
              }
            }
          }
        }
      }
    }

.. code-block:: yaml


        allow_sql: false

.. code-block:: json

    {
      "allow_sql": false
    }

.. code-block:: yaml


        allow_sql:
          id: root

.. code-block:: json

    {
      "allow_sql": {
        "id": "root"
      }
    }

.. code-block:: yaml


        databases:
          mydatabase:
            allow_sql:
              id: root

.. code-block:: json

    {
      "databases": {
        "mydatabase": {
          "allow_sql": {
            "id": "root"
          }
        }
      }
    }

.. code-block:: yaml


        permissions:
          debug-menu:
            id: '*'

.. code-block:: json

    {
      "permissions": {
        "debug-menu": {
          "id": "*"
        }
      }
    }

.. code-block:: yaml


        databases:
          docs:
            permissions:
              create-table:
                id: editor

.. code-block:: json

    {
      "databases": {
        "docs": {
          "permissions": {
            "create-table": {
              "id": "editor"
            }
          }
        }
      }
    }

.. code-block:: yaml


        databases:
          docs:
            tables:
              reports:
                permissions:
                  insert-row:
                    id: editor

.. code-block:: json

    {
      "databases": {
        "docs": {
          "tables": {
            "reports": {
              "permissions": {
                "insert-row": {
                  "id": "editor"
                }
              }
            }
          }
        }
      }
    }

{
    "id": "user_id",
    "token": "dstok",
    "token_expires": 1667717426
}

datasette create-token root --expires-after 3600

datasette create-token root --secret my-secret-goes-here

Tokens created using ``datasette create-token ACTOR_ID`` will inherit all of the permissions of the actor that they are associated with.

You can pass additional options to create tokens that are restricted to a subset of that actor's permissions.

To restrict the token to just specific permissions against all available databases, use the ``--all`` option::

    datasette create-token root --all insert-row --all update-row

This option can be passed as many times as you like. In the above example the token will only be allowed to insert and update rows.

You can also restrict permissions such that they can only be used within specific databases::

    datasette create-token root --database mydatabase insert-row

The resulting token will only be able to insert rows, and only to tables in the ``mydatabase`` database.

Finally, you can restrict permissions to individual resources - tables, SQL views and :ref:`named queries <queries>` - within a specific database::

    datasette create-token root --resource mydatabase mytable insert-row

These options have short versions: ``-a`` for ``--all``, ``-d`` for ``--database`` and ``-r`` for ``--resource``.

You can add ``--debug`` to see a JSON representation of the token that has been created. Here's a full example::

    datasette create-token root \
        --secret mysecret \
        --all view-instance \
        --all view-table \
        --database docs view-query \
        --resource docs documents insert-row \
        --resource docs documents update-row \
        --debug

This example outputs the following::

    dstok_.eJxFizEKgDAMRe_y5w4qYrFXERGxDkVsMI0uxbubdjFL8l_ez1jhwEQCA6Fjjxp90qtkuHawzdjYrh8MFobLxZ_wBH0_gtnAF-hpS5VfmF8D_lnd97lHqUJgLd6sls4H1qwlhA.nH_7RecYHj5qSzvjhMU95iy0Xlc

    Decoded:

    {
      "a": "root",
      "token": "dstok",
      "t": 1670907246,
      "_r": {
        "a": [
          "vi",
          "vt"
        ],
        "d": {
          "docs": [
            "vq"
          ]
        },
        "r": {
          "docs": {
            "documents": [
              "ir",
              "ur"
            ]
          }
        }
      }
    }

Restrictions act as an allowlist layered on top of the actor's existing
permissions. They can only remove access the actor would otherwise have—they
cannot grant new access. If the underlying actor is denied by ``allow`` rules in
``datasette.yaml`` or by a plugin, a token that lists that resource in its
``"_r"`` section will still be denied.

To create tokens with restrictions in Python code, use the :ref:`TokenRestrictions <TokenRestrictions>` builder and pass it to :ref:`datasette.create_token() <datasette_create_token>`.

.. _permissions_plugins:

Checking permissions in plugins
===============================

Datasette plugins can check if an actor has permission to perform an action using :ref:`datasette_allowed`—for example::

    from datasette.resources import TableResource

    can_edit = await datasette.allowed(
        action="update-row",
        resource=TableResource(database="fixtures", table="facetable"),
        actor=request.actor,
    )

Use :ref:`datasette_ensure_permission` when you need to enforce a permission and
raise a ``Forbidden`` error automatically.

Plugins that define new operations should return :class:`~datasette.permissions.Action`
objects from :ref:`plugin_register_actions` and can supply additional allow/deny
rules by returning :class:`~datasette.permissions.PermissionSQL` objects from the
:ref:`plugin_hook_permission_resources_sql` hook. Those rules are merged with
configuration ``allow`` blocks and actor restrictions to determine the final
result for each check.

.. _authentication_actor_matches_allow:

actor_matches_allow()
=====================

Plugins that wish to implement this same ``"allow"`` block permissions scheme can take advantage of the ``datasette.utils.actor_matches_allow(actor, allow)`` function:

.. code-block:: python

    from datasette.utils import actor_matches_allow

    actor_matches_allow({"id": "root"}, {"id": "*"})
    # returns True

The currently authenticated actor is made available to plugins as ``request.actor``.

.. _PermissionsDebugView:

Permissions debug tools
=======================

The debug tool at ``/-/permissions`` is available to any actor with the ``permissions-debug`` permission. By default this is just the :ref:`authenticated root user <authentication_root>` but you can open it up to all users by starting Datasette like this::

    datasette -s permissions.permissions-debug true data.db

The permission debug tools answer four different questions:

Why was this decision allowed or denied?
    Use :ref:`PermissionCheckView`. It shows every matching rule, identifies the winning specificity level, applies actor restrictions and evaluates any required actions.

Which resources can the current actor access?
    Use :ref:`AllowedResourcesView` to view an access map for a selected action.

Which raw rules did Datasette and its plugins contribute?
    Use :ref:`PermissionRulesView` to inspect the rules before they are resolved into decisions.

Which checks has this Datasette instance performed recently?
    Use ``/-/permissions`` to view recent permission activity.

These tools are designed to help administrators and plugin authors understand and confirm the effective permissions configuration.

These debug endpoints are exempt from the :ref:`JSON API stability promise <json_api_stability>` - their JSON shapes may change in future releases.

.. _AllowedResourcesView:

Allowed resources view
----------------------

The ``/-/allowed`` endpoint displays resources that the current actor can access for a specified ``action``.

This endpoint provides an interactive HTML form interface. Add ``.json`` to the URL path (e.g. ``/-/allowed.json``) to get the raw JSON response instead.

Pass ``?action=view-table`` (or another action) to select the action. Optional ``parent=`` and ``child=`` query parameters can narrow the results to a specific database/table pair. Results are paginated: ``?_size=`` sets the page size (default 50, maximum 200, ``max`` for the maximum) and ``?_page=`` selects a page.

This endpoint is publicly accessible to help users understand their own permissions. The potentially sensitive ``reason`` field is only shown to users with the ``permissions-debug`` permission - it shows the plugins and explanatory reasons that were responsible for each decision.

.. _PermissionRulesView:

Permission rules view
---------------------

The ``/-/rules`` endpoint displays all permission rules (both allow and deny) for each candidate resource for the requested action.

This endpoint provides an interactive HTML form interface. Add ``.json`` to the URL path (e.g. ``/-/rules.json?action=view-table``) to get the raw JSON response instead.

Pass ``?action=`` as a query parameter to specify which action to check. The ``?_size=`` and ``?_page=`` pagination parameters work the same as on ``/-/allowed``.

This endpoint requires the ``permissions-debug`` permission.

.. _PermissionCheckView:

Permission check view
---------------------

The ``/-/check`` endpoint evaluates and explains a single actor, action and resource decision. The explanation includes:

* Every matching allow and deny rule, with its source and reason.
* The winning resource, parent or global scope.
* Rules ignored because a more specific rule matched, or because a deny won at the same scope.
* Actor restriction allowlists that included or excluded the resource.
* Additional actions required by the requested action.
* An explicit default-deny explanation when no rule matched.

This endpoint provides an interactive HTML form interface. Add ``.json`` to the URL path (e.g. ``/-/check.json?action=view-instance``) to get the raw JSON response instead.

Pass ``?action=`` to specify the action to check, and optional ``?parent=`` and ``?child=`` parameters to specify the resource. The interactive form also accepts actor JSON, allowing a hypothetical actor to be tested without signing in as that actor. The JSON endpoint accepts the same value using the ``actor`` query string parameter. Use ``actor=null`` to represent an anonymous actor.

This endpoint requires the ``permissions-debug`` permission. The hypothetical actor is used only for the decision being explained; access to the debug tool is checked against the actor who is actually signed in.

.. _authentication_ds_actor:

The ds_actor cookie
===================

Datasette includes a default authentication plugin which looks for a signed ``ds_actor`` cookie containing a JSON actor dictionary. This is how the :ref:`root actor <authentication_root>` mechanism works.

Authentication plugins can set signed ``ds_actor`` cookies themselves like so:

.. code-block:: python

    response = Response.redirect("/")
    datasette.set_actor_cookie(response, {"id": "cleopaws"})

The shape of data encoded in the cookie is as follows:

.. code-block:: json

    {
      "a": {
        "id": "cleopaws"
      }
    }

To implement logout in a plugin, use the ``delete_actor_cookie()`` method:

.. code-block:: python

    response = Response.redirect("/")
    datasette.delete_actor_cookie(response)

.. _authentication_ds_actor_expiry:

Including an expiry time
------------------------

``ds_actor`` cookies can optionally include a signed expiry timestamp, after which the cookies will no longer be valid. Authentication plugins may chose to use this mechanism to limit the lifetime of the cookie. For example, if a plugin implements single-sign-on against another source it may decide to set short-lived cookies so that if the user is removed from the SSO system their existing Datasette cookies will stop working shortly afterwards.

To include an expiry pass ``expire_after=`` to ``datasette.set_actor_cookie()`` with a number of seconds. For example, to expire in 24 hours:

.. code-block:: python

    response = Response.redirect("/")
    datasette.set_actor_cookie(
        response, {"id": "cleopaws"}, expire_after=60 * 60 * 24
    )

The resulting cookie will encode data that looks something like this:

.. code-block:: json

    {
      "a": {
        "id": "cleopaws"
      },
      "e": "1jjSji"
    }

.. _LogoutView:

The /-/logout page
------------------

The page at ``/-/logout`` provides the ability to log out of a ``ds_actor`` cookie authentication session.

.. _actions:

Built-in actions
================

This section lists all of the permission checks that are carried out by Datasette core, along with the ``resource`` if it was passed.

.. _actions_view_instance:

view-instance
-------------

Top level permission - Actor is allowed to view any pages within this instance, starting at https://latest.datasette.io/

.. _actions_view_database:

view-database
-------------

Actor is allowed to view a database page, e.g. https://latest.datasette.io/fixtures

``resource`` - ``datasette.permissions.DatabaseResource(database)``
    ``database`` is the name of the database (string)

.. _actions_view_database_download:

view-database-download
----------------------

Actor is allowed to download a database, e.g. https://latest.datasette.io/fixtures.db

``resource`` - ``datasette.resources.DatabaseResource(database)``
    ``database`` is the name of the database (string)

.. _actions_view_table:

view-table
----------

Actor is allowed to view a table (or view) page, e.g. https://latest.datasette.io/fixtures/complex_foreign_keys

``resource`` - ``datasette.resources.TableResource(database, table)``
    ``database`` is the name of the database (string)

    ``table`` is the name of the table (string)

.. _actions_view_query:

view-query
----------

Actor is allowed to view a stored query page, e.g. https://latest.datasette.io/fixtures/pragma_cache_size. Executing an untrusted stored query also requires ``execute-sql`` or the relevant write permissions; :ref:`trusted stored queries ` can execute with ``view-query`` alone.

``resource`` - ``datasette.resources.QueryResource(database, query)``
    ``database`` is the name of the database (string)

    ``query`` is the name of the query (string)

.. _actions_store_query:

store-query
-----------

Actor is allowed to create stored queries against a database.

``resource`` - ``datasette.resources.DatabaseResource(database)``
    ``database`` is the name of the database (string)

.. _actions_update_query:

update-query
------------

Actor is allowed to update a stored query.

``resource`` - ``datasette.resources.QueryResource(database, query)``
    ``database`` is the name of the database (string)

    ``query`` is the name of the query (string)

.. _actions_delete_query:

delete-query
------------

Actor is allowed to delete a stored query.

``resource`` - ``datasette.resources.QueryResource(database, query)``
    ``database`` is the name of the database (string)

    ``query`` is the name of the query (string)

.. _actions_insert_row:

insert-row
----------

Actor is allowed to insert rows into a table.

``resource`` - ``datasette.resources.TableResource(database, table)``
    ``database`` is the name of the database (string)

    ``table`` is the name of the table (string)

.. _actions_delete_row:

delete-row
----------

Actor is allowed to delete rows from a table.

``resource`` - ``datasette.resources.TableResource(database, table)``
    ``database`` is the name of the database (string)

    ``table`` is the name of the table (string)

.. _actions_update_row:

update-row
----------

Actor is allowed to update rows in a table.

``resource`` - ``datasette.resources.TableResource(database, table)``
    ``database`` is the name of the database (string)

    ``table`` is the name of the table (string)

.. _actions_create_table:

create-table
------------

Actor is allowed to create a database table.

``resource`` - ``datasette.resources.DatabaseResource(database)``
    ``database`` is the name of the database (string)

.. _actions_create_view:

create-view
-----------

Actor is allowed to create a database view.

``resource`` - ``datasette.resources.DatabaseResource(database)``
    ``database`` is the name of the database (string)

.. _actions_alter_table:

alter-table
-----------

Actor is allowed to alter a database table.

``resource`` - ``datasette.resources.TableResource(database, table)``
    ``database`` is the name of the database (string)

    ``table`` is the name of the table (string)

.. _actions_set_column_type:

set-column-type
---------------

Actor is allowed to set assigned :ref:`column types ` for columns in a table.

``resource`` - ``datasette.resources.TableResource(database, table)``
    ``database`` is the name of the database (string)

    ``table`` is the name of the table (string)

.. _actions_drop_table:

drop-table
----------

Actor is allowed to drop a database table.

``resource`` - ``datasette.resources.TableResource(database, table)``
    ``database`` is the name of the database (string)

    ``table`` is the name of the table (string)

.. _actions_drop_view:

drop-view
---------

Actor is allowed to drop a database view.

``resource`` - ``datasette.resources.TableResource(database, table)``
    ``database`` is the name of the database (string)

    ``table`` is the name of the view (string)

.. _actions_execute_sql:

execute-sql
-----------

Actor is allowed to run arbitrary read-only SQL queries against a specific database using the :ref:`custom SQL query page `, e.g. https://latest.datasette.io/fixtures/-/query?sql=select+100

``resource`` - ``datasette.resources.DatabaseResource(database)``
    ``database`` is the name of the database (string)

See also :ref:`the default_allow_sql setting <setting_default_allow_sql>`.

.. _actions_execute_write_sql:

execute-write-sql
-----------------

Actor is allowed to run arbitrary writable SQL queries against a specific database using the :ref:`write SQL queries page `, subject to table-level write permissions such as ``insert-row``, ``update-row`` and ``delete-row``. SQL functions are allowed and are not separately restricted by Datasette permissions.

``resource`` - ``datasette.resources.DatabaseResource(database)``
    ``database`` is the name of the database (string)

.. _actions_permissions_debug:

permissions-debug
-----------------

Actor is allowed to view the ``/-/permissions`` debug tools.

.. _actions_debug_menu:

debug-menu
----------

Controls if the various debug pages are displayed in the jump menu.

---

## File: docs/binary_data.rst

.. _binary:

=============
 Binary data
=============

SQLite tables can contain binary data in ``BLOB`` columns.

Datasette includes special handling for these binary values. The Datasette interface detects binary values and provides a link to download their content, for example on https://latest.datasette.io/fixtures/binary_data

.. image:: https://raw.githubusercontent.com/simonw/datasette-screenshots/0.62/binary-data.png
   :width: 311px
   :alt: Screenshot showing download links next to binary data in the table view

.. _binary_json_format:

Binary values in JSON
---------------------

Binary data is represented in ``.json`` exports using Base64 encoding. Datasette uses this representation for every ``BLOB`` value, including binary values that could also be decoded as UTF-8 text.

https://latest.datasette.io/fixtures/binary_data.json?_shape=array

.. code-block:: json

    [
        {
            "rowid": 1,
            "data": {
                "$base64": true,
                "encoded": "FRwCx60F/g=="
            }
        },
        {
            "rowid": 2,
            "data": {
                "$base64": true,
                "encoded": "FRwDx60F/g=="
            }
        },
        {
            "rowid": 3,
            "data": null
        }
    ]

The same format can be used with the :ref:`JSON write API <json_api_write>`.
If a column value in a ``row``, ``rows`` or ``update`` object is a JSON object with exactly ``"$base64"`` set to ``true`` and an ``"encoded"`` string, Datasette will decode that Base64 string and store the resulting bytes:

.. code-block:: json

    {
        "data": {
            "$base64": true,
            "encoded": "FRwCx60F/g=="
        }
    }

This works for inserts, upserts and updates. It also works when creating a table from example ``row`` or ``rows`` data: Datasette decodes the value before inferring the schema, allowing that column to be created as a ``BLOB`` column.

To store a JSON object with that exact shape literally, wrap it in a ``"$raw"`` object:

.. code-block:: json

    {
        "data": {
            "$raw": {
                "$base64": true,
                "encoded": "FRwCx60F/g=="
            }
        }
    }

``"$raw"`` unwraps exactly one layer. To store a literal ``"$raw"`` object containing a Base64 object, wrap it again:

.. code-block:: json

    {
        "data": {
            "$raw": {
                "$raw": {
                    "$base64": true,
                    "encoded": "FRwCx60F/g=="
                }
            }
        }
    }

.. _binary_linking:

Linking to binary downloads
---------------------------

The ``.blob`` output format is used to return binary data. It requires a ``_blob_column=`` query string argument specifying which BLOB column should be downloaded, for example:

https://latest.datasette.io/fixtures/binary_data/1.blob?_blob_column=data

This output format can also be used to return binary data from an arbitrary SQL query. Since such queries do not specify an exact row, an additional ``?_blob_hash=`` parameter can be used to specify the SHA-256 hash of the value that is being linked to.

Consider the query ``select data from binary_data`` - `demonstrated here <https://latest.datasette.io/fixtures?sql=select+data+from+binary_data>`__.

That page links to the binary value downloads. Those links look like this:

https://latest.datasette.io/fixtures.blob?sql=select+data+from+binary_data&_blob_column=data&_blob_hash=f3088978da8f9aea479ffc7f631370b968d2e855eeb172bea7f6c7a04262bb6d

These ``.blob`` links are also returned in the ``.csv`` exports Datasette provides for binary tables and queries, since the CSV format does not have a mechanism for representing binary data.

Binary plugins
--------------

Several Datasette plugins are available that change the way Datasette treats binary data.

- `datasette-render-binary <https://github.com/simonw/datasette-render-binary>`__ modifies Datasette's default interface to show an automatic guess at what type of binary data is being stored, along with a visual representation of the binary value that displays ASCII strings directly in the interface.
- `datasette-render-images <https://github.com/simonw/datasette-render-images>`__ detects common image formats and renders them as images directly in the Datasette interface.
- `datasette-media <https://github.com/simonw/datasette-media>`__ allows Datasette interfaces to be configured to serve binary files from configured SQL queries, and includes the ability to resize images directly before serving them.

---

## File: docs/cli-reference.rst

.. _cli_reference:

===============
 CLI reference
===============

The ``datasette`` CLI tool provides a number of commands.

Running ``datasette`` without specifying a command runs the default command, ``datasette serve``.  See :ref:`cli_help_serve___help` for the full list of options for that command.

.. [[[cog
    from datasette import cli
    from click.testing import CliRunner
    import textwrap
    def help(args):
        title = "datasette " + " ".join(args)
        cog.out("\n::\n\n")
        result = CliRunner().invoke(cli.cli, args)
        output = result.output.replace("Usage: cli ", "Usage: datasette ")
        cog.out(textwrap.indent(output, '    '))
        cog.out("\n\n")
.. ]]]
.. [[[end]]]

.. _cli_help___help:

datasette --help
================

Running ``datasette --help`` shows a list of all of the available commands.

.. [[[cog
    help(["--help"])
.. ]]]

::

    Usage: datasette [OPTIONS] COMMAND [ARGS]...

      Datasette is an open source multi-tool for exploring and publishing data

      About Datasette: https://datasette.io/
      Full documentation: https://docs.datasette.io/

    Options:
      --version  Show the version and exit.
      --help     Show this message and exit.

    Commands:
      serve*        Serve up specified SQLite database files with a web UI
      create-token  Create a signed API token for the specified actor ID
      inspect       Generate JSON summary of provided database files
      install       Install plugins and packages from PyPI into the same...
      package       Package SQLite files into a Datasette Docker container
      plugins       List currently installed plugins
      publish       Publish specified SQLite database files to the internet...
      uninstall     Uninstall plugins and Python packages from the Datasette...


.. [[[end]]]

Additional commands added by plugins that use the :ref:`plugin_hook_register_commands` hook will be listed here as well.

.. _cli_help_serve___help:

datasette serve
===============

This command starts the Datasette web application running on your machine::

    datasette serve mydatabase.db

Or since this is the default command you can run this instead::

    datasette mydatabase.db

Once started you can access it at ``http://localhost:8001``

.. [[[cog
    help(["serve", "--help"])
.. ]]]

::

    Usage: datasette serve [OPTIONS] [FILES]...

      Serve up specified SQLite database files with a web UI

    Options:
      -i, --immutable PATH            Database files to open in immutable mode
      -h, --host TEXT                 Host for server. Defaults to 127.0.0.1 which
                                      means only connections from the local machine
                                      will be allowed. Use 0.0.0.0 to listen to all
                                      IPs and allow access from other machines.
      -p, --port INTEGER RANGE        Port for server, defaults to 8001. Use -p 0 to
                                      automatically assign an available port.
                                      [0<=x<=65535]
      --uds TEXT                      Bind to a Unix domain socket
      --reload                        Automatically reload if code or metadata
                                      change detected - useful for development
      --cors                          Enable CORS by serving Access-Control-Allow-
                                      Origin: *
      --load-extension PATH:ENTRYPOINT?
                                      Path to a SQLite extension to load, and
                                      optional entrypoint
      --inspect-file TEXT             Path to JSON file created using "datasette
                                      inspect"
      -m, --metadata FILENAME         Path to JSON/YAML file containing
                                      license/source metadata
      --template-dir DIRECTORY        Path to directory containing custom templates
      --plugins-dir DIRECTORY         Path to directory containing custom plugins
      --static MOUNT:DIRECTORY        Serve static files from this directory at
                                      /MOUNT/...
      --memory                        Make /_memory database available
      -c, --config FILENAME           Path to JSON/YAML Datasette configuration file
      -s, --setting SETTING...        nested.key, value setting to use in Datasette
                                      configuration
      --secret TEXT                   Secret used for signing secure values, such as
                                      signed cookies
      --root                          Output URL that sets a cookie authenticating
                                      the root user
      --default-deny                  Deny all permissions by default
      --get TEXT                      Run an HTTP GET request against this path,
                                      print results and exit
      --headers                       Include HTTP headers in --get output
      --token TEXT                    API token to send with --get requests
      --actor TEXT                    Actor to use for --get requests (JSON string)
      --version-note TEXT             Additional note to show on /-/versions
      --help-settings                 Show available settings
      --pdb                           Launch debugger on any errors
      -o, --open                      Open Datasette in your web browser
      --create                        Create database files if they do not exist
      --crossdb                       Enable cross-database joins using the /_memory
                                      database
      --nolock                        Ignore locking, open locked files in read-only
                                      mode
      --ssl-keyfile TEXT              SSL key file
      --ssl-certfile TEXT             SSL certificate file
      --internal PATH                 Path to a persistent Datasette internal SQLite
                                      database
      --help                          Show this message and exit.


.. [[[end]]]

.. _cli_datasette_serve_env:

Environment variables
---------------------

Some of the ``datasette serve`` options can be provided by environment variables:

- ``DATASETTE_SECRET``: Equivalent to the ``--secret`` option.
- ``DATASETTE_SSL_KEYFILE``: Equivalent to the ``--ssl-keyfile`` option.
- ``DATASETTE_SSL_CERTFILE``: Equivalent to the ``--ssl-certfile`` option.
- ``DATASETTE_LOAD_EXTENSION``: Equivalent to the ``--load-extension`` option.

.. _cli_datasette_get:

datasette --get
---------------

The ``--get`` option to ``datasette serve`` (or just ``datasette``) specifies the path to a page within Datasette and causes Datasette to output the content from that path without starting the web server.

This means that all of Datasette's functionality can be accessed directly from the command-line.

For example:

.. code-block:: bash

    datasette --get '/-/versions.json' | jq .

.. code-block:: json

    {
      "python": {
        "version": "3.8.5",
        "full": "3.8.5 (default, Jul 21 2020, 10:48:26) \n[Clang 11.0.3 (clang-1103.0.32.62)]"
      },
      "datasette": {
        "version": "0.46+15.g222a84a.dirty"
      },
      "asgi": "3.0",
      "uvicorn": "0.11.8",
      "sqlite": {
        "version": "3.32.3",
        "fts_versions": [
          "FTS5",
          "FTS4",
          "FTS3"
        ],
        "extensions": {
          "json1": null
        },
        "compile_options": [
          "COMPILER=clang-11.0.3",
          "ENABLE_COLUMN_METADATA",
          "ENABLE_FTS3",
          "ENABLE_FTS3_PARENTHESIS",
          "ENABLE_FTS4",
          "ENABLE_FTS5",
          "ENABLE_GEOPOLY",
          "ENABLE_JSON1",
          "ENABLE_PREUPDATE_HOOK",
          "ENABLE_RTREE",
          "ENABLE_SESSION",
          "MAX_VARIABLE_NUMBER=250000",
          "THREADSAFE=1"
        ]
      }
    }

You can use the ``--token TOKEN`` option to send an :ref:`API token <CreateTokenView>` with the simulated request.

Or you can make a request as a specific actor by passing a JSON representation of that actor to ``--actor``:

.. code-block:: bash

    datasette --memory --actor '{"id": "root"}' --get '/-/actor.json'

The exit code of ``datasette --get`` will be 0 if the request succeeds and 1 if the request produced an HTTP status code other than 200 - e.g. a 404 or 500 error.

This lets you use ``datasette --get /`` to run tests against a Datasette application in a continuous integration environment such as GitHub Actions.

.. _cli_help_serve___help_settings:

datasette serve --help-settings
-------------------------------

This command outputs all of the available Datasette :ref:`settings <settings>`.

These can be passed to ``datasette serve`` using ``datasette serve --setting name value``.

.. [[[cog
    help(["--help-settings"])
.. ]]]

::

    Settings:
      default_page_size            Default page size for the table view
                                   (default=100)
      max_returned_rows            Maximum rows that can be returned from a table or
                                   custom query (default=1000)
      max_insert_rows              Maximum rows that can be inserted at a time using
                                   the bulk insert API (default=100)
      max_post_body_bytes          Maximum size in bytes for a POST body read into
                                   memory, e.g. JSON API requests - set 0 to disable
                                   this limit (default=2097152)
      num_sql_threads              Number of threads in the thread pool for
                                   executing SQLite queries (default=3)
      sql_time_limit_ms            Time limit for a SQL query in milliseconds
                                   (default=1000)
      default_facet_size           Number of values to return for requested facets
                                   (default=30)
      facet_time_limit_ms          Time limit for calculating a requested facet
                                   (default=200)
      facet_suggest_time_limit_ms  Time limit for calculating a suggested facet
                                   (default=50)
      allow_facet                  Allow users to specify columns to facet using
                                   ?_facet= parameter (default=True)
      allow_download               Allow users to download the original SQLite
                                   database files (default=True)
      allow_signed_tokens          Allow users to create and use signed API tokens
                                   (default=True)
      default_allow_sql            Allow anyone to run arbitrary SQL queries
                                   (default=True)
      max_signed_tokens_ttl        Maximum allowed expiry time for signed API tokens
                                   (default=0)
      suggest_facets               Calculate and display suggested facets
                                   (default=True)
      default_cache_ttl            Default HTTP cache TTL (used in Cache-Control:
                                   max-age= header) (default=5)
      cache_size_kb                SQLite cache size in KB (0 == use SQLite default)
                                   (default=0)
      allow_csv_stream             Allow .csv?_stream=1 to download all rows
                                   (ignoring max_returned_rows) (default=True)
      max_csv_mb                   Maximum size allowed for CSV export in MB - set 0
                                   to disable this limit (default=100)
      truncate_cells_html          Truncate cells longer than this in HTML table
                                   view - set 0 to disable (default=2048)
      force_https_urls             Force URLs in API output to always use https://
                                   protocol (default=False)
      template_debug               Allow display of template debug information with
                                   ?_context=1 (default=False)
      trace_debug                  Allow display of SQL trace debug information with
                                   ?_trace=1 (default=False)
      base_url                     Datasette URLs should use this base path
                                   (default=/)



.. [[[end]]]

.. _cli_help_plugins___help:

datasette plugins
=================

Output JSON showing all currently installed plugins, their versions, whether they include static files or templates and which :ref:`plugin_hooks` they use.

.. [[[cog
    help(["plugins", "--help"])
.. ]]]

::

    Usage: datasette plugins [OPTIONS]

      List currently installed plugins

    Options:
      --all                    Include built-in default plugins
      --requirements           Output requirements.txt of installed plugins
      --plugins-dir DIRECTORY  Path to directory containing custom plugins
      --help                   Show this message and exit.


.. [[[end]]]

Example output:

.. code-block:: json

    [
        {
            "name": "datasette-geojson",
            "static": false,
            "templates": false,
            "version": "0.3.1",
            "hooks": [
                "register_output_renderer"
            ]
        },
        {
            "name": "datasette-geojson-map",
            "static": true,
            "templates": false,
            "version": "0.4.0",
            "hooks": [
                "extra_body_script",
                "extra_css_urls",
                "extra_js_urls"
            ]
        },
        {
            "name": "datasette-leaflet",
            "static": true,
            "templates": false,
            "version": "0.2.2",
            "hooks": [
                "extra_body_script",
                "extra_template_vars"
            ]
        }
    ]


.. _cli_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 <https://datasette.io/plugins/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_help_uninstall___help:

datasette uninstall
===================

Uninstall one or more plugins.

.. [[[cog
    help(["uninstall", "--help"])
.. ]]]

::

    Usage: datasette uninstall [OPTIONS] PACKAGES...

      Uninstall plugins and Python packages from the Datasette environment

    Options:
      -y, --yes  Don't ask for confirmation
      --help     Show this message and exit.


.. [[[end]]]

.. _cli_help_publish___help:

datasette publish
=================

Shows a list of available deployment targets for :ref:`publishing data ` with Datasette.

Additional deployment targets can be added by plugins that use the :ref:`plugin_hook_publish_subcommand` hook.

.. [[[cog
    help(["publish", "--help"])
.. ]]]

::

    Usage: datasette publish [OPTIONS] COMMAND [ARGS]...

      Publish specified SQLite database files to the internet along with a
      Datasette-powered interface and API

    Options:
      --help  Show this message and exit.

    Commands:
      cloudrun  Publish databases to Datasette running on Cloud Run
      heroku    Publish databases to Datasette running on Heroku


.. [[[end]]]


.. _cli_help_publish_cloudrun___help:

datasette publish cloudrun
==========================

See :ref:`publish_cloud_run`.

.. [[[cog
    help(["publish", "cloudrun", "--help"])
.. ]]]

::

    Usage: datasette publish cloudrun [OPTIONS] [FILES]...

      Publish databases to Datasette running on Cloud Run

    Options:
      -m, --metadata FILENAME         Path to JSON/YAML file containing metadata to
                                      publish
      --extra-options TEXT            Extra options to pass to datasette serve
      --branch TEXT                   Install datasette from a GitHub branch e.g.
                                      main
      --template-dir DIRECTORY        Path to directory containing custom templates
      --plugins-dir DIRECTORY         Path to directory containing custom plugins
      --static MOUNT:DIRECTORY        Serve static files from this directory at
                                      /MOUNT/...
      --install TEXT                  Additional packages (e.g. plugins) to install
      --plugin-secret <TEXT TEXT TEXT>...
                                      Secrets to pass to plugins, e.g. --plugin-
                                      secret datasette-auth-github client_id xxx
      --version-note TEXT             Additional note to show on /-/versions
      --secret TEXT                   Secret used for signing secure values, such as
                                      signed cookies
      --title TEXT                    Title for metadata
      --license TEXT                  License label for metadata
      --license_url TEXT              License URL for metadata
      --source TEXT                   Source label for metadata
      --source_url TEXT               Source URL for metadata
      --about TEXT                    About label for metadata
      --about_url TEXT                About URL for metadata
      -n, --name TEXT                 Application name to use when building
      --service TEXT                  Cloud Run service to deploy (or over-write)
      --spatialite                    Enable SpatialLite extension
      --show-files                    Output the generated Dockerfile and
                                      metadata.json
      --memory TEXT                   Memory to allocate in Cloud Run, e.g. 1Gi
      --cpu [1|2|4]                   Number of vCPUs to allocate in Cloud Run
      --timeout INTEGER               Build timeout in seconds
      --apt-get-install TEXT          Additional packages to apt-get install
      --max-instances INTEGER         Maximum Cloud Run instances (use 0 to remove
                                      the limit)  [default: 1]
      --min-instances INTEGER         Minimum Cloud Run instances
      --artifact-repository TEXT      Artifact Registry repository to store the
                                      image  [default: datasette]
      --artifact-region TEXT          Artifact Registry location (region or multi-
                                      region)  [default: us]
      --artifact-project TEXT         Project ID for Artifact Registry (defaults to
                                      the active project)
      --help                          Show this message and exit.


.. [[[end]]]


.. _cli_help_publish_heroku___help:

datasette publish heroku
========================

See :ref:`publish_heroku`.

.. [[[cog
    help(["publish", "heroku", "--help"])
.. ]]]

::

    Usage: datasette publish heroku [OPTIONS] [FILES]...

      Publish databases to Datasette running on Heroku

    Options:
      -m, --metadata FILENAME         Path to JSON/YAML file containing metadata to
                                      publish
      --extra-options TEXT            Extra options to pass to datasette serve
      --branch TEXT                   Install datasette from a GitHub branch e.g.
                                      main
      --template-dir DIRECTORY        Path to directory containing custom templates
      --plugins-dir DIRECTORY         Path to directory containing custom plugins
      --static MOUNT:DIRECTORY        Serve static files from this directory at
                                      /MOUNT/...
      --install TEXT                  Additional packages (e.g. plugins) to install
      --plugin-secret <TEXT TEXT TEXT>...
                                      Secrets to pass to plugins, e.g. --plugin-
                                      secret datasette-auth-github client_id xxx
      --version-note TEXT             Additional note to show on /-/versions
      --secret TEXT                   Secret used for signing secure values, such as
                                      signed cookies
      --title TEXT                    Title for metadata
      --license TEXT                  License label for metadata
      --license_url TEXT              License URL for metadata
      --source TEXT                   Source label for metadata
      --source_url TEXT               Source URL for metadata
      --about TEXT                    About label for metadata
      --about_url TEXT                About URL for metadata
      -n, --name TEXT                 Application name to use when deploying
      --tar TEXT                      --tar option to pass to Heroku, e.g.
                                      --tar=/usr/local/bin/gtar
      --generate-dir DIRECTORY        Output generated application files and stop
                                      without deploying
      --help                          Show this message and exit.


.. [[[end]]]

.. _cli_help_package___help:

datasette package
=================

Package SQLite files into a Datasette Docker container, see :ref:`cli_package`.

.. [[[cog
    help(["package", "--help"])
.. ]]]

::

    Usage: datasette package [OPTIONS] FILES...

      Package SQLite files into a Datasette Docker container

    Options:
      -t, --tag TEXT            Name for the resulting Docker container, can
                                optionally use name:tag format
      -m, --metadata FILENAME   Path to JSON/YAML file containing metadata to
                                publish
      --extra-options TEXT      Extra options to pass to datasette serve
      --branch TEXT             Install datasette from a GitHub branch e.g. main
      --template-dir DIRECTORY  Path to directory containing custom templates
      --plugins-dir DIRECTORY   Path to directory containing custom plugins
      --static MOUNT:DIRECTORY  Serve static files from this directory at /MOUNT/...
      --install TEXT            Additional packages (e.g. plugins) to install
      --spatialite              Enable SpatialLite extension
      --version-note TEXT       Additional note to show on /-/versions
      --secret TEXT             Secret used for signing secure values, such as
                                signed cookies
      -p, --port INTEGER RANGE  Port to run the server on, defaults to 8001
                                [1<=x<=65535]
      --title TEXT              Title for metadata
      --license TEXT            License label for metadata
      --license_url TEXT        License URL for metadata
      --source TEXT             Source label for metadata
      --source_url TEXT         Source URL for metadata
      --about TEXT              About label for metadata
      --about_url TEXT          About URL for metadata
      --help                    Show this message and exit.


.. [[[end]]]


.. _cli_help_inspect___help:

datasette inspect
=================

Outputs JSON representing introspected data about one or more SQLite database files.

If you are opening an immutable database, you can pass this file to the ``--inspect-data`` option to improve Datasette's performance by allowing it to skip running row counts against the database when it first starts running::

    datasette inspect mydatabase.db > inspect-data.json
    datasette serve -i mydatabase.db --inspect-file inspect-data.json

This performance optimization is used automatically by some of the ``datasette publish`` commands. You are unlikely to need to apply this optimization manually.

.. [[[cog
    help(["inspect", "--help"])
.. ]]]

::

    Usage: datasette inspect [OPTIONS] [FILES]...

      Generate JSON summary of provided database files

      This can then be passed to "datasette --inspect-file" to speed up count
      operations against immutable database files.

    Options:
      --inspect-file TEXT
      --load-extension PATH:ENTRYPOINT?
                                      Path to a SQLite extension to load, and
                                      optional entrypoint
      --help                          Show this message and exit.


.. [[[end]]]


.. _cli_help_create_token___help:

datasette create-token
======================

Create a signed API token, see :ref:`authentication_cli_create_token`.

.. [[[cog
    help(["create-token", "--help"])
.. ]]]

::

    Usage: datasette create-token [OPTIONS] ID

      Create a signed API token for the specified actor ID

      Example:

          datasette create-token root --secret mysecret

      To allow only "view-database-download" for all databases:

          datasette create-token root --secret mysecret \
              --all view-database-download

      To allow "create-table" against a specific database:

          datasette create-token root --secret mysecret \
              --database mydb create-table

      To allow "insert-row" against a specific table:

          datasette create-token root --secret myscret \
              --resource mydb mytable insert-row

      Restricted actions can be specified multiple times using multiple --all,
      --database, and --resource options.

      Add --debug to see a decoded version of the token.

    Options:
      --secret TEXT                   Secret used for signing the API tokens
                                      [required]
      -e, --expires-after INTEGER     Token should expire after this many seconds
      -a, --all ACTION                Restrict token to this action
      -d, --database DB ACTION        Restrict token to this action on this database
      -r, --resource DB RESOURCE ACTION
                                      Restrict token to this action on this database
                                      resource (a table, SQL view or named query)
      --debug                         Show decoded token
      --plugins-dir DIRECTORY         Path to directory containing custom plugins
      --help                          Show this message and exit.


.. [[[end]]]

---

## File: docs/configuration.rst

.. _configuration:

Configuration
=============

Datasette offers several ways to configure your Datasette instances: server settings, plugin configuration, authentication, and more.

Most configuration can be handled using a ``datasette.yaml`` configuration file, passed to datasette using the ``-c/--config`` flag:

.. code-block:: bash

    datasette mydatabase.db --config datasette.yaml

This file can also use JSON, as ``datasette.json``. YAML is recommended over JSON due to its support for comments and multi-line strings.

.. _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:

.. code-block:: bash

    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 <https://datasette.io/plugins/datasette-cluster-map>`_:

.. code-block:: bash

    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 <https://datasette.io/plugins/datasette-proxy-url>`_:

.. code-block:: bash

    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()
      )
.. ]]]

.. tab:: datasette.yaml

    .. code-block:: yaml

        plugins:
          datasette-proxy-url:
            paths:
            - path: /proxy
              backend: http://example.com/

.. tab:: datasette.json

    .. code-block:: json

        {
          "plugins": {
            "datasette-proxy-url": {
              "paths": [
                {
                  "path": "/proxy",
                  "backend": "http://example.com/"
                }
              ]
            }
          }
        }
.. [[[end]]]


.. _configuration_reference:

``datasette.yaml`` reference
----------------------------

The following example shows some of the valid configuration options that can exist inside ``datasette.yaml``.

.. [[[cog
    from metadata_doc import config_example
    import textwrap
    config_example(cog, textwrap.dedent(
      """
        # Datasette settings block
        settings:
          default_page_size: 50
          sql_time_limit_ms: 3500
          max_returned_rows: 2000

        # top-level plugin configuration
        plugins:
          datasette-my-plugin:
            key: valueA

        # Database and table-level configuration
        databases:
          your_db_name:
            # plugin configuration for the your_db_name database
            plugins:
              datasette-my-plugin:
                key: valueA
            tables:
              your_table_name:
                allow:
                  # Only the root user can access this table
                  id: root
                # plugin configuration for the your_table_name table
                # inside your_db_name database
                plugins:
                  datasette-my-plugin:
                    key: valueB
        """)
      )
.. ]]]

.. tab:: datasette.yaml

    .. code-block:: yaml


        # Datasette settings block
        settings:
          default_page_size: 50
          sql_time_limit_ms: 3500
          max_returned_rows: 2000

        # top-level plugin configuration
        plugins:
          datasette-my-plugin:
            key: valueA

        # Database and table-level configuration
        databases:
          your_db_name:
            # plugin configuration for the your_db_name database
            plugins:
              datasette-my-plugin:
                key: valueA
            tables:
              your_table_name:
                allow:
                  # Only the root user can access this table
                  id: root
                # plugin configuration for the your_table_name table
                # inside your_db_name database
                plugins:
                  datasette-my-plugin:
                    key: valueB


.. tab:: datasette.json

    .. code-block:: json

        {
          "settings": {
            "default_page_size": 50,
            "sql_time_limit_ms": 3500,
            "max_returned_rows": 2000
          },
          "plugins": {
            "datasette-my-plugin": {
              "key": "valueA"
            }
          },
          "databases": {
            "your_db_name": {
              "plugins": {
                "datasette-my-plugin": {
                  "key": "valueA"
                }
              },
              "tables": {
                "your_table_name": {
                  "allow": {
                    "id": "root"
                  },
                  "plugins": {
                    "datasette-my-plugin": {
                      "key": "valueB"
                    }
                  }
                }
              }
            }
          }
        }
.. [[[end]]]

.. _configuration_reference_settings:

Settings
~~~~~~~~

:ref:`settings` can be configured in ``datasette.yaml`` with the ``settings`` key:

.. [[[cog
    from metadata_doc import config_example
    import textwrap
    config_example(cog, textwrap.dedent(
      """
        # inside datasette.yaml
        settings:
          default_allow_sql: off
          default_page_size: 50
        """).strip()
      )
.. ]]]

.. tab:: datasette.yaml

    .. code-block:: yaml

        # inside datasette.yaml
        settings:
          default_allow_sql: off
          default_page_size: 50

.. tab:: datasette.json

    .. code-block:: json

        {
          "settings": {
            "default_allow_sql": "off",
            "default_page_size": 50
          }
        }
.. [[[end]]]

The full list of settings is available in the :ref:`settings documentation <settings>`. Settings can also be passed to Datasette using one or more ``--setting name value`` command line options.`

.. _configuration_reference_plugins:

Plugin configuration
~~~~~~~~~~~~~~~~~~~~

:ref:`Datasette plugins ` often require configuration. This plugin configuration should be placed in ``plugins`` keys inside ``datasette.yaml``.

Most plugins are configured at the top-level of the file, using the ``plugins`` key:

.. [[[cog
    from metadata_doc import config_example
    import textwrap
    config_example(cog, textwrap.dedent(
      """
        # inside datasette.yaml
        plugins:
          datasette-my-plugin:
            key: my_value
        """).strip()
      )
.. ]]]

.. tab:: datasette.yaml

    .. code-block:: yaml

        # inside datasette.yaml
        plugins:
          datasette-my-plugin:
            key: my_value

.. tab:: datasette.json

    .. code-block:: json

        {
          "plugins": {
            "datasette-my-plugin": {
              "key": "my_value"
            }
          }
        }
.. [[[end]]]

Some plugins can be configured at the database or table level. These should use a ``plugins`` key nested under the appropriate place within the ``databases`` object:

.. [[[cog
    from metadata_doc import config_example
    import textwrap
    config_example(cog, textwrap.dedent(
      """
        # inside datasette.yaml
        databases:
          my_database:
            # plugin configuration for the my_database database
            plugins:
              datasette-my-plugin:
                key: my_value
          my_other_database:
            tables:
              my_table:
                # plugin configuration for the my_table table inside the my_other_database database
                plugins:
                  datasette-my-plugin:
                    key: my_value
      """).strip()
      )
.. ]]]

.. tab:: datasette.yaml

    .. code-block:: yaml

        # inside datasette.yaml
        databases:
          my_database:
            # plugin configuration for the my_database database
            plugins:
              datasette-my-plugin:
                key: my_value
          my_other_database:
            tables:
              my_table:
                # plugin configuration for the my_table table inside the my_other_database database
                plugins:
                  datasette-my-plugin:
                    key: my_value

.. tab:: datasette.json

    .. code-block:: json

        {
          "databases": {
            "my_database": {
              "plugins": {
                "datasette-my-plugin": {
                  "key": "my_value"
                }
              }
            },
            "my_other_database": {
              "tables": {
                "my_table": {
                  "plugins": {
                    "datasette-my-plugin": {
                      "key": "my_value"
                    }
                  }
                }
              }
            }
          }
        }
.. [[[end]]]


.. _configuration_reference_permissions:

Permissions configuration
~~~~~~~~~~~~~~~~~~~~~~~~~

Datasette's :ref:`authentication and permissions <authentication>` system can also be configured using ``datasette.yaml``.

Here is a simple example:

.. [[[cog
    from metadata_doc import config_example
    import textwrap
    config_example(cog, textwrap.dedent(
      """
        # Instance is only available to users 'sharon' and 'percy':
        allow:
          id:
          - sharon
          - percy

        # Only 'percy' is allowed access to the accounting database:
        databases:
          accounting:
            allow:
              id: percy
      """).strip()
      )
.. ]]]

.. tab:: datasette.yaml

    .. code-block:: yaml

        # Instance is only available to users 'sharon' and 'percy':
        allow:
          id:
          - sharon
          - percy

        # Only 'percy' is allowed access to the accounting database:
        databases:
          accounting:
            allow:
              id: percy

.. tab:: datasette.json

    .. code-block:: json

        {
          "allow": {
            "id": [
              "sharon",
              "percy"
            ]
          },
          "databases": {
            "accounting": {
              "allow": {
                "id": "percy"
              }
            }
          }
        }
.. [[[end]]]

:ref:`authentication_permissions_config` has the full details.

.. _configuration_reference_queries:

Queries configuration
~~~~~~~~~~~~~~~~~~~~~

:ref:`Queries <queries>` are named SQL queries that appear in the Datasette interface. They can be configured in ``datasette.yaml`` using the ``queries`` key at the database level:

.. [[[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"
                   }
               }
           }
        }
    })
.. ]]]

.. tab:: datasette.yaml

    .. code-block:: yaml

        databases:
          sf-trees:
            queries:
              just_species:
                sql: select qSpecies from Street_Tree_List


.. tab:: datasette.json

    .. code-block:: json

        {
          "databases": {
            "sf-trees": {
              "queries": {
                "just_species": {
                  "sql": "select qSpecies from Street_Tree_List"
                }
              }
            }
          }
        }
.. [[[end]]]

See the :ref:`queries documentation <queries>` for more, including how to configure :ref:`writable queries <queries_writable>`.

.. _configuration_reference_css_js:

Custom CSS and JavaScript
~~~~~~~~~~~~~~~~~~~~~~~~~

Datasette can load additional CSS and JavaScript files, configured in ``datasette.yaml`` like this:

.. [[[cog
    from metadata_doc import config_example
    config_example(cog, """
        extra_css_urls:
        - https://simonwillison.net/static/css/all.bf8cd891642c.css
        extra_js_urls:
        - https://code.jquery.com/jquery-3.2.1.slim.min.js
    """)
.. ]]]

.. tab:: datasette.yaml

    .. code-block:: yaml


            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


.. tab:: datasette.json

    .. code-block:: json

        {
          "extra_css_urls": [
            "https://simonwillison.net/static/css/all.bf8cd891642c.css"
          ],
          "extra_js_urls": [
            "https://code.jquery.com/jquery-3.2.1.slim.min.js"
          ]
        }
.. [[[end]]]

The extra CSS and JavaScript files will be linked in the ``<head>`` of every page:

.. code-block:: html

    <link rel="stylesheet" href="https://simonwillison.net/static/css/all.bf8cd891642c.css">
    <script src="https://code.jquery.com/jquery-3.2.1.slim.min.js"></script>

You can also specify a SRI (subresource integrity hash) for these assets:

.. [[[cog
    config_example(cog, """
        extra_css_urls:
        - url: https://simonwillison.net/static/css/all.bf8cd891642c.css
          sri: sha384-9qIZekWUyjCyDIf2YK1FRoKiPJq4PHt6tp/ulnuuyRBvazd0hG7pWbE99zvwSznI
        extra_js_urls:
        - url: https://code.jquery.com/jquery-3.2.1.slim.min.js
          sri: sha256-k2WSCIexGzOj3Euiig+TlR8gA0EmPjuc79OEeY5L45g=
    """)
.. ]]]

.. tab:: datasette.yaml

    .. code-block:: yaml


            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=


.. tab:: datasette.json

    .. code-block:: json

        {
          "extra_css_urls": [
            {
              "url": "https://simonwillison.net/static/css/all.bf8cd891642c.css",
              "sri": "sha384-9qIZekWUyjCyDIf2YK1FRoKiPJq4PHt6tp/ulnuuyRBvazd0hG7pWbE99zvwSznI"
            }
          ],
          "extra_js_urls": [
            {
              "url": "https://code.jquery.com/jquery-3.2.1.slim.min.js",
              "sri": "sha256-k2WSCIexGzOj3Euiig+TlR8gA0EmPjuc79OEeY5L45g="
            }
          ]
        }
.. [[[end]]]

This will produce:

.. code-block:: html

    <link rel="stylesheet" href="https://simonwillison.net/static/css/all.bf8cd891642c.css"
        integrity="sha384-9qIZekWUyjCyDIf2YK1FRoKiPJq4PHt6tp/ulnuuyRBvazd0hG7pWbE99zvwSznI"
        crossorigin="anonymous">
    <script src="https://code.jquery.com/jquery-3.2.1.slim.min.js"
        integrity="sha256-k2WSCIexGzOj3Euiig+TlR8gA0EmPjuc79OEeY5L45g="
        crossorigin="anonymous"></script>

Modern browsers will only execute the stylesheet or JavaScript if the SRI hash
matches the content served. You can generate hashes using `www.srihash.org <https://www.srihash.org/>`_

Items in ``"extra_js_urls"`` can specify ``"module": true`` if they reference JavaScript that uses `JavaScript modules <https://developer.mozilla.org/en-US/docs/Web/JavaScript/Guide/Modules>`__. This configuration:

.. [[[cog
    config_example(cog, """
        extra_js_urls:
        - url: https://example.datasette.io/module.js
          module: true
    """)
.. ]]]

.. tab:: datasette.yaml

    .. code-block:: yaml


            extra_js_urls:
            - url: https://example.datasette.io/module.js
              module: true


.. tab:: datasette.json

    .. code-block:: json

        {
          "extra_js_urls": [
            {
              "url": "https://example.datasette.io/module.js",
              "module": true
            }
          ]
        }
.. [[[end]]]

Will produce this HTML:

.. code-block:: html

    <script type="module" src="https://example.datasette.io/module.js"></script>

.. _configuration_reference_table:

Table configuration
~~~~~~~~~~~~~~~~~~~

Datasette supports a number of table-level configuration options inside ``datasette.yaml``. These are placed under ``databases.database_name.tables.table_name``.

.. _table_configuration_sort:

``sort`` / ``sort_desc``
^^^^^^^^^^^^^^^^^^^^^^^^

By default Datasette tables are sorted by primary key. You can set a default sort order for a specific table using the ``sort`` or ``sort_desc`` properties:

.. [[[cog
    from metadata_doc import config_example
    import textwrap
    config_example(cog, textwrap.dedent(
      """
        databases:
          mydatabase:
            tables:
              example_table:
                sort: created
      """).strip()
    )
.. ]]]

.. tab:: datasette.yaml

    .. code-block:: yaml

        databases:
          mydatabase:
            tables:
              example_table:
                sort: created

.. tab:: datasette.json

    .. code-block:: json

        {
          "databases": {
            "mydatabase": {
              "tables": {
                "example_table": {
                  "sort": "created"
                }
              }
            }
          }
        }
.. [[[end]]]

Or use ``sort_desc`` to sort in descending order:

.. [[[cog
    config_example(cog, textwrap.dedent(
      """
        databases:
          mydatabase:
            tables:
              example_table:
                sort_desc: created
      """).strip()
    )
.. ]]]

.. tab:: datasette.yaml

    .. code-block:: yaml

        databases:
          mydatabase:
            tables:
              example_table:
                sort_desc: created

.. tab:: datasette.json

    .. code-block:: json

        {
          "databases": {
            "mydatabase": {
              "tables": {
                "example_table": {
                  "sort_desc": "created"
                }
              }
            }
          }
        }
.. [[[end]]]

.. _table_configuration_size:

``size``
^^^^^^^^

Datasette defaults to displaying 100 rows per page, for both tables and views. You can change this on a per-table or per-view basis using the ``size`` key:

.. [[[cog
    config_example(cog, textwrap.dedent(
      """
        databases:
          mydatabase:
            tables:
              example_table:
                size: 10
      """).strip()
    )
.. ]]]

.. tab:: datasette.yaml

    .. code-block:: yaml

        databases:
          mydatabase:
            tables:
              example_table:
                size: 10

.. tab:: datasette.json

    .. code-block:: json

        {
          "databases": {
            "mydatabase": {
              "tables": {
                "example_table": {
                  "size": 10
                }
              }
            }
          }
        }
.. [[[end]]]

This size can still be over-ridden by passing e.g. ``?_size=50`` in the query string.

.. _table_configuration_sortable_columns:

``sortable_columns``
^^^^^^^^^^^^^^^^^^^^

Datasette allows any column to be used for sorting by default. If you need to control which columns are available for sorting you can do so using ``sortable_columns``:

.. [[[cog
    config_example(cog, textwrap.dedent(
      """
        databases:
          mydatabase:
            tables:
              example_table:
                sortable_columns:
                - height
                - weight
      """).strip()
    )
.. ]]]

.. tab:: datasette.yaml

    .. code-block:: yaml

        databases:
          mydatabase:
            tables:
              example_table:
                sortable_columns:
                - height
                - weight

.. tab:: datasette.json

    .. code-block:: json

        {
          "databases": {
            "mydatabase": {
              "tables": {
                "example_table": {
                  "sortable_columns": [
                    "height",
                    "weight"
                  ]
                }
              }
            }
          }
        }
.. [[[end]]]

This will restrict sorting of ``example_table`` to just the ``height`` and ``weight`` columns.

You can also disable sorting entirely by setting ``"sortable_columns": []``

You can use ``sortable_columns`` to enable specific sort orders for a view called ``name_of_view`` in the database ``my_database`` like so:

.. [[[cog
    config_example(cog, textwrap.dedent(
      """
        databases:
          my_database:
            tables:
              name_of_view:
                sortable_columns:
                - clicks
                - impressions
      """).strip()
    )
.. ]]]

.. tab:: datasette.yaml

    .. code-block:: yaml

        databases:
          my_database:
            tables:
              name_of_view:
                sortable_columns:
                - clicks
                - impressions

.. tab:: datasette.json

    .. code-block:: json

        {
          "databases": {
            "my_database": {
              "tables": {
                "name_of_view": {
                  "sortable_columns": [
                    "clicks",
                    "impressions"
                  ]
                }
              }
            }
          }
        }
.. [[[end]]]

.. _table_configuration_label_column:

``label_column``
^^^^^^^^^^^^^^^^

Datasette's HTML interface attempts to display foreign key references as labelled hyperlinks. By default, it automatically detects a label column using the following rules (in order):

1. If there is exactly one unique text column, use that.
2. If there is a column called ``name`` or ``title`` (case-insensitive), use that.
3. If the table has only two columns - a primary key and one other - use the non-primary-key column.

You can override this automatic detection by specifying which column should be used for the link label with the ``label_column`` property:

.. [[[cog
    config_example(cog, textwrap.dedent(
      """
        databases:
          mydatabase:
            tables:
              example_table:
                label_column: title
      """).strip()
    )
.. ]]]

.. tab:: datasette.yaml

    .. code-block:: yaml

        databases:
          mydatabase:
            tables:
              example_table:
                label_column: title

.. tab:: datasette.json

    .. code-block:: json

        {
          "databases": {
            "mydatabase": {
              "tables": {
                "example_table": {
                  "label_column": "title"
                }
              }
            }
          }
        }
.. [[[end]]]

.. _table_configuration_hidden:

``hidden``
^^^^^^^^^^

You can hide tables from the database listing view (in the same way that FTS and SpatiaLite tables are automatically hidden) using ``"hidden": true``:

.. [[[cog
    config_example(cog, textwrap.dedent(
      """
        databases:
          mydatabase:
            tables:
              example_table:
                hidden: true
      """).strip()
    )
.. ]]]

.. tab:: datasette.yaml

    .. code-block:: yaml

        databases:
          mydatabase:
            tables:
              example_table:
                hidden: true

.. tab:: datasette.json

    .. code-block:: json

        {
          "databases": {
            "mydatabase": {
              "tables": {
                "example_table": {
                  "hidden": true
                }
              }
            }
          }
        }
.. [[[end]]]

.. _table_configuration_facets:

``facets`` / ``facet_size``
^^^^^^^^^^^^^^^^^^^^^^^^^^^

You can turn on facets by default for specific tables. ``facet_size`` controls how many unique values are shown for each facet on that table (the default is controlled by the :ref:`setting_default_facet_size` setting). See :ref:`facets_metadata` for full details.

.. [[[cog
    config_example(cog, textwrap.dedent(
      """
        databases:
          sf-trees:
            tables:
              Street_Tree_List:
                facets:
                - qLegalStatus
                facet_size: 10
      """).strip()
    )
.. ]]]

.. tab:: datasette.yaml

    .. code-block:: yaml

        databases:
          sf-trees:
            tables:
              Street_Tree_List:
                facets:
                - qLegalStatus
                facet_size: 10

.. tab:: datasette.json

    .. code-block:: json

        {
          "databases": {
            "sf-trees": {
              "tables": {
                "Street_Tree_List": {
                  "facets": [
                    "qLegalStatus"
                  ],
                  "facet_size": 10
                }
              }
            }
          }
        }
.. [[[end]]]

You can also specify :ref:`array <facet_by_json_array>` or :ref:`date <facet_by_date>` facets using JSON objects with a single key of ``array`` or ``date``:

.. code-block:: yaml

    facets:
    - array: tags
    - date: created

.. _table_configuration_fts:

``fts_table`` / ``fts_pk`` / ``searchmode``
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

These configure :ref:`full-text search <full_text_search>` for a table or view. See :ref:`full_text_search_table_or_view` for full details.

``fts_table`` specifies which FTS table to use for search. ``fts_pk`` sets the primary key column if it is something other than ``rowid``. ``searchmode`` can be set to ``"raw"`` to enable `SQLite advanced search operators <https://www.sqlite.org/fts5.html#full_text_query_syntax>`__.

.. [[[cog
    config_example(cog, textwrap.dedent(
      """
        databases:
          russian-ads:
            tables:
              display_ads:
                fts_table: ads_fts
                fts_pk: id
                searchmode: raw
      """).strip()
    )
.. ]]]

.. tab:: datasette.yaml

    .. code-block:: yaml

        databases:
          russian-ads:
            tables:
              display_ads:
                fts_table: ads_fts
                fts_pk: id
                searchmode: raw

.. tab:: datasette.json

    .. code-block:: json

        {
          "databases": {
            "russian-ads": {
              "tables": {
                "display_ads": {
                  "fts_table": "ads_fts",
                  "fts_pk": "id",
                  "searchmode": "raw"
                }
              }
            }
          }
        }
.. [[[end]]]

.. _table_configuration_column_types:

``column_types``
^^^^^^^^^^^^^^^^

You can assign semantic column types to columns, which affect how values are rendered, validated, transformed, and edited. Built-in column types include ``url``, ``email``, ``json``, and ``textarea``. Plugins can register additional column types using the :ref:`register_column_types ` plugin hook.

Column types can optionally declare which SQLite column types they apply to using ``sqlite_types``. Datasette will reject incompatible assignments. The built-in ``url``, ``email``, ``json``, and ``textarea`` column types are all restricted to ``TEXT`` columns.

The simplest form maps column names to type name strings:

.. [[[cog
    config_example(cog, textwrap.dedent(
      """
        databases:
          mydatabase:
            tables:
              example_table:
                column_types:
                  website: url
                  contact: email
                  extra_data: json
                  notes: textarea
      """).strip()
    )
.. ]]]

.. tab:: datasette.yaml

    .. code-block:: yaml

        databases:
          mydatabase:
            tables:
              example_table:
                column_types:
                  website: url
                  contact: email
                  extra_data: json
                  notes: textarea

.. tab:: datasette.json

    .. code-block:: json

        {
          "databases": {
            "mydatabase": {
              "tables": {
                "example_table": {
                  "column_types": {
                    "website": "url",
                    "contact": "email",
                    "extra_data": "json",
                    "notes": "textarea"
                  }
                }
              }
            }
          }
        }
.. [[[end]]]

For column types that accept additional configuration, use an object with ``type`` and ``config`` keys:

.. [[[cog
    config_example(cog, textwrap.dedent(
      """
        databases:
          mydatabase:
            tables:
              example_table:
                column_types:
                  website:
                    type: url
                    config:
                      prefix: "https://"
      """).strip()
    )
.. ]]]

.. tab:: datasette.yaml

    .. code-block:: yaml

        databases:
          mydatabase:
            tables:
              example_table:
                column_types:
                  website:
                    type: url
                    config:
                      prefix: "https://"

.. tab:: datasette.json

    .. code-block:: json

        {
          "databases": {
            "mydatabase": {
              "tables": {
                "example_table": {
                  "column_types": {
                    "website": {
                      "type": "url",
                      "config": {
                        "prefix": "https://"
                      }
                    }
                  }
                }
              }
            }
          }
        }
.. [[[end]]]

---

## File: docs/contributing.rst

.. _contributing:

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 :ref:`plugins`.

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.

.. _devenvironment:

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 <https://docs.python-guide.org/starting/install3/osx/>`__) 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 <https://github.com/simonw/datasette/fork>`__ under your own GitHub account.

Now clone that repository somewhere on your computer::

    git clone [email protected]:YOURNAME/datasette

If you want to get started without creating your own fork, you can do this instead::

    git clone [email protected]:simonw/datasette

The quickest way to set up a development environment is to use `uv <https://github.com/astral-sh/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

.. _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 <https://docs.pytest.org/>`__ like so::

    uv run pytest

You can run the tests faster using multiple CPU cores with `pytest-xdist <https://pypi.org/project/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"

.. _contributing_playwright:

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.

.. _Playwright: https://playwright.dev/python/

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:

.. code-block:: bash

    just playwright-install

Then run the Playwright test module:

.. code-block:: bash

    just playwright

You can also run the same tests against Firefox or WebKit by installing that
browser engine and passing it to ``just playwright``:

.. code-block:: bash

    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:

.. code-block:: bash

    just playwright-install-all
    just playwright-all

You can pass extra ``pytest`` options after the browser name:

.. code-block:: bash

    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:

.. code-block:: bash

    just playwright firefox --headed

Combine this with ``-k`` to watch a specific test:

.. code-block:: bash

    just playwright chromium --headed -k test_insert_row

If you are not using ``just``, the equivalent ``uv run`` commands are:

.. code-block:: bash

    uv run --group playwright playwright install chromium
    uv run --group playwright pytest tests/test_playwright.py --playwright --browser chromium

.. _contributing_using_fixtures:

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
:ref:`customization_static_files`.

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/

.. _contributing_debugging:

Debugging
---------

Any errors that occur while Datasette is running while display a stack trace on the console.

You can tell Datasette to open an interactive ``pdb`` (or ``ipdb``, if present) debugger session if an error occurs using the ``--pdb`` option::

    uv run datasette --pdb fixtures.db

For `ipdb <https://pypi.org/project/ipdb/>`__, first run this::

    uv run datasette install ipdb

.. _contributing_formatting:

Code formatting
---------------

Datasette uses opinionated code formatters: `Black <https://github.com/psf/black>`__ for Python and `Prettier <https://prettier.io/>`__ 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 <https://github.com/casey/just>`__ the quickest way to run these is like so::

    just black
    just prettier

Or run both at the same time::

    just format

.. _contributing_formatting_black:

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.

.. _contributing_formatting_blacken_docs:

blacken-docs
~~~~~~~~~~~~

The `blacken-docs <https://pypi.org/project/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

.. _contributing_formatting_prettier:

Prettier
~~~~~~~~

To install Prettier, `install Node.js <https://nodejs.org/en/download/package-manager/>`__ 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

.. _contributing_documentation:

Editing and building the documentation
--------------------------------------

Datasette's documentation lives in the ``docs/`` directory and is deployed automatically using `Read The Docs <https://readthedocs.org/>`__.

The documentation is written using reStructuredText. You may find this article on `The subset of reStructuredText worth committing to memory <https://simonwillison.net/2018/Aug/25/restructuredtext/>`__ 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 <https://pypi.org/project/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.

.. _contributing_documentation_cog:

Running Cog
~~~~~~~~~~~

Some pages of documentation (in particular the :ref:`cli_reference`) are automatically updated using `Cog <https://github.com/nedbat/cog>`__.

To update these pages, run the following command::

    uv run cog -r docs/*.rst

.. _contributing_template_contexts:

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 :ref:`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.

.. _contributing_continuous_deployment:

Continuously deployed demo instances
------------------------------------

The demo instance at `latest.datasette.io <https://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 <https://github.com/simonw/datasette/blob/main/.github/workflows/deploy-latest.yml>`__.

Specific branches can also be set to automatically deploy by adding them to the ``on: push: branches`` block at the top of the workflow YAML file. Branches configured in this way will be deployed to a new Cloud Run service whether or not their tests pass.

The Cloud Run URL for a branch demo can be found in the GitHub Actions logs.

.. _contributing_release:

Release process
---------------

Datasette releases are performed using tags. When a new release is published on GitHub, a `GitHub Action workflow <https://github.com/simonw/datasette/blob/main/.github/workflows/deploy-latest.yml>`__ 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 <https://semver.org/>`__::

    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.

:ref:`contributing_alpha_beta` 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 :ref:`the changelog <changelog>` with highlights of the new version. An example `commit can be seen here <https://github.com/simonw/datasette/commit/0e1e89c6ba3d0fbdb0823272952cf356f3016def>`__::

    # Update changelog
    git commit -m " Release 0.51a1

    Refs #1056, #1039, #998, #1045, #1033, #1036, #1034, #976, #1057, #1058, #1053, #1064, #1066" -a
    git push

Referencing the issues that are part of the release in the commit message ensures the name of the release shows up on those issue pages, e.g. `here <https://github.com/simonw/datasette/issues/581#ref-commit-d56f402>`__.

You can generate the list of issue references for a specific release by copying and pasting text from the release notes or GitHub changes-since-last-release view into this `Extract issue numbers from pasted text <https://observablehq.com/@simonw/extract-issue-numbers-from-pasted-text>`__ tool.

To create the tag for the release, create `a new release <https://github.com/simonw/datasette/releases/new>`__ on GitHub matching the new version number. You can convert the release notes to Markdown by copying and pasting the rendered HTML into this `Paste to Markdown tool <https://euangoddard.github.io/clipboard2markdown/>`__.

Don't forget to create the release from the correct branch - usually ``main``, but sometimes ``0.64.x`` or similar for a bugfix release.

While the release is running you can confirm that the correct commits made it into the release using the https://github.com/simonw/datasette/compare/0.64.6...0.64.7 URL.

Finally, post a news item about the release on `datasette.io <https://datasette.io/>`__ by editing the `news.yaml <https://github.com/simonw/datasette.io/blob/main/news.yaml>`__ file in that site's repository. Use `this preview tool <https://tools.simonwillison.net/datasette-io-preview>`__ to preview the edits to the YAML.

.. _contributing_alpha_beta:

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 <https://github.com/simonw/datasette/issues>`__ to share your thoughts and experiences with on alpha and beta features that you try out.

.. _contributing_bug_fix_branch:

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 <https://github.com/simonw/datasette/releases/new>`__ 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

.. _contributing_upgrading_codemirror:

Upgrading CodeMirror
--------------------

Datasette bundles `CodeMirror <https://codemirror.net/>`__ for the SQL editing interface, e.g. on `this page <https://latest.datasette.io/fixtures>`__. 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.

---

## File: docs/csv_export.rst

.. _csv_export:

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:

.. image:: https://github.com/simonw/datasette-screenshots/blob/0.62/advanced-export.png?raw=true
   :alt: Advanced export form. You can get the data in different JSON shapes, and CSV options are download file, expand labels and stream all rows.

* **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
  <https://latest.datasette.io/fixtures/facetable.csv?_labels=on&_size=max>`_
  the ``city_id`` column is accompanied by a ``city_id_label`` column.

* **stream all rows** - by default CSV files only contain the first
  :ref:`setting_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

.. _csv_export_url_parameters:

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.

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 :ref:`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 :ref:`setting_max_csv_mb` config
setting. You can also disable the CSV export feature entirely using
:ref:`setting_allow_csv_stream`.

---

## File: docs/deploying.rst

.. _deploying:

=====================
 Deploying Datasette
=====================

The quickest way to deploy a Datasette instance on the internet is to use the ``datasette publish`` command, described in :ref:`publishing`. This can be used to quickly deploy Datasette to a number of hosting providers including Heroku, Google Cloud Run and Vercel.

You can deploy Datasette to other hosting providers using the instructions on this page.

.. _deploying_fundamentals:

Deployment fundamentals
=======================

Datasette can be deployed as a single ``datasette`` process that listens on a port. Datasette is not designed to be run as root, so that process should listen on a higher port such as port 8000.

If you want to serve Datasette on port 80 (the HTTP default port) or port 443 (for HTTPS) you should run it behind a proxy server, such as nginx, Apache or HAProxy. The proxy server can listen on port 80/443 and forward traffic on to Datasette.

.. _deploying_systemd:

Running Datasette using systemd
===============================

You can run Datasette on Ubuntu or Debian systems using ``systemd``.

First, ensure you have Python 3 and ``pip`` installed. On Ubuntu you can use ``sudo apt-get install python3 python3-pip``.

You can install Datasette into a virtual environment, or you can install it system-wide. To install system-wide, use ``sudo pip3 install datasette``.

Now create a folder for your Datasette databases, for example using ``mkdir /home/ubuntu/datasette-root``.

You can copy a test database into that folder like so::

    cd /home/ubuntu/datasette-root
    curl -O https://latest.datasette.io/fixtures.db

Create a file at ``/etc/systemd/system/datasette.service`` with the following contents:

.. code-block:: ini

    [Unit]
    Description=Datasette
    After=network.target

    [Service]
    Type=simple
    User=ubuntu
    Environment=DATASETTE_SECRET=
    WorkingDirectory=/home/ubuntu/datasette-root
    ExecStart=datasette serve . -h 127.0.0.1 -p 8000
    Restart=on-failure

    [Install]
    WantedBy=multi-user.target

Add a random value for the ``DATASETTE_SECRET`` - this will be used to sign Datasette cookies such as the CSRF token cookie. You can generate a suitable value like so::

    python3 -c 'import secrets; print(secrets.token_hex(32))'

This configuration will run Datasette against all database files contained in the ``/home/ubuntu/datasette-root`` directory. If that directory contains a ``metadata.yml`` (or ``.json``) file or a ``templates/`` or ``plugins/`` sub-directory those will automatically be loaded by Datasette - see :ref:`config_dir` for details.

You can start the Datasette process running using the following::

    sudo systemctl daemon-reload
    sudo systemctl start datasette.service

You will need to restart the Datasette service after making changes to its ``metadata.json`` configuration or adding a new database file to that directory. You can do that using::

    sudo systemctl restart datasette.service

Once the service has started you can confirm that Datasette is running on port 8000 like so::

    curl 127.0.0.1:8000/-/versions.json
    # Should output JSON showing the installed version

Datasette will not be accessible from outside the server because it is listening on ``127.0.0.1``. You can expose it by instead listening on ``0.0.0.0``, but a better way is to set up a proxy such as ``nginx`` - see :ref:`deploying_proxy`.

.. _deploying_openrc:

Running Datasette using OpenRC
==============================
OpenRC is the service manager on non-systemd Linux distributions like `Alpine Linux <https://www.alpinelinux.org/>`__ and `Gentoo <https://www.gentoo.org/>`__.

Create an init script at ``/etc/init.d/datasette`` with the following contents:

.. code-block:: sh

    #!/sbin/openrc-run

    name="datasette"
    command="datasette"
    command_args="serve -h 0.0.0.0 /path/to/db.db"
    command_background=true
    pidfile="/run/${RC_SVCNAME}.pid"

You then need to configure the service to run at boot and start it::

    rc-update add datasette
    rc-service datasette start

.. _deploying_buildpacks:

Deploying using buildpacks
==========================

Some hosting providers such as `Heroku <https://www.heroku.com/>`__, `DigitalOcean App Platform <https://www.digitalocean.com/docs/app-platform/>`__ and `Scalingo <https://scalingo.com/>`__ support the `Buildpacks standard <https://buildpacks.io/>`__ for deploying Python web applications.

Deploying Datasette on these platforms requires two files: ``requirements.txt`` and ``Procfile``.

The ``requirements.txt`` file lets the platform know which Python packages should be installed. It should contain ``datasette`` at a minimum, but can also list any Datasette plugins you wish to install - for example::

    datasette
    datasette-vega

The ``Procfile`` lets the hosting platform know how to run the command that serves web traffic. It should look like this::

    web: datasette . -h 0.0.0.0 -p $PORT --cors

The ``$PORT`` environment variable is provided by the hosting platform. ``--cors`` enables CORS requests from JavaScript running on other websites to your domain - omit this if you don't want to allow CORS. You can add additional Datasette :ref:`settings` options here too.

These two files should be enough to deploy Datasette on any host that supports buildpacks. Datasette will serve any SQLite files that are included in the root directory of the application.

If you want to build SQLite files or download them as part of the deployment process you can do so using a ``bin/post_compile`` file. For example, the following ``bin/post_compile`` will download an example database that will then be served by Datasette::

    wget https://fivethirtyeight.datasettes.com/fivethirtyeight.db

`simonw/buildpack-datasette-demo <https://github.com/simonw/buildpack-datasette-demo>`__ is an example GitHub repository showing a Datasette configuration that can be deployed to a buildpack-supporting host.

.. _deploying_proxy:

Running Datasette behind a proxy
================================

You may wish to run Datasette behind an Apache or nginx proxy, using a path within your existing site.

You can use the :ref:`setting_base_url` configuration setting to tell Datasette to serve traffic with a specific URL prefix. For example, you could run Datasette like this::

    datasette my-database.db --setting base_url /my-datasette/ -p 8009

This will run Datasette with the following URLs:

- ``http://127.0.0.1:8009/my-datasette/`` - the Datasette homepage
- ``http://127.0.0.1:8009/my-datasette/my-database`` - the page for the ``my-database.db`` database
- ``http://127.0.0.1:8009/my-datasette/my-database/some_table`` - the page for the ``some_table`` table

You can now set your nginx or Apache server to proxy the ``/my-datasette/`` path to this Datasette instance.

Nginx proxy configuration
-------------------------

Here is an example of an `nginx <https://nginx.org/>`__ configuration file that will proxy traffic to Datasette::

    daemon off;

    events {
      worker_connections  1024;
    }
    http {
      server {
        listen 80;
        location /my-datasette {
          proxy_pass http://127.0.0.1:8009/my-datasette;
          proxy_set_header Host $host;
        }
      }
    }

You can also use the ``--uds`` option to Datasette to listen on a Unix domain socket instead of a port, configuring the nginx upstream proxy like this::

    daemon off;
    events {
      worker_connections  1024;
    }
    http {
      server {
        listen 80;
        location /my-datasette {
          proxy_pass http://datasette/my-datasette;
          proxy_set_header Host $host;
        }
      }
      upstream datasette {
        server unix:/tmp/datasette.sock;
      }
    }

Then run Datasette with ``datasette --uds /tmp/datasette.sock path/to/database.db --setting base_url /my-datasette/``.

Apache proxy configuration
--------------------------

For `Apache <https://httpd.apache.org/>`__, you can use the ``ProxyPass`` directive. First make sure the following lines are uncommented::

    LoadModule proxy_module lib/httpd/modules/mod_proxy.so
    LoadModule proxy_http_module lib/httpd/modules/mod_proxy_http.so

Then add these directives to proxy traffic::

    ProxyPass /my-datasette/ http://127.0.0.1:8009/my-datasette/
    ProxyPreserveHost On

A live demo of Datasette running behind Apache using this proxy setup can be seen at `datasette-apache-proxy-demo.datasette.io/prefix/ <https://datasette-apache-proxy-demo.datasette.io/prefix/>`__. The code for that demo can be found in the `demos/apache-proxy <https://github.com/simonw/datasette/tree/main/demos/apache-proxy>`__ directory.

Using ``--uds`` you can use Unix domain sockets similar to the nginx example::

    ProxyPass /my-datasette/ unix:/tmp/datasette.sock|http://localhost/my-datasette/

The `ProxyPreserveHost On <https://httpd.apache.org/docs/2.4/mod/mod_proxy.html#proxypreservehost>`__ directive ensures that the original ``Host:`` header from the incoming request is passed through to Datasette. Datasette needs this to correctly assemble links to other pages using the :ref:`datasette_absolute_url` method.

## 2. Official Technical Reference & Guides (simonw/docs)

# docs cookiecutter template

I use this cookiecutter template to create `docs/` folders in my projects when I want to use Sphinx to document them.

This template uses the [Furo](https://github.com/pradyunsg/furo) theme and configures [myst-parser](https://myst-parser.readthedocs.io/en/latest/), allowing documentation to be written in Markdown.

## Usage

```bash
cookiecutter gh:simonw/docs
```
It will ask questions like this:
```
  [1/3] project (): shot-scraper
  [2/3] author (): Simon Willison
  [3/3] docs_directory (docs): 
```
Then it will create a `docs/` directory. To start using it:

```bash
cd docs
pip install -r requirements.txt
make livehtml
```
Create files called `something.md` in the `docs/` directory and add them to the `index.md` file's `toctree` directive to include them in the contents.