One of the reasons Vim1 is powerful is that it gives you easy access to your shell. If you’ve invested in developing your skills with the Unix shell, and in writing shell-scripts, that work also pays off in Vim.
Here’s a simple example that I use frequently: let’s say I find a slow SQL statement in my Postgres logs, and I’d like to rewrite it. To rewrite it, I first want to read it, so I’d like to turn this:
select person.first_name, person.last_name, person.email, person.phone, person.active, perm.name, perm.created_at from person join person_permission on person.id = person_permission.person_id join permission on person_permission.permission_id = permission.id where person.last_name like 'Fl%one';
into something I can read.
Happily there’s a command-line tool called
sqlformat that formats SQL
queries tidily. I don’t want to leave Vim to use this command-line tool, and I
don’t have to: Vim has
commands. I can run
sqlformat on my query by selecting the line with
!! (a special case of the
! to run an external program on the current line) and running
sqlformat -r -
sqlformat to reformat the SQL query supplied on standard input. Vim
will pipe the selected text to
sqlformat’s stdin, and replace the selected
text with the output of
sqlformat -r - (on stdout), which is:
select person.first_name, person.last_name, person.email, person.phone, person.active, permission.name, permission.created_at from person join person_permission on person.id = person_permission.person_id join permission on person_permission.permission_id = permission.id where person.last_name like 'Fl%one';
This may not seem like a big deal at first: you could also do this directly from a terminal. If you copy the SQL query into your clipboard, you can run:
$ pbpaste | sqlformat -r - | pbcopy
and paste that back into your editor. If you do it often, you could write a
shell script to automate it and perhaps even bind it to a keyboard shortcut.
Even if you do all that though, you still don’t get the immediate in-place
replacement that Vim affords with
If you find that your
! maneuver went badly (perhaps you typoed your
command-line), you can always
undo the error and try again.
You may have a query that spans multiple lines. In such cases I use
run the filter on the entire “paragraph” (
ip: inside paragraph).
What if you have multiple queries in one text file? You can run
. repeat to
repeat it for other occurrences.
While I’ve talked about
sqlformat here, that’s just one example. Here are
some other examples:
Reformat JSON in Vim
If you have machine-output JSON that you’d like to pretty-print in-place, you
For instance, given
a} around the JSON’s
jq) will reformat it.
If you work with JSON a lot, consider Tim Pope’s vim-jdaddy plugin
Run SQL queries
If you have a SQL query in your editor that you’d like to run on a Postgres
database, you can use
SELECT 90 ^ 3
!!psql will evaluate it. (The obvious caveats apply:
psql on the command-line
must take you to your preferred database.)
If you have some Python code you’d like to evaluate:
print(33 ** 5)
You can use
!!python to run it.
Use visual mode when unsure
When you’re new to Vim, you may not be confident in what text you’re electing
to run the external program on. Visual mode is handy when you’re building your
intuition of Vim’s motions and text objects:
vip will select the query as
before, but visually highlight it so you can confirm you’ve selected what you
want before you hit
An important catch with visual mode is that it doesn’t play well with repeating
., and it encourages you to read the visual selection to
cross-check what you’re doing. For things you do often, avoid visual mode:
you’ll work faster.