2018-05-12 22:29:06 +00:00
.. _metadata:
2017-12-07 17:19:35 +00:00
Metadata
========
Data loves metadata. Any time you run Datasette you can optionally include a
2023-07-08 16:37:01 +00:00
YAML or JSON file with metadata about your databases and tables. Datasette will then
2017-12-07 17:19:35 +00:00
display that information in the web UI.
Run Datasette like this::
2023-07-08 16:37:01 +00:00
datasette database1.db database2.db --metadata metadata.yaml
2017-12-07 17:19:35 +00:00
2023-07-08 16:37:01 +00:00
Your `` metadata.yaml `` file can look something like this:
2020-04-02 19:30:53 +00:00
2017-12-07 17:19:35 +00:00
2023-07-08 16:37:01 +00:00
.. [[[cog
from metadata_doc import metadata_example
metadata_example(cog, {
2017-12-07 17:19:35 +00:00
"title": "Custom title for your index page",
"description": "Some description text can go here",
"license": "ODbL",
"license_url": "https://opendatacommons.org/licenses/odbl/",
"source": "Original Data Source",
"source_url": "http://example.com/"
2023-07-08 16:37:01 +00:00
})
.. ]]]
2017-12-07 17:19:35 +00:00
2023-10-12 16:16:37 +00:00
.. tab :: metadata.yaml
2023-07-08 16:37:01 +00:00
.. code-block :: yaml
2023-07-08 17:26:50 +00:00
title: Custom title for your index page
2023-07-08 16:37:01 +00:00
description: Some description text can go here
license: ODbL
license_url: https://opendatacommons.org/licenses/odbl/
source: Original Data Source
source_url: http://example.com/
2023-10-12 16:16:37 +00:00
.. tab :: metadata.json
2023-07-08 16:37:01 +00:00
.. code-block :: json
{
"title": "Custom title for your index page",
"description": "Some description text can go here",
"license": "ODbL",
"license_url": "https://opendatacommons.org/licenses/odbl/",
"source": "Original Data Source",
"source_url": "http://example.com/"
}
.. [[[end]]]
2024-02-05 21:01:03 +00:00
Choosing YAML over JSON adds support for multi-line strings and comments.
2020-04-02 19:30:53 +00:00
2017-12-07 17:19:35 +00:00
The above metadata will be displayed on the index page of your Datasette-powered
site. The source and license information will also be included in the footer of
every page served by Datasette.
Any special HTML characters in `` description `` will be escaped. If you want to
include HTML in your description, you can use a `` description_html `` property
instead.
Per-database and per-table metadata
-----------------------------------
2023-07-08 16:37:01 +00:00
Metadata at the top level of the file will be shown on the index page and in the
2017-12-07 17:19:35 +00:00
footer on every page of the site. The license and source is expected to apply to
all of your data.
2020-04-02 19:30:53 +00:00
You can also provide metadata at the per-database or per-table level, like this:
2023-07-08 16:37:01 +00:00
.. [[[cog
metadata_example(cog, {
2017-12-07 17:19:35 +00:00
"databases": {
"database1": {
"source": "Alternative source",
"source_url": "http://example.com/",
"tables": {
"example_table": {
"description_html": "Custom <em>table</em> description",
"license": "CC BY 3.0 US",
"license_url": "https://creativecommons.org/licenses/by/3.0/us/"
}
}
}
}
2023-07-08 16:37:01 +00:00
})
.. ]]]
2023-10-12 16:16:37 +00:00
.. tab :: metadata.yaml
2023-07-08 16:37:01 +00:00
.. code-block :: yaml
databases:
database1:
source: Alternative source
source_url: http://example.com/
tables:
example_table:
description_html: Custom <em>table</em> description
license: CC BY 3.0 US
license_url: https://creativecommons.org/licenses/by/3.0/us/
2023-10-12 16:16:37 +00:00
.. tab :: metadata.json
2023-07-08 16:37:01 +00:00
.. code-block :: json
{
"databases": {
"database1": {
"source": "Alternative source",
"source_url": "http://example.com/",
"tables": {
"example_table": {
"description_html": "Custom <em>table</em> description",
"license": "CC BY 3.0 US",
"license_url": "https://creativecommons.org/licenses/by/3.0/us/"
}
}
}
}
}
.. [[[end]]]
2017-12-07 17:19:35 +00:00
Each of the top-level metadata fields can be used at the database and table level.
2019-05-19 21:24:12 +00:00
.. _metadata_source_license_about:
2019-05-19 20:13:27 +00:00
Source, license and about
-------------------------
2019-07-26 10:25:44 +00:00
The three visible metadata fields you can apply to everything, specific databases or specific tables are source, license and about. All three are optional.
2019-05-19 20:13:27 +00:00
**source** and **source_url** should be used to indicate where the underlying data came from.
**license** and **license_url** should be used to indicate the license under which the data can be used.
**about** and **about_url** can be used to link to further information about the project - an accompanying blog entry for example.
For each of these you can provide just the `` *_url `` field and Datasette will treat that as the default link label text and display the URL directly on the page.
2021-08-12 23:53:23 +00:00
.. _metadata_column_descriptions:
Column descriptions
-------------------
You can include descriptions for your columns by adding a `` "columns": {"name-of-column": "description-of-column"} `` block to your table metadata:
2023-07-08 16:37:01 +00:00
.. [[[cog
metadata_example(cog, {
2021-08-12 23:53:23 +00:00
"databases": {
"database1": {
"tables": {
"example_table": {
"columns": {
"column1": "Description of column 1",
"column2": "Description of column 2"
}
}
}
}
}
2023-07-08 16:37:01 +00:00
})
.. ]]]
2023-10-12 16:16:37 +00:00
.. tab :: metadata.yaml
2023-07-08 16:37:01 +00:00
.. code-block :: yaml
databases:
database1:
tables:
example_table:
columns:
column1: Description of column 1
column2: Description of column 2
2023-10-12 16:16:37 +00:00
.. tab :: metadata.json
2023-07-08 16:37:01 +00:00
.. code-block :: json
{
"databases": {
"database1": {
"tables": {
"example_table": {
"columns": {
"column1": "Description of column 1",
"column2": "Description of column 2"
}
}
}
}
}
}
.. [[[end]]]
2021-08-12 23:53:23 +00:00
These will be displayed at the top of the table page, and will also show in the cog menu for each column.
You can see an example of how these look at `latest.datasette.io/fixtures/roadside_attractions <https://latest.datasette.io/fixtures/roadside_attractions> `__ .
2018-04-14 10:16:09 +00:00
Specifying units for a column
-----------------------------
Datasette supports attaching units to a column, which will be used when displaying
values from that column. SI prefixes will be used where appropriate.
2020-04-02 19:30:53 +00:00
Column units are configured in the metadata like so:
2023-07-08 16:37:01 +00:00
.. [[[cog
metadata_example(cog, {
2018-04-14 10:16:09 +00:00
"databases": {
"database1": {
"tables": {
"example_table": {
"units": {
"column1": "metres",
"column2": "Hz"
}
}
}
}
}
2023-07-08 16:37:01 +00:00
})
.. ]]]
2023-10-12 16:16:37 +00:00
.. tab :: metadata.yaml
2023-07-08 16:37:01 +00:00
.. code-block :: yaml
databases:
database1:
tables:
example_table:
units:
column1: metres
column2: Hz
2023-10-12 16:16:37 +00:00
.. tab :: metadata.json
2023-07-08 16:37:01 +00:00
.. code-block :: json
{
"databases": {
"database1": {
"tables": {
"example_table": {
"units": {
"column1": "metres",
"column2": "Hz"
}
}
}
}
}
}
.. [[[end]]]
2018-04-14 10:16:09 +00:00
Units are interpreted using Pint_, and you can see the full list of available units in
2018-04-14 14:08:20 +00:00
Pint's `unit registry`_ . You can also add `custom units`_ to the metadata, which will be
2020-04-02 19:30:53 +00:00
registered with Pint:
2023-07-08 16:37:01 +00:00
.. [[[cog
metadata_example(cog, {
2018-04-14 11:27:06 +00:00
"custom_units": [
"decibel = [] = dB"
]
2023-07-08 16:37:01 +00:00
})
.. ]]]
2023-10-12 16:16:37 +00:00
.. tab :: metadata.yaml
2023-07-08 16:37:01 +00:00
.. code-block :: yaml
custom_units:
- decibel = [] = dB
2023-10-12 16:16:37 +00:00
.. tab :: metadata.json
2023-07-08 16:37:01 +00:00
.. code-block :: json
{
"custom_units": [
"decibel = [] = dB"
]
}
.. [[[end]]]
2018-04-14 10:16:09 +00:00
.. _Pint: https://pint.readthedocs.io/
.. _unit registry: https://github.com/hgrecco/pint/blob/master/pint/default_en.txt
2018-04-14 14:08:20 +00:00
.. _custom units: http://pint.readthedocs.io/en/latest/defining.html
2018-04-14 10:16:09 +00:00
2020-03-22 02:28:35 +00:00
.. _metadata_default_sort:
Setting a default sort order
----------------------------
2020-04-02 19:30:53 +00:00
By default Datasette tables are sorted by primary key. You can over-ride this default for a specific table using the `` "sort" `` or `` "sort_desc" `` metadata properties:
2023-07-08 16:37:01 +00:00
.. [[[cog
metadata_example(cog, {
2020-03-22 02:28:35 +00:00
"databases": {
"mydatabase": {
"tables": {
"example_table": {
"sort": "created"
}
}
}
}
2023-07-08 16:37:01 +00:00
})
.. ]]]
2020-03-22 02:28:35 +00:00
2023-10-12 16:16:37 +00:00
.. tab :: metadata.yaml
2023-07-08 16:37:01 +00:00
.. code-block :: yaml
databases:
mydatabase:
tables:
example_table:
sort: created
2023-10-12 16:16:37 +00:00
.. tab :: metadata.json
2020-04-02 19:30:53 +00:00
2023-07-08 16:37:01 +00:00
.. code-block :: json
2020-03-22 02:28:35 +00:00
2023-07-08 16:37:01 +00:00
{
"databases": {
"mydatabase": {
"tables": {
"example_table": {
"sort": "created"
}
}
}
}
}
.. [[[end]]]
Or use `` "sort_desc" `` to sort in descending order:
.. [[[cog
metadata_example(cog, {
2020-03-22 02:28:35 +00:00
"databases": {
"mydatabase": {
"tables": {
"example_table": {
"sort_desc": "created"
}
}
}
}
2023-07-08 16:37:01 +00:00
})
.. ]]]
2023-10-12 16:16:37 +00:00
.. tab :: metadata.yaml
2023-07-08 16:37:01 +00:00
.. code-block :: yaml
databases:
mydatabase:
tables:
example_table:
sort_desc: created
2023-10-12 16:16:37 +00:00
.. tab :: metadata.json
2023-07-08 16:37:01 +00:00
.. code-block :: json
{
"databases": {
"mydatabase": {
"tables": {
"example_table": {
"sort_desc": "created"
}
}
}
}
}
.. [[[end]]]
2020-03-22 02:28:35 +00:00
2020-05-28 05:00:04 +00:00
.. _metadata_page_size:
Setting a custom page size
--------------------------
2023-06-29 14:46:22 +00:00
Datasette defaults to displaying 100 rows per page, for both tables and views. You can change this default page size on a per-table or per-view basis using the `` "size" `` key in `` metadata.json `` :
2020-05-28 05:00:04 +00:00
2023-07-08 16:37:01 +00:00
.. [[[cog
metadata_example(cog, {
2020-05-28 05:00:04 +00:00
"databases": {
"mydatabase": {
"tables": {
"example_table": {
"size": 10
}
}
}
}
2023-07-08 16:37:01 +00:00
})
.. ]]]
2023-10-12 16:16:37 +00:00
.. tab :: metadata.yaml
2023-07-08 16:37:01 +00:00
.. code-block :: yaml
databases:
mydatabase:
tables:
example_table:
size: 10
2023-10-12 16:16:37 +00:00
.. tab :: metadata.json
2023-07-08 16:37:01 +00:00
.. code-block :: json
{
"databases": {
"mydatabase": {
"tables": {
"example_table": {
"size": 10
}
}
}
}
}
.. [[[end]]]
2020-05-28 05:00:04 +00:00
2021-01-11 21:33:54 +00:00
This size can still be over-ridden by passing e.g. `` ?_size=50 `` in the query string.
2020-05-28 05:00:04 +00:00
2018-09-19 17:48:12 +00:00
.. _metadata_sortable_columns:
2018-04-09 04:58:25 +00:00
Setting which columns can be used for sorting
---------------------------------------------
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 the optional
2020-04-02 19:30:53 +00:00
`` sortable_columns `` key:
2023-07-08 16:37:01 +00:00
.. [[[cog
metadata_example(cog, {
2018-04-09 04:58:25 +00:00
"databases": {
"database1": {
"tables": {
"example_table": {
"sortable_columns": [
"height",
"weight"
]
}
}
}
}
2023-07-08 16:37:01 +00:00
})
.. ]]]
2023-10-12 16:16:37 +00:00
.. tab :: metadata.yaml
2023-07-08 16:37:01 +00:00
.. code-block :: yaml
databases:
database1:
tables:
example_table:
sortable_columns:
- height
- weight
2023-10-12 16:16:37 +00:00
.. tab :: metadata.json
2023-07-08 16:37:01 +00:00
.. code-block :: json
{
"databases": {
"database1": {
"tables": {
"example_table": {
"sortable_columns": [
"height",
"weight"
]
}
}
}
}
}
.. [[[end]]]
2018-04-09 04:58:25 +00:00
This will restrict sorting of `` example_table `` to just the `` height `` and
`` weight `` columns.
You can also disable sorting entirely by setting `` "sortable_columns": [] ``
2020-05-28 18:54:57 +00:00
You can use `` sortable_columns `` to enable specific sort orders for a view called `` name_of_view `` in the database `` my_database `` like so:
2020-04-02 19:30:53 +00:00
2023-07-08 16:37:01 +00:00
.. [[[cog
metadata_example(cog, {
2018-08-28 09:56:34 +00:00
"databases": {
"my_database": {
"tables": {
"name_of_view": {
"sortable_columns": [
"clicks",
"impressions"
]
}
2018-08-06 00:29:23 +00:00
}
}
}
2023-07-08 16:37:01 +00:00
})
.. ]]]
2023-10-12 16:16:37 +00:00
.. tab :: metadata.yaml
2023-07-08 16:37:01 +00:00
.. code-block :: yaml
databases:
my_database:
tables:
name_of_view:
sortable_columns:
- clicks
- impressions
2023-10-12 16:16:37 +00:00
.. tab :: metadata.json
2023-07-08 16:37:01 +00:00
.. code-block :: json
{
"databases": {
"my_database": {
"tables": {
"name_of_view": {
"sortable_columns": [
"clicks",
"impressions"
]
}
}
}
}
}
.. [[[end]]]
2018-08-06 00:29:23 +00:00
?_labels= and ?_label=COL to expand foreign keys in JSON/CSV
These new querystring arguments can be used to request expanded foreign keys
in both JSON and CSV formats.
?_labels=on turns on expansions for ALL foreign key columns
?_label=COLUMN1&_label=COLUMN2 can be used to pick specific columns to expand
e.g. `Street_Tree_List.json?_label=qSpecies&_label=qLegalStatus`
{
"rowid": 233,
"TreeID": 121240,
"qLegalStatus": {
"value" 2,
"label": "Private"
}
"qSpecies": {
"value": 16,
"label": "Sycamore"
}
"qAddress": "91 Commonwealth Ave",
...
}
The labels option also works for the HTML and CSV views.
HTML defaults to `?_labels=on`, so if you pass `?_labels=off` you can disable
foreign key expansion entirely - or you can use `?_label=COLUMN` to request
just specific columns.
If you expand labels on CSV you get additional columns in the output:
`/Street_Tree_List.csv?_label=qLegalStatus`
rowid,TreeID,qLegalStatus,qLegalStatus_label...
1,141565,1,Permitted Site...
2,232565,2,Undocumented...
I also refactored the existing foreign key expansion code.
Closes #233. Refs #266.
2018-06-16 22:18:57 +00:00
.. _label_columns:
2018-06-18 03:21:02 +00:00
2018-04-22 17:51:43 +00:00
Specifying the label column for a table
---------------------------------------
Datasette's HTML interface attempts to display foreign key references as
labelled hyperlinks. By default, it looks for referenced tables that only have
two columns: a primary key column and one other. It assumes that the second
column should be used as the link label.
If your table has more than two columns you can specify which column should be
2020-04-02 19:30:53 +00:00
used for the link label with the `` label_column `` property:
2023-07-08 16:37:01 +00:00
.. [[[cog
metadata_example(cog, {
2018-04-22 17:51:43 +00:00
"databases": {
"database1": {
"tables": {
"example_table": {
"label_column": "title"
}
}
}
}
2023-07-08 16:37:01 +00:00
})
.. ]]]
2023-10-12 16:16:37 +00:00
.. tab :: metadata.yaml
2023-07-08 16:37:01 +00:00
.. code-block :: yaml
databases:
database1:
tables:
example_table:
label_column: title
2023-10-12 16:16:37 +00:00
.. tab :: metadata.json
2023-07-08 16:37:01 +00:00
.. code-block :: json
{
"databases": {
"database1": {
"tables": {
"example_table": {
"label_column": "title"
}
}
}
}
}
.. [[[end]]]
2018-04-22 17:51:43 +00:00
2020-05-30 18:40:30 +00:00
.. _metadata_hiding_tables:
2018-04-26 03:42:57 +00:00
Hiding tables
-------------
You can hide tables from the database listing view (in the same way that FTS and
2020-05-28 18:27:24 +00:00
SpatiaLite tables are automatically hidden) using `` "hidden": true `` :
2020-04-02 19:30:53 +00:00
2023-07-08 16:37:01 +00:00
.. [[[cog
metadata_example(cog, {
2018-04-26 03:42:57 +00:00
"databases": {
"database1": {
"tables": {
"example_table": {
2023-07-08 16:37:01 +00:00
"hidden": True
2018-04-26 03:42:57 +00:00
}
}
}
}
2023-07-08 16:37:01 +00:00
})
.. ]]]
2023-10-12 16:16:37 +00:00
.. tab :: metadata.yaml
2023-07-08 16:37:01 +00:00
.. code-block :: yaml
databases:
database1:
tables:
example_table:
hidden: true
2023-10-12 16:16:37 +00:00
.. tab :: metadata.json
2023-07-08 16:37:01 +00:00
.. code-block :: json
{
"databases": {
"database1": {
"tables": {
"example_table": {
"hidden": true
}
}
}
}
}
.. [[[end]]]
2020-04-02 19:30:53 +00:00
2023-10-12 16:16:37 +00:00
.. _metadata_reference:
2023-10-12 22:05:02 +00:00
Metadata reference
------------------
2023-10-12 16:16:37 +00:00
A full reference of every supported option in a `` metadata.json `` or `` metadata.yaml `` file.
2023-10-12 22:05:02 +00:00
Top-level metadata
2023-10-12 16:16:37 +00:00
~~~~~~~~~~~~~~~~~~
"Top-level" metadata refers to fields that can be specified at the root level of a metadata file. These attributes are meant to describe the entire Datasette instance.
The following are the full list of allowed top-level metadata fields:
- `` title ``
- `` description ``
- `` description_html ``
- `` license ``
- `` license_url ``
- `` source ``
- `` source_url ``
2023-10-12 22:05:02 +00:00
Database-level metadata
~~~~~~~~~~~~~~~~~~~~~~~
2023-10-12 16:16:37 +00:00
"Database-level" metadata refers to fields that can be specified for each database in a Datasette instance. These attributes should be listed under a database inside the `"databases"` field.
The following are the full list of allowed database-level metadata fields:
- `` source ``
- `` source_url ``
- `` license ``
- `` license_url ``
- `` about ``
- `` about_url ``
2023-10-12 22:05:02 +00:00
Table-level metadata
~~~~~~~~~~~~~~~~~~~~
2023-10-12 16:16:37 +00:00
"Table-level" metadata refers to fields that can be specified for each table in a Datasette instance. These attributes should be listed under a specific table using the `"tables"` field.
The following are the full list of allowed table-level metadata fields:
- `` source ``
- `` source_url ``
- `` license ``
- `` license_url ``
- `` about ``
- `` about_url ``
- `` hidden ``
- `` sort/sort_desc ``
- `` size ``
- `` sortable_columns ``
- `` label_column ``
- `` facets ``
- `` fts_table ``
- `` fts_pk ``
- `` searchmode ``
- `` columns ``