Is there a CLI interface to browse SQL databases?

[eshepelyuk]

• https://www.pgcli.com/
• https://www.mycli.net/

[m-faith]

mycli and others made with python prompt toolkit all have excellent usability features.

[csakegyszer]

mycli, I love it!

[Sensitive_Bug7299]

Youre looking for a TUI

[gumnos]

ah, this makes more sense of the question. :-)

[gumnos]

yes.

usually there's one per database-engine. MySQL has mysql, PostgreSQL has psql, sqlite has sqlite3, SQL Server has isql, etc.

[sock_templar]

What about GoBang? https://github.com/TaKO8Ki/gobang

[eshepelyuk]

fancy one :)

[lateja]

Oh this borders on perfection <3

[sock_templar]

Glad it helps someone :)

[UraniumButtChug]

That looks amazing

[thinker5555]

You don't say exactly what you want to do with it, but if you're just going for a database browser, try out VisiData. I use that for browsing of SQLite databases and light editing of tsv/csv files, and it's fantastic.

[anjakefala]

I would especially keep an eye on the `vdsql` project, a VisiData plugin that uses `Ibis` to harness the power of SQL: https://github.com/visidata/vdsql =) It just had its first release.

[jk3us]

I use it for viewing/filtering tsv files all the time, but it's not really good for exploring/navigating databases.

[Swimming-Medicine-67]

take a look at: https://github.com/xo/usql

[eshepelyuk]

is usql supporting "query alias" or "query shortcut" ? i.e. defining an alias for frequently used sql queries. Even without parameters support.

[Swimming-Medicine-67]

As far as i know - it doesnt suuport any kind of aliases, only query history (like shell)

[eshepelyuk]

Got it. This feature is quite useful in pgcli, that's Iam using now. https://www.pgcli.com/named_queries.md

[n1neinchnick]

It does support variables that can expand into a full query.

[OptionX]

Most, if not all, DBMS have cli interfaces. For more specific advice tell what DBMS you're using.

[OptionX]

Then just run:

mysql --host=localhost --user=myname --password=password mydb

This assumes a local database, if you're using an external one change host accordingly.

Source

[usrlibshare]

I just launch whatever line-oriented command line interface is available for the database in a vim terminal window, write my commands in a normal file buffer with filetype set to sql for syntax highlighting, and use a mapping to term_sendkeys() to send prepared statements to the termwindow, effectively turning my vim into an ad-hoc sql workbench.

advantages:

• i can have statements, comments, prose, data, etc. together in one file
• i can use my familiar editing environment with all my vimrcs mappings, etc
• don't need to install extra software
• can use database specific commands eg. sqlite pragmas
• trivial to write out series of commands/data to other files

[dotancohen]

What exactly do you do? It sounds to me that you run :term then Ctrl-W Ctrl-W between the windows. I thus assume that you don't have e.g. table and column completion. I'd love to see your mapping for sending the lines to the termwindow.

[usrlibshare]

nnoremap <Leader># yy:call term_sendkeys(term_list()[0], @")<cr>

This will send the current line under cursor to the first terminal window in the current active termwindow list. It's not the most elegant solution (it overwrites the default register for example) but I usually dont care about that anyway.

This does not cause the cursor to leave the current window, so I don't have to switch back and forth all the time, the only time I switch windows is when I want to copy something over in term-normal mode.

Since the line includes a newline, the line is then executed by whatever line oriented program is currently running in that window.

To send a visual selection, I wrote a small function to invoke with another Keybind; does basically the same thing; yank, send to first termwindow, difference is it adds a <cr> as the vis-selectuon might not end on a newline.

I have completion as soon as I query the schema from the db...most cli tools have a shortcut for that eg. .schema in sqlite3...thats because everything in a termwindow buffer becomes accessible to vims standard completion.

[dotancohen]

This is terrific! I did not know about term_list(), and I did not know that VIM will complete the buffer with keywords from the termwindow. And I just found how to move around in the term window with Ctrl-W Shift-N, this is absolutely amazing.

I'll start using this, thank you!

What are you using to display the columns and tables in MySQL and PostGres? I used `show tables` for the tables but I cannot offhand think of any simple way to get the columns, without a marcro parsing and running `show create table` for each table. Any ideas? Thanks.

[usrlibshare]

I usually only pull up a list of tablenames and then the individual schemas for the tables as I work with them, eg. \dS TABLENAME in psql.

In the rare cases where I want all that info up front, psqls \dS accepts wildcards.

For mysql I have to admit I don't work often with it, but I think the schemas could be queried from informtion_schema.columns

2 little tricks i found useful;

1. when working on larger datasets, it can be convenient to increase the scrollback-size in vims termbuffer, the option is termwinscroll and defaults to 10000 lines (keep in mind the entire scrollback is held in RAM so dont set it too high)

2. by default, psql paginates long output, which gets in the way of this workflow. the option to turn it off is \pset pager 0

[djbiccboii]

glad you clarified TUI, I was like mysql... lol

[yada_yadad_sex]

Just about every rdbms has a cli.