Plugin Hooks for "compile to SQL" languages

[asg017]

There's a ton of tools/languages that compile to SQL, which may be nice in Datasette. Some examples:

• Logica https://logica.dev
• PRQL https://prql-lang.org
• Malloy, but not sure if it works with SQLite? https://github.com/malloydata/malloy

It would be cool if plugins could extend Datasette to use these languages, in both the code editor and API usage.

A few things I'd imagine a datasette-prql or datasette-logica plugin would do:

• prql= instead of sql=
• Code editor support (syntax highlighting, autocomplete)
• Hide/show SQL

[simonw]

I'm not interested in these in Datasette core itself, but I think they have a ton of potential for plugins.

I wonder what the best way to handle that would be?

Right now it's possible to write a plugin that adds extra routes, so someone could build a /dbname/-/prql?query=xxx endpoint.

If this could return JSON, they could add JavaScript to the /dbname page that provided a UI for kicking off one of those queries.

Something that could make that more ergonomic might be the plugin hook that allows plugins to add extra HTML to different core database pages - e.g. adding a "Query this database using PRQL" button or link or even a full form at the top of that database page. That's this issue here:

• Ability for plugins to collaborate when adding extra HTML to blocks in default templates #1191

[asg017]

Thinking about this more, here a list of things I imagine a "compile-to-sql" plugin would want to do:

1. Attach itself to the SQL code editor (switch from SQL -> PRQL/Logica, additional syntax highlighting)
2. Add "Query using PRQL" buttons in various parts of Datasette's UI, like /dbname page
3. Use $LANGUAGE= instead of sql= in the JSON API and the SQL results pages
4. Have their own dedicated code editor page

1. and 2) would be difficult to do with current plugin hooks, unless we add the concept of "slots" and get the JS plugin support in. 3) could maybe be done with the asgi_wrapper(datasette) hook? And 4) ca n be done easily with the register_routes() hooks.

So it really only sounds like extending the SQL editor will be the hard part. In #2094 I want to add JavaScript plugin hooks for extending the SQL editor, which may work here.

If I get the time/motivation, I might try out a datasette-prql extension, just because I like playing with it. It'd be really cool if I can get the asgi_wrapper() hook to work right there...