Add actual results in the book

[max-sixty]

From https://github.com/PRQL/prql/pull/1642#discussion_r1111315974

Would showing results of queries be helpful in the book? Currently we compile to SQL. For some queries, showing the actual results, executed against an artists table, might be helpful.

We would need to:

• Producing the HTML that displays a small table of results as well as the SQL. Possibly with "tabs"? Likely there are existing approaches to this so we wouldn't have to write much HTML ourselves (though I'm not sure if there are native mdbook approaches, so we may have to embed some HTML...)
• Run queries in the book's snapshot.rs, probably with duck-db, to generate the results. We could use prql_results as the language to hint to this file that it should produce results as well as the SQL, in the same way we use prql_no_test to avoid attempting to produce SQL.
• Adjust some queries so they can run on the artists db we have in the playground. We don't need to adjust them all, we can do this very gradually.

Any of these would make a welcome PR. While they'd all be required in order to have this functional, we can merge individual PRs to split up the work.

[Jelenkee]

I guess the next step is making the PRQL-snippet editable and executable like in the offical Rust book :wink:

[max-sixty]

I guess the next step is making the PRQL-snippet editable and executable like in the offical Rust book 😉

Yes that would be very cool!

A button for "open in playground" might be a tractable thing?

To have it work live in the book would be possible, but would require bundling the wasm in the book, and writing the logic to do the live updating — not trivial at all. (Though actually much easier than doing it for rust, since there's a wasm module which will compile it quickly enough to have it on every keystroke)

[eitsupi]

An easy way might be to pass a query to the DuckDB CLI, receive a Markdown table, and embed it, as I wrote here. https://github.com/PRQL/prql/pull/3634#issuecomment-1751975845

• DuckDB CLI binary is required, but tedious DuckDB compilation is not required.
• We don't need to format the results into a Markdown table.

[eitsupi]

An easy way might be to pass a query to the DuckDB CLI, receive a Markdown table, and embed it, as I wrote here. #3634 (comment)

Here is an example of this. https://github.com/eitsupi/prql-book-test/blob/f962f1be627da025c85f6bd8b96575e565920524/src/main.rs#L132-L138

[max-sixty]

Here is an example of this. https://github.com/eitsupi/prql-book-test/blob/f962f1be627da025c85f6bd8b96575e565920524/src/main.rs#L132-L138

That looks great @eitsupi ! It's actually not so hard with the -markdown flag.

To the extent it's possible to get it working with duckdb-rs, that would mean it's possible to run with cargo test. Otherwise we won't be able to accept most docs contributions without an additional setup step...

[eitsupi]

Otherwise we won't be able to accept most docs contributions without an additional setup step...

I see what you mean, but would it be difficult to add a DuckDB CLI installation where we already need to install Hugo, mdBook, etc.? Personally, I find it much more painful to build duckdb-rs.

[max-sixty]

Personally, I find it much more painful to build duckdb-rs.

Interesting — because it uses so much resources?

---

would it be difficult to add a DuckDB CLI installation where we already need to install Hugo, mdBook, etc.?

Yes, agree (though fwiw we don't actually require installing those in order to contribute to the docs — only cargo insta. mdbook is required to preview the docs locally.)

It would be a big enough win to get the results in the book that if we can, I think we should pursue it, and then we can figure out the easiest way to allow contributions. We could even have CI auto-commit and push the changes.

[eitsupi]

Interesting — because it uses so much resources?

Yes, it consumes large amounts of machine power and time. (It is also surprisingly difficult on Windows.)

If we stick to pure Rust, I vote for using SQLite instead of DuckDB....... (Here is also a SQLite implementation written in Rust https://github.com/kawasin73/prsqlite)

[max-sixty]

If we stick to pure Rust, I vote for using SQLite instead of DuckDB.......

Yes great idea — this would work well! And we already have an integration

[eitsupi]

In that case, the DuckDB CLI source code would be a good reference for table formatting. https://github.com/duckdb/duckdb/blob/0ef2a6faa2ec98b92b4c0e28827c193c32121bd1/tools/shell/shell.c#L12802-L12814