Plugin hook for adding new output formats

[simonw]

What would it take to add a format hook? I'm still thinking about my GIS workflow, and being able to do sqlite-utils query ... --geojson would be nice. It's the one place my Datasette workflow is messy, having to do datasette . --get /path/to/query.geojson --setting max_rows_returned 10000 --load-extension spatialite.
I know the current pattern is --csv, but maybe --format geojson is more future-proof.

https://discord.com/channels/823971286308356157/997738192360964156/1133076679011602432

[simonw]

The main blocker here is coming up with a design. The challenge is cleanly integrating it with the existing format options:

sqlite-utils/docs/cli-reference.rst

Lines 114 to 132 in 8bee145

	--nl Output newline-delimited JSON
	--arrays Output rows as arrays instead of objects
	--csv Output CSV
	--tsv Output TSV
	--no-headers Omit CSV headers
	-t, --table Output as a formatted table
	--fmt TEXT Table format - one of asciidoc, double_grid,
	double_outline, fancy_grid, fancy_outline, github,
	grid, heavy_grid, heavy_outline, html, jira,
	latex, latex_booktabs, latex_longtable, latex_raw,
	mediawiki, mixed_grid, mixed_outline, moinmoin,
	orgtbl, outline, pipe, plain, presto, pretty,
	psql, rounded_grid, rounded_outline, rst, simple,
	simple_grid, simple_outline, textile, tsv,
	unsafehtml, youtrack
	--json-cols Detect JSON cols and output them as JSON, not
	escaped strings
	-r, --raw Raw output, first column of first row
	--raw-lines Raw output, first column of each row

I'd like to avoid breaking backwards compatibility.

The most obvious solution is to add a --format x option. This is slightly confusing as --fmt already exists.

Or... --fmt could be enhanced to handle plugins too - and the existing set of formats (from the tabulate integration) could be refactored to use the new hook.

If I ever do release sqlite-utils 4 I'm tempted to move that tabulate stuff into a plugin.

[simonw]

I'm currently leaning very slightly towards --format geojson - and explaining in the docs that --format is for formats added by plugins.

[simonw]

A related feature would be support for plugins to add new ways of ingesting data - currently sqlite-utils insert works against JSON, newline-JSON, CSV and TSV.

[eyeseast]

A related feature would be support for plugins to add new ways of ingesting data - currently sqlite-utils insert works against JSON, newline-JSON, CSV and TSV.

This is my goal, to have one plugin that handles input and output symmetrically. I'd like to be able to do something like this:

sqlite-utils insert data.db table file.geojson --format geojson
# ... explore and manipulate in Datasette
sqlite-utils query data.db ... --format geojson > output.geojson

This would work especially well with datasette-query-files, since I already have the queries I need saved in standalone SQL files.

[eyeseast]

I wonder if this should be two hooks: input and output. The current --csv (and --tsv) options apply to both. Haven't looked at how it's implemented. Or maybe it's one hook that returns a format for reading and for writing.