
Postgres CLI with autocompletion and syntax highlighting - dmmalam
https://github.com/dbcli/pgcli
======
bajsejohannes
The author also recently completed a kickstarter for the same thing for MySQL:
[https://www.kickstarter.com/projects/1839007321/mysql-
cli](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...

~~~
amjith
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. :)

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

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

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

~~~
bwblabs
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! (:

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

[https://github.com/dbcli/pgcli/issues/283](https://github.com/dbcli/pgcli/issues/283)

Thank you for catching that.

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

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

~~~
amjith
Done! :)

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

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

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

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

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

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

~~~
amjith
I was following the design philosophy of fish shell
[http://fishshell.com/docs/current/design.html#design-
overvie...](http://fishshell.com/docs/current/design.html#design-overview)

Especially these two:

* Configurability is the root of all evil

* The law of discoverability

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

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

------
pi-rat
Wow, well done! So much better than psql, thanks!

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

------
vamega
What emacs mode is that in the screenshot?

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

------
munchhausen
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?

------
Mister_Snuggles
Wow, that looks incredible!

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

------
fsiefken
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?

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

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

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

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

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

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

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

~~~
amjith
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](https://github.com/dbcli/pgcli/issues/18)

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

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

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

~~~
cbd1984
[http://www.catb.org/jargon/html/D/DWIM.html](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.

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

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

------
brightball
This looks great!

------
abledon
beautiful work

