Datasette: Browse and Publish SQLite Data

To make SQLite data available to a script, an API can provide a way to request it. Datasette is a Python application that provides a web interface for browsing SQLite databases and a JSON API for their tables.

I’ll walk through starting Datasette with a database and requesting rows as JSON objects.

TL;DR: Browse a SQLite Database with Datasette

Run datasette films.db to serve the database locally, then open the table page or add the suffix .json?_shape=objects to its URL for object-shaped JSON.

• Datasette reads SQLite files and exposes tables in a web interface.
• The table URL also has a JSON form for scripts and applications.
• Install plugins into the same Python environment as Datasette.

What Is Datasette?

Datasette is a Python application for exploring and publishing data. Point it at a SQLite database and it provides pages for the database and its tables, along with a JSON API for those pages.

A SQLite database stores data in tables. Each table has columns and rows, much like a grid, but it is queried with SQL rather than edited as a spreadsheet. Datasette sits above that file: SQLite remains the storage layer, while Datasette supplies browsing, filtering and HTTP responses.

On a table page, facets summarize values in a column so you can narrow the visible rows. They let you choose a value and filter the table to matching rows. That supports exploratory data analysis before you write a query or build a chart.

During a prototype, SQLite holds the data while Datasette supplies a page and API that another tool can query. You can check whether the data answers a question before investing in a custom backend. Database preparation is a separate task.

That makes it useful for inspecting a database before building a custom interface. For more ways to work with SQLite, see the SQLite tools comparison and our guide to using sqlite3 on macOS.

How to Run Datasette on a SQLite File

Start with a SQLite database file that you can read and a Python environment for Datasette.

Step 1: Install Datasette in an isolated environment

A virtual environment keeps Datasette and its dependencies separate from other Python projects. Create one, activate it, and install the package:

python3 -m venv .venv
source .venv/bin/activate
python -m pip install datasette

To confirm the active environment has Datasette, run its version command:

datasette --version
# datasette, version 0.65.5

The version output confirms the shell resolves the copy installed in the active environment. Keep the virtual environment active for the next steps so its command and plugins stay together.

Step 2: Start Datasette with the database

Pass the SQLite filename to Datasette. The following command starts a local server using the default port:

datasette films.db

Datasette serves the database on port 8001 by default. Open http://127.0.0.1:8001/ in a browser and choose the database. The table page in this example is http://127.0.0.1:8001/films/films.

The verification run used port 8019 and bound the server to 127.0.0.1, which limits that test server to connections from the same machine. That URL differs from the default port only because the check used a separate local port.

Step 3: Request the table as JSON

Add .json to a table URL to request its data as JSON. The _shape=objects option returns each row as an object whose keys are the column names:

http://127.0.0.1:8001/films/films.json?_shape=objects

The tested response returned HTTP 200 and three rows. A shortened view of its data is:

{
  "database": "films",
  "table": "films",
  "rows": [
    {"id": 1, "title": "Arrival", "year": 2016},
    {"id": 2, "title": "Moonlight", "year": 2016},
    {"id": 3, "title": "The Farewell", "year": 2019}
  ],
  "truncated": false
}

The row objects use the table’s column names, which is convenient when a client needs to read a field by name. The full response also includes pagination and query details. Use its next_url field when a result spans multiple pages.

Step 4: Install one plugin

Plugins extend Datasette. Install them with the datasette install command from the same environment that runs the server. For example, install datasette-json-html like this:

datasette install datasette-json-html

Successfully installed datasette-json-html-1.0.1

Check that the plugin is visible to the installed Datasette command:

datasette plugins --all | python3 -c 'import json,sys; p=[x for x in json.load(sys.stdin) if x["name"]=="datasette-json-html"]; print(json.dumps(p,indent=2))'

[
  {
    "name": "datasette-json-html",
    "static": false,
    "templates": false,
    "version": "1.0.1",
    "hooks": ["prepare_connection", "render_cell"]
  }
]

The plugin list reports version 1.0.1 and its two hooks, confirming that it is installed in this environment. Restart Datasette after installing a plugin so the running process can load it. Check the plugin’s documentation for its behavior and compatibility limits.

Common Edge Cases with Datasette

A local test and a public deployment have different access boundaries. The local example serves on the loopback address, so it is available from the same machine. A deployment exposes data to the audience allowed by its hosting and permission settings.

Before putting a database online, inspect its contents and decide who should be able to read it. Datasette’s deployment guide describes hosting options and the associated configuration.

Cloud Run can scale to zero, but the official guide warns that search crawlers may request pages for each row in a large table and raise hosting costs. Decide whether crawlers should index the instance, and review provider billing before publishing a large database.

Situation	What to check
Local browser cannot reach the instance	Use the host and port printed by Datasette, usually localhost:8001.
Plugin does not appear	Run datasette install from the same environment, then restart the server.
Preparing a public deployment	Review database contents, access permissions, crawler behavior and provider costs.

These checks separate a local startup issue from a plugin environment mismatch or a deployment decision.

If plugin installation succeeds but the server fails during startup, check the traceback and confirm the plugin supports the Datasette version in the environment. An issue report about a plugin startup failure documents a version-specific problem, so test plugin changes locally before deploying them: Datasette issue 889.

Conclusion: Use Datasette to Share SQLite Data

Datasette gives a SQLite file a browser interface and a JSON endpoint without requiring a custom application first. Run it locally to inspect the tables and API response, then review access, plugin compatibility and hosting costs before making the data public.

For deployment details, use the official Datasette publishing guide. For queryable data, the JSON API reference describes response formats and pagination.

FAQ

The answers below cover the local SQLite workflow.