Hacker News new | past | comments | ask | show | jobs | submit login
Show HN: pgcmd – An alternative to psql with JSON output (github.com/soheilpro)
76 points by soheilpro on Aug 22, 2019 | hide | past | favorite | 35 comments



What is the motivation behind writing tools like these in JavaScript? Not everyone is going to have Node or NPM. I don't want to first install NPM to install a utility.

But Perl/Python/Ruby are very likely to be present on more number of systems (Windows being an exception). Why aren't more of these utility tools written in Perl/Python/Ruby?


Perl and Python perhaps, but what systems ship with Ruby by default.

But anyway even if your OS ships with perl/python/ruby installed by default (and also the right version) it probably doesn't ship with whatever Postgres library they're using so you'll still have to deal with extra dependencies anyway. And talking about Windows, installing node tools on Windows with npm is on the whole much easier than doing the same with Python/Perl/Ruby, so there is that.

If you want to make an argument that it should have been written in some language that easily compiles down to a single binary you can just download and run, then perhaps people would agree with you. But I cannot see how Node is in any way worse, harder or less common than Perl/Python/Ruby.


Perl and Python perhaps, but what systems ship with Ruby by default.

OSX comes with Ruby and has for ages, although I think that's being phased out.

But I cannot see how Node is in any way worse, harder or less common than Perl/Python/Ruby.

Node has been notoriously difficult to get running on FreeBSD, for instance. Patches were submitted and just sat on by the maintainers. Something like Perl/Python/Ruby let you target a POSIX-ish system easily, Javascript does not (case in point: electron).


Can you expand on how electron relates to the argument that JavaScript does not easily target POSIX? Isn’t the issue the runtime (Node) not some GUI “library” (electron)?


> What is the motivation behind writing tools like these in JavaScript?

I mean it's probably as simple as the person writing it being most familiar with JavaScript. There are a LOT of javascript developers who have never used perl/python/ruby before. They're not going to learn another tool just to write the tool they want.

From a technical perspective, node also has much platform support (notably on windows) than perl/python/ruby.


The nicest thing to do is release binaries for all major platforms (dependency free if possible). Then code it in whatever you want.


Go rewrite it in your right language, nobody cares about haters.


This wasn’t a hate post. It was a legitimate question. My systems have python by default. Why do I want to add another dependency for node plus it’s inherent requirement for internet access to drag all the dependencies?


The answer is: because the author of the tool is comfortable in the language, wrote it for their use case and have NPM and node already set up. They were nice enough to share it so other people can use it too or port it to their favorite language.


Sure they have python, but which version of python do they have? 2? 3? 3.5? 3.7?

Arguably writing command line tools in Node.js has similar problems (8? 10? 12? 12.9?) though it has the advantage that not having a baked in version into your OS distribution means you have to install one yourself (which hopefully will match).

I think Go is a better choice for this type of thing as the end result is a copy-and-run-anywhere staticly linked binary.


>> This wasn’t a hate post. It was a legitimate question.

Seen through a different lens, it's actually a compliment.

The poster sees the utility in the tool, even though it's not built on one of the poster's preferred platforms.

But the great thing is that the OP shared the source code on Github, so it can be ported to other platforms... if one is motivated enough to do so.


> it’s inherent requirement for internet access to drag all the dependencies

Isn't this required by package managers of all languages? pip/compose/gems all require internet access.


I can see this being convenient. I'm surprised that json isn't one of the output formats for pgcli, given that there are already tons of formats including one optimized for Jira comments. (https://www.pgcli.com/)


TIL. I use pgcli daily and it's truly awesome software. My favorite trick is the old Ctrl-X Ctrl-E to edit the query on VIM.


Cool! Now, can we just always use postgresql: scheme URIs for connection information? Also, no passwords on the command-line please -- use a password file, or prompt. Also, please add GSS-API support and such when you can -- or wait till this is popular and others do it for you.


Another approach to this is wrapping the query in a `copy to stdout` statement (either in the .sql or with a tiny wrapper), then converting the CSV to JSON.

If you're doing the processing with jq and don't want to install any other tools than that, a jq csv -> json script should be about 10 lines.


Ew ew ew, that's an ugly hack if ever there was one. Anything involving CSV is guaranteed to have a nasty bite, as soon as you get CSV's "control characters" (which ones? Ha, trick question! There's about 6 sets of them in common use, almost but not entirely different.) in the mix.

TL;DR: CSV brittle, do not want.


Believe me, I could sing songs of pain about "CSV", its various delimiters, escapes and encodings. Makes you almost wish for the horrible format garbage that is Excel…

But in this case you're not messing with some arbitrary CSV, you're generating it yourself right before converting into another horrible format. Slightly better.


psql with right query and right flag can do that.


Can you share that query and flag please?


I was sure most of this existed so I looked it up... Mostly centers around adding row_to_json() to the query. Using the example from the github repo:

    $ psql -P pager -nqtc "select row_to_json(pg_database) from pg_database where datname = 'template0'" | jq
    {
      "datname": "template0",
    ...
      "datacl": [
        "=c/postgres",
        "postgres=CTc/postgres"
      ]
    }


Use --tuples-only / -t and --no-align / -A. Or -tA for short.

For example:

  $ psql dbname -tAc 'SELECT json_agg(some_table) FROM some_table'
That will produce a valid JSON if you ever need to pipe it to another command. And I'm sure, psql will do the job a lot faster than node.

By the way, you should not underestimate the built-in psql power and postgresql's json capability. Postgresql's JSON power rivals that of other JSON based No-SQL databases.


What, besides being implemented in Node, makes this substantially different from using the built in json_agg function from Postgres, as in:

https://stackoverflow.com/questions/24006291/postgresql-retu...


I made this because I have a bunch of .sql files that I want to run against Postgres and then process the results with other tools. I cannot simply go and add json_agg to them.


You can’t select * into json_agg


Does

    SELECT json_agg(row_to_json(*))
work?


There's also json_build_object for building a JSON object and then you can use json_agg inside that.


    select array_to_json(array_agg(row_to_json(t))) from (select * from name) t
seems to work...


Just to be clear your “library” is literally one line of code:

  console.log(JSON.stringify(rows, null, 2));
So what’s the point?


Yes, it's essentially one line of code but it makes my daily routine of running queries against Postgres and then processing the results with other tools much easier.


I would be willing to bet that you can do everything you need to entirely with in Postgres/psql/PGPLSQL.


Yeah, tempted to post all my scripts (in various languages) here which are over 100 LOC on average. There are hundreds.


Welcome to Node


I guess the reason this is getting downvoted is because there isn't sufficient information in the comment. It's true, however.

I find this is rampant in the Node community - For any small feature, rather than making a PR or suggesting changes to the original module (maybe via an option), the goal is to create a new library.

I feel what the Node community doesn't realize is that this creates a dependency hell where I can't run my programs which were running a week back, by pulling in the latest libraries.

Another example I came across recently was redux-thunk[0], which is used practically wherever redux is used. All it does is add a callback!

This is entire code of redux-thunk library!

  function createThunkMiddleware(extraArgument) {
    return ({ dispatch, getState }) => next => action => {
      if (typeof action === 'function') {
        return action(dispatch, getState, extraArgument);
      }
  
      return next(action);
    };
  }
  
  const thunk = createThunkMiddleware();
  thunk.withExtraArgument = createThunkMiddleware;
  
  export default thunk;
[0]: https://github.com/reduxjs/redux-thunk


feel what the Node community doesn't realize is that this creates a dependency hell

You misunderstand the dynamic here. In the Node community you get bragging rights for how many “libraries” you have written and how many times they have been downloaded (99.99% of which will be by automated build systems, usually without the end user even being aware of any individual libraries except at the top level). These entangled dependencies are the entire point.




Consider applying for YC's Spring batch! Applications are open till Feb 11.

Guidelines | FAQ | Lists | API | Security | Legal | Apply to YC | Contact

Search: