Using Emacs as a database client

It will come as no surprise that Emacs, being the extensible text editor, supports working with SQL files. But since Emacs also supports embedded prompts, like a shell, it can also work just fine as a database client.

Connecting to a database

Let’s assume we’ve got a PostgreSQL database running locally. We can connect to it from Emacs using M-x sql-postgres. There are equivalent commands for MySQL and a few others. Emacs will prompt us for a few connection details: the username, database name and host of our database. If you enter them correctly, Emacs should then open a new window with a database prompt in it. It will look like you ran psql manually from a shell.

The new *SQL: Postgres* buffer in this window will work mostly like psql from the regular shell does, but it is still Emacs. If, like me, you use evil-mode for Vim keybindings, those will also apply to this buffer. You can move around and select and copy text like in any other buffer. At the prompt, you can enter a query, hit and see the results.

Saving connection details

If you are working on a typical web development project, you might have a single development database you will want to keep connecting to. To avoid having to type all the connection details out every time you want to connect to the database, you can configure some connection defaults in the sql-postgres-login-params variable. You could set these as directory-local variables in your project:

;; /path/to/project/.dir-locals.el
((sql-mode . ((sql-postgres-login-params 
  '((user :default "username")
    (database :default "app_development")
    (server :default "localhost")
    (port :default 5432))))))

Once you have set up these defaults, M-x sql-postgres in an SQL file will immediately drop you in your database prompt.

Linking prompt and buffer

If this is all we could do with sql-mode, it would give us little more than just opening a shell and running psql manually. But this being Emacs, of course there’s more.

Running M-x sql-postgres from an SQL buffer will link the SQL buffer to the *SQL: Postgres* buffer with your database REPL. That means that you can use commands like sql-send-buffer, sql-send-paragraph and sql-send-region to send queries to the database prompt for evaluation. That way, you can use the full power of Emacs to write your SQL in a dedicated file, and only use the database REPL to evaluate them and print the results.

My favourite command is sql-send-paragraph, which is bound by default to C-c C-c by default. A paragraph in Emacs is usually delineated by blank lines, which I have found to be a good approximation of “the current query”.

Other tips and tricks

Using pgformatter

One more thing I like to do is to auto-format my SQL code. There are no great tools to do so out there, but there a few decent ones. I use pgformatter, installed via Homebrew, which gives me the pg_format program. I combine that with the sqlformat Emacs package so that my SQL buffers are automatically formatted on save:

(use-package sqlformat
  :commands (sqlformat sqlformat-buffer sqlformat-region)
  :hook (sql-mode . sqlformat-on-save-mode)
  :init
  (setq sqlformat-command 'pgformatter
        sqlformat-args '("-s2" "-g" "-u1")))

Literate programming with org-mode

Finally, I have found it to be quite useful to embed my SQL code in org-mode files using org-babel for literate programming. That means I can include my SQL code and human-language documentation in a single file and use Emacs to generate both human-readable HTML documentation — weaving in literate programming terms — and the actual source code files — called tangling.

Here’s what a typical literate programming SQL file written with org-mode might look like:

#+title: Users report

* Introduction

This report produces a listing of the number of users in our system per month.

* The query

Marketing needs the total number of users, the year and the month.

#+begin_src sql
select
  extract(year from created_at) as year,
  extract(month from created_at) as month,
  count(*) as nr_of_users
#+end_src

Note that we will have to combine the signups with the users table.

#+begin_src sql
from
  users
join
  signups using (user_id)
#+end_src

We want to group by the year and month when the user signed up, so we truncate the date to the nearest month.

#+begin_src sql
group by
  date_trunc('month', created_at)
#+end_src

List the results with latest month first, and then earlier months.

#+begin_src sql
order by
  1, 2
#+end_src

From this contrived example, we might use regular Org-mode exporting functions to produce documentation in HTML (or Markdown, or any other format). But with org-babel-tangle we can extract all the source code blocks into a single .sql file named after our org-mode file:

select
  extract(year from created_at) as year,
  extract(month from created_at) as month,
  count(*) as nr_of_users
from
  users
join
  signups using (user_id)
group by
  date_trunc('month', created_at)
order by
  1, 2

With some configuration, we can even generate multiple files, annotate files with comments or even pull external changes back into our .org file. I have found this to be a great way of documenting complex queries, as org-mode is readable enough for humans — even if they don’t use Emacs. Moreover, GitHub will neatly render .org files inline, so that you don’t even need to export to HTML yourself.

Closing thoughts

There are times when I like to use a dedicated GUI database client (my favourite is Postico). They’re great for visually exploring data by clicking around. But although most GUI editors come with query editors, these usually do not match up to the editing capabilities of Emacs. Being able to iterate on some complex SQL code with rapid feedback without the friction of switching applications is powerful. That idea is core to Emacs. Learning how to use it will be well worth your time.