Org-mode and SQL

Use literate programming to write and document SQL queries directly in Emacs.

While learning about Common Lisp and Emacs, I discovered this post about running a bakery using Emacs and org-mode¹. The approach combines literate programming principles²—mixing natural language and code—with database work. This post explains how to set up Emacs for SQL development using org-mode and org-babel.

Follow these steps to be able to use SQL and org-mode:

1. Configure org-mode in your emacs configuration file. Find my emacs configuration file here.
2. Configure org-babel, which ships with org-mode, so there’s nothing extra to install. Enable these languages:

;; Run/highlight code using babel in org-mode
(org-babel-do-load-languages
 'org-babel-load-languages
 '(
   (lisp . t)
   (shell . t)
   (sql . t)
   ))
;; Syntax highlight in #+BEGIN_SRC blocks
(setq org-src-fontify-natively t)
;; Don't prompt before running code in org
(setq org-confirm-babel-evaluate nil)

3. Configure database connection details in org-mode. You can set these per block, as shown in the documentation, or at the section level:

* Your section name
  :PROPERTIES:
  :header-args:sql: :engine postgresql :dbport 5432 :dbhost 127.0.0.1 :dbuser testuser :dbpassword password :database testdb
  :END:

The password here is for a throwaway local database. For anything real, keep credentials out of the org file: store them in an encrypted auth-source file such as ~/.authinfo.gpg (or, for PostgreSQL, ~/.pgpass) and drop :dbpassword.

🡹

Footnotes

1. Org-mode is an Emacs extension for working with structured text. It supports literate programming through org-babel, spreadsheets, task management, and more. While powerful for these tasks, many users still prefer pen and paper for personal task management. ↩

2. Literate programming combines natural language and code snippets to express programs as a single document. This reference offers more detail. Donald Knuth originated the concept; modern implementations include Jupyter notebooks. ↩