8 ms·
Show HN: pgcmd – An alternative to psql with JSON output
- llarsson 7y agoWhat, 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-return-result-set-as-json-array/24006432 https://stackoverflow.com/questions/24006291/postgresql-retu...
- soheilpro 7y agoI 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.
- teej 7y agoYou can’t select * into json_agg
- striking 7y agoDoes SELECT json_agg(row_to_json(*)) work?
- newaccoutnas 7y agoThere's also json_build_object for building a JSON object and then you can use json_agg inside that.
- eddyg 7y agoselect array_to_json(array_agg(row_to_json(t))) from (select * from name) t seems to work...
- byteshock 7y agoJust to be clear your “library” is literally one line of code: console.log(JSON.stringify(rows, null, 2)); So what’s the point?
- soheilpro 7y agoYes, 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 7y agoI would be willing to bet that you can do everything you need to entirely with in Postgres/psql/PGPLSQL.
- techntoke 7y agoWelcome to Node
- thunderbong 7y agoI 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 https://github.com/reduxjs/redux-thunk
- bagol 7y agopsql with right query and right flag can do that.
- daniub 7y agoCan you share that query and flag please?
- kbaker 7y agoI 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" ] }
- bagol 7y agoUse --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.
- Renevith 7y agoI 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 7y agoTIL. 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.
- travislane 7y agoWhat 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 7y agoPerl 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 7y agoPerl 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 7y agoCan 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)?
- paulcarroty 7y ago
- cryptonector 7y agoCool! 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 7y agoAnother 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 7y agoEw 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 7y agoBelieve 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.