6 ms·

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

227 points by amjith 12y ago

amjith 12y ago
I'm the author of pgcli. There is an FAQ page: http://pgcli.com/faq http://pgcli.com/faq I'll be happy to answer questions here as well.
- jaytaylor 12y ago
  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 https://github.com/amjith/pgcli OP: why no "Fork Me" banner? ;)
  - amjith 12y ago
    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?
    - jaytaylor 12y ago
      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?
      - amjith 12y ago
        Copied from FAQ. I'm a huge fan of CLIs. Especially REPLs. When I first encountered BPython (http://www.bpython-interpreter.org/ 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.
- tgkokk 12y ago
  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/homebrew/Python-for-Formula-Authors.md https://github.com/Homebrew/homebrew/blob/master/share/doc/h...
  - amjith 12y ago
    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.
    - lloeki 12y ago
      Example: duplicity[0] is available via homebrew. [0]: https://github.com/Homebrew/homebrew/blob/master/Library/Formula/duplicity.rb https://github.com/Homebrew/homebrew/blob/master/Library/For...
    - lloeki 12y ago
      Example: duplicity[0] is available via homebrew. [0]: https://github.com/Homebrew/homebrew/blob/master/Library/Formula/duplicity.rb https://github.com/Homebrew/homebrew/blob/master/Library/For...
    - lloeki 12y ago
      Example: duplicity[0] is available via homebrew. [0]: https://github.com/Homebrew/homebrew/blob/master/Library/Formula/duplicity.rb https://github.com/Homebrew/homebrew/blob/master/Library/For...
    - lloeki 12y ago
      Example: duplicity[0] is available via homebrew. [0]: https://github.com/Homebrew/homebrew/blob/master/Library/Formula/duplicity.rb https://github.com/Homebrew/homebrew/blob/master/Library/For...
    - lloeki 12y ago
- vectorpush 12y ago
  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!
- HCIdivision17 12y ago
  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.
  - amjith 12y ago
    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!
IbJacked 12y ago
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...
- amjith 12y ago
  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.
rlvesco7 12y ago
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 http://www.emacswiki.org/emacs/SqlMode
- amjith 12y ago
  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.
- bad_login 12y ago
  I have build https://github.com/antoineB/emacs-assistant 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.
random_number 12y ago
Looks fantastic, great idea!
pmontra 12y ago
Tried it. It's wonderful. I think it will replace psql quickly, at least in my workflow.
xfalcox 12y ago
Sorry, but how to see tables from other schemas besides public in the FROM clause autocomplete?
- amjith 12y ago
  I've created a GH issue for this. https://github.com/amjith/pgcli/issues/22 https://github.com/amjith/pgcli/issues/22 Would you mind adding a bit of description to the issue, perhaps an example sql statement?
lobster_johnson 12y ago
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.
- amjith 12y ago
  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.
  - lobster_johnson 12y ago
    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.
- deleted 12y ago
  [deleted]
  - lobster_johnson 12y ago
    I prefer one package manager for OS-wide system tools. Then I can just do "brew upgrade" and have everything in sync. I'm not a Python dev, so I don't care what Pip has installed. I would want to be able to blow my Python installation and not worry about my system tools disappearing.
- mike-cardwell 12y ago
  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.
asolove 12y ago
Lazyweb: is there a way to get this to be the default handler for connecting to my heroku db?
Svenstaro 12y ago
Awesome! I made a package for Arch Linux and stuck it into AUR: https://aur.archlinux.org/packages/pgcli/ https://aur.archlinux.org/packages/pgcli/
klaussilveira 12y ago
Does anyone know something similar, but for mysql?
a3n 12y ago
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 http://linux.die.net/man/3/readline https://www.gnu.org/software/bash/manual/bashref.html#Command-Line-Editing 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 https://docs.python.org/2/library/readline.html
- euroclydon 12y ago
  > 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?
  - a3n 12y ago
    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.
beefield 12y ago
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.
- noir_lord 12y ago
  Also python-dev if you are installing on 14.04 Server (i.e. using Vagrant or something).
pca2 12y ago
This is great Is there support or planned support for expanded display? (\x in psql)
pca2 12y ago
This is great Is there support or planned support for expanded display? (\x in psql)
pca2 12y ago
This is great Is there support or planned support for expanded display? (\x in psql)
pca2 12y ago
This is great Is there support or planned support for expanded display? (\x in psql)
pca2 12y ago
This is great Is there support or planned support for expanded display? (\x in psql)
- amjith 12y ago
  Yes! That is the most popular feature request so far. https://github.com/amjith/pgcli/issues/19 https://github.com/amjith/pgcli/issues/19
wldcordeiro 12y ago
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.
RyanHamilton 12y ago
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-chart-generator http://www.sqldashboards.com/help/sqlchart-command-line-sql-...),this is brilliant! Thanks.
tmaly 12y ago
I wish there was something like this for Oracle. sqlplus is so painful
- amjith 12y ago
  Did you try https://pypi.python.org/pypi/sqlpython https://pypi.python.org/pypi/sqlpython? It's not quite the same as pgcli but I took some inspiration from that for auto-completion.
ravipratapm 12y ago
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?

227 points by amjith 12y ago

I'm the author of pgcli. There is an FAQ page: http://pgcli.com/faq http://pgcli.com/faq I'll be happy to answer questions here as well.

jaytaylor 12y ago
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 https://github.com/amjith/pgcli OP: why no "Fork Me" banner? ;)
- amjith 12y ago
  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?
  - jaytaylor 12y ago
    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?
    - amjith 12y ago
      Copied from FAQ. I'm a huge fan of CLIs. Especially REPLs. When I first encountered BPython (http://www.bpython-interpreter.org/ 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.
tgkokk 12y ago
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/homebrew/Python-for-Formula-Authors.md https://github.com/Homebrew/homebrew/blob/master/share/doc/h...
- amjith 12y ago
  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.
  - lloeki 12y ago
    Example: duplicity[0] is available via homebrew. [0]: https://github.com/Homebrew/homebrew/blob/master/Library/Formula/duplicity.rb https://github.com/Homebrew/homebrew/blob/master/Library/For...
  - lloeki 12y ago
    Example: duplicity[0] is available via homebrew. [0]: https://github.com/Homebrew/homebrew/blob/master/Library/Formula/duplicity.rb https://github.com/Homebrew/homebrew/blob/master/Library/For...
  - lloeki 12y ago
    Example: duplicity[0] is available via homebrew. [0]: https://github.com/Homebrew/homebrew/blob/master/Library/Formula/duplicity.rb https://github.com/Homebrew/homebrew/blob/master/Library/For...
  - lloeki 12y ago
    Example: duplicity[0] is available via homebrew. [0]: https://github.com/Homebrew/homebrew/blob/master/Library/Formula/duplicity.rb https://github.com/Homebrew/homebrew/blob/master/Library/For...
  - lloeki 12y ago
vectorpush 12y ago
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!
HCIdivision17 12y ago
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.
- amjith 12y ago
  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!

IbJacked 12y ago

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

amjith 12y ago
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.

rlvesco7 12y ago

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 http://www.emacswiki.org/emacs/SqlMode

amjith 12y ago
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.
bad_login 12y ago
I have build https://github.com/antoineB/emacs-assistant 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.

random_number 12y ago

Looks fantastic, great idea!

pmontra 12y ago

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

xfalcox 12y ago

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

amjith 12y ago
I've created a GH issue for this. https://github.com/amjith/pgcli/issues/22 https://github.com/amjith/pgcli/issues/22 Would you mind adding a bit of description to the issue, perhaps an example sql statement?

lobster_johnson 12y ago

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.

amjith 12y ago
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.
- lobster_johnson 12y ago
  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.
deleted 12y ago
[deleted]
- lobster_johnson 12y ago
  I prefer one package manager for OS-wide system tools. Then I can just do "brew upgrade" and have everything in sync. I'm not a Python dev, so I don't care what Pip has installed. I would want to be able to blow my Python installation and not worry about my system tools disappearing.
mike-cardwell 12y ago
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.

asolove 12y ago

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

Svenstaro 12y ago

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

klaussilveira 12y ago

Does anyone know something similar, but for mysql?

a3n 12y ago

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 http://linux.die.net/man/3/readline https://www.gnu.org/software/bash/manual/bashref.html#Command-Line-Editing 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 https://docs.python.org/2/library/readline.html

euroclydon 12y ago
> 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?
- a3n 12y ago
  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.

beefield 12y ago

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.

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

pca2 12y ago