2

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.

3
  • One option would be to modify the library that almost does what you want and add whatever features are needed.
    – lawlist
    Dec 10, 2022 at 19:10
  • emacs.stackexchange.com/tags/elisp/info
    – Drew
    Dec 10, 2022 at 22:15
  • @lawlist That is an option, but I do not consider myself proficient enough to do that. I was hoping that there is some tool or library out there that I missed. If there is no simple solution, I guess I will have to fall back to using sqlite3 to convert the table to JSON and then parse it into Emacs. Dec 10, 2022 at 23:05

4 Answers 4

3

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
2
  • This is great! How performant is it? If it works fast, I can wait a few months until it goes upstream. Dec 12, 2022 at 11:39
  • @BettaGeorge - I updated the answer with a performance comparison between Elisp sqlite functions from emacs29 and the command line sqlite3 client.
    – g-gundam
    Dec 12, 2022 at 17:42
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).

0

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

1
  • It does indeed do exactly what I want, but it is so slow (compared to using the sqlite3 CLI and then parsing the dump myself) that I consider it unusable. Dec 10, 2022 at 22:55
0

It looks like https://github.com/pekingduck/emacs-sqlite3-api also fits your requirements as well and has the advantage of being usable from Emacs 25.1 onwards.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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