Feature Request: PRQL support

[dljsjr]

So, first, sorry about the PR spam on all your repos the last few days 😅

The reason I was doing that was because I've been on a personal education journey to learn both DuckDB (because I resonate w/ SQL much more than pandas) and PRQL because it's such a powerful and expressive alternative to raw SQL.

So if you look at my fork on the prql branch, I've cobbled together a janky version of this based on all my PR's to this repo and textual-textarea.

Granted that branch is definitely not ready for prime-time, but the bones are there and it actually works pretty great.

The meat of the changes are this:

1. ℹ️ This functionality is based on this DuckDB extension: https://github.com/ywelsch/duckdb-prql
   1. PRQL works by getting compiled directly to SQL
   2. PRQL already has a duckdb dialect target built-in
   3. This plugin works by running the PRQL compiler in duckdb dialect mode on-the-fly
   4. Because of this, no changes are needed to the Python API outside of making sure the plugin is installed and loaded before passing PRQL strings to the sql() method on the connection.
2. ℹ️ The change to textarea was just a convenience for me so that I could have some "init" scripts in my home dir that were easy to load
3. ⚠️ Unfortunately, this extension does require that unsigned extensions be enabled, hence the PR for the -unsigned flag.
4. ⚠️ This extension has its own release cadence, and doesn't have a 0.8.1 release yet, which is why on my personal fork I've pinned duckdb to 0.8.0 in the pyproject.toml.
   1. This is an artifact of the URL convention that duckdb uses for extension downloads even though semver kinda indicates that the extensions should remain compatible
   2. I think the fix to this would be vendoring the plugin but that would drastically complicate build and delivery of this project so I'm not sure the way you'd want to deal with that if this went forward.

Obviously, there's a lot from the above that raises questions about if this feature is worth it. But if you think it's something that would be valuable I'm more than happy to continue assisting with contributions.

And there would be more work down the road to make "prql mode" more of a 1st class citizen like including syntax support.

So I understand if it's not something you're interested in including, and it's pretty trivial to keep a patch for this going. Also, if we merge the -unsigned changes, then I don't even need the code patches, because you can load prql using regular plugin load statements.

Thanks in advance for looking at this!

[tconbeer]

Don't apologize -- I really love it when people contribute!

Thanks for the detailed issue.

We'll definitely merge the -unsigned PR, as well as the expanduser PR.

Unfortunately, to support MotherDuck we need duckdb >=0.8.1; I think we could loosen the pin and check the duckdb version before executing any motherduck options, and emit an error or warning if someone tries to connect to MD using the wrong version of duckdb. Today the package actually just pins >=0.8.0, so if this is installed in a project that pins harlequin to a lesser version, that should be fine.

I don't want to vendor the PRQL extension, but I think if we loosen the duckdb pin in this project, people could pin duckdb 0.8.0 separately and then use the PRQL extension.

I'm also open to better ergonomics around loading extensions using a command-line option. For any of the official extensions we could support something like:

harlequin --extension httpfs -e excel

We could also support uris:

harlequin -e https://extensions.duckdb.org/v0.8.1/windows_amd64/json.duckdb_extension.gz

And in the case of prql, a custom repo:

harlequin --custom-extension-repository 'welsch.lu/duckdb/prql/latest' --extension prql

[tconbeer]

Just confirmed that you can even downgrade duckdb in a pipx installation:

pipx install harlequin
pipx inject harlequin "duckdb==0.8.0"
harlequin

Then in harlequin,

pragma version

Returns 0.8.0.

[tconbeer]

Also just wanted to note that PRQL isn't supported by Pygments, so syntax highlighting won't work.

[tconbeer]

Released in 0.0.24:

harlequin -u -e prql --custom-extension-repo welsch.lu/duckdb/prql/latest

Also, PRQL released a 0.8.1-compatible extension yesterday (except for Windows), so you shouldn't need to downgrade duckdb any more.