8 ms·

Postgres CLI with autocompletion and syntax highlighting

304 points by dmmalam 11y ago

bajsejohannes 11y ago
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 11y 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 11y ago
    This was so good I rolled it into my default vagrant deploy script, outstanding work my friend.
  - meddlepal 11y ago
    This looks awesome man. Keep up the great work :)
brightball 11y ago
This looks great!
sgt 11y 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.
- amjith 11y 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 11y 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! (:
    - amjith 11y ago
      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.
- 1rae 11y ago
  wow \x is great, I never knew about that.
pmontra 11y ago
I've been using it for months, it's definitely an improvement over psql. I recommend it to everybody.
bosdev 11y ago
This is wonderful. I have struggled with the lack of real readline compatibility in the PG CLI, this is a huge upgrade.
man5quid 11y 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.
- amjith 11y ago
  I was following the design philosophy of fish shell http://fishshell.com/docs/current/design.html#design-overview 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 11y ago
Wow, well done! So much better than psql, thanks!
barosl 11y 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.
clessg 11y ago
Great work. Is there a way to issue multiline commands?
- amjith 11y 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.
  - clessg 11y ago
    Oh, thanks, just noticed the little thing in the bottom! :) It's time for another cup of coffee, it would appear.
VeejayRampay 11y ago
Perfect project for an animated gif in the README (if that's possible), would have been better to showcase the product.
- amjith 11y ago
  Done! :)
vamega 11y ago
What emacs mode is that in the screenshot?
- amjith 11y 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 11y ago
Wow, that looks incredible! I haven't tried it yet, but I seriously need to now.
limaoscarjuliet 11y 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 11y ago
  Have you tried pgadmin, it even has a 'visual' query builder.
  - limaoscarjuliet 11y 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 11y 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.
- amjith 11y ago
  There is no native support for ssh port forwarding. But I do support connecting via a socket.
ochoa 11y ago
Off topic: what did you use to create your terminal gif's?
- amjith 11y ago
  I used licecap (http://www.cockos.com/licecap/ http://www.cockos.com/licecap/) to create that gif.
abledon 11y ago
beautiful work
shazeline 11y ago
Is there any way to set tcpKeepAlive=true, ssl=true, and specify a sslrootcert (for Redshift use)?
- amjith 11y 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 https://github.com/dbcli/pgcli/issues/18
  - shazeline 11y ago
    Cool! I think this project is really awesome and your blog is full of interesting things. Keep up the great work!
deleted 11y ago
[deleted]
Animats 11y ago
Autocompleting DROP TABLE or DELETE is probably not a good idea.
- cbd1984 11y ago
  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 11y 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.
    - mark_l_watson 11y ago
      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.
cel1ne 11y ago
Finally, I almost started writing something like this myself a couple of times.
fsiefken 11y 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?
- amjith 11y 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 11y 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 11y 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.

304 points by dmmalam 11y ago

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 11y 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 11y ago
  This was so good I rolled it into my default vagrant deploy script, outstanding work my friend.
- meddlepal 11y ago
  This looks awesome man. Keep up the great work :)

brightball 11y ago

This looks great!

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

amjith 11y 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 11y 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! (:
  - amjith 11y ago
    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.
1rae 11y ago
wow \x is great, I never knew about that.

pmontra 11y ago

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

bosdev 11y ago

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

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

amjith 11y ago
I was following the design philosophy of fish shell http://fishshell.com/docs/current/design.html#design-overview 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 11y ago

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

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

clessg 11y ago

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

amjith 11y 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.
- clessg 11y ago
  Oh, thanks, just noticed the little thing in the bottom! :) It's time for another cup of coffee, it would appear.

VeejayRampay 11y ago

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

amjith 11y ago
Done! :)

vamega 11y ago

What emacs mode is that in the screenshot?

amjith 11y 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 11y ago

Wow, that looks incredible! I haven't tried it yet, but I seriously need to now.

limaoscarjuliet 11y 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 11y ago
Have you tried pgadmin, it even has a 'visual' query builder.
- limaoscarjuliet 11y 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 11y 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.

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

ochoa 11y ago

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

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

abledon 11y ago

beautiful work

shazeline 11y ago

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

amjith 11y 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 https://github.com/dbcli/pgcli/issues/18
- shazeline 11y ago
  Cool! I think this project is really awesome and your blog is full of interesting things. Keep up the great work!

deleted 11y ago

[deleted]

Animats 11y ago

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

cbd1984 11y ago
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 11y 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.
  - mark_l_watson 11y ago
    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.

cel1ne 11y ago

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

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

amjith 11y 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 11y 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 11y 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.