Posts tagged "Emacs":

2024-10-02

SQL queries with org-babel

Recently, I had to do queries on SQL databases and tried Emacs org-babel SQL integration. I set up sql-connection-alist with all necessary information for database connections except the passwords and configure this in a (use-package sql) section. Since sql-connection-alist is used in other packages, this is a portable way to configure database connections.

(setq sql-connection-alist
      '((db1 (sql-product 'oracle)
             (sql-port 1521)
             (sql-server "db1.server.org")
             (sql-user "username")
             (sql-database "db1"))))

Because it is a bad idea to have passwords saved somewhere unencrypted, they are stored in ~/.authinfo.gpg:

machine db1.server.org/db1 login username password ***** port 1521

Database host and database name are concatenated with a slash to an entry assigned to machine. While one could probably add another key/value pair, doing it this way makes it quite easy to use, as you will see in the following function for looking up the credentials:

(defun my/sql-auth-source-search-wallet (wallet product user server database port)
  "Read auth source WALLET to locate the USER secret.
Sets `auth-sources' to WALLET and uses `auth-source-search' to locate the entry.
The DATABASE and SERVER are concatenated with a slash between them as the
host key."
  (when-let (results (auth-source-search :host (concat server "/" database)
                                         :user user
                                         :port (number-to-string port)))
    (when (and (= (length results) 1)
               (plist-member (car results) :secret))
      (plist-get (car results) :secret))))

Now hook it up…

(setq sql-password-search-wallet-function #'my/sql-auth-source-search-wallet)
(setq sql-password-wallet "~/.authinfo.gpg")

…and use it in an org mode source block:

#+BEGIN_SRC sql-mode :product oracle :dbconnection db1 :results raw
  SELECT to_char(sysdate, 'YYYY-MM-DD HH24:ii:ss') AS today,
         to_char(sysdate + 1, 'YYYY-MM-DD HH24:ii:ss') AS tomorrow
    FROM dual;
#+END_SRC