xql: an introduction

One resource per session

Three command shapes — one binds a CSV file, one binds a SharePoint list, one binds a xinglet snapshot. The binding is the resource the whole session edits, and it is the FROM clause that the SQL itself does not have to mention.

$ xql csv tasks.csv                                # local CSV file
$ xql sp https://contoso.sharepoint.com/.../Tasks  # SharePoint list (device-code auth)
$ xql xinglet xinglet://<uuid>                     # xinglist snapshot (read-only)

That single-resource discipline is the price for not having a FROM clause, and it is the same trade ed and sed make: the buffer is implicit, the verbs are short, and the cost is that one session edits one thing. If you need two, you run two sessions. If you need to combine them, you join upstream — xql leaves that to DuckDB or your shell.

A REPL and a one-shot script accept the same SQL. --exec '<sql>' runs one statement and exits, the way sed -e does. With no --exec, you land at a prompt and type statements until you quit.

$ xql csv tasks.csv --exec "SELECT Title WHERE Priority > 2"

A first session

xql opens to a xql> prompt and a banner naming the resource and its column count. The first move on an unfamiliar table is describe, which prints the column schema with the type inferred from the first 1024 rows. There is no FROM, so the verb names the table-shaped operation directly.

$ xql csv tasks.csv
Connected to: tasks.csv (5 columns). Type "help" for commands, "quit" to exit.
xql> describe
column      type
Title       string
Status      string
Priority    int
Modified    date
Owner       string

Two things to know about the inferred type. Comparisons use it — Priority > 2 does a numeric compare and Modified < '2024-01-01' does a date compare with ISO 8601 literals. And inference is conservative: a column of leading-zero values like "07030" stays a string by default, because writing it back as an int would silently drop the zero, and that round-trip integrity matters more than catching every numeric column. --type Code=int overrides at startup if you know better.

A bare SELECT runs against the bound table. Column names are case-sensitive and match the file's header exactly.

xql> SELECT Title, Status WHERE Priority > 2 ORDER BY Modified DESC
| Title              | Status      |
| ------------------ | ----------- |
| Migrate auth layer | Open        |
| Backfill activity  | In Progress |
(2 rows)

Results render as an ASCII table on a terminal. When stdout is not a TTY — in a pipe, or redirected to a file — they auto-switch to TSV, so xql ... | column -t and xql ... > out.tsv work the way you would expect. The grammar surface is the standard one: DISTINCT, GROUP BY, HAVING, ORDER BY, LIMIT, OFFSET, and the five aggregates (COUNT, SUM, AVG, MIN, MAX). The grammar reference has the full BNF.

Meta-commands

The REPL accepts SQL plus a small set of meta-commands as plain words — no leading dot, no backslash. They follow the shapes sqlite picked, minus the prefix.

Redirection is sticky until you clear it: typing output with no argument turns it off again, and a bare command reports the current path. once is the psql \once shape — one statement to a file, then back to stdout. Both serialize CSV regardless of mode, since mode is about the terminal view and output is about the file on disk.

The four modes correspond to the four things one usually wants to do with a SELECT: table for reading the result yourself, tsv for piping to column -t / cut / awk, csv for saving to disk for Excel or another consumer, and json for handing the result to a script. mode json produces a JSON array of objects, one per row, with the column name as the key and a typed value (int / float / bool / string) as the value. Empty cells become null.

Writes preview before they commit

This is the part xql does differently from every other SQL prompt, and it is the part that matters when the bound resource is a SharePoint list shared with your colleagues. An UPDATE, INSERT, or DELETE first runs as a preview: xql parses the statement, prints the affected row count and a sample of which rows match, and prompts. Anything but y cancels.

xql> UPDATE SET Status = 'Done' WHERE Modified < '2024-01-01'
Would update 8 rows in tasks.csv:
  SET Status = "Done"
Apply? [y/N]: y
Updated 8 of 8 rows.

The preview's job is to catch the WHERE clause that selects more rows than you meant, before the change is on disk or in SharePoint. When you have already checked the count and are ready to commit without the prompt, append ! to the statement:

xql> DELETE WHERE Status = 'Archived' !
Deleted 12 rows.

In --exec mode there is no interactive prompt to confirm against, so writes default to a dry run: the preview prints, the change does not apply. --commit opts in. And a bare DELETE — one with no WHERE, which would empty the table — additionally requires --confirm-destructive, because a fat-finger DELETE on a SharePoint list cannot be undone with Ctrl-Z.

$ xql csv tasks.csv --exec "DELETE WHERE Status = 'Archived'" --commit
$ xql sp ".../Lists/Tasks" --exec "DELETE" --commit --confirm-destructive

Three gates, layered: the preview prompt is the default, --commit is the explicit one-shot opt-in, and --confirm-destructive is the extra latch in front of the irreversible shape. They are the same discipline a deliberate-save line editor teaches: a write that requires you to mean it.

Pointing at SharePoint

The SharePoint backend takes a list URL and binds to it through Microsoft Graph. The first run prints a device-code prompt; the resulting refresh token caches at ~/.config/xql/sp-token.json (file mode 0600), so subsequent runs reauthenticate silently.

$ xql sp https://contoso.sharepoint.com/sites/team/Lists/Tasks
To sign in, visit https://microsoft.com/devicelogin and enter the code XYZ-ABCDEF
Authenticated as: david@contoso.com
Connected to: Tasks (12 columns). Type "help" for commands, "quit" to exit.
xql>

The list URL can be the bare /Lists/<name> root, an AllItems.aspx view URL, or a URL-encoded variant of either — xql resolves all three to the same list. describe is the first verb you run here too, and it returns a wider schema than the CSV version did:

xql> describe
column      internal     type      flags
Title       Title        string
Status      Status       choice    Open|In Progress|Done|Archived
Priority    Priority     number
Modified    Modified     datetime  read-only
Owner       AssignedTo   lookup    list=Users

The internal column matters in a way it does not on CSV. SharePoint stores every list field under two names: a display name (what the UI shows) and an internal name (what Graph speaks). They are often the same, but a field renamed after creation, or one with spaces or German characters in its display name, keeps an internal name like WBSCode or Description0. xql's identifiers resolve to the internal name, the same way the OData filters that run server-side do. describe shows both columns side by side precisely so the gap is visible.

Writes validate against the list schema before any Graph round-trip. Unknown columns, type mismatches, writes to read-only fields like Modified, and writes to column kinds the backend cannot safely round-trip (Person, Hyperlink, Calculated) all fail with a clear message naming the field and the problem. None of those failures touch the list — the rejection happens locally, before a single HTTPS call goes out.

What SharePoint takes from SQL

A SELECT against a SharePoint list compiles to a server-side OData $filter and runs there. That is what makes a large list responsive — the rows you want are the rows that come back, not the rows the client filters out of a GetItems of the entire list. The price is that not every SQL predicate translates: OData's surface is smaller than SQL's, and where xql cannot translate truthfully it refuses rather than fall back to client-side filtering.

The clearest example is LIKE. SharePoint's OData supports three pattern shapes and only three:

Anything else — an underscore wildcard, a mid-pattern % like 'a%b%c', an escaped delimiter inside the pattern — has no OData equivalent. xql does not translate to a near match and silently lose precision; it errors out and names the alternative.

xql> SELECT Title WHERE Title LIKE 'tax_%2024'
single-character wildcard '_' is not supported against SharePoint;
rephrase to a prefix/suffix/contains shape, or filter with `contains`
first and refine the result with `xql csv` on the export.

The same refusal style applies to writes the backend cannot safely perform. A SET AssignedTo = 'alice@contoso.com' against a Person field fails before sending anything, because the field stores a user ID, not an email, and silently resolving one to the other would be a worse tool than naming the problem. That refusal style is policy: a SharePoint backend that quietly degraded would be the wrong shape.

What does translate runs server-side: WHERE predicates, IN expansion to an or chain, BETWEEN to a ge/le pair, and IS NULL to OData's eq null. ORDER BY, LIMIT, OFFSET, and DISTINCT apply client-side after the filtered fetch, and the affected-row preview before a write counts what the OData filter returned, not an estimate.

Prototype on CSV, run on SharePoint

This is the move xql exists to make easy. The grammar across the two backends is the same, and the meta-commands are the same, so a query that runs against a CSV export of the list runs unchanged against the list itself. The CSV pass is fast, has no network round-trip, and is reversible — the file is on disk and git checkout undoes any UPDATE. The SharePoint pass commits to shared state. Running them in that order catches a wrong WHERE before it costs anything.

A typical rhythm — export the list to CSV once, prototype against it until the UPDATE is right, then run the same statement against the live list:

$ xql csv tasks.csv
xql> SELECT COUNT(*) WHERE Status = 'Archived' AND Modified < '2024-01-01'
| COUNT(*) |
| -------- |
| 47       |
(1 row)
xql> UPDATE SET Status = 'Done' WHERE Status = 'Archived' AND Modified < '2024-01-01'
Would update 47 rows in tasks.csv: ...
Apply? [y/N]: y
xql> quit

$ xql sp https://contoso.sharepoint.com/sites/team/Lists/Tasks --exec \
    "UPDATE SET Status = 'Done' WHERE Status = 'Archived' AND Modified < '2024-01-01'" \
    --commit
Updated 47 of 47 rows.

The 47 on the CSV pass matches the 47 the SharePoint pass reports, and you knew to expect 47 from the COUNT. That match is the whole point: the grammar is identical, the predicate compiles the same way (modulo the OData translation), and the CSV pass is a rehearsal that costs nothing. When the CSV and the list diverge — someone edited the list between the export and your run — refresh re-reads the resource and the preview shows the actual rows that match. You only ever commit what xql showed you.

The xinglet backend

xinglet is a third binding shape, narrower than the other two. It reads a published xinglist snapshot — a versioned slice of tabular data identified by a UUID — over HTTPS and runs SELECT against it. Writes are rejected before parsing, because the server exposes no write endpoint over the Bearer auth xql uses.

$ export XINGLET_TOKEN=xglt_...
$ xql xinglet xinglet://4babff02-909f-4dba-b3df-3edf14b778bf
Connected to: People Roster (8 columns). Type "help" for commands, "quit" to exit.
xql> SELECT Name, Department WHERE Joined > '2024-01-01' ORDER BY Joined
...
xql> UPDATE SET Department = 'Eng'
xinglet is read-only: UPDATE is not supported. To edit the source, open the
list in xinglet and re-publish.

Two things make the backend useful even though it cannot write. The snapshot is a single HTTPS GET, so queries run as fast as a local CSV after the first fetch. And the export carries inline column type annotations (Joined:date, Count:number, Status:choice(active|inactive)), so type-aware comparisons work without a --type flag. refresh re-fetches the snapshot if you want to catch upstream edits without restarting.

The token never persists. It lives in XINGLET_TOKEN for the lifetime of the process and is sent only on the URL named on the command line.

Where xql stops

Three pieces of SQL are deliberately absent. JOIN is not in the grammar — one session edits one resource, and a join needs two; the join belongs upstream, in DuckDB or in a shell that fans the rows out and pipes them back. Subqueries are not in the grammar either, and neither are UNION, INTERSECT, and EXCEPT. Each of them needs the same join machinery, and the same upstream pattern handles them.

A fourth absence is more specific. WHERE col1 = col2 is not in the grammar — the right side of every comparison is a literal value, because the SharePoint backend's OData translation needs it to be one and the CSV backend does not justify a diverging grammar on its own. Queries shaped like compare two columns of the same row belong to xled, where a row-selector address picks rows by a cell-level predicate and the next clause edits them.

What is left is small and sharp: the SQL you already know, against three resources that mostly were not built for SQL, with a preview gate in front of every write. The pieces compose down a pipe the way Unix intends:

duckdb -csv ... | xql csv /dev/stdin --exec "SELECT Department, COUNT(*) GROUP BY Department"
xql sp .../Lists/Tasks --exec "SELECT * WHERE Status = 'Open'" | xled '[Owner] s/.*/\U&/'

The SQL you already know, in three places it usually is not. That is the whole offer.

Ready to try it?

xql is a single Go binary for Linux and macOS, MIT-licensed and built with no runtime dependencies. The xql page has the install steps for Go, apt, and prebuilt binaries; the full source, the formal grammar, and the security policy live at github.com/excelano/xql.

Install xql