Path-tables

A path-table is a table you never declare. Write a path where a table name goes, and dirsql scans the filesystem for you:

SELECT basename, size FROM './' ORDER BY size DESC LIMIT 5;

No .dirsql.toml entry, no DDL, no on_file. The name is the query.

How resolution works

dirsql never parses your SQL. It hands the statement to SQLite untouched; only when SQLite reports no such table: X does dirsql look at X:

1. If X starts with ./, dirsql registers a path-table over the matching files and re-prepares the statement.
2. Otherwise the SQLite error stands, unchanged.

Because discovery rides on SQLite's own errors, joins, subqueries and CTEs work with no extra machinery — SQLite names each missing target in turn, and dirsql resolves them one at a time.

Two consequences follow directly:

• A declared table always wins. The fallback only runs after SQLite has already failed to find the name, so a real table is found first. A table you genuinely named "./" shadows the path, and dirsql will not argue.
• A typo stays a typo. SELECT * FROM usrs fails with SQLite's error and nothing else. dirsql never guesses that an ordinary identifier meant a file.

Writing the path

A ./ path is relative to the index root — the directory dirsql is indexing, not your shell's working directory.

A directory name is one level, like ls. Naming a directory lists the files directly inside it and no deeper; * matches one level and ** any depth, as in the shell. A trailing / is * appended, so './*/' is './*/*': the files directly inside each top-level directory, like ls */.

You write	dirsql scans
'./'	files directly inside the index root, and no deeper
'./docs', './docs/'	files directly inside docs/
'./*'	the same as './'
'./**', './**/'	every file under the index root, recursively
'./*/'	the same as './*/*': files directly inside each top-level directory
'./docs/*.md'	markdown files directly inside docs/
'./docs/**/*.md'	markdown files at any depth under docs/
'./notes/today.md'	exactly that one file — one file is one row

A path containing *, ?, [ or { is a glob and is used exactly as written. The glob page has the syntax, examples and every divergence from bash, including braces and unbalanced brackets.

The scan starts at the last directory named outright before the first glob component, and descends only as deep as the pattern can match -- './small/*.md' lists small/ and nothing else -- so a query over one directory costs what ls small costs, however large the directories beside or below it. Only ** walks a whole subtree.

A path naming a single file yields exactly one row. dirsql never splits a file into rows on its own — that is what a table's on_file hook is for.

The ./ is required for index-relative paths. A bare glob is rejected with a hint rather than silently accepted:

SELECT * FROM '**/*.md';
-- no such table: **/*.md; did you mean './**/*.md'?

The quotes are required too. A path is not a bare SQL identifier — ./ is punctuation to SQLite's parser, so an unquoted path fails at parse time, before dirsql ever sees a table name to resolve. Single or double quotes both work; the error names the quoted form:

SELECT * FROM ./;
-- near ".": syntax error in SELECT * FROM ./ at offset 14
-- hint: paths used as table names must be quoted; did you mean "./"?

Symlinks

Symlinks are followed the way bash -O globstar follows them. A symlinked file is a file: './*' lists it, and so does naming it. A symlinked directory is entered by any component of the path except **, which never walks into one. A broken link lists nothing.

With linkdir -> real:

You write	dirsql scans
'./*/*'	linkdir/r.md and real/r.md
'./**'	everything under real/, nothing under linkdir/
'./**/*.md'	linkdir/r.md too: ** may stop on linkdir for *.md to enter
'./linkdir/**'	everything under linkdir/

Each link entered uses up one component of the path, so a symlink cycle is followed at most once per component and never hangs a scan.

Paths outside the index root

Three other prefixes resolve, with their usual shell meanings:

You write	dirsql scans
'/var/log/*.log'	an absolute path
'../notes'	relative to the index root's parent, as written
'~/notes/*.md'	relative to your home directory

A directory named this way is one level too; '../notes/**' descends. The trailing-/ rule holds here as well: '~/*/' is '~/*/*'. A ../ path-table reports path as written, relative to the directory dirsql runs in, as bash prints it: '../notes/*.md' gives ../notes/a.md. A / or ~/ path-table is absolute already, so it reports the full path. Either value is one you can paste into another command:

SELECT path FROM '/var/log/*.log';
-- /var/log/syslog

On Windows, the absolute forms also include a drive letter ('C:\logs\*.log' or 'C:/logs/*.log'), a UNC share ('\\server\share\*.md') and '~\notes'. \ and / both separate there, and the path these report uses / throughout (C:/logs/app.log), matching the /-separated relative paths.

A file name that is not valid Unicode keeps its exact bytes in path, basename, dir and ext. Reading such a value as text fails the query (column "path" holds text that is not valid UTF-8); select its bytes instead, with hex(path) or CAST(path AS BLOB).

On a system with no home directory, a ~/ path-table reports that it cannot resolve rather than guessing.

Columns

A path-table has the same virtual columns as any dirsql table:

Column	Type	Meaning
path	TEXT	path relative to the index root (as written for ../ tables, absolute for / and ~/ tables)
basename	TEXT	filename with extension
dir	TEXT	parent directory, relative to the index root
ext	TEXT	extension without the dot
size	INTEGER	size in bytes
mtime	INTEGER	modification time, Unix seconds
ctime	INTEGER	creation/change time, Unix seconds

There is also a hidden content column holding the file's text. It is excluded from SELECT * and read only when you name it, so scanning a large tree costs nothing until you ask for file bodies:

SELECT path FROM './docs/*.md' WHERE content LIKE '%deprecated%';

A file that cannot be read, or is not valid UTF-8, yields NULL content rather than failing the query.

Parsing rows with --on-file

By default a path-table's columns are the stat columns above — one row per file. When you want one row per record inside each file, attach a parser with the dirsql query flag --on-file:

dirsql query "SELECT title, author FROM './posts/*.md'" \
  --on-file 'extract.py'

The command runs once, with every matched file's absolute path appended as an argument, and prints one JSON object per line, exactly like a declared table's on-file hook — same argv splitting, same {root} placeholder, same argument-list splitting over a very large table. Its output is the table:

• The parser supplies the whole schema. Columns are inferred from the keys across the emitted rows. The stat columns (path, size, …) are not reachable on a parsed path-table — a parser that wants the path emits it (it has the paths). The two modes stay cleanly separate.

• The parser runs from the index root. There is no config file to anchor it, so the command's working directory is the directory dirsql query was run in — the same directory the ./ path is relative to — and {root} names it. python3 extract.py finds extract.py in that directory, whatever directory the path-table scans.

• A parser failure is the query's failure. A run that fails (spawn or non-zero exit) or whose output is not one JSON object per line fails the query with the command's stderr tail; there is no partial table, and the CLI exits 1. A run that succeeds but prints nothing fails too, because there are no keys to infer columns from:

  dirsql_parsed: parser produced no rows for `posts/*.md`; cannot infer a schema

• The skip rules still apply. A parsed scan honors the same .git /ignore rules a stat scan does (see below).

• The parser runs on every start. A parsed path-table is not kept in the persistent cache; declared tables are.

--on-file applies to every path-table in the query and may be given at most once. For different parsers per file set, define named tables in a .dirsql.toml with their own on-file keys and pass it with -c — the flag never touches config-declared tables. It is a query-subcommand flag; server mode does not accept it.

Freshness and scope

A path-table is scanned when the statement runs, so it always reflects the filesystem as it is now — unlike declared tables, which are indexed on build and updated by the watcher. A file created a moment ago shows up immediately.

The scan is live all the way down to content: a file's body is read only when the query names the content column, not when the row is discovered. A query that names it has the bodies of the rows it selects read all at once, several files at a time, before the first row comes back. A file deleted after the scan finds it but before its content is read yields NULL content — the same NULL an unreadable or non-UTF-8 file gives — rather than failing the query. This is an accepted consequence of reading live, not a bug to design around.

The table itself is per-connection: it lives in temp, so it cannot leak into sqlite_master or survive a restart. Under --persist a parsed table's rows outlive the connection in the cache (above), but the table is still minted fresh each run and the scan still decides what exists. A .dirsql/ directory is an ordinary dot directory.

When to promote to a declared table

Every query re-scans the filesystem: a path-table has no index and no watcher. That is the right trade for a hundreds-of-files, run-it-once question. When the same tree is queried repeatedly, or is large, declare a table for it instead — a declared table is indexed on build, kept fresh by the watcher, and (with --persist) survives restarts, so its rows are read from SQLite rather than re-walked each time. It is also the only place a keyword or vector index can live (templates).

Neither kind is the better one: each buys what the other gives up, and the choice is stated as one trade in how dirsql thinks.

Skip rules

A path-table scan applies the same ignore patterns your declared tables use — matched against root-relative paths under the same glob rule as the path itself, * one level and ** any depth — plus one built-in default, **/.git/**, which applies at any depth. node_modules is an ordinary directory: a .gitignore that lists it hides it, as it hides any other.

.gitignore

Path-table scans also respect .gitignore files by default, inside a git repo, the way git, fd and ripgrep do: a .gitignore applies below its own directory, deeper files override shallower ones, !pattern re-includes, and an ignored directory is pruned rather than walked. In a typical repo this excludes build output, virtualenvs, and caches with zero ceremony.

A .gitignore never hides a path the pattern spells out: './debug.log' lists that file even when *.log is ignored, while './*.log' does not.

A .gitignore is in force only when a directory holding .git encloses it. Outside a repo none applies, at the scan's start, above it or below it: a ~/.claude whose .gitignore is * still lists its files when ~/.claude is not a repo. Inside one, every .gitignore from the repo root down applies, including those above the directory a scan starts in, so './docs/*.log' and './**/*.log' agree about docs/.

Pass --no-ignore to restore the full walk — the determinism switch for scripted use, since results otherwise depend on .gitignore state. It disables only the .gitignore respect; the built-in default and configured ignore patterns still apply.

Naming a skipped directory

Skip rules are judged from the directory the scan starts in, so pointing at a skipped directory — built-in or gitignored — still scans it:

SELECT path FROM './.git/*';               -- scans .git anyway
SELECT path FROM './dist';                 -- scans dist/ even when gitignored

The .gitignore files in force there, including those above it, still filter what lies beneath it.

Hidden files

A path component starting with . is skipped unless the path spells it, the rule ls and fd use. './' and './**' skip .gitignore, .env and everything under .claude/; naming the component lists it:

You write	dirsql scans
'./**'	every file, skipping dot-named files and directories at any depth
'./.claude', './.claude/**'	the files under .claude/
'./.env'	that one file
'./**/.env'	every .env whose parent directories are not themselves dot-named
'~/.claude/projects/*/*.jsonl'	the walk starts inside .claude/, so nothing there is hidden

A spelled component may be a glob of its own: './.env*' lists .env.local, and './.*' lists every dot-named entry one level down. A dot component the path does not spell stays hidden however deep the scan goes, so './.claude/**' lists .claude/sub/notes.md but not .claude/sub/.cache/x.

This is independent of .gitignore: --no-ignore restores gitignored files and leaves dot-named entries hidden.

Joining against declared tables

Path-tables are ordinary SQLite tables once resolved, so they join freely:

SELECT p.basename, d.size
FROM './docs/*.md' AS p
JOIN pages AS d ON d.path = p.path;

A zero-match path-table is not an error — it is an empty table, and the query returns no rows.