Datasette for coding agents

When a coding agent needs to inspect data in a SQLite database, Datasette serves it through a web interface and a JSON API. The agent can browse tables and query records without changing the database.

Datasette is a command-line tool that serves SQLite databases. An agent with shell access can start a local server and read its JSON responses with curl. Datasette can also answer a single request from the command line without starting a server. This guide follows the documentation for the stable 0.65 release.

Serve a local database

Install Datasette with pip install datasette, or with brew install datasette on a Mac with Homebrew. Both are listed in the installation guide. The getting-started documentation then starts a web server on port 8001. Replace analytics.db below with a SQLite database file that already exists:

datasette serve analytics.db

Open http://127.0.0.1:8001/ to browse the database. Datasette also provides data in JSON, a format for structured information that tools can read. The interface links to the available data. An agent that can reach the local server can use those responses to inspect tables and records. The command serves a database; it does not install Datasette or create a database file. By default the server listens on 127.0.0.1, so only the local machine can connect. The CLI reference describes the --host option that changes this.

For example, an agent could inspect a local analytics database to find which table stores event names and how the records represent timestamps. The agent could then use those findings to propose a query or explain a code change. Ask it to inspect the database structure, including its tables and columns, and a small number of relevant records first. Tell it not to change the database.

Read the JSON API from the shell

Datasette usually names a database after its file name without .db, so analytics.db is served at /analytics. Adding .json to a page’s URL returns the same data as JSON. The database page lists its tables, and a table page returns rows. In the commands below, events is a placeholder for one of your tables:

curl 'http://127.0.0.1:8001/analytics.json'
curl 'http://127.0.0.1:8001/analytics/events.json?_shape=array&_size=5'

The _shape=array argument returns the rows as a list of JSON objects. _size=5 limits the page to five rows. Larger results come in pages, and the default JSON includes a next_url key that points to the next page.

The --get option runs one request against a path and prints the result without starting the web server:

datasette analytics.db --get '/analytics.json'

A practical request for an agent with shell access might be:

Inspect analytics.db with Datasette. Use the JSON API to identify the tables that contain event names and timestamps, then summarize the fields and show a few representative records. Do not modify data. Flag any assumptions you make about timestamp formats.

Keep credentials out of this prompt. If a Datasette instance requires a token, keep the secret in the local configuration or environment of the shell that runs the agent, and keep it out of the conversation text.

Check access before sharing data

Datasette does not require authentication by default. Its documentation says visitors can browse all the data and run read-only SQL queries, which retrieve data without changing it, unless permissions limit access. An agent connected to an unprotected instance gets the same broad view as any other visitor. Check which database the instance serves and who can reach it before connecting an agent, especially when records contain private or sensitive information. Datasette documents its authentication and permission behavior.

The stable documentation on running SQL queries says Datasette treats SQLite files as read-only and does not run INSERT or UPDATE statements. That reduces the risk of accidental edits, but it does not stop an agent from reading or repeating data it can access. The Datasette 1.0 alpha releases add a JSON write API. It needs an authenticated API token with write permissions, so check which version you run before you hand an agent a token.