5

I'm working with org-mode and org-babel to query a PostgreSQL database. It's pretty cool setup and I love it.

At the moment, I have multiple org files with the same database property configuration like the following.

#+PROPERTY: header-args:sql :dbhost myhost
#+PROPERTY: header-args:sql+ :dbuser username :database secretdb :engine postgresql :exports result

This is duplicated in each org file now. I'm wondering if there's a way to configure this in one place and org-babel pick it up from.

I also have database configuration for multiple environments. So configuration for each database is also duplicated in the respective org files. It was fine in the beginning, now I have too many files with same +#PROPERTY: value in them, and it kind of getting confusing and tedious.

I'm looking for a way to have the configuration in one place and org-babel have them pickup.

Here's is what I tried so far.

  1. #+INCLUDE: "./connection-properties.org"

You get the idea. This includes the org file, and also says Local setup has been refreshed when I do Ctrl-C-Ctrl-C. But when I execute the sql block, it fails with org-babel-execute:sql: Wrong type argument: stringp, nil

Looking for clues! Thanks!

5

I did it! Yaay!

I learned about org-global-properties here https://orgmode.org/manual/Property-syntax.html and gave a shot by setting in it in .dir-locals.el file. It works exactly as I wanted!

I have a .dir-locals.el file in each directory that represents and environment.

├── prod │   ├── .dir-locals.el │   └── database │   └── 01-queries.org │   └── 02-queries.org ├── staging │   ├── .dir-locals.el │   └── database │   └── 01-queries.org │   └── 02-queries.org ├── uat │   ├── .dir-locals.el │   └── database │   └── 01-queries.org │   └── 02-queries.org ├── localhost │   ├── .dir-locals.el │   └── database │   └── 01-queries.org │   └── 02-queries.org

.dir-locals.el file works like magic!

And here is the content of .dir-locals.el file.

((org-mode . (
              (org-global-properties . (
                                        (header-args:sql . ":dbhost host :database dbname :engine postgresql :exports result")
                                        )))))

Instead of using :dbpassword in org-global-properties, I set it in ~/.pgpass file.

This serves my purpose, but if you have any suggestions for improvements please comment or give a separate answer. Thank you :)

| improve this answer | |
  • how exactly you are loading this .dir-locals.el in org file? – Mateusz Tocha Jan 26 at 16:01
  • @MateuszTocha - .dir-locals.el should be loaded automatically when you visit any files in the same directory as the .dir-locals.el file. In addition, emacs should prompt you for confirmation to allow directory local variables to execute. Hope that helps. – Melioratus Feb 2 at 14:20
2

If you want to configure it in .org format instead of Lisp, so that you can keep your property declarations the between files as in a specific file, then you can use the #+SETUPFILE setting:

The setup file or a URL pointing to such file is for additional in-buffer settings. Org loads this file and parses it for any settings in it only when Org opens the main file. If URL is specified, the contents are downloaded and stored in a temporary file cache. C-c C-c on the settings line parses and loads the file, and also resets the temporary file cache. Org also parses and loads the document during normal exporting process. Org parses the contents of this document as if it was included in the buffer. It can be another Org file. To visit the file—not a URL—use C-c ' while point is on the line with the file name.

If .dir-locals.el works for you, then at one level I can't argue with that. But I prefer this approach to .dir-locals.el because

  1. Even though I know Emacs Lisp, the code in .dir-locals.el to set properties, particularly if I am setting very many, is not as simple as Org's native syntax for it.
  2. The pure Org solution is more fluid, allowing you to extract properties (and other settings) without having to reformat them into S-expressions. There is something more aesthetically pleasing to me about it as well, to let Org configure Org.
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.