Postgres CLI with autocompletion and syntax highlighting

by dmmalam • 11 years ago

304 45 11 years ago

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

amjith12• 11 years ago

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• 11 years ago

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

meddlepal• 11 years ago

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

brightball• 11 years ago

This looks great!

sgt• 11 years ago

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.

amjith12• 11 years ago

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• 11 years ago

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

1rae• 11 years ago

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

pmontra• 11 years ago

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

bosdev• 11 years ago

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

man5quid• 11 years ago

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.

amjith12• 11 years ago

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

pi-rat• 11 years ago

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

barosl• 11 years ago

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.

clessg2• 11 years ago

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

amjith12• 11 years ago

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.

clessg2• 11 years ago

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

VeejayRampay• 11 years ago

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

amjith12• 11 years ago

Done! :)

vamega• 11 years ago

What emacs mode is that in the screenshot?

amjith12• 11 years ago

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.

Mister_Snuggles• 11 years ago

Wow, that looks incredible!

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

limaoscarjuliet2• 11 years ago

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• 11 years ago

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

limaoscarjuliet2• 11 years ago

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.

krick• 11 years ago

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.

amjith12• 11 years ago

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

abledon• 11 years ago

beautiful work

ochoa• 11 years ago

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

amjith12• 11 years ago

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

shazeline2• 11 years ago

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

amjith12• 11 years ago

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

shazeline2• 11 years ago

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

Animats2• 11 years ago

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

cbd1984• 11 years ago

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.

Animats2• 11 years ago

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.

amjith12• 11 years ago

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

cel1ne• 11 years ago

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

fsiefken• 11 years ago

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?

amjith12• 11 years ago

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.

munchhausen• 11 years ago

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?

LukeHoersten• 11 years ago

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.

Postgres CLI with autocompletion and syntax highlighting

Related Stories