For every web app I work on, a database client is always present for exploring data and building complex queries. Recently, I have moved on from my PgAdmin to org-mode for this purpose, because why not.
Org-mode allows executing code snippets and can show results in a nice tabular form. But org-mode isn't written by Microsoft. So it don't allow arbitrary code execution without our explicit consent. To tell org-mode that it is okay to execute SQL snippets, we need to run following elisp snippet:
(with-eval-after-load 'org (org-babel-do-load-languages 'org-babel-load-languages '((sql . t))))
Org-mode has a number of header arguments for SQL blocks which can be used to configure how a particular SQL source-code snippet is executed. Most important ones perhaps are:
:enginewhich tells which database to run the SQL against
:dbhostto configure the database host, most likely
localhostfor a dev environment
:dbpasswordfor database authentication
:databasefor database name
By default org-mode runs the code snippets in current OS environment. I use this fact to get the ability to run SQL against our postgres database without passing most of the above arguments. Postgres allows setting some environment variables, which it then use to for making connections by default. In my flake.nix file, I setup the required environment variables:
PGDIR=$PWD/storage/postgres export PGDATA=$PGDIR/data export PGHOST=$PGDIR/run export PGDATABASE=entropy export DATABASE_URL="postgresql:///$PGDATABASE?host=$PGHOST" # not required by postgres, but will be helpful
With this, only header option left to set is
:engine, which I prefer to
configure globally for my sql-playground.org since I usually have one database
for my personal projects. You can check this commit on Entropy project for how I
have setup my sql-playground there. Following snippet on top of the
sql-playground.org does the trick:
#+PROPERTY: header-args:sql :engine postgres
Now any SQL snippets that we create can be executed against our development
database right within the org file. For example, pressing
C-c C-c in following
SQL block produces output shown below that.
#+name: events #+begin_src sql SELECT events.title, events.slug, start_time, events.id, events.source, format('%s mins', extract(epoch from (events.end_time - events.start_time))/60) FROM events; #+end_src #+RESULTS: events | title | slug | start_time | end_time | source | format | |--------------------+----------------------+---------------------+---------------------+--------+---------| | Lol bro what | lol-rofl-lmao | 2021-11-29 12:30:00 | 2021-11-29 13:30:00 | local | 60 mins | | Nested event title | nested-lol-rofl-lmao | 2021-10-29 12:30:00 | 2021-10-29 13:30:00 | local | 60 mins |
Building SQL queries is my primary use-case, and org-mode configured so far serves that purpose. But since I am putting all this effort in writing this blog post anyway, I thought let's go a step further and try configuring LSP mode for SQL snippets in my org file. To my surprise, it is possible. SQLS provides an LSP server for SQL files, which once configured can provide certain features like auto-completing table and column names. I have created a .dir-locals file for adding directory local variables to configure LSP to connect to correct database.
((nil . ((eval (lambda () (setq lsp-sqls-connections `(((driver . "postgresql") (dataSourceName . ,(getenv "DATABASE_URL"))))))))))
This snippet isn't actually the right way configuring directory local variables,
becuase it sets
lsp-sqls-connections globally. This is the result of me
time-boxing the task of evaluating
(getenv "DATABASE_URL"), so I won't need to
duplicate my database configuration here.
LSP for org-mode also need to provide a file to which it can tangle source blocks. So I added another snippet to top of my org file.
#+PROPERTY: header-args:sql :tangle test.sql
I can now run
lsp-org in an SQL block, and 8/10 times it has lsp configured
properly. It isn't flawless though, but that is expected since lsp support in
org-snippets is in alpha stage right now.
I also tried setting up lsp support in edit buffer for SQL statements (which you
get by pressing
C-c ' in org src blocks), but because of a bug in lsp-mode,
that didn't work out too well.
(defun org-babel-edit-prep:sql (babel-info) (setq-local buffer-file-name (->> babel-info caddr (alist-get :tangle))) (setq-local lsp-buffer-uri (->> babel-info caddr (alist-get :tangle) lsp--path-to-uri)) (setq-local lsp-headerline-breadcrumb-enable nil) (lsp))
Problems with this setup
There are 2 main problems I face during my primary use-case.
- Org tables don't work well if a column has a lot of data. e.g
eventmesses up the table real bad. lsp-mode allow executing queries which don't suffer from this.
- No LSP support in edit buffer is a major bummer.