How can I query an existing SQLite database from elisp, that is, non-interactively?

Question

There are multiple questions with "sqlite" in the title, but they all want to run SQL queries interactively, which sql-mode does.

What I want is the following:

I have a pre-existing SQLite database whose format I know. This database is maintained and updated by another program, so I cannot just convert it to a different format and work with that.

I now want to SELECT some stuff from a table in that database and get the result into a variable in elisp (specifically, this will be used to build a helm source).

emacsql-sqlite almost does this, but it requires all entries in the database to be valid sexpressions, which I cannot enforce in this database.

Is there a package without this limitation? I would rather avoid calling sqlite3 and parsing the output myself, because that will definitely break things in the future.

Answer 1

Soon... (maybe early 2023)

Starting from version 29, native SQLite support will be included in Emacs. I just compiled it, and I took a look at sqlite-mode.el to see what kinds of functions it offered.

(declare-function sqlite-execute "sqlite.c")
(declare-function sqlite-more-p "sqlite.c")
(declare-function sqlite-next "sqlite.c")
(declare-function sqlite-columns "sqlite.c")
(declare-function sqlite-finalize "sqlite.c")
(declare-function sqlite-select "sqlite.c")
(declare-function sqlite-open "sqlite.c")

I then opened up M-x ielm to try it out as well.

ELISP> (setq x/file "/my/db")
"/my/db"
ELISP> (setq x/db (sqlite-open x/file))
#<sqlite db=0x55b423800028 name=/my/db>
ELISP> (sqlite-select x/db "SELECT * FROM forum")
((0 nil "Forum")
 (1 0 "States & Cities")
 (39 1 "California")
 (477 39 "Los Angeles")
 (483 39 "Orange County")
 (491 39 "San Diego")
 (496 39 "Santa Barbara"))

I think this is the functionality you're looking for.

Performance

I tried selecting from a post table with 100k+ rows in it. First I tried it in Emacs.

ELISP> (sqlite-select x/db "SELECT count(*) FROM post")
((103604))

ELISP> (benchmark-run-compiled (sqlite-select x/db "SELECT * FROM post"))
(2.654469372 1 0.34534770399999815)

Then I tried it from the command line sqlite3 client.

sqlite> .timer on
sqlite> SELECT count(*) FROM post;
103604
Run Time: real 0.001 user 0.000378 sys 0.000897

sqlite> SELECT * FROM post;
-- result omitted
Run Time: real 1.143 user 0.261333 sys 0.534172

Answer 2

You might want to investigate Org Babel, Org mode's source evaluation facility. There is an ob-sqlite backend that you can use for straight sqlite3 queries that return the results in various forms, in particular as a list that can be used to initialize an Emacs Lisp variable. Here's a simple example from an existing music database:

#+name: titles
#+begin_src sqlite :db ~/.config/banshee-1/banshee.db :results list
select albumid, title from corealbums;
#+end_src

#+begin_src elisp :var x=titles
(setq dbtitles x)
#+end_src

The second source block evaluates the first source block assigning the result to a variable x, which in turn is used to set the variable dbtitles. Use C-h v dbtitles to see the value. The format of the result is like this:

((145 "Unknown Album")
 (500 "The 3 Symphonies and the Poem of Ecstasy")
 (514 "Sacrificium")
 (515 "Songs")
 ...
)

a list of lists, each of which consists of the selected fields in the query.

You can execute source blocks non-interactively too, by using the org-sbe macro - do C-h f org-sbe for details. The whole thing might feel a bit rickety, but I believe it can be made to work. However, you are going to have to make sure that there aren't too many rows returned: Org mode has a limit of 999 rows in a table; you can make that larger, but the evaluation of the result might be too slow (that's part of the reason for the 999 limit).

Answer 3

The sqlite library on MELPA looks to do what you want.