sections
478 rows sorted by references
This data as json, CSV (advanced)
id | page | ref | title | content | breadcrumbs | references ▼ |
---|---|---|---|---|---|---|
custom_templates:custom-pages-redirects | custom_templates | custom-pages-redirects | Custom redirects | You can use the custom_redirect(location) function to redirect users to another page, for example in a file called pages/datasette.html : {{ custom_redirect("https://github.com/simonw/datasette") }} Now requests to http://localhost:8001/datasette will result in a redirect. These redirects are served with a 302 Found status code by default. You can send a 301 Moved Permanently code by passing 301 as the second argument to the function: {{ custom_redirect("https://github.com/simonw/datasette", 301) }} | ["Custom pages and templates", "Custom pages"] | [] |
custom_templates:customization | custom_templates | customization | Custom pages and templates | Datasette provides a number of ways of customizing the way data is displayed. | [] | [] |
custom_templates:customization-static-files | custom_templates | customization-static-files | Serving static files | Datasette can serve static files for you, using the --static option. Consider the following directory structure: metadata.json static-files/styles.css static-files/app.js You can start Datasette using --static assets:static-files/ to serve those files from the /assets/ mount point: $ datasette -m metadata.json --static assets:static-files/ --memory The following URLs will now serve the content from those CSS and JS files: http://localhost:8001/assets/styles.css http://localhost:8001/assets/app.js You can reference those files from metadata.json like so: { "extra_css_urls": [ "/assets/styles.css" ], "extra_js_urls": [ "/assets/app.js" ] } | ["Custom pages and templates", "Custom CSS and JavaScript"] | [] |
custom_templates:id1 | custom_templates | id1 | Custom pages | You can add templated pages to your Datasette instance by creating HTML files in a pages directory within your templates directory. For example, to add a custom page that is served at http://localhost/about you would create a file in templates/pages/about.html , then start Datasette like this: $ datasette mydb.db --template-dir=templates/ You can nest directories within pages to create a nested structure. To create a http://localhost:8001/about/map page you would create templates/pages/about/map.html . | ["Custom pages and templates"] | [] |
custom_templates:publishing-static-assets | custom_templates | publishing-static-assets | Publishing static assets | The datasette publish command can be used to publish your static assets, using the same syntax as above: $ datasette publish cloudrun mydb.db --static assets:static-files/ This will upload the contents of the static-files/ directory as part of the deployment, and configure Datasette to correctly serve the assets from /assets/ . | ["Custom pages and templates", "Custom CSS and JavaScript"] | [] |
deploying:deploying | deploying | deploying | Deploying Datasette | The quickest way to deploy a Datasette instance on the internet is to use the datasette publish command, described in Publishing data . 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:deploying-fundamentals | deploying | 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 Datasette"] | [] |
deploying:deploying-proxy | deploying | 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 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. | ["Deploying Datasette"] | [] |
deploying:deploying-systemd | deploying | 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: [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 Configuration directory mode 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 th… | ["Deploying Datasette"] | [] |
facets:facets-in-query-strings | facets | facets-in-query-strings | Facets in query strings | To turn on faceting for specific columns on a Datasette table view, add one or more _facet=COLUMN parameters to the URL. For example, if you want to turn on facets for the city_id and state columns, construct a URL that looks like this: /dbname/tablename?_facet=state&_facet=city_id This works for both the HTML interface and the .json view. When enabled, facets will cause a facet_results block to be added to the JSON output, looking something like this: { "state": { "name": "state", "results": [ { "value": "CA", "label": "CA", "count": 10, "toggle_url": "http://...?_facet=city_id&_facet=state&state=CA", "selected": false }, { "value": "MI", "label": "MI", "count": 4, "toggle_url": "http://...?_facet=city_id&_facet=state&state=MI", "selected": false }, { "value": "MC", "label": "MC", "count": 1, "toggle_url": "http://...?_facet=city_id&_facet=state&state=MC", "selected": false } ], "truncated": false } "city_id": { "name": "city_id", "results": [ { "value": 1, "label": "San Francisco", "count": 6, "toggle_url": "http://...?_facet=city_id&_facet=state&city_id=1", "selected": false }, { "value": 2, "label": "Los Angeles", "count": 4, "toggle_url": "http://...?_facet=city_id&_facet=state&city_id=2", "selected": false }, { "value": 3, "label": "Detroit", "count": 4, "toggle_url": "http://...?_facet=city_id&_facet=state&city_id=3", "selected": false }, { "value": 4, "label": "Memnonia", "count": 1, "toggle_url": "http://...?_facet=city_id&_facet=state&city_id=4", "selected": false } ], "truncated": false } } If Datasette detect… | ["Facets"] | [] |
facets:facets-metadata | facets | facets-metadata | Facets in metadata.json | You can turn facets on by default for specific tables by adding them to a "facets" key in a Datasette Metadata file. Here's an example that turns on faceting by default for the qLegalStatus column in the Street_Tree_List table in the sf-trees database: { "databases": { "sf-trees": { "tables": { "Street_Tree_List": { "facets": ["qLegalStatus"] } } } } } Facets defined in this way will always be shown in the interface and returned in the API, regardless of the _facet arguments passed to the view. You can specify array or date facets in metadata using JSON objects with a single key of array or date and a value specifying the column, like this: { "facets": [ {"array": "tags"}, {"date": "created"} ] } You can change the default facet size (the number of results shown for each facet) for a table using facet_size : { "databases": { "sf-trees": { "tables": { "Street_Tree_List": { "facets": ["qLegalStatus"], "facet_size": 10 } } } } } | ["Facets"] | [] |
facets:suggested-facets | facets | suggested-facets | Suggested facets | Datasette's table UI will suggest facets for the user to apply, based on the following criteria: For the currently filtered data are there any columns which, if applied as a facet... Will return 30 or less unique options Will return more than one unique option Will return less unique options than the total number of filtered rows And the query used to evaluate this criteria can be completed in under 50ms That last point is particularly important: Datasette runs a query for every column that is displayed on a page, which could get expensive - so to avoid slow load times it sets a time limit of just 50ms for each of those queries. This means suggested facets are unlikely to appear for tables with millions of records in them. | ["Facets"] | [] |
full_text_search:full-text-search-fts-versions | full_text_search | full-text-search-fts-versions | FTS versions | There are three different versions of the SQLite FTS module: FTS3, FTS4 and FTS5. You can tell which versions are supported by your instance of Datasette by checking the /-/versions page. FTS5 is the most advanced module but may not be available in the SQLite version that is bundled with your Python installation. Most importantly, FTS5 is the only version that has the ability to order by search relevance without needing extra code. If you can't be sure that FTS5 will be available, you should use FTS4. | ["Full-text search"] | [] |
getting_started:getting-started | getting_started | getting-started | Getting started | [] | [] | |
index:contents | index | contents | Contents | Getting started Play with a live demo Follow a tutorial Datasette in your browser with Datasette Lite Try Datasette without installing anything using Glitch Using Datasette on your own computer Installation Basic installation Datasette Desktop for Mac Using Homebrew Using pip Advanced installation options Using pipx Using Docker A note about extensions The Datasette Ecosystem sqlite-utils Dogsheep CLI reference datasette --help datasette serve datasette --get datasette serve --help-settings datasette plugins datasette install datasette uninstall datasette publish datasette publish cloudrun datasette publish heroku datasette package datasette inspect Pages and API endpoints Top-level index Database Table Row Publishing data datasette publish Publishing to Google Cloud Run Publishing to Heroku Publishing to Vercel Publishing to Fly Custom metadata and plugins datasette package Deploying Datasette Deployment fundamentals Running Datasette using systemd Running Datasette using OpenRC Deploying using buildpacks Running Datasette behind a proxy Nginx proxy configuration Apache proxy configuration JSON API Different shapes Pagination Special JSON arguments Table arguments Column filter arguments Special table arguments Expanding foreign key references Discovering the JSON for a page Running SQL queries Named parameters Views Canned queries Canned query parameters Additional canned query options Writable canned queries Magic parameters JSON API for writable canned queries Pagination Cross-database queries Authentication and permissions Actors Using the "root" actor Permissions Defining permissions with "allow" blocks The /-/allow-debug tool Configuring permissions in metadata.json Controlling access to an instance Controlling access to specific databases Controlling access to specific tables and views Controlling access to specific canned queries Controlling the ability to execute arbitrary SQL Checking permissions in plugins actor_matches_allow() The permissions debug tool The ds_actor cookie Including an expiry time Th… | ["Datasette"] | [] |
installation:id1 | installation | id1 | Installation | If you just want to try Datasette out you don't need to install anything: see Try Datasette without installing anything using Glitch There are two main options for installing Datasette. You can install it directly on to your machine, or you can install it using Docker. If you want to start making contributions to the Datasette project by installing a copy that lets you directly modify the code, take a look at our guide to Setting up a development environment . Basic installation Datasette Desktop for Mac Using Homebrew Using pip Advanced installation options Using pipx Installing plugins using pipx Upgrading packages using pipx Using Docker Loading SpatiaLite Installing plugins A note about extensions | [] | [] |
installation:installation-advanced | installation | installation-advanced | Advanced installation options | ["Installation"] | [] | |
installation:installation-basic | installation | installation-basic | Basic installation | ["Installation"] | [] | |
installation:installation-extensions | installation | installation-extensions | A note about extensions | SQLite supports extensions, such as SpatiaLite for geospatial operations. These can be loaded using the --load-extension argument, like so: datasette --load-extension=/usr/local/lib/mod_spatialite.dylib Some Python installations do not include support for SQLite extensions. If this is the case you will see the following error when you attempt to load an extension: Your Python installation does not have the ability to load SQLite extensions. In some cases you may see the following error message instead: AttributeError: 'sqlite3.Connection' object has no attribute 'enable_load_extension' On macOS the easiest fix for this is to install Datasette using Homebrew: brew install datasette Use which datasette to confirm that datasette will run that version. The output should look something like this: /usr/local/opt/datasette/bin/datasette If you get a different location here such as /Library/Frameworks/Python.framework/Versions/3.10/bin/datasette you can run the following command to cause datasette to execute the Homebrew version instead: alias datasette=$(echo $(brew --prefix datasette)/bin/datasette) You can undo this operation using: unalias datasette If you need to run SQLite with extension support for other Python code, you can do so by install Python itself using Homebrew: brew install python Then executing Python using: /usr/local/opt/python@3/libexec/bin/python A more convenient way to work with this version of Python may be to use it to create a virtual environment: /usr/local/opt/python@3/libexec/bin/python -m venv datasette-venv Then activate it like this: source datasette-venv/bin/activate Now running python and pip will work against a version of … | ["Installation"] | [] |
installation:installing-plugins-using-pipx | installation | installing-plugins-using-pipx | Installing plugins using pipx | You can install additional datasette plugins with pipx inject like so: $ pipx inject datasette datasette-json-html injected package datasette-json-html into venv datasette done! ✨ 🌟 ✨ $ datasette plugins [ { "name": "datasette-json-html", "static": false, "templates": false, "version": "0.6" } ] | ["Installation", "Advanced installation options", "Using pipx"] | [] |
installation:upgrading-packages-using-pipx | installation | upgrading-packages-using-pipx | Upgrading packages using pipx | You can upgrade your pipx installation to the latest release of Datasette using pipx upgrade datasette : $ pipx upgrade datasette upgraded package datasette from 0.39 to 0.40 (location: /Users/simon/.local/pipx/venvs/datasette) To upgrade a plugin within the pipx environment use pipx runpip datasette install -U name-of-plugin - like this: % datasette plugins [ { "name": "datasette-vega", "static": true, "templates": false, "version": "0.6" } ] $ pipx runpip datasette install -U datasette-vega Collecting datasette-vega Downloading datasette_vega-0.6.2-py3-none-any.whl (1.8 MB) |████████████████████████████████| 1.8 MB 2.0 MB/s ... Installing collected packages: datasette-vega Attempting uninstall: datasette-vega Found existing installation: datasette-vega 0.6 Uninstalling datasette-vega-0.6: Successfully uninstalled datasette-vega-0.6 Successfully installed datasette-vega-0.6.2 $ datasette plugins [ { "name": "datasette-vega", "static": true, "templates": false, "version": "0.6.2" } ] | ["Installation", "Advanced installation options", "Using pipx"] | [] |
internals:database-close | internals | database-close | db.close() | Closes all of the open connections to file-backed databases. This is mainly intended to be used by large test suites, to avoid hitting limits on the number of open files. | ["Internals for plugins", "Database class"] | [] |
internals:database-constructor | internals | database-constructor | Database(ds, path=None, is_mutable=True, is_memory=False, memory_name=None) | The Database() constructor can be used by plugins, in conjunction with .add_database(db, name=None, route=None) , to create and register new databases. The arguments are as follows: ds - Datasette class (required) The Datasette instance you are attaching this database to. path - string Path to a SQLite database file on disk. is_mutable - boolean Set this to False to cause Datasette to open the file in immutable mode. is_memory - boolean Use this to create non-shared memory connections. memory_name - string or None Use this to create a named in-memory database. Unlike regular memory databases these can be accessed by multiple threads and will persist an changes made to them for the lifetime of the Datasette server process. The first argument is the datasette instance you are attaching to, the second is a path= , then is_mutable and is_memory are both optional arguments. | ["Internals for plugins", "Database class"] | [] |
internals:database-execute | internals | database-execute | await db.execute(sql, ...) | Executes a SQL query against the database and returns the resulting rows (see Results ). sql - string (required) The SQL query to execute. This can include ? or :named parameters. params - list or dict A list or dictionary of values to use for the parameters. List for ? , dictionary for :named . truncate - boolean Should the rows returned by the query be truncated at the maximum page size? Defaults to True , set this to False to disable truncation. custom_time_limit - integer ms A custom time limit for this query. This can be set to a lower value than the Datasette configured default. If a query takes longer than this it will be terminated early and raise a dataette.database.QueryInterrupted exception. page_size - integer Set a custom page size for truncation, over-riding the configured Datasette default. log_sql_errors - boolean Should any SQL errors be logged to the console in addition to being raised as an error? Defaults to True . | ["Internals for plugins", "Database class"] | [] |
internals:database-execute-fn | internals | database-execute-fn | await db.execute_fn(fn) | Executes a given callback function against a read-only database connection running in a thread. The function will be passed a SQLite connection, and the return value from the function will be returned by the await . Example usage: def get_version(conn): return conn.execute( "select sqlite_version()" ).fetchall()[0][0] version = await db.execute_fn(get_version) | ["Internals for plugins", "Database class"] | [] |
internals:database-execute-write | internals | database-execute-write | await db.execute_write(sql, params=None, block=True) | SQLite only allows one database connection to write at a time. Datasette handles this for you by maintaining a queue of writes to be executed against a given database. Plugins can submit write operations to this queue and they will be executed in the order in which they are received. This method can be used to queue up a non-SELECT SQL query to be executed against a single write connection to the database. You can pass additional SQL parameters as a tuple or dictionary. The method will block until the operation is completed, and the return value will be the return from calling conn.execute(...) using the underlying sqlite3 Python library. If you pass block=False this behaviour changes to "fire and forget" - queries will be added to the write queue and executed in a separate thread while your code can continue to do other things. The method will return a UUID representing the queued task. | ["Internals for plugins", "Database class"] | [] |
internals:database-execute-write-fn | internals | database-execute-write-fn | await db.execute_write_fn(fn, block=True) | This method works like .execute_write() , but instead of a SQL statement you give it a callable Python function. Your function will be queued up and then called when the write connection is available, passing that connection as the argument to the function. The function can then perform multiple actions, safe in the knowledge that it has exclusive access to the single writable connection for as long as it is executing. fn needs to be a regular function, not an async def function. For example: def delete_and_return_count(conn): conn.execute("delete from some_table where id > 5") return conn.execute( "select count(*) from some_table" ).fetchone()[0] try: num_rows_left = await database.execute_write_fn( delete_and_return_count ) except Exception as e: print("An error occurred:", e) The value returned from await database.execute_write_fn(...) will be the return value from your function. If your function raises an exception that exception will be propagated up to the await line. If you specify block=False the method becomes fire-and-forget, queueing your function to be executed and then allowing your code after the call to .execute_write_fn() to continue running while the underlying thread waits for an opportunity to run your function. A UUID representing the queued task will be returned. Any exceptions in your code will be silently swallowed. | ["Internals for plugins", "Database class"] | [] |
internals:database-hash | internals | database-hash | db.hash | If the database was opened in immutable mode, this property returns the 64 character SHA-256 hash of the database contents as a string. Otherwise it returns None . | ["Internals for plugins", "Database class"] | [] |
internals:datasette-absolute-url | internals | datasette-absolute-url | .absolute_url(request, path) | request - Request The current Request object path - string A path, for example /dbname/table.json Returns the absolute URL for the given path, including the protocol and host. For example: absolute_url = datasette.absolute_url( request, "/dbname/table.json" ) # Would return "http://localhost:8001/dbname/table.json" The current request object is used to determine the hostname and protocol that should be used for the returned URL. The force_https_urls configuration setting is taken into account. | ["Internals for plugins", "Datasette class"] | [] |
internals:datasette-add-database | internals | datasette-add-database | .add_database(db, name=None, route=None) | db - datasette.database.Database instance The database to be attached. name - string, optional The name to be used for this database . If not specified Datasette will pick one based on the filename or memory name. route - string, optional This will be used in the URL path. If not specified, it will default to the same thing as the name . The datasette.add_database(db) method lets you add a new database to the current Datasette instance. The db parameter should be an instance of the datasette.database.Database class. For example: from datasette.database import Database datasette.add_database( Database( datasette, path="path/to/my-new-database.db", ) ) This will add a mutable database and serve it at /my-new-database . Use is_mutable=False to add an immutable database. .add_database() returns the Database instance, with its name set as the database.name attribute. Any time you are working with a newly added database you should use the return value of .add_database() , for example: db = datasette.add_database( Database(datasette, memory_name="statistics") ) await db.execute_write( "CREATE TABLE foo(id integer primary key)" ) | ["Internals for plugins", "Datasette class"] | [] |
internals:datasette-add-memory-database | internals | datasette-add-memory-database | .add_memory_database(name) | Adds a shared in-memory database with the specified name: datasette.add_memory_database("statistics") This is a shortcut for the following: from datasette.database import Database datasette.add_database( Database(datasette, memory_name="statistics") ) Using either of these pattern will result in the in-memory database being served at /statistics . | ["Internals for plugins", "Datasette class"] | [] |
internals:datasette-add-message | internals | datasette-add-message | .add_message(request, message, type=datasette.INFO) | request - Request The current Request object message - string The message string type - constant, optional The message type - datasette.INFO , datasette.WARNING or datasette.ERROR Datasette's flash messaging mechanism allows you to add a message that will be displayed to the user on the next page that they visit. Messages are persisted in a ds_messages cookie. This method adds a message to that cookie. You can try out these messages (including the different visual styling of the three message types) using the /-/messages debugging tool. | ["Internals for plugins", "Datasette class"] | [] |
internals:datasette-check-visibility | internals | datasette-check-visibility | await .check_visibility(actor, action=None, resource=None, permissions=None) | actor - dictionary The authenticated actor. This is usually request.actor . action - string, optional The name of the action that is being permission checked. resource - string or tuple, optional The resource, e.g. the name of the database, or a tuple of two strings containing the name of the database and the name of the table. Only some permissions apply to a resource. permissions - list of action strings or (action, resource) tuples, optional Provide this instead of action and resource to check multiple permissions at once. This convenience method can be used to answer the question "should this item be considered private, in that it is visible to me but it is not visible to anonymous users?" It returns a tuple of two booleans, (visible, private) . visible indicates if the actor can see this resource. private will be True if an anonymous user would not be able to view the resource. This example checks if the user can access a specific table, and sets private so that a padlock icon can later be displayed: visible, private = await self.ds.check_visibility( request.actor, action="view-table", resource=(database, table), ) The following example runs three checks in a row, similar to await .ensure_permissions(actor, permissions) . If any of the checks are denied before one of them is explicitly granted then visible will be F… | ["Internals for plugins", "Datasette class"] | [] |
internals:datasette-databases | internals | datasette-databases | .databases | Property exposing a collections.OrderedDict of databases currently connected to Datasette. The dictionary keys are the name of the database that is used in the URL - e.g. /fixtures would have a key of "fixtures" . The values are Database class instances. All databases are listed, irrespective of user permissions. This means that the _internal database will always be listed here. | ["Internals for plugins", "Datasette class"] | [] |
internals:datasette-ensure-permissions | internals | datasette-ensure-permissions | await .ensure_permissions(actor, permissions) | actor - dictionary The authenticated actor. This is usually request.actor . permissions - list A list of permissions to check. Each permission in that list can be a string action name or a 2-tuple of (action, resource) . This method allows multiple permissions to be checked at once. It raises a datasette.Forbidden exception if any of the checks are denied before one of them is explicitly granted. This is useful when you need to check multiple permissions at once. For example, an actor should be able to view a table if either one of the following checks returns True or not a single one of them returns False : await self.ds.ensure_permissions( request.actor, [ ("view-table", (database, table)), ("view-database", database), "view-instance", ], ) | ["Internals for plugins", "Datasette class"] | [] |
internals:datasette-get-database | internals | datasette-get-database | .get_database(name) | name - string, optional The name of the database - optional. Returns the specified database object. Raises a KeyError if the database does not exist. Call this method without an argument to return the first connected database. | ["Internals for plugins", "Datasette class"] | [] |
internals:datasette-permission-allowed | internals | datasette-permission-allowed | await .permission_allowed(actor, action, resource=None, default=False) | actor - dictionary The authenticated actor. This is usually request.actor . action - string The name of the action that is being permission checked. resource - string or tuple, optional The resource, e.g. the name of the database, or a tuple of two strings containing the name of the database and the name of the table. Only some permissions apply to a resource. default - optional, True or False Should this permission check be default allow or default deny. Check if the given actor has permission to perform the given action on the given resource. Some permission checks are carried out against rules defined in metadata.json , while other custom permissions may be decided by plugins that implement the permission_allowed(datasette, actor, action, resource) plugin hook. If neither metadata.json nor any of the plugins provide an answer to the permission query the default argument will be returned. See Built-in permissions for a full list of permission actions included in Datasette core. | ["Internals for plugins", "Datasette class"] | [] |
internals:datasette-plugin-config | internals | datasette-plugin-config | .plugin_config(plugin_name, database=None, table=None) | plugin_name - string The name of the plugin to look up configuration for. Usually this is something similar to datasette-cluster-map . database - None or string The database the user is interacting with. table - None or string The table the user is interacting with. This method lets you read plugin configuration values that were set in metadata.json . See Writing plugins that accept configuration for full details of how this method should be used. The return value will be the value from the configuration file - usually a dictionary. If the plugin is not configured the return value will be None . | ["Internals for plugins", "Datasette class"] | [] |
internals:datasette-remove-database | internals | datasette-remove-database | .remove_database(name) | name - string The name of the database to be removed. This removes a database that has been previously added. name= is the unique name of that database. | ["Internals for plugins", "Datasette class"] | [] |
internals:datasette-setting | internals | datasette-setting | .setting(key) | key - string The name of the setting, e.g. base_url . Returns the configured value for the specified setting . This can be a string, boolean or integer depending on the requested setting. For example: downloads_are_allowed = datasette.setting("allow_download") | ["Internals for plugins", "Datasette class"] | [] |
internals:datasette-unsign | internals | datasette-unsign | .unsign(value, namespace="default") | signed - any serializable type The signed string that was created using .sign(value, namespace="default") . namespace - string, optional The alternative namespace, if one was used. Returns the original, decoded object that was passed to .sign(value, namespace="default") . If the signature is not valid this raises a itsdangerous.BadSignature exception. | ["Internals for plugins", "Datasette class"] | [] |
internals:internals | internals | internals | Internals for plugins | Many Plugin hooks are passed objects that provide access to internal Datasette functionality. The interface to these objects should not be considered stable with the exception of methods that are documented here. | [] | [] |
internals:internals-database | internals | internals-database | Database class | Instances of the Database class can be used to execute queries against attached SQLite databases, and to run introspection against their schemas. | ["Internals for plugins"] | [] |
internals:internals-database-introspection | internals | internals-database-introspection | Database introspection | The Database class also provides properties and methods for introspecting the database. db.name - string The name of the database - usually the filename without the .db prefix. db.size - integer The size of the database file in bytes. 0 for :memory: databases. db.mtime_ns - integer or None The last modification time of the database file in nanoseconds since the epoch. None for :memory: databases. db.is_mutable - boolean Is this database mutable, and allowed to accept writes? db.is_memory - boolean Is this database an in-memory database? await db.attached_databases() - list of named tuples Returns a list of additional databases that have been connected to this database using the SQLite ATTACH command. Each named tuple has fields seq , name and file . await db.table_exists(table) - boolean Check if a table called table exists. await db.table_names() - list of strings … | ["Internals for plugins", "Database class"] | [] |
internals:internals-datasette | internals | internals-datasette | Datasette class | This object is an instance of the Datasette class, passed to many plugin hooks as an argument called datasette . You can create your own instance of this - for example to help write tests for a plugin - like so: from datasette.app import Datasette # With no arguments a single in-memory database will be attached datasette = Datasette() # The files= argument can load files from disk datasette = Datasette(files=["/path/to/my-database.db"]) # Pass metadata as a JSON dictionary like this datasette = Datasette( files=["/path/to/my-database.db"], metadata={ "databases": { "my-database": { "description": "This is my database" } } }, ) Constructor parameters include: files=[...] - a list of database files to open immutables=[...] - a list of database files to open in immutable mode metadata={...} - a dictionary of Metadata config_dir=... - the configuration directory to use, stored in datasette.config_dir | ["Internals for plugins"] | [] |
internals:internals-datasette-urls | internals | internals-datasette-urls | datasette.urls | The datasette.urls object contains methods for building URLs to pages within Datasette. Plugins should use this to link to pages, since these methods take into account any base_url configuration setting that might be in effect. datasette.urls.instance(format=None) Returns the URL to the Datasette instance root page. This is usually "/" . datasette.urls.path(path, format=None) Takes a path and returns the full path, taking base_url into account. For example, datasette.urls.path("-/logout") will return the path to the logout page, which will be "/-/logout" by default or /prefix-path/-/logout if base_url is set to /prefix-path/ datasette.urls.logout() Returns the URL to the logout page, usually "/-/logout" datasette.urls.static(path) Returns the URL of one of Datasette's default static assets, for example "/-/static/app.css" datasette.urls.static_plugins(plugin_name, path) Returns the URL of one of the static assets belonging to a plugin. datasette.urls.static_plugins("datasette_cluster_map", "datasette-cluster-map.js") would return "/-/static-plugins/datasette_cluster_map/datasette-cluster-map.js" datasette.urls.static(path) … | ["Internals for plugins", "Datasette class"] | [] |
internals:internals-multiparams | internals | internals-multiparams | The MultiParams class | request.args is a MultiParams object - a dictionary-like object which provides access to query string parameters that may have multiple values. Consider the query string ?foo=1&foo=2&bar=3 - with two values for foo and one value for bar . request.args[key] - string Returns the first value for that key, or raises a KeyError if the key is missing. For the above example request.args["foo"] would return "1" . request.args.get(key) - string or None Returns the first value for that key, or None if the key is missing. Pass a second argument to specify a different default, e.g. q = request.args.get("q", "") . request.args.getlist(key) - list of strings Returns the list of strings for that key. request.args.getlist("foo") would return ["1", "2"] in the above example. request.args.getlist("bar") would return ["3"] . If the key is missing an empty list will be returned. request.args.keys() - list of strings Returns the list of available keys - for the example this would be ["foo", "bar"] . key in request.args - True or False You can use if key in request.args to check if a key is present. for key in request.args - iterator This lets you loop through every available key. le… | ["Internals for plugins"] | [] |
internals:internals-response | internals | internals-response | Response class | The Response class can be returned from view functions that have been registered using the register_routes(datasette) hook. The Response() constructor takes the following arguments: body - string The body of the response. status - integer (optional) The HTTP status - defaults to 200. headers - dictionary (optional) A dictionary of extra HTTP headers, e.g. {"x-hello": "world"} . content_type - string (optional) The content-type for the response. Defaults to text/plain . For example: from datasette.utils.asgi import Response response = Response( "<xml>This is XML</xml>", content_type="application/xml; charset=utf-8", ) The quickest way to create responses is using the Response.text(...) , Response.html(...) , Response.json(...) or Response.redirect(...) helper methods: from datasette.utils.asgi import Response html_response = Response.html("This is HTML") json_response = Response.json({"this_is": "json"}) text_response = Response.text( "This will become utf-8 encoded text" ) # Redirects are served as 302, unless you pass status=301: redirect_response = Response.redirect( "https://latest.datasette.io/" ) Each of these responses will use the correct corresponding content-type - text/html; charset=utf-8 , application/json; charset=utf-8 or text/plain; charset=utf-8 respectively. Each of the helper methods take optional status= and headers= argument… | ["Internals for plugins"] | [] |
internals:internals-response-asgi-send | internals | internals-response-asgi-send | Returning a response with .asgi_send(send) | In most cases you will return Response objects from your own view functions. You can also use a Response instance to respond at a lower level via ASGI, for example if you are writing code that uses the asgi_wrapper(datasette) hook. Create a Response object and then use await response.asgi_send(send) , passing the ASGI send function. For example: async def require_authorization(scope, receive, send): response = Response.text( "401 Authorization Required", headers={ "www-authenticate": 'Basic realm="Datasette", charset="UTF-8"' }, status=401, ) await response.asgi_send(send) | ["Internals for plugins", "Response class"] | [] |
internals:internals-response-set-cookie | internals | internals-response-set-cookie | Setting cookies with response.set_cookie() | To set cookies on the response, use the response.set_cookie(...) method. The method signature looks like this: def set_cookie( self, key, value="", max_age=None, expires=None, path="/", domain=None, secure=False, httponly=False, samesite="lax", ): ... You can use this with datasette.sign() to set signed cookies. Here's how you would set the ds_actor cookie for use with Datasette authentication : response = Response.redirect("/") response.set_cookie( "ds_actor", datasette.sign({"a": {"id": "cleopaws"}}, "actor"), ) return response | ["Internals for plugins", "Response class"] | [] |
internals:internals-shortcuts | internals | internals-shortcuts | Import shortcuts | The following commonly used symbols can be imported directly from the datasette module: from datasette import Response from datasette import Forbidden from datasette import NotFound from datasette import hookimpl from datasette import actor_matches_allow | ["Internals for plugins"] | [] |
internals:internals-tracer-trace-child-tasks | internals | internals-tracer-trace-child-tasks | Tracing child tasks | If your code uses a mechanism such as asyncio.gather() to execute code in additional tasks you may find that some of the traces are missing from the display. You can use the trace_child_tasks() context manager to ensure these child tasks are correctly handled. from datasette import tracer with tracer.trace_child_tasks(): results = await asyncio.gather( # ... async tasks here ) This example uses the register_routes() plugin hook to add a page at /parallel-queries which executes two SQL queries in parallel using asyncio.gather() and returns their results. from datasette import hookimpl from datasette import tracer @hookimpl def register_routes(): async def parallel_queries(datasette): db = datasette.get_database() with tracer.trace_child_tasks(): one, two = await asyncio.gather( db.execute("select 1"), db.execute("select 2"), ) return Response.json( { "one": one.single_value(), "two": two.single_value(), } ) return [ (r"/parallel-queries$", parallel_queries), ] Adding ?_trace=1 will show that the trace covers both of those child tasks. | ["Internals for plugins", "datasette.tracer"] | [] |
internals:internals-utils-parse-metadata | internals | internals-utils-parse-metadata | parse_metadata(content) | This function accepts a string containing either JSON or YAML, expected to be of the format described in Metadata . It returns a nested Python dictionary representing the parsed data from that string. If the metadata cannot be parsed as either JSON or YAML the function will raise a utils.BadMetadataError exception. datasette.utils. parse_metadata content : str dict Detects if content is JSON or YAML and parses it appropriately. | ["Internals for plugins", "The datasette.utils module"] | [] |
introspection:id1 | introspection | id1 | Introspection | Datasette includes some pages and JSON API endpoints for introspecting the current instance. These can be used to understand some of the internals of Datasette and to see how a particular instance has been configured. Each of these pages can be viewed in your browser. Add .json to the URL to get back the contents as JSON. | [] | [] |
introspection:jsondataview-actor | introspection | jsondataview-actor | /-/actor | Shows the currently authenticated actor. Useful for debugging Datasette authentication plugins. { "actor": { "id": 1, "username": "some-user" } } | ["Introspection"] | [] |
introspection:messagesdebugview | introspection | messagesdebugview | /-/messages | The debug tool at /-/messages can be used to set flash messages to try out that feature. See .add_message(request, message, type=datasette.INFO) for details of this feature. | ["Introspection"] | [] |
json_api:column-filter-arguments | json_api | column-filter-arguments | Column filter arguments | You can filter the data returned by the table based on column values using a query string argument. ?column__exact=value or ?_column=value Returns rows where the specified column exactly matches the value. ?column__not=value Returns rows where the column does not match the value. ?column__contains=value Rows where the string column contains the specified value ( column like "%value%" in SQL). ?column__endswith=value Rows where the string column ends with the specified value ( column like "%value" in SQL). ?column__startswith=value Rows where the string column starts with the specified value ( column like "value%" in SQL). ?column__gt=value Rows which are greater than the specified value. ?column__gte=value Rows which are greater than or equal to the specified value. ?column__lt=value Rows which are less than the specified value. … | ["JSON API", "Table arguments"] | [] |
json_api:expand-foreign-keys | json_api | expand-foreign-keys | Expanding foreign key references | Datasette can detect foreign key relationships and resolve those references into labels. The HTML interface does this by default for every detected foreign key column - you can turn that off using ?_labels=off . You can request foreign keys be expanded in JSON using the _labels=on or _label=COLUMN special query string parameters. Here's what an expanded row looks like: [ { "rowid": 1, "TreeID": 141565, "qLegalStatus": { "value": 1, "label": "Permitted Site" }, "qSpecies": { "value": 1, "label": "Myoporum laetum :: Myoporum" }, "qAddress": "501X Baker St", "SiteOrder": 1 } ] The column in the foreign key table that is used for the label can be specified in metadata.json - see Specifying the label column for a table . | ["JSON API"] | [] |
json_api:id1 | json_api | id1 | JSON API | Datasette provides a JSON API for your SQLite databases. Anything you can do through the Datasette user interface can also be accessed as JSON via the API. To access the API for a page, either click on the .json link on that page or edit the URL and add a .json extension to it. If you started Datasette with the --cors option, each JSON endpoint will be served with the following additional HTTP headers: Access-Control-Allow-Origin: * Access-Control-Allow-Headers: Authorization Access-Control-Expose-Headers: Link This means JavaScript running on any domain will be able to make cross-origin requests to fetch the data. If you start Datasette without the --cors option only JavaScript running on the same domain as Datasette will be able to access the API. | [] | [] |
json_api:id2 | json_api | id2 | Table arguments | The Datasette table view takes a number of special query string arguments. | ["JSON API"] | [] |
json_api:json-api-discover-alternate | json_api | json-api-discover-alternate | Discovering the JSON for a page | Most of the HTML pages served by Datasette provide a mechanism for discovering their JSON equivalents using the HTML link mechanism. You can find this near the top of the source code of those pages, looking like this: <link rel="alternate" type="application/json+datasette" href="https://latest.datasette.io/fixtures/sortable.json"> The JSON URL is also made available in a Link HTTP header for the page: Link: https://latest.datasette.io/fixtures/sortable.json; rel="alternate"; type="application/json+datasette" | ["JSON API"] | [] |
json_api:json-api-shapes | json_api | json-api-shapes | Different shapes | The default JSON representation of data from a SQLite table or custom query looks like this: { "database": "sf-trees", "table": "qSpecies", "columns": [ "id", "value" ], "rows": [ [ 1, "Myoporum laetum :: Myoporum" ], [ 2, "Metrosideros excelsa :: New Zealand Xmas Tree" ], [ 3, "Pinus radiata :: Monterey Pine" ] ], "truncated": false, "next": "100", "next_url": "http://127.0.0.1:8001/sf-trees-02c8ef1/qSpecies.json?_next=100", "query_ms": 1.9571781158447266 } The columns key lists the columns that are being returned, and the rows key then returns a list of lists, each one representing a row. The order of the values in each row corresponds to the columns. The _shape parameter can be used to access alternative formats for the rows key which may be more convenient for your application. There are three options: ?_shape=arrays - "rows" is the default option, shown above ?_shape=objects - "rows" is a list of JSON key/value objects ?_shape=array - an JSON array of objects ?_shape=array&_nl=on - a newline-separated list of JSON objects ?_shape=arrayfirst - a flat JSON array containing just the first value from each row ?_shape=object - a JSON object keyed using the primary keys of the rows _shape=objects looks like this: { "database": "sf-trees", ... "rows": [ { "id": 1, … | ["JSON API"] | [] |
metadata:id1 | metadata | id1 | Metadata | Data loves metadata. Any time you run Datasette you can optionally include a JSON file with metadata about your databases and tables. Datasette will then display that information in the web UI. Run Datasette like this: datasette database1.db database2.db --metadata metadata.json Your metadata.json file can look something like this: { "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/" } You can optionally use YAML instead of JSON, see Using YAML for metadata . 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. | [] | [] |
metadata:label-columns | metadata | label-columns | 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 used for the link label with the label_column property: { "databases": { "database1": { "tables": { "example_table": { "label_column": "title" } } } } } | ["Metadata"] | [] |
metadata:metadata-default-sort | metadata | metadata-default-sort | Setting a default sort order | 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: { "databases": { "mydatabase": { "tables": { "example_table": { "sort": "created" } } } } } Or use "sort_desc" to sort in descending order: { "databases": { "mydatabase": { "tables": { "example_table": { "sort_desc": "created" } } } } } | ["Metadata"] | [] |
metadata:metadata-hiding-tables | metadata | metadata-hiding-tables | Hiding tables | You can hide tables from the database listing view (in the same way that FTS and SpatiaLite tables are automatically hidden) using "hidden": true : { "databases": { "database1": { "tables": { "example_table": { "hidden": true } } } } } | ["Metadata"] | [] |
metadata:metadata-page-size | metadata | metadata-page-size | Setting a custom page size | 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 : { "databases": { "mydatabase": { "tables": { "example_table": { "size": 10 } } } } } This size can still be over-ridden by passing e.g. ?_size=50 in the query string. | ["Metadata"] | [] |
metadata:metadata-sortable-columns | metadata | metadata-sortable-columns | 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 sortable_columns key: { "databases": { "database1": { "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: { "databases": { "my_database": { "tables": { "name_of_view": { "sortable_columns": [ "clicks", "impressions" ] } } } } } | ["Metadata"] | [] |
metadata:metadata-source-license-about | metadata | metadata-source-license-about | Source, license and about | The three visible metadata fields you can apply to everything, specific databases or specific tables are source, license and about. All three are optional. 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. | ["Metadata"] | [] |
metadata:metadata-yaml | metadata | metadata-yaml | Using YAML for metadata | Datasette accepts YAML as an alternative to JSON for your metadata configuration file. YAML is particularly useful for including multiline HTML and SQL strings. Here's an example of a metadata.yml file, re-using an example from Canned queries . title: Demonstrating Metadata from YAML description_html: |- <p>This description includes a long HTML string</p> <ul> <li>YAML is better for embedding HTML strings than JSON!</li> </ul> license: ODbL license_url: https://opendatacommons.org/licenses/odbl/ databases: fixtures: tables: no_primary_key: hidden: true queries: neighborhood_search: sql: |- select neighborhood, facet_cities.name, state from facetable join facet_cities on facetable.city_id = facet_cities.id where neighborhood like '%' || :text || '%' order by neighborhood; title: Search neighborhoods description_html: |- <p>This demonstrates <em>basic</em> LIKE search The metadata.yml file is passed to Datasette using the same --metadata option: datasette fixtures.db --metadata metadata.yml | ["Metadata"] | [] |
metadata:per-database-and-per-table-metadata | metadata | per-database-and-per-table-metadata | Per-database and per-table metadata | Metadata at the top level of the JSON will be shown on the index page and in the footer on every page of the site. The license and source is expected to apply to all of your data. You can also provide metadata at the per-database or per-table level, like this: { "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/" } } } } } Each of the top-level metadata fields can be used at the database and table level. | ["Metadata"] | [] |
pages:pages | pages | pages | Pages and API endpoints | The Datasette web application offers a number of different pages that can be accessed to explore the data in question, each of which is accompanied by an equivalent JSON API. | [] | [] |
performance:performance | performance | performance | Performance and caching | Datasette runs on top of SQLite, and SQLite has excellent performance. For small databases almost any query should return in just a few milliseconds, and larger databases (100s of MBs or even GBs of data) should perform extremely well provided your queries make sensible use of database indexes. That said, there are a number of tricks you can use to improve Datasette's performance. | [] | [] |
performance:performance-immutable-mode | performance | performance-immutable-mode | Immutable mode | If you can be certain that a SQLite database file will not be changed by another process you can tell Datasette to open that file in immutable mode . Doing so will disable all locking and change detection, which can result in improved query performance. This also enables further optimizations relating to HTTP caching, described below. To open a file in immutable mode pass it to the datasette command using the -i option: datasette -i data.db When you open a file in immutable mode like this Datasette will also calculate and cache the row counts for each table in that database when it first starts up, further improving performance. | ["Performance and caching"] | [] |
performance:performance-inspect | performance | performance-inspect | Using "datasette inspect" | Counting the rows in a table can be a very expensive operation on larger databases. In immutable mode Datasette performs this count only once and caches the results, but this can still cause server startup time to increase by several seconds or more. If you know that a database is never going to change you can precalculate the table row counts once and store then in a JSON file, then use that file when you later start the server. To create a JSON file containing the calculated row counts for a database, use the following: datasette inspect data.db --inspect-file=counts.json Then later you can start Datasette against the counts.json file and use it to skip the row counting step and speed up server startup: datasette -i data.db --inspect-file=counts.json You need to use the -i immutable mode against the database file here or the counts from the JSON file will be ignored. You will rarely need to use this optimization in every-day use, but several of the datasette publish commands described in Publishing data use this optimization for better performance when deploying a database file to a hosting provider. | ["Performance and caching"] | [] |
plugin_hooks:plugin-hook-forbidden | plugin_hooks | plugin-hook-forbidden | forbidden(datasette, request, message) | datasette - Datasette class You can use this to access plugin configuration options via datasette.plugin_config(your_plugin_name) , or to render templates or execute SQL queries. request - Request object The current HTTP request. message - string A message hinting at why the request was forbidden. Plugins can use this to customize how Datasette responds when a 403 Forbidden error occurs - usually because a page failed a permission check, see Permissions . If a plugin hook wishes to react to the error, it should return a Response object . This example returns a redirect to a /-/login page: from datasette import hookimpl from urllib.parse import urlencode @hookimpl def forbidden(request, message): return Response.redirect( "/-/login?=" + urlencode({"message": message}) ) The function can alternatively return an awaitable function if it needs to make any asynchronous method calls. This example renders a template: from datasette import hookimpl, Response @hookimpl def forbidden(datasette): async def inner(): return Response.html( await datasette.render_template( "render_message.html", request=request ) ) return inner | ["Plugin hooks"] | [] |
plugin_hooks:plugin-hook-register-magic-parameters | plugin_hooks | plugin-hook-register-magic-parameters | register_magic_parameters(datasette) | datasette - Datasette class You can use this to access plugin configuration options via datasette.plugin_config(your_plugin_name) . Magic parameters can be used to add automatic parameters to canned queries . This plugin hook allows additional magic parameters to be defined by plugins. Magic parameters all take this format: _prefix_rest_of_parameter . The prefix indicates which magic parameter function should be called - the rest of the parameter is passed as an argument to that function. To register a new function, return it as a tuple of (string prefix, function) from this hook. The function you register should take two arguments: key and request , where key is the rest_of_parameter portion of the parameter and request is the current Request object . This example registers two new magic parameters: :_request_http_version returning the HTTP version of the current request, and :_uuid_new which returns a new UUID: from uuid import uuid4 def uuid(key, request): if key == "new": return str(uuid4()) else: raise KeyError def request(key, request): if key == "http_version": return request.scope["http_version"] else: raise KeyError @hookimpl def register_magic_parameters(datasette): return [ ("request", request), ("uuid", uuid), ] | ["Plugin hooks"] | [] |
plugins:deploying-plugins-using-datasette-publish | plugins | deploying-plugins-using-datasette-publish | Deploying plugins using datasette publish | The datasette publish and datasette package commands both take an optional --install argument. You can use this one or more times to tell Datasette to pip install specific plugins as part of the process: datasette publish cloudrun mydb.db --install=datasette-vega You can use the name of a package on PyPI or any of the other valid arguments to pip install such as a URL to a .zip file: datasette publish cloudrun mydb.db \ --install=https://url-to-my-package.zip | ["Plugins", "Installing plugins"] | [] |
plugins:one-off-plugins-using-plugins-dir | plugins | one-off-plugins-using-plugins-dir | One-off plugins using --plugins-dir | You can also define one-off per-project plugins by saving them as plugin_name.py functions in a plugins/ folder and then passing that folder to datasette using the --plugins-dir option: datasette mydb.db --plugins-dir=plugins/ | ["Plugins", "Installing plugins"] | [] |
plugins:plugins-configuration | plugins | plugins-configuration | Plugin configuration | Plugins can have their own configuration, embedded in a Metadata file. Configuration options for plugins live within a "plugins" key in that file, which can be included at the root, database or table level. Here is an example of some plugin configuration for a specific table: { "databases": { "sf-trees": { "tables": { "Street_Tree_List": { "plugins": { "datasette-cluster-map": { "latitude_column": "lat", "longitude_column": "lng" } } } } } } } This tells the datasette-cluster-map column which latitude and longitude columns should be used for a table called Street_Tree_List inside a database file called sf-trees.db . | ["Plugins"] | [] |
plugins:plugins-configuration-secret | plugins | plugins-configuration-secret | Secret configuration values | Any values embedded in metadata.json will be visible to anyone who views the /-/metadata page of your Datasette instance. Some plugins may need configuration that should stay secret - API keys for example. There are two ways in which you can store secret configuration values. As environment variables . If your secret lives in an environment variable that is available to the Datasette process, you can indicate that the configuration value should be read from that environment variable like so: { "plugins": { "datasette-auth-github": { "client_secret": { "$env": "GITHUB_CLIENT_SECRET" } } } } As values in separate files . Your secrets can also live in files on disk. To specify a secret should be read from a file, provide the full file path like this: { "plugins": { "datasette-auth-github": { "client_secret": { "$file": "/secrets/client-secret" } } } } If you are publishing your data using the datasette publish family of commands, you can use the --plugin-secret option to set these secrets at publish time. For example, using Heroku you might run the following command: $ datasette publish heroku my_database.db \ --name my-heroku-app-demo \ --install=datasette-auth-github \ --plugin-secret datasette-auth-github client_id your_client_id \ --plugin-secret datasette-auth-github client_secret your_client_secret This will set the necessary environment variables and add the following to the deployed metadata.json : { "plugins": { "datasette-auth-github": { "client_id": { "$env": "DATASETTE_AUTH_GITHUB_CLIENT_ID" }, "client_secret": { "$env": "DATASETTE_AUTH_GITHUB_CLIENT_SECRET" } } } } | ["Plugins", "Plugin configuration"] | [] |
plugins:plugins-installing | plugins | plugins-installing | Installing plugins | If a plugin has been packaged for distribution using setuptools you can use the plugin by installing it alongside Datasette in the same virtual environment or Docker container. You can install plugins using the datasette install command: datasette install datasette-vega You can uninstall plugins with datasette uninstall : datasette uninstall datasette-vega You can upgrade plugins with datasette install --upgrade or datasette install -U : datasette install -U datasette-vega This command can also be used to upgrade Datasette itself to the latest released version: datasette install -U datasette These commands are thin wrappers around pip install and pip uninstall , which ensure they run pip in the same virtual environment as Datasette itself. | ["Plugins"] | [] |
publish:publishing | publish | publishing | Publishing data | Datasette includes tools for publishing and deploying your data to the internet. The datasette publish command will deploy a new Datasette instance containing your databases directly to a Heroku or Google Cloud hosting account. You can also use datasette package to create a Docker image that bundles your databases together with the datasette application that is used to serve them. | [] | [] |
settings:config-dir | settings | config-dir | Configuration directory mode | Normally you configure Datasette using command-line options. For a Datasette instance with custom templates, custom plugins, a static directory and several databases this can get quite verbose: $ datasette one.db two.db \ --metadata=metadata.json \ --template-dir=templates/ \ --plugins-dir=plugins \ --static css:css As an alternative to this, you can run Datasette in configuration directory mode. Create a directory with the following structure: # In a directory called my-app: my-app/one.db my-app/two.db my-app/metadata.json my-app/templates/index.html my-app/plugins/my_plugin.py my-app/static/my.css Now start Datasette by providing the path to that directory: $ datasette my-app/ Datasette will detect the files in that directory and automatically configure itself using them. It will serve all *.db files that it finds, will load metadata.json if it exists, and will load the templates , plugins and static folders if they are present. The files that can be included in this directory are as follows. All are optional. *.db (or *.sqlite3 or *.sqlite ) - SQLite database files that will be served by Datasette metadata.json - Metadata for those databases - metadata.yaml or metadata.yml can be used as well inspect-data.json - the result of running datasette inspect *.db --inspect-file=inspect-data.json from the configuration directory - any database files listed here will be treated as immutable, so they should not be changed while Datasette is running settings.json - settings that would normally be passed using --setting - here they should be stored as a JSON object of key/value pairs templates/ - a di… | ["Settings"] | [] |
settings:id1 | settings | id1 | Settings | [] | [] | |
settings:id2 | settings | id2 | Settings | The following options can be set using --setting name value , or by storing them in the settings.json file for use with Configuration directory mode . | ["Settings"] | [] |
settings:setting-allow-csv-stream | settings | setting-allow-csv-stream | allow_csv_stream | Enables the CSV export feature where an entire table (potentially hundreds of thousands of rows) can be exported as a single CSV file. This is turned on by default - you can turn it off like this: datasette mydatabase.db --setting allow_csv_stream off | ["Settings", "Settings"] | [] |
settings:setting-allow-download | settings | setting-allow-download | allow_download | Should users be able to download the original SQLite database using a link on the database index page? This is turned on by default. However, databases can only be downloaded if they are served in immutable mode and not in-memory. If downloading is unavailable for either of these reasons, the download link is hidden even if allow_download is on. To disable database downloads, use the following: datasette mydatabase.db --setting allow_download off | ["Settings", "Settings"] | [] |
settings:setting-allow-facet | settings | setting-allow-facet | allow_facet | Allow users to specify columns they would like to facet on using the ?_facet=COLNAME URL parameter to the table view. This is enabled by default. If disabled, facets will still be displayed if they have been specifically enabled in metadata.json configuration for the table. Here's how to disable this feature: datasette mydatabase.db --setting allow_facet off | ["Settings", "Settings"] | [] |
settings:setting-base-url | settings | setting-base-url | base_url | If you are running Datasette behind a proxy, it may be useful to change the root path used for the Datasette instance. For example, if you are sending traffic from https://www.example.com/tools/datasette/ through to a proxied Datasette instance you may wish Datasette to use /tools/datasette/ as its root URL. You can do that like so: datasette mydatabase.db --setting base_url /tools/datasette/ | ["Settings", "Settings"] | [] |
settings:setting-default-allow-sql | settings | setting-default-allow-sql | default_allow_sql | Should users be able to execute arbitrary SQL queries by default? Setting this to off causes permission checks for execute-sql to fail by default. datasette mydatabase.db --setting default_allow_sql off There are two ways to achieve this: the other is to add "allow_sql": false to your metadata.json file, as described in Controlling the ability to execute arbitrary SQL . This setting offers a more convenient way to do this. | ["Settings", "Settings"] | [] |
settings:setting-default-cache-ttl | settings | setting-default-cache-ttl | default_cache_ttl | Default HTTP caching max-age header in seconds, used for Cache-Control: max-age=X . Can be over-ridden on a per-request basis using the ?_ttl= query string parameter. Set this to 0 to disable HTTP caching entirely. Defaults to 5 seconds. datasette mydatabase.db --setting default_cache_ttl 60 | ["Settings", "Settings"] | [] |
settings:setting-default-facet-size | settings | setting-default-facet-size | default_facet_size | The default number of unique rows returned by Facets is 30. You can customize it like this: datasette mydatabase.db --setting default_facet_size 50 | ["Settings", "Settings"] | [] |
settings:setting-default-page-size | settings | setting-default-page-size | default_page_size | The default number of rows returned by the table page. You can over-ride this on a per-page basis using the ?_size=80 query string parameter, provided you do not specify a value higher than the max_returned_rows setting. You can set this default using --setting like so: datasette mydatabase.db --setting default_page_size 50 | ["Settings", "Settings"] | [] |
settings:setting-facet-suggest-time-limit-ms | settings | setting-facet-suggest-time-limit-ms | facet_suggest_time_limit_ms | When Datasette calculates suggested facets it needs to run a SQL query for every column in your table. The default for this time limit is 50ms to account for the fact that it needs to run once for every column. If the time limit is exceeded the column will not be suggested as a facet. You can increase this time limit like so: datasette mydatabase.db --setting facet_suggest_time_limit_ms 500 | ["Settings", "Settings"] | [] |
settings:setting-facet-time-limit-ms | settings | setting-facet-time-limit-ms | facet_time_limit_ms | This is the time limit Datasette allows for calculating a facet, which defaults to 200ms: datasette mydatabase.db --setting facet_time_limit_ms 1000 | ["Settings", "Settings"] | [] |
settings:setting-force-https-urls | settings | setting-force-https-urls | force_https_urls | Forces self-referential URLs in the JSON output to always use the https:// protocol. This is useful for cases where the application itself is hosted using HTTP but is served to the outside world via a proxy that enables HTTPS. datasette mydatabase.db --setting force_https_urls 1 | ["Settings", "Settings"] | [] |
settings:setting-max-csv-mb | settings | setting-max-csv-mb | max_csv_mb | The maximum size of CSV that can be exported, in megabytes. Defaults to 100MB. You can disable the limit entirely by settings this to 0: datasette mydatabase.db --setting max_csv_mb 0 | ["Settings", "Settings"] | [] |
settings:setting-max-returned-rows | settings | setting-max-returned-rows | max_returned_rows | Datasette returns a maximum of 1,000 rows of data at a time. If you execute a query that returns more than 1,000 rows, Datasette will return the first 1,000 and include a warning that the result set has been truncated. You can use OFFSET/LIMIT or other methods in your SQL to implement pagination if you need to return more than 1,000 rows. You can increase or decrease this limit like so: datasette mydatabase.db --setting max_returned_rows 2000 | ["Settings", "Settings"] | [] |
settings:setting-publish-secrets | settings | setting-publish-secrets | Using secrets with datasette publish | The datasette publish and datasette package commands both generate a secret for you automatically when Datasette is deployed. This means that every time you deploy a new version of a Datasette project, a new secret will be generated. This will cause signed cookies to become invalid on every fresh deploy. You can fix this by creating a secret that will be used for multiple deploys and passing it using the --secret option: datasette publish cloudrun mydb.db --service=my-service --secret=cdb19e94283a20f9d42cca5 | ["Settings"] | [] |
Advanced export
JSON shape: default, array, newline-delimited, object
CREATE TABLE [sections] ( [id] TEXT PRIMARY KEY, [page] TEXT, [ref] TEXT, [title] TEXT, [content] TEXT, [breadcrumbs] TEXT, [references] TEXT );