Postgres CLI with autocompletion and syntax highlighting | Hacker News Reader

Postgres CLI with autocompletion and syntax highlighting

github.com

304 pointsdmmalam45 comments

The author also recently completed a kickstarter for the same thing for MySQL: https://www.kickstarter.com/projects/1839007321/mysql-cli

It's been in private beta for backers for a while. I forget when it's going public, but I don't think it's far away...

I'm the author.

I'm hoping to go public with the MySQL version before the end of this month. So a week or so. :)

This was so good I rolled it into my default vagrant deploy script, outstanding work my friend.

This looks awesome man. Keep up the great work :)

I like the idea. It lacks some important functionality e.g. \x does not work. This is a feature I use regularly in the psql command line tool.

Also, I'm not sure if it's a bug, but with Smart Completion set to off, it still auto completes as you are typing. This can get annoying after a while. Lastly, if you type ';' followed by enter, the program crashes.

Can you tell me which version of pgcli you're running? Can you try upgrading?

\x is implemented and available in the latest version. The ';' enter bug is also fixed in the latest version.

We're still slowly adding more of the back-slash commands supported by psql.

Also autocomplete doesn't seem to work with an alias of a quoted table, in case of using keywords as table names or having casing in names, e.g.:

    SELECT * FROM "access" a WHERE a.

But it's a nice tool! (:

That is a bug. I've filed an issue on your behalf.

https://github.com/dbcli/pgcli/issues/283

Thank you for catching that.

wow \x is great, I never knew about that.

For the record, the standard psql already supports auto-completion, albeit with much less pretty interface (a common readline-style horizontal candidate listing). Syntax highlighting seems great, though.

Perfect project for an animated gif in the README (if that's possible), would have been better to showcase the product.

Done! :)

I've been using it for months, it's definitely an improvement over psql. I recommend it to everybody.

Informmix isql tool used to have a form mode, where one could:

* select table or form from a list, hit enter

* then a form with all fields would show up

* one could populate some fields with query condition, e.g. name = 'xyz'

* then hit enter and tool would run a query

* then one could navigate prev/next through the results.

I was not fond of this, but some folks in the office still swear by this and ask if they can have a tool like that for postgres. I sent them to psql, but just typing SQL gives them heartburn.

I must admit some of them were quite faster with isql than with psql. This tool helps a bit, but still hoping for the form mode.

Have you tried pgadmin, it even has a 'visual' query builder.

My support folks have only terminal access. Sure, they could tunnel pgadmin in (via SSH) if they knew how and even what they are doing. As such they are stuck with command line only.

I've been using this for a while and it's great. The author is also excellent at running open source projects. Great mentality. I thought this was posted to HN a while ago but I could be mistaken. Definitely worth the re-post if it was.

I have been using this in production for a few weeks now and I quite like it. A real improvement over most of the CLI interfaces I have even with my expanded and frankly bloated .psqlrc.

I was following the design philosophy of fish shell http://fishshell.com/docs/current/design.html#design-overvie...

Especially these two:

* Configurability is the root of all evil

* The law of discoverability

This is great! I'm thrilled to finally see something like this.

I have a question, mostly about MySQL version of it: have you made some effort to natively support SSH port-forwarding/tsocks with this client? Because my experience is that it really works bad with the default client. But, TBH, I don't even know how much it depends on the client/server-side.

There is no native support for ssh port forwarding. But I do support connecting via a socket.

Wow, well done! So much better than psql, thanks!

This is wonderful. I have struggled with the lack of real readline compatibility in the PG CLI, this is a huge upgrade.

What emacs mode is that in the screenshot?

That's the keybindings to use. For example when emacs mode is selected, you can use C-a to jump to home or C-e to jump to the end of the line.

If it is changed to Vi mode then you can hit escape to go to normal mode and then use '0' to jump home or '$' to jump to the end etc.

This is great work, but I can't figure out how to configure the colors. My issue is that the grey in the green/cyan auto-complete box is too washed-out and the contrast is too low. I'd like to make the box darker, and the lettering a lighter color.

Any way to do that without patching the pgstyle.py file?

Wow, that looks incredible!

I haven't tried it yet, but I seriously need to now.

Realy nice, thanks! What would be the added value of this compared to using vim handling autocompletion and syntax highlighting for editing queries and tmux for the output?

I don't think Vim's autocompletion can suggest table names and column names from the current database. I think the advantage of this tool is in using the metadata from the database to suggest relevant completions instead of mere keyword completions.

Off topic: what did you use to create your terminal gif's?

I used licecap (http://www.cockos.com/licecap/) to create that gif.

Great work. Is there a way to issue multiline commands?

Yes. You can toggle the multi-line mode by pressing F3. You can also make that permanent by editing the config file in ~/.myclirc

In the multi-line mode a semi-colon signifies the end of a query.

Oh, thanks, just noticed the little thing in the bottom! :) It's time for another cup of coffee, it would appear.

Is there any way to set tcpKeepAlive=true, ssl=true, and specify a sslrootcert (for Redshift use)?

Redshift is not officially supported since I'm targeting Postgres 9.x.

But there is some work underway to add support for Redshift.

https://github.com/dbcli/pgcli/issues/18

Cool! I think this project is really awesome and your blog is full of interesting things. Keep up the great work!

Finally, I almost started writing something like this myself a couple of times.

Autocompleting DROP TABLE or DELETE is probably not a good idea.

http://www.catb.org/jargon/html/D/DWIM.html

    DWIM: /dwim/

    [acronym, ‘Do What I Mean’]

<snip>

    Warren Teitelman originally wrote DWIM to fix his typos and spelling errors, so it
    was somewhat idiosyncratic to his style, and would often make hash of anyone
    else's typos if they were stylistically different. Some victims of DWIM thus
    claimed that the acronym stood for ‘Damn Warren’s Infernal Machine!'.

    In one notorious incident, Warren added a DWIM feature to the command interpreter
    used at Xerox PARC. One day another hacker there typed delete *$ to free up some
    disk space. (The editor there named backup files by appending $ to the original
    file name, so he was trying to delete any backup files left over from old editing
    sessions.) It happened that there weren't any editor backup files, so DWIM
    helpfully reported `*$ not found, assuming you meant 'delete *'`. It then
    started to delete all the files on the disk! The hacker managed to stop it
    with a Vulcan nerve pinch after only a half dozen or so files were lost.

    The disgruntled victim later said he had been sorely tempted to go to Warren's
    office, tie Warren down in his chair in front of his workstation, and then type
    delete *$ twice.

I used that system, Interlisp, the Warren Teitelman version of LISP, on SRI's DECsystem 2060. I had a similar experience. I typed "EDIT" when in the wrong mode, and got "=EXIT" from DWIM. Interlisp terminated without saving.

It really was keyed to Warren's errors. It would try to correct "9" and "0" into "(" and ")", a mistake he apparently made frequently but I never did.

I also used InterLisp. I had a Xerox 1108. The first 5 minutes I used my 1108 my mind was blown when it corrected a typo in a short program I entered and ran.

I'm planning to add confirmation for those commands to make sure people don't accidentally drop something they don't intend to.

This looks great!

beautiful work

Reply on news.ycombinator.com