Plugin system

[asg017]

I'd like there to be a plugin system for sqlite-utils, similar to the datasette/llm plugins. I'd like to make plugins that would do things like:

• Register SQLite extensions for more SQL functions + virtual tables
• Register new subcommands
• Different input file formats for sqlite-utils memory
• Different output file formats (in addition to --csv --tsv --nl etc.

A few real-world use-cases of plugins I'd like to see in sqlite-utils:

• Register many of my sqlite extensions in sqlite-utils (sqlite-http, sqlite-lines, sqlite-regex, etc.)
• New subcommands to work with sqlite-vss vector tables
• Input/ouput Parquet/Avro/Arrow IPC files with sqlite-arrow

[asg017]

Another use-case: I want to make a sqlite-utils plugin that'll help me insert data into Datasette.

sqlite-utils insert-datasette \
  --token $DATASETTE_API_KEY \
  https://latest.datasette.io/fixtures/my-table \
  'select ...'

This could also be a datasette plugin (ex datasette upload-data ..., but you can also think of sqlite-utils plugins that upload to S3, a postgres table, other DBMS's, etc.)

[simonw]

I really like this. I'm also interested in:

• Plugins that make new custom SQL functions available - similar to this Datasette hook: https://docs.datasette.io/en/stable/plugin_hooks.html#prepare-connection-conn-database-datasette
• Plugins that register functions that can be used as recipes for sqlite-utils convert https://sqlite-utils.datasette.io/en/stable/cli.html#sqlite-utils-convert-recipes

The upload-data-to-Datasette problem is planned to be solved by a future version of https://github.com/simonw/dclient

[eyeseast]

Would this possibly make a bunch of x-to-sqlite tools obsolete? Or nudge some to become plugins?

[simonw]

Would this possibly make a bunch of x-to-sqlite tools obsolete? Or nudge some to become plugins?

Yeah, it could do! That's not a terrible idea to be honest, those things have really been proliferating.

Alternatively, they could each register themselves as plugins in addition - so if you install e.g. pocket-to-sqlite you could then optionally also run it as sqlite-utils pocket-to-sqlite ...

The benefit there is for people who install sqlite-utils from Homebrew, where it gets its own virtual environment. They could run:

brew install sqlite-utils
sqlite-utils install pocket-to-sqlite

sqlite-utils pocket-to-sqlite ...

[simonw]

I'm going to start by adding the register_command hook using the exact same pattern as Datasette and LLM.

[simonw]

Here's documentation for the new plugins mechanism, including a very short tutorial on writing a new plugin (inspired by https://llm.datasette.io/en/stable/plugins/tutorial-model-plugin.html):

https://sqlite-utils.datasette.io/en/latest/plugins.html

[eyeseast]

Cool. I might try to add a geojson plugin that handles both input and output. That would help me out a lot.

[simonw]

Splitting off an issue for prepare_connection() since Alex got the PR in seconds before I shipped 3.34!

[simonw]

Now that we have two plugin hooks I'm closing this issue (we can open other issues for further hooks).