datasette-peirce 0.1.0

Query Datasette databases using Peirce semantic syntax instead of SQL

datasette-peirce

Query any SQLite database using semantic coordinates instead of SQL — asking who, what, when, where, why, and how rather than writing column names and JOIN syntax.

It's as close as you can get to asking a question naturally without being probabilistic. No language model is guessing your intent. The query means exactly what it says, and the SQL it generates is always visible underneath.

WHO.artist_name = "Nina Simone"
WHAT.genre = "Jazz" AND WHEN.album_release_decade = "1960s"
WHO.attendee_name = "Alex" AND WHEN.session_date BETWEEN "2024-01-01" AND "2025-01-01"
WHAT.genre CONTAINS "Rock" OR WHAT.genre CONTAINS "Electronic"
WHEN.album_release_decade PREFIX "196"

---

How it works

You map your columns to dimensions once — in a browser, no JSON editing required. After that you query by meaning, not by column name.

open any SQLite database
       ↓
map columns in browser (WHO is a person, WHAT is a thing, WHEN is a date...)
       ↓
ask a question in Peirce
       ↓
see the SQL it generated
       ↓
hand off to Datasette's native view with filters already applied

The mapping step is the valuable part. Once your columns have stable semantic names, queries survive schema changes. Rename a column — update one line in the mapping, every query still works.

---

Install

pip install datasette-peirce

On Windows, use python -m pip if pip alone isn't recognised:

python -m pip install datasette-peirce

---

Quick start with the sample database

python make_sample_db.py
datasette data.db

On Windows:

python -m datasette data.db

Then open your browser and go to:

http://127.0.0.1:8001/-/peirce-query

If no mapping exists for your database yet, the plugin redirects you to the setup page automatically. Try a query:

WHO.attendee = "Smith"

You should see matching rows and the SQL it generated underneath. Press Ctrl+C to stop the server.

---

Using your own CSV

setup_datasette.py converts a CSV to a SQLite database and walks you through the column mapping interactively — no JSON editing:

python setup_datasette.py mydata.csv

It will read your CSV, show sample values for each column, suggest a dimension, and ask you to confirm or change it. When done it saves mydata.db and mydata.lens.json and prints three example queries from your actual data.

Then:

datasette mydata.db

You don't have to map every column — just the ones you want to query.

---

Query syntax

Syntax	Example
Equality	WHO.name = "Smith"
Inequality	WHO.name != "Smith"
Substring	WHAT.title CONTAINS "garden"
Prefix	WHEN.decade PREFIX "196"
Range	WHEN.year BETWEEN "1960" AND "1980"
AND	WHO.name = "Smith" AND WHAT.genre = "Jazz"
OR	WHO.name = "Smith" OR WHO.name = "Jones"

Available coordinates for your data are shown at the bottom of every query page.

---

Browser-based setup

Navigate to /-/peirce-query/setup to map your columns to dimensions in the browser. The plugin reads your schema, shows sample values, and suggests dimensions based on column names. You can also reach it via the "Edit mapping" link on any query page.

The six dimensions:

Dimension	Means	Examples
WHO	a person or organisation	author, artist, attendee
WHAT	a thing, topic, or type	title, genre, ingredient
WHEN	a date, time, or year	published, session_date, decade
WHERE	a place or location	city, venue, country
WHY	a reason or classification	occasion, purpose, status
HOW	a method, format, or quantity	style, method, format

---

The lens file

The mapping is saved as a .lens.json file next to your database. It's plain JSON and compatible with the broader snf-peirce ecosystem.

{
  "lens_id": "my_data_v1",
  "default_table": "my_table",
  "mappings": [
    { "dimension": "WHO",  "semantic_key": "author", "column": "author_name" },
    { "dimension": "WHAT", "semantic_key": "title",  "column": "book_title"  },
    { "dimension": "WHEN", "semantic_key": "year",   "column": "pub_year"    }
  ]
}

---

What this is not

This plugin is predicate pushdown into SQLite via a semantic facade. SQLite still does all the execution. It is not full SNF substrate routing — that's Reckoner.

Think of this as the lightweight entry point. If you find the model useful and want more — multiple sources, versioning, a full query workbench — that's what Reckoner is for.

---

Roadmap

• v0.1 (this): browser setup, Peirce query interface, SQL visible underneath, handoff to Datasette native view
• v0.2: NOT operator, multi-table support
• v0.3: DuckDB substrate support

---

Part of the peirce-lang ecosystem.