Vim/Neovim — Managing Databases

Managing databases and run SQL statements, all within Vim/Neovim.

Photo by Caspar Camille Rubin on Unsplash

Overview

In my previous article, I walked through with you how to manage multiple projects within Vim/Neovim. In this article let’s extend this to see how to manage multiple databases.

Setup

I am going to use PostgreSQL and MySQL as examples. For this to work, on the client machine you will need to install the database clients.

Other databases are also supported. More on this later.

If you want to use Docker for MySQL and PostgreSQL for testing, you can refer to this docker-compose.yml file.

docker-compose.yml

You can find the initialization SQL and .env files from this repository.

Plugins

I am going to leverage the Lua-based dotfiles I developed in my previous article. However, the plugins should work for both Vim and Neovim.

Under lua/plugins.lua, install these 2 plugins.

-- Database
use { 'tpope/vim-dadbod' }
use { 'kristijanhusak/vim-dadbod-ui' }

Run :luafile % and :PackerInstall to install the plugins.

Create a file called db.vim under the plugin folder with the following content.

nnoremap <silent> <leader>du :DBUIToggle<CR>
nnoremap <silent> <leader>df :DBUIFindBuffer<CR>
nnoremap <silent> <leader>dr :DBUIRenameBuffer<CR>
nnoremap <silent> <leader>dl :DBUILastQueryInfo<CR>
let g:db_ui_save_location = '~/.config/db_ui'

In db.vim I defined the key mappings forvim-dadbod-ui and also specified the location to store the database configurations and files.

vim-dadbod

Dadbod is a Vim plugin for interacting with databases. It supports a modern array of backends, including NoSQL databases:

  • ClickHouse
  • Impala
  • jq
  • MongoDB
  • MySQL
  • Oracle
  • osquery
  • PostgreSQL
  • Presto
  • Redis
  • SQL Server
  • SQLite
  • Your own easily implemented adapter

vim-dadbod-ui

vim-dadbod-ui is a simple UI for vim-dadbod. It allows simple navigation through databases and allows saving queries for later use.

Managing Database

With the plugins installed and configured, press <Leader>du and you should see the following screen.

vim-dadbod-ui: Add connection

Press ? you can see the help screen.

vim-dadbod-ui: Help

To add database connection, I can press A to add the following connections for testing.

  • postgres — postgresql://user1:userpwd@localhost:5432/testdb
  • mysql — mysql://user1:userpwd@127.0.0.1:3306/testdb

With the database connections added, your screen should look like below.

Database Connection Configuration

PostgreSQL

Now I can run queries against the database. E.g. I can create a new database, schema, table, and perform database operations on the table.

For the queries I want to execute, I select them in Visual Mode and then press <Leader>S to run.

create database alpha2phi;\connect alpha2phi;create schema if not exists myschema1;CREATE TABLE myschema1.users( 
id SERIAL PRIMARY KEY,
age INT,
first_name TEXT,
last_name TEXT,
email TEXT UNIQUE NOT NULL
);
insert into myschema1.users(age, first_name, last_name, email)
values(24, 'alpha', 'phi', 'alpha2phi@gmail.com');
select current_schema();-- show schema
\dn
set search_path to myschema1;
show search_path;
select * from users;
PostgreSQL Running Database Statements

In case the output is folded, just close the buffer, run :set nofoldenable, and run the queries again.

To save the buffer, just type <Leader>W.

MySQL

Similarly, for MySQL you can run any SQL statements and save them accordingly.

MySQL Running Database Statements

AutoCompletion

For database auto-completion, check out this article to see how I use LSP to implement database auto-completion.

Summary

vim-dabbod and vim-dadbod-ui may not fully replace the feature-rich database clients that you use. However, they come in handy when you want to run some database operations without leaving Vim/Neovim.

The dotfiles I use can be found from this repository.

Programmer and occasional blogger.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store