Show HN: Pgcli – A CLI for Postgres with auto-completion and syntax highlighting | Hacker News Reader

Show HN: Pgcli – A CLI for Postgres with auto-completion and syntax highlighting

pgcli.com

227 pointsamjith52 comments

Open articleView on HN

I'm the author of pgcli.

There is an FAQ page: http://pgcli.com/faq

I'll be happy to answer questions here as well.

Wow, I just installed it and tested it out - pgcli is remarkable! This is going to be revolutionary for my psql command-line forays! The auto-complete is fantastic.

The source code is available on GH: https://github.com/amjith/pgcli

OP: why no "Fork Me" banner? ;)

I'm using Pelican static generator to create the site. Once I figure out how to add the "Fork Me!" banner, I'll add that in.

The link to the GH repo is on the home page. I figured that makes it obvious, but perhaps not?

It was a little hard to find the link since it was buried at the bottom in "social" alongside twitter.

Again, this is truly awesome work. Can you comment on what inspired you to create pgcli?

Copied from FAQ.

I'm a huge fan of CLIs. Especially REPLs. When I first encountered BPython (http://www.bpython-interpreter.org/), I fell in love with it. BPython is a Python REPL that has auto-completion, syntax-highlighting and displays the docstring of functions as you type. Once you get used to that level of pampering, all other REPLs start to look like Dumb Terminals.

When I started using psql for interacting with my Postgres database, I yearned for a BPython equivalent for psql.

One fine day I got a twitter DM from Jonathan Slenders asking me if I'd be interested in trying a new Python library that he's working on called Python-Prompt-Toolkit. This library provided a way to write CLIs without having to deal with curses and had the requisite tools for doing auto-completion and syntax-highlighting. So I jumped at the opportunity to try it out. After I got myself familiarized, I started writing pgcli to scratch my own itch.

So this app exists because Jonathan was kind enough to request my feedback.

For the request on the homepage for a Homebrew formula:

From [1]:

> Homebrew generally won't accept libraries that can be installed correctly with `pip install foo`.

[1]: https://github.com/Homebrew/homebrew/blob/master/share/doc/h...

Yes. But pgcli is not a python library, it's an application that happens to be written in python.

> Homebrew is happy to accept applications that are built in Python, whether the apps are available from PyPI or not.

Example: duplicity[0] is available via homebrew.

[0]: https://github.com/Homebrew/homebrew/blob/master/Library/For...

Example: duplicity[0] is available via homebrew.

[0]: https://github.com/Homebrew/homebrew/blob/master/Library/For...

Example: duplicity[0] is available via homebrew.

[0]: https://github.com/Homebrew/homebrew/blob/master/Library/For...

Example: duplicity[0] is available via homebrew.

[0]: https://github.com/Homebrew/homebrew/blob/master/Library/For...

Example: duplicity[0] is available via homebrew.

[0]: https://github.com/Homebrew/homebrew/blob/master/Library/For...

Example: duplicity[0] is available via homebrew.

[0]: https://github.com/Homebrew/homebrew/blob/master/Library/For...

Example: duplicity[0] is available via homebrew.

[0]: https://github.com/Homebrew/homebrew/blob/master/Library/For...

Example: duplicity[0] is available via homebrew.

[0]: https://github.com/Homebrew/homebrew/blob/master/Library/For...

Example: duplicity[0] is available via homebrew.

[0]: https://github.com/Homebrew/homebrew/blob/master/Library/For...

This is really great man, thanks. I had simply accepted the pgsql client for what it is, but you took it as an opportunity to innovate. Kudos!

You mentioned you'd like to know if it works in Windows, and I think it does!

My setup is a bit of a one-off. I'm running it inside ConEmu, with the environment initialized from a batch script. That way, my whole setup is portable. Postgres is fired up with a tab in ConEmu (cleaned up once ConEmu closes by the waiting script that spawned it). None of it has admin, but seems to allow me to bootstrap any tool I'd like to use. I also have MinGW and the GNU tools that were compiled for Windows. So from now on, I'll have pgcli fire up instead of just psql when that ConEmu tab opens.

I have to say it's a pleasure to see it Just Work. I haven't done anything clever yet, just some basic fiddlin'. But it's really quite nice to have a pretty CLI tool for PG in Windows!

EDIT: whoops, wrong tool name.

Thank you for testing it on Windows. I just got an Azure instance, so I should start doing proper tests on Windows before releases. Thank you!

This looks really nice, especially the colors.

As a reminder/heads up, you can often get simple command line editing and completion in tools like psql in a bash-like environment, just by having a .inputrc file in your home directory. Basically anything that uses readline, like bash, psql, ipython, etc.

Extra benefit, you don't need to set -o vi (or the other flavor) in your .bashrc or on the command line; anything that uses readline (including bash and many other cli interfaces) will read the .inputrc file and do what it says. I'm often pleasantly surprised that a new cli tool uses my .inputrc.

http://linux.die.net/man/3/readline

https://www.gnu.org/software/bash/manual/bashref.html#Comman...

You can easily add the capability to command line tools that you write. For example:

https://docs.python.org/2/library/readline.html

> you can often get simple command line editing and completion in tools like psql in a bash-like environment, just by having a .inputrc file in your home directory.

I'm genuinely confused by this. Why do you need to .inputrc file? Doesn't readline default to EMACS style command shortcuts? And isn't TAB the EMACS (and near universal) key for auto-complete?

If you like the default behavior, then you don't need .inputrc.

If you want vi command line edit keybindings, then you do.

Or if you like emacs but you want to customize, then you need a .inputrc.

Awesome! I made a package for Arch Linux and stuck it into AUR: https://aur.archlinux.org/packages/pgcli/

Awesome! Adding a django-extensions command for a 'dbshell_plus' that utilizes this sounds like a great idea. I think I may just do that.

Looks great! Just reported an incompatibility with Unicode.

Also, is it possible to tweak the autocompletion so that hitting Enter selects the first offered completion? Right now, you have to hit tab or use the arrow keys to select.

A Homebrew formula would be great.

Thanks for the bug report. Sorry about the Unicode woes, I'll take a look.

Hitting enter to select the first completion is much harder. I can't promise I'll be able to do this one.

A homebrew formula would be fantastic. I tried making it last night but I ran into issues with psycopg2 dependency. So if you have any experience with building brew formulae, I'd appreciate some help.

While I know how to do formulas, unfortunately I have no idea how to do it cleanly with Python, which has its own package system.

Ah. I was wondering why it was returning no results for any of the tables I was selecting data from. I've been purposefully filling this database with non-ascii characters during testing to make sure there are no encoding issues.

Does this work with emacs? I love the integration of psql and emacs, but this would be even better. http://www.emacswiki.org/emacs/SqlMode

I didn't know about the SqlMode. I've never tried it.

I don't have a working setup of emacs to try it right away. If you can try it out and find that it's not working, please file an issue.

I have build https://github.com/antoineB/emacs-assistant to work with sql-mode it provide a simple completion for table and columns.

For now it serve me well.

Based on 3 minute testing, I like, thanks.

If someone tries to install this on ubuntu with just the quick start instructions and fails, note that you need to

sudo apt-get install libpq-dev

Obviously, this is told in the more detailed install instructions.

Also python-dev if you are installing on 14.04 Server (i.e. using Vagrant or something).

I actually work on a piece of software for creating sql charts from the command line (http://www.sqldashboards.com/help/sqlchart-command-line-sql-...),this is brilliant! Thanks.

Lazyweb: is there a way to get this to be the default handler for connecting to my heroku db?

Looks pretty slick, I'll give it try when I get home.

I'll go ahead and ask, since I'm probably not the only one thinking it: will there be a MySQL version? ;)

EDIT: oh, and I guess the name wouldn't be very fitting for a MySQL cli...

Copied from the FAQ page:

Probably. Now that I've worked out the hard parts (sqlcompletion and special commands), I'd like to write a CLI for Sqlite and MySql. But I'm not sure if I'll have the time to do it in the immediate future. But if you're interested in doing it, I'd be happy to help you. Please get in touch.

Sorry, but how to see tables from other schemas besides public in the FROM clause autocomplete?

I've created a GH issue for this. https://github.com/amjith/pgcli/issues/22

Would you mind adding a bit of description to the issue, perhaps an example sql statement?

Tried it. It's wonderful. I think it will replace psql quickly, at least in my workflow.

This is cool! Thanks for sharing.

Considering I'm new to Postgres (been on MySQL for too many years), this is definitely making my life easier because of the autocomplete :)

But a question for you: how does this help someone who's a power user of Postgres?

This is great Is there support or planned support for expanded display? (\x in psql)

Yes!

That is the most popular feature request so far. https://github.com/amjith/pgcli/issues/19

Does anyone know something similar, but for mysql?

I wish there was something like this for Oracle. sqlplus is so painful

Did you try https://pypi.python.org/pypi/sqlpython?

It's not quite the same as pgcli but I took some inspiration from that for auto-completion.

Looks fantastic, great idea!

This is great Is there support or planned support for expanded display? (\x in psql)

This is great Is there support or planned support for expanded display? (\x in psql)

This is great Is there support or planned support for expanded display? (\x in psql)

This is great Is there support or planned support for expanded display? (\x in psql)

Reply on news.ycombinator.com