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:

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 via the command-line

The recommended way to configure Datasette is using a datasette.yaml file passed to -c/--config. You can also pass individual settings to Datasette using the -s/--setting option, which can be used multiple times:

datasette mydatabase.db \
  --setting settings.default_page_size 50 \
  --setting settings.sql_time_limit_ms 3500

This option takes dotted-notation for the first argument and a value for the second argument. This means you can use it to set any configuration value that would be valid in a datasette.yaml file.

It also works for plugin configuration, for example for datasette-cluster-map:

datasette mydatabase.db \
  --setting plugins.datasette-cluster-map.latitude_column xlat \
  --setting plugins.datasette-cluster-map.longitude_column xlon

If the value you provide is a valid JSON object or list it will be treated as nested data, allowing you to configure plugins that accept lists such as datasette-proxy-url:

datasette mydatabase.db \
  -s plugins.datasette-proxy-url.paths '[{"path": "/proxy", "backend": "http://example.com/"}]'

This is equivalent to a datasette.yaml file containing the following:

datasette.yaml

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

datasette.json

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

datasette.yaml reference

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

datasette.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

datasette.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"
            }
          }
        }
      }
    }
  }
}

Settings

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

datasette.yaml

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

datasette.json

{
  "settings": {
    "default_allow_sql": "off",
    "default_page_size": 50
  }
}

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

Plugin configuration

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:

datasette.yaml

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

datasette.json

{
  "plugins": {
    "datasette-my-plugin": {
      "key": "my_value"
    }
  }
}

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:

datasette.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

datasette.json

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

Permissions configuration

Datasette's authentication and permissions system can also be configured using datasette.yaml.

Here is a simple example:

datasette.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

datasette.json

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

Access permissions in datasette.yaml has the full details.

Canned queries configuration

Canned 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:

datasette.yaml

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

datasette.json

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

See the canned queries documentation for more, including how to configure writable canned queries.

Custom CSS and JavaScript

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

datasette.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

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

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

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

datasette.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=

datasette.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="
    }
  ]
}

This will produce:

<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

Items in "extra_js_urls" can specify "module": true if they reference JavaScript that uses JavaScript modules. This configuration:

datasette.yaml

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

datasette.json

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

Will produce this HTML:

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

Table configuration

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

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:

datasette.yaml

databases:
  mydatabase:
    tables:
      example_table:
        sort: created

datasette.json

{
  "databases": {
    "mydatabase": {
      "tables": {
        "example_table": {
          "sort": "created"
        }
      }
    }
  }
}

Or use sort_desc to sort in descending order:

datasette.yaml

databases:
  mydatabase:
    tables:
      example_table:
        sort_desc: created

datasette.json

{
  "databases": {
    "mydatabase": {
      "tables": {
        "example_table": {
          "sort_desc": "created"
        }
      }
    }
  }
}

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:

datasette.yaml

databases:
  mydatabase:
    tables:
      example_table:
        size: 10

datasette.json

{
  "databases": {
    "mydatabase": {
      "tables": {
        "example_table": {
          "size": 10
        }
      }
    }
  }
}

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

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:

datasette.yaml

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

datasette.json

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

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:

datasette.yaml

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

datasette.json

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

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:

datasette.yaml

databases:
  mydatabase:
    tables:
      example_table:
        label_column: title

datasette.json

{
  "databases": {
    "mydatabase": {
      "tables": {
        "example_table": {
          "label_column": "title"
        }
      }
    }
  }
}

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:

datasette.yaml

databases:
  mydatabase:
    tables:
      example_table:
        hidden: true

datasette.json

{
  "databases": {
    "mydatabase": {
      "tables": {
        "example_table": {
          "hidden": true
        }
      }
    }
  }
}

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 default_facet_size setting). See Facets in configuration for full details.

datasette.yaml

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

datasette.json

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

You can also specify array or date facets using JSON objects with a single key of array or date:

facets:
- array: tags
- date: created

fts_table / fts_pk / searchmode

These configure full-text search for a table or view. See Configuring full-text search for a 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.

datasette.yaml

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

datasette.json

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

column_types

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

The simplest form maps column names to type name strings:

datasette.yaml

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

datasette.json

{
  "databases": {
    "mydatabase": {
      "tables": {
        "example_table": {
          "column_types": {
            "website": "url",
            "contact": "email",
            "extra_data": "json"
          }
        }
      }
    }
  }
}

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

datasette.yaml

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

datasette.json

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