Datasette Lite
Datasette running in your browser using WebAssembly and Pyodide
Live tool: https://lite.datasette.io/
More about this project:
- Datasette Lite: a server-side Python web application running in a browser
- Joining CSV files in your browser using Datasette Lite
- Plugin support for Datasette Lite
How this works
Datasette Lite runs the full server-side Datasette Python web application directly in your browser, using the Pyodide build of Python compiled to WebAssembly.
When you launch the demo, your browser will download and start executing a full Python interpreter, install the datasette package (and its dependencies), download one or more SQLite database files and start the application running in a browser window (actually a Web Worker attached to that window).
Datasette's pages are displayed in a full-window pointing at URLs under /app/. Those URLs don't exist on the server: a service worker (sw.js) intercepts every request for them - link clicks, GET and POST form submissions, fetch() calls made by JavaScript, static assets - and the page relays each one to Datasette's ASGI application running in Pyodide. This means Datasette's own JavaScript and the JavaScript and CSS provided by plugins work, as do POST forms, CSRF protection, sign-in cookies, file uploads and downloads.
The page you are on mirrors the iframe's location into its #fragment, so URLs like https://lite.datasette.io/?url=...#/fixtures/facetable?_facet=state can be bookmarked and shared, and the back button works. Links to other sites open in a new window, with noopener so the linked site gets no reference back to the Datasette Lite page. Each tab gets its own random so multiple Datasette Lite tabs with different data can be open at once.
A response that Datasette sends over time - server-sent events, a streaming CSV export - reaches the page as it is produced rather than once it is complete. If the page stops reading one, by navigating away or aborting the fetch(), Datasette is told the client has disconnected.
Cookies are kept in a cookie jar inside the Python ASGI bridge (service workers cannot read or set cookies), so they last until the tab is closed or reloaded - as does all of the data.
If the tab that owns a session goes away, requests for that session fail with an error rather than hanging: a 503 if the tab has already been closed or reloaded, a 504 if it stops answering part way through a request (after four minutes, to leave room for slow SQL queries) and a 500 if the Pyodide worker itself fails. A tab that was opened before Datasette Lite was updated to an incompatible release gets a 409 asking for a reload.
A browser with service worker support is required. Some private browsing modes (Firefox private windows for example) disable service workers. The Web Worker is a module worker, which Pyodide needs: Chrome 80, Safari 15 and Firefox 114 or later.
Python is version 3.14, from Pyodide 314.0.8.
Load a different Datasette version
Datasette Lite uses the most recent Datasette release from PyPI, including alpha and beta preview releases. At the moment that means the Datasette 1.0 alphas.
To use the most recent stable release instead, add ?ref=stable:
- https://lite.datasette.io/?ref=stable
?ref=:
- https://lite.datasette.io/?ref=0.64.2
- https://lite.datasette.io/?ref=1.0a11
?ref=pre used to be how you asked for a preview release. It still works, and is now the same as leaving ?ref= out.
Unreleased code: a branch, tag or commit
Any other ?ref= is treated as a branch, tag or commit in the simonw/datasette repository on GitHub, and Datasette is run from that source code instead of from a PyPI release:
- https://lite.datasette.io/?ref=main - the latest development code
- https://lite.datasette.io/?ref=dfeec630f86b4cbd06d24a090e654bd130344b71 - an exact commit
A ?ref= that looks like a version number, such as 1.0a11, is always installed from PyPI, even though the repository has a tag of the same name.
The loading screen shows the version number and commit that was loaded. ?install= plugins work as usual, and get their most recent preview releases.
This needs a commit that has a pyproject.toml, so it does not work for old releases - use their version number instead.
GitHub does not serve its source zip files with CORS headers, so Datasette Lite downloads them through a small Cloudflare Worker that adds those headers. The proxy only serves repositories owned by simonw, and only to pages on lite.datasette.io and localhost. If you host your own copy of Datasette Lite on another domain you will need your own proxy: change GITHUB_ZIP_PROXY at the top of webworker.js.
Load a different SQLite version
Datasette Lite uses the SQLite that is built into Pyodide, which is version 3.39.0. Add ?sqlite= with a version number to use a different version of SQLite:
- https://lite.datasette.io/?sqlite=3.54.1
The chosen version is used for everything: by Datasette, by plugins that import sqlite3, and for loading ?csv=, ?json=, ?parquet= and ?sql= files. /-/versions and select sqlite_version() both report it. It can be combined with any of the other options, for example to run a specific Datasette release on a specific SQLite:
- https://lite.datasette.io/?ref=stable&sqlite=3.54.1
Loading CSV data
You can load data from a CSV file hosted online (provided it allows access-control-allow-origin: *) by passing that URL as a ?csv= parameter - or by clicking the "Load CSV by URL" button and pasting in a URL.
This example loads a CSV of college fight songs from the fivethirtyeight/data GitHub repository:
- https://lite.datasette.io/?csv=https%3A%2F%2Fraw.githubusercontent.com%2Ffivethirtyeight%2Fdata%2Fmaster%2Ffight-songs%2Ffight-songs.csv
?csv= multiple times to load more than one CSV file. You can then execute SQL joins to combine that data.
This example loads the latest Covid-19 per-county data from the NY Times, the 2019 county populations data from the US Census, joins them on FIPS code and runs a query that calculates cases per million across that data:
Loading JSON data
If you have data in a JSON file that looks something like this you can load it directly into Datasette Lite using the ?json=URL parameter:
[
{
"id": 1,
"name": "Item 1"
},
{
"id": 2,
"name": "Item 2"
}
]
This also works with JSON documents where one of the keys is a list of objects, such as this one:
{
"rows": [
{
"id": 1,
"name": "Item 1"
},
{
"id": 2,
"name": "Item 2"
}
]
}
In this case it will search for the key that contains the most objects.
If a document is a JSON object where every value is a JSON object, like this:
{
"anchor-positioning": {
"spec": "https://drafts.csswg.org/css-anchor-position-1/#anchoring"
},
"array-at": {
"spec": "https://tc39.es/ecma262/multipage/indexed-collections.html#sec-array.prototype.at"
},
"array-flat": {
"caniuse": "array-flat",
"spec": "https://tc39.es/ecma262/multipage/indexed-collections.html#sec-array.prototype.flat"
}
}
Each of those objects will be loaded as a separate row, with a _key primary key column containing the object key.
This example loads scraped data from this repo.
Newline-delimited JSON works too - for example a file that looks like this:
{"id": 1, "name": "Item 1"}
{"id": 2, "name": "Item 2"}
Loading SQLite databases
You can use this tool to open any SQLite database file that is hosted online and served with a access-control-allow-origin: * CORS header. Files served by GitHub Pages automatically include this header, as do database files that have been published online using datasette publish.
Copy the URL to the .db file and either paste it into the "Load SQLite DB by URL" prompt, or construct a URL like the following:
https://lite.datasette.io/?url=https://latest.datasette.io/fixtures.db
Pass url more than once to load multiple databases. Each database is named after its file, without the extension - chinook.sqlite becomes chinook - and repeated names are suffixed with _2, _3, and so on:
https://lite.datasette.io/?url=https://example.com/data.db&url=https://other.example.com/data.db
Some examples to try out:
- Global Power Plants - 33,000 power plants around the world
- United States members of congress - the example database from the Learn SQL with Datasette tutorial
Loading Parquet
To load a Parquet file, pass a URL to ?parquet=.
For example this file:
https://github.com/Teradata/kylo/blob/master/samples/sample-data/parquet/userdata1.parquet
Can be loaded like this:
https://lite.datasette.io/?parquet=https://github.com/Teradata/kylo/blob/master/samples/sample-data/parquet/userdata1.parquet
Initializing with SQL
You can also initialize the data.db database by passing the URL to a SQL file. The easiest way to do this is to create a GitHub Gist.
This example SQL file creates a table and populates it with three records. It's hosted in this Gist.
https://gist.githubusercontent.com/simonw/ac4e19920b4b360752ac0f3ce85ba238/raw/90d31cf93bf1d97bb496de78559798f849b17e85/demo.sql
You can paste this URL into the "Load SQL by URL" prompt, or you can pass it as the ?sql= parameter like this.
SQL will be executed before any CSV imports, so you can use initial SQL to create a table and then use ?csv= to import data into it.
Starting with just an in-memory database
To skip loading the default databases and just provide /_memory - useful for demonstrating plugins - pass ?memory=1, for example:
https://lite.datasette.io/?memory=1
Loading metadata
Datasette supports metadata, as a metadata.json or metadata.yml file.
You can load a metadata file in either of these formats by passing a URL to the ?metadata= query string option.
Loading configuration
Datasette 1.0 alphas take configuration - plugin settings, permissions, settings: and more - from a datasette.yaml or datasette.json file. Pass a URL to one using ?config=:
https://lite.datasette.io/?config=https://example.com/datasette.yml
This needs Datasette 1.0a8 or later, so it does not work with ?ref=stable while the stable release is 0.65.
You can also set individual configuration values in the URL using ?s.key=value, the equivalent of the datasette -s key value command-line option. This can be used multiple times. A setting can be given by its name:
https://lite.datasette.io/?s.template_debug=1&s.default_page_size=10
Other keys are a path into the configuration, with values that are valid JSON treated as JSON:
https://lite.datasette.io/?s.settings.sql_time_limit_ms=3500&s.plugins.datasette-cluster-map.latitude_column=lat
These are applied on top of any ?config= file, and also need Datasette 1.0a8 or later.
Datasette Lite always sets the base_url and num_sql_threads settings itself.
Signing in as root
Add ?root=1 to sign in as the root actor, the equivalent of running datasette --root. This is needed by plugins that write to the database, and for the Datasette 1.0 alpha write API and table editing features:
https://lite.datasette.io/?root=1
Use "Log out" from the menu to return to being an anonymous user.
Special handling of GitHub URLs
A tricky thing about using Datasette Lite is that the files you load via URL need to be hosted somewhere that serves open CORS headers.
Both regular GitHub and GitHub Gists do this by default. This makes them excellent options to host data files that you want to load into Datasette Lite.
You can paste in the "raw" URL to a file, but Datasette Lite also has a shortcut: if you paste in the URL to a page on GitHub or a Gist it will automatically convert it to the "raw" URL for you.
Try the following to see this in action:
- https://lite.datasette.io/?json=https://gist.github.com/simonw/7eacc70cd8b2868be0a18796cec078b9 (this Gist)
- https://lite.datasette.io/?csv=https://github.com/nytimes/covid-19-data/blob/master/us-counties-recent.csv (this file)
Installing plugins
Datasette has a number of plugins that enable new features.
You can install plugins into Datasette Lite by adding one or more ?install=name-of-plugin parameters to the URL.
Plugins that load their own JavaScript and CSS now work, as do plugins that accept POST requests - for example datasette-cluster-map, datasette-vega, datasette-write, datasette-edit-schema, datasette-upload-csvs and datasette-auth-passwords. Plugins need to be pure Python (or available as Pyodide packages) and must not require threads or subprocesses.
Because Datasette Lite runs a preview release of Datasette by default, ?install= also picks the most recent preview release of each plugin - that is where plugin authors publish support for the Datasette alphas. Some plugins do not work with the latest alpha yet. If one misbehaves, add ?ref=stable to run it against the stable Datasette release, which installs the plugin's stable release too.
Some plugins hardcode root-relative URLs such as /-/password-tool rather than respecting Datasette's base_url setting. Datasette Lite rewrites those in HTML attributes and routes requests for them back to the right tab, including links that a plugin's JavaScript adds to the page. One case is handled less smoothly in Firefox and Safari: when JavaScript navigates to such a URL (location.href = "/-/...") from a table, row or query page, those browsers give the service worker no way to tell which tab the navigation came from. The request reaches the web server first, and Datasette Lite then loads the right page itself - so it works, but a form submitted that way loses its POST data.
- datasette-cluster-map - Datasette plugin that shows a map for any data with latitude/longitude columns - demo
- datasette-write - Datasette plugin providing a UI for executing SQL writes against the database (needs
?root=1) - demo - datasette-edit-schema - Datasette plugin for modifying table schemas (needs
?root=1) - demo - datasette-upload-csvs - Datasette plugin for uploading CSV files and converting them to database tables (needs
?root=1) - demo
- datasette-packages - Show a list of currently installed Python packages - demo
- datasette-dateutil - dateutil functions for Datasette - demo
- datasette-schema-versions - Datasette plugin that shows the schema version of every attached database - demo
- datasette-debug-asgi - Datasette plugin for dumping out the ASGI scope. - demo
- datasette-query-links - Turn SELECT queries returned by a query into links to execute them - demo'%0D%0Aunion%0D%0Aselect%0D%0A++'select+this+is+invalid+SQL+so+will+not+be+linked')
- datasette-json-html - Datasette plugin for rendering HTML based on JSON values - demo
- datasette-haversine - Datasette plugin that adds a custom SQL function for haversine distances - demo
- datasette-jellyfish - Datasette plugin that adds custom SQL functions for fuzzy string matching, built on top of the Jellyfish Python library - demo
- datasette-pretty-json - Datasette plugin that pretty-prints any column values that are valid JSON objects or arrays. - demo
- datasette-yaml - Export Datasette records as YAML - demo
- datasette-copyable - Datasette plugin for outputting tables in formats suitable for copy and paste - demo
- datasette-mp3-audio - Turn
.mp3URLs into an audio player in the Datasette interface - demo - datasette-multiline-links - Make multiple newline separated URLs clickable in Datasette - demo
- datasette-copyable - adds an interface for copying out data in CSV, TSV, LaTeX, GitHub Markdown tables and many other formats - demo
- datasette-statistics - SQL functions for statistical calculations - demo
- datasette-simple-html - simple SQL functions for stripping tags and escaping or unescaping HTML strings - demo
Analytics
By default, hits to https://lite.datasette.io/ are logged using Plausible.
Plausible is a privacy-focused, cookie-free, GDPR-compliant analytics system.
Each navigation within Datasette Lite is logged as a separate event to Plausible, capturing the fragment hash and the URL to the currently loaded file.
The site is hosted on GitHub Pages, which does not offer any analytics that are visible to the site owner. GitHub Pages can only log visits to the https://lite.datasette.io/ root page - it will not have visibility into any subsequent # fragment navigation.
To opt out of analytics, you can add ?analytics=off or &analytics=off to the URL. This will prevent any analytics being sent to Plausible.
Running the tests
Run the tests by running pytest after first installing the dev-requirements.txt dependencies.
Easy way is to run ./test.sh. Or do this:
uv run --python 3.12 \
--with-requirements dev-requirements.txt \
python -m playwright install
Then run pytest like this:
uv run --python 3.12 \
--with-requirements dev-requirements.txt \
python -m pytest
tests/test_bridge.py- fast pure-Python tests of the ASGI bridge, cookie jar andbase_urlworkarounds, extracted fromwebworker.js. Installdatasetteto also run them against a real Datasette.tests/test_datasette_lite.py- the original browser tests, loading the default databases.tests/test_url_patterns.py- every supported?option and#URL pattern, using the small files intests/fixtures/.tests/test_plugins_and_post.py- JavaScript (core and plugins), POST forms, CSRF, sign-in, uploads, downloads, the 1.0 alpha write API, multiple tabs, other pages open on the same origin, worker failures, a cross-origin page in the iframe and service worker restarts.tests/test_streaming.py- responses that arrive over time, using the plugin intests/plugins/.
network; skip them with -m "not network". To use an existing Chromium binary set CHROMIUM_EXECUTABLE=/path/to/chrome. If the browser needs an HTTPS proxy set DATASETTE_LITE_TEST_PROXY (or HTTPS_PROXY).