5 ms·

Using Emacs Org-Mode With Databases: A getting-started guide

142 points by adityaathalye 1y ago

robbingtherob 1y ago
[dead]
sondr3 1y ago
I used Org Mode for all mandatory assignments [0] when I was a student, it worked super well and I could create well formatted LaTeX documents/PDFs from it as well. The supervisors were very impressed that I did my assignment in LaTeX, and I could never be bothered to correct them :-) [0]: https://gist.github.com/sondr3/ae4eda2816cfeda7b8597ce1c48d0555 https://gist.github.com/sondr3/ae4eda2816cfeda7b8597ce1c48d0..., best viewed as the raw file for all the details
- adityaathalye 1y ago
  Very much this. In fact, I had made a note about earning "professor points", in my (giant) blog post [0] describing my org-mode use... 6. Professor Points: I also get intermediate tex output for free, which I don't need, but may please you if you are a professor (or said professor's hopelessly sleep derived grad students). --- [0] Discussed here recently: Why and How I use “Org Mode” for my writing and more (2022) (evalapply.org) 217 points by sebg | 6 months ago | 64 comments https://news.ycombinator.com/item?id=43157672 https://news.ycombinator.com/item?id=43157672
  - dotancohen 1y ago
    ^derived^deprived
    - adityaathalye 1y ago
      ah ... hahaahhh ... quite possibly, I was sleep, ah, derived at the time of finishing it up ... i shall let the typo remain, to vex yet another eagle-eyed reader
  - Y_Y 1y ago
    YMMV, my probability professor forbade typeset homework submissions, regardless of how well presented they were. I don't recommend SP's section for MA421, regardless of the excellent teaching quality.
- schonfinkel 1y ago
  Kudos to you, my biggest regret nowadays was not learning how to use orgmode (and org-roam) in college. As long as you set up a proper environment and have an org-publish config you barely need to bother with verbose LaTeX documents. https://www.orgroam.com/ https://www.orgroam.com/
- tlavoie 1y ago
  I had an amusing incident that was related, quite some years back as a CS undergrad. One prof had quite poor eyesight, so had a no-pencil rule for assignments. Typed was OK, neat pen was fine also. (My writing has always been atrocious, typed it is.) As it happened, I used LaTeX (and I knew she did also.) She also had a rule about promptness of assignment delivery, meaning the start of class when she arrived. Fair enough! On this one assignment, I had done all but the last question at home, and printed it off, with the hope that I could finish it at the lab and send it to a laser printer in another building. At the time, labs used shared, dot-matrix printers that were usually jammed. I got the last question done, sent it to the printer, then checked the queue. Apparently someone was printing a book, not going to get done before class. I emailed her my .tex file, with the explanation that I'd hand in my incomplete printout in class, but would appreciate if she could mark the last section so I knew how I did. This was sent well before class, as the timestamp would show. The prof ended up printing, marking, and returning this version, and I received full marks!
ibizaman 1y ago
I used a similar setup a long while ago and wanted a way to connect to databases easily. So I wrote some code to parse the MySQL and Postres config files to extract database info in Emacs: https://stackoverflow.com/a/46114944 https://stackoverflow.com/a/46114944
adityaathalye 1y ago
My “Poor Man’s SQL Workbench” trick is to… Use org-babel to execute the queries, from my “sql-queries.org” file. Overwrite results into a /tmp file, also an org-mode file, with latest query results (call it anything — “query-result.org”). Make psql spit out org-formatted tables for extra oomph. Keep that results file open in another buffer (or frame on a big screen, if the table is wide). Let Emacs auto-refresh the buffer, when it detects the file has changed. Et voila! L’établi SQL du pauvre. Screenshot + sample code in the gist below (a `.org` file, as befits this topic :) https://gist.github.com/adityaathalye/a6004acd34c683bcc806b2a3df6b1cec https://gist.github.com/adityaathalye/a6004acd34c683bcc806b2...
jhoechtl 1y ago
Emacs with databases without org would be interessing. I now there is sql-mode which supposedly works to connect with an inferior process but a tuorial about that would be nice
- mediumsmart 1y ago
  I second that and would be interested in the sqlite-mode version of the tutorial.
- emarsden 1y ago
  You may be interested in PGmacs, a viewing/editing interface for PostgreSQL. https://github.com/emarsden/pgmacs https://github.com/emarsden/pgmacs (self-plug)
- sceadu 1y ago
  I also use: https://github.com/kostafey/ejc-sql https://github.com/kostafey/ejc-sql for stuff not natively supported by sql-mode in emacs FWIW
- foobarqux 1y ago
  M-x sql-sqlite Then C-c C-c on paragraph you want to send/execute M-x sql-send- for the other commands The mode is called sql-interactive-mode That's it.
schonfinkel 1y ago
You can also set a `.pgpass` in your `$HOME` or point another path to a `PGPASSFILE` envar, so postgresql credentials don't leak to main org file. https://www.postgresql.org/docs/current/libpq-pgpass.html https://www.postgresql.org/docs/current/libpq-pgpass.html
- iLemming 1y ago
  I think even better to encrypt the password in ~/.authinfo.gpg, e.g., machine my_db login my_usr password bla-bla-123 And then in the source block header, you can inject it into a variable :var pass=(auth-source-pick-first-password :host "my_db") That's a built-in elisp function. Yes, you can call arbitrary functions to assign vars for org-mode source blocks. You can even assign shell command output, I often do things like: :var token=(shell-command-to-string "echo $MY_TOKEN") What's crazy about this shit is that you can even pipe the content of the file back into itself: #+begin_src sh :pretty :var token=(shell-command-to-string (format "grep my_token %s" buffer-file-name)) echo $token #+end_src That's an absurd bananas degree of ingenious engineering that makes you question the sanity of programmers who deliberately abandon Emacs, even after experiencing such power.
stebalien 1y ago
You can also combine this with TRAMP to work on databases you can't directly access from your local machine. E.g.: #+header: :dir /ssh:user@remote-host:/ #+begin_src sql .. #+end_src
olivierestsage 1y ago
Slight off-topic, but getting organized was a lifelong struggle for me (I was the person who had a five-foot high stack of unorganized papers and crap in their locker in middle and high school), and org-mode is the only system that broke me out of that cycle. It has genuinely changed my life. It's not for everyone, but if you've been unsatisfied with everything else and keep jumping from system to system, it's worth a try. (Especially now that you can get Claude to just explain the basics of Emacs configuration to you, which wasn't the case when I started.)
- Barrin92 1y ago
  >Especially now that you can get Claude to just explain the basics of Emacs configuration to you, which wasn't the case when I started although also worth pointing out that Emacs often leads with the adjective self-documenting for a reason. Emacs has fantastic facilities built in to get information on virtually anything. Tutorials with C-h t, manuals with C-h r, apropos and describe- to search for any object etc. And pretty much any major extension like org has great documentation. Which btw I think is well worth reading or at least skimming to get an idea on how much you can do with it.
- andnand 1y ago
  Orgmode has been on the back of my mind forever. Ive been working on implementing a system recently. My hesitation with orgmode is that I'd like to share calendar/todo list with my non technically minded spouse and I like Vim. Any suggestions?
  - Carrok 1y ago
    I basically just wrote my answers in a neighboring comment, but I would try Spacemacs (default is Evil mode aka vim mode) backed with an Obsidian vault. I share a subfolder of the vault with my wife via iCloud, who can open it up on her phone and see the content and make edits in a non-techie way.
  - uint32_t 1y ago
    I'm in the exact same boat. I've started exploring https://github.com/nvim-orgmode/orgmode https://github.com/nvim-orgmode/orgmode and it seems to be a pretty accurate emulation of Emacs Orgmode, or at least I haven't run into its limitations yet since I'm just starting to explore it. I still haven't solved the sharing of calender/todo info but this page has some ideas that could work to varying degrees: https://orgmode.org/worg/org-tutorials/org-google-sync.html https://orgmode.org/worg/org-tutorials/org-google-sync.html
PiraticSomate 1y ago
[dead]

142 points by adityaathalye 1y ago

[dead]

I used Org Mode for all mandatory assignments [0] when I was a student, it worked super well and I could create well formatted LaTeX documents/PDFs from it as well. The supervisors were very impressed that I did my assignment in LaTeX, and I could never be bothered to correct them :-) [0]: https://gist.github.com/sondr3/ae4eda2816cfeda7b8597ce1c48d0555 https://gist.github.com/sondr3/ae4eda2816cfeda7b8597ce1c48d0..., best viewed as the raw file for all the details

adityaathalye 1y ago
Very much this. In fact, I had made a note about earning "professor points", in my (giant) blog post [0] describing my org-mode use... 6. Professor Points: I also get intermediate tex output for free, which I don't need, but may please you if you are a professor (or said professor's hopelessly sleep derived grad students). --- [0] Discussed here recently: Why and How I use “Org Mode” for my writing and more (2022) (evalapply.org) 217 points by sebg | 6 months ago | 64 comments https://news.ycombinator.com/item?id=43157672 https://news.ycombinator.com/item?id=43157672
- dotancohen 1y ago
  ^derived^deprived
  - adityaathalye 1y ago
    ah ... hahaahhh ... quite possibly, I was sleep, ah, derived at the time of finishing it up ... i shall let the typo remain, to vex yet another eagle-eyed reader
- Y_Y 1y ago
  YMMV, my probability professor forbade typeset homework submissions, regardless of how well presented they were. I don't recommend SP's section for MA421, regardless of the excellent teaching quality.
schonfinkel 1y ago
Kudos to you, my biggest regret nowadays was not learning how to use orgmode (and org-roam) in college. As long as you set up a proper environment and have an org-publish config you barely need to bother with verbose LaTeX documents. https://www.orgroam.com/ https://www.orgroam.com/
tlavoie 1y ago
I had an amusing incident that was related, quite some years back as a CS undergrad. One prof had quite poor eyesight, so had a no-pencil rule for assignments. Typed was OK, neat pen was fine also. (My writing has always been atrocious, typed it is.) As it happened, I used LaTeX (and I knew she did also.) She also had a rule about promptness of assignment delivery, meaning the start of class when she arrived. Fair enough! On this one assignment, I had done all but the last question at home, and printed it off, with the hope that I could finish it at the lab and send it to a laser printer in another building. At the time, labs used shared, dot-matrix printers that were usually jammed. I got the last question done, sent it to the printer, then checked the queue. Apparently someone was printing a book, not going to get done before class. I emailed her my .tex file, with the explanation that I'd hand in my incomplete printout in class, but would appreciate if she could mark the last section so I knew how I did. This was sent well before class, as the timestamp would show. The prof ended up printing, marking, and returning this version, and I received full marks!

ibizaman 1y ago

I used a similar setup a long while ago and wanted a way to connect to databases easily. So I wrote some code to parse the MySQL and Postres config files to extract database info in Emacs: https://stackoverflow.com/a/46114944 https://stackoverflow.com/a/46114944

adityaathalye 1y ago

My “Poor Man’s SQL Workbench” trick is to… Use org-babel to execute the queries, from my “sql-queries.org” file. Overwrite results into a /tmp file, also an org-mode file, with latest query results (call it anything — “query-result.org”). Make psql spit out org-formatted tables for extra oomph. Keep that results file open in another buffer (or frame on a big screen, if the table is wide). Let Emacs auto-refresh the buffer, when it detects the file has changed. Et voila! L’établi SQL du pauvre. Screenshot + sample code in the gist below (a `.org` file, as befits this topic :) https://gist.github.com/adityaathalye/a6004acd34c683bcc806b2a3df6b1cec https://gist.github.com/adityaathalye/a6004acd34c683bcc806b2...

jhoechtl 1y ago

Emacs with databases without org would be interessing. I now there is sql-mode which supposedly works to connect with an inferior process but a tuorial about that would be nice

mediumsmart 1y ago
I second that and would be interested in the sqlite-mode version of the tutorial.
emarsden 1y ago
You may be interested in PGmacs, a viewing/editing interface for PostgreSQL. https://github.com/emarsden/pgmacs https://github.com/emarsden/pgmacs (self-plug)
sceadu 1y ago
I also use: https://github.com/kostafey/ejc-sql https://github.com/kostafey/ejc-sql for stuff not natively supported by sql-mode in emacs FWIW
foobarqux 1y ago
M-x sql-sqlite Then C-c C-c on paragraph you want to send/execute M-x sql-send- for the other commands The mode is called sql-interactive-mode That's it.

schonfinkel 1y ago

You can also set a `.pgpass` in your `$HOME` or point another path to a `PGPASSFILE` envar, so postgresql credentials don't leak to main org file. https://www.postgresql.org/docs/current/libpq-pgpass.html https://www.postgresql.org/docs/current/libpq-pgpass.html

iLemming 1y ago
I think even better to encrypt the password in ~/.authinfo.gpg, e.g., machine my_db login my_usr password bla-bla-123 And then in the source block header, you can inject it into a variable :var pass=(auth-source-pick-first-password :host "my_db") That's a built-in elisp function. Yes, you can call arbitrary functions to assign vars for org-mode source blocks. You can even assign shell command output, I often do things like: :var token=(shell-command-to-string "echo $MY_TOKEN") What's crazy about this shit is that you can even pipe the content of the file back into itself: #+begin_src sh :pretty :var token=(shell-command-to-string (format "grep my_token %s" buffer-file-name)) echo $token #+end_src That's an absurd bananas degree of ingenious engineering that makes you question the sanity of programmers who deliberately abandon Emacs, even after experiencing such power.

stebalien 1y ago

You can also combine this with TRAMP to work on databases you can't directly access from your local machine. E.g.: #+header: :dir /ssh:user@remote-host:/ #+begin_src sql .. #+end_src

olivierestsage 1y ago

Slight off-topic, but getting organized was a lifelong struggle for me (I was the person who had a five-foot high stack of unorganized papers and crap in their locker in middle and high school), and org-mode is the only system that broke me out of that cycle. It has genuinely changed my life. It's not for everyone, but if you've been unsatisfied with everything else and keep jumping from system to system, it's worth a try. (Especially now that you can get Claude to just explain the basics of Emacs configuration to you, which wasn't the case when I started.)

Barrin92 1y ago
>Especially now that you can get Claude to just explain the basics of Emacs configuration to you, which wasn't the case when I started although also worth pointing out that Emacs often leads with the adjective self-documenting for a reason. Emacs has fantastic facilities built in to get information on virtually anything. Tutorials with C-h t, manuals with C-h r, apropos and describe- to search for any object etc. And pretty much any major extension like org has great documentation. Which btw I think is well worth reading or at least skimming to get an idea on how much you can do with it.
andnand 1y ago
Orgmode has been on the back of my mind forever. Ive been working on implementing a system recently. My hesitation with orgmode is that I'd like to share calendar/todo list with my non technically minded spouse and I like Vim. Any suggestions?
- Carrok 1y ago
  I basically just wrote my answers in a neighboring comment, but I would try Spacemacs (default is Evil mode aka vim mode) backed with an Obsidian vault. I share a subfolder of the vault with my wife via iCloud, who can open it up on her phone and see the content and make edits in a non-techie way.
- uint32_t 1y ago
  I'm in the exact same boat. I've started exploring https://github.com/nvim-orgmode/orgmode https://github.com/nvim-orgmode/orgmode and it seems to be a pretty accurate emulation of Emacs Orgmode, or at least I haven't run into its limitations yet since I'm just starting to explore it. I still haven't solved the sharing of calender/todo info but this page has some ideas that could work to varying degrees: https://orgmode.org/worg/org-tutorials/org-google-sync.html https://orgmode.org/worg/org-tutorials/org-google-sync.html

PiraticSomate 1y ago

[dead]