SQLite Source Code Blocks in Org Mode

See the SQLite download page for installation and configuration instructions.

Emacs' SQL mode supports the SQLite server. SQL mode is used to edit SQLite source code blocks.

The ob-sqlite.el file is part of Emacs. To activate SQLite as a Babel language, simply add (sqlite . t) to the org-babel-do-load-languages function in your Emacs configuration file, as shown below:

(org-babel-do-load-languages
 'org-babel-load-languages (quote ((emacs-lisp . t)
                                    (sqlite . t)
                                    (R . t)
                                    (python . t))))

Babel uses the SQLite command line shell sqlite3 to evaluate SQL statements. The name of the shell is held in the variable org-babel-sqlite3-command.

SQLite sessions are not supported.

SQLite source code blocks typically return the results of a query. The header arguments :csv, :column, :line, :list, and :html determine the output format.

#+name: sqlite-populate-test
#+header: :results silent
#+header: :dir ~/temp/
#+header: :db test-sqlite.db
#+begin_src sqlite
create table greeting(one varchar(10), two varchar(10));
insert into greeting values('Hello', 'world!');
#+end_src

#+name: sqlite-hello
#+header: :list
#+header: :separator \ 
#+header: :results raw
#+header: :dir ~/temp/
#+header: :db test-sqlite.db
#+begin_src sqlite
select * from greeting;
#+end_src

#+results: sqlite-hello
Hello world!

Note that db and dir together specify the path to the file that holds the SQLite database.

In this example we create a variable with the name of the relation to query and a value to use in a query where clause. Note that the replacement excludes the quotes of string variables.

#+BEGIN_SRC sqlite :db /tmp/rip.db :var rel="tname" n=300 :colnames yes
drop table if exists $rel;
create table $rel(n int, id int);
insert into $rel(n,id) values (1,210), (3,800);
select * from $rel where id > $n;
#+END_SRC

#+RESULTS:
| 3 | 800 |

We can also pass a table to a query. In this case, the contents of the table are exported as a csv file that can then be imported into a relation:

#+NAME: tableexample
| id |  n |
|----+----|
|  1 |  5 |
|  2 |  9 |
|  3 | 10 |
|  4 |  9 |
|  5 | 10 |

#+begin_src sqlite :db /tmp/rip.db :var orgtable=tableexample :colnames yes
drop table if exists testtable;
create table testtable(id int, n int);
.mode csv testtable
.import $orgtable testtable
select n, count(*) from testtable group by n;
#+end_src

#+RESULTS:
|  n | count(*) |
|----+----------|
|  5 |        1 |
|  9 |        2 |
| 10 |        2 |

If dropping/overwriting a table is undesirable, a temporary SQL table can be used to insert new values into an existing table:

#+begin_src sqlite :db /tmp/rip.db :var orgtable=tableexample :colnames yes
create temporary table temp_table(id int, n int);
.mode csv testtable
.import $orgtable temp_table
insert into existing_table (id, n) select id,n from temp_table;
#+end_src

Org tables can be used to conveniently display some data from a SQLite table, allow the user to edit it in Org, and the re-insert the updated data into the underlying SQLite table. Do this by naming the results table, then using it as input to another SQLite block that updates rows. If your table has a primary key, you'll definitely want to use it to make sure the correct rows are edited.

#+begin_src sqlite :db /tmp/reviews.db
select id,title,rating from bookreview where rating is null;
#+end_src

#+name: ratings
#+RESULTS:
|  5 | To Kill a Mockingbird | null |
| 12 | Three Body Problem    | null |

#+begin_src sqlite :db /tmp/reviews.db :var ratings=ratings
create temporary table updates (id, title, rating);
.mode csv updates
.import $ratings updates
update bookreview set rating = (select rating from updates
where bookreview.id = updates.id)
where exists (select * from updates where updates.id = bookreview.id);

By editing the intermediary table to replace "null" values with a numerical rating, and then running the second source block, the SQLite table will be updated correctly.

Sometimes it is useful to open the database in readonly mode to make sure no modifications are made to it.

#+begin_src sqlite :readonly yes :db /tmp/test.db
create table atable(a int, b int);
#+end_src

Attempting to run this block will generate the following error:

Runtime error near line 2: attempt to write a readonly database (8)
[ Babel evaluation exited with code 1 ]