SQL in Org-Mode

Everything in org-mode

Published April 17, 2021 #emacs, #sql, #org-mode

I do a lot of sql, and I do a lot of org-mode. Lets put them together.

Native SQLite

In org-babel-load-languages turn on sqlite. (You can use customize for this if you don't have another method in use.)

Then you have a source block that looks like

  _#+begin_src sqlite :db test.db :colnames yes
  create table if not exists  greeting(one varchar(10), two varchar(10));
  insert into greeting values( 'Hello', 'world!');
  select * from greeting;
  _#+end_src

(Remove the _ if you are copy and pasting.)

And that will print out

  create table if not exists  greeting(one varchar(10), two varchar(10));
  insert into greeting values( 'Hello', 'world!');
  select * from greeting;
onetwo
Helloworld!

You can also set the :db property globally in your org file with:

#+PROPERTY: header-args :db test-sqlite.db

And then reload the file.

org-sql-mode

Install ob-sql-mode using the normal methods. This will use sql-mode to run queries, which lets you use other databases than sqlite3.

  (use-package ob-sql-mode
    :ensure t)

In org-babel-load-languages turn on sql.

And then you can run queries like so:

  _#+begin_src sql-mode :product postgres
    select * from commits;
  _#+end_src

There's more to it but that gives you the rough idea!

References

  1. https://www.reddit.com/r/emacs/comments/bb5mnv/sql_workflows_inside_emacs_anyone_have_a_nice/

  2. https://orgmode.org/worg/org-contrib/babel/languages/ob-doc-sqlite.html

  3. https://github.com/nikclayton/ob-sql-mode

  4. https://orgmode.org/manual/Using-Header-Arguments.html#Using-header-arguments

Read next

See also

Setting up emacs for typescript development

If we are going to bother with static types, might as well use them

I've been playing with deno and typescript, so I thought I'd document how to setup a basic tide environment with emacs. Prerecs Things will go better if you have nativejson support in your emacs build. I build from scratch which is super easy, but to check what you have you can run the following elisp: (if (functionp 'json-serialize) (message "Native JSON is available") (message "Native JSON is *not* available")) Native JSON is available You also will need Node > 0.

Read more

Emacs Blog Writing and Navigation Mode

emacs and hugo sitting in a tree

This blog is basically my labnotes where I explore different parts of technology. Almost all of my coding related activity starts off in this repo, while I explore different things to see how they work. I have a lot of things in drafts, and I wanted to learn how to build a simple emacs interface to let me navigate around my file system. I couldn't find any good documentation on how to do anything with tabulated-list-mode so I spend the evening poking around and seeing how it works.

Read more

Installing emacs on buster

so many ways to get software

I've already written about installing emacs-snapshot on debian buster, here are two additional ways. The first is to use flatpak, and the other is to build from source. Flatpack sudo apt-get install flatpak sudo apt install gnome-software-plugin-flatpak sudo flatpak remote-add --if-not-exists flathub https://flathub.org/repo/flathub.flatpakrepo Then: sudo flatpak install flathub org.gnu.emacs And running it: flatpak run org.gnu.emacs You can then pin it in your dock and you are good to go.

Read more