Datasette for Publishing CSV as a Searchable Site in Minutes

A CSV file is a poor way to publish data to people. It cannot be searched in a browser, it cannot be filtered without a spreadsheet and it does not say which columns are categories. Datasette is the shortest route I know from a CSV to something that does all three. It is a Python tool that serves a SQLite database as a website: every table gets a page with sortable columns, filters in the URL, facets for categorical columns, optional full-text search, a JSON and CSV API and, unless you turn it off, a box for read-only SQL. This article builds one from a 200,000-row orders file, shows the API calls with their output, flags the settings that matter before putting it on the public internet and covers when it is the wrong way to share data. It builds on using sqlite-utils to load CSV fast, and it is one of the tools in CSV tools worth knowing.

Build the database

Datasette serves SQLite, so the first step is a database. With type detection off, so identifiers stay text, and a primary key:

sqlite-utils insert orders200.db orders orders-200k.csv --csv --pk order_id --no-detect-types
sqlite-utils transform orders200.db orders --type amount float --type customer_id integer
sqlite-utils enable-fts orders200.db orders note status country --create-triggers

The transform line types the two columns we want to sort and compare numerically and leaves the rest as text. The enable-fts line builds a full-text index over three text columns, with triggers so it stays current. If you skip type handling, you get the default of version 4, which turns zip into an integer and drops its leading zeros, a bad outcome for a published dataset. The reasoning is in the loading article.

One more decision, and it bit me in the test. I left order_id as TEXT because I had set detection off, and the default listing of the table then sorted the key as text. The first API call returned the first two rows as 1 and 10:

$ datasette orders200.db --get '/orders200/orders.json?_size=2&_col=order_id&_col=status&_col=amount&_shape=array'
[{"order_id": "1", "status": "shipped", "amount": 103.1}, {"order_id": "10", "status": "pending", "amount": 125.88}]

Order 10 comes before order 2 because strings sort character by character, and the id is quoted in the JSON. For a table people will browse, type the key: --type order_id integer in the same transform. This is exactly the kind of thing worth seeing before publication, not after.

Serve it

datasette orders200.db

That starts a server on 127.0.0.1:8001. Open the address and you see the database, its tables and a row count. The table page has a search box if full-text search is enabled, column headers you can click to sort, a filter form that builds URL parameters and a list of suggested facets: columns with few distinct values that it offers to count. Because every filter is a URL, a view is shareable: send someone the link to "failed orders from the UK over 100".

The things you will adjust are options and metadata. --host 0.0.0.0 listens on all interfaces, which you want in a container and should not use on a laptop on public Wi-Fi. -i (immutable) tells Datasette the file will not change, which speeds it up. A metadata.yml file gives the site a title, a description, a source and license and per-table settings such as which columns to facet and sort by default:

title: Orders sample
source: Synthetic data for testing
databases:
  orders200:
    tables:
      orders:
        facets: [status, country]
        sort_desc: amount

Run datasette orders200.db -m metadata.yml and the status and country facets appear on the table page without a click.

Facets, filters and the API

Facets count the distinct values of a column within the current filter, and they are the quickest way to learn the shape of the data. The API shows the same thing as JSON. A facet on status, with the size set to zero to skip the rows:

shipped 40077
paid 40032
refunded 39988
pending 39977
failed 39926

Those five counts are identical to what qsv frequency -s status printed on the same file (qsv for fast CSV statistics), and that agreement is worth taking as a check: a published table that gives different counts from the source file means a row went missing in the load.

Filters are URL parameters with an operator suffix. amount__gt=650 is "greater than 650", and the response for the orders file was a single row:

$ datasette orders200.db --get '/orders200/orders.json?amount__gt=650&_shape=array&_col=order_id&_col=amount'
[{"order_id": "158192", "amount": 651.04}]

Order 158192, at 651.04, matches the largest amount qsv sort found earlier. Other operators include __contains, __startswith, __in, __isnull and __date. Full-text search is ?_search=leave+door, and on this data it matched 40,079 rows, since leave at door appears in about a fifth of the notes. Every table also has a CSV endpoint, /orders200/orders.csv, with the same filters, so a reader can download exactly the slice they are looking at, which is the best answer to "can I get this as a spreadsheet". (In my run, requesting specific columns repeated the key column in the CSV, a quirk worth checking in your version before you rely on column selection.)

The SQL box

Each database has a page where visitors can type SQL, and a JSON endpoint for it:

$ datasette orders200.db --get '/orders200.json?sql=select country, count(*) as n from orders group by country order by n desc limit 3&_shape=array'
[{"country": "IN", "n": 28805}, {"country": "FR", "n": 28783}, {"country": "BR", "n": 28638}]

The counts match Miller's count-distinct on the same file (Miller for records and pipelines), which is two tools agreeing again. Datasette opens the file read-only and caps each query's running time (one second by default; --setting sql_time_limit_ms changes it), so a visitor cannot modify the data and cannot run a query forever. Even so, a public SQL box is an invitation to read everything in the file. If any of it is private, do not publish it, or disable arbitrary SQL with --setting allow_sql off and expose only the table views.

Publishing

For the web, you have three kinds of option. Run it yourself: a small VM or a container, with datasette serve behind a reverse proxy, which is the most control. Use datasette publish, which packages the database into a container image and deploys it to a supported host with a plugin (datasette-publish-vercel, the built-in cloudrun and heroku commands and others); I did not run a publish, so check the documentation for current support. Or export a static result: Datasette is a live server, and if you only need static pages, a generated HTML table or a notebook is lighter.

Before any of them, check the data. Strip personal columns (stripping personal data from a CSV before sharing it), settle the license line in the metadata and decide whether you want the CSV download. If the data changes, rebuild the SQLite file in a job and deploy it again, since Datasette treats the file as the unit; for live data you point it at a database that your pipeline writes to.

When Datasette is the wrong thing to publish

Datasette is for browsing and querying a table. It is not a reporting tool: it makes no charts out of the box (plugins add them), and a result like "revenue by month as a line chart" wants a notebook or a BI tool. It is not a good fit for very large data, because SQLite on one machine has limits, though a few gigabytes is fine. And it is the wrong choice for anything private, regulated or editable. People who need to change rows need an application, and people who need access control need an authenticated product; Datasette has permissions and plugins for login, which are worth a careful read before you rely on them for secrets.

A static file on a web server, a CSV with a README, is still the right way to publish a small dataset to people who already have tools (converting CSV to Markdown tables for documentation covers the tiny case). Datasette starts to pay once the question is "can people explore this without installing anything". The documentation is at datasette.io, and the SQLite full-text search behind it is described in SQLite's FTS5 page.