11 ms·
Sq.io: jq for databases and more
- mlhpdx 2y agoDang, I wish I had this while I still had SQL databases.
- fforflo 2y agoI 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 https://github.com/Florents-Tselai/liteJQ [1] https://github.com/Florents-Tselai/pgJQ https://github.com/Florents-Tselai/pgJQ
- candiddevmike 2y agoThis 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 2y agoI 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.
- maxfurman 2y agoYou may know this already, but the SQLite CLI can actually read and query data directly from a csv file, with the right flags
- ec109685 2y agoThey are Jedi’s at jq though.
- aurareturn 2y agoI agree with @candiddevmike. Might I add that you can do those things fairly easily now with ChatGPT/Claude. I doubt LLMs know how to use Sq.io that much.
- dangitman 2y ago[dead]
- neilotoole 2y ago> 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 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 https://sq.io/docs/diff
- renewiltord 2y agoRelated is Google’s pipe syntax for SQL https://research.google/pubs/sql-has-problems-we-can-fix-them-pipe-syntax-in-sql/ https://research.google/pubs/sql-has-problems-we-can-fix-the...
- robertclaus 2y agoMore 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 2y agoMore 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.
- varenc 2y agoI 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 2y agoDeveloper here. Thanks for the kind words. Substring matching is on my short list (also totally open to a PR!).
- tgmatt 2y agoSorry 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.
- doctorpangloss 2y agoAt 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 2y agoIts written in Go...
- rout39574 2y agoI 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.
- hnbad 2y agoPresumably 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?
- neilotoole 2y agoDeveloper here. That was exactly the target audience. Note that sq doesn't just handle relational DBs, it also has (varying quality) support for CSV, JSON, Excel, and so on. At the time (2013) I wasn't aware of a convenient one-liner mechanism for munging all of those together from the command line.
- AlphaSite 2y agoI 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.
- larodi 2y agoA reply by s.o. who sides with your (potentially unpopular) opinion. With all due respect to DSL languages, IMHO only few people can get on this APL-level of abstraction and cryptic choice for APIs..., and would have the nerves to write it. From learning perspective JQ seems much more difficult than RegEX for example, its learning curve is potentially steeper than that of CSS and XPATH, which are other examples for querying tree-like-structs. While LLMs are welcome to write it (the jq) for me, my work has relatively little JSON transformations, and for the most part handling these in python/js/perl is okay as. Stating all this with much fascination for the JQ language itself, as technology, but not as a tool that I find the need for on a daily basis. Besides for me it is much more easier to feed and transform data into Postgres (or even SQLite, which can also be challenging), rather than crunch it w/pandas or R where you can also find fourth generation language capabilities, but performance lags.
- hvenev 2y agoThe 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.
- cassepipe 2y agoNot to be confused with the gpg alternative from sequoia-pgp also called sq : https://sequoia-pgp.org/ https://sequoia-pgp.org/
- neilotoole 2y agoThat's an unfortunate naming clash. This "sq" (sq.io) predates the sequoia "sq" by several years I believe.
- wreq2luz 2y agoI was reading about something like json output coming to Postgres one day (https://www.postgresql.org/message-id/flat/ZYBdnGW0gKxXL5I_@msg.df7cb.de https://www.postgresql.org/message-id/flat/ZYBdnGW0gKxXL5I_@...). Also the `.wrangle | .data` wraps on an iPhone 13 mini.
- jasongill 2y agoThis 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)
- minikomi 2y agoduckdb! https://duckdb.org/docs/extensions/json.html https://duckdb.org/docs/extensions/json.html
- danielhep 2y agoI'm using DuckDB to parse GTFS data, which comes in a CSV format. It works wonderfully.
- kwailo 2y agoclickhouse-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-local-files-with-sql-clickhouse-local https://clickhouse.com/blog/extracting-converting-querying-l...
- ramraj07 2y agoWhy is this better than DuckDB?
- kitd 2y agoWhy is DuckDB better than clickhouse-local?
- snthpy 2y agoduckdb is shorter to type than clickhouse-local and at the command line brevity is king! Of course the winner here is chdb! (And don't talk to me about shell aliases) :-p While on the topic, how exactly does chdb relate to clickhouse-local?
- mynameyeff 2y agoWow, very cool. I was looking for something like this
- mrbluecoat 2y agoTSV support might be nice for Zeek logs
- franchb 2y agoMaybe integrate with https://github.com/brimdata/zed https://github.com/brimdata/zed ?
- lionkor 2y agoFor anyone else wondering; it's written in Go, and it keeps state inside its config file, for example sources (like a db connection string).
- Summerbud 2y agoTo 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
- tmountain 2y agoEven without a JSON column in Postgres, this is pretty trivial: SELECT jsonb_pretty(to_jsonb(employees)) FROM employees;
- rurban 2y agoBetter would be the reverse. SQL queries over json: octosql.
- dartos 2y agoWow what an expensive domain name.
- dewey 2y agoSometimes 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 2y agoHN 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.
- dlisboa 2y ago> It's a weird trend that seems almost entirely motivated by people wanting open source projects in their resume That’s really harsh and misguided. If people didn’t do “paper thin abstractions” projects on their own time for the simple pleasure of doing it we wouldn’t have 90% of the successful projects we have today. Let people have fun and don’t judge their motives when they’re making something Open Source. I can guarantee the person just thought “this would be cool to have” and implemented it.
- 6LLvveMx2koXfwn 2y agoUnless you're the GP your guarantee about the persons motivation is as meaningless as the post you're replying to.
- ddispaltro 2y agoI think his point is that we should treat something given freely, charitably
- EGreg 2y agoCharitably? This! Is! HN!
- pratio 2y agoThough 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 https://duckdb.org/docs/extensions/postgres MySQL: https://duckdb.org/docs/extensions/mysql https://duckdb.org/docs/extensions/mysql SQLite: https://duckdb.org/docs/extensions/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 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&environment=cli&platform=macos&download_method=package_manager 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.
- mritchie712 2y agonot 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.
- _hyn3 2y agoDuckdb 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/ https://duckdb.org/docs/api/cli/
- lightningspirit 2y agoAlthough 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.
- gampleman 2y agoIt 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.
- nashashmi 2y ago> 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.
- wvh 2y agoDon'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.
- deskr 2y agoD'Addario is of course pronounced "Dadda Rio", with emphasis on Rio and a slight Italian accent.
- neilotoole 2y agoThe 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
- Gbox4 2y agoIf "sq" is pronounced "seek", then is "jq" pronounced "jeek"?
- lnxg33k1 2y agoIt 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 2y agoYou can already install sq using several of the common package managers, or build from (Go) source if you prefer. https://sq.io/docs/install https://sq.io/docs/install
- novoreorx 2y agoI really like the idea of https://github.com/dinedal/textql 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.
- peter_d_sherman 2y agoFirst 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!