Org-mode and SQL

Thomas Dickson

2 minute read



In this post I describe how to use org-mode and org-babel to run SQL queries.

Recently I’ve enjoyed dipping my toe into learning about Common Lisp and Emacs. Along the way I came across this post about running a bakery using Emacs and org-mode1. I thought the ability to use literate programming principles 2 to help develop on and work with a database to be quite empowering. This post is about how I’ve set up Emacs to allow me to use SQL.

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. Install and configure org-babel. This is how I’ve set up org-babel for my work:
1
2
3
4
5
6
7
8
9
10
11
12

;; 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)
  1. Pass the connection details to your SQL database of choice to org-mode. This can be done per block, as seen in the documentation here or at section level. Here’s how I define database connection details at the section level.
1
2
3
4

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

🡹

  1. Org-mode is a really powerful Emacs extension - it enables many things with a simple format. I think it has some great features for literate programming, including org-babel and spreadsheets. However it hasn’t managed to replace the pen and paper for my life organisation and task management. ↩

  2. Literate programming is the idea that a computer program is expressed in a combination of a natural language and macros/code snippets that implement functionality. This link contains a better explanation. The original idea was expressed by Donald Knuth. A more modern implementation can be seen in https://jupyter.org/. ↩