Sq.io: jq for databases and more (sq.io)

They are Jedi’s at jq though.

Kind of. However on first glance it seems like the separator in PRQL is a newline instead of a `|`, which is less ergonomic in a shell.

    ```sh
    > prqlc compile <<EOF - | duckdb
    let get = path obj -> s"""{obj} -> {path}"""
    let getstr = path obj -> s"""{obj} ->> {path}"""
    let extract = obj path -> s"""json_extract({obj}, {path})"""
    
    from [{data='{"duck": [1, 2, 3]}'}]
    select { (data | get '$.duck[0]'), (data | getstr '$.duck[1]'), extract data '$.duck[2]'}
    EOF
    ┌───────────────────────┬──────────────────────────┬───────────────────────────────────┐
    │ "data" -> '$.duck[0]' │ ("data" ->> '$.duck[1]') │ json_extract("data", '$.duck[2]') │
    │         json          │         varchar          │               json                │
    ├───────────────────────┼──────────────────────────┼───────────────────────────────────┤
    │ 1                     │ 2                        │ 3                                 │
    └───────────────────────┴──────────────────────────┴───────────────────────────────────┘
    ```

Some time ago I wrote pq (https://github.com/prql/prql-query) which aimed to be a simple CLI for PRQL to wrangle data on the command line, much like sq.

Unfortunately I haven't had time to maintain it so it is now archived and out of date. I hope that I might get a chance to update it again. More has happened since then and there are low hanging fruit to make it more usable, for example adding connector_arrow (https://github.com/aljazerzen/connector_arrow) support for other databases, etc...

Quick example of how things looked with pq:

    ```sh
    $ pq --from i=invoices.csv "from i | take 5"
    +------------+-------------+-------------------------------+-------------------------+--------------+---------------+-----------------+---------------------+-------+
    | invoice_id | customer_id | invoice_date                  | billing_address         | billing_city | billing_state | billing_country | billing_postal_code | total |
    +------------+-------------+-------------------------------+-------------------------+--------------+---------------+-----------------+---------------------+-------+
    | 1          | 2           | 2009-01-01T00:00:00.000000000 | Theodor-Heuss-Straße 34 | Stuttgart    |               | Germany         | 70174               | 1.98  |
    | 2          | 4           | 2009-01-02T00:00:00.000000000 | Ullevålsveien 14        | Oslo         |               | Norway          | 0171                | 3.96  |
    | 3          | 8           | 2009-01-03T00:00:00.000000000 | Grétrystraat 63         | Brussels     |               | Belgium         | 1000                | 5.94  |
    | 4          | 14          | 2009-01-06T00:00:00.000000000 | 8210 111 ST NW          | Edmonton     | AB            | Canada          | T6G 2C7             | 8.91  |
    | 5          | 23          | 2009-01-11T00:00:00.000000000 | 69 Salem Street         | Boston       | MA            | USA             | 2113                | 13.86 |
    +------------+-------------+-------------------------------+-------------------------+--------------+---------------+-----------------+---------------------+-------+
    $ # When there is only one input table then this automatically becomes the source relation, i.e. `from i | ` is prepended to the query
    $ # so this can be simplified to:
    $ pq --from invoices.csv "take 5"
    ...
    ```

Why is DuckDB better than clickhouse-local?

Thankfully PG17 is released and ready for production querying needs!

Unless you're the GP your guarantee about the persons motivation is as meaningless as the post you're replying to.

Thank you for creating a composable, simple to grok Unix tool. sq is an example of what is so great about the Unix philosophy.

Personally, I can see a spot for sq in my developer toolbox. Instead of reaching for DBeaver’s bloated interface, or psql’s perplexing TUI, I like the idea of picking up an sq multitool to answer some simple questions quickly about my databases.

Thanks again!

Out of the random shit I had to deal with CSV file wrangling in the last two years:

- no defined encoding, so it may be anything from US-ASCII over ISO-8859-x to UTF-8 and it's always a guesswork what it actually is

- no definition of the separator sign, usually it can be a literal comma, a semicolon, or tabs

- escaping of the separator sign in column values is always fun

- escaping of newlines is even MORE fun

- line endings are not specified

Every piece of software there is has their own expectations on what specific kind of format it expects, and to make it worse, using "Microsoft Excel" as a target/source isn't foolproof either because it behaves differently between the Mac and the Windows version!

JSON in contrast is clearly defined in all of these issues and has battle-tested implementations in every programming language there is.

   The values in each record are separated by the DELIMITER character. If the value contains the delimiter character, the QUOTE character, the NULL string, a carriage return, or line feed character, then the whole value is prefixed and suffixed by the QUOTE character, and any occurrence within the value of a QUOTE character or the ESCAPE character is preceded by the escape character.

Having the control characters mixed in with the data, and having no defined encoding.

I agree with the GP’s point that people should probably just learn SQL, but there are things about SQL that are objectively bad, especially from the perspective of a software developer.

It’s not a very composable or consistent language, so I think it makes total sense that we see so many abstractions over it.

> People who work with it all the time, don't even think about it past the first week.

I think SQL is often quite awkward, at least when you look at people exploring alternatives, like https://prql-lang.org/

Not just “the standard” variety, but also all of the vendor specific varieties that you’ll use in practice (MySQL/MariaDB, PostgreSQL, SQLite, Oracle, SQL Server etc.) and how the features offered by each differ, how the support for custom types differs, how the procedural languages differ, the actual experience of using them, seemingly half baked error messages when compared to most imperative languages, varying support for getting the query optimiser to do what you want (e.g. hints), query plans that aren’t pleasant to stare at, often no support for automated analysis of how things are running and suggestions for indices (e.g. Oracle had that sort of feature, it is sometimes helpful, but the automatically generated indices are or at least last I checked were treated as their own special thing and you couldn’t easily delete them). Even things like varying support for working with geospatial data, JSON or time series data.

Not just that, but also the tooling (or the lack of it) - good luck debugging the execution of stored procedures in your DB or placing breakpoints in there, good luck hooking up observability/tracing solutions as easily as you would for your back end, good luck debugging why your database link calls take 100x longer when executed through JDBC in your app but not in the developer tooling.

Not that ORMs or NoSQL make everything much better, you’d just trade one set of annoyances for another, especially when you end up trying to generate dynamic SQL with the likes of myBatis XML mappers. Don’t get me started on people over fetching or ending up with N+1 problems with most ORMs, or executing bunches of queries against the DB just to retrieve some data that you might as well get in one go with a well crafted DB view. Not that you can’t make your DB schema a mess as well with a liberal application of EAV or OTLT (or just attempting to make polymorphic links).

I think SQL as a language makes sense, but PRQL often feels more sensible. I feel like the current RDBMSes out there are very powerful, but that there are also aspects about them (and the differences between the ones you’ll use) that absolutely suck. It feels like where back end languages are getting progressively better DX, databases instead gave us… CTEs? Some syntactic sugar for JSON in PostgreSQL? Feels like they move painfully slow.

For what it's worth, that's why I welcome every new tool or attempt at improving things, even if they won't replace the usual enterprise stacks of having some RDBMS, a JDBC/ODB/whatever driver and probably an ORM on the back end.

I spent 5 years working on a SQL database (materialize.com) and I think SQL is awful.

https://www.scattered-thoughts.net/writing/against-sql (by another former Materialize employee) is a good takedown.

I like Go a lot and can work with it's error handling paradigm, but I still often wonder if the same semantics could be accomplished with less verbosity in a way that makes the underlying algorithm more clear when reading the code.

Take the simple example of joining via foreign key to another table in a parent child relationship. SQL makes you redundantly specify the other table even though it is fully defined by the foreign key relationship. Purists will say "what if you actually want to join on some other column" or "what if there are multiple foreign key relationships" and a plethora of other "what ifs" and all these ignore the reality that 99.99% of the time it is completely unambiguous and they are just happy that millions of developers all around the world are uselessly typing redundant terms into queries. This is what leads people to eventually say "screw it, I'm scratching that itch" and we have "yet another abstraction layer".

I did it -- posting it here: https://github.com/Qbix/Db

There's no reason not to post it.

SQL is based on relational algebra. Poorly some would argue.

If I have to go look at log files to see if the intermediary tool is doing the right thing, I might as well just write the SQL myself, right? jq has more use cases dealing with JSON that could come in handy. sq seems to handle trivial use cases but for complicated data models its going to be a hindrance.