
Show HN: pgcmd – An alternative to psql with JSON output - soheilpro
https://github.com/soheilpro/pgcmd
======
travislane
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?

~~~
dagw
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.

~~~
inferiorhuman
_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).

~~~
IggleSniggle
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)?

------
Renevith
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/](https://www.pgcli.com/))

~~~
ORioN63
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.

------
cryptonector
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.

------
mhd
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.

~~~
Piskvorrr
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.

~~~
mhd
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.

------
bagol
psql with right query and right flag can do that.

~~~
daniub
Can you share that query and flag please?

~~~
kbaker
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"
          ]
        }

------
llarsson
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...](https://stackoverflow.com/questions/24006291/postgresql-return-
result-set-as-json-array/24006432)

~~~
teej
You can’t select * into json_agg

~~~
striking
Does

    
    
        SELECT json_agg(row_to_json(*))
    

work?

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

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

    
    
      console.log(JSON.stringify(rows, null, 2));
    

So what’s the point?

~~~
soheilpro
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.

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

