Existing HN: Qsh – Improved database querying from your terminal

Search info from SHell – improved database querying from your terminal

Pre-requisites

You are going to must install & spend tmux, which is compulsory to administer the split panes. It desires to be accessible from your equipment supervisor.

For better viewing of SQL outcomes, the pspg pager is generally recommended, alternatively, that you just would possibly well also spend much less -SinFX. When showing outcomes, qsh will try to develop a large preference, alternatively, that you just’ll be ready to as but any other explicitly resolve a pager.

To format SQL statements, you can have python 3 and sqlparse.

Setup

Clone this repository to your inform:

$ git clone [email protected]:muhmud/qsh.git ~/.qsh

After which add the ~/.qsh/bin directory to your PATH.

You now enticing must setup the editor you prefer to make spend of for writing SQL statements, which will be triggered from your SQL consumer tool. Whereas you prefer to, that you just’ll be ready to setup keyboard shortcuts for this in ~/.inputrc. The following instance does this for Alt-q:

$if mysql
  "eq": '\e;C-m'
$endif

$if psql
  "eq": '\eC-m'
$endif

That you just can also currently spend either vim/nvim or micro.

vim/nvim

vim-drag

Slide 'muhmud/qsh', { 'dir': '~/.qsh', 'department': 'main', 'rtp': 'editors/vim' }

packer

{ "~/.qsh", rtp = "editors/vim" }

These are the default key mappings, which will be disabled by environment g:qsh_enable_key_mappings to 0:

" Alt+e (for attain)
vnoremap    e :call QshExecuteSelection()
vnoremap     :call QshExecuteSelection()
vnoremap     :call QshExecuteSelection()

" Alt+y
inoremap    y :call QshExecuteAll()
inoremap     :call QshExecuteAll()
nnoremap    y :call QshExecuteAll()
nnoremap     :call QshExecuteAll()

" Alt+g (for lunge)
inoremap    g :call QshExecute()
inoremap     :call QshExecute()
nnoremap    g :call QshExecute()
nnoremap     :call QshExecute()

" Alt+G
inoremap    G :call QshExecute("^---$" 0)
inoremap     :call QshExecute("^---$", 0)
nnoremap    G :call QshExecute("^---$", 0)
nnoremap     :call QshExecute("^---$", 0)

" Alt+d (for dispute)
vnoremap    d :call QshExecuteNamedScriptVisually("dispute")
vnoremap     :call QshExecuteNamedScriptVisually("dispute")
nnoremap    d :call QshExecuteNamedScript("dispute")
nnoremap     :call QshExecuteNamedScript("dispute")
inoremap    d :call QshExecuteNamedScript("dispute")
inoremap     :call QshExecuteNamedScript("dispute")

" Alt+r (for rows)
vnoremap    r :call QshExecuteNamedScriptVisually("snatch-some")
vnoremap     :call QshExecuteNamedScriptVisually("snatch-some")
nnoremap    r :call QshExecuteNamedScript("snatch-some")
nnoremap     :call QshExecuteNamedScript("snatch-some")
inoremap    r :call QshExecuteNamedScript("snatch-some")
inoremap     :call QshExecuteNamedScript("snatch-some")

" Alt+t (for tidy)
vnoremap    t :call QshExecuteNamedSnippetVisually("format")
vnoremap     :call QshExecuteNamedSnippetVisually("format")

" Alt+v
vnoremap    v :call QshExecuteScriptVisually()
vnoremap     :call QshExecuteScriptVisually()
nnoremap    v :call QshExecuteScript()
nnoremap     :call QshExecuteScript()
inoremap    v :call QshExecuteScript()
inoremap     :call QshExecuteScript()

" Alt+Space
vnoremap     :call QshExecuteSnippetVisually()
vnoremap     :call QshExecuteSnippetVisually()
nnoremap     :call QshExecuteSnippet()
nnoremap     :call QshExecuteSnippet()
inoremap     :call QshExecuteSnippet()
inoremap     :call QshExecuteSnippet()

That you just can also add personalized key mappings love this:

autocmd Filetype sql call QshCustomSqlKeyMappings()
feature QshCustomSqlKeyMappings() 
   ...
endfunction

Micro

The micro plugin would possibly also be installed by executing the next:

$ mkdir -p ~/.config/micro/drag && cp -r ~/.qsh/editors/micro ~/.config/micro/drag/qsh

The following key mappings, or an identical, would possibly also be added to ~/.config/micro/bindings.json:

"Alt-g": "expose:QshExecute",
"Alt-G": "expose:QshExecute '^---$' 0",
"Alt-e": "expose:QshExecuteSelection",
"Alt-y": "expose:QshExecuteAll",
"Alt-d": "expose:QshExecuteNamedScript 'dispute'",
"Alt-r": "expose:QshExecuteNamedScript 'snatch-some'",
"Alt-v": "expose:QshExecuteScript",
"Alt-Space": "expose:QshExecuteSnippet",
"Alt-t": "expose:QshExecuteNamedSnippet 'format'"

Utilization

From within a tmux session, prefix the invocation of your SQL consumer with qsh:

This would possibly well also setup your SQL consumer environment precisely for qsh. Now, plight off the editor utilizing the expose for your environment. For mysql, this is able to be e;, and for psql, e, or at the same time as you happen to setup a keyboard shortcut, as described above, that you just would possibly well spend that also.

You ought to glance the editor pane created, the assign that you just’ll be ready to now form in queries. A default SQL file is created for you, alternatively, that you just would possibly well launch up every other file you prefer to.

Executing Queries

• Alt-e – Highlight a expect to lunge and accomplish it
• Alt-g – Elevate out a expect with out desiring to highlight it
• Alt-G – Elevate out extra than one statements or feature/blueprint definitions with out desiring to highlight
• Alt-y – Elevate out the entirety in the editor buffer

For Alt-g, qsh will seek a statement delimited on either side by a semi-colon. This makes it more uncomplicated to attain a shining SQL statement with out desiring to highlight it at any time when.

Alternatively, utilizing Alt-G does the comparable aspect but changes the delimiter to be the string ---, which desires to be essentially most definitely the greatest aspect on a line. That you just can also trade this to what you like, right here’s simply the default as defined in the main mapping.

The following gives an instance:

fabricate blueprint take a look at(a int)
originate up
  update take a look at
    plight a = 1;                  /* <- If the cursor is right here, Alt-G will fabricate blueprint take a look at simplest */
end;

---                             /<- That is the customizable delimiter defined in the main binding */

fabricate blueprint test2(a int)
originate up
  update take a look at
    plight a = 2;                  /* <- If the cursor is right here, Alt-G will fabricate blueprint test2 simplest */
end;

Scripts

• Alt-v – Elevate out a script, which will be carried out with or with out highlighting

Scripts are shortcuts for SQL statements that return a consistent data plight all over diverse database servers. As an illustration, to get a checklist of tables in the present database, whether or no longer mysql or postgresql, attain the next script:

There are rather a few scripts accessible. That you just can also glance what they’re by executing the scripts script. That you just can also also add you have personalized scripts to ~/.qsh/potentialities/psql/scripts or ~/.qsh/potentialities/mysql/scripts, searching on the database platform you are concentrated on.

For reference, right here is an instance of the forms of scripts accessible:

$ ls ~/.qsh/potentialities/psql/scripts
all-columns     all-references  all-sessions  columns    procedures  scripts      tables
all-databases   all-routines    all-tables    databases  references  snatch       triggers
all-functions   all-schemas     all-triggers  dispute   routines    snatch-some  views
all-procedures  all-snatch      all-views     functions  schemas     sessions

Named Scripts

• Alt-d – Describe a explicit table, that would possibly well also or is doubtlessly no longer highlighted
• Alt-r – Pick among the info for a explicit table

Named scripts resolve info from the editor as a payload, which is former to provide context. The scripts mentioned above are created by default, alternatively, that you just’ll be ready to also add your have.

That you just can also either highlight the name of the table to be former with these scripts, alternatively, it be also OK for the cursor to easily be on the table name.

Snippets

• Alt-Space – Elevate out a snippet, that would possibly well also or is doubtlessly no longer highlighted

Snippets are an comparable to scripts, alternatively, the implications are injected into the editor as but any other of being displayed as expect outcomes. That you just can also also add your have personalized snippets to ~/.qsh/potentialities/psql/snippets for postgresql, to illustrate.

The supreme snippet currently accessible is:

• columns() – To find a comma-separated checklist of column names for a explicit table

Named Snippets

• Alt-t – Format a SQL statement, which desires to be highlighted

Equivalent to named scripts, these snippets resolve a payload from the editor. At the 2d, this characteristic would possibly also be former to format a SQL statement.

Registering Connections

That you just can also register connections for database servers that you just get entry to veritably. This would possibly well also moreover be former to store the password for the connection utilizing the native mechanism of every SQL consumer. That is completed utilizing the qsh-reg tool.

As an illustration:

$ qsh-reg -p dev-server psql -hmy-dev-server -Uroot -ddevdb

This would possibly well also register a connection known as dev-server utilizing the provided psql invocation. The -p risk capacity that a password is to be saved, which the script will instructed you for.

As soon as the connection is registered, that you just’ll be ready to connect love this:

That you just can also also attain other instruments utilizing the connection info, equivalent to pg_dump:

$ qsh -c pg_dump dev-server --table=public.some_table -s

Elevate out qsh-reg with none arguments to glance the final accessible options.

Alternatives

The following environment variables would possibly also be modified if required:

• QSH_EDITOR – The editor you are going to be utilizing, which defaults to $VISUAL
• QSH_PAGER – The pager you will most definitely be utilizing, which by default will try pspg, much less, and cat in that picture.

The spend of SSH

To work with database servers over SSH, you ought to install qsh to your faraway host and originate tmux for your SSH session. That you just can no longer, alternatively, be ready to get qsh to work when the editor is running in the community and the SQL consumer is on a faraway server, i.e. running within an SSH connection.

If that that you just’ll be ready to mediate, it can well even be more uncomplicated to easily provide host and port connection particulars to the SQL consumer to your workstation and lunge the entirety in the community.

Exit

To tremendous up any non permanent recordsdata & return to commonplace, simply exit the editor. Whereas you exit by accident, enticing plight off the editor again, and it will return to how it used to be.

That you just can also also explicitly cleanup non permanent recordsdata created by qsh by executing: