Sq.io: jq for databases and more

[rout39574]

I love JQ. But ... I'd never considered its query language to be particularly admirable. If I want to ask questions of some databases, I don't understand why I'd choose JQ's XPATH-like language to do it.

[VMG]

What I love about `jq` that I can edit my query (or "program") by appending tokens at the end. Similar to unix pipes. With plain SQL that is not easy.

[AlphaSite]

I think for certain types of data manipulation and querying it’s notable more succinct, sql with CTEs is a little better but still far more verbose than data piping.

[hnbad]

Presumably the target audience is people who already frequently use JQ and don't want to juggle different query languages when dealing with different data sources?

[fer]

Same, if anything I'd look for a SQL-like language for JSON.

[lucideer]

This!

JQ is great because JSON didn't have a query language & needed one. JQ isn't the best query language - it's just the one that won adoption.

DBs already have query languages that are mostly superior to JQ's.

[baq]

honestly I'd rather have sql for json than jq for a database... guess this is exactly what clickhouse-local does

[dewey]

Sometimes I wonder if it wouldn't be more efficient for people to just learn SQL instead of trying to build tools or layers on top of it that introduce more complexities and are harder to search for.

[remon]

HN is inundated with posts announcing paper thin abstractions on top of existing technology or utilities that just move the goalpost of what you knowledge you need to be effective. It's a weird trend that seems almost entirely motivated by people wanting open source projects in their resume, or seek funding if its a startup.

[goosejuice]

There are a several features here that go beyond 'learn SQL'. I don't see how you could jump to this conclusion if you read the docs.

Why does pgcli exist. What about dbeaver, datagrip, pgadmin, postico, LINQ, ORMs? It's almost as if people value different interfaces to databases.

Maybe it's not for you, but it's not hard to imagine that someone, beyond the author, might find it useful. Maybe it's just me but to dismiss those people as individuals that should just 'learn SQL' is a pretty rude thing to say.

[krosaen]

I know sql pretty well and still find value in this kind of tool - creating schemas and inserting data is a clunky part of sql - the query language is where it really shines. So I can imagine using this to quickly insert some data or to get familiar with the schema and then dive in with normal sql queries.

[8338550bff96]

Lots of people struggle to understand the declarative programming language paradigm. It is really pesky because since you're declaring what kinds of results you expect rather than dictating what must be done, you're forced to define your boundary conditions up-front. Much more fun and exciting to charge ahead without worrying about such things

[raydev]

Sometimes I wonder if it wouldn't be more efficient for people to just learn assembly instead of trying to build tools or layers on top of it that introduce more complexities and are harder to search for.

[fforflo]

Paraphrasing Spencer:

Those who don't understand SQL are doomed to reinvent it, poorly

[Xenoamorphous]

It's funny because recently a "full stack" dev who's in reality 95% frontend was telling me he's not a fan of Tailwind and that he'd rather learn "proper" CSS.

And the irony is of course, he never wants to use a relational DB to avoid SQL, so No-SQL DB it is.

[matt_s]

The sq and jq tools are both neat command line gimmicks but in my workflow their usefulness is very short. I can't imagine using sq on a query involving a handful of tables and some inner/outer joins. How would I know its outputting the correct SQL? If you mess up joins you end up with bad output.

Here's my theory: some developers see simple languages that are easy to learn and want to build something more complex to output that language. Maybe its a sub-conscious thing.

HTML is another one, if explained simply, HTML and CSS basic use cases should be easy-ish to pick up by nearly anyone. The fact that we have so many over-engineered frameworks and libraries out there that generate these is evidence of over complicating simple things. Maybe its called resume driven development? Maybe people see genuine useful frameworks that get wide adoption and are wannabes?

[_hyn3]

I tend to agree! but this seems to have a subtly different use case. It's actually very cool. I can see this being a good addition to my toolbox.

[zmmmmm]

I think it'd be a moot point if SQL wasn't painful and awkward to work with in the first place. But database purists control it and won't let go, so we will have to live with everyone else inventing layers to make their lives easier.

[quotemstr]

Yeah. Alternatives to long-established and useful technologies have to meet a high bar before their option pays for their disruption.

Things that seem potentially worth it to me:

* seL4

* Google's SQL syntax tweak

* Rust

* GraalVM

* systemd

* Tree sitter

* LSP

* CMake

* Bazel

These all get you a step change improvement in comprehensibility, safety, or something else important.

Things that seem like more churn than they're worth:

* Noise protocol (relative to TLS)

* JMAP (compared to good old IMAP)

* Nim/Zig/etc.

* Wayland (fait accompli now, but still)

* Varlink

* Fish shell

* YAML/TOML

* Sq?

* Meson

I wish we, as an industry, invested more in improving existing technologies instead of continually abandoning and replacing working solutions.

[mschuster91]

SQL interop is where the pain is at. Using standard tooling of most database systems, best you can get is CSV with all the pains this shithole of a data transfer format brings.

[rvalue]

Sometimes I wonder why these SQL standards cost so much to burn a hole in a millionaire's pocket.

[jasongill]

This is interesting. I wonder if there is anything that does the opposite - takes JSON input and allows you to query it with SQL syntax (which would be more appealing to an old-timer like me)

[eproxus]

This tool can do that:

    $ cat example.json | sq sql 'SELECT * FROM data'
    actor_id  first_name  last_name  last_update
    1         PENELOPE    GUINESS    2020-06-11T02:50:54Z
    2         NICK        WAHLBERG   2020-06-11T02:50:54Z

One of the data source types is 'json' and the command 'sq sql ...' lets you execute SQL directly instead of using their default query language (SLQ).

[minikomi]

duckdb!

https://duckdb.org/docs/extensions/json.html

[kwailo]

clickhouse-local is incredible, and, in addition to JSON, support TSV, CSV, Parquet and many other input formats. See https://clickhouse.com/blog/extracting-converting-querying-l...

[zie]

In PostgreSQL, you can just select against JSON and query away to your hearts content. They have JSON data types and functions to work on it.

[nbk_2000]

Octosql does this as well as a few other formats. I've found it useful several times.

https://github.com/cube2222/octosql

[snthpy]

This question has come up a few times in this thread. However I don't see how people expect this to be possible unless they are talking about ndjson with flat records. JSON in general is a very nested format so languages based on relational algebra/calculus like SQL and PRQL are not going to be that useful unless the data is flattened and normalised first.

[fmajid]

https://github.com/postgrespro/jsquery

[ikari_pl]

Postgres:) load It into a jsonb column and the possibilities are endless, including indexing

[beembeem]

let me introduce you to this hot, open source, nosql database that's webscale...

[Summerbud]

To be honest, JQ is handy but it's so hard to maintain. I found myself not able to fully read other's JQ related script

[rurban]

Better would be the reverse. SQL queries over json: octosql.

[renewiltord]

Related is Google’s pipe syntax for SQL https://research.google/pubs/sql-has-problems-we-can-fix-the...

[robertclaus]

More tools are always great! Even if it doesn't become the mainstream, it's always great to see people explore new ways of dealing with databases!

[prepend]

More good tools are always great. But I don’t think random clutter is always good.

Fortunately we don’t have to see it so it’s not like it blocks my vision.

But I just wanted to note that the idea of “anything is good” is not really true and I don’t like its spread as there’s opportunity cost. I think we need to spend more attention on evaluation and quality and making good things than the idea that even creating lots of bad things is good in some way.

[pratio]

Though I respect and applaud the effort that went into creating this and successfully releasing it, It has fewer features than duckdb supports at the moment.

Duckdb supports both Postgres, Mysql, SQLite and many other extensions.

Postgres: https://duckdb.org/docs/extensions/postgres

MySQL: https://duckdb.org/docs/extensions/mysql

SQLite: https://duckdb.org/docs/extensions/sqlite

You can try this yourself.

1. Clone this repo and create a postgres container with sample data: https://github.com/TemaDobryyR/simple-postgres-container

2. Install duckdb if you haven't and if you have just access it on the console: https://duckdb.org/docs/installation/index?version=stable&en...

3. Load the postgres extension: INSTALL postgres;LOAD postgres;

4. Connect to the postgres database: ATTACH 'dbname=postgres user=postgres host=127.0.0.1 password=postgres' AS db (TYPE POSTGRES, READ_ONLY);

5. SHOW ALL TABLES;

6. select * from db.public.transactions limit 10;

Trying to access SQL data without using SQL only gets you so far and you can just use basic sql interface for that.

[_hyn3]

Duckdb is different, though. Not having tried SQ but it seems like a better tool for quick declarative data-munges/parsing/etc, while Duckdb is more of a real project tool with real SQL.

https://duckdb.org/docs/api/cli/

[mritchie712]

not to mention the dozen+ other sources DuckDB supports (Iceberg, Parquet, CSV, Delata, JSON, etc.).

DuckDB extension support / dev experience is quite good now too. I've been working on some improvements (e.g. predicate pushdown) to the Iceberg extension and it's been pretty smooth.

[Gbox4]

If "sq" is pronounced "seek", then is "jq" pronounced "jeek"?

[candiddevmike]

This is neat but I'm not really seeing anything I can't do with standard SQL and CLI tools like psql. Seems like you'd learn more reusable things using standard SQL too.

[varenc]

I find sq handy when you use it to accomplish things you can't (easily) do with just raw SQL. Things like: exporting certain rows to JSON or CSV, transforming rows into nicely formatted log lines for viewing, or reading in a CSV file and querying it the same way you'd query other databases. It's particularly easy to start using if you're already familiar with the jq.

If you use things like `array_to_json(array_agg(row_to_json(....)))` in your psql commands to output some rows to JSON, then sq's `--json` or `--jsonl` is quite a bit easier IMHO. If you know the exact SQL query you want to run you can just do `sq sql '....'` as well, but I agree there's not much point in doing that if you aren't taking advantage of some other sq feature.

[neilotoole]

> I'm not really seeing anything I can't do with standard SQL and CLI tools like psql.

Developer here. There's a few features other than the query stuff that I still think are pretty handy.

The "sq inspect" stuff isn't easy to do with the standard CLI tools, or at least wasn't when I started working on sq back in 2013 or so.

https://sq.io/docs/inspect

I also regularly make use of the ability to diff the metadata/schema of different DB instances (e.g. "sq diff @pg_prod @pg_qa").

https://sq.io/docs/diff

[hvenev]

The demo appears too stateful for me. The real power of `jq` is its reliability and the ability to reason about its behavior, which stateful tools inherently lack.

[peter_d_sherman]

First there was shell scripting, then grep, then sed, then awk, later Perl... well, now there's 'sq'!

Looks like an absolutely great (and necessary!) utility, which will automate many future workflows and dataflows, save countless hours of time collectively for many people en masse, and therefore change the world (allow more people to get more done in less time!) much like Unix, shell scripting, grep, sed, awk and Perl gave the world...

Congratulations on writing what no doubt will become one of the major Unix/Windows/MacOS/Other OS/Linux shell scripting commands in the future, if it isn't already!

Well done!

[varenc]

I love sq. It's handy for quickly performing simple operations on DBs and outputting that as CSV or JSON. Though my one wish is that the sq query language (SLQ) supported substring matching like SQL's `... LIKE "SOME_STRING%"`. Though you can just invoke SQL manually with `sq sql`

[neilotoole]

Developer here. Thanks for the kind words.

Substring matching is on my short list (also totally open to a PR!).

[dartos]

Wow what an expensive domain name.

[gampleman]

It still seems to me a better solution to these sorts of problems is to use a better shell like nushell, that has richer datatypes, and so you can use the same tool to manipulate files, processes, json, csv, databases and more.

[lightningspirit]

Although jq query style is not absolutely pleasant I see many examples where this tool can be used such as data transformation, import/export and linux pipelines that need access to databases.

[novoreorx]

I really like the idea of https://github.com/dinedal/textql, which uses SQL to interact with file-based data stores. However, I don't understand why sq does the opposite—using a new DSL to access a database that already has a widely-adopted and easy-to-use language: good old SQL.

[lnxg33k1]

It is great, I installed it, only thing I'd suggest, probably minor, is to also extract the commands to install from the bash script, and put them in the `Install` section directly, I don't run .sh script, especially if they need privileges, so I went through the bash script to take the commands for debian, they're there, probably could also be outside for other kind of people

[neilotoole]

You can already install sq using several of the common package managers, or build from (Go) source if you prefer.

https://sq.io/docs/install

[lionkor]

For anyone else wondering; it's written in Go, and it keeps state inside its config file, for example sources (like a db connection string).

[wreq2luz]

I was reading about something like json output coming to Postgres one day (https://www.postgresql.org/message-id/flat/ZYBdnGW0gKxXL5I_@...). Also the `.wrangle | .data` wraps on an iPhone 13 mini.

[tmountain]

Even without a JSON column in Postgres, this is pretty trivial: SELECT jsonb_pretty(to_jsonb(employees)) FROM employees;

[nashashmi]

> sq is pronounced like seek. Its query language, SLQ, is pronounced like sleek

As a person who is apart from the tech scene, and lurks in the tech space out of interest, I appreciate this guidance. For the longest time I didn’t know nginx was pronounced Engine-X; I called it N-jinx.

[neilotoole]

The theory at the time was that if "SQL" is pronounced like "sequel", and "sq" is just dropping the "L" from "SQL", then "sq" must be...

I suspect the uptake on the "seek" pronunciation is about 2%, if I'm being generous

[wvh]

Don't sweat it. It's a running joke amongst guitar players no two people pronounce D'Addario the same way, not to mention the tremolo bar which technically should be called a vibrato bar. I surmise any scene has its trip-up words.

[tgmatt]

Sorry but I am pronouncing that as 'ess-cue` and there is nothing anyone can do about it.

Looks kinda neat for when I don't want or need anything more than bash for a script.

[mlhpdx]

Dang, I wish I had this while I still had SQL databases.

[fforflo]

I love the idea of pushing JQ and other DSLs close to the database. I've written jq extensions for SQLite [0] and Postgres [1], but my approach involves basically embedding=pushing the jq compiler into the db. So you can do `select jq(json, jqprogram)` as an alternative to jsonpath.

Trying to understand: Is the main purpose of this to use jq-syntax for cataloging-like functionality and/or cross-query? I mean it's quite a few lines of code, but you inspect the database catalogs and offer a layer on top of that? I mean, how much data is actually leaving the database?

[0] https://github.com/Florents-Tselai/liteJQ [1] https://github.com/Florents-Tselai/pgJQ

[mynameyeff]

Wow, very cool. I was looking for something like this

[mrbluecoat]

TSV support might be nice for Zeek logs

[franchb]

Maybe integrate with https://github.com/brimdata/zed ?

[cassepipe]

Not to be confused with the gpg alternative from sequoia-pgp also called sq : https://sequoia-pgp.org/

[neilotoole]

That's an unfortunate naming clash. This "sq" (sq.io) predates the sequoia "sq" by several years I believe.

[doctorpangloss]

At some point, why not package Python into a single executable, and symbolic link applications and modules into it for Unixy-ness?

Another POV is all the developers I know who thrive the most and have found the most success: they rate aesthetic concerns the lowest when looking at their tools. That is to say that the packaging or aesthetic coherence in some broader philosophy matters less than other factors.

[sweeter]

Its written in Go...